Oracle8i Migration
Release 3 (8.1.7)

Part Number A86632-01

Library

Product

Contents

Index

Go to previous page Go to next page

13
Downgrading to an Older Version 8 Release

The information in this chapter only applies to release 8.1 installations of Oracle. The term downgrading describes transforming an Oracle database into a previous release of the same version, such as transforming a database from release 8.1.5 to release 8.0.5. The term downgrading also describes transforming an Oracle database into a previous version, such as transforming a database from Oracle8i to Oracle7. This chapter describes downgrading to an older 8.1 release of Oracle or to an 8.0 release of Oracle. If you want to downgrade to Oracle7, then see Chapter 14, "Downgrading to Oracle7".

Perform the procedures in the following sections, in the order shown, to downgrade your database:

Perform a Full Offline Backup

Perform a full offline backup of your release 8.1 database before you downgrade.

See Also:

Oracle8i Backup and Recovery Guide for more information. 

Remove Incompatibilities

The process for removing incompatibilities depends on whether you are downgrading to a previous 8.1 release or to an 8.0 release. First, check the compatibility level of your database to see if your database might have incompatibilities with the release to which you are downgrading.

Checking the Compatibility Level of Your Database

If the compatibility level of your database is higher than the release to which you are downgrading, then your database may have incompatibilities with the previous release that must be removed before you downgrade. Your compatibility level matches the setting for the COMPATIBLE initialization parameter. Check your COMPATIBLE initialization parameter setting by issuing the following SQL statement:

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

You do not need to remove incompatibilities if the COMPATIBLE parameter is set to the release to which you are downgrading or lower. For example, if you are downgrading to release 8.0.6 and the COMPATIBLE parameter is set to 8.0.6 or lower, then you do not need to remove incompatibilities. In this case, no incompatibilities exist in your database with the release to which you are downgrading, and you can skip the rest of this section and go to the "Downgrade the Database" section.

However, if you are downgrading to a release and the COMPATIBLE parameter is set higher than the release to which you are downgrading, then some incompatibilities may exist. For example, if you are downgrading to release 8.0.6, and COMPATIBLE is set to 8.1.0 or higher, then incompatibilities may exist. Similarly, if you are downgrading to release 8.1.5, and COMPATIBLE is set to 8.1.6 or higher, then incompatibilities may exist.

Follow the instructions in the appropriate section below based on the release to which you are downgrading:

Removing Incompatibilities If You Are Downgrading to Release 8.1.5

If you are downgrading to release 8.1.5, then complete the actions in the following sections to remove incompatibilities:

Discontinue Use of CACHE READS Specification for LOBs

Before you downgrade to release 8.1.5, you must stop using the CACHE READS storage parameter for LOBs.

The following sections contain SQL statements that identify existing uses of CACHE READS specification for LOBs.

To identify tables that have LOB columns specified as cache reads, issue the following SQL statement as SYS user:

SELECT owner, table_name, column_name 
  FROM dba_lobs 
  WHERE cache = 'CACHEREADS'; 
 

To identify partitioned tables that have LOB columns specified as CACHE READS as default attributes at the table level, issue the following SQL statement as SYS user:

SELECT table_owner, table_name, column_name 
  FROM dba_part_lobs 
  WHERE def_cache = 'CACHEREADS'; 
 

To identify partitioned tables that have LOB columns specified as CACHE READS at the partition level, issue the following SQL statement as SYS user:

SELECT table_owner, table_name, column_name, partition_name 
  FROM dba_lob_partitions 
  WHERE cache = 'CACHEREADS'; 
 

To identify partitioned tables that have LOB columns specified as CACHE READS at the subpartition level, issue the following SQL statement as SYS user:

SELECT table_owner, table_name, column_name, subpartition_name 
  FROM dba_lob_subpartitions 
  WHERE cache = 'CACHEREADS'; 
 

After you have identified all of the uses of the CACHE READS specification for LOBs, you can change them to use the CACHE or NOCACHE specification, because these specifications are compatible with release 8.1.5. The following sections provide examples of the SQL statements you must issue to make these changes.

To change a LOB storage parameter from CACHE READS to CACHE or NOCACHE, use the ALTER TABLE ... MODIFY LOB statement. For example, on a table named LOB_TAB with a LOB column named LOB_COL, issue the following SQL statement to change the storage specification to CACHE:

ALTER TABLE lob_tab MODIFY LOB (lob_col) (CACHE); 
 

This statement also can be used to modify LOB storage parameters at the both the table and partition level for partitioned tables. To modify only the default table level attributes of LOB columns from CACHE READS to CACHE or NOCACHE for partitioned tables, use the ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES statement. For example, on a table named LOB_PART_TAB with a LOB column named LOB_COL, issue the following SQL statement to change the storage specification to NOCACHE:

ALTER TABLE lob_part_tab 
  MODIFY DEFAULT ATTRIBUTES LOB (lob_col) (NOCACHE); 
 

To modify LOB storage parameter from CACHE READS to CACHE or NOCACHE at the partition level for partitioned tables, use the ALTER TABLE ... MODIFY PARTITION statement. For example, on a table named LOB_PART_TAB with a LOB column named LOB_COL and a partition named PART_1, issue the following SQL statement to change the storage specification to CACHE:

ALTER TABLE lob_part_tab 
  MODIFY PARTITION part_1 LOB (lob_col) (CACHE);
Remove Incompatible Triggers

A new feature in release 8.1.6 and higher supports triggers on all SQL DDL statements, instead of only CREATE, ALTER, and DROP statements. These triggers must be dropped before downgrading. To check for triggers that are incompatible with release 8.1.5, connect as SYSDBA user and issue the following SQL statement:

SELECT owner, trigger_name, triggering_event 
   FROM dba_triggers 
   WHERE base_object_type LIKE '%DATABASE%' OR base_object_type LIKE '%SCHEMA%';

Drop all of the triggers that have one of the following events in the TRIGGERING_EVENT column:

Removing or Recreating Global Users Whose External Name Is NULL

The schema-independent user feature of Oracle Advanced Security, in which many enterprise users access a shared schema, is not compatible with release 8.1.5. If you are using this feature, you need to identify the shared schemas. That is, the global users whose external name is NULL. To identify the shared schemas, issue the following SQL statement:

SELECT name FROM dba_users
   WHERE password='GLOBAL' AND external_name IS NULL;

You then need to either remove the users listed or recreate them so that they have a non-NULL external name.

Also, any enterprise users who access the shared schema need to be created as database users so that they can now access database objects. These enterprise users did not previously exist in the database. You can create them as global users (that is, authenticated by SSL), as externally authenticated users, or as users authenticated by password.

For example, suppose you created the following global user in release 8.1.6 or higher:

CONNECT system/system_password
CREATE USER user1 IDENTIFIED GLOBALLY AS '';

To make this user compatible with release 8.1.5 by authenticating the user with a password, issue the following SQL statements:

CONNECT system/system_password
ALTER USER user1 IDENTIFIED BY welcome;

Substitute your SYSTEM user password to connect.

Drop Queue Tables Containing Special User-Defined Types

Release 8.1.6 and higher creates JMS types for use in queue tables. These types are dropped automatically during downgrade. So, before downgrading, you must drop the queue tables containing these JMS types. To identify these queue tables, issue the following SQL statement:

SELECT owner, queue_table, object_type FROM all_queue_tables 
  WHERE object_type LIKE 'SYS.AQ$_JMS%';

To drop the queue tables listed, execute the DBMS_AQADM.DROP_QUEUE_TABLE procedure. For example, if a queue table named QTABLE1 owned by user SCOTT is listed, execute the following procedure to drop the queue table:

EXECUTE dbms_aqadm.drop_queue_table(queue_table => 'scott.qtable1',
  force => TRUE);
Drop or Convert Migrated Tablespaces

Release 8.1.6 and higher supports tablespace migration, which allows tablespaces to be migrated from dictionary managed format to locally managed format and vice versa. When a tablespace is migrated from dictionary managed to locally managed format, the tablespace is marked as a 'migrated tablespace' under certain conditions. You cannot downgrade to a previous release of Oracle if your database has such tablespaces.

To identify incompatible migrated tablespaces, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM dba_segments 
  WHERE segment_type = 'SPACE HEADER';

You can either convert the tablespaces listed to dictionary managed tablespaces, or you can drop them. To convert migrated tablespaces listed to dictionary managed tablespaces, execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL procedure. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_migrate_from_local('TS1'); 

To drop them, issue a DROP TABLESPACE statement. For example, if a tablespace named TS1 is listed, issue the following statement to drop the tablespace:

DROP TABLESPACE TS1 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS; 
Correct Transient Segments

During migration of tablespaces from dictionary managed format to locally managed format (and vice versa), the segments in the tablespace are temporarily put in a transient state. You cannot downgrade a database with these transient segments.

To identify transient segments, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM sys_dba_segs 
  WHERE DECODE(BITAND(segment_flags, 16), 16, 1 , 0) = 1; 

To correct the transient segments listed, execute the DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES procedure on the tablespace. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_fix_segment_states('TS1'); 
Dropping Segments in Optimized Locally Managed Tablespaces

In release 8.1.6 and higher, dropping segments in locally managed tablespaces is optimized by storing some additional information in the data dictionary. You cannot downgrade until all of the temporary segments in the locally managed tablespaces are dropped. You can check for these segments either by querying the V$COMPATSEG view or by attempting to start up the database with a COMPATIBLE initialization parameter setting lower than 8.1.6.

To query the V$COMPATSEG view, issue the following SQL statement:

SELECT * FROM v$compatseg;

The incompatible segments have FASTDROP in the TYPE_ID column and a value of 8.1.6.0.0 in the RELEASE column.

If you start up the database with a COMPATIBLE setting lower than 8.1.6 and there are incompatible segments, startup fails and error messages similar to the following are displayed:

ORA-00402: database changes by release 8.1.6.0.0 cannot be used by release 
8.1.5.0.0
ORA-00405: compatibility type "Faster segment drop"

When you reset database compatibility later in the downgrade process, Oracle will attempt to remove the temporary segments. If these segments are not removed the first time you reset compatibility, repeat the procedure to reset database compatibility.

See Also:

"Reset Database Compatibility" for instructions on resetting database compatibility. 

Continue with Your Downgrade to Release 8.1.5

After you remove all of the incompatibilities with release 8.1.5, go to "Reset Database Compatibility".

Removing Incompatibilities If You Are Downgrading to an 8.0 Release

If incompatibilities may exist with the 8.0 release to which you are downgrading, then use the following general procedure to remove these incompatibilities:

  1. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

  2. Start Server Manager. On most operating systems, enter svrmgrl at a command prompt to start Server Manager in Oracle8i.

  3. Connect to the database instance as INTERNAL user.

  4. Identify incompatibilities by completing the following steps:

    1. Query the V$COMPATIBILITY dynamic performance view to identify the incompatibilities:

      SVRMGR> SELECT * FROM v$compatibility WHERE release != '0.0.0.0.0';
      
      
      

      An incompatibility exists wherever the value in the RELEASE column is higher than the release to which you are downgrading.


      Note:

      This query does not show features with a compatibility level of 0.0.0.0.0. These features currently are not in use, and no action is required for them. 


    2. Run utlimcmpt.sql:

      SVRMGR> SPOOL utlincmp.out
      SVRMGR> @utlincmp.sql
      SVRMGR> SPOOL OFF
      
      

      The utlincmp.sql script runs all of the queries described in the rest of this chapter to identify incompatibilities. Therefore, you can perform all of the SELECT statements described in the rest of this chapter simply by running the utlincmp.sql script.

      After the utlincmp.sql script runs, view the utlincmp.out file and look for instances where a SELECT statement returned values. The values returned are incompatibilities with release 8.0.

  5. Drop or change all incompatibilities to make your database compatible with the release to which you are downgrading.

The following sections provide detailed information about removing incompatibilities with release 8.0. To remove incompatibilities, you may need to complete actions that require the privileges of SYS user. Therefore, you should log in as SYS user and connect as SYSDBA to perform the actions described in the following sections, unless instructed otherwise.

Also, if you created your database at 8.1.0 compatibility level or higher, then Oracle created certain system-defined types that are incompatible with 8.0 releases. To remove these incompatibilities, run the utldst.sql script supplied with release 8.1:

@utldst.sql


Note:

If you are downgrading from Oracle8i Enterprise Edition to Oracle8i (formerly Workgroup Server), then, before you downgrade, modify any applications that use the advanced features of Oracle8i Enterprise Edition so that they do not use these advanced features. See Getting to Know Oracle8i for more information about the differences between the editions. 


Tablespaces

This section describes removing incompatibilities relating to tablespaces.

Remove Transported Tablespaces

If you used the transportable tablespace feature to either move a tablespace into the database you are downgrading, or to transport a tablespace from this database to another database, then perform the following steps before downgrading:

  1. Identify the transported tablespaces that were plugged into the database by issuing the following SQL statement:

    SELECT tablespace_name, plugged_in
        FROM dba_tablespaces
        WHERE plugged_in = 'YES';
    
    
  2. Either drop or move each transported tablespace listed by the SQL statement.

    If you do not need to preserve the data in a transported tablespace, then drop the tablespace. If you need to preserve the data, then either export the data from your current database and import the data after you downgrade, or transport the tablespace to another database before you downgrade.

  3. Execute DBMS_TTS.DOWNGRADE:

    EXECUTE dbms_tts.downgrade;
    
    

    The DBMS_TTS.DOWNGRADE procedure drops the temporary tables in the system tablespace used by the transportable tablespace feature.

Drop or Convert Migrated Tablespaces

Release 8.1.6 and higher supports tablespace migration, which allows tablespaces to be migrated from dictionary managed format to locally managed format and vice versa. When a tablespace is migrated from dictionary managed to locally managed format, the tablespace is marked as a 'migrated tablespace' under certain conditions. You cannot downgrade to a previous release of Oracle if your database has such tablespaces.

To identify incompatible migrated tablespaces, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM dba_segments 
  WHERE segment_type = 'SPACE HEADER';

You can either convert the tablespaces listed to dictionary managed tablespaces, or you can drop them. To convert migrated tablespaces listed to dictionary managed tablespaces, execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL procedure. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_migrate_from_local('TS1'); 

To drop them, issue a DROP TABLESPACE statement. For example, if a tablespace named TS1 is listed, issue the following statement to drop the tablespace:

DROP TABLESPACE TS1 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS; 

Correct Transient Segments

During migration of tablespaces from dictionary managed format to locally managed format (and vice versa), the segments in the tablespace are temporarily put in a transient state. You cannot downgrade a database with these transient segments.

To identify transient segments, enter the following SQL statement:

SELECT DISTINCT(tablespace_name) FROM sys_dba_segs 
  WHERE DECODE(BITAND(segment_flags, 16), 16, 1 , 0) = 1; 

To correct the transient segments listed, execute the DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES procedure on the tablespace. For example, if a tablespace named TS1 is listed, execute the following procedure:

EXECUTE dbms_space_admin.tablespace_fix_segment_states('TS1'); 

Discontinue Use of Locally Managed Tablespaces

Release 8.1 supports locally managed tablespaces. Before you downgrade, you must convert all locally managed tablespaces to dictionary tablespaces.

To identify locally managed tablespaces, enter the following SQL statement:

SELECT tablespace_name, extent_management
    FROM dba_tablespaces 
    WHERE extent_management = 'LOCAL';

Run the DBMS_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL procedure on all tablespaces listed. For example, if a tablespace named TS_1 is listed, then enter the following SQL statement to convert TS_1 to a dictionary tablespace:

EXECUTE dbms_admin.tablespace_migrate_from_local('ts_1');

Dropping Segments in Optimized Locally Managed Tablespaces

In release 8.1.6 and higher, dropping segments in locally managed tablespaces is optimized by storing some additional information in the data dictionary. You cannot downgrade until all of the temporary segments in the locally managed tablespaces are dropped. You can check for these segments either by querying the V$COMPATSEG view or by attempting to start up the database with a COMPATIBLE initialization parameter setting lower than 8.1.6.

To query the V$COMPATSEG view, issue the following SQL statement:

SELECT * FROM v$compatseg;

The incompatible segments have FASTDROP in the TYPE_ID column and a value of 8.1.6.0.0 in the RELEASE column.

If you start up the database with a COMPATIBLE setting lower than 8.1.6 and there are incompatible segments, startup fails and error messages similar to the following are displayed:

ORA-00402: database changes by release 8.1.6.0.0 cannot be used by release 
8.0.5.0.0
ORA-00405: compatibility type "Faster segment drop"

When you reset database compatibility later in the downgrade process, Oracle will attempt to remove the temporary segments. If these segments are not removed the first time you reset compatibility, repeat the procedure to reset database compatibility.

See Also:

"Reset Database Compatibility" for instructions on resetting database compatibility. 

Schema Objects

This section describes removing incompatibilities relating to schema objects.

Drop Temporary Tables

Before you downgrade, drop all temporary tables. To identify existing temporary tables, issue the following SQL statement:

SELECT owner, table_name FROM dba_tables
    WHERE temporary = 'Y' AND
        table_name NOT LIKE 'RUPD$%' AND
        table_name NOT LIKE 'ATEMPTAB$';

Drop all tables listed.

Discontinue Use of Key Compression on Indexes and Index-Organized Tables

Before you downgrade, discontinue use of all indexes and index-organized tables with key compression in your database. To identify existing indexes and index-organized tables with key compression, issue the following SQL statement:

SELECT index_name, index_type, table_owner, table_name 
    FROM dba_indexes WHERE compression = 'ENABLED';

For each index listed, issue an ALTER INDEX ... REBUILD NOCOMPRESS statement. For example, if you have an index with key compression named I_JOB, then enter the following SQL statement:

ALTER INDEX i_job REBUILD NOCOMPRESS;

For all of the index-organized tables listed, issue an ALTER TABLE ... MOVE NOCOMPRESS statement. For example, if you have and index-organized table with key compression named IOT_ITEM, then issue the following SQL statement:

ALTER TABLE iot_item MOVE NOCOMPRESS;


Note:

The ALTER TABLE ... MOVE NOCOMPRESS statement is not allowed on nested table storage tables that are stored as index-organized tables. For each such nested table column, either drop the column, or recreate the parent table with a CREATE TABLE ... AS SELECT statement, specifying a heap storage table for the nested table column. The parent table is the table containing the nested table column. 


Discontinue Use of LOBs and Varrays in Index-Organized Tables

Before you downgrade, drop all index-organized tables with LOBs or varrays in your database. To identify existing index-organized tables with LOBs, issue the following SQL statement:

SELECT column_name, t.owner, t.table_name 
   FROM dba_lobs l, dba_tables t 
   WHERE l.table_name = t.table_name 
      AND l.owner = t.owner 
      AND t.iot_type = 'IOT'; 

To identify existing index-organized tables with varrays, issue the following SQL statement:

SELECT v.parent_table_column, t.owner, t.table_name 
    FROM dba_varrays v, dba_tables t 
    WHERE v.parent_table_name = t.table_name 
        AND v.owner = t.owner 
        AND t.iot_type = 'IOT'; 

If you do not need to preserve the data in the tables listed by these SQL statements, then drop the tables. However, if you need to preserve the data in any of these tables, then complete the following steps for each table:

  1. Create a new table that is not index-organized by selecting all rows from the index-organized table with LOBs or varrays.

    For example, assume you have an index-organized table with LOBs named LOBIOT with the following definition:

    CREATE TABLE lobiot (a INT, b CLOB, c INT PRIMARY KEY) ORGANIZATION INDEX;
    
    

    Issue the following SQL statement to create a table that is not index-organized named NIOTD2 using the data in LOBIOT:

    CREATE TABLE niotd2 (a,b,c PRIMARY KEY) AS SELECT * FROM lobiot;
    
    
  2. When you are sure the new table is functioning properly, drop the original index-organized table with LOBs or varrays.

  3. Rename the new table to its original name.

Drop All Secondary Indexes on Index-Organized Tables

Before you downgrade, drop all secondary indexes on index-organized tables in your database. To identify existing secondary indexes on index-organized tables, issue the following SQL statement:

SELECT index_name, i.owner, t.table_name 
   FROM dba_indexes i, dba_tables t  
   WHERE i.index_type = 'NORMAL' 
      AND i.table_name = t.table_name 
      AND t.owner = i.table_owner 
      AND t.iot_type = 'IOT'; 

Drop the indexes listed.

Drop Unused and Partially Dropped Columns

Before you downgrade, drop all unused and partially dropped columns.

Dropping Unused Columns

You will not be able to downgrade if any tables in your database have unused columns. To identify tables that have unused columns, issue the following SQL statement:

SELECT * FROM dba_unused_col_tabs;

To drop all of the unused columns in a table, use the ALTER TABLE ... DROP UNUSED COLUMNS statement. Run this statement for each table in the list. For example, to drop all of the unused columns in a table named CUSTOMERS, enter the following statement:

ALTER TABLE customers DROP UNUSED COLUMNS;
Dropping Partially Dropped Columns

You will not be able to downgrade if any tables in your database have partially dropped columns. To identify tables that have partially dropped columns, issue the following SQL statement:

SELECT * FROM dba_partial_drop_tabs;

To drop all of the partially dropped columns in a table, use the ALTER TABLE ... DROP COLUMNS CONTINUE statement. Run this statement for each table in the list. For example, to drop all partially dropped columns in a table named CUSTOMERS, enter the following statement:

ALTER TABLE customers DROP COLUMNS CONTINUE;

Drop All Single-Table Hash Clusters

You must drop all single-table hash clusters before you downgrade. To check for single table-only hash clusters, issue the following SQL statement:

SELECT cluster_name, single_table FROM dba_clusters
    WHERE single_table='Y'; 

Drop all of the clusters listed.

Drop Incompatible Materialized Views

Identify materialized views that are incompatible with release 8.0 by issuing the following SQL statement:

SELECT mv.owner, mv.name
   FROM dba_snapshots mv, dba_mview_analysis mva
   WHERE mva.owner = mv.owner
   AND mva.mview_name = mv.name;

Drop all of the materialized views listed. For example, if a materialized view owned by SCOTT and named MV_1 is listed, then issue the following SQL statement to drop the materialized view:

DROP MATERIALIZED VIEW scott.mv_1; 

Identify Materialized Views That Will Be Changed or Dropped During Downgrade

The following sections provide instructions for identifying materialized views that will be changed or dropped during the downgrade process described in "Downgrade the Database".


Note:

The word "materialized view" is synonymous with the word "snapshot". 


REFRESH ON COMMIT Mode Changed to REFRESH ON DEMAND Mode

Release 8.1 enables you to use the REFRESH ON COMMIT mode for materialized views, but this mode is not available in release 8.0. To identify the materialized views in REFRESH ON COMMIT mode, issue the following SQL statement:

SELECT owner, name, refresh_mode 
    FROM dba_snapshots
    WHERE refresh_mode = 'COMMIT';

All of the materialized views listed are in REFRESH ON COMMIT mode. When you downgrade, these materialized views will be changed to REFRESH ON DEMAND mode automatically.

FAST REFRESH Mode Unavailable After Downgrade

Materialized views that use joins or the GROUP BY clause (aggregate queries) can no longer use the FAST REFRESH mode after you downgrade.

NEVER REFRESH Mode Materialized Views Dropped

Release 8.1 enables you to use the NEVER REFRESH mode for materialized views, but this mode is not available in release 8.0. To identify the materialized views in NEVER REFRESH mode, issue the following SQL statement:

SELECT owner, name, type 
    FROM dba_snapshots
    WHERE type = 'NEVER';

All of the materialized views listed are in NEVER REFRESH mode. When you downgrade, these materialized views will be dropped automatically.

Materialized Views Created with the PREBUILT TABLE Clause Dropped

Release 8.1 enables you to use the PREBUILT TABLE clause to create materialized views, but these views are not supported in release 8.0. Any views created with the PREBUILT TABLE clause will be dropped automatically when you downgrade.

Materialized Views Created without a View

Any materialized views created without a view will be dropped automatically when you downgrade.

Materialized Views Created with the BUILD DEFERRED Clause Refreshed

When you downgrade, complete refresh will be performed automatically on any views created with the BUILD DEFERRED clause.

Mutually Referencing Views and Downgrading to Release 8.0.4.1 or Lower

If you have mutually referencing views, and you are downgrading to release 8.0.4.1 or lower, then you must drop these views. If you do not have mutually referencing views, or if you are downgrading to release 8.0.4.2 or higher, then skip this section.


Note:

You can either drop the mutually referencing views before you downgrade or after you downgrade. They will not affect the downgrade operation 


Mutually referencing views are not supported in release 8.0.3. If you are downgrading to release 8.0.3, then drop all mutually referencing views.

Mutually referencing views are supported in release 8.0.4.0 and higher. However, you still must drop these views if you are downgrading to release 8.0.4.0 or 8.0.4.1. After you downgrade, you can recreate the previously dropped mutually referencing views. This action is required because of bug #662863, which is present in release 8.0.4.0 and 8.0.4.1, but is corrected in release 8.0.4.2 and higher.

Mutually referencing views are views in which the object views refer to each other through the MAKE_REF operator. In the following example of mutually referencing views, HUSBAND and WIFE types have references to each other, and object views were created with MAKE_REF operators:

CREATE TYPE husband
/ 

CREATE TYPE wife AS object 
    (id2 NUMBER,  
    name2 CHAR(10),  
    salary number,  
    buddy2 REF husband)
/  

CREATE OR replace TYPE husband AS object 
    (id NUMBER,  
    name CHAR(10),  
    buddy REF wife)
/  

CREATE TABLE husbandtab  
    (id NUMBER,  
    name CHAR(10),  
    buddy NUMBER);  

CREATE TABLE wifetab  
    (id2 NUMBER,  
    name2 CHAR(10),  
    salary NUMBER,  
    buddy2 NUMBER);  

CREATE VIEW husbandview OF husband  
    WITH object OID(id) AS  
    SELECT id, name, NULL FROM husbandtab;  

CREATE VIEW wifeview OF wife WITH object OID(id2) AS  
    SELECT id2, name2, salary,  
    MAKE_REF(husbandview, buddy2)  
    FROM wifetab;  

CREATE OR replace VIEW husbandview  
    OF husband WITH object OID(id) AS  
    SELECT id, name, MAKE_REF(wifeview, buddy)  
    FROM husbandtab;  

Drop Bitmap Indexes That Are Incompatible with Release 8.0

Release 8.1 provides protections for bitmap indexes. These protections prevent bitmap indexes from being unintentionally invalidated.

See Also:

"Bitmap Index Protection" for information. 

When you downgrade to release 8.0, any bitmap indexes that were protected by this new feature must be dropped. To list the indexes these bitmap indexes, issue the following SQL statement:

SELECT o1.name INDEX_NAME, u1.name 
   FROM obj$ o1, user$ u1 
   WHERE o1.obj# IN (SELECT i.obj# FROM user$ u, obj$ o, ind$ i, tab$ t 
      WHERE t.obj#=o.obj# AND i.bo#=t.obj# AND t.spare1>32767 AND i.type#=2 
      AND o.owner#=u.user#) 
   AND o1.owner#=u1.user#;

To drop all of the indexes listed, use a DROP INDEX statement. For example, to drop a bitmap index named BIT1, issue the following SQL statement:

DROP INDEX bit1;

Issue this statement for each bitmap index listed.

Also, run the ALTER TABLE table_name NOMINIMIZE RECORDS_PER_BLOCK. For example, if the SCOTT.EMP table had an incompatible bitmap index, issue the following statement:

ALTER TABLE scott.emp NOMINIMIZE RECORDS_PER_BLOCK;

Drop Function-Based Indexes

You will not be able to downgrade if your database has any function-based indexes. To identify function-based indexes, issue the following SQL statement:

SELECT DISTINCT index_owner, index_name FROM dba_ind_columns
    WHERE column_name IS NULL;

To drop all of the function-based indexes, use a DROP INDEX statement. For example, to drop a function-based index named FUNCIN1, issue the following SQL statement:

DROP INDEX funcin1;

Issue this statement for each function-based index listed.

Discontinue Use of Extensible Indexing

Release 8.1 supports extensible indexing. This feature enables the creation of domain indexes, indextypes, and operators. Before you downgrade, you must drop these objects.

Identifying and Dropping Domain Indexes

To identify domain indexes, issue the following SQL statement:

SELECT owner, index_name, index_type 
    FROM dba_indexes 
    WHERE index_type = 'DOMAIN';

To drop all of the indexes listed, use a DROP INDEX statement. For example, to drop a domain index named DOM1, issue the following SQL statement:

DROP INDEX dom1;

Issue this statement for each domain index listed.

Identifying and Dropping Indextypes

To identify indextypes, issue the following SQL statement:

SELECT owner, indextype_name FROM dba_indextypes;

To drop the indextypes listed, use a DROP INDEXTYPE statement. For example, if an indextype named IX_TYPE owned by USER2 is listed, then issue the following SQL statement to drop the indextype:

DROP INDEXTYPE user2.ix_type;
Identifying and Dropping Operators

To identify operators, issue the following SQL statement:

SELECT owner, operator_name FROM dba_operators;

To drop the operators listed, use a DROP OPERATOR statement. For example, if an operator named OP1 owned by USER3 is listed, then issue the following SQL statement to drop the operator:

DROP OPERATOR user3.op1;

Drop All Dimensions

Before you downgrade, you must drop all dimensions. Dimensions are not supported in release 8.0.

To identify the dimensions that must be dropped, issue the following SQL statement:

SELECT * FROM dba_dimensions;

To drop the dimensions listed, use a DROP DIMENSION statement. For example, if a dimension named DIM1 owned by USER4 is listed, then issue the following SQL statement to drop the operator:

DROP OPERATOR user4.dim1;

Partitioning

This section describes disabling release 8.1 partitioning features.

Discontinue Use of Partitioned Index-Organized Tables

Before you downgrade, drop all partitioned index-organized tables in your database. To identify existing partitioned index-organized tables, issue the following SQL statement:

SELECT table_name, tablespace_name, iot_type, partitioned 
    FROM dba_tables WHERE partitioned = 'YES' AND iot_type = 'IOT';

If you do not need to preserve the data in the tables listed, then drop the tables. However, if you need to preserve the data in a table, then complete the following steps for the table:

  1. Drop all partitioned secondary indexes on the table.

  2. Create a new table that is either not index-organized or not partitioned by selecting all rows from the partitioned index-organized table.

    For example, assume you have a partitioned index-organized table named PIOT with the following definition:

    CREATE TABLE piot (a int, b int, c int, d int, e int, 
        PRIMARY KEY (d,e)) ORGANIZATION INDEX
        PARTITION BY RANGE (d) 
        ( 
            PARTITION itp1 VALUES LESS THAN (15), 
            PARTITION itp2 VALUES LESS THAN (30), 
            PARTITION itp3 VALUES LESS THAN (MAXVALUE)
        );
    
    

    Create a non-partitioned index-organized table named IOT using the data in PIOT by issuing the following SQL statement:

    CREATE TABLE iot (a, b, c, d, e, 
        PRIMARY KEY (d,e)) ORGANIZATION INDEX 
        AS SELECT * FROM piot;
    
    

    Or, if you want to keep the partitions but not the index organization, then create a partitioned table that is not index-organized named PAR using the data in PIOT by issuing the following SQL statement:

    CREATE TABLE par (a, b, c, d, e, 
        PRIMARY KEY (d,e)) PARTITION BY RANGE (d) 
        ( 
            PARTITION itp1 VALUES LESS THAN (15), 
            PARTITION itp2 VALUES LESS THAN (30), 
            PARTITION itp3 VALUES LESS THAN (MAXVALUE) 
        ) AS SELECT * FROM piot;
    
    
  3. When you are sure the new table is functioning properly, drop the partitioned index-organized table.

  4. Rename the new table to its original name.

Discontinue Use of Partitioned Object Tables

Release 8.1 supports the partitioning of object tables and tables with the following user-defined types:

Before you downgrade, drop all partitioned object tables. To identify all partitioned object tables, issue the following SQL statement:

SELECT UNIQUE t.table_name, t.owner
    FROM dba_part_tables t, dba_tab_columns c
    WHERE t.table_name = c.table_name
    AND c.data_type IN
    (SELECT type_name
        FROM dba_types
        WHERE predefined = 'NO');

If you do not need to preserve the data in the tables listed, then drop the tables. However, if you need to preserve the data in one or more of the tables listed, then use the CREATE TABLE ... AS SELECT statement to copy the data in a table to a non-partitioned table.

For example, if a table named OBP1 is listed by the SQL statement, and you want to save the data in this table, then complete the following steps:

  1. Create a table named TEMP_OBP1 by issuing the following SQL statement:

    CREATE TABLE temp_obp1 AS SELECT * FROM obp1;
    
    
  2. When you are sure that table TEMP_OBP1 has the data and is functioning properly, drop OBP1:

    DROP TABLE obp1;
    
    
  3. Rename TEMP_OBP1 to OBP1:

    ALTER TABLE temp_obp1 RENAME TO obp1;
    

Discontinue Use of Partitioned Tables That Use Composite Methods

Release 8.1 supports the creation of partitioned tables using composite methods and non-composite methods other than RANGE. If you have any such tables in your database, then you must perform one of the following actions:

To list the tables partitioned with composite methods and non-composite methods other than RANGE, issue the following SQL statement:

SELECT owner, table_name FROM dba_part_tables
    WHERE partitioning_type != 'RANGE' or SUBPARTITIONING_TYPE != 'NONE';

Datatypes

This section describes disabling datatypes that are available only in release 8.1 and higher.

Drop All Uses of the Universal ROWID Datatype

Complete the procedures in the following sections to remove all uses of the UROWID (universal rowid) datatype.

Drop All UROWID Columns

To list all of the tables with UROWID datatype columns, issue the following SQL statement:

SELECT owner, table_name, column_name FROM dba_tab_columns
    WHERE data_type = 'UROWID' ORDER BY owner, table_name;

For each table listed as a result of this statement, drop its UROWID datatype columns, or drop the whole table.

Drop All Stored Procedures with UROWID Arguments

To list all stored procedures with arguments of UROWID datatype, issue the following SQL statement:

SELECT owner, object_name, package_name, argument_name 
    FROM all_arguments 
    WHERE data_type = 'UROWID' AND package_name != 'STANDARD'
    ORDER BY owner, object_name, package_name;

Drop each of the procedures listed, or change the argument to ROWID datatype.

Drop Existing Chained Row and Exception Tables

In release 8.1, the UROWID datatype enables you to use a single table for chained rows and a single table for exceptions, but this functionality is not supported in release 8.0 databases. Therefore, you must prepare multiple tables for both chained rows and exceptions because you need at least one table for all regular tables and at least one table for each index-organized table.

Complete the following steps to downgrade a chained rows table called CH_ROWS:

  1. Drop the existing CH_ROWS table.

  2. Recreate the CH_ROWS table using the utlchain.sql script to store chained rows for the regular tables.

  3. Create an individual chained table for each index-organized table using the DBMS_IOT.BUILD_CHAIN_ROWS_TABLE procedure.

Complete the following steps to downgrade an exception table called EXC_TB:

  1. Drop the existing EXC_TB table.

  2. Recreate the EXC_TB table using the utlexcp.sql script to store exceptions for the regular tables.

  3. Create an individual exception table for each index-organized table using the DBMS_IOT.BUILD_EXCEPTIONS_TABLE procedure.

Discontinue Use of Release 8.1 LOB Features

Release 8.1 supports several new LOB features. Before you downgrade, discontinue the use of these new features by performing the actions described in the following sections.

Discontinue Use of CACHE READS Specification for LOBs

Before you downgrade to release 8.0, you must stop using the CACHE READS storage parameter for LOBs.

To identify tables that have LOB columns specified as cache reads, issue the following SQL statement as SYS user:

SELECT owner, table_name, column_name 
  FROM dba_lobs 
  WHERE cache = 'CACHEREADS'; 
  

After you have identified all of the uses of the CACHE READS specification for LOBs, change them to use the CACHE or NOCACHE specification, because these specifications are compatible with release 8.0.

To change a LOB storage parameter from CACHE READS to CACHE or NOCACHE, use the ALTER TABLE ... MODIFY LOB statement. For example, on a table named LOB_TAB with a LOB column named LOB_COL, issue the following SQL statement to change the storage specification to CACHE:

ALTER TABLE lob_tab MODIFY LOB (lob_col) (CACHE); 
Remove CLOBs and NCLOBs from Tables in a Database with a Varying-Width Character Set

If your database is using a varying-width character set, then remove all CLOB and NCLOB columns by completing the following steps. You do not need to complete this procedure if your database has a fixed-width character set.

  1. List all of the tables that contain LOB columns by issuing the following SQL statement:

    SELECT owner, table_name, column_name FROM dba_lobs 
        WHERE dba_lobs.owner != 'SYSTEM'
        AND table_name NOT IN ('KOTAD$', 'KOTMD$', 'KOTTB$', 'KOTTD$');
    
    
  2. Determine whether your database contains CLOB or NCLOB columns by running DESC on each of the tables listed; check the TYPE column when you run DESC.

  3. Either drop the CLOB and NCLOB columns, or drop each table that contains the columns.


    Caution:

    Check the tables created by SYS carefully, and make sure you do not drop tables that are required for version 8; that is, do not drop the tables that include a symbol in the name. Examples of tables that you should not drop include: kotad$, kotmd$, kottb$, and kottd$. 


  4. If you use Oracle replication, then complete the following steps. If you do not use Oracle replication, then these steps are not required.

    1. Push the replication deferred transaction queue using SQL*Plus:

      DECLARE  
          rc number; 
      BEGIN 
          rc := dbms_defer_sys.push(); 
      END; 
      
      
    2. Drop the deferred LOB view by issuing the following SQL statement:

      DROP VIEW deflob; 
      
      

      See Also:

      Oracle8i Replication Management API Reference for more information about completing these replication steps. 

Remove LOB Columns from Partitioned Tables

Before you downgrade, remove all LOB columns from partitioned tables. To determine if your database contains LOB columns in partitioned tables, issue the following SQL statement:

SELECT table_name, lob_name FROM dba_part_lobs;

If you do not need to preserve your LOB data in partitioned tables, then drop the LOB columns. However, if you need to preserve your LOB data in partitioned tables, then use the ALTER TABLE ... EXCHANGE PARTITION statement to move the data into non-partitioned tables, as illustrated in the following example:

Assume you have an existing partitioned table with a LOB column, and the LOB column already contains data that you want to save before downgrading from release 8.1 to release 8.0. The partitioned table has the following definition:

CREATE TABLE part_lob_table (part_id NUMBER, part_blob_col BLOB) 
    PARTITION BY RANGE (part_id) (
    PARTITION p1 VALUES LESS THAN (10) TABLESPACE ts1, 
    PARTITION p2 VALUES LESS THAN (20) TABLESPACE ts2) 
    TABLESPACE tsx; 

Complete the following steps to move the LOB data into non-partitioned tables:

  1. Create non-partitioned tables with a LOB column by issuing statements similar to the following:

    CREATE TABLE lob_table_p1 (id NUMBER, blob_col BLOB);
    
    CREATE TABLE lob_table_p2 (id NUMBER, blob_col BLOB);
    
    

    Create one table for each partition that is in the partitioned LOB table, but do not insert any data into these new non-partitioned tables.

  2. Use EXCHANGE to swap the partitioned table's LOB data from the PART_BLOB_COL column with the non-partitioned tables' LOB data in the BLOB_COL column:

    ALTER TABLE part_lob_table 
        EXCHANGE PARTITION p1 WITH TABLE lob_table_p1; 
    
    ALTER TABLE part_lob_table 
        EXCHANGE PARTITION p2 WITH TABLE lob_table_p2;
    
    

    These statements move the data from the LOB column PART_BLOB_COL in the partitioned table to the LOB column BLOB_COL in each non-partitioned table.

    After you have moved all of the LOB data in partitioned tables to non-partitioned tables, you can downgrade your database and preserve the data.

User-Defined Datatypes

This section describes disabling release 8.1 features related to user-defined datatypes.

Convert User-Defined Datatypes to Release 8.0 Format

Release 8.1 supports a new format for user-defined datatypes. The new format can result in significant performance improvements over the format used in release 8.0.

When you downgrade your database to release 8.0, you must convert your user-defined datatypes to the release 8.0 format. However, if your release 8.1 database has no user-defined datatypes in the new format, then you do not need to perform the conversion procedure below.

To identify the user-defined types at 8.1 compatibility level, issue the following SQL statement:

SELECT u.name AS USER_NAME, o.name AS TABLE_NAME, c.name AS COLUMN_NAME 
   FROM sys.user$ u, sys.obj$ o, sys.tab$ t, sys.col$ c, sys.coltype$ ct 
   WHERE bitand(ct.flags, 128) != 128 AND
      o.obj# = c.obj# and o.obj# = ct.obj# and t.obj# = o.obj# and 
      c.intcol# = ct.intcol# AND 
      bitand(t.property, 8192) = 0 AND                  
      u.user# = o.owner# AND 
      o.type# = 2 AND                                 
      bitand(c.property, 32) = 0 AND                   
        (c.type# = 123 OR                                                                                   
          (c.type# = 121 and bitand(c.property, 8) = 0) OR 
          (c.type# = 122 and exists  
          (SELECT * FROM sys.ntab$ n1, sys.col$ c1, sys.coltype$ ct1 
           WHERE n1.obj# = c.obj# AND n1.intcol# = c.intcol# AND  
                 n1.ntab# = ct1.obj# and bitand(ct1.flags, 128) = 0 AND 
                 ct1.obj# = c1.obj# and ct1.intcol# = c1.intcol# AND 
                 bitand(c1.property, 8) = 0)));

To downgrade the user-defined datatypes listed, complete the following steps:

  1. Export the parts of your release 8.1 database that contain user-defined types at 8.1 compatibility level using the release 8.1 Export utility.

  2. Drop the parts of your release 8.1 database that contain user-defined datatypes at 8.1 compatibility level.

  3. Reset the compatibility of your database to an 8.0.x release, such as 8.0.5, by following the instructions in "Reset Database Compatibility". You may need to remove other incompatibilities in your database before you can reset the compatibility level to an 8.0.x release.

  4. Import the exported file into the downgraded database using the release 8.1 Import utility.

    See Also:

    Oracle8i Utilities for Export/Import instructions. 

Drop Tables With User-Defined Object Identifiers

Release 8.1 supports user-defined object identifiers (OIDs). This functionality enables you to specify your own object identifiers instead of using Oracle's default mechanism for specifying these identifiers. Before you downgrade, drop all tables that have user-defined object identifiers and all tables with REF columns that are based on user-defined object identifiers.

To identify tables with user-defined object identifiers, issue the following SQL statement:

SELECT owner, table_name FROM dba_object_tables
    WHERE object_id_type = 'USER-DEFINED';

Drop all tables listed.

To identify tables with REF columns that are based on user-defined object identifiers, issue the following SQL statement:

SELECT owner, table_name, column_name FROM dba_refs
    WHERE object_id_type = 'USER-DEFINED';

Drop all tables listed.

Referential Constraints on System-Generated REF Columns

Release 8.0 does not support referential constraints defined on system-generated REF columns. To identify such columns, issue the following SQL statement:

SELECT u.name AS USER_NAME, 
  o.name AS TABLE_NAME,
  DECODE(BITAND(c.property, 1), 1, ac.name, c.name) AS COLUMN_NAME
  FROM refcon$ r, coltype$ ct, obj$ o, user$ u, col$ c, attrcol$ ac
  WHERE BITAND(r.reftyp, 4) != 4   /* not a user-defined REF */
    AND ct.obj# = r.obj#
    AND ct.intcol# = r.intcoL#
    AND ct.intcols = 1
    AND c.obj# = r.obj#
    AND c.intcol# = r.intcol#
    AND o.obj# = r.obj#
    AND o.owner# = u.user#
    AND c.obj# = ac.obj#(+)
    AND c.intcol# = ac.intcol#(+);

If columns are returned by this query, then perform one of the following actions to remove incompatibilities with release 8.0:

Discontinue Use of Release 8.1 Nested Table Features

Before you downgrade, discontinue use of the following release 8.1 nested table features:

To identify tables that use one or more of these features, issue the following SQL statement:

SELECT owner, parent_table_name
    FROM dba_nested_tables  
    WHERE storage_spec LIKE '%USER_SPECIFIED%' 
    OR return_type LIKE '%LOCATOR%';

Drop all of the tables listed.

Discontinue Use of Release 8.1 Varray Features

Before you downgrade, discontinue use of specifications of storage parameters for storing varrays as LOBs. To identify tables that use storage parameters for storing a varray as a LOB, issue the following SQL statement:

SELECT owner, parent_table_name
    FROM dba_varrays  
    WHERE storage_spec LIKE '%USER_SPECIFIED%';

Drop all of the tables listed.

Mutually Referencing Types and Downgrading to Release 8.0.4.0 or Lower

If you are using mutually referencing types, then downgrading to release 8.0.3.0 or 8.0.4.0 is not supported. You have two options for downgrading if you are using mutually referencing types:

The following SQL statements provide an example of mutually referencing types:

CREATE TYPE manager
/ 

CREATE TYPE employee AS OBJECT  
    (empno NUMBER, ename VARCHAR2(20), mgr REF manager)
/ 

CREATE OR REPLACE TYPE manager AS OBJECT  
    (dept NUMBER, empno REF employee)
/ 


Note:

Due to bug #629468, which exists in release 8.0.3.0 and 8.0.4.0, but is fixed in release 8.0.4.1 and higher, the existence of mutually referencing types causes the compilation of package STANDARD to enter a loop and exit with error ORA-01000: "maximum open cursors exceeded". Package STANDARD is required for compilation of PL/SQL code, and is run during a downgrade operation. This bug causes the downgrade to fail. 


SQL and PL/SQL

Release 8.1 introduces many changes and additions to SQL and PL/SQL. If you currently use any SQL or PL/SQL code in a script or stored procedure that is available only in release 8.1 and higher, then remove this code before you downgrade. You will encounter errors if you try to compile or run the code on a release 8.0 database.

See Also:

Getting to Know Oracle8i, Oracle8i SQL Reference, and PL/SQL User's Guide and Reference for information about new SQL and PL/SQL functionality. Also see Appendix E, "New Internal Datatypes and SQL Functions" in this book. 

The following sections describe specific SQL and PL/SQL downgrading issues. The actions described in these sections help you to avoid compile and runtime errors in SQL scripts and stored procedures. Although these actions are not strictly required, Oracle Corporation recommends that you perform them before you downgrade.

Remove C Call Specifications

Before you downgrade, remove stored procedures defined as C call specifications.

Remove Invoker-Rights Clauses

If you use invoker-rights clauses in your SQL code, then remove them before you downgrade. Invoker-rights clauses include the AUTHID clause and the SQL_NAME_RESOLVE clause.

Remove Native Dynamic SQL in PL/SQL

PL/SQL programs using native dynamic SQL will cause compile-time errors in releases prior to PL/SQL release 8.1. Before you downgrade, delete all native dynamic SQL syntax in order to compile your programs successfully in release 8.0.

Remove Bulk Binds in PL/SQL

PL/SQL programs using the bulk binds feature will cause compile-time errors in releases prior to PL/SQL 8.1. The bulk binds feature defines new syntax and semantics; thus, the programs containing this feature must be deleted, or, whenever possible, modified to use the scalar binds. PL/SQL statements that use the bulk binds feature contain one or more of the following keywords:

Remove the UROWID Datatype in PL/SQL

If you are using the UROWID datatype as a variable in PL/SQL code, then remove this variable before you downgrade.

Delete References to NOCOPY Parameter Passing Mode in PL/SQL

PL/SQL programs using NOCOPY mode will cause compile-time errors in releases prior to PL/SQL 8.1. Before you downgrade, delete references to NOCOPY in order to compile your programs successfully in release 8.0. When you delete references to NOCOPY, make sure the changed aliasing and exception semantics are acceptable.

Java

Java support is not available in release 8.0. Before you downgrade, you must drop all Java objects in your database. The utljavrm.sql script drops all Java objects. To identify the Java objects dropped by the utljavrm.sql script, issue the following SQL statement:

SELECT object_name, owner FROM all_objects WHERE object_type LIKE 'JAVA%';

To run the utljavrm.sql script, complete the following steps:

  1. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

  2. Start SQL*Plus and connect as a user with SYS privileges.

  3. Enter the following:

    SQL> SPOOL utljavrm.out
    SQL> @utljavrm.sql
    SQL> SPOOL OFF
    
    

    Check the spool file and verify that the statements executed successfully.

Advanced Queuing (AQ)

Complete the following tasks to disable release 8.1 AQ features in your queue tables:

  1. Drop all non-persistent queues and queue tables.

  2. Identify the release 8.1 compatible queue tables.

  3. Remove the incompatibilities from the release 8.1 compatible queue tables.

  4. Downgrade the queue tables.

  5. Export the queue tables and import them after downgrading.

  6. Drop queue tables containing special user-defined types.

  7. If you are downgrading to release 8.0.3.0, then remove propagation.

These steps are described in detail in the following sections.

See Also:

Oracle8i Application Developer's Guide - Advanced Queuing for more information about completing the actions described in these sections. 

Task 1: Drop All Non-persistent Queues and Queue Tables

If you are using any non-persistent queues, then you must drop these queues and the queue tables that contain them. For every schema (user) that has non-persistent queues, there may be one or two queue tables that contain all the non-persistent queues for that schema. To check for the existence of queue tables that contain non-persistent queues, enter the following SQL statement:

SELECT owner, queue_table FROM dba_queue_tables
    WHERE queue_table = 'AQ$_MEM_MC' OR queue_table = 'AQ$_MEM_SC';

For every queue table returned by the SQL statement, use the DBMS_AQADM.DROP_QUEUE_TABLE procedure (with the FORCE parameter set to TRUE) to drop all of the non-persistent queues and the corresponding queue table. The following is an example of the procedure:

EXECUTE dbms_aqadm.drop_queue_table (
    queue_table => 'SCOTT.AQ$_MEM_MC', 
    force => TRUE);

Task 2: Identify the Release 8.1 Compatible Queue Tables

If any of your queue tables are release 8.1 compatible, then you must downgrade them. To check the compatibility of your queue tables, enter the following SQL statement:

SELECT owner, queue_table, compatible FROM dba_queue_tables
    WHERE compatible LIKE '8.1%';

The listed queue tables are release 8.1 compatible and have incompatibilities with release 8.0 that must be removed before you downgrade. Print a list of the queue tables that are release 8.1 compatible. You will need to downgrade these queue tables when you reach "Task 4: Downgrade the Queue Tables".


Note:

This query does not list queue tables that were at release 8.1 compatibility and then downgraded back to release 8.0 compatibility. However, if you have any such queue tables, then you must drop them before you downgrade to release 8.0. Follow the instructions in "Task 5: Export the Queue Tables and Import Them After Downgrading" for these queue tables. 


Task 3: Remove Incompatibilities from the Release 8.1 Compatible Queue Tables

Your queue tables may have many incompatibilities. These incompatibilities are caused by the use of certain features that are available on release 8.1 but not on release 8.0.

The following sections provide instructions for removing these incompatibilities based on the release 8.1 features in use.

Rule Based Subscriptions

Use the AQ$queue_table_name_r view to identify queues that use release 8.1 rule based subscription functionality. Perform the check for all of the release 8.1 compatible queue tables listed in Task 2. For example, if a queue is named QTABLE3, then issue the following SQL statement to check for rule based subscribers:

SELECT * FROM aq$qtable3_r;


Note:

If you receive the error "ORA-04063: view 'aq$queue_table_name_r' has errors" when you issue this SQL statement, then the queue table does not contain any queues with rule-subscribers. 


Either drop the rule based subscribers, or change the rule for each rule based subscriber to null using the DBMS_AQADM.ALTER_SUBSCRIBER procedure. For example, suppose you have a subscriber for a queue named AQ.MSG_QUEUE with the values shown in Table 13-1:

Table 13-1 Sample Subscriber Values
Parameter  Value 

name 

SUBSCRIBER1 

address 

AQ2.MSG_QUEUE2@LONDON 

protocol 

NULL 

rule 

'PRIORITY = 1' 

You can change the rule to NULL for this subscriber in two different ways: using a PL/SQL block or using a SQL statement. Example 13-1 shows the PL/SQL block, and Example 13-2 shows the SQL statement.

Example 13-1 PL/SQL Block for Changing a Rule to NULL

DECLARE 
    subscriber sys.aq$_agent; 
BEGIN 
    subscriber := sys.aq$_agent ('SUBSCRIBER1', 'aq2.msg_queue2@london', null); 
    dbms_aqadm.alter_subscriber 
        (queue_name => 'aq.msg_queue', 
        subscriber => subscriber, 
        rule => NULL); 
END; 

Example 13-2 SQL Statement for Changing a Rule to NULL

EXECUTE dbms_aqadm.alter_subscriber (
    'aq.msg_queue', 
    sys.aq$_agent ('SUBSCRIBER1', 'aq2.msg_queue2@london', NULL), 
    NULL); 

See Also:

Oracle8i Application Developer's Guide - Advanced Queuing and Oracle8i Supplied PL/SQL Packages Reference for more information about the DBMS_AQADM.ALTER_SUBSCRIBER procedure. 

Object Level and System Level Privileges

You are using object level and system level privileges if you used any of the following procedures in the DBMS_AQADM package:

If you used any of these procedures, then all object level and system level privileges must be revoked before you downgrade.

To identify the object level privileges, issue the following SQL statement:

SELECT owner, table_name, privilege
    FROM dba_tab_privs WHERE privilege LIKE '%QUEUE%';

Use the DBMS_AQADM.REVOKE_ACCESS_PRIVILEGES procedure to revoke each privilege with ENQUEUE or DEQUEUE in the PRIVILEGE column.

To identify the system level privileges, issue the following SQL statement:

SELECT * FROM dba_sys_privs WHERE privilege LIKE '%QUEUE%';

Use the DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE procedure to revoke each privilege with any of the following types of privileges listed in the PRIVILEGE column:

Task 4: Downgrade the Queue Tables

Complete the following steps to downgrade each queue table that was incompatible with release 8.0. You listed all of the incompatible queue tables in "Task 2: Identify the Release 8.1 Compatible Queue Tables".

  1. Before you downgrade, disable all propagation schedules for all queues in the queue table using DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE.

  2. Downgrade the incompatible queue tables back to release 8.0 compatibility.

    To downgrade a queue table, run the DBMS_AQADM.MIGRATE_QUEUE_TABLE procedure and specify 8.0 for the COMPATIBLE setting. The following example illustrates running the this procedure:

    EXECUTE dbms_aqadm.migrate_queue_table (
        queue_table => 'sys.tkaqqtdef',
        compatible => '8.0');
    
    


    Note:

    Only the owner of a queue table can run the DBMS_AQADM.MIGRATE_QUEUE_TABLE procedure on the queue table. 


Task 5: Export the Queue Tables and Import Them After Downgrading

Complete the following steps to export the incompatible queue tables and import them after downgrading. You listed all of the incompatible queue tables in "Task 2: Identify the Release 8.1 Compatible Queue Tables".

  1. Using the release 8.1 Export utility, export all incompatible queue tables.

  2. Drop all the queue tables that have been exported.

  3. Reset the compatibility of your database to an 8.0.x release, such as 8.0.5, by following the instructions in "Reset Database Compatibility". You may need to remove other incompatibilities in your database before you can reset the compatibility level to an 8.0.x release.

  4. Import the exported file into the database using the release 8.1 Import utility.

Repeat these steps for every incompatible queue table.

See Also:

Oracle8i Application Developer's Guide - Advanced Queuing for information about exporting and importing queue tables. 

Task 6: Drop Queue Tables Containing Special User-Defined Types

Release 8.1.6 and higher creates JMS types for use in queue tables. These types are dropped automatically during downgrade. So, before downgrading, you must drop the queue tables containing these JMS types. To identify these queue tables, issue the following SQL statement:

SELECT owner, queue_table, object_type FROM all_queue_tables 
  WHERE object_type LIKE 'SYS.AQ$_JMS%';

To drop the queue tables listed, execute the DBMS_AQADM.DROP_QUEUE_TABLE procedure. For example, if a queue table named QTABLE1 owned by user SCOTT is listed, execute the following procedure to drop the queue table:

EXECUTE dbms_aqadm.drop_queue_table(queue_table => 'scott.qtable1',
  force => TRUE);

Task 7: If You Are Downgrading to Release 8.0.3, Then Remove Propagation

If you are using message propagation in the Advanced Queuing Option, and you are downgrading to release 8.0.3, then remove propagation. If you are not using message propagation, or if you are downgrading to release 8.0.4 or higher, then skip this task.

Complete the following steps to remove propagation:

  1. Identify your multi-consumer queues by issuing the following SQL statement:

    SELECT owner, queue_table FROM dba_queue_tables
        WHERE recipients = 'MULTIPLE';
    
    

    Save the results of this query. Propagation is supported only from multi-consumer queues. If this query does not return any rows, then propagation is not in use and you can skip the remaining steps in this procedure.

  2. Determine if any of the multi-consumer queues are utilizing the propagation feature by running the following SQL statement for each queue table listed by the query in Step 1.

    The following SQL statement uses queue table SCOTT.QTABLE1 as an example:

    SELECT unique(q_name) FROM scott.qtable1 a 
        WHERE EXISTS (SELECT consumer 
            FROM the (SELECT cast(history as sys.aq$_dequeue_history_t)
                FROM scott.qtable1 b 
                WHERE a.msgid = b.msgid) 
        WHERE consumer like 'AQ$_%');
    
    

    For each queue table, you must drop all queues returned by the SQL statement.


    Note:

    To list all of your queue tables, query the DBA_QUEUE_TABLES static data dictionary view. 


  3. Check the DBA_QUEUE_SCHEDULES static data dictionary view to determine if there are propagation schedules for any queues. You must unschedule propagation for each queue that is selected in the view using the DBMS_AQADM.UNSCHEDULE_PROPAGATION procedure.

  4. Eliminate all remote subscribers. Remote subscribers have the address field or protocol field specified. Run the DBMS_AQADM.QUEUE_SUBSCRIBERS procedure and check for subscribers with a non-null address field or a non-null protocol field. Drop all of these subscribers.

Procedures and Packages

This section describes disabling release 8.1 features related to procedures and packages.

Syntax Change for the SET_SESSION_LONG_LONGOPS Procedure

Release 8.1 introduces changes to the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. If any of your applications use this procedure and you changed them to conform to the release 8.1 syntax, then change the applications accordingly so that they conform to the release to which you are downgrading. For information about the syntax, refer to the dbmsapin.sql file in the release to which you are downgrading.

The UTL_REF Package

If you are downgrading to release 8.0.3, then discontinue use of the UTL_REF package. This package is not available in release 8.0.3.

If you are downgrading to release 8.0.4, then the UTL_REF package will be dropped automatically during the downgrading process. The package is dropped because the UTL_REF package is not part of the standard release 8.0.4 installation. To continue using this package, you must re-install it manually after downgrading to release 8.0.4.


Note:

If you are downgrading to release 8.0.5 or higher, then no action is required for the UTL_REF package. This package is part of the standard installation for release 8.0.5 and higher and is preserved automatically during the downgrade process. 


The DBMS_REPAIR Package

Release 8.1 supports the DBMS_REPAIR package. Before you downgrade, make sure all objects have skip corrupt disabled.

To identify objects that have skip corrupt enabled, issue the following SQL statement:

SELECT owner, table_name FROM dba_tables WHERE skip_corrupt = 'ENABLED'; 

For each such table selected, clear the skip corrupt attribute. For example, for a table named TB_5 owned by SCOTT, enter the following:

EXECUTE DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (schema_name => 'SCOTT', 
   object_name => 'TB_5', flags => DBMS_REPAIR.NOSKIP_FLAG); 

Constraints and Triggers

This section describes removing incompatibilities relating to constraints and triggers.

Discontinue Use of DISABLE VALIDATE Constraints

Release 8.1 supports the DISABLE VALIDATE constraint state. Before you downgrade, you must drop or invalidate all DISABLE VALIDATE constraints.

To identify DISABLE VALIDATE constraints, issue the following SQL statement:

SELECT constraint_name, status, validated
    FROM dba_constraints
    WHERE status = 'DISABLED'
    AND validated = 'VALIDATED';

Use the DROP clause in the ALTER TABLE statement to drop all of the constraints listed. Or, use the DISABLE clause in the ALTER TABLE statement to invalidate all of the constraints listed.

Drop Triggers on Nested Table View Columns

Release 8.1 supports creating triggers on nested table view columns. Before you downgrade, you must drop all of these triggers.

To identify nested table triggers on view columns, enter the following SQL statement:

SELECT owner, trigger_name, table_name, column_name 
    FROM dba_triggers 
    WHERE column_name IS NOT NULL;

Drop all of the triggers listed using the DROP TRIGGER statement.

Drop Incompatible Triggers for Database Event Publication

Triggers are enhanced in release 8.1 to support database event publication. Before you downgrade, all triggers that are incompatible with release 8.0 must be dropped.

To identify the triggers that must be dropped during the downgrade process, issue the following SQL statement:

SELECT owner, trigger_name 
   FROM dba_triggers 
   WHERE base_object_type LIKE '%DATABASE%'
      OR base_object_type LIKE '%SCHEMA%' 
      OR action_type LIKE '%CALL%'; 

Triggers on SCHEMA and DATABASE cannot be made compatible with release 8.0; you must drop these triggers. However, CALL triggers can be preserved during the downgrade process. To make CALL triggers compatible with release 8.0, wrap a BEGIN ... END block around the CALL statement.

Oracle Optimizer

This section describes removing incompatibilities relating to the Oracle optimizer.

Extensible Optimizer

Release 8.1 supports the extensible optimizer. Before you downgrade, you must discontinue use of the extensible optimizer by dropping all associations. To identify associations, enter the following SQL statement:

SELECT object_owner, object_name, column_name, object_type
    FROM dba_associations;

For each association listed, run the DISASSOCIATE STATISTICS statement with the FORCE option. For example, assume you receive the following output when you issue the preceding SQL statement:

OBJECT_OWNER      OBJECT_NAME      COLUMN_NAME      OBJECT_TYPE 
------------      -----------      -----------      -----------
SYS               TYPE1                             TYPE      
TKOQEX            TKOQ_TAB1         A               COLUMN 
2 rows selected. 

Issue the appropriate DISASSOCIATE STATISTICS statement corresponding to the object type listed. To drop the associations listed, where the object types are TYPE and COLUMN, issue the following SQL statements:

DISASSOCIATE STATISTICS FROM types sys.type1 FORCE;

DISASSOCIATE STATISTICS FROM columns tkoqex.tkoq_tab1.a FORCE;

See Also:

Oracle8i SQL Reference for more information about the DISASSOCIATE STATISTICS statement. 

Optimizer Plan Stability

Release 8.1 supports optimizer plan stability. This feature enables you to create stored outlines with the CREATE OUTLINE statement. Stored outlines are not supported in release 8.0.

To identify stored outlines, issue the following SQL statement:

SELECT owner, name FROM dba_outlines;

Drop any outlines listed by this SQL statement.

Security

This section describes removing incompatibilities relating to database security.

Drop All Application Contexts

The ability to specify an application context is a new feature in release 8.1. Before you downgrade, drop all application contexts. To identify the application contexts, issue the following SQL statement:

SELECT * FROM dba_context;

Drop all of the application contexts listed by this SQL statement using a DROP CONTEXT statement.

Drop All User-Defined Security Policies

Fine-grained access control is a new feature in release 8.1 that enables the creation of user-defined security policies. Before you downgrade, drop all user-defined security policies.

To identify user-defined security policies, issue the following SQL statement:

SELECT object_owner, object_name, policy_name
   FROM dba_policies;

Drop all of the policies listed by this SQL statement using the DBMS_RLS.DROP_POLICY procedure.

See Also:

Oracle8i Supplied PL/SQL Packages Reference for more information about the DBMS_RLS.DROP_POLICY procedure. 

Removing or Recreating Global Users Whose External Name Is NULL

Global users whose external name is NULL are not compatible with release 8.0. To identify global users whose external name is NULL, issue the following SQL statement:

SELECT name FROM dba_users
   WHERE password='GLOBAL' AND external_name IS NULL;

Either remove the users listed or recreate them so that they have a non-NULL external name. The users can recreated to become local database, external, or global users (with a distinguished name in Oracle Security Server). Oracle Corporation does not recommend that you create users as global users with a distinguished name in Oracle Security Server, because the feature is deprecated in release 8.1.5 and higher. If you create global users in release 8.0, you will not be able to easily migrate them to future releases.

In the following example, the a user whose external name is NULL has been recreated to be a release 8.0 local database account. For example, suppose you created the following global user in release 8.1:

CONNECT system/system_password
CREATE USER user1 IDENTIFIED GLOBALLY AS '';

To make this user compatible with release 8.0, issue the following SQL statements:

CONNECT system/system_password
ALTER USER user1 IDENTIFIED BY welcome;

Substitute your SYSTEM user password to connect.

Revoke CONNECT THROUGH Privileges for Proxy Users

The release 8.1 n-tier authentication and authorization feature is not available in release 8.0. Therefore, if any proxy users have CONNECT THROUGH privileges, then you must revoke these privileges.

To list the proxy users, issue the following SQL statement:

SELECT * FROM proxy_users;

To revoke CONNECT THROUGH privileges, issue an ALTER USER ... REVOKE CONNECT THROUGH statement. For example, the following statement revokes the right of proxy user APPSERVER1 to connect as the user JANE:

ALTER USER jane REVOKE CONNECT THROUGH appserver1;

Database Backup and Recovery

This section provides information about ensuring that your backups can be recovered by your downgraded database.

Oracle Media Management API and Proxy Copy

Oracle Media Management API version 2 supports proxy copy functionality, but this functionality will not be supported after you downgrade your database to release 8.0. Therefore, any release 8.1 proxy backups created using a version 2 software backup to tape (SBT) layer that supports proxy copy cannot be restored using release 8.0.

If you may need to restore backups of your release 8.1 database with your downgraded release 8.0 database, then, before you downgrade, create these backups with proxy copy turned off, because turning proxy copy off enables release 8.0 to restore the backups. Also, if your media manager provides only Oracle Media Management API version 2 support, then you should obtain a version 1.1 SBT layer to use with release 8.0.

Change Back to the Old Archive Log Destination Parameters

If you used the new archive log destination parameters in release 8.1 (LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n), then switch back to the old archive log destination parameters before you downgrade (LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

See Also:

"Changing Back to the Old Archive Log Destination Parameters" for instructions. 

Distributed Databases

This section describes removing incompatibilities relating to distributed databases.

Prepare Your Oracle Replication Environment for Downgrading

If you are using Oracle replication, then perform the actions described in the following sections to prepare the Oracle replication environment for downgrading.

See Also:

Oracle8i Replication for more information about completing these actions. 

Remove Incompatibilities In Your Oracle Replication Environment

If any database in the replication environment sends RPCs that use the release 8.1 protocol to the database you are downgrading, then you must either apply or delete all deferred RPCs before you downgrade. A database's deferred RPCs use the release 8.1 protocol if GENERATE_80_COMPATIBLE has ever been set to FALSE in any of the following calls:

Also, if you have updateable snapshots that do not contain all of the columns of their master tables, then it may lead to the use of the release 8.1 protocol.

Complete the following steps to apply or delete all deferred RPCs:

  1. Make sure all snapshot groups are at the top flavor.

    Each snapshot group of the database to be downgraded must be at the top flavor before you downgrade. To identify the snapshot groups that are not at the top flavor, issue the following SQL statement:

    SELECT gname, fname FROM dba_repgroup
        WHERE fname IS NOT NULL;
    
    
    

    If any of the database's object groups are listed, then you must drop and recreate the object group.

    If no snapshot groups are listed because FNAME is null for all of them, then all of the snapshot groups already are at the top flavor, and no action is required.


    Caution:

    Flavors are for internal use only. Use flavors only when instructed to do so by Oracle Worldwide Support. 


  2. Make sure each updateable snapshot contains all of the columns of its master table.

  3. If the database to be downgraded is a master site for one or more snapshot sites that are at release 8.1 or higher, then complete the following steps:

    1. Regenerate support for all snapshots at these sites by setting GENERATE_80_COMPATIBLE to TRUE in the GENERATE_SNAPSHOT_SUPPORT calls.

    2. Push the queue from each release 8.1 or higher snapshot site.

  4. Use either the DBMS_DEFER_SYS.EXECUTE_ERROR or the DBMS_DEFER_SYS.DELETE_ERROR procedure to apply or delete each error in the error queue for the database to be downgraded.

    See Also:

    Oracle8i Replication Management API Reference for more information about the DBMS_DEFER_SYS package. 

Remove Temporary Updatable Snapshot Logs

Determine if you have temporary updatable snapshot logs by issuing the following SQL statement:

SELECT owner, table_name FROM dba_tables
    WHERE temporary='Y' AND
    table_name LIKE 'RUPD$%';

If any rows are returned, then temporary updatable snapshot logs exist in your database. Run the following PL/SQL block to remove them:

DECLARE 
  sql_cur  BINARY_INTEGER; 
  dummy    BINARY_INTEGER; 
  new_flag BINARY_INTEGER; 
 
  CURSOR mv_logs IS 
    SELECT '"'||mowner||'"."'||temp_log||'"' temp_log, 
           flag, mowner, master 
      FROM mlog$ m 
     WHERE temp_log IS NOT NULL 
    FOR UPDATE; 
BEGIN 
  sql_cur := dbms_sql.open_cursor; 
  FOR alog IN mv_logs LOOP 
    new_flag := alog.flag; 
    IF dbms_ijob.bit(new_flag, 64) THEN ---KKZLOGTUPS 
      new_flag := new_flag - 64; 
    END IF; 
 
    BEGIN 
      dbms_sql.parse(sql_cur, 'DROP TABLE ' || alog.temp_log, dbms_sql.v7); 
      dummy := dbms_sql.execute(sql_cur); 
 
      UPDATE mlog$ m 
         SET flag = new_flag, temp_log = NULL 
       WHERE m.mowner = alog.mowner AND m.master = alog.master; 
    EXCEPTION WHEN others THEN 
      NULL; --- Ignore the error 
    END; 
  END LOOP; 
  dbms_sql.close_cursor(sql_cur); 
  COMMIT; 
EXCEPTION WHEN others THEN 
  IF dbms_sql.is_open(sql_cur) THEN 
    dbms_sql.close_cursor(sql_cur); 
  END IF; 
  RAISE; 
END; 
/ 
Identify Incompatibilities in Snapshots

The word "snapshot" is synonymous with the word "materialized view".

See Also:

"Identify Materialized Views That Will Be Changed or Dropped During Downgrade" for information about identifying incompatibilities in materialized views. 

Net8

This section describes removing incompatibilities relating to Net8.

Discontinue Use of Service Naming

Release 8.1 supports service naming in Net8, but service naming is not supported in release 8.0. To discontinue use of service naming, perform the following actions:

Reset Database Compatibility

After you have removed all of the incompatibilities with the release to which you are downgrading, reset the compatibility level of the database to the prior release by completing the following steps:

  1. If you are using any initialization parameters that were added in a release higher than the release to which you are downgrading, then remove them from your initialization parameter file.

    See Also:

    Appendix B, "Changes to Initialization Parameters" for lists of parameters added in each release. 

  2. Start SQL*Plus and connect as a user with SYSDBA privileges.

  3. Start the database using STARTUP.

  4. Run ALTER DATABASE RESET COMPATIBILITY:

    SQL> ALTER DATABASE RESET COMPATIBILITY;
    
    

    See Also:

    "About ALTER DATABASE RESET COMPATIBILITY" for more information. 

  5. Run SHUTDOWN IMMEDIATE:

    SQL> SHUTDOWN IMMEDIATE
    
    


    Caution:

    Do not open the database with COMPATIBLE set higher than the release to which you are downgrading after completing the step. 


  6. Set the COMPATIBLE initialization parameter in the initialization parameter file to match the release to which you are downgrading.

    For example, if you are downgrading to release 8.0.5, then set the COMPATIBLE parameter to the following:

    COMPATIBLE=8.0.5
    
    
  7. Open the database to ensure that it is compatible with release you specified with the COMPATIBLE parameter.

    If your database fails to open, then some incompatibilities still exist. If so, then reset the COMPATIBLE parameter to a higher setting, such as 8.1.0. Then, remove the incompatibilities and attempt to reset database compatibility again. All incompatibilities with the database to which you are downgrading must be removed before you proceed with the downgrading process described in "Downgrade the Database".

    See Also:

    "Remove Incompatibilities" for information about removing incompatibilities. 

Downgrade the Database

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section. See "Remove Incompatibilities" if you have not removed incompatibilities yet.

Complete the following steps to downgrade your release 8.1 database to an older release:

  1. Copy the following files from the ORACLE_HOME/rdbms/admin directory to a directory outside of Oracle home, such as the temporary directory on your system:

    • catlg803.sql (if you are downgrading to release 8.0.3)

    • utlip.sql

    • utlrp.sql

    Also, copy one of the following files from ORACLE_HOME/javavm/install to a directory outside of Oracle home, such as the temporary directory on your system:

    • jvmd816.sql (if you have Java installed and you are downgrading to release 8.1.6)

    • jvmd815.sql (if you have Java installed and you are downgrading to release 8.1.5)

    Make a note of the new location of these files. You may need them later in the downgrade process.

  2. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

  3. Start Server Manager. On most operating systems, enter svrmgrl at a command prompt to start Server Manager in Oracle8i.

  4. Connect to the database instance as user INTERNAL.

  5. If the database is shut down, then start the database using STARTUP:

    SVRMGR> STARTUP
    
    

    You may need to use the PFILE option to specify the location of your initialization parameter file.

  6. Set the system to spool results to a log file for later verification of success:

    SVRMGR> SPOOL catoutd.log
    
    

    If you want to see the output of the scripts you will run on your screen, then you also can issue a SET ECHO ON statement:

    SVRMGR> SET ECHO ON
    
    
  7. Run dold_release.sql where old_release refers to the release to which you are downgrading. See Table 13-2 to choose the correct script. Each script provides a direct downgrade to the release specified in the "Downgrading To" column.

    To run a script, enter the following:

    SVRMGR> @dold_release.sql
    
    Table 13-2 Downgrade Scripts
    Downgrading To  Run Script 

    8.1.6 

    d0801060.sql 

    8.1.5 

    d0801050.sql 

    8.1.4 

    Not supported. 

    8.1.3 

    Not supported. 

    8.1.2 

    Not supported. 

    8.1.1 

    Not supported. 

    8.0.6 

    d0800060.sql 

    8.0.5 

    d0800050.sql 

    8.0.4S 

    Not supported. 

    8.0.4 

    d0800040.sql 

    8.0.3 

    d0800030.sql 

    8.0.2 

    Not supported. 

    8.0.1 

    Not supported. 


    Note:

    If the release to which you are downgrading is not included in Table 13-2, then see the README files in the new installation for the correct downgrade script to run. 


    The following are notes about running the script:

    • You must use the version of the script included with the release from which you are downgrading.

    • You must run the script in the environment of the release from which you are downgrading.

    • You only need to run one script, even if your downgrade spans several releases. For example, if you are downgrading to release 8.0.3, then you need to run only d0800030.sql.

    • If you are using mutually referencing types, then downgrading to release 8.0.3 or 8.0.4.0 is not supported. See "Mutually Referencing Types and Downgrading to Release 8.0.4.0 or Lower" for more information.

    If you encounter any problems when you run the script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script. You can rerun any of the scripts described in this chapter as many times as necessary.

    See Also:

    "Running Scripts" for information about the types of errors to look for when you run a script. 

  8. Run SHUTDOWN IMMEDIATE and exit Server Manager:

    SVRMGR> SHUTDOWN IMMEDIATE
    SVRMGR> EXIT
    
    

    If you are using Oracle Parallel Server, then shutdown all instances.

  9. If your operating system is UNIX, then change the following environment variables point to the directories of the release to which you are downgrading:

  10. If your operating system is Windows, then complete the following steps:

    1. Stop all Oracle services, including the Oracle service OracleServiceSID of the database you are downgrading, where SID is the instance name.

      For example, if your SID is ORCL, then enter the following at an MS-DOS prompt:

      C:\> NET STOP OracleServiceORCL
      
      

      See Also:

      Your Administrator's Guide for Windows for information about stopping services. 

    2. Delete the Oracle service at the MS-DOS command prompt by issuing the ORADIM command. For example, if your SID is ORCL, then enter the following MS-DOS command:

      C:\> ORADIM -DELETE -SID ORCL
      
      
  11. Deinstall your current release of Oracle if both of the following conditions are true:

    • You are downgrading to a previous 8.1 release.

    • You plan to use the same Oracle home for your current release and the previous release to which you are downgrading.

    If you plan to use separate Oracle home directories for your current release and the previous release to which you are downgrading, then you do not need to deinstall your current release.


    Note:

    If you are downgrading to an 8.0 release, then you must install the 8.0 release in an Oracle home separate from the current 8.1 release. However, if you are downgrading to a previous 8.1 release, then this restriction does not apply, and you can install the new release into the same Oracle home as the previous release if you wish. 


  12. Install the release to which you are downgrading using the installation media for that release.

    For example, if you are downgrading to release 8.0.5, then use the release 8.0.5 installation media to install the release 8.0.5 distribution of Oracle.

    Also, if you are downgrading to an 8.0 release, then you must install the release 8.0 software in an Oracle home separate from the 8.1 release. However, if you are downgrading to a previous 8.1 release, then this restriction does not apply, and you can install the new release into the same Oracle home if you wish.


    Note:

    Installation is operating system-specific. For installation instructions, see your operating system-specific installation documentation and the README for your operating system. 


  13. If your operating system is Windows, then complete the following steps:

    1. Shut down and restart your computer.

    2. Create the Oracle database service at the MS-DOS command prompt using the ORADIM command.

      If you are downgrading to Oracle release 8.0, then use the ORADIM80 command:

      C:\> ORADIM80 -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS 
           -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA 
      
      
      

      If you are downgrading to a prior 8.1 release of Oracle, such as release 8.1.5, then use the ORADIM command:

      C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS 
           -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA 
      
      

      This syntax includes the following variables:

      SID 

      is the same SID name as the SID of the release 8.1 database you are downgrading. 

      PASSWORD 

      is the password for the database instance. This is the password for the INTERNAL user. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.  

      USERS 

      is the maximum number of users who can be granted SYSDBA and SYSOPER privileges. 

      ORACLE_HOME 

      is the Oracle home directory of the database to which you are downgrading. Ensure that you specify the full pathname with the -PFILE option, including drive letter of the Oracle home directory. 

      For example, if you are downgrading to Oracle release 8.0.6, if your SID is ORCL, your PASSWORD is TWxy579, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORANT, then enter the following command:

      C:\> ORADIM80 -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10 
           -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA 
      
      
  14. Copy configuration files to a location outside of the Oracle home from which you are downgrading:

    1. If you are using separate Oracle homes and your initialization parameter file resides within the Oracle home of the database from which you are downgrading, then copy the initialization parameter file to a location outside of the Oracle home. By default Oracle looks for the initialization parameter file in ORACLE_HOME/dbs on UNIX and ORACLE_HOME\database on Windows platforms. The initialization parameter file can reside anywhere you wish, but it should not reside in the Oracle home of the release from which you are downgrading.

    2. If your initialization parameter file has an IFILE (include file) entry and the file specified in the IFILE entry resides within the Oracle home of the database from which you are downgrading, then copy the file specified by the IFILE entry to a location outside of the Oracle home. The file specified in the IFILE entry has additional initialization parameters. After you copy this file, edit the initialization parameter file to point to its new location.

    3. If you have a password file that resides within the Oracle home of the database from which you are downgrading, then move or copy the password file to the Oracle home of the database to which you are downgrading. The name and location of the password file is operating system-specific; for example, on UNIX operating systems, the default password file is ORACLE_HOME/dbs/orapwsid, but on Windows NT, the default password file is ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

  15. In the initialization parameter file, set the _SYSTEM_TRIG_ENABLED initialization parameter to FALSE:

    _SYSTEM_TRIG_ENABLED = FALSE
    
    
  16. Copy the following files into the ORACLE_HOME/rdbms/admin directory:

    • catlg803.sql (if you are downgrading to release 8.0.3)

    • utlip.sql

    • utlrp.sql

    Copy one of the following files to the ORACLE_HOME/javavm/install directory:

    • jvmd816.sql (if you have Java installed and you are downgrading to release 8.1.6)

    • jvmd815.sql (if you have Java installed and you are downgrading to release 8.1.5)

    You copied these files to a directory outside of Oracle home in Step 1.

  17. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

  18. Start Server Manager.

  19. Connect to the database instance as user INTERNAL.

  20. Run STARTUP:

    SVRMGR> STARTUP
    
    

    You may need to use the PFILE option to specify the location of your initialization parameter file.

  21. Run utlip.sql:

    SVRMGR> @utlip.sql 
    
    

    The UTLIP.SQL script invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will happen in the format required by the database. It also reloads packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.

    See Also:

    "Changing Word-Size" for more information about changing word-size. 

  22. Run either the catalog.sql script or the catlg803.sql script, depending on the release to which you are downgrading. Do not run both of these scripts.

    If you are downgrading to release 8.0.4 or higher, then run catalog.sql:

    SVRMGR> @catalog.sql 
    
    

    If you are downgrading to release 8.0.3, then run catlg803.sql:

    SVRMGR> @catlg803.sql 
    
    


    Note:

    Due to bug #571546, which exists in release 8.0.3 but is fixed in release 8.0.4 and higher, you should not run catalog.sql after downgrading to release 8.0.3. The recreation of package STANDARD triggers this bug. Because catalog.sql recreates package STANDARD, Oracle has provided a new script (catlg803.sql) that effectively does everything the release 8.0.3 catalog.sql script does, except for performing a few additional steps to work around the problem described in bug #571546.  


  23. Run catproc.sql:

    SVRMGR> @catproc.sql 
    
    
  24. If the Oracle system has Oracle replication installed, then run the following catalog script supplied with the release to which you downgraded:

    SVRMGR> @catrep.sql
    
    
  25. If the Oracle system has Parallel Server installed, then run the following catalog script supplied with the release to which you downgraded:

    SVRMGR> @catparr.sql
    
    
  26. If the Oracle system has Java installed, then run the appropriate downgrade script to downgrade the Java component. When you run the script, replace ORACLE_HOME with the full path to your Oracle home directory.

    If you are downgrading to release 8.1.6, then run the following script:

    SVRMGR> @ORACLE_HOME/javavm/install/jvmd816.sql
    
    

    If you are downgrading to release 8.1.5, then run the following script:

    SVRMGR> @ORACLE_HOME/javavm/install/jvmd815.sql
    
    
  27. Run utlrp.sql. This step is optional and can be done regardless of whether there was a change in word-size.

    SVRMGR> @utlrp.sql
    
    

    The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future.

    Oracle Corporation highly recommends running utlrp.sql.

  28. Turn off the spooling of script results to the log file:

    SVRMGR> SPOOL OFF;
    
    

    Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 6; the suggested name was catoutd.log. Correct any problems you find in this file.

    If you specified SET ECHO ON, then you may want to SET ECHO OFF now:

    SVRMGR> SET ECHO OFF;
    
    
  29. If you removed mutually referencing views while following the instructions in "Mutually Referencing Views and Downgrading to Release 8.0.4.1 or Lower", and you downgraded to release 8.0.4, then recreate these views now.


    Note:

    You cannot recreate these views if you downgraded to release 8.0.3, because mutually referencing views are not supported in release 8.0.3. 


  30. Shut down all instances using SHUTDOWN IMMEDIATE:

    SVRMGR> SHUTDOWN IMMEDIATE
    
    


    Note:

    For Oracle Parallel Server, set the PARALLEL_SERVER initialization parameter to FALSE. You can change it back to TRUE after the upgrade operation is complete. 


  31. In the initialization parameter file, set the _SYSTEM_TRIG_ENABLED initialization parameter to TRUE:

    _SYSTEM_TRIG_ENABLED = TRUE
    
    
  32. If you exited Server Manager, start Server Manager. On most operating systems, enter svrmgrl at a command prompt to start Server Manager.

  33. Connect to the database instance as user INTERNAL.

  34. Start the database using STARTUP:

    SVRMGR> STARTUP
    
    

    You may need to use the PFILE option to specify the location of your initialization parameter file.

Your database is now downgraded. Complete the procedures described in the following sections to finish downgrading specific components.

Regenerating Replication Support

If you are using Oracle replication, then, before you regenerate Oracle replication support, make sure you completed the procedures described in "Prepare Your Oracle Replication Environment for Downgrading". Then, complete the actions described below based on whether the downgraded database is a master site or a snapshot site.

Master Site

If the downgraded database is a master site for one or more object groups, then complete the following steps to regenerate replication support:

  1. Quiesce each object group.

  2. Generate replication support for each replicated table in the group.

  3. Resume master activity for the object group. If the masterdef site is at release 8.1, then make sure you specify GENERATE_80_COMPATIBLE=>TRUE in the GENERATE_REPLICATION_SUPPORT calls.

Snapshot Site

If the downgraded database is a snapshot site, then generate replication support for each updatable snapshot.

See Also:

Oracle8i Replication for more information about generating replication support. 

Re-Installing the UTL_REF Package on Release 8.0.4

If you downgraded to release 8.0.4, and you were using the UTL_REF package before you downgraded, then re-install the UTL_REF package. This package was automatically dropped during the downgrading process because the package is not part of the standard installation for release 8.0.4.


Note:

If you downgraded to release 8.0.5 or higher, then no action is required. The UTL_REF package was preserved during the downgrading process. If you downgraded to release 8.0.3, then you cannot use the UTL_REF package because it is not available in release 8.0.3. 


Re-Installing Recovery Manager Packages on Release 8.0.3

If you downgraded to release 8.0.3, and if you used Recovery Manager (RMAN) release 8.0.4 or higher before you downgraded, then re-install the following release 8.0.3 packages on the recovery catalog database:


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index