Skip Headers

Oracle® Database Upgrade Guide
10g Release 1 (10.1)

Part Number B10763-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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

Complete the following tasks after you have upgraded your database.

Back Up the Database


Note:

You do not need to perform this step if the Database Upgrade Assistant was used to upgrade the database.

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


See Also:

Oracle Database Backup and Recovery Basics for details about backing up a database

Change Passwords for Oracle-Supplied Accounts


Note:

You do not need to perform this step if the Database Upgrade Assistant was used to upgrade the database.

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


Note:

You do not need to perform this step if the Database Upgrade Assistant was used to upgrade the database.

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

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.


See Also:

Oracle Database Application Developer's Guide - Large Objects for information about modifying applications to use LOB data

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.


See Also:

Oracle Net Services Administrator's Guide for information about modifying 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.

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 Oracle 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 Oracle Database release on production sites and follow the instructions in Oracle Database 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 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.

Upgrade or Downgrade the Oracle Data Guard Broker

If you need to upgrade or downgrade Oracle Data Guard broker to a different release, then see Oracle Data Guard Broker for the following release scenarios:

  • Upgrading from release 9.0.1 to release 10.1

  • Upgrading from release 9.2 to release 10.1

  • Downgrading from release 10.1

Upgrading Oracle Text


See Also:

Oracle Text Application Developer's Guide for information about upgrading your applications from previous releases of Oracle Text

Supplied Knowledge Bases

The Supplied Knowledge Bases have been moved to the Oracle Database 10g Companion CD and are not immediately available after an upgrade to Oracle Database 10g. Any Text features dependent on the Supplied Knowledge Bases which were available before the upgrade will not function after the upgrade. To re-enable such features, you must install the Supplied Knowledge Bases from the Companion CD.

After an upgrade, all user-extensions to the Supplied Knowledge Bases must be regenerated. These changes affect all databases installed in the given ORACLE_HOME.


See Also:

  • Oracle Text Application Developer's Guide for information about Supplied Knowledge Bases

  • The post-installation tasks section of the Database Installation Guide and the Companion CD Installation Guide for information about installing products from the Companion CD

Copy Files from the Previous ORACLE_HOME to the New ORACLE_HOME

After an upgrade to Oracle Database 10g, copy the following files from the previous ORACLE_HOME to the new ORACLE_HOME:

  • Stemming user-dictionary files

  • User-modified KOREAN_MORPH_LEXER dictionary files

  • USER_FILTER executables

These files affect all databases installed in the given ORACLE_HOME.


See Also:

Oracle Text Reference for more information about these files

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


Note:

You do not need to perform this step if the Database Upgrade Assistant was used to upgrade the database.

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.


See Also:

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

Tasks to Complete Only After Upgrading a Release 8.0.6 Database

Complete the following additional tasks only if you upgraded your database from release 8.0.6. 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 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.


Note:

Do not perform the following procedure on Oracle releases prior to release 8.1.6.

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.


Note:

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 0 to 7, and finally perform the recovery.

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


Note:

The word "snapshot" is synonymous with the word "materialized view".

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


See Also:

Oracle Database Backup and Recovery Advanced User's Guide for information about upgrading 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.

Test the Database and Compare Results

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


See Also:

Oracle Database Performance Tuning Guide for tuning information