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
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
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.sql8.0.6 u0800060.sql8.1.7 u0801070.sql9.0.1 u0900010.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:
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
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
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>@
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.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP RESTRICT
You may need to use the PFILE option to specify the location of your initialization parameter file.
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
