DB · TECH

Migrating an Oracle 11g Database to a 12c Pluggable Database using Data Pump

1- Export DB:

as oradb:
if application is not down and in order to make consistent import get DB SCN when you export:

$ sqlplus / as sysdba
> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)
—————————————-
236040412

Of course any changes happened after this SCN will be lost, but using SCN helps to test DB migration, for production DB you need to consider shutting down application or putting DB Tablespaces in read only mode.

expdp \’/ as sysdba\’ VERSION=12 full=Y flashback_scn=236040412 dumpfile=DATA_PUMP_DIR:export_${ORACLE_SID}.dmp logfile=DATA_PUMP_DIR:export_${ORACLE_SID}.log

2- Copy the dump file to target system

3- Generate ddl sql file from exported dump file:

impdp PDBADMIN/<password>@<service name>.tns DIRECTORY=DUMP_DIR dumpfile=export.dmp sqlfile=sample_ddl.sql

4- Using generated ddl.sql, create:

A- table spaces considering changing datafile if you are migrating from system datafile to ASM, you need to maintain same creation options

for example:

CREATE TABLESPACE “DATA” DATAFILE +DATAC1 SIZE <TABLESPACE SIZE>

B- Create Users owning Schemas:
Example:
CREATE USER “User1” IDENTIFIED BY VALUES ‘<Same value in SQL file>’
DEFAULT TABLESPACE “DATA”
TEMPORARY TABLESPACE “TEMP”
PROFILE “UNLIMITED”;

 

5- Create parameter file:

$ cat import.par
DIRECTORY=DUMP_DIR
FULL=YES
DUMPFILE=export.dmp
logfile=import.log

6- Run data import:
impdp PDBADMIN/<Password>@<service name>.tns parfile=import.par

7- Recompile All Invalid Objects

sqlplus “/ AS SYSDBA”
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

8- Check &Fix compilation errors:

Check for warnings and errors, usually system may complain about missing DB links, or objects owned by SYS, you need to check them one by one and resolve them.