Database Migration using Transportable Tablespace with in the same server.

I have received a request for migration from one DB server to other and also to standardize(upgrade) the Database version to 11.2.0.4 .

Standard EXPDP and IMPDP is taking 15 hours (Storage I/O is slow) , post which application need 1 hour time to setup things (all togther 16 hours down time) and application team is not ready for it.

Assumptions:

----------------------------------------------------

1. My Current Stage server is on version 11.2.0.3(which need to be moved to live).

2. Database Size is around 900 GB.

3. My new Database version should be 11.2.0.4 and DB name need to changed as per the application requirement.

4. Current ORACLE HOME = /oracle/app/product/11.2.0/dbhome2

    New ORACLE_HOME = /oracle/app/product/11.2.0/dbhome3

5. We are using OMF for datafile storage, my db_create_file_dest is /user1/data/oradata/

Here is what the plan ,with minimal down time .

1. Create a new ORACLE HOME (11.2.0.4) on the same server with in new directory.

2. Create a new DB (NEW11204) on new ORACLE HOME.

3. Take the RMAN hot backup of CURRENT 11.2.0.3 Database, as a back out plan.

4.  Post getting the down time , make the CURRENT (11.2.0.3) database tablespaces( User Tablespaces) as READ ONLY.

4. Take expdp using TRANSPORTABLE TABLESPACE option.

5. Take export of sequences ( sequence using SYSTEM tablespace) .

6. Shut down the 11.2.0.3 Database.

7. Move the Datafile(s) from my CURRENT location to new Location, as I am using OMF , my new location would be /user1/data/oradata/NEW11204/datafile.

NOTE:I am using mv (move) not cp (copy) i.e renaming the directories.*

8. Create corresponding schemas related to the tablespace exported from 11.2.0.3 on 11.2.0.4 DB (NEW11204).

9.Import the tablespace(s) metadata by specifying the datafile location (new location).

10. Import the sequences.

11. Make the tablespace(s) READ WRITE.

12. Verfiy the object count , roles, privs .

13. Drop the 11.2.0.3 DB.

14. Rename  the Server.

Bingo done!.