|Oracle9i Database Migration
Release 2 (9.2)
Part Number A96530-01
This chapter guides you through the procedures to perform after you have completed an upgrade of your database. This chapter covers the following topics:
Complete the following tasks after you have upgraded your database.
Make sure you perform a complete backup of the production database. This backup must be complete, including all datafiles, control files, online redo log files, parameter files, and SQL scripts that create objects in the new database. To accomplish a complete backup, a full database export or a cold backup is required, because a hot backup cannot afford full recoverability. This backup can be used as a return point, if necessary, in case subsequent steps adversely affect the database.
Oracle9i User-Managed Backup and Recovery Guide for details about backing up a database
Depending on the release from which you upgraded, there may be some new Oracle-supplied accounts. Oracle Corporation recommends that you lock all Oracle-supplied accounts except for
SYSTEM, and expire their passwords, requiring new passwords to be specified if the accounts are unlocked.
You can view the status of all accounts by issuing the following SQL statement:
To LOCK and EXPIRE passwords, issue the following SQL statement:
If you are upgrading from an Oracle9i release earlier than release 18.104.22.168.0, then you must migrate your Oracle Managed Files. In Oracle9i releases earlier than release 22.214.171.124.0, Oracle sometimes incorrectly considered non-OMF files as OMF. This resulted in the following error when adding a datafile, control file, or log file to the database:
Also, Oracle sometimes incorrectly deleted the operating system files associated with a tablespace or redo log when dropping the tablespace or redo log.
Starting with release 126.96.36.199.0, the format of Oracle Managed Files file names on Windows and UNIX operating systems has changed. OMF files created in earlier Oracle9i releases will not be recognized as OMF files unless they are renamed to conform to the new OMF file name format.
In earlier Oracle9i releases, a file was considered OMF if its base file name contained:
In release 188.8.131.52.0 and higher, a file is now considered OMF if its base file name contains:
_) immediately preceding the extension
You can migrate old OMF datafiles, tempfiles, and log files by renaming them in the file system and in the control file. Complete the following steps:
OMF control files can be migrated by renaming them in the file system and in the
CONTROL_FILES initialization parameter. Complete the following steps:
CONTROL_FILESinitialization parameter to reference the new names.
This section contains Oracle OLAP upgrade instructions.
Oracle OLAP provides access to analytic workspaces through SQL. If your
COMPATIBLE initialization parameter is set to
8.1.6 or higher, then the standard upgrade procedure provides this functionality. No additional steps are required.
Oracle OLAP also offers the OLAP API (a Java interface) and the OLAP Catalog Metadata. To include these features when
8.1.6 or higher, perform the following steps:
If you only want access to analytic workspaces through SQL, without the OLAP API and the OLAP Catalog Metadata, then complete the following steps:
If you want support for the OLAP API and OLAP Catalog metadata in addition to analytic workspace access through SQL, then complete the following steps instead:
LOB datatypes (
NCLOB) can provide many advantages over
LONG datatypes. See Oracle9i Database Concepts for information about the differences between
In Oracle9i, the
ALTER TABLE statement can be used to change the datatype of a
LONG column to
CLOB and that of a
LONG RAW column to
In the following example, the
LONG column named
long_col in table
long_tab is changed to datatype
After using this method to change
LONG columns to LOBs, all the existing constraints and triggers on the table will still be usable. However, all the indexes, including Domain indexes and Functional indexes, on all columns of the table will become unusable and will have to be rebuilt using an
ALTER INDEX ... REBUILD statement. Also, the Domain indexes on the
LONG column will have to be dropped before changing the
LONG column to a LOB.
Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about modifying applications to use LOB data
In release 8.1, the
TO_LOB SQL function copies data from a
LONG column in a table to a
LOB column. The datatype of the
LOB must correspond for a successful copy. For example,
LONG RAW data must be copied to
BLOB data, and
LONG data must be copied to
In the examples in the following procedure, the
LONG column named
long_col in table
long_tab is copied to a
LOB column named
lob_col in table
lob_tab. These tables include an
id column that contains identification numbers for each row in the table.
Complete the following steps to copy data from a
LONG column to a
LONGcolumn, but use a
LOBdatatype in place of the
For example, suppose you have a table with the following definition:
Create a new table using the following SQL statement:
INSERTstatement using the
TO_LOBfunction to insert the data from the table with the
LONGdatatype into the table with the
For example, issue the following SQL statement:
For example, issue the following SQL statement to drop the
LONGdata. The synonym ensures that your database and applications continue to function properly.
For example, issue the following SQL statement:
Once the copy is complete, any applications that use the table must be modified to use the
Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about modifying applications to use
You need to modify your
listener.ora file only if one of the following conditions is true:
listener.orafile updated automatically.
If neither of these conditions is true, then skip this section. If one of these conditions is true, then you need to modify your
"listener.ora" for information about modifying your
The following procedures contain information about upgrading your current release of Oracle to the new Oracle9i release for a configuration that includes one or more standby databases.
If multiple standby databases exist, then repeat the steps in this section for each standby database to be upgraded:
Install the new Oracle9i release on production sites and follow the instructions in Oracle9i for upgrading the production database.
Make the following additional adjustments to your parameter file before the upgrade:
PARALLEL_SERVERinitialization parameter and set
CLUSTER_DATABASE = trueon the production site.
Ensure that all archived redo logs have been applied to the standby prior to the upgrade.
After the upgrade is complete, switch logfiles to archive any redo that remains in the last log:
Manually transfer archive logs from the upgrade from the primary archive destination on the production site to the standby archive destination on the standby host.
Shut down the standby database and listener
Start up and mount the standby database.
Place the standby database in managed recovery mode. At the SUGGESTION prompt, type AUTO to apply all of the archive logs generated during the upgrade process.
Verify that the standby database has been recovered to the last log that was transferred to the standby host. Resolve any archive log gaps between the production and the standby.
Re-enable remote archiving on the primary site by changing the standby destination from defer to enable.
Place standby into a recovery state.
Oracle9i Database New Features describes many of the new features available in the new Oracle9i release. Determine which of these new features can benefit the database and applications; then, develop a plan for using these features.
It is not necessary to make any immediate changes to begin using your new Oracle9i database. You may prefer to introduce these enhancements into your database and corresponding applications gradually.
Chapter 6, "Upgrading Your Applications" describes ways to enhance your applications so that you can take advantage of new Oracle9i features. However, before you implement new Oracle9i features, test your applications and successfully run them with the upgraded database.
After familiarizing yourself with new Oracle9i features, review your database administration scripts and procedures to determine whether any changes are necessary.
Coordinate your changes to the database with the changes that are necessary for each application. For example, by enabling integrity constraints in the database, you may be able to remove some data checking from your applications.
Each release of Oracle introduces new initialization parameters, deprecates some initialization parameters, and makes some initialization parameters obsolete. You should adjust your parameter file to account for these changes and to take advantage of new initialization parameters that may be beneficial to your system.
COMPATIBLE initialization parameter controls the compatibility level of your database. Set the
COMPATIBLE initialization parameter based on the compatibility level you want for your new database.
"Setting the COMPATIBLE Initialization Parameter" for information
You only need to normalize filenames if you are running Oracle on a Windows operating system. You do not need to perform these steps on UNIX platforms.
The control file and the recovery catalog both store filenames so that they can access files that are required by the database, such as:
In releases prior to release 8.1.6 on Windows operating systems, a flawed filename normalization mechanism allowed two different filenames to refer to the same physical file. For example, because of this flaw, Oracle may not record the fully specified pathname for a file in the control file. That is, Oracle may record only
dbfile1.dbf instead of
c:\oracle\oradata\dbfile1.dbf. If this happens, then, in subsequent statements that modify
c:\oracle\oradata\dbfile1.dbf, Oracle might conclude that this file is different than
Also, because of this behavior, SQL statements and Recovery Manager commands that refer to existing files must be specified exactly as they were originally entered or they are not recognized. An example of a SQL statement that refers to existing files is the
ALTER DATABASE RENAME FILE statement.
In release 8.1.6 and higher, the flawed filename normalization mechanism is corrected. However, existing filenames in the control file and recovery catalog must be normalized with the new filename normalization mechanism.
To normalize these filenames, complete the following steps:
Oracle9i User-Managed Backup and Recovery Guide for more information about operating system backups.
STARTUP MOUNTto mount the database without opening it:
DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMESprocedure to normalize the filenames in your control file:
DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMESprocedure has completed successfully, open the database:
For example, if the network service name for the target database is
TGT_DB and the network service name for the recovery catalog database is
CAT_DB, then you can enter the following, substituting the appropriate schema names and passwords:
RENORMALIZE CATALOGcommand to normalize the filenames in the recovery catalog for this target database:
Your filenames are now normalized.
If you need to restore a control file for a point-in-time recovery from a backup that was taken before you completed the filename normalization procedure described above, then first restore the backup control file, then perform Steps 1 to 7, and finally perform the recovery.
Complete the following additional tasks only if you upgraded your database from release 8.1.7 or lower. These tasks are not required if you upgraded from release 9.0.1.
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 Oracle9i.
The following steps convert your
NCHAR columns from the old format and character set to the new Oracle9i format. In addition, if your old National Character Set was UTF8, it will remain UTF8 in Oracle9i. However, your National Character Set will be converted to AL16UTF16 if it was not UTF8 in the old release.
You can override the default upgrade selection of the National Character Set. That is, a version 8 UTF8 National Character Set can be converted to an Oracle9i AL16UTF16 National Character Set or a version 8 non-UTF8 National Character Set can be converted to an Oracle9i UTF8 National Character Set.
You will encounter the following error when attempting to use the
NCHAR columns in Oracle9i until you perform the steps in this section:
Once you upgrade your NCHAR columns, you will not be able to downgrade to a previous release of Oracle until all NCHAR columns have been dropped.
To upgrade user tables with
NCHAR columns, perform the following steps:
You may need to use the
PFILE option to specify the location of your initialization parameter file.
Alternatively, to override the default upgrade selection, run
Once you have upgraded your SQL NCHAR columns (NCHAR, NVARCHAR2, and NCLOB) to Oracle9i, you will not be able to downgrade to a previous release until all SQL NCHAR columns have been dropped. If you need to recover the version 8 SQL NCHAR data, you will need to reimport the data from a previous backup.
Oracle9i no longer supports the use of Server Manager. If you run SQL scripts using Server Manager line mode, you must modify these scripts so that they are compatible with SQL*Plus. Appendix C, "Migrating from Server Manager to SQL*Plus" contains instructions for modifying your Server Manager line mode scripts to work with SQL*Plus.
If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:
If you are using Oracle9i Real Application Clusters, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for cluster databases, are discussed in:
CREATE SPFILEstatement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a
Complete the following additional tasks only if you upgraded your database from release 8.0.6 or lower. These tasks are not required if you upgraded from release 8.1.7 or higher.
Starting with release 8.1, parallel execution message buffers can be allocated from the large pool. In past releases, this allocation was from the shared pool. To avoid problems resulting from this change, you may need to adjust the following initialization parameters in your initialization parameter file:
"Parallel Execution Allocated from Large Pool" for information about adjusting these parameters.
During an upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:
Rebuild the unusable function-based indexes listed.
Materialized views upgraded from release 8.0 or imported from a release 8.0 database cannot use the new summary management features available in release 8.1 and higher. If you want to use these new features, then complete the following steps for each materialized view and for each materialized view imported from release 8.0:
If the materialized view references any schema objects outside its owner's schema, then you must issue a
GRANT GLOBAL QUERY REWRITE statement.
ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITEstatement on the materialized views you want to upgrade.
For example, on a materialized view named
SSORDERS, issue the following statement:
In addition, if you do not
ENABLE QUERY REWRITE on a materialized view, then the
ATOMIC=FALSE option of the
DBMS_MVIEW.REFRESH procedure may not work unless you issue an
ALTER MATERIALIZED VIEW ... COMPILE statement on the materialized view. For example, for a materialized view named
SSCUST, issue the following statement:
You do not need to issue this statement if you have issued any other
ALTER MATERIALIZED VIEW statement on the materialized view, such as the
ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE statement.
The following sections describe the actions required to upgrade the Advanced Queuing (AQ) option.
The following release 8.1 and higher AQ enhancements are available only if you upgrade your existing queue tables:
To upgrade an existing queue table, run the
DBMS_AQADM.MIGRATE_QUEUE_TABLE procedure, specifying 8.1 for the option. For example, for a queue table named
tb_queue owned by user
scott, run the following procedure:
To create a new queue table that is compatible with release 8.1 and higher, connect as the owner of the queue table and run the
DBMS_AQADM.CREATE_QUEUE_TABLE procedure, specifying 8.1 for the
COMPATIBLE option, as in the following example:
EXECUTE dbms_aqadm.create_queue_table( queue_table => 'scott.tkaqqtpeqt', queue_payload_type =>'message', sort_list => 'priority,enq_time', multiple_consumers => true, comment => 'Creating queue with priority and enq_time sort order', compatible => '8.1');
Your recovery catalog schema for the upgraded database may reside in a database that is separate from the database you upgraded. If you upgraded the Recovery Manager executable to release 8.1, then you must upgrade the recovery catalog to release 8.1 as well.
Also, if you have multiple databases of different releases managed by a single recovery catalog, then you need to consider compatibility issues between a particular Recovery Manager release and the recovery catalog release. For example, release 8.1.3 and 8.1.4 of Recovery Manager cannot access a release 8.1.5 or higher recovery catalog. Therefore, in this case, you must upgrade all of the databases managed by the recovery catalog to release 8.1.5 or higher. For more information about recovery catalog compatibility with Recovery Manager, see "Recovery Manager".
Complete the following steps to upgrade the recovery catalog:
For example, if RCAT/RCAT is the user name and password for the recovery catalog owner, and RECDB is the network service name, then enter the following:
The first time you connect to an older recovery catalog with the 8.1 release of Recovery Manager, you will see message RMAN-06186, indicating that the recovery catalog must be upgraded.
Here is the log from a session that upgrades the recovery catalog from release 8.0.4:
Recovery Manager: Release 184.108.40.206.0 RMAN-06008: connected to recovery catalog database RMAN-06186: PL/SQL package rcat.DBMS_RCVCAT version 08.00.04 in RCVCAT database is too old RMAN> upgrade catalog RMAN-06435: recovery catalog owner is rcat RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog RMAN-06408: recovery catalog upgraded to version 08.01.05
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:
where SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Execute this procedure for each statistics table.
Complete the following tasks only if you upgraded your database from release 7.3.4. These tasks are not required if you upgraded your database from release 8.0.6 or higher.
During the upgrade, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement:
SELECT index_name, index_type, table_owner, status FROM dba_indexes WHERE index_type = 'BITMAP' AND status = 'UNUSABLE';
Rebuild the unusable bitmap indexes listed.
Oracle9i Database Performance Tuning Guide and Reference and Oracle9i Database Concepts for more information about using bitmap indexes
Partition views are not recommended for new applications in Oracle9i, and existing partition views should be converted to partitioned tables. You can convert partition views created for Oracle7 databases to partitioned tables by using the
EXCHANGE PARTITION option of the
ALTER TABLE statement.
Oracle9i Database Administrator's Guide for information about converting partitioned views to partitioned tables and Oracle9i Database Concepts for background information about partition views and partitioned tables
A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The
utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 allowed you to create constraints with a two-digit year date constant. However, release 8.0 and higher returns an error if the check constraint date constant does not include a four-digit year.
To run the
utlconst.sql script, complete the following steps:
After you run the script, the
utlresult.log log file includes all the constraints that have invalid date constraints.
Migrating or upgrading to the new release of Oracle Net is not required. However, Oracle Net provides significant advantages over SQL*Net V2, including simplified configuration and expanded functionality. The new release of Oracle Net also provides the following advantages over past releases of Oracle Net and SQL*Net:
Oracle9i Net Services Administrator's Guide for more information about the advantages of Oracle Net, and see Appendix B, "Upgrade Considerations for Oracle Net Services" for detailed instructions on migrating or upgrading to the new release of Oracle Net.
Test the new Oracle9i database using the testing plan you developed in "Develop a Testing Plan". Compare the results of the test with the results obtained with the original database and make certain the same, or better, results are achieved.
Generally, the performance of the new Oracle9i database should be as good as, or better than, the performance of the previous database. If you notice any decline in database performance with the new Oracle9i database, then make sure the initialization parameters are set properly, because improperly set initialization parameters can impede performance.
If you want to improve the performance of the upgraded database, then tune the database. Actions you used to tune your previous database and applications should not impair the performance of the upgraded Oracle9i database.
Oracle9i Database Performance Tuning Guide and Reference for tuning information
The instructions in this section guide you through the process of changing the word size of your current release (switching from 32-bit software to 64-bit software or from 64-bit software to 32-bit software).
"Changing Word Size" for more information about changing word-size.
Complete the following steps to change the word size of your current release:
SHUTDOWN IMMEDIATEon the database:
Oracle9i User-Managed Backup and Recovery Guide for more information
IFILE(include file) entry and the file specified in the
IFILEentry resides within the old environment's Oracle home, then copy the file specified by the
IFILEentry to a location outside of the old environment's Oracle home. The file specified in the
IFILEentry has additional initialization parameters. After you copy this file, edit the
IFILEentry in the parameter file to point to its new location.
sid, but on Windows operating systems, the default password file is
.ora. In both cases, sid is your Oracle instance ID.
You may need to use the
PFILE option to specify the location of your initialization parameter file.
If you want to see the output of the script you will run on your screen, then you can also issue a
SET ECHO ON command:
utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. This script first alters certain dictionary tables. Then, it reloads the STANDARD and DBMS_STANDARD packages, which are necessary for using PL/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as packages, procedures, types, and so on.
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 12; the suggested name was
catoutw.log. Correct any problems you find in this file.
If you issued a
SET ECHO ON command, then you may want to issue a
SET ECHO OFF command now:
ALTER SYSTEM DISABLE RESTRICTED SESSION:
The word size of your database is changed. You can open the database for normal use.