了解Oracle补丁知识
了解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.0 Production Oracle Database 10g Enterprise Edition 10.2.0.1.0 64bi PL/SQL 10.2.0.1.0 Production TNS for Linux: 10.2.0.1.0 Production
经过我补丁包升级之后如下：
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 10.2.0.4.0 Production Oracle Database 10g Enterprise Edition 10.2.0.4.0 64bi PL/SQL 10.2.0.4.0 Production TNS for Linux: 10.2.0.4.0 Production
SQL> select action,comments from registry$history; ACTION COMMENTS
-------------------- ------------------------------
UPGRADE Upgraded from 10.2.0.1.0 APPLY PSU 10.2.0.4.4 APPLY 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，这对于企业来说是个非常大的动作！
了解Opatch
Opatch是Oracle为了安装管理个别补丁而设计的工具，从Oracle 9.2版开始提供使用。通过Opatch工具，DBA可以方便安装、卸载补丁，也可以检测冲突等。
针对Oracle Database Server产品，包括CRS、ASM、RAC，Opatch对应版本如下表格：
|
Oracle 产品版本 |
下载 Opatch 版本 |
OPATCH 版本 (截至 2012年12月17日) |
|
9.2.0.x or 10.1.0.x |
"10.1.0.0.0" (description "OPatch 9i, 10.1") |
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]
应用补丁示例
Apply PSU Patch 14727310
PSU补丁包：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 version OPatch Version: 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/ Oracle Interim Patch Installer version 11.2.0.3.3 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.3/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc OPatch version : 11.2.0.3.3 OUI version : 11.2.0.3.0 Log 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: 13343438 13696216 13923374 14275605 14727310 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: 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. (Oracle Home = '/u01/app/oracle/product/11.2.0.3/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying sub-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... Applying sub-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... Applying sub-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 Oracle Home or 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... Applying sub-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 Oracle Home or 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... Applying sub-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 lspatches 14727310;Database Patch Set Update : 11.2.0.3.5 (14727310) 查看更新补丁内容： [oracle@khm7 ~]$ opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.3 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.3/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc OPatch version : 11.2.0.3.3 OUI version : 11.2.0.3.0 Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2013-03-15_13-40-00PM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_13-40-00PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.3.0 There are 1 products installed in this Oracle Home. Interim patches (1) : Patch 14727310 : applied on Fri Mar 15 13:35:43 CST 2013 Unique Patch ID: 15663328 Patch description: "Database Patch Set Update : 11.2.0.3.5 (14727310)" Created on 27 Dec 2012, 00:06:30 hrs PST8PDT Sub-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)" Bugs fixed: 13566938, 13593999, 10350832, 14138130, 12919564, 13624984, 13588248 13080778, 13804294, 14258925, 12873183, 13645875, 12880299, 14664355 14409183, 12998795, 14469008, 13719081, 13492735, 12857027, 14263036 14263073, 13742433, 13732226, 12905058, 13742434, 12849688, 12950644 13742435, 13464002, 12879027, 13534412, 14613900, 12585543, 12535346 12588744, 11877623, 12847466, 13649031, 13981051, 12582664, 12797765 14262913, 12923168, 13612575, 13384182, 13466801, 13484963, 11063191 13772618, 13070939, 12797420, 13041324, 12976376, 11708510, 13742437 13026410, 13737746, 13742438, 13326736, 13001379, 13099577, 14275605 13742436, 9873405, 9858539, 14040433, 12662040, 9703627, 12617123 12845115, 12764337, 13354082, 13397104, 12964067, 13550185, 12780983 12583611, 14546575, 13476583, 15862016, 11840910, 13903046, 15862017 13572659, 13718279, 13657605, 13448206, 13419660, 14480676, 13632717 14063281, 13430938, 13467683, 13420224, 14548763, 12646784, 14035825 12861463, 12834027, 15862021, 13377816, 13036331, 14727310, 13685544 13499128, 15862018, 12829021, 15862019, 12794305, 14546673, 12791981 13503598, 13787482, 10133521, 12718090, 13399435, 14023636, 12401111 13257247, 13362079, 12917230, 13923374, 14480675, 13524899, 13559697 14480674, 13916709, 14076523, 13773133, 13340388, 13366202, 13528551 12894807, 13343438, 13454210, 12748240, 14205448, 13385346, 15853081 12971775, 13035804, 13544396, 13035360, 14062795, 12693626, 13332439 14038787, 14062796, 12913474, 14841409, 14390252, 13370330, 14062797 13059165, 14062794, 12959852, 13358781, 12345082, 12960925, 9659614 13699124, 14546638, 13936424, 13338048, 12938841, 12658411, 12620823 12656535, 14062793, 12678920, 13038684, 14062792, 13807411, 12594032 13250244, 15862022, 9761357, 12612118, 13742464, 14052474, 13457582 13527323, 15862020, 12780098, 13502183, 13705338, 13696216, 10263668 15862023, 13554409, 15862024, 13103913, 13645917, 14063280, 13011409 --------------------------------------------------------------------------------
OPatch succeeded.
数据库启动，并加载修改SQL Files到数据库
[oracle@khm7 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 15 13:40:39 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 272633136 bytes Database Buffers 138412032 bytes Redo Buffers 4272128 bytes Database mounted. Database opened. SQL> show user USER is "SYS" SQL> @?/rdbms/admin/catbundle.sql psu apply
## 这个脚本执行时间跟大家的系统有关
… 输出略 …
执行完后我们可以查到PSU更新信息：
SQL> set line 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a20 SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
17-SEP-11 10.21.11.595816 AM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
15-MAR-13 10.40.00.705490 AM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
15-MAR-13 01.43.05.319842 PM APPLY SERVER 11.2.0.3 5 PSU PSU 11.2.0.3.5
3 rows selected.
Apply PSU Patch 14727315
PSU补丁包：p14727315_112020_Linux-x86-64.zip
Apply 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 version OPatch Version: 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 lspatches 14727315;Database Patch Set Update : 11.2.0.2.9 (14727315) 执行catbundle.sql脚本之前，无法看到PSU更新信息： SQL> set line 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a20 SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
05-SEP-10 06.22.14.370943 AM APPLY SERVER 11.2.0.2 0 PSU Patchset 11.2.0.2.0
15-MAR-13 11.22.14.833238 AM APPLY SERVER 11.2.0.2 0 PSU Patchset 11.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-10 06.22.14.370943 AM APPLY SERVER 11.2.0.2 0 PSU Patchset 11.2.0.2.0
15-MAR-13 11.22.14.833238 AM APPLY SERVER 11.2.0.2 0 PSU Patchset 11.2.0.2.0
15-MAR-13 02.14.09.659104 PM APPLY SERVER 11.2.0.2 9 PSU PSU 11.2.0.2.9
Upgrade Oracle 10g R2 from 10201 to 10204 and Apply PSU Patch 9352164、12879933
Upgrade 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]$ ./runInstaller
– OUI交互设置的时候需要注意的地方：
Specify Home Details时，NAME和目录选择原安装路径，如果环境变量设置未改变，默认即可。
中间会提示用ROOT去执行脚本，执行完DATABASE SOFTWARE升级就算完成了。
接下来，需要启动到升级模式，然后更新数据字典
[oracle@khm11 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 15 16:30:42 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 390070272 bytes Fixed Size 2084168 bytes Variable Size 322962104 bytes Database Buffers 58720256 bytes Redo Buffers 6303744 bytes Database mounted. Database opened. 执行脚本，此过程非常缓慢，从下面的输出信息中可以看到，升级一共耗费了43分钟多，当然这根据不同的系统有快有慢。 另外，按照README的要求，我们也可以将输出信息SPOOL到一个文件里，好方便查阅！ SQL> @?/rdbms/admin/catupgrd.sql … 部分内容省略 ... Total Upgrade Time: 00:43:33 DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above PL/SQL lists the SERVER components in the upgraded DOC> database, along with their current version and status. DOC> DOC> Please review the status and version columns and look for DOC> any errors in the spool log file. If there are errors in the spool DOC> file, or any components are not VALID or not the current version, DOC> consult the Oracle Database Upgrade Guide for troubleshooting DOC> recommendations. DOC> DOC> Next shutdown immediate, restart for normal operation, and then DOC> run utlrp.sql to recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC>#
数据字典更新完后重新正常启动，然后重编译失效对象
SQL> shutdown immediate SQL> startup SQL> @?/rdbms/admin/utlrp.sql -- 输出内容略 --
单实例的版本升级到这里就算完事了，当然有CATALOG恢复目录，也升级一下即可。
最后查看数据库组件的版本、状态等：
SQL> col comp_name format a30 SQL> col version format a30 SQL> col status format a10 SQL> SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ----------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0
VALID Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.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 version Invoking OPatch 10.2.0.4.2 OPatch Version: 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 apply Invoking OPatch 10.2.0.5.1 Oracle Interim Patch Installer version 10.2.0.5.1 Copyright (c) 2010, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/10.2.0/dbhome_1 Central Inventory : /u01/app/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.5.1 OUI version : 10.2.0.4.0 OUI location : /u01/app/oracle/product/10.2.0/dbhome_1/oui Log 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 and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: 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 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 = '/u01/app/oracle/product/10.2.0/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing 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 ': Return Code = 0 … 中间大量信息略 … Verifying the update... Inventory check OK: Patch ID 9352164 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 9352164 are present in Oracle Home. -------------------------------------------------------------------------------- ******************************************************************************** ******************************************************************************** ** ATTENTION ** ** ** ** Please note that the Patch Set Update Installation (PSU Deinstallation) ** ** is not complete until all the Post Installation (Post Deinstallation) ** ** instructions noted in the Readme accompanying this PSU, have been ** ** successfully completed. ** ** ** ******************************************************************************** ******************************************************************************** -------------------------------------------------------------------------------- Execution of 'sh /install/patch/9352164/custom/scripts/post -apply 9352164 ': Return Code = 0 The local system has been patched and can be restarted. OPatch succeeded.
数据库启动，并执行脚本来加载修改SQL Files到数据库：
代码代码
因此，Apply PSU Patch完成，查看：
SQL> startup SQL> @?/rdbms/admin/catbundle.sql psu apply … 输出信息略 … 因此，Apply PSU Patch完成，查看： SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a40 SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- ----------------------------------------
16-MAR-13 11.24.53.986137 PM UPGRADE SERVER 10.2.0.4.0 Upgraded from 10.2.0.1.0
17-MAR-13 12.45.32.609861 AM APPLY SERVER 10.2.0.4 4 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 sysdba SQL> 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 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a20 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a30 SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPACE VERSION ID BUNDL COMMENTS ------------------------------ -------- -------------------- ---------- ---------- ----- ------------------------------ 16-MAR-13 11.24.53.986137 PM UPGRADE SERVER 10.2.0.4.0 Upgraded from 10.2.0.1.0 17-MAR-13 12.45.32.609861 AM APPLY SERVER 10.2.0.4 4 PSU PSU 10.2.0.4.4 17-MAR-13 01.37.02.320305 AM APPLY SERVER 10.2.0.4 8 OPSU PSU 10.2.0.4.12 3 rows selected. 打完PSU之后如下方式查看： $ opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU' 9654991 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 9952234 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10248636 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 11724977 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 8576156 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009) 8833280 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009) 9119284 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010) 9352164 9352164 Wed May 25 15: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 14727319
Upgrade 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 sysdba SQL> startup upgrade SQL> @?/rdbms/admin/catupgrd.sql SQL> shutdown immediate SQL> startup SQL> @?/rdbms/admin/utlrp.sql SQL> col comp_name format a30 SQL> col version format a30 SQL> col status format a10 SQL> SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ----------
Oracle Enterprise Manager 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID OLAP Catalog 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and T 10.2.0.5.0 VALID ypes JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID OLAP Analytic Workspace 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID 17 rows selected.
Apply PSU Patch 14727319
PSU补丁包：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 sysdba SQL> startup SQL> @?/rdbms/admin/catbundle.sql psu apply SQL> set line 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a40 SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- ----------------------------------------
17-MAR-13 12.09.03.762743 AMOMPILE VIEW REC 8289601 view recompilation 17-MAR-13 12.09.03.872147 AM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.1.0
17-MAR-13 01.11.02.521324 AM APPLY SERVER 10.2.0.5 10 PSU PSU 10.2.0.5.10
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30182853/viewspace-1479128/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
