Upgrade from Oracle 9i to Oracle 11g

The following is a real scenario for upgrading a 9.2.0.8 DB to 11.2.0.1.0 on RedHat Enterprise Linux 4 in 34 Steps.

First please check Metalink [ID 730365.1]
This note is a very great one. It Includes all patchsets download + How to upgrade from any Oracle DB version to another one.

Required packages for installing 11gR2 software: On RedHat Enterprise Linux 4  x86-64
----------------------------------------------------------------
(install the same packages versions or later)

binutils-2.15.92.0.2
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.97
elfutils-libelf-devel-0.97
expat-1.95.7
gcc-3.4.6
gcc-c++-3.4.6
glibc-2.3.4-2.41
glibc-2.3.4-2.41 (32 bit)
glibc-common-2.3.4
glibc-devel-2.3.4
glibc-headers-2.3.4
libaio-0.3.105
libaio-0.3.105 (32 bit)
libaio-devel-0.3.105
libaio-devel-0.3.105 (32 bit)
libgcc-3.4.6
libgcc-3.4.6 (32-bit)
libstdc++-3.4.6
libstdc++-3.4.6 (32 bit)
libstdc++-devel 3.4.6
make-3.80
numactl-0.6.4.x86_64
pdksh-5.2.14
sysstat-5.0.5

*If you will install version 11.2.0.2 instead of 11.2.0.1 (11.2.0.1 is the one I installed in this demonstration) the packages in pink color are no longer needed.

Required packages for installing 11gR2 software: On RedHat Enterprise Linux 5  x86-64
----------------------------------------------------------------
(install the same packages versions or later)

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
elfutils-libelf-devel-static-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel 4.1.2
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11 (32 bit)
unixODBC-devel-2.2.11
unixODBC-devel-2.2.11 (32 bit)
numactl-devel-0.9.8.i386

*If you will install version 11.2.0.2 instead of 11.2.0.1 (11.2.0.1 is the one I installed in this demonstration) the packages in pink color are no longer needed.

In this post I'll not go through the Oracle software installation as it's not the scope of this post,
I've installed the 11gR2 software on a new server and applied the latest CPU patch. So some upgrade steps will run on the 9i server and the rest will run on the new 11g server.

Note: You can apply the CPU patch on the 11g ORACLE_HOME before having a database as a pre-upgrade step. Check Metalink Note [ID 461082.1]

Database upgrade steps from 9i to 11g:
#################################


Step 1: (On the 11g Server)
--------
Login to 11g server and copy the following scripts from 11g ORACLE_HOME/rdbms/admin directory on 11g server to 9i server:

$scp /u01/oracle/ora11g/11.2.0.1/rdbms/admin/utlu112i.sql oracle@9i_server:/home/oracle
$scp /u01/oracle/ora11g/11.2.0.1/rdbms/admin/utltzuv2.sql oracle@9i_server:/home/oracle

HHHHHHHHHHHHHHHHHHHHHHHHHHHH

Following steps will be run on the 9i server:

HHHHHHHHHHHHHHHHHHHHHHHHHHHH

Step 2:
--------
=Run utlrp script to fix any invalid object before the upgrade:
--------------------------------------------------------------------------
SQL> @?/rdbms/admin/utlrp.sql

Note down invalid objects to compare with invalid ones after the upgrade to 11g:
--------------------------------------------------------------------------------------------------
SQL> set pages 0
SQL> set linesize 170
SQL> select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID';

Step 3: Save a script for Deprecated CONNECT Role:
--------
In 11g version CONNECT role has only the "CREATE SESSION" privilege, the other privileges are NOT exist in CONNECT role in 11g version like:

CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

So you need to re-grant these privileges to users who have connect role after upgrade to 11g.
This SQL will help you just save the result in somewhere to run it after the upgrade:

SQL> set linesize 170
SQL> set pages 100
SQL> spool /home/oracle/connect_role.sql
SQL> SELECT 'grant CREATE VIEW,CREATE TABLE,ALTER SESSION,CREATE CLUSTER,CREATE SYNONYM,CREATE SEQUENCE to '||grantee||';'
FROM dba_role_privs WHERE granted_role = 'CONNECT' AND grantee
NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR',
'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST',
'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR',
'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA',
'XDB', 'ODM');
SQL> spool off

Step 4: DBLINKS
--------
Create a script to save DBLINKS creation script:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

Step 5: Upgrade the TIMEZONE version:
---------
Convert the 9i database TIMEZONE from version 1 to version 4:

Download this interm patch 5632264 .. Extract it .. run "opatch apply" command .. As the following:

First shutdown the database:
---------------------------
SQL> shu immediate;

Apply the patch:
---------------
$cd /u01/oracle/ora9i/
$unzip p5632264_92080_timezoneV4.zip
$cd /u01/oracle/ora9i/5632264
$opatch apply

Start up the database again:
---------------------------
SQL> startup

The following query must return version 4:

SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
----------
4

Step 6: Run utlu112i.sql
---------
Run the script you've copied from 11g binaries in step 1:

$cd /home/oracle/

$sqlplus "/ as sysdba"

SQL> spool utlu112i.log
SQL> @utlu112i.sql
SQL> spool off


This script will give you information about tablespaces if they need to be adjusted according to 11g requirements ,also gives you information regarding the parameters need to be modified, Obsolete/Deprecated parameters and deprecated roles like connect role.

Step 7: Gather SYS schema statistics
--------
Gather statistics for SYS schema:
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);


Step 8: Check the Dictionary
--------
Check for any corruption in the dictionary -will create a script then will run it-:

Script Creation:
Following code creates a script called analyze.sql


Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

Run the following scripts for Dictionary checking:

SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

Step 9: Database consistency
--------

Ensure there is no files need media recovery or in backup mode:

SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status!='NOT ACTIVE';

Step 10:
----------

Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

If that query returned rows you should do the following:
--
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;


Step 11:
----------
Ensure the users sys and system have 'system' as their default tablespace.

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');

Step 12:
----------
Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';

Tip: Truncating AUD$ table will speedup the upgrade process (make sure to backup audit data before truncating it)
SQL> truncate SYS.AUD$; 

Step 13: Saving database files location
----------
Note down the location of datafiles, redo logs, control files.

SQL> col name for a70
SQL> col file_name for a70
SQL> col member for a70
SQL> set feedback off
SQL> spool db_files.txt
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
SQL> spool off

Step 14:externally authenticated SSL users
-----------
Check whether database has any externally authenticated SSL users.

SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';

If any SSL users are found then Step 31 has to be followed after the upgrade

Step 15: Set the DB in NoArchivelog mode
----------
Put the database in noarchivelog mode to minimize the upgrade time.

$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> alter database noarchivelog;
SQL> archive log stop;
SQL> shutdown immediate;

Step 16: (Optional) It's recommended to take a cold backup 
----------
If you upgrading the database on the same server -which is not my scenario here- this step will be mandatory for fallback strategy.

Step 17: Transfer the DBFILES
----------
Copy the Datafiles + PFile from the 9i server to the new 11g server -in case you will upgrade your 9i DB in a new server-


HHHHHHHHHHH
On The 11g Server:
HHHHHHHHHHH

Step 18: Modify the 9i Pfile
----------
=Comment out obsoleted parameters like:

transaction_auditing
hash_join_enabled
DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS

=Replace all deprecated parameters like:

BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
STANDBY_ARCHIVE_DEST
log_archive_start
max_enabled_roles
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)

=set the COMPATIBLE parameter to 10.1.0
compatible=10.1.0

=set the parameter CLUSTER_DATABASE=FALSE

=Add the parameter DIAGNOSTIC_DEST to hold database logs:
diagnostic_dest=/u01/oracle/ora11g/11.2.0.1/diagnostics/ORCL

When done copy the pfile to 11g ORACLE_HOME/dbs

Step 19: Create the Diagnostic directory for the DB
----------
mkdir -p $ORACLE_HOME/diagnostics/ORCL

Step 20: Set 11g Env variables
----------
Make sure that ".bash_profile" holds the new 11g installation environment variables like:
ORACLE_BASE
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
TNS_ADMIN
CLASSPATH
PATH

These variables are supposed to be exist since the 11gr2 installation on that server, If you are doing the upgrade on the same server you must create a new profile something like ".11g_bash_profile" holding all variables for 11g installation.

Step 21: Update oratab & oraInventory
----------
Update the oratab file:

$vi /etc/oratab

#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g/11.2.0.1:Y

-Hash the entry for 9i ORACLE_HOME in case you upgrading on the same server then add the an entry for 11g ORACLE_HOME

Update oraInventory location:
-------------------------------------
$vi /etc/oraInst.loc

inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall


Step 22: Upgrading the Database to 11gR2...
----------

Note: If you are upgrading on the same server run the profile holds the 11g environment variables -you prepared before- for the 11g installation.

Start up the DB in upgrade mode:
---------------------------------------
SQL> startup UPGRADE

If you encounter a message listing obsolete initialization parameters in this stage remove the obsolete initialization parameters from the parameter file.

Create the SYSAUX tablespace:
----------------------------------------
SQL>CREATE TABLESPACE SYSAUX
DATAFILE '/fiber_ocfs_data_1/oracle/ORCL/sysaux01.dbf' SIZE 2000M reuse
autoextend on next 100m maxsize unlimited
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

Execute the upgrade script:
--------------------------------
$cd $ORACLE_HOME/rdbms/admin

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

Database will shutdown by catupgrd.sql script. Restart the Instance to reinitialize the system parameters for normal operation.

Run the Post-Upgrade Status Tool:
--------------------------------------------
SQL> @?/rdbms/admin/utlu112s.sql

Recompile any remaining stored PL/SQL:
---------------------------------------------------
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql script:
------------------------------------------------------------------------------------------------------
Download that script from Metalink Note [ID 556610.1] and run it.

During the test I found some duplicate objects between SYS and SYSTEM so I followed the Metalink Note and dropped duplicate objects owned by SYSTEM user:

Warning:
Don't run the following commands unless you make sure that following objects are duplicate ones in your DB.

SQL> drop TABLE system.AQ$_SCHEDULES;
SQL> drop INDEX system.AQ$_SCHEDULES_PRIMARy;
SQL> drop PACKAGE system.DBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.BODYDBMS_REPCAT_AUTH;
SQL> drop PACKAGE system.DBMS_SHARED_POOL;
SQL> drop PACKAGE system.BODYDBMS_SHARED_POOL;
SQL> drop TABLE system.UTL_RECOMP_INVALID ;
SQL> drop TABLE system.UTL_RECOMP_SORTED;
SQL> drop TABLE system.UTL_RECOMP_COMPILED;
SQL> drop TABLE system.UTL_RECOMP_BACKUP_JOBS;
SQL> drop TABLE system.UTL_RECOMP_LOG;
SQL> drop PACKAGE system.UTL_RECOMP;
SQL> drop PACKAGE system.BODYUTL_RECOMP;
SQL> drop TABLE system.CHAINED_ROWS;

Step 23:
----------
Grant the missing privileges related to "connect" which been deprecated in 11g:
---------------------------------------------------------------------------------------------------
-Using Script been created in Step3-
SQL> @/home/oracle/connect_role.sql


Re-point the directories to the new ORACLE_HOME:
-------------------------------------------------------------------
As the ORACLE_HOME path became different, The directories that point to old ORACLE_HOME should point to the new ORACLE_HOME:

E.g:

SQL> col DIRECTORY_PATH for a80
SQL> SELECT * FROM dba_directories;

OWNER DIRECTORY_NAME        DIRECTORY_PATH
---------------  ------------------------------ ------------------------------------------
SYS QUEST_SOO_UDUMP_DIR            /u01/oracle/ora9i/admin/orcl/udump

SQL> create or replace directory QUEST_SOO_UDUMP_DIR as '/u01/oracle/ora11g/11.2.0.1/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/';

##################
Post Upgrade Steps:
##################

Step 24:
----------
Check listener.ora for any modifications needed to listen on the upgraded DB.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pc-ora-test)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/ora11g/11.2.0.1)
(SID_NAME = ORCL)
)
)

Start up the listener:
------------------------
$lsnrctl start

Step 25: Create the SPFILE & Password file
-----------
Create the SPFILE:
-----------------------
SQL> create spfile from pfile;

Create the Password File:
------------------------------
$cd /u01/oracle/ora11g/11.2.0.1/dbs

$orapwd file=orapwORCL password=

Step 26: Compatibility version
----------
Change the compatibility version to use the new 11g features:

alter system set compatible='11.2.0.1' scope=spfile;

shu immediate;
startup;

Step 27: Load Product user profile information
----------
This step is needed to tackle the error "Error accessing PRODUCT_USER_PROFILE" when you login to SQLPLUS.

Running script pupbld.sql as SYSTEM user:

SQL> conn system/xxx
SQL> @/u01/oracle/ora11g/11.2.0.1/sqlplus/admin/pupbld.sql

Step 28: Rebuild unusable indexes
----------
Using this script:
SQL> select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online parallel 4;'from dba_indexes where status ='UNUSABLE';

Step 29: Gather Statistics on the database
----------
This step is very crucial for DB performance.

1-Gather DICTIONARY stats:
- ----------------------------------
SQL> Execute DBMS_STATS.GATHER_DICTIONARY_STATS ();

2-Gather DATABASE statistics:
- -------------------------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 4);

3-Gather FIXED OBJECTS stats: <Do this step after the upgrade and during production workload time*>
- --------------------------------------
SQL> Execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (no_invalidate => FALSE );

Note:"gather fixed objects statistics" is not included in gather_dictionary_stats, also not included in the the daily gather statistics job. The database does not gather these statistics automatically, So you have to gather it manually, but before gathering Fixed Objects stats read the following notes:

*Note: 
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours as long as the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity. 
-Performance degradation may be experienced while the statistics being gathered.

So after reading those notes I recommend you to re-gather Fixed Objects Statistics few days after the upgrade during the database workload time.

Step 30: Fix invalid objects:
-----------
SQL> @?/rdbms/admin/utlrp

Step 31: In case you have externally authenticated SSL users in the DB (not in my scenario)
-----------
You need to upgrade externally authenticated SSL users using this command:

$ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring &lt;hostname:port_no:sid> --dbuser &lt;db admin> --dbuserpassword &lt;password> -a

Step 32: Lock Oracle default accounts (Optional)
-----------
-Oracle recommends to lock all Oracle supplied accounts except SYS and SYSTEM.
-Never lock these two accounts DBSNMP & SYSMAN or the Enterprise Manager will not work.

List of default accounts and their description:

User             Description
----              -----------
SYS:           All of the base tables and views for the database's data dictionary are stored in the schema 
                   SYS.
SYSTEM:    Contains additional tables and views that display administrative information and used by
                    Oracle tools.
DBSNMP:  Supports Oracle SNMP (Simple Network Management Protocol).
OUTLN:     Centrally manage metadata associated with stored outlines.
MDSYS:     Supports Oracle Spatial.
ORDSYS:   Supports Oracle interMedia.
ORDPLUGINS: Supports Oracle interMedia.
CTXSYS:   Supports Oracle ConText for advanced linguistic processing of English-language text.
DSSYS:     Dynamic Services Secured Web Service.
WKSYS:   Support Oracle's Ultrasearch option.
WMSYS:   Store all the metadata information for Oracle Workspace Manager.
XDB:        Support SQL XML management: XML DB
ODM:       Support Oracle Data Mining.
OLAPSYS: Used to create OLAP metadata structures.
REPADMIN:  Replication user.
ANONYMOUS: Support SQL XML management.

Please check this link for full description:
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml

Step 33: Check for Oracle recommended patches (Optional)
-----------
Metalink Note ID 756671.1 includes the recommended patches starting from release 10.2.0.3 and above.

Also: Note ID 742060.1 includes the most accurate information Oracle can provide for coming releases.

Step 34: The End.
----------
Have Fun ;-)