This chapter guides you through the procedures to perform after you have completed an upgrade of your database. The following topics are discussed:
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 databaseIf 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
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 (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 dataIf 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 (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.
See Also:
$ORACLE_HOME/oracore/zoneinfo/readme.txt
for detailed information about time zone file updatesFor 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');
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.
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 theextusrupgrade
scriptThe 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
.
See Also:
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-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:
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 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.
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 theallow-repository-anonymous-access
element and configuring Oracle XML DBTo 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:
Migrate Your Initialization Parameter File to a Server Parameter File
Adjust the Initialization Parameter File for the New Release
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;
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.
See Also:
Oracle Database Administrator's Guide for more information about creating server parameter files
Oracle Database SQL Reference for information about the CREATE SPFILE
statement
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
Oracle Text Application Developer's Guide for information about upgrading your applications from previous releases of Oracle Text
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
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.
See Also:
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
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.
See Also:
"Setting the COMPATIBLE Initialization Parameter" for informationIf 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 ORACLE_HOME
/rdbms/admin
directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA
privileges.
If the instance is running, shut it down using SHUTDOWN IMMEDIATE
:
SQL> SHUTDOWN IMMEDIATE
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.
Run utlnchar.sql
:
SQL> @utlnchar.sql
Alternatively, to override the default upgrade selection, run n_switch.sql
:
SQL> @n_switch.sql
Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
Exit 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.