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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s