Migrate and Upgrade Oracle 11gR2 Windows Database to Oracle 12cR2 Linux Database using Datapump
In this article, we are going to migrate the oracle database from windows to linux server with database upgrade from Oracle Database 11.2.0.1 to 12.2.0.1 using export/import.
DESCRIPTION:
1) Pre-checks in Source database
2) Export Source database
3) Pre-checks in Target database
4) Import into Target database
5) Post-checks in Target database
1) Pre-checks in Source database:
Step 1 :Check the Database Size in SOURCE:-
SQL> select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Size in GB”
from
dual;
Step 2 :Execute the below script to check schema objects are placed in which tablespaces except the default schemas:-
set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;
Step 3 :Execute the below script in Source Database to get the DDL of all the tablespaces except the default tablespaces:-
SET heading OFF;
SET echo OFF;
SET pages 990;
SET long 90009;
spool ddl_tablespace.sql
SELECT dbms_metadata.get_ddl(‘TABLESPACE’, tb.tablespace_name)
FROM dba_tablespaces tbas;
spool OFF
Step 4 :Compile Invalid Objects if any in SOURCE:-
SQL> @?/rdbms/admin/utlrp.sql
Check the invalid count now in SOURCE:-
SQL> select count(*) from dba_objects where status=’INVALID’;
2) Export Source database:
Export Source Database using expdp:-
Step 1 :Check Estimate Size of Dumpfile:-
Make sure free space is more than the estimated size in the export directory:
$ expdp directory=MOM_DIR full=Y nologfile=Y estimate_only=Y
Step 2 :Export Roles & Privileges:-
$ expdp directory=MOM_DIR dumpfile=WINDOWS_LINUX_ROLES.DMP logfile=WINDOWS_LINUX_ROLES.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE
Step 3 :Export the database in full and exclude the default schema to reduce the export time with “EXCLUDE” parameter as below:-
$ expdp directory=MOM_DIR dumpfile=EXP_WINDOWS_LINUX_FULL.DMP logfile=EXP_WINDOWS_LINUX_FULL.log full=Y EXCLUDE=SCHEMA:\”IN \(\’ANONYMOUS\’, \’APEX_030200\’, \’APEX_PUBLIC_USER\’, \’APPQOSSYS\’, \’CTXSYS\’, \’DBSNMP\’, \’DIP\’, \’EXFSYS\’, \’FLOWS_FILES\’, \’MDDATA\’, \’MDSYS\’, \’MGMT_VIEW\’, \’OLAPSYS\’, \’ORACLE_OCM\’, \’ORDDATA\’, \’ORDPLUGINS\’, \’ORDSYS\’, \’OUTLN\’, \’OWBSYS\’, \’OWBSYS_AUDIT\’, \’SCOTT\’, \’SI_INFORMTN_SCHEMA\’, \’SPATIAL_CSW_ADMIN_USR\’, \’SPATIAL_WFS_ADMIN_USR\’, \’SYS\’, \’SYSMAN\’, \’SYSTEM\’, \’WMSYS\’, \’XDB\’, \’XS$NULL\’\)\”
Once the export is completed check the log file and make sure no errors in it and then both the dumpfiles to the target server.
3) Pre-checks in Target database:
Step 1 :
Create a fresh database in the Target Server as described here with appropriate characterset, in order to avoid characterset conversion error.
Step 2 :
Create the required tablespaces as we already taken the tablespace DDL from SOURCE in Step 3 of Pre-checks in SOURCE.
Step 3 :
Create a directory for import on the target server in OS and database level:-
$ mkdir -p /u02/dpdump/LINUX_MIG
SQL> create directory DPDUMP as ‘/u02/dpdump/LINUX_MIG’;
SQL> select * from dba_directories;
4) Import into Target database:
Step 1 :
Import Roles & Privileges:-
$ impdp directory=DPDUMP dumpfile=WINDOWS_LINUX_ROLES.DMP logfile=WINDOWS_LINUX_ROLES.log full=Y
Step 2 :
Import the database:-
$ impdp directory=DPDUMP dumpfile=EXP_WINDOWS_LINUX_FULL.DMP logfile=IMP_WINDOWS_LINUX_FULL.log full=Y
Once the import is completed the review the log file for any errors.
5) Post-checks in Target database:
Step 1 :
Compile Invalid Objects in TARGET:-
SQL> @?/rdbms/admin/utlrp.sql
Once done with above steps execute below command to verify target database:-
SQL> select count(*) from dba_objects where status=’INVALID’;
Step 2:
Query V$OPTION to get currently installed database options:-
SQL> select * from V$OPTION where value=’TRUE’ order by 1;
Step 3 :
Query DBA_REGISTRY to get currently installed database components:-
SQL> select * from DBA_REGISTRY;
Step 4 :
Create database link to the source database to compare the schema object count in source & target :-
SQL> CREATE DATABASE LINK “SOURCE.DBLINK”
CONNECT TO “SYSTEM” IDENTIFIED BY <Password>
USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
oracledbwr101)(PORT = 1521)))
(CONNECT_DATA = (SID = WINORCL)))’;
Step 5 :
Execute the below scripts in target and verify the count of objects manually:-
Below script will provide the object count of schemas in SOURCE except the default schemas:
SQL>set pages 990
col “size MB” format 999,999,990
col “Objects” format 999,999,990
select obj.owner “Owner”, obj_cnt “Objects”, decode(seg_size, NULL, 0, seg_size) “size MB”
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;
Below script will provide the object count of schemas in TARGET except the default schemas:
SQL>set pages 990
col “size MB” format 999,999,990
col “Objects” format 999,999,990
select obj.owner “Owner”, obj_cnt “Objects”, decode(seg_size, NULL, 0, seg_size) “size MB”
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;
Step 6 :
Check the CONSTRAINTS count in both source & target:-
SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type;
Catch Me On:- Hariprasath Rajaram
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
