All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at neeraj.vishen@gmail.com .

Tuesday, August 14, 2012

Upgradation is the process of replacing our existing software with a newer version of the same product . For example, replacing oracle 9i release to oracle 10g release . Upgrading our applications usually does not require special tools. Our existing reports should look and behave the same in both products. However, sometimes minor changes may be seen in product .Upgradation is done at Software level .

I received a mail from a reader regarding the upgradation of database . He wants to upgrade his database from 9i to 10g . Here, i will like advice that it's better to upgrade our database from 9i to 11g as compare to 9i to 10g because Oracle extended support for 10gR2 will ends on 31-Jul-2013 and also there are more features available in Oracle 11g . We can directly upgrade to oracle 11g, if our curent database is 9.2.0.4 or newer then its supports direct upgrades to versions 9.2.0.4, 10.1 and 10.2 . We can upgrade the version as

There are generally four method to Upgrade the Oracle database .1.) Manual Upgradation :2.) Upgradation Using the DBUA .3.) export/import4.) Data Copying

Let's have a look on manual upgradation .

Manual Upgradation :A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release. While a manual upgrade gives us finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order. Below are the steps

4.) Login into 9i home and run the utlu102i.sql : This script is in oracle 10g home .
SQL> spool pre_upgrd.sql
SQL> @<ORACLE_10G_HOME>/rdbms/admin/utlu102i.sql
SQL> spool off

The above scripts checks a number of areas to make sure the instance is suitable for upgrade including

Database version

Log file sizes

Tablespace sizes

Server options

Initialization parameters (updated, depercated and obsolete)

Database components

Miscellaneous Warnings

SYSAUX tablespace present

Cluster information

The issues indicated by this script should be resolved before a manual upgrade is attempted. Once we have resolved the above warning , then re-run the above script once more to cross-check .

5.) Check for the timestamp with timezone Datatype : The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE datatype. To preserve this TIMESTAMP data for updating according to the new time zone transition rules, we must run the utltzuv2.sql script on the database before upgrading. This script analyzes our database for TIMESTAMP WITH TIME ZONE columns that a re affected by the updated time zone transition rules.

If the utltzuv2.sql script identifies columns with time zone data affected by a database upgrade, then back up the data in character format before we upgrade the database. After the upgrade, we must update the tables to ensure that the data is stored based on the new rules. If we export the tables before upgrading and import them after the upgrade, the conversion will happen automatically during the import.

6.) Shutdown the database :
shut down the database and copy the spfile(or pfile) and password file from 9i home to 10g home .

7.) Upgrade Database : Set following environment for 10g and login using "SYS" user . It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.

8.) Recompile any invalid objects : Compare the number of invalid objects with the number noted in step 4 . It should hopefully be the same or less.

SQL>@ORACLE_HOME/rdbms/admin/utlrp.sql

9.) Check the status of the upgrade :
SQL> @ORACLE_HOME/rdbms/admin/utlu102s.sql

The above script queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to rerun to fix the errors.

10.) Edit the spfile : Create a pfile from spfile as
SQL>create pfile from spfile ;

Open the pfile and set the compatible parameter to 10.2.0.0.0 . Shutdown the database and create the new modified spfile .

SQL>shut immediate
SQL> create spfile from pfile ;

11.) Start the database normally
SQL> startup
and finally configure the Oracle net and drop the old Oracle database software i.e, 9i using the OUI .

Search This Blog

Translate My Page

About Me

I have started this blog to share my knowledge and experience with other Oracle DBA enthusiasts.I have experience on database architecture ,design and administrating ranging from 9i,10g,11g on various platforms.My main interests are high availability and disaster recovery solutions for mission critical 24×7 systems.