Oracle Corporation and its affiliates disclaim any responsibility for any damages caused by the use of this software in unsafe applications. This software and documentation may provide access to or information about third party content, products and services.
Preface
Audience
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at An Oracle Support Services engineer will handle technical issues and provide customer support according to Oracle's service request process.
Related Documentation
This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. For more information, visit the Oracle Accessibility Program website at http://www.oracle.com/accessibility/.
Conventions
Many of the examples in this guide use the sample schemas that are installed by default when you select the Basic Installation option with an Oracle Database installation. This chapter provides an overview of the database upgrade process as well as information about running multiple releases of Oracle Database.
Overview of the Database Upgrade Process
- Prepare to Upgrade
- Test the Upgrade Process
- Test the Upgraded Test Database
- Prepare and Preserve the Production Database
- Upgrade the Production Database
- Tune and Adjust the New Production Database
Prepare the current production database as appropriate to ensure that the upgrade to the new Oracle Database 11g release is successful. The new production database should perform as well, or better, than the database before the upgrade.
Oracle Release Numbers
Running Multiple Oracle Releases
You can upgrade any or all of your Oracle8i, Oracle9i, Oracle Database 10g, or Oracle Database 11g clients to the new Oracle Database 11g release. The new Oracle Database 11g release client can be used to access your Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Database 11g databases.
Compatibility and Interoperability
You can install Oracle8i, Oracle9i, Oracle Database 10g and Oracle Database 11g databases in multiple (separate) Oracle homes on the same computer and have Oracle8i, Oracle9i, Oracle Database 10g and Oracle Database 11g clients, that connects to any or all of the databases. You can install Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Database 11g databases in multiple (separate) Oracle homes on separate computers and have Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Database 11g clients to connect to any or all of the databases.
What Is Compatibility?
If this happens, set the COMPATIBLE initialization parameter to an appropriate value for the database. For example, if you set the COMPATIBLE initialization parameter to 11.0.0, the database will operate at compatibility level 11.0.0.
What Is Interoperability?
If you try to use any new feature that makes the database incompatible with the COMPATIBLE initialization parameter, then an error is raised. Compatibility level The compatibility level of your database corresponds to the value of the COMPATIBLE initialization parameter.
Using Optimal Flexible Architecture (OFA)
Converting Databases to 64-bit Oracle Database Software
About Rolling Upgrades
Moving From the Standard Edition to the Enterprise Edition
Moving From the Enterprise Edition to the Standard Edition
Upgrading from Oracle Database Express Edition to Oracle Database
This chapter covers in detail steps 1 through 3 of the upgrade process that were described in "Overview of the Database Upgrade Process" on page 1-1.
Prepare to Upgrade
Become Familiar with New Oracle Database Features
Determine the Upgrade Path
Choose an Upgrade Method
Unlike DBUA or a manual upgrade, the Export/Import utilities physically copy data from your current database to a new database. Export/Import can copy a subset of the data into a database, leaving the original database unchanged.
Choose an Oracle Home Directory
Allows the creation of the new database on an operating system or hardware platform that is different from the one that supports the database being upgraded. Network-based data pump import allows the new Oracle database to be loaded directly onto the network from the old database being upgraded.
Develop a Testing Plan
Bulk loading execution plans or SQL plan baselines is especially useful when upgrading a database from a previous release to Oracle Database 11g. On the Oracle Database 11g test system, after completing all testing and tuning, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Enterprise Manager to load all the execution plans into the cursor cache in SQL Management Base.
Prepare a Backup Strategy
Pack the baselines of the SQL plan you created in step 1 into the staging table using the DBMS_SPM.PACK_STGTAB_BASELINE function. Extract the SQL plan baselines from the staging table into the SQL Management Base on the target system using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.
Test the Upgrade Process
Test the Upgraded Test Database
This chapter guides you through the process of upgrading a database to the new release of Oracle Database 11g. The upgrade processes discussed in this chapter do not describe how to perform continuous upgrades.
System Considerations and Requirements
Oracle ASM is Installed with Oracle Grid Infrastructure
Upgrading Oracle Clusterware and Automatic Storage Management (ASM) Databases
Beginning with Oracle Database 11g Release 1 (11.1), you must use the SYSASM privilege to separate database and storage management responsibilities. For example, if there are n databases using ASM on a given node, you can configure n + 1 sets of operating system credential groups whose members have SYS privileges: one OSDBA group for each database with SYSDBA privileges and one OSASM group for ASM instance with SYSASM privileges.
Upgrading with Read-Only and Offline Tablespaces
Offline data file headers are updated later when they are brought online, and read-only tablespace headers are updated if and when they become read/write enabled sometime after the upgrade. In no other case do you ever need to enable read/write to read-only tablespaces.
Upgrading Using Standby Databases
Upgrading Your Operating System
Migrating Data to a Different Operating System
Upgrading Databases That Use Oracle Streams Downstream Capture
Upgrading Databases That Use Oracle Database Vault
Install the New Oracle Database Software
Install the Latest Available Patchset Release and Any Required Patches
The latest patchset release and critical patch update for Oracle Database 11g Release 2 (11.2) must be installed before upgrading your databases.
Run the Pre-Upgrade Information Tool
WARNING: --> The SYSTEM tablespace is not large enough for the upgrade. Tablespace DOES NOT ENABLE AUTOEXTEND. gt; The UNDOTBS1 tablespace is eligible for upgrade. This section displays a list of initialization parameters in the current database's parameters file that must be adjusted before the database can be upgraded.
Pre-Upgrade Information Tool Miscellaneous Warnings
This section displays the minimum required size for the SYSAUX tablespace, which is required in the new Oracle Database 11g release. The emdwgrd utility is in the ORACLE_HOME/bin directory in the new Oracle Database 11g release.
Run the Oracle Net Configuration Assistant
Ensure that all logs are transported to the standby server after a final log switch to the primary. It is REQUIRED that the recycle bin is empty during the update to avoid possible ORA-00600 errors as well as to minimize the update time.
Upgrade a Database Using Database Upgrade Assistant
If the database instance is not running, DBUA attempts to start the instance using the default initialization parameter file. If this fails, you will be prompted to enter the name of the correct initialization parameter file or to start the instance.
Using the DBUA Graphical User Interface
If you do not centrally manage your Oracle environment, you can still use Enterprise Manager to manage your database. Complete the following steps if you want the SYSMAN user (the default super administrator and owner of the Management Repository schema) to receive.
Using DBUA in Silent Mode
DBUA removes the upgraded database entry from the old listener.ora file and reloads the old database listener. Specifies a comma-delimited list of initialization parameter values of the form name=value -disableArchiveLogMode This option disables archiving and rollback.
Upgrade a Database Manually
Back Up the Database
Prepare the New Oracle Home
Next, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step a through Step d. If you are upgrading a cluster database, then edit the initdb_name.ora file the same way you edited the parameters file.
Upgrade the Database
Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) home directory. Below are common errors that may occur when trying to start the new Oracle Database 11g Release 2 (11.2) database.
Troubleshoot the Upgrade
Determine the root cause of the problem and take appropriate action to proceed with the upgrade. The workaround is to delete the REPCAT$_AUDIT_COLUMN_IDX1 index and restart the upgrade as described in "Restarting the Upgrade" on page 3-45.
Cancel the Upgrade
Upgrade an ASM Instance
Upgrade an ASM Instance Manually
The oratab file points to the Oracle home for the new Oracle Database 11g Release 2 (11.2) ASM instance. Log in to the system as the owner of the Oracle home for the new Oracle Database 11g Release 2 (11.2) ASM instance.
Required Tasks After Database Upgrades
Update Environment Variables (Linux and UNIX Systems Only)
Upgrade the Recovery Catalog
Upgrade the Time Zone File Version
Upgrade Statistics Tables Created by the DBMS_STATS Package
Upgrade Externally Authenticated SSL Users
Install Oracle Text Supplied Knowledge Bases
Update Your Oracle Application Express Configuration
Configure Fine-Grained Access to External Network Services
AS DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path, .. user_name','connect') NULL IS DAN IS DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,. DBMS_NETWORK_ACL_ADMIN.CREATE_ACL_ADMIN.CREATE_ACL_ADMIN.CREATE_ACL_ADMIN.CREATE_ACL_name,'xml_ACL.'us beskrywing,'er_ACL.'us 'koppel' );.
Enable Oracle Database Vault and Revoke the DV_PATCH_ADMIN Role
Recommended Tasks After Database Upgrades
Recommended Tasks After All Database Upgrades
The upgraded Oracle Database 11g Release 1 (11.1) database has tablespace warnings disabled (thresholds set to zero). As of Oracle Database 11g Release 1 (11.1), automatic undo management is the default way to manage undo space.
Recommended Tasks After Upgrading an Oracle Database 10g Release 1 (10.1) Database
If anonymous access to XML DB repository data via HTTP is required, you must ensure proper configuration. Therefore, anonymous access to XML DB repository data via HTTP is disabled when you upgrade to Oracle Database 10g Release 2 (10.2) or later.
Recommended Tasks After Upgrading an Oracle Express Edition Database
To access data from the XML DB repository anonymously over HTTP, you must modify the configuration file to set this new element to true, in addition to unlocking the ANONYMOUS user account.
Tasks to Complete Only After Manual Database Upgrades
Change Passwords for Oracle-Supplied Accounts
Migrate Your Initialization Parameter File to a Server Parameter File
Upgrade Oracle Text
Upgrade the Oracle Cluster Registry (OCR) Configuration
Adjust the Initialization Parameter File for the New Release
Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.
Configure Enterprise Manager
Increasing the COMPATIBLE initialization parameter may cause your database to become incompatible with previous releases of Oracle Database, and a backup ensures that you can revert to the previous release if necessary. You must run this from the Oracle home page of the new Oracle Database 11g release.
Set CLUSTER_DATABASE Initialization Parameter
Required Tasks After ASM Upgrades
Set Environment Variables
Single-Instance ASM Upgrade
Cluster ASM Upgrade
Recommended Tasks After ASM Upgrades
Reset ASM Passwords to Enforce Case-Sensitivity
Advance the ASM and Oracle Database Disk Group Compatibility
Set Up ASM Preferred Read Failure Groups
Optional Tasks After ASM Upgrades
Database Upgrade After ASM Upgrade
If necessary, the user can change the database operating system user to allow separate databases to run as separate operating system users.
Tasks to Complete Only After Manual ASM Upgrades
Upgrade the Oracle Cluster Registry (OCR) Configuration for ASM
Adjust the ASM Initialization Parameter File for the New Release
Install and Configure Enterprise Manager Database Control for ASM
Overview of Upgrading Applications
Compatibility Issues for Applications
So Oracle8i, Oracle9i, Oracle Database 10g and Oracle Database 11g can communicate using Net8 and Oracle Net Services.
Upgrading Precompiler and OCI Applications
Understanding Software Upgrades and Your Client/Server Configuration
Different computers The client software and the server software are on different computers and are connected through a network. Same Oracle Home The client software and the server software are installed in the same Oracle home on the same computer.
Compatibility Rules for Applications When Upgrading Client/Server Software
Different Oracle home directories on the same computer The client software and the server software are on the same computer, but they are installed in different Oracle home directories. In this case, any upgrade to the server software is also an upgrade to the client software.
Upgrading Options for Your Precompiler and OCI Applications
Leave the Application Unchanged
Precompile or Compile the Application Using the New Software
Some problems in the application code that were not detected by previous versions of Oracle software may appear when you precompile or compile with the new Oracle software. Also, recompiling ensures maximum stability for your application because you are sure it works with the new Oracle software.
Change the Application Code to Use New Oracle Database 11g Features You can make code changes to your application to take advantage of new Oracle
Therefore, precompiling and compiling with the new software often helps you detect and fix problems in the application code that may have gone unnoticed before. In addition, you may benefit from performance improvements that are only available with the new Oracle software after you recompile and relink.
Upgrading SQL*Plus Scripts and PL/SQL
Evaluation of Numeric Literals
Upgrading Oracle Forms or Oracle Developer Applications
Note: New releases of Oracle Developer may introduce new reserved words that are specific to Oracle Developer. Code changes may be required if your application uses any of these new reserved words.
Supported Releases for Downgrading
This chapter guides you through the process of downgrading a database to a previous Oracle Database release. However, if you save your Oracle Enterprise Manager Database Control files and data before you upgrade your database, then you can restore the old version of Database Control after you downgrade the database.
Check for Incompatibilities
Perform a Full Backup
Downgrade the Database
At a system prompt, go to the ORACLE_HOME/rdbms/admin folder from the previous release. Copy the olstrig.sql script from Oracle Database 11g Release 2 (11.2) Oracle home to the Oracle home of the version to which the database is to be downgraded.
Perform Post-Downgrade Tasks
Downgrading Oracle Clusterware Configuration
Restoring Oracle Enterprise Manager
Set ORACLE_HOME to the Oracle home page from which the database was originally upgraded. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the Oracle home page from which the database upgrade was originally performed.
Enabling Oracle Database Vault
When emdwgrd ends, Oracle Enterprise Manager Database Control has been downgraded to the old native Oracle.
Reload Oracle Application Express
This chapter walks you through using Data Pump Export and Import utilities, or the original Export and Import utilities, to move data from one database to another.
When to Use Data Pump Export/Import Versus Original Export/Import
If you are upgrading from a release earlier than Oracle Database 10g Release 1 (10.1), you must use the original export/import tools to move the data. Similarly, if you downgrade your database to a release prior to Oracle Database 10g Release 1 (10.1), you must use the original Export/Import tools.
Export and Import Requirements
If you need to downgrade to a version prior to Oracle Database 10g Release 1 (10.1), then the original Export program continues to provide support to ensure backward compatibility. If you are moving XMLType tables or tables containing XMLType columns (schema-based rather than schema-based) from Oracle Database 11g Release 1 (11.1) to Oracle Database 10g Release 2 (10.2).
Export and Import Requirements for Upgrades
Export and Import Requirements for Downgrades
Export/Import Usage on Data Incompatible with a Previous Release
Upgrade the Database Using Export/Import
Use the Import New Database tool to import objects exported from the current database. If changes are made to the current database after the export, then ensure that these changes are propagated to the new database before making it available to users.
Importing a Full Database Using a Network Link
Run the following command, where import_user is the username for the import user, and db_link is the name of the database link owned by the export user:. Note: XML objects are not exported from the source database. https://metalink.oracle.com/) for more information about datapump imports using a database link.
Compatibility and Interoperability Issues in Oracle Database 11g Release 2 (11.2)
This appendix does not describe all the changed features or new features in the new release of Oracle Database 11g.
Initialization Parameters Deprecated in Oracle Database 11g Release 2 (11.2)
Initialization Parameters Obsolete in Oracle Database 11g Release 2 (11.2)
Static Data Dictionary Views Deprecated in Oracle Database 11g Release 2 (11.2)
Dynamic Performance Views Deprecated in Oracle Database 11g Release 2 (11.2)
Deprecated Features in Oracle Database 11g Release 2 (11.2)
LOG_ARCHIVE_DEST_n Parameters
Compatibility and Interoperability Issues in Oracle Database 11g Release 1 (11.1)
Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1)
Initialization Parameters Obsolete in Oracle Database 11g Release 1 (11.1)
Static Data Dictionary Views with Dropped Columns in Oracle Database 11g Release 1 (11.1)
Deprecated Features in Oracle Database 11g Release 1 (11.1)
Automatic Maintenance Tasks Management
New SYSASM Privilege and OSASM Group for ASM Administration
Members of the OSASM group can connect to AS SYSASM using OS authentication and have full access to ASM.
ASM Disk Group Compatibility
COMPUTE STATISTICS and ESTIMATE STATISTICS Clauses
Oracle Data Mining Models and the DMSYS Schema Objects
If COMPATIBLE is set to 10.1.0 or 10.2.0 while the database is upgraded to Oracle Database 11g Release 1 (11.1), then all new and existing Data Mining features and functions should work. If you have built new mining models that are only available in Oracle Database 11g Release 1 (11.1), and subsequently decide to.
Oracle Data Mining Scoring Engine
After upgrading (and removing the DMSYS schema after setting the COMPATIBLE initialization parameter to 11.0.0), importing models exported from Oracle Database 10g Release 1 (10.1) may present some complications due to their reference to the now nonexistent DMSYS schema. To handle this case, Oracle provides scripts to sufficiently (and minimally) mimic the DMSYS interface present in the Oracle Database 10g Release 1 (10.1) database to allow the import process to continue.
SQL Plan Management and Control of SQL Plan Baselines
Note that as of Oracle Database 11g Release 1 (11.1), the offline SYSAUX tablespace termination may have potential SQL performance. If a saved outline is available for the SQL statement, the SQL plan management feature is not used.
Binary XML Support for Oracle XML Database
If a saved statement for an SQL statement is active for the user session (for example, the saved outline category matches the user session category), then the statement is constructed using the saved statement. If a private statement is available for an SQL statement, then the statement is constructed using the private statement.
PL/SQL Native Compilation and Access Control for Network Utility Packages
However, if another user session uses the same SQL statement but without an active saved outline, the SQL plan management feature is used. For database users upgrading to Oracle Database 11g Release 1 (11.1), applications that depend on the PL/SQL network utility packages compile without problems.
PL/SQL Control Parameters
The default behavior for controlling access to network utility packages has been changed to deny network operations to all unprivileged users. This default behavior differs from and is not compatible with earlier versions of Oracle Database.
Change in WebDAV ACL Evaluation Rules in Oracle XML DB
The default behavior is defined only by the first
Summary Management and SQL Access Advisor
That is, the first entry determines the behavior for that principal, and additional ACEs for that principal have no effect. That is, if there are any ACLs that are
SQL Access Advisor Tasks
Standard Edition Starter Database
Core Dump Location
New Default Value for UNDO_MANAGEMENT
SHARED_POOL_SIZE Parameter
JOB_QUEUE_PROCESSES Parameter
Automatic Diagnostic Repository
Compatibility and Interoperability Issues in Oracle Database 10g Release 2 (10.2)
Initialization Parameters Deprecated in Oracle Database 10g Release 2 (10.2)
Initialization Parameters Obsolete in Oracle Database 10g Release 2 (10.2)
Static Data Dictionary Views with Dropped Columns in Oracle Database 10g Release 2 (10.2)
CONNECT Role
Time Zone Files
New Limit for FAILED_LOGIN_ATTEMPTS
Compatibility and Interoperability Issues in Oracle Database 10g Release 1 (10.1)
Initialization Parameters Deprecated in Oracle Database 10g Release 1 (10.1)
Initialization Parameters Obsolete in Oracle Database 10g Release 1 (10.1)
OPTIMIZER_MAX_PERMUTATIONS ORACLE_TRACE_COLLECTION_NAME ORACLE_TRACE_COLLECTION_PATH ORACLE_TRACE_COLLECTION_SIZE ORACLE_TRACE_ENABLE. ORACLE_TRACE_FACILITY_NAME ORACLE_TRACE_FACILITY_PATH PARTITION_VIEW_ENABLED PLSQL_NATIVE_C_COMPILER PLSQL_NATIVE_LINKER.
Static Data Dictionary Views Deprecated in Oracle Database 10g Release 1 (10.1)
Static Data Dictionary Views Obsolete in Oracle Database 10g Release 1 (10.1)
Dynamic Performance Views Deprecated in Oracle Database 10g Release 1 (10.1)
Dynamic Performance Views Obsolete in Oracle Database 10g Release 1 (10.1)
SQL Optimizer
In Oracle Database 10g Release 1 (10.1) and later versions, it is an initialization parameter and defaults to true. Starting with Oracle Database 10g Release 1 (10.1), VPD policies are attached to synonyms rather than base objects.
Invalid Synonyms After an Upgrade
This setting allows all operations (insert, delete, update, and select) on tables with unusable indexes or index partitions. As of Oracle Database 10g Release 1 (10.1), CLOB <-> NCLOB implicit conversion in SQL and PL/SQL is allowed.
Manageability
If the base object of a synonym does not exist, then the SQL compiler now tries to look for PUBLIC.base_object.
Transaction and Space
Recovery and Data Guard
RMAN
CREATE DATABASE
Oracle Real Application Clusters
Materialized Views
Change Data Capture
Change in the Default Archival Processing to Remote Archive Destinations
Limitations on NCHAR Data Types
PL/SQL Native Compilation
This is because releases after Oracle Database 10g Release 1 (10.1) use INTEGER arithmetic (about 9 significant digits) for some expressions, while Oracle9i Release 2 (9.2) used NUMBER arithmetic (about 38 significant digits). The solution to the error is to specify one of the numeric literals as decimal (256.0), as follows: .
Change in Behavior for SESSION_CACHED_CURSORS
Evaluation of numeric literals has been changed so that at least one of the constants in a numeric calculation using literals must be specified as a decimal to the 10th place. So if you are dealing with results of more than 9 significant digits, one of the literal numbers should be in decimal format to avoid numeric overflow errors.
New Default Value for DB_BLOCK_SIZE
OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_FEATURES_ENABLE
Change in Behavior for LOG_ARCHIVE_FORMAT
New Default Value for PGA_AGGREGATE_TARGET
Change in Behavior for SHARED_POOL_SIZE
Shared Server Parameters
Starting with Oracle Database 10g Release 1 (10.1), if DISPATCHERS is set so that the total number of dispatchers equals 0, then SHARED_SERVERS defaults to 0. In previous releases, if DISPATCHERS was set so that the number of dispatchers equals 0 , then SHARED_SERVERS defaults to 1.
Collecting Statistics for System Component Schemas
In addition, some schemas referenced in these scripts may not exist if some database components are not installed. The following topics are covered: . method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);.
Creating a Statistics Table
Index
Numerics
