Dear Readers,
In this article we will see Oracle to Oracle GoldenGate Unidirectional Replication.
Prerequisites for Replication:
1.Create two Vm/s on your machine
2.Install Oracle Software and Create databases.
3.Make sure two Vm’s are pining each other
Node Details :
GG1 (SOURCE)
192.168.0.51 gg1.orcl.com gg1
Database Name :PRIMARY
Instance Name : primary
GG2(TARGET)
192.168.0.61 gg2.orcl.com gg2
Database Name :PRIMARY
Instance Name : primary
(I have taken same names on both machines)
Download Goldengate Binaries
Download GoldenGate binaries from Oracle.com using below link
https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
Make sure that you are using the correct OS version of binaries.
GG1 :
Setting bash_profile
Export the path in the bash profile
vi .bash_profile
1 2 3 4 5 6 |
export ORACLE_SID=primary export ORACLE_HOME=/u01/app/oracle/product/12.1.0 export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_NET=$ORACLE_HOME/network/admin export GGS_HOME=/oraeng/app/oracle/product/ogg_src export LD_LIBRARY_PATH=$ORACLE_HOME/lib |
Run the bash_profile file
1 |
[oracle@gg1 ~]$ . .bash_profile |
Check instance info :
1 2 3 4 5 |
SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PRIMARY - primary@gg1.orcl.com - primary - 12.1.0.2.0 - READ WRITE - 05-JUN-2019 18:00:05 - PRIMARY |
Create directory for gg home :
Create directory fo gghome
1 2 3 |
[root@gg1 ~]# mkdir -p /oraeng/app/oracle/product/ogg_src [root@gg1 ~]# chown -R oracle:oinstall /oraeng/app/oracle/product/ogg_src [root@gg1 ~]# chmod -R 755 /oraeng/app/oracle/product/ogg_src |
Copy the downloaded file to /opt location Using Winscp
Unzip GoldenGate Software :
Check file in /opt dir :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@gg1 opt]$ ls -ltr total 532148 drwxr-xr-x. 3 oracle dba 4096 May 9 21:30 fbo_ggs_Linux_x64_shiphome -rwxrwxrwx. 1 oracle dba 189027 May 11 02:25 OGG_WinUnix_Rel_Notes_19.1.0.0.0.pdf -rwxrwxrwx. 1 oracle dba 1412 May 11 03:19 OGG-19.1.0.0-README.txt -rw-r--r--. 1 root root 544713582 May 31 08:46 191000_fbo_ggs_Linux_x64_shiphome.zip GOTO gbo_ directory [oracle@gg1 opt]$ cd fbo_ggs_Linux_x64_shiphome/ [oracle@gg1 fbo_ggs_Linux_x64_shiphome]$ ls -ltr total 4 drwxr-xr-x. 5 oracle dba 4096 May 9 21:30 Disk1 GOTO Disk1 directory [oracle@gg1 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/ [oracle@gg1 Disk1]$ ls -ltr total 6 drwxr-xr-x.4 oracle dba 4096 May 9 21:29 install drwxr-xr-x.12 oracle dba 4096 May 9 21:30 stage -rwxr-xr-x.1 oracle dba 918 May 9 21:30 runInstaller drwxrwxr-x.2 oracle dba 4096 May 9 21:30 response |
Now do runinstaller as Oracle user :
1 2 3 4 5 6 7 8 |
[oracle@gg1 Disk1]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 3551 MB Passed Checking swap space: must be greater than 150 MB. Actual 7999 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-06-04_06-23-03PM. Please wait ... |
On step 1 choose version of your oracle software
Click on Next
Browse location for GG_HOME
Start Manager with any port number
Click on Next
Check summary
Click on Install
Installation will start
Finally GG will successfully installed on GG1
Click on Close
Goto GGS_HOME and connect to ./ggsci
Give info all to check manager process is running or not
Open New terminal for SQL prompt On GG1 :
Connect to database and create user and tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@gg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 4 18:58:11 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing option SYS>>grant dba to Ktexperts identified by ktexperts; Grant succeeded. SYS>>conn ktexperts/ktexperts Connected. KTEXPERTS>> KTEXPERTS>>create table dept (deptno number,dname varchar2(10),loc varchar2(15)); Table created. Elapsed: 00:00:00.20 KTEXPERTS>>alter table dept add primary key(deptno); Table altered. Elapsed: 00:00:00.56 KTEXPERTS>> KTEXPERTS>>SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME=' DEPT’; CONSTRAINT_NAME CONSTRAINT_TYPE --------------------------- ----------------------------- SYS_C005224 |
Enable the parameter for GoldenGate replication
1 2 |
SYS>>alter system set enable_goldengate_replication=true; System altered. |
Create admin for GoldeGate
1 2 3 4 5 6 |
SYS>>create user ggadmin identified by ggadmin; User created. Elapsed: 00:00:00.09 SYS>>grant dba to ggadmin; Grant succeeded. Elapsed: 00:00:00.01 |
Now execute below Package for additional privs
1 2 3 |
SYS>>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin',privilege_type=>'CAPTURE',grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. Elapsed: 00:00:06.34 |
Add supplemental logdata on columns
1 2 |
SYS>>alter database add supplemental log data(all) columns; Database altered. |
Check archive-log enabled or not
1 2 3 4 5 6 7 |
SYS>>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oradata/primary/arch Oldest online log sequence 1287 Next log sequence to archive 1288 Current log sequence 1288 |
On GGSCI terminal (GG1) :
Login with user credentials(ggadmin)
1 2 |
GGSCI (gg1.orcl.com) 2> dblogin userid ggadmin@primary,password ggadmin Successfully logged into database. |
Add Trandata on which table you want to perform replication
1 2 3 4 |
GGSCI (gg1.orcl.com as ggadmin@primary) 3> add trandata ktexperts.dept 2019-06-04 19:21:59 INFO OGG-15132 Logging of supplemental redo data enabled for table KTEXPERTS.DEPT. 2019-06-04 19:21:59 INFO OGG-15133 TRANDATA for scheduling columns has been added on table KTEXPERTS.DEPT. 2019-06-04 19:21:59 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table KTEXPERTS.DEPT. |
Add extract using below command
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 4> add extract extint,integrated tranlog,begin now EXTRACT (Integrated) added. |
Register extract process on database
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 5> register extract extint database 2019-06-04 19:25:05 INFO OGG-02003 Extract EXTINT successfully registered with database at SCN 2510571. |
Now add Exttrail file
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 6> ADD EXTTRAIL /oraeng/app/oracle/product/ogg_src/dirdat/lt, EXTRACT EXTINT EXTTRAIL added. |
Add datapump process
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 7> add extract dpint,exttrailsource /oraeng/app/oracle/product/ogg_src/dirdat/lt EXTRACT added. |
Add Remote trail file
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 8> add rmttrail /oraeng/app/oracle/product/ogg_trg/dirdat/rt, extract dpint RMTTRAIL added |
Check info all
Two more extract process will added
1 2 3 4 5 6 |
GGSCI (gg1.orcl.com as ggadmin@primary) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED DPINT 00:00:00 00:01:19 EXTRACT STOPPED EXTINT 00:00:00 00:09:05 |
Create wallet and add credentialstore to make aliasname for userlogin
1 2 3 4 5 6 7 |
GGSCI (gg1.orcl.com as ggadmin@primary) 10> create wallet Created wallet. Opened wallet. GGSCI (gg1.orcl.com as ggadmin@primary) 11> add credentialstore Credential store created. GGSCI (gg1.orcl.com as ggadmin@primary) 12> alter credentialstore add user ggadmin@primary,password ggadmin,alias ggadmin_src Credential store altered. |
From now user can able to login with alias-name called ggadmin_src
1 2 |
GGSCI (gg1.orcl.com as ggadmin@primary) 13> dblogin useridalias ggadmin_src Successfully logged into database. |
Edit the parameter for extint
1 2 3 4 5 6 7 8 |
GGSCI (gg1.orcl.com as ggadmin@primary) 14> edit param extint EXTRACT EXTINT SETENV(ORACLE_SID='primary') SETENV(ORACLE_HOME='/u01/app/oracle/product/12.1.0') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100) EXTTRAIL /oraeng/app/oracle/product/ogg_src/dirdat/lt TABLE KTEXPERTS.DEPT; |
Edit the parameter for dpint
1 2 3 4 5 6 |
GGSCI (gg1.orcl.com as ggadmin@primary) 15> edit param dpint EXTRACT DPINT RMTHOST 192.168.0.61,MGRPORT 7809 RMTTRAIL /oraeng/app/oracle/product/ogg_trg/dirdat/rt PASSTHRU TABLE KTEXPERTS.DEPT; |
Check info all
1 2 3 4 5 6 7 |
GGSCI (gg1.orcl.com as ggadmin@primary) 16> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED DPINT 00:00:00 00:25:29 EXTRACT STOPPED EXTINT 00:00:00 00:33:15 |
Start both params extint and dpint
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
GGSCI (gg1.orcl.com as ggadmin@primary) 17> start extint Sending START request to MANAGER ... EXTRACT EXTINT starting GGSCI (gg1.orcl.com as ggadmin@primary) 18> ! start extint Sending START request to MANAGER ... EXTRACT EXTINT starting GGSCI (gg1.orcl.com as ggadmin@primary) 19> ! start extint EXTRACT EXTINT is already running. GGSCI (gg1.orcl.com as ggadmin@primary) 20> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED DPINT 00:00:00 00:25:43 EXTRACT RUNNING EXTINT 00:00:00 00:33:28 ######EXTINT RUNNING ############################## ####################START DPINT ############################ GGSCI (gg1.orcl.com as ggadmin@primary) 21> start dpint Sending START request to MANAGER ... EXTRACT DPINT starting GGSCI (gg1.orcl.com as ggadmin@primary) 22> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:01 EXTRACT RUNNING EXTINT 00:00:03 00:00:06 ###############BOTH ARE RUNNING fine ####################### ##If not running then use <span style="color: #000000;" data-mce-style="color: #000000;">view report dpint/extint </span>####################### |
On GG2 Machine :
Export the path in the bash profile
vi .bash_profile
1 2 3 4 5 6 |
export ORACLE_SID=primary export ORACLE_HOME=/u01/app/oracle/product/12.1.0 export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_NET=$ORACLE_HOME/network/admin export GGS_HOME=/oraeng/app/oracle/product/ogg_trg export LD_LIBRARY_PATH=$ORACLE_HOME/lib |
Run the .bash_profile
1 |
[oracle@gg2~]$ . .bash_profile |
Check the instance info
1 2 3 4 5 |
SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PRIMARY - primary@gg2.orcl.com - primary - 12.1.0.2.0 - READ WRITE - 05-JUN-2019 18:00:05 - PRIMARY |
Create directory for gg home :
1 2 3 |
[root@gg2 ~]# mkdir -p /oraeng/app/oracle/product/ogg_trg [root@gg2 ~]# chown -R oracle:oinstall /oraeng/app/oracle/product/ogg_trg [root@gg2 ~]# chmod -R 755 /oraeng/app/oracle/product/ogg_trg |
Copy the downloaded file to /opt location Using WinScp
Goto /opt dire and check for files
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@gg2 opt]$ ls -ltr total 532148 drwxr-xr-x. 3 oracle dba 4096 May 9 21:30 fbo_ggs_Linux_x64_shiphome -rwrwxrwx. 1 oracle dba 189027 May 11 02:25 OGG_WinUnix_Rel_Notes_19.1.0.0.0.pdf -rwxrwxrwx. 1 oracle dba 1412 May 11 03:19 OGG-19.1.0.0-README.txt -rw-r--r--. 1 root root 544713582 May 31 08:46 191000_fbo_ggs_Linux_x64_shiphome.zip GOTO fbo_ directory [oracle@gg2 opt]$ cd fbo_ggs_Linux_x64_shiphome/ [oracle@gg2 fbo_ggs_Linux_x64_shiphome]$ ls -ltr total 4 drwxr-xr-x. 5 oracle dba 4096 May 9 21:30 Disk1 GOTO Disk1 directory [oracle@gg1 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/ [oracle@gg2 Disk1]$ ls -ltr total 16 drwxr-xr-x 4 oracle dba 4096 May 9 21:29 install drwxr-xr-x. 12 oracle dba 4096 May 9 21:30 stage -rwxr-xr-x. 1 oracle dba 918 May 9 21:30 runInstaller drwxrwxr-x. 2 oracle dba 4096 May 9 21:30 response |
Now run ./runInstaller
1 2 3 4 5 6 |
[oracle@gg2 Disk1]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 3551 MB Passed Checking swap space: must be greater than 150 MB. Actual 7999 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-06-04_06-23-03PM. Please wait ... |
On step 1 choose version of your oracle software
Click on Next
Browse location for GG_HOME
Start Manager with any port number
Click on Next
Check summary
Click on Install
Installation will start
Finally GG will successfully installed on GG2
Click on Close
Goto GGS_HOME and run ./ggsci
Check Manager is running or not
Open New terminal for SQL prompt
Connect to database and create user and tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SYS>>grant dba to Ktexperts identified by ktexperts; Grant succeeded. SYS>>conn ktexperts/ktexperts Connected. KTEXPERTS>> KTEXPERTS>>create table dept (deptno number,dname varchar2(10),loc varchar2(15)); Table created. Elapsed: 00:00:00.20 KTEXPERTS>>alter table dept add primary key(deptno); Table altered. Elapsed: 00:00:00.56 KTEXPERTS>> KTEXPERTS>>SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME=' DEPT’; CONSTRAINT_NAME CONSTRAINT_TYPE --------------------------- ----------------------------- SYS_C005224 |
Enable the parameter for GoldenGate replication
1 2 3 |
SYS>>alter system set enable_goldengate_replication=true; System altered. |
Create admin for GoldeGate
1 2 3 4 |
SYS>>create user ggadmin identified by ggadmin; User created SYS>>grant dba to ggadmin; Grant succeeded. |
Now execute below Package for additional privs
1 2 |
SYS>>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin',privilege_type=>'CAPTURE',grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. |
Add supplemental logdata on columns
1 2 |
SYS>>alter database add supplemental log data(all) columns; Database altered. |
Check archive-log enabled or not
1 2 3 4 5 6 7 |
SYS>>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 10 Next log sequence to archive 12 Current log sequence 12 |
Open GGSCI GG2 terminal
Add REPLICAT process
1 2 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 5> add replicat repint,integrated exttrail /oraeng/app/oracle/product/gg_trg/dirdat/rt REPLICAT (Integrated) added |
Check info all
1 2 3 4 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPINT 00:00:00 00:00:20 |
Edit the param RPINT
1 2 3 4 5 6 7 8 9 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 7> edit param repint REPLICAT REPINT SETENV(ORACLE_SID='primary') SETENV(ORACLE_HOME='/u01/app/oracle/product/12.1.0') useridalias ggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP KTEXPERTS.DEPT,target KTEXPERTS.DEPT; |
START RPINT
1 2 3 4 5 6 7 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 18> start repint Sending START request to MANAGER ... REPLICAT REPINT starting GGSCI (gg2.orcl.com as ggadmin@orcl) 9> ! start repint REPLICAT REPINT is already running. |
1 2 3 4 5 6 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 19> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPINT 00:00:00 00:03:40 |
Now check the information of each process
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
################ON GG##################### GGSCI (gg1.orcl.com as ggadmin@primary) 20> info dpint EXTRACT DPINT Last Started 2019-06-04 20:10 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Process ID 10717 Log Read Checkpoint File /oraeng/app/oracle/product/gg_src/lt000000000 First Record RBA 0 ##################ON GG2################################### GGSCI (gg2.orcl.com as ggadmin@orcl) 21> info repint REPLICAT REPINT Last Started 2019-06-04 20:41 Status RUNNING INTEGRATED Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Process ID 14906 Log Read Checkpoint File /oraeng/app/oracle/product/gg_trg/dirdat/rt000000000 First Record RBA 0 |
Now do insert operation on GG1 SQL terminal
1 2 3 4 5 6 7 8 9 |
KTEXPERTS>>insert into dept values(10,'SALES','IND'); 1 row created. Elapsed: 00:00:00.21 KTEXPERTS>>insert into dept values(20,'MARK','SA'); 1 row created. KTEXPERTS>>insert into dept values(30,'IT','CAN'); 1 row created. KTEXPERTS>> commit; commit complete. |
On GG1 GGSCI terminal
check the stats of DPINT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
GGSCI (gg1.orcl.com as ggadmin@primary) 22> stats dpint Sending STATS request to EXTRACT DPINT ... Start of Statistics at 2019-06-04 21:10:32. Output to /oraeng/app/oracle/product/gg_trg/dirdat/rt: Extracting from KTEXPERTS.DEPT to KTEXPERTS.DEPT: *** Total statistics since 2019-06-04 21:05:14 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Daily statistics since 2019-06-04 21:05:14 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Hourly statistics since 2019-06-04 21:05:14 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Latest statistics since 2019-06-04 21:05:14 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 End of Statistics. |
Now check On GG2 terminal records are replicated are not
1 2 3 4 5 6 7 |
KTEXPERTS>>select * from dept; DEPTNO DNAME LOC -------- --------- ---------- 10 SALES IND 20 MARK SA 30 IT CAN |
Please follow above steps for unidirectional GoldenGate replication for Oracle to Oracle.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Ajay Kumar
Hi All
If anyone needs GG Training and also needs to configure GG for heterogeneous Databases then Please contact Ashish..
Deails :Ashish Agarwal
Email id ashish agarwalag@gmail.com
YouTube Channel:
https://www.youtube.com/c/ashishagarwal_gg
Linkedin:
https://www.linkedin.com/in/ashish-agarwal-a1399663/