Search This Blog

downgrading oracle database to earlier release , to , oracle 11g to 10g

Below are the steps for downgrading oracle databases from to and from 11g to 10g

This assumes a) you do not have oracle valult installed. 2) You do not have oracle application express 3) you do not have objects created from fixed tables 4) database is not configured for Label Security 5) this is single instance database

Step 1 : Timzone data types consideration:

A). If you previously had upgraded the database and then used the DBMS_DST PL/SQL package to update the database time zone version, then you must apply the patch for the same time zone file version into the earlier release's Oracle home before downgrading.

As an example scenario, assume that a release database on Linux x64 using DSTv4 had been upgraded to release, and DBMS_DST was then run to update this database to DSTv14. Then, before downgrading from release to, you need to apply on the release side the DSTv14 patch for for Linux x64. This ensures that your TIMESTAMP WITH TIME ZONE data is not logically corrupted during retrieval.

To find which time zone file version your database is using, run:

B). If you had 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
◦A smaller time zone file at
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

Step 3:  Connect to sys user from higher ORACLE_HOME and run downgrade scripts

sqlplus / as sysdba
SQL> drop user sysman cascade [ if you have existing sysman user]
SQL> spool downgrade.log
SQL> @?/rdbms/admin/catdwgrd.sql
SQL>spool off

Step 4:  Start Oracle database service from lower ORACLE_HOME and reload old dictionary

A) Now copy init files and password files from higher ORACLE_HOME to lower ORACLE_HOME
B) If you are on windows then delete existing Oracle database service by
 oradim -delete -sid SID_NAME from Higher ORACLE_HOME\bin

and create oracle service in lower ORACLE_HOME by
oradim -new -sid ORCL -startmode auto -srvcstart system
D) sqlplus / as sysdba [ using binaries of OLD_ORACLE_HOME]
SQL>spool reload.log

SQL> startup upgrade
SQL> @?/rdbms/admin/catrelod.sql
Additional steps:
If you are downgrading to Oracle Database 11g Release 1 (, run the xsrelod.sql script:
SQL> @xsrelod.sql
If you are downgrading to Oracle Database 10g Release 1 ( and you have XDB in your database, then run the dbmsxdbt.sql script:
SQL>spool off

Step 6: Open the database in normal mode

sqlplus / as sysdba
SQL>shutdown immediate
SQL> startup
SQL> col comp_name form a50
SQL> select comp_name,version,status from dba_registry ;

-- done--
Query from DBA_REGISTRY should show all components to earlier ORACLE_HOME versions.

Below is output from downgrading to
Before upgrade i.e before running catdwgrd in higher ORACLE_HOME:

COMP_NAME                                          VERSION                        STATUS  
-------------------------------------------------- ------------------------------ -----------                                                                                                                                                                                                              
OWB                                                           VALID                  
Oracle Application Express                                   VALID   
Oracle Enterprise Manager                                     VALID   
LAP Catalog                                                 VALID                                                                                                                                                                                                                    
Spatial                                                       VALID      
Oracle Multimedia                                             VALID
Oracle XML Database                                           VALID
Oracle Text                                                   VALID  
Oracle Expression Filter                                      VALID 
Oracle Rules Manager                                          VALID  
Oracle Workspace Manager                                      VALID 
Oracle Database Catalog Views                                 VALID
Oracle Database Packages and Types                            VALID    
JServer JAVA Virtual Machine                                  VALID 
Oracle XDK                                                    VALID
Oracle Database Java Packages                                 VALID   
OLAP Analytic Workspace                                       VALID                                                                                                                                                                                                                    
Oracle OLAP API                                               VALID   

After upgrade:

COMP_NAME                           STATUS      VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types  VALID
Oracle Database Catalog Views       VALID
JServer JAVA Virtual Machine        VALID
Oracle XDK                          VALID
Oracle Database Java Packages       VALID
Oracle Text                         INVALID
Oracle XML Database                 VALID
Oracle Workspace Manager            VALID
OLAP Analytic Workspace             VALID
OLAP Catalog                        VALID
Oracle OLAP API                     VALID
Oracle Multimedia                   INVALID
Spatial                             INVALID
Oracle Expression Filter            VALID
Oracle Rules Manager                VALID
Oracle Application Express          VALID
OWB                                 VALID

1. Oracle MultiMedia component became VALID after running utlrp.sql
2. For making Oracle Text Valid perform below step in Lower Oracle home:

sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup upgrade
SQL> drop public synonym ctx_filter_cache_statistics;
SQL> drop view ctx_filter_cache_statistics;
SQL>shutdown immediate

Follow by Email