Revert Oarcle Patch "10.2.0.5" to "10.2.0.1"

What is the reasons ? 10g is De-supported now, people Goes up not down

Check the README of patch 8202632 for de-installation instructions

HTH
Srini

Hi,
try below...



Removing the Patch Set Software for Single Instance Installation

Perform. the following steps for removing the patch set from the 10.2.0.5 patch release:

Use SQL*Plus to log in to the database as theSYSuser withSYSDBAprivileges:

$ sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
SQL>
SHUTDOWN IMMEDIATE
EXIT
SQL>
Take a backup of the following file in 10.2.0.5 Oracle home directory:

$ ORACLE_HOME/rdbms/admin/catrelod.sql
$ ORACLE_HOME/network/admin/tnsnames.ora
Enter the following SQL*Plus commands:

$ cd $ORACLE_HOME/bin
$ ./sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
SQL>
STARTUP DOWNGRADE
SPOOL downgrade.out
@?/rdbms/admin/catdwgrd.sql
SPOOL OFF
SHUTDOWN IMMEDIATE
EXIT
SQL>
Review thedowngrade.outfile for errors.

Restore the Oracle10ginstallation 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 ofcatrelod.sqlscript. into the restored
Oracle homerdbms/admindirectory. Also copy the saved version of10.2.0.5/network/admin/tnsnames.oradirectory
into the restored Oracle home/network/admin/tnsnames.oradirectory, and then perform. the following steps:

$ sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
SQL>
STARTUP DOWNGRADE
SPOOL catrelod.out
@?/rdbms/admin/catrelod.sql
SPOOL OFF
SHUTDOWN IMMEDIATE
SQL>
Review the catrelod.outfile for errors.


Note:
Ignore the ORA-29844 and ORA-31085 errors found in thecatrelod.outfile. These errors are tracked with Oracle bugs 4758112 and 4425495 respectively.
After reloading a 10.2.0.2 release, the version number for Oracle Data Mining remain at 10.2.0.5. This issue is tracked with Oracle bug 4758695.

After reloading a 10.2.0.1 or a 10.2.0.2 release, the version number for Oracle OLAP Analytic Workspace remain at 10.2.0.5. This issue is tracked with Oracle bug 4751917.

Start the database and recompile the remaining invalid objects:

SQL>
STARTUP
@?/rdbms/admin/utlrp.sql
SQL>
Cross-check the status of installed components with the following script.:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

check below link for more info.
http://www.dba-oracle.com/t_downgrading_oracle.htm
thanks,
DBC.

Hello Hershit,

tell me your database statistics are up to date or not?
=====
CPU overload It will/may be depends on the hardware and also the configuration of the database.

possible cause: More likely it was optimizer changes between 10.2.0.1 and 10.2.0.5. 10.2.0.1 came up with a better plan where 10.2.0.5 came up with a worse plan may be
(most of the cases after patch set upgrade or any version upgrade)

If the statistics are not collected, then basically the DB is unable to choose the right index for every execution or none, hence putting extra load on the system for full table scan.
After the patchset update your DB cache is empty and collecting statistics will help in running the database to run in the way it is designed.
There could have been other things that could have also contributed to the CPU utilisation.
Oracle enhances CBO from version to version, and gathering correct and recent statistics for CBO will help in generating effective execution plans.
The statistics from the previous version (in your case very old statistics) will make CBO to generate less optimal execution plans.

hope statistics collection daily/weekly will solve your issue. But if you are curious then look at the awr reports, the database should be collecting stats every hour and one of those stats is top sql statements.

thanks,
DBC.