【Patch】CPU的查找及安装




  • 1    CPU的介绍

    Critical Patch Update(以下简称CPU),是Oracle在2005年开始引入的产品安全更新策略。一般来说CPU包含了Oracle产品安全漏洞的修复补丁集(set of security bug fix)。CPU最早的雏形出现在2005年,该项目致力于为客户周期性地提供累积性的补丁以修复安全漏洞。一个CPU内包含了对多个安全漏洞的修复,并且也包括相应必需的非安全漏洞的补丁。CPU是累积型的,只要安装最新发布的CPU即可,其中包括之前发布的所有CPU的内容。

    2    CPU的获取

    通常CPU补丁会在每季度开始第一个月的15号发布,可以使用下面的链接,来了解发布的CPU情况。

    http://www.oracle.com/technetwork/topics/security/alerts-086861.html#CriticalPatchUpdates

    在网页中列出了所有以发布的CPU,如图:
    1.[ID 20130514.1]

    选择其中某个CPU后,进入CPU的说明页面,此时可以根据不同的产品来选择不同的链接,这里选择了Database。

    4.[ID 20130514.1]

    点击链接后,进入MOS的说明页面。

    3.[ID 20130514.1]

    定位到页面中的Oracle Database位置。按照数据库平台和版本来选择PATCH的链接,进行下载。

    2.[ID 20130514.1]

    3    使用Opatch工具安装CPU

    3.1  上传patch到$ORACLE_HOME目录

    注:opatch默认到$ORACLE_HOME目录查找并安装patch

    -bash-3.00$ mv p9119226_10204_Solaris-64.zip /oracle/product/10.2.0/db_1/
    -bash-3.00$ cd /oracle/product/10.2.0/db_1/
    -bash-3.00$ unzip p9119226_10204_Solaris-64.zip
    Archive: p9119226_10204_Solaris-64.zip
    creating: 9119226/
    creating: 9119226/7155250/
    creating: 9119226/7155250/files/
    .............................................
    creating: 9119226/8836684/etc/xml/
    inflating: 9119226/8836684/etc/xml/ShiphomeDirectoryStructure.xml
    inflating: 9119226/8836684/etc/xml/GenericActions.xml
    inflating: 9119226/cpu_root.sh
    -bash-3.00$

    3.2  检查opatch版本

    -bash-3.00$ cd /oracle/product/10.2.0/db_1/OPatch
    -bash-3.00$ opatch version
    Invoking OPatch 10.2.0.4.3

    OPatch Version: 10.2.0.4.3

    OPatch succeeded.

    如果opatch版本过低,需要升级OPatch工具,升级也很简单下载p6880880补丁,替换原OPatch目录即可。如图:

    5.[ID 20130514.1]

    3.3  检查CPU安装情况

    -bash-3.00$ opatch lsinv
    Invoking OPatch 10.2.0.4.3

    Oracle Interim Patch Installer version 10.2.0.4.3
    Copyright (c) 2007, Oracle Corporation. ALL rights reserved.

    Oracle Home : /oracle/product/10.2.0/db_1
    Central Inventory : /oracle/oraInventory
    FROM : /var/opt/oracle/oraInst.loc
    OPatch version : 10.2.0.4.3
    OUI version : 10.2.0.4.0
    OUI location : /oracle/product/10.2.0/db_1/oui
    Log file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_18-57-21PM.log

    Lsinventory Output file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-05-14_18-57-21PM.txt

    --------------------------------------------------------------------------------
    Installed Top-level Products (2):

    Oracle DATABASE 10g 10.2.0.1.0
    Oracle DATABASE 10g Release 2 Patch SET 3 10.2.0.4.0
    There are 2 products installed IN this Oracle Home.

    There are no Interim patches installed IN this Oracle Home.

    --------------------------------------------------------------------------------

    OPatch succeeded.
    -bash-3.00$

    没有安装任何CPU。

    3.4  预演补丁实施(可以跳过)

    可以跳过此步骤.

    -bash-3.00$ /oracle/product/10.2.0/db_1/OPatch/opatch napply -report
    Invoking OPatch 10.2.0.4.3

    Oracle Interim Patch Installer version 10.2.0.4.3
    Copyright (c) 2007, Oracle Corporation. ALL rights reserved.

    Setting N-Apply implicit patch base-directory TO /tmp/9119226
    UTIL SESSION

    Oracle Home : /oracle/product/10.2.0/db_1
    Central Inventory : /oracle/oraInventory
    FROM : /var/opt/oracle/oraInst.loc
    OPatch version : 10.2.0.4.3
    OUI version : 10.2.0.4.0
    OUI location : /oracle/product/10.2.0/db_1/oui
    Log file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_19-07-15PM.log

    Invoking utility "napply"
    Checking conflict among patches...
    Checking IF Oracle Home has components required BY patches...
    Checking conflicts against Oracle Home...
    OPatch continues WITH these patches: 7155248 7155249 7155250 7155251 7155252 7155253 7155254 7197583 7375611 7375613 7375617 7609057 7609058 8309592 8309632 8309642 8568395 8568397 8568398 8568402 8568404 8568405 8836667 8836671 8836675 8836677 8836678 8836681 8836683 8836684 8836686 9119226 9173244 9173248 9173253

    Do you want TO proceed? [y|n]
    y
    USER Responded WITH: Y

    Running prerequisite checks...

    OPatch detected non-cluster Oracle Home FROM the inventory AND will patch the LOCAL system ONLY.

    Please shutdown Oracle instances running OUT OF this ORACLE_HOME ON the LOCAL system.
    (Oracle Home = '/oracle/product/10.2.0/db_1')

    IS the LOCAL system ready FOR patching? [y|n]
    y
    USER Responded WITH: Y
    Backing up files affected BY the patch 'NApply' FOR restore. This might take a while...

    3.5  关闭实例和监听

    由于要更新Oracle软件,因此要关闭数据库和监听

    -bash-3.00$ id
    uid=202(oracle) gid=300(oinstall)
    -bash-3.00$ env|grep ORACLE
    ORACLE_SID=OSSDB10
    ORACLE_BASE=/oracle
    ORACLE_HOME=/oracle/product/10.2.0/db_1
    -bash-3.00$ sqlplus / AS sysdba

    SQL*Plus: Release 10.2.0.4.0 - Production ON 星期二 514 18:52:42 2013

    Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.

    Connected TO:
    Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options

    SQL> ALTER system checkpoint;

    System altered.

    SQL> shutdown immediate
    DATABASE closed.
    DATABASE dismounted.
    ORACLE instance shut down.
    SQL> quit
    Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
    -bash-3.00$ lsnrctl stop

    LSNRCTL FOR Solaris: Version 10.2.0.4.0 - Production ON 14-5-2013 19:14:39

    Copyright (c) 1991, 2007, Oracle. ALL rights reserved.

    Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ultrdb1)(PORT=1521)))
    The command completed successfully
    -bash-3.00$
    -bash-3.00$ ps -ef|grep ora_
    oracle 15120 15102 0 19:14:46 pts/2 0:00 grep ora_

    3.6  安装CPU补丁

    -bash-3.00$ cd /oracle/product/10.2.0/db_1/OPatch/
    -bash-3.00$ opatch napply /oracle/product/10.2.0/db_1/9119226/ -skip_subset -skip_duplicate
    Invoking OPatch 10.2.0.4.3

    Oracle Interim Patch Installer version 10.2.0.4.3
    Copyright (c) 2007, Oracle Corporation. ALL rights reserved.

    Setting N-Apply patch base-directory TO /oracle/product/10.2.0/db_1/9119226
    UTIL SESSION

    Oracle Home : /oracle/product/10.2.0/db_1
    Central Inventory : /oracle/oraInventory
    FROM : /var/opt/oracle/oraInst.loc
    OPatch version : 10.2.0.4.3
    OUI version : 10.2.0.4.0
    OUI location : /oracle/product/10.2.0/db_1/oui
    Log file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_19-15-59PM.log

    Invoking utility "napply"
    Checking conflict among patches...
    Checking IF Oracle Home has components required BY patches...
    Checking skip_duplicate
    Checking skip_subset
    Checking conflicts against Oracle Home...
    OPatch continues WITH these patches: 7155248 7155249 7155250 7155251 7155252 7155253 7155254 7197583 7375611 7375613 7375617 7609057 7609058 8309592 8309632 8309642 8568395 8568397 8568398 8568402 8568404 8568405 8836667 8836671 8836675 8836677 8836678 8836681 8836683 8836684 8836686 9119226 9173244 9173248 9173253

    Do you want TO proceed? [y|n]
    y
    USER Responded WITH: Y

    Running prerequisite checks...

    OPatch detected non-cluster Oracle Home FROM the inventory AND will patch the LOCAL system ONLY.

    Please shutdown Oracle instances running OUT OF this ORACLE_HOME ON the LOCAL system.
    (Oracle Home = '/oracle/product/10.2.0/db_1')

    IS the LOCAL system ready FOR patching? [y|n]
    y
    USER Responded WITH: Y
    Backing up files affected BY the patch 'NApply' FOR restore. This might take a while...
    ....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
    Verifying the UPDATE...
    Inventory CHECK OK: Patch ID 9173253 IS registered IN Oracle Home inventory WITH proper meta-DATA.
    Files CHECK OK: Files FROM Patch ID 9173253 are present IN Oracle Home.
    Running make FOR target iextjob
    Running make FOR target iextjobo
    Running make FOR target ioracle
    Running make FOR target itnslsnr
    Running make FOR target client_sharedlib
    Running make FOR target client_sharedlib
    Running make FOR target iwrap
    Running make FOR target genplusso

    --------------------------------------------------------------------------------
    **********************************************************************
    ** ATTENTION **
    ** **
    ** Please note that this Patch Installation IS **
    ** NOT complete until ALL the Post Installation instructions **
    ** noted IN the Readme accompanying this patch, have been **
    ** successfully completed. **
    ** **
    **********************************************************************

    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    ********************************************************************************
    ********************************************************************************
    ** ATTENTION **
    ** **
    ** Please note that the Security Patch Installation (Patch Deinstallation) IS **
    ** NOT complete until ALL the Post Installation (Post Deinstallation) **
    ** instructions noted IN the Readme accompanying this patch, have been **
    ** successfully completed. **
    ** **
    ********************************************************************************
    ********************************************************************************

    --------------------------------------------------------------------------------

    Execution OF 'sh /oracle/product/10.2.0/db_1/9119226/9119226/custom/scripts/post -apply 9119226 ':

    RETURN Code = 0

    The LOCAL system has been patched AND can be restarted.

    UtilSession: N-Apply done.
    --------------------------------------------------------------------------------
    The following warnings have occurred during OPatch execution:
    1) OUI-67294:
    *****************************************************************
    The Oracle Configuration Manager had been installed IN the
    Oracle Home. Prior TO contacting Oracle Support, please
    refer TO http://www.oracle.com/technology/documentation/ocm.html
    FOR answers TO your questions.

    Please complete the configuration OF the Software BY
    invoking the command:

    /oracle/product/10.2.0/db_1/ccr/bin/setupCCR

    *****************************************************************
    --------------------------------------------------------------------------------
    OPatch SESSION completed WITH warnings.

    OPatch completed WITH warnings.
    -bash-3.00$ ls

    3.7  再次检查CPU安装情况

    -bash-3.00$ opatch lsinv
    Invoking OPatch 10.2.0.4.3

    Oracle Interim Patch Installer version 10.2.0.4.3
    Copyright (c) 2007, Oracle Corporation. ALL rights reserved.

    Oracle Home : /oracle/product/10.2.0/db_1
    Central Inventory : /oracle/oraInventory
    FROM : /var/opt/oracle/oraInst.loc
    OPatch version : 10.2.0.4.3
    OUI version : 10.2.0.4.0
    OUI location : /oracle/product/10.2.0/db_1/oui
    Log file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_19-24-43PM.log

    Lsinventory Output file location : /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-05-14_19-24-43PM.txt

    --------------------------------------------------------------------------------
    Installed Top-level Products (2):

    Oracle DATABASE 10g 10.2.0.1.0
    Oracle DATABASE 10g Release 2 Patch SET 3 10.2.0.4.0
    There are 2 products installed IN this Oracle Home.

    Interim patches (35) :

    Patch 9173253 : applied ON Tue May 14 19:23:53 CST 2013
    Created ON 24 DEC 2009, 03:10:54 hrs PST8PDT
    Bugs fixed:
    9173253

    Patch 9173248 : applied ON Tue May 14 19:23:49 CST 2013
    Created ON 24 DEC 2009, 02:36:01 hrs PST8PDT
    Bugs fixed:
    9173248

    Patch 9173244 : applied ON Tue May 14 19:23:44 CST 2013
    Created ON 24 DEC 2009, 02:35:58 hrs PST8PDT
    Bugs fixed:
    9173244

    Patch 9119226 : applied ON Tue May 14 19:23:33 CST 2013
    Created ON 5 Jan 2010, 23:03:26 hrs PST8PDT
    Bugs fixed:
    8534387, 8290506, 9119226, 7375644, 8836308, 7150470, 7592346

    Patch 8836686 : applied ON Tue May 14 19:23:29 CST 2013
    Created ON 12 Sep 2009, 05:12:54 hrs PST8PDT
    Bugs fixed:
    8836686

    Patch 8836684 : applied ON Tue May 14 19:23:25 CST 2013
    Created ON 12 Sep 2009, 05:12:52 hrs PST8PDT
    Bugs fixed:
    8836684

    Patch 8836683 : applied ON Tue May 14 19:23:21 CST 2013
    Created ON 12 Sep 2009, 05:12:51 hrs PST8PDT
    Bugs fixed:
    8309623, 8836683

    Patch 8836681 : applied ON Tue May 14 19:23:16 CST 2013
    Created ON 12 Sep 2009, 05:12:49 hrs PST8PDT
    Bugs fixed:
    8836681, 8309587

    Patch 8836678 : applied ON Tue May 14 19:22:58 CST 2013
    Created ON 12 Sep 2009, 05:12:48 hrs PST8PDT
    Bugs fixed:
    8836678

    Patch 8836677 : applied ON Tue May 14 19:22:48 CST 2013
    Created ON 12 Sep 2009, 05:14:53 hrs PST8PDT
    Bugs fixed:
    8836677

    Patch 8836675 : applied ON Tue May 14 19:22:43 CST 2013
    Created ON 12 Sep 2009, 05:12:45 hrs PST8PDT
    Bugs fixed:
    8309637, 8836675

    Patch 8836671 : applied ON Tue May 14 19:22:39 CST 2013
    Created ON 12 Sep 2009, 05:12:44 hrs PST8PDT
    Bugs fixed:
    8836671

    Patch 8836667 : applied ON Tue May 14 19:22:35 CST 2013
    Created ON 12 Sep 2009, 05:12:43 hrs PST8PDT
    Bugs fixed:
    8836667

    Patch 8568405 : applied ON Tue May 14 19:22:31 CST 2013
    Created ON 8 Jun 2009, 15:22:49 hrs PST8PDT
    Bugs fixed:
    8568405

    Patch 8568404 : applied ON Tue May 14 19:22:19 CST 2013
    Created ON 8 Jun 2009, 15:22:47 hrs PST8PDT
    Bugs fixed:
    8568404

    Patch 8568402 : applied ON Tue May 14 19:22:15 CST 2013
    Created ON 8 Jun 2009, 15:22:45 hrs PST8PDT
    Bugs fixed:
    6870937, 8568402

    Patch 8568398 : applied ON Tue May 14 19:22:04 CST 2013
    Created ON 8 Jun 2009, 15:22:42 hrs PST8PDT
    Bugs fixed:
    6392076, 8568398

    Patch 8568397 : applied ON Tue May 14 19:21:31 CST 2013
    Created ON 8 Jun 2009, 15:22:39 hrs PST8PDT
    Bugs fixed:
    8568397

    Patch 8568395 : applied ON Tue May 14 19:21:26 CST 2013
    Created ON 8 Jun 2009, 15:22:38 hrs PST8PDT
    Bugs fixed:
    8309639, 8568395

    Patch 8309642 : applied ON Tue May 14 19:21:22 CST 2013
    Created ON 2 Apr 2009, 12:13:59 hrs PST8PDT
    Bugs fixed:
    8309642

    Patch 8309632 : applied ON Tue May 14 19:21:17 CST 2013
    Created ON 2 Apr 2009, 12:13:57 hrs PST8PDT
    Bugs fixed:
    8309632

    Patch 8309592 : applied ON Tue May 14 19:21:13 CST 2013
    Created ON 2 Apr 2009, 12:13:13 hrs PST8PDT
    Bugs fixed:
    8309592

    Patch 7609058 : applied ON Tue May 14 19:21:03 CST 2013
    Created ON 31 DEC 2008, 00:42:37 hrs PST8PDT
    Bugs fixed:
    7609058

    Patch 7609057 : applied ON Tue May 14 19:20:51 CST 2013
    Created ON 31 DEC 2008, 01:17:11 hrs PST8PDT
    Bugs fixed:
    7609057

    Patch 7375617 : applied ON Tue May 14 19:20:46 CST 2013
    Created ON 17 Sep 2008, 05:02:15 hrs PST8PDT
    Bugs fixed:
    7375617

    Patch 7375613 : applied ON Tue May 14 19:20:42 CST 2013
    Created ON 17 Sep 2008, 05:02:13 hrs PST8PDT
    Bugs fixed:
    7375613

    Patch 7375611 : applied ON Tue May 14 19:20:38 CST 2013
    Created ON 17 Sep 2008, 05:02:09 hrs PST8PDT
    Bugs fixed:
    7375611

    Patch 7197583 : applied ON Tue May 14 19:20:27 CST 2013
    Created ON 2 Jul 2008, 10:06:43 hrs PST8PDT
    Bugs fixed:
    7197583

    Patch 7155254 : applied ON Tue May 14 19:20:20 CST 2013
    Created ON 2 Jul 2008, 10:06:14 hrs PST8PDT
    Bugs fixed:
    7155254

    Patch 7155253 : applied ON Tue May 14 19:19:11 CST 2013
    Created ON 2 Jul 2008, 10:05:45 hrs PST8PDT
    Bugs fixed:
    7155253

    Patch 7155252 : applied ON Tue May 14 19:18:15 CST 2013
    Created ON 2 Jul 2008, 10:05:15 hrs PST8PDT
    Bugs fixed:
    7155252

    Patch 7155251 : applied ON Tue May 14 19:18:04 CST 2013
    Created ON 2 Jul 2008, 10:04:30 hrs PST8PDT
    Bugs fixed:
    7155251

    Patch 7155250 : applied ON Tue May 14 19:17:54 CST 2013
    Created ON 2 Jul 2008, 10:03:58 hrs PST8PDT
    Bugs fixed:
    7155250

    Patch 7155249 : applied ON Tue May 14 19:17:41 CST 2013
    Created ON 2 Jul 2008, 10:03:27 hrs PST8PDT
    Bugs fixed:
    7155249

    Patch 7155248 : applied ON Tue May 14 19:17:37 CST 2013
    Created ON 2 Jul 2008, 10:01:21 hrs PST8PDT
    Bugs fixed:
    7155248

    --------------------------------------------------------------------------------

    OPatch succeeded.
    -bash-3.00$

    3.8  数据库中运行脚本

    更新数据库,将修改过的SQL文件应用到数据库中,很多DBA在执行完上述安装命令以后就不再进行这一步,那么实际上PSU是没有完整安装的。
    如果安装PSU需要使用下面的命令:@?/rdbms/admin/catbundle.sql psu apply

    -bash-3.00$ sqlplus / AS sysdba

    SQL*Plus: Release 10.2.0.4.0 - Production ON 星期二 514 19:26:19 2013

    Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.

    Connected TO an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed SIZE 2139256 bytes
    Variable SIZE 893016968 bytes
    DATABASE Buffers 704643072 bytes
    Redo Buffers 10813440 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL> SELECT * FROM dba_registry_history;

    no ROWS selected

    SQL> SELECT action,comments FROM registry$history;

    no ROWS selected

    SQL> @?/rdbms/admin/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:
    /oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_CPU_OSSDB10_GENERATE_2013May14_19_27_39.log
    Apply script: /oracle/product/10.2.0/db_1/rdbms/admin/catbundle_CPU_OSSDB10_APPLY.SQL
    ROLLBACK script: /oracle/product/10.2.0/db_1/rdbms/admin/catbundle_CPU_OSSDB10_ROLLBACK.SQL

    PL/SQL PROCEDURE successfully completed.

    Executing script file...

    .................................................
    .................................................
    .................................................

    Commit complete.

    SQL> SPOOL off
    SQL> SET echo off
    CHECK the following log file FOR errors:
    /oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_CPU_OSSDB10_APPLY_2013May14_19_27_43.log
    SQL>

    3.9  重新编译数据库对象

    重新编译CPU相关视图。该步骤在一个数据库上永远只需要执行一次,是为了完成在2008年1月份第一次发布CPU补丁时的后续工作,如果在安装以前的PSU或者CPU时执行过这个步骤那么就可以无需再次执行,另外,即使不执行该步骤,数据库也是正常运行的,只不过意味着2008年1月份的 CPU补丁没有正常结束安装。

    SQL> @?/cpu/view_recompile/recompile_precheck_jan2008cpu.SQL

    Running precheck.SQL...

    NUMBER OF views TO be recompiled :2073
    -----------------------------------------------------------------------

    NUMBER OF objects TO be recompiled :4169
    Please follow the README.txt instructions FOR running viewrecomp.SQL

    PL/SQL PROCEDURE successfully completed.

    SQL> shutdown immediate
    DATABASE closed.
    DATABASE dismounted.
    ORACLE instance shut down.
    SQL> startup upgrade
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed SIZE 2139256 bytes
    Variable SIZE 909794184 bytes
    DATABASE Buffers 687865856 bytes
    Redo Buffers 10813440 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL> @?/cpu/view_recompile/view_recompile_jan2008cpu.SQL

    1 ROW selected.

    SP2-0768: Illegal SPOOL command
    Usage: SPOOL { <file> | OFF | OUT }
    WHERE <file> IS file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

    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 :1743
    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_OSSDB10_145月 2013_19_32_46.log
    NOT spooling currently
    SQL> shutdown immediate;
    Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
    -bash-3.00$

    3.10  检查是否有无效的对象

    如果自动编译无效对象失败,需要手动编译无效对象.

    -bash-3.00$ sqlplus / AS sysdba

    SQL*Plus: Release 10.2.0.4.0 - Production ON 星期二 514 19:37:25 2013

    Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.

    Connected TO an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed SIZE 2139256 bytes
    Variable SIZE 909794184 bytes
    DATABASE Buffers 687865856 bytes
    Redo Buffers 10813440 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL> SELECT COUNT(*) FROM dba_objects WHERE STATUS='INVALID';

    COUNT(*)
    ----------
    1727

    SQL> @?/rdbms/admin/utlrp.SQL

    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN 2013-05-14 19:38:40

    DOC> The following PL/SQL block invokes UTL_RECOMP TO recompile invalid
    DOC> objects IN the DATABASE. Recompilation TIME IS proportional TO the
    DOC> NUMBER OF invalid objects IN the DATABASE, so this command may take
    DOC> a long TIME TO EXECUTE ON a DATABASE WITH a LARGE NUMBER OF invalid
    DOC> objects.
    DOC>
    DOC> USE the following queries TO track recompilation progress:
    DOC>
    DOC> 1. Query returning the NUMBER OF invalid objects remaining. This
    DOC> NUMBER should decrease WITH TIME.
    DOC> SELECT COUNT(*) FROM obj$ WHERE STATUS IN (4, 5, 6);
    DOC>
    DOC> 2. Query returning the NUMBER OF objects compiled so far. This NUMBER
    DOC> should increase WITH TIME.
    DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
    DOC>
    DOC> This script automatically chooses serial OR parallel recompilation
    DOC> based ON the NUMBER OF CPUs available (parameter cpu_count) multiplied
    DOC> BY the NUMBER OF threads per CPU (parameter parallel_threads_per_cpu).
    DOC> ON RAC, this NUMBER IS added across ALL RAC nodes.
    DOC>
    DOC> UTL_RECOMP uses DBMS_SCHEDULER TO CREATE jobs FOR parallel
    DOC> recompilation. Jobs are created WITHOUT instance affinity so that they
    DOC> can migrate across RAC nodes. USE the following queries TO verify
    DOC> whether UTL_RECOMP jobs are being created AND run correctly:
    DOC>
    DOC> 1. Query showing jobs created BY UTL_RECOMP
    DOC> SELECT job_name FROM dba_scheduler_jobs
    DOC> WHERE job_name LIKE 'UTL_RECOMP_SLAVE_%';
    DOC>
    DOC> 2. Query showing UTL_RECOMP jobs that are running
    DOC> SELECT job_name FROM dba_scheduler_running_jobs
    DOC> WHERE job_name LIKE 'UTL_RECOMP_SLAVE_%';
    DOC>#

    PL/SQL PROCEDURE successfully completed.

    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_END 2013-05-14 19:39:40

    PL/SQL PROCEDURE successfully completed.

    DOC> The following query reports the NUMBER OF objects that have compiled
    DOC> WITH errors (objects that compile WITH errors have STATUS SET TO 3 IN
    DOC> obj$). IF the NUMBER IS higher than expected, please examine the error
    DOC> messages reported WITH each object (USING SHOW ERRORS) TO see IF they
    DOC> point TO system misconfiguration OR resource constraints that must be
    DOC> fixed BEFORE attempting TO recompile these objects.
    DOC>#

    OBJECTS WITH ERRORS
    -------------------
    42

    DOC> The following query reports the NUMBER OF errors caught during
    DOC> recompilation. IF this NUMBER IS non-zero, please query the error
    DOC> messages IN the TABLE UTL_RECOMP_ERRORS TO see IF any OF these errors
    DOC> are due TO misconfiguration OR resource constraints that must be
    DOC> fixed BEFORE objects can compile successfully.
    DOC>#

    ERRORS DURING RECOMPILATION
    ---------------------------
    0

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT COUNT(*) FROM dba_objects WHERE STATUS='INVALID';

    COUNT(*)
    ----------
    42

    SQL> SHOW errors
    No errors.

    发现仍然有42个对象无法编译。

    SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE STATUS='INVALID';

    OWNER OBJECT_NAME OBJECT_TYPE
    ----------- ------------------- -------------
    ZHJK_CM P_TEMP_FACTCELL260 PROCEDURE
    ESS_CM SP_SYNC_CM PROCEDURE
    ZHJK_PM P_TEMP_FACTMSC60 PROCEDURE
    ZHJK_PM P_TEMP_FACTMSS60 PROCEDURE
    .
    .
    .

    42 ROWS selected.

    SQL> ALTER PROCEDURE ZHJK_APP.P_NOTICE compile;

    Warning: PROCEDURE altered WITH compilation errors.

    SQL> SHOW errors
    Errors FOR PROCEDURE ZHJK_APP.P_NOTICE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/3 PL/SQL: SQL Statement ignored
    3/22 PL/SQL: ORA-00942: TABLE OR VIEW does NOT exist
    SQL>

    手动编译后依然报错,查看错误信息是对象不存在。原来是有些表已经删除,这些过程都已经作废了,但没有删除。

    3.11  查看CPU是否正确安装

    SQL> col action_time format a30
    SQL> col action format a10
    SQL> col namespace format a10
    SQL> col version format a10
    SQL> col comments format a15
    SQL> col namespace FOR a20
    SQL> col BUNDLE_SERIES FOR a5
    SQL> SELECT * FROM dba_registry_history;

    ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
    ------------------------------- ------- --------- --------- ------- -------------- -------------------
    14-5-13 07.28.26.961799 下午 APPLY SERVER 10.2.0.4 6 CPU CPUJan2010
    14-5-13 07.34.26.837943 下午 CPU 6452863 VIEW recompilation

    SQL> SELECT action_time,action,version,id,comments FROM dba_registry_history WHERE bundle_series='CPU' ORDER BY action_time;

    ACTION_TIME ACTION VERSION ID COMMENTS
    ------------------------------ ------- -------- -- -------------
    14-5-13 07.28.26.961799 下午 APPLY 10.2.0.4 6 CPUJan2010

    SQL> SELECT action,comments FROM registry$history;

    ACTION COMMENTS
    ------------ -------------------
    APPLY CPUJan2010
    CPU VIEW recompilation

    SQL>

    Speak Your Mind

    *