Thursday, September 13, 2012

Oracle PSU 9 Post-Install issues

Platform: Windows 2003 R2 x64
Version : Oracle PSU 9

After applying the PSU the DBMS_MEATADA didn't work.

ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

A side effect of this, when trying to execute DataPump it failed, since this requries DBMS_METADATA to extract the object metadata.

C:\> expdp aUser/aPassword schemas=SCOTT dumpfile=SCOTT logfile=SCOTT job_name=SCOTT

Export: Release - Production on Thu Sep 13 11:15:16 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

The reason is because after applying the PSU according to the MOS note: "Data Pump Export Started Failing After Applying CPU Patch [ID 453796.1]" some tables required by Data Pump are not loaded and the XML Style sheets for DBMS_METADATA are not loaded either.

The dictionary tables required by Data Pump are not loaded. 
If you execute this query:

SQL> select count(*) from metanametrans$;


and if there are no rows returned then you'll have to proceed as stated in the MOS note.

The procedure to fix this issue is listed in that MOS Note, so since you found this error after applying a PSU or a CPU, then it means you have access to My Oracle Support, just look for the previously referred MOS Note and you'll see what's the procedure to fix this issue.

Once the procedure has been applied, the above query returns the following result:

SQL> -- Oracle 11g Rel.2 ( PSU9)
SQL> select count(*) from metanametrans$;


And both, the DBMS_METADATA and Data Pump work like a charm.

No comments: