Search This Blog

Tuesday, July 26, 2011

Export Failed after upgrade 11.2.0.1 to 11.2.0.2 with 'DMSYS.DBMS_DM_MODEL_EXP'

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips


After upgraded 11.2.0.1 to 11.2.0.2 export failed with the following errors

About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_MODEL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_MODEL_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp


Cause:
unpublished Bug 9694190 "11.2.0.1 UPGRADE TO 11.2.0.2 ODM BECOMES INVALID WITH A FEW INVALID PACKAGE BODIES"

Solution:
SQL> CONNECT / AS SYSDBA;
SQL> DROP USER DMSYS CASCASE;
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = 'DMSYS';
SQL> SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';


If the above SQL returns non-zero rows, create and run a SQL script as shown in the following example:

SQL> SET HEAD OFF
SQL> SPOOL /DROP_DMSYS_SYNONYMS.SQL
SQL> SELECT 'Drop public synonym ' || SYNONYM_NAME || ' ;'
FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';
SQL> SPOOL OFF
SQL> @/DROP_DMSYS_SYNONYMS.SQL
SQL> EXIT;

2 comments:

Anonymous said...

before upgrading your database to 11.2.0.2 , you should run the utlu112i.sql script and read the output carefully.It tells you to drop that schema before upgrading ...

Mohammed Taj said...

Thanks for the info.