Review and complete these required tasks that are specified for your environment after you complete your upgrade.
You must complete these postupgrade tasks after you upgrade Oracle Database. You must complete these tasks both when you perform the upgrade manually, and when you upgrade by using Database Upgrade Assistant (DBUA).
Setting Environment Variables on Linux and UNIX Systems After Manual Upgrades
If you performed a manual upgrade of Oracle Database, then you must ensure that required operating system environment variables point to the directories of the new Oracle Database release.
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:
DBUA automatically makes necessary changes to Oracle environment variables.
Oracle Database Administrator's Guide for information about setting environment variables for Oracle Database
Oracle Database Installation Guide for your operating system for information about setting other environment variables that affect Oracle Database single-instance Oracle Databas
Oracle Grid Infrastructure Installation Guide for your operating system for information about setting other environment variables that affect Oracle Real Application Clusters Oracle Databases, and other deployments on Oracle Grid Infrastructure clusters
Recompiling All Invalid Objects
Oracle recommends you run the
utlrp.sql script after you install, patch, or upgrade a database, to identify and recompile invalid objects.
utlrp.sqlscript recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects.
- Start SQL*Plus:
sqlplus "/ AS SYSDBA"
- Run the
Oracle_homeis the Oracle home path:
utlrp.sql script automatically recompiles invalid objects in either serial or parallel recompilation, based on both the number invalid objects, and on the number of CPUs available. CPUs are calculated using the number of CPUs (cpu_count) multiplied by the number of threads for each CPU (parallel_threads_per_cpu). On Oracle Real Application Clusters (Oracle RAC), this number is added across all Oracle RAC nodes.
Recompiling All Invalid Objects on Multitenant Architecture Databases
On multitenant architecture Oracle Databases, after upgrading. Oracle recommends that you recompile invalid objects using the
catcon Perl script.
catcon.pl utility to run
utlrp.sql on all containers in your container database (CDB).
catcon.pl script runs utlrp.sql from the
$ORACLE_HOME/rdbms/admin directory. The script recompiles any remaining stored PL/SQL and Java code.
Example 4-1 Running the utlrp.sql Script On All Containers in the CDB With the CATCON Utility
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
Note the following conditions of this use case:
-nparameter is set to 1, so the script runs each PDB recompilation in sequence.
Expect a time delay for the serial recompilation of PDBs to complete. Depending on the number of PDBs that you are upgrading, the recompilation can extend significantly beyond the time required for the upgrade scripts to complete.
Track Invalid Object Recompilation Progress
Use these SQL queries to track the progress of utlrp.sql script recompilation of invalid objects.
utlrp.sqlscript after upgrade to recompile invalid objects. You can run SQL queries to monitor the script.
Example 4-2 Number of Invalid Objects Remaining
Enter this query to return the number of remaining invalid objects. This number decreases over time as the
utlrp.sql script runs.
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
Example 4-3 Number of Objects Recompiled
Enter this query to return the number of objects that utlrp.sql has compiled. This number increases over time as the script runs.
SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
Example 4-4 Number of Objects Recompiled with Errors
Enter this query to return the number of objects that
utlrp.sql has compiled with errors.
select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;
If the number is higher than expected, then examine the error messages reported with each object. If you see errors due to system misconfiguration or resource constraints, then fix the cause of these errors, and run
Running OPatch Commands After Upgrading Oracle Database
After you upgrade Oracle Database, you must run OPatch commands from the new Oracle home.
OPatch is a Java-based utility that you install with Oracle Universal Installer. Opatch is platform-independent. It runs on all supported operating systems. Another version of OPatch, called standalone OPatch, is also available. It runs on Oracle homes without Oracle Universal Installer.
Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products. When applied to the correct version of an installed product, patches result in an upgraded version of the product.
Run Opatch to Check the Oracle Database Inventory
Log in as the Oracle installation owner, and run the
lsinventory command from the new Oracle home. The command generates an accurate and complete inventory of the Oracle software installed on the system:
opatch lsinventory –patch
Obtain Interim Patches
Oracle releases interim patches frequently, either to fix a bug, or to fix a set of bugs. You can obtain interim patches by specifying the patch identifier in My Oracle Support.
Setting oratab and Scripts to Point to the New Oracle Location After Upgrading Oracle Database
You must set scripts to point to the new Oracle home location.
After you upgrade Oracle Database to a 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 12c release. DBUA automatically points
oratab to the new Oracle home. However, you must check client scripts regardless of the method you use to upgrade.
Oracle Database Administrator's Guide for information about setting operating system environment variables
Check PL/SQL Packages and Dependent Procedures
Packages that you installed in the earlier release Oracle Database may not be upgraded automatically in the new release, which may affect applications.
After the upgrade, check to ensure that any packages that you have used in your own scripts, or that you call from your scripts, are available in the new release. Testing procedures dependent on packages should be part of your upgrade plan.
Code in database applications can reference objects in the connected database. For example, Oracle Call Interface (OCI) and precompiler applications can submit anonymous PL/SQL blocks. Triggers in Oracle Forms applications can reference a schema object. Such applications are dependent on the schema objects they reference. Dependency management techniques vary, depending on the development environment. Oracle Database does not automatically track application dependencies.
Upgrading Tables Dependent on Oracle-Maintained Types
Starting with Oracle Database 12c release 2 (12.2), you must manually upgrade user tables that depend on Oracle-Maintained types.
If your database has user tables that are dependent on Oracle-Maintained types (for example, AQ queue tables), then run the
utluptabdata.sql command after the upgrade to carry out ALTER TABLE UPGRADE on any user tables affected by changes in Oracle-Maintained types. This change in behavior enables user tables to remain in READ-ONLY state during an upgrade. Users are prevented from logging into applications AS SYSDBA and changing application tables that are dependent on Oracle-Maintained types.
To identify tables that need to be upgraded after the database upgrade, connect AS SYSDBA and run the following query:
COLUMN owner FORMAT A30 COLUMN table_name FORMAT A30 SELECT DISTINCT owner, table_name FROM dba_tab_cols WHERE data_upgraded = 'NO' ORDER BY 1,2;
This query lists all tables that are not listed as UPGRADED. However, the
utluptabdata.sql script only upgrades tables that depend on Oracle-Maintained types. If any tables are listed by the query, then run the
utluptabdata.sql script to perform ALTER TABLE UPGRADE commands on dependent user tables to upgrade these Oracle-Maintained types to the latest version of the type.
You must run the
utluptabdata.sql script either with a user account with the privileges to ALTER all of the tables dependent on Oracle-Maintained types, or with a user granted the SYSDBA system privileges that is logged in AS SYSDBA.
When the parameter SERVEROUTPUT is set to
utluptabdata.sql script displays the names of all upgraded tables, and lists any error encountered during the table upgrade. Run the following command to set the server output to
SET SERVEROUTPUT ON @utluptabdata.sql
Enabling the New Extended Data Type Capability
Enabling a system to take advantage of the new extended data types requires specific upgrade actions.
Oracle Database 12c introduces
MAX_STRING_SIZE to control the maximum size of
RAW data types in SQL. Setting
MAX_STRING_SIZE = EXTENDED enables the 32767 byte limit introduced in Oracle Database 12c.
You must set the
COMPATIBLE initialization parameter to
18.104.22.168 or higher to be able to set
MAX_STRING_SIZE = EXTENDED.
You can change the value of
EXTENDED. However, you cannot change the value of
STANDARD. By setting
MAX_STRING_SIZE = EXTENDED, you are taking an explicit action that could introduce application incompatibility in your database.
Oracle Database Reference for complete information about
MAX_STRING_SIZE, including recommendations and procedures
Adjusting Minimum and Maximum for Parallel Execution Servers
You may need to adjust the PARALLEL_MIN_SERVERS default setting, depending on your environment.
In Oracle Database 12c the default for
PARALLEL_MIN_SERVERS changes from
0 to a value depending on your hardware platform, to provide sufficient minimal support for parallel execution. If the new default setting is too high for your environment, then adjust the setting for your requirements. The default for
PARALLEL_MAX_SERVERS has not changed. If the default in your old environment is unchanged, then you do not need to take further action.
Oracle Database Reference for information about
About Recovery Catalog Upgrade After Upgrading Oracle Database
If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it.
Upgrading the Time Zone File Version After Upgrading Oracle Database
If the Pre-Upgrade Information Tool instructs 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. There are two types of file associated with each time zone file: 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. 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
In the following example,
green is the owner of the statistics table and
STAT_TABLE is the name of the statistics table.
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('green', 'stat_table');
Perform this procedure for each statistics table.
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_STATS package
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.
extusrupgrade script has the following syntax, where
ORACLE_HOME is the Oracle database home,
hostname is the name of the host on which the database is running,
port_no is the listener port number,
sid is the system identifier for the database instance, and
db_admin is the database administrative user with privileges to modify user accounts.
ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring hostname:port_no:sid --dbuser db_admin --dbuserpassword password -a
extusrupgrade --dbconnectstring dlsun88:1521:10gR2 --dbuser system --dbuserpassword manager -a
If you are upgrading from Oracle Database 10g Release 2 (10.2) or later, then you are not required to run the
Oracle Database Enterprise User Security Administrator's Guide for more information about the
Configuring the FTP and HTTP Ports and HTTP Authentication for Oracle XML DB
Database Creation Assistant (DBCA) does not configure ports for Oracle XML DB on Oracle Database 12c.
Oracle recommends that when you configure ports, you also configure the authentication for HTTP for accessing Oracle XML DB Repository to take advantage of improved security features.
Starting with Oracle Database 12c, Oracle has enhanced database security by supporting digest authentication. Digest authentication is an industry-standard protocol that is commonly used with the HTTP protocol. It is supported by most HTTP clients. Digest authentication ensures that passwords are always transmitted in a secure manner, even when an encrypted (HTTPS) connection is not in use. Support for digest authentication enables organizations to deploy applications that use Oracle XML DB HTTP, without having to worry about passwords being compromised. Digest authentication support in Oracle XML DB also ensures that the Oracle XML DB HTTP server remains compatible with Microsoft Web Folders WebDAV clients.
After installing or upgrading for the new release, you must manually configure the FTP and HTTP ports for Oracle XML DB as follows:
HTTP port number) to set the HTTP port for Oracle XML DB:
SQL> exec DBMS_XDB_CONFIG.setHTTPPort(port_number);
FTP port number) to set the FTP port for Oracle XML DB:
SQL> exec DBMS_XDB_CONFIG.setFTPPort( port_number);
You can query the port numbers to use for FTP and HTTP in the procedure by using
To see all the used port numbers, query
Oracle XML DB Developer’s Guide for more information about accessing the Oracle XML DB Repository data using FTP, HTTP, HTTPS, and WebDAV protocols
Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database
Regenerate user extensions to Oracle Text after upgrading.
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.
The Oracle Text-supplied knowledge bases are part of the companion products for Oracle Database 12c and are not immediately available after an upgrade to Oracle Database 12c. 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.
Rebuild Oracle Text Indexes Using AUTO_LEXER
If you are upgrading Oracle Text from releases earlier than Oracle Database 12c, then review this topic.
After you complete your upgrade to Oracle Database 12c release 1, if you use Oracle Text indexes created with AUTO_LEXER, then you must rebuild the indexes for your queries to work.
In addition, you must rebuild indexes that have the following INDEX_STEMS types of BASIC_LEXER set:
Update Oracle Application Express Configuration After Upgrading Oracle Database
Oracle Application Express affects upgrade procedures, depending on the Oracle Application Express release and your database installation type.
If the Oracle Database release that you upgrade includes Oracle Application Express release 3.2 or later, then you do not need to carry out additional configuration after upgrading to Oracle Database 12c. However, if Oracle Application Express is in the registry, so that Oracle Application Express is included in the upgrade, then set the open_cursors parameter to a minimum of 200.
If the Oracle Database you upgrade is an Oracle Express Edition database, but it contains an earlier release of Oracle Application Express, then the latest release 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 12c.
If your database is an Oracle Express Edition database, then it contains an earlier release of Oracle Application Express that is tailored for the Oracle Express Edition environment.
Oracle Application Express Installation Guide for postinstallation tasks for Oracle Application Express
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.htmlfor a description of the differences between Oracle Express Edition and Oracle Application Express
Configure Access Control Lists (ACLs) to External Network Services
Oracle Database 12c includes fine-grained access control to the
If you have applications that use these packages, then after upgrading Oracle Database you must configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases. Without the ACLs, your applications may fail with the error "ORA-24247: network access denied by access control list (ACL)."
Oracle Database Security Guide for more complicated situations, such as connecting some users to host A and other users to host B
Enabling Oracle Database Vault After Upgrading Oracle Database
Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.
Starting with Oracle Database 12c release 2 (12.2), if you have Oracle Database Vault enabled, then you can upgrade the Oracle Database without first disabling Oracle Database Vault. After the upgrade, Oracle Database Vault is enabled by default with the enforcement settings that you had in place before the upgrade.
If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade.
If you did not have Oracle Database Vault enabled before the upgrade, then you can enable it manually after the upgrade.
Enable Oracle Database Vault in the upgraded database by using the procedure
dvsys.dbms_macadm.enable_dv(). Run this procedure with a user account that is granted either
DV_ADMIN. After you run the procedure, restart the database instance so that the procedure takes effect.
Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior
Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol.
Starting with Oracle Database 12c, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter changes from 8 to 11. The use of this parameter has been deprecated.
SQLNET.ALLOWED_LOGON_VERSION is now replaced with the SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters. If you have not explicitly set the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter in the upgraded database, then connections from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol. For better security, check the password verifiers of your database users, and then configure the database to use the correct password verifier by setting the SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters.
If you have password-protected roles (secure roles) in your existing database, and if you upgrade to Oracle Database 12c with the default
SQLNET.ALLOWED_LOGON_VERSION_SERVER setting of 11, because those secure roles only have release 10g verifiers, then the password for each secure role must be reset by the administrator so that the secure roles will remain usable after the upgrade.