Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)
Posted by Mir Sayeed Hassan on October 2nd, 2017
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)
Export on 12c (12.1.0.2) – Tested in our env : 10.20.0.82
SQL> select instance_name,version from v$instance; INSTANCE_NAME VERSION ---------------- ----------------- ora12c 12.1.0.2.0
SQL> conn sys/sys as sysdba Connected.
SQL> conn hassan/(password); Connected.
SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST
SQL> select * from test; COLUMN1 NAME -------------------- -------------------- 1 abc 2 xyz
[oracle@ora12c u02]$ mkdir dump
[oracle@ora12c u02]$ chmod -R 777 dump/
[oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle. 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
SQL>; create directory dump as '/u02/dump/'; Directory created.
SQL> grant read,write on directory dump to system; Grant succeeded.
SQL> grant exp_full_database to system; Grant succeeded.
[oracle@ora12c dump]$ expdp system/stmora12c directory=dump dumpfile=hassan.dmp logfile=hassan.log schemas=hassan version=11.2 Export: Release 12.1.0.2.0 - Production on Wed Jan 25 16:25:41 2017 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 WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump dumpfile=hassan.dmp logfile=hassan.log schemas=hassan version=11.2 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/SYSTEM_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/STATISTICS/TABLE_STATISTICS . . exported "HASSAN"."TEST" 5.492 KB 2 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u02/dump/hassan.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 25 16:26:21 2017 elapsed 0 00:00:29 Verify:
[oracle@ora12c dump]$ ls hassan.dmp hassan.log
[oracle@ora12c dump]$ chmod -R 777 *
[oracle@ora12c dump]$ ls hassan.dmp hassan.log
======================
Import on 11g (11.2.0.4) – Tested in our env :
======================
SQL> select instance_name,version from v$instance; INSTANCE_NAME VERSION ---------------- ----------------- testdb 11.2.0.4.0
[oracle@TestDB ~]$ cd /u01
[oracle@TestDB u01]$ mkdir dump
[oracle@TestDB u01]$ chmod -R 777 dump
[oracle@TestDB u01]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 25 16:30:32 2017 Copyright (c) 1982, 2013, Oracle. 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
SQL> create directory dump as '/u01/dump/'; Directory created.
SQL> grant read,write on directory dump to system; Grant succeeded.
SQL> grant exp_full_database to system; Grant succeeded.
SQL> grant imp_full_database to system; Grant succeeded.
SQL> grant create any directory to system; Grant succeeded.
SQL> create user hassan identified by (password); User created.
SQL> grant connect,resource to hassan; Grant succeeded.
SQL> exit
[oracle@TestDB dump]$ impdp system/testdbpspetick dumpfile=hassan.dmp logfile=hassan.log directory=dump schemas=hassan Import: Release 11.2.0.4.0 - Production on Wed Jan 25 16:46:39 2017 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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** dumpfile=hassan.dmp logfile=hassan.log directory=dump schemas=hassan Processing object type SCHEMA_EXPORT/SYSTEM_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 "HASSAN"."TEST" 5.492 KB 2 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Jan 25 16:46:46 2017 elapsed 0 00:00:05
Verify:
SQL> conn hassan/hassan123; Connected.
SQL> select * from test; COLUMN1 NAME -------------------- -------------------- 1 abc 2 xyz
Hence tested in our Env