A Changes for Earlier Releases of Oracle Database

A.1.1 Deprecation of SNMP Support in Oracle Net Listener

Oracle has deprecated SNMP support in Oracle Net Listener in release 11.2.0.3 of Oracle Database. Oracle recommends not using SNMP in new implementations.

A.1.2 Changes to PL/SQL Procedures

Some PL/SQL procedures have been moved from package DBMS_XDB to package DBMS_XDB_ADMIN in release 11.2.0.3 of Oracle Database.

The procedures moved to DBMS_XDB_ADMIN include:

• moveXDB_tablespace

• rebuildHierarchicalIndex

A.1.3 JOB_QUEUE_PROCESSES Parameter and Scheduling Jobs

Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run. The default value is 1000.

Oracle Database overrides the job queue setting to disable scheduler jobs during upgrade mode. Therefore, you can ignore this setting when upgrading Oracle Database. When the database is opened under UPGRADE mode, Oracle internally enforces that no jobs can be run (under dbms_job or scheduler). It does not matter what JOB_QUEUE_PROCESSES is set to in UPGRADE mode.

A.1.4 Deprecated XML DB Constructs

The following XML DB constructs have been deprecated in release 11.2.0.3:

• PL/SQL procedure DBMS_XDB_ADMIN.createRepositoryXMLIndex

• PL/SQL procedure DBMS_XDB_ADMIN.XMLIndexAddPath

• PL/SQL procedure DBMS_XDB_ADMIN.XMLIndexRemovePath

• PL/SQL procedure DBMS_XDB_ADMIN.dropRepositoryXMLIndex

• XML schema annotation (attribute) csx:encodingType

• XMLIndex index on CLOB portions of hybrid XMLType storage (this is CLOB data that is embedded within object-relational storage)

A.1.5 Cursor_sharing=similar Deprecated in Oracle Database

The cursor_sharing=similar parameter is deprecated in Oracle Database release 11.2.0.3. Use adaptive cursor sharing instead.

A.1.6 Planned Desupport of Change Data Capture

Oracle Change Data Capture is being replaced with Oracle GoldenGate. Therefore, Oracle strongly recommends that you use Oracle GoldenGate for new applications.

For Oracle Database 11g Release 2 (11.2), Change Data Capture continues to function as in earlier releases. If you are currently using Change Data Capture, then you can continue to do so for the foreseeable future. However, Change Data Capture is not being further enhanced.

A.1.7 Deprecation of the Data Mining Java API

Starting with release 11.2.0.3 of Oracle Database, the Data Mining Java API is deprecated. Oracle Data Mining now supports a new release of Oracle Data Miner. The earlier release, Oracle Data Miner Classic, is still available for download on Oracle Technology Network (OTN), but it is no longer under active development.

A.1.8 Initialization Parameters Deprecated in Oracle Database 11g Release 2 (11.2)

A deprecated parameter behaves the same way as a regular parameter, except that a warning message is displayed at instance startup if a deprecated parameter is specified in the parameter file. In addition, all deprecated parameters are logged to the alert log at instance startup.

To get a list of all initialization parameters that are specified as deprecated for the current database, issue the following SQL statement:

SQL> SELECT name FROM v$parameter
         WHERE isdeprecated = 'TRUE';

The following initialization parameters were deprecated in Oracle Database 11g Release 2 (11.2):

A.1.9 Initialization Parameters Desupported in Oracle Database 11g Release 2 (11.2)

The following initialization parameters were desupported in Oracle Database 11g Release 2 (11.2).

A.1.10 Static Data Dictionary Views Deprecated in Oracle Database 11g Release 2 (11.2)

These static data dictionary views were deprecated in Oracle Database 11g Release 2 (11.2):

A.1.11 Dynamic Performance Views Deprecated in Oracle Database 11g Release 2 (11.2)

A dynamic performance view was deprecated in Oracle Database 11g Release 2 (11.2).

A.1.12 Deprecated Features in Oracle Database 11g Release 2 (11.2)

Oracle Database features have been deprecated in Oracle Database 11g Release 2 (11.2). The deprecated features are supported in this release for backward compatibility. However, Oracle recommends that you migrate away from these deprecated features.

• Dictionary-managed Tablespaces

• MAX_JOB_SLAVE_PROCESSES

Dictionary-managed Tablespaces

Oracle recommends that you create locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces.

MAX_JOB_SLAVE_PROCESSES

MAX_JOB_SLAVE_PROCESSES has been deprecated. Use JOB_QUEUE_PROCESSES instead.

A.1.13 Changes to LOG_ARCHIVE_DEST_n Parameters

Starting with Oracle Database 11g Release 2 (11.2), the number of supported destinations in the LOG_ARCHIVE_DEST_n and the LOG_ARCHIVE_DEST_STATE_n parameters have been increased from 10 to 31. Destinations LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 do not support the SYNC, ARCH, LOCATION, MANDATORY, ALTERNATE, or DEPENDENCY attributes, and cannot be specified as the target of the ALTERNATE or DEPENDENCY attributes.

LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 can only be used when the COMPATIBLE initialization parameter is set to 11.2.0 or higher.

A.2.1 Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1)

The following initialization parameters were deprecated in Oracle Database 11g Release 1 (11.1).

To get a list of all deprecated initialization parameters, issue the following SQL statement:

SQL> SELECT name FROM v$parameter
         WHERE isdeprecated = 'TRUE';

A deprecated parameter behaves the same way as a regular parameter, except that a warning message is displayed at instance startup if a deprecated parameter is specified in the parameter file. In addition, all deprecated parameters are logged to the alert log at instance startup.

A.2.2 Initialization Parameters Desupported in Oracle Database 11g Release 1 (11.1)

These initialization parameters were desupported in Oracle Database 11g Release 1 (11.1).

A.2.3 Static Data Dictionary Views with Dropped Columns in Oracle Database 11g Release 1 (11.1)

These static data dictionary view columns were dropped in Oracle Database 11g Release 1 (11.1):

A.2.4 Deprecated Features in Oracle Database 11g Release 1 (11.1)

This section lists Oracle Database features deprecated in Oracle Database 11g Release 1 (11.1). They are supported in this release for backward compatibility. However, Oracle recommends that you migrate away from these deprecated features.

• Oracle Ultra Search

  Oracle recommends that you use Oracle Secure Enterprise Search.

• Java Development Kit (JDK) 1.4

  Oracle recommends that you use JDK 5.0; however, JDK 1.5 is also fully supported.

• CTXXPATH index

  Oracle recommends that you use XMLIndex instead.

A.2.5 Automatic Maintenance Tasks Management

Automatic Maintenance Tasks Management, a new database component in Oracle Database 11g Release 1 (11.1), schedules all automatic maintenance tasks in an expanded set of maintenance windows. Automatic Maintenance Tasks Management enables you to exercise finer control over maintenance task scheduling for tasks such as optimizer statistics gathering, Segment Advisor, and Automatic SQL Tuning Advisor.

Automatic Maintenance Tasks Management uses all existing maintenance windows (for example, windows that are current members of the MAINTENANCE_WINDOW_GROUP. Existing resource plans associated with the maintenance windows are used. However, AUTOTASK_CONSUMER_GROUP is replaced in the resource plans by the AutoTask Resource Subplan.

If you disable either Optimizer Statistics Gathering or Segment Advisor jobs in 10g, then the corresponding Automatic Maintenance Tasks Management feature is disabled after upgrading to Oracle Database 11g Release 1 (11.1).

The following list shows the default settings for maintenance tasks:

• Online backup is disabled

• Optimizer Statistics Gathering is on

• Segment Advisor is on

• Automatic SQL Tuning is on

All other Automatic Maintenance Tasks Management clients are enabled by default.

Although Automatic Maintenance Tasks Management is automatically enabled when upgrading to Oracle Database 11g Release 1 (11.1), AutoTask online backup is not enabled automatically. You must configure online backup manually, if desired, after upgrading the database. If you perform a database downgrade, then Automatic Maintenance Tasks Management reverts to the default behavior for that release.

A.2.6 New SYSASM Privilege and OSASM Group for Oracle ASM Administration

Oracle Database 11g Release 1 (11.1) introduces a new SYSASM privilege that is specifically intended for performing Oracle ASM administration tasks. Using the SYSASM privilege instead of the SYSDBA privilege provides a clearer division of responsibility between ASM administration and database administration.

Warning messages appear in the Oracle ASM alert.log if SYSDBA performs disk group maintenance (CREATE DISKGROUP, MOUNT/DISMOUNT, ADD/DROP DISK, ONLINE/OFFLINE DISK, DROP DISKGROUP). These tasks are deprecated for SYSDBA; they should be performed by SYSASM.

OSASM is a new operating system group that is used exclusively for Oracle ASM. Members of the OSASM group can connect AS SYSASM using operating system authentication and have full access to Oracle ASM.

A.2.7 ASM Disk Group Compatibility

Starting with Oracle Database 11g Release 1 (11.1), you can advance the Oracle Database and the ASM disk group compatibility settings across software versions. Using the new compatibility attributes, compatible.rdbms and compatible.asm, you can specify the minimum software version required to use the disk group for the database and the disk group for ASM, respectively.

This feature enables heterogeneous environments with disk groups from Oracle Database 10g Release 1 (10.1), Oracle Database 10g Release 2 (10.2), and Oracle Database 11g Release 1 (11.1). By default, both attributes are set to 10.1. You must advance these attributes to take advantage of the new features.

A.2.8 COMPUTE STATISTICS and ESTIMATE STATISTICS Clauses

In earlier releases, the ANALYZE...COMPUTE STATISTICS and ANALYZE...ESTIMATE STATISTICS clauses could be used to start or stop the collection of statistics on an index. These clauses have been desupported. Oracle Database 11g Release 1 (11.1) automatically collects statistics during index creation and rebuild. These clauses are no longer supported.

A.2.9 Oracle Data Mining Models and the DMSYS Schema Objects

During the upgrade to Oracle Database 11g Release 1 (11.1), DMSYS schema objects along with user models residing in user schemas are upgraded from any previous release without major constraints. Upon completion of the upgrade, the mining metadata is migrated into the SYS schema while the user models continue functioning with the new metadata. Oracle recommends that you drop the DMSYS schema after setting the COMPATIBLE initialization parameter to 11.0.0. In addition, the DBA must grant the new CREATE MINING MODEL privilege so that existing users can continue to build mining models.

Data mining models residing in a user schema are automatically upgraded as part of the model upgrade, which is an integral part of the Oracle Database upgrade process. Data mining model Export and Import utilities can also be used for upgrading data mining models from one release to another.

During the database downgrade process, the data mining component is downgraded to a previous release. The downgrade process reloads DMSYS objects such as packages, types, and table objects plus downgrading model objects residing in user schemas (if any). Objects that were created as a part of the database upgrade are removed from the SYS schema during the downgrade procedure. The process is transparent and does not require any user intervention.

After upgrading (and dropping the DMSYS schema after setting the COMPATIBLE initialization parameter to 11.0.0), importing models that were exported from Oracle Database 10g Release 1 (10.1) might have 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 so that the Import process can proceed. This is not a common occurrence because models become stale over time and users typically want to rebuild their models rather than import older ones.

Data Mining is not protected by the COMPATIBLE initialization parameter. If COMPATIBLE is set at 10.1.0 or 10.2.0 while the database has been 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 downgrade the database to Oracle Database 10g Release 2 (10.2), you are required to drop the new mining models before downgrading.

A.2.10 Oracle Data Mining Scoring Engine

Starting with Oracle Database 11g Release 1 (11.1), the Oracle Data Mining Scoring Engine can no longer be installed.

A.2.11 SQL Plan Management and Control of SQL Plan Baselines

The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement. Using the execution plan history, the optimizer can detect a new plan representing a plan change for a SQL statement. When the optimizer detects a new plan, it stores the new plan and marks it for performance evaluation and uses the old (currently known good) plan. The optimizer uses the new plan only after its performance is verified to be better than that of the old plan. A SQL plan baseline consists of a set of known good plans for a SQL statement.

Migration of SQL Profiles

SQL Profiles are SQL management objects that were introduced in Oracle Database 10g Release 1 (10.1). These objects resided in a section of the dictionary that was defined in SYSTEM tablespace. The dictionary tables storing the SQL profiles are restructured to accommodate the storage of SQL plan baselines, which are also SQL management objects. Further, these dictionary tables are now defined in the SYSAUX tablespace.

When you upgrade from Oracle Database 10g Release 1 (10.1) to Oracle Database 11g Release 1 (11.1), the database upgrade script moves existing SQL profiles from the SYSTEM tablespace to the SYSAUX tablespace. Thus, if an Oracle Database 11g Release 1 (11.1) database instance is up but the SYSAUX tablespace is offline, then the optimizer is not able to access SQL Management objects, which can affect the performance on SQL workload. In contrast, in Oracle Database 10g Release 1 (10.1), because SQL profiles were stored in SYSTEM tablespace, the unavailability of SQL profiles did not exist. Taking the SYSAUX tablespace offline can have potential SQL performance consequences.

Backward Compatibility

In Oracle Database 11g Release 1 (11.1):

• If a stored outline for a SQL statement is active for the user session (for example, the stored outline category matches with the user session category), then the statement is compiled using the stored outline.

• If a private outline is available for a SQL statement, then the statement is compiled using the private outline.

If a stored outline is available for a SQL statement, then the SQL Plan Management feature is not used. However, if another user session uses the same SQL statement but without an active stored outline, then the SQL plan management feature is used.

A.2.12 Binary XML Support for Oracle XML Database

The binary XML storage option that is new in Oracle Database 11g Release 1 (11.1) is available when the COMPATIBLE initialization parameter is set to 11.0.0 or higher. When you create a table or column with this storage option, the minimum compatibility requirement is checked. This also applies when storing binary XML documents directly in the XML DB repository.

A.2.13 PL/SQL Native Compilation and Access Control for Network Utility Packages

Oracle introduced compatibility and interoperability changes in PL/SQL for Oracle Database 11g Release 1 (11.1).

• PL/SQL Native Compilation

• Access Control for Network Utility Packages

A.2.14 PL/SQL Control Parameters

The behavior of some Oracle parameters that control the behavior of PL/SQL have changed as of Oracle Database 11g Release 1 (11.1).

• If PL/SQL debug code generation mode is selected by any parameter setup, then native code generation is turned off.

• Debug code generation is on if the PLSQL_OPTIMIZE_LEVEL <= 1.

• PLSQL_DEBUG is deprecated.

  You should use PLSQL_OPTIMIZE_LEVEL instead. A deprecation warning is issued if PLSQL_DEBUG is used.

• If PLSQL_OPTIMIZE_LEVEL <= 1, then native code generation is turned off.

• PLSQL_COMPILER_FLAGS is desupported. It has no effect any longer and draws an error message that an illegal option is being set.

• PLSQL_V2_COMPATIBILITY is deprecated.

A.2.15 Change in WebDAV ACL Evaluation Rules in Oracle XML DB

Oracle XML DB uses a security mechanism that is based on access-control lists (ACLs) to restrict access to any Oracle XML DB resource. An ACL is a list of access-control entries (ACEs) that determine which users, roles, and groups have access to a given resource.

There have been changes to the treatment of WebDAV ACL entries. Before Oracle Database 11g Release 1 (11.1), a <deny> entry always trumped any <allow> entry in a given ACL. Starting with Oracle Database 11g Release 1 (11.1), ACE order is irrelevant. The default behavior is determined only by the first <allow> or <deny> entry that is encountered. That is, the first entry determines the behavior for that principal and additional ACEs for that principal have no effect.

This change in the default behavior is different from, and is incompatible with, previous versions of Oracle Database. When upgrading to Oracle Database 11g Release 1 (11.1), you can get the same behavior as in previous releases by manually reordering the ACLs (if necessary). That is, if there are any ACLs that have <allow> followed somewhere by <deny>, then you should (manually) reorder the ACLs so that the <deny> entry occurs first.

A.2.16 Summary Management and SQL Access Advisor

The DBMS_OLAP package is deprecated and has been replaced by the SQLAccess Advisor.

A.2.17 SQL Access Advisor Tasks

Due to internal structural changes to the SQL Access Advisor repository, a database upgrade resets all existing SQL Access Advisor tasks to their initial state. This effectively deletes all recommendation information for tasks that have successfully completed before the upgrade.

After upgrade, the recommendation information can be restored by re-executing the existing SQL Access Advisor tasks.

A.2.18 Standard Edition Starter Database

When the Standard Edition (SE) starter database is upgraded, the following components cannot be upgraded by the SE server because they require options that are not installed in the Standard Edition:

• OLAP Catalog

• OLAP Analytic Workspace

• Oracle OLAP API

After the upgrade, these components have a STATUS value of OPTION OFF in the DBA_REGISTRY view, and there are some invalid objects in the associated component schemas. The Database Upgrade Assistant (DBUA) shows unsuccessful upgrades for these components.

A.2.19 Core Dump Location

On UNIX systems, when an application program crashes due to an unhandled signal, such as a segmentation fault, a core dump file is usually generated. The system default file name for this file is core, and it is located in the directory in which the application is currently running.

Starting with Oracle Database 11g Release 1 (11.1), applications using the Oracle Call Interface (OCI) can create a subdirectory named core_process_id, where process_id is the UNIX ID of the process that crashed. The core file is then placed in that subdirectory instead of the location where the application is running.

In sqlnet.ora, setting DIAG_SIGHANDLER_ENABLED = TRUE also puts the generated core file in the directory named core_process_id.

A.2.20 New Default Value for UNDO_MANAGEMENT

Starting with Oracle Database 11g Release 1 (11.1), the default value of the UNDO_MANAGEMENT parameter is AUTO so that automatic undo management is enabled by default. You must set the parameter to MANUAL to turn off automatic undo management, if required.

The UNDO_MANAGEMENT and ROLLBACK_SEGMENTS initialization parameters have changed from basic initialization parameters to non-basic initialization parameters. Most databases must have only basic parameters set to run properly and efficiently.

A.2.21 LOG_ARCHIVE_DEST_n Parameters

You can use the LOG_ARCHIVE_DEST_n parameter to specify a local archiving destination on a database instance running Oracle Standard Edition. Previously, this parameter could only be specified on a database instance running Oracle Enterprise Edition.

A.2.22 SHARED_POOL_SIZE Parameter

Migration utilities for this release recommend new values for SHARED_POOL_SIZE based on the value of internal SGA overheads in the pre-upgrade environment, which you can determine by running the following query before upgrading to Oracle Database 11g Release 1 (11.1):

SQL> SELECT SUM(BYTES) FROM v$sgastat WHERE pool = 'shared pool';

In Oracle Database 11g Release 1 (11.1), the exact value of internal SGA overhead, or Startup overhead in Shared Pool, is listed in the new v$sgainfo view.

In manual SGA mode, values of SHARED_POOL_SIZE that are too small to accommodate the internal SGA overhead result in an ORA-00371 error during startup. This generated error message includes a suggested value for the SHARED_POOL_SIZE parameter. If you are using automatic shared memory management, the size of the shared pool is tuned automatically, and the ORA-00371 error is never generated.

The amount of shared pool memory allocated by Oracle Database releases before Oracle Database 10g Release 1 (10.1) was equal to the sum of the value of the SHARED_POOL_SIZE initialization parameter and the internal SGA overhead computed during instance startup. This overhead was based on the values of several other initialization parameters.

For example, if the SHARED_POOL_SIZE parameter is 64 megabytes and the internal SGA overhead is 12 megabytes, then the real size of shared pool in the SGA would be 76 megabytes, although the value of the SHARED_POOL_SIZE parameter would still be displayed as megabytes.

Starting with Oracle Database 10g Release 1 (10.1), the size of internal SGA overhead is included in the value of the SHARED_POOL_SIZE parameter. The shared pool memory allocated at startup is exactly the value of SHARED_POOL_SIZE. Therefore, this parameter must be set such that it includes both the internal SGA overhead and the desired effective value of the shared pool size.

Assuming that the internal SGA overhead remains unchanged, the effective available value of shared pool after startup would be 12 megabytes less than the value of the SHARED_POOL_SIZE parameter, or 52 megabytes. To maintain 64 megabytes for the effective value of shared pool memory, set the parameter to 76 megabytes.

A.2.23 JOB_QUEUE_PROCESSES Parameter New Default Setting

Beginning with Oracle Database 11g Release 1 (11.1), the JOB_QUEUE_PROCESSES parameter is changed from a basic to a non-basic initialization parameter. Most databases must only have basic parameters set to run properly and efficiently. The default value is also changed from 0 to 1000.

Oracle Database overrides the job queue setting to disable scheduler jobs during upgrade mode. Therefore, you can ignore this setting when upgrading Oracle Database. When the database is opened under UPGRADE mode, Oracle internally enforces that no jobs can be run (under dbms_job or scheduler). It does not matter what JOB_QUEUE_PROCESSES is set to in UPGRADE mode.

A.2.24 Automatic Diagnostic Repository

The locations of alert logs and trace files are no longer set by the initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST. They are now kept in the Automatic Diagnostic Repository (ADR), whose location is set the by the initialization parameter DIAGNOSTIC_DEST.

A.3.1 Initialization Parameters Deprecated in Oracle Database 10g Release 2 (10.2)

The following initialization parameters were deprecated in Oracle Database 10g Release 2 (10.2). To get a list of all deprecated initialization parameters, issue the following SQL statement:

SQL> SELECT name FROM v$parameter
         WHERE isdeprecated = 'TRUE';

A deprecated parameter behaves the same way as a regular parameter, except that a warning message is displayed at instance startup if a deprecated parameter is specified in the parameter file. In addition, all deprecated parameters are logged to the alert log at instance startup:

A.3.2 Initialization Parameters Desupported in Oracle Database 10g Release 2 (10.2)

The following initialization parameters were desupported in Oracle Database 10g Release 2 (10.2).

A.3.3 Static Data Dictionary Views with Dropped Columns in Oracle Database 10g Release 2 (10.2)

The following static data dictionary view columns were dropped in Oracle Database 10g Release 2 (10.2):

A.3.4 SQL

The behavior of date formats has changed when used with XML functions. The XML Schema standard specifies that dates and timestamps in XML data be in standard formats. Before Oracle Database 10g Release 2 (10.2), dates and timestamps in XML data did not follow this standard; rather, the format of dates and timestamps in generated XML was determined by the database format.

As of Oracle Database 10g Release 2 (10.2), the XML generation functions in Oracle XML DB produce dates and timestamps according to the XML schema standard.

A.3.5 CONNECT Role

After upgrading from a release earlier than Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade.

A.3.6 Time Zone Files

The time zone files that are supplied with Oracle Database 10g Release 2 (10.2) have been updated from version 4 to version 8 to reflect changes in transition rules for some time zone regions. The changes might affect existing data of TIMESTAMP WITH TIME ZONE data type.

A.3.7 New Limit for FAILED_LOGIN_ATTEMPTS

As of Oracle Database 10g Release 2 (10.2), the limit for FAILED_LOGIN_ATTEMPTS for the DEFAULT profile is 10. Before Oracle Database 10g Release 2 (10.2), the default was UNLIMITED.