31623 oracle,expdp遭遇ORA-39006/ORA-39213故障解决

expdp导出数据遇到ORA-39006/ORA-39213错误,通过执行执行dbms_metadata_util.load_stylesheets解决

expdp工作异常

--导出awr信息

SQL> @?/rdbms/admin/awrextr.sql

…………

Exception encountered in AWR_EXTRACT

ORA-39006: internal error

ORA-39213: Metadata processing is not available

begin

*

ERROR at line 1:

ORA-31623: a job is not attached to this session via the specified handle

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 911

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710

ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 656

ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 962

ORA-06512: at line 3

--导出一个表

$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 13:03:20

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

ORA-39006: internal error

ORA-39213: Metadata processing is not available

错误提示

$ oerr ora 39006

39006, 00000, "internal error"

// *Cause: An unexpected error occurred while processing a Data Pump job.

// Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS

// will further describe the error.

// *Action: Contact Oracle Customer Support.

$ oerr ora 39213

39213, 00000, "Metadata processing is not available"

// *Cause: The Data Pump could not use the Metadata API. Typically,

// this is caused by the XSL stylesheets not being set up properly.

// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets

// to reload the stylesheets.

解决ORA-39006/ORA-39213问题

--查询数据库已经安装组件

SQL> col COMP_NAME for a35

SQL> select comp_name, version, status from dba_registry;

COMP_NAME VERSION STATUS

----------------------------------- ------------------------------ ----------------------

Oracle Database Catalog Views 10.2.0.1.0 VALID

Oracle Database Packages and Types 10.2.0.1.0 VALID

Oracle Workspace Manager 10.2.0.1.0 VALID

JServer JAVA Virtual Machine 10.2.0.1.0 VALID

Oracle XDK 10.2.0.1.0 VALID

Oracle Database Java Packages 10.2.0.1.0 VALID

Oracle Expression Filter 10.2.0.1.0 VALID

Oracle Data Mining 10.2.0.1.0 VALID

Oracle Text 10.2.0.1.0 VALID

Oracle XML Database 10.2.0.1.0 VALID

Oracle Rules Manager 10.2.0.1.0 VALID

Oracle interMedia 10.2.0.1.0 VALID

OLAP Analytic Workspace 10.2.0.1.0 VALID

Oracle OLAP API 10.2.0.1.0 VALID

OLAP Catalog 10.2.0.1.0 VALID

Spatial 10.2.0.1.0 VALID

Oracle Enterprise Manager 10.2.0.1.0 VALID

17 rows selected.

--如果缺少下面组件,使用下面对应的程序安装

Oracle Database Catalog Views

Oracle Database Packages and Types

JServer JAVA Virtual Machine

Oracle XDK

Oracle Database Java Packages

--使用下面脚本安装(根据组件选择)

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/xdk/admin/initxml.sql

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

--执行sys.dbms_metadata_util.load_stylesheets

SQL> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

测试expdp导出

$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei Directory=AWR_DIR

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 14:18:04

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' dumpfile=xifenfei.dmp

tables=scott.t_xifenfei Directory=AWR_DIR

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 7 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T_XIFENFEI" 5.374 MB 57376 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

/data/enmotech/xifenfei.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:18:11

测试证明，在不缺少相关组件的情况下,使用dbms_metadata_util.load_stylesheets可以解决expdp导出报ORA-39006/ORA-39213错误；如果缺少组件,需要先安装对应组件,然后再执行dbms_metadata_util.load_stylesheets解决该问题