4 After Upgrading a Database

Back Up the Database

Make sure you perform a full backup of the production database.

Change Passwords for Oracle-Supplied Accounts

Depending on the release from which you upgraded, there may be new Oracle-supplied accounts. Oracle recommends that you lock all Oracle-supplied accounts except for SYS and SYSTEM, and expire their passwords, thus requiring new passwords to be specified when 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 the Oracle Database 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.

Upgrading and Tablespace Alerts

An upgraded Oracle Database 10g database has the Tablespace Alerts disabled (the thresholds are set to null). Tablespaces in the database that are candidates for monitoring need to be identified and the appropriate threshold values set.

The default threshold values (for a newly created Oracle Database 10g database) are:

• 85% full warning

• 97% full critical

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 Oracle9i releases earlier than release 9.0.1.2.0, a file was considered an Oracle-managed file if its base file name contained:

• An ora_ prefix

• A .dbf, .tmp, .log, or .ctl extension

In release 9.0.1.2.0 and later, a file is considered an Oracle-managed file 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.

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 Real Application Clusters, then 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: Oracle Real Application Clusters Installation and Configuration Guide Oracle Real Application Clusters Administrator's Guide

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.

Migrate Tables from LONGs to LOBs

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

In Oracle9i release 9.0.1 and later, 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 the Oracle Database to the new Oracle Database release for a configuration that includes one or more standby databases.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Upgrading Oracle Text

Add New Features as Appropriate

Oracle Database New Features describes many of the new features available in the new Oracle Database 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 Oracle 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 Oracle Database features. However, before you implement new Oracle Database features, test your applications and successfully run them with the upgraded database.

Develop New Administrative Procedures as Needed

After familiarizing yourself with new Oracle Database 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 the Oracle Database 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.

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 the Oracle Database.

The following steps convert your NCHAR columns from the old format and character set to the new Oracle Database format. In addition, if your old National Character Set was UTF8, it will remain UTF8 in the Oracle Database. 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 Oracle Database AL16UTF16 National Character Set or a version 8 non-UTF8 National Character Set can be converted to an Oracle Database UTF8 National Character Set.

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

Migrate Your Server Manager Line Mode Scripts to SQL*Plus

The Oracle Database 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 B, "Migrating from Server Manager to SQL*Plus" contains instructions for modifying your Server Manager line mode scripts to work with SQL*Plus.

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

Normalize Filenames on Windows Operating Systems

You only need to normalize filenames if you are running the Oracle Database 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, the Oracle Database may not record the fully specified pathname for a file in the control file. That is, the Oracle Database 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, the Oracle Database 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: Oracle Database Backup and Recovery Basics 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 Oracle Database Backup and Recovery Advanced User's Guide. The command is only intended for use on databases 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

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.

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 Oracle Database.