4 After Upgrading a Database

Back Up the Database

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

Update Environment Variables After the Upgrade (UNIX Systems Only)

If your operating system is UNIX, then make sure that the following environment variables point to the new release 10.2 directories:

• ORACLE_HOME

• PATH

• ORA_NLS10

  (Note that the ORA_NLS10 environment variable replaces the ORA_NLS33 environment variable, so you may need to unset ORA_NLS33 and set ORA_NLS10.)

• LD_LIBRARY_PATH

Set Threshold Values for 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 Tables from the LONG Datatype to the LOB Datatype

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.

Upgrade the TIMESTAMP Data

If your database has TIMESTAMP WITH TIMEZONE data, you must update the data so that it is converted and stored based on the new time zone rules that come with the upgrade. (See "TIMESTAMP WITH TIMEZONE Datatype").

If you used the export utility to export a copy of the affected tables, you should now use the import utility to import your data from these tables back into your database. The import utility will update the timestamp data as it imports.

If you used the manual script method, you will need to update the affected timestamp data based on your backed up table. For example, if you previously backed up your table, you need to run an update statement similar to the one below to update your timestamp data.

UPDATE tztab t SET t.y = 
  (SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
FROM tztab_back t1
WHERE t.x=t1.x);

Although the transition rule changes for some time zone regions may affect data of TIMESTAMP WITH LOCAL TIME ZONE datatype, there is no way to upgrade the data. The data cannot be upgraded because this type does not preserve the original time zone/region associated with the data.

Time zone regions in Brazil and Israel may have frequent transition rules changes, perhaps as often as every year. Use the time zone offset instead of the time zone region name to avoid storing inconsistent data.

Use the Latest Time Zone File for Clients

Customers using time zone regions that have been updated in version 2 of the time zone files are required to update all Oracle9i Database clients and databases that will communicate with an Oracle Database 10g server. This ensures that all environments will have the same version of the time zone file. Upgrading to the latest time zone file is not a requirement for customers that do not use the TIMESTAMP WITH TIME ZONE type or manipulate data from regions with frequent changes to their time zone transition rules. However Oracle recommends this action to avoid future issues. Users who need to update their time zone files to version 2 can find the following information on Oracle MetaLink (http://metalink.oracle.com):

• readme.txt contains the list of time zone regions that have changed from version 1 to version 2

• Actual time zone files for version 2 for the Oracle9i Database release

Oracle Database 10g clients that communicate with Oracle Database 10g servers automatically get version 2 of the time zone file, so there is no need to download the new time zone file.

Upgrade the Recovery Catalog

For information about upgrading the recovery catalog, see Oracle Database Backup and Recovery Advanced User's Guide.

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.

Upgrade Externally Authenticated SSL Users

If you are using externally authenticated SSL users, you must run the following command to upgrade those users:

$ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring 
<hostname:port_no:sid> --dbuser <db admin> --dbuserpassword 
<password> -a

Install Supplied Knowledge Bases

The Supplied Knowledge Bases have been moved to be part of the companion Oracle Database 10g Products 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 installation media.

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.

Upgrade Change Data Capture

Beginning with Oracle 10g Release 10.2, Asynchronous Change Data Capture (CDC) no longer requires the same operating system for source and target databases. This feature enables a heterogeneous CDC setup with different operating systems and Oracle versions, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 (9.2) system as a source.

See the Oracle Database Data Warehousing Guide for complete information about how to upgrade a release 9.2 or 10.1 Oracle Database to release 10.2 with Change Data Capture. The discussion describes the supported configurations for the Distributed HotLog mode of Change Data Capture as well as the restrictions.

Configure Secure HTTP

If HTTPS access to Oracle XML DB is required, then you must provide correct configuration information, as described in this section.

When a database is upgraded to release 10.2, the XML schema for the XDB configuration file is automatically upgraded so that the XDB configuration file (located at /xdbconfig.xml in the repository) can have two additional elements, http2-port and http2-protocol. These elements are not added to the XDB configuration file by default during an upgrade. If you wish to have support for HTTPS, you must edit the configuration file to add these two new elements (see the XML schema for their exact locations), and to set the value of http2-protocol to tcps. The value of http2-port should be different from the value of http-port.

In addition to specifying the parameters http2-port and http2-protocol in the XDB configuration file, you must configure the database and the listener to enable Oracle XML DB to use HTTPS. Additionally, if the following steps were not taken before the upgrade, then you must perform them after the upgrade:

1. Enable the HTTP listener and the database to use SSL

2. Enable launching of a TCPS dispatcher

For more information on how to do this, see Oracle XML DB Developer's Guide.

Provide Anonymous Access to XML DB Repository Data via HTTP

If anonymous access to XML DB repository data via HTTP is not required, then you do not have to perform this step. If anonymous access to XML DB repository data via HTTP is required, then you must provide correct configuration information, as described in this section. The administrator must carefully consider whether anonymous access is to be allowed, given the inherent security risks.

When a database is upgraded to Oracle 10g Release 2, the XML schema for the XML DB configuration file (located at /xdbconfig.xml in the repository) is automatically upgraded so that it can have an additional element, allow-repository-anonymous-access. This element is of Boolean type which means it can have a value of true or false. It can be used to disallow unauthenticated access to your Oracle XML DB Repository data through HTTP even if you unlock the ANONYMOUS user account. It is not added to the XML DB configuration file by default during an upgrade but when this element is missing, it is interpreted as false.

Therefore, anonymous access to XML DB repository data via HTTP is disabled when you upgrade to Oracle 10g Release 2. If you wish to have anonymous access to XML DB repository data via HTTP, you must change the configuration file to set this new element to true, in addition to unlocking the ANONYMOUS user account.

Update Your HTML DB Configuration

To update your HTML DB configuration, you must complete a series of post-installation steps. These steps are described in the section on post-installation tasks in your platform-specific Oracle Database installation guide for companion products for your particular platform. In particular, refer to the sections on Post-Installation Tasks for Oracle HTML DB and Post-Installation Tasks for Oracle HTTP Server.

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.

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;

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 Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

   • The Real Application Cluster installation guide for your operating system

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.

Upgrade Oracle Text

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.

Upgrade the Oracle Cluster Registry (OCR) Configuration

If you are using Oracle Cluster Services, then you must upgrade the Oracle Cluster Registry (OCR) keys for the database.

Use one of the following options to upgrade the OCR configuration to 10g:

• Use srvconfig from the 10g ORACLE_HOME. For example:

  % srvconfig -upgrade -dbname db_name -orahome pre-10g_Oracle_home
  

• Run srvctl. For example:

  pre-10g_Oracle_home/bin/srvctl remove database -d db_name
  10g_Oracle_home/bin/srvctl add database -d db_name -o 10g_Oracle_home
  10g_Oracle_home/bin/srvctl add instance -d db_name -i instance -n node
  

Adjust the Initialization 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 the 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. When you are certain that you no longer need the ability to downgrade your database back to its original version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

Install and Configure Enterprise Manager Database Control

If you want to use Enterprise Manager Database Control with your database, then you must install and configure it. For information on how to do this, see the section on Configuring the Database Control with EMCA in Oracle Enterprise Manager Advanced Configuration.

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.