Oracle8i Migration
Release 8.1.5

A67774-01

Library

Product

Contents

Index

Prev Next

12
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, see Chapter 13, "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. Follow the instructions in the appropriate section based on the release to which you are downgrading.

Removing Incompatibilities If You Are Downgrading to an 8.1 Release

If you are downgrading to either release 8.1.4 or 8.1.3, complete the following actions to remove incompatibilities:

After completing these actions, proceed to the "Reset Database Compatibility" section if you used any of the following features:

However, if you did not use these features, you do not need to reset database compatibility, and you can proceed to the "Downgrade the Database" section.

Removing Incompatibilities If You Are Downgrading to an 8.0 Release

If the compatibility level of your database is higher than the release to which you are downgrading, your database may have incompatibilities with the previous release that must be removed before you downgrade. Check your COMPATIBLE 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.5 and the COMPATIBLE parameter is set to 8.0.5 or lower, 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 move on to the "Downgrade the Database" section.

However, if you are downgrading to an 8.0 release and the COMPATIBLE parameter is set higher than the release to which you are downgrading, some incompatibilities may exist. For example, if you are downgrading to release 8.0.5, and COMPATIBLE is set to 8.1.0 or higher, incompatibilities may exist. Similarly, if you are downgrading to release 8.0.3, and COMPATIBLE is set to 8.0.4 or higher, incompatibilities may exist.

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

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

  2. Start Server Manager.

  3. Connect to the database instance:

    SVRMGR> CONNECT INTERNAL
    
    
  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 utlincmpt.out
      SVRMGR> @utlincmpt.sql
      SVRMGR> SPOOL OFF
      
      

      The utlincmpt.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 utlincmpt.sql script.

      After the utlincmpt.sql script runs, view the utlincmpt.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, 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), 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, 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, drop the tablespace. If you need to preserve the data, 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.

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, enter the following SQL statement to convert TS_1 to a dictionary tablespace:

EXECUTE dbms_admin.tablespace_migrate_from_local('ts_1');

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, 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, 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, drop the tables. However, if you need to preserve the data in any of these tables, 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 command. Run this command for each table in the list. For example, to drop all of the unused columns in a table named CUSTOMERS, enter the following command:

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 command. Run this command for each table in the list. For example, to drop all partially dropped columns in a table named CUSTOMERS, enter the following command:

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, 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 or Lower

If you have mutually referencing views, and you are downgrading to release 8.0.4 or lower, you must drop these views. If you do not have mutually referencing views, or if you are downgrading to release 8.0.5 or higher, 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, drop all mutually referencing views.

Mutually referencing views are supported in release 8.0.4 and higher. However, you still must drop these views if you are downgrading to release 8.0.4. After you downgrade, you can recreate the previously dropped mutually referencing views in your 8.0.4 release. This action is required because of bug #662863, which is present in release 8.0.4, but is corrected in release 8.0.5 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;  

Check for Bitmap Indexes That Will Be Invalidated

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 will be invalidated automatically during the downgrade process described in "Downgrade the Database". To list the indexes that will be invalidated during the downgrade process, issue the following SQL statement:

SELECT o.name INDEX_NAME, u.name INDEX_OWNER 
    FROM sys.user$ u, sys.obj$ o, sys.ind$ i, sys.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#;

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';

Drop the domain indexes 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, 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, 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;

Drop the dimensions listed by this SQL statement.

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, drop the tables. However, if you need to preserve the data in a table, 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, 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, drop the tables. However, if you need to preserve the data in one or more of the tables listed, 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, 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, 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 command, 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.

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, 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 Advanced Replication, complete the following steps. If you do not use Advanced Replication, 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 for more information about completing these Advanced 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, drop the LOB columns. However, if you need to preserve your LOB data in partitioned tables, use the ALTER TABLE ... EXCHANGE PARTITION command 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 commands 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 commands 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, 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. Downgrade your database using the instructions in "Downgrade the Database".

  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.

Discontinue Use of Release 8.1 Nested Table Features

Before your 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 your 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, 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, 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, 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 utljavarm.sql script drops all Java objects. To identify the Java objects dropped by the utljavarm.sql script, issue the following SQL statement:

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

To run the utljavarm.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 utljavaout.log
    SQL> @utljavarm.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. If you are downgrading to release 8.0.3.0, 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, 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 command:

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, 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, 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, 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, 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 12-1:

Table 12-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 12-1 shows the PL/SQL block, and Example 12-2 shows the SQL statement.

Example 12-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 12-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 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, 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. Follow the instructions in the rest of this chapter to downgrade the database.

  4. After the database is downgraded to release 8.0, 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: If You Are Downgrading to Release 8.0.3, Remove Propagation

If you are using message propagation in the Advanced Queuing Option, and you are downgrading to release 8.0.3, remove propagation. If you are not using message propagation, or if you are downgrading to release 8.0.4 or higher, 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, 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, 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, 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, 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, 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 command to drop all of the constraints listed. Or, use the DISABLE clause in the ALTER TABLE command 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 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 command.

Drop Incompatible Triggers

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 trigger_name, base_object_type, action_type  
    FROM dba_triggers 
    WHERE base_object_type 
    IN ('DATABASE', 'SCHEMA') 
    OR action_type = '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 command 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 command.  

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.

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, 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, 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, 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), 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 Advanced Replication Environment for Downgrading

If you are using Advanced Replication, perform the actions described in the following sections to prepare the Advanced Replication environment for downgrading.

See Also:

Oracle8i Replication for more information about completing these actions.  

Remove Incompatibilities In Your Advanced Replication Environment

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

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

  1. If the database to be downgraded is a master site for one or more object groups and the masterdef site is at release 8.1 or higher, complete the following steps:

    1. Quiesce each of the object groups.

    2. Convert each master to the top flavor of each object group.

    3. Regenerate replication support for each replicated table by setting GENERATE_80_COMPATIBLE to TRUE in the GENERATE_REPLICATION_SUPPORT calls.

    4. Resume master activity for the quiesced replication object groups.

    5. Convert each snapshot site of each master to the top flavor of each object group.

  2. If the database to be downgraded is a master site for one or more snapshot sites that are at release 8.1 or higher, 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.

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

Make Sure the Database's Object Groups Are At the Top Flavor

Each object group of the database to be downgraded must be at the top flavor before you downgrade. To identify the object 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, you must change the flavor of the listed object groups to the top flavor before downgrading. If an object group is not listed because FNAME is null for the object group, then the object group already is at the top flavor, and no action is required for it.

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, 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, remove them from your initsid.ora 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 parameter in the initsid.ora file to match the release to which you are downgrading.

    For example, if you are downgrading to release 8.0.5, 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, some incompatibilities still exist. If so, 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

    • utlip.sql

    • utlrp.sql

    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 SQL*Plus with the NOLOG option:

    sqlplus /nolog
    
    


    Note:

    You must use SQL*Plus for this part of the downgrade procedure. Do not use Server Manager.  


  4. Connect to the database instance:

    SQL> CONNECT INTERNAL
    
    
  5. If the database is shut down, start the database using STARTUP:

    SQL> STARTUP
    
    

    You may need to use the PFILE option to specify the location of your initsid.ora file.

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

    SQL> SPOOL catoutd.log
    
    

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

    SQL> SET ECHO ON
    
    
  7. Run dold_release.sql where old_release refers to the release to which you are downgrading. See Table 12-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:

    SQL> @dold_release.sql
    
    Table 12-2 Downgrade Scripts
    Downgrading To  Run Script 

    8.1.4  

    d0801040.sql  

    8.1.3  

    d0801030.sql  

    8.1.2  

    Not supported.  

    8.1.1  

    Not supported.  

    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 12-2, 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, 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 SQL*Plus:

    SQL> SHUTDOWN IMMEDIATE
    SQL> EXIT
    
    

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

  9. Change the following environment variables point to the directories of the release to which you are downgrading:

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


  11. If you are using separate Oracle homes and your initsid.ora file resides within the Oracle home of the database from which you are downgrading, copy the initsid.ora file to a location outside of the Oracle home. In release 8.1, the default location for the initsid.ora file is $ORACLE_BASE/admin/sid/pfile, where sid is the Oracle instance ID, but in past releases, the default was $ORACLE_HOME/dbs on UNIX and $ORACLE_HOME\database on Windows NT. The initsid.ora file can reside anywhere you wish, but it should not reside in the Oracle home of the release from which you are downgrading.

    If your initsid.ora 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 initsid.ora file to point to its new location.


    Note:

    For Oracle Parallel Server, perform this step on all nodes. Also, set the PARALLEL_SERVER initialization parameter to FALSE. You can change it back to TRUE after the downgrade operation is complete.  


  12. Copy the following files into the $ORACLE_HOME/rdbms/admin directory:

    • catlg803.sql

    • utlip.sql

    • utlrp.sql

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

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

  14. Start Server Manager and run CONNECT INTERNAL:

    SVRMGR> CONNECT INTERNAL
    
    


    Note:

    You must use Server Manager for the rest of the downgrade procedure. Do not use SQL*Plus.  


  15. Run STARTUP:

    SVRMGR> STARTUP
    
    

    You may need to use the PFILE option to specify the location of your initsid.ora file.

  16. Perform this step if either one of the following conditions is true:

    • Your installation involves a change in word-size (switching between 32-bit and 64-bit software).

    • You are downgrading to release 8.0.

    Otherwise, this step is optional (performing this step if it is not necessary could cause invalidations).

    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 package STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.

  17. 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, run catalog.sql:

    SVRMGR> @catalog.sql 
    
    

    If you are downgrading to release 8.0.3, 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.  


  18. Run catproc.sql:

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

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

    SVRMGR> @catparr.sql
    
    
  21. 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.

  22. 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, you may want to SET ECHO OFF now:

    SVRMGR> SET ECHO OFF;
    
    
  23. If you removed mutually referencing views while following the instructions in "Mutually Referencing Views and Downgrading to Release 8.0.4 or Lower", and you downgraded to release 8.0.4, 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.  


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

Regenerating Advanced Replication Support

If you are using Advanced Replication, before you regenerate Advanced Replication support, make sure you completed the procedures described in "Prepare Your Advanced 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, complete the following steps to regenerate Advanced 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, make sure you specify GENERATE_80_COMPATIBLE=>TRUE in the GENERATE_REPLICATION_SUPPORT calls.

Snapshot Site

If the downgraded database is a snapshot site, 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, 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, no action is required. The UTL_REF package was preserved during the downgrading process. If you downgraded to release 8.0.3, 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, re-install the following release 8.0.3 packages on the recovery catalog database:




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index