Release 8.1.5
A67774-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Migration Release 8.1.5 A67774-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, 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 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. Follow the instructions in the appropriate section based on the release to which you are downgrading.
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.
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:
$ORACLE_HOME/rdbms/admin directory.
SVRMGR> CONNECT INTERNAL
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 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.
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. |
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, 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, 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.
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 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');
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, 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;
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:
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 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;
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;
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, 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 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.
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;
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 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#;
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';
Drop the domain indexes 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, 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, 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;
Drop the dimensions listed by this SQL statement.
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, drop the tables. However, if you need to preserve the data in a table, 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, 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, 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:
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, 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 command, 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.
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.
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 for more information about completing these Advanced 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, 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:
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 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.
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, 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.
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.
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.
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, 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, 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, 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 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:
$ORACLE_HOME/rdbms/admin directory.
SQL> SPOOL utljavaout.log SQL> @utljavarm.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, 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);
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. |
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, 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 12-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 12-1 shows the PL/SQL block, and Example 12-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 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, 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. |
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:
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.
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, 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, 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.
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 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.
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.
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.
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 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;
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.
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;
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, 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.
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).
This section describes removing incompatibilities relating to distributed databases.
If you are using Advanced Replication, perform the actions described in the following sections to prepare the Advanced 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, 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:
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.
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; /
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:
initsid.ora file.
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:
initsid.ora file.
Appendix B, "Changes to Initialization Parameters" for lists of parameters added in each release.
See Also:
SQL> ALTER DATABASE RESET COMPATIBILITY;
SQL> SHUTDOWN IMMEDIATE
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
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".
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:
Make a note of the new location of these files. You may need them later in the downgrade process.
$ORACLE_HOME/rdbms/admin directory.
sqlplus /nolog
SQL> CONNECT INTERNAL
SQL> STARTUP
You may need to use the PFILE option to specify the location of your initsid.ora file.
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
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
|
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:
d0800030.sql.
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. |
SQL> SHUTDOWN IMMEDIATE SQL> EXIT
If you are using Oracle Parallel Server, shutdown all instances.
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.
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.
$ORACLE_HOME/rdbms/admin directory:
You copied these files to a directory outside of Oracle home in Step 1.
$ORACLE_HOME/rdbms/admin directory.
SVRMGR> CONNECT INTERNAL
SVRMGR> STARTUP
You may need to use the PFILE option to specify the location of your initsid.ora file.
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.
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
catproc.sql:
SVRMGR> @catproc.sql
SVRMGR> @catrep.sql
SVRMGR> @catparr.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, you may want to SET ECHO OFF now:
SVRMGR> SET ECHO OFF;
Your database is now downgraded. Complete the procedures described in the following sections to finish downgrading specific components.
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.
If the downgraded database is a master site for one or more object groups, complete the following steps to regenerate Advanced Replication support:
If the downgraded database is a snapshot site, 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, 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, re-install the following release 8.0.3 packages on the recovery catalog database:
