Infrastructure at your Service

Daniel Westermann

Setting up a sample replication with GoldenGate

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 12.1.0.2 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 LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
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/[email protected]
$ sqlplus ggadmin/[email protected]

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 12.1.2.1.9 21381983 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150728.2027_FBO
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 (oelgg1.it.dbi-services.com) 1> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (oelgg1.it.dbi-services.com) 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI (oelgg1.it.dbi-services.com) 4> alter credentialstore add user [email protected] alias DB1 domain admin
Password: 

Credential store in ./dircrd/ altered.

GGSCI (oelgg1.it.dbi-services.com) 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 (oelgg1.it.dbi-services.com) 2> dblogin userid [email protected]
Password: 
Successfully logged into database.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 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 (oelgg1.it.dbi-services.com as [email protected]) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           


GGSCI (oelgg1.it.dbi-services.com as [email protected]) 5> start manager
Manager started.


GGSCI (oelgg1.it.dbi-services.com as [email protected]) 6> info manager

Manager is running (IP port oelgg1.it.dbi-services.com.7809, Process ID 17810).


GGSCI (oelgg1.it.dbi-services.com as [email protected]) 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:

[email protected]:/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 (oelgg1.it.dbi-services.com) 11> edit params mgr

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

PURGEOLDEXTRACTS .dirdat/aa*, USECHECKPOINTS, MINKEEPHOURS 2

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 (oelgg1.it.dbi-services.com) 2> edit params extract1

The following parameters are added to the extract configuration file:

EXTRACT extract1
USERIDALIAS DB1 DOMAIN admin
EXTTRAIL ./dirdat/aa
TABLE hr.employees;

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

GGSCI (oelgg1.it.dbi-services.com) 3> add extract extract1, integrated tranlog, begin now
EXTRACT added.
GGSCI (oelgg1.it.dbi-services.com) 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 (oelgg1.it.dbi-services.com) 6> add exttrail ./dirdat/aa, extract extract1
EXTTRAIL added.

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

GGSCI (oelgg1.it.dbi-services.com) 1> dblogin useridalias DB1 domain admin
Successfully logged into database.

GGSCI (oelgg1.it.dbi-services.com as [email protected]) 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 (oelgg1.it.dbi-services.com as [email protected]) 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 (oelgg1.it.dbi-services.com) 2> add extract datapmp1, exttrailsource ./dirdat/aa begin now
EXTRACT added.

GGSCI (oelgg1.it.dbi-services.com) 4> add rmttrail ./dirdat/bb extract datapmp1
RMTTRAIL added.

Finally we can start the extract:

GGSCI (oelgg1.it.dbi-services.com) 6> start extract extract1

Sending START request to MANAGER ...
EXTRACT EXTRACT1 starting

GGSCI (oelgg1.it.dbi-services.com) 16> start extract datapmp1

Sending START request to MANAGER ...
EXTRACT DATAPMP1 starting

GSCI (oelgg1.it.dbi-services.com) 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 (oelgg2.it.dbi-services.com) 1> edit params replcat1

The parameters to add are:

REPLICAT replcat1
USERIDALIAS DB2 DOMAIN admin
DISCARDFILE ./dirdsc/replcat1.dsc, PURGE
ASSUMETARGETDEFS
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 (oelgg2.it.dbi-services.com) 5> dblogin useridalias DB2 domain admin
Successfully logged into database.

GGSCI (oelgg2.it.dbi-services.com as [email protected]) 6> add replicat replcat1 integrated exttrail ./dirdat/bb
REPLICAT (Integrated) added.

GGSCI (oelgg2.it.dbi-services.com as [email protected]) 8> start replicat replcat1

Sending START request to MANAGER ...
REPLICAT REPLCAT1 starting

GGSCI (oelgg2.it.dbi-services.com) 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.

SQL> 

Was this replicated to the target database?

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

EMPLOYEE_ID PHONE_NUMBER
----------- --------------------
	199 000.000.0000

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

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.

8 Comments

  • Vagish Tupsakri , from India says:

    The blog is very good and exact. Thanks …

  • Clint Morgan says:

    Awesome write up! This helped me get past some sticking points. Now I am at a new one. My extracts and replicat have been created and they say they are starting, but never actually start. I’ve tried with a begin now and begin 2012-May-01. Is there a way to tell why they are “starting”, but never start?

    • Clint Morgan says:

      I see it now in the Event Viewer.

      – ERROR OGG-00664 Oracle GoldenGate Capture for Oracle, EXTMAIL.prm: OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied).

      I’m just not sure what it’s referring to.

  • rajagiri says:

    Nice content. able to replicate between different database. can we replicate table within the same database. I know that datapump not needed for this process. what are mandatory parameters of extract and replicate for this scenario?

  • rajagiri says:

    I could able to replicate and datapump was used for this process.

  • Guy Lambregts says:

    Thank you, much appreciated
    I used this for initial setup
    I found the Oracle documentation somewhat confusing, at least to start with

  • Alexandre Alvarenga says:

    I always install the GG binaries on the database servers, but I would like to know if there is a possibility of creating a server only to have GG installed and from doing the readings in the sources and propagating to the targets, I only created the users GG and would give the necessary permissions on the database servers, I would not perform any binary installation on the servers, I would have a server with only GG installed and in it I would configure the replications.
    Do you know if this works?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure