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/

Deinstall 9.2.0.5 intermedia

Follow these steps to deinstall Oracle interMedia:

1) Start SQL*Plus and connect as SYSDBA.

2) Invoke the imdinst.sql script to render Oracle interMedia inactive:

-> SQL> @/ord/im/admin/imdinst.sql (on UNIX)
        @\ord\im\admin\imdinst.sql (on Windows NT)

3) Drop all tables and types that depend on interMedia object types.

4) Invoke the imdtyp.sql script to drop all the interMedia object types.

-> SQL> @/ord/im/admin/imdtyp.sql (on UNIX)
        @\ord\im\admin\imdtyp.sql (on Windows NT)


Encounter the following error message in 9.2.0.5 when running imdtyp.sql. Problem solved by re-run it.

More info about the dependencies example can be found here


SQL> @imdtyp

Session altered.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Indextype dropped.


Operator dropped.


Operator dropped.


Function dropped.


Function dropped.


Type dropped.

drop type ORDSYS.ORDImageSignature
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents



Type dropped.

drop type ORDSYS.ORDImage
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSYS.ORDIMGB
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSYS.ORDIMGF
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSource
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

DBUA complains database not in mounted state

"The Upgrade Assistant failes in bringing up the database XXX. Oracle Home /oracle/XXX/11202 obtained from file /etc/oratab was used to connect to the database. Either the database is not running from Oracle Home /oracle/XXX/11202 or the correct Initialization Parameter file (pfile) was not found" Then it asks me to locate the pfile.

My case is similar to http://scn.sap.com/thread/1938507

So cat the Welcome_.txt 

> cat  Welcome_TTTT.txt
The presence of this file indicates that the database is opened up with the new Oracle Home binaries.

/software/orattt/admin/cfgtoollogs/dbua/logs_bak> ls -lrt
total 188
-rw-r----- 1 orattt orattt    102 Oct 21  2010 Welcome_TTTT.txt
-rw-r----- 1 orattt orattt 140879 May 24 16:03 sqls.log
-rw-r----- 1 orattt orattt   2104 May 24 16:07 PreUpgradeResults.html
-rw-r----- 1 orattt orattt  20034 May 24 16:07 trace.log

That is the culprit.

After I rename the directory, everything goes smoothly.