5 Compatibility and Interoperability

The COMPATIBLE Initialization Parameter

The Oracle Database enables you to control the compatibility of your database with the COMPATIBLE initialization parameter. By default, when the COMPATIBLE initialization parameter is not set in your parameter file, it defaults to 10.2.0 for Oracle Database 10g release 10.2. You cannot use new Oracle Database 10g features that would make your upgraded database incompatible until the COMPATIBLE initialization parameter is set to this value.

Table 5-1 illustrates the default value and the range of values of the COMPATIBLE initialization parameter in the new Oracle Database 10g release and in each release supported for upgrading to the new Oracle Database 10g release.

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

Setting 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.2.0, issue the following statement:

      SQL> ALTER SYSTEM SET COMPATIBLE = '10.2.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.2.0, enter the following in the initialization parameter file:

      COMPATIBLE = 10.2.0
      

   3. Start the instance using STARTUP.

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. Prior to release 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 release 10.2, the XML generation functions in Oracle XML DB produce dates and timestamps according to the XML schema standard.

CONNECT Role

After upgrading to Oracle Database 10g, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. For further information about this, see "Deprecated CONNECT Role".

Timezone Files

The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE datatype. For further information about this, see "TIMESTAMP WITH TIMEZONE Datatype".

SQL Optimizer

This section describes compatibility and interoperability issues relating to the SQL Optimizer in Oracle Database 10g.

SQL

Starting with Oracle Database 10g release 10.1, CLOB <-> NCLOB implicit conversion in SQL and PL/SQL is allowed.

Starting with release 10.1, name resolution for synonyms has changed. If the base object of a synonym does not exist, the SQL compiler now tries looking up PUBLIC.base_object.

Starting with release 10.1, VPD policies are attached to synonyms rather than the base objects.

Invalid Synonyms After an Upgrade

Starting with Oracle Database 10g release 10.1, if a synonym (public or private) is pointing to an object that does not exist or is invalid, then the synonym will be invalid after the upgrade.

Manageability

Database performance statistics are now automatically collected by the Automatic Workload Repository (AWR) database component for databases upgraded to Oracle Database 10g and for newly created Oracle Database 10g 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 (ORACLE_HOME/rdbms/admin/spdoc.txt) for directions on using Statspack in Oracle Database 10g to avoid conflict with the AWR.

Transaction and Space

Starting with Oracle Database 10g release 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 release 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 will always be suppressed.

Starting with release 10.1, the default AUTOEXTEND NEXT size is larger for Oracle-managed files (OMF).

Recovery and Data Guard

Starting with Oracle Database 10g release 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 release 10.1, the LOG_PARALLELISM initialization parameter has been deprecated. Log file parallelism is now automatically enabled.

Starting with release 10.1, the default value for the RECOVERY_PARALLELISM initialization parameter now defaults to allow parallel recovery.

Starting with release 10.1, the default value for the parallel clause in the ALTER DATABASE RECOVER DATABASE statement has changed to PARALLEL.

Starting with release 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.

Starting with release 10.1, the default values of the parameters MAX_SGA and MAX_SERVERS as set by the DBMS_LOGSTDBY.APPLY_SET() procedure have changed.

Starting with release 10.1, the default values for the Data Guard broker properties ApplyParallel, AsyncBlocks, and LogXptMode have changed.

Starting with release 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 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 release 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.

CREATE DATABASE

In Oracle Database 10g, a SYSAUX tablespace is always created at database creation time or whenever a database is upgraded. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because SYSAUX is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces that a DBA must maintain.

Starting with release 10.1, the minimum and default log file sizes have increased. The minimum size is now 4 MB. The default size is 50 MB, unless using Oracle-managed files (OMF) in which case the default is 100 MB.

Real Application Clusters

In Oracle Database 10g, there is now an automated high availability (HA) framework for Real Application Clusters. The framework provides detection, recovery, restart, and notification services.

Materialized Views

Starting with Oracle Database 10g release 10.1, some privilege name changes have been made. The new names appear in all data dictionary views, but both the old and new names are accepted by the GRANT and REVOKE SQL statements.

• CREATE SNAPSHOT changed to CREATE MATERIALIZED VIEW

• CREATE ANY SNAPSHOT changed to CREATE ANY MATERIALIZED VIEW

• ALTER ANY SNAPSHOT changed to ALTER ANY MATERIALIZED VIEW

• DROP ANY SNAPSHOT changed to DROP ANY MATERIALIZED VIEW

Change Data Capture

Starting with Oracle Database 10g release 10.1, the interfaces in DBMS_CDC_SUBSCRIBE and DBMS_CDC_PUBLISH now take a subscription name parameter instead of a subscription handle.

Starting with release 10.1, subscriber views are now managed automatically. There is no longer any need to call the DBMS_CDC_SUBSCRIBE and DBMS_CDC_PUBLISH interfaces PREPARE_SUBSCRIBER_VIEW() and DROP_SUBSCRIBER_VIEW().

Starting with release 10.1, the computation of synchronous Change Data Capture's RSID$ column has been changed to facilitate joining a subscriber view to itself in order to show both old and new values in the same row. The RSID$ values for the UO and UN rows associated with the same update operation are now the same. To revert to the Oracle9i behavior where UN RSID$ value is UO RSID$ value + 1 for the same update operation, set event 10983 to level 4.

Change in the Default Archival Processing to Remote Archive Destinations

Starting with Oracle Database 10g release 10.1, the default archival processing to remote destinations has changed so that archiver processes on the primary database will 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 release 10.1. 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 release 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 may 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.

Limitations on NCHAR Datatypes

In Oracle Database 10g, the NCHAR datatypes such as NCHAR, NVARCHAR2, and NCLOB, are limited to the Unicode character set encoding, UTF8 and AL16UTF16.

PL/SQL Native Compilation

Starting with Oracle Database 10g release 10.1, the configuration of initialization parameters and the command setup for native compilation has been simplified. The only required parameter is PLSQL_NATIVE_LIBRARY_DIR. The parameters related to the compiler, linker, and make utility have been obsoleted. Native compilation is turned on and off by a separate initialization parameter, PLSQL_CODE_TYPE, rather than being one of several options in the PLSQL_COMPILER_FLAGS parameter, which is now deprecated. The $ORACLE_HOME/plsql/spnc_commands file contains the commands and options for compiling and linking, rather than a makefile.

Locally Managed SYSTEM Tablespace

Starting with Oracle9i release 9.2, the SYSTEM tablespace can be locally managed. The SYSTEM tablespace can be migrated from dictionary managed format to locally managed format using the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

Before the SYSTEM tablespace can be migrated to locally managed format, you should ensure the following:

• The database has a default temporary tablespace which is not SYSTEM

• There are not any rollback segments in dictionary managed tablespaces

• There is at least one online rollback segment in a locally managed tablespace, or an undo tablespace (if using automatic undo management mode) should be online.

• All tablespaces other than the tablespace containing the undo space (undo tablespace or the tablespace containing the rollback segment) and the default temporary tablespace are in read-only mode.

• There is a complete backup of the system.

• The system is in restricted mode.

The following query determines whether the SYSTEM tablespace is locally managed:

SQL> SELECT ts# FROM ts$
         WHERE ts# = 0 AND bitmapped <> 0;

If 0 rows are returned, then the SYSTEM tablespace is dictionary managed. Otherwise, the SYSTEM tablespace is locally managed.

New AnyData Datatypes

Starting with Oracle9i release 9.2, persistent storage of AnyData values of the following datatypes is allowed:

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• NCHAR

• NVARCHAR2

• NCLOB

Dictionary Managed Tablespaces

Starting with Oracle9i release 9.2, dictionary managed tablespaces are deprecated. Once the SYSTEM tablespace has been migrated from dictionary managed format to locally managed format, existing dictionary managed tablespaces are read-only. That is, they cannot be made read-write once the SYSTEM tablespace is locally managed.

Once the SYSTEM tablespace is locally managed (either due to a new installation or SYSTEM tablespace migration), new dictionary managed tablespaces cannot be created.

Change in Compatibility for Automatic Segment-Space Managed Tablespaces

Starting with Oracle9i release 9.0.1.3.0, the compatibility requirement for automatic segment-space managed tablespaces has been changed from 9.0.0 when first introduced in Oracle9i release 9.0.1 to 9.0.1.3. If you are upgrading from an Oracle9i release earlier than release 9.0.1.3.0 and the database contains any automatic segment-space managed tablespaces, then the COMPATIBLE initialization parameter will need to be set to 9.0.1.3 or higher in order to open the database. The existing tablespaces need not be dropped.

Compatibility and Object Types

Starting with Oracle9i release 9.2, object types support user-defined constructors using the CONSTRUCTOR keyword that cannot be referred to from PL/SQL programs in previous releases of the Oracle Database. Specifically, such programs will fail to compile with an error.

Oracle Managed Files

Starting with Oracle9i release 9.0.1.2.0, the naming scheme used by the Oracle Database to keep track of Oracle Managed Files has changed. As a result, existing Oracle Managed Files created in Oracle9i releases earlier than release 9.0.1.2.0 will appear to the Oracle Database to be regular operating system files.

Oracle OLAP

The OLAP API client provided with Oracle9i release 9.0.1 is not compatible with newer Oracle Database releases; similarly, the OLAP API client provided with Oracle9i release 9.2 is not compatible with earlier Oracle Database releases.

The procedure that an application uses to make a connection through the OLAP API has changed in release 9.2. Connections in previous releases relied on CORBA software, but in release 9.2 and later, connections are made through Java Database Connectivity (JDBC). Consequently, programs created using the OLAP API client provided with release 9.0.1 will not execute in later releases, and programs created using the OLAP API client provided with release 9.2 will not execute in earlier Oracle releases.

To upgrade OLAP API applications designed to run in release 9.0.1, application developers must use the OLAP API client provided with release 9.2 and revise the code for making a connection and for creating a MetadataProvider.

For information about using the OLAP API in release 9.2 to perform these actions, see the Oracle OLAP Developer's Guide to the OLAP API and the online Oracle OLAP API Reference help.

Log Format Change with Parallel Redo

Starting with Oracle9i release 9.2, the parallel redo feature generates redo logs using a new format. Previous releases of the Oracle Database cannot apply parallel redo generated logs. However, when previous Oracle Database releases detect that release 9.2 parallel redo is being applied, the following error is displayed:

ORA-00303: cannot process Parallel Redo

Release 9.2 can process Oracle9i release 9.0.1 and earlier format logs as well as release 9.2 parallel redo format logs.

Oracle Dynamic Services

Starting with Oracle9i Database release 9.2, Oracle Dynamic Services has been Deprecated. Oracle Dynamic Services, an XML-based broker for the creation, aggregation, and deployment of services from various content sources, was released with Oracle9i Database release 9.0.1.

Starting with Oracle9iAS release 2 (9.0.2), Oracle is delivering an integrated, J2EE-compliant Web Services platform. Oracle Dynamic Services has been integrated with Oracle9iAS Web Services as the XML/HTML Stream Processing Tool.

Oracle9iAS release 2 (9.0.2) provides a standards-based, fully integrated J2EE and Web services deployment platform. The current Dynamic Services functionality has been integrated into the Oracle9iAS platform, and the Dynamic Services terminal release is being delivered with Oracle9i Database release 9.2.

Oracle Syndication Server

Starting with Oracle9i Database release 9.2, Oracle Syndication Server has been Deprecated. Oracle Syndication Server, designed to deliver file system and database content to Information and Content Exchange (ICE)-compliant subscribers, was released with Oracle9i Database release 9.0.1.

Starting with Oracle9iAS release 2 (9.0.2), Oracle Syndication Server has become a feature of Oracle9iAS. The current Syndication Server functionality has been integrated into this platform, and the Syndication Server terminal release is being delivered with Oracle9i Database release 9.2.

Oracle9iAS Syndication Server is automatically installed with the Oracle9iAS Portal install.

The STARTUP Command

This section describes compatibility and interoperability issues related to the SQL*Plus STARTUP command.

Tablespaces and Datafiles

This section describes compatibility and interoperability issues related to tablespaces and datafiles.

Datatypes

This section describes compatibility and interoperability issues relating to datatypes.

User-Defined Datatypes

This section describes compatibility and interoperability issues relating to user-defined datatypes.

Oracle Replication

If you plan to use CHAR column length semantics in Oracle Database 10g, or if your replication database contains tables with NCHAR or NVARCHAR2 columns, then this section contains considerations for upgrading a replication environment to Oracle Database 10g.