轉oracle資料泵匯出時報錯
Oracle 12c 資料泵匯出錯誤
錯誤程式碼:
ORA-39002: 操作無效
ORA-39070: 無法開啟日誌檔案。
ORA-29283: 檔案操作無效
ORA-06512: 在 "SYS.UTL_FILE", line 536
ORA-29283: 檔案操作無效
DIRECTORY 這個目錄不對,你用這個查一下:
select * from dba_directories;
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
SYMPTOMS
During DataPump export or import you receive the below error messages:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
CAUSE
1. One of the reason this problem usually can occurs when the listener process has not been started under the same account as the database instance service. The listener forks the new server process and when this runs under a different security context as the database, then access to directories and files are likely impacted.
Please verify the following information:
1) the output of:
ps -ef | grep SMON
2) the output of:
ps -ef | grep tnslsnr
3) the output of:
ps -ef|grep LIST
4) the output of:
ls -ld <full directory name of the directory to which the export/import is written>
Note:
When using ASM, the listener may have been started from the ASM Home instead of the RDBMS Home. Depending on your security settings, this may give to this issue.
2. This issue can occur in RAC. DataPump export/import runs fine without the connect string.
expdp system/xxx directory=xxxxxxx dumpfile=xxxxxx.dmp logfile= xxxxxx.log schemas=xxxx
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 08 March, 2011 11:15:2 3
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03": system/******** directory=xxxxx dumpfile=xxxxx.dmp logfile= xxxxxx.log schemas=xxxxxxx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.25 MB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "xxxxxx"."xxxxxxxxxxxx" 1.122 MB 1156 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 131.1 KB 3980 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 7.710 KB 1 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 6.054 KB 5 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 5.5 KB 2 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 5.492 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/opt/xxxxxxxxxx/3.0/admin/dbbackup/dump/xxxxxxxxxxx.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:15:41
But with the connect strings, it fails with the below error message
expdp system/xxxx@db directory=xxxxxxxxxx dumpfile=xxxxxxxx.dmp logfile= xxxxxxx.log schemas=xxxxxx
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 08 March, 2011 11:16:2 0
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing 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 536
ORA-29283: invalid file operation
Note : Both commands ran on the same server.
The reason can be because the connect string (TNS Name) is a load balancing connect string and whenever you try to use the connect string with expdp/impdp, it goes to the other node where the directory information is not available, or the directory might be a local folder which is not a shared one.
3. For single node or RAC instance (this should fail with both connect string and without connect string) because the folder path or the folder does not exist .
4. Directory path/folder exist but create directory is executed by a different user in the database and the import is run by a different user.
Run the below query to cross verify who owns the directory
set pages 999 lines 200
select * from dba_directories where directory_name = '< directory_name >';
SOLUTION
1. Make sure the listener and instance services are started by the same account
2. Make sure that the directory are shared between nodes so that the directory can be accessed on any instance, or, create a folder similar to the other nodes locally, if there is already a folder created locally on the all the node with the same file directory path structure check if the permission are correct.
3. Make sure the folder exist has specified in during creation in the "CREATE DIRECTORY" syntax command.
4. Grant the required permission to the importing user to use the directory.
grant read, write on directory ,directory_name> to <username>;
Oracle資料泵(Data Dump)錯誤彙集
2014-09-07 13:15 編輯
Oracle資料泵(Data Dump)使用過程當中經常會遇到一些奇奇怪怪的錯誤案例,下面總結一些自己使用資料泵(Data Dump)過程當中遇到的問題以及解決方法。都是在使用過程中遇到的問題,以後陸續遇到資料泵(Data Dump)的錯誤案例,都會補充在此篇。
錯誤案例1:
ORA-39065: DISPATCH 中出現意外的主程式異常錯誤;ORA-44002: 物件名無效
Windows 平臺錯誤提示:
檢視大圖
Linux平臺錯誤提示
檢視大圖
解決方法:
[oracle@DB-Server admin]$ oerr ora 39065
39065, 00000, "unexpected master process exception in %s"
// *Cause: An unhandled exception was detected internally within the master
// control processforthe Data Pump job. This is an internal error.
// messages will detail the problems.
// *Action: If problem persists, contact Oracle Customer Support.
[oracle@DB-Server admin]$ oerr ora 39097
39097, 00000, "Data Pump job encountered unexpected error %s"
// *Cause: An unexpected, potentially non-fatal error occurred while
// processing a Data Pump job.
// *Action: Contact Oracle Customer Support.
執行$ORACLE_HOME/rdbms/admin目錄下面的catmet2.sql,utlrp.sql 兩個SQL檔案即可解決這個問題。
[oracle@DB-Server ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@DB-Server admin]$ sqlplus / assysdba
SQL*Plus: Release 10.2.0.1.0 - Production onSun Sep 7 08:24:23 2014
Copyright (c) 1982, 2005, Oracle. Allrights reserved.
Connected toan idle instance.
SQL> @catmet2.sql
SQL> @utlrp.sql
錯誤案例2:
使用expdp/impdp時遭遇ORA-39006: internal error;ORA-39213: Metadata processing is notavailable錯誤
[oracle@DB-Server backup]$ impdp frnt/frnt directory=dum_dir dumpfile=20120420.FRNT_02.dmp tablespaces=TBS_TR_DATA;
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 25 April, 2012 14:41:48
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
解決方法:
錯誤原因如下所示,因為Data Pump不能使用Metadata API, 這個是因為XSL stylesheets 沒有正確設定緣故。需要以SYSDBA執行dbms_metadata_util.load_stylesheets
[oracle@DB-Server admin]$ 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.
SQL>exec dbms_metadata_util.load_stylesheets
錯誤案例3:
錯誤如下所示:
etl@NMS_ODS_NDB1[/jkfile/klbtmp]#expdp userid=username/password@tnsname dumpfile=ref.dmp directory=DUMP_TEST schemas=ref content=all logfile=ref.log
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:28:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, 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
解決方法:
出現這個錯誤,需要檢查DIRECTORY目錄,建立DIRECTORY時,ORACLE並不檢查作業系統目錄是否已經存在
首先檢查DIRECTORY目錄DUMP_TEST對應的DIRECTORY_PATH,然後檢查作業系統下,驗證該目錄是否存在。
SQL> SELECT* FROMDBA_DIRECTORIES WHEREDIRECTORY_NAME='DUMP_TEST';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------ ------------------------------ -----------------
SYS DUMP_TEST /jkfile/klbtmp/
SQL>
結果檢查發現該目錄被刪除了,建立該目錄即可解決問題。
另外一種情況,如果建立DIRECTORY目錄時,出現了換行,也會出現上面錯誤資訊,這種錯誤玩玩很難發現,非常折騰人。所以需要非常細心。
SQL> create directory DUMP_TEST as '/jkfile/klbtmp/
';
錯誤案例4:
如下所示:
etl@NMS_ODS_NDB1[/jkfile/work/klb]#expdp userid=etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR schemas=ref content=all logfile=ref.log
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:50:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
解決方法:
主要是etl賬號缺少exp_full_database 許可權,給etl賬號授予exp_full_database 許可權後,上述問題解決。
# su - oracle
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 2 10:58:37 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> grant exp_full_database to etl;
Grant succeeded.
錯誤案例5:
etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query='DATE_CD >=20121201 AND DATE_CD <=20130131';
LRM-00101: unknown parameter name '>'
解決方法:
etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query="DATE_CD \>\=20121201 AND DATE_CD \<\=20130131";
錯誤案例6:
LRM-00121: 'DATA_ONLY' is not an allowable value for 'compression'。如下所示:
etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR dumpfile=DM.TM_ALARM_LOG201212.dmp tables=DM.TM_ALARM_LOG COMPRESSION=DATA_ONLY query='DATE_CD >=20121201 AND DATE_CD <=20130131';
LRM-00121: 'DATA_ONLY' is not an allowable value for 'compression'
解決方法:
首先檢視EXPDP工具的版本,如下所示:
etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp version
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 04 March, 2013 14:46:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:
請注意,在ORACLE 10g下 COMPRESSION只有METADATA_ONLY和NONE兩個選項,ORACLE 11g下才有DATA_ONLY選項。所以報如上錯誤。所以在使用前,請注意一下EXPDP工具的版本。
錯誤案例7:
[oracle@DB-Server]$ expdp system/***** TABLES=INVENRTY.INV_STK_HD dumpfile=INV_STK_HD.dmp logfile=1.log DIRECTORY=CUR_DUMP_DIR
Export: Release 10.2.0.4.0 - Production on Sunday, 14 July, 2013 8:27:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_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_20130714082716" and "KUPC$S_1_20130714082716" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-00832: no streams pool created and cannot automatically create one
解決方法:
這個案例我以前已經介紹過,具體參考我的部落格 Expdp 導數錯誤 ORA-00832
錯誤案例8:
[oracle@testlnx01 u03]$ cd tmp/
[oracle@testlnx01 tmp]$ expdp system/***** directory=DUMPDIR dumpfile=ESCMUSER.dmp schemas=ESCMUSER logfile=ESCMUSER.log
Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 27 August, 2014 16:30:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-00959: tablespace 'TOOLS' does not exist
解決方法:
這個案例有點特別,剛遇到的時候,確實有點莫名其妙,經過苦苦求索、求證後才發現,本來有一個TOOLS的表空間,不知道是哪位頭腦發熱的人,居然指定SYSTEM使用者的預設表空間為TOOLS,但是這個表空間又被人刪除了。於是便有了這樣一個案例。
SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
User altered.
修改使用者SYSTEM的預設表空間後,問題解決。
錯誤程式碼:
ORA-39002: 操作無效
ORA-39070: 無法開啟日誌檔案。
ORA-29283: 檔案操作無效
ORA-06512: 在 "SYS.UTL_FILE", line 536
ORA-29283: 檔案操作無效
DIRECTORY 這個目錄不對,你用這個查一下:
select * from dba_directories;
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
SYMPTOMS
During DataPump export or import you receive the below error messages:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
CAUSE
1. One of the reason this problem usually can occurs when the listener process has not been started under the same account as the database instance service. The listener forks the new server process and when this runs under a different security context as the database, then access to directories and files are likely impacted.
Please verify the following information:
1) the output of:
ps -ef | grep SMON
2) the output of:
ps -ef | grep tnslsnr
3) the output of:
ps -ef|grep LIST
4) the output of:
ls -ld <full directory name of the directory to which the export/import is written>
Note:
When using ASM, the listener may have been started from the ASM Home instead of the RDBMS Home. Depending on your security settings, this may give to this issue.
2. This issue can occur in RAC. DataPump export/import runs fine without the connect string.
expdp system/xxx directory=xxxxxxx dumpfile=xxxxxx.dmp logfile= xxxxxx.log schemas=xxxx
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 08 March, 2011 11:15:2 3
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03": system/******** directory=xxxxx dumpfile=xxxxx.dmp logfile= xxxxxx.log schemas=xxxxxxx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.25 MB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "xxxxxx"."xxxxxxxxxxxx" 1.122 MB 1156 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 131.1 KB 3980 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 7.710 KB 1 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 6.054 KB 5 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 5.5 KB 2 rows
. . exported "xxxxxx"."xxxxxxxxxxxx" 5.492 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/opt/xxxxxxxxxx/3.0/admin/dbbackup/dump/xxxxxxxxxxx.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:15:41
But with the connect strings, it fails with the below error message
expdp system/xxxx@db directory=xxxxxxxxxx dumpfile=xxxxxxxx.dmp logfile= xxxxxxx.log schemas=xxxxxx
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 08 March, 2011 11:16:2 0
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing 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 536
ORA-29283: invalid file operation
Note : Both commands ran on the same server.
The reason can be because the connect string (TNS Name) is a load balancing connect string and whenever you try to use the connect string with expdp/impdp, it goes to the other node where the directory information is not available, or the directory might be a local folder which is not a shared one.
3. For single node or RAC instance (this should fail with both connect string and without connect string) because the folder path or the folder does not exist .
4. Directory path/folder exist but create directory is executed by a different user in the database and the import is run by a different user.
Run the below query to cross verify who owns the directory
set pages 999 lines 200
select * from dba_directories where directory_name = '< directory_name >';
SOLUTION
1. Make sure the listener and instance services are started by the same account
2. Make sure that the directory are shared between nodes so that the directory can be accessed on any instance, or, create a folder similar to the other nodes locally, if there is already a folder created locally on the all the node with the same file directory path structure check if the permission are correct.
3. Make sure the folder exist has specified in during creation in the "CREATE DIRECTORY" syntax command.
4. Grant the required permission to the importing user to use the directory.
grant read, write on directory ,directory_name> to <username>;
Oracle資料泵(Data Dump)錯誤彙集
2014-09-07 13:15 編輯
Oracle資料泵(Data Dump)使用過程當中經常會遇到一些奇奇怪怪的錯誤案例,下面總結一些自己使用資料泵(Data Dump)過程當中遇到的問題以及解決方法。都是在使用過程中遇到的問題,以後陸續遇到資料泵(Data Dump)的錯誤案例,都會補充在此篇。
錯誤案例1:
ORA-39065: DISPATCH 中出現意外的主程式異常錯誤;ORA-44002: 物件名無效
Windows 平臺錯誤提示:
檢視大圖
Linux平臺錯誤提示
檢視大圖
解決方法:
[oracle@DB-Server admin]$ oerr ora 39065
39065, 00000, "unexpected master process exception in %s"
// *Cause: An unhandled exception was detected internally within the master
// control processforthe Data Pump job. This is an internal error.
// messages will detail the problems.
// *Action: If problem persists, contact Oracle Customer Support.
[oracle@DB-Server admin]$ oerr ora 39097
39097, 00000, "Data Pump job encountered unexpected error %s"
// *Cause: An unexpected, potentially non-fatal error occurred while
// processing a Data Pump job.
// *Action: Contact Oracle Customer Support.
執行$ORACLE_HOME/rdbms/admin目錄下面的catmet2.sql,utlrp.sql 兩個SQL檔案即可解決這個問題。
[oracle@DB-Server ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@DB-Server admin]$ sqlplus / assysdba
SQL*Plus: Release 10.2.0.1.0 - Production onSun Sep 7 08:24:23 2014
Copyright (c) 1982, 2005, Oracle. Allrights reserved.
Connected toan idle instance.
SQL> @catmet2.sql
SQL> @utlrp.sql
錯誤案例2:
使用expdp/impdp時遭遇ORA-39006: internal error;ORA-39213: Metadata processing is notavailable錯誤
[oracle@DB-Server backup]$ impdp frnt/frnt directory=dum_dir dumpfile=20120420.FRNT_02.dmp tablespaces=TBS_TR_DATA;
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 25 April, 2012 14:41:48
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
解決方法:
錯誤原因如下所示,因為Data Pump不能使用Metadata API, 這個是因為XSL stylesheets 沒有正確設定緣故。需要以SYSDBA執行dbms_metadata_util.load_stylesheets
[oracle@DB-Server admin]$ 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.
SQL>exec dbms_metadata_util.load_stylesheets
錯誤案例3:
錯誤如下所示:
etl@NMS_ODS_NDB1[/jkfile/klbtmp]#expdp userid=username/password@tnsname dumpfile=ref.dmp directory=DUMP_TEST schemas=ref content=all logfile=ref.log
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:28:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, 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
解決方法:
出現這個錯誤,需要檢查DIRECTORY目錄,建立DIRECTORY時,ORACLE並不檢查作業系統目錄是否已經存在
首先檢查DIRECTORY目錄DUMP_TEST對應的DIRECTORY_PATH,然後檢查作業系統下,驗證該目錄是否存在。
SQL> SELECT* FROMDBA_DIRECTORIES WHEREDIRECTORY_NAME='DUMP_TEST';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------ ------------------------------ -----------------
SYS DUMP_TEST /jkfile/klbtmp/
SQL>
結果檢查發現該目錄被刪除了,建立該目錄即可解決問題。
另外一種情況,如果建立DIRECTORY目錄時,出現了換行,也會出現上面錯誤資訊,這種錯誤玩玩很難發現,非常折騰人。所以需要非常細心。
SQL> create directory DUMP_TEST as '/jkfile/klbtmp/
';
錯誤案例4:
如下所示:
etl@NMS_ODS_NDB1[/jkfile/work/klb]#expdp userid=etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR schemas=ref content=all logfile=ref.log
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 02 March, 2013 10:50:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas
解決方法:
主要是etl賬號缺少exp_full_database 許可權,給etl賬號授予exp_full_database 許可權後,上述問題解決。
# su - oracle
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 2 10:58:37 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> grant exp_full_database to etl;
Grant succeeded.
錯誤案例5:
etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query='DATE_CD >=20121201 AND DATE_CD <=20130131';
LRM-00101: unknown parameter name '>'
解決方法:
etl@NMS_ODS_NDB1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=DM.TM_ALARM_LOG.dmp directory=DUMP_DIR tables=DM.TM_ALARM_LOG query="DATE_CD \>\=20121201 AND DATE_CD \<\=20130131";
錯誤案例6:
LRM-00121: 'DATA_ONLY' is not an allowable value for 'compression'。如下所示:
etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp etl/sunrise@wgods1 dumpfile=ref.dmp directory=DUMP_DIR dumpfile=DM.TM_ALARM_LOG201212.dmp tables=DM.TM_ALARM_LOG COMPRESSION=DATA_ONLY query='DATE_CD >=20121201 AND DATE_CD <=20130131';
LRM-00121: 'DATA_ONLY' is not an allowable value for 'compression'
解決方法:
首先檢視EXPDP工具的版本,如下所示:
etl@NMS_ODS_NDB1[/etl1_cx/etl]#expdp version
Export: Release 10.2.0.3.0 - 64bit Production on Monday, 04 March, 2013 14:46:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:
請注意,在ORACLE 10g下 COMPRESSION只有METADATA_ONLY和NONE兩個選項,ORACLE 11g下才有DATA_ONLY選項。所以報如上錯誤。所以在使用前,請注意一下EXPDP工具的版本。
錯誤案例7:
[oracle@DB-Server]$ expdp system/***** TABLES=INVENRTY.INV_STK_HD dumpfile=INV_STK_HD.dmp logfile=1.log DIRECTORY=CUR_DUMP_DIR
Export: Release 10.2.0.4.0 - Production on Sunday, 14 July, 2013 8:27:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_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_20130714082716" and "KUPC$S_1_20130714082716" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-00832: no streams pool created and cannot automatically create one
解決方法:
這個案例我以前已經介紹過,具體參考我的部落格 Expdp 導數錯誤 ORA-00832
錯誤案例8:
[oracle@testlnx01 u03]$ cd tmp/
[oracle@testlnx01 tmp]$ expdp system/***** directory=DUMPDIR dumpfile=ESCMUSER.dmp schemas=ESCMUSER logfile=ESCMUSER.log
Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 27 August, 2014 16:30:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-00959: tablespace 'TOOLS' does not exist
解決方法:
這個案例有點特別,剛遇到的時候,確實有點莫名其妙,經過苦苦求索、求證後才發現,本來有一個TOOLS的表空間,不知道是哪位頭腦發熱的人,居然指定SYSTEM使用者的預設表空間為TOOLS,但是這個表空間又被人刪除了。於是便有了這樣一個案例。
SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
User altered.
修改使用者SYSTEM的預設表空間後,問題解決。
相關文章
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle資料泵的匯入和匯出Oracle
- Oracle expdp資料泵遠端匯出Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 資料泵匯出匯入
- ORACLE 資料泵impdp匯入報錯之ORA-31693 ORA-04098Oracle
- oracle10g expdp資料泵的bug,按schema匯出,匯入impdp時無jobOracle
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- 資料泵匯出時報ORA-31623、ORA-06512問題的解決
- oracle12c還原資料庫遇到的問題-將一個11.2.0.1的資料泵匯出檔案匯入12.1.0.2版本報錯Oracle資料庫
- Oracle 資料匯入匯出Oracle
- Oracle資料匯入匯出Oracle
- 資料泵匯出匯入物化檢視(ORA-39083)
- Oracle資料庫——資料匯出時出現匯出成功終止, 但出現警告。Oracle資料庫
- 【資料泵】EXPDP匯出表結構(真實案例)
- oracle資料匯出匯入(exp/imp)Oracle
- 細緻入微:如何使用資料泵匯出表的部分列資料
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- Oracle 資料匯出注意事項Oracle
- Oracle資料泵(Oracle Data Pump) 19cOracle
- ORACLE 資料泵expdp莫名其妙的報錯ORA-31693&ORA-19502&ORA-27063Oracle
- 【DATAPUMP】Oracle資料泵定時備份刪除指令碼Oracle指令碼
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- oracle 備份資料庫,匯出資料庫Oracle資料庫
- 把TXT文字匯入SQLServer 出錯:資料轉換失敗SQLServer
- Oracle資料庫(資料泵)遷移方案(上)Oracle資料庫
- Oracle資料庫(資料泵)遷移方案(下)Oracle資料庫
- Oracle匯出資料庫與還原Oracle資料庫
- 資料庫匯入匯出時報ORA-01843的解決方法資料庫
- oracle邏輯備份之--資料泵Oracle
- 【ASK_ORACLE】重灌Oracle資料泵(Datapump)工具的方法Oracle
- 運維中資料泵匯出常用的細節小功能兩例運維
- 【ORA-01555】Oracle LOB欄位匯出 報錯 ORA-01555Oracle
- oracle按照表條件expdp匯出資料Oracle
- Oracle Data Pump 11G 資料泵元件Oracle元件
- 史上最全Oracle資料泵常用命令Oracle
- oracle單表按時間匯出Oracle
- 連線oracle資料庫時,報錯:{dataSource-1} init errorOracle資料庫Error
- 分享運維中資料泵匯出常用的細節小功能兩例運維