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 San Jose and one in Connecticut. The HR departments are logically separated between the west coast and South coasts, so the west coast schema is HRWEST and the south coast schema is HRSOUTH.
All data in the HRWEST schema is for east coast employees, and all data for the HRSOUTH schema is for the west coast employees. The HRWEST schema is updated on the hrwestrh7 VM and the data is replicated using GoldenGate (one way) to hrsouthrh7. Similarly, the data from the HRSOUTH schema is replicated (one way) to the hrwestrh7 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 presplaycld.
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):
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:
Next, our tnsnames entries:
HRWESTDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hrwestrh7)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = WEST) ) ) HRSOUTHDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hrsouthrh7)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = SOUTH) ) )
The above entries go in this file on the hub server:
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@presplaycld ]$ cd $GGHOME [oracle@presplaycld gghome]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 184.108.40.206.0 OGGCORE_220.127.116.11.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 (presplaycld) > 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 (presplaycld) > edit param mgr PORT 7865 GGSCI (presplaycld) > start mgr Manager started. Next, we set up the credentialstore. GGSCI (presplaycld) > add credentialstore Credential store created in /u01/app/oracle/product/12.3/gghome/dircrd/. GGSCI (presplaycld) > alter credentialstore add user ggadmin@hrsouthdb password ggadmin alias ggadmine Credential store in /u01/app/oracle/product/12.3/gghome/dircrd/ altered. GSCI (presplaycld) > alter credentialstore add user ggadmin@hrsouthdb 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@hrwestdb > add schematrandata hrwest ALLCOLS ggsci as ggadmin@hrwestdb > add schematrandata hrsouth ALLCOLS ggsci as ggadmin@hrwestdb > 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 HRWEST.*; ggsci as ggadmin@hrwestdb > 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 (presplaycld as ggadmin@hrwestdb) 8> add extract extehre, integrated tranlog, begin now EXTRACT (Integrated) added. GGSCI (presplaycld as ggadmin@hrwestdb) 26> ADD EXTTRAIL ./dirdat/ee, EXTRACT extehre EXTTRAIL added. GGSCI (presplaycld as ggadmin@hrwestdb) 27> start extract extehre Sending START request to MANAGER ... EXTRACT extehre starting
At this point, the data from the HRWEST schema is being replicated from the HRWEST database, but is not as yet being applied to the HRSOUTH database. The next step is to import the initial data into the HRSOUTH 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 HRSOUTH database, create a database link that points to HRWEST.
SQL> Create database link hrwest connect to system identified by system using ‘hrwest’;
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=hrwest table_exists_action=replace network_link=hrwest flashback_scn=1980352 (one less than the SCN recorded from above)
4 – The data will be imported into the HRSOUTHDB.
Now we are ready to configure the replicat. This applies the changes being captured using the extract.
Connect to the hrsouth database as ggadmin from ggsci:
ggsci> dblogin useridalias ggadminw GGSCI (presplaycld as ggadmin@hrsouthdb) > add schematrandata hrwest ALLCOLS GGSCI (presplaycld as ggadmin@hrsouthdb) > add schematrandata hrsouth ALLCOLS GGSCI (presplaycld as ggadmin@hrsouthdb) > 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 HRWEST.*, TARGET HRWEST.*; GGSCI (presplaycld as ggadmin@hrsouthdb) > start replicat wesrepe
At this point, replication should be working from HRWESTDB to HRSOUTHDB. Be sure to run updates in HRWESTDB to verify that everything is working correctly.
Now, follow the same process to replicate the HRSOUTH schema from HRSOUTHDB to HRWESTDB.
We are already connected to HRSOUTHDB, 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 (presplaycld as ggadmin@hrsouthdb) > add extract extwhrw, integrated tranlog, begin now EXTRACT (Integrated) added. GGSCI (presplaycld as ggadmin@hrsouthdb) > ADD EXTTRAIL ./dirdat/we, EXTRACT extwhrw EXTTRAIL added. GGSCI (presplaycld as ggadmin@hrsouthdb) > 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 HRSOUTH.*; GGSCI (presplaycld as ggadmin@hrsouthdb) > start extract extwhrw
Now set up the replicat:
- In the HRWEST database, create a database link that points to HRSOUTH.
SQL> Create database link HRSOUTH connect to system identified by system using ‘hrsouth’;
- Create a SQLPLUS directory for datapump to use. In this case, I used HOMEDIR: SQL> Create directory homedir as ‘/home/oracle’;
- Use the datapump network link option to load the data:
impdp directory=homedir schemas=hrsouth table_exists_action=replace network_link=hrsouth flashback_scn=1980352 (one less than the SCN recorded from above)
- The data will be imported into the HRWESTDB.
Now we are ready to configure the replicat:
GGSCI (presplaycld as ggadmin@hrwestdb) >dblogin useridalias ggadmine GGSCI (presplaycld as ggadmin@hrwestdb) > add schematrandata hrsouth ALLCOLS GGSCI (presplaycld as ggadmin@hrwestdb) > 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 HRSOUTH.*, TARGET HRSOUTH.*; GGSCI (presplaycld as ggadmin@hrsouthdb) > start replicat easrepw
At this point, we have replication running from HRSOUTHDB, schema HRSOUTH, to HRWESTDB. Schema HRWEST is replicating from HRWESTDB to HRSOUTHDB. 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.