Thursday, May 20, 2010

Exporting from 11.2 to 11.1 and 10.2 causes error when tables are in the keep pool

Sometimes it is needed to export from a higher oracle db version to a lower version (from 11.2 to 11.1 or 11.2 to 10.2). But there appear to be a bug in the 11.2 expdp which causes export to fail for tables and indexes which have been moved to keep pool in the source data source. But this does not appear in 11.1 version.

This is the test case. All databases used have been patched to PSU April 2010.

Below code is used to create a table, populate it with some data and then move it to the keep pool. Databases all have keep pool configured on them.


create table abc (a number, b varchar2(10), c number primary key);

begin
 for i in 1 .. 1000
 loop
   insert into abc values (i+10,'abc'||i,i);
 end loop;
end;
/   

show parameter keep
NAME                 TYPE        VALUE
-------------------- ----------- ------
db_keep_cache_size   big integer 16M

alter table abc storage (buffer_pool keep);
alter index SYS_C0084932 storage (buffer_pool keep);

First exporting from 11.1.0.7 to 10.2.0.4

expdp asanga/*** directory=dumpdir dumpfile=asanga11g1.dmp logfile=11g1.log version="10.2.0.4" schemas=asanga
Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 20 May, 2010 14:04:40

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, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g1.dmp logfile=11g1.log version=10.2.0.4 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              24.03 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
/ssdoracle/dpdumps/asanga11g1.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:17


Now import to 10.2.0.4

impdp asanga/asa directory=dumpdir dumpfile=asanga11g1.dmp logfile=10gimp.log full=y version="10.2.0.4"

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 20 May, 2010 14:15:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g1.dmp logfile=10gimp.log full=y version=10.2.0.4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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 "ASANGA"."ABC"                              24.03 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:15:31

No errors there, import completes successfully. So the erroneous behavior didn't start with 11.1

Now exporting from 11.2.0 to 11.1.0.7
Create, populate and move the source table to keep pool using the aforementioned code. Afterwards do the export on 11.2

expdp asanga/*** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=11g1.log version="11.1.0.7" schemas=asanga

Export: Release 11.2.0.1.0 - Production on Thu May 20 14:27:52 2010

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, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=11g1.log version=11.1.0.7 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              24.03 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
 /ssdoracle/dpdumps/asanga11g2_11107.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:28:19

Do the import on 11.1.0.7

impdp asanga/*** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=10gimp.log full=y version="11.1.0.7"

Import: Release 11.1.0.7.0 - 64bit Production on Thursday, 20 May, 2010 14:29:55

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, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=10gimp.log full=y version=11.1.0.7
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-25126: Invalid name specified for BUFFER_POOL
Failing sql is:
CREATE TABLE "ASANGA"."ABC" ("A" NUMBER, "B" VARCHAR2(10 BYTE), "C" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ) TABLESPACE "TRAVELBOX"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"ASANGA"."SYS_C0034668" skipped, base object type TABLE:"ASANGA"."ABC" creation failed
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 14:30:02
As it could be seen from the above output, import fails due to invalid name specified on the buffer pool.

Now export 11.2 to 10.2.0.4

expdp asanga/*** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=11g1.log version="10.2.0.4" schemas=asanga

Export: Release 11.2.0.1.0 - Production on Thu May 20 14:22:57 2010

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, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=11g1.log version=10.2.0.4 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              23.84 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
  /ssdoracle/dpdumps/asanga11g2_10204.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:23:42


And now the import on 10.2.0.4

impdp asanga/*** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=10gimp.log full=y version="10.2.0.4"

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 20 May, 2010 14:27:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=10gimp.log full=y version=10.2.0.4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-25126: Invalid name specified for BUFFER_POOL
Failing sql is:
CREATE TABLE "ASANGA"."ABC" ("A" NUMBER, "B" VARCHAR2(10), "C" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ) TABLESPACE "TRAVELBOX"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"ASANGA"."SYS_C0034668" skipped, base object type TABLE:"ASANGA"."ABC" creation failed
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 14:27:05


Again the export failes for the same reason as in 11.1.
Although here creation of index is skipped due to the fact table was not created but a similar test could be carried out by moving only the index to keep pool.
Error could be avoided if the source tables and indexes were moved to default pool before the export (how practical is this?) or recreate them beforehand (if there are only few) and use if table exists option in the impdp



Update 26 March 2013
Issue is mentioned in the following Oracle metalink notes.
Data Pump Import Fails With ORA-25126 If Data Pump Export From 11.2.0.1 With VERSION=10.2 [ID 1051792.1]
Bug 9160088 - 11.2 EXPDP to lower version errors on import (ORA-25126) [ID 9160088.8]
Problem seem to be fixed in 11.2.0.3 (checked with PSU 11.2.0.3.5 applied).
Posted by at
Labels: , , , , ,