expdp備份ORA-ORA-31693/ORA-02354/ORA-00942
問題:expdp日常備份時經常會有下面的報錯
ORA-31693: Table data object "Chenjch"."VTZMWF8RG89LSV6YXXX" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00942: table or view does not exist
環境:Oracle 11.2.0.1.0 for Windows Server 2012
問題原因:
expdp是先導出表結構,然後在導出資料,
報錯的表都是一些臨時堆表,在expdp導出表結構時這些表還存在,但是在導出完表結構到導出資料期間表被删除,導緻後面導出資料時報錯;
問題重制:
建構測試資料,在expdp導出完表結構到導出資料期間手動删除部分表,可以重制;
并且通過這次的expdp備份恢複的資料,是存在這些報錯的表,隻是沒有資料;
---1 建立測試資料
---Oracle 11.2.0.4.0
SQL> create tablespace cjc_tbs datafile '/u01/app/oracle/oradata/orcltest/cjc_tbs01a.dbf' size 10M autoextend on;
SQL> create user chenjch identified by a default tablespace cjc_tbs;
SQL> grant connect,resource,dba to chenjch;
SQL> create directory dir_expdp as '/home/oracle/backup/expdp';
SQL> grant read,write on directory dir_expdp to public;
SQL> conn chenjch/a
SQL> create table tt1 as select level as id from dual connect by level <=10;
SQL> create table tt2 as select level as id from dual connect by level <=10;
SQL> create table tt3 as select level as id from dual connect by level <=10;
SQL> create table tt4 as select level as id from dual connect by level <=10;
SQL> create table tt5 as select level as id from dual connect by level <=10;
SQL>
declare
begin
for i in 1 .. 10000000 loop
insert into tt5 values (i);
commit;
end loop;
end;
---2 expdp導出資料庫,問題重制
SQL> create directory dir_expdp as 'D:\data';
SQL> grant read,write on directory dir_expdp to public;
[[email protected] expdp]$ expdp chenjch/a directory=dir_expdp dumpfile=chenjch_20180927.dmp logfile=chenjch_20180927.log schemas=chenjch
Export: Release 11.2.0.4.0 - Production on Thu Sep 27 16:30:28 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHENJCH"."SYS_EXPORT_SCHEMA_01": chenjch/******** directory=dir_expdp dumpfile=chenjch_20180927.dmp logfile=chenjch_20180927.log schemas=chenjch
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120.2 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 ---導出表結構後可以馬上執行删除表進行測試(删除表tt1,tt2,tt3)
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
. . exported "CHENJCH"."TT5" 94.31 MB 10000010 rows
ORA-31693: Table data object "CHENJCH"."TT1" failed to load/unload and is being skipped due to error: ---問題重制
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (CHENJCH.TT1) could not be resolved.
ORA-31693: Table data object "CHENJCH"."TT2" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (CHENJCH.TT2) could not be resolved.
ORA-31693: Table data object "CHENJCH"."TT3" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (CHENJCH.TT3) could not be resolved.
. . exported "CHENJCH"."TT4" 5.070 KB 10 rows
Master table "CHENJCH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHENJCH.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/backup/expdp/chenjch_20180927.dmp
Job "CHENJCH"."SYS_EXPORT_SCHEMA_01" completed with 3 error(s) at Thu Sep 27 16:30:44 2018 elapsed 0 00:00:15
---3 導入資料進行測試
SQL> create user chenjch1 identified by a default tablespace cjc_tbs;
SQL> grant connect,resource,dba to chenjch;
[[email protected] expdp]$ impdp chenjch/a directory=dir_expdp dumpfile=chenjch_20180927.dmp logfile=chenjch_impdp_20180927.log remap_schema=chenjch:chenjch1
Import: Release 11.2.0.4.0 - Production on Thu Sep 27 17:06:33 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CHENJCH"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CHENJCH"."SYS_IMPORT_FULL_01": chenjch/******** directory=dir_expdp dumpfile=chenjch_20180927.dmp logfile=chenjch_impdp_20180927.log remap_schema=chenjch:chenjch1
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CHENJCH1" already exists
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/TABLE_DATA
. . imported "CHENJCH1"."TT5" 94.31 MB 10000010 rows
. . imported "CHENJCH1"."TT4" 5.070 KB 10 rows
Job "CHENJCH"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Sep 27 17:06:45 2018 elapsed 0 00:00:11
SQL> conn chenjch1/a
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TT1 TABLE
TT2 TABLE
TT3 TABLE
TT4 TABLE
TT5 TABLE
SQL> select * from tt1;
no rows selected
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2215082/,如需轉載,請注明出處,否則将追究法律責任。
轉載于:http://blog.itpub.net/29785807/viewspace-2215082/