Below are the steps for downgrading oracle databases from 11.2.0.3 to 11.2.0.1 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 databaseStep 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 10.2.0.4 database on Linux x64 using DSTv4 had been upgraded to release 11.2.0.2, and DBMS_DST was then run to update this database to DSTv14. Then, before downgrading from release 11.2.0.3 to 10.2.0.4, you need to apply on the release 10.2.0.4 side the DSTv14 patch for 10.2.0.4 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:
SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
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
$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
Step 3: Connect to sys user from higher ORACLE_HOME and run downgrade scripts
sqlplus / as sysdba
SQL> STARTUP DOWNGRADE
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
C) set ORACLE_HOME=LOWER_ORACLE_HOME or export ORACLE_HOME=LOWER_ORACLE_HOME
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 (11.1.0.6), run the xsrelod.sql script:
SQL> @xsrelod.sql
If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the dbmsxdbt.sql script:
@dbmsxdbt.sql
SQL>spool off
Step 6: Open the database in normal mode
sqlplus / as sysdba
SQL>shutdown immediate
SQL> startup
SQL>@?/rdbms/admin/utlrp.sql
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 11.2.0.3 to 11.2.0.1
Before upgrade i.e before running catdwgrd in higher ORACLE_HOME:
COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ -----------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
LAP Catalog 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID
After upgrade:
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types VALID 11.2.0.1.0
Oracle Database Catalog Views VALID 11.2.0.1.0
JServer JAVA Virtual Machine VALID 11.2.0.1.0
Oracle XDK VALID 11.2.0.1.0
Oracle Database Java Packages VALID 11.2.0.1.0
Oracle Text INVALID 11.2.0.1.0
Oracle XML Database VALID 11.2.0.1.0
Oracle Workspace Manager VALID 11.2.0.1.0
OLAP Analytic Workspace VALID 11.2.0.1.0
OLAP Catalog VALID 11.2.0.1.0
Oracle OLAP API VALID 11.2.0.1.0
Oracle Multimedia INVALID 11.2.0.1.0
Spatial INVALID 11.2.0.1.0
Oracle Expression Filter VALID 11.2.0.1.0
Oracle Rules Manager VALID 11.2.0.1.0
Oracle Application Express VALID 3.2.1.00.1
OWB VALID 11.2.0.1.0
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>@?/rdbms/admin/catrelod.sql
SQL>shutdown immediate
SQL>startup
SQL>@utlrp.sql
scbw116_x64.reg
