Configuration of the source
- Configure Database
- Configure Golden Gate
  - Add the job to the configuration

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.

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)

Table of Contents