5 Compatibility and Interoperability

Downgrading and Compatibility

Before upgrading to the new Oracle Database 10g release, the COMPATIBLE initialization parameter must be set to 9.2.0, which is the lowest possible setting for Oracle Database 10g release 10.1. Only a subset of Oracle Database 10g features are available while the COMPATIBLE initialization parameter is set to this value.

After upgrading to the new Oracle Database 10g release, you can set the COMPATIBLE initialization parameter to match the release number of the new release. Doing so enables you to use all of the features of the new release, but prevents you from downgrading back to your previous release. If you want to downgrade, then you must leave the COMPATIBLE initialization parameter set to 9.2.0 after the upgrade.

How the COMPATIBLE Initialization Parameter Operates

The COMPATIBLE initialization parameter operates in the following way:

• It controls the behavior of your database. For example, if you run a release 10.1 database with the COMPATIBLE initialization parameter set to 9.2.0, then the release 10.1 database generates release 9.2 compatible database structures on disk. Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new features that make the database incompatible with the COMPATIBLE initialization parameter, then an error is displayed. However, any new features that do not make incompatible changes on disk are enabled.

• It makes sure that the database is compatible with its setting. If the database becomes incompatible with its setting, then the database does not start and terminates with an error. If this happens, then you must set the COMPATIBLE initialization parameter to an appropriate value for the database.

Compatibility Level

The compatibility level of your database corresponds to the value of the COMPATIBLE initialization parameter. For example, if you set the COMPATIBLE initialization parameter to 10.0.0, then the database runs at 10.0.0 compatibility level.

Checking the Current Value of the COMPATIBLE Initialization Parameter

To check the current value of the COMPATIBLE initialization parameter, issue the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter
         WHERE name = 'compatible';

When to Set the COMPATIBLE Initialization Parameter

You should set the COMPATIBLE initialization parameter at a specific point during the upgrade or downgrade process. Follow the procedure in the appropriate chapter and set the COMPATIBLE initialization parameter only when you are instructed to do so.

Raising the COMPATIBLE Initialization Parameter

Complete the following steps to set the COMPATIBLE initialization parameter to a higher value:

1. Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).

   Raising the COMPATIBLE initialization parameter may cause your database to become incompatible with earlier releases of the Oracle Database, and a backup ensures that you can return to the earlier release if necessary.

   
   

   See Also: Oracle Database Backup and Recovery Basics for more information about performing a backup

2. If you are using a server parameter file, then complete the following steps:

   1. Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.

      For example, to set the COMPATIBLE initialization parameter to 10.0.0, issue the following statement:

      SQL> ALTER SYSTEM SET COMPATIBLE = '10.0.0' SCOPE=SPFILE;
      
      

   2. Shut down and restart the instance.

3. If you are using an initialization parameter file, then complete the following steps:

   1. Shut down the instance if it is running:

      SQL> SHUTDOWN IMMEDIATE
      
      

   2. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.

      For example, to set the COMPATIBLE initialization parameter to 10.0.0, enter the following in the initialization parameter file:

      COMPATIBLE = 10.0.0
      
      

   3. Start the instance using STARTUP.

Rule-Based Optimizer Desupported

Starting with Oracle Database 10g release 10.1, the cost-based optimizer (CBO) is now enabled by default. The rule-based optimizer is no longer supported in release 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 to Oracle Database 10g, and for newly created Oracle Database 10g 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 now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

SKIP_UNUSABLE_INDEXES

In earlier releases, SKIP_UNUSABLE_INDEXES was a session parameter only. In Oracle Database 10g release 10.1 and later, it is now 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.

Change in Default Parameter File Selection

When the STARTUP command is issued without the PFILE option, the Oracle Database attempts to start up the instance using a default parameter file. Starting with Oracle9i release 9.0.1, the search criteria for selecting the default parameter file has changed to facilitate the use of a server parameter file.

In previous releases of the Oracle Database, the STARTUP command looked for an initialization parameter file with the name ORACLE_HOME/dbs/initSID.ora, where SID is the instance name.

In release 9.0.1 and later, the process of selecting a default parameter file is as follows:

• The STARTUP command first looks for a server parameter file with the name ORACLE_HOME/dbs/spfileSID.ora, where SID is the instance name.

• The STARTUP command next looks for a server parameter file with the name ORACLE_HOME/dbs/spfile.ora.

• If the STARTUP command cannot find a server parameter file, it defaults to the behavior of the STARTUP command in previous releases, and looks for an initialization parameter file with the name ORACLE_HOME/dbs/initSID.ora.

  
  

  See Also: Oracle Database Administrator's Guide for more information about server parameter files

CREATE TABLESPACE: New Behavior

In Oracle8i, the default type of tablespace that is created is dictionary managed if the EXTENT MANAGEMENT clause is not specified in the CREATE TABLESPACE statement.

Starting with Oracle9i release 9.0.1, the default for the EXTENT MANAGEMENT clause is locally managed. The default storage clause is parsed to determine whether to use AUTOALLOCATE or UNIFORM allocation policy for this tablespace.

In addition, there was another change made to disallow assigning permanent locally managed tablespaces as a user's temporary tablespace. In Oracle8i, an error would be signalled only when a temporary segment had to be created in the tablespace.

Default Temporary Tablespaces

Oracle strongly recommends using a default temporary tablespace for the database. The default temporary tablespace should be created using the CREATE TEMPORARY TABLESPACE statement.

Undo Tablespaces

Oracle Database instances can run in one of two undo space management modes:

• Automatic undo management mode

• Manual undo management mode

All instances of the same database must run in the same undo space management mode.

The instance is started in manual undo management mode if the UNDO_MANAGEMENT initialization parameter is not specified.

In the manual undo management mode, CREATE, ALTER, and DROP operations on undo tablespaces are allowed. Rollback segments can coexist with undo tablespaces. That is, rollback segments can exist while running in automatic undo management mode and undo tablespaces can exist while running in manual undo management mode. Undo tablespaces cannot be brought online unless the instance is running in automatic undo management mode.

In automatic undo management mode, DROP ROLLBACK SEGMENT operations are allowed. Rollback segments cannot be brought online.

Datetime and Interval Datatypes

When a database is upgraded to Oracle9i release 9.0.1 or later, the database time zone is set to the time zone of the environment variable ORA_SDTZ. If ORA_SDTZ is not set, the database time zone is set to the time zone of the operating system clock. If the time zone of the operating system clock is not set or is not valid, the database time zone defaults to UTC.

old Oracle Database DATE data with time portion can be migrated to either TIMESTAMP to support fractional seconds or TIMESTAMP WITH LOCAL TIME ZONE to support time zone adjustments in addition to fractional seconds without having legacy data rewritten. An ALTER TABLE statement must be explicitly issued to modify a DATE column to a TIMESTAMP column or a TIMESTAMP WITH LOCAL TIME ZONE column.

Database Character Sets

In Oracle8i and earlier releases, the SQL NCHAR datatypes (NCHAR, NVARCHAR2, and NCLOB) will be limited to the Unicode character set encoding (UTF8 and AL16UTF16) only. Any other character sets that were available under the NCHAR data type, including Asian character sets (such as JA16SJISFIXED), will no longer be supported.

Before migrating your SQL NCHAR data to the new Unicode NCHAR, Oracle recommends that you analyze your SQL NCHAR data, using the Character Set Scanner for the identification of possible invalid character set conversion or data truncation.

When you upgrade to Oracle Database 10g, the value of the National Character Set of the upgraded database is set based on the value of the National Character Set of the database being upgraded.

If the old National Character Set is UTF8, then the new National Character Set will be UTF8. Otherwise, the National Character Set is changed to AL16UTF16.

During the upgrade, the existing NCHAR columns in the data dictionary are changed to use the new Oracle Database format and, if the National Character Set has been changed to AL16UTF16, the dictionary NCHAR columns will be converted to the AL16UTF16 character set.

AL24UTFFSS Character Set Desupported

The AL24UTFFSS Unicode character set has been desupported in Oracle9i release 9.0.1 and later. AL24UTFFSS was introduced in Oracle7 as the Unicode character set supporting the UTF-8 encoding scheme based on the Unicode 1.1 standard, which is now obsolete. In release 9.0.1 and later, The Unicode database character sets AL32UTF8 and UTF8, include the Unicode enhancements based on the Unicode 3.1 standard.

The migration path for existing AL24UTFFSS databases is to upgrade your database character set to UTF8 prior to upgrading your Oracle Database. As with all migrations to a new database character set, Oracle recommends you use the Character Set Scanner for data analysis before attempting to migrate your existing database character set to UTF8.

Subtypes and Non-Final Types

Types created in Oracle8i release 8.1 and earlier are considered to be FINAL types. Thus, they cannot be used as supertypes in Oracle9i release 9.0.1 and later. However, an ALTER statement can be explicitly used to change the type to be NOT FINAL.

CHAR Column Length Semantics

If you plan to use CHAR column length semantics in a replication database after you upgrade it to Oracle Database 10g, then all of the databases participating with that database in the replication environment must also use CHAR column length semantics. In this case, Oracle recommends that you upgrade all of the databases participating in the replication environment at the same time. This applies to both master sites and materialized view sites in your replication environment.

If you cannot upgrade all of the databases in your replication environment at the same time, then you can only use CHAR column length semantics in your Oracle Database if all of the databases prior to Oracle9i are using a single-byte character set. Otherwise, do not switch to CHAR column length semantics in the Oracle Database until all of the other databases in the replication environment are upgraded to Oracle Database 10g.

NCHAR or NVARCHAR2 Columns

If your replication database contains tables with NCHAR or NVARCHAR2 columns, then Oracle recommends that you upgrade all of the databases participating in the replication environment at the same time. This applies to both master sites and materialized view sites in your replication environment. In Oracle Database 10g, all columns specified as NCHAR or NVARCHAR2 datatype are stored in Unicode format.

If you cannot upgrade all of the databases in your replication environment at the same time, then interoperability is only supported if all of the databases prior to Oracle9i are using a fixed width national character set. If any of the databases prior to Oracle9i are using a variable width character set, then you must convert these databases to fixed width character sets before you upgrade any of the other databases in the replication environment to Oracle Database 10g.

General Compatibility and Interoperability Issues for Applications

This section describes general compatibility and interoperability issues for applications.

OCI Applications

This section describes compatibility and interoperability issues relating to OCI applications.

Precompiler Applications

This section describes compatibility and interoperability issues relating to precompiler applications.

EXEC SQL CONNECT :sys IDENTIFIED BY :sys_passwd; 

EXEC SQL CONNECT :sys IDENTIFIED BY :sys_passwd IN SYSDBA MODE; 

EXEC SQL 
    CONNECT :sys IDENTIFIED BY :SYS-PASSWD 
END-EXEC.

EXEC SQL 
    CONNECT :sys IDENTIFIED BY :SYS-PASSWD IN SYSDBA MODE 
END-EXEC.

... DBMS=Oracle7

PL/SQL Applications

This section includes compatibility and interoperability issues for PL/SQL applications.

ALTER SESSION SET events = '10933 trace name context forever, level 512';

ALTER SESSION SET events = '10932 trace name context level 32768'

Transportable Tablespace

There are compatibility issues when you transport a tablespace between two databases.

Tempfiles

Release 8.1 introduced tempfiles. The information about tempfiles is in different static data dictionary views and dynamic performance views than the information about datafiles. To view information about tempfiles, consult the DBA_TEMP_FILES static data dictionary view and the following dynamic performance views:

• V$TEMPFILE

• V$TEMP_EXTENT_MAP

• V$TEMP_EXTENT_POOL

• V$TEMP_SPACE_HEADER

• V$TEMPSTAT

• V$TEMP_PING

Oracle automatically assigns numbers to both datafiles and tempfiles. Two datafiles cannot share the same number; similarly, two tempfiles cannot share the same number. However, a tempfile and a datafile can share the same number.

Data Dictionary Protection

The data dictionary protection mechanism introduced in release 8.0 may cause problems in any applications that create user tables in the SYS schema and access them using the 'ANY' privileges. For example, the user must have DELETE CATALOG ROLE to use the DELETE statement to purge the audit records in the AUD$ table.

Creating and accessing user tables in SYS schema is not secure. Therefore, applications are expected to move the objects to a different schema. Use the O7_DICTIONARY_ACCESSIBILITY initialization parameter for temporary compatibility. However, this parameter is only for interim use.

Applications should not attempt to connect to user SYS without SYSDBA privileges. Instead of connecting to user SYS and sharing the password, grant DBA privilege to a normal user, who will connect to the database as a user with SYSDBA privileges to connect to SYS schema.

In Oracle Database, a user can be granted the SELECT ANY DICTIONARY privilege. A user with this privilege can access objects in the SYS schema regardless of the setting of O7_DICTIONARY_ACCESSIBILITY.

Bitmap Index Protection

In releases prior to release 8.1, it was possible to unintentionally invalidate bitmap indexes by issuing certain SQL statements. The most common causes of bitmap index invalidation were the following types of statements:

• ALTER TABLE statements that define a primary key on an existing table that did not previously have a primary key.

• ALTER TABLE statements that define a NOT NULL constraint on a column that did not previously have this constraint.

Oracle Database eliminates these unintentional invalidations.

Large Objects (LOBs)

This section describes compatibility and interoperability issues relating to LOBs.

SQL> SELECT index_name, index_type, tablespace_name
         FROM dba_indexes
         WHERE index_type = 'LOB';

Oracle ROWIDs

This section describes compatibility and interoperability issues related to rowids.

NCHAR and NLS Environment Variables and Compatibility

You should set NLS_LANG to your environment as follows:

• Set the ORA_NLS33 environment variable for the release 8.0 and higher environment

Verify that the client has the correct NLS character set environment variables.

New Format for User-Defined Datatypes

Release 8.1 introduced a new format for user-defined datatypes. The new format can result in significant performance improvements over the format used in release 8.0. You can use release 8.0 user-defined datatypes in a release 8.1 or higher database without causing compatibility problems. However, the database will not realize the performance gains possible with the new format.

Nested Tables

Release 8.0 clients do not support the following release 8.1 and higher nested table features:

• Collection locators

• User-specified storage for collection columns, including storage of nested table data in an index-organized table

Therefore, access fails with an incompatibility error when a release 8.0 client attempts to access a release 8.1 or higher server and a nested table is specified to be returned as a locator, or the storage for the nested table is user-specified.

Varrays Stored as LOBs

Release 8.0 clients do not support specifications of storage parameters for storing varrays as LOBs. Therefore, access fails with an incompatibility error when a release 8.0 client attempts to access a release 8.1 or higher server where there is a specification of storage parameters for storing a varray as a LOB.

Functions GREATEST_LB, LEAST_UB, and TO_LABEL Desupported

Starting with release 8.1, the built-in PL/SQL functions GREATEST_LB, LEAST_UB, and TO_LABEL are no longer supported.

SQL Scripts utlchain.sql and utlchn1.sql

The Oracle Database installation includes the following two scripts for creating a table that stores migrated and chained rows: utlchain.sql and utlchn1.sql. The utlchn1.sql script can be run on index-organized tables as well as regular tables, while the utlchain.sql script can be run only on regular tables, but not on index-organized tables.

In Oracle8i release 8.1 and later, you must always run the utlchn1.sql script.

SQL Scripts utlexcpt.sql and utlexpt1.sql

The Oracle Database installation includes the following two scripts for creating a table that stores exceptions from enabling constraints: utlexcpt.sql and utlexpt1.sql. The utlexpt1.sql script can be run on index-organized tables as well as regular tables, while the utlexcpt.sql script can be run only on regular tables, but not on index-organized tables.

In Oracle8i release 8.1 and later, you must always run the utlexpt1.sql script.

Interoperability and the Sender's ID Column

In release 8.1 and higher, the sender's ID is mapped as an additional attribute in the message properties. This new attribute is ignored when there is communication between release 8.0 and release 9.0.1 and higher databases.

For OCI applications, the sender's ID attribute is available as a new attribute in the message properties descriptor. Release 8.1 and higher OCI clients use a new RPC code to send and receive the message properties to and from the server.

Rule Based Subscriptions

When you migrate a queue table from release 8.0 to release 8.1 or higher using the DBMS_AQADM.MIGRATE_QUEUE_TABLE procedure, any existing subscribers are upgraded automatically to subscribers with null rules.

INSTANCES Keyword in PARALLEL Clause

The INSTANCES keyword can be used in release 8.1 and higher, but it will be interpreted differently than in past releases. In release 8.0, the INSTANCES keyword could be used in the PARALLEL clause of statements such as the following:

• ALTER CLUSTER

• ALTER DATABASE ... RECOVER

• ALTER INDEX ... REBUILD

• ALTER TABLE

• CREATE CLUSTER

• CREATE INDEX

• CREATE TABLE

It also could be used in hints. The INSTANCES keyword was used to specify the number of Oracle Parallel Server instances to use in a parallel operation.

Also starting with release 8.1, the syntax for specifying degree in a PARALLEL clause has changed. You can specify degree simply by placing a number after PARALLEL, as in the following example:

ALTER TABLE emp PARALLEL 5;

However, the DEGREE keyword remains valid if you choose to use it. The preceding syntax is equivalent to the following statement:

ALTER TABLE emp PARALLEL (DEGREE 5 INSTANCES 1);

Regardless of the syntax, the value you specify is the number of query threads used in a parallel operation. Neither syntax will affect how many instances are used to execute a query. The system will determine how many instances to use based on the instances available and the load on each of the instances. So, either syntax will produce the same result.

Enterprise User Management

This section includes compatibility and interoperability issues related to enterprise user management. This functionality is part of the Oracle Advanced Security feature.

Recovery Manager

Recovery Manager Commands

Release 8.1 of Recovery Manager introduced changes to some Recovery Manager commands. However, all commands used in prior releases will continue to work with release 8.1 and higher of Recovery Manager.

For example, the CLONE command is changed to the DUPLICATE command, but the CLONE command will continue to work. Also, the CLONE option of the ALLOCATE and CONNECT commands is now the AUXILIARY option, but the CLONE option will continue to work. Similarly, the CLONENAME keyword in the COPY and SET commands is now AUXNAME, but the CLONENAME keyword will continue to work.

Datafile Backups

A datafile backup taken with release 8.0 and higher can be restored and recovered with any later Oracle release, if a direct upgrade path between the release that backed-up the file and the release that recovers the file is supported. See Table 2-1, "Upgrade Paths".

Standby Database

The following compatibility restrictions apply to standby databases:

• The primary and standby databases should run on the same operating system. In addition, Oracle recommends that the primary and standby databases run on the same release of the operating system.

• The primary and standby databases must run the same maintenance release of Oracle. For example, if your primary database is running release 8.1.6, then the standby database can run any production 8.1 release, such as release 8.1.5, 8.1.6, or 8.1.7. However, in this case, the standby database cannot run release 8.0.

Fast-Start On-Demand Rollback and Fast-Start Parallel Rollback

As part of the recovery process, after a session or instance is abnormally terminated, Oracle rolls back uncommitted transactions. Oracle8i introduced two features to improve rollback performance: fast-start on-demand rollback and fast-start parallel rollback.

When a dead transaction holds a row lock on a row that another transaction needs, fast-start on-demand rollback automatically recovers the data block required by the new transaction. Other data blocks and transactions that do not block any new transaction's progress are rolled back in the background.

Fast-start parallel rollback improves background rollback performance by recovering each dead transaction using multiple server processes. Fast-start parallel rollback recovers each dead transaction using multiple server processes only if the following conditions are met:

• There are enough server processes to allocate one or more processes to each dead transaction.

Archiving of Redo Logs

Release 8.1 and higher enables you to archive online redo log files to multiple destinations, including to a local disk-based file or to a specified standby database. The compatibility and interoperability issues described in this section may arise because of this functionality.

LogMiner

LogMiner runs in a release 8.1 or higher instance and can analyze redo log files from any database that meets the following criteria:

• Has the same DBCS (Database Character Set) as the analyzing Oracle instance

• Is running on the same hardware platform as the analyzing Oracle instance

LogMiner does not require a mounted database to analyze redo log files. However, to fully translate the contents of the redo log files, LogMiner requires access to a LogMiner dictionary (catalog). LogMiner uses the dictionary to translate internal object identifiers and data types to object names and external data formats. You can use the PL/SQL package DBMS_LOGMNR_D to extract a database dictionary into an external file for later use in analyzing redo log files. Without a dictionary, LogMiner returns the internal object identifiers and presents data as hex bytes.

Oracle Media Management API and Proxy Copy

Starting with Oracle Media Management API version 2, proxy copy functionality is supported. If a Recovery Manager proxy backup is attempted, and Oracle is linked with Oracle Media Management API release 1.1, or a version 2 that does not support proxy copy functionality, then Recovery Manager will return an error and the backup will fail.

Materialized Views

Prior to release 8.1, an Oracle materialized view always consisted of a materialized view base table and a view on the base table. For example, creating a materialized view SNAP_EMP creates a view SNAP_EMP and a base table normally called SNAP$_SNAP_EMP. In release 8.1 and higher, most materialized views will have only a base table with the same name as the materialized view. The view will not be created.

A view will be added to the materialized view under the following conditions:

• The materialized view was imported from a database prior to release 8.1, such as release 8.0.

• The materialized view requires hidden columns (that is, rowid materialized views and fast-refreshable materialized views that contain subqueries).

Oracle Replication

The following compatibility restrictions apply to a replicated environment:

• If you have a replicated environment with different releases of the Oracle Database, then you cannot replicate data that is incompatible on the lower release. For example, in a replicated environment with a database at 9.2.0 compatibility level and another database at 8.1.0 compatibility level, you cannot replicate data between them if the data is incompatible with release 8.1.

• To improve performance and protect data integrity, a number of Advanced Replication packages that were external prior to release 8.1 have been internalized in release 8.1 and higher. Oracle Database Advanced Replication contains a list of these internalized packages.

If one or more of your master sites is a release prior to release 8.1, then the GENERATE_80_COMPATIBLE flag must be unset or set to TRUE in the following procedures:

• GENERATE_REPLICATION_SUPPORT

• CREATE_SNAPSHOT_REPOBJECT

• GENERATE_SNAPSHOT_SUPPORT

Heterogeneous Services Agents

This section describes compatibility and interoperability issues related to Heterogeneous Services agents.

Service Naming and Connection Load Balancing

Release 8.1 and higher supports service naming and connection load balancing for services that include more than one database instance. Each service can include multiple instances, and each instance can include multiple handlers. This support enables clients to access a service rather than a specific database instance, and logically separates the service name from any particular instance name.

To support services that include multiple instances, use the following new parameters in connect descriptors:

• SERVICE_NAME

• INSTANCE_NAME

The new parameters enable connection load balancing by taking requests through the following process:

1. A client program specifies the name of the service to which it wants to connect.

2. The TNS Listener finds the least loaded instance in the service.

3. The TNS Listener finds the least loaded handler in the instance.

4. The TNS Listener redirects the client to the optimal handler, or passes the client connection to the handler, if necessary.

To use connection load balancing, perform the following actions:

• Discontinue the use of the SID parameter in connect descriptors.

• Use the SERVICE_NAMES and INSTANCE_NAME initialization parameters in your parameter file.

• Use the SERVICE_NAME parameter in the tnsnames.ora file.