Differences

This shows you the differences between two versions of the page.

--- oracle_12_goldengate_conf_prepare_source [2019/10/18 20:04] – external edit 127.0.0.1
+++ oracle_12_goldengate_conf_prepare_source [2020/11/09 19:50] (current) – andonovj
@@ Line 1: / Line 1: @@
 ======Configuration of the source======
+To configure the source database we have to do two things:
+  * Ensure the database is set correctly.
+  * Ensure the GoldenGate is set correctly.
-=====Clean the environment=====
+Both need to be configured if you want the Golden Gate to work properly.
-First assure that you don't have any running jobs:
+=====Configure Database=====
+We have to ensure that we have enabled:
-<sxh>
+  * Supplemental logging
-/u01/app/oracle/product/12.1.2/DBNAME_gg/ggsci
+  * Golden Gate Replication
-GGSCI (sourceSRV.izb.local as ggate@DBNAME) 1> info all
+  * Force Logging
+  * Necessary rights for the user
-Program     Status      Group       Lag at Chkpt  Time Since Chkpt
-MANAGER     RUNNING     <- Except this one, this one is by default there (Boring)
+So let's get started. You can set supplemental logging in Oracle database in three levels:
-</sxh>
+<Code:bash|Database Level>
+SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
+SQL> ALTER DATABASE FORCE LOGGING;
+</Code>
-If any jobs are present, STOP them, DELETE them and Clean them.
+<Code:bash|Schema Level>
+GGSCI> ADD SCHEMATRANDATA schema [ALLCOLS|NOSCHEDULINGCOLS]
+</Code>
+<Code:bash|Table Level>
+GGSCI> ADD TRANDATA [container.]schema.table[ALLCOLS|NOSCHEDULINGCOLS]
+</Code>
+The commands from the GGSCI are heterogenous, which means, if the database is DB2, it will enable the necessary logging for DB2.
-<sxh>
+<Code:bash|Enable Supplemental Logging>
-GGSCI (sourceSRV.izb.local) 1> info all
+[oracle@edvmr1p0 les03]$ sqlplus / as sysdba
+SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
+SUPPLEME FORCE_LOGGING
+-------- ---------------------------------------
+NO	 NO
-Program     Status      Group       Lag at Chkpt  Time Since Chkpt
+SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-MANAGER     RUNNING
+Database altered.
-EXTRACT     RUNNING     EBKDMP1     00:00:00      00:00:08
+SQL> ALTER DATABASE FORCE LOGGING;
-EXTRACT     RUNNING     EBKEXT1     00:00:00      00:00:04
+Database altered.
-GGSCI (sourceSRV.izb.local) 2> stop EBKDMP1
+SQL> ALTER SYSTEM SWITCH LOGFILE;
-Sending STOP request to EXTRACT EBKDMP1 ...
+System altered.
-Request processed.
+SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
+SUPPLEME FORCE_LOGGING
+-------- ---------------------------------------
+YES	 YES
-GGSCI (sourceSRV.izb.local) 3> info EBKEXT1
+</Code>
-EXTRACT    EBKEXT1   Last Started 2018-01-16 11:15   Status RUNNING
+<Code:bash|Enable Replication>
-Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
+SQL> show parameter enable_goldengate
-Process ID           17209
-Log Read Checkpoint  Oracle Redo Logs
--01-16 12:36:12  Seqno 2664, RBA 5423616
-                     SCN 0.474510079 (474510079)
+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
-GGSCI (sourceSRV.izb.local) 4> stop EBKEXT1
+NAME				     TYPE	 VALUE
+------------------------------------ ----------- ------------------------------
+enable_goldengate_replication	     boolean	 TRUE
+</Code>
-Sending STOP request to EXTRACT EBKEXT1 ...
+Finally, grant the necessary rights to the user:
-Request processed.
+<Code:bash|Grant rights to user>
+SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGG_ADMIN',container=>'all');
+                                                         user                   containers...duh :)
+PL/SQL procedure successfully completed.
+SQL>
+</Code>
+=====Configure Golden Gate=====
-GGSCI (sourceSRV.izb.local) 5> cleanup EBKDMP1
-Cleanup completed
-GGSCI (sourceSRV.izb.local) 6> dblogin userid ggate password "password"
-Successfully logged into database.
-GGSCI (sourceSRV.izb.local as ggate@DBNAME) 7> cleanup EBKEXT1
-Cleanup completed
-GGSCI (sourceSRV.izb.local as ggate@DBNAME) 8> delete EBKDMP1
-Deleted EXTRACT EBKDMP1.
-GGSCI (sourceSRV.izb.local as ggate@DBNAME) 9> delete EBKEXT1
-Deleted EXTRACT EBKEXT1.
-GGSCI (sourceSRV.izb.local as ggate@DBNAME) 10> info all
-Program     Status      Group       Lag at Chkpt  Time Since Chkpt
-MANAGER     RUNNING
-</sxh>
-Be sure to delete any retail file: local retail and remote retail file.
-Any configuration can files can be located via:
-<sxh>
-GGSCI (sourceSRV.izb.local) 4> show all
-Parameter settings:
-SET DEBUG      OFF
-Current directory: /u01/app/oracle/product/12.1.2/<instance release>
-Editor:  vi
-Reports (.rpt)                 /u01/app/oracle/product/12.1.2/DBNAME_gg/dirrpt
-Parameters (.prm)              /u01/app/oracle/product/12.1.2/DBNAME_gg/dirprm
-Replicat Checkpoints (.cpr)    /u01/app/oracle/product/12.1.2/DBNAME_gg/dirchk
-Extract Checkpoints (.cpe)     /u01/app/oracle/product/12.1.2/DBNAME_gg/dirchk
-Process Status (.pcs)          /u01/app/oracle/product/12.1.2/DBNAME_gg/dirpcs
-SQL Scripts (.sql)             /u01/app/oracle/product/12.1.2/DBNAME_gg/dirsql
-Database Definitions (.def)    /u01/app/oracle/product/12.1.2/DBNAME_gg/dirdef
-Dump files (.dmp)              /u01/app/oracle/product/12.1.2/DBNAME_gg/dirdmp
-Masterkey wallet files (.wlt)  /u01/app/oracle/product/12.1.2/DBNAME_gg/dirwlt
-Credential store files (.crd)  /u01/app/oracle/product/12.1.2/DBNAME_gg/dircrd
-</sxh>
-**!!!!!P.S. DO NOT DELETE THE REDO LOG FILES :) !!!!!!**
-=====Configuration=====
 In order to configure a export on the SOURCE, we should configure 3 jobs:
@@ Line 116: / Line 90: @@
   * Extract Job <- Reads the redo
   * Dump Job  <- Takes the information from the previous job and dump it into retail file.
-Be sure to clean any remaining files:
-<sxh>
-[oracle@sourceSRV dirdat]$ ls -lart
-total 8
-drwxr-xr-x 29 oracle oinstall 4096 Nov 14 14:35 ..
-drwxr-x---  2 oracle oinstall 4096 Jan 16 12:35 .
-</sxh>
-====Configuration Files====
 Create the configuration file with the same name as you want the JOB name to be called.
 ===Manager===
-<sxh bash>
+<Code:bash|Manager Config>
 [oracle@matar dirprm]$ cat mgr.prm
 PORT 7809
@@ Line 139: / Line 102: @@
 Autostart Extract EBK*
 AUTORESTART Extract *, WaitMinutes 2, Retries 6
-</sxh>
+</Code>
 ===Extract job: EBKEXT1===
-<sxh>
+<Code:bash|Extract Config>
 cat /u01/app/oracle/product/12.1.2/<instance name>/dirprm/ebkext1.prm
 Extract ebkext1
@@ Line 156: / Line 119: @@
 Table SCHEMANAME1.* ;
 Table SCHEMANAME2.* ;
-</sxh>
+</Code>
 ===DUMP job: EBKDMP1===
+<Code:bash|Data Pump Config>
 cat /u01/app/oracle/product/12.1.2/<instance name>/dirprm/ebkdmp1.prm
-<sxh bash>
 Extract EBKDMP1
 USERID ggate, PASSWORD "password!"
@@ Line 169: / Line 132: @@
 Table schema_name1.* ;
 Table schema_name2.* ;
-</sxh>
+</Code>
@@ Line 175: / Line 138: @@
 The jobs should be added to the configuration as well using the GoldenGate Software Command Interface(GGSCI)
-<sxh>
+<Code:bash|Golden Gate Config>
 [oracle@sourceSRV dirdat]$ /u01/app/oracle/product/12.1.2/DBNAME_gg/ggsci
@@ Line 190: / Line 153: @@
 Successfully logged into database.
+---Add the Classical Extract
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 2> add extract EBKEXT1, TRANLOG, BEGIN NOW
 EXTRACT added.
+---Create Local Trail and associate it with the Classical Extract
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 5> add exttrail ./dirdat/lt, EXTRACT EBKEXT1
 EXTTRAIL added.
+---Create Data Pump process and associate it with the local trail
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 6> add EXTRACT EBKDMP1, EXTTRAILSOURCE ./dirdat/lt
 EXTRACT added.
+---Add Remote trail and associate it with the Data pump process
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 7> add RMTTRAIL ./dirdat/rt, EXTRACT EBKDMP1
 RMTTRAIL added.
+---Add Supplemental logging for the schema
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 8> ADD SCHEMATRANDATA SCHEMANAME1   <- Schema name
@@ Line 216: / Line 183: @@
 *****************************************************************************************
+---Add supplemental logging for 2nd schema
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 9> add schematrandata SCHEMANAME2
@@ Line 228: / Line 195: @@
 *****************************************************************************************
-</sxh>
+</Code>
@@ Line 235: / Line 202: @@
-<sxh>
+<Code:bash|Golden Gate Config Check>
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 10> info all
@@ Line 265: / Line 232: @@
 EXTRACT     RUNNING     EBKEXT1     00:07:52      00:00:08
-</sxh>
+</Code>
 Wait them for couple minutes, to assure that they will not end abnormally (ABEND)