Oracle Goldengate Replication Solutions Overview
Explore various Oracle replication solutions like Oracle GoldenGate and Streams Replication for efficient data propagation and synchronization. Learn about setting up replication processes, handling conflicts, and utilizing administrative options. Dive into the intricacies of replicat processes, extract trails, and data loading techniques for seamless data replication in Oracle databases.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Oracle Goldengate Replicat
Oracle Streams Replication Source Database Target Database Propagate Capture Apply Redo Logs
GG architecture 10.x (2010) Source Database Target Database Redo Logs Replicat Extract Datapump Trail Files Trail Files
GG architecture 11.x(2011) Source Database Target Database Logmining Server Redo Logs Extract Replicat Integrated Extract Datapump Trail Files Trail Files
GG architecture 12.1 (2013) Source Database Target Database Logmining Server Apply Redo Logs Integrated Replicat Replicat Extract Integrated Extract Datapump Trail Files
Integrated Replicat 12.1 Applies changes through database build-in preparer-coordinator- servers mechanism Requirement: RDBMS >= 11.2.0.4 File IO LCR SQL Target Database Trail Files Server Coordinator Prepare Receiver Server Replicat Server Inbound 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
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 AssumeTargetDefs assuming both table metadata is same Discard the Data to discard file when the target rows are not matched HandleCollisions To handle the data conflicts SourceDef If metadata i.e column structure is different you specify the definition file location ReportCount Report the replication statistics to report file Every n Minutes, Rate Report at HH:24 Report at what time ReportRollover at 01:15 - Rotate the report file at what time
Example Replicat Admin Parameters USERID GGS_OWNER@ORA11TRG, PASSWORD g HANDLECOLLISIONS AssumeTargetDefs ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV3.dsc, Append DiscardRollover 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@ORA11TRG, PASSWORD g HANDLECOLLISIONS AssumeTargetDefs ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV2.dsc, Append DiscardRollover at 02:00 ON SUNDAY Map HR.COUNTRIES, Target HR.COUNTRIES; Map HR.REGIONS, Target HR.REGIONS;
Replicat Entire Schema - MAP USERID GGS_OWNER@ORA11TRG, PASSWORD g HANDLECOLLISIONS AssumeTargetDefs ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover 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@ORA11TRG, PASSWORD g HANDLECOLLISIONS AssumeTargetDefs SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.sql ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover 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@ORA11TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.sql ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover 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@ORA11TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.sql ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover at 02:00 ON SUNDAY Map HR.EMP, Target HR.EMP, FILTER (@STRFIND (job, MANAGER ) ) COLMAP (usedefaults,salary = sal),
Replicat - RepError 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 rep1 REPLICAT REP1 USERID GGS_OWNER@ORA11TRG, PASSWORD g HANDLECOLLISIONS SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.sql ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover 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 - ResolveConflict ResolveConflict 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 rep1 REPLICAT REP1 USERID GGS_OWNER@ORA11TRG, PASSWORD g SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.sql ReportCount Every 30 Minutes, Rate Report at 01:00 ReportRollover at 01:15 DiscardFile dirrpt/RHRDEV1.dsc, Append DiscardRollover 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) RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))), RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
Replicat Adding Trails If its initial Loading, use GGSCI> ADD REPLICAT RHRDEV1, SPECIALRUN If its continues change process. GGSCI> ADD REPLICAT RHRDEV1, EXTTRAIL dirdat/l2, CHECKPOINTTABLE HR.GG_CHKPT Here, Replicat RHRDEV1 looks at trail location dirdat/l2 and start replicating and records the replication progress in checkpoint table HR.GG_CHKPT
Replicat - Starting If a Replicat to start from beginning of trail file GGSCI> start replicat RHRDEV1 If a Replicat to start from specific sequence trail file, first alter it GGSCI> alter replicat RHRDEV1, EXTSEQ 10 or specific RBA GGSCI> alter replicat RHRDEV1, EXTRBA 439393 Then start GGSCI> START REPLICAT RHRDEV1
Replicat Monitoring - Status GGSCI (devu007) 2> info replicat myload2 REPLICAT MYLOAD2 Last Started 2016-09-05 11:05 Status RUNNING Checkpoint Lag 00:00:03 (updated 00:11:44 ago) Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/bb000010 2016-09-05 11:31:03.999504 RBA 1225 Replicat Name : MYLOAD2 Started on : 2016-09-05 11:05 Status : RUNNING/ABENDED/STOPPED/INTIALIZED CheckPointLag: 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 (devu007) 37> stats replicat rep2 reportrate hr Sending STATS request to REPLICAT REP2... Start of Statistics at 2010-02-26 10:04:46. Output to /u01/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 - GetLag GGSCI> lag replicat rep2 Lag 00:10:00 Time Since Chkpt 00:17:56 Log Read Checkpoint File ./dirdat/t0000062 Shows the Lag is 10 Mins and since chkpoint read is 17 mins and reading t000062 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 from V$GG_APPLY_READER; APPLY_NAME STATE ------------------------------ ------------------------------------ TOTAL_MESSAGES_DEQUEUED TOTAL_MESSAGES_SPILLED ----------------------- ---------------------- OGG$MYREP1 IDLE 1000005 0 SQL> select APPLY_NAME,TOTAL_APPLIED, TOTAL_RECEIVED from V$GG_APPLY_COORDINATOR; APPLY_NAME TOTAL_APPLIED TOTAL_RECEIVED ------------------------------ ------------- -------------- OGG$MYREP1 5 5 SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ; APPLY_NAME ------------------------------ --------------------- OGG$MYREP1 STATE IDLE
Issue 1 : Data Conflicts Data conflicts arises when Ora-1401 no datafound Ora-001 Primary/Unique Key Violation To Solve this GGSCI> start replicat rep2, handlecollisions or GGSCI> start replicat rep2, skiptransactions or Include Handlecollision in parameter file and start replicat GGSCI> edit params rep2 REPLICAT REP2 . 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: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 REP2, EXTSEQNO 36, EXTRBA 0 EXTSEQNO tell to read 36 file EXTRBA 0 tell to read from Starting of file.
