Setup of an Oracle GoldenGate Hub

posted December 5, 2017, 3:21 PM by

Andy Kerber (@dbakerber), Senior Consultant

In this post, we will provide the steps to setup a GoldenGate hub.  In our hypothetical environment, we have a medium sized company, with two offices, one in Delaware and one in Washington.  The HR departments are logically separated between the east and west coasts, so the east coast schema is HREAST and the west coast schema is HRWEST.

All data in the HREAST schema is for east coast employees, and all data for the HRWEST schema is for the west coast employees.  The HREAST schema is updated on the hreastrh7 VM and the data is replicated using GoldenGate (one way) to hrwestrh7.  Similarly, the data from the HRWEST schema is replicated (one way) to the hreastrh7 VM.

In this first article, will cover the implementation of a GoldenGate hub in the environment described above.  In the second part, we will discuss how to enable the schemas for bi-directional replication.

Among the uses of a GoldenGate hub is the movement of Oracle data to Amazon Web Services (AWS).   There are several steps unique to that process that we are not covering here, but we will cover in a future post.

Step one is to install the Oracle client on the database hub server.  The name of that server in this environment is rhel72c.

The first step is to install the most recent Oracle client on the GoldenGate hub server, choosing the administrator installation. In a hub configuration, GoldenGate is installed only on the hub server, it is not installed on the database servers.  The Oracle client is installed on the hub server.  We will be using the thick client.

This blog forgoes covering the steps for the remainder of the client installation.  Select the defaults after choosing administrator.

Next, install the GoldenGate software.  Follow the instructions for the most recent version of GoldenGate, and install it in the directory listed below (for ease of installation and to follow along with this blog):

/u01/app/oracle/product/12.1.0/gghome

Also for ease of use, define the GoldenGate home directory as GGHOME in a Linux system variable at system start, or use an Oracle user login.  Put the line ‘export GGHOME=/u01/app/oracle/product/12.1.0/gghome_1’ in the .bash_profile to set it at user login, or in the file /etc/profile (to set system wide).

Next, put the client home directory in /etc/oratab:

client:/u01/app/oracle/product/12.1.0/client:N

 

Next, our tnsnames entries:

HREASTDB =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = hreastrh7)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = EAST)
 )
 )

HRWESTDB =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = hrwestrh7)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = WEST)
 )
 )

 

The above entries go in this file on the hub server:

/u01/app/oracle/product/12.1.0/client/network/admin/tnsnames.ora

Define the TNS_ADMIN directory to point to the directory containing the tnsnames.ora file at either the system or the user login level as described above.

export TNS_ADMIN= /u01/app/oracle/product/12.1.0/client/network/admin

 

Now lets begin.

1) Set the environment.

. oraenv

ORACLE_SID= [oracle] client

The Oracle base remains unchanged at /u01/app/oracle

 

Note that setting the environment is required because GoldenGate needs various files binaries from the client installation in order to access remote Oracle databases.

2) Go the GoldenGate installation directory and start the set up process.

[oracle@rhel72c ]$ cd $GGHOME

[oracle@rhel72c gghome]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
 Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
 Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
 Operating system character set identified as UTF-8.

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

GGSCI (rhel72c) > create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/12.3/gghome

Parameter file                 /u01/app/oracle/product/12.3/gghome/dirprm: created.
 Report file                    /u01/app/oracle/product/12.3/gghome/dirrpt: created.
 Checkpoint file                /u01/app/oracle/product/12.3/gghome/dirchk: created.
 Process status files           /u01/app/oracle/product/12.3/gghome/dirpcs: created.
 SQL script files               /u01/app/oracle/product/12.3/gghome/dirsql: created.
 Database definitions files     /u01/app/oracle/product/12.3/gghome/dirdef: created.
 Extract data files             /u01/app/oracle/product/12.3/gghome/dirdat: created.
 Temporary files                /u01/app/oracle/product/12.3/gghome/dirtmp: created.
 Credential store files         /u01/app/oracle/product/12.3/gghome/dircrd: created.
 Masterkey wallet files         /u01/app/oracle/product/12.3/gghome/dirwlt: created.
 Dump files                     /u01/app/oracle/product/12.3/gghome/dirdmp: created.

GGSCI (rhel72c) > edit param mgr

PORT 7865

GGSCI (rhel72c) > start mgr
 Manager started.

Next, we set up the credentialstore.

GGSCI (rhel72c) > add credentialstore

Credential store created in /u01/app/oracle/product/12.3/gghome/dircrd/.

GGSCI (rhel72c) > alter credentialstore add user ggadmin@hrwestdb password ggadmin alias ggadmine

Credential store in /u01/app/oracle/product/12.3/gghome/dircrd/ altered.

GSCI (rhel72c) > alter credentialstore add user ggadmin@hrwestdb password ggadmin alias ggadminw

Credential store in /u01/app/oracle/product/12.3/gghome/dircrd/ altered.

 

3) Configure the databases for GoldenGate.

On each database do the following.

Create the ggadmin user, grant privileges, and configure the database as shown below (in SQLPLUS):

alter database add supplemental log data
 alter system set enable_goldengate_replication=true scope=both;
 create tablespace ggs_data datafile '/u01/app/oracle/oradata/ggdemo/ggs_data01.dbf'
 size 1024m autoextend on;
 create user ggadmin identified by ggadmin default tablespace ggs_data
 temporary tablespace temp;
 grant connect,resource,create session, alter session to
 ggadmin;
 grant select any dictionary, select any table,create table to
 ggadmin;
 grant alter any table to ggadmin;
 grant execute on utl_file to ggadmin;
 grant flashback any table to ggadmin;
 grant execute on dbms_flashback to ggadmin;
 @marker_setup.sql
 @ddl_setup.sql
 @role_setup.sql
 @ddl_enable.sql
 @sequence.sql
 EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(‘GGADMIN’);
 grant ggs_ggsuser_role to ggadmin;
 @ddl_enable
 shutdown immediate;
 startup mount;
 alter database archivelog;
 alter database flashback on;
 alter database open;
 alter database add supplemental log data;
 alter database force logging;
 grant EXECUTE on dbms_logmnr_d to GGADMIN;
 grant SELECT on sys.logmnr_buildlog to GGADMIN;
 GRANT EXECUTE ON UTL_FILE TO  GGADMIN;
 grant EXEMPT ACCESS POLICY to GGADMIN;

 

4) Go back to ggsci to set up the replication.

ggsci > dblogin useridalias ggadmine

ggsci as ggadmin@hreastdb > add schematrandata hreast ALLCOLS

ggsci as ggadmin@hreastdb > add schematrandata hrwest ALLCOLS

ggsci as ggadmin@hreastdb > edit params extehre

extract extehre
 exttrail ./dirdat/ee
 tranlogoptions IntegratedParams (max_sga_size 256)
 discardfile ./dirrpt/silext01.dsc, append megabytes 50
 logallsupcols
 updaterecordformat compact
 reportcount every 2 hours, rate
 useridalias ggadmine
 tabe HREAST.*;

ggsci as ggadmin@hreastdb > register extract extehre database

2017-11-29 17:11:40  INFO    OGG-02003  Extract extehre successfully registered with database at SCN 1980353.<= Record this for future use.

 

GGSCI (rhel72c as ggadmin@hreastdb) 8> add extract extehre, integrated tranlog, begin now
 EXTRACT (Integrated) added.

GGSCI (rhel72c as ggadmin@hreastdb) 26> ADD EXTTRAIL ./dirdat/ee, EXTRACT extehre
 EXTTRAIL added.

GGSCI (rhel72c as ggadmin@hreastdb) 27> start extract extehre

Sending START request to MANAGER ...
 EXTRACT extehre starting

 

At this point, the data from the HREAST schema is being replicated from the HREAST database, but is not as yet being applied to the HRWEST database.  The next step is to import the initial data into the HRWEST database.  We need to import all data prior to the start of replication.  Datapump is one method for building this initial load as described below:

1 – In the HRWEST database, create a database link that points to HREAST.

SQL> Create database link hreast connect to system identified by system using ‘hreast’;

 

2 – Create a SQLPLUS directory for datapump to use.  In this case, I used HOMEDIR: SQL> Create directory homedir as ‘/home/oracle’;

3 – Use the datapump network link option to load the data:

impdp directory=homedir schemas=hreast table_exists_action=replace network_link=hreast flashback_scn=1980352 (one less than the SCN recorded from above)

 

4 – The data will be imported into the HRWESTDB.

Now we are ready to configure the replicat.  This applies the changes being captured using the extract.

Connect to the hrwest database as ggadmin from ggsci:

ggsci> dblogin useridalias ggadminw

GGSCI (rhel72c as ggadmin@hrwestdb) > add schematrandata hreast ALLCOLS

GGSCI (rhel72c as ggadmin@hrwestdb) > add schematrandata hrwest ALLCOLS

GGSCI (rhel72c as ggadmin@hrwestdb) > add replicat wesrepe integrated exttrail ./dirdat/ee  <= remember, this is the same as the exttrail declared for the extract.
 REPLICAT (Integrated) added.

edit params wesrepe

replicat wesrepe
 ASSUMETARGETDEFS
 DISCARDFILE ./dirrpt/weserep01.dsc
 DDL INCLUDE ALL
 USERIDALIAS ggadminw
 REPORTCOUNT EVERY 1 HOURS, RATE
 MAP HREAST.*, TARGET HREAST.*;

GGSCI (rhel72c as ggadmin@hrwestdb) > start replicat wesrepe

 

At this point, replication should be working from HREASTDB to HRWESTDB.  Be sure to run updates in HREASTDB to verify that everything is working correctly.

Now, follow the same process to replicate the HRWEST schema from HRWESTDB to HREASTDB.

We are already connected to HRWESTDB, so we can define the extract here:

ggsci as ggadmin@hrwestdb > register extract extwhre database

2017-11-29 17:11:40  INFO    OGG-02003  Extract extwhrw successfully registered with database at SCN 1980353.<= Record this for future use.

GGSCI (rhel72c as ggadmin@hrwestdb) > add extract extwhrw, integrated tranlog, begin now
 EXTRACT (Integrated) added.

GGSCI (rhel72c as ggadmin@hrwestdb) > ADD EXTTRAIL ./dirdat/we, EXTRACT extwhrw
 EXTTRAIL added.

GGSCI (rhel72c as ggadmin@hrwestdb) > edit params extwhrw

extract extwhrw
 exttrail ./dirdat/ww
 tranlogoptions IntegratedParams (max_sga_size 256)
 discardfile ./dirrpt/orcext01.dsc, append megabytes 50
 logallsupcols
 updaterecordformat compact
 reportcount every 2 hours, rate
 useridalias ggadmino
 table HRWEST.*;

GGSCI (rhel72c as ggadmin@hrwestdb) > start extract extwhrw

 

Now set up the replicat:

  1. In the HREAST database, create a database link that points to HRWEST.
SQL> Create database link HRWEST connect to system identified by system using ‘hrwest’;

 

  1. Create a SQLPLUS directory for datapump to use.  In this case, I used HOMEDIR: SQL> Create directory homedir as ‘/home/oracle’;
  2. Use the datapump network link option to load the data:
impdp directory=homedir schemas=hrwest table_exists_action=replace network_link=hrwest flashback_scn=1980352 (one less than the SCN recorded from above)

 

  1. The data will be imported into the HREASTDB.

Now we are ready to configure the replicat:

GGSCI (rhel72c as ggadmin@hreastdb) >dblogin useridalias ggadmine

GGSCI (rhel72c as ggadmin@hreastdb) > add schematrandata hrwest ALLCOLS

GGSCI (rhel72c as ggadmin@hreastdb) > add replicat easrepw integrated exttrail ./dirdat/we  <= remember this is the same as the exttrail declared for the extract.
 REPLICAT (Integrated) added.

edit params easrepw

replicat easrepw
 ASSUMETARGETDEFS
 DISCARDFILE ./dirrpt/easrepw01.dsc
 DDL INCLUDE ALL
 USERIDALIAS ggadmine
 REPORTCOUNT EVERY 1 HOURS, RATE
 MAP HRWEST.*, TARGET HRWEST.*;

GGSCI (rhel72c as ggadmin@hrwestdb) > start replicat easrepw

 

At this point, we have replication running from HRWESTDB, schema HRWEST, to HREASTDB. Schema HREAST is replicating from HREASTDB to HRWESTDB.  We are using a GoldenGate hub server, on a third system, to manage the replication process.

Please note that you will need to run updates in each schema in order to verify that everything is working properly.

In part two, we will make the necessary changes to allow for multi-master replication, in case we need to be able to run it in both databases.

 
Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

WANT TO LEARN MORE?

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone