In our case we are upgrading from 9.2.0.8.0 to 11.2.0.3.0:
Target server: AIX 6.1 with oracle database 11.2.0.3.0
Source server: AIX 5.3 with oracle database 9.2.0.8.0
Steps:
1- Install Oracle database 11g binaries on the target server.
2- Copy the following scripts from the target server to the source server:
$ scp /Oracle/product/11.2.0/db/rdbms/admin/utlu112i.sql oracle@bahix02:/home/oracle/
$ scp /Oracle/product/11.2.0/db/rdbms/admin/utltzuv2.sql oracle@bahix02:/home/oracle/
3- 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 feedback off
SQL> set verify off
SQL> set echo off
SQL> set termout off
SQL> set pages 80
SQL> set heading off
SQL> set linesize 120
SQL> spool invalid_objects.sql
SQL> select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID';
SQL> spool off;
4- 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
SQL> set feedback off
SQL> set verify off
SQL> set echo off
SQL> set termout off
SQL> set pages 80
SQL> set heading off
SQL> set linesize 120
SQL> spool invalid_objects.sql
SQL> spool 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
5- DBLINKS
------
Create a script to save DBLINKS creation script:
SQL> set feedback off
SQL> set verify off
SQL> set echo off
SQL> set termout off
SQL> set pages 80
SQL> set heading off
SQL> set linesize 120
SQL> spool dblinks.sql
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#;
6- 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> shutdown immediate;
Apply the patch:
---------------
$cd /u01/oracle/ora9i/
$unzip p5632264_92080_timezoneV4.zip
$cd /u01/oracle/ora9i/5632264
$export PATH=$PATH:/oracle/OraHome9i/OPatch
$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
7- Run utlu112i.sql
-------
Run the script you've copied from 11g binaries in step 2:
$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.
8- Gather SYS schema statistics
------
Gather statistics for SYS schema:
SQL> @?/rdbms/admin/dbmsstat.sql;
SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.gather_schema_stats('SYS');
9- 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
10- 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';
11- 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;
12- 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');
13- 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 table SYS.AUD$;
14- 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
15- externally authenticated SSL
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 30 has to be followed after the upgrade
16- Set the DB in NoArchivelog mode
Put the database in noarchivelog mode to minimize the upgrade time.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> archivelog stop;
SQL> alter database open;
17- (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.
18- Transfer the DBFILES
------
$ sqlplus "/as sysdba"
SQL> alter database backup controlfile to trace as '/oracle/control.txt';
SQL> shutdown immediate;
Copy the control.txt + Datafiles + PFile from the 9i server to the new 11g server
19- Modify the 9i Pfile and control.txt file
------
=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=/oracle/product/ora11g/diagnostics/amexprd
When done copy the pfile to 11g ORACLE_HOME/dbs
Modify the control file as: Bold line is the modifications
shutdown abort;
STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "AMEXPRD" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 25
MAXLOGMEMBERS 3
MAXDATAFILES 101
MAXINSTANCES 1
MAXLOGHISTORY 5907
LOGFILE
GROUP 1 '/oradata/amexprd/redo01amexprd.log' SIZE 10M,
GROUP 2 '/oradata/amexprd/redo02amexprd.log' SIZE 10M,
GROUP 3 '/oradata/amexprd/redo03amexprd.log' SIZE 10M,
GROUP 4 '/oradata/amexprd/redo04amexprd.log' SIZE 10M,
GROUP 5 '/oradata/amexprd/redo05amexprd.log' SIZE 10M,
GROUP 6 '/oradata/amexprd/redo06amexprd.log' SIZE 10M,
GROUP 7 '/oradata/amexprd/redo07amexprd.log' SIZE 10M,
GROUP 8 '/oradata/amexprd/redo08amexprd.log' SIZE 10M,
GROUP 9 '/oradata/amexprd/redo09amexprd.log' SIZE 10M,
GROUP 10 '/oradata/amexprd/redo10amexprd.log' SIZE 10M
DATAFILE
'/oradata/amexprd/system01amexprd.dbf',
'/oradata/amexprd/undotbs01amexprd.dbf',
'/oradata/amexprd/indx01amexprd.dbf',
'/oradata/amexprd/tools01amexprd.dbf',
'/oradata/amexprd/users01amexprd.dbf',
'/oradata/amexprd/xdb01amexprd.dbf',
'/oradata/amexprd/onlsindx02amexprd.dbf',
'/oradata/amexprd/onlsindx01amexprd.dbf',
'/oradata/amexprd/onls02amexprd.dbf',
'/oradata/amexprd/onls01amexprd.dbf',
'/oradata/amexprd/onls03amexprd.dbf',
'/oradata/amexprd/owbrep01amexprd.dbf',
'/oradata/amexprd/desrep01amexprd.dbf',
'/oradata/amexprd/centwebportal01.dbf',
'/oradata/amexprd/SOA01.dbf',
'/oradata/amexprd/seweb01.dbf',
'/oradata/amexprd/ibmportal01.dbf',
'/oradata/amexprd/ICMLFQ32_01amexprd.dbf',
'/oradata/amexprd/ICMLNF32_01amexprd.dbf',
'/oradata/amexprd/ICMVFQ04_01amexprd.dbf',
'/oradata/amexprd/ICMLSNDX_01amexprd.dbf',
'/oradata/amexprd/ICMSFQ04_01amexprd.dbf',
'/oradata/amexprd/MONDSTS.dbf',
'/oradata/amexprd/MONDMSTS.dbf',
'/oradata/amexprd/MONIDXTS.dbf',
'/oradata/amexprd/MONLOBTS.dbf',
'/oradata/amexprd/IBMBPMBS_BSPACE.dbf',
'/oradata/amexprd/WSRR01amexprd.dbf',
'/oradata/amexprd/XSELL01.dbf',
'/oradata/amexprd/oem_repository.dbf',
'/oradata/amexprd/EVENT_cei_ts_extended',
'/oradata/amexprd/EVENT_cei_ts_base',
'/oradata/amexprd/BSPREGTABSPACE',
'/oradata/amexprd/BSPBS.dbf',
'/oradata/amexprd/SYSTEM12233.dbf',
'/oradata/amexprd/SYSTEMBPM.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE open upgrade;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/amexprd/temp01amexprd.dbf' ;
ALTER TABLESPACE EVENT_CEI_TS_TEMP ADD TEMPFILE '/oradata/amexprd/EVENT_cei_ts_temp' ;
CREATE TABLESPACE SYSAUX
DATAFILE '/oradata/amexprd/sysaux01.dbf' SIZE 2000M
autoextend on next 100m maxsize unlimited
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;
############################
Step 19: Create the Diagnostic directory for the DB
------
mkdir -p $ORACLE_HOME/diagnostics/amexprd
Step 20: Set 11g Env variables
------
Make sure that ".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
amexprd:/oracle/product/ora11g/: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=/oracle/oraInventory
inst_group=dba
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 '/oradata/amexprd/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
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 /oracle/product/ora11g/amexprd/udump
SQL> create or replace directory QUEST_SOO_UDUMP_DIR as '/oracle/product/ora11g/diagnostics/amexprd/diag/rdbms/amexprd/amexprd/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 = bahix10)(PORT = 1531))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/ora11g)
(SID_NAME = amexprd)
)
)
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=orapwAMEXPRD 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
