Install Oracle Goldengate on LINUX 11gr2

Here I’m going to install oracle Goldengate 11.2.1.0.3  on my oracle 11.2.0.2 database, Linux 64 bit

If it is a RAC, then install in a common location
Download Goldengate Software and move the ZIP file to Linux server

Copy software(ZIP file) to some directory in the database and unzip the file

$ Mkdir ggate

$ cd ggate

$ unzip V34339-01.zip

Archive:  V34339-01.zip

inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar

inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc

inflating: Oracle GoldenGate_11.2.1.0.3_README.txt

inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf

$  tar -xvof fbo_ggs_Linux_x64_ora11g_64bit.tar

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Create Sub directories using “CREATE SUBDIRS”, this will create all required sub directories for oracle goldengate.

GGSCI (oracledev.domain.com) 1> CREATE SUBDIRS

Creating subdirectories under current directory /opt/oracle/GG

Parameter files                /opt/oracle/GG/dirprm: already exists

Report files                   /opt/oracle/GG/dirrpt: created

Checkpoint files               /opt/oracle/GG/dirchk: created

Process status files           /opt/oracle/GG/dirpcs: created

SQL script files               /opt/oracle/GG/dirsql: created

Database definitions files     /opt/oracle/GG/dirdef: created

Extract data files             /opt/oracle/GG/dirdat: created

Temporary files                /opt/oracle/GG/dirtmp: created

Stdout files                   /opt/oracle/GG/dirout: created

GGSCI (oracledev.domain.com) 2>

Then we need to create a database user and tablespace on both Source and TARGET servers which will be used by the GoldenGate Manager, Extract and Replicat processes.

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 14 20:59:57 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace ggs_data datafile ‘/opt/oracle/ggs_data01.dbf’ size 100m autoextend on next 10m maxsize unlimited;

Tablespace created.

SQL> create user gguser identified by gguser default tablespace ggs_data temporary tablespace temp;

User created.

SOURCE grants:

SQL>  grant create session to gguser;

grant connect,resource to gguser;

GRANT ALTER ANY TABLE TO GGUSER;

GRANT CREATE ANY TABLE TO GGUSER;

GRANT CREATE TABLE TO GGUSER;

GRANT DELETE ANY TABLE TO GGUSER;

GRANT DROP ANY TABLE TO GGUSER;

GRANT FLASHBACK ANY TABLE TO GGUSER;

GRANT INSERT ANY TABLE TO GGUSER;

GRANT SELECT ANY DICTIONARY TO GGUSER;

GRANT SELECT ANY TABLE TO GGUSER;

GRANT QUOTA UNLIMITED ON GGS_DATA TO GGUSER;

GRANT UPDATE ANY TABLE TO GGUSER;
TARGET grants:

GRANT CREATE SESSION to gguser;

GRANT ALTER SESSION to gguser;

GRANT ALTER SYSTEM to gguser;

GRANT RESOURCE to gguser;

GRANT CONNECT to gguser;

GRANT SELECT ANY DICTIONARY to gguser;

GRANT SELECT ANY TABLE to gguser;

GRANT INSERT,UPDATE, DELETE ON TARGET_SCHEMA.* to gguser;

GRANT CREATE TABLE to gguser;

NOTE:  Please refer below Oracle Goldengate installation Doc for the GGUSER grants on Source and Target side
http://docs.oracle.com/cd/E35209_01/doc.1121/e35957.pdf

Uninstall Oracle GOLDENGATE on LINUX

  1. Log on to the database server (as oracle) where the GoldenGate software is installed.

cd /home/oracle/ggs

  1. Start GGSCI:

./ggsci

  1. Stop all GoldenGate processes:

GGSCI (dbserver1) 1> stop EXTRACT *

or

GGSCI (dbserver1) 1> stop REPLICAT *

Then:

GGSCI (dbserver1) 2> stop MGR

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER …

Request processed.

Manager stopped.

GGSCI (dbserver1) 3> exit

  1. Change directory to the installation directory:

cd /home/oracle

  1. Remove the GoldenGate files:

rm -rf ggs

  1. Logon to the Oracle database as SYSDBA and drop the GoldenGate Admin user. Include the CASCADE keyword:

oracle@host.com:/opt/oracle INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 1 19:19:23 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user gguser cascade;

drop user gguser cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot

DROP object used in Oracle GoldenGate replication while trigger is enabled.

Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical

Support if you wish to do so., error stack: ORA-06512: at line 231

ORA-06512: at line 1030

SQL> select * from dba_triggers db where db.owner=’GGUSER’;

no rows selected

SQL>  SELECT a.obj#, a.sys_evts, b.nameFROM trigger$ a,obj$ b

WHERE a.sys_evts> 0AND a.obj#=b.obj#AND baseobject = 0;

OBJ#   SYS_EVTS NAME

———- ———- ——————————

204316       8256 EXPFIL_ALTEREXPTAB_MAINT

204314        128 EXPFIL_DROPUSR_MAINT

204464       4096 RLMGR_TRUNCATE_MAINT

357922         64 CDC_ALTER_CTABLE_BEFORE

357923         32 CDC_CREATE_CTABLE_AFTER

357924         32 CDC_CREATE_CTABLE_BEFORE

357925        128 CDC_DROP_CTABLE_BEFORE

10520       8416 NO_VM_DDL

10521        128 NO_VM_DROP_A

204315         96 EXPFIL_RESTRICT_TYPEEVOLVE

204313        128 EXPFIL_DROPOBJ_MAINT

OBJ#   SYS_EVTS NAME

———- ———- ——————————

805933     524256 GGS_DDL_TRIGGER_BEFORE

203646       4224 XDB_PI_TRIG

7743        128 AW_DROP_TRG

341038       4096 AW_TRUNC_TRG

341040       8192 AW_REN_TRG

16 rows selected.

SQL> drop trigger ggs_ddl_trigger_before;

Trigger dropped.

SQL> drop user gguser cascade;

User dropped.

STEP BY STEP INSTALLATION OF ORACLE GOLDEN GATE ONE WAY REPLICATION FOR NON RAC DATABASE( ACTIVE – PASSIVE)

STEP 0   ————— Hardware and O/S configuration

STEP 1   ————— Database Pre requisites

STEP 2   ————— GG Software Installation

STEP 3   ————— Preparing source database for replication

STEP 3a ————— GG DDL Support Replication

STEP 4   ————— Create Manager process @ Source

STEP 5   ————— Create Extract process @ Source

STEP 6   ————— Create Checkpoint table @ Target

STEP 7   ————— Create Replicate process @ Target

STEP 8   ————— Initial Data load (EXP/IMP)

STEP 9   ————— Start Extract

STEP 10 ————— Start Replicat

STEP 11————— TEST CASES

STEP TASK
STEP 0:-

Hardware and O/S configuration

Now, let’s take a look at the Oracle database details which we will be using for our GOLDEN GATE configuration.( I am using VMWARE nodes)

SOURCE Database
Oracle Release: Oracle10 Release 2 – (10.2.0.5.0)
Machine Name: Source.gg.com
Operating System: Red Hat Linux 5
Oracle SID: DBSOURCE
Replication Schema SCOTT
TARGET Database
Oracle Release: Oracle10 Release 2 – (10.2.0.5.0)
Machine Name: Target.gg.com
Operating System: Red Hat Linux 5
Oracle SID: DBTARGET
Replication Schema SCOTT

Download the oracle Golden Gate software from below URL  and copy to both source and target servers

we need to download the required software from the Oracle E-Delivery web site

Step 1:–

Database Pre requisites

Need to be carried out at both source and target 

SQL> create tablespace ggs_data

datafile ‘/oradata/<DBNAME>ggs_data01.dbf’ size 200m;

SQL> create user ggs_owner identified by ggs_owner

default tablespace ggs_data    temporary tablespace temp;

grant connect, resource to ggs_owner;

grant select any dictionary, select any table to ggs_owner;

grant create table to ggs_owner;

grant flashback any table to ggs_owner;

grant execute on dbms_flashback to ggs_owner;

grant execute on utl_file to ggs_owner;

grant create any table to ggs_owner;

grant insert any table to ggs_owner;

grant update any table to ggs_owner;

grant delete any table to ggs_owner;

grant drop any table to ggs_owner;

    INIT_ORA change

  1. UNDO_MANAGEMENT=AUTO
  2. UNDO_RETENTION=86400
Step 2:–

GG Software Installation

Need to be carried out at both source and targetNote:

1 binary for SOURCE

1 binary for TARGET REPLICAT

SOURCE

n  mkdir  /oradata/gg/

TARGET

n  mkdir /oradata/gg/

set environment variables on both SOURCE and TARGET before starting the installation on all the three locations

 $mkdir /oradata/gg

$unzip V18157-01.zip

Archive:  V18157-01.zip

inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg

$  . /ggsci

    — YOU WILL GET GG COMMAND PROMPT

                   GGSCI (hostname) 1> CREATE SUBDIRS

    — Issue the following command to exit GGSCI.    

                  GGSCI (hostname) 1> EXIT

STEP 3:-

Preparing source database for replication

Need to be carried out at  source Switch the database to archivelog mode:shutdown immediate startup mount

alter database archivelog;

alter database open;

 Enable minimal supplemental logging:

 SQL> alter database add supplemental log data;

Prepare the database to support ddl replication.

 SQL> alter system set recyclebin=off scope=spfile;

n  bounce the database

Step3a :-

Run scripts for creating all necessary objects for support DDL replication

Need to be carried out at  source

Note – run the scripts as SYSDBA

SQL> @/oradata/gg/marker_setup.sql 

Marker setup 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: GGS_OWNER

Marker setup table script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

MARKER TABLE

——————————-

OK

MARKER SEQUENCE

——————————-

OK

Script complete.

 SQL> @/oradata/gg/ddl_setup.sql 

 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 GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name: GGS_OWNER

You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP

Working, please wait …

Spooling to file ddl_setup_spool.txt

Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait …

RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script…

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

CLEAR_TRACE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

DDL HISTORY TABLE

———————————–

OK

DDL HISTORY TABLE(1)

———————————–

OK

DDL DUMP TABLES

———————————–

OK

DDL DUMP COLUMNS

———————————–

OK

DDL DUMP LOG GROUPS

———————————–

OK

DDL DUMP PARTITIONS

———————————–

OK

DDL DUMP PRIMARY KEYS

———————————–

OK

DDL SEQUENCE

———————————–

OK

GGS_TEMP_COLS

———————————–

OK

GGS_TEMP_UK

———————————–

OK

DDL TRIGGER CODE STATUS:

Line/pos   Error

———- —————————————————————–

No errors  No errors

DDL TRIGGER INSTALL STATUS

———————————–

OK

DDL TRIGGER RUNNING STATUS

———————————–

ENABLED

STAYMETADATA IN TRIGGER

———————————–

OFF

DDL TRIGGER SQL TRACING

———————————–

0

DDL TRIGGER TRACE LEVEL

———————————–

0

LOCATION OF DDL TRACE FILE

——————————————————————————–

/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION

——————————————————————————–

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

SQL> @/oradata/gg/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:GGS_OWNER

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 ggs_owner;

Grant succeeded.

SQL> @/oradata/gg/ddl_enable

Trigger altered.

SQL> @/oradata/gg/ddl_pin GGS_OWNER

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enable additional logging at the table level

 Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by Golden Gate for DDL support.

GGSCI (soruce hostname) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

 SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’RPD_PRC’ and object_type=’TABLE’;

GGSCI(source hostname) 6> add trandata <owner>.<tablename>

STEP 4:-

Create and start manager on the source and the destination.

 NEED TO BE CARRIED OUT AT SOURCE

[oracle@DBSOURCE gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

GGSCI (DBSOURCE) 44> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (DBSOURCE) 45> edit params mgr

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /oradata/gg/dirdat/ex, USECHECKPOINTS

GGSCI (DBSOURCE) 46> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (DBSOURCE) 47> dblogin USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.

GGSCI (DBSOURCE) 48> start manager

Manager started.

                      STEP 5 :-         Create the extract group               NEED TO BE CARRIED OUT AT SOURCE

GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin nowEXTRACT added.

GGSCI (DBSOURCE) 50> add exttrail /oradata/gg/dirdat/lt, extract ext1

EXTTRAIL added.

GGSCI (DBSOURCE) 51> edit params ext1

Add the following lines to the new parameter file for our extract:

–extract group–

extract ext1

–connection to database–

userid ggs_owner, password ggs_owner

–hostname and port for trail–

rmthost 172.168.10.108, mgrport 7810

–path and name for trail–

rmttrail /oradata/gg/dirdat/lt

–DDL support

DDL INCLUDE ALL

ddl include mapped objname SCOTT.*;

–DML

table SCOTT.*;

table SCOTT.EMP;

table SCOTT.DEPT;

table SCOTT.SALARY;

:wq

STEP 6:-

CREATE CHECKPOINT TABLE

NEED TO BE CARRIED OUT AT TARGET

[oracle@DBTARGET gg]$ ./ggsci add checkpoint table to the destination database

GGSCI (DBTARGET) 1> edit params ./GLOBAL

and put following lines to the global parameter file:

GGSCHEMA ggs_owner

CHECKPOINTTABLE ggs_owner. Checkpoint

GGSCI (DBTARGET) 2> dblogin userid ggs_owner password password

Successfully logged into database.

GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.checkpoint

Successfully created checkpoint table ggs_owner.CHECKPOINT.

STEP 7:-

CREATE REPLICAT

NEED TO BE CARRIED OUT AT TARGET

GGSCI (DBTARGET) 4>add replicat rep1, exttrail /oradata/gg/dirdat/lt,checkpointtable ggs_owner.checkpointGGSCI (DBTARGET) 4> EDIT PARAMS rep1

edit params rep1

–target database login –

userid ggs_owner, password ggs_owner

–file for dicarded transaction –

–discardfile /oradata/gg/discard/rep1_discard.txt, append, megabytes 10

–ddl support

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP

–Specify table mapping —

MAP SCOTT.*,       TARGET SCOTT.*;

MAP SCOTT.EMP,   TARGET SCOTT.EMP;

MAP SCOTT.DEPT,   TARGET SCOTT.DEPT;

MAP SCOTT.SALARY,   TARGET SCOTT.SALARY;

STEP 8:-

INITIAL DATALOAD

EXPORT: @ SOURCE

expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=scott Scp from SOURCE and TARGET

$scp –p schema_gg.dmp 172.168.10.108:/oradata

 IMPORT: @ TARGET

$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=SCOTT

STEP 9:-

START EXTRACT

NEED TO BE CARRIED OUT AT  SOURCE

GGSCI (DBSOURCE) 45>  start EXT1

Sending START request to MANAGER …

EXTRACT EXT2 starting

STEP 10:-

START REPLICAT

NEED TO BE CARRIED OUT AT TARGET 

GGSCI (DBTARGET) 10> start replicat rep1

Sending START request to MANAGER …

REPLICAT REP1 starting

STEP 11:-

TEST CASES

CASE 1: CREATE TABLE IN THE SOURCE DATABASE 

SOURCE:create table SCOTT.Employee(

ID                 VARCHAR2(4 BYTE) ,

First_Name         VARCHAR2(10 BYTE),

Last_Name          VARCHAR2(10 BYTE),

Start_Date         DATE,

End_Date           DATE,

Salary             Number(8,2),

City               VARCHAR2(10 BYTE),

Description        VARCHAR2(15 BYTE)

);

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

TARGET:

SELECT * FROM SCOTT.EMPLOYEE;

NO ROWS SELECTED

Case 2:CREATE TABLE AND INSERT DATA INTO IT

SOURCE:

Create table test_gg_1(

Id             number2(10),

Username varchar2(20)

);

Insert into test_gg_1 values(1, ‘USER1’);

Insert into test_gg_1 values(2,’USER2’);

Insert into test_gg_1 values(3,’USER3’);

Insert into test_gg_1 values(4,’USER4’);

Insert into test_gg_1 values(5,’USER5’);

COMMIT;

SELECT * FROM SCOTT.TEST_GG_1;

TARGET :

SELECT * FROM SCOTT.TEST_GG_1;

CASE3: UPDATE THE RECORD

SOURCE:

Update scott.test_gg_1 set username=’USERDBA’ where id=1;

Commit;

TARGET:

Select * from scott.test_gg_1 where id=1

n  you will find the updated record in the target database

 CASE4: DELETE THE RECORD

SOURCE :

Delete from scott.test_gg_1 where id=4;

Commit;

TARGET:

Select * from scott.test_gg_1 where id=4;

n  you will not find the deleted record in the target database

CASE 5: TRUNCAT THE RECORDS

SOURCE:

Truncate table scott.test_gg_1;

Commit;

Select * from scott.test_gg_1;

TARGET :

Select * from scott.test_gg_1;

n  since the records are truncated in the source you will not find any records in the target.

CASE 6: CREATE A TRIGGER

SOURCE:

create or replace trigger SCOTT.emp_biu

BEFORE INSERT OR UPDATE

of salary on employee for each row

declare

v_error VARCHAR2(2000);

begin

if :new.salary > 10000

then

v_error:=:old.first_name||’ cannot have that much!’;

raise_application_error(-20999,v_error);

end if;

end;

/

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

TARGET:

SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME=’EMP_BIU’;

CASE 7: create table with primary Key

If you create any table with constrains, Golden gate will not replicated it automatically. We need to perform below steps:

  1. stop the extract
  2. stop the replicat
  3. take the export of the table which is having constrains.
  4. Copy it to target server.
  5. Import the exported dump.
  6. Map the table in the replicat parameter file
  7. Enable HANDLECOLLUSION parameter in the replicat parameter.
  8. Start the replicat
  9. Start the extract.

Oracle Golden Gate

Oracle Golden Gate is a tool provided by oracle for transactional data replication among oracle databases and other RDBMS tools (SQL SERVER, DB2.Etc). Its modular architecture gives you the flexibility to easily decouple or combined to provide best solution to meet the business requirements.

Because of this flexibility in the architecture, Golden Gate supports numerous business requirements:

  • High Availability
  • Data Integration
  • Zero downtime upgrade and migration
  • Live reporting database

etc

Oracle Golden Gate Architecture

 Oracle Golden Gate Architecture is composed of the following Components:

  • Extract
  • Data pump
  • Replicat
  • Trails or extract
  • Checkpoints
  • Manager
  • Collector

Below is the architecture diagram of GG:

 

 Oracle Golden Gate server runs on both source and target server. Oracle Golden Gate is installed as an external component to the database and it wont uses database resource, in turn it won’t effect database performance. Where as Oracle streams which uses built in packages which are provided by oracle, which uses most of the database resources and there are chances of performance slow down in both source and target databases.

Let first have a look at architectural components of Oracle Golden Gate:

EXTRACT:

Extract runs on the source system and it is the extraction mechanism for oracle Golden Gate( capture the changes which happens at the source database).

The Extract process extracts the necessary data from the database transaction logs. For oracle database transaction logs are nothing both REDO log file data. Unlike streams which runs in the oracle database itself and needs the access to the database. Oracle Golden Gate does not needs access to the oracle database and also it will extract only the committed transaction from the online redo log file.

Whenever there is a long running transaction which generates more number of redo data will force to switch the redo log file and in turn more number of archive logs will be generated. In these cases the extract process need to read the archive log files to get the data.

Extract process captures all the changes that are made to objects that are configured for synchronization.  Multiple Extract processes can operate on different objects at the same time. For example once process could continuously extract transactional data changes and stream them to a decision support database. while another process performs batch extracts for periodic reporting or, two extract processes could extract and transmit in parallel to two replicat processes ( with two trails) to minimize target latency when the databases are large.

DATAPUMP

 Datapump is the secondary extract process within source oracle Golden Gate configuration. You can have the source oracle Golden Gate configured without Datapump process also, but in this case Extract process has to send the data to trail file at the target. If the Datapump is configured the primary extract process writes the data to the source trail file and Datapump will read this trail file and propagate the data over the network to target trail file. The Datapump adds the storage flexibility and it isolates the primary extract process from TCP/IP activity.

You can configure the primary extract process and Data pump extract to extract online or extract during batch processing.

REPLICAT

 Replicat process runs on the target system. Replicat reads extracted transactional data changes and DDL changes (IF CONFIGURED) that are specified in the Replicat configuration, and then it replicates them to the target database.

 TRAILS OR EXTRACTS

 To support the continuous extraction and replication of source database changes, Oracle Golden Gate stores the captured changes temporarily on the disk in a series of files call a TRAIL. A trail can exist on the source or target system and even it can be in a intermediate system, depending on how the configuration is done. On the local system it is known as an EXTRACT TRAIL and on the remote system it is known as REMOTE TRAIL.

The use of a trail also allows extraction and replication activities to occur independently of each other. Since these two ( source trail and target trail) are independent you have more choices for how data is delivered.

CHECKPOINT

 Checkpoints stores the current read and write positions of a process to disk for recovery purposes. These checkpoints ensure that data changes that are marked for synchronization are extracted by extract and replicated by replicat.

Checkpoint work with inter process acknowledgments to prevent messages from being lost in the network. OracleGolden Gatehas a proprietary guaranteed-message delivery technology.

Checkpoint information is maintained in checkpoint files within the dirchk sub-directory of the Oracle Golden Gate directory. Optionally, Replicat checkpoints can be maintained in a checkpoint table within the target database, apart from standard checkpoint file.

MANAGER

 The Manager process runs on both source and target systems and it is the heart or control process of Oracle Golden Gate. Manager must be up and running before you create EXTRAT or REPLICAT process. Manager performs Monitoring, restarting oracle golden gate process, report errors, report events, maintains trail files and logs etc.

COLLECTOR

 Collector is a process that runs in the background on the target system. Collector receives extracted database changes that are sent across the TCP/IP network and it writes them to a trail or extract file.

For more refer:
Master Note – Oracle GoldenGate (Doc ID 1298817.1)