impdp with ora-39083 ora-01403

impdp with ora-39083 ora-01403

problem summary：when excute expdp dump of 11.2.0.1 without error，but impdp the data dump with ora 39083，ora 01403

 

 

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

环境：

oracle 11.2.0.1 on solaris 10 sparc 64bit

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

Oracle 官方说这是11.2.0.1的Bug

Bug 8543770 ORA-39083 / ORA-1403 from IMPDP on INDEX_STATISTICSOS:SUN Solaris 10database 11.2.0.1

整个解决过程如下：

1.resource database 查看数据对象:

select count(*) from dba_objects where owner='MOIA_UAT';
 COUNT(*)
----------
 421

target database 执行导入：

db1> impdp  \'/ as sysdba\' schemas=MOIA_UAT directory=data_pump_dir dumpfile=EXP_20121226_MOIA_UAT.dmp logfile=IMP_20121227_include_index_MOIA_UAT.log
Import: Release 11.2.0.1.0 - Production on Thu Dec 27 15:07:32 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=MOIA_UAT include=index directory=data_pump_dir dumpfile=EXP_20121226_MOIA_UAT.dmp logfile=IMP_20121227_inclu
de_index_MOIA_UAT.log 
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS
_METADATA.GET_STAT_COLNAME('MOIA_UAT','S_APP_TOPIC','0           ',NULL,0);  DBMS_METADATA.GET_STAT_INDNAME('MOIA_UAT','S_APP_TOPIC',c,1,i_o,i_n);   INSERT INTO "SYS"."IMPDP_STAT
S" (type,version,fl
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found

....

Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS
_METADATA.GET_STAT_COLNAME('MOIA_UAT','W_USER_MOULD','0           ',NULL,0);  DBMS_METADATA.GET_STAT_INDNAME('MOIA_UAT','W_USER_MOULD',c,1,i_o,i_n);   INSERT INTO "SYS"."IMPDP_ST
ATS" (type,version,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS
_METADATA.GET_STAT_COLNAME('MOIA_UAT','W_TASKFLOW_LINE','0           ',NULL,0);  c(2) :=   DBMS_METADATA.GET_STAT_COLNAME('MOIA_UAT','W_TASKFLOW_LINE',''' ''         ',NULL,0); 
DBMS_METADATA.GET_S
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 164 error(s) at 15:07:41

 

2.查看target database 数据对象是否导入成功：

SQL> select count(*) from dba_objects where owner='MOIA_UAT';  
now rows 

--从报错中可以看到：在执行impdp过程中，执行了以下（sql）

Failing sql is:DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('MOIA_UAT','W_TASKFLOW_LINE','0 ',NULL,0); c(2) := DBMS_METADATA.GET_STAT_COLNAME('MOIA_UAT','W_TASKFLOW_LINE',''' '' ',NULL,0);

--根据mos文章[ID 755253.1]提到在expdp 对象的时候，CREATE INDEX statements in wrong order into the dumpfile：故这里考虑先将index 排除。

3.resource database 排除index 重新执行导入：

db1> impdp  \'/ as sysdba\' schemas=MOIA_UAT exclude=index directory=data_pump_dir dumpfile=EXP_20121226_MOIA_UAT.dmp logfile=IMP_20121227_MOIA_UAT.log
Import: Release 11.2.0.1.0 - Production on Thu Dec 27 15:25:19 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=MOIA_UAT exclude=index directory=data_pump_dir dumpfile=EXP_20121226_MOIA_UAT.dmp logfile=IMP_20121227_MOIA_
UAT.log 
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/PASSWORD_HISTORY
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 "MOIA_UAT"."S_PARA_ID_STAT"                 2.625 MB   45000 rows
. . imported "MOIA_UAT"."S_BAT_JOB_INFO"                 242.3 KB     962 rows
. . imported "MOIA_UAT"."S_EVENT_INFO"                   181.4 KB    1092 rows
. . imported "MOIA_UAT"."S_PNODE_RESLOG"                 207.8 KB    1848 rows
. . imported "MOIA_UAT"."W_USER_RULE"                    163.6 KB    2145 rows
. . imported "MOIA_UAT"."S_BAT_JOB_ATTR"                 140.3 KB     962 rows
. . imported "MOIA_UAT"."S_APP_MACRO_PARA"               78.53 KB     767 rows
. . imported "MOIA_UAT"."S_BAT_JOB_PARAM"                97.62 KB     783 rows
. . imported "MOIA_UAT"."S_TASK_JOB"                     84.97 KB     965 rows
. . imported "MOIA_UAT"."W_SEQ_IMG"                      57.31 KB     916 rows
. . imported "MOIA_UAT"."S_APP_INFO"                     9.179 KB       1 rows
. . imported "MOIA_UAT"."S_APP_STAT"                     9.156 KB       1 rows
. . imported "MOIA_UAT"."S_APP_TOPIC"                    8.789 KB       1 rows
. . imported "MOIA_UAT"."S_BAT_CHAIN"                    11.57 KB      45 rows.... . imported "MOIA_UAT"."W_USER_MOULD"                       0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 15:25:50

 

4.查询dba_objects表 查看数据对象是否成功导入：

SQL> select count(*) from dba_objects where owner='MOIA_UAT';  
COUNT(*)
----------
 342
--依然与resource 的数量不一致。

[ID 1454880.1]提到一种方法，需要重新收集该schema的统计信息：

FixThis is due to the unpublished Bug 9316756 IMPORTING STATISTICS USING ORACLE DATA PUMP IS TIME CONSUMING, fixed in Oracle 12.1 
The fix will change the dumpfile format of the statistics collected. That's why the fix will be in the next release and will not be backported to previous one.
Use one of the below methods:
1. Re-export with exclude statistics, then import and gather the statistics after import.
Or:
2. Run the import excluding the statistics and gather the statistics manually.

同样也是个bug引发的问题：@ BUG:9316756 IMPORTING STATISTICS USING ORACLE DATA PUMP IS TIME CONSUMINGtarget database5.

 至于12.1 暂时不考虑了，还是老老实实试下第二种方法：

6.重新收集统计信息：

SQL> exec dbms_stats.gather_schema_stats('MOIA_UAT',cascade => TRUE); 
PL/SQL procedure successfully completed.

7.查询target database 数据对象信息：

SQL> select count(*) from dba_objects where owner='MOIA_UAT'; 

COUNT(*)
----------
421 

--问题貌似解决，根据oracle 工程师的答复该bug 8543770在 11.2.0.2 FIX。