Oracle Goldengate Replicat Oracle Streams Replication Source Database

Oracle Goldengate Replicat

Oracle Streams Replication Source Database Target Database Propagate Redo Logs Capture Apply

GG architecture 10. x (2010) Source Database Target Database Redo Logs Replicat Extract Trail Files Datapump Trail Files

GG architecture 11. x(2011) Source Database Target Database Logmining Server Redo Logs Integrated Extract Trail Files Extract Replicat Datapump Trail Files

GG architecture 12. 1 (2013) Source Database Target Database Logmining Server Apply Redo Logs Integrated Extract Replicat Extract Datapump Integrated Replicat Trail Files

Integrated Replicat 12. 1 • Applies changes through database build-in preparer-coordinatorservers mechanism • Requirement: RDBMS >= 11. 2. 0. 4 File IO LCR SQL Target Database Trail Files Server Receiver Replicat Inbound Server Prepare Coordinator Server

Setting Up Replication • Step 1 – Set the Parameter File • Step 2 – Add the replicat process to read the extract file • Step 3 – Start the replicat process

Step 1 – Set the Parameters

Replicat • Replicat process reads the extract trails and apply • Replicat can be used for – Initial Data Loading (sourceistable) – Continue Change Processing (Extrail) • Replicat process while applying the changes use different options , classified as – Administrative Options – Data Replicat Options

Replicat Administrative Options • Assume. Target. Defs – assuming both table metadata is same • Discard the Data to discard file when the target rows are not matched • Handle. Collisions – To handle the data conflicts • Source. Def – If metadata i. e column structure is different you specify the definition file location • Report. Count – Report the replication statistics to report file Every n Minutes, Rate • Report at HH: 24 – Report at what time • Report. Rollover at 01: 15 - Rotate the report file at what time

Example Replicat Admin Parameters USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS Assume. Target. Defs Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 3. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY …. Cont. .

Replicat Data Related Parameters • MAP – To Map the Source/Target Objects • COLMAP – Column Level Mapping Source To Target • FILTER – To Filter the data before replication • Range – For Parallel replication process to read same trail file by different replicat process • RESOLVECONFLICT – Options for data missing/exists

Replicat – Specific Tables Only – MAP USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS Assume. Target. Defs Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 2. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. COUNTRIES, Target HR. COUNTRIES; Map HR. REGIONS, Target HR. REGIONS;

Replicat – Entire Schema - MAP USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS Assume. Target. Defs Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. *, Target HR. *; Alternatively if you have different schema you can specify Map HR. *, Target HRDUPLICATE. *;

Replicat – Table with different Column Structure than Source • Step 1 – On Extract side create a definition file using defgen utility – Copy the definition file to replicat side • Step 2 – Edit replicat parameter file, remove assumetargetdef and keep sourcedef parameter USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS Assume. Target. Defs SOURCEDEFS /u 01/oracle/software/goldengate/dirsql/myemp. s ql Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. EMP, Target HR. EMP;

Replicat – COLMAP • Assuming there is change in column name of Salary – Source Column name in EMP table is Salary – Target Column name in EMP table is sal The following COLMAP parameter tells usedefaults, all columns are same, except See Salary as Sal instead USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u 01/oracle/software/goldengate/dirsql/myemp. sql Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. EMP, Target HR. EMP, COLMAP (usedefaults, salary = sal);

Replicat – Filter Data • Filtering of Data can be done extract / replicat level In the following case, replicat will read the extract file and look only records for the Job match as MANAGER and replicate those records only. USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u 01/oracle/software/goldengate/dirsql/myemp. sql Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. EMP, Target HR. EMP, FILTER (@STRFIND (job, “MANAGER”) ) COLMAP (usedefaults, salary = sal),

Replicat - Rep. Error • • At times, if we aware there may be data mismatch and some errors are obvious we can specify those ORA errors in parameter to ignore and continue In this case – – – Removed the handlecollision parameter so it wont ignore for all errors and continue But specific errors according to REPERROR parameter as below when replicat encounter ORA-1401 and ORA-001 it will ignore and continue, rest all errors will be sent to discard file • dgsci> Edit params rep 1 REPLICAT REP 1 USERID GGS_OWNER@ORA 11 TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u 01/oracle/software/goldengate/dirsql/myemp. sql Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. EMP, Target HR. EMP, FILTER (@STRFIND (job, “MANAGER”) ) COLMAP (usedefaults, salary = sal), -- Specify error handling rules: -- writes operations that could not be processed to a discard file REPERROR (DEFAULT, DISCARD) REPERROR (1401, IGNORE) REPERROR(001, IGNORE)

Replicat - Resolve. Conflict • Resolve. Conflict parameter can be used to manage the data whether the same row exists what to do, if not exists what to do, before applying rather reperror and skip errors dgsci> Edit params rep 1 REPLICAT REP 1 USERID GGS_OWNER@ORA 11 TRG, PASSWORD g SOURCEDEFS /u 01/oracle/software/goldengate/dirsql/myemp. sql Report. Count Every 30 Minutes, Rate Report at 01: 00 Report. Rollover at 01: 15 Discard. File dirrpt/RHRDEV 1. dsc, Append Discard. Rollover at 02: 00 ON SUNDAY Map HR. EMP, Target HR. EMP, FILTER (@STRFIND (job, “MANAGER”) ) COLMAP (usedefaults, salary = sal), REPERROR (DEFAULT, DISCARD) REPERROR (1401, IGNORE) REPERROR(001, IGNORE)

Step 2 – Add the extract trail to replicat process

Replicat – Adding Trails • If its initial Loading, use GGSCI> ADD REPLICAT RHRDEV 1, SPECIALRUN • If its continues change process. GGSCI> ADD REPLICAT RHRDEV 1, EXTTRAIL dirdat/l 2, CHECKPOINTTABLE HR. GG_CHKPT Here, Replicat RHRDEV 1 looks at trail location dirdat/l 2 and start replicating and records the replication progress in checkpoint table HR. GG_CHKPT

Step 3 – Start the Replicat

Replicat - Starting • If a Replicat to start from beginning of trail file GGSCI> start replicat RHRDEV 1 • If a Replicat to start from specific sequence trail file, first alter it GGSCI> alter replicat RHRDEV 1, EXTSEQ 10 or specific RBA GGSCI> alter replicat RHRDEV 1, EXTRBA 439393 Then start GGSCI> START REPLICAT RHRDEV 1

Monitoring Replicat

Replicat – Monitoring - Status GGSCI (devu 007) 2> info replicat myload 2 REPLICAT MYLOAD 2 Last Started 2016 -09 -05 11: 05 Status RUNNING Checkpoint Lag 00: 03 (updated 00: 11: 44 ago) Log Read Checkpoint File /u 01/oracle/software/goldengate/dirdat/bb 000010 2016 -09 -05 11: 31: 03. 999504 RBA 1225 Replicat Name : MYLOAD 2 Started on : 2016 -09 -05 11: 05 Status : RUNNING/ABENDED/STOPPED/INTIALIZED Check. Point. Lag: How much lag i. e the replicat behind i. e 3 seconds ago Current Trail File & Location: the current sequence is 10 RBA: Redo Byte Address that is reading in Current Log file

Replicat – Monitoring - Statistics View processing rate - can use 'hr', 'min' or 'sec' as a parameter GGSCI (devu 007) 37> stats replicat rep 2 reportrate hr Sending STATS request to REPLICAT REP 2. . . Start of Statistics at 2010 -02 -26 10: 04: 46. Output to /u 01/oracle/ggs/dirdat/cc: Extracting from SH. CUSTOMERS to SH. CUSTOMERS: *** Total statistics since 2010 -02 -26 09: 29: 48 *** Total inserts/hour: 0. 00 Total updates/hour: 95258. 62 Total deletes/hour: 0. 00 Total discards/hour: 0. 00 Total operations/hour: 95258. 62 *** Latest statistics since 2010 -02 -26 09: 29: 48 *** Total inserts/hour: 0. 00 Total updates/hour: 95258. 62 Total deletes/hour: 0. 00 Total discards/hour: 0. 00 Total operations/hour: 95258. 62 End of Statistics.

Replicat – Monitoring - Get. Lag GGSCI> lag replicat rep 2 Lag 00: 10: 00 Time Since Chkpt 00: 17: 56 Log Read Checkpoint File. /dirdat/t 0000062 Shows the Lag is 10 Mins and since chkpoint read is 17 mins and reading t 000062 file

Replicat – Monitoring – Integrated Replicat Status For integrated Replicat the Replicat can be checked by using V$views like below. SQL> select apply_name, state, TOTAL_MESSAGES_DEQUEUED, TOTAL_MESSAGES_SPILLED APPLY_NAME STATE -----------------------------------TOTAL_MESSAGES_DEQUEUED TOTAL_MESSAGES_SPILLED ------------OGG$MYREP 1 IDLE 1000005 0 from V$GG_APPLY_READER; SQL> select APPLY_NAME, TOTAL_APPLIED, TOTAL_RECEIVED from V$GG_APPLY_COORDINATOR; APPLY_NAME TOTAL_APPLIED TOTAL_RECEIVED ---------------------OGG$MYREP 1 5 5 SQL> select apply_name, state from V$GG_APPLY_COORDINATOR ; APPLY_NAME STATE ---------------OGG$MYREP 1 IDLE

General Issues

Issue 1 : Data Conflicts • Data conflicts arises when – Ora-1401 – no datafound – Ora-001 – Primary/Unique Key Violation To Solve this GGSCI> start replicat rep 2, handlecollisions or GGSCI> start replicat rep 2, skiptransactions or Include Handlecollision in parameter file and start replicat GGSCI> edit params rep 2 REPLICAT REP 2 …. HANDLECOLLISIONS … … MAP HR. EMP TARGET HR. EMP GGSCI> start replicat

Issue 2 : Missing Extract File or Corrupted Extract File • Replicat may fail if Extract trail is missed or corrupted, in this cases start the replicat from specific point once extract files are recreated from source side. • Example 1 , To read from trail file sequence 53 – ALTER REPLICAT finance, EXTSEQNO 53 • Example 2 , to read from Start of the Extract file i. e from begining – ALTER REPLICAT finance, EXTRBA 0 • Example 3 , to read from specific time – ALTER REPLICAT finance, BEGIN 2011 -01 -07 08: 00 • Example 4 , for integrated Replicat to read from internally – ALTER REPLICAT finance, INTEGRATED

Issue 3 – Etrollover performed on Extract side • Some times due to extract side issues, there may be case that new extract files need to be generated with etrollover, but on replication side we must tell from which file it should start read , disregard the old extract files. GGSCI > alter REPLICAT REP 2, EXTSEQNO 36, EXTRBA 0 EXTSEQNO tell to read 36 file EXTRBA 0 tell to read from Starting of file.

Thank you!