Release 3 (8.1.7)
Part Number A86632-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Migration Release 3 (8.1.7) Part Number A86632-01 |
|
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 of your release 8.1 database before you downgrade.
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.
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:
If you are downgrading to release 8.1.5, then complete the actions in the following sections to remove incompatibilities:
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);
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:
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.
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);
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;
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');
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.
After you remove all of the incompatibilities with release 8.1.5, go to "Reset Database Compatibility".
If incompatibilities may exist with the 8.0 release to which you are downgrading, then use the following general procedure to remove these incompatibilities:
ORACLE_HOME/rdbms/admin directory.
svrmgrl at a command prompt to start Server Manager in Oracle8i.
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.
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.
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. |
This section describes removing incompatibilities relating to 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:
SELECT tablespace_name, plugged_in FROM dba_tablespaces WHERE plugged_in = 'YES';
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.
EXECUTE dbms_tts.downgrade;
The DBMS_TTS.DOWNGRADE procedure drops the temporary tables in the system tablespace used by the transportable tablespace feature.
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;
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');
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');
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.
This section describes removing incompatibilities relating to schema objects.
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.
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;
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:
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;
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.
Before you downgrade, drop all unused and partially dropped 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;
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;
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.
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;
The following sections provide instructions for identifying materialized views that will be changed or dropped during the downgrade process described in "Downgrade the Database".
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.
Materialized views that use joins or the GROUP BY clause (aggregate queries) can no longer use the FAST REFRESH mode after you downgrade.
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.
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.
Any materialized views created without a view will be dropped automatically when you downgrade.
When you downgrade, complete refresh will be performed automatically on any views created with the BUILD DEFERRED clause.
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.
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;
Release 8.1 provides protections for bitmap indexes. These protections prevent bitmap indexes from being unintentionally invalidated.
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;
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.
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.
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.
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;
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;
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;
This section describes disabling release 8.1 partitioning features.
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:
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;
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:
CREATE TABLE temp_obp1 AS SELECT * FROM obp1;
DROP TABLE obp1;
ALTER TABLE temp_obp1 RENAME TO obp1;
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';
This section describes disabling datatypes that are available only in release 8.1 and higher.
Complete the procedures in the following sections to remove all uses of the UROWID (universal rowid) datatype.
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.
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.
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:
utlchain.sql script to store chained rows for the regular tables.
Complete the following steps to downgrade an exception table called EXC_TB:
utlexcp.sql script to store exceptions for the regular tables.
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.
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);
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.
SELECT owner, table_name, column_name FROM dba_lobs WHERE dba_lobs.owner != 'SYSTEM' AND table_name NOT IN ('KOTAD$', 'KOTMD$', 'KOTTB$', 'KOTTD$');
DECLARE rc number; BEGIN rc := dbms_defer_sys.push(); END;
DROP VIEW deflob;
|
See Also:
Oracle8i Replication Management API Reference for more information about completing these replication steps. |
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:
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.
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.
This section describes disabling release 8.1 features related to user-defined datatypes.
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:
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.
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:
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.
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.
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) /
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.
Before you downgrade, remove stored procedures defined as C call specifications.
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.
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.
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:
If you are using the UROWID datatype as a variable in PL/SQL code, then remove this variable before you downgrade.
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 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:
ORACLE_HOME/rdbms/admin directory.
SQL> SPOOL utljavrm.out SQL> @utljavrm.sql SQL> SPOOL OFF
Check the spool file and verify that the statements executed successfully.
Complete the following tasks to disable release 8.1 AQ features in your queue tables:
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. |
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);
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. |
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.
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;
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:
| 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.
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;
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. |
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:
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".
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');
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".
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. |
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);
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:
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.
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.
This section describes disabling release 8.1 features related to procedures and packages.
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.
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.
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);
This section describes removing incompatibilities relating to constraints and triggers.
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.
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.
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.
This section describes removing incompatibilities relating to the Oracle 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;
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.
This section describes removing incompatibilities relating to database security.
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.
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. |
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.
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;
This section provides information about ensuring that your backups can be recovered by your downgraded database.
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.
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).
This section describes removing incompatibilities relating to distributed databases.
If you are using Oracle replication, then perform the actions described in the following sections to prepare the Oracle replication environment for downgrading.
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:
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.
Oracle8i Replication Management API Reference for more information about the DBMS_DEFER_SYS package.
See Also:
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; /
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. |
This section describes removing incompatibilities relating to Net8.
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:
tnsnames.ora file.
listener.ora file to manually configure information about the instances served by the listener.
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:
Appendix B, "Changes to Initialization Parameters" for lists of parameters added in each release.
See Also:
SQL> ALTER DATABASE RESET COMPATIBILITY;
SQL> SHUTDOWN IMMEDIATE
For example, if you are downgrading to release 8.0.5, then set the COMPATIBLE parameter to the following:
COMPATIBLE=8.0.5
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".
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:
ORACLE_HOME/rdbms/admin directory to a directory outside of Oracle home, such as the temporary directory on your system:
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.
ORACLE_HOME/rdbms/admin directory.
svrmgrl at a command prompt to start Server Manager in Oracle8i.
SVRMGR> STARTUP
You may need to use the PFILE option to specify the location of your initialization parameter file.
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
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
|
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:
d0800030.sql.
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. |
SVRMGR> SHUTDOWN IMMEDIATE SVRMGR> EXIT
If you are using Oracle Parallel Server, then shutdown all instances.
For example, if your SID is ORCL, then enter the following at an MS-DOS prompt:
C:\> NET STOP OracleServiceORCL
C:\> ORADIM -DELETE -SID ORCL
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.
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.
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:
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
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.
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.
_SYSTEM_TRIG_ENABLED = FALSE
ORACLE_HOME/rdbms/admin directory:
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.
ORACLE_HOME/rdbms/admin directory.
SVRMGR> STARTUP
You may need to use the PFILE option to specify the location of your initialization parameter file.
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.
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
catproc.sql:
SVRMGR> @catproc.sql
SVRMGR> @catrep.sql
SVRMGR> @catparr.sql
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
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.
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;
SVRMGR> SHUTDOWN IMMEDIATE
_SYSTEM_TRIG_ENABLED = TRUE
svrmgrl at a command prompt to start Server Manager.
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.
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.
If the downgraded database is a master site for one or more object groups, then complete the following steps to regenerate replication support:
If the downgraded database is a snapshot site, then generate replication support for each updatable snapshot.
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.
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:
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|