Downgrading with datapump

What happens if you need to do a logical extract from an Oracle 12 database and load that into an earlier version (11.2 for example) - is that possible?

Well the short answer is yes. The longer answer is you have to do the export with an additional parameter and you will of course not be able to move everything back (for example some new object/option that only exists in 12 can't be exported and loaded into 11). In the main though tables/indexes and the like should work just fine - so how do you go about it?

Well first up we'll create a really basic user and table in 12c (12.1.0.2 here but the versions dont matter too much)

SQL> create user demo identified by demo;

User created.

SQL> grant create session, unlimited tablespace,create table to demo;

Grant succeeded.

SQL> create table demo.demotab(col1 number);

Table created.

SQL> insert into demo.demotab values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
Now we just do a normal export (no special settings)

 expdp demo/demo schemas=demo dumpfile=12c.dmp directory=data_pump_dir

Export: Release 12.1.0.2.0 - Production on Mon Nov 10 10:10:47 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/******** schemas=demo dumpfile=12c.dmp directory=data_pump_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 40 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "DEMO"."DEMOTAB"                            5.054 KB       1 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  /oracle/12.1.0.2/rdbms/log/12c.dmp
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 10 10:11:25 2014 elapsed 0 00:00:38

At the same time we do the exact same command but with one extra option (version = 11.2 - this extracts the dump in 11.2 format)

expdp demo/demo schemas=demo dumpfile=12cversion11.dmp directory=data_pump_dir version=11.2

Export: Release 12.1.0.2.0 - Production on Mon Nov 10 10:12:00 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/******** schemas=demo dumpfile=12cversion11.dmp directory=data_pump_dir version=11.2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 40 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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 "DEMO"."DEMOTAB"                            5.054 KB       1 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  /oracle/12.1.0.2/rdbms/log/12cversion11.dmp
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 10 10:12:22 2014 elapsed 0 00:00:21


So now we have both files lets switch to an 11.2 database and try and load them

SYS@11G>create directory demo as '/oracle/12.1.0.2/rdbms/log';

Directory created.

 impdp / directory=demo dumpfile=12c.dmp

Import: Release 11.2.0.2.0 - Production on Mon Nov 10 10:14:28 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.1 in dump file "/oracle/12.1.0.2/rdbms/log/12c.dmp"

As we might expect the first file is in 12c format so cannot be loaded by the 11.2 imp utility, now lets try the file that was extracted in 'downgraded' format.

impdp / directory=demo dumpfile=12cversion11.dmp

Import: Release 11.2.0.2.0 - Production on Mon Nov 10 10:15:03 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
Master table "OPS$ORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_FULL_01":  /******** directory=demo dumpfile=12cversion11.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema DEMO is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'EDJSON', inst_scn=>'11480476');COMMIT; END;
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"DEMO"."DEMOTAB" failed to create with error:
ORA-01918: user 'DEMO' does not exist
Failing sql is:
CREATE TABLE "DEMO"."DEMOTAB" ("COL1" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "OPS$ORACLE"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:15:06

And it works , but with errors. Now the errors are the result of the demo user not having exp_full_database role - this means it can;t extract it's own create user statement and causes the error above. Let's resolve that so we get a nice clear demo that works.

So we switch back to 12c and run this
 
SQL> grant exp_full_database to demo;

Grant succeeded.

 Now re-run the extract now we have the extra rights

[oracle@server]:12c:[~]# expdp demo/demo schemas=demo dumpfile=12cversion11.dmp directory=data_pump_dir version=11.2 reuse_dumpfiles=y

Export: Release 12.1.0.2.0 - Production on Mon Nov 10 10:24:21 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  demo/******** schemas=demo dumpfile=12cversion11.dmp directory=data_pump_dir version=11.2 reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 40 KB
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/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 "DEMO"."DEMOTAB"                            5.054 KB       1 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  /oracle/12.1.0.2/rdbms/log/12cversion11.dmp
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 10 10:24:46 2014 elapsed 0 00:00:25

The sharp eyed of you may now notice that there are more things being exported (USER etc...)



Now we import this as before

impdp / directory=demo dumpfile=12cversion11.dmp

Import: Release 11.2.0.2.0 - Production on Mon Nov 10 10:25:09 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
Master table "OPS$ORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_FULL_01":  /******** directory=demo dumpfile=12cversion11.dmp
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/TABLE_DATA
. . imported "DEMO"."DEMOTAB"                            5.054 KB       1 rows
Job "OPS$ORACLE"."SYS_IMPORT_FULL_01" successfully completed at 10:25:12


And there we have it a 12c export loaded into an 11g db