了解Oracle补丁知识

最新推荐文章于 2023-06-02 19:37:38 发布

niudba

最新推荐文章于 2023-06-02 19:37:38 发布

阅读量126

点赞数

分类专栏： ORACLE日常管理文章标签：数据库 java 操作系统

本文链接：https://blog.csdn.net/niudba/article/details/88301407

版权

ORACLE日常管理专栏收录该内容

77 篇文章 0 订阅

订阅专栏

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.064bi
PL/SQL                                   10.2.0.1.0Production
TNS forLinux:10.2.0.1.0Production

经过我补丁包升级之后如下：

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
 
PRODUCT                                  VERSION         STATUS
----------------------------------------------------------------------
NLSRTL                                   10.2.0.4.0ProductionOracleDatabase10gEnterpriseEdition10.2.0.4.064bi
PL/SQL                                   10.2.0.4.0Production
TNS forLinux:10.2.0.4.0Production
SQL>select action,comments from registry$history; 
 
ACTION               COMMENTS
--------------------------------------------------
UPGRADE              Upgradedfrom10.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]

Oracle补丁术语介绍

了解Oracle Critical Patch Update

应用补丁示例

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
OPatchVersion: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_1
CentralInventory:/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.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]
y
UserRespondedwith: Y
All checks passed.Provide your email address to be informed of security issues, install and
initiate 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]
y
UserRespondedwith: Y
Backing 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 lspatches
14727310;DatabasePatchSetUpdate:11.2.0.3.5(14727310)查看更新补丁内容：[oracle@khm7 ~]$ opatch lsinventory
OracleInterimPatchInstaller version 11.2.0.3.3Copyright(c)2012,OracleCorporation.All rights reserved.
 
 
OracleHome:/u01/app/oracle/product/11.2.0.3/dbhome_1
CentralInventory:/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.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 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)"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> startup
ORACLE instance started.
 
TotalSystemGlobalArea417546240 bytes
FixedSize2228944 bytes
VariableSize272633136 bytes
DatabaseBuffers138412032 bytes
RedoBuffers4272128 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-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 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
OPatchVersion: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;DatabasePatchSetUpdate: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-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、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:Release10.2.0.4.0-Production on FriMar1516:30:422013
 
Copyright(c)1982,2007,Oracle.AllRightsReserved.
 
Connected to an idle instance.
 
SQL> startup upgrade
ORACLE instance started.
 
TotalSystemGlobalArea390070272 bytes
FixedSize2084168 bytes
VariableSize322962104 bytes
DatabaseBuffers58720256 bytes
RedoBuffers6303744 bytes
Database mounted.Database opened.执行脚本，此过程非常缓慢，从下面的输出信息中可以看到，升级一共耗费了43分钟多，当然这根据不同的系统有快有慢。另外，按照README的要求，我们也可以将输出信息SPOOL到一个文件里，好方便查阅！
SQL>@?/rdbms/admin/catupgrd.sql
…部分内容省略...TotalUpgradeTime: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 ornot the current version,
DOC>   consult the OracleDatabaseUpgradeGuidefor troubleshooting
DOC>   recommendations.
DOC>
DOC>Next shutdown immediate, restart for normal operation,andthen
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
----------------------------------------------------------------------OracleDatabaseCatalogViews10.2.0.4.0                     VALID
OracleDatabasePackagesand T 10.2.0.4.0                     VALID
ypes
 
OracleWorkspaceManager10.2.0.4.3                     VALID
JServer JAVA VirtualMachine10.2.0.4.0                     VALID
Oracle XDK                     10.2.0.4.0                     VALID
OracleDatabaseJavaPackages10.2.0.4.0                     VALID
OracleExpressionFilter10.2.0.4.0                     VALID
OracleDataMining10.2.0.4.0                     VALID
OracleText10.2.0.4.0                     VALID
Oracle XML Database10.2.0.4.0                     VALID
OracleRuleManager10.2.0.4.0                     VALID
Oracle interMedia              10.2.0.4.0                     VALID
OLAP AnalyticWorkspace10.2.0.4.0                     VALID
Oracle OLAP API                10.2.0.4.0                     VALID
OLAP Catalog10.2.0.4.0                     VALID
Spatial10.2.0.4.0                     VALID
OracleEnterpriseManager10.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
InvokingOPatch10.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 apply
InvokingOPatch10.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_1
CentralInventory:/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 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]
y
UserRespondedwith: Y
Backing 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> 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-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 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-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 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
----------------------------------------------------------------------OracleEnterpriseManager10.2.0.5.0                     VALID
Spatial10.2.0.5.0                     VALID
Oracle interMedia              10.2.0.5.0                     VALID
OLAP Catalog10.2.0.5.0                     VALID
Oracle XML Database10.2.0.5.0                     VALID
OracleText10.2.0.5.0                     VALID
OracleExpressionFilter10.2.0.5.0                     VALID
OracleRuleManager10.2.0.5.0                     VALID
OracleWorkspaceManager10.2.0.5.0                     VALID
OracleDataMining10.2.0.5.0                     VALID
OracleDatabaseCatalogViews10.2.0.5.0                     VALID
OracleDatabasePackagesand T 10.2.0.5.0                     VALID
ypes
 
JServer JAVA VirtualMachine10.2.0.5.0                     VALID
Oracle XDK                     10.2.0.5.0                     VALID
OracleDatabaseJavaPackages10.2.0.5.0                     VALID
OLAP AnalyticWorkspace10.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-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