How to set up basic Oracle GoldenGate(OGG) and implement high availability(HA) in Oracle Database Appliance(ODA) environment using Oracle Clusterware

Asset ID:	1-71-1391398.1
Update Date:	2017-09-12
Keywords:

Solution Type Technical Instruction Sure

Solution 1391398.1 : How to set up basic Oracle GoldenGate(OGG) and implement high availability(HA) in Oracle Database Appliance(ODA) environment using Oracle Clusterware

Applies to:

Oracle Database Appliance Software - Version 2.1.0.1 to 12.1.2.9 [Release 2.1 to 12.1]
Oracle Database Appliance - Version All Versions and later
Information in this document applies to any platform.
Applies to:

Oracle GoldenGate - Version 11.1.1.1 [and later release]
Source database - RAC in Oracle Database Appliance(ODA) environment
Target database - RAC in ODA environment, regular Oracle RAC database,
Oracle single instance database

Goal

This document includes sample code that can be used to configure Oracle Clusterware to manage Oracle GoldenGate. The example can serve as a starting point for a more customized Oracle GoldenGate HA implementation.

For more information please refer the following website:

Oracle Clusterware:

https://www.oracle.com/pls/db112/portal.portal_db?selected=16&frame=#oracle_clusterware

Oracle GoldenGate:

https://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Solution

Section 1: OGG basic set up

Step 1. Get OGG software

Go to OGG official website: https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Get OGG version for ODA(as this document is written):
Oracle GoldenGate v11.1.1.1.1 for Oracle 11g on Linux x86-64 (88 MB) And get the corresponding OGG software for your target system based on the OS and database version.

Step 2. OGG Software installation

On the source ODA system:

1. Log on to one of your ODA nodes(use 'slcac452' as example in the rest of this document)
as Oracle database software owner(use default 'oracle' in the rest of this document).

2. Go to the ACFS* shared directory - (default '/cloudfs' on source ODA),
-- create a new directory 'goldengate' for Oracle GoldenGate.

* Comment: ACFS is required for this implementation

3. Unzip and extract OGG software to this directory
-- this is the default OGG directory in the rest of this document:

'/cloudfs/goldengate'

$ unzip fbo_ggs_Linux_x64_ora11g_64bit.zip
$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

4. Set library paths for dynamic builds, including both OGG directory and Oracle database library path:

$ export LD_LIBRARY_PATH=/cloudfs/goldengate:/u01/app/oracle/product/11.2.0/dbhome_1/lib:$LD_LIBRARY_PATH

NOTE: The default Oracle database installation directory(ORACLE_HOME) in ODA which will be used in the rest of this document.

'/u01/app/oracle/product/11.2.0/dbhome_1'

5. From the OGG directory, start the GGSCI program:

$ ggsci

6. In GGSCI, issue the following command to create the OGG working directories:

GGSCI > create subdirs

7. (optional, support for DDL/Sequence) Create and edit the parameter file for GLOBALS:

GGSCI > EDIT PARAMS ./GLOBALS

8. (optional, support for DDL/Sequence) Add this line to GLOBALS parameter file:

GGSCHEMA ggs

NOTE: 'ggs' is the example OGG user and will be used in the rest of this document.

9. Save this file and exit.

10. Create and edit the Manager parameter file:

GGSCI > EDIT PARAMS mgr

11. Add the following lines to Manager parameter file:

PORT 7809
AUTOSTART ER *
AUTORESTART ER *

12. Save this file and exit.

13. Start Manager process:

GGSCI > START mgr

On the target system:

Following the same steps as above on the source system, but make necessary changes to file names, library paths and make sure to install OGG to a shared directory if you also want to implement HA on the target system, otherwise you can just use local directory.

NOTE: Directory '/mycloudfs/goldengate' will be used as OGG directory on the target system in the rest of this document.

Step 3 Prcepare the source and target database for OGG

1. Create OGG user 'ggs' on both the source and target database, connect to database using
SQL*Plus as SYSDBA:

SQL> CREATE USER ggs IDENTIFIED BY ggs;
SQL> GRANT CONNECT,RESOURCE,DBA TO ggs;

2. (optional, add Oracle sequence replication support)

On both source and target database, go to OGG directory and run this SQL, enter OGG user 'ggs' as prompted:

SQL> @sequence.sql

3. Enable supplemental logging on source ODA database:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;

(Optional) Add Oracle DDL replication support

4. On the source system, go to OGG directory, connect to database using SQL*Plus as SYSDBA.

SQL> GRANT EXECUTE ON utl_file TO ggs;

5 On the source system, run the following script, provide OGG user 'ggs' as prompted.

SQL> @marker_setup.sql
SQL> @ddl_setup.sql

NOTE: enter 'INITIALSETUP' when prompted for the mode of installation.

SQL> @role_setup.sql
SQL> @ddl_enable.sql
SQL> @ddl_pin ggs

NOTE: 'ggs' here is the OGG user.

Step 4 Configure the primary Extract group on the source system

1. Log on to slcac452 as oracle user, set up environment variables

ORACLE_HOME, ORACLE_SID:

NOTE: Use database 'ggdb' as example in the rest of this document, and assume the instance on slcac452 is 'ggdb1'

2. Start GGSCI from the OGG directory:

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_SID=ggdb1

$ ggsci

3. Issue the following command to log on to the database.

GGSCI > DBLOGIN USERID ggs, PASSWORD ggs

4. Create a primary Extract group 'myext':

GGSCI > ADD EXTRACT myext, TRANLOG, BEGIN NOW, THREADS 2

NOTE: THREADS value is the number of your RAC instances

5. Create a local trail. The primary Extract writes to this trail, and the data-pump Extract reads it.

GGSCI > ADD EXTTRAIL /cloudfs/goldengate/dirdat/et, EXTRACT myext

NOTE: 'et' is the example trail identifier for Extract 'myext'.

6. Create and edit the parameter file for Extract 'myext':

GGSCI > EDIT PARAMS myext

7. Add following lines to this parameter file:

EXTRACT myext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ggs@ggdb, PASSWORD ggs
TRANLOGOPTIONS DBLOGREADER
@THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL /cloudfs/goldengate/dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE hr.*;

@ NOTE: Regarding THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

@ ODA only runs on 11.2.0.2 and higher:

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY is deprecated for Oracle
versions 11.2 and later because the corresponding Oracle
MAX_COMMIT_PROPAGATION_DELAY parameter was made obsolete in Oracle 11.2

See:<Note 966207.1>

NOTE 1: make sure the SQL*Net connection string 'ggdb' works.
NOTE 2: 'hr' is the example schema which will be synchronized to the target system.

8. Save this file and exit.

Step 5. Configure the data pump Extract group on the source system

1. Create a data pump group 'mypump':

GGSCI > ADD EXTRACT mypump, EXTTRAILSOURCE /cloudfs/goldengate/dirdat/et,
BEGIN now

2. Specify a remote trail that will be created on the target system.

GGSCI > ADD RMTTRAIL /mycloudfs/goldengate/dirdat/rt, EXTRACT mypump

NOTE: 'rt' is the example trail identifier for Extract 'mypump', and use the target OGG directory '/mycloudfs/goldengate' here.

3. Create and edit the parameter file for Extract 'mypump':

GGSCI > EDIT PARAMS mypump

4. Add following lines to this parameter file:

EXTRACT mypump
RMTHOST rac12box-scan, MGRPORT 7809
RMTTRAIL /mycloudfs/goldengate/dirdat/rt
PASSTHRU
TABLE hr.*;

NOTE: RMTHOST is the target host. If you also prefer to set up HA on the target system, specify the VIP for your target system as RMTHOST, otherwise just use the IP address/hostname of your target system

COMMENT: For this example the RMTHOST is pointing to the scan name( e.g RMTHOST rac12box-scan) ..

5. Save this file and exit.

6. Start Extract 'myext' and 'mypump':

GGSCI > START myext
GGSCI > START mypump

7. Check the status of OGG processes:

GGSCI > info all

Program Status Group Lag
Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING MYEXT 00:00:07 00:00:04
EXTRACT RUNNING MYPUMP 00:00:00 00:00:05

Step 6. Configure the Replicat group on the target system

1. Log on to the target host(or one node of the target cluster) as Oracle database software owner(also use 'oracle' in the rest of this document), start GGSCI from the OGG directory.

$ ggsci

2. Create a Replicat group 'rept', which reads trails from Extract 'mypump':

GGSCI > ADD REPLICAT rept, EXTTRAIL /mycloudfs/goldengate/dirdat/rt, nodbcheckpoint

3. Create and edit the parameter file for Replicat 'rept' *

GGSCI > EDIT PARAMS rept

4. Add following lines to this parameter file, assume the same ORACLE_HOME
and target database 'ggdb' as in source ODA environment:

REPLICAT rept
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ggs@ggdb,PASSWORD ggs
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE /mycloudfs/goldengate/repsz.dsc,append,megabytes 100
MAP hr.*, TARGET hr.*;
NOTE: make sure the SQL*Net connection string 'ggdb' works.

5. Save this file and exit.

6. Start Replicat 'rept':

GGSCI > START rept

7. Check the status of OGG processes: GGSCI > info all

Program Status Group Lag
Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPT 00:00:07 0
0:00:04

Step 7. Verify OGG basic function

1. Log on to source database as user 'hr', do some simple DDL and DML operations.

2. Check the data change has been captured by Extract on source system:

GGSCI > STATS myext
GGSCI > STATS mypump

3. Log on to target system as oracle user, check the status of Replicat 'rept':

GGSCI > STATS rept

4. Compare the output and make sure data change is synchronized.

5. (optional, for further HA setup) Stop OGG on source system:

GGSCI > STOP myext
GGSCI > STOP mypump
GGSCI > STOP mgr

6. (optional, for further HA setup) Stop OGG on target system:

GGSCI > STOP rept
GGSCI > STOP mgr

Section 2: Set up high availability for OGG

Step 1. Add one VIP resource for OGG

1. Create new VIP resource, use IP address 10.2xx.xx.xx as example, login as root and run:

# /u01/app/11.2.0/grid/bin/appvipcfg create \
-network=1 \
-ip=10.2xx.xx.xx \
-vipname=ggatevip \
-user=root

NOTE 1: '/u01/app/11.2.0/grid' is the default Oracle GRID infrastructure software directory in ODA.
NOTE 2: The example VIP resource name is 'ggatevip' and will be used in the rest of this document.
NOTE 3: -network refers to the network number, default value is '1' in ODA environment.

And you can find the network number using this command:

# /u01/app/11.2.0/grid/bin/crsctl stat res -p |grep -ie .network -ie
subnet |grep -ie name -ie subnet
NAME=ora.net1.network
USR_ORA_SUBNET=10.xxx.xx.0

net1 indicates this is network 1, and the second line indicates the subnet
on which the VIP will be created.

2. Allow oracle user to start the VIP, run this command as root:

# /u01/app/11.2.0/grid/bin/crsctl setperm resource ggatevip -u user:oracle:r-x

3. Log on as oracle user, start the VIP resource:

$ /u01/app/11.2.0/grid/bin/crsctl start resource ggatevip

4. Validate VIP is running:

$ /u01/app/11.2.0/grid/bin/crsctl status resource ggatevip

NAME=ggatevip
TYPE=app.appvip.type
TARGET=ONLINE
STATE=ONLINE on slcac452
NOTE: The TARGET and STATE should both be ONLINE.

5. Ping the VIP's IP address(10.2xx.xx.xx) and make sure it's reachable.

NOTE: If you also prefer to set up HA on target system, follow the same
steps, and make necessary changes to directory, VIP resource name, IP address, etc.

Step 2. Develop an agent script

This document provides a sample agent script. This generic agent action
script will verify whether manager is running and if it is,
assume that all is fine (the manager parameter file contains AUTOSTART
and AUTORESTART parameters to manage other Extracts and/or Replicats).

1. Save the script in a file 11gr2_gg_action.scr and copy it to OGG directory.

NOTE: Make necessary changes to GGS_HOME(OGG directory) and ORACLE_HOME.
(this script uses default setting for ODA environment)

2. Make sure the script is executable:

$ chmod +x 11gr2_gg_action.scr

NOTE: If you also prefer to set up HA on target system, follow the same
steps, and make necessary changes to GGS_HOME, ORACLE_HOME in this file.

Step 3. Register OGG resource in Oracle Clusterware

1. Register OGG as a resource in Oracle Clusterware:

$ /u01/app/11.2.0/grid/bin/crsctl add resource ggateapp \
-type cluster_resource \
-attr "ACTION_SCRIPT=/cloudfs/goldengate/11gr2_gg_action.s
cr, \
CHECK_INTERVAL=30, START_DEPENDENCIES='hard(ggatevip,ora.ggdb.db) \
pullup(ggatevip)', STOP_DEPENDENCIES='hard(ggatevip)'"

NOTE 1: 'ggateapp' is the example OGG resource name and will be used in the rest of this document.
NOTE 2: 'ora.ggdb.db' is the resource name for database 'ggdb'.

2. Check the ggateapp resource is available as Oracle Clusterware resource:

$ /u01/app/11.2.0/grid/bin/crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=OFFLINE
STATE=OFFLINE

NOTE: If you also prefer to set up HA on target system, follow the same
steps, and make necessary changes to directory, VIP and database resource
name, etc

Step 4. Start the OGG resource

1. Start the resource, from now on you should always use Oracle Clusterware to start OGG:

$ /u01/app/11.2.0/grid/bin/crsctl start resource ggateapp

2. Check the status of OGG resource:

$ /u01/app/11.2.0/grid/bin/crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on slcac452

NOTE: TARGET and STATE should both be ONLINE.

3. Check status of OGG processes:

GGSCI > info all
Program Status Group Lag
Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING MYEXT 00:00:07
00:00:04
EXTRACT RUNNING MYPUMP 00:00:00 0
0:00:05

NOTE: If you also prefer to set up HA on target system, follow the same steps.

Step 5. Test OGG resource failover

1. Log on to node slcac452 as oracle user:

$ /u01/app/11.2.0/grid/bin/crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'slcac452'
CRS-2677: Stop of 'ggateapp' on 'slcac452' succeeded
CRS-2673: Attempting to stop 'ggatevip' on 'slcac452'
CRS-2677: Stop of 'ggatevip' on 'slcac452' succeeded
CRS-2672: Attempting to start 'ggatevip' on 'slcac453'
CRS-2676: Start of 'ggatevip' on 'slcac453' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'slcac453'
CRS-2676: Start of 'ggateapp' on 'slcac453' succeeded

2. Check the status of OGG resource:

$ /u01/app/11.2.0/grid/bin/crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on slcac453
NOTE: TARGET and STATE should both be ONLINE on slcac453.

3. Log on to another node 'slcac453' in this example as oracle user, go to OGG directory.

4. Set up LD_LIBRARY_PATH as described in Section 1, start GGSCI program:

$ ggsci

5. Check status of OGG processes:

GGSCI > info all

Program Status Group Lag
Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING MYEXT 00:00:09 0
0:00:04
EXTRACT RUNNING MYPUMP 00:00:00 0
0:00:05

NOTE: If you also prefer to set up HA on target system, follow the same steps, and make necessary changes to directory, etc.

# Following is the content of 11gr2_gg_action.scr
#!/bin/sh
#set the Oracle Goldengate installation directory
export GGS_HOME=/cloudfs/goldengate
#set the oracle home to the database to ensure GoldenGate will get the
#right environment settings to be able to connect to the database
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
#specify delay after start before checking for successful start
start_delay_secs=5
#Include the GoldenGate home in the library path to start GGSCI
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:${GGS_HOME}:${LD_LIBRARY_PATH}
#check_process validates that a manager process is running at the PID
#that GoldenGate specifies.
check_process () {
        if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
        then
                pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
                runningpid=`pgrep -s $pid -u oracle -f mgr`
                if [ ${pid} = ${runningpid} ]
                then
                        #manager process is running on the PID exit success
                        exit 0
                else
                        #manager process is not running on the PID
                        exit 1
                fi
        else
                #manager is not running because there is no PID file
                exit 1
        fi
}
#call_ggsci is a generic routine that executes a ggsci command
call_ggsci () {
ggsci_command=$1
ggsci_output=`${GGS_HOME}/ggsci << EOF
${ggsci_command}
exit
EOF`
}
case $1 in
'start')
#start manager
call_ggsci 'start manager'
#there is a small delay between issuing the start manager command
#and the process being spawned on the OS. wait before checking
sleep ${start_delay_secs}
#check whether manager is running and exit accordingly
check_process
;;
'stop')
#attempt a clean stop for all non-manager processes
#call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'check')
check_process
;;
'clean')
#attempt a clean stop for all non-manager processes
#call_ggsci 'stop er *'
#ensure everything is stopped
#call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'abort')
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
esac

References:
----------------
Oracle GoldenGate
11g Release 1 Patch Set 1 (11.1.1.1.1)
Release Notes E22504-01 2011

https://docs.oracle.com/cd/E22355_01/doc.11111/e22504.pdf

Community Discussions

Still have questions? Use the communities to search for similar discussions or start a new discussion on this subject.

Click here to open ODA (Oracle Database Appliance) Community in a new browser window.

Click here to open Golden Gate Community in a new browser window.

References

<NOTE:1112325.1> - Deploying Oracle GoldenGate to Achieve Operational Reporting for Oracle E-Business Suite
<NOTE:1494198.1> - GoldenGate Setup and Troubleshooting Webcast - Questions and Answers
<NOTE:1313703.1> - Oracle GoldenGate Best Practices: Oracle GoldenGate High Availability Using Oracle Clusterware
<NOTE:1433100.1> - Master Note for Oracle GoldenGate Setup Sample Scripts

Attachments

This solution has no attachment