Oracle 11g 故障处理

监控平台V$SEGMENT_STATISTICS 和V$SEGSTAT动态视图查询慢解决方法

2018年12月18日2020年12月18日 handsup

在集群环境中，自主测试的监控软件在所有的数据库实例中只有一个节点查询V$SEGMENT_STATISTICS时几个小时都未出结果, 查询这个问题影响了11.2.0.3 ,11.2.0.4多个版本及平台, 这个问题在MOS上有一个相似的NOTE, 记录查询的是v$segstat view.
其实我们可以确认的是 V$SEGMENT_STATISTICS 和V$SEGSTAT 在11G版本中都共同使用了x$ksolsfts。有关查询网站：https://www.anbob.com/archives/2812.html
这个问题主要表现查询的view中使用了x$ksolsfts ,而x$ksolsfts 表存在大量的记录及重复记录, 而导致的查询缓慢, 而且在RAC环境中有可能只影响一个节点
解决方法：
1, 应用对应版本中BUG 21050285的补丁
2, 重启问题节点的实例可以缓解该问题, 会在启动后重新构造数据.

本次实验是测试安装BUG 21050285的补丁，解决监控数据库性能问题。
准备：
从mos上下载相对应版本的补丁，如果数据库有打过PSU补丁就要选取相应版本的21050285的补丁

oracle-->ORACLE_HOME:/u01/app/oracle/product/11.2.0.4/db_1
grid-->ORACLE_HOME:/u01/grid/11.2.0.4

1.断开所有会话

ps -ef|grep 'LOCAL=NO'|grep $ORACLE_SID |grep -v grep|awk '{print $2}'|xargs -i kill -9 {}
检查确认
ps -ef |grep LOCAL=NO

2.关闭dbconsole

echo $ORACLE_UNQNAME
$ORACLE_HOME/bin/emctl status dbconsole
$ORACLE_HOME/bin/emctl stop dbconsole

3.关闭数据库

shutdown immediate

4.关闭集群

crsctl stop crs

5.配置最新版本的OPatch,11.2.0.3.5及以上

chown -R oracle:oinstall OPatch/
chmod -R 775 OPatch/
mv OPatch OPacth_bak
mv OPatch $ORACLE_HOME/
su - oracle
cd $ORACLE_HOME/OPatch
./opatch lsinventory 验证

6.上传补丁包到u01目录下，解压安装包赋予权限

uznip 21050285
chown -R oracle:oinstall 21050285/
chmod -R 775 21050285/

7.确定当前安装的临时补丁是否与这个补丁21050285冲突，如下图所示：

cd /u01/21050285
/u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

8.安装补丁（Oracle用户）

cd /u01/21050285
/u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch apply
成功记录：
[oracle@rac1 21050285]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.25
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-03-23PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   21050285  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '21050285' to OH '/u01/app/oracle/product/11.2.0.4/db_1'

Patching component oracle.rdbms, 11.2.0.4.0...
Patch 21050285 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-03-23PM_1.log

OPatch succeeded.

9.验证补丁信息

[oracle@rac1 21050285]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.25
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-06-02PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-12-18_17-06-02PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  21050285     : applied on Fri Dec 18 17:04:12 CST 2020
Unique Patch ID:  19467713
   Created on 19 Oct 2015, 16:38:09 hrs 
   Bugs fixed:
     21050285

--------------------------------------------------------------------------------

OPatch succeeded.

10.启动CRS，启动数据库

11.如果补丁未应用成功，则回滚补丁

cd /u01/21050285
/u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch rollback -id 21050285
[oracle@rac1 ~]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch rollback -id 21050285
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.25
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-22-31PM_1.log

Patches will be rolled back in the following order: 
   21050285
The following patch(es) will be rolled back: 21050285  

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y

Rolling back patch 21050285...

RollbackSession rolling back interim patch '21050285' from OH '/u01/app/oracle/product/11.2.0.4/db_1'

Patching component oracle.rdbms, 11.2.0.4.0...
yRollbackSession removing interim patch '21050285' from inventory

Log file location: /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-22-31PM_1.log

OPatch succeeded.

12.查看补丁是否被回滚

[oracle@rac1 ~]$ /u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version    : 11.2.0.3.25
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2020-12-18_17-25-32PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-12-18_17-25-32PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.