ORACLE Golden Gate

1. Download Golden Gate software 11.1.1.1
unzip fbo_ggs_Linux_x64_ora11g_64bit.zip
tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
create a dir /ogg

2. Launch GGSCI
cd /ogg/
GGSCI

4. Create Sub directory:
GGSCI (soucredb1-lnx) 1> create subdirs
GGSCI (pricedev1-lnx) 1> create subdirs

===== Create GoldenGate database user ggs_owner on both source and target database =====

1. Create tablespace GGS_DATA
CREATE SMALLFILE TABLESPACE GGS_DATA
DATAFILE '+CFIP_DATA' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 30g LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

2. Create user ggs_owner
create user ggs_owner identified by ********
default tablespace ggs_data
temporary tablespace temp;

3. Grant privilege to ggs_owner
grant connect,resource,dba to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;

===== Setup and start GoldenGate Manager Process on both source and target=====
GGSCI > Edit param MGR
PORT 7809
GGSCI > start manager

=====Enabling Database-Level Supplemental Logging on source database=====
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--Verify that it is enabled
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

=====Enabling Table-Level Supplemental Logging on source database=====
GGSCI (oragdev1-lnx) 2> dblogin userid ggs_owner@CFIPDEV password ********
Successfully logged into database.
GGSCI (oragdev1-lnx) 5> add trandata ORACUSP.*
--Verify that it is enabled
SQL> select owner, log_group_name, table_name
from dba_log_groups where owner = 'ORACUSP';

=====Enabling DDL replication on source database=====
cd /ggs
-- following scripts were provided by GoldenGate
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant ggs_ggsuser_role to ggs_owner;
SQL> @ddl_enable.sql
-- Disable Recycle Bin
SQL> alter system set recyclebin = off scope=spfile;
stop and start datbase

=====Starging Extract on source database =====
1. Configuring the Local Extract
GGSCI (sourceserver) 1> edit params lcuspd1
EXTRACT lcuspd1
------------------------------------------------
-- Local extract for ORACUSIP on CFIPDEV
------------------------------------------------
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERID ggs_owner@CFIPDEV, PASSWORD ********
EXTTRAIL dirdat/cfipdev/oracusp/l1
TRANLOGOPTIONS ASMUSER sys@ASM, ASMPASSWORD ********
-- TRANLOGOPTIONS EXCLUDEUSER ggs_owner
DDL INCLUDE MAPPED
TABLE oracusp.*;

2. Adding the Extract
GGSCI (sourceserver) > dblogin userid ggs_owner@CFIPDEV password ********
GGSCI (sourceserver) > ADD EXTRACT lcuspd1, TRANLOG, THREADs 2, BEGIN NOW
GGSCI (sourceserver) > ADD EXTTRAIL dirdat/cfipdev/oracusp/l1, EXTRACT lcuspd1, MEGABYTES 100

3. Starting the Extract
GGSCI (sourceserver) > START EXTRACT lcuspd1

4. Verifying the Extract
GGSCI (sourceserver) > info EXTRACT lcuspd1
GGSCI (sourceserver) > info EXTRACT lcuspd1, detail
GGSCI (sourceserver) > stats EXTRACT lcuspd1

=====Starging the Data Pump on source database =====
1. Configuring the Data Pump
GGSCI (sourceserver) 1> edit params pcuspd1
EXTRACT pcuspd1
-------------------------------------------------------------------
-- Data Pump extract for ORACUSP on CFIPDEV
-------------------------------------------------------------------
PASSTHRU
RMTHOST pricedev_clus, MGRPORT 7809
RMTTRAIL dirdat/cfipdev/oracusp/r1
TABLE ORACUSP.*;

2. Adding the Data Pump
GGSCI (sourceserver) > ADD EXTRACT pcuspd1, EXTTRAILSOURCE dirdat/cfipdev/oracusp/l1
GGSCI (sourceserver) > ADD RMTTRAIL dirdat/cfipdev/oracusp/r1, EXTRACT pcuspd1, MEGABYTES 100

3. Starting the Data Pump
GGSCI (sourceserver) > START EXTRACT pcuspd1

4. Verifying the Data Pump
GGSCI (sourceserver) > info EXTRACT pcuspd1
GGSCI (sourceserver) > info EXTRACT pcuspd1, detail
GGSCI (sourceserver) > stats EXTRACT pcuspd1

=====Loading the Data from source database to target database =====
1. Create tablespaces and schema user on target database
2. Use expdp to export source schema from source database
3, Copy dumpfile from source database server to target database server
4. Use impdp to import dumpfile to same schema on target database

=====Disable Triggers and Cascade-Delete Constraints on target database =====
set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off
spool &&SCHEMA..disable_cascade_delete_constraints.sql
select 'alter table '||owner||'.'||table_name||
' disable constraint '||constraint_name||';'
from all_constraints
where delete_rule = 'CASCADE'
and owner = '&SCHEMA';
spool off
spool &SCHEMA..disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||
' disable ;'
from all_triggers
where owner = '&SCHEMA';
spool off


=====Starging the Replicat on target database =====
1. Adding Checkpoint Table
Edit GLOBALS file to Add following 2 lines:
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (targetserver) > dblogin userid ggs_owner@DBAPDEV password ********
GGSCI (targetserver) > add checkpointtable
2. Configuring the Replicat
GGSCI (targetserver) 1> edit params RCUSPD1
Replicat RCUSPD1
-------------------------------------------------------------------
-- Replicat for ORACUSP Schema
-------------------------------------------------------------------
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERID ggs_owner@DBAPDEV, PASSWORD Abcd_1234
HandleCollisions
AssumeTargetDefs
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP -- handle ddl error caused abend.
Map ORACUSP.*, Target ORACUSP.* ;

3. Adding the Replicat
GGSCI (targetserver) > ADD REPLICAT RCUSPD1, EXTTRAIL dirdat/cfipdev/oracups/r1

4. Starting the Replicat
GGSCI (sourceserver) > START Replicat RCUSPD1

5. Verifying the Replicat
GGSCI (sourceserver) > info Replicat RCUSPD1
GGSCI (sourceserver) > info Replicat RCUSPD1, detail
GGSCI (sourceserver) > stats Replicat RCUSPD1