Custom Search . . .

Sunday, December 23, 2007

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.


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);


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


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.

No comments: