Oracle Goldengate: Quick Start Guide

Oracle Goldengate(GG) is one of the key products of Oracle’s Data Integration product portfolio.

This series of entries is intended to help you with the introduction and initial setup of Oracle Goldengate replication between source and target Oracle Databases 11.2.0.2 running on Oracle Linux 5.4 x86 architecture.

On this post we will go through the basic initial setup..

How to obtain installation media?

As usual from Oracle Technology Network

( http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html )

Or, from Oracle e-Delivery

( https://edelivery.oracle.com )

Select a Product Pack: Oracle Fusion Middleware

Platform: ( platform of your OS )

Example:

[oracle@sourcesrv ~]$ uname -a

Linux sourcesrv 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux

Select -> Linux x86

Click Oracle GoldenGate on Oracle v11.1.1.1.0 Media Pack for Linux x86

And select the version of Goldengate(GG) depending on the version of your source and target database.

Example:

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 20 02:24:26 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

SQL> select version from v$instance;

VERSION

-----------------

11.2.0.2.0

SQL> select PLATFORM_NAME from v$database;

PLATFORM_NAME

--------------------------------------------------------------------------------

Linux IA (32-bit)

 

 

 

 

 

 

 

 

 

 

Or, support.oracle.com with the latest updates and patches. ( if you have an Oracle Support Account ofcourse )

For production deployments, it is highly recommended.

Important Things to check before starting?

  • Ensure that time is synchronous between source and target db servers.

How to verify:

[root@sourcesrv ~]# date

Tue Dec 20 02:41:51 EET 2011

[root@sourcesrv ~]# ssh targetsrv date

root@targetsrv's password:

Tue Dec 20 02:41:55 EET 2011

  • Ensure that source database is in archivelog mode.

How to check:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

 

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

SQL>

  • Ensure that source database is in force logging mode.

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 20 20:32:43 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

SQL> COL FORCE_LOGGING FOR A40

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOGGING

----------------------------------------

NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOGGING

----------------------------------------

YES

 

 

 

 

 

 

 

 

 

 

 

· Check supplemental logging status in source database.

How to check and add:

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 20 09:14:42 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN

----------------------------------------------------------------------

NO

SQL> set timing on

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Elapsed: 00:00:00.08

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN

----------------------------------------------------------------------

YES

 

 

 

 

 

 

 

 

 

 

 

 

 

  • Ensure right session environment variables are set for the oracle user.

How to check:

[oracle@sourcesrv ~]$ env | grep ORA

ORACLE_SID=source

ORACLE_HOME=/oracle/product/11.2.0/db

[oracle@sourcesrv ~]$ env | grep NLS

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

  • Ensure there is enough disk space for the product itself and the trail files. Check for the network connectivity between source and target servers.

How to check:

[root@sourcesrv ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 17G 9.4G 6.3G 60% /

tmpfs 1.0G 228M 797M 23% /dev/shm

For testing purposes more than 5 GB disk space is enough. For production deployment roughly amount of peek redosize all day / 4 amount of disk space would be needed for trail file storing.

Network requirements

Check (http://docs.oracle.com/cd/E22355_01/doc.11111/e21406.pdf ) Goldengate Install and Setup Guide.

Configure the system to use TCP/IP services, including DNS.

Configure the network with the host names or IP addresses of all systems that will be hosting Oracle GoldenGate processes and to which Oracle GoldenGate will be connecting. Host names are easier to use.

Oracle GoldenGate requires the following unreserved and unrestricted TCP/IP ports:

One port for communication between the Manager process and other Oracle GoldenGate processes.

A range of ports for local Oracle GoldenGate communications: can be the default range starting at port 7840 or a customized range of up to 256 other ports.

Keep a record of the ports that you assigned to Oracle GoldenGate. You will specify them with parameters when configuring the Manager process.

Configure your firewalls to accept connections through the Oracle GoldenGate ports.

Installation

Overview of setup process:

1) Create required sub directories ( create subdirs just for once ) source & target

2) Add and start manager processes ( source & target )

3) Add extract process to source

4) Add extract trail file

5) Add pump process to source

6) Add remote trail file to pump

7) Add replicat process

Create a directory as GoldenGate home and copy the GG installation zip file to the server.

As root:

[root@sourcesrv ~]# mkdir /gg

[root@sourcesrv ~]# chown -R oracle:dba /gg

As oracle:

[oracle@sourcesrv gg]$ pwd

/gg

[oracle@sourcesrv gg]$ ls

V26188-01.zip

[oracle@sourcesrv gg]$ unzip V26188-01.zip

Archive: V26188-01.zip

inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar

inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf

inflating: README.txt

[oracle@sourcesrv gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

UserExitExamples/

UserExitExamples/ExitDemo_more_recs/

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX

Do the same for the target server:

[root@targetsrv ~]# mkdir /gg

[root@targetsrv ~]# chown -R oracle:dba /gg

[oracle@sourcesrv gg]$ scp V26188-01.zip targetsrv:/gg

oracle@targetsrv's password:

V26188-01.zip 100% 82MB 20.5MB/s 00:04

[oracle@targetsrv gg]$ unzip V26188-01.zip

Archive: V26188-01.zip

inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar

inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf

inflating: README.txt

[oracle@targetsrv gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

On source system:

[oracle@sourcesrv gg]$ ./ggsci

./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

Do not forget to set environment variable LD_LIBRARY_PATH !!!

[oracle@sourcesrv gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/gg

[oracle@sourcesrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (sourcesrv) 1>

Persist LD_LIBRARY_PATH to .bash_profile

[oracle@sourcesrv ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_HOME=/oracle/product/11.2.0/db

export ORACLE_SID=source

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/gg

Last step before the configurations and the unique command of installation:

CREATE SUBDIRS

[oracle@sourcesrv ~]$ cd /gg

[oracle@sourcesrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (sourcesrv) 1> CREATE SUBDIRS

Creating subdirectories under current directory /gg

Parameter files /gg/dirprm: created

Report files /gg/dirrpt: created

Checkpoint files /gg/dirchk: created

Process status files /gg/dirpcs: created

SQL script files /gg/dirsql: created

Database definitions files /gg/dirdef: created

Extract data files /gg/dirdat: created

Temporary files /gg/dirtmp: created

Veridata files /gg/dirver: created

Veridata Lock files /gg/dirver/lock: created

Veridata Out-Of-Sync files /gg/dirver/oos: created

Veridata Out-Of-Sync XML files /gg/dirver/oosxml: created

Veridata Parameter files /gg/dirver/params: created

Veridata Report files /gg/dirver/report: created

Veridata Status files /gg/dirver/status: created

Veridata Trace files /gg/dirver/trace: created

Stdout files /gg/dirout: created

[oracle@targetsrv ~]$ cd /gg

[oracle@targetsrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (targetsrv) 1> CREATE SUBDIRS

Creating subdirectories under current directory /gg

Parameter files /gg/dirprm: created

Report files /gg/dirrpt: created

Checkpoint files /gg/dirchk: created

Process status files /gg/dirpcs: created

SQL script files /gg/dirsql: created

Database definitions files /gg/dirdef: created

Extract data files /gg/dirdat: created

Temporary files /gg/dirtmp: created

Veridata files /gg/dirver: created

Veridata Lock files /gg/dirver/lock: created

Veridata Out-Of-Sync files /gg/dirver/oos: created

Veridata Out-Of-Sync XML files /gg/dirver/oosxml: created

Veridata Parameter files /gg/dirver/params: created

Veridata Report files /gg/dirver/report: created

Veridata Status files /gg/dirver/status: created

Veridata Trace files /gg/dirver/trace: created

Stdout files /gg/dirout: created

CREATE SOURCE AND TARGET GOLDENGATE USERS:

For both source and target systems:

SQL> CREATE USER GG IDENTIFIED BY oracle;

User created.

SQL> GRANT DBA TO GG;

Grant succeeded.

CONFIGURE AND START MANAGER PROCESS:

For both source and target systems:

[oracle@sourcesrv ~]$ cd /gg

[oracle@sourcesrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (sourcesrv) 1> edit params mgr

GGSCI (sourcesrv) 2> view params mgr

port 7809

GGSCI (sourcesrv) 3> start mgr

Manager started.

GGSCI (sourcesrv) 4> info mgr

Manager is running (IP port sourcesrv.7809).

[oracle@targetsrv ~]$ cd /gg

[oracle@targetsrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (targetsrv) 1> edit params mgr

GGSCI (targetsrv) 2> view params mgr

port 7809

GGSCI (targetsrv) 3> start mgr

Manager started.

GGSCI (targetsrv) 4> info mgr

Manager is running (IP port targetsrv.7809).

ADD SUPPLEMENTAL LOGGING FOR THE SOURCE OBJECTS:

[oracle@sourcesrv gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040

Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06

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

GGSCI (sourcesrv) 1> dblogin userid gg, password oracle

Successfully logged into database.

GGSCI (sourcesrv) 2> info trandata hr.*

Logging of supplemental redo log data is disabled for table HR.COUNTRIES.

Logging of supplemental redo log data is disabled for table HR.DEPARTMENTS.

Logging of supplemental redo log data is disabled for table HR.EMPLOYEES.

Logging of supplemental redo log data is disabled for table HR.JOBS.

Logging of supplemental redo log data is disabled for table HR.JOB_HISTORY.

Logging of supplemental redo log data is disabled for table HR.LOCATIONS.

Logging of supplemental redo log data is disabled for table HR.REGIONS.

GGSCI (sourcesrv) 3> add trandata hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

GGSCI (sourcesrv) 4> history

GGSCI Command History

1: dblogin userid gg, password oracle

2: info trandata hr.*

3: add trandata hr.*

4: history

GGSCI (sourcesrv) 5> info trandata hr.*

Logging of supplemental redo log data is enabled for table HR.COUNTRIES

Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS

Logging of supplemental redo log data is enabled for table HR.EMPLOYEES

Logging of supplemental redo log data is enabled for table HR.JOBS

Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY

Logging of supplemental redo log data is enabled for table HR.LOCATIONS

Logging of supplemental redo log data is enabled for table HR.REGIONS

GGSCI (sourcesrv) 6>

ADD EXTRACT PROCESS FOR CAPTURING THE SOURCE CHANGES:

GGSCI (sourcesrv) 8> add extract xhr, tranlog, begin now

2011-12-20 09:53:44 INFO OGG-01749 Successfully registered EXTRACT XHR to start managing log retention at SCN 832756.

EXTRACT added.

GGSCI (sourcesrv) 13> edit params xhr

GGSCI (sourcesrv) 14> view params xhr

EXTRACT xhr

USERID gg, PASSWORD oracle

EXTTRAIL ./dirdat/hr

TABLE hr.*;

GGSCI (sourcesrv) 15> ADD EXTTRAIL ./dirdat/hr, EXTRACT xhr

EXTTRAIL added.

GGSCI (sourcesrv) 16> ADD EXTRACT phr, EXTTRAILSOURCE ./dirdat/hr

EXTRACT added.

GGSCI (sourcesrv) 17> ADD RMTTRAIL ./dirdat/hr, EXTRACT phr

RMTTRAIL added.

GGSCI (sourcesrv) 18> EDIT PARAMS phr

GGSCI (sourcesrv) 19> VIEW PARAMS phr

EXTRACT phr

USERID gg, PASSWORD oracle

RMTHOST targetsrv, MGRPORT 7809

RMTTRAIL ./dirdat/hr

PASSTHRU

TABLE HR.*;

CONFIGURE REPLICAT RELATED COMPONENTS ON TARGET SYSTEM:

GGSCI (targetsrv) 2> dblogin userid gg, password oracle

Successfully logged into database.

GGSCI (targetsrv) 3> add checkpointtable

ERROR: Missing checkpoint table specification.

GGSCI (targetsrv) 3> edit params ./GLOBALS

GGSCI (targetsrv) 3> view params ./GLOBALS

checkpointtable gg.ggschkpt

GGSCI (targetsrv) 4> ADD REPLICAT rhr, EXTTRAIL ./dirdat/hr

REPLICAT added.

GGSCI (targetsrv) 5> edit params rhr

GGSCI (targetsrv) 6> view params rhr

REPLICAT rhr

ASSUMETARGETDEFS

USERID gg, PASSWORD oracle

MAP HR.*, TARGET HR.*;

[oracle@sourcesrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 20 10:15:28 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

SQL> select salary from hr.employees where employee_id=200;

SALARY

----------

4400

SQL> update hr.employees set salary=5000 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

SQL> exit

GGSCI (sourcesrv) 2> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED PHR 00:00:00 00:19:24

EXTRACT STOPPED XHR 00:00:00 00:22:57

GGSCI (targetsrv) 2> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED RHR 00:00:00 00:08:25

GGSCI (sourcesrv) 3> start *

Sending START request to MANAGER ...

EXTRACT PHR starting

Sending START request to MANAGER ...

EXTRACT XHR starting

GGSCI (sourcesrv) 4> stats xhr

Sending STATS request to EXTRACT XHR ...

Start of Statistics at 2011-12-20 10:18:25.

Output to ./dirdat/hr:

Extracting from HR.EMPLOYEES to HR.EMPLOYEES:

*** Total statistics since 2011-12-20 10:17:37 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2011-12-20 10:17:37 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2011-12-20 10:17:37 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2011-12-20 10:17:37 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (targetsrv) 4> start *

Sending START request to MANAGER ...

REPLICAT RHR starting

GGSCI (targetsrv) 5> stats rhr

Sending STATS request to REPLICAT RHR ...

Start of Statistics at 2011-12-20 10:19:46.

Replicating from HR.EMPLOYEES to HR.EMPLOYEES:

*** Total statistics since 2011-12-20 10:19:14 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2011-12-20 10:19:14 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2011-12-20 10:19:14 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2011-12-20 10:19:14 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (targetsrv) 6>

[oracle@targetsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 20 10:20:34 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

SQL> select salary from hr.employees where employee_id=200;

SALARY

----------

5000

Comments:

Excellent note Sezgi :)! Really very useful!

Aman....

Posted by Aman.... on December 22, 2011 at 10:51 PM CET #

Sezci,

Will it be possible for you to compile a list of useful(and published) MOS notes related to Golden Gate and post a blog post of it? I am sure that's going to be a really useful to all.

Aman....

Posted by Aman.... on December 26, 2011 at 11:23 PM CET #

Excellent content for anyone wanting to try out OGG.

Thanks.

Posted by Rakesh Tripathi on March 22, 2012 at 08:06 PM CET #

Nice tutorial!!
Thanks for this Sezgi.
Btw, is it possible to setup GoldenGate (both src and trgt) in Oracle VM VirtualBox Manager?

Posted by guest on May 16, 2012 at 10:12 AM CEST #

Great blog

Posted by guest on August 06, 2012 at 04:36 PM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle ECEMEA Partner Hubs Migration Center Team

We share our skills to maximize your revenue!
Our dedicated team of consultants can rapidly and successfully assist you to adopt and implement the latest of Oracle Technology in your solutions.

Stay Connected
partner.imc
@
beehiveonline.oracle-DOT-com
Google+

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today