Replication Troubleshooting

While transferring data from materialized view site end. I was faced the below error message.

ORA-12012: error on auto execute of job 2
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Oracle suggest to fix this error automatically using “refresh_after_errors= true”

MviewAdmin@Materialized view site.

BEGIN

DBMS_REFRESH.CHANGE(
name => '"MVIEWADMIN"."MIS_REFG"',
next_date => to_date('01-01-2020 06:06:08','DD-MM-YYYY HH24:MI:SS'),
interval => 'SYSDATE + 1 + (1/24)',
implicit_destroy => FALSE,
rollback_seg => 'NULL',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);

END;

By logically it’s not good. You should be find why data’s not transfer? Any problem in your database?

Sys@MasterSite

Here you can find out those problems using the following data dictionary views,

SQL> select deferred_tran_id, destination, error_number, error_msg from deferror;

Using deferred transaction id, you can find out which transaction not updated in materialized view site. Fix those errors. After that only transfer your data’s.

Please post your comments.