|Oracle® Database Upgrade Guide
10g Release 2 (10.2)
|PDF · Mobi · ePub|
Complete the following tasks after you have upgraded your database, regardless of whether you performed the upgrade manually or by using the Database Upgrade Assistant (DBUA):
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
If your operating system is UNIX, then make sure that the following environment variables point to the new release 10.2 directories:
(Note that the
ORA_NLS10 environment variable replaces the
ORA_NLS33 environment variable, so you may need to unset
ORA_NLS33 and set
Note:If you are upgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.
See Also:Your operating system-specific Oracle Database installation documents for information about setting other important environment variables on your operating system.
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
LOB datatypes (
NCLOB) can provide many advantages over
LONG datatypes. See Oracle Database Concepts for information about the differences between
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
In the following example, the
LONG column named
long_col in table
long_tab is changed to datatype
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
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.
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 (
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.
$ORACLE_HOME/oracore/zoneinfo/readme.txtfor detailed information about time zone file updates
For information about upgrading the recovery catalog, see Oracle Database Backup and Recovery Advanced User's Guide.
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');
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.
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
See Also:Oracle Database Enterprise User Administrator's Guide for more information on the
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 Text Application Developer's Guide for information about Supplied Knowledge Bases
The post-installation tasks section of your platform-specific Oracle Database installation guide for companion products for your platform
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.
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-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
tcps. The value of
http2-port should be different from the value of
In addition to specifying the parameters
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:
Enable the HTTP listener and the database to use SSL
Enable launching of a TCPS dispatcher
For more information on how to do this, see Oracle XML DB Developer's Guide.
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
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
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.
Caution:There is an inherent security risk associated with allowing unauthenticated access to the repository.
See Also:Oracle XML DB Developer's Guide for more information about the
allow-repository-anonymous-accesselement and configuring Oracle XML DB
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.
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.
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.
If you are performing a manual upgrade rather than using the Database Upgrade Assistant (DBUA), then you must perform the following tasks after your database is upgraded:
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
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;
If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:
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:
The Real Application Cluster installation guide for your operating system
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.
Start up the instance using the newly-created server parameter file.
After an upgrade to Oracle Database 10g, copy the following files from the previous
ORACLE_HOME to the new
Stemming user-dictionary files
KOREAN_MORPH_LEXER dictionary files
These files affect all databases installed in the given
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:
srvconfig from the 10g ORACLE_HOME. For example:
% srvconfig -upgrade -dbname db_name -orahome pre-10g_Oracle_home
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
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.
Oracle Database Reference for a list of the new initialization parameters in release 10.2, and for information about each parameter
Appendix A, "Initialization Parameter and Data Dictionary Changes" for lists of obsolete and deprecated initialization parameters in release 10.2
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.
See Also:"Setting the COMPATIBLE Initialization Parameter" for information
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.
Complete the following additional tasks only if you upgraded your database from release 8.1.7.
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:
Log in to the system as the owner of the Oracle home directory.
At a system prompt, change to the
Connect to the database instance as a user with
If the instance is running, shut it down using
SQL> SHUTDOWN IMMEDIATE
Start up the instance in
SQL> STARTUP RESTRICT
You may need to use the
PFILE option to specify the location of your initialization parameter file.
Alternatively, to override the default upgrade selection, run
Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
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.