expdp时遇到的三个异常

最新推荐文章于 2022-05-31 23:24:15 发布

cuiquan1055

最新推荐文章于 2022-05-31 23:24:15 发布

阅读量336

点赞数

文章标签：数据库

上个礼拜在帮忙一同事解决expdp时碰到三个异常错误，今天总结如下：
首先遇到的异常如下：
[oracle@linux3200 oracle]$ expdp system/wicp_annie@db3200 directory=dir_expdp dumpfile='wicp_all_071026.dmp';

[@more@]

Export: Release 10.2.0.1.0 - Production on Thursday, 25 October, 2007 20:56:51

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20071025205651" and "KUPC$S_1_20071025205651" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-00832: no streams pool created and cannot automatically create one

解决方法如下：
SQL> show parameter stream

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
将streams_pool_size的值设置为非0，
SQL> alter system set streams_pool_size=10M scope=memory;

System altered.

SQL>
接下来又抛了另外一个异常“ORA-39213: 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.
可是再次调用时又抛了一个异常，是目录不存在的原因，通过创建完目录和赋权限后问题解决。见如下：
[oracle@linux3200 bdump]$ expdp system/wicp_annie directory=dir_expdp dumpfile='wicp_all_071026.dmp'

Export: Release 10.2.0.1.0 - Production on Thursday, 25 October, 2007 21:55:52

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
[oracle@linux3200 bdump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 25 21:55:56 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc dba_directories
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)

SQL> col owner for a10
SQL> col DIRECTORY_NAME for a20
SQL> col DIRECTORY_PATH for a30
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- ------------------------------
SYS DIR_EXPDP wicp_backup
SYS DATA_PUMP_DIR /opt/oracle/product/10g/rdbms/
log/

SYS XMLDIR /opt/oracle/product/10g/demo/s
chema/order_entry/

SYS MEDIA_DIR /opt/oracle/product/10g/demo/s
chema/product_media/

SYS LOG_FILE_DIR /opt/oracle/product/10g/demo/s

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- ------------------------------
chema/log/

SYS WORK_DIR /ade/aime_10.2_lnx_push/oracle
/work

SYS DATA_FILE_DIR /opt/oracle/product/10g/demo/s
chema/sales_history/

SYS SUBDIR /opt/oracle/product/10g/demo/s
chema/order_entry//2002/Sep

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- ------------------------------
SYS ADMIN_DIR /ade/aime_10.2_lnx_push/oracle
/md/admin

9 rows selected.

SQL> drop directory dir_expdp;

Directory dropped.

SQL> create directory dir_expdp as '/wicp_backup';

Directory created.

SQL> grant all on directory dir_expdp to public;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@linux3200 bdump]$ expdp system/wicp_annie directory=dir_expdp dumpfile='wicp_all_071026.dmp'

Export: Release 10.2.0.1.0 - Production on Thursday, 25 October, 2007 21:59:26

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dir_expdp dumpfile=wicp_all_071026.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 5.953 KB 2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.507 KB 28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.648 KB 19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.296 KB 3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 5.914 KB 2 rows
. . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_TEMP$LOB" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$APPLY_MILESTONE" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$APPLY_PROGRESS":"P0" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$EVENTS" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$HISTORY" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$PARAMETERS" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$PLSQL" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$SCN" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$SKIP" 0 KB 0 rows
. . exported "SYSTEM"."LOGSTDBY$SKIP_TRANSACTION" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_INDEX" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_PARTITION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/wicp_backup/wicp_all_071026.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:59:47