我之前的文章中有提到过,linux系统下oracle运行一段时间之后,系统层面sy%数值会一直居高不下,本来以为是linux在处理页交换方面的BUG,
最后发现不是这个原因,今天对相关会话进行跟踪发现,出现很多警告,具体如下:
*** ACTION NAME:() 2011-12-15 19:50:35.714
*** MODULE NAME:(JDBC Thin Client) 2011-12-15 19:50:35.714
*** SERVICE NAME:(SYS$USERS) 2011-12-15 19:50:35.714
*** SESSION ID:(1205.33189) 2011-12-15 19:50:35.714
WARNING:Could not increase the asynch I/O limit to 896 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 960 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 960 for SQL direct I/O. It is set to 128
*** 2011-12-15 19:58:53.204
WARNING:Could not increase the asynch I/O limit to 928 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 928 for SQL direct I/O. It is set to 128
*** 2011-12-15 20:04:05.175
WARNING:Could not increase the asynch I/O limit to 1024 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 1024 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 1056 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 1024 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 1024 for SQL direct I/O. It is set to 128
网上查了下,居然是oracle的BUG,编号:9949948,只发生在10.2.0.5.0和11.2.0.1.0,解决方法有2种,一种是在操作系统层面修改相关参数,另一种方法就是打补丁,我们选择了后者
相关补丁如下(在此要感谢我以前的同事(老徐,杰斌,晨),很多问题都请教过他们,很多补丁也是帮忙下载的):
p12419392_10205_Linux-x86-64.zip -- 直接升级到10.2.0.5.4,相关BUG都已经得到解决
p9772888_10205_Linux-x86-64.zip -- 针对上面的BUG的补丁(防止p12419392补丁打完之后出现问题,以便直接打对应补丁)--可选
p6880880_102000_Linux-x86-64.zip -- 数据库升级到10.2.0.5.0之后,相关的opatch版本仍然是10.2.0.4.9,打补丁p12419392必须要求opatch版本至少10.2.0.5.0
由于我们的线上库是RAC环境,根据官方文档,RAC环境操作步骤如下:
1.环境检测,检测是否有相冲突的补丁,具体操作如下:
在做这步之前,先把opatch的版本升到10.2.5.0.1,操作如下:
[oracle@rac1 ~]$ cp p6880880_102000_Linux-x86-64.zip $ORACLE_HOME/
[oracle@rac1 ~]$ cd $ORACLE_HOME && mv OPatch OPatchbak && unzip p6880880_102000_Linux-x86-64.zip
解压完之后进入OPatch目录:
[oracle@rac1 OPatch]$ ./opatch version
Invoking OPatch 10.2.0.5.1
OPatch Version: 10.2.0.5.1
OPatch succeeded.
表示opatch已经升级到10.2.0.5.1
而之前显示的版本如下:
[oracle@rac1 ~]$ /oracle/product/10.2.0/db_1/OPatch/opatch version
Invoking OPatch 10.2.0.4.9
OPatch Version: 10.2.0.4.9
开始进行环境检测:
[oracle@rac1 ~]$./$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419392
Invoking OPatch 10.2.0.4.9
Oracle Interim Patch Installer version10.2.0.4.9
Copyright (c) 2009, OracleCorporation. All rights reserved.
PREREQ session
Oracle Home : /oracle/product/10.2.0/db_1
Central Inventory :/oracle/oraInventory
: /etc/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /oracle/product/10.2.0/db_1/oui
Log file location :/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-09_20-23-29PM.log
Patch history file:/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
Invoking prereq"checkconflictagainstohwithdetail"
ZOP-40: The patch(es) hasconflicts/supersets with other patches installed in the Oracle Home (or) amongthemselves.
Prereq"checkConflictAgainstOHWithDetail" failed.
Summary of Conflict Analysis:
Patches that can be applied now without anyconflicts are :
12419392
Following patches are not required, as theyare subset of the patches in Oracle Home or subset of the patches in the givenlist :
8350262
Following patches will be rolled back Oracle Home on application of the patches in the given list :
8350262
Conflicts/Supersets for each patch are:
Patch : 12419392
Bug Superset of 8350262
Super set bugs are:
8350262
OPatch succeeded.
这里提示8350262 与我们要安装的patch 12419392 冲突,而8350262是我之前为解决OEM的问题而打的补丁,该bug存在于10.2.0.4 和10.2.0.5 版本。 在10.2.0.5.4 中已经修复了该bug。所以这2个bug有冲突。 解决这个问题的办法是rollback 掉8350262。
以下是回滚操作:
进入到解压后的8350262目录,执行如下命令:
[oracle@rac1 ~]$./$ORACLE_HOME/OPatch/opatch rollback -id 8350262
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version10.2.0.5.1
Copyright (c) 2010, OracleCorporation. All rights reserved.
Oracle Home : /oracle/product/10.2.0/db_1
Central Inventory :/oracle/oraInventory
: /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle/product/10.2.0/db_1/oui
Log file location :/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-09_21-33-24PM.log
Patch history file:/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
RollbackSession rolling back interim patch""8350262"" OH ""/oracle/product/10.2.0/db_1""
Running prerequisite checks...
OPatch detected non-cluster Oracle Home the inventory and will patch the local system only.
Backing up files affected by the patch""8350262"" for restore. This might take a while...
Patching componentoracle.sysman.agent.core, 10.2.0.5.0a...
Updating jar file"/oracle/product/10.2.0/db_1/sysman/jlib/emCORE.jar" with"/oracle/product/10.2.0/db_1/.patch_storage/8350262_Sep_13_2010_14_09_12/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
…
Updating jar file"/oracle/product/10.2.0/db_1/sysman/jlib/emd_java.jar" with"/oracle/product/10.2.0/db_1/.patch_storage/8350262_Sep_13_2010_14_09_12/files//sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class"
RollbackSession removing interim patch""8350262"" inventory
OPatch succeeded.
再次检测,过程如下:
[oracle@rac1 ~]$./$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419392
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version10.2.0.5.1
Copyright (c) 2010, OracleCorporation. All rights reserved.
PREREQ session
Oracle Home : /oracle/product/10.2.0/db_1
Central Inventory :/oracle/oraInventory
: /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle/product/10.2.0/db_1/oui
Log file location :/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-09_21-34-40PM.log
Patch history file:/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
Invoking prereq"checkconflictagainstohwithdetail"
Prereq"checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
2.正式开始打补丁,具体过程如下:
关闭RAC的其中一个节点,之后执行如下命令:
[oracle@rac1 12419392]$./$ORACLE_HOME/OPatch/opatch apply
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version10.2.0.5.1
Copyright (c) 2010, OracleCorporation. All rights reserved.
Oracle Home : /oracle/product/10.2.0/db_1
Central Inventory :/oracle/oraInventory
: /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle/product/10.2.0/db_1/oui
Log file location :/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-09_21-46-56PM.log
Patch history file:/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
ApplySession applying interim patch""12419392"" to OH ""/oracle/product/10.2.0/db_1""
The node 'rac1' has been patched. You can restart Oracle instances on it.
The node 'rac3' will be patched next.
Please shutdown Oracle instances running out of this ORACLE_HOME on 'rac3'.
(Oracle Home = '/oracle/product/10.2.0/db_1')
Is the node ready for patching? [y|n]
至此按照提示,RAC1已经打好补丁了,之后把RAC1启起来,然后依次关闭RAC2,上面输入"y"后回车,等提示RAC2补丁打完之后,启动RAC2,最后关闭RAC3
。。。按照提示一路敲下去就ok了,具体如下:
Running command on remote node 'rac1':
cd /oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk irman ORACLE_HOME=/oracle/product/10.2.0/db_1 || echo REMOTE_MAKE_FAILED::>&2
The node 'rac1' has been patched. You can restart Oracle instances on it.
The node 'rac3' will be patched next.
Please shutdown Oracle instances running out of this ORACLE_HOME on 'rac3'.
(Oracle Home = '/oracle/product/10.2.0/db_1')
Is the node ready for patching? [y|n]
----
The node 'rac3' has been patched. You can restart Oracle instances on it.
There were relinks on remote nodes. Remember to check the binary size and timestamp on the nodes 'rac1' 'rac3' .
The following make commands were invoked on remote nodes:
'cd /oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/oracle/product/10.2.0/db_1
cd /oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/product/10.2.0/db_1
cd /oracle/product/10.2.0/db_1/plsql/lib; /usr/bin/make -f ins_plsql.mk iwrap ORACLE_HOME=/oracle/product/10.2.0/db_1
cd /oracle/product/10.2.0/db_1/network/lib; /usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/10.2.0/db_1
cd /oracle/product/10.2.0/db_1/precomp/lib; /usr/bin/make -f ins_precomp.mk proc ORACLE_HOME=/oracle/product/10.2.0/db_1
cd /oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk irman ORACLE_HOME=/oracle/product/10.2.0/db_1
'
OPatch succeeded.
出现上面的提示,就表示补丁已经打成功了!离最后的胜利仅一步之遥,小小的窃喜一下:
最后按照文档说明,在其中的一个节点上执行以下的2个脚本就行了:
SQL>@$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply
....
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
...
...
Session altered.
Updating registry...
1 row created.
Commit complete.
Check the following log file for errors:
/oracle/product/10.2.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_YESMYNET_APPLY_2011Dec16_22_06_17.log
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
....
....
中间省略n多字...
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
2
ORDIM INVALID OBJECTS: ORDX_HTTP_SOURCE - 2 - 11
ORDIM INVALID OBJECTS: ORDX_FILE_SOURCE - 2 - 11
PL/SQL procedure successfully completed.
到这里,已经表示补丁打成功了!!!
查询相关升级后的版本信息:
操作系统层面也可以,具体如下:
[oracle@rac1 ~]$./$RACLE_HOME/OPatch/opatch lsinv -bugs_fixed|grep PSU
9952230 12419392 Fri Dec 16 21:22:41 CST 2011 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10248542 12419392 Fri Dec 16 21:22:41 CST 2011 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
11724962 12419392 Fri Dec 16 21:22:41 CST 2011 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
12419392 12419392 Fri Dec 16 21:22:41 CST 2011 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
上面的显示表示PSU版本已经是10.2.0.5.4了!
数据库里也可以查询到具体信息,(v$version里面不会显示这个小版本号的)具体如下:
select * from registry$history;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-713468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-713468/