Oracle Goldengate III

I am in the process of putting my notes into web pages, please keep checking in later for more contents

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

Contents

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