4 Post-Upgrade Tasks for Oracle Database

Setting Environment Variables on Linux and UNIX Systems After Manual Upgrades

If your operating system is Linux or UNIX, and if you performed a manual upgrade of Oracle Database, then you must ensure that certain environment variables point to the directories of the new Oracle Database release. Note that DBUA automatically makes necessary changes to environment variables. Additionally, if you are upgrading a cluster database, then perform these checks on all nodes on which the cluster database has instances configured.

Confirm that the following environment variables point to the directories of the new Oracle home:

• ORACLE_HOME

• PATH

Setting oratab and Scripts to Point to the New Oracle Home After Upgrading Oracle Database

After you upgrade Oracle Database to the new release, you must ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database 11g release. Although DBUA automatically points oratab to the new Oracle home, client scripts must be checked no matter which method you use to upgrade.

Upgrading the Recovery Catalog After Upgrading Oracle Database

For complete information about upgrading the recovery catalog and the UPGRADE CATALOG command, see Oracle Database Backup and Recovery User's Guide for the topic that describes the procedures.

Upgrading the Time Zone File Version After Upgrading Oracle Database

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to upgrade the time zone file.

Oracle Database supplies multiple versions of time zone files, and there are two types of file associated with each one: a large file, which contains all the time zones defined in the database, and a small file, which contains only the most commonly used time zones. The large versions are designated as timezlrg_version_number.dat, while the small versions are designated as timezone_version_number.dat. The files are located in the oracore/zoneinfo subdirectory under the Oracle Database home directory.

Upgrading Statistics Tables Created by the DBMS_STATS Package After Upgrading Oracle Database

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running the following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table'); 

In the example, SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Perform this procedure for each statistics table.

Upgrading Externally Authenticated SSL Users After Upgrading Oracle Database

If you are upgrading from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), and you are using externally authenticated SSL users, then you must run the SSL external users conversion (extusrupgrade) script to upgrade those users. The script has the following syntax:

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

Installing Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database

The Oracle Text supplied knowledge bases are part of the companion Oracle Database 11g release products and are not immediately available after an upgrade to the new Oracle Database 11g release. Any Oracle Text features dependent on the supplied knowledge bases which were available before the upgrade do not function after the upgrade. To re-enable such features, you must install the Oracle Text supplied knowledge bases from the installation media.

After an upgrade, all user extensions to the Oracle Text supplied knowledge bases must be regenerated. These changes affect all databases installed in the given Oracle home.

Updating Your Oracle Application Express Configuration After Upgrading Oracle Database

If your database originally included Application Express Version 3.2 or higher, then there is no additional configuration necessary after upgrading to the new Oracle Database 11g release.

If your database was not an Oracle Express Edition (XE) database, but contained an earlier version of Application Express (HTML DB), then the latest version is automatically installed during the upgrade. You must complete a series of postinstallation steps to configure Application Express for use with the new Oracle Database 11g release.

If your database is an Oracle Express Edition (XE) database, then it contains an earlier version of Application Express, which is tailored for the XE environment. Review the OTN document describing the differences between Oracle XE and Oracle Application Express at the following URL:

http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

The database administration features available with the XE edition of Application Express are not available in version 3.2, but Oracle Enterprise Manager Database Control can, optionally, be installed to provide a graphical interface for database administration.

Configuring Fine-Grained Access to External Network Services After Upgrading Oracle Database

Oracle Database 11g includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use these packages, then you must install Oracle XML DB if it is not installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL, only if that user does not have this privilege. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
  acl_path  VARCHAR2(4000);
BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
   WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path, 
                                         'user_name','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                                         'user_name', TRUE, 'connect');
END IF;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml',
      'ACL description', 'user_name', TRUE, 'connect');
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
END;
COMMIT;

Enabling Oracle Database Vault and Revoking the DV_PATCH_ADMIN Role After Upgrading Oracle Database

If you use Oracle Database Vault, then you were instructed to disable it before upgrading your database. You must now:

• Enable Database Vault.

• Revoke the Database Vault DV_PATCH_ADMIN role for the SYS account.

Recommended Tasks to Perform After All Database Upgrades

Performing the following tasks is recommended, but not required, after you have upgraded your database.

• Back Up the Database

• Reset Passwords to Enforce Case-Sensitivity

• Understand Changes with Oracle Grid Infrastructure

• Understand Oracle ASM and Oracle Grid Infrastructure Installation and Upgrade

• Add New Features as Appropriate

• Develop New Administrative Procedures as Needed

• Set Threshold Values for Tablespace Alerts

• Migrate From Rollback Segments to Automatic Undo Mode

• Configure Oracle Data Guard Broker

• Migrate Tables from the LONG Data Type to the LOB Data Type

• Test the Upgraded Production Database

sql> alter system set sec_case_sensitive_logon=false;

SQL> ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Recommended Tasks After Upgrading an Oracle Database 10g Release 1 (10.1) Database

Performing the following tasks is recommended, but not required, after you have upgraded from Oracle Database 10g Release 1 (10.1) or Oracle Database 10g Release 2 (10.2).

• Upgrade Change Data Capture

• Configure Secure HTTP

• Provide Anonymous Access to XML DB Repository Data Through HTTP

Recommended Tasks After Upgrading an Oracle Express Edition Database

An Oracle Express Edition database contains only a subset of the components available in a Standard Edition or Enterprise Edition database. After upgrading to the new Oracle Database 11g release, you can use the Database Configuration Assistant to install additional components into your database. If you did not install Enterprise Manager Database Control during the DBUA upgrade, then you can install it, along with any other components you would like to have in the database.

Recommended Tasks After Upgrading an Oracle RAC Database

Oracle Real Application Clusters (Oracle RAC) 11g Release 2 (11.2) introduces the Single Client Access Name (SCAN). The SCAN is a single name that resolves to three IP addresses in the public network. When an earlier release of an Oracle RAC database is upgraded to 11g release 2 (11.2), it is registered with SCAN listeners as remote listeners, and also continues to register with all node listeners. You can configure clients to use SCANs, or continue to use the node listeners. If you migrate all of your client connections to use SCANs, you can then remove the node listeners from the REMOTE_LISTENERS parameter. However, you cannot remove the listeners themselves, because only node listeners can create dedicated servers for the database.

Change Passwords for Oracle Supplied Accounts

Depending on the release from which you upgraded, there might 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;

Create Password File with ORAPWD

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to either exclusive or shared, create a password file with ORAPWD.

Migrate Your Initialization Parameter File to a Server Parameter File

If you are currently using a traditional initialization parameter file, then perform the following steps to migrate to a server parameter file:

1. If the initialization parameter file is located on a client computer, then transfer the file from the client computer to the server computer.

   Note:

   If you are using Oracle RAC, 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 Administration and Deployment Guide

   • The Oracle Real Application Clusters 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. You are not required to start the database 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 the new Oracle Database 11g release, 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.

You can obtain a list of these files by doing the following:

1. Looking at $ORACLE_HOME/ctx/admin/ctxf102.txt

2. Executing $ORACLE_HOME/ctx/admin/ctxf102.sql as database user SYS, SYSTEM, or CTXSYS

If your Oracle Text index uses KOREAN_LEXER which was deprecated in Oracle 9i and desupported in Oracle Database 10g Release 2 (10.2), see Note 300172.1 on My Oracle Support for further information on manual migration from KOREAN_LEXER to KOREAN_MORPH_LEXER.

Upgrade the Oracle Clusterware Configuration

If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database.

Run srvctl for release 11.2.0.3 to upgrade the database. For example:

%11.2.0.3_ORACLE_HOME/bin/srvctl upgrade database -d <name> -o 11.2.0.3_ORACLE_HOME

Adjust the Initialization Parameter File for the New Release

Each release of Oracle Database introduces new initialization parameters, deprecates some initialization parameters, and makes some initialization parameters obsolete. You must adjust the parameter file to account for these changes and to take advantage of new initialization parameters that might be beneficial to your system. Additionally, when you perform a manual upgrade without using DBUA, the tnsnames.ora file is not automatically populated with new configuration information and settings. Therefore, you may need to manually update tnsnames.ora and adjust local_listener and remote_listener parameter references if these need to be resolved.

Configure Enterprise Manager

If you are not yet using Oracle Enterprise Manager to manage your database, then install and configure Enterprise Manager Database Control.

If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:

emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]

You must run this from the Oracle home of the new Oracle Database 11g release. When prompted, provide the Oracle home from which the configuration is being upgraded.

You can also configure Enterprise Manager using DBCA. Select the Configure Database Options option, and then select the Enterprise Manager Repository option.

Set CLUSTER_DATABASE Initialization Parameter For Oracle RAC

For upgrades of Oracle RAC databases, in "Preparing the New Oracle Home for a Manual Upgrade", you were instructed to set the CLUSTER_DATABASE initialization parameter to false before upgrading a cluster database. Now that the upgrade is finished, you must set this parameter to true.

Using Environment Variables for Grid Infrastructure Installations

If your operating system is Linux or UNIX, then you may need to change environment variable settings after performing an upgrade.

If you use a single Oracle installation owner for all installations, then be aware that you should change environment variables such as ORACLE_HOME either to an Oracle Database home, or to the grid home, depending on whether you are administering an Oracle Database instance as part of database administration, or administering an Oracle ASM instance as part of storage administration.

If you use role-allocated Oracle installation owners, so that you have a separate owner for the Oracle Grid Infrastructure (Oracle Clusterware and Oracle ASM) software, then set the following environment variables for the grid infrastructure installation owner so that they point to the directories of the Oracle ASM home in the grid infrastructure home:

• ORACLE_HOME

• PATH

Also, check that your oratab file and any client scripts for Oracle ASM that set the value of ORACLE_HOME point to the Oracle ASM home in the grid infrastructure home.

Upgrading An Earlier Release of Oracle ASM to Oracle Grid Infrastructure

In earlier releases, Oracle ASM was installed as part of the Oracle Database installation. With Oracle Database 11g Release 2 (11.2), Oracle ASM is installed when you install the grid infrastructure components. If you install Oracle Grid Infrastructure for a cluster, then it is part of the grid home, with Oracle Clusterware. If you install Oracle Grid Infrastructure for a single server, then Oracle ASM shares an Oracle home with Oracle Restart.

If you want to upgrade an existing Oracle ASM, then you must upgrade Oracle ASM by running an Oracle Grid Infrastructure upgrade. If you do not have Oracle ASM installed and you want to use Oracle ASM as your storage option, then you must complete an Oracle Grid Infrastructure installation before you start your Oracle Database installation.

Oracle ASM Configuration Assistant (ASMCA) supports installing and configuring Oracle ASM instances, disk groups, volumes, and Oracle ASM Cluster File System (Oracle ACFS). Additionally, you can use the ASMCA command-line interface as a non-GUI utility (command name asmca).

You can use the asmca command to complete the upgrade separately. However, you must run asmca soon after you upgrade Oracle Clusterware, as Oracle ASM management tools such as srvctl do not work until Oracle ASM is upgraded.

Upgrading Oracle ASM

The procedure in this section describes how to upgrade Oracle ASM using Oracle ASM Configuration Assistant (ASMCA).

To upgrade Oracle ASM 

1. Log on as the installation owner of the Oracle Grid Infrastructure installation.

2. If you are upgrading on a cluster, then on the node you plan to start the upgrade, set the environment variable ASMCA_ROLLING_UPGRADE as true. For example:

   $ export ASMCA_ROLLING_UPGRADE=true
   

3. From the Oracle Grid Infrastructure 11g release 2 (11.2) home, start ASMCA. For example:

   $ cd /u01/11.2/grid/bin
   $ ./asmca
   

4. Select Upgrade.

   The Oracle ASM Configuration Assistant upgrades Oracle ASM in succession for all nodes in the cluster.

Set Environment Variables

If your operating system is Linux or UNIX, then make sure that the following environment variables point to the directories of the new Oracle Database 11g release:

• ORACLE_HOME

• PATH

Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle home of the new Oracle Database 11g release.

Single-Instance Oracle ASM Upgrade

The following procedure assumes that Oracle ASM is installed in Oracle home 1 (OH1) and the operating system user is orauser.

To perform a single-instance upgrade of Oracle ASM 

1. As orauser, upgrade Oracle ASM to release 11.2 using OUI and ASMCA. The new Oracle ASM release 11.2 runs in the grid infrastructure home. Oracle ASM should still be running as orauser.

2. As orauser, bring down the Oracle ASM instance and the listener.

3. As root, run /etc/init.d/init.cssd stop to stop CSS.

4. As a new user (asmuser), install 11.2 in a third Oracle home (OH3). This should be a software-only installation.

5. As root, run localconfig reset from OH3.

6. Update /etc/oratab so that OH3 is the Oracle home with the +ASM entry.

7. Copy listener.ora, sqlnet.ora, and tnsnames.ora from OH2.

8. Run EMCP to change the Oracle ASM instance path and connect-string role.

9. Make sure the disks are owned by asmuser and by OSASM for Oracle ASM. They should also have the O660 permission set.

10. As asmuser, start the listener.

11. As asmuser, start Oracle ASM (connect as SYSASM).

12. Run the command, GRANT sysasm TO sys.

Cluster Oracle ASM Upgrade

To perform an upgrade of Oracle ASM on a cluster 

1. As orauser, upgrade Oracle ASM to release 11.2 using OUI and ASMCA. The new Oracle ASM release 11.2 should be running in a new Oracle home 2 (OH2). Oracle ASM should still be running as orauser.

2. Bring down the Oracle ASM and Listener resources from CRS home.

3. As a new user (crs for example), install 11.2 into a third Oracle home (OH3) to match that of the Grid Infrastructure home. This must be a software-only installation.

4. From CRS home, run:

   srvctl remove listener -n node_name
   srvctl add listener -n node_name -o OH3
   srvctl modify asm -n node_name -i ASM_instance_name -o ORACLE_HOME_path
   

   Caution:

   By default, any named user may create a server pool. To restrict the operating system users that have this privilege, Oracle strongly recommends that you add specific users to the CRS Administrators list.

   See Also: Oracle Clusterware Administration and Deployment Guide for more information about adding users to the CRS Administrators list

5. Update /etc/oratab so that OH3 is the Oracle home with the +ASM entry.

6. Copy listener.ora, sqlnet.ora, and tnsnames.ora from OH2.

7. Run EMCP to change the Oracle ASM instance path and connect-string role.

8. Make sure the disks are owned by asmuser and by OSASM for Oracle ASM. They should also have the O660 permission set.

9. Start Oracle ASM and Listener resources from the new Oracle ASM 11g ORACLE_HOME or the new Oracle Database 11g ORACLE_HOME.

10. Run the command, GRANT sysasm TO sys.

If you have clustered Oracle ASM instances, then you also have the option of performing a rolling Oracle ASM upgrade. A rolling upgrade enables you to independently upgrade or patch Oracle ASM nodes without affecting database availability, thus providing greater uptime.

Additional Considerations After Oracle ASM Upgrades

If you separate the operating system user ownership of the Oracle Grid Infrastructure binaries and the Oracle Database installation owners of one or more databases, then you must migrate the operating system user of an upgraded Oracle ASM or database home as described in "Role-Allocated Software Owners and Database Upgrade After Oracle ASM Upgrade".

Reset Oracle ASM Passwords to Enforce Case-Sensitivity

Starting with Oracle Database 11g Release 1 (11.1), you can enforce case sensitivity for passwords. For example, the password hPP5620qr fails if it is entered as hpp5620QR or hPp5620Qr. In previous releases, passwords were not case sensitive.

To take advantage of enforced case-sensitive passwords, you must reset the passwords of existing users during the database upgrade procedure. For new Oracle Oracle ASM instances, there are no additional tasks or management requirements. For upgraded Oracle ASM instances, each user password must be reset with an ALTER USER statement.

Advance the Oracle ASM and Oracle Database Disk Group Compatibility

Starting with Oracle Database 11g Release 1 (11.1), you can advance the Oracle Database and the Oracle ASM disk group compatibility settings across software versions.

Advancing compatibility enables new features only available in the new release. However, doing so makes the disk group incompatible with older releases of the software. Note that advancing the on-disk compatibility is an irreversible operation.

You use the compatible.rdbms and compatible.asm attributes to specify the minimum software release required by the database instance and the Oracle ASM instance, respectively, to access the disk group. For example, the following ALTER DISKGROUP statement advances the Oracle ASM compatibility of the disk group asmdg2:

ALTER DISKGROUP asmdg2 SET ATTRIBUTE 'compatible.asm' = '11.1'

In this case, the disk group can be managed only by Oracle ASM software of release 11.1 or higher, while any database client of release 10.1 or higher can use the disk group.

Set Up Oracle ASM Preferred Read Failure Groups

Oracle ASM administrators can specify some disks to be preferred over others for read I/O operations. When Oracle ASM preferred read failure groups are defined, Oracle ASM can read from the extent that is closest to it, rather than always reading the primary copy.