Oracle® Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01 |
|
|
View PDF |
This chapter guides you through the process of downgrading a database back to the previous Oracle Database release. This chapter covers the following topics:
See Also: Some aspects of downgrading are operating system-specific. See your operating system-specific Oracle documentation for additional instructions about downgrading on your operating system. |
In Oracle Database 10g release 10.2, downgrading is supported back to release 10.1 and release 9.2. However, if the release number of your Oracle9i database is lower than release 9.2.0.6.0, then you should install the latest patch release for release 9.2; downgrading is not supported to releases prior to release 9.2.0.6.0. Likewise, if the release number of your Oracle10g database is lower than 10.1.0.4, then you should install the latest patch for release 10.1.
If you have Messaging Gateway or Workspace Manager in your database, be aware that neither of them are part of Oracle Database patch sets. Therefore, you will need to separately apply all relevant patches to release 9.2 or 10.1 before downgrading.
Note: You do not need to first upgrade your previous database to release 9.2.0.6.0 or later, but the release 9.2.0.6.0 or later software must be installed before the downgrade from release 10.2.Likewise, you do not need to first upgrade your previous database to release 10.1.0.4 or later, but the release 10.1.0.4 or later software must be installed before the downgrade from 10.2. |
Check the compatibility level of your database to see if your database might have incompatibilities that prevent you from downgrading. If the compatibility level of your release 10.2 database is 10.2.0
or higher, then you will not be able to 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';
If you are downgrading to release 10.1, the COMPATIBLE
initialization parameter must be set to 10.1.0
or lower.
If you are downgrading to release 9.2, the COMPATIBLE
initialization parameter must be set to 9.2.0
.
Perform a full offline backup of your release 10.2 database before you downgrade.
Complete the following steps to downgrade your release 10.2 database to the Oracle Database release from which you originally upgraded:
Log in to the system as the owner of the release 10.2 Oracle home directory.
Note: This step is required only if the Enterprise Manager Database Control is already configured for the database.
Stop the Database Control, as follows:
Set the ORACLE_SID environment variable to the databaseSid
Execute the following command: ORACLE_HOME/bin/emctl stop dbconsole
If the database being downgraded is a Real Application Clusters (RAC) database, this step should be performed for all the instances.
At a system prompt, change to the ORACLE_HOME
/rdbms/admin
directory.
Note: If you are downgrading a cluster database, shut down the instance completely and change theCLUSTER_DATABASE parameter to false . After the downgrade, you must set this parameter back to true . |
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
SQL> @catdwgrd.sql
The following are notes about running the script:
You must use the version of the script included with release 10.2.
You must run the script in the release 10.2 environment.
The script downgrades all Oracle Database components in the database to the major release from which you originally upgraded.
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.
If the downgrade for a component fails, then an ORA-39709
error will be displayed and the downgrade will not complete. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded.
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Then, check the spool file and verify that there were no errors generated during the downgrade. You named the spool file in Step 7; the suggested name was downgrade.log
. Correct any problems you find in this file and rerun the downgrade script if necessary.
Shut down the instance:
SQL> SHUTDOWN IMMEDIATE
Exit SQL*Plus.
If your operating system is UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:
ORACLE_HOME
PATH
ORA_NLS33
LD_LIBRARY_PATH
See Also: Your operating system-specific Oracle Database 10g installation documents for information about setting other important environment variables on your operating system |
If your operating system is Windows, then complete the following steps:
Stop all Oracle services, including the OracleService
SID
Oracle service of the release 10.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
See Also: Your Administrator's Guide for Windows for information about stopping services |
Delete the Oracle service at a command prompt by issuing the ORADIM command. For example, if your SID is ORCL, then enter the following command:
C:\> ORADIM -DELETE -SID ORCL
Create the Oracle service of the database to which you are downgrading at a command prompt using 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:
Variable | Description |
---|---|
SID | is the same SID name as the SID of the database being downgraded. |
PASSWORD | is the password for the database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required. |
USERS | is the maximum number of users who can be granted SYSDBA and SYSOPER privileges. |
ORACLE_HOME | is the Oracle home directory of the database to which you are downgrading. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory. |
For example, if you are downgrading to release 9.2, 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
Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.
At a system prompt, change to the ORACLE_HOME
/rdbms/admin
directory of the previous release.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA
privileges.
Start up the instance:
SQL> STARTUP MIGRATE
Set the system to spool results to a log file for later verification of success:
SQL> SPOOL reload.log
Run catrelod.sql
:
SQL> @catrelod.sql
The catrelod.sql
script reloads the appropriate version of all of the database components in the downgraded database.
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 19; the suggested name was reload.log
. Correct any problems you find in this file and rerun the appropriate script if necessary.
ORA-22308: operation not allowed on evolved type
errors in the spool file may safely be ignored.
Shut down and restart the instance for normal operation:
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP
You may need to use the PFILE
option to specify the location of your initialization parameter file.
Do this step if the database is configured for Oracle Label Security. Copy the olstrig.sql
script from the 10.2 Oracle Home to the version to which the database will be downgraded. Run olstrig.sql
to re-create DML triggers on tables with Oracle Label Security policies. (See Oracle Database Enterprise User Administrator's Guide for more information.)
SQL> @olstrig.sql
Run utlrp.sql
:
SQL> @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.
Exit SQL*Plus.
Your database is now downgraded.
Note: This step is required only if you are downgrading to 10.1 and some form of Enterprise Manager is configured on the host.
Execute the emca -restore
command with the appropriate options to restore the 10.1 environment. The options that you specify depend on whether the database being downgraded is a Real Application Clusters (RAC) database or an Automatic Storage Management (ASM) database, as follows:
Non-RAC, Non-ASM database - 102Home/bin/emca -restore db
RAC, Non-ASM database - 102Home/bin/emca -restore db -cluster
Non-RAC, ASM instance - 102Home/bin/emca -restore asm
RAC ASM instance(s) - 102Home/bin/emca -restore asm -cluster
Non-RAC, Database and ASM instance - 102Home/bin/emca -restore db_asm
RAC, Database and ASM instance(s) - 102Home/bin/emca -restore db_asm -cluster
When you execute these commands, you will be prompted to enter some or all of the following information, depending on what needs to be restored:
Oracle Home (ORACLE_HOME
): Current Oracle Home
Source Oracle Home (SRC_OH
): Oracle Home from where the database will be running after downgrade
Port (PORT
): Database port after downgrade
ASM Port (ASM_PORT
): ASM instance port after downgrade
SID (SID
): SID for the non-RAC database
Database unique name (DB_UNIQUE_NAME
): Database unique name for the RAC database
ASM Oracle Home (ASM_OH
): Oracle Home from where the ASM instance will be running after downgrade
ASM SID (ASM_SID
): ASM instance SID for non-RAC ASM instance