Compatibility Matrix
Database Upgrade
is common but risky task for a DBA if not done properly. Here, I am listing
detailed method of upgrade with verification and validation.
Minimum Version
of the Oracle database software that can be directly upgraded to Oracle 11g
Release 2, so before upgrade remote DBA needs to check this.
Source Database Target Database
9.2.0.8 or
higher 11.2.x
10.1.0.5 or
higher 11.2.x
10.2.0.2 or
higher 11.2.x
11.1.0.6 or
higher 11.2.x
The following
database software version will require an indirect upgrade path. In this case
DBA needs to do double effort, because two upgrades are needed.
Source Database ---> Upgrade
Path for Target Database--->Target Database
7.3.3 (or
lower)-----> 7.3.4 ----> 9.2.0.8 ---->11.2.x
8.0.5 (or
lower)----> 8.0.6 ----> 9.2.0.8 ---->11.2.x
8.1.7 (or
lower)----> 8.1.7.4---> 10.2.0.4---->11.2.x
9.0.1.3 (or
lower)----> 9.0.1.4---> 10.2.0.4---->11.2.x
9.2.0.7(or
lower)---->9.2.0.8----> 11.2.x
Here I am
upgrading my Oracle 10.2.0.4 database software to Oracle 11.2.0.3
1. Copy Pre upgrade Information gathering Script:
Log in to the system as the owner of the
Oracle Database 11g Release 2 (11.2) Oracle home directory. Copy the Pre-Upgrade Information Tool
(utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2)
ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home,
such as the temporary directory on your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2. Execute Pre Upgrade Script:
Change to the
directory where utlu112i.sql had been copied in the previous step. Start SQL*Plus and connect to the database
instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql
file. Please note that the database should be started using the Source Oracle
Home (i.e. from oracle 10g home).
$ sqlplus '/ as
sysdba'
SQL> spool
upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
Check the spool
file and examine the output of the upgrade information tool.
3. Check for the integrity of the source database:
Check for the
integrity of the source database prior to starting the upgrade by downloading
and running dbupgdiag.sql script from below My Oracle Support article
Note
556610.1 Script to Collect DB
Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Ignore this step if you don’t have support access)
If the
dbupgdiag.sql script reports any invalid objects, run
$ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid
objects in the database, until there is no change in the number of invalid
objects.
$ cd
$ORACLE_HOME/rdbms/admin
$ sqlplus
"/ as sysdba"
SQL>
@utlrp.sql
After validating
the invalid objects, re-run dbupgdiag.sql in the database once again and make
sure that everything is fine.
4. Check that National Characterset:
Check that National
Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.
select value
from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
If this is UTF8
or AL16UTF16 then no action is needed.
If is not UTF8
or AL16UTF16 then refer the following article
Note 276914.1
The National Character Set in Oracle 9i and 10g.
5. Optimizer Statistics:
When upgrading
to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for
dictionary tables that lack statistics. This statistics collection can be time
consuming for databases with a large number of dictionary tables, but
statistics gathering only occurs for those tables that lack statistics or are
significantly changed during the upgrade
$ sqlplus
"/as sysdba"
SQL> EXEC
DBMS_STATS.GATHER_DICTIONARY_STATS;
6. Ensure that no files need media recovery:
Sql> SELECT *
FROM v$recover_file;
7. Stop the listener for the database:
Make pfile from
spfile;
$ lsnrctl stop
8. Suhtdown the Database:
Shutdown the
database.
$ sqlplus
"/as sysdba"
SQL> shutdown
immediate;
9. Back Up the Database:
1- Perform Cold
Backup
(or)
2- Take a backup
using RMAN
Connect to RMAN:
rman
"target / nocatalog"
RUN
{
ALLOCATE CHANNEL
chan_name TYPE DISK;
BACKUP DATABASE
FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT
CONTROLFILE TO '';
}
10. Backup and change
pfile:
Make a backup of
the init.ora file.
Comment out
obsolete parameters
* The
DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST,
BACKGROUND_DUMP_DEST.
Note: Once the
Parameter file is modified as per your requirement, copy the file to
$ORACLE_HOME/dbs (11g Oracle Home )
11. Set Environment
Variables:
If your
operating system is UNIX then complete this step, else skip to next Step.
1. Make sure the
following environment variables point to the Oracle database software 11g
Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
$ export
ORACLE_HOME=
$ export
PATH=$ORACLE_HOME/bin:$PATH
$ export
ORACLE_BASE=
Note : If
ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute
'orabase', which will point the location of base.
$ orabase
/uo1/app/oracle
2. Update the
oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable
automatic startup
Sample
/etc/oratab
#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N
Note : After
/etc/oratab is updated to have sid and Oracle Home (11.2), you can execute
oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the
sid which is entered in /etc/oratab against 11g home.
for Instance,
[oracle@localhost
~]$ . oraenv
ORACLE_SID =
[orcl] ? orcl
The Oracle base
for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost
~]$
12. Upgrade Database:
At the operating
system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle
Home. Make sure that sqlplus is working from 11gR2 environment. Now start the
upgrade processes as follows:-
$ cd
$ORACLE_HOME/rdbms/admin
$ sqlplus
"/ as sysdba"
SQL> startup
UPGRADE
Set the system
to spool results to a log file for later verification after the upgrade is
completed and start the upgrade script.
SQL> set echo
on
SQL> SPOOL
upgrade.log
SQL> @catupgrd.sql
SQL> spool
off
These measures
are an important final step to ensure the integrity and consistency of the
newly upgraded Oracle Database software. Also, if you encountered a message
listing obsolete initialization parameters when you started the database for
upgrade, then remove the obsolete initialization parameters from the parameter
file before restarting. If necessary, convert the SPFILE to a PFILE so you can
edit the file to delete parameters.
Post Upgrade Steps
13. Post Upgrade:
Start the
database in 11g and run the Post-Upgrade Status Tool
$ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade
at the end of the spool log. It displays the status of the database components
in the upgraded database and the time required to complete each component upgrade.
Any errors that occur during the upgrade are listed with each component and
must be addressed
$ sqlplus
"/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql
14. Recompile Invalid
Objects:
This script can
be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining
stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
15. Check for the
integrity of the source database:
Check for the
integrity of the upgraded database by running dbupgdiag.sql script from below
Metalink article.
Note 556610.1 Script to Collect DB Upgrade/Migrate
Diagnostic Information (dbupgdiag.sql)
If the
dbupgdiag.sql script reports any invalid objects, run
$ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid
objects in the database, until there is no change in the number of invalid
objects.
After validating
the invalid objects, re-run dbupgdiag.sql in the upgraded database once again
and make sure that everything is fine.
16. Configure and Start
Listener.ora :
Modify the
listener.ora file:
For the upgraded
instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME.
Start the listener:
lsnrctl start
17. Crosscheck
Environment Variables:
Set Environment
Variables
1. Make sure the
following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
Also check that
your oratab file and any client scripts that set the value of ORACLE_HOME point
to the Oracle database software11g Release 2 (11.2) home.
Note : If you
are upgrading a cluster database, then perform these checks on all nodes in
which this cluster database has instances configured.
2. Modify
/etc/oratab entry to use automatic startup
SID:ORACLE_HOME:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
18. Spfile from Pfile:
Edit init.ora:
- If you changed
the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your
initialization parameter file to a server parameter file.
Create a server
parameter file with a initialization parameter file
SQL> create
spfile from pfile;
This will create
a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX)
& %ORACLE_HOME%\database (Windows).
19. Start the Database
with spfile:
Shutdown the
database:
Sql> shutdown
immediate
Now start the
database it will user spfile
Sql> startup
Check the Alert
log file for any Error.
Database is
ready to use now with Database Software Oracle 11g.
