Key Features of GoldenGate
1) Latency Reduction: In GoldenGate, Data movement across multiple systems or databases is in real-time which helps in reducing latency.
2) Consistency & Performance: Oracle GoldenGate Replication only moves committed transactions. It keeps the operations of each transaction in a controlled virtual-memory pool called a cache until the transaction is committed or rolled back. The Oracle GoldenGate cache manager makes use of the operating system’s memory management functions to ensure consistency and improve performance.
3) Support of Databases: Oracle Database versions and releases are supported, as well as a wide range of heterogeneous databases running on a variety of operating systems. Data from an Oracle database can be replicated in a heterogeneous database.
4) Simplicity: GoldenGate has a simple architecture and easy configuration.
5) Minimal Overhead: GoldenGate has a low overhead on the underlying databases and infrastructure, resulting in high performance.
Plan:
Install Oracle19c and GoldenGate19c on both the machine and configure replication
-- source
[oracle@example01 ~]$ hostname -i
172.168.100.1
[oracle@example01 ~]$
-- Target
[oracle@example02 ~]$ hostname -i
172.168.100.2
[oracle@example02 ~]$
-- Install Oracle 19c Database Software and Create Database
1) create path
mkdir -p /u01/app/oracle/product/19.3.0.0/dbhome_1
cp /software/LINUX.X64_193000_db_home.zip /u01/app/oracle/product/19.3.0.0/dbhome_1
cd /u01/app/oracle/product/19.3.0.0/dbhome_1
unzip LINUX.X64_193000_db_home.zip
$ ./runInstaller -silent -debug -force \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=dba \
ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
DECLINE_SECURITY_UPDATES=true
Source GG:
172.168.100.1
oracle/oracle
$ pgrep -lf pmon
2226 ora_pmon_oemdb
5918 ora_pmon_democd
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName SRCGGDB -sid SRCGGDB -sysPassword sys -systemPassword system -emConfiguration NONE -datafileDestination /u01/oradata -storageType FS -characterSet AL32UTF8
export ORACLE_SID=SRCGGDB
Target GG:
[oracle@example02 scratch]$ hostname -i
172.168.100.2
oracle/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName TGTGGDB -sid TGTGGDB -sysPassword sys -systemPassword system -emConfiguration NONE -datafileDestination /u01/oradata -storageType FS -characterSet AL32UTF8
GG Installation: Source Server :
copy software to server
unzip into a folder
unzip -d GG19c_Software V983658-01.zip
cd /softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1
create folders for GG Home software:
mkdir -p /u01/goldengate/gg_home
cd /softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response
$ cat oggcore.rsp
oracle.install.responseFileVersion=/u01/goldengate/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/goldengate/gg_home
START_MANAGER=false
INVENTORY_LOCATION=/u01/app/oraInventory
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0.0.0/dbhome_1
UNIX_GROUP_NAME=dba
$ cd /softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller -silent -showProgress -waitforcompletion -responseFile /softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
[oracle@example01 Disk1]$ pwd
/softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@example01 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /softwares/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 62877 MB Passed
Checking swap space: must be greater than 150 MB. Actual 14697 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-10-20_07-33-32AM. Please wait ...You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-10-20_07-33-32AM.log
Prepare in progress.
.................................................. 10% Done.
Prepare successful.
Copy files in progress.
.................................................. 36% Done.
.................................................. 54% Done.
.................................................. 77% Done.
.................................................. 82% Done.
.................................................. 88% Done.
....................
Copy files successful.
Link binaries in progress.
..........
Link binaries successful.
Setup files in progress.
.................................................. 93% Done.
.................................................. 95% Done.
.................................................. 96% Done.
.................................................. 98% Done.
.................................................. 99% Done.
Setup files successful.
Setup Inventory in progress.
Setup Inventory successful.
.................................................. 95% Done.
Successfully Setup Software.
.................................................. 100% Done.
Finish Setup successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-10-20_07-33-32AM.log' for more details.
===============================================
GG Installation: Target Server
===============================================
-- copy software to server
unzip into a folder
unzip -d GG19c_Software V983658-01.zip
cd /software/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1
-- create folders for GG Home software:
mkdir -p /u01/goldengate/gg_home
--create response file:
[oracle@example02 response]$ pwd
/software/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response
[oracle@example02 response]$ vi oggcore.rsp
$ cat oggcore.rsp
oracle.install.responseFileVersion=/u01/goldengate/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/goldengate/gg_home
START_MANAGER=false
INVENTORY_LOCATION=/u01/app/oraInventory
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0.0/dbhome_1
UNIX_GROUP_NAME=dba
$ cd /software/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller -silent -showProgress -waitforcompletion -responseFile /software/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
log:
[oracle@example02 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /software/GG19c_Software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 19273 MB Passed
Checking swap space: must be greater than 150 MB. Actual 14946 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-10-20_07-25-03AM. Please wait ...You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-10-20_07-25-03AM.log
Prepare in progress.
.................................................. 10% Done.
Prepare successful.
Copy files in progress.
.................................................. 36% Done.
.................................................. 54% Done.
.................................................. 77% Done.
.................................................. 82% Done.
.................................................. 88% Done.
....................
Copy files successful.
Link binaries in progress.
..........
Link binaries successful.
Setup files in progress.
.................................................. 93% Done.
.................................................. 95% Done.
.................................................. 96% Done.
.................................................. 98% Done.
.................................................. 99% Done.
Setup files successful.
Setup Inventory in progress.
Setup Inventory successful.
.................................................. 95% Done.
Successfully Setup Software.
.................................................. 100% Done.
Finish Setup successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-10-20_07-25-03AM.log' for more details.
[oracle@example02 Disk1]$
=========================================================
Configure Golden Gate in both Source and Target Databases
=========================================================
1) Must be in archive log mode (source & Target)
``````````````````````````````````````````````````````````````````````````````````````````````````
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/fra
db_recovery_file_dest_size big integer 10G
SQL> select LOG_MODE from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
2) Verify that supplemental logging and forced logging are set properly.(source)
``````````````````````````````````````````````````````````````````````````````````````````````````
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES YES
3) This parameter must be changed on source and target databases:
``````````````````````````````````````````````````````````````````````````````````````````````````
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
SQL>
4) Create the administrator and user/schema owners on both source and target database.
``````````````````````````````````````````````````````````````````````````````````````````````````
-- Source
SQL> create tablespace goldengate datafile '/u01/oradata/SRCGGDB/goldengate01.dbf' size 1G autoextend on MAXSIZE UNLIMITED;
Tablespace created.
SQL> create user srcgguser identified by srcgguser default tablespace goldengate quota unlimited on goldengate;
User created.
SQL> grant create session,connect,resource,alter system to srcgguser;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'srcgguser', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
PL/SQL procedure successfully completed.
-- Target
SQL> create tablespace goldengate datafile '/u01/oradata/TGTGGDB/goldengate01.dbf' size 1G autoextend on MAXSIZE UNLIMITED;
Tablespace created.
SQL> create user tgtgguser identified by tgtgguser default tablespace goldengate quota unlimited on goldengate;
User created.
SQL> grant create session,connect,resource,alter system to tgtgguser;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'tgtgguser', privilege_type=>'*', grant_optional_privileges=>'*');
PL/SQL procedure successfully completed.
--------------set alias - source & target (optional) -----------------------------
alias src='export ORACLE_SID=SRCGGDB;export ORACLE_UNQNAME=SRCGGDB;export OGG_HOME=/u01/goldengate/gg_home;export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1;export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
alias tgt='export ORACLE_SID=TGTGGDB;export ORACLE_UNQNAME=TGTGGDB;export OGG_HOME=/u01/goldengate/gg_home;export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1;export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
---------------------------------------------------------------------------------------------------------------------------------------
5) Go to Golden Gate Installed location (in our scenario /u01/goldengate/gg_home)
and then run the following Golden Gate inbuild scripts for creating all necessary objects to support DDL replication.
````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
-- Source ( Use source GG user as 'srcgguser')
[oracle@example01 ~]$ hostname -i
172.168.100.1
[oracle@example01 ~]$
cd /u01/goldengate/gg_home
[oracle@example01 gg_home]$ pwd
/u01/goldengate/gg_home
$ sqlplus / as sysdba
SQL>
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
@ddl_enable.sql
export OGG_HOME=/u01/goldengate/gg_home
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME
Start GGSCI and login into database using dblogin command.
dblogin userid <<gguser>>, password <<gguser>>
-- Target ( Use source GG user as 'tgtgguser')
[oracle@example02 ~]$ hostname -i
172.168.100.2
[oracle@example02 ~]$
cd /u01/goldengate/gg_home
[oracle@example01 gg_home]$ pwd
/u01/goldengate/gg_home
$ sqlplus / as sysdba
SQL>
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
@ddl_enable.sql
6) Configure Golden Gate to start GG processes
````````````````````````````````````````````````````````
Create below directories in both Source and Target
export OGG_HOME=/u01/goldengate/gg_home
cd $OGG_HOME
mkdir /u01/goldengate/gg_home/dirprm
mkdir /u01/goldengate/gg_home/dirdat
mkdir /u01/goldengate/gg_home/dirchk
mkdir /u01/goldengate/gg_home/BR
mkdir /u01/goldengate/gg_home/dirdmp
mkdir /u01/goldengate/gg_home/dirrpt
mkdir /u01/goldengate/gg_home/dirpcs
mkdir /u01/goldengate/gg_home/dirtmp
7) Create the Manager in Source database and start the Manager
`````````````````````````````````````````````````````````
-- source
cd $OGG_HOME/dirprm
vi mgr.prm
PORT 7801
AUTORESTART EXTRACT EXT*, RETRIES 5, WAITMINUTES 5, RESETMINUTES 60
PURGEOLDEXTRACTS dirdat/*, USECHECKPOINTS
-- start manager
[oracle@example01 gg_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (example01) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (example01) 2> start manager
Manager started.
GGSCI (example01) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (example01) 4>
8) Create the Manager in Target database and start the Manager
``````````````````````````````````````````````````````````````````````
Note: Grant dba role to target GG user ( tgtgguser)
SQL> grant dba to tgtgguser;
Grant succeeded.
SQL> @role_setup.sql
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
SQL> GRANT GGS_GGSUSER_ROLE TO tgtgguser;
SQL> EXECUTE ON dbms_logmnr_d package
-- create manager
cd $OGG_HOME/dirprm
vi mgr.prm
PORT 7801
AUTORESTART REPLICAT REP*, RETRIES 5, WAITMINUTES 5, RESETMINUTES 60
PURGEOLDEXTRACTS dirdat/*, USECHECKPOINTS
-- start manager
[oracle@example02 gg_home]$ ./ggsci
GGSCI (example02) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (example02) 2> start manager
Manager started.
GGSCI (example02) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (example02) 4>
============================================================================
Create Demo data in source database : SRCGGDB
cd $ORACLE_HOME
cd demo/schema/human_resources
ls
hr_analz.sql hr_code.sql hr_comnt.sql hr_cre.sql hr_drop_new.sql hr_drop.sql hr_idx.sql hr_main_new.sql hr_main.sql hr_popul.sql
$ sqlplus / as sysdba
SQL>@hr_main.sql
-- verify
$ sqlplus hr/oracle
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
7 rows selected.
SQL>
============================================================================
9) Create HR schema only in target database : TGTGGDB
```````````````````````````````````````````````````````````````
SQL> create tablespace HR datafile '/u01/oradata/TGTGGDB/hr01.dbf' size 1G autoextend on MAXSIZE UNLIMITED;
Tablespace created.
SQL> create user HR identified by tgtoracle default tablespace HR quota unlimited on HR;
User created.
SQL> grant create session,connect,resource,alter system to HR;
Grant succeeded.
--------------------- Add both databases entries in tnsnames.ora file ---------------------
SRCGGDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.100.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SRCGGDB)
)
)
----------------------------------------------------------------------------------------------
10) Create Sample Extract and Datapump in Source:
``````````````````````````````````````````````````````````````
-- source
$ cd $OGG_HOME
[oracle@example01 gg_home]$ ./ggsci
GGSCI (example01) 1> dblogin userid srcgguser,password srcgguser
Successfully logged into database.
GGSCI (example01 as srcgguser@SRCGGDB) 2>
add schematrandata HR
register extract ext1 database
add extract ext1, integrated tranlog, begin now
add exttrail /u01/goldengate/gg_home/dirdat/tr, extract ext1
edit params ext1
extract ext1
SETENV (ORACLE_HOME ="/u01/app/oracle/product/19.3.0.0.0/dbhome_1")
SETENV (ORACLE_SID ="SRCGGDB")
Userid srcgguser@SRCGGDB, PASSWORD srcgguser
EXTTRAIL /u01/goldengate/gg_home/dirdat/tr
DDL INCLUDE MAPPED
SEQUENCE HR.*;
TABLE HR.*;
:wq
-- to view
ggsci> view params ext1
-- Configure Pump process:
ggsci> add extract extpmp1, EXTTRAILSOURCE /u01/goldengate/gg_home/dirdat/tr
-- Below one will copy to remote/ target GG server for replication purpose
ggsci> add rmttrail /u01/goldengate/gg_home/dirdat/tr, extract extpmp1
ggsci> edit params extpmp1
extract extpmp1
PASSTHRU
RMTHOST 172.168.100.2, MGRPORT 7801
RMTTRAIL /u01/goldengate/gg_home/dirdat/tr
SEQUENCE HR.*;
TABLE HR.*;
:wq
-- start all the process
GGSCI (example01) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:28:18
EXTRACT STOPPED EXTPMP1 00:00:00 00:00:04
GGSCI (example01) 4> start *
Sending START request to MANAGER ...
EXTRACT EXT1 starting
Sending START request to MANAGER ...
EXTRACT EXTPMP1 starting
GGSCI (example01) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:28:28
EXTRACT RUNNING EXTPMP1 00:00:00 00:00:14
GGSCI (example01) 6>
11)Configure the Target Machine (Single Instance Database): TGTGGDB
`````````````````````````````````````````````````````````````````````````````
-- Target
ggsci> dblogin userid tgtgguser,password tgtgguser
ggsci> add CHECKPOINTTABLE tgtgguser.GGCHKPT
-- Register Replicats to database:
ggsci> register replicat REP1 database
-- Add Replicats:
ggsci> add replicat REP1, INTEGRATED, exttrail /u01/goldengate/gg_home/dirdat/tr CHECKPOINTTABLE tgtgguser.GGCHKPT
-- Parameter file for Replicat:
ggsci> edit params rep1
REPLICAT REP1
SETENV (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/dbhome_1")
SETENV (ORACLE_SID = "TGTGGDB")
USERID tgtgguser, PASSWORD tgtgguser
DISCARDFILE /u01/goldengate/gg_home/dirrpt/carerpt.dsc, PURGE
DISCARDROLLOVER ON SUNDAY
MAP HR.* TARGET HR.*;
:wq
-- start the replicat process
GGSCI (example02 as tgtgguser@TGTGGDB) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:01:34
GGSCI (example02 as tgtgguser@TGTGGDB) 5> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (example02 as tgtgguser@TGTGGDB) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (example02 as tgtgguser@TGTGGDB) 7>
12) Perform the Initial Load (using export Import):
`````````````````````````````````````````````````````````````````````
sqlplus hr/tgtoracle
connected.
Export the HR schema from source and import in Target database.
Then, do the transaction in the source and verify those in target.
Testing:
============
--- Source
sqlplus hr/oracle
connected.
SQL> insert into DEPARTMENTS values(999,'Goldengate',200,1700);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(1) from DEPARTMENTS;
COUNT(1)
----------
28
SQL> !hostname -i
172.168.100.1
SQL>
-- Target
sqlplus hr/tgtoracle
connected.
SQL> select * from select count(1) from DEPARTMENTS;^C
SQL> select count(1) from DEPARTMENTS;
COUNT(1)
----------
28
SQL> select * from DEPARTMENTS where DEPARTMENT_ID=999;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
999 Goldengate 200 1700
SQL> !hostname -i
172.168.100.2
SQL>
-- data maching. Replication working fine.
This comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
Delete