Oracle GoldenGate - Basic Configuration
The following procedure describes a Bi-Directional Oracle Golden Gate configuration. It is intended for research and/or training purposes and is not intended to be a production configuration.
For a Bi-Directional configuration I used two Linux VMs (RHEL 7.5) running single instance Oracle 19.3.0.0.0 databases. I created both databases using DBCA.
In the following configuration I have used the following hosts and databases:
Prepare Databases
On source(GGSOURCE) and target(GGTARGET) verify that LOG_MODE is set to ARCHIVELOG.
SOURCE
[code]
[dev:oracle:ggsource}$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 19:26:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select LOG_MODE from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 721420288 bytes
Database Buffers 469762048 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select LOG_MODE from v$database;
LOG_MODE
------------
ARCHIVELOG
[/code]
TARGET
[code]
[dev1:oracle:ggtarget]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 19:26:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select LOG_MODE from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 721420288 bytes
Database Buffers 469762048 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select LOG_MODE from v$database;
LOG_MODE
------------
ARCHIVELOG
[/code]
Enable Forced Logging
Forced logging should be enabled on the both source and target database to ensure that all changes are written to the redo logs. DBCA does not enable force logging during database creation.
To check if FORCE_LOGGING is enabled in the source and target database use:
Both SOURCE & TARGET
[code] SQL> select LOG_MODE from v$database; LOG_MODE ------------ ARCHIVELOG SQL> SELECT force_logging FROM v$database; FORCE_LOGGING --------------------------------------- NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> SELECT force_logging FROM v$database; FORCE_LOGGING --------------------------------------- YES [/code]
The documentation recommends performing a redo log switch to ensure that the change is applied in all subsequent redo. Do it on both SOURCE and TARGET.
[code] SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. [/code]
Enable Minimal Supplemental Logging - Both Source and Target
If minimal supplemental logging is not enabled, the extract process fails to start. DBCA does not enable minimal supplemental during database creation.
Minimal supplemental logging only needs to be configured on the source and target databases.
Check if minimal supplemental logging is currently enabled:
[code] SQL> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- YES [/code]
Switch the log file again to ensure that all subsequent redo contains minimal supplemental logging: Both source and target
Enable ENABLE_GOLDENGATE_REPLICATION to TRUE in both source and target
[code] SQL> show parameter ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean FALSE SQL> SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH; System altered. SQL> show parameter ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean TRUE [/code]
Prepare Test Environment
In both databases grant the following permissions to the new user. For example:
[code] SQL> GRANT CONNECT,RESOURCE,DBA TO us01; Grant succeeded. [/code]
Create Test Table
In the source database (GGSOURCE) create the T1 table using a subset of rows from DBA_OBJECTS
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> CREATE TABLE t1 AS SELECT object_id,owner,object_name,object_type FROM dba_objects WHERE object_id <= 10000; Table created. [/code]
[code] SQL> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- YES [/code]
Switch the log file again to ensure that all subsequent redo contains minimal supplemental logging: Both source and target
Enable ENABLE_GOLDENGATE_REPLICATION to TRUE in both source and target
[code] SQL> show parameter ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean FALSE SQL> SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH; System altered. SQL> show parameter ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean TRUE [/code]
Prepare Test Environment
Create Test User - In both source and target
In order to test the GoldenGate configuration I created a new schema (US01) containing a new table (T1) as follows. The table is derived from DBA_OBJECTS.
In both databases create a user (schema) called US01. For example:
[code]
SQL> CREATE USER us01 IDENTIFIED BY us01;
User created.
[/code]
In both databases grant the following permissions to the new user. For example:
[code] SQL> GRANT CONNECT,RESOURCE,DBA TO us01; Grant succeeded. [/code]
Create Test Table
In the source database (GGSOURCE) create the T1 table using a subset of rows from DBA_OBJECTS
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> CREATE TABLE t1 AS SELECT object_id,owner,object_name,object_type FROM dba_objects WHERE object_id <= 10000; Table created. [/code]
In the source database (GGSOURCE), add a primary key constraint and index to table T1:
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id); Table altered. [/code]
Start Listener Processes
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id); Table altered. [/code]
Start Listener Processes
Before attempting to configure the network, ensure that the listener processes are running on both servers.
SOURCE
[code] [dev:oracle:ggsource}$ lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-MAY-2020 20:16:25 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev.oracledbashop.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-MAY-2020 20:16:04 Uptime 0 days 0 hr. 0 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dev/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev.oracledbashop.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ggsource.oracledbashop.com" has 1 instance(s). Instance "ggsource", status READY, has 1 handler(s) for this service... Service "ggsourceXDB.oracledbashop.com" has 1 instance(s). Instance "ggsource", status READY, has 1 handler(s) for this service... The command completed successfully [/code]
TARGET
[code] [dev1:oracle:ggtarget]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-MAY-2020 20:18:38 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1.oracledbashop.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-MAY-2020 20:18:25 Uptime 0 days 0 hr. 0 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dev1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev1.oracledbashop.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ggtarget.oracledbashop.com" has 1 instance(s). Instance "ggtarget", status READY, has 1 handler(s) for this service... Service "ggtargetXDB.oracledbashop.com" has 1 instance(s). Instance "ggtarget", status READY, has 1 handler(s) for this service... The command completed successfully [/code]
Configure Network
SOURCE
[code] [dev:oracle:ggsource}$ lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-MAY-2020 20:16:25 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev.oracledbashop.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-MAY-2020 20:16:04 Uptime 0 days 0 hr. 0 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dev/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev.oracledbashop.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ggsource.oracledbashop.com" has 1 instance(s). Instance "ggsource", status READY, has 1 handler(s) for this service... Service "ggsourceXDB.oracledbashop.com" has 1 instance(s). Instance "ggsource", status READY, has 1 handler(s) for this service... The command completed successfully [/code]
TARGET
[code] [dev1:oracle:ggtarget]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-MAY-2020 20:18:38 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dev1.oracledbashop.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-MAY-2020 20:18:25 Uptime 0 days 0 hr. 0 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dev1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev1.oracledbashop.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ggtarget.oracledbashop.com" has 1 instance(s). Instance "ggtarget", status READY, has 1 handler(s) for this service... Service "ggtargetXDB.oracledbashop.com" has 1 instance(s). Instance "ggtarget", status READY, has 1 handler(s) for this service... The command completed successfully [/code]
Configure Network
SOURCE
For this bi-directional configuration, an entry is required in the TNSNAMES.ORA file on each server describing the database on the other server.
On the source database (GGSOURCE) I added the following entry for the GGTARGET database to $ORACLE_HOME/network/admin/tnsnames.ora
[code] GGTARGET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.111)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ggtarget.oracledbashop.com) ) ) [/code]
Verify the connection using SQL*Plus. For example:
[code] [dev:oracle:ggsource}$ sqlplus us01/us01@GGTARGET SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:23:52 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 18:35:17 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name from v$database; NAME --------- GGTARGET [/code]
[code] GGTARGET = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.111)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ggtarget.oracledbashop.com) ) ) [/code]
Verify the connection using SQL*Plus. For example:
[code] [dev:oracle:ggsource}$ sqlplus us01/us01@GGTARGET SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:23:52 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 18:35:17 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name from v$database; NAME --------- GGTARGET [/code]
TARGET
On the target database (GGTARGET), I added the following entry for the GGSOURCE database to $ORACLE_HOME/network/admin/tnsnames.ora
[code] GGSOURCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ggsource.oracledbashop.com) ) ) [/code]
Verify the connection using SQL*Plus. For example:
[code] [dev1:oracle:ggtarget]$ sqlplus us01/us01@GGSOURCE SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:28:10 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 18:39:07 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name from v$database; NAME --------- GGSOURCE [/code]
Create Database Links
SOURCE
Configure GoldenGate
Create the GoldenGate Schema Owner - Both source and target
On the target database (GGTARGET), I added the following entry for the GGSOURCE database to $ORACLE_HOME/network/admin/tnsnames.ora
[code] GGSOURCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ggsource.oracledbashop.com) ) ) [/code]
Verify the connection using SQL*Plus. For example:
[code] [dev1:oracle:ggtarget]$ sqlplus us01/us01@GGSOURCE SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:28:10 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 18:39:07 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name from v$database; NAME --------- GGSOURCE [/code]
Create Database Links
SOURCE
On each server create a database link to the other database. This is only required to set up the test data. It is not required for GoldenGate.
On the source server (GGSOURCE) as the US01 user, create the following database link:
[code] SQL> create database link ggtarget connect to us01 IDENTIFIED BY "us01" USING 'GGTARGET'; Database link created. [/code]
[code] SQL> create database link ggtarget connect to us01 IDENTIFIED BY "us01" USING 'GGTARGET'; Database link created. [/code]
TARGET
On the target server (GGTARGET) as the US01 user, create the following database link:
[code] SQL> create database link ggsource connect to us01 IDENTIFIED BY "us01" USING 'GGSOURCE'; Database link created. [/code]
Copy Test Data to Target Database
On the target server (GGTARGET) as the US01 user, create the following database link:
[code] SQL> create database link ggsource connect to us01 IDENTIFIED BY "us01" USING 'GGSOURCE'; Database link created. [/code]
Copy Test Data to Target Database
Create an initial copy of the test data on the target database
In the target database (GGTARGET) create a copy of the T1 table using the SQL*Plus command:
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> CREATE TABLE t1 AS SELECT * FROM t1@ggsource; Table created. [/code]
In the target database (GGTARGET), add a primary key constraint and index to table T1:
[code] SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id); Table altered. [/code]
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL> CREATE TABLE t1 AS SELECT * FROM t1@ggsource; Table created. [/code]
In the target database (GGTARGET), add a primary key constraint and index to table T1:
[code] SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id); Table altered. [/code]
In the source database (GGSOURCE), verify the number of rows in the original table:
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:55:00 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 20:28:10 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 9980 [/code]
In the target database, verify that there is the same number of rows in the new table:
[code] [dev1:oracle:ggtarget]$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:57:56 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 20:23:52 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 9980 [/code]
Note that the actual number of rows in table T1 should be identical for databases GGSOURCE and GGTARGET. [code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:55:00 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 20:28:10 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 9980 [/code]
In the target database, verify that there is the same number of rows in the new table:
[code] [dev1:oracle:ggtarget]$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 20:57:56 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 20:23:52 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 9980 [/code]
Configure GoldenGate
Create the GoldenGate Schema Owner - Both source and target
A new user should be created to own the GoldenGate database objects.
On each server create the GoldenGate schema owner. For example:
[code] SQL> CREATE USER gg01 IDENTIFIED BY gg01; User created. SQL> GRANT CONNECT, RESOURCE, DBA TO gg01; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gg01', privilege_type=>'CAPTURE', grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. [/code]
Create GoldenGate Tablespace - Both source and target
[code] SQL> CREATE USER gg01 IDENTIFIED BY gg01; User created. SQL> GRANT CONNECT, RESOURCE, DBA TO gg01; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gg01', privilege_type=>'CAPTURE', grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. [/code]
Create GoldenGate Tablespace - Both source and target
On the source server create a new tablespace for the GoldenGate objects. Ensure that AUTOEXTEND is enabled.
[code] SQL> CREATE TABLESPACE goldengate DATAFILE '/u01/app/oracle/oradata/GGSOURCE/goldengate01.dbf' SIZE 100M AUTOEXTEND ON; 2 3 4 Tablespace created. [/code]
Set the new tablespace as the default for the GoldenGate user:
[code] SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate; User altered. [/code]
On the target server create a new tablespace for the GoldenGate objects. Again ensure that AUTOEXTEND is enabled.
[code] SQL> CREATE TABLESPACE goldengate DATAFILE '/u01/app/oracle/oradata/GGTARGET/goldengate01.dbf' SIZE 100M AUTOEXTEND ON; 2 3 4 Tablespace created. [/code]
[code] SQL> CREATE TABLESPACE goldengate DATAFILE '/u01/app/oracle/oradata/GGSOURCE/goldengate01.dbf' SIZE 100M AUTOEXTEND ON; 2 3 4 Tablespace created. [/code]
Set the new tablespace as the default for the GoldenGate user:
[code] SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate; User altered. [/code]
On the target server create a new tablespace for the GoldenGate objects. Again ensure that AUTOEXTEND is enabled.
[code] SQL> CREATE TABLESPACE goldengate DATAFILE '/u01/app/oracle/oradata/GGTARGET/goldengate01.dbf' SIZE 100M AUTOEXTEND ON; 2 3 4 Tablespace created. [/code]
Set the new tablespace as the default for the GoldenGate user:
[code] SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate; User altered. [/code]
[code] SQL> ALTER USER gg01 DEFAULT TABLESPACE goldengate; User altered. [/code]
Both source and target side go to Golden Gate Installed location (in our scenario /u01/app/oracle/product/gghome) and then run the following Golden Gate inbuild scripts for creating all necessary objects to support DDL replication.
- marker_setup.sql
- ddl_setup.sql
- role_setup.sql
- GRANT GGS_GGSUSER_ROLE TO <Loggeduser>; In my case gg01
- ddl_enable.sql
SOURCE (dev.oracledbashop.com) - GGSOURCE:
Start GGSCI and login into database using dblogin command.
By default manager parameter file will be created while installing the GoldenGate software we just need to add the user information to manager parameter file.
[code] [dev:oracle:ggsource}$ 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 (dev.oracledbashop.com) 1> login userid gg01, password gg01 ERROR: Invalid command. GGSCI (dev.oracledbashop.com) 2> dblogin userid gg01, password gg01 Successfully logged into database. GGSCI (dev.oracledbashop.com as gg01@ggsource) 3> info mgr Manager is running (IP port TCP:dev.oracledbashop.com.7809, Process ID 6177). GGSCI (dev.oracledbashop.com as gg01@ggsource) 4> view param mgr PORT 7809 GGSCI (dev.oracledbashop.com as gg01@ggsource) 5> edit param mgr GGSCI (dev.oracledbashop.com as gg01@ggsource) 6> view param mgr PORT 7809 USERIDALIAS gg01 [/code]
Source side add trandata for particular table which we wants to replicate the data to target database.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 11> add trandata t1 2020-05-10 17:27:36 ERROR OGG-15120 Schema name is required for table specification t1. GGSCI (dev.oracledbashop.com as gg01@ggsource) 12> add trandata us01.* 2020-05-10 17:28:10 INFO OGG-15132 Logging of supplemental redo data enabled for table US01.T1. 2020-05-10 17:28:11 INFO OGG-15133 TRANDATA for scheduling columns has been added on table US01.T1. 2020-05-10 17:28:11 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.T1. [/code]
Create the primary Extract parameter file - ext1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 14> edit param ext1 GGSCI (dev.oracledbashop.com as gg01@ggsource) 15> view param ext1 EXTRACT ext1 USERID gg01, PASSWORD gg01 EXTTRAIL /u01/app/oracle/product/gghome/dirdat/aa DDL INCLUDE ALL TABLE us01.*; [/code]
Create the Extract group and the local Extract trail file and start the extract process.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 16> add extract ext1 tranlog begin now EXTRACT added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 17> add exttrail /u01/app/oracle/product/gghome/dirdat/aa extract ext1 EXTTRAIL added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 18> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting [/code]
Check the status of primary extract (ext1)process.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 29> info ext1 EXTRACT EXT1 Last Started 2020-05-10 17:46 Status RUNNING Checkpoint Lag 00:06:44 (updated 00:00:05 ago) Process ID 8350 Log Read Checkpoint Oracle Redo Logs 2020-05-10 17:39:39 Seqno 9, RBA 14939136 SCN 0.0 (0) [/code]
Create the secondary Extract (data pump) parameter file - dpump1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 53> view param dpump1 EXTRACT dpump1 USERID gg01, PASSWORD gg01 RMTHOST 192.168.56.111, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gghome/dirdat/rt DDL INCLUDE ALL TABLE us01.*; [/code]
Create the data pump group and the remote Extract trail file and start the data pump process. [code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 42> add extract dpump1 exttrailsource /u01/app/oracle/product/gghome/dirdat/aa ERROR: EXTRACT DPUMP1 already exists. GGSCI (dev.oracledbashop.com as gg01@ggsource) 43> add rmttrail /u01/app/oracle/product/gghome/dirdat/rt extract dpump1 TARGETEXTTRAIL already exists. GGSCI (dev.oracledbashop.com as gg01@ggsource) 44> start extract dpump1 Sending START request to MANAGER ... EXTRACT DPUMP1 starting [/code]
To check the status of data pump process - dpump1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 52> info dpump1 EXTRACT DPUMP1 Last Started 2020-05-10 18:03 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:04:03 ago) Process ID 9691 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/aa000000000 First Record RBA 0 [/code]
Start GGSCI and login into database using dblogin command.
By default manager parameter file will be created while installing the GoldenGate software we just need to add the user information to manager parameter file.
[code] [dev:oracle:ggsource}$ 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 (dev.oracledbashop.com) 1> login userid gg01, password gg01 ERROR: Invalid command. GGSCI (dev.oracledbashop.com) 2> dblogin userid gg01, password gg01 Successfully logged into database. GGSCI (dev.oracledbashop.com as gg01@ggsource) 3> info mgr Manager is running (IP port TCP:dev.oracledbashop.com.7809, Process ID 6177). GGSCI (dev.oracledbashop.com as gg01@ggsource) 4> view param mgr PORT 7809 GGSCI (dev.oracledbashop.com as gg01@ggsource) 5> edit param mgr GGSCI (dev.oracledbashop.com as gg01@ggsource) 6> view param mgr PORT 7809 USERIDALIAS gg01 [/code]
Source side add trandata for particular table which we wants to replicate the data to target database.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 11> add trandata t1 2020-05-10 17:27:36 ERROR OGG-15120 Schema name is required for table specification t1. GGSCI (dev.oracledbashop.com as gg01@ggsource) 12> add trandata us01.* 2020-05-10 17:28:10 INFO OGG-15132 Logging of supplemental redo data enabled for table US01.T1. 2020-05-10 17:28:11 INFO OGG-15133 TRANDATA for scheduling columns has been added on table US01.T1. 2020-05-10 17:28:11 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.T1. [/code]
Create the primary Extract parameter file - ext1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 14> edit param ext1 GGSCI (dev.oracledbashop.com as gg01@ggsource) 15> view param ext1 EXTRACT ext1 USERID gg01, PASSWORD gg01 EXTTRAIL /u01/app/oracle/product/gghome/dirdat/aa DDL INCLUDE ALL TABLE us01.*; [/code]
Create the Extract group and the local Extract trail file and start the extract process.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 16> add extract ext1 tranlog begin now EXTRACT added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 17> add exttrail /u01/app/oracle/product/gghome/dirdat/aa extract ext1 EXTTRAIL added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 18> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting [/code]
Check the status of primary extract (ext1)process.
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 29> info ext1 EXTRACT EXT1 Last Started 2020-05-10 17:46 Status RUNNING Checkpoint Lag 00:06:44 (updated 00:00:05 ago) Process ID 8350 Log Read Checkpoint Oracle Redo Logs 2020-05-10 17:39:39 Seqno 9, RBA 14939136 SCN 0.0 (0) [/code]
Create the secondary Extract (data pump) parameter file - dpump1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 53> view param dpump1 EXTRACT dpump1 USERID gg01, PASSWORD gg01 RMTHOST 192.168.56.111, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gghome/dirdat/rt DDL INCLUDE ALL TABLE us01.*; [/code]
Create the data pump group and the remote Extract trail file and start the data pump process. [code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 42> add extract dpump1 exttrailsource /u01/app/oracle/product/gghome/dirdat/aa ERROR: EXTRACT DPUMP1 already exists. GGSCI (dev.oracledbashop.com as gg01@ggsource) 43> add rmttrail /u01/app/oracle/product/gghome/dirdat/rt extract dpump1 TARGETEXTTRAIL already exists. GGSCI (dev.oracledbashop.com as gg01@ggsource) 44> start extract dpump1 Sending START request to MANAGER ... EXTRACT DPUMP1 starting [/code]
To check the status of data pump process - dpump1
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 52> info dpump1 EXTRACT DPUMP1 Last Started 2020-05-10 18:03 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:04:03 ago) Process ID 9691 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/aa000000000 First Record RBA 0 [/code]
TARGET (dev1.oracledbashop.com) - GGTARGET
Start GGSCI and login into database using dblogin command.
[code] [oracle@dev1 gghome]$ 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 (dev1.oracledbashop.com) 1> info mgr Manager is running (IP port TCP:dev1.oracledbashop.com.7809, Process ID 8512). GGSCI (dev1.oracledbashop.com) 2> dblogin userid gg01, password gg01 Successfully logged into database. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 3> view param mgr PORT 7809 GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 4> info mgr Manager is running (IP port TCP:dev1.oracledbashop.com.7809, Process ID 8512). [/code]
Create a checkpoint Table in the target database[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 5> add checkpointtable gg01.chkpt Successfully created checkpoint table gg01.chkpt. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 6> info checkpointtable gg01.chkpt Checkpoint table gg01.chkpt created 2020-05-10 18:16:05. [/code]
Create the REPLICAT parameter file for target side - rep1 [code] GGSCI (dev1.oracledbashop.com) 1> view param rep1 REPLICAT rep1 USERID gg01, PASSWORD gg01 DDL INCLUDE ALL DDLERROR DEFAULT IGNORE MAP us01.*, TARGET us01.*; [/code]
Create and start the REPLICAT process - rep1
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 8> add replicat rep1 exttrail /u01/app/oracle/product/gghome/dirdat/rt checkpointtable gg01.chkpt REPLICAT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 9> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 10> info rep1 REPLICAT REP1 Last Started 2020-05-10 18:21 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Process ID 10903 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/rt000000000 First Record RBA 0 [/code]
CONFIGURE BI-DIRECTIONAL PROCESS
[code] [oracle@dev1 gghome]$ 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 (dev1.oracledbashop.com) 1> info mgr Manager is running (IP port TCP:dev1.oracledbashop.com.7809, Process ID 8512). GGSCI (dev1.oracledbashop.com) 2> dblogin userid gg01, password gg01 Successfully logged into database. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 3> view param mgr PORT 7809 GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 4> info mgr Manager is running (IP port TCP:dev1.oracledbashop.com.7809, Process ID 8512). [/code]
Create a checkpoint Table in the target database[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 5> add checkpointtable gg01.chkpt Successfully created checkpoint table gg01.chkpt. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 6> info checkpointtable gg01.chkpt Checkpoint table gg01.chkpt created 2020-05-10 18:16:05. [/code]
Create the REPLICAT parameter file for target side - rep1 [code] GGSCI (dev1.oracledbashop.com) 1> view param rep1 REPLICAT rep1 USERID gg01, PASSWORD gg01 DDL INCLUDE ALL DDLERROR DEFAULT IGNORE MAP us01.*, TARGET us01.*; [/code]
Create and start the REPLICAT process - rep1
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 8> add replicat rep1 exttrail /u01/app/oracle/product/gghome/dirdat/rt checkpointtable gg01.chkpt REPLICAT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 9> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 10> info rep1 REPLICAT REP1 Last Started 2020-05-10 18:21 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Process ID 10903 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/rt000000000 First Record RBA 0 [/code]
CONFIGURE BI-DIRECTIONAL PROCESS
TARGET SIDE (dev1.oracledbashop.com) - GGTARGET
From the target database we have to create Extract and Pump processes for bidirectional replication.
Target side add trandata for particular table which we wants to replicate the data to target database.
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 11> add trandata us01.* 2020-05-10 18:24:53 INFO OGG-15132 Logging of supplemental redo data enabled for table US01.T1. 2020-05-10 18:24:53 INFO OGG-15133 TRANDATA for scheduling columns has been added on table US01.T1. 2020-05-10 18:24:53 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.T1. 2020-05-10 18:24:53 INFO OGG-10471 ***** Oracle Goldengate support information on table US01.T1 ***** Oracle Goldengate support native capture on table US01.T1. Oracle Goldengate marked following column as key columns on table US01.T1: OBJECT_ID. [/code]
Create the primary Extract parameter file fro target side - ext2
[code] GGSCI (dev1.oracledbashop.com) 2> view param ext2 EXTRACT ext2 USERID gg01, PASSWORD gg01 EXTTRAIL /u01/app/oracle/product/gghome/dirdat/ac DDL INCLUDE ALL TABLE us01.*; [/code]
Create the Extract group and the local Extract trail file and start the extract process
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 13> add extract ext2 tranlog begin now EXTRACT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 14> add exttrail /u01/app/oracle/product/gghome/dirdat/ac extract ext2 EXTTRAIL added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 15> start ext2 Sending START request to MANAGER ... EXTRACT EXT2 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 16> info ext2 EXTRACT EXT2 Last Started 2020-05-10 18:27 Status RUNNING Checkpoint Lag 00:00:41 (updated 00:00:03 ago) Process ID 11325 Log Read Checkpoint Oracle Redo Logs 2020-05-10 18:26:22 Seqno 9, RBA 21545984 SCN 0.0 (0) [/code]
Create the secondary Extract (data pump) parameter file for target side [code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 19> view param dpump2 EXTRACT dpump2 USERID gg01, PASSWORD gg01 RMTHOST 192.168.56.110, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gghome/dirdat/ad DDL INCLUDE ALL TABLE us01.*; [/code]
Create the data pump group and the remote Extract trail file and start the data pump process.
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 20> add extract dpump2 exttrailsource /u01/app/oracle/product/gghome/dirdat/ac EXTRACT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 21> add rmttrail /u01/app/oracle/product/gghome/dirdat/ad extract dpump2 RMTTRAIL added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 22> start dpump2 Sending START request to MANAGER ... EXTRACT DPUMP2 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 23> info dpump2 EXTRACT DPUMP2 Last Started 2020-05-10 18:30 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:23 ago) Process ID 11580 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/ac000000000 First Record RBA 0 [/code]
SOURCE SIDE (dev.oracledbashop.com) - GGSOURCE
Create a checkpoint Table in the source database
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 55> add checkpointtable gg01.chkpt Successfully created checkpoint table gg01.chkpt. GGSCI (dev.oracledbashop.com as gg01@ggsource) 56> info checkpointtable gg01.chkpt Checkpoint table gg01.chkpt created 2020-05-10 18:31:08. [/code]
Create the REPLICAT parameter file for source side - rep2
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 58> view param rep2 REPLICAT rep2 USERID gg01, PASSWORD gg01 DDL INCLUDE ALL DDLERROR DEFAULT IGNORE MAP us01.*, TARGET us01.*; [/code]
Create and start the REPLICAT process
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 59> add replicat rep2 exttrail /u01/app/oracle/product/gghome/dirdat/ad checkpointtable gg01.chkpt REPLICAT added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 60> start rep2 Sending START request to MANAGER ... REPLICAT REP2 starting GGSCI (dev.oracledbashop.com as gg01@ggsource) 61> info rep2 REPLICAT REP2 Last Started 2020-05-10 18:33 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:05 ago) Process ID 11921 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/ad000000000 First Record RBA 0 [/code]
Now check the replication from source to target database - GGSOURCE
[code] SQL> conn us01/us01 Connected. SQL> INSERT INTO t1 (object_id,owner,object_name,object_type) SELECT object_id,owner,object_name,object_type FROM dba_objects WHERE object_id BETWEEN 10001 AND 11000; 2 3 4 999 rows created. SQL> select count(*) from t1; COUNT(*) ---------- 10979 SQL> commit; Commit complete. [/code]
Now Check the count in target - GGTARGET
[code] SQL> conn us01/us01 Connected. SQL> select count(*) from t1; COUNT(*) ---------- 9980 SQL> / COUNT(*) ---------- 9980 SQL> / COUNT(*) ---------- 10979 [/code]
Now Check the count in source - GGSOURCE
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 23:45:36 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 22:59:10 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 11935 [/code]
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 11> add trandata us01.* 2020-05-10 18:24:53 INFO OGG-15132 Logging of supplemental redo data enabled for table US01.T1. 2020-05-10 18:24:53 INFO OGG-15133 TRANDATA for scheduling columns has been added on table US01.T1. 2020-05-10 18:24:53 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.T1. 2020-05-10 18:24:53 INFO OGG-10471 ***** Oracle Goldengate support information on table US01.T1 ***** Oracle Goldengate support native capture on table US01.T1. Oracle Goldengate marked following column as key columns on table US01.T1: OBJECT_ID. [/code]
Create the primary Extract parameter file fro target side - ext2
[code] GGSCI (dev1.oracledbashop.com) 2> view param ext2 EXTRACT ext2 USERID gg01, PASSWORD gg01 EXTTRAIL /u01/app/oracle/product/gghome/dirdat/ac DDL INCLUDE ALL TABLE us01.*; [/code]
Create the Extract group and the local Extract trail file and start the extract process
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 13> add extract ext2 tranlog begin now EXTRACT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 14> add exttrail /u01/app/oracle/product/gghome/dirdat/ac extract ext2 EXTTRAIL added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 15> start ext2 Sending START request to MANAGER ... EXTRACT EXT2 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 16> info ext2 EXTRACT EXT2 Last Started 2020-05-10 18:27 Status RUNNING Checkpoint Lag 00:00:41 (updated 00:00:03 ago) Process ID 11325 Log Read Checkpoint Oracle Redo Logs 2020-05-10 18:26:22 Seqno 9, RBA 21545984 SCN 0.0 (0) [/code]
Create the secondary Extract (data pump) parameter file for target side [code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 19> view param dpump2 EXTRACT dpump2 USERID gg01, PASSWORD gg01 RMTHOST 192.168.56.110, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gghome/dirdat/ad DDL INCLUDE ALL TABLE us01.*; [/code]
Create the data pump group and the remote Extract trail file and start the data pump process.
[code] GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 20> add extract dpump2 exttrailsource /u01/app/oracle/product/gghome/dirdat/ac EXTRACT added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 21> add rmttrail /u01/app/oracle/product/gghome/dirdat/ad extract dpump2 RMTTRAIL added. GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 22> start dpump2 Sending START request to MANAGER ... EXTRACT DPUMP2 starting GGSCI (dev1.oracledbashop.com as gg01@ggtarget) 23> info dpump2 EXTRACT DPUMP2 Last Started 2020-05-10 18:30 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:23 ago) Process ID 11580 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/ac000000000 First Record RBA 0 [/code]
SOURCE SIDE (dev.oracledbashop.com) - GGSOURCE
Create a checkpoint Table in the source database
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 55> add checkpointtable gg01.chkpt Successfully created checkpoint table gg01.chkpt. GGSCI (dev.oracledbashop.com as gg01@ggsource) 56> info checkpointtable gg01.chkpt Checkpoint table gg01.chkpt created 2020-05-10 18:31:08. [/code]
Create the REPLICAT parameter file for source side - rep2
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 58> view param rep2 REPLICAT rep2 USERID gg01, PASSWORD gg01 DDL INCLUDE ALL DDLERROR DEFAULT IGNORE MAP us01.*, TARGET us01.*; [/code]
Create and start the REPLICAT process
[code] GGSCI (dev.oracledbashop.com as gg01@ggsource) 59> add replicat rep2 exttrail /u01/app/oracle/product/gghome/dirdat/ad checkpointtable gg01.chkpt REPLICAT added. GGSCI (dev.oracledbashop.com as gg01@ggsource) 60> start rep2 Sending START request to MANAGER ... REPLICAT REP2 starting GGSCI (dev.oracledbashop.com as gg01@ggsource) 61> info rep2 REPLICAT REP2 Last Started 2020-05-10 18:33 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:05 ago) Process ID 11921 Log Read Checkpoint File /u01/app/oracle/product/gghome/dirdat/ad000000000 First Record RBA 0 [/code]
Now check the replication from source to target database - GGSOURCE
[code] SQL> conn us01/us01 Connected. SQL> INSERT INTO t1 (object_id,owner,object_name,object_type) SELECT object_id,owner,object_name,object_type FROM dba_objects WHERE object_id BETWEEN 10001 AND 11000; 2 3 4 999 rows created. SQL> select count(*) from t1; COUNT(*) ---------- 10979 SQL> commit; Commit complete. [/code]
Now Check the count in target - GGTARGET
[code] SQL> conn us01/us01 Connected. SQL> select count(*) from t1; COUNT(*) ---------- 9980 SQL> / COUNT(*) ---------- 9980 SQL> / COUNT(*) ---------- 10979 [/code]
Now check the replication from TARGET to SOURCE database - GGTARGET
[code]
[dev1:oracle:ggtarget]$ sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 22:57:07 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun May 10 2020 20:57:56 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from t1;
COUNT(*)
----------
10979
SQL> INSERT INTO t1 (object_id,owner,object_name,object_type)
SELECT object_id,owner,object_name,object_type
FROM dba_objects
WHERE object_id BETWEEN 11001 AND 12000; 2 3 4
956 rows created.
SQL> select count(*) from t1;
COUNT(*)
----------
11935
SQL> commit;
[/code]
Now Check the count in source - GGSOURCE
[code] [dev:oracle:ggsource}$ sqlplus us01/us01 SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 10 23:45:36 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sun May 10 2020 22:59:10 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select count(*) from t1; COUNT(*) ---------- 11935 [/code]
No comments:
Post a Comment