ORAFAQ:11g OracleGoldenGate
NOTE: Unless stated otherwise all the steps required on both primary and destination legs
NOTE: Operations performed on primary will be reflected on destination if destination is restored afterwards
NOTE: Example here is to set up a bidirectional replication using Classic Capture method
NOTE: Oracle Golden Gate Documentation links:
http://docs.oracle.com/cd/E35209_01/index.htm
Oracle Golden Gate software bundle is available under Fusion Middleware category.
A Note from Oracle:
ggsci
EDIT PARAMS ./GLOBALS --> GGSCHEMA GG_ADMIN
DBLOGIN, USERID gg_admin PASSWORD gg_admin
ADD CHECKPOINTTABLE gg_admin.chkpttable
-- ------
Install DDL support
sqlplus
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
-- -------
edit PARAMS ./GLOBALS
CHECKPOINTTABLE chkpttable
-- -------
ADD SCHEMATRANDATA DBPCRU
ADD SCHEMATRANDATA INTORDER
ADD SCHEMATRANDATA CRURPT
-- -------
edit params mgr(creates param file called mgr.prm under ./dirprm)
PORT 7800 - Alp
PORT 7900 - Boca
start mgr
info mgr
info all
-- ------
e_cru_a.prm --->
-- Identify tables to extract...using schema and wildcard
TABLE DBPCRU.*;
TABLE INTORDER.*;
TABLE CRURPT.*;
ADD EXTRACT E_CRU_A, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/EA, EXTRACT E_CRU_A, MEGABYTES 100
-- ------
- Best way to download GG binaries is through support.oracle.com under .patches & updates. and then .product or family.:then select the product .Oracle GoldenGate..
- Search for your version. The newest version is 11.2.1.0.6. All these version are rollups and not one off patches. That.s just how GG does their releases.
- Please note you can use OTN or edelivery for binaries but sometimes they don.t have the last versions out there.
- Ancillary products like management packs and Veridata should be done with edelivery though.
Copy in our s/w repository:
/net/alanetapp001a/vol/install/oracle/OGG/112106/p16441092_112106_Linux-x86-64.zip
Once software is available as a tar ball, simply untar to a separate ogg area(ex: /db/p/cru/gg) to complete the installation.
Ex: cd /db/p/cru/gg
tar -xvf <name of tar ball>
ORACLE_SID=CRUP
Make entries into tnsnames to connect to primary and destination databases, say as aliases CRUP11 and CRUP12
Make a note that ogg doesn't use tnsnames entries to connect to destinations...these entries just for establishing/verifying generic connectivity aspects
CRUP11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alalpdba048)(PORT = 1580))
)
(CONNECT_DATA =
(SID = CRUP1)
)
)
CRUP12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bctlpdba013)(PORT = 1580))
)
(CONNECT_DATA =
(SID = CRUP1)
)
)
Create ogg environment file, say as "gg.env" and run this file to set up necessary settings
ex:
GG=/db/p/cru/gg ; export GG
ORACLE_HOME=/db/p/cru/v1/product/11.2.0; export ORACLE_HOME
ORACLE_SID=CRUP1 ; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH ; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
alias ggsci='cd $GG; ./ggsci'
ogg requires several subdirectories to place files related different aspects of configuration and on-going operations. Create all these sub directories by invoking ggsci
ggsci create subdir
GGSCI (bctlpdba013.risk.regn.net) 1> CREATE SUBDIRS Creating subdirectories under current directory /db/p/cru/gg Parameter files /db/p/cru/gg/dirprm: already exists Report files /db/p/cru/gg/dirrpt: created Checkpoint files /db/p/cru/gg/dirchk: created Process status files /db/p/cru/gg/dirpcs: created SQL script files /db/p/cru/gg/dirsql: created Database definitions files /db/p/cru/gg/dirdef: created Extract data files /db/p/cru/gg/dirdat: created Temporary files /db/p/cru/gg/dirtmp: created Stdout files /db/p/cru/gg/dirout: created
Forced logging - required to make sure NOLOGGING operations also logged in for ogg
Supplemental logging - required to gather additional information for ogg
ALTER DATABASE FORCE LOGGING;
alter database add supplemental log data;
Create a separate tablespace and assign it to gg_admin user which is used for all ogg related activities.
Grant dba privilege to gg_admin so that we won't have to worry about granting several ogg related roles specifically.
create tablespace gg_admin datafile '/db/p/cru/v3/table/CRUP1/crup1_gg_admin_01.dbf' size 200m;
create user gg_admin identified by gg_admin default tablespace gg_admin;
grant dba to gg_admin;
Make entry into GLOBALS parameter file about this generic schema
ggsci
EDIT PARAMS ./GLOBALS
GGSCHEMA GG_ADMIN
We have to create support for DDL if we need ogg to relicate DDL changes as well
SQL> @marker_setup.sql Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gg_admin Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GG_ADMIN
MARKER TABLE ------------------------------- OK
MARKER SEQUENCE ------------------------------- OK Script complete. SQL> @ddl_setup.sql Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gg_admin Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete.
WARNING: Tablespace GG_ADMIN does not have AUTOEXTEND enabled.
Using GG_ADMIN as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GG_ADMIN
CLEAR_TRACE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
CREATE_TRACE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
TRACE_PUT_LINE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
INITIAL_SETUP STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDLREPLICATION PACKAGE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDLREPLICATION PACKAGE BODY STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDL IGNORE TABLE ----------------------------------- OK
DDL IGNORE LOG TABLE ----------------------------------- OK
DDLAUX PACKAGE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDLAUX PACKAGE BODY STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDL HISTORY TABLE ----------------------------------- OK
DDL HISTORY TABLE(1) ----------------------------------- OK
DDL DUMP TABLES ----------------------------------- OK
DDL DUMP COLUMNS ----------------------------------- OK
DDL DUMP LOG GROUPS ----------------------------------- OK
DDL DUMP PARTITIONS ----------------------------------- OK
DDL DUMP PRIMARY KEYS ----------------------------------- OK
DDL SEQUENCE ----------------------------------- OK
GGS_TEMP_COLS ----------------------------------- OK
GGS_TEMP_UK ----------------------------------- OK
DDL TRIGGER CODE STATUS: Line/pos Error ---------- ----------------------------------------------------------------- No errors No errors
DDL TRIGGER INSTALL STATUS ----------------------------------- OK
DDL TRIGGER RUNNING STATUS ----------------------------------- ENABLED
STAYMETADATA IN TRIGGER ----------------------------------- OFF
DDL TRIGGER SQL TRACING ----------------------------------- 0
DDL TRIGGER TRACE LEVEL ----------------------------------- 0
LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /db/p/cru/v1/admin/CRUP1/logs/diag/rdbms/crup1/CRUP1/trace/ggs_ddl_trace.log Analyzing installation status...
STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete.
SQL> @role_setup.sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:gg_admin Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes. @ddl_enable.sql - to enable the DDL trigger
SQL> @ddl_enable.sql Trigger altered.
ggsci DBLOGIN, USERID gg_admin PASSWORD gg_admin ADD CHECKPOINTTABLE gg_admin.chkpttable
GGSCI (bctlpdba013.risk.regn.net) 1> DBLOGIN, USERID gg_admin PASSWORD gg_admin Successfully logged into database.
GGSCI (bctlpdba013.risk.regn.net) 2> ADD CHECKPOINTTABLE gg_admin.chkpttable Successfully created checkpoint table gg_admin.chkpttable.
Add checkpoint table to GLOBALS file
edit PARAMS ./GLOBALS CHECKPOINTTABLE chkpttable
TRANDATA will gather additional transactions related data for ogg.
Use ADD TRANDATA only if you are not using the Oracle GoldenGate DDL replication feature.
If you are using the Oracle GoldenGate DDL replication feature, use the ADD SCHEMATRANDATA command to log the required supplemental data.
ADD SCHEMATRANDATA DBPCRU ADD SCHEMATRANDATA INTORDER ADD SCHEMATRANDATA CRURPT
GGSCI (bctlpdba013.risk.regn.net) 2> ADD SCHEMATRANDATA CRURPT 2013-04-24 14:39:30 INFO OGG-01788 SCHEMATRANDATA has been added on schema CRURPT.
GGSCI (bctlpdba013.risk.regn.net) 3> ADD SCHEMATRANDATA INTORDER 2013-04-24 14:39:43 INFO OGG-01788 SCHEMATRANDATA has been added on schema INTORDER.
GGSCI (bctlpdba013.risk.regn.net) 4> ADD SCHEMATRANDATA DBPCRU 2013-04-24 14:39:50 INFO OGG-01788 SCHEMATRANDATA has been added on schema DBPCRU.
GGSCI (bctlpdba013.risk.regn.net) 5> exit
Here is the basic information regarding different processes involved with ogg. Each one of these processes require a parameter file.
MANAGER - Manages all other processes, runs on a port thus to accept communication from other legs(dbs) in a replicating
environment.
Runs on both source and destination.
EXTRACT - Extracts changes from local database. Runs on source(and destination for bidirectional)
DATAPUMP - Pumps extracted changes to destination database. Runs on source(and destination for bidirectional)
REPLICATOR - Applies changes on destination database. Runs on destination(and sourcefor bidirectional)
Naming of the processes - Manager can't have user defined name whereas all other processes can have site-specific names but restricted to 8 characters.
Different files in use by ogg - we have to specify the names of different files in use(as explained below) in the parameter files of the processes
Log in files - Files used to have log in information of gg_admin schema
Ex: cru_login_a.inc(on primary) , cru_login_b.inc(on destination) with following entry:
USERID gg_admin, PASSWORD gg_admin
Trail Files - Files generated by extract and pump processes
EXTTRAIL - Trail generated by extract process on local db machine
RMTTRAIL - Trail generated by pump process on remote db machine
Trailfile Prefixes - There can only a two letter prefix, hence decided to use first letter to represent the process and
second letter to represent site
Ex: EA - Extract on A
PA - Pump from A(even through "pumped" file will be on B side)
Dicard files - Files generated by different processes with exceptions(ex: rejected data etc..)
Ex: ext_siteA.dcs(discard file generated by extract), rep_siteB.dcs(discard file generated by pump) etc..
MANAGER:
ggsci edit params mgr(creates param file called mgr.prm under ./dirprm)
PORT 7800 - Alp PORT 7900 - Boca
start mgr
GGSCI (alalpdba048.risk.regn.net) 1> start mgr Manager started.
GGSCI (alalpdba048.risk.regn.net) 1> info mgr Manager is running (IP port alalpdba048.risk.regn.net.7800).
GGSCI (alalpdba048.risk.regn.net) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED
EXTRACT(source):
edit params E_CRU_A (creates e_cru_a.prm file)
e_cru_a.prm entries:
-- Name of the primary extract EXTRACT E_CRU_A
-- Database login info INCLUDE ./dirprm/cru_login_a.inc
-- For Extract to ignore the transaction coming from GG_ADMIN (as part of Replication from other site) TRANLOGOPTIONS EXCLUDEUSER GG_ADMIN
-- Location of local trail file on system A EXTTRAIL ./dirdat/EA
-- Set report generation interval REPORTCOUNT EVERY 2 MINUTES, RATE --REPORTCOUNT EVERY 500 RECORDS, RATE
DDL INCLUDE OBJNAME DBPCRU.* EXCLUDE OBJTYPE 'SEQUENCE' & INCLUDE OBJNAME INTORDER.* EXCLUDE OBJTYPE 'SEQUENCE' & INCLUDE OBJNAME CRURPT.* EXCLUDE OBJTYPE 'SEQUENCE'
-- REPORT is to get missed ddl for sequences as SEQ are in the above line DDLOPTIONS ADDTRANDATA, REPORT
-- Identify tables to extract...using schema and wildcard TABLE DBPCRU.*; TABLE INTORDER.*; TABLE CRURPT.*;
ADD EXTRACT E_CRU_A, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/EA, EXTRACT E_CRU_A, MEGABYTES 100
info all
GGSCI (alalpdba048.risk.regn.net) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED EXTRACT ABENDED E_CRU_A 00:00:00 155:47:20
NOTE: Make a note of SCN reported in "ggserr.log" by the extract processes right after it is started..
Ex: <date/time info> INFO OGG-01517 Oracle GoldenGate Capture for Oracle, <extract process name>: Position of first record processed Sequence 1,
RBA 3991568, SCN 0.2025414234, <date/time info>.
PUMP(source):
edit params P_CRU_A (creates p_cru_a.prm file)
p_cru_a.prm entries:
-- Name of the Pump Extract EXTRACT P_CRU_A
-- Process primary extract trail files in passthru mode, i.e. no transformations. PASSTHRU
-- Define remote host and manager port RMTHOST bctlpdba013, MGRPORT 7900
-- Location of source trail file on system A EXTTRAILSOURCE dirdat/EA
-- Location of remote trail file on system B RMTTRAIL ./dirdat/PA
-- Set report generation interval REPORTCOUNT EVERY 2 MINUTES, RATE --REPORTCOUNT EVERY 1000 RECORDS, RATE
-- Identify tables to extract...using schema and wildcard table DBPCRU.*; table INTORDER.*; table CRURPT.*;
ADD EXTRACT P_CRU_A, EXTTRAILSOURCE dirdat/EA
ADD RMTTRAIL ./dirdat/PA, EXTRACT P_CRU_A, MEGABYTES 100
info all
GGSCI (alalpdba048.risk.regn.net) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED EXTRACT RUNNING E_CRU_A 00:00:00 155:47:20 EXTRACT RUNNING P_CRU_A 00:00:00 00:00:08
NOTE: From this point on the changes from source will be captured by EXTRACT and pumped to destination by DATAPUMP process
Now build the destination (say using typical restore/recovery procedures) and grab the scn the destination restored to:
select current_scn from v$database;
NOTE: We will use SCN identified right after extract process is added as a reference to decide a recovery point. Recover until a log sequence whose switch scn is after theSCN identified after extract process. Then we can supply fisrst_change scn# as the starting scn for replicator.
Make sure sequences are set to start with an odd number and incrementing by two(for a two-legged ogg environment).
REPLICAT(Destination):
edit params R_CRU_B (creates r_cru_b.prm file)
r_cru_b.prm entries:
-- Name of the replicat REPLICAT r_cru_b
-- Database login info INCLUDE ./dirprm/cru_login_b.inc
--Indicates the source and target schema defs are the same ASSUMETARGETDEFS
-- Suppress Triggers from firing during the Replicat Session. DBOPTIONS SUPPRESSTRIGGERS DBOPTIONS DEFERREFCONST
REPORTCOUNT EVERY 500 RECORDS, RATE
-- Resolve meta data for table def as it appears in log record WILDCARDRESOLVE DYNAMIC
-- For Active-Active configuration, instruct Replicate to update its metadata cache DDLOPTIONS UPDATEMETADATA, REPORT
-- Set up a discard file for rejected records with housekeeping DISCARDFILE ./dirout/rep_bct.dcs, APPEND, MEGABYTES 500 REPERROR (default, discard) --REPERROR 933 discard --REPERROR 1403 discard
-- Identify tables to extract...using schema and wildcard MAP DBPCRU.*, TARGET DBPCRU.*; MAP INTORDER.* TARGET INTORDER.*; MAP CRURPT.*, TARGET CRURPT.*;
ADD REPLICAT R_CRU_B, EXTTRAIL ./dirdat/PA
start replicat R_CRU_B, aftercsn <scn upto which destination is recovered from latest backups on source>
info all
GGSCI (bctlpdba013.risk.regn.net) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED REPLICAT ABENDED R_CRU_B 35:22:30 156:30:22
FOR Bidirectional - Add extract and pump on destination AND replicat on source and once all processes are added, up and running here below is how it looks:
SOURCE:
GGSCI (alalpdba048.risk.regn.net) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED EXTRACT RUNNING E_CRU_A 00:00:00 155:47:20 EXTRACT RUNNING P_CRU_A 00:00:00 00:00:08 REPLICAT RUNNING R_CRU_A 00:00:00 00:00:09
DEST:
GGSCI (bctlpdba013.risk.regn.net) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED EXTRACT RUNNING E_CRU_B 00:00:00 156:40:21 EXTRACT RUNNING P_CRU_B 00:00:00 00:00:09 REPLICAT ABENDED R_CRU_B 35:22:30 156:30:22
The top level generic log file is called "ggserr.log" and is lcoated under ogg home.
We got a range of ports opened up between subnets of Alp and Boca oracle db host sebnets.
1500-1600 for listener
7800-8000 for ogg
Let us follow the following allocation for ogg(with mgr running on same port on alp and bct..like lsnr..so that there won.t be any confusion..).
We are not anticipating to use any parallelism as of now. If needed we can try to address it later.
7800 . btip1
7801 . csp1
7802 . dmvp1
7803 . mvrp1
7804 . otsp1
7805 . padp1
7806 . ps
7807 . ps
7808 - ps
Introduced new vips similarly named as original vips but with a postfix called "gg".
The golden gate manager processes run on these vips so that they can be swung between hosts in case of failover
Ex:
dbpbti-alagg - Alp
dbpbti-bctgg - Boca
Introduced scripts to startup and shutdown godlen gate processes and integrated them with regular startup/shutdown scripts.
And these scripts are integrated into vcs cluster so that ogg processes also will be swung along with db and listener.
Ex:
startup.sh
startup_oracle.sh
startup_listener.sh
startup_gg.sh
startup_gg.sh
. /db/p/bti/gg/gg.env
ggsci <<EOF
info all
start manager
info all
start er *
info all
exit
EOF
shutdown.sh
shutdown_gg.sh
shutdown_listener.sh
shutdown_oracle.sh
startup_gg.sh
. /db/p/bti/gg/gg.env
ggsci <<EOF
info all
stop er *
info all
stop manager!
info all
exit
EOF
Introduced the following shell script to monitor golden gate processes minimally to being with and we need to enhance it as needed.
. /db/p/bti/gg/gg.env
HOST=`hostname`
UP=0
if ( [ -f "${GG}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GG}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |awk '{ print $1 }'` ]
then
echo "manager process is running on the PID . exit success"> /tmp/check.out
UP=0
else
echo "manager process is not running on the PID" > /tmp/check.out
UP=1
fi
else
echo "manager is not running because there is no PID file" > /tmp/check.out
UP=1
fi
#echo $HOST
if [ ${UP} = 1 ]
then
mailx -s "Golden Gate Manager process IS NOT RUNNING for ${ORACLE_SID} on $HOST" Surendra.Tirumala@lexisnexis.com </dev/null
#mailx -s "Golden Gate Manager process IS NOT RUNNING for ${ORACLE_SID} on $HOST" ln-ora-dba@lexisnexis.com </dev/null
else
mailx -s "Golden Gate Manager process IS RUNNING for ${ORACLE_SID} on $HOST" Surendra.Tirumala@lexisnexis.com </dev/null
#mailx -s "Golden Gate Manager process IS RUNNING for ${ORACLE_SID} on $HOST" ln-ora-dba@lexisnexis.com </dev/null
fi
The godlen gate internal processsing can be monitored closely as explained in the following metalink doc.
Oracle GoldenGate - Heartbeat process to monitor lag and performance in GoldenGate [ID 1299679.1]
Add SYSLOG parameter to GLOBALS file and bounce ogg processes to supress all BUT warning and error messages to /var/log/messages file:
-- To supress messages to /var/log/messages SYSLOG WARN, ERROR