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

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

patch or upgrade an Oracle clustered (RAC) database

Patching or upgrading RAC database is as easy as patching or upgrading non RAC or non Clustered database, only difference is cluster_database parameter needs to be set to false while running the catupgrd.sql script(10g,11g) or catpatch.sql(9i). Other difference is one and only one instance must be opened in upgrade mode(10,g,11g) or migrate mode(9i) when running upgrade script,which is obviously the only option as setting cluster_database=false means only one instance can be mounted or opened.

OUTLINE:

1. set init parameter cluster_database=false
2. start only one instance in migrate or upgrade mode
    startup upgrade
3. run the catupgrd.sql script in same way it is run on non rac, non clustered database
4. after the catupgrd.sql script has been successfully run i.e you have queried dba_registry and it is showing all required database components have been upgraded then
 a) set the init parameter cluster_database=true
 b) start all the clustered/rac instanaces
  


 

Don't assume!

We can not assume in same way we don't believe in rule of thumbs!

consider query:

select c1, sum(c2)

from t1

group by c1

this query returns result set in sorted order of c1 but it changes in 10g R2 as 10g used HASH GROUP BY Operation to implement grouping,rather than using SORT GROUP BY as it would do in earlier versions. So Here if sorting is desired there must be explicit order by query.

Similary There can be some join queries in which users might be getting sorted result set , but they can not rely on it always, may be if execution plan changes it can not sort the result set, so if sorting is required, developers need explicit specify order by clause in query.

I remember a case in which a junior developer wrote a query to dump the table data to asciii csv file, Here was obviously clear columns data in csv need in same order as in table. But as Developer came to know about view user_tab_columns I told him, he used query on this view to estimate the maximum record length of table in csv file(rather than manully summing the all columns widths of table) what he could have done alternate way is set large linesize along with trimspool on, but he wamted to cut short work of typing select c1||','|| c2||','||c3||','||... from table. So he generated this select query from user_tab_columns. But he assumed columns orders would be same as in table name. Result was wrong columns order in csv file. So please don't assume - it was view- so not guaranteed.