Critical Patch Update JAN 2008 Linux

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip

Download it from OracleMetaLink

SYSTEM Overview

Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.

Check OPatch Version

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.

[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate

Invoking OPatch 11.1.0.6.2

[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395

Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]

Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Post Installation Instructions

[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:

Loading Modified .sql Files into the Database

There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.

You can go directly to Recompiling Views in the Database

You must recompile views for all databases except the following:

Databases created with Release 11.1.0.6 or later

Databases 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

The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. 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.

If you want to check whether view recompilation has already been performed for the database, execute the following statement.

SELECT * FROM registry$history where ID = '6452863';

[oracle@oracle11gr1]$ sqlplus /nolog

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected

If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.

To recompile the views in the database, follow these steps:

-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2715
-----------------------------------------------------------------------

Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.

-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log

SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit

If any invalid objects were reported, run the utlrp.sql script as follows:

[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22

[Output Edited]
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50

PL/SQL procedure successfully completed.

[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;