In this activity we are going to assume we have GoldenGate software already installed, if not, you can follow these steps here:
We are are going to replicate schema HR from/to these server:
| SOURCE (PDB): | TARGET (NON CDB): |
| hostname: racnode1 | hostname: standbyracnode |
| IP: 192.168.24.1 | IP: 192.168.24.3 |
| RDBMS: 19c | RDBMS: 19c |
| SID: cdb19c1 | SID: devdbnoncdb |
| PDB: CDB19C_PDB | PDB: N/A |
| ORACLE_HOME: /u01/app/oracle/product/19c/dbhome_1 | ORACLE_HOME: /u01/app/oracle/product/19c/db_1 |
| GoldenGate HOME: /u01/app/goldengate/19.1.0.0/ | GoldenGate HOME: /u01/app/goldengate/19.1.0.0/ |
Please be aware that since we are working with PDB’s, we need to use an INTEGRATED extrat.
Here the steps to implement this activity:
1:- Initial load / Import data to target DB:
EXPORT:
[oracle@RACnode1 ~]$ mkdir /tmp/EXPORT
[oracle@RACnode1 ~]$ chown oracle:oinstall /tmp/EXPORT
[oracle@RACnode1 ~]$
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> create or replace directory HR_SYNC as '/tmp/EXPORT';
Directory created.
[oracle@RACnode1 ~]$ cd /tmp/EXPORT
[oracle@RACnode1 EXPORT]$ vi expdp_script.sh
[oracle@RACnode1 EXPORT]$ ls -tlr
total 4
-rw-r--r--. 1 oracle oinstall 236 Oct 1 10:11 expdp_script.sh
[oracle@RACnode1 EXPORT]$ chmod 744 expdp_script.sh
[oracle@RACnode1 EXPORT]$ cat expdp_script.sh
#!/bin/bash
SYS_PWD=Pas5w0rd
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
nohup $ORACLE_HOME/bin/expdp \"sys/$SYS_PWD@CDB19C_PDB as sysdba\" directory=HR_SYNC dumpfile=HR_%U.dmp logfile=HR_EXPORT.log cluster=NO schemas=HR &
[oracle@RACnode1 EXPORT]$
Make sure pdb is pingable and set in tnsnames.ora:
[or acle@RACnode1 EXPORT]$ tnsping cdb19c_pdb
...
Use d TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDR ESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB19C_PDB)))
OK (10 msec)
[oracle@RACnode1 EXPORT]$
[oracle@RACnode1 EXPORT]$ ./expdp_script.sh
[oracle@RACnode1 EXPORT]$ tail HR_EXPORT.log
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/tmp/EXPORT/HR_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 1 10:39:48 2021 elapsed 0 00:04:12
[oracle@RACnode1 EXPORT]$
IMPORT:
[oracle@standbyracnode ~]$ mkdir /tmp/IMPORT
[oracle@standbyracnode ~]$
[oracle@RACnode1 EXPORT]$ scp -p HR_01.dmp oracle@standbyracnode:/tmp/IMPORT
oracle@standbyracnode's password:
HR_01.dmp 100% 716KB 32.3MB/s 00:00
[oracle@RACnode1 EXPORT]$
SQL> create or replace directory HR_SYNC as '/tmp/IMPORT';
Directory created.
SQL>
[oracle@standbyracnode IMPORT]$ cat import_script.sh
#!/bin/bash
SYS_PWD=Pas5w0rd
export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
nohup $ORACLE_HOME/bin/impdp \"sys/$SYS_PWD@DEVDB_NONCDB as sysdba\" directory=HR_SYNC dumpfile=HR_%U.dmp logfile=HR_IMPORt.log cluster=NO schemas=HR &
[oracle@standbyracnode IMPORT]$
[oracle@standbyracnode IMPORT]$ ./import_script.sh
[oracle@standbyracnode IMPORT]$ tail HR_IMPORt.log
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Oct 1 13:51:55 2021 elapsed 0 00:02:03
[oracle@standbyracnode IMPORT]$
**ERROR for HR schema already exist, can be ignored
2:- Prepare SOURCE DB:
Enable minimal supplemental logging:
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
System altered.
SQL>
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL>
Create common user:
SQL> CREATE USER c##ggadmin IDENTIFIED BY ggadmin
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
User created.
SQL> GRANT DBA to c##ggadmin CONTAINER=ALL;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege( grantee => 'c##ggadmin',container => 'ALL');
PL/SQL procedure successfully completed.
SQL> 3:- Prepare TARGET DB:
Enable RDBMS to be used by GoldenGate:
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
System altered.
Create common user:
SQL> CREATE USER ggadmin IDENTIFIED BY ggadmin
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
User created.
SQL> GRANT DBA TO ggadmin;
Grant succeeded.
SQL> EXEC dbms_goldengate_auth.grant_admin_privilege('ggadmin');
PL/SQL procedure successfully completed.
SQL> 4:- Prepare GoldenGate in SOURCE:
Configure Manager parameter:
GGSCI (RACnode1.localdomain) 1> EDIT PARAM mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/et* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR 192.168.24.3, ALLOW
Create credentialstore:
GGSCI (RACnode1.localdomain) 5> add credentialstore
Credential store created.
GGSCI (RACnode1.localdomain) 6> alter credentialstore add user c##ggadmin@192.168.24.1:1521/cdb19c alias cdb19c
Password:
Credential store altered.
GGSCI (RACnode1.localdomain) 7> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: cdb19c
Userid: c##ggadmin@192.168.24.1:1521/cdb19c
GGSCI (RACnode1.localdomain) 8>
GGSCI (RACnode1.localdomain) 8> dblogin USERIDALIAS cdb19c
Successfully logged into database CDB$ROOT.
Configure EXTRACT parameter file:
GGSCI (RACnode1.localdomain) 2> EDIT PARAM exthr
EXTRACT exthr
SETENV (ORACLE_SID='CDB19C_PDB')
SETENV (ORACLE_HOME = '/u01/app/oracle/product/19c/dbhome_1')
DISCARDFILE ./dirrpt/exthr.dsc, APPEND
EXTTRAIL ./dirdat/et
--- User login
USERIDALIAS cdb19c
--- DDL Parameters
LOGALLSUPCOLS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
SOURCECATALOG CDB19C_PDB
TABLE HR.* ;
Configure EXTRACT PUMP parameter file:
GSCI (xtivia12) 3> EDIT PARAM ephr
EXTRACT ephr
RMTHOST 192.168.24.3, MGRPORT 7809
PASSTHRU
RMTTRAIL ./dirdat/rt
SOURCECATALOG CDB19C_PDB
TABLE HR.* ;
Create EXTRACT group, EXTRAIL, PUMP AND RMTTRAIL:
GGSCI (RACnode1.localdomain) 17> ADD EXTRACT exthr, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
GGSCI (RACnode1.localdomain) 18> ADD EXTTRAIL ./dirdat/et, EXTRACT exthr, MEGABYTES 5
EXTTRAIL added.
GGSCI (RACnode1.localdomain) 19> ADD RMTTRAIL ./dirdat/rt, EXTRACT exthr, MEGABYTES 5
RMTTRAIL added.
GGSCI (RACnode1.localdomain) 20> ADD EXTRACT ephr, EXTTRAILSOURCE ./dirdat/et
EXTRACT added.
GGSCI (RACnode1.localdomain) 21> ADD RMTTRAIL ./dirdat/rt, EXTRACT ephr, MEGABYTES 5
RMTTRAIL added.
GGSCI (RACnode1.localdomain) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EPHR 00:00:00 00:00:07
EXTRACT STOPPED EXTHR 00:00:00 00:00:15
GGSCI (RACnode1.localdomain) 22>
Register EXTRACT in DB:
GGSCI (RACnode1.localdomain) 10> dblogin userid c##ggadmin, password ggadmin
Successfully logged into database CDB$ROOT.
GGSCI (RACnode1.localdomain as c##ggadmin@cdb19c1/CDB$ROOT) 11> register extract exthr database container (CDB19C_PDB)
2021-10-01 16:20:35 INFO OGG-02003 Extract EXTHR successfully registered with database at SCN 2985612.
GGSCI (RACnode1.localdomain as c##ggadmin@cdb19c1/CDB$ROOT) 12>
5:- Prepare GoldenGate in TARGET:
Configure Manager parameter:
GGSCI (standbyracnode.localdomain) 2> EDIT PARAM mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR 192.168.24.3, ALLOW
ACCESSRULE, PROG *, IPADDR 192.168.24.1, ALLOW
Create credentialstore:
GGSCI (standbyracnode.localdomain) 3> add credentialstore
Credential store created.
GGSCI (standbyracnode.localdomain) 5> alter credentialstore add user ggadmin@DEVDBNONCDB alias DEVDBNONCDB
Password:
Credential store altered.
GGSCI (standbyracnode.localdomain) 5> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: DEVDBNONCDB
Userid: ggadmin@DEVDBNONCDB
GGSCI (standbyracnode.localdomain) 6>
GGSCI (standbyracnode.localdomain) 7> dblogin USERIDALIAS DEVDBNONCDB
Successfully logged into database.
Configure REPLICAT parameter file:
GGSCI (standbyracnode.localdomain) 2> EDIT PARAM rephr
REPLICAT rephr
DISCARDFILE ./dirrpt/rephr.dsc, APPEND
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
ASSUMETARGETDEFS
--- User login
USERIDALIAS DEVDBNONCDB
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP CDB19C_PDB.HR.*, TARGET HR.*;
Create REPLICAT group and Trail Files:
GGSCI (standbyracnode.localdomain) 3> dblogin USERIDALIAS DEVDBNONCDB
Successfully logged into database.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 4> ADD CHECKPOINTTABLE ggadmin.chktbl
Successfully created checkpoint table ggadmin.chktbl.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 5> ADD REPLICAT rephr, INTEGRATED, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE ggadmin.chktbl
REPLICAT (Integrated) added.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPHR 00:00:00 00:00:03
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 10> 6:- START EXTRACT:
Restart Manager (since we edited parameter)
GGSCI (RACnode1.localdomain) 1> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (RACnode1.localdomain) 2> start mgr
Manager started.
Start EXTRACT and PUMP:
GGSCI (RACnode1.localdomain) 3> start extract exthr
Sending START request to MANAGER ...
EXTRACT EXTHR starting
GGSCI (RACnode1.localdomain) 4> start extract ephr
Sending START request to MANAGER ...
EXTRACT EPHR starting
GGSCI (RACnode1.localdomain) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPHR 00:00:00 00:00:34
EXTRACT RUNNING EXTHR 00:00:00 00:00:27
GGSCI (RACnode1.localdomain) 6> 7:- Start REPLICAT:
Restart Manager (since we edited parameter):
GGSCI (standbyracnode.localdomain) 1> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (standbyracnode.localdomain) 2> start mgr
Manager started.
Start REPLICAT:
GGSCI (standbyracnode.localdomain) 3> start replicat REPHR
Sending START request to MANAGER ...
REPLICAT REPHR starting
GGSCI (standbyracnode.localdomain) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPHR 00:00:00 00:00:10
GGSCI (standbyracnode.localdomain) 5> TESTS:
1:- INSERT:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
19
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
19
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
Insert on SOURCE DB:
SQL> show con_name
CON_NAME
------------------------------
CDB19C_PDB
SQL> INSERT INTO HR.jobs
(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY )
VALUES
('DB_MAN', 'DBA Administrator',40000,70000);
1 row created.
SQL> commit;
Commit complete.
REPLICATED in target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
202:- DELETE:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
20
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
20
Delete on Source DB:
SQL> delete from HR.JOBS where JOB_TITLE like '%DBA%';
1 row deleted.
SQL> commit;
Commit complete.
SQL>
REPLICATED on target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
193:- DDL:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> desc HR.TEST
ERROR:
ORA-04043: object HR.TEST does not exist
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> desc HR.TEST
ERROR:
ORA-04043: object HR.TEST does not exist
Create table in Source:
SQL> CREATE TABLE HR.TEST
( test_id NUMBER PRIMARY KEY,
test__name VARCHAR2(30) ); 2 3
Table created.
SQL> desc HR.TEST
Name Null? Type
------------------------- -------- ---------------------
TEST_ID NOT NULL NUMBER
TEST__NAME VARCHAR2(30)
REPLICATED on target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> desc HR.TEST
Name Null? Type
------------------------- -------- ---------------------
TEST_ID NOT NULL NUMBER
TEST__NAME VARCHAR2(30)
and voila! We have GoldenGate up and running now!
Comments
Hi Alex, In RAC there will be more than 1 node, what about extrial files where it will store..?
Thanks for checking this page! On a RAC enviorment,you will need to have a cluster resource where GG resources move from one node to another, you might want to include FS too (via DBFS if you want). but yes, FS needs to be shared between nodes if the plan is to have GG running on either of the nodes
Hi Alex, Thanks for the article, it is brilliant. I just got 1 question. Should the extract be started first before the initial load (export/import)? If the source DB is active, the SCN will be moving, so there will be gap if the initial load is done before the extract. (In your case, maybe the DB is quiet so no gap I guess). Thanks, Ho
Thanks a lot for using this blog, glad it helps someone! That is correct, we are assuming here between the initial load and creating/registering the extract. Ideally you don’t want any transactions on the table, but if there are any, you will need to export the table using the flashback_scn= from the time you added extract/pump
Thanks, this article helped me a lot friend