4
After Upgrading a Database

This chapter guides you through the procedures to perform after you have completed an upgrade of your database. This chapter covers the following topics:

• Tasks to Complete After Upgrading Your Database
• Tasks to Complete Only After Upgrading a Release 8.1.7 or Lower Database
• Tasks to Complete Only After Upgrading a Release 8.0.6 or Lower Database
• Tasks to Complete Only After Upgrading a Release 7.3.4 Database
• Test the Database and Compare Results
• Changing the Word Size of Your Current Release

Tasks to Complete After Upgrading Your Database

Complete the following tasks after you have upgraded your database.

Back Up the 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.

Change Passwords for Oracle-Supplied Accounts

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 SYS and 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:

SQL> SELECT username, account_status
         FROM dba_users
         ORDER BY username;

To LOCK and EXPIRE passwords, issue the following SQL statement:

SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

Upgrading from the Standard Edition to the Enterprise Edition

If you are using the Standard Edition of Oracle and want to upgrade to the Enterprise Edition, then complete the following steps:

1. Ensure that the release number of your Standard Edition server software is the same release as the Enterprise Edition server software.

   For example, if your Standard Edition server software is release 9.2.0.1.0, then you should upgrade to release 9.2.0.1.0 of the Enterprise Edition.

2. Shut down your database.
3. If your operating system is Windows, then stop all Oracle services, including the OracleServiceSID Oracle service, where SID is the instance name.
4. Deinstall the Standard Edition server software.
5. Install the Enterprise Edition server software using the Oracle Universal Installer.

   Select the same Oracle home that was used for the de-installed Standard Edition. During the installation, be sure to select the Enterprise Edition. When prompted, choose Software Only from the Database Configuration screen.

6. Start up your database.

Your database is now upgraded to the Enterprise Edition.

Migrate Your Oracle Managed Files

If you are upgrading from an Oracle9i release earlier than release 9.0.1.2.0, then you must migrate your Oracle Managed Files. In Oracle9i releases earlier than release 9.0.1.2.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:

ORA-01276: Cannot add a file with an Oracle Managed Files file name

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 9.0.1.2.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:

• An ora_ prefix
• A .dbf, .tmp, .log, or .ctl extension

In release 9.0.1.2.0 and higher, a file is now considered OMF if its base file name contains:

• An o1_mf_ prefix
• A .dbf, .tmp, .log, or .ctl extension
• An underscore (_) 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:

1. Find the OMF files by issuing the following SQL statements:

   SQL> SELECT name FROM v$datafile;
   SQL> SELECT name FROM v$tempfile;
   SQL> SELECT member FROM v$logfile;
   
   

2. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

3. Rename the files in the file system:
   • Change ora_ to o1_mf_
   • Add _ before the extension

     For example, for a file named ora_tbs1_2ixfh90q.dbf, the new name would be o1_mf_tbs1_2ixfh90q_.dbf.

4. Mount the database.
5. Rename the files in the control file. For example:

   SQL> ALTER DATABASE RENAME FILE 'old_filename' TO 'new_omf_filename';
   
   

6. Open the database.

OMF control files can be migrated by renaming them in the file system and in the CONTROL_FILES initialization parameter. Complete the following steps:

1. Find the OMF files by examining the CONTROL_FILES initialization parameter.
2. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

3. Rename the files in the file system:
   • Change ora_ to o1_mf_
   • Add _ before the extension

     For example, for a file named ora_cmr7t90p.ctl, the new name would be o1_mf_cmr7t90p_.ctl.

4. Modify the CONTROL_FILES initialization parameter to reference the new names.
5. Mount and open the database.

Upgrade Oracle OLAP

This section contains Oracle OLAP upgrade instructions.

Upgrading from Release 8.1.7 or Later

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 COMPATIBLE is 8.1.6 or higher, perform the following steps:

1. Complete the standard upgrade procedure.
2. Set COMPATIBLE to 9.2.0.
3. Restart the database.
4. Run the following script:

   ORACLE_HOME/olap/admin/olapapi.sql
   
   

5. If you are upgrading from a release that is earlier than release 9.0.1, then complete the following additional steps which create the OLAP Catalog Metadata in its own tablespace:
   1. Create a tablespace with a statement similar to the following. You can specify any valid tablespace name and any valid database file name:

      CREATE TABLESPACE OLAPCAT LOGGING
          DATAFILE 'ORACLE_HOME/rdbms/dbs/olap01.dbf'
          SIZE 20M REUSE AUTOEXTEND ON NEXT 640K
          MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
          SEGMENT SPACE MANAGEMENT AUTO;
      

   2. Run the following script specifying the name of the tablespace that you created and the name of a temporary tablespace for your database. In this example, the names are OLAPCAT and TEMP:

      ORACLE_HOME/cwmlite/admin/oneinstl.sql OLAPCAT TEMP
      
      

Upgrading from Release 8.0.6 or Earlier

If you only want access to analytic workspaces through SQL, without the OLAP API and the OLAP Catalog Metadata, then complete the following steps:

1. Complete the standard upgrade procedure.
2. Set COMPATIBLE to 8.1.6 or higher.
3. Restart the database.
4. Run the following script:

   ORACLE_HOME/olap/admin/olapaw.sql
   
   

   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:

1. Complete the standard upgrade procedure.
2. Set COMPATIBLE to 9.2.0.
3. Restart the database.
4. Run the following script:

   ORACLE_HOME/olap/admin/olap.sql
   
   

5. Create a tablespace with a statement similar to the following. You can specify any valid tablespace name and any valid database file name:

   CREATE TABLESPACE OLAPCAT LOGGING
       DATAFILE 'ORACLE_HOME/rdbms/dbs/olap01.dbf'
       SIZE 20M REUSE AUTOEXTEND ON NEXT 640K
       MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
       SEGMENT SPACE MANAGEMENT AUTO;
   
   

6. Run the following script specifying the name of the tablespace that you created and the name of a temporary tablespace for your database. In this example, the names are OLAPCAT and TEMP:

   ORACLE_HOME/cwmlite/admin/oneinstl.sql OLAPCAT TEMP
   
   

Migrate Your Initialization Parameter File to a Server Parameter File

If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:

1. If the initialization parameter file is located on a client machine, transfer the file from the client machine to the server machine.

   Note: 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: Oracle9i Real Application Clusters Setup and Configuration Oracle9i Real Application Clusters Administration

2. Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.
3. Start up the instance using the newly created server parameter file.

   See Also: Oracle9i Database Administrator's Guide for more information about creating server parameter files Oracle9i SQL Reference for information about the CREATE SPFILE statement

Migrate Tables from LONGs to LOBs

LOB datatypes (BFILE, BLOB, CLOB, and NCLOB) can provide many advantages over LONG datatypes. See Oracle9i Database Concepts for information about the differences between LONG and LOB datatypes.

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 BLOB.

In the following example, the LONG column named long_col in table long_tab is changed to datatype CLOB:

SQL> ALTER TABLE Long_tab MODIFY ( long_col CLOB );

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.

Modify Your listener.ora File

You need to modify your listener.ora file only if one of the following conditions is true:

• You did not use the Database Upgrade Assistant to upgrade your database.
• You used the Database Upgrade Assistant to upgrade your database but chose not to have the listener.ora file 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 file.

Upgrade Your Standby Database

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.

Prepare to Upgrade

If multiple standby databases exist, then repeat the steps in this section for each standby database to be upgraded:

1. Check for the existence of nologging operations. If nologging operations have been performed, then the standby will need to be updated. Refer to Oracle9i Data Guard Concepts and Administration for further details.
2. Make note of any tablespaces or datafiles that need recovery due to offline immediate. Tablespaces or datafiles should be recovered and either brought online or taken offline prior to upgrading.

Upgrade the Production Site

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:

• Do not enable remote archiving within the production database's parameter file if it was not already enabled. If remote archiving is enabled, then set the remote destination to defer.
• Cancel managed recovery on the standby database if running.
• If upgrading from release 8.1.7 or earlier and running Oracle9i Real Application Clusters Guard, make sure to comment out the PARALLEL_SERVER initialization parameter and set CLUSTER_DATABASE = true on 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:

SQL> ALTER SYSTEM SWITCH LOGFILE;

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.

Add New Features as Appropriate

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.

Develop New Administrative Procedures as Needed

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.

Adjust Your Parameter File for the New Release

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.

The 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.

Tasks to Complete Only After Upgrading a Release 8.1.7 or Lower Database

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.

Upgrade User NCHAR Columns

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:

ORA-12714: invalid national character set specified

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

1. Log in to the system as the owner of the Oracle home directory.
2. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
3. Start SQL*Plus.
4. Connect to the database instance as a user with SYSDBA privileges.
5. If the instance is running, shut it down using SHUTDOWN IMMEDIATE:

   SQL> SHUTDOWN IMMEDIATE
   
   

6. 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.

7. Run utlnchar.sql:

   SQL> @utlnchar.sql
   
   

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

   SQL> @n_switch.sql
   
   

8. Shut down the instance:

   SQL> SHUTDOWN IMMEDIATE
   
   

9. Exit SQL*Plus.

Downgrading SQL NCHAR Columns

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.

Migrate Your Server Manager Line Mode Scripts to SQL*Plus

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.

Tasks to Complete Only After Upgrading a Release 8.0.6 or Lower Database

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.

Avoid Problems with Parallel Execution

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:

• SHARED_POOL_SIZE
• LARGE_POOL_SIZE

  See Also: "Parallel Execution Allocated from Large Pool" for information about adjusting these parameters.

Normalize Filenames on Windows Operating Systems

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:

• Datafiles
• Control files
• Online and archived redo logs used by Oracle
• Datafile copies and on-disk backup pieces used by Recovery Manager

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 dbfile1.dbf.

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:

1. Using SQL*Plus, connect to the database as a user with SYSDBA privileges.
2. Shut down the database using SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE:

   SQL> SHUTDOWN IMMEDIATE
   
   

3. Make an operating system backup of your control file.

   See Also: Oracle9i User-Managed Backup and Recovery Guide for more information about operating system backups.

4. Run STARTUP MOUNT to mount the database without opening it:

   SQL> STARTUP MOUNT
   
   

5. Run the DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES procedure to normalize the filenames in your control file:

   SQL> EXECUTE DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES;
   
   

6. When the DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES procedure has completed successfully, open the database:

   SQL> ALTER DATABASE OPEN;
   
   

7. Exit SQL*Plus.
8. Log in to Recovery Manager and connect to a target database and recovery catalog.

   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:

   rman target sys/password@tgt_db catalog rcat_schema/rcat_password@cat_db 
   
   

9. Issue the RENORMALIZE CATALOG command to normalize the filenames in the recovery catalog for this target database:

   RMAN> renormalize catalog;
   
   

   Note: The RENORMALIZE CATALOG command is not considered part of the Recovery Manager syntax and is not documented in the Oracle9i Recovery Manager User's Guide. The command is only intended for use on databases migrated or upgraded from a release prior to release 8.1.6 on Windows platforms.

10. Repeat Steps 8 through 9 for each release 8.1.6 or higher target database registered in this recovery catalog.

Your filenames are now normalized.

Rebuild Unusable Function-Based Indexes

During an upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT owner, index_name, funcidx_status 
   FROM dba_indexes WHERE funcidx_status = 'DISABLED';

Rebuild the unusable function-based indexes listed.

Upgrade Materialized Views

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:

1. GRANT QUERY REWRITE privileges to the owner of the materialized view. Only local materialized views are available for query rewrite.

   If the materialized view references any schema objects outside its owner's schema, then you must issue a GRANT GLOBAL QUERY REWRITE statement.

2. Issue the ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE statement on the materialized views you want to upgrade.

   For example, on a materialized view named SSORDERS, issue the following statement:

   ALTER MATERIALIZED VIEW ssorders ENABLE QUERY REWRITE;
   
   

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:

ALTER MATERIALIZED VIEW sscust COMPILE;

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.

Upgrade Your Queue Tables

The following release 8.1 and higher AQ enhancements are available only if you upgrade your existing queue tables:

• Addition of the original message ID column for propagated messages
• Addition of a sender's ID column
• Queue and system level privileges
• Rule based subscriptions
• Separate storage of history management information, which was stored in a varray in release 8.0

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:

EXECUTE dbms_aqadm.migrate_queue_table (
    queue_table => 'scott.tb_queue',
    compatible => '8.1');

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'); 

Upgrade the Recovery Catalog

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:

1. Log in to Recovery Manager and connect to 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:

   rman rcvcat rcat/rcat@recdb 
   
   

   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.

2. Use the UPGRADE CATALOG command to upgrade the recovery catalog to the most current release. Recovery Manager prompts you to enter the command twice to confirm the catalog upgrade. If any errors are encountered while upgrading, then they are displayed in the Recovery Manager log.

   Here is the log from a session that upgrades the recovery catalog from release 8.0.4:

   Recovery Manager: Release 8.1.5.0.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
   

Upgrade Statistics Tables Created by the DBMS_STATS Package

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table'); 

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.

Tasks to Complete Only After Upgrading a Release 7.3.4 Database

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.

Rebuild Unusable Bitmap Indexes

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.

Migrate Partition Views to Partition Tables

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.

Check for Bad Date Constraints

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:

1. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
2. Start SQL*Plus.
3. Connect to the database instance as a user with SYSDBA privileges.
4. Enter the following:

   SQL> SPOOL utlresult.log
   SQL> @utlconst.sql
   SQL> SPOOL OFF
   
   

After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints.

Upgrade to the New Release of Oracle Net Services (Optional)

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:

• Service naming enables clients to access a service as a whole, using the service name, rather than a specific database instance. Service naming logically separates the service name from any particular instance name and replaces the SID parameter, enabling one instance to serve multiple services. Individual instances also can be part of multiple services.
• Instance registration is automatic. Instances register themselves with the listener when they are started. In past releases, information about the instance was configured manually in the listener.ora file.
• Failover is automatic. If an instance is down, a client connect request is sent to a different listener automatically.
• Load balancing distributes connections over the available listeners.

  See Also: 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 Database and Compare Results

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.

Tune the Upgraded Database

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.

Changing the Word Size of Your Current Release

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 switching from 64-bit software to 32-bit software).

Complete the following steps to change the word size of your current release:

1. Start SQL*Plus.
2. Connect to the database instance as a user with SYSDBA privileges.
3. Run SHUTDOWN IMMEDIATE on the database:

   SQL> SHUTDOWN IMMEDIATE
   
   

   Note: For Oracle9i Real Application Clusters, issue this statement for all instances. Also, set the CLUSTER_DATABASE initialization parameter to false. You can change it back to true after the change in word size is complete.

4. Perform a complete backup of the database.

   See Also: Oracle9i User-Managed Backup and Recovery Guide for more information

5. If you are using the same Oracle home for your current release and the release to which you are switching, then deinstall your current release using the Oracle Universal Installer. You do not need to deinstall your current release if you are using separate Oracle home directories.
6. If you currently have a 32-bit installation, then install the 64-bit release. Or, if you currently have a 64-bit installation, then install the 32-bit release.

   Note: Installation and deinstallation are operating system-specific. For installation and deinstallation instructions, see your Oracle9i operating system-specific installation documentation and the Oracle9i README for your operating system.

7. Copy configuration files to a location outside of the old Oracle home:
   1. If your parameter file resides within the old environment's Oracle home, then copy it to a location outside of the old environment's Oracle home. The parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you switch to the new release.
   2. If your parameter file has an IFILE (include file) entry and the file specified in the IFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE entry to a location outside of the old environment's Oracle home. The file specified in the IFILE entry has additional initialization parameters. After you copy this file, edit the IFILE entry in the parameter file to point to its new location.
   3. If you have a password file that resides within the old Oracle home, then move or copy the password file to the new Oracle9i Oracle home. The name and location of the password file are operating system-specific; for example, on UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid, but on Windows operating systems, the default password file is ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

      Note: For Oracle9i Real Application Clusters, perform this step on all nodes. Also, if your initdb_name.ora file resides within the old environment's Oracle home, then move or copy the initdb_name.ora file to a location outside of the old environment's Oracle home.

8. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
9. Start SQL*Plus.
10. Connect to the database instance as a user with SYSDBA privileges.
11. Run STARTUP MIGRATE:

    SQL> STARTUP MIGRATE
    
    

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

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

    SQL> SPOOL wordsize.log
    
    

13. Run utlirp.sql:

    SQL> @utlirp.sql
    
    

    The 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.

14. 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. You named the spool file in Step 12; the suggested name was wordsize.log. Correct any problems you find in this file.

15. Run ALTER SYSTEM DISABLE RESTRICTED SESSION:

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
    
    

The word size of your database is now changed. You can open the database for normal use.