Oracle9i Database Migration Release 2 (9.2) Part Number A96530-01 |
|
This chapter guides you through the process of downgrading a database back to the previous Oracle release. This chapter covers the following topics:
Perform a full offline backup of your release 9.2 database before you downgrade.
See Also:
Oracle9i User-Managed Backup and Recovery Guide for more information |
The process of removing incompatibilities depends on the release to which you are downgrading. 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 is determined by the setting of the COMPATIBLE
initialization parameter. Check your COMPATIBLE
initialization parameter setting by issuing the following SQL statement:
SQL> 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 9.0.1 and the COMPATIBLE
parameter is set to 9.0.0
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 see "Downgrade the Database".
However, if 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.1.7 and COMPATIBLE
is set to 9.0.0
or higher, then incompatibilities may exist.
To identify any incompatibilities that may exist with the release to which you are downgrading, perform the following steps:
ORACLE_HOME
/rdbms/admin
directory.SYSDBA
privileges.RESTRICT
mode:
SQL> STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
V$COMPATIBILITY
dynamic performance view to identify any incompatibilities:
SQL> 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.
utlincmp.sql
:
SQL> SPOOL utlincmp.log SQL> @utlincmp.sql SQL> 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.log
file and look for instances where a SELECT
statement returned values. The values returned are incompatibilities that may need to be removed depending on the release to which you are downgrading.
The following sections provide detailed information about removing incompatibilities with previous Oracle releases. Depending on the release to which you are downgrading, you may need to complete the steps in some or all of the following sections.
For example, if you are downgrading to release 9.0.1, then you only need to complete the steps in "Removing Release 9.2 Incompatibilities". However, if you are downgrading to release 8.1.7, then you need to complete the steps in "Removing Release 9.2 Incompatibilities" as well as the steps in "Removing Release 9.0.1 Incompatibilities".
Note: If you are downgrading from Oracle9i Enterprise Edition to Oracle9i (formerly Workgroup Server), then, before you downgrade, modify any applications that use the advanced features of Oracle9i Enterprise Edition so that they do not use these advanced features. See Oracle9i Database New Features for more information about the differences between the editions. |
If you are downgrading to release 9.0.1 or lower, then complete the actions in the following sections to remove incompatibilities:
This section describes removing incompatibilities relating to release 9.2 DEFAULT
partitions.
Before you downgrade to release 9.0.1 or lower, drop all DEFAULT
partitions on list partitioned tables. To identify all list partitioned tables with DEFAULT
partitions, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME, o.subname AS PARTITION_NAME FROM sys.user$ u, sys.obj$ o, sys.tabpart$ tp WHERE BITAND(tp.flags, 16384) = 16384 AND tp.obj# = o.obj# AND o.owner# = u.user#;
For each partition represented by the PARTITION_NAME
column in the table represented by the OWNER
.TABLE_NAME
columns, simply drop the partition:
ALTER TABLE OWNER.TABLE_NAME DROP PARTITION PARTITION_NAME;
This section describes removing incompatibilities relating to release 9.2 partitioning methods.
Before you downgrade to release 9.0.1 or lower, drop all partitioned tables that use range-list methods. To identify existing tables partitioned with range-list methods, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME FROM sys.user$ u, sys.obj$ o, sys.partobj$ po WHERE po.parttype = 1 AND MOD(po.spare2, 256) = 4 AND o.obj# = po.obj# AND o.owner# = u.user#;
If you do not need to preserve the table data, then, for each table represented by the OWNER
.TABLE_NAME
columns, simply drop the table:
DROP TABLE OWNER.TABLE_NAME;
However, if you need to preserve the table data, then copy the data into non-partitioned tables, or copy the data into tables partitioned by range, hash, list, or another composite method.
This section describes removing incompatibilities relating to release 9.2 Streams.
Before you downgrade to release 9.0.1 or lower, drop all Streams capture processes. To identify existing capture processes, issue the following SQL statement:
SELECT capture_name FROM dba_capture;
For each capture process listed in the CAPTURE_NAME
column, issue the following SQL statement:
EXECUTE dbms_capture_adm.drop_capture(capture_name => 'CAPTURE_NAME');
Before you downgrade to release 9.0.1 or lower, drop all Streams apply processes. To identify existing apply processes, issue the following SQL statement:
SELECT apply_name FROM dba_apply;
For each apply process listed in the APPLY_NAME
column, issue the following SQL statement:
EXECUTE dbms_apply_adm.drop_apply(apply_name => 'APPLY_NAME');
This section describes removing incompatibilities relating to release 9.2 subpartition templates.
Before you downgrade to release 9.0.1 or lower, drop all subpartition templates in composite partitioned tables. To identify existing composite partitioned tables with subpartition templates, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME FROM sys.user$ u, sys.obj$ o WHERE o.owner# = u.user# AND o.obj# in (SELECT DISTINCT bo# FROM defsubpart$) UNION SELECT u.name AS OWNER, o.name AS TABLE_NAME FROM sys.user$ u, sys.obj$ o WHERE o.owner# = u.user# AND o.obj# in (SELECT DISTINCT bo# from defsubpartlob$);
For each table represented by the OWNER
.TABLE_NAME
columns, simply drop the subpartition template:
ALTER TABLE OWNER.TABLE_NAME SET SUBPARTITION TEMPLATE ();
This section describes removing incompatibilities relating to LOB retention.
Before you downgrade to release 9.0.1 or lower, drop retention stored in LOB columns. To identify existing LOB columns with retention, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME, c.name AS LOB_COL_NAME FROM sys.user$ u, sys.obj$ o, sys.col$ c, sys.lob$ l WHERE BITAND(l.flags, 64) = 64 AND l.obj# = o.obj# AND c.obj# = o.obj# AND c.col# = l.col# AND o.owner# = u.user#;
For each column represented by the LOB_COL_NAME
column in the table represented by the OWNER
.TABLE_NAME
columns, simply drop the retention:
ALTER TABLE OWNER.TABLE_NAME MODIFY LOB(LOB_COL_NAME) (REBUILD FREEPOOLS);
This section describes removing incompatibilities relating to automatic segment-space managed tablespaces with LOBs.
Before you downgrade to release 9.0.1 or lower, drop all LOB columns in automatic segment-space managed tablespaces. To identify existing automatic segment-space managed tablespaces with LOB columns, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME, c.name AS LOB_COL_NAME FROM sys.lob$ l, sys.ts$ t, sys.user$ u, sys.obj$ o, sys.col$ c WHERE l.ts# = t.ts# AND (DECODE(BITAND(t.flags, 32), 32, 1, 0) = 1 AND t.online$ <> 3) AND o.owner# = u.user# AND l.obj# = o.obj# AND l.obj# = c.obj# AND l.col# = c.col#;
For each LOB segment listed, perform one of the following actions:
ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COL_NAME) STORE AS (TABLESPACE manual segment space-managed tablespace);
DROP TABLE OWNER.TABLE_NAME;
If you are downgrading to release 8.1.7 or lower, then complete the actions in the following sections to remove incompatibilities:
This section describes removing incompatibilities relating to tablespaces that were introduced in release 9.0.1.
Before you downgrade to release 8.1.7 or lower, drop all automatic segment-space managed tablespaces. To identify existing automatic segment-space managed tablespaces, issue the following SQL statement:
SELECT TABLESPACE_NAME FROM dba_tablespaces WHERE segment_space_management = 'AUTO';
For each tablespace represented by the TABLESPACE_NAME
column, simply drop the tablespace:
DROP TABLESPACE TABLESPACE_NAME;
Before you downgrade to release 8.1.7 or lower, drop all undo tablespaces. To identify existing undo tablespaces, issue the following SQL statement:
SELECT name AS TABLESPACE_NAME FROM sys.ts$ WHERE BITAND(flags, 16) = 16 AND online$ <> 3;
For each tablespace represented by the TABLESPACE_NAME
column, simply drop the tablespace:
DROP TABLESPACE TABLESPACE_NAME;
This section describes removing incompatibilities relating to schema objects that were introduced in release 9.0.1.
Before you downgrade to release 8.1.7 or lower, drop all external tables. To identify existing external tables, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME FROM sys.user$ u, sys.obj$ o, sys.tab$ t WHERE t.obj# = o.obj# AND o.owner# = u.user# AND BITAND(t.property, 2147483648) != 0;
For each table represented by the OWNER
.TABLE_NAME
columns, simply drop the table:
DROP TABLE OWNER.TABLE_NAME;
Before you downgrade to release 8.1.7 or lower, drop all bitmap secondary indexes on non-partitioned and partitioned index organized tables in your database. To identify existing bitmap 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 = 'BITMAP' AND i.table_name = t.table_name AND t.owner = i.table_owner AND t.iot_type = 'IOT';
Before you downgrade to release 8.1.7 or lower, after dropping all bitmap secondary indexes on non-partitioned and partitioned index-organized tables, you need to rebuild the corresponding index-organized tables without mapping tables.
To identify index-organized tables with mapping tables, issue the following SQL statement:
SELECT owner, iot_name FROM dba_tables WHERE iot_type = 'IOT_MAPPING';
For each of the tables (for example iot), you can rebuild without mapping tables as follows:
ALTER TABLE iot MOVE NOMAPPING;
Before you downgrade to release 8.1.7 or lower, drop all B-tree indexes on heap and index organized tables. To identify such B-tree indexes, issue the following SQL statement:
SELECT index_owner, index_name FROM dba_ind_columns ic, dba_tab_columns tc WHERE tc.data_type = 'UROWID' AND tc.table_name = ic.table_name AND tc.column_name = ic.column_name;
Before downgrading to release 8.1.7 or lower, remove Any index with large keys. To identify such indexes, issue the following SQL statement:
SELECT u.name, o.name, i.flags FROM sys.obj$ o, sys.user$ u, sys.ind$ i WHERE u.user# = o.owner# AND o.obj# = i.obj# AND BITAND(i.flags, 16384) != 0;
Drop any indexes identified by this statement.
This section describes removing incompatibilities relating to release 9.0 partitioning methods.
Before you downgrade to release 8.1.7 or lower, drop all partitioned tables that use list methods. To identify existing tables partitioned with list methods, issue the following SQL statement:
SELECT u.name AS OWNER, o.name AS TABLE_NAME FROM sys.user$ u, sys.obj$ o, sys.partobj$ po WHERE po.parttype = 4 AND o.obj# = po.obj# AND o.owner# = u.user#;
If you do not need to preserve the table data, then, for each table represented by the OWNER
.TABLE_NAME
columns, simply drop the table:
DROP TABLE OWNER.TABLE_NAME;
However, if you need to preserve the table data, then copy the data into non-partitioned tables, or copy the data into tables partitioned by range, hash, or another composite method.
This section describes removing incompatibilities relating to hash partitioned index-organized tables.
Before you downgrade to release 8.1.7 or lower, drop all hash partitioned index-organized tables. To identify existing hash partitioned index-organized tables, issue the following SQL statement:
SELECT t.OWNER, t.TABLE_NAME FROM dba_tables t, dba_part_tables p WHERE t.table_name = p.table_name AND t.owner = p.owner AND t.iot_type = 'IOT' AND t.partitioned = 'YES' AND p.partitioning_type = 'HASH';
If you do not need to preserve the table data, then, for each table represented by the OWNER
.TABLE_NAME
columns, simply drop the table:
DROP TABLE OWNER.TABLE_NAME;
However, if you need to preserve the table data, then you can do it in one of the following ways:
CREATE TABLE ... AS SELECT
statement.
CREATE range or non-partitioned index-organized table ... AS SELECT * FROM OWNER.TABLE_NAME; DROP TABLE OWNER.TABLE_NAME;
Before you downgrade to release 8.1.7 or lower, remove all PDML ITL invariants. To identify existing PDML ITL invariants, issue the following SQL statement:
SELECT COUNT(*) FROM sys.tab$ WHERE BITAND(property, 536870912) > 0;
If this query returns a result greater than 0
, then perform the following steps:
This section describes removing incompatibilities relating to partitioned index-organized tables with LOBs.
Before you downgrade to release 8.1.7 or lower, drop all LOB columns in partitioned index-organized tables. To identify existing partitioned index-organized tables with LOB columns, issue the following SQL statement:
SELECT t.OWNER, t.TABLE_NAME, l.COLUMN_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' AND t.partitioned = 'YES';
If you do not need to preserve the LOB columns and their data, then, for each column represented by the COLUMN_NAME
column in the table represented by the OWNER
.TABLE_NAME
columns, simply drop the column:
ALTER TABLE OWNER.TABLE_NAME DROP COLUMN COLUMN_NAME;
However, if you need to preserve the LOB columns, then you can create corresponding non-partitioned index-organized tables:
CREATE non-partitioned index-organized table ... AS SELECT * FROM OWNER.TABLE_ NAME; DROP TABLE OWNER.TABLE_NAME;
Before you downgrade to release 8.1.7 or lower, drop all varray columns in partitioned index-organized tables. To identify existing partitioned index-organized tables with varray columns, issue the following SQL statement:
SELECT v.OWNER, v.PARENT_TABLE_NAME, v.PARENT_TABLE_COLUMN 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' AND t.partitioned = 'YES';
If you do not need to preserve the varray columns and their data, then, for each column represented by the PARENT_TABLE_COLUMN
column in the table represented by the OWNER
.PARENT_TABLE_NAME
columns, simply drop the column:
ALTER TABLE OWNER.PARENT_TABLE_NAME DROP COLUMN PARENT_TABLE_COLUMN;
However, if you need to preserve the varray columns, then you can create corresponding non-partitioned index-organized tables:
CREATE non-partitioned index-organized table ... AS SELECT * FROM OWNER.PARENT_ TABLE_NAME; DROP TABLE OWNER.PARENT_TABLE_NAME;
This section describes disabling datatypes that are available only in release 9.0.1 and higher.
Before you downgrade to release 8.1.7 or lower, the following datetime and interval datatypes have to be dropped:
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
However, when the datatype is TIMESTAMP WITH LOCAL TIME ZONE
, the TIMESTAMP WITH LOCAL TIME ZONE
columns can be converted to DATE
columns by explicitly issuing an ALTER TABLE
statement.
The ALTER TABLE
statement scans all rows of the table. If the TIMESTAMP WITH LOCAL TIME ZONE
data has fractional seconds, the row data for the column will be updated by rounding the fractional seconds; if the TIMESTAMP WITH LOCAL TIME ZONE
data has the minute field greater than or equal to 60, the row data for the column will be updated by subtracting 60 from its minute field. When modifying a TIMESTAMP WITH LOCAL TIME ZONE
column to a DATE
column, the information for fractional seconds and time zone adjustment will be lost.
Downgrading will fail if any of the following objects exist in the database:
These objects have to be dropped in order to downgrade to a previous release.
To list tables with columns of type TIMESTAMP
, issue the following SQL statement:
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE data_type LIKE 'TIMESTAMP(%)';
For each table listed as a result of this statement, drop its TIMESTAMP
datatype columns, or drop the whole table.
To list tables with columns of type TIMESTAMP WITH TIME ZONE
, issue the following SQL statement:
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE data_type LIKE 'TIMESTAMP(%) WITH TIME ZONE';
For each table listed as a result of this statement, drop its TIMESTAMP WITH TIME ZONE
datatype columns, or drop the whole table.
To list tables with columns of type TIMESTAMP WITH LOCAL TIME ZONE
, issue the following SQL statement:
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE data_type LIKE 'TIMESTAMP(%) WITH LOCAL TIME ZONE';
For each table listed as a result of this statement, drop its TIMESTAMP WITH LOCAL TIME ZONE
datatype columns, or drop the whole table.
To list tables with columns of type INTERVAL YEAR TO MONTH
, issue the following SQL statement:
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE data_type LIKE 'INTERVAL YEAR(%) TO MONTH';
For each table listed as a result of this statement, drop its INTERVAL YEAR TO MONTH
datatype columns, or drop the whole table.
To list tables with columns of type INTERVAL DAY TO SECOND
, issue the following SQL statement:
SELECT owner, table_name, column_name FROM dba_tab_columns WHERE data_type LIKE 'INTERVAL DAY(%) TO SECOND';
For each table listed as a result of this statement, drop its INTERVAL DAY TO SECOND
datatype columns, or drop the whole table.
To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP
, issue the following SQL statement:
SELECT owner, object_name, package_name, argument_name FROM all_arguments WHERE data_type = 'TIMESTAMP';
To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP WITH TIME ZONE
, issue the following SQL statement:
SELECT owner, object_name, package_name, argument_name FROM all_arguments WHERE data_type = 'TIMESTAMP WITH TIME ZONE';
To find a list of procedures and functions declared with arguments or a result of type TIMESTAMP WITH LOCAL TIME ZONE
, issue the following SQL statement:
SELECT owner, object_name, package_name, argument_name FROM all_arguments WHERE data_type = 'TIMESTAMP WITH LOCAL TIME ZONE';
To find a list of procedures and functions declared with arguments or a result of type INTERVAL YEAR TO MONTH
, issue the following SQL statement:
SELECT owner, object_name, package_name, argument_name FROM all_arguments WHERE data_type = 'INTERVAL YEAR TO MONTH';
To find a list of procedures and functions declared with arguments or a result of type INTERVAL DAY TO SECOND
, issue the following SQL statement:
SELECT owner, object_name, package_name, argument_name FROM all_arguments WHERE data_type = 'INTERVAL DAY TO SECOND';
To find a list of object types with attributes of type TIMESTAMP
, or member functions with arguments or a result of type TIMESTAMP
, issue the following SQL statement:
SELECT owner, type_name, attr_name FROM dba_type_attrs WHERE attr_type_name = 'TIMESTAMP'; SELECT owner, type_name, method_name, param_name FROM dba_method_params WHERE param_type_name = 'TIMESTAMP'; SELECT owner, type_name, method_name FROM dba_method_results WHERE result_type_name = 'TIMESTAMP';
To find a list of object types with attributes of type TIMESTAMP WITH TIME ZONE
, or member functions with arguments or a result of type TIMESTAMP WITH TIME ZONE
, issue the following SQL statement:
SELECT owner, type_name, attr_name FROM dba_type_attrs WHERE attr_type_name = 'TIMESTAMP WITH TIME ZONE'; SELECT owner, type_name, method_name, param_name FROM dba_method_params WHERE param_type_name = 'TIMESTAMP WITH TIME ZONE'; SELECT owner, type_name, method_name FROM dba_method_results WHERE result_type_name = 'TIMESTAMP WITH TIME ZONE';
To find a list of object types with attributes of type TIMESTAMP WITH LOCAL TIME ZONE
, or member functions with arguments or a result of type TIMESTAMP WITH LOCAL TIME ZONE
, issue the following SQL statement:
SELECT owner, type_name, attr_name FROM dba_type_attrs WHERE attr_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE'; SELECT owner, type_name, method_name, param_name FROM dba_method_params WHERE param_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE'; SELECT owner, type_name, method_name FROM dba_method_results WHERE result_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';
To find a list of object types with attributes of type INTERVAL YEAR TO MONTH
, or member functions with arguments or a result of type INTERVAL YEAR TO MONTH
, issue the following SQL statement:
SELECT owner, type_name, attr_name FROM dba_type_attrs WHERE attr_type_name = 'INTERVAL YEAR TO MONTH'; SELECT owner, type_name, method_name, param_name FROM dba_method_params WHERE param_type_name = 'INTERVAL YEAR TO MONTH'; SELECT owner, type_name, method_name FROM dba_method_results WHERE result_type_name = 'INTERVAL YEAR TO MONTH';
To find a list of object types with attributes of type INTERVAL DAY TO SECOND
, or member functions with arguments or a result of type INTERVAL DAY TO SECOND
, issue the following SQL statement:
SELECT owner, type_name, attr_name FROM dba_type_attrs WHERE attr_type_name = 'INTERVAL DAY TO SECOND'; SELECT owner, type_name, method_name, param_name FROM dba_method_params WHERE param_type_name = 'INTERVAL DAY TO SECOND'; SELECT owner, type_name, method_name FROM dba_method_results WHERE result_type_name = 'INTERVAL DAY TO SECOND';
To find a list of collection types with elements of type TIMESTAMP
, issue the following SQL statement:
SELECT owner, type_name, coll_type FROM dba_coll_types WHERE elem_type_name = 'TIMESTAMP';
To find a list of collection types with elements of type TIMESTAMP WITH TIME ZONE
, issue the following SQL statement:
SELECT owner, type_name, coll_type FROM dba_coll_types WHERE elem_type_name = 'TIMESTAMP WITH TIME ZONE';
To find a list of collection types with elements of type TIMESTAMP WITH LOCAL TIME ZONE
, issue the following SQL statement:
SELECT owner, type_name, coll_type FROM dba_coll_types WHERE elem_type_name = 'TIMESTAMP WITH LOCAL TIME ZONE';
To find a list of collection types with elements of type INTERVAL YEAR TO MONTH
, issue the following SQL statement:
SELECT owner, type_name, coll_type FROM dba_coll_types WHERE elem_type_name = 'INTERVAL YEAR TO MONTH';
To find a list of collection types with elements of type INTERVAL DAY TO SECOND
, issue the following SQL statement:
SELECT owner, type_name, coll_type FROM dba_coll_types WHERE elem_type_name = 'INTERVAL DAY TO SECOND';
This section describes disabling features related to user-defined datatypes that are only available in release 9.0.1 and higher.
Before you downgrade to release 8.1.7 or lower, drop all user-defined aggregate functions. To identify existing user-defined aggregate functions, issue the following SQL statement:
SELECT procedure_name FROM dba_procedures WHERE aggregate = 'YES';
Drop all aggregate functions listed.
Before you downgrade to release 8.1.7 or lower, all evolved types and their dependent types and tables must be removed. To identify all evolved types, issue the following SQL statement:
SELECT UNIQUE owner, type_name FROM dba_types WHERE version_name != '$8.0';
To identify all tables that reference an evolved type, issue the following SQL statement:
SELECT UNIQUE owner, table_name FROM dba_tab_columns WHERE data_type_owner IS NOT NULL AND version_name != '$8.0';
Before you downgrade to release 8.1.7 or lower, discontinue use of all subtypes and non-final types in tables. To identify the use of existing subtypes and non-final types in tables, issue the following SQL statement:
SELECT c.name AS COLUMN_NAME, o.name AS TABLE_NAME, u.name AS TABLE_OWNER FROM user$ u, sys.obj$ o, sys.col$ c, sys.coltype$ ct, sys.type$ t WHERE u.user# = o.owner# AND o.obj# = c.obj# AND c.obj# = ct.obj# AND c.intcol# = ct.intcol# and ct.toid = t.toid AND o.type# = 2 AND BITAND(t.properties, 3153928) > 0;
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 to release 8.1.7 or lower, discontinue use of all pipelined table functions. To identify existing pipelined table functions, issue the following SQL statement:
SELECT procedure_name FROM dba_procedures WHERE pipelined = 'YES';
Before you downgrade to release 8.1.7 or lower, discontinue use of all parallel table functions. To identify existing parallel table functions, issue the following SQL statement:
SELECT procedure_name FROM dba_procedures WHERE parallel = 'YES';
This section describes removing incompatibilities relating to constraints and triggers.
Before you downgrade to release 8.1.7 or lower, drop all view related primary key, unqiue, and foreign key constraints. To identify existing view constraints, issue the following SQL statement:
SELECT * FROM dba_constraints WHERE view_related = 'DEPEND_ON_VIEW';
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 previous release.
If your database fails to open after lowering the value of the COMPATIBLE
initialization parameter, then some incompatibilities still exist. If so, reset the COMPATIBLE
initialization parameter to the higher setting. Remove the incompatibilities and attempt to reset database compatibility again. All incompatibilities with the release to which you are downgrading must be removed before you proceed with the downgrade process.
See Also:
"Remove Incompatibilities" for information about removing incompatibilities |
Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.
See Also:
"Remove Incompatibilities" if you have not yet removed incompatibilities |
Complete the following steps to downgrade your release 9.2 database to the previous Oracle release:
ORACLE_HOME
/rdbms/admin
directory.SYSDBA
privileges.MIGRATE
mode:
SQL> STARTUP MIGRATE
You may need to use the PFILE
option to specify the location of your initialization parameter file.
SQL> SPOOL downgrade.log
If you want to see the complete detailed output of the script you will run, then you can also issue a SET ECHO ON
command:
SQL> SET ECHO ON
d
old_release
.sql
, where old_release
refers to the release to which you are downgrading. See Table 7-1 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
Downgrading To | Run Script |
---|---|
9.0.1 |
|
8.1.7 |
|
Note: If the release to which you are downgrading is not included in Table 7-1, then see the README files in the new installation for the correct downgrade script to run. |
The following are notes about running the script:
d0801070.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.
SQL> 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 downgrade.log
. Correct any problems you find in this file and rerun the appropriate downgrade script if necessary.
If you issued a SET ECHO ON
command, then you may want to issue a SET ECHO OFF
command now:
SQL> SET ECHO OFF
SQL> SHUTDOWN IMMEDIATE
If you are downgrading a cluster database, then shut down all instances.
If you are downgrading to release 8.1.7, then copy the following files from the release 9.2 Oracle home to the release 8.1.7 Oracle home:
OracleService
SID
Oracle service of the release 9.2 database, where SID
is the instance name.
For example, if your SID is ORCL, then enter the following at a command prompt:
C:\> NET STOP OracleServiceORCL
C:\> ORADIM -DELETE -SID ORCL
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 release 8.1.7, 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:\> ORADIM -NEW -SID ORCL -INTPWD TWxy579 -MAXUSERS 10 -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA
SPFILE
(server parameter file) entry, then complete the following steps:
CREATE PFILE[=pfile-name] [FROM spfile-name];
The initialization parameter file will be created as a text file. In an Oracle9i Real Application Clusters environment, it will contain all parameter settings of all instances.
SPFILE
parameter to specify a server parameter file, then change the SPFILE
parameter to an IFILE
parameter in the initialization parameter file used to start up the instance. Make sure the IFILE
parameter points to the initialization parameter file that you exported from the server parameter file.You can use the IFILE
parameter in each instance-specific parameter file to point to the initialization parameter file that you exported from the server parameter file.
ORACLE_HOME
/dbs
on UNIX platforms and in ORACLE_HOME
\database
on Windows operating systems. The initialization parameter file can reside anywhere you wish, but it should not reside in the release 9.2 Oracle home.IFILE
(include file) entry and the file specified in the IFILE
entry resides within the release 9.2 Oracle home directory, then copy the file specified by the IFILE
entry to the Oracle home of the release to which you are downgrading. The file specified in the IFILE
entry contains additional initialization parameters. After you copy this file, edit the parameter file to point to its new location.ORACLE_HOME
/dbs/orapw
sid
. On Windows operating systems, the default password file is ORACLE_HOME
\database\pwd
sid
.ora
. On both UNIX platforms and Windows operating systems, sid is your Oracle instance ID.
_SYSTEM_TRIG_ENABLED = false JOB_QUEUE_PROCESSES = 0 AQ_TM_PROCESSES = 0
If you are downgrading to release 9.0.1, then add the following aditional initialization parameter to your parameter file:
NLS_LENGTH_SEMANTICS = BYTE
These initialization parameters should be removed from your parameter file after the downgrade is complete.
ORACLE_HOME
/rdbms/admin
directory of the previous release.SYSDBA
privileges.RESTRICT
mode:
STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
SPOOL old_scripts.log
If you want to see the complete detailed output of the scripts you will run, then you can also issue a SET ECHO ON
command:
SET ECHO ON
utlip.sql
:
@utlip.sql
The utlip.sql
script invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will happen in the format required by the database. It also reloads packages STANDARD
and DBMS_STANDARD
, which are necessary for any PL/SQL compilations.
See Also:
"Changing Word Size" for more information about changing word size |
catalog.sql
:
@catalog.sql
catproc.sql
:
@catproc.sql
catrep.sql
:
@catrep.sql
caths.sql
:
@caths.sql
If you are downgrading to release 9.0.1, then run the following script:
@ORACLE_HOME/javavm/install/jvmd901.sql
If you are downgrading to release 8.1.7, then run the following script:
@ORACLE_HOME/javavm/install/jvmd817.sql
If you are downgrading to release 9.0.1, then run the following script:
@ORACLE_HOME/xdk/admin/xmld901.sql
If you are downgrading to release 8.1.7, then run the following script:
@ORACLE_HOME/xdk/admin/xmld817.sql
If you are downgrading to release 9.0.1, then run the following script:
@ORACLE_HOME/mgw/admin/mgwd901.sql
If you are downgrading to release 9.0.1, then run the following script:
@ORACLE_HOME/rdbms/admin/owmd901.plb
utlrp.sql
. This step is optional and can be done regardless of whether there was a change in word-size.
@utlrp.sql
The utlrp.sql
script recompiles all existing PL/SQL modules that were previously in an INVALID
state, such as packages, procedures, types, and so on. 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
.
SPOOL OFF
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 21; the suggested name was catoutd2.log
. Correct any problems you find in this file and rerun the appropriate script if necessary.
If you issued a SET ECHO ON
command, then you may want to issue a SET ECHO OFF
command now:
SET ECHO OFF
SHUTDOWN IMMEDIATE
Your database is now downgraded.
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|