Tag: Oracle Golden Gate

Overview:
Oracle Golden Gate provides very fast replication of data by reading transaction logs and writing the changes to one or more target databases in a homogeneous and heterogeneous environment. It is useful for High Availability Architectures and especially for Data Warehouse and Decision Support Systems. Thus, the variety of techniques and methods spreads from unidirectional environments for query offloading/reporting to bidirectional or Peer-to-Peer architectures in an active-active fashion.

Prerequisites:
Oracle Golden Gate Software Installed
Database should be in archive log mode
Supplemental logging should be enable.
Force logging should be set to yes. 


Environment Details:

 
Steps to setup Oracle Golden Gate One way Replication:
 1. Install Oracle Golden Gate Software, refer below link.

2. Upgrade Oracle Golden gate version to latest release, Refer following link.

Oracle Golden Gate upgrade on Windows Server

3. Verify archive log mode, then enable the archive log mode.

4. Verify force logging and supplemental logging are set to ‘YES’ and Enable goldengate replication.

GGSCI  5> edit param ./GLOBALS

6. Set db_recovery_file_dest_size parameter.

7. Create golden gate database tablespace, user account and grant permissions.

8. Enable DDL replication, as sysdba run the following in sequence providing ggadmin as schema.

9. Login as sysdba and execute role_setup then grant GGS_GGSUSER_ROLE then ddl_enable and ddl_pin.

10. Enable sequence replication, as sysdba run the following script from gg home.

11. Enable trandata for the tables to be replicated, login to database from ggsci.
GGSCI (  as ggadmin@****) 4> add trandata <Schema_name>.*,

GGSCI (  as ggadmin@****) 5> add trandata <Schema_name>.*,

Prepare Source environment
 
12. Create parameter file for manager

PORT 7809
purgeoldextracts ./dirdat/shelldb/sh*, usecheckpoints, minkeepdays 2
lagreportminutes 1
laginfominutes 10
lagcriticalminutes 90

— delay starting other process after rebooting servers by 30min
–bootdelayminutes 20
–autostart ER *

— auto start pump processes to startup if network failed
autorestart EXTRACT p*, retries 4, waitminutes 10
autorestart EXTRACT e*, retries 4, waitminutes 10

13. Verify manager parameter file.

14. Create PARAM file for primary extract (e_shl01s).
EXTRACT e_shl01s
— add extract e_shl01s, tranlog, begin 2015-04-28, threads 2
— add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

— add extract e_shl01s, tranlog, begin now, threads 2
— add exttrail ./dirdat/****/sh, extract e_shl01s, megabytes 50

discardfile ./dirrpt/discard/e_shl01s.dsc, append megabytes 50

SETENV (ORACLE_SID=’****’)
SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
–SETENV (ORACLE_HOME=D:oracle11204product11.2.0dbhome_1)

userid ggadmin, password ggadmin123

— to read from asm
— tranlogoptions dblogreader
— tranlogoptions ASMUSER SYS@ASM1, ASMPASSWORD GCSasmadmin2015

— to prevent looping in bidirectional replication or you can user excludetag
tranlogoptions excludeuser ggadmin

exttrail ./dirdat/shelldb/sh
cachemgr cachesize 1GB
fetchoptions usesnapshot, uselatestversion
FETCHOPTIONS FETCHPKUPDATECOLS
dboptions allowunusedcolumn

include ./dirprm/include_reporting.inc
–warnlongtrans 3H, chekinterval 1H

logallsupcols

ddl include all
ddloptions addtrandata, report

–include ./dirprm/HB_Extract.inc

table ggadmin.ggsync;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table test_user.*;

sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence test_user.*;

15. Create PARAM file for pump extract (p_shl01s).

extract p_shl01s
— add extrat p_shl01s, exttrailsource ./dirdat/****/sh
— add rmttrail ./dirdat/****/th, extract p_shl01s, megabytes 50
discardfile ./dirrpt/discard/p_shl01s.dsc, append megabytes 50
passthru
rmthost 10.10.10.10  mgrport 7809
rmttrail ./dirdat/****/th
include ./dirprm/include_reporting.inc

–include ./dirprm/HB_pmp.inc

table ggadmin.ggsync;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table <Schema_name>.*;
table test_user.*;

sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence <Schema_name>.*;
sequence test_user.*;

16. View param file of pump extract.

17. Add primary extract

18. Add pump extract.

19. Add checkpoint table.

Prepare Target Environment
 
19. Repeat the steps from 1 to 11 on Target site.

20. Create parameter file for manager on target site.

replicat r_shl01t
— add replicat r_shl01t, exttrail ./dirdat/****/th
discardfile ./dirrpt/discard/r_shl01t.dsc, append megabytes 50

SETENV (ORACLE_SID=’****’)
SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)
–SETENV (ORACLE_HOME=C:oraclerdbms11gproduct11.2.0dbhome_1)

userid ggadmin, password ggadmin123
AssumeTargetDefs
— for triggers starting from 11.2.0.2 oracle automatically disable trigger fire
dboptions suppresstriggers
— for cascading const
dboptions deferrefconst

DDL include all
–DDLSUBST ‘SYS_C0040303’ WITH ‘SYS_C0040298’
–ddlerror 1031 ignore
grouptransops 1000

include ./dirprm/include_reporting.inc

–include ./dirprm/HB_Rep.inc
APPLYNOOPUPDATES

map ggadmin.ggsync, target ggadmin.ggsync;
Map <Schema_name>.*, Target <Schema_name>.*;
Map <Schema_name>.*, Target <Schema_name>.*;
Map <Schema_name>.*, Target <Schema_name>.*;
Map <Schema_name>.*, Target <Schema_name>.*;
Map <Schema_name>.*, Target <Schema_name>.*;
Map <Schema_name>.*, Target <Schema_name>.*;
Map test_user.*, Target test_user.*;

22. View replicate parameter and start the process.

Conclusion
In above article we have learned that, how to setup Oracle Golden Gate One way Replication on windows Server, where we have prepared Source and Target sides with respective extract process.

BY
Name: Mirza Hidayathullah Baig
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com