Migration from Oracle 9iR2 (9.2.0.4) database to Oracle 11gR2 (11.2.0.4) with 3 Node Real Application Cluster with ASM and 2 Node RAC DR site on Linux RHEL in Minimum Downtime.

Today i am sharing my experiance of one of the major database migration from oracle 9iR2 single instance to 11gR2 3 Node RAC instances.

Existing Environment:
--- Customer was using more than 200 mount point for keeping datafiles.
--- Single Instance running on RHEL 32 bit Machine.
--- Poor performance during peak time.
--- No High Avilability

Proposed solution:
--- 3 Node RAC Instances on primary data centre (RHEL 64 bit)
--- 2 Node RAC Instances on DR site (RHEL 64 bit)

Before upgrade of Prod instance from 9ir2 to 11gR2 we need to do pre-requisite
============================================================

1) Created standby database from exxisting 9iR2
2) Syned with 9iR2 primary DB and opened standby DB in READ WRITE mode and Keft Primary (PROD) as it is for backout in case happened.

On Oracle 9iR2 DB on RHEL standby side
============================================================
 select owner,status,object_type from dba_objects
 where owner in('TEST1','TEST2','TEST3','TEST4','TEST5');

select 'alter user '||username||' default tablespace '||default_tablespace||';'
from dba_users where username in ('TEST1','TEST2','TEST3','TEST4','TEST5');

A containment violation occurs when a referenced object is not stored in the transportable set. 
The violations are usually caused by dependent or interdependent objects residing in different tablespaces. 
The following lists some of the object relationships that may cause containment violations

Check self content dependencies of all TABLESPACES
------------------------------------------------------
execute dbms_tts.transport_set_check('TEST1_ts,TEST2_ts,TEST3_ts,TEST4_TS,TEST5_ts',TRUE,TRUE,TRUE,TRUE,TRUE);

select * from transport_set_violations;


 alter tablespace  TEST1_ts read only;
 alter tablespace  TEST2_ts read only;
 alter tablespace  TEST5_ts read only;
 alter tablespace  TEST4_ts read only;
 alter tablespace  TEST3_ts read only;

Note == Add temporary tablespace

exp transport_tablespace=y tablespaces=(TEST1_ts,TEST2_ts,TEST3_ts,TEST4_ts,TEST5_TS) tts_full_check=y file=expdb_tts_table.dmp log=expdb_tts_table.log
exp transport_tablespace=y tablespaces=(TEST1_ts,TEST2_ts,TEST3_ts,TEST4_ts,TEST5_TS) file=expdb_tts_table.dmp log=expdb_tts_table.log

exp full=y file=fulldb.dmp rows=n


###########################################################

On oracle 11gr2 db on RAC
===========================
Create user and tablespace
===========================

create user TEST1 identified by test1
temporary tablespace temp
default tablespace xyz;

create user TEST2 identified by test2
temporary tablespace temp
default tablespace xyz;

create user TEST3 identified by t3st3
temporary tablespace temp
default tablespace xyz;

create user TEST4 identified by Test4
temporary tablespace temp
default tablespace xyz;

create user TEST5 identified by test5
temporary tablespace temp
default tablespace xyz;

Mount the filesytem where all datafiles of standby server on Oracle 11gR2 64 DB
===============================================================

NOTE: SAME FILESYSTEMS MOUNT POINT WILL BE ATTACHED FROM STANDBY DB TO 11GR2 SERVER (THIS WILL REDUCE TIME FOR COPYING DATAFILE FROM STANDBY 9iR2 SERVER TO NEW 11GR2 SQL

imp TABLESPACES=TEST3_ts,TEST5_TS,TEST2_ts,TEST4_ts,TEST1_ts transport_tablespace=y file=expdb_tts_table.dmp log=imp_expdb_tts_table.log datafiles=('/u03/test/oradata/TEST3s01.dbf','/u03/test/oradata/TEST5s01.dbf','/u03/test/oradata/TEST2s01.dbf','/u03/test/oradata/TEST4s01.dbf','/u03/test/oradata/TEST1s01.dbf','/u03/test/oradata/TEST1s02.dbf','/u03/test/oradata/TEST1s03.dbf','/u03/test/oradata/TEST1s04.dbf','/u03/test/oradata/TEST1s05.dbf','/u03/test/oradata/TEST1s06.dbf','/u03/test/oradata/TEST1s07.dbf')

imp file=fulldb.dmp ignore=Y

 Check_tablespace status
 ========================
 alter tablespace  TEST1_ts read write;
 alter tablespace  TEST2_ts read write;
 alter tablespace  TEST3_ts read write;
 alter tablespace  TEST4_ts read write;
 alter tablespace  TEST5_ts read write;


 sql> select tablespace_name, status, plugged_in from dba_tablespaces;

 Note: TEST5_ts ,TEST4_ts ,TEST3_ts,TEST1_ts,TEST2_ts  show "YES" in column PLUGGED_IN

  SQL> select name, status, plugged_in from v$datafile  <=== For check the datafile physical location

  STATUS====> It will show "ONLINE"

  PLUGGED_IN===> It contain the value "1"

SQL> select username,default_tablespace from dba_users where username in ('TEST1','TEST2','TEST3','TEST4','TEST5')

This scripts should be run oracle 9i
--------------------------------------
select 'alter user '||username||' default tablespace '||default_tablespace||';'
from dba_users where username in ('TEST1','TEST2','TEST3','TEST4','TEST5')

alter user TEST1 default tablespace TEST1_ts;
alter user TEST3 default tablespace TEST3_ts;
alter user TEST5 default tablespace TEST5_ts;
alter user TEST4 default tablespace TEST4_ts;
alter user TEST2 default tablespace TEST2_ts;

To check default tablespace
===============================================
select tablespace_name, status, plugged_in from dba_tablespaces;

======= Migration datafile from NON-ASM to ASM ========
select 'alter tablespace '||tablespace_name||' offline;' from dba_tablespaces
where tablespace_name in ('TEST3_ts','TEST5_TS','TEST2_ts','TEST4_ts','TEST1_ts');

TO CHECK tablespace
------------------------------------
 select file#,name from v$datafile;

 select  'copy datafile '||file#||'  to ''+ASMDB'';' from v$datafile where name like '/%';

 Connect to RMAN target /

 Run scripts copy datafile

 select  'switch datafile '||file#||' to copy;' from v$datafile where name like '/%';

 Connect to RMAN target /

 Run scripts Switch datafile

 The file is now if the new location
----------------------------------
 select name from v$datafile;

 select 'alter tablespace '||tablespace_name||' online;' from dba_tablespaces
 where tablespace_name in ('TEST3_ts','TEST5_TS','TEST2_ts','TEST4_ts','TEST1_ts');

 select tablespace_name, status, plugged_in from dba_tablespaces;

TO check object status "INVALID"  on Oracle 9i and oracle 11gR2
---------------------------------------------------------------
 select owner,status,object_type from dba_objects
 where owner in('TEST1','TEST2','TEST3','TEST4','TEST5');

Run "utlirp.sql" & "utlrp.sql" on oracle 11gR264
-----------------------------------------------
A) select object_name,status,owner from dba_objects
   where status='INVALID'

1) shutdown normal
2) startup upgrade
3) @?/rdbms/admin/utlirp.sql
4) shutdown normal
5) startup normal
6) @?/rdbms/admin/utlrp.sql

B) select object_name,status,owner from dba_objects
   where status='INVALID';

C) select count(*),object_name,owner from dba_objects
   where status='INVALID'
   group by object_name,owner;