Upgrade Oracle Database 10g to 11g walkthrough in detail, by upgrade paths

Contents

*/]]>

In-place migration using Database Upgrade Assistant

• Prerequisites and checks
• Preparation for RAC migration
• Restart DB
• DBUA Migration steps
  • Run DBUA
  • Check Objects

Data Pump

• Create new target database
  • Install Software
  • Create tablespaces in target database
  • Create Data Pump directory
  • Compare database parameters
    • Especially investigate on parameters
• Export data
  • Create Export Directory
  • Create Data Pump Parameter File
  • Restart Source database
• Export & Transport
  • Start Export
  • Move Dump File
• Import
  • Import Data

Aftermath

• Check Database Links
• Crontab Jobs
• Schemas & Objects

Data Guard

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.

Pre-Migration steps

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.

SQL> show parameter cluster

 NAME TYPE VALUE

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

 cluster_database boolean TRUE

SGA should at least be 1600MB.

SQL> show sga

 Total System Global Area 838860800 bytes

 Fixed Size 2055824 bytes

 Variable Size 473956720 bytes

 Database Buffers 348127232 bytes

 Redo Buffers 14721024 bytes

Parameter Files

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.

Additional backups

Take an additional backup of the oratab.

Save the output of:

Set environment

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 DB

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.

Stuff like:

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.

Miscallenous

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

Run DBUA

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.

Management Options

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.

Network configuration

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 11.2.0.1, 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.

Post-Upgrade

Review Oratab

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:

Check Objects

Recompile any invalid objects again.

Compare invalid objects with list created pre upgrade.

Check SPFile

Make sure:

1. an initSID.ora exists in $ORACLE_HOME/dbs
2. it points to the location of SPFile
3. an SPFile exists (in /dbs or ASM)

Configure Listener

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.

Directory registration

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.

Configure parameters

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.

Default Profile

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:

TNS

Verify chnaged done by DBUA to local TNS configuration or edit TNSNames configuration manually.

Unix

If you disabled any external (cron-) jobs during migration, you may no re-enable them now.

Network ACLS

Setup any Network ACLs, if defined.

Check availability

Perform a last general check through all layes of configuration.

$ tnsping OPS
 TNS Ping Utility for HPUX: Version 11.2.0.2.0 - Production on 25-NOV-2011 12:51:36
 Copyright (c) 1997, 2010, Oracle. All rights reserved.
 Used parameter files:
 /oracle/dbsystem/product/11.2_2/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1)(PORT = 
 1550))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ops.loopback.org)) ) OK (10 msec)

$ sqlplus scott/tiger@OPS

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 25 12:49:41 2011
 Copyright (c) 1982, 2010, Oracle. All rights reserved.
 ERROR:
 ORA-01017: invalid username/password; logon denied
 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Data Pump

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 Software

Install new 11g database software home and install a new Custom database using custom scripts or DBCA.

Create tablespaces in target database

Create Tablepspaces as in source database.
See Generate Create Tablespace DDL.
See Free.sql also to control available space.

Create Data Pump directory

Compare database parameters

Generate a pfile from both databases and compare.

Especially investigate on parameters

• All „_size“ – Parameters
• cursor_sharing
• db_domain
• db_files
• dbwr_io_slaves
• dml_locks
• fast_start_mttr_target
• fast_start_parallel_rollback

Global Names has consequences on the way database links are resolved.
If you get ORA-02085 after migration, try resetting this parameter.

• instance_name
• job_queue_processes
• log_buffer
• log_checkpoint_interval
• max_dump_file_size
• nls_date_format
• nls_length_semantics
• open_cursors
• open_links
• optimizer_index_caching
• optimizer_index_cost_adj
• os_authent_prefix
• pga_aggregate_target
• pre_page_sga
• processes
• query_rewrite_enabled
• session_max_open_files
• sga_target
• star_transformation_enabled
• tape_asynch_io
• timed_statistics
• undo_retention
• undo_tablespace

In many cases, these parameters have to be reset:

Export data

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

Start Export

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.

Import

Import Data

Aftermath

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.

Crontab Jobs

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.

SQL> select count(*) from dba_objects;
SQL> select count(*) from dba_objects where status = 'INVALID';

Check private and public synonyms.
Compare grants.

Data Guard

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.