Skip Headers
Oracle® Database Upgrade Guide
11g Release 2 (11.2)

Part Number E10819-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Downgrading a Database

This chapter guides you through the process of downgrading a database to a previous Oracle Database release. In all discussions of downgrading, it is important to understand that you can only downgrade to the release from which you upgraded. For example, if you upgraded from Oracle Database 10g Release 1 (10.1.0.5) to Oracle Database 11g Release 2 (11.2), then you could not subsequently downgrade to Oracle Database 10g Release 2 (10.2); you could only downgrade to Oracle Database 10g Release 1 (10.1.0.5).

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.

Supported Releases for Downgrading

You can downgrade both major releases and patchset releases, based on the original release from which the database was upgraded. Major release downgrades are supported back to Oracle Database 11g Release 1 (11.1.0.6), Oracle Database 10g Release 2 (10.2.0.2), and Oracle Database 10g Release 1 (10.1.0.5). Patchset downgrades are supported back to all prior 11.2 patch releases.

Note:

You cannot downgrade a database that was upgraded from Oracle Database Express Edition.

If you have Oracle Database Vault installed with your Oracle Database 11g Release 2 (11.2) database, then you can downgrade to release 11.1.0.6 and release 11.1.0.7 only.

If you have Messaging Gateway or Workspace Manager in your database, then be aware that neither of them are part of Oracle Database patchsets prior to release 10.2.0.4. Therefore, you must separately apply all relevant patches to the release 10.2.0.3 or release 10.1.0.5 Oracle home before downgrading.

Downgrade is not supported for Oracle Enterprise Manager. However, if you save your Oracle Enterprise Manager Database Control files and data before upgrading your database, then you can restore the old version of Database Control after downgrading the database.

Check for Incompatibilities

Check the compatibility level of your database to see if the database might have incompatibilities that prevent you from downgrading. If the compatibility level of your Oracle Database 11g Release 2 (11.2) database is 11.2.0 or higher, then you are not able to downgrade.

If you are downgrading to Oracle Database 11g Release 1 (11.1), then the COMPATIBLE initialization parameter must be set to 11.0.0 or lower.

If you are downgrading to Oracle Database 10g Release 2 (10.2), then the COMPATIBLE initialization parameter must be set to 10.2.0 or lower.

If you are downgrading to Oracle Database 10g Release 1 (10.1), then the COMPATIBLE initialization parameter must be set to 10.1.0.

Perform a Full Backup

Perform a full backup of your Oracle Database 11g Release 2 (11.2) database before you downgrade.

See Also:

Oracle Database Backup and Recovery User's Guide for more information

Downgrade the Database

Complete the following steps to downgrade your Oracle Database 11g Release 2 (11.2) database to a major release or a relevant patchset upgrade:

  1. If you have enabled Oracle Database Vault on your database, then you must:

    • Grant the Database Vault DV_PATCH_ADMIN role for the SYS account.

    • Disable Database Vault before downgrading the database.

    See Also:

    Oracle Database Vault Administrator's Guide for instructions about disabling Oracle Database Vault
  2. If you set the ORA_TZFILE environment variable to the full path name of the timezone.dat file when you upgraded to Oracle Database 11g Release 2 (11.2), then you must unset it if you subsequently downgrade your database.

    Two time zone files are included in the Oracle home directory:

    • The default time zone file at

      $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat
      
    • A smaller time zone file at

      $ORACLE_HOME/oracore/zoneinfo/timezone.dat
      

    If you do not unset the ORA_TZFILE variable, then connecting to the database using the smaller time zone file might produce the following errors:

    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly
    

    See Also:

    Oracle Database Globalization Support Guide for more information about datetime data types and time zone support
  3. If you have Oracle Application Express on your database, then you must copy the apxrelod.sql file from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as the temporary directory on your system.

    Make a note of the new location of this file.

  4. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

  5. This step is required only if Enterprise Manager Database Control is already configured for the database.

    Stop Database Control, as follows:

    1. Set the ORACLE_UNQNAME environment variable to the database unique name.

    2. Run the following command:

      ORACLE_HOME/bin/emctl stop dbconsole
      

    If the database being downgraded is an Oracle Real Application Clusters (Oracle RAC) database, then perform this step on all instances.

  6. If you are downgrading an Oracle RAC database to 10g Release 1 (10.1), then you must remove extra voting disks before shutting down the Oracle Clusterware stack.

    1. To see the number of voting disks used and to list voting disk paths, run the following command:

      Oracle_Clusterware_Home/bin/crsctl query css votedisk
      
    2. Remove each additional voting disk you find in the previous step by running the following command, where path is a voting disk path discovered in the previous step:

      Oracle_Clusterware_Home/bin/crsctl delete css votedisk path
      
  7. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

    Note:

    If you are downgrading a cluster database, then shut down the instance completely and change the CLUSTER_DATABASE initialization parameter to false. After the downgrade, you must set this parameter back to true.
  8. Start SQL*Plus.

  9. Connect to the database instance as a user with SYSDBA privileges.

  10. Start up the instance in DOWNGRADE mode:

    SQL> STARTUP DOWNGRADE
    

    You might be required to use the PFILE option to specify the location of your initialization parameter file.

  11. If you have Enterprise Manager configured in your database, then drop the Enterprise Manager user:

    DROP USER sysman CASCADE;
    

    Note:

    After this step, MGMT* synonyms may be invalid. Follow the guidelines in "Restoring Oracle Enterprise Manager" to validate the synonyms.
  12. Set the system to spool results to a log file for later verification of success:

    SQL> SPOOL downgrade.log
    
  13. Run catdwgrd.sql:

    SQL> @catdwgrd.sql
    

    The following are notes about running the script:

    • You must use the version of the script included with Oracle Database 11g Release 2 (11.2).

    • You must run the script in the Oracle Database 11g Release 2 (11.2) environment.

    • The script downgrades all Oracle Database components in the database to the major release or Oracle Database 11g patch 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 is displayed and the SQL*Plus session terminates without downgrading the Oracle Database data dictionary. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded. You must identify and fix the problem before rerunning the catdwgrd.sql script.

  14. 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 12; the suggested name was downgrade.log. Correct any problems you find in this file and rerun the downgrade script if necessary.

  15. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    
  16. Exit SQL*Plus.

  17. If your operating system is Linux or UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:

    • ORACLE_HOME

    • PATH

    You should also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the downgraded Oracle home.

    See Also:

    Your operating system-specific Oracle Database 11g Release 2 (11.2) installation documents for information about setting other important environment variables on your operating system
  18. If your operating system is Windows, then complete the following steps:

    1. Stop all Oracle services, including the OracleServiceSID Oracle service of the Oracle Database 11g Release 2 (11.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:

      The Oracle Database Platform Guide for Microsoft Windows that is specific to your Microsoft Windows environment for information about stopping services
    2. 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
      
    3. Create the Oracle service of the database that 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 Same SID name as the SID of the database being downgraded.
      PASSWORD 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 Maximum number of users who can be granted SYSDBA and SYSOPER privileges.
      ORACLE_HOME 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 Oracle Database 10g Release 2 (10.2), if your SID is ORCL, your PASSWORD is TWxy5791, 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 TWxy5791 -MAXUSERS 10
           -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA
      

      Note:

      You are not required to change any Windows Registry settings when downgrading a database. The oradim utility makes all necessary changes automatically.
  19. Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

    If this is an Oracle RAC database, execute the following command to return the database to single instance mode:

    SET CLUSTER_DATABASE=FALSE
    

    Note:

    If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.
    • Set the CLUSTER_DATABASE initialization parameter to false. After the downgrade, you must set this initialization parameter back to TRUE.

  20. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the previous release.

  21. Start SQL*Plus.

  22. Connect to the database instance as a user with SYSDBA privileges.

  23. Start up the instance:

    SQL> STARTUP UPGRADE
    
  24. Set the system to spool results to a log file for later verification of success:

    SQL> SPOOL reload.log
    
  25. Run catrelod.sql:

    SQL> @catrelod.sql
    

    The catrelod.sql script reloads the appropriate version of all of the database components in the downgraded database.

  26. If you are downgrading to Oracle Database 11g Release 1 (11.1.0.6), run the xsrelod.sql script:

    SQL> @xsrelod.sql
    

    Running the xsrelod.sql script avoids the following error:

    PLS-00306: wrong number or types of arguments in call
    to 'INVALIDATE_DSD_CACHE' DBMS_XS_DATA_SECURITY_EVENTS
    PL/SQL: Statement ignored
    
  27. If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the following script after running catrelod.sql:

    @dbmsxdbt.sql
    
  28. 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 24; the suggested name was reload.log. Correct any problems you find in this file and rerun the appropriate script if necessary.

  29. Shut down and restart the instance for normal operation:

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP
    

    You might be required to use the PFILE option to specify the location of your initialization parameter file.

  30. Perform this step if the database is configured for Oracle Label Security and you are downgrading to Oracle Database 10g Release 1 (10.1).

    1. Copy the olstrig.sql script from the Oracle Database 11g Release 2 (11.2) Oracle home to the Oracle home of the version to which the database is to be downgraded.

    2. Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.

      SQL> @olstrig.sql
      

    See the Oracle Label Security Administrator's Guide for more information.

  31. Run the utlrp.sql script:

    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.

  32. Exit SQL*Plus.

Your database is now downgraded.

Perform Post-Downgrade Tasks

This section discusses tasks that might be required after downgrading a database. This section contains the following topics:

Downgrading Oracle Clusterware Configuration

Use one of the following options to downgrade the Oracle Clusterware 11g configuration:

  • Use srvconfig from the Oracle Database 11g Release 2 (11.2) Oracle home. For example:

    % srvconfig -downgrade -dbname db_name -orahome 11g_Oracle_home
    
  • Run srvctl. For example:

    11g_Oracle_home/bin/srvctl remove database -d db_name
    pre-11g_Oracle_home/bin/srvctl add database -d db_name -o pre-11g_Oracle_home
    pre-11g_Oracle_home/bin/srvctl add instance -d db_name -i instance -n node
    

    Note:

    Downgrade the database to release 10g before running emca -restore.

Restoring Oracle Enterprise Manager

This task is required only if you are downgrading in some form and Oracle Enterprise Manager is configured on the host. To restore Oracle Enterprise Manager, you must have saved your Oracle Enterprise Manager files and data before upgrading.

If this is an Oracle RAC database using Oracle Clusterware, the database must be registered with the srvctl before running the emca -restore command. This needs to be executed from the ORACLE_HOME/bin of the version to which the database is being downgraded.

Run the emca -restore command with the appropriate options to restore Oracle Enterprise Manager Database Control or Grid Control to the old Oracle home. The options that you specify depend on whether the database being downgraded is an Oracle Real Application Clusters (Oracle RAC) database or an Automatic Storage Management (ASM) database, as follows:

Note:

Use the Oracle Database 11g Release 2 (11.2) version of emca for this procedure.

Single-Instance Oracle Database Without ASM

111Home/bin/emca -restore db

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database SID

  • Listener port number

Oracle RAC Database Without ASM

111Home/bin/emca -restore db -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database unique name

  • Listener port number

Single-Instance Oracle ASM Instance

111Home/bin/emca -restore asm

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • ASM port

  • ASM SID

Oracle RAC ASM Instance

111Home/bin/emca -restore asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • ASM port

Single-Instance Oracle Database With ASM

111Home/bin/emca -restore db_asm

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database SID

  • Listener port number

  • ASM port

  • ASM Oracle home

  • ASM SID [+ASM]

Oracle RAC Database and ASM Instance

111Home/bin/emca -restore db_asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database unique name

  • Listener port number

  • ASM port

  • ASM Oracle home

  • ASM SID [+ASM]

The output of emca will vary with the options you specify and the values you enter at the prompts; but it will look something like this:

> emca -restore db
 
STARTED EMCA at Mar 23, 2007 2:44:17 PM
EM Configuration Assistant, Version 11.1.0.3.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Mar 23, 2007 2:44:17 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
SEVERE: No SID specified
ORACLE_HOME for the database to be restored: /scratch/oracle/10.2.0/product/db_1
Database SID: DB102
Listener port number: 1521
Password for SYS user:  oracle
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 23, 2007 2:47:29 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /scratch/oracle/cfgtoollogs/emca/DB102/emca_2007_03_23_14_44_17.log.
Mar 23, 2007 2:47:30 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 23, 2007 2:47:41 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 23, 2007 2:48:06 PM oracle.sysman.emcp.EMDBPostConfig performRestore
INFO: Database Control started successfully
Mar 23, 2007 2:48:06 PM oracle.sysman.emcp.EMDBPostConfig performRestore
INFO: >>>> The Database Control URL is http://stadd17.us.oracle.com:1158/em <<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 23, 2007 2:48:06 PM

For an inplace patchset upgrade, the original home backup you saved before applying the patchset must be restored. This step must be repeated on all the nodes in RAC environments. Running the emca -restore command is not required for an inplace patchset upgrade.

After completing the emca -restore procedure you are ready to restore the Oracle Enterprise Manager Database files and data with the emdwgrd utility. You must run the emdwgrd utility from the Oracle Database 11g Release 2 (11.2) home. But ORACLE_HOME and other environment variables must be set to point to the Oracle home from which the upgrade originally took place.

The following procedure is for Linux and UNIX. To run it on Windows, simply substitute emdwgrd.bat for emdwgrd.

Follow these steps to restore your Database Control files and data:

  1. Set ORACLE_HOME to the Oracle home from which the database upgrade originally took place.

    For an inplace patchset upgrade, restore the backup of the original home (with one-off patch of Bug 7131048 if upgrading from 11.1.0.6), and set ORACLE_HOME to the current Oracle home.

  2. Set ORACLE_SID to the SID of the database that was upgraded and then downgraded.

  3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database upgrade originally took place.

  4. Go to the Oracle Database 11g Release 2 (11.2) home:

    cd ORACLE_HOME/bin
    
  5. Execute one of the following:

    1. For a single-instance database, run the following command, where SID is the SID of the database that was upgraded and then downgraded and save_directory is the path to the storage location you chose when saving your Database Control files and data:

      emdwgrd -restore -sid SID -path save_sirectory -tempTablespace TEMP
      
    2. For an Oracle RAC database, remote copy is required across the cluster nodes. Define an environment variable to indicate which remote copy is configured. For example:

      setenv EM_REMCP /usr/bin/scp
      

      Then, execute the following restore command:

      emdwgrd -restore -tempTablespace TEMP -cluster -sid SID10g -path save_directory
      

      If 10g Oracle home is on a shared device, add -shared to the previous command line.

  6. Enter the SYS and SYSMAN passwords when prompted by emdwgrd.

  7. On a single-instance database, the emdwgrd utility produces output similar to the following:

    Sat Apr 28 09:27:09 2007 - Verify EM DB Control files ... pass
    Sat Apr 28 09:27:09 2007 - Validating DB Connection to DB102 ... pass
    Sat Apr 28 09:27:19 2007 - Validating TEMP tablespace in DB102 ... pass
    Sat Apr 28 09:27:20 2007 - creating directory ... created
    Sat Apr 28 09:27:20 2007 - Stopping DB Control ... stopped
    Sat Apr 28 09:27:21 2007 - dropping sysman schema ... dropped
    Sat Apr 28 09:27:22 2007 - recreating sysman user ... recreated
    Sat Apr 28 09:27:23 2007 - Restoring DB Control files ... restored
    Sat Apr 28 09:27:23 2007 - Importing sysman schema ... imported
    Sat Apr 28 09:30:42 2007 - recompiling invalid objects ... recompiled
    Sat Apr 28 09:30:54 2007 - Starting DB Control ... started
    Sat Apr 28 09:32:37 2007 - DB Control was restored successfully.
    

    When emdwgrd finishes, Oracle Enterprise Manager Database Control has been downgraded to the old Oracle home.

    On an Oracle RAC database, the emdwgrd utility produces output similar to the following:

    $ /scratch/oracle/product/11.1.0/db_1/bin/emdwgrd -srcOracleHome $ORACLE_HOME -sid DB102 -path /scratch/rpattabh/ravi/tmp/dbcdir5 -restore –cluster -tempTablespace TEMP
     
    Enter sys password for database DB102?
    *****
     
    Enter sysman password for database DB102?
    *****
     
    Sat Apr 28 09:27:09 2007 - Verify EM DB Control files ... pass
    Sat Apr 28 09:27:09 2007 - Validating DB Connection to DB102 ... pass
    Sat Apr 28 09:27:19 2007 - Validating TEMP tablespace in DB102 ... pass
    Sat Apr 28 09:27:20 2007 - creating directory ... created
    Sat Apr 28 09:27:20 2007 - Stopping DB Control on all Nodes 
    stbdq04, r101b1, /oradbnas/sangeeta/10.1.0/db, stop, 0
    stbdq05, r101b2, /oradbnas/sangeeta/10.1.0/db, stop, 1
     
    Please Execute '/tmp/racdwgrd_dbctl.sh' on Node1, Node2. 
     
    Press yes to continue when the operations are successful. 
    Continue (yes/no) ?
    y
     
    ... stopped
    Sat Apr 28 09:27:21 2007 - dropping sysman schema ... dropped
    Sat Apr 28 09:27:22 2007 - recreating sysman user ... recreated
    Sat Apr 28 09:27:23 2007 - Restoring DB Control files 
    Executing Restore directories to node Node1
    Executing Restore directories to node Node2
     
    ... restored
    Sat Apr 28 09:27:23 2007 - Importing sysman schema ... imported
    Sat Apr 28 09:30:42 2007 - recompiling invalid objects ... recompiled
    Sat Apr 28 09:32:37 2007 - DB Control was restored successfully.
    Sat Apr 28 09:33:54 2007 - Starting DB Control On All nodes 
     
    Please Execute '/tmp/racdwgrd_dbctl.sh' on Node1, Node2. 
     
    Press yes to continue when the operations are successful. 
    Continue (yes/no) ?
    y
     
    ... started
    Sat Apr 28 09:38:57 2007 - Dump directory was dropped successfully.
    

Enabling Oracle Database Vault

If you use Oracle Database Vault, then you were instructed to disable it before downgrading your database. You must now re-enable Database Vault. Connect to SQL*Plus as a user who has been granted the DV_OWNER role and issue the following statements:

ALTER TRIGGER DVSYS.DV_BEFORE_DDL_TRG ENABLE;
ALTER TRIGGER DVSYS.DV_AFTER_DDL_TRG ENABLE;

See Also:

Oracle Database Vault Administrator's Guide for instructions about enabling Oracle Database Vault

Reload Oracle Application Express

Change to the directory to which you copied the apxrelod.sql file (in step 3 of the downgrade steps), and manually reload Oracle Application Express by running the apxrelod.sql file.