Migrating Oracle Enterprise Edition Database to Standard Edition using full export and import process - 19c or Previous releases

Migrating Oracle Enterprise Edition Database to Standard Edition using full export and import process

Its 19c Enterprise with PDB architecture to 19c Standard Edition PDB architecture, Same can be used with NON-CDB architecture also.

Make sure RUP level is same in source and target.

Source PDB name – DEV and same PDB name would be used in target.

1) Take full export of DEV PDB on the source Enterprise Edition

    During export sys schema objects are not exported.

    Create dba directory EXPPUMP

    nohup expdp system/manager@DEV directory=EXPPUMP FILESIZE=100GB COMPRESS=Y  dumpfile=DEV%U.dmp FULL=Y logfile=FULLEXP_DEV.log & 

     Took 2 hrs for 600 GB DB size.

    %U in dump file would automatically suffix the numbers like DEV01, DEV02         etc… after dump reaches FILESIZE=100GB

2) Use dbca to create 19c Standard Edition SE 2 CDB database with empty PDB.

    Took 1 hour

3) Create PDB with same name as of source - DEV

    Create pluggable database 

    Took 30 min

create pluggable database "DEV" admin user PDBADMIN identified by pass1234AB file_name_convert='/dev_data/oradata/pdb/pdbseed','/dev_data/oradata/DEV/datafiles');

4) Register the listener: 

alter system set local_listener='(address=(protocol=tcp)(host=test.vij.com)(port=1521))' scope=spfile;

alter system register;

Bounce the database and listener

alter pluggable database DEV close immediate;

alter pluggable database DEV open;

alter pluggable database DEV save state;

5) Import the database: 

 Time taken 6-7 hrs 

Create tablespaces in DEV (target) similar to those in DEV (source), you can check the tablespace list in export log above during full export.

Add 10 GB space temp tablespace to avoid import session from hanging.

Create dba directory EXPPUMP and copy the dump files during export in step 1.

nohup impdp system/manager@DEV directory= EXPPUMP dumpfile=DEV%U.dmp FULL=Y logfile=DEV_IMPORT_FULL.log &

Compile invalids using utlrp.sql

Check the invalids and compile using utlrp.sql

6) For pre 19c do full export and do full import , don’t use @DEV in expdp and  impdp.

7) After the Import in the Standard Edition database, you only need to drop all user schemas related to Enterprise Edition features if exists.