INSERTMISSINGUPDATES and NOINSERTMISSINGUPDATES

One of the member in the Oracle Community raised the below query. So I tested it and the solution is given below,

Suppose if i am deleting some records in Target Database like(one month data i deleted).Same data if i try to update in source database what will happen?
I tried this scenario in our environment,as i expected no change in target database(no error).If it has to update in Target database ,the data should be there.Now what my question is ,how should i replicate the deleted data in Target?is there any configuration available in OGG.I hope so ,i explained clearly.Please let me know if you need any details.Plesae help me for solution.

INSERTMISSINGUPDATES – NOINSERTMISSINGUPDATES

INSERTMISSINGUPDATES

This parameter should be added to the Replicat. It means, this is VALID only for REPLICAT Process and should be used only when the Source database logs all column values.

ADD SCHEMATRANDATA SCOTT ALLCOLS
ALLCOLS – Enables the unconditional supplemental logging of all supported key and non-key columns for all current and future tables in the given schema.

When this parameter is added to the Replicat file, it inserts a record based on the Source record when the target record does not exist.

This means, If a record is updated in Source, On the target the same record will be inserted if the record does not exists instead a missing record error or REPLICAT ABEND.
This parameter also supports the database which is using the COMPRESSED form of updates (where only changed values are logged instead of logging all columns) provided that the NULL can be used for missing column values on the target database.

 

NOINSERTMISSINGUPDATES

This parameter is a default one. When this is in place, whenever a record is updated on the Source side, and that record is not present in the Target then this leads to a Missing Record Error and the Transaction might ABEND depending on the REPERROR parameter setting in the REPLICAT file.

 

In General, these parameters are specific to tables. A single parameter will be in effect for all the MAP statements until the other parameter is detected or encountered by the Process.

 

The below example shows how the INSERTMISSINGUPDATES works.,

GoldenGate Version – 12.1.2.0.0
Oracle Version – 11.2.0.4.0
OS Version – Oracle Enterprise Linux 6
Replicat Type – Integrated
Schema Used – SCOTT
Table Used – DEPT

I have used the INSERTMISSINGUPDATES in my Replicat Parameter File.

Replicat REP1
userid ggadmin, password *******
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
DiscardFile /ogg/dirrpt/rep1.dsc, Purge
INSERTMISSINGUPDATES
Map scott.*, target scott.*;

ON SOURCE

Check the records on the Source table.

SQL> select * from dept; DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

ON TARGET

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

Now I am going to delete a record on the Target Side,.

SQL> delete from dept where deptno=50;
1 row deleted.
SQL> commit;
Commit complete.

Check if the record has been deleted or not.,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON

Now in Target we have only 4 rows. One row is deleted. Now I am going to process an update in the source side in the row which I have deleted in the Target side.

 

ON SORUCE

The records on the Source is as below,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     INDIA

Now update the record.,

SQL> update dept set LOC=’WASHINGTON’ where deptno=50;
1 row updated.
SQL> commit;
Commit complete.

Check if the update has been reflected.,

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
10          ACCOUNTING   NEW YORK
20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON
50          IT                     WASHINGTON

 

ON TARGET

Check the rows on the target table.

SQL> select * from dept;DEPTNO DNAME             LOC
———-    ————–          ————-
50          IT                     WASHINGTON
10          ACCOUNTING   NEW YORK

20          RESEARCH       DALLAS
30          SALES              CHICAGO
40          OPERATIONS   BOSTON

You can see the row which I have updated in Source has been inserted in Target side..

CONCLUSION

INSERTMISSINGUPDATES – Using this parameter we can handle the Missing record error on the TARGET – REPLICAT.

We can clearly see that, whenever an update is made in the SOURCE, the record got inserted in the TARGET table, eventhough it was not present in the TARGET table.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *