The first post in this series outlined on how to install and patch Oracle GoldenGate to the latest release:

In this post I’ll look in how to prepare the databases and how to setup GoldenGate for a first sample replication between two Oracle databases.

My test setup is as follows:

Hostname Database Type
oelgg1 DB1 Source
oelgg2 DB2 Target

The idea is that GoldenGate replicates a table in the HR schema in oelgg1/DB1 to oelgg2/DB2. As, in case of replication, you do not want to loose any transaction at least the source database needs to be set in force logging mode. Additionally then database needs to be in archive log mode so that GoldenGate will be able to read the archived log files in case replication fails for some time.

alter system set enable_goldengate_replication=TRUE scope=both;
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
alter system switch logfile;
alter database force logging;
alter database add supplemental log data;
archive log list;

The force logging mode captures transactions even if nologging is specified for some operations by the end users or applications. The supplemantal log data is necessary so that GoldenGate is able to extract committed transactions out of the redo logs.

The next step is to create a GoldenGate admin user both databases:

create user ggadmin identified by "manager" default tablespace users temporary tablespace temp;
grant create session, dba to ggadmin;
exec dbms_goldengate_auth.grant_admin_privilege ('GGADMIN');

Note that the dba role granted to ggadmin is just a convenient way for getting all required privileges. For a detailed list of required privileges check the documentation.

As GoldenGate uses flashback query to create before images of some data types we need to enable this:

grant flashback any table to ggadmin;
alter system set undo_retention=86400 scope=both;

Before we proceed further you should test if you can connect from the source database to the target database and vice versa:

$ sqlplus ggadmin/manager@DB1
$ sqlplus ggadmin/manager@DB2

Now is a good time to create a wallet and store the credentials for the ggamin user. This is done directly with the GoldenGate command line interpreter ggsci (do this for the source and the target database):

$ /u01/app/oracle/product/12.1.0/gg_12121/ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 21381983 OGGCORE_12.
Linux, x64, 64bit (optimized), Oracle 12c on Jul 29 2015 04:52:08
Operating system character set identified as UTF-8.

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

GGSCI ( 1> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI ( 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI ( 4> alter credentialstore add user ggadmin@DB1 alias DB1 domain admin

Credential store in ./dircrd/ altered.

GGSCI ( 5> info credentialstore domain admin

Reading from ./dircrd/:

Domain: admin

  Alias: ggadmin_source
  Userid: ggadmin

In my test setup I have installed the Oracle sample schemas and will use the HR schema for replication. Now is the time to add supplemental log data to the tables we want to replicate. This is achieved by using the GoldenGate trandata command.

GGSCI ( 2> dblogin userid ggadmin@DB1
Successfully logged into database.

GGSCI ( as ggadmin@DB1) 3> add trandata hr.employees

Logging of supplemental redo data enabled for table HR.EMPLOYEES.
TRANDATA for scheduling columns has been added on table 'HR.EMPLOYEES'.

Before we do any additional configuration make sure the GoldenGate manager is started on both hosts:

GGSCI ( as ggadmin@DB1) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           

GGSCI ( as ggadmin@DB1) 5> start manager
Manager started.

GGSCI ( as ggadmin@DB1) 6> info manager

Manager is running (IP port, Process ID 17810).

GGSCI ( as ggadmin@DB1) 7> 

We can start the manager because oui created a manager configuration file when we installed GoldenGate. The configuration file is located in the dirprm sub-directory of the GoldenGate installation:

oracle@oelgg1:/u01/app/oracle/product/12.1.0/gg_12121/ [DB1] ls -la dirprm
total 16
drwxr-xr-x.  2 oracle oinstall   37 Aug 29 14:03 .
drwxr-xr-x. 30 oracle oinstall 4096 Sep  1 08:31 ..
-rwxr-x---.  1 oracle oinstall  103 Aug  7  2014 jagent.prm
-rw-r--r--.  1 oracle oinstall    9 Aug 29 14:03 mgr.prm

Using the ggsci interpreter this file can be edited with:

GGSCI ( 11> edit params mgr

I’ll add the following line to the manager configuration and restart the manager afterwards:


This tells GoldenGate to purge trail files once they have been processed and saves disk space. Trail files can be become huge.

Now that the managers are configured we can continue with configuring the capture/extract process:

GGSCI ( 2> edit params extract1

The following parameters are added to the extract configuration file:

EXTRACT extract1
EXTTRAIL ./dirdat/aa
TABLE hr.employees;

Having the parameter file ready we can now create the capture process:

GGSCI ( 3> add extract extract1, integrated tranlog, begin now
EXTRACT added.
GGSCI ( 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXTRACT1    00:00:00      00:00:45    

Note that the extract1 is still stopped. Lets create the local trail for the extract:

GGSCI ( 6> add exttrail ./dirdat/aa, extract extract1

For the integrated capture mode to work we now must register the capture process with the database:

GGSCI ( 1> dblogin useridalias DB1 domain admin
Successfully logged into database.

GGSCI ( as ggadmin@DB1) 2> register extract extract1 database
Extract EXTRACT1 successfully registered with database at SCN 1706387.

Is it recommended to use the GoldenGate data pump process to isolate the primary extract process from network latency. So, lets configure this:

GGSCI ( as ggadmin@DB1) 5> edit params datapmp1

The following parameters are added to the data pump configuration file:

EXTRACT datapmp1
USERIDALIAS DB1 domain admin
RMTHOST oelgg2, MGRPORT 7809
RMTTRAIL ./dirdat/bb
TABLE hr.employees;

Before starting the capture process we need to assign the datapump to the extract1:

GGSCI ( 2> add extract datapmp1, exttrailsource ./dirdat/aa begin now
EXTRACT added.

GGSCI ( 4> add rmttrail ./dirdat/bb extract datapmp1

Finally we can start the extract:

GGSCI ( 6> start extract extract1

Sending START request to MANAGER ...

GGSCI ( 16> start extract datapmp1

Sending START request to MANAGER ...

GSCI ( 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DATAPMP1    00:00:00      00:00:37    
EXTRACT     RUNNING     EXTRACT1    00:00:10      00:00:05    

Both extracts are running which is what we expected. Now the counterpart, the apply/replicat process must be configured on the the target system:

GGSCI ( 1> edit params replcat1

The parameters to add are:

REPLICAT replcat1
DISCARDFILE ./dirdsc/replcat1.dsc, PURGE
MAP hr.employees, TARGET hr.employees;

As with the capture process on the source database the apply process on the target needs to get registered with the database:

GGSCI ( 5> dblogin useridalias DB2 domain admin
Successfully logged into database.

GGSCI ( as ggadmin@DB2) 6> add replicat replcat1 integrated exttrail ./dirdat/bb
REPLICAT (Integrated) added.

GGSCI ( as ggadmin@DB2) 8> start replicat replcat1

Sending START request to MANAGER ...

GGSCI ( 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLCAT1    00:00:00      00:05:30    

Time to test if the replication really works. On the source database:

SQL> update hr.employees set PHONE_NUMBER = '000.000.0000' where EMPLOYEE_ID = 199;

1 row updated.

SQL> commit;

Commit complete.


Was this replicated to the target database?

SQL> select EMPLOYEE_ID,PHONE_NUMBER from hr.employees where EMPLOYEE_ID = 199;

----------- --------------------
	199 000.000.0000

SQL> select instance_name from v$instance;


Cool. The first GoldenGate replication is up and running. Hope this helps.

In the next post I’ll look into how to setup a replication when we need to stage the tables on the target before starting the replication. In the setup of this post both tables (hr.employees) had the same data when the replication started. In real life you’d use e.g. data pump to populate the target database and start the replication afterwards. That is what the next post will be about.