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.
Both need to be configured if you want the Golden Gate to work properly.
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:
Database Level
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> ALTER DATABASE FORCE LOGGING;
Schema Level
GGSCI> ADD SCHEMATRANDATA schema [ALLCOLS|NOSCHEDULINGCOLS]
Table Level
GGSCI> ADD TRANDATA [container.]schema.table[ALLCOLS|NOSCHEDULINGCOLS]
The commands from the GGSCI are heterogenous, which means, if the database is DB2, it will enable the necessary logging for DB2.
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
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
Finally, grant the necessary rights to the user:
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>
Configure Golden Gate
In order to configure a export on the SOURCE, we should configure 3 jobs:
- Manager
- Extract Job ← Reads the redo
- Dump Job ← Takes the information from the previous job and dump it into retail file.
Create the configuration file with the same name as you want the JOB name to be called.
Manager
Manager Config
[oracle@matar dirprm]$ cat mgr.prm PORT 7809 USERID ggate, PASSWORD "password!" DYNAMICPORTLIST 15010-15020 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MinkeepHours 24 Autostart Extract EBK* AUTORESTART Extract *, WaitMinutes 2, Retries 6
Extract job: EBKEXT1
Extract Config
cat /u01/app/oracle/product/12.1.2/<instance name>/dirprm/ebkext1.prm Extract ebkext1 USERID ggate, PASSWORD "password" TRANLOGOPTIONS ASMUSER sys@ASM ASMPASSWORD "ASMPASSWORD" --TranlogOptions IntegratedParams (max_sga_size 256) Exttrail ./dirdat/lt LOGALLSUPCOLS DDL INCLUDE MAPPED UPDATERECORDFORMAT COMPACT Table SCHEMANAME1.* ; Table SCHEMANAME2.* ;
DUMP job: EBKDMP1
Data Pump Config
cat /u01/app/oracle/product/12.1.2/<instance name>/dirprm/ebkdmp1.prm Extract EBKDMP1 USERID ggate, PASSWORD "password!" RMTHOST 10.200.11.73, MGRPORT 7809 RMTTRAIL ./dirdat/rt PASSTHRU Table schema_name1.* ; Table schema_name2.* ;
Add the job to the configuration
The jobs should be added to the configuration as well using the GoldenGate Software Command Interface(GGSCI)
Golden Gate Config
[oracle@sourceSRV dirdat]$ /u01/app/oracle/product/12.1.2/DBNAME_gg/ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved. GGSCI (sourceSRV.izb.local) 1> dblogin userid ggate password "password" 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 2018-01-16 12:41:32 INFO OGG-01788 SCHEMATRANDATA has been added on schema "SCHEMANAME1". 2018-01-16 12:41:32 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "SCHEMANAME1". 2018-01-16 12:41:32 INFO OGG-10471 ***** Oracle Goldengate support information on table SCHEMANAME1.CM_AUTHORIZATION_CUSTOMER_BACKUP ***** Oracle Goldengate support native capture on table SCHEMANAME1.CM_AUTHORIZATION_CUSTOMER_BACKUP. Oracle Goldengate marked following column as key columns on table SCHEMANAME1.CM_AUTHORIZATION_CUSTOMER_BACKUP: ID, USER_PROFILE_ID, CUSTOMER_ID, ALL_ACCOUNT_AUTH_PACKAGE_ID, ALL_CARD_AUTH_PACKAGE_ID, CREATED_BY, CREATE_TS No unique key is defined for table SCHEMANAME1.CM_AUTHORIZATION_CUSTOMER_BACKUP. ***************************************************************************************** ---Add supplemental logging for 2nd schema GGSCI (sourceSRV.izb.local as ggate@DBNAME) 9> add schematrandata SCHEMANAME2 2018-01-16 12:41:51 INFO OGG-01788 SCHEMATRANDATA has been added on schema "SCHEMANAME2". 2018-01-16 12:41:51 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "SCHEMANAME2". 2018-01-16 12:41:51 INFO OGG-10471 ***** Oracle Goldengate support information on table SCHEMANAME2.ADMIN_INSTRUCTION ***** Oracle Goldengate support native capture on table SCHEMANAME2.ADMIN_INSTRUCTION. Oracle Goldengate marked following column as key columns on table SCHEMANAME2.ADMIN_INSTRUCTION: ID. *****************************************************************************************
After the server added configuration of the schema into the configuration, we can check and start the jobs:
Golden Gate Config Check
GGSCI (sourceSRV.izb.local as ggate@DBNAME) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EBKDMP1 00:00:00 00:01:10 EXTRACT STOPPED EBKEXT1 00:00:00 00:03:33 GGSCI (sourceSRV.izb.local as ggate@DBNAME) 11> start EBKEXT1 Sending START request to MANAGER ... EXTRACT EBKEXT1 starting GGSCI (sourceSRV.izb.local as ggate@DBNAME) 12> start EBKDMP1 Sending START request to MANAGER ... EXTRACT EBKDMP1 starting GGSCI (sourceSRV.izb.local as ggate@DBNAME) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EBKDMP1 00:00:00 00:05:36 EXTRACT RUNNING EBKEXT1 00:07:52 00:00:08
Wait them for couple minutes, to assure that they will not end abnormally (ABEND)