Differences

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

--- oracle_12_goldengate_conf_prepare_source [2020/10/26 19:10] – andonovj
+++ oracle_12_goldengate_conf_prepare_source [2020/11/09 19:50] (current) – andonovj
@@ Line 7: / Line 7: @@
 Both need to be configured if you want the Golden Gate to work properly.
-=====Configuration=====
+=====Configure Database=====
+We have to ensure that we have enabled:
+  * Supplemental logging
+  * Golden Gate Replication
+  * Force Logging
+  * Necessary rights for the user
+So let's get started. You can set supplemental logging in Oracle database in three levels:
+<Code:bash|Database Level>
+SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
+SQL> ALTER DATABASE FORCE LOGGING;
+</Code>
+<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.
+<Code:bash|Enable Supplemental Logging>
+[oracle@edvmr1p0 les03]$ sqlplus / as sysdba
+SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
+SUPPLEME FORCE_LOGGING
+-------- ---------------------------------------
+NO	 NO
+SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
+Database altered.
+SQL> ALTER DATABASE FORCE LOGGING;
+Database altered.
+SQL> ALTER SYSTEM SWITCH LOGFILE;
+System altered.
+SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
+SUPPLEME FORCE_LOGGING
+-------- ---------------------------------------
+YES	 YES
+</Code>
+<Code:bash|Enable Replication>
+SQL> show parameter enable_goldengate
+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
+NAME				     TYPE	 VALUE
+------------------------------------ ----------- ------------------------------
+enable_goldengate_replication	     boolean	 TRUE
+</Code>
+Finally, grant the necessary rights to the user:
+<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=====
 In order to configure a export on the SOURCE, we should configure 3 jobs:
@@ Line 14: / 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 37: / 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 54: / 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 67: / Line 132: @@
 Table schema_name1.* ;
 Table schema_name2.* ;
-</sxh>
+</Code>
@@ Line 73: / 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 88: / 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 114: / Line 183: @@
 *****************************************************************************************
+---Add supplemental logging for 2nd schema
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 9> add schematrandata SCHEMANAME2
@@ Line 126: / Line 195: @@
 *****************************************************************************************
-</sxh>
+</Code>
@@ Line 133: / Line 202: @@
-<sxh>
+<Code:bash|Golden Gate Config Check>
 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 10> info all
@@ Line 163: / 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)