revert back to 10.2.0.2 from 10.2.0.3

[Ratzie]

Unfortunately because of Vendor specifications we need to revert back our Oracle database from 10.2.0.3 to 10.2.0.2
Is there an easy way to do this?

[Steven E. Protter]

Shalom,

Try export import, but this is a tough thing to pull off.

Review the upgrade procedure. If there was a data upgrade/conversion, its going to be difficult.

SEP

[Ariel Cary]

Hi,

For a detailed step-by-step downgrading procedure, take a look at this chapter.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#i1010243

In particular, check for incompatibility issues. This is what the documentation says: "... If the compatibility level of your release 10.2 database is 10.2.0 or higher, then you will not be able to downgrade. ... Check your COMPATIBLE initialization parameter setting by issuing the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter WHERE name='compatible';
"

If you hit this compatibility issue, then your fastest option would be data pump; just be careful to use the 10.2.0.2 version of expdp.

HTH,

-Ariel

[Yogeeraj_1 · Accepted solution]

hi,

There is not much differences between 10.2.0.3 and 10.2.0.2 except that 10.2.0.3 contains bug fixes for 10.2.0.2.

There should not be any problem. However, if you really need to revert back, you should to through the steps found the installation readme file (10.2.0.3).

Below a quote from the file:

Removing the Patch Set Software for Single Instance Installation
========================================
Perform the following steps for removing the patch set from the 10.2.0.3 patch release:

Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:

$ sqlplus /NOLOG
SQL> CONNECT SYS/SYS_password AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT


Take a backup of the following file in 10.2.0.3 Oracle home directory:

$ ORACLE_HOME/rdbms/admin/catrelod.sql
$ ORACLE_HOME/network/admin/tnsnames.ora


Enter the following SQL*Plus commands:

$ cd $ORACLE_HOME
$ sqlplus /NOLOG
SQL> CONNECT SYS/SYS_password AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL downgrade.out
SQL> @?/rdbms/admin/catdwgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT


Review the downgrade.out file for errors.

Restore the Oracle10g installation and Central Inventory (see /etc/oraInst.loc) that you backed up before applying the patch set.

After restoring the original Oracle home, copy the saved version of catrelod.sql script into the restored Oracle home rdbms/admin directory. Also copy the saved version of 10.2.0.3/network/admin/tnsnames.ora directory into the restored Oracle home /network/admin/tnsnames.ora directory, and then perform the following steps:

$ sqlplus /NOLOG
SQL> CONNECT SYS/SYS_password AS SYSDBA
SQL> STARTUP DOWNGRADE
SQL> SPOOL catrelod.out
SQL> @?/rdbms/admin/catrelod.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE


Review the catrelod.out file for errors.


Note:
Ignore the ORA-29844 and ORA-31085 errors found in the catrelod.out file. These errors are tracked with Oracle bugs 4758112 and 4425495 respectively.

After reloading the 10.2.0.2 release, the version numbers for Oracle Data Mining and Oracle OLAP Analytic Workspace remain at 10.2.0.3. These issues are tracked with Oracle bugs 4758695 and 4751917.

Start the database and recompile the remaining invalid objects:

SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql



hope this helps!
kind regards
yogeeraj

[Ratzie]

appreciated