Upgrade from 8.1.7.0.0 to 9i release 2 (9.2.0.8.0)

We can use three methods to upgrade our oracle database.

• Export / Import
• Database Upgrade Assistant
• Manually by using Scripts (We prefer manually method to upgrade our
  database)

Step 1 (Prepare the Database to be Upgraded)

1. Log in to the system as the owner of the Oracle home directory of the database being upgraded and Start SQL*Plus.

2. Connect to the database instance as a user with SYSDBA privileges.

3. Add space to your SYSTEM tablespace and to the tablespaces where you store rollback segments, if necessary.

Release SYSTEM Tbs Additional SYSTEM Tbs (with JServer)

9.0.1 16 MB 30 MB

8.1.7 52 MB 80 MB

8.0.6 70 MB N/A

7.3.4 85 MB N/A

How to add more space to the SYSTEM tablespace:

ALTER TABLESPACE system ADD DATAFILE 'Path of Datafile' SIZE 16M

AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

ALTER ROLLBACK SEGMENT 'TB_NAME'

STORAGE (MAXEXTENTS UNLIMITED);

NOTE: A rollback segment of at least 70 MB is recommended.

4. Run SHUTDOWN IMMEDIATE on the database and backup your database.

SQL> SHUTDOWN IMMEDIATE

Step 2 (Upgrade the Database)

1. Stop the OracleServiceSID Oracle service of the oracle 8i database

C:>NET STOP OracleService

2. Delete the OracleServiceSID at command line of 8i Home

C:>ORADIM –DELETE –SID

3. Create the new oracle database 9i service at command prompt using the
following command.

C:>ORADIM –NEW –SID –INTPWD –STARTMODE A

4.Put your init file in database folder at new oracle 9i home from 8i.

5. Remove obsolete initialization parameters and adjust deprecated initialization parameters.

• Make sure the SHARED_POOL_SIZE initialization parameter is set to at least 48 MB.
  
• Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to at least 24 MB.
  
• Make sure the LARGE_POOL_SIZE initialization parameter is set to at least 8 MB.
  
• Make sure the COMPATIBLE initialization parameter is properly set for the new Oracle9i
  release. If COMPATIBLE is set below 8.1.0, then you will encounter the following error when you attempt to start up your release 9.2 database.

ORA-00401: the value for parameter compatible is not supported by this release.

Details about COMPATIBLE issue:

Depending on your old release set COMPATIBLE as follows:

Old Release: 7.3.4

Set COMPATIBLE To: Either
remove COMPATIBLE from your parameter file, or set COMPATIBLE to 8.1.0.

Old Release: 8.0.6

Set COMPATIBLE To: Either remove COMPATIBLE from your parameter file, or set COMPATIBLE to 8.1.0

Old Release: 8.1.7

Set COMPATIBLE To: If COMPATIBLE is set to 8.0.x, then either remove COMPATIBLE from your parameter file, or set COMPATIBLE to 8.1.0. If COMPATIBLE is set to 8.1.x, then leave the setting as is.

Old Release: 9.0.1

If one or more automatic segment-space managed tablespaces exist in the database, then set COMPATIBLE to 9.0.1.3 Otherwise, leave the setting as is.

6. Connect to the new oracle 9i instance as a user SYSDBA privilege and issue following command:

SQL>STARTUP migrate

You don’t need to use the PFILE option to specify the location of your initialization parameter file in our case because we are using INIT file in default location (which is reside in Oracle9iHome/database). We have just put init file at new oracle 9i home from 8i.

7. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

8. Run the upgrade scripts.

SQL>@u0801070.sql

Details about Upgrade Scripts: (Run Scripts according your Old Release)

Old Release Run Script

7.3.4 u0703040.sql
8.0.6 u0800060.sql
8.1.7 u0801070.sql
9.0.1 u0900010.sql

Note: You only need to run one script. For example, if your old release was 8.1.7, then you only need to run u0801070.sql

The script you run creates and alters certain dictionary tables. It also runs the catalog.sql
and catproc.sql scripts that come with the new 9.2 release, which create the system catalog
views and all the necessary packages for using PL/SQL.

The following components are upgraded by running the Upgarde script:
Oracle9i Catalog Views and Oracle 9i Package and Types

9. Display the contents of the component registry to determine which components need to be
upgraded:

SQL> SELECT comp_name, version, status FROM dba_registry;

COMP_NAME VERSION STATUS

Oracle9i Catalog Views 9.2.0.8.0 VALID

Oracle9i Packages and Types 9.2.0.8.0 VALID

JServer JAVA Virtual Machine 8.1.7 LOADED

Java Packages 8.1.7 LOADED

Oracle XDK for Java 8.1.7 LOADED

Oracle interMedia Text 8.1.7 LOADED

Oracle interMedia 8.1.7.0.0 LOADED

Oracle Spatial 8.1.7.0.0 PRODUCTION LOADED

Oracle Visual Information Retrieval 8.1.7.0.0 LOADED

10. Run the cmpdbmig.sql script to upgrade components that can be upgraded while connected with SYSDBA privileges:

SQL>@cmpdbmig.sql

The following components are upgraded by running the cmpdbmig.sql script:

JServer JAVA Virtual Machine
Oracle9i Java Packages
Oracle XDK for Java
Messaging Gateway
Oracle9i Real Application Clusters
Oracle Workspace Manager
Oracle Data Mining
OLAP Catalog
OLAP Analytic Workspace
Oracle Label Security

11. Display the contents of the component registry to determine which components were upgraded:

SQL> SELECT comp_name, version, status FROM dba_registry;

12. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

13. Then, check the spool file and verify that the packages and procedures compiled successfully. Correct any problems you find in this file and rerun the appropriate upgrade scripts if necessary.

14. Shut down and restart the instance to reinitialize the system parameters for normal operation.

SQL> SHUTDOWN IMMEDIATE

15. Upgrade any remaining components that existed in the previous database.

The following components require separate upgrade steps:

Oracle Text
Oracle Ultra Search
Oracle Spatial
Oracle interMedia
Oracle Visual Information Retrieval

16. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

17. Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';

SQL> SELECT destinct object_name FROM dba_objects WHERE status='INVALID';

18. Verify that all components are valid and have been upgraded to release 9.2:

SQL> SELECT comp_name, version, status FROM dba_registry;

Steps for Upgrading remaining components that existed
in the previous database.

Upgrading Oracle Spatial

(From Release 8.1.5,8.1.6 or 8.1.7 to 9i release 9.2.0)

1. Connect as a SYSDBA.

2.Run following scripts. This will Grand the required privileges to the MDSYS user:

SQL>@ORACLE_HOME\md\admin\mdprivs.sql

3. Connect as s MDSYS user.

4.Run following procedure:

SQL>@ORACLE_HOME\md\admin\c81xu9x.sql

Upgrading Oracle interMedia

(From Release 8.1.5,8.1.6 or 8.1.7 to 9i release 9.2.0)

After upgrading your database, perform the following steps to upgrade interMedia manually:

1. First you invoke the imdbma script to determine whether you need to upgrade:

2. Connect as SYSDBA

3. Run following scripts:

SQL>@\ord\im\admin\imdbma.sql

This script displays one of the following strings:

NOT_INSTALLED - if no prior release of interMedia components were installed on your system. You must install interMedia, rather than an upgrade.

INSTALLED - if the current interMedia release is already installed

u0nnnnn0.sql - the script that performs the upgrade. nnnnn is the release of interMedia or Image Cartridge that is currently installed. For example, u0800060.sql upgrades from Image Cartridge release 8.0.6.0.0.

4. If an upgrade is required and your system is ready, perform the upgrade.

a) Connect / as SYSDBA

b) First upgrade Oracle interMedia Common Files.

SQL>@<ORACLE_HOME>\ord\admin\u0nnnnn0.sql

c) Then upgrade interMedia.

SQL>@<ORACLE_HOME>\ord\im\admin\u0nnnnn0.sql

4) Verify the upgrade:

a) Connect as ORDSYS user.

B) Run following Command:

SQL>@<ORACLE_HOME>\ord\im\admin\imchk.sql

Upgrading Oracle Visual Information Retrieval

From Release 8.1.5,8.1.6 or 8.1.7 to 9i release 9.2.0

1. Invoke the virdbma SQL script to decide whether or not you need to upgrade.

2. Connect as SYSDBA

SQL> @<ORACLE_HOME>\ord\vir\admin\virdbma.sql

This script displays one of the following strings:

NOT_INSTALLED - if no prior Visual Information Retrieval release was installed on your system.

INSTALLED - if Visual Information Retrieval Compatible API is already installed.

u0nnnnn0.sql - the script for upgrade. nnnnn is the release of Visual Information Retrieval that you have currently installed. For example, u0801070.sql upgrades from Visual Information Retrieval release 8.1.7.0.0.

3. If an upgrade is required, perform the upgrade:

SQL>@<ORACLE_HOME>\ord\vir\admin\u0nnnnn0.sql

where u0nnnnn0.sql is the upgrade script displayed by step 1, if an upgrade is necessary.

Upgrading Oracle Text

If the Oracle system has Oracle Text installed, then complete the following steps:

1. Log in to the system as the owner of the Oracle home directory of the new release.

At a system prompt, change to the ORACLE_HOME/ctx/admin directory.

2. Start SQL*Plus.

3. Connect to the database instance as a user with SYSDBA privileges.

4. If the instance is running, shut it down using SHUTDOWN IMMEDIATE

SQL> SHUTDOWN IMMEDIATE

5. Start up the instance in RESTRICT mode:

SQL> STARTUP RESTRICT

You may need to use the PFILE option to specify the location of your initialization parameter file.

6. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL text_upgrade.log

If you are upgrading from release 8.1.7, then complete the following steps. Skip to Step 9 if you are upgrading from release 9.0.1.

7.Run S0900010.sql

SQL>@s0900010.sql

This script grants new, required database privileges to user CTXSYS.

8. Connect to the database instance as user CTXSYS.

Run u0900010.sql

SQL> @u0900010.sql

Connect to the database instance as a user with SYSDBA privileges.

9. If you are upgrading from release 8.1.7 or release 9.0.1, then complete the following steps.

Run S0902000.sql

SQL> @s0902000.sql

This script grants new, required database privileges to user CTXSYS.

10. Connect to the database instance as user CTXSYS.

Run u0902000.sql

SQL> @u0902000.sql

This script upgrades the CTXSYS schema to release 9.2.

Connect to the database instance as a user with SYSSBA privileges. Check for any invalid CTXSYS objects and alter compile as needed. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and verify that the packages and procedures compiled successfully.

11. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

Exit SQL*Plus.

Upgrade User NCHAR Columns (Tasks to Complete Only After Upgrading a Release 8.1.7 Database)

If you upgraded from a version 8 release and your database contains user tables with NCHAR columns, you must upgrade the NCHAR columns before they can be used in the Oracle Database.

You will encounter the following error when attempting to use the NCHAR columns in the Oracle Database until you perform the steps in this section:

ORA-12714: invalid national character set specified

To upgrade user tables with NCHAR columns, perform the following steps:

1. Connect to the database instance as a user with SYSDBA privileges.

2. If the instance is running, shut it down using SHUTDOWN IMMEDIATE:

SQL> SHUTDOWN IMMEDIATE

3. Start up the instance in RESTRICT mode:

SQL> STARTUP RESTRICT

4. Run utlnchar.sql:

SQL> @utlnchar.sql

Alternatively, to override the default upgrade selection, run n_switch.sql:

SQL> @n_switch.sql

5. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

6. Exit SQL* PLUS