Upgrading Database

In the March 2000 Tip of the Month, we saw how to migrate an Oracle7 database to Oracle8i. This month, we will discuss how to upgrade databases in Oracle8 and Oracle8i and how the script conventions have changed from Oracle7...

Upgrading concept is the same in versions 7 as well as 8 and 8i. Basically you need to follow these steps:

1. SHUTDOWN NORMAL the database
2. Backup the database
3. Change ORACLE_HOME environment (oratab file also) to the new database version
4. STARTUP database in the new version
5. Run the "appropriate" script(s)

Here are the scripts you would run when upgrading from and to various versions.... The scripts are located under $ORACLE_HOME/rdbms/admin directory of the new version.

Upgrading Oracle7 databases

1. From 7.1.3, 7.1.4, or 7.1.5 to 7.1.6: Run

cat7106.sql
catalog.sql
catproc.sql
catexp.sql

2. From 7.1.x to 7.2.1

cat7201.sql
catalog.sql
catproc.sql
catexp.sql

3. From 7.2.x to 7.3.1

cat7301.sql
catalog.sql
catproc.sql
catexp.sql

4. From 7.3.1 to 7.3.4

cat7302.sql -- upgrade to 7.3.2
cat7303.sql -- upgrade to 7.3.3
cat7304.sql -- upgrade to 7.3.4
catalog.sql
catproc.sql
catexp.sql

When applying patches to databases, you need to run the catalog.sql, catproc.sql and catexp.sql scripts...

The steps for applying patch is:

Upgrading Oracle8 databases

In Oracle7, there were individual scripts to run for each version upgrade. In Oracle8 and 8i, you run only one script to bring the database to the current version. For example, if you are upgrading a 8.0.3 database to 8.0.6, you run only one script named u0800030.sql. Similarly, to upgrade from 8.0.5 to 8.0.6, simply run u0800050.sql. These scripts call the necessary scripts including catalog.sql and catproc.sql. Normally after an upgrade, all your PL/SQL programs will be invalid and you need to recompile them. In Oracle8, you can run the script utlrp.sql, which will recompile invalid Pl/sql modules.

Upgrading Oracle8i databases

Similar to Oracle8 upgrade procedures, in Oracle8i also, you need to run only one script to upgrade the database from any version of Oracle8 or 8i to the current version of 8i. For example, the script u0800050.sql upgrades 8.0.5 database to 8.1.6 (current release) and u0801050.sql upgrades an 8.1.5 database to 8.1.6. Recompile the objects using utlrp.sql.

Basically, to upgrade a database from Oracle8 or Oracle8i to a most recent version, perform these steps.

Scripts to run based on the from Version:

u0800030.sql - From 8.0.3
u0800040.sql - From 8.0.4
u0800050.sql - From 8.0.5
u0800060.sql - From 8.0.6
u0801030.sql - From 8.1.3
u0801040.sql - From 8.1.4
u0801050.sql - From 8.1.5
u0801060.sql - From 8.1.6

If you notice the script naming in the Oracle7 and Oracle8 (or 8i), you definitely can see a standard. Once you understand the basics, it is easier to remember...

As always, please check the Oracle documentation and release notes that come with the version for specific parameters that need to be set to avoid bugs and hiccups!