Downgrading a Database

Step 1:

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 1 (11.1) database is 11.0.0 or higher, then you are not able to downgrade.
To check the current value of the COMPATIBLE initialization parameter, enter the following SQL statement:
SQL> SELECT name, value, description FROM v$parameter
WHERE name = 'compatible';






Step 2:

Perform a full Backup of your 11g Database.

Step 3:
Log in to the system as the owner of the Oracle Database 11g Release 1 (11.1) Oracle home directory.
Note: This step is required only if Enterprise Manager Database Control is already configured for the database.
Stop the Database Control, as follows:
1. Set the ORACLE_SID environment variable to the databaseSid
2. Run the following command:
3. ORACLE_HOME/bin/emctl stop dbconsole
Step 4:

• 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


Step 5:
• Drop the SYSMAN schema:
SQL> DROP USER sysman CASCADE;

Step 6:
• Set the system to spool results to a log file for later verification of success:
SQL> SPOOL downgrade.log
• Run catdwgrd.sql:
SQL> @catdwgrd.sql
• Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
• Shut down the instance:
SQL> SHUTDOWN IMMEDIATE

Exit SQL*Plus.

In case if the downgrade is to be done on windows please complete the following steps:

• Stop all Oracle services, including the OracleServiceSID Oracle service of the Oracle database 11g Release 1 (11.1) 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
• 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 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

Step 7:

• Install the Oracle Software Version to which the database is to be downgraded.
• Set ORACLE_HOME
• Set PATH

Step 8:

Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

Step 9:

• 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 UPGRADE
• 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
o If you are downgrading to release 10.1.0.5 and you have XDB in your database, then run the following script after running catrelod.sql:
@dbmsxdbt.sql
• Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
• 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.
• 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.
• Exit SQL*Plus.

Your database is now downgraded.