Installation and DML Replication Configuration of Oracle GoldenGate 11g

In this Post, I have described the steps to Install Oracle GoldenGate 11g and also to Configure DML Replication using Oracle GoldenGate 11g.

PRIMARY SERVER	OGG1 – GGDB1
SECONDARY SERVER	OGG2 – GGDB2
ORACLE VERSION	11.2.0.2.0
ORACLE GOLDENGATE VERSION	11.2.1.0.1
OS VERSION	IBM AIX6.1

PRE-STEPS

Before the Configuration of Oracle GoldenGate, create a user Oracle GoldenGate admin user. As we have SYS user in Oracle Database, we can have a GGADMIN user in Oracle GoldenGate to manage it.

create tablespace tbsggate datafile ‘+DATA1/’ size 2G;

create user ggadmin identified by oracle default tablespace tbsggate;

grant connect,resource to ggadmin;

grant dba to ggadmin;

grant execute on utl_file to ggadmin;

STEP 1 – Begin —> PRIMARY

Created a directory ggate and moved the Oracle GoldenGate software in to it.

OGG1:/vol1/ggate /> ls –lart
drwxr-xr-x 2 root system 256 Mar 29 15:46 lost+found
drwxr-xr-x 4 root system 256 Mar 30 11:27 ..
-rw——- 1 oracle oradba 49963147 May 29 22:25 V32437-01.zip
drwxr-xr-x 6 oracle dba 4096 Jun 08 10:33 .

OGG1:/vol1/ggate />unzip V32437-01.zip

GOLDENGATE PROMPT

GGSCI – GoldenGate Software Command Interface

To enter in to the GoldenGate Prompt.,

OGG1:/vol1/ggate />./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (OGG1) 1>

LOGIN AS GGADMINN ID IN GOLDENGATE

Once you enter in to GoldenGate prompt, login using ggadmin userid

GGSCI (OGG1) 1> dblogin userid ggadmin, password oracle
Successfully logged into database.

CREATING SUBDIRECTORIES

Create sub directories for oracle goldengate.

Executing the below command creates directories for Oracle GoldenGate to store and process parameters, reports, checkpoints, scripts etc.

GGSCI (OGG1) 1> CREATE SUBDIRS
Creating subdirectories under current directory /vol1/ggate

Parameter files /vol1/ggate/dirprm: created
Report files /vol1/ggate/dirrpt: created
Checkpoint files /vol1/ggate/dirchk: created
Process status files /vol1/ggate/dirpcs: created
SQL script files /vol1/ggate/dirsql: created
Database definitions files /vol1/ggate/dirdef: created
Extract data files /vol1/ggate/dirdat: created
Temporary files /vol1/ggate/dirtmp: created
Veridata files /vol1/ggate/dirver: created
Veridata Lock files /vol1/ggate/dirver/lock: created
Veridata Out-Of-Sync files /vol1/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files /vol1/ggate/dirver/oosxml: created
Veridata Parameter files /vol1/ggate/dirver/params: created
Veridata Report files /vol1/ggate/dirver/report: created
Veridata Status files /vol1/ggate/dirver/status: created
Veridata Trace files /vol1/ggate/dirver/trace: created
Stdout files /vol1/ggate/dirout: created

MANAGER PROCESS:

Manager Process manages all the sub processes of Oracle GoldenGate. This process will be there in both the primary and secondary server.

Each and every process in Oracle GoldenGate runs in its own port. Only for the Manager Process we will assign a port explicitly.

To assign a port to the Manager Process issue the below command.,

GGSCI (OGG1) 2> EDIT PARAMS MGR

PORT 7878

(You can specify any port number which is not used by other service)

TO START THE MANAGER

Once the Port is assigned to the MANAGER Process, we can start the manager process.,

GGSCI (OGG1) 3> START MGR

Manager started.

TO CHECK MANAGER STARTED

INFO ALL – is a command to check the status of the Oracle GoldenGate Processes.

GGSCI (OGG1) 4> INFO ALL

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

ENABLE SUPPLEMENTAL LOGIN TO DATABASE

We should enable supplemental log to the database. Each and Every table in the database should have a primary key.

SQL> conn sys/oracle@GGDB1 as sysdba
Connected.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit PRIMARY
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OGG1:/vol1/ggate />./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (OGG1) 6> DBLOGIN USERID ggadmin, PASSWORD oracle
Successfully logged into database.

ENABLE SUPPLEMENTAL LOGIN TO ALL TABLES

In Oracle GoldenGate level enable the supplemental login to all the tables or the schema.

In the earlier Oracle GoldenGate, we had option to enable supplemental login at table level. But now in the latest version, we can enable supplemental login at schema level.

To enable the supplemental login please issue the below command.,

GGSCI (OGG1) 7> ADD TRANDATA scott.*

This QUERY WILL enable supplemental log for all table (one by one)

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

STEP 1 END ––> PRIMARY

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

STEP 2 Begin —> SECONDARY

UNZIP THE SOFTWARE

GGDB2:/vol1/ggate /> ls –lart
drwxr-xr-x 2 root system 256 Mar 29 15:46 lost+found
drwxr-xr-x 4 root system 256 Mar 30 11:27 ..
-rw——- 1 oracle oradba 49963147 May 29 22:25 V32437-01.zip
drwxr-xr-x 6 oracle dba 4096 Jun 08 10:33 .

GGDB2:/vol1/ggate />unzip V32437-01.zip

GOLDENGATE PROMPT LOGIN

GGDB2:/vol1/ggate />./ggsci

CREATION OF SUBDIRECTORIES

GGSCI (OGG2) 1> CREATE SUBDIRS

Creating subdirectories under current directory /vol1/ggate

EDIT MGR AND GIVE PORT NUMBER

GGSCI (GGDB2) 2> EDIT PARAMS MGR

PORT 7879

TO START MANAGER

GGSCI (GGDB2) 3> START MGR

Manager started.

TO CHECK MANAGER IS RUNNING

GGSCI (GGDB2) 4> INFO ALL

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING

LOGIN TO DATATBASE THROUGH GOLDENGATE

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

STEP 2 ENDED – SECONDARY

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

STEP 3 BEGIN —> PRIMARY

CREATING EXTRACT FOR LOCAL SERVER

GGSCI (OGG1) 9> EDIT PARAMS EXT1
–EXTRACT GROUPNAME
EXTRACT EXT1
–SOURCE DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–SOURCE TRAIL FILE PATH AND PREFIX
EXTTRAIL /vol1/ggate/dirdat/et
–ASM USER AND PASSWORD
TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD oracle
–SOURCE TABLE NAMES THAT ARE TO BE REPLICATED
TABLE scott.*;

Note : Specify <local_path> for EXTTRAIL for EXT1

ADDING EXTRACT

Once the Parameters are set to the EXTRACT Process EXT1, we need to add this Extract to the GoldenGate.

GGSCI (OGG1) 10> ADD EXTRACT EXT1, TRANLOG, BEGIN NOW
EXTRACT added.

TRANLOG – Tells the EXTRACT Process to capture the changes directly from the Database Online Redo Logfiles.

BEGIN NOW – To start the process Immediately

ADDING EXTRAIL

Trail files are created mapped / created to the EXTRACT Process EXT1. So the changed data/committed data are captured by EXTRACT Process EXT1 and written to the Trail Files. Each Trail Files are of size 5M.

To Add the trail file to the EXTRACT Process EXT1, Please issue the below command.,

GGSCI (OGG1) 11> ADD EXTTRAIL /vol1/ggate/dirdat/et, EXTRACT EXT1, MEGABYTES 5
EXTTRAIL added.

CREATING DATAPUMP FOR REMOTE SERVER

GGSCI (OGG1) 12> EDIT PARAMS DPMP1

–DATAPUMP PROCESS NAME
EXTRACT DPMP1
–PROCESSING OPTION (PASSTHRU OR NOPASSTHRU)
PASSTHRU
–TARGET HOSTNAME,MANAGER PORT NUMBER
RMTHOST GGDB2, MGRPORT 7879
–TARGET TRAIL FILE PATH AND PREFIX
RMTTRAIL /vol1/ggate/dirdat/et
–SOURCE TABL NAME
TABLE scott.*;

Note : Specify <remote_path> for RMTTRAIL for DPMP1

PASSTHRU – This Parameter ensures the data is propagated to the target host without any manipulation.

ADDING EXTRACT

GGSCI (OGG1) 13> ADD EXTRACT DPMP1, EXTTRAILSOURCE /vol1/ggate/dirdat/et
(i.e local path)

EXTRACT added.

ADDING RMTTRAIL

GGSCI (OGG1) 14> ADD RMTTRAIL /vol1/ggate/dirdat/et, EXTRACT DPMP1, MEGABYTES 5
(i.e remote path)

RMTTRAIL added.

BEFORE STARTING THE EXTRACT PROCESS CREATE THE REPLICAT PROCESSES IN SECONDARY.. (FOLLOW STEP 4)

STARTING THE EXTRACT

GGSCI (OGG1) 15> START EXTRACT EXT1
Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (OGG1) 16> START EXTRACT DPMP1
Sending START request to MANAGER …
EXTRACT DPMP1 starting

CHECKING ITS IN RUNNING

GGSCI (OGG1) 17> INFO ALL

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:02
EXTRACT RUNNING DPMP1 00:00:00 00:00:00

——————————————————
STEP 3 END ––> PRIMARY
——————————————————

STEP 4 BEGIN —> SECONDARY

CHECKPOINTTABLE

As we have controlfile in Oracle Database, Similarly we have CHECKPOINTTABLE table in Oracle GoldenGate. Each and every changes are written to this table.

In our configuration, we are creating a global checkpointtable. So to specify globally, Oracle GoldenGate has a file called GLOBALS. We have to edit this file.

GGSCI (GGDB2) 3> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggadmin.ggschkpt

GGSCI (GGDB2) 4> exit

Note: Checkpointtables can also be created at Process level.

Once you specify checkpoint table in ./GLOBALS You have to exit the current GGSCI prompt and then login to New GGSCI prompt to ADD CHECKPOINTTABLE

GGSCI (GGDB2) 1> ADD CHECKPOINTTABLE
ERROR: Not logged into database, use DBLOGIN.

IF THIS ERROR COMES YOU HAVE TO LOGIN AS FOLLOWS

GGSCI (GGDB2) 2> DBLOGIN USERID ggadmin, PASSWORD oracle
Successfully logged into database.

GGSCI (GGDB2) 3> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ggadmin.ggschkpt)…
Successfully created checkpoint table ggadmin.GGSCHKPT.

CREATING REPLICAT

GGSCI (GGDB2) 4> EDIT PARAMS REP1
–REPLICAT GROPUP NAME
REPLICAT REP1
–TARGET DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–REQUIRED FOR INITIAL LOAD HANDLES DUPLICATION
HANDLECOLLISIONS
–STRUCTURES OF PRIMARY AND SECONDARY ARE SAME
ASSUMETARGETDEFS
–THE DISCARDED DATA FILE
DISCARDFILE /vol1/ggate/dirrpt/REP1.DSC, PURGE
–MAPPING SOURCE AND TARGET TABLES
MAP scott.*, TARGET scott.*;

DISCARDFILE – Disacards data records that suffer an error during apply. It contains records that cannot be processed.

HANDLECOLLISSIONS – Directs automatic resolution of duplicate and missing record errors when applying data on the target database. This parameter is generally used for the INITIAL LOAD.

ADDING REPLICAT

Similar to EXTRACT Process, Trail files are created/added to REPLICAT Process also.

GGSCI (GGDB2) 5> ADD REPLICAT REP1,EXTTRAIL /vol1/ggate/dirdat/et
REPLICAT added.

Now You can Start the Extract Process(EXT1 and DPMP1) in PRIMARY and followed by Replicat in SECONDARY

START REPLICAT

GGSCI (GGDB2) 6> START REPLICAT REP1
Sending START request to MANAGER …

REPLICAT REP1 starting

CHECK THE STATUS

GGSCI (GGDB2) 7> INFO ALL

Program Status Group Lag Time Since ChkptMANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:09

——————————————————
STEP 4 ENDED ––> SECONDARY
——————————————————

IF YOU ARE FACING ANY ERROR PLEASE CHECK WITH LOGFILE NAME ggserr.log IN THE LOCATION IN WHICH YOU HAVE INSTALLED THE SOFTWARE

Hope this is a Clear Document for the Beginners to Install Oracle GoldenGate and Configure DML Replication in it.

In my next post we will see about the Configuration of DDL Replications using GoldenGate.

One Comment

Rajat Sharma says:
May 8, 2016 at 7:16 pm
Hello Sir,
i was reading about supplemental logging for golden gate
“Supplemental logging generates additional undo which is stored in the redo log. The additional information allows rows to be located when the ROWID is unavailable. Undo is used because we want to locate the row in the target database in order to apply the change. The change may update columns in the target database so we cannot use any values in the redo. This is required for logical standby databases for example.”
could you please explain how this undo is being used to identify the rows for which rowid is unavailbale..
Thanks a lot for sharing such good posts on goldengate..

Installation and DML Replication Configuration of Oracle GoldenGate 11g

Veera

Previous PostDDL Replication Configuration of Oracle GoldenGate 11g

Next PostSHOWSYNTAX : Oracle GoldenGate Tool To Debug A Replicat

One Comment

Leave a Reply Cancel Reply

Polls

Calendar

Visitor’s Count

Subscribe For Latest Updates

Installation and DML Replication Configuration of Oracle GoldenGate 11g

Veera

Previous PostDDL Replication Configuration of Oracle GoldenGate 11g

Next PostSHOWSYNTAX : Oracle GoldenGate Tool To Debug A Replicat

Related Posts

Integration of Oracle GoldenGate with Oracle Database 21c and OGG Enhancements

ADD or ALTER OGG Process with DESCRIPTION

Which PDBs are registered with Integrated Extract?

One Comment

Leave a Reply Cancel Reply

Polls

Calendar

Visitor’s Count

Subscribe For Latest Updates