Monday, January 30, 2012

History of CPU/PSU applied

When patching, you have two entities in your environment
  - Oracle Database Software
  - Oracle Database

You can check the patches installed using opatch utility which you can find in $ORACLE_HOME/OPatch:

./opatch lsinv -bugs_fixed | grep -i 'database cpu'

Following patching your database with a PSU/CPU, you want to keep a history of the patches applied.
In 11g (actually this started since 10.2.0.4), there is a new post patch installation script which does the job.
It is catbundle.sql, and is located in $ORACLE_HOME/rdbms/admin. (In previous releases this was done by catcpu.sql)
This shall be invoked with 2 parameters, patch type (PSU/CPU) and action (apply or rollback).
The purpose of the script is to update the entry in registry$history.

SQL> @catbundle.sql CPU APPLY
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.


Generating apply and rollback scripts...
Check the following file for errors:
/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_GENERATE_2012Jan30_11_07_27.log
Apply script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_APPLY.sql
Rollback script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...


SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/' || 'catbundle_CPU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;


SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/PROD/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_APPLY_2012Jan30_11_07_32.log
The control script you can use is the following - this will list which CPU is implemented into your database:

select substr(action_time,1,30) action_time,
substr(id,1,8) id,
substr(action,1,10) action,
substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) BUNDLE_SERIES,
substr(comments,1,20) comments
from registry$history;

References:
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]
Is it required to run PSU/CPU patch post installation step after creating the database using DBCA in already patched Oracle Home ? [ID 1388555.1]
Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch [ID 865255.1]

No comments:

Post a Comment

Post a Comment