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

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

http://orababy.blogspot.kr/2013/08/downgrade-oracle-database-to-earlier.html

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 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 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