CPUJan2008: One thing that every DBA should know about it.

Written by Paweł Barut
While reading documentation for latest Oracle security patch CPUJan2008 for database I've noticed one new required step. This step is: Recompile Views. One whould say that it's nothing strange, but to run this script database have to be started in UPGRADE MODE. For non-RAC installations it's not big issue, as anyway you have to plan downtime. But Oracle suggest that this recompilation can take 30 minutes:

"In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes."

Practically in my case it took no more than 5 minutes, so it's not big deal.
But it could be problem for RAC installations, that used when CPU is applied on one node, then catcpu.sql is run, then second node is being patched. So normally DB will be all the time available. But if it's needed to start database in UPGRADE mode it means that downtime is required. According to documentation it's not required to run view recompilation script during paching process. This script can be run after,

"however, the CPU installation will not be complete until the view recompilation is completed."

You can ommit this step only if:

• Databases was created with Release 11.1.0.6 or later

• Databases was created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

I hope it also means that after CPUJan2008 was applied you will not have to run this script after next CPU came out (CPUApr2008, etc).

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• Security: getting DBA rights quite easy
• CPUApr2007 and Interim patch 3 for 10.2.0.3 on Windows
• Applying Oracle 10.2.0.3 patch
• Autobackup of SPFILE and CONTROLFILE
• Blogger Beta Backup tool