5 Compatibility and Interoperability

This chapter describes compatibility and interoperability issues that may arise because of differences between Oracle Database releases. These differences may affect general database administration and existing applications.

This chapter covers the following topics:

What Is Compatibility?

When you upgrade to a new release of the Oracle Database, certain new features may make your database incompatible with your previous release. Your upgraded Oracle database becomes incompatible with your previous release under the following conditions:

  • A new feature stores any data on disk (including data dictionary changes) that cannot be processed with your previous release.

  • An existing feature behaves differently in the new environment as compared to the old environment. This type of incompatibility is classified as a language incompatibility.

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.

Table 5-1 The COMPATIBLE Initialization Parameter

Oracle Database Release Default Value Minimum Value Maximum Value

Oracle8i release 8.1.7

8.0.0

8.0.0.0.0

8.1.7.x.x

Oracle9i release 9.0.1

8.1.0

8.1.0.0.0

9.0.1.x.x

Oracle9i release 9.2

8.1.0

8.1.0.0.0

9.2.0.x.x

Oracle Database 10g release 10.1

10.0.0

9.2.0.0.0

10.1.0.x.x

Oracle Database 10g release 10.2

10.2.0

9.2.0.0.0

10.2.0.x.x


Downgrading and Compatibility

Before upgrading to the new Oracle Database 10g release, the COMPATIBLE initialization parameter must be set to at least 9.2.0, which is the lowest possible setting for Oracle Database 10g release 10.2. 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, after upgrading, you will want to downgrade, then the COMPATIBLE initialization parameter must be left as follows after the upgrade:

  • Set to 9.2.0 if you upgraded from release 9.2

  • Set to 10.1.0 or earlier if you upgraded from release 10.1

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.2 database with the COMPATIBLE initialization parameter set to 9.2.0, then the release 10.2 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.

See Also:

Oracle Database Concepts for more information about database structures

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.2.0, then the database runs at 10.2.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

Once the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the Oracle Database 10g release. However, after you do this, the database cannot subsequently be downgraded.

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.

What Is Interoperability?

Interoperability is the ability of different releases of the Oracle Database to communicate and work together in a distributed environment. A distributed database system can have different releases of the Oracle Database, and all supported releases of the Oracle Database can participate in a distributed database system. However, the applications that work with a distributed database must understand the functionality that is available at each node in the system.

Note:

Since this book documents upgrading and downgrading between different releases of the Oracle Database, this definition of interoperability is appropriate. However, other Oracle Database documentation may use a broader definition of the term interoperability; for example, in some cases, interoperability may describe communication between different hardware platforms and operating systems.

Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.2

The following sections describe compatibility and interoperability issues introduced in Oracle Database 10g Release 2 (10.2). The following sections discuss actions you can take to prevent problems resulting from these issues.

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.

See Also:

Oracle XML DB Developer's Guide for more information

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".

Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1

The following sections describe compatibility and interoperability issues introduced in Oracle Database 10g release 1 (10.1). If you are upgrading to the new Oracle Database 10g release from a release prior to 10.1, see the following sections for information about actions you can take to prevent problems resulting from these issues:

SQL Optimizer

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

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 Oracle Database 10g. 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.

See Also:

Oracle Database Performance Tuning Guide for more information about the cost-based optimizer

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.

See Also:

Oracle Database Performance Tuning Guide for more information about optimizer statistics

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.

See Also:

SKIP_UNUSABLE_INDEXES in Oracle Database Reference

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.

See Also:

Oracle Database Administrator's Guide for more information about the SYSAUX tablespace

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.

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).

See Also:

Oracle Data Guard Concepts and Administration for complete information about setting up archival to remote 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.

See Also:

Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.2

The following sections describe compatibility and interoperability issues introduced in Oracle9i release 9.2. If you are upgrading to the new Oracle Database 10g release from a release earlier than release 9.2, then the sections which follow discuss actions you can take to prevent problems resulting from these issues.

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.

Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.0.1

The following sections describe compatibility and interoperability issues introduced in Oracle9i release 9.0.1. If you are upgrading to the new Oracle Database 10g release from a release earlier than release 9.0.1, then the sections which follow discuss actions you can take to prevent problems resulting from these issues.

The STARTUP Command

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

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

Tablespaces and Datafiles

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

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.

See Also:

Oracle Database Administrator's Guide for more information about managing undo space.

Datatypes

This section describes compatibility and interoperability issues relating to datatypes.

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 datatype, 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.

See Also:

Oracle Database Globalization Support Guide for more information about the Character Set Scanner

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.

Note:

NCHAR columns in user tables are not changed during the upgrade. To change NCHAR columns in user tables, see "Upgrade User NCHAR Columns".

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.

See Also:

Oracle Database Globalization Support Guide for more information about the Character Set Scanner

User-Defined Datatypes

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

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.

Release 8.1 Clients Accessing a Release 9.0.1 or Higher Server

Any transfer involving data of non-final types will return an error. Release 8.1 clients cannot access a release 9.0.1 or higher server if the type has been altered to non-final on the server.

Release 9.0.1 and Higher Clients Accessing a Release 8.1 Server

Since the release 8.1 server can have only non-final types, no errors occur.

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.

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.

See Also: