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/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 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 ggadmin@DB1 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 ggadmin@DB1 Password: Successfully logged into database. GGSCI (oelgg1.it.dbi-services.com 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 (oelgg1.it.dbi-services.com as ggadmin@DB1) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 5> start manager Manager started. GGSCI (oelgg1.it.dbi-services.com as ggadmin@DB1) 6> info manager Manager is running (IP port oelgg1.it.dbi-services.com.7809, Process ID 17810). GGSCI (oelgg1.it.dbi-services.com 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 (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 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 (oelgg1.it.dbi-services.com 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 (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 ggadmin@DB2) 6> add replicat replcat1 integrated exttrail ./dirdat/bb REPLICAT (Integrated) added. GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)