ORA-12034 and Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo

1. Refresh group failed with  FAST REFRESH mode .

SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');
BEGIN dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"'); END;

*
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view
"ARBOR"."EXTERNAL_ID_EQUIP_MAP"
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


2. So I refresh the mview directly,still failed.
 
09:08:41 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false);
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 00:00:00.55

 3. Than I try complete refresh, 16GB UNDO was not sufficient.

SQL> set time on timing on
11:05:16 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 03:26:13.56


4. It stops growing until 37GB after I enable autoextensible, but refresh job can't complete after running for 46 hours, and it took anther 6.5 hours for rollback.

14:54:35 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');

c

BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 52:26:01.08

As the mview contains 6.3 millions rows.

10:05:38 SQL> select count(*) from "ARBOR"."EXTERNAL_ID_ACCT_MAP";

  COUNT(*)
----------
   6382386



5. Finally I found ATOMIC_REFRESH=>false, and gave it a try. This works in 26 minutes!

09:08:44 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:25:12.26
 
11:08:57 SQL> select * from user_mview_refresh_times;

OWNER          NAME                           MASTER_OWNER
-------------- ------------------------------ --------------
MASTER                         LAST_REFRESH
------------------------------ --------------------


ARBOR          EXTERNAL_ID_EQUIP_MAP          ARBOR
EXTERNAL_ID_EQUIP_MAP          29-jun-2012 09:09:06

...
6 rows selected.
 
6. The subsequent FAST REFRESH is also succeed.


11:09:01 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.99
11:09:28 SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75


7. Key timestamp in dictionary views


mview site: 


11:12:43 SQL> select last_refresh from dba_mview_refresh_times where name='EXTERNAL_ID_EQUIP_MAP';

LAST_REFRESH
--------------------
29-jun-2012 11:12:43

master site:

ORACLE1> select CURRENT_SNAPSHOTS from dba_snapshot_logs where MASTER='EXTERNAL_ID_EQUIP_MAP';

CURRENT_SNAPSHOTS
--------------------
29-jun-2012 11:12:43

ORACLE1> select LAST_PURGE_DATE from sys.mlog$ where MASTER='EXTERNAL_ID_EQUIP_MAP';

LAST_PURGE_DATE
--------------------
29-jun-2012 11:12:43
 


8. Findings:



The reason of ORA-12034 is due to both mview and master site are cloned database, and the date cut date of mview site is older than master site, caused the out-of-sync in between mview and mview log.


References:

Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo Activity [ID 413188.1]

 http://aprakash.wordpress.com/2010/11/05/mview-complete-refresh-is-it-slow-high-redo-generation-oracle-10g/

http://gavinsoorma.com/2009/07/10g-materialized-view-complete-refresh-using-atomic_refresh/