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]](http://www.dbstyle.net/wordpress/wp-content/uploads/2013/05/1.ID-20130514.1.jpg)
选择其中某个CPU后，进入CPU的说明页面，此时可以根据不同的产品来选择不同的链接，这里选择了Database。
![4.[ID 20130514.1]](http://www.dbstyle.net/wordpress/wp-content/uploads/2013/05/4.ID-20130514.1.jpg)
点击链接后，进入MOS的说明页面。
![3.[ID 20130514.1]](http://www.dbstyle.net/wordpress/wp-content/uploads/2013/05/3.ID-20130514.1.jpg)
定位到页面中的Oracle Database位置。按照数据库平台和版本来选择PATCH的链接，进行下载。
![2.[ID 20130514.1]](http://www.dbstyle.net/wordpress/wp-content/uploads/2013/05/2.ID-20130514.1.jpg)
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$
-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.
-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]](http://www.dbstyle.net/wordpress/wp-content/uploads/2013/05/5.ID-20130514.1.jpg)
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$
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...
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 星期二 5月 14 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_
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 星期二 5月 14 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
-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$
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 星期二 5月 14 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>
SQL*Plus: Release 10.2.0.4.0 - Production ON 星期二 5月 14 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$
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 星期二 5月 14 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.
SQL*Plus: Release 10.2.0.4.0 - Production ON 星期二 5月 14 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>
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>
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