expdp备份ORA-ORA-31693/ORA-02354/ORA-00942

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/