Replication + Logical

Replication –Logical things

I was facing some new thing in replication. I want to share my experience to you.

Actually my master and materialized view site its different oracle version. Master size 10.2.0.3 and materialized view site one is 10.2.0.3 and another one is 9.2.0.8.

One day I dropped one materialized view and again re-created materialized view because I add some column in base table in mater site. But dropped materialized view objects name I’m not removed in replication group. So, no need to re-add materialized view name in replication group.

Once completed everything I try to transfer data. I’m getting the below error message.

Error 2091 trapped in 2PC on transaction 8.10.95082. Cleaning up.

Error stack returned to user:

ORA-02091: transaction rolled back

ORA-02291: integrity constraint (SAMIINVENTORY.FK_GRNMST_SUP) violated - parent key not found

Error 3113 trapped in 2PC on transaction 6.44.93771. Cleaning up.

Tue Jan 01 18:34:36 2008

DISTRIB TRAN KNGDB.SAMIINDIA.6db37e82.6.44.93771

is local tran 6.44.93771 (hex=06.2c.16e4b)

insert pending collecting tran, scn=379132697 (hex=0.16991b19)

Tue Jan 01 18:34:36 2008

Error stack returned to user:

ORA-02050: transaction 6.44.93771 rolled back, some remote DBs may be in-doubt

ORA-03113: end-of-file on communication channel

ORA-23324: error ORA-00604: error occurred at recursive SQL level

ORA-02063: preceding line from SAMIDB, while creating deferror entry at "SAMIDB.SAMIINDIA" with error -604

Tue Jan 01 18:34:41 2008

Actually problem is while transferring data, some transaction roll backing its called 2PC on transaction error. But you can able to check what are pending transaction using DBA_2PC_PENDING and DBA_2PC_NEIGHBORS. 

But in my case NO values return (I know it’s happens). We found two solutions.

• Deferred Constraints

In master site we have lot referable tables (parent and child table). Here no need to set deferrable constraints but the same we are replicated in materialized view site. Here we should be use deferrable constraints in parent and child tables.

Because replication is going on for a refresh group; we have no control over the order in which the tables inside the refresh group gets refreshed. Sometimes, child tables may get refreshed first and then parent tables. So in such cases we need to defer the enforcement of the constraint till commit time. Hence we need to use Deferrable constraints.

• DBMS_MVIEW.I_AM_A_REFRESH

Application related reasons, we don’t want the trigger (especially those which update new table (this table it’s not replicated)) to execute while data comes from other materialized view site. So to see whether the replication operation is going on or not, we are using the above mentioned function. Do note that our triggers are NOT replicated triggers, we are creating triggers locally. Also our stock table is a local table, NOT a replicated table.