Oracle Database Behavior Changes

Just a quick post of some(not all, but most important for me) interesting facts about Oracle Database Behavior Changes in releases 11.2, 11.1, 10.2 & 10.1 for those who are quite long time with Oracle, but not always has time to read Documentation:

Compatibility and Interoperability Issues in Oracle Database 11g Release 2 (11.2):

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

The following dynamic performance views were deprecated in Oracle Database 11g Release 2 (11.2).

V$FLASH_RECOVERY_AREA_USAGE (replaced by V$RECOVERY_AREA_USAGE)

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.

Non-Uniform Memory Access Optimizations and Support Disabled in 11.2

With Oracle Database 11g Release 2 (11.2), non-uniform memory access support is disabled by default. This restriction applies to all platforms and operating systems (reference Bug 8450932).

Non-uniform memory access optimizations and support in the Oracle Database are only available for specific combinations of Oracle version, operating systems, and platforms. Work with Oracle Support Services and your hardware vendor to enable non-uniform memory access support.

Support for Raw Devices

Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) do not support raw devices (or block devices on Linux). However, command-line utilities such as SQL*Plus and CRSCTL do support raw or block devices.

Oracle Clusterware and Oracle ASM Installed Into Oracle Grid Infrastructure for a Cluster Home

Oracle Clusterware and Oracle Automatic Storage Management (Oracle ASM) are installed into a single Oracle home called the Grid home. This installation is referred to as the Oracle Grid Infrastructure for a cluster installation.

When upgrading, you must upgrade both Oracle Clusterware and Oracle ASM at the same time.

Deprecated Parameters in the Oracle ASM Instance

The following initialization parameters are deprecated in the Oracle ASM instance:

  • CLUSTER_DATABASEparameterIf the INSTANCE_TYPE is Oracle ASM and the Oracle RAC option is turned on, then you do not have to specify the CLUSTER_DATABASE parameter. In this case, the CLUSTER_DATABASE parameter defaults to TRUE.
  • COMPATIBLEparameterDo not set the COMPATIBLE parameter in an Oracle ASM instance. To advance the disk group compatibility, change the COMPATIBLE.[RDBMS|ASM|ADVM] attributes of the disk group.

Compatibility and Interoperability Issues in Oracle Database 11g Release 1 (11.1)

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

BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
CURSOR_SPACE_FOR_TIME
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
RESOURCE_MANAGER_CPU_ALLOCATION
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)

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 made obsolete. Oracle Database 11g Release 1 (11.1) automatically collects statistics during index creation and rebuild. These clauses are no longer supported and using them causes errors.

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 is able to 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 some of the 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. Note that starting with Oracle Database 11g Release 1 (11.1), 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.

PL/SQL Access Control for Network Utility Packages

Access Control for Network Utility Packages

The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

For database users upgrading to Oracle Database 11g Release 1 (11.1), applications that depend on the PL/SQL network utility packages compile without any issues. However, at runtime the applications might receive exceptions when attempting to perform privileged network operations. Although you can restore the compatibility by using a wildcard to grant those privileges to perform any network operations to PUBLIC, Oracle strongly advises that database administrators carefully review each situation on an individual basis and grant privileges only as needed.

Note:

Oracle XML DB is required to properly maintain the access control lists. If Oracle XML DB is not already installed on the system, then you must install it during the upgrade procedure.

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 should be required to have only basic parameters set to run properly and efficiently.

LOG_ARCHIVE_DEST_n Parameters

Starting with Oracle Database 11g Release 1 (11.1), the LOG_ARCHIVE_DEST_n parameter can be used 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.

JOB_QUEUE_PROCESSES Parameter

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 only need to have basic parameters set in order to run properly and efficiently. The default value is also changed from 0 to 1000.

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.

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.

Compatibility and Interoperability Issues in Oracle Database 10g Release 2 (10.2)

CONNECT Role

After upgrading from a release prior to 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. For further information about this, see “Deprecated CONNECT Role”.

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. Prior to Oracle Database 10g Release 2 (10.2), the default was UNLIMITED.

Compatibility and Interoperability Issues in Oracle Database 10g Release 1 (10.1)

Initialization Parameters Deprecated in Oracle Database 10g Release 1 (10.1)

BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE
LOG_ARCHIVE_START
MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)
SQL_VERSION

Rule-Based Optimizer Desupported

Starting with Oracle Database 10g Release 1 (10.1), the cost-based optimizer (CBO) is now enabled by default. The rule-based optimizer is no longer supported in Oracle Database 10g Release 1 (10.1). As a result, rule and choose are no longer supported as OPTIMIZER_MODE initialization parameter values and a warning is displayed in the alert log if OPTIMIZER_MODE is set to either of these values.

Optimizer Statistics

Collection of optimizer statistics is now automatically performed by default for all schemas (including SYS), for pre-existing databases upgraded from a release prior to Oracle Database 10g Release 1 (10.1), and for newly created databases. Gathering optimizer statistics on stale objects is scheduled by default to occur daily during the maintenance window.

COMPUTE STATISTICS Clause of CREATE INDEX

In earlier releases, the COMPUTE STATISTICS clause of CREATE INDEX could be used to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database 10g Release 1 (10.1) and later releases automatically collect statistics during index creation and rebuild. This clause is supported for backward compatibility and does not cause errors.

SKIP_UNUSABLE_INDEXES

In earlier releases, SKIP_UNUSABLE_INDEXES was a session parameter only. In Oracle Database 10g Release 1 (10.1) and later releases, it is an initialization parameter and defaults to true. The true setting disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.

Manageability

Database performance statistics are now automatically collected by the Automatic Workload Repository (AWR) database component for databases upgraded from a release prior to Oracle Database 10g Release 1 (10.1) and for newly created databases. This data is stored in the SYSAUX tablespace, and is used by the database for automatic generation of performance recommendations.

If you currently use Statspack for performance data gathering, then refer to the Statspack README (spdoc.txt, located in the ORACLE_HOME/rdbms/admin directory) for directions on using Statspack in Oracle Database 10g Release 1 (10.1) and later releases to avoid conflict with the AWR.

Transaction and Space

Starting with Oracle Database 10g Release 1 (10.1), dropped objects are now moved to the recycle bin where the space is only reused when it is needed. This allows an object to be undropped using the FLASHBACK DROP feature.

Starting with Oracle Database 10g Release 1 (10.1), automatic tuning of undo retention is enabled by default. The UNDO_SUPPRESS_ERRORS initialization parameter has been deprecated. Errors generated when executing rollback segment operations while in automatic undo management mode are always suppressed.Starting with Oracle Database 10g Release 1 (10.1), the default AUTOEXTEND NEXT size is larger for Oracle managed files (OMF).

Recovery and Data Guard

Starting with Oracle Database 10g Release 1 (10.1), the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.

Starting with Oracle Database 10g Release 1 (10.1), the LOG_PARALLELISM initialization parameter has been deprecated. Log file parallelism is now automatically enabled.

Starting with Oracle Database 10g Release 1 (10.1), the default value for the RECOVERY_PARALLELISM initialization parameter now defaults to allow parallel recovery.

Starting with Oracle Database 10g Release 1 (10.1), the default value for the parallel clause in the ALTER DATABASE RECOVER DATABASE statement has changed to PARALLEL.

Starting with Oracle Database 10g Release 1 (10.1), the default buffer size for the ASYNC attribute of the LOG_ARCHIVE_DEST_n initialization parameter has increased from 2,048 blocks to 61,440 blocks.

Oracle Data Guard BrokerStarting with Oracle Database 10g Release 1 (10.1), the default behavior of the STARTUP SQL*Plus command and the ALTER DATABASE MOUNT and ALTER DATABASE OPEN SQL statements have changed for physical standby databases. The commands now automatically detect that the database is a physical standby and thus the STANDBY DATABASE and READ ONLY options are made default.

RMAN

Starting with Oracle Database 10g Release 1 (10.1), RMAN now creates an empty file when restoring a file from backup and no backup of the file exists. RMAN backup of archived logs now automatically backs up logs that were created before the last resetlogs. Such logs were previously ignored.

Starting with Oracle Database 10g Release 1 (10.1), RMAN now continues to run the remaining portions of a backup or restore job when it encounters an error. RMAN now tries to restore from an alternate backup if it finds the targeted backup is corrupt.

Change in the Default Archival Processing to Remote Archive Destinations

Starting with Oracle Database 10g Release 1 (10.1), the default archival processing to remote destinations has changed so that archiver processes on the primary database completely and successfully archive the local online redo log files before transmitting the redo data to remote standby destinations. This default behavior is equivalent to setting the LOG_ARCHIVE_LOCAL_FIRST initialization parameter to true, which is also new in Oracle Database 10g Release 1 (10.1) and later releases. Note that this new default archival processing is relevant only when log transport services are defined to use archiver processes (ARCn), not the log writer process (LGWR), when the archiver processes are writing to remote destinations, and when the remote standby destination is not a mandatory destination.

Prior to Oracle Database 10g Release 1 (10.1), the default behavior was to transmit redo data to the standby destination at the same time the online redo log file was being archived to the local online redo log files. You can achieve this behavior by setting the LOG_ARCHIVE_LOCAL_FIRST initialization parameter to false. This archival processing is also relevant only when log transport services are defined to use archiver processes (ARCn), not the log writer process (LGWR), when the archiver processes are writing to remote destinations, and when the remote standby destination is not a mandatory destination.

The benefit of the new default behavior is that local archiving, and hence, processing on the primary database, are not affected by archival to non-mandatory, remote destinations. Because local archiving is now disassociated with remote archiving, sites that might have policies to delete archived redo log files on the primary database immediately after backing them up must make sure that the standby destinations have received the corresponding redo data before deleting the archived redo log files on the primary database. You can query the V$ARCHIVED_LOG view to verify that the redo data has been received on standby destinations.

Note:

Any value specified for the LOG_ARCHIVE_LOCAL_FIRST initialization parameter is ignored for mandatory destinations (configured with the MANDATORY attribute of the LOG_ARCHIVE_DEST_n initialization parameters).

Change in Behavior for SESSION_CACHED_CURSORS

Starting with Oracle Database 10g Release 1 (10.1), the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter. In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter.

New Default Value for DB_BLOCK_SIZE

Starting with Oracle Database 10g Release 1 (10.1), the default value of DB_BLOCK_SIZE is operating system specific, but is typically 8 KB (8192 bytes). In previous Oracle Database releases, the default value was 2 KB (2048 bytes). If DB_BLOCK_SIZE is not specified in the parameter file when upgrading from Oracle9i Release 2 (9.2), then you receive an error when attempting to start up your database. Add the following to your parameter file:

DB_BLOCK_SIZE = 2048

If DB_BLOCK_SIZE is specified in the parameter file, then Oracle Database uses this value instead of the default value of 8 KB.

Change in Behavior for LOG_ARCHIVE_FORMAT

Starting with Oracle Database 10g Release 1 (10.1), if the COMPATIBLE initialization parameter is set to 10.0.0 or higher, then archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements.

New Default Value for PGA_AGGREGATE_TARGET

Starting with Oracle Database 10g Release 1 (10.1), Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET defaults to 20% of the size of the SGA, unless explicitly set. Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s