Upgrade Oracle Database 10g to 11g walkthrough in detail, by upgrade paths
In-place migration using Database Upgrade Assistant
In any standard cases, live migration with DBUA is recommended.
Prerequisites and checks
To be checked well in advance to the migration date.
Check time zone
Caution with source databases version < 10.2.0.4
Check for Streams errors
Check for Archive Log Mode
Check free space
Check for backup in place
check with active backup mechanism
Check for enough space for additional cold backup during migration
Check for internal / evil parameters
Investigate necessarity of network connections
Are there any network connections to be initiated from within the database ?
With DB 11g, network ACLs (access control lists) have been introduced.
Network connections and usage of certain packages like UTL_SMTP or UTL_TCP are restricted ba ACLS. If these are present in the 10g source databasem the ACLs have to be defined.
For example: connections to an external mail server.
To be performed during the migration date.
Get AWR report
Get an AWR report of, say, the last two weeks, to have performance related numbers in case a comparison is necessary after the upgrade.
Adjust SYSTEM ans SYSAUX tablespaces
Have at least 256MB available on SYSTEM ans SYSAUX tablespaces.
Or, in case of ASM:
Adjust memory-related parameters
Both SGA_TARGET and SGA_MAX_SIZE are recommended to have 750M as target size, usually.
In case DBUA crashes with error "sga_target is bigger then sga_max_size", it has been useful to set SGA_MAX_SIZE bigger, like 1G, disregarding the meaning of the error message...
Adjust JAVA_POOL_SIZE and Sharted Pool Size:
Preparation for RAC migration
Parameters for RAC
If migration a Real Application Cluster database, make sure the parameter "cluster_database" is set when migrating via DBUA.
SGA should at least be 1600MB.
Create a spare pfile.
Make sure there is a parameter file in $ORACLE_HOME/dbs, which is pointing to, if possible, the spfile inside ASM.
Take an additional backup of the oratab.
Save the output of:
Unset the Oracle environment
Check for dependencies to ACLs
For these schemas, network ACLs may have to be created after the upgrade.
Purge Recycle Bin
Recompile invalid objects
Create dictionay statistics
In Oracle 10g:
In Oracle 9i:
Restart the database to make to parameter changes active.
You max cross-check you are connect to the correct database before:
To restart a single instance database:
To restart a RAC database:
Run Pre Upgrade script
Run Pre-Upgrade-Script from Ortacle 11g home.
Analyse the output.
Most of the tips told here have already been taken care of.
will be taken care of later by the Upgrade Assistant.
Take care when Underscore Parameters are found, i.e.:
Note invalid objects
Take a note of still invalid objects for later reference.
Check for periodic OS jobs
Check for UNIX jobs ruinning and affecting the database, and un-schedule them.
Create a directory for cold backup
Create a directory for the cold backup the Upgrade Assistant will perform later.
Drop some synonyms which trigger certain bugs:
If this sysnonym was present, also issue:
see also: MOS note: 1271490.1
Set 11g Oracle Home
Make sure you are connected with X11 forwarding / tunneling.
DBUA Migration steps
If the tools do not start with Java Null Pointer Exception, try setting ulimit.
See Java Null Pointer Exception.
Select database to migrate
Ignore the invalid objects warning
The messages shown should correspond to the results we got earlier in the pre-migration phase.
Select upgrade options
Do not necessarily select the most extensive degree of parallelism.
You can turn off archiving during upgrade to gain performance, since the database is backued up just before the upgrdae.
But do not do this if Streams is using in DB.
Do upgrade the timezone thing.
Backup the database and select the directory you create before.
Move Database Files
Decide on wether you want to migrate data to ASM.
Recovery and Diagnostic Locations
Decide on wether you want to introduce a Fast Recovery Area (FRA), which is recommended.
Decide on wether to configure Database Control (aka Enterprise Manager), or let your database join Grid Control, which is recommended in case you do have an installation.
Decide wether you want to register the database to any directory (if you have one), and which Listeners you want to be configured automatically.
You can configure the Listeners later, manually.
In this case, select the Listeners tab and select "Register the database with selected listeners only" and do not select any listeners.
Review the summary
Review the summary page and Finish the upgrade.
Errors doring DBUA run
IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED
11GR2 DBUA ORA-06550 PLS-00201 IDENTIFIER SYS.DBMS_JAVA MUST BE DECLARED
See MOS note: ID 1066828.1
When upgrading from 10.2.0.4 to 18.104.22.168, the following error is returned:
Cause: Bug 9315778 - DBUPGRADE: ORA-06550 SYS.DBMS_JAVA NOT DECLARED
Ignore the error if the upgrade is still running or completed, or install patch 9315778 before running the upgrade
sga_target is bigger then sga_max_size
Restore the backup with DBUA, set SGA_MAX_SIZE way bigger then SGA_TARGET and retry DBUA.
Review the Results
Review the DBUA Upgrade Results page and Close DBUA.
Review the entry in Oratab (/etc/oratab or /var/opt/oratab) DBUA has created, and check it reflects the correct settings regarding autostart und Oracle Home.
Post Upgrade Script
Login with a fresh shell and review your Unix environment is set correct.
Run Sqlplus and see everything looks correct.
Run the Post Upgrade Script:
Recompile any invalid objects again.
Compare invalid objects with list created pre upgrade.
- an initSID.ora exists in $ORACLE_HOME/dbs
- it points to the location of SPFile
- an SPFile exists (in /dbs or ASM)
Check Listener registration DBUA performed, or create them manually if you skipped DBUA Listener configuration.
Check which listeners are running:
Use the name of your listener.
Sometimes, the Listener refuses to add a new entry without complanining.
Stop and start the Listener in these cases.
If you have different Listeners for 10g and 11g installations on your machine, you have to reload both listeners.
Review the directory registration DBUA did, if you configured it do do any.
Optionally, register the database with OID manually using netmgr.
In netmgr, numeric keys and backspace key do not work.
Pay special attntion the correct port is listed.
Restart the database to get the SPFile right:
Set COMPATIBLE parameter, and verify wether LOCAL_LISTENER is needed.
Use srvctl in case of RAC.
LOCAL_LISTENER may not be needed if Data Guard is not in place.
From the moment you set COMPATIBLE to 11.x no downgrade is possible any more.
It is possible to let COMPATIBLE be 10.x for test purposes.
Verify the Default Profile is set accoding to your local policies.
Create any 11g Profiles you might have in your enterprise for security purposes, like e.g. password verification.
You may optionally reset all accounts to have a fresh starting point in time for password aging:
Fixed Table Stats
Renew Fixewd Table Statistics:
Verify chnaged done by DBUA to local TNS configuration or edit TNSNames configuration manually.
If you disabled any external (cron-) jobs during migration, you may no re-enable them now.
Setup any Network ACLs, if defined.
Perform a last general check through all layes of configuration.
I the new 11g database system can be build in parallel on separate hardware, Export / Import (via Data Pump) is a possible migration scenario.
It is very helpful for the scenario to have proper create scripts for the database schemas and objects.
Tablespaces and Schemata are recommended to be created before any import is done.
Is is alos possible to do a full database import from Oracle 10g to 11g, but this is not recommended, since SYS objects are often also imported accidentially.
Create new target database
Install new 11g database software home and install a new Custom database using custom scripts or DBCA.
Create tablespaces in target database
Create Data Pump directory
Compare database parameters
Generate a pfile from both databases and compare.
Especially investigate on parameters
- All "_size" - Parameters
Global Names has consequences on the way database links are resolved.
If you get ORA-02085 after migration, try resetting this parameter.
In many cases, these parameters have to be reset:
Create Export Directory
Create Data Pump Parameter File
Restart Source database
Restart Sorce database in Restricted Mode to prevent users from logging in if schedules and possible.
In RAC environments, use srvctl to shutdown all nodes, of course.
Export & Transport
You can schedule this with "at", but take care of the environment.
Move Dump File
Move the dump file to the new system, using scp or ftp or cp.
Check Database Links
Compare and check all database links.
Check for all TNS configuration entries on the source DB being necessary for database links on target DB.
Transfer all external Jobs and Programs necessary to the new system.
Schemas & Objects
Check wether all users and schemas have been transfered.
Compare all objects and there validity.
Check private and public synonyms.
This is the preferred scenario for upgrades in HA-environments where parallel build of the 11g system is possible hardware-wise and downtime shall be minimal.