Golden Gate step by step Implementation notes

Step By Step Golden Gate Configuration Step By Step Golden Gate Configuration: GoldenGate enables us to extract and replicate data across a variety of topologies as shown the diagram below as well as the exchange and manipulation of data at the transactional level between a variety of database platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc. It can support a number of different business requirements like:  Business Continuity and High Availability  Data migrations and upgrades  Decision Support Systems and Data Warehousing  Data integration and consolidation

Manager The Manager process must be running on both the source as well as target systems before the Extract or Replicat process can be started and performs a number of functions including monitoring and starting other GoldenGate processes, managing the trail files and also reporting. Extract The Extract process runs on the source system and is the data capture mechanism of GoldenGate. It can be configured both for initial loading of the source data as well as to synchronize the changed data on the source with the target. This can be configured to also propagate any DDL changes on those databases where DDL change support is available. Replicat The Replicat process runs on the target system and reads transactional data changes as well as DDL changes and replicates then to the target database. Like the Extract process, the Replicat process can also be configured for Initial Load as well as Change Synchronization. Collector The Collector is a background process which runs on the target system and is started automatically by the Manager (Dynamic Collector) or it can be configured to stsrt manually (Static Collector). It receives extracted data changes that are sent via TCP/IP and writes then to the trail files from where they are processed by the Replicat process. Trails Trails are series of files that GoldenGate temporarily stores on disks and these files are written to and read from by the Extract and Replicat processes as the case may be. Depending on the configuration chosen, these trail files can exist on the source as well as on the target systems. If it exists on the local system, it will be known an Extract Trail or as an Remote Trail if it exists on the target system. Data Pumps Data Pumps are secondary extract mechanisms which exist in the source configuration. This is optional component and if Data Pump is not used then Extract sends data via TCP/IP to the remote trail on the target. When Data Pump is configured, the Primary Extract process will write to the Local Trail and then this trail is read by the Data Pump and data is sent over the network to Remote Trails on the target system. In the absence of Data Pump, the data that the Extract process extracts resides in memory alone and there is no storage of this data anywhere on the source system. In case of network of target failures, there could be cases where the primary extract process can abort or abend. Data Pump can also be useful in those cases where we are doing complex filtering and transformation of data as well as when we are consolidating data from many sources to a central target. Data source When processing transactional data changes, the Extract process can obtain data directly from the database transaction logs (Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where the database vendor (for example Teradata) will provide the required components that will be used by Extract to extract the data changes. Groups To differentiate between the number of different Extract and Replicat groups which can potentially co-exist on a system, we can define processing groups. For instance, if we want to replicate different sets of data in parallel, we can create two Replicat groups. A processing group consists of a process which could be either a Extract or Replicat process, a corresponding parameter file, checkpoint file or checkpoint table (for Replicat) and other files which could be associated with the process. Steps :- 1. Golden Gate Setup at Source and Target 2. Configure Source :- A. Configure Schema B. Configure Manager C. Configure Extract D. Add Trandata E. Configure Pump 3. Configure Target A. Configure Manager B. Create Check point table C. Configure Replicat Database And Machine Info SOURCE Database Oracle Release: Oracle11g Release 2 – (11.2.0.1.0) Machine Name: PRODDB Operating System: Red Hat Linux 5 Oracle SID: PROD Replication Schema GGS_OWNER TARGET Database Oracle Release: Oracle11g Release 2 – (11.2.0.1.0) Machine Name: GOLDDB Operating System: Red Hat Linux 5 Oracle SID: TARGET Replication Schema GGS_OWNER ¨ Golden Gate Setup at Source and Target 1. mkdir /mount1/stfolder/ 1. $unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar 1. $ tar -xf fbo_ggs_Linux_x86_ora11g_32bit.tar 1. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg 1. [oracle@PRODDB soft]$ ls bcpfmt.tpl ddl_ora10upCommon.sql defgen freeBSD.txt libxml2.txt role_setup.sql bcrypt.txt ddl_ora11.sql demo_more_ora_create.sql ggcmd logdump sequence.sql cfg ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat marker_remove.sql server chkpt_ora_create.sql ddl_pin.sql demo_ora_create.sql ggsci marker_setup.sql sqlldr.tpl cobgen ddl_purgeRecyclebin.sql demo_ora_insert.sql help.txt marker_status.sql tcperrs convchk ddl_remove.sql demo_ora_lob_create.sql jagent.sh mgr ucharset.h db2cntl.tpl ddl_session1.sql demo_ora_misc.sql keygen notices.txt ulg.sql ddl_cleartrace.sql ddl_session.sql demo_ora_pk_befores_create.sql libantlr3c.so oggerr UserExitExamples ddlcob ddl_setup.sql demo_ora_pk_befores_insert.sql libdb-5.2.so params.sql usrdecs.h ddl_ddl2file.sql ddl_status.sql demo_ora_pk_befores_updates.sql libgglog.so prvtclkm.plb zlib.txt ddl_disable.sql ddl_staymetadata_off.sql dirjar libggrepo.so pw_agent_util.sh ddl_enable.sql ddl_staymetadata_on.sql dirprm libicudata.so.38 remove_seq.sql ddl_filter.sql ddl_tracelevel.sql emsclnt libicui18n.so.38 replicat ddl_nopurgeRecyclebin.sql ddl_trace_off.sql extract libicuuc.so.38 retrace ddl_ora10.sql ddl_trace_on.sql fbo_ggs_Linux_x86_ora11g_32bit.tar libxerces-c.so.28 reverse 1. [oracle@PRODDB soft]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. — YOU WILL GET GG COMMAND PROMPT 1. GGSCI (PRODDB) 1> create subdirs Creating subdirectories under current directory /mount1/stfolder Parameter files /mount1/stfolder/dirprm: already exists Report files /mount1/stfolder/dirrpt: created Checkpoint files /mount1/stfolder/dirchk: created Process status files /mount1/stfolder/dirpcs: created SQL script files /mount1/stfolder/dirsql: created Database definitions files /mount1/stfolder/dirdef: created Extract data files /mount1/stfolder/dirdat: created Temporary files /mount1/stfolder/dirtmp: created Stdout files /mount1/stfolder/dirout: created 1. Issue the following command to exit GGSCI. GGSCI (PRODDB) 2> exit  Configure Schema  create tablespace  create user  Give Grants  grant connect, resource 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;  grant create any table to ggs_owner;  grant insert any table to ggs_owner;  grant update any table to ggs_owner;  grant delete any table to ggs_owner;  grant drop any table to ggs_owner;  Change Parameter as per requirement  UNDO_MANAGEMENT=AUTO  UNDO_RETENTION=86400 Script : SQL> startup ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 247466368 bytes Database Buffers 167772160 bytes Redo Buffers 6094848 bytes Database mounted. Database opened. SQL> SQL> alter system set recyclebin=off scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 247466368 bytes Database Buffers 167772160 bytes Redo Buffers 6094848 bytes SQL> alter database archivelog; Database altered. SQL> alter database Open; Database altered. SQL> create tablespace ggs_data datafile ‘/u01/ggs_data01.dbf’ size 200m; tablespace created SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp; User created. SQL> grant connect, resource 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; grant create any table to ggs_owner; grant insert any table to ggs_owner; grant update any table to ggs_owner; grant delete any table to ggs_owner; grant drop any table to ggs_owner; grant drop any table to ggs_owner; SQL> show parameter undo NAME TYPE VALUE ———————————— ———– —————————— undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set UNDO_RETENTION=86400 scope=both; System altered. sql> alter database add supplemental log data; Database altered. – bounce the database SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 251660672 bytes Database Buffers 163577856 bytes Redo Buffers 6094848 bytes Database mounted. Database opened. INITIAL DATALOAD :- EXPORT: @ SOURCE $ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=ggtest Scp from SOURCE and TARGET $scp –p schema_gg.dmp 172.168.10.108:/oradata IMPORT: @ TARGET $impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=ggtest Execute following script at Source by connecting SYS user which will create required object for Golden Gate. Run scripts for creating all necessary objects for support DDL replication [oracle@PRODDB soft]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 05:42:29 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 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:GGS_OWNER Marker setup table script complete, running verification script… Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS_OWNER 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:GGS_OWNER 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. Using GGS_OWNER 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 GGS_OWNER 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 ———————————————————————————————————————— /u01/app/oracle/diag/rdbms/PROD/PROD/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:GGS_OWNER 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 where is the user assigned to the GoldenGate processes. SQL> SQL> grant ggs_ggsuser_role to ggs_owner; Grant succeeded. SQL> @ddl_enable Trigger altered. SQL> @ddl_pin GGS_OWNER PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Configure Source :- 1. Add Trandata i. Create trandata statement and execute it on gg prompt SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’GGTEST’ and object_type=’TABLE’; ‘ADDTRANDATA’||OWNER||’.’||OBJECT_NAME||’;’ ——————————————————————————– add trandata GGS_OWNER.SCOTT; add trandata GGS_OWNER.DEPT; ii. Syntax for reference add trandata . b. Configure Manager i. DBLOGIN USERID ggs_owner, PASSWORD ggs_owner ii. info all iii. edit params mgr IT Jobs in Dubai – Script :- Parameter file for MANAGER – Manager Parameter file PORT 7809 –Automatically start extract and pump as soon as manager starts –AUTOSTART EXTRACT GGEC01G1 –If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts AUTOSTART EXTRACT GGEC01G1 –,RETRIES 20 ,WAITMINUTES 2 –Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3 –Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message LAGREPORTMINUTES 60 LAGCRITICALMINUTES 20 iv. info all v. dblogin USERID ggs_owner, PASSWORD ggs_owner vi. start manager c. Configure Extract i. Create Obey file if required and put below code into that or directly execute it on gg prompt. IT Jobs in Dubai – Script :- To Add Extract –Add extract for EMP – TARGET DATABASE DBLOGIN USERID ggs_owner, PASSWORD ggs_owner DELETE GGEC01G1 ADD EXTRACT GGEC01G1, TRANLOG, BEGIN NOW ADD EXTTRAIL /mount1/stfolder/dirdat/g1, EXTRACT GGEC01G1 –, MEGABYTES 5 ii. Create parameter file by “edit params” command and put below code in the parameter file. 1. Edit params GGEC01G1 IT Jobs in Dubai – Script :- Parameter file for Extract – Parameter Syntax check ( Remove after syntax Validation ) – CHECKPARAMS EXTRACT GGEC01G1 –Source Database USERID ggs_owner, PASSWORD ggs_owner –Generate SQL output –FORMATSQL –Discard file parameters DISCARDFILE /mount1/stfolder/dirrpt/GGEC01G1.dsc, PURGE –Transaction log parameter –TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT – Local Trail File Parameters EXTTRAIL /mount1/stfolder/dirdat/g1 –Output in SQL format –To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group –CHECKPARAMS –NODYNAMICRESOLUTION – Update and delete operation parameters capture full ops image –NOCOMPRESSDELETES –NOCOMPRESSUPDATES –IGNORE DELETE, UPDATE, INSERT – IGNOREDELETES –IGNOREUPDATES –IGNOREINSERTS –Check parameter syntax also check the table syntax –after correcting the syntax, remove these 2 parameters –CHECKPARAMS –NODYNAMICRESOLUTION – Runtime parameters STATOPTIONS RESETREPORTSTATS REPORT AT 00:01 REPORTROLLOVER AT 00:01 REPORTCOUNT EVERY 1 HOUR, RATE –Wildcard table processing parameters WILDCARDRESOLVE DYNAMIC DDL INCLUDE MAPPED – GG data capture table list: EMP TABLE GGS_OWNER.EMP; d. Configure Pump i. Create Obey file if required and put below code into that or directly execute it on gg prompt. IT Jobs in Dubai – Script :- To Add Extract –Add extract for EMP – TARGET DATABASE DBLOGIN USERID ggs_owner, PASSWORD ggs_owner – EXTRACT PUMP DELETE GGPC01G1 ADD EXTRACT GGPC01G1, EXTTRAILSOURCE /mount1/stfolder/dirdat/g1 ADD RMTTRAIL /mount1/stfolder/dirdat/1g, EXTRACT GGPC01G1 ii. Create parameter file by “edit params” command and put below code in the parameter file. 1. Edit params GGPC01G1 IT Jobs in Dubai – Script :- Parameter file for Pump – Parameter Syntax check ( Remove after syntax Validation ) – CHECKPARAMS EXTRACT GGPC01G1 –Data Pump mode (This should be commented) PASSTHRU – Control Parameters RMTHOST 10.184.93.74, MGRPORT 7809, COMPRESS –Remote Trail File parameter RMTTRAIL /mount1/stfolder/dirdat/1g –To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group –CHECKPARAMS –NODYNAMICRESOLUTION –Wildcard table processing parameters WILDCARDRESOLVE DYNAMIC – GG data pump table list: EMP TABLE GGS_OWNER.EMP; –TABLE EMP FILTER(ORANGE(1,2)); Configure Target :- 1. Edit GLOBAL parameter file by connecting gg prompt i. edit params ./GLOBAL GGSCHEMA ggs_owner CHECKPOINTTABLE ggs_owner. Checkpoint Configure Manager i. DBLOGIN USERID ggs_owner, PASSWORD ggs_owner ii. info all iii. edit params mgr IT Jobs in Dubai – Script :- Parameter file for MANAGER – Manager Parameter file PORT 7809 –Automatically start extract and pump as soon as manager starts –AUTOSTART EXTRACT GGPC01G1 –If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts AUTOSTART EXTRACT GGPC01G1 –,RETRIES 20 ,WAITMINUTES 2 –Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3 –Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message LAGREPORTMINUTES 60 LAGCRITICALMINUTES 20 iv. info all v. dblogin USERID ggs_owner, PASSWORD ggs_owner vi. start manager 1. Create Check point table i. dblogin userid ggs_owner password ggs_owner ii. add checkpointtable ggs_owner.checkpoint Configure Replicat i. Create Obey file if required and put below code into that or directly execute it on gg prompt. IT Jobs in Dubai – Script :- Replicat Obey File – login to the database DBLOGIN USERID ggs_owner, PASSWORD ggs_owner – Add replicat DELETE GGRC01G1 ADD REPLICAT GGRC01G1, EXTTRAIL /mount1/stfolder/dirdat/1g, checkpointtable ggs_owner.checkpoint ii. Create parameter file by “edit params” command and put below code in the parameter file. IT Jobs in Dubai – Script :- Parameter file for Replicat –Following 3 options are to display SQL statements comment out BATCHSQL –SHOWSYNTAX –NODYNSQL –NOBINARYCHARS – –NOLIST will not expand the macros in the report file –INCLUDE the macro library –LIST is end of NOLIST – REPLICAT GGRC01G1 – Handle the collisions after the initial load HANDLECOLLISIONS – TARGET DATABASE –#DB_Connect() USERID ggs_owner, PASSWORD ggs_owner –Discard file parameters DISCARDFILE /mount1/stfolder/dirrpt/GGEC01G1.dsc, PURGE – –Source and Target table parameters ASSUMETARGETDEFS –SOURCEDEFS C:\GG\DIRSQL\MYTABLES.SQL – –To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. –CHECKPARAMS –NODYNAMICRESOLUTION – Performance parameters for <5k bytes per row data change – Replaced “BATCHSQL” with “BATCHSQL BATCHTRANSOPS … “ BATCHSQL BATCHTRANSOPS 10000 –Running reports parameters –#generate_stats() REPORTCOUNT EVERY 1 HOUR, RATE STATOPTIONS RESETREPORTSTATS REPORT AT 00:01 REPORTROLLOVER AT 00:01 – Wildcard table processing parameter WILDCARDRESOLVE DYNAMIC TRANSACTIONTIMEOUT 5 S –ddl support DDL INCLUDE ALL DDLERROR DEFAULT IGNORE RETRYOP –Specify table mapping — MAP GGS_OWNER.*, TARGET GGS_OWNER.*; MAP GGS_OWNER.EMP, TARGET GGS_OWNER.EMP; START EXTRACT :- NEED TO BE CARRIED OUT AT SOURCE 1. START MGR 2. START EXTRACT GG* START REPLICAT :- NEED TO BE CARRIED OUT AT TARGET 1. START MGR 2. START replicat GG* Some more scripts Golden Gate script