expdp from lower version (11.2.0) and import into higher version (19.x) in pluggable DB
Posted by Mir Sayeed Hassan on June 25th, 2022
expdp from lower version (11.2.0) and import into higher version (19.x) in pluggable DB
Check the status of database.
SQL> select instance_name,version, open_mode from v$instance, v$database; INSTANCE_NAME VERSION OPEN_MODE ---------------- --------------------------------- testdb 11.2.0.4.0 READ WRITE
Create a directory at OS level
[oracle@TestDB ~]$ mkdir -p /u01/dumpfile_bkp/
[oracle@TestDB u01]$ chown oracle:oinstall dumpfile_bkp/
Create a directory at Database level
SQL> create directory dumpfile_bkp as '/u01/dumpfile_bkp/'; Directory created.
SQL> grant read, write on directory dumpfile_bkp to sys; Grant succeeded.
SQL> grant read, write on directory dumpfile_bkp to system; Grant succeeded.
For an example: Created a user “mir” & provide appropriate privilege to user as shown below.
Note:
-
In case if you have schema already exist in a database. No need to create..
-
You can use any schema of database to perform the expdp
SQL> create user mir identified by mirhassan123; User created.
SQL> select username, default_tablespace from dba_users where username='MIR'; USERNAME DEFAULT_TABLESPACE ---------------------------------- MIR USERS
Note: You can create a non-default tablespace & assign this user into it.
SQL> grant connect, resource to mir; Grant succeeded.
SQL> grant unlimited tablespace to mir; Grant succeeded.
SQL> connect mir/mirhassan123;
Connected.
SQL> show user;
USER is “MIR”
SQL> connect mir/mirhassan123;
Connected.
Create a table & insert some data into it.
SQL> create table test(eno number(10)); Table created.
SQL> insert into test values(1); 1 row created. SQL> insert into test values(2); 1 row created. SQL> insert into test values(3); 1 row created. SQL> insert into test values(4); 1 row created. SQL> insert into test values(5); 1 row created. SQL> commit; Commit complete.
SQL> select * from test; ENO ---------- 1 2 3 4 5
Export the schema “mir” from the lower version database 11.2.0. as shown below
[oracle@TestDB dumpfile_bkp]$ expdp directory=dumpfile_bkp dumpfile=mir_250622.dmp logfile=mir260622.log schemas=mir Export: Release 11.2.0.4.0 - Production on Sat Jun 25 17:19:45 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba 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 "SYS"."SYS_EXPORT_SCHEMA_02": /******** AS SYSDBA directory=dumpfile_bkp dumpfile=mir_250622.dmp logfile=mir260622.log schemas=mir 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/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 . . exported "MIR"."TEST" 5.085 KB 5 rows Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is: /u01/dumpfile_bkp/mir_250622.dmp Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Sat Jun 25 17:20:44 2022 elapsed 0 00:00:34
Hence the export of Schema “mir” is exported from the lower version of database.
Import the schema “mir” in version database 19.x. as shown below
Transfer the file from 11.20 db to 19.x db., by using the winscp or any other alternative method
Check the status of database
SQL> select instance_name,version, open_mode from v$instance, v$database; INSTANCE_NAME VERSION OPEN_MODE ---------------------------------------------------- 19cdb 19.0.0.0.0 READ WRITE
Create a directory at OS level & provide appropriate permission
[oracle@19cdb ~]$ mkdir -p /u01/dumpfile_bkp [oracle@19cdb ~]$ cd /u01/ [oracle@19cdb u01]$ chown oracle:oinstall dumpfile_bkp/
Create a directory at Database level
SQL> show user USER is "SYS"
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------------------------------------------------------------------- 2 PDB$SEED READ ONLY NO 5 PDB1 READ WRITE NO
Connect to Pluggable Database (PDB) & Create a database directory & provide appropriate privilege.
SQL> alter session set container=PDB1; Session altered.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------------------------------------------------------------- 4 PDB1 READ WRITE NO SQL> create directory dumpfile_bkp as '/u01/dumpfile_bkp/'; Directory created.
SQL> grant read, write on directory dumpfile_bkp to sys; Grant succeeded. SQL> grant read, write on directory dumpfile_bkp to system; Grant succeeded.
Create a user to import the dumpfile
SQL> create user mir identified by mirhassan321; User created. SQL> grant connect, resource to mir; Grant succeeded. SQL> grant unlimited tablespace to mir; Grant succeeded.
Note:
-
If you are using non default tablespace then its require to create., if not its not mandatory to create a above user., import will create a user automatically
Import the schema “mir” which is transfer from the lower version.
Verify the transfer file on 19c db
[oracle@shoptest ~]$ cd /u01/dumpfile_bkp/ [oracle@shoptest dumpfile_bkp]$ ls mir_250622.dmp
Start import of the schema “mir” to 19c Pluggable Database (PDB)
[oracle@shoptest dumpfile_bkp]$ impdp system/testdb123@dumptest directory=dumpfile_bkp dumpfile=mir_250622.dmp logfile=imp_mir_250622.log version=11.2.0 Import: Release 19.0.0.0.0 - Production on Sat Jun 25 09:24:37 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdb1 directory=dumpfile_bkp dumpfile=mir_250622.dmp logfile=imp_mir_250622.log version=11.2.0 Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"MIR" 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 "MIR"."TEST" 5.085 KB 5 rows Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Jun 25 09:25:05 2022 elapsed 0 00:00:19
Therefore the import is successfully completed from the lower version to higher version.
Verify the import
SQL> alter session set container=PDB1; Session altered. SQL> connect mir/mirhassan321@pdb1 Connected.
SQL> sho user USER is "MIR" SQL> select * from test; ENO ---------- 1 2 3 4 5
======Hence tested & verified in our test env======