Sometimes you need to roll back the applied Oracle database PSU (Patch Set Update) for whatever the reason is. So you need to know how to de-install the patch applied in quick and swift way especially if you are doing it in production environment.

On the other hand, a DBA shouldn’t resort to this unless there is a big impact on production applications since in a controlled IT change environments patches are usually applied first in test, QA, and then production and tested during these stages.

Also, another important information to mention is that Oracle is now releasing 2 patches ( 1 database, and 1 for JAVA component) every quarter. And the Java Component patch sometimes could lead to application problems with specific vendors so you need to be careful.

The following is general overview procedure in how to roll back a database patch (either for database or Java component), in my example I am referring to JULY 2015 Oracle release quarterly security patch. It’s very important that you read the (readme.html) accompanied with the patches downloaded.

1.de-installing the database patch:

• Shtudwon the database and the listener of your database using the commands:

lsnrctl stop LISTENER_TESTDB

sqlplus ‘/as sysdba’

SQL> shutdown immediate

**** Then run the Opatch utility to roll back the applied patch:

cd /app/oracle/downloaded_patch/july2015/20831110

opatch rollback -id 20831110

You should receive message similar “OPatch completed successfully”

If you have warning then you can check the logs in the directory specified in the output.

• Now the Post De-instillation will take place:

sqlplus ‘/as sysdba’

startup

cd $ORACLE_HOME/OPatch

./datapatch –verbose

*** To verify that the patch is rolled back successfully:

select * from dba_registry_sqlpatch where PATCH_ID=20831110;

Important Remark:

My recommendation is to use utilrip after that  to re-compile all database objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Also checking that all database components are vaild post de-instillation:

Select * from dba_registry;

lsnrctl start LISTENER_TESTDB

********************************************************************

2.de-installing the Java patch:

• Shtudwon the database and the listener of your database using the commands:

lsnrctl stop LISTENER_TESTDB

SQL> shutdown immediate

*** Then run the Opatch utility to roll back the applied patch:

cd /app/oracle/downloaded_patch/july2015/JAVA/21068507

opatch rollback -id 21068507

• Now the Post De-instillation will take place:

sqlplus ‘/as sysdba’

SQL> startup upgrade ;

SQL>exit

cd $ORACLE_HOME/OPatch

./datapatch –verbose

You will receive a message similar to this:

Patch 21068507 rollback: SUCCESS

SQL> shutdown immediate;

SQL> startup

Don’t forget to startup the listener:

lsnrctl start LISTENER_TESTDB

*** To verify that the patch is rolled back successfully:

select * from dba_registry_sqlpatch where PATCH_ID=21068507;

Important Remark:

My recommendation is to use utilrip after that re-compile all database objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Also checking that all database components are valid post de-instillation:

Select * from dba_registry;

I hope this would help…..