 |
|
Downgrading Oracle - Downgrade tips
Oracle Tips by Burleson Consulting |
Question:
I need to downgrade my oracle
database from 10.2.0.3 to 10.2.0.2.
The closest instructions I can find is the 10g Release 2
(Downgrading a Database Back to the Previous Oracle Database
Release) guide.
As for compatibility, it says "If the compatibility level of
your release 10.2 database is 10.2.0 or higher, then you will
not be able to downgrade."
SELECT
name,
value,
description
FROM
v$parameter
WHERE
name='compatible';
I get 10.2.0.2 . Does this mean I won't be able to downgrade
Oracle?
Am I looking at the right documentation ?
Answer:
First, see
MOSC note 398372.1
(Downgrading The Database From 10.2.0 To Lower Releases).
If your COMPATIBLE parm is at 10.2, then you were using 10.2
features and you cannot downgrade directly.
From
Oracle® Database Upgrade Guide 10g Release 2 (10.2)
When you upgrade to a new release of the Oracle Database,
certain new features may make your database incompatible with
your previous release. Your upgraded Oracle database becomes
incompatible with your previous release under the following
conditions:
A new feature stores any data on disk (including data dictionary
changes) that cannot be processed with your previous release.
An existing feature behaves differently in the new environment
as compared to the old environment. This type of incompatibility
is classified as a language incompatibility.
The COMPATIBLE Initialization Parameter
The Oracle Database enables you to control the compatibility of
your database with the COMPATIBLE initialization parameter. By
default, when the COMPATIBLE initialization parameter is not set
in your parameter file, it defaults to 10.2.0 for Oracle
Database 10g release 10.2. You cannot use new Oracle Database
10g features that would make your upgraded database incompatible
until the COMPATIBLE initialization parameter is set to this
value.
Table 5-1 illustrates the default value and the range of values
of the COMPATIBLE initialization parameter in the new Oracle
Database 10g release and in each release supported for upgrading
to the new Oracle Database 10g release.
Table 5-1 The COMPATIBLE Initialization Parameter
Oracle
Default Min
Maximum
Release Value
Value Value
Oracle8i 8.1.7 8.0.0
8.0.0.0.0 8.1.7.x.x
Oracle9i 9.0.1 8.1.0
8.1.0.0.0 9.0.1.x.x
Oracle9i 9.2 8.1.0
8.1.0.0.0 9.2.0.x.x
Oracle10g 10.1 10.0.0
9.2.0.0.0 10.1.0.x.x
Oracle10g 10.2 10.2.0
9.2.0.0.0 10.2.0.x.x
**************************************************
The
Oracle® Database Upgrade Guide 10g Release 1 (10.1) has
directions for downgrading Oracle:
**********************************************
Downgrade the Oracle Database
Make sure your database is compatible with the release to which
you are downgrading before you perform the downgrade steps in
this section.
Complete the following steps to downgrade your release 10.1
database to the previous Oracle Database release:
Log in to the system as the owner of the release 10.1 Oracle
home directory.
-
At a system prompt, change to the ORACLE_HOME/rdbms/admin
directory.
-
Start SQL*Plus.
-
Connect to the database instance as a user
with SYSDBA privileges.
-
Start up the instance in DOWNGRADE mode:
SQL> STARTUP DOWNGRADE
You may need to use the PFILE option to specify the location of
your initialization parameter file.
Set the system to spool results to a log file for later
verification of success:
SQL> SPOOL
downgrade.log
Run dold_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
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
