11g Upgrade Option Transportable Tablespaces

This is the third Blog on 11g upgrade options when you are moving to new hardware on both the production and standby instances. 

This procedure is involves building a new database and pointing it to the old database files that hold the application data. Since the database is prebuilt as an 11g database there is no upgrade needed it is just the movement of data. There is a limited amount data that will be exported/imported but it relates to mainly to users and grants. This type of migration works best with smaller data sets. If there is limited network connectivity then enough space for some small dump files will need to exist and be moved between servers.

Benefits

• Works cross-platform and cross-Endians since Oracle 10g
•  Faster than exporting/importing 
• Tablespaces can be mounted from both databases simultaneously as long as they are READ ONLY as soon as a tablespace is set READ WRITE on the target database it will be be "lost" to the source

Limitations

•  Additional steps necessary to move views, synonyms, grants
•  System and sysaux tables can’t be transported
•  Databases must have identical charactersets

Assumptions:

1.       Method exists to move/copy files (datafiles/dumpfiles) between servers.

High Overview

Source System	Destination
Prepare SOURCE system	Install software and patches
Run Pre-Upgrade scripts	Create ‘empty’ database
Gather Source Database Information	Create USERs
Create USER scripts
Create EMP/IMP scripts
Set tablespace READ ONLY
Export TS metadata
Export non-segment obj.
Shutdown immediate
Copy datafiles	Import Tablespace metadata
	Import non-segment user objects*
	READ-WRITE tablespace
	Run ALTER USER script
	Recompile INVALID OBJECTS

Visual Overview