了解Oracle补丁知识
Oracle软件版本是如何命名的我们都在使用Oracle数据库，但不知大家有没有仔细关注过自己所使用的版本，或者更深入去了解这些版本是如何命名的。作为Oracle DBA，我觉得还是有必要略知一二的，但往往有着多年经验的DBA都对这些内容比较模糊。首先，介绍如何从官方文档查找相关内容：HOME > Administrator's Guide -> 1 Overview of Administering an Oracle Database -> Identifying Your Oracle Database Software Release。我们拿11.2.0.1.0 这个版本举例，从中我们看到五个被句号所分开的数字，它们的含义如下：
- 主数据库发布号 Major Database Release Number数据库维护发布号 Database Maintanence Release Number应用服务器发布号 Application Server Release Number组建相关发布号 Component Specific Release Number平台相关发布号 Platform Specific Release Number但事实上，经过我们补丁包升级之后，Oracle数据库软件版本中的第4位或第5位将进行变化，其中第4位是补丁集(Patch Set)号，第5位则是补丁集更新(Patch Set Update, PSU)号，比如，我有一个初始化安装之后的Oracle数据库，其版本如下：
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;?PRODUCT VERSION STATUS----------------------------------------------------------------------NLSRTL 10.2.0.1.0ProductionOracleDatabase10gEnterpriseEdition10.2.0.1.064biPL/SQL 10.2.0.1.0ProductionTNS forLinux:10.2.0.1.0Production经过我补丁包升级之后如下：
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;?PRODUCT VERSION STATUS----------------------------------------------------------------------NLSRTL 10.2.0.4.0ProductionOracleDatabase10gEnterpriseEdition10.2.0.4.064biPL/SQL 10.2.0.4.0ProductionTNS forLinux:10.2.0.4.0ProductionSQL>select action,comments from registry$history; ?ACTION COMMENTS--------------------------------------------------UPGRADE Upgradedfrom10.2.0.1.0APPLY PSU 10.2.0.4.4APPLY PSU 10.2.0.4.12可见我这套数据库当前版本为10.2.0.4.12。
大家可能还不是很了解什么是Patch Set，什么叫Patch Set Update，下面会介绍。
了解补丁分类下面了解一下相关术语的解释，O记罗敏的《品悟性能优化》中有介绍：1) Version/版本针对前一个版本的所有补丁进行整理，增加新的功能或对软件有较大的改动，进行整体测试，得到一个软件版本"包"，称为版本Version。比如 11.2。2) Patch Set/补丁集在两次产品版本之间发布的一组经过全面测试的累计整体修复程序(一般每年发布一两次)，如11.2.0.2 11.2.0.3。3) Critical Patch Update(CPU)/关键补丁更新指每季度提供一次的一组高优先级修复程序（往往针对安全性问题）。对于以前的安全性修复程序而言，这些CPU是累积的，但也可包含其他修复程序，目的是解决与非安全性补丁之间的补丁冲突问题（即降低合并请求的必要性）。目前已经更名为Security Patch Update (SPU)。4) Patch Set Update(PSU)/补丁集更新从10.2.0.4起，在两个补丁集之间发布，每年发布4次；每个补丁集更新包含50~100个修复，经测试和集成后发布。如11.2.0.3.1,11.2.0.3.2。5) CRS Bundle Patch/集群软件补丁集专门修复Oracle Clusterware的补丁，以累计补丁的方式发布。6）Composite Patch从2012年4月份的Database PSU 11.2.0.3.2开始，推出一种新的概念叫Composite Patches。 这是一种新型的补丁包，它不同于其他的累积型补丁包。如果是第一次安装Composite Patches，那么该Composite Patches所包括的全部补丁都会被安装，后续安装的Composite Patches，只会安装对比前一次Composite Patches有变化的部分和新增加的补丁。关于Composite Patch更多详细内容，请阅读Composite Patches for Oracle Products [Video] [ID 1376691.1]。其他：
- Diagnostic patch 诊断补丁Patch Set Exception 个别补丁集(PSE)Interim patch 临时补丁Merged patch 合并补丁Patch bundle 补丁包看到这里，大家可能迷糊了，原来补丁也有这么多的分类名称！其实，对于Oracle DBA来说，一般只要了解PSU(Patch Set Update)和CPU(Critical Patch Update)[现更名为SPU]就行。CPU是Oracle每个季度发布的安全补丁包，而PSU则是Oracle每个季度发布的包含Bug修复的补丁包，它也包含了最新的CPU。一般情况下，我们可能仅应用PSU即可。
在这里需要提醒一下，尽管应用PSU、CPU方法并不是很困难，官方在其补丁包里提供了详细的Readme，但在生产应用之前，还是希望咨询原厂工程师并在其支持下实施。
那如何查到目前最新的PSU呢？Oracle Support站点(我更喜欢叫MOS)中有个文档专门更新PSU的内容，其文档ID位756671.1，Oracle Recommended Patches — Oracle Database。类似如下：?
?Upgrade与Update首先，我们针对所使用的数据库可能会进行如下措施，版本升级或补丁包升级，那何为版本升级、何为补丁包升级呢？比如我的当前数据库是10G R2版本，但公司最近有个升级计划，把这套数据库升级到当下最新的11G R2，这种大版本间升级动作即为Upgrade。根据公司计划在原厂工程师和DBA共同努力下，数据库已升级到11G R2，当下版本为11.2.0.3.0。这时候原厂工程师推荐把最新的PSU给打上，获得老板的批准之后，我们又把数据库进行补丁包的升级，应用了PSU Patch 14727310之后，数据库版本现在成为11.2.0.3.5，这个过程即是Update。不得不再次提醒，Upgrade和Update都希望在获得原厂的支持下进行，尤其是Upgrade，这对于企业来说是个非常大的动作！?了解OpatchOpatch是Oracle为了安装管理个别补丁而设计的工具，从Oracle 9.2版开始提供使用。通过Opatch工具，DBA可以方便安装、卸载补丁，也可以检测冲突等。针对Oracle Database Server产品，包括CRS、ASM、RAC，Opatch对应版本如下表格：
Oracle
1.0.0.0.64
10.2.0.x
"10.2.0.0.0" (description "OPatch 10.2")
10.2.0.5.1
11.1.0.x
"11.1.0.0.0" (description "OPatch 11.1")
11.1.0.9.10
11.2.0.x
"11.2.0.0.0" (description "OPatch 11.2")
11.2.0.3.3
?参考资料关于补丁更多内容，请参考以下资料：Oracle Recommended Patches — Oracle Database [ID 756671.1]?Patch Set Updates for Oracle Products [ID 854428.1]?Introduction To Oracle Database catbundle.sql [ID 605795.1]?How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]Composite Patches for Oracle Products [Video] [ID 1376691.1]OPatch 可以在什么位置找到最新版本的 OPatch？[视频] [ID 1525335.1]Oracle补丁术语介绍了解Oracle Critical Patch Update??应用补丁示例Apply PSU Patch 14727310PSU补丁包：p14727310_112030_Linux-x86-64.zip首先，请务必打开PSU包里面的Readme并详细阅读。默认的Opatch版本过低导致无法Apply 最新PSU，需要进行Opatch更新[oracle@khm7 install]$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.bk[oracle@khm7 install]$ unzip p6880880_112000_Linux-x86-64.zip-d $ORACLE_HOME/[oracle@khm7 ~]$ opatch versionOPatchVersion:11.2.0.3.3?OPatch succeeded.可见opatch版本已升级到11.2.0.3.3。下面需要解压PSU补丁包：[oracle@khm7 install]$ unzip p14727310_112030_Linux-x86-64.zip -d patch手动Apply PSU Patch，当然，我们也可以选择自动(但auto可能会出错)：
[oracle@khm7 ~]$ opatch napply -oh $ORACLE_HOME -local/install/patch/14727310/OracleInterimPatchInstaller version 11.2.0.3.3Copyright(c)2012,OracleCorporation.All rights reserved.??OracleHome:/u01/app/oracle/product/11.2.0.3/dbhome_1CentralInventory:/u01/app/oraInventory from:/u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.locOPatch version :11.2.0.3.3OUI version :11.2.0.3.0Log file location :/u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2013-03-15_13-29-02PM_1.log?Verifying environment and performing prerequisite checks...OPatch continues with these patches:1334343813696216139233741427560514727310 ?Do you want to proceed?[y|n]yUserRespondedwith: YAll checks passed.Provide your email address to be informed of security issues, install andinitiate OracleConfigurationManager.Easierfor you if you use your MyOracleSupportEmail address/UserName.Visit http://www.oracle.com/support/policies.html for details.Email address/UserName: ?You have not provided an email address for notification of security issues.Do you wish to remain uninformed of security issues ([Y]es,[N]o)[N]: y???Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.(OracleHome='/u01/app/oracle/product/11.2.0.3/dbhome_1')??Is the local system ready for patching?[y|n]yUserRespondedwith: YBacking up files...Applyingsub-patch '13343438' to OH '/u01/app/oracle/product/11.2.0.3/dbhome_1'?Patching component oracle.rdbms.rsf,11.2.0.3.0...?Patching component oracle.rdbms,11.2.0.3.0...?Patching component oracle.rdbms.dbscripts,11.2.0.3.0...?Verifying the update...Applyingsub-patch '13696216' to OH '/u01/app/oracle/product/11.2.0.3/dbhome_1'?Patching component oracle.rdbms.rsf,11.2.0.3.0...?Patching component oracle.rdbms,11.2.0.3.0...?Patching component oracle.sdo.locator,11.2.0.3.0...?Patching component oracle.sysman.console.db,11.2.0.3.0...?Patching component oracle.sysman.oms.core,10.2.0.4.4...?Verifying the update...Applyingsub-patch '13923374' to OH '/u01/app/oracle/product/11.2.0.3/dbhome_1'ApplySession:Optional component(s)[ oracle.network.cman,11.2.0.3.0]not present in the OracleHomeor a higher version is found.?Patching component oracle.rdbms.rsf,11.2.0.3.0...?Patching component oracle.rdbms,11.2.0.3.0...?Patching component oracle.rdbms.dbscripts,11.2.0.3.0...?Patching component oracle.network.rsf,11.2.0.3.0...?Patching component oracle.network.listener,11.2.0.3.0...?Patching component oracle.sysman.console.db,11.2.0.3.0...?Verifying the update...Applyingsub-patch '14275605' to OH '/u01/app/oracle/product/11.2.0.3/dbhome_1'ApplySession:Optional component(s)[ oracle.precomp.lang,11.2.0.3.0]not present in the OracleHomeor a higher version is found.?Patching component oracle.network.client,11.2.0.3.0...?Patching component oracle.network.rsf,11.2.0.3.0...?Patching component oracle.precomp.common,11.2.0.3.0...?Patching component oracle.rdbms,11.2.0.3.0...?Patching component oracle.rdbms.dbscripts,11.2.0.3.0...?Patching component oracle.rdbms.rman,11.2.0.3.0...?Patching component oracle.rdbms.rsf,11.2.0.3.0...?Patching component oracle.rdbms.util,11.2.0.3.0...?Verifying the update...Applyingsub-patch '14727310' to OH '/u01/app/oracle/product/11.2.0.3/dbhome_1'?Patching component oracle.rdbms,11.2.0.3.0...?Patching component oracle.rdbms.dbscripts,11.2.0.3.0...?Patching component oracle.rdbms.deconfig,11.2.0.3.0...?Patching component oracle.rdbms.rsf,11.2.0.3.0...?Patching component oracle.sdo.locator,11.2.0.3.0...?Patching component oracle.sysman.console.db,11.2.0.3.0...?Patching component oracle.sysman.oms.core,10.2.0.4.4...?Verifying the update...?OPatch found the word "warning"in the stderr of the make command.Please look at this stderr.You can re-run this make command.Stderr output:ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'/u01/app/oracle/product/11.2.0.3/dbhome_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'/u01/app/oracle/product/11.2.0.3/dbhome_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'/u01/app/oracle/product/11.2.0.3/dbhome_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'??Composite patch 14727310 successfully applied.OPatch Session completed with warnings.Log file location: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2013-03-15_13-29-02PM_1.log?OPatch completed with warnings.上面结果显示OPatch completed with warnings，但这里并无大碍，你可以查看日志获得更多详细内容。
查看更新结果，可见已然是11.2.0.3.5了：[oracle@khm7 ~]$ opatch lspatches14727310;DatabasePatchSetUpdate:11.2.0.3.5(14727310)查看更新补丁内容：[oracle@khm7 ~]$ opatch lsinventoryOracleInterimPatchInstaller version 11.2.0.3.3Copyright(c)2012,OracleCorporation.All rights reserved.??OracleHome:/u01/app/oracle/product/11.2.0.3/dbhome_1CentralInventory:/u01/app/oraInventory from:/u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.locOPatch version :11.2.0.3.3OUI version :11.2.0.3.0Log file location :/u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2013-03-15_13-40-00PM_1.log?LsinventoryOutput file location :/u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_13-40-00PM.txt?--------------------------------------------InstalledTop-level Products(1): ?OracleDatabase11g11.2.0.3.0There are 1 products installed inthisOracleHome.??Interim patches (1):?Patch14727310: applied on FriMar1513:35:43 CST 2013UniquePatch ID:15663328Patch description:"Database Patch Set Update : 11.2.0.3.5 (14727310)"Created on 27Dec2012,00:06:30 hrs PST8PDTSub-patch 14275605;"Database Patch Set Update : 11.2.0.3.4 (14275605)"Sub-patch 13923374;"Database Patch Set Update : 11.2.0.3.3 (13923374)"Sub-patch 13696216;"Database Patch Set Update : 11.2.0.3.2 (13696216)"Sub-patch 13343438;"Database Patch Set Update : 11.2.0.3.1 (13343438)"Bugsfixed:13566938,13593999,10350832,14138130,12919564,13624984,1358824813080778,13804294,14258925,12873183,13645875,12880299,1466435514409183,12998795,14469008,13719081,13492735,12857027,1426303614263073,13742433,13732226,12905058,13742434,12849688,1295064413742435,13464002,12879027,13534412,14613900,12585543,1253534612588744,11877623,12847466,13649031,13981051,12582664,1279776514262913,12923168,13612575,13384182,13466801,13484963,1106319113772618,13070939,12797420,13041324,12976376,11708510,1374243713026410,13737746,13742438,13326736,13001379,13099577,1427560513742436,9873405,9858539,14040433,12662040,9703627,1261712312845115,12764337,13354082,13397104,12964067,13550185,1278098312583611,14546575,13476583,15862016,11840910,13903046,1586201713572659,13718279,13657605,13448206,13419660,14480676,1363271714063281,13430938,13467683,13420224,14548763,12646784,1403582512861463,12834027,15862021,13377816,13036331,14727310,1368554413499128,15862018,12829021,15862019,12794305,14546673,1279198113503598,13787482,10133521,12718090,13399435,14023636,1240111113257247,13362079,12917230,13923374,14480675,13524899,1355969714480674,13916709,14076523,13773133,13340388,13366202,1352855112894807,13343438,13454210,12748240,14205448,13385346,1585308112971775,13035804,13544396,13035360,14062795,12693626,1333243914038787,14062796,12913474,14841409,14390252,13370330,1406279713059165,14062794,12959852,13358781,12345082,12960925,965961413699124,14546638,13936424,13338048,12938841,12658411,1262082312656535,14062793,12678920,13038684,14062792,13807411,1259403213250244,15862022,9761357,12612118,13742464,14052474,1345758213527323,15862020,12780098,13502183,13705338,13696216,1026366815862023,13554409,15862024,13103913,13645917,14063280,13011409???--------------------------------------------?OPatch succeeded.数据库启动，并加载修改SQL Files到数据库
[oracle@khm7 ~]$ sqlplus /as sysdba?SQL*Plus:Release11.2.0.3.0Production on FriMar1513:40:392013?Copyright(c)1982,2011,Oracle.All rights reserved.?Connected to an idle instance.?SQL> startupORACLE instance started.?TotalSystemGlobalArea417546240 bytesFixedSize2228944 bytesVariableSize272633136 bytesDatabaseBuffers138412032 bytesRedoBuffers4272128 bytesDatabase mounted.Database opened.SQL> show userUSER is"SYS"SQL>@?/rdbms/admin/catbundle.sql psu apply## 这个脚本执行时间跟大家的系统有关… 输出略 …执行完后我们可以查到PSU更新信息：
SQL>set line 150SQL> col ACTION_TIME for a30SQL> col ACTION for a8SQL> col NAMESPACE for a8SQL> col VERSION for a10SQL> col BUNDLE_SERIES for a5SQL> col COMMENTS for a20SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS-------------------------------------------------------------------------------------------17-SEP-1110.21.11.595816 AM APPLY SERVER 11.2.0.30 PSU Patchset11.2.0.2.015-MAR-1310.40.00.705490 AM APPLY SERVER 11.2.0.30 PSU Patchset11.2.0.2.015-MAR-1301.43.05.319842 PM APPLY SERVER 11.2.0.35 PSU PSU 11.2.0.3.5?3 rows selected.?Apply PSU Patch 14727315PSU补丁包：p14727315_112020_Linux-x86-64.zipApply PSU 14727315(11.2.0.2.0 to 11.2.0.9)方法与2.1的内容大同小异，因此这里只列出步骤：
[root@khm8 ~]# chown oracle.oinstall /install/p*Opatch更新：[oracle@khm7 install]$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.bk[oracle@khm7 install]$ unzip p6880880_112000_Linux-x86-64.zip-d $ORACLE_HOME/[oracle@khm7 ~]$ opatch versionOPatchVersion:11.2.0.3.3?OPatch succeeded.[oracle@khm8 install]$ unzip p14727315_112020_Linux-x86-64.zip-d patch?[oracle@khm8 ~]$ opatch napply -oh $ORACLE_HOME -local/install/patch/14727315/?[oracle@khm8 ~]$ opatch lspatches14727315;DatabasePatchSetUpdate:11.2.0.2.9(14727315)执行catbundle.sql脚本之前，无法看到PSU更新信息：SQL>set line 150SQL> col ACTION_TIME for a30SQL> col ACTION for a8SQL> col NAMESPACE for a8SQL> col VERSION for a10SQL> col BUNDLE_SERIES for a5SQL> col COMMENTS for a20SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS-------------------------------------------------------------------------------------------05-SEP-1006.22.14.370943 AM APPLY SERVER 11.2.0.20 PSU Patchset11.2.0.2.015-MAR-1311.22.14.833238 AM APPLY SERVER 11.2.0.20 PSU Patchset11.2.0.2.0执行脚本后，即可看到：SQL>@?/rdbms/admin/catbundle.sql psu apply?SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS-------------------------------------------------------------------------------------------05-SEP-1006.22.14.370943 AM APPLY SERVER 11.2.0.20 PSU Patchset11.2.0.2.015-MAR-1311.22.14.833238 AM APPLY SERVER 11.2.0.20 PSU Patchset11.2.0.2.015-MAR-1302.14.09.659104 PM APPLY SERVER 11.2.0.29 PSU PSU 11.2.0.2.9??Upgrade Oracle 10g R2 from 10201 to 10204 and Apply PSU Patch 9352164、12879933Upgrade Oracle 10g R2 from 10201 to 10204首先，我们需要将数据库升级到10.2.0.4版本，一切还是以Readme为参考资料，展开操作。所需包：p6810189_10204_Linux-x86-64.zip升级前，有几项准备动作。
确保参数shared_pool_size和java_pool_size至少为150M大小：SQL> alter system set shared_pool_size=150M scope=spfile;SQL> alter system set java_pool_size=150M scope=spfile;如果SGA大小设置不够大，下次启动将会保证，所以也保证SGA足够：SQL> alter system set sga_target=400M scope=spfile;关闭数据库SQL> shutdown immediate以防万一，备份Database Software。需要停止所有运行中的程序，如监听器、OEM、ISQLPLUS等。下面就解压升级包，并通过OUI进行升级即可。
[oracle@khm11 install]$ unzip p6810189_10204_Linux-x86-64.zip[oracle@khm11 install]$ cd Disk1/[oracle@khm11 Disk1]$ export DISPLAY=192.168.1.1:0.0[oracle@khm11 Disk1]$ ./runInstallerOUI交互设置的时候需要注意的地方：Specify Home Details时，NAME和目录选择原安装路径，如果环境变量设置未改变，默认即可。中间会提示用ROOT去执行脚本，执行完DATABASE SOFTWARE升级就算完成了。接下来，需要启动到升级模式，然后更新数据字典
[oracle@khm11 ~]$ sqlplus /as sysdba?SQL*Plus:Release10.2.0.4.0-Production on FriMar1516:30:422013?Copyright(c)1982,2007,Oracle.AllRightsReserved.?Connected to an idle instance.?SQL> startup upgradeORACLE instance started.?TotalSystemGlobalArea390070272 bytesFixedSize2084168 bytesVariableSize322962104 bytesDatabaseBuffers58720256 bytesRedoBuffers6303744 bytesDatabase mounted.Database opened.执行脚本，此过程非常缓慢，从下面的输出信息中可以看到，升级一共耗费了43分钟多，当然这根据不同的系统有快有慢。另外，按照README的要求，我们也可以将输出信息SPOOL到一个文件里，好方便查阅！SQL>@?/rdbms/admin/catupgrd.sql…部分内容省略...TotalUpgradeTime:00:43:33DOC>#######################################################################DOC>#######################################################################DOC>DOC>The above PL/SQL lists the SERVER components in the upgradedDOC> database, along with their current version and status.DOC>DOC>Please review the status and version columns and look forDOC> any errors in the spool log file.If there are errors in the spoolDOC> file,or any components are not VALID ornot the current version,DOC> consult the OracleDatabaseUpgradeGuidefor troubleshootingDOC> recommendations.DOC>DOC>Next shutdown immediate, restart for normal operation,andthenDOC> run utlrp.sql to recompile any invalid application objects.DOC>DOC>#######################################################################DOC>#######################################################################DOC>#数据字典更新完后重新正常启动，然后重编译失效对象
SQL> shutdown immediateSQL> startupSQL>@?/rdbms/admin/utlrp.sql--输出内容略--单实例的版本升级到这里就算完事了，当然有CATALOG恢复目录，也升级一下即可。最后查看数据库组件的版本、状态等：
SQL> col comp_name format a30SQL> col version format a30SQL> col status format a10SQL> SELECT comp_name, version, status FROM dba_registry;?COMP_NAME VERSION STATUS----------------------------------------------------------------------OracleDatabaseCatalogViews10.2.0.4.0 VALIDOracleDatabasePackagesand T 10.2.0.4.0 VALIDypes?OracleWorkspaceManager10.2.0.4.3 VALIDJServer JAVA VirtualMachine10.2.0.4.0 VALIDOracle XDK 10.2.0.4.0 VALIDOracleDatabaseJavaPackages10.2.0.4.0 VALIDOracleExpressionFilter10.2.0.4.0 VALIDOracleDataMining10.2.0.4.0 VALIDOracleText10.2.0.4.0 VALIDOracle XML Database10.2.0.4.0 VALIDOracleRuleManager10.2.0.4.0 VALIDOracle interMedia 10.2.0.4.0 VALIDOLAP AnalyticWorkspace10.2.0.4.0 VALIDOracle OLAP API 10.2.0.4.0 VALIDOLAP Catalog10.2.0.4.0 VALIDSpatial10.2.0.4.0 VALIDOracleEnterpriseManager10.2.0.4.0 VALID?17 rows selected.?Apply PSU Patch 9352164首先，我们需要明白数据库无法直接打PSU Patch 12879933，在我们阅读PSU Patch 12879933 Readme的时候可以发现如下内容：Patch Set Update PSU 10.2.0.4.12 is an overlay PSU whose base PSU is 10.2.0.4.4. This patch can only be applied in an Oracle home for which PSU 10.2.0.4.4 has already been installed.这里明显的提示我们数据库先Update到10.2.0.4.4，然后才可以Update到10.2.0.4.12。那Update到10.2.0.4.4会不会有其他要求呢？我们阅读PSU Patch 9352164的Readme会发现：To install the PSU 10.2.0.4.4 patch, the Oracle home must have the 10.2.0.4.0 Database patch set installed. Subsequent PSU patches can be installed on Oracle Database 10.2.0.4.0 or any PSU with a lower 5th numeral version than the one being installed. For example, PSU 10.2.0.4.4 can be installed on 10.2.0.4.0, 10.2.0.4.1, 10.2.0.4.2, and 10.2.0.4.3.我们当前版本为10.2.0.4.0，因此满足需求。PSU补丁包：p9352164_10204_Linux-x86-64.zip首先把所有活动程序包括OEM、监听器、数据库实例全部关闭：操作略。检查Opatch版本：
[oracle@khm11 install]$ opatch versionInvokingOPatch10.2.0.4.2?OPatchVersion:10.2.0.4.2?OPatch succeeded.解压PSU补丁包，并进行应用：
[oracle@khm11 install]$ unzip p9352164_10204_Linux-x86-64.zip-d patch[oracle@khm21 install]$ cd patch/9352164/[oracle@khm21 9352164]$ opatch applyInvokingOPatch10.2.0.5.1?OracleInterimPatchInstaller version 10.2.0.5.1Copyright(c)2010,OracleCorporation.All rights reserved.??OracleHome:/u01/app/oracle/product/10.2.0/dbhome_1CentralInventory:/u01/app/oracle/oraInventory from:/etc/oraInst.locOPatch version :10.2.0.5.1OUI version :10.2.0.4.0OUI location :/u01/app/oracle/product/10.2.0/dbhome_1/ouiLog file location :/u01/app/oracle/product/10.2.0/dbhome_1/cfgtoollogs/opatch/opatch2013-03-17_00-24-14AM.log?Patch history file:/u01/app/oracle/product/10.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt?ApplySession applying interim patch '9352164' to OH '/u01/app/oracle/product/10.2.0/dbhome_1'?Running prerequisite checks...Provide your email address to be informed of security issues, install andinitiate OracleConfigurationManager.Easierfor you if you use your MyOracleSupportEmail address/UserName.Visit http://www.oracle.com/support/policies.html for details.Email address/UserName: ?You have not provided an email address for notification of security issues.Do you wish to remain uninformed of security issues ([Y]es,[N]o)[N]: y?OPatch detected non-cluster OracleHomefrom the inventory and will patch the local system only.??Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.(OracleHome='/u01/app/oracle/product/10.2.0/dbhome_1')??Is the local system ready for patching?[y|n]yUserRespondedwith: YBacking up files and inventory (notforauto-rollback)for the OracleHomeBacking up files affected by the patch '9352164'for restore.This might take a while...Backing up files affected by the patch '9352164'for rollback.This might take a while...Execution of 'sh /install/patch/9352164/custom/scripts/pre -apply 9352164 ':??ReturnCode=0…中间大量信息略…Verifying the update...Inventory check OK:Patch ID 9352164is registered inOracleHome inventory with proper meta-data.Files check OK:FilesfromPatch ID 9352164 are present inOracleHome.?--------------------------------------------****************************************************************************************************************************************************************** ATTENTION ********Please note that the PatchSetUpdateInstallation(PSU Deinstallation)****isnot complete until all the PostInstallation(PostDeinstallation)**** instructions noted in the Readme accompanying this PSU, have been **** successfully completed.**********************************************************************************************************************************************************************?--------------------------------------------?Execution of 'sh /install/patch/9352164/custom/scripts/post -apply 9352164 ':??ReturnCode=0?Thelocal system has been patched and can be restarted.??OPatch succeeded.数据库启动，并执行脚本来加载修改SQL Files到数据库：代码代码因此，Apply PSU Patch完成，查看：
SQL> startupSQL>@?/rdbms/admin/catbundle.sql psu apply…输出信息略…因此，Apply PSU Patch完成，查看：SQL> col ACTION_TIME for a30SQL> col ACTION for a8SQL> col NAMESPACE for a8SQL> col VERSION for a10SQL> col BUNDLE_SERIES for a5SQL> col COMMENTS for a40SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS---------------------------------------------------------------------------------------------------------------16-MAR-1311.24.53.986137 PM UPGRADE SERVER 10.2.0.4.0Upgradedfrom10.2.0.1.017-MAR-1312.45.32.609861 AM APPLY SERVER 10.2.0.44 PSU PSU 10.2.0.4.4
?
Apply PSU Patch 12879933方法与Apply PSU Patch9352164类似，这里不做详细说明。PSU补丁包：p12879933_10204_Linux-x86-64.zip[oracle@khm11 install]$ unzip p12879933_10204_Linux-x86-64.zip-d patch[oracle@khm21 install]$ cd patch/12879933/[oracle@khm21 12879933]$ opatch apply启动数据库：[oracle@khm21 install]$ sqlplus /as sysdbaSQL> startup将修改过的SQL文件应用到数据库：SQL>@?/rdbms/admin/catbundle.sql opsu apply注意：如果PSU是overlay PSU，比如10.2.0.4.8，则需要执行@catbundle.sql opsu apply。编译失效对象：SQL>@?/rdbms/admin/utlrp.sql查看版本更新信息：SQL>set line 150SQL> col ACTION_TIME for a30SQL> col ACTION for a8SQL> col NAMESPACE for a20SQL> col VERSION for a10SQL> col BUNDLE_SERIES for a5SQL> col COMMENTS for a30SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPACE VERSION ID BUNDL COMMENTS-----------------------------------------------------------------------------------------------------------------16-MAR-1311.24.53.986137 PM UPGRADE SERVER 10.2.0.4.0Upgradedfrom10.2.0.1.017-MAR-1312.45.32.609861 AM APPLY SERVER 10.2.0.44 PSU PSU 10.2.0.4.417-MAR-1301.37.02.320305 AM APPLY SERVER 10.2.0.48 OPSU PSU 10.2.0.4.12?3 rows selected.打完PSU之后如下方式查看：$ opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU'9654991??? 11724977? WedMay2516:37:17 CST 2011?? DATABASE PSU 10.2.0.4.5(REQUIRES PRE-REQUISITE 9952234??? 11724977? WedMay2516:37:17 CST 2011?? DATABASE PSU 10.2.0.4.6(REQUIRES PRE-REQUISITE 10248636?? 11724977? WedMay2516:37:17 CST 2011?? DATABASE PSU 10.2.0.4.7(REQUIRES PRE-REQUISITE 11724977?? 11724977? WedMay2516:37:17 CST 2011?? DATABASE PSU 10.2.0.4.8(REQUIRES PRE-REQUISITE 8576156??? 9352164?? WedMay2515:10:48 CST 2011?? DATABASE PSU 10.2.0.4.1(INCLUDES CPUJUL2009)8833280??? 9352164?? WedMay2515:10:48 CST 2011?? DATABASE PSU 10.2.0.4.2(INCLUDES CPUOCT2009)9119284??? 9352164?? WedMay2515:10:48 CST 2011?? DATABASE PSU 10.2.0.4.3(INCLUDES CPUJAN2010)9352164??? 9352164?? WedMay2515:10:48 CST 2011?? DATABASE PSU 10.2.0.4.4(INCLUDES CPUAPR2010)?Upgrade ?Oracle 10g R2 from 10201 to 10205 and Apply PSU Patch 14727319Upgrade Oracle 10g R2 from 10201 to 10205首先，把数据库升级到10.2.0.5.0版本，操作和2.3雷同。所需包：p8202632_10205_Linux-x86-64.zip
SQL> alter system set shared_pool_size=150M scope=spfile;SQL> alter system set java_pool_size=150M scope=spfile;SQL> alter system set sga_target=400M scope=spfile;SQL> shutdown immediate[oracle@khm11 install]$ unzip p8202632_10205_Linux-x86-64.zip[oracle@khm11 install]$ cd Disk1/[oracle@khm11 Disk1]$ export DISPLAY=192.168.1.1:0.0[oracle@khm11 Disk1]$ ./runInstaller [oracle@khm11 ~]$ sqlplus /as sysdbaSQL> startup upgradeSQL>@?/rdbms/admin/catupgrd.sqlSQL> shutdown immediateSQL> startupSQL>@?/rdbms/admin/utlrp.sqlSQL> col comp_name format a30SQL> col version format a30SQL> col status format a10SQL> SELECT comp_name, version, status FROM dba_registry;?COMP_NAME VERSION STATUS----------------------------------------------------------------------OracleEnterpriseManager10.2.0.5.0 VALIDSpatial10.2.0.5.0 VALIDOracle interMedia 10.2.0.5.0 VALIDOLAP Catalog10.2.0.5.0 VALIDOracle XML Database10.2.0.5.0 VALIDOracleText10.2.0.5.0 VALIDOracleExpressionFilter10.2.0.5.0 VALIDOracleRuleManager10.2.0.5.0 VALIDOracleWorkspaceManager10.2.0.5.0 VALIDOracleDataMining10.2.0.5.0 VALIDOracleDatabaseCatalogViews10.2.0.5.0 VALIDOracleDatabasePackagesand T 10.2.0.5.0 VALIDypes?JServer JAVA VirtualMachine10.2.0.5.0 VALIDOracle XDK 10.2.0.5.0 VALIDOracleDatabaseJavaPackages10.2.0.5.0 VALIDOLAP AnalyticWorkspace10.2.0.5.0 VALIDOracle OLAP API 10.2.0.5.0 VALID?17 rows selected.?Apply PSU Patch 14727319PSU补丁包：p6880880_102000_Linux-x86-64.zip?Apply PSU Patch 14727319需要先升级Opatch.
[oracle@primary install]$ unzip p6880880_102000_Linux-x86-64.zip[oracle@primary install]$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.bk[oracle@primary install]$ mv OPatch/ $ORACLE_HOME/[oracle@primary install]$ $ORACLE_HOME/OPatch/opatch关闭所有活动程序[oracle@khm22 install]$ unzip p14727319_10205_Linux-x86-64.zip-d patch[oracle@khm22 install]$ cd patch/14727319/[oracle@khm22 12879933]$ opatch apply[oracle@khm22 install]$ sqlplus /as sysdbaSQL> startupSQL>@?/rdbms/admin/catbundle.sql psu applySQL>set line 150SQL> col ACTION_TIME for a30SQL> col ACTION for a8SQL> col NAMESPACE for a8SQL> col VERSION for a10SQL> col BUNDLE_SERIES for a5SQL> col COMMENTS for a40SQL>select*from dba_registry_history;?ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS---------------------------------------------------------------------------------------------------------------17-MAR-1312.09.03.762743 AM VIEW REC 8289601 view recompilation OMPILE?17-MAR-1312.09.03.872147 AM UPGRADE SERVER 10.2.0.5.0Upgradedfrom10.2.0.1.017-MAR-1301.11.02.521324 AM APPLY SERVER 10.2.0.510 PSU PSU 10.2.0.5.10