Oracle Uni-Directional Synch using GoldenGate
In this article we will be setting up Goldengate for Uni-Directional Synching
Source Setup HOSTNAME => HOST12C2020 DB Name => NCDB DB Version => 12.2.0 GG Version => 12.3 GG Admin User => ggsource Schema to replicate => dbsource Target Setup HOSTNAME => HOST12C2021 DB Name => TCDB DB Version =>12.2.0 GG Version => 12.3 GG Admin User => ggtarget Schema to replicate => dbtarget
Check Tnsping for Both database and connectivity from Source to target an vice-versa.
[oracle@HOST12C2020 ~]$ tnsping NCDB TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-MAY-2020 00:05:26 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NCDB))) OK (140 msec) [oracle@HOST12C2020 ~]$ tnsping TCDB TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-MAY-2020 00:05:30 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TCDB))) OK (840 msec) [oracle@HOST12C2020 ~]$
Check Connectivity b/w Source and Target
[oracle@HOST12C2020 ~]$ ping -c 2 HOST12C2020 PING HOST12C2020 (192.168.188.100) 56(84) bytes of data. 64 bytes from HOST12C2020 (192.168.188.100): icmp_seq=1 ttl=64 time=0.020 ms 64 bytes from HOST12C2020 (192.168.188.100): icmp_seq=2 ttl=64 time=0.048 ms --- HOST12C2020 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1000ms rtt min/avg/max/mdev = 0.020/0.034/0.048/0.014 ms [oracle@HOST12C2020 ~]$ [oracle@HOST12C2020 ~]$ ping -c 2 HOST12C2021 PING HOST12C2021 (192.168.188.101) 56(84) bytes of data. 64 bytes from HOST12C2021 (192.168.188.101): icmp_seq=1 ttl=64 time=0.325 ms 64 bytes from HOST12C2021 (192.168.188.101): icmp_seq=2 ttl=64 time=0.856 ms --- HOST12C2021 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1001ms rtt min/avg/max/mdev = 0.325/0.590/0.856/0.266 ms [oracle@HOST12C2020 ~]$
==>> Configuring Source Database <<==
Check Archivelog on Source Side This has to be enabled as Classic Capture will use Redo log to process data. [oracle@HOST12C2020 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 12.2.0.1.0 Production on Thu May 21 00:09:50 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Current log sequence 5 SQL> SQL> shut immediate; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 876609536 bytes Fixed Size 8626672 bytes Variable Size 599789072 bytes Database Buffers 264241152 bytes Redo Buffers 3952640 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
Check Force Logging and Supplemental Log on Source
This is required to supply extra information to Oracle process and to provide forcefully logs to Oracle for every operation in database. SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database; SUPPLEME FORCE_LOGGING -------- --------------------------------------- NO NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database; SUPPLEME FORCE_LOGGING -------- --------------------------------------- YES YES SQL>
Check GoldenGate Mandatory Parameter value
ENABLE_GOLDENGATE_REPLICATION
This parameter has to be set on Both side Source and target .This is mandatory parameter. 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 SQL>
Create DB User on Source Side
Now there will be one DB user (SCHEMA) which will be holding tables and objects that we need to replicate on target . SQL> create user dbsource identified by dbsource default tablespace source_tbs quota unlimited on source_tbs; User created. SQL> grant connect,resource to dbsource; Grant succeeded. [oracle@HOST12C2020 gg_home]$ sqlplus "dbsource/dbsource" SQL*Plus: Release 12.2.0.1.0 Production on Thu May 21 01:09:55 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Create Sample table on Source Side
SQL> create table Source_table (name varchar2(10),id int);
Table created.
SQL> commit;
Commit complete.
SQL> insert into Source_table values ('RODRIGUE',1);
1 row created.
SQL> insert into Source_table values ('Kewin',2);
1 row created.
SQL> commit;
Commit complete.
Create Goldengate Admin Account
This account will synch data of database users objects from Source System to target System . SQL> create user ggsource identified by ggsource default tablespace ggsource_tbs quota unlimited on ggsource_tbs; User created. SQL> grant create session,connect,resource,alter system to ggsource; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggsource', privilege_type=>'CAPTURE', grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. SQL>
Run Marker and DDL-enable/setup and Roll-setup script
These scripts are required to perform DDL replication from Source to target . [oracle@HOST12C2020 trace]$ cd $GG_HOME [oracle@HOST12C2020 gg_home]$ pwd /u01/app/oracle/gg_home [oracle@HOST12C2020 gg_home]$ sqlplus "/as sysdba" SQL*Plus: Release 12.2.0.1.0 Production on Thu May 21 01:00:59 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @marker_setup.sql Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ggsource Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGSOURCE MARKER TABLE -------------------------------- OK MARKER SEQUENCE -------------------------------- OK Script complete. SQL> SQL> @ddl_setup Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ggsource Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. SQL> @role_setup.sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggsource Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO where is the user assigned to the GoldenGate processes. SQL> GRANT GGS_GGSUSER_ROLE TO ggsource; Grant succeeded. SQL> @ddl_enable.sql Trigger altered.
Add trandata on Source database for Schema which need to be replicated to target =>
[oracle@HOST12C2020 gg_home]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30 Operating system character set identified as UTF-8. GGSCI (HOST12C2020) 3> dblogin userid ggsource password ggsource Successfully logged into database. GGSCI (HOST12C2020 as ggsource@NCDB) 4> add trandata dbsource.* 2020-05-21 01:12:18 WARNING OGG-06439 No unique key is defined for table SOURCE_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2020-05-21 01:12:18 INFO OGG-15130 No key found for table DBSOURCE.SOURCE_TABLE. All viable columns will be logged. 2020-05-21 01:12:21 INFO OGG-15132 Logging of supplemental redo data enabled for table DBSOURCE.SOURCE_TABLE. 2020-05-21 01:12:21 INFO OGG-15133 TRANDATA for scheduling columns has been added on table DBSOURCE.SOURCE_TABLE. 2020-05-21 01:12:21 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table DBSOURCE.SOURCE_TABLE. 2020-05-21 01:12:25 INFO OGG-10471 ***** Oracle Goldengate support information on table DBSOURCE.SOURCE_TABLE ***** Oracle Goldengate support native capture on table DBSOURCE.SOURCE_TABLE. Oracle Goldengate marked following column as key columns on table DBSOURCE.SOURCE_TABLE: NAME, ID No unique key is defined for table DBSOURCE.SOURCE_TABLE.
Create Extract and Pump file to Fetch and send data to Target system
GGSCI (HOST12C2020) 3> dblogin userid ggsource password ggsource
Successfully logged into database.
Creating Extract on Source DB
GGSCI (HOST12C2020) 2> view params ext1
EXTRACT ext1
USERID ggsource@NCDB, PASSWORD ggsource
EXTTRAIL /u01/app/oracle/gg_home/dirdat/aa
TABLE dbsource.*;
GGSCI (HOST12C2020) 3>
GGSCI (HOST12C2020) 3> add extract ext1 tranlog begin now
EXTRACT added.
GGSCI (HOST12C2020) 4> add exttrail /u01/app/oracle/gg_home/dirdat/aa extract ext1
EXTTRAIL added.
GGSCI (HOST12C2020) 5> info ext1
EXTRACT EXT1 Initialized 2020-05-23 12:29 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:29 ago)
Log Read Checkpoint Oracle Redo Logs
2020-05-23 12:29:33 Seqno 0, RBA 0
SCN 0.0 (0)
Creating Extract (pump) on Source DB
GGSCI (HOST12C2020) 6> edit params dpump1
EXTRACT dpump1
USERID ggsource@NCDB, PASSWORD gsource
RMTHOST HOST12C2021, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg_home/dirdat/ab
TABLE dbsource.*;
GGSCI (HOST12C2020) 7> add extract dpump1 exttrailsource /u01/app/oracle/gg_home/dirdat/aa
EXTRACT added.
GGSCI (HOST12C2020) 8> add rmttrail /u01/app/oracle/gg_home/dirdat/ab extract dpump1
RMTTRAIL added.
GGSCI (HOST12C2020) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPUMP1 00:00:00 00:03:35
EXTRACT STOPPED EXT1 00:00:00 00:08:57
==>>Configure Target Database<<==
Check Archivelog on Target Side
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Current log sequence 3 SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 843055104 bytes Fixed Size 8626288 bytes Variable Size 587206544 bytes Database Buffers 243269632 bytes Redo Buffers 3952640 bytes Database mounted. SQL> SQL> ALTER DATABASE ARCHIVELOG; Database altered. SQL> alter database open; Database altered. SQL> SQL> select LOG_MODE from v$database; LOG_MODE ------------ ARCHIVELOG SQL>
Check GoldenGate Mandatory Parameter value
ENABLE_GOLDENGATE_REPLICATION
This parameter has to be set on Both side Source and target .This is mandatory parameter. 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
Create DB User on Target Side
SQL> create user dbtarget identified by dbtarget default tablespace dbtarget_tbs quota unlimited on dbtarget_tbs; User created. SQL> grant connect,resource to dbtarget; Grant succeeded.
Create Goldengate Admin Account
SQL> create user ggtarget identified by ggtarget default tablespace ggtarget_tbs quota unlimited on ggtarget_tbs;
User created.
SQL> grant create session,connect,resource,alter system to ggtarget;
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggtarget', privilege_type=>'APPLY', grant_optional_privileges=>'*');
Initializing Target database from Source DB
On Source SQL> select current_scn from v$database; CURRENT_SCN ----------- 1841208 SQL> SQL> create directory Exp_1 as '/u01/app/oracle/'; Directory created. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@HOST12C2020 gg_home]$ expdp userid="' / as sysdba'" schemas=dbsource dumpfile=dbsource.dmp logfile=dbsource.log directory=EXP_1 flashback_scn=1841208 Export: Release 12.2.0.1.0 - Production on Sat May 23 13:11:07 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_SCHEMA_01": userid="/******** AS SYSDBA" schemas=dbsource ....... . . exported "DBSOURCE"."SOURCE_TABLE" 5.523 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/dbsource.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat May 23 13:12:40 2020 elapsed 0 00:01:18 [oracle@HOST12C2020 gg_home]$ On Target Scp dump files from Source to Target and import then [oracle@HOST12C2020 oracle]$ scp dbsource.dmp oracle@HOST12C2021:/u01/app/oracle/ Import Data into Target Schema. SQL> create directory IMP_1 as '/u01/app/oracle/'; Directory created. [oracle@HOST12C2021 oracle]$ impdp userid="' / as sysdba'" remap_schema=dbsource:dbtarget remap_tablespace=SOURCE_TBS:dbtarget_tbs dumpfile=dbsource.dmp logfile=dbtarget.log directory=IMP_1 Import: Release 12.2.0.1.0 - Production on Sat May 23 13:53:47 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": userid="/******** AS SYSDBA" remap_schema=dbsource:dbtarget remap_tablespace=SOURCE_TBS:dbtarget_tbs dumpfile=dbsource.dmp logfile=dbtarget.log directory=IMP_1 Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"DBTARGET" already exists ....... ....... Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat May 23 13:54:48 2020 elapsed 0 00:00:58
Add Checkpoint table and Create REPLICAT on Target Server.
GGSCI (HOST12C2021) 3> dblogin userid ggtarget password ggtarget Successfully logged into database. GGSCI (HOST12C2021 as ggtarget@TCDB) 4> info mgr Manager is running (IP port HOST12C2021.7809, Process ID 17365). GGSCI (HOST12C2021 as ggtarget@TCDB) 5> add checkpointtable ggtarget.chkpt Successfully created checkpoint table ggtarget.chkpt. GGSCI (HOST12C2021 as ggtarget@TCDB) 6> info checkpointtable ggtarget.chkpt Checkpoint table ggtarget.chkpt created 2020-05-23 13:58:47. GGSCI (HOST12C2021 as ggtarget@TCDB) 8> view params rep1 REPLICAT rep1 ASSUMETARGETDEFS USERID ggtarget@TCDB PASSWORD ggtarget MAP dbsource.*, TARGET dbtarget.*; GGSCI (HOST12C2021 as ggtarget@TCDB) 10> add replicat rep1 exttrail /u01/app/oracle/gg_home/dirdat/ab checkpointtable ggtarget.chkpt REPLICAT added. [oracle@HOST12C2021 gg_home]$ sqlplus "/as sysdba" SQL*Plus: Release 12.2.0.1.0 Production on Sat May 23 14:23:53 2020 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> GRANT insert,update,delete on dbtarget.SOURCE_TABLE TO GGTARGET; Grant succeeded. GGSCI (HOST12C2021) 3> GGSCI (HOST12C2021 as ggtarget@TCDB) 12> start replicat rep1 , aftercsn 1841208 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (HOST12C2021 as ggtarget@TCDB) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STARTING REP1 00:00:00 00:00:38
Testing Time
Insert value in Source table
[oracle@HOST12C2020 gg_home]$ sqlplus "dbsource/dbsource"
SQL> select * from SOURCE_TABLE;
NAME ID
---------- ----------
RODRIGUE 1
Kewin 2
SQL> insert into SOURCE_TABLE values ('PETER',3);
1 row created.
SQL> COMMIT;
Commit complete.
Verify value in Target table
[oracle@HOST12C2021 gg_home]$ sqlplus "dbtarget/dbtarget"
SQL> select * from SOURCE_TABLE;
NAME ID
---------- ----------
RODRIGUE 1
Kewin 2
PETER 3
SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444