Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

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======