7 - 9 minutes readUpgrade Database from 8.1.7.0.0 to 9i release 2 (9.2.0.8.0)

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_HOMEmdadminmdprivs.sql

3. Connect as s MDSYS user.

4.Run following procedure:

SQL>@ORACLE_HOMEmdadminc81xu9x.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>@ordimadminimdbma.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>ordadminu0nnnnn0.sql

c) Then upgrade interMedia.

SQL>@<ORACLE_HOME>ordimadminu0nnnnn0.sql

4) Verify the upgrade:

a) Connect as ORDSYS user.

B) Run following Command:

SQL>@<ORACLE_HOME>ordimadminimchk.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>ordviradminvirdbma.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>ordviradminu0nnnnn0.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