Crash Restore Repeat - Edge of Tomorrow's Recovery (Part 1)

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

Upgrade to 11g using Import / Export

Datapump Import / Export

This procedure involves building a new database and importing all the application data from the old database to the new database.  Since the database is prebuilt as an 11g database there is no upgrade needed it is just the movement of data. This type of migration works best with smaller data sets. There needs to be at least enough space to equal the size of the current data (unless compression is implemented).  If there is limited network connectivity then enough space for dump files will need to exist.

Benefits

• When importing data from an earlier release, the Import utility of the new oracle database 11g release makes appropriate changes to the data definitions as it reads export dumps from earlier versions.
• Easy Fallback because Export/Import method does not change the source database. 
• Defragments the data.  You can compress the imported data to improve performance 
• Restructures the database.  Can create new tablespaces, modify existing, tables, tablespaces, or partitions.
• Enables the copying of specified objects or users allowing for the merging or separating of applications within databases.
• If a full database export to the file system is used then it can serve as a logical backup archive.
• Export can be done across the network and eliminate the need of creating export files..  this will not work on LONG/LONG RAW or nested tables…limited by network bandwidth
• Can change the database charactersets.**** It needs to be noted that changing charactersets does require a lot of research on the current database objects, procedures, triggers, data and application to make sure that it will not corrupt the data. It is not recommended to do this as part of the upgrade.****

Limitations

• In order to maintain data integrity database must remain in restricted mode (or otherwise protected from changes). 
• This creates an entire new database therefore any factors that might affect performance such as tuning parameters, disk placement of data, non-default initialization parameters need to be accounted for outside the upgrade process.
•  May take an exceptional period of time to accomplish.  (Probably the largest downtime of all options)
•  Does not handle XMLSCHEMA data types in 10.2
•  Standby database will need to be recreated.

Assumptions:

1.       Disk space exists for importing entire database

2.       Network stable and bandwidth large enough to handle network traffic or new servers can access disks export dumps are written to.

High Overview

1.       Create new database

2.       Perform full export using SYSTEM from SOURCE database (depends on size)

a.        Grants on SYS’s objects have to be exported separately (a few hours)

3.       Perform full import using SYSTEM to TARGET database (depends on size and method)

a.        Rebuild indexes (depends on the number of indexes but could be several hours)

4.       Verify all objects exist

5.       Backup Database / Recreate Standby Database

Visual Overview