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:
- 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’;
- 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=hrwest table_exists_action=replace network_link=hrwest flashback_scn=1980352 (one less than the SCN recorded from above)
- 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.
