ORAchk-数据库健康检查好帮手
ORAchk 之前被称为RACcheck,后来它的检查范围进行了扩展,改名为了ORAchk,它是在数据库系统进行健康检查的一个专用工具,这个工具主要用来检查软件的配置是否符合要求以及一些最佳实践是否被应用了。通过这个工具,用户可以很方便地、自动化地对自己的系统进行健康检查和评估。
ORAchk 能够检查的软件主要有:OS、Oracle Clusterware (CRS)、 Grid Infrastructure environment(GI)、Automatic Storage Management (ASM)以及Real Application Clusters (RAC)、单机的数据库、Golden Gate。
检查的项目包括:
o OS kernel 参数
o OS 包/补丁
o OS上其它和RAC相关的配置
o CRS/Grid Infrastructure
o RDBMS
o ASM
o 数据库参数
o 对于RAC数据库影响较大的设置
o 升级到11.2.0.3/11.2.0.4/12c时的升级检查
o Maximum Availability Architecture (MAA)检查
目前支持的平台:
o Linux x86-64* (Enterprise Linux, RedHat and SuSE 9, SuSE 10 & SuSE 11)
o Oracle Solaris SPARC (64-bit)(Solaris 10 and 11)
o Oracle Solaris x86-64 (Solaris 10 and 11)
o IBM AIX on POWER Systems (64-bit) **
o HP-UX PA-RISC (64-bit)**
o HP-UX Itanium **
* 不支持32位平台,不支持 Linux Itanium
** 需要安装BASH Shell 3.2 或之上
目前支持的数据库版本:
o 10gR2
o 11gR1
o 11gR2
o 12cR1
注:自版本 2.2.0 起,ORAchk 支持单实例、Oracle Restart 以及 RAC(包括 RAC One)
我们推荐使用最新版本的ORAchk,这是因为ORAchk基于最佳实践,这些最佳实践会不断更新,另一方面ORAchk会检查补丁的安装情况,PSU 是按季度推出的,所以ORAchk也会至少每两到三个月更新一次。
1.首先看一下如何下载和安装ORAchk
ORAchk是从下面的My Oracle Support 文档中下载:
ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.1)
对于RAC环境,只需要在任意一个节点执行ORAchk就可以,它会通过SSH连接到别的节点收集数据,由于它会以sysdba身份登录到数据库中做很多检查,所以要求用数据库软件的属主来执行ORAchk,而且要对oracle用户配置用户等效性,使它能够无需密码就连接到远程节点。
下载之后的文件为orachk.zip。 将这个文件用数据库软件属主上传到RAC集群的任一节点,比如节点1的一个目录:
su - oracle <=======用数据库属主执行
$cd /home/oracle
$mkdir orachk
$cd orachk
$pwd
/home/oracle/orachk <==本例中把orachk.zip上传到了这个目录
$ls -l orachk
-rw-r--r-- 1 oracle oinstall 5237947 May 14 05:15 orachk.zip
$ unzip orachk.zip <==解压这个文件
$ls -l
total 33324
-rw-rw-r-- 1 oracle oinstall 21234729 Mar 1 04:35 collections.dat
-rwxr-xr-x 1 oracle oinstall 1428107 Mar 1 04:34 orachk<==这个文件的权限是755,属主是oracle
-rw-r--r-- 1 oracle oinstall 5237947 May 14 05:15 orachk.zip
-rwxr-xr-x 1 oracle oinstall 1428107 Mar 1 04:34 raccheck
-rw-r--r-- 1 oracle oinstall 2541 Mar 1 04:34 readme.txt
-rw-rw-r-- 1 oracle oinstall 4716612 Mar 1 04:35 rules.dat
-rw-r--r-- 1 oracle oinstall 296 Mar 1 04:34 UserGuide.txt
2. 接下来看一下如何执行orachk
它的执行也是非常简单的,用oracle用户执行:
[oracle@rac1 orachk]$ ./oracheck
CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0.4/grid?[y/n][y]y <==确认CRS_HOME是否正确
Checking ssh user equivalency settings on all nodes in cluster
Node rac2 is configured for ssh user equivalency for oracle user
Searching for running databases . . . . .
. .
List of running databases registered in OCR
1. RACDB
2. None of above
Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number
like 1,2 etc [1-2][1].1 《===========选择要进行检查的数据库
. .
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
rac1 Yes Yes Yes Yes Yes Yes RACDB1
rac2 Yes Yes Yes Yes Yes Yes RACDB2
-------------------------------------------------------------------------------------------------------
Copying plug-ins
. . . . . . . . . . . . . . . . . .
. . . . . .
113 of the included audit checks require root privileged data collection . If sudo is not configured or the root password is
not available, audit checks which require root privileged data collection can be skipped.
1. Enter 1 if you will enter root password for each host when prompted
2. Enter 2 if you have sudo configured for oracle user to execute root_raccheck.sh script
3. Enter 3 to skip the root privileged collections
4. Enter 4 to exit and work with the SA to configure sudo or to arrange for root access and run the tool later.
Please indicate your selection from one of the above options for root access[1-4][1]:-1 <=========root密码输入方式,选择1表示手工输入
注:
选项1:在执行阶段根据提示输入root密码
选项2:表示为oracle用户配置了sudo。
选项3:跳过需要使用root权限进行的检查,不推荐。
选项4:退出ORAchk来进行root权限相关的配置。
Is root password same on all nodes?[y/n][y]y 《===root的密码是否在所有节点都一致
Enter root password : <==输入root用户的密码
Verifying root password.
. . .
*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) *** 《========开始进行检查
Collections and audit checks log file is
/home/oracle/raccheck/raccheck_rac1_RACDB_111013_185118/log/raccheck.log
Checking for prompts in /home/oracle/.bash_profile on rac1 for oracle user...
Checking for prompts in /home/oracle/.bash_profile on rac2 for oracle user...
Starting to run raccheck in background on rac2 <==========对远程节点也进行检查
=============================================================
Node name - rac1
=============================================================
Collecting - ASM DIsk I/O stats
Collecting - ASM Disk Groups
Collecting - ASM Diskgroup Attributes
Collecting - ASM disk partnership imbalance
Collecting - ASM diskgroup attributes
Collecting - ASM initialization parameters
完成后,将显示以下(或类似)内容:
Detailed report (html) - /home/oracle/orachk/orachk_rac1_RACDB_111013_185118/orachk_rac1_RACDB_111013_185118.html
UPLOAD(if required) - /home/oracle/orachk/orachk_rac1_RACDB_111013_185118.zip
两个节点一般需要运行10多分钟,每个节点大概需要5分钟。ORAchk占用的资源很少,在我的测试库上大概占用了10%左右的CPU。另外,ORAchk生成的结果一般在5M左右,自动压缩生成的.zip文件大概几百K。
3. 再看一下ORAchk生成的报告
ORAchk 检查完成之后会生成一个html报告,这个报告的命名为:orachk_节点名_数据库名_时间.html,比如orachk_rac1_RACDB_111013_185118.html。同时,它还会把所有的输出文件都打包为一个.zip文件,比如orachk_rac1_RACDB_111013_185118.zip,可以把这个.zip文件从RAC服务器上下载到本地的windows,解压后查看其中的生成的orachk_节点名_数据库名_时间.html 这一个文件就可以。
生成的报告也非常简单、清晰,在报告最上部会列出这次检查的分数,满分是100分。
在html报告中会列出下面的内容:
Findings Needing Attention <==检查失败的项目,分别为FAIL、WARNING、INFO
On Database Server
MAA Scorecard <==MAXIMUM AVAILABILITY ARCHITECTURE,也就是最大可用架构
Findings Passed <==通过的项目
On Database Server
Cluster Wide
GRID and RDBMS patch recommendation Summary report <==补丁检查结果
GRID and RDBMS patch recommendation Detailed report <==推荐打的补丁
对于每一项失败的项目,点击对应的“View"按钮会,会列出这项检查为何失败、这个问题会导致的影响、 推荐的解决方案,相关文档等。
4. ORAchk 升级检查功能
ORAchk 还有一个很赞的功能就是升级前和升级后的检查,当前的ORAchk版本2.2.3_20131007已经支持升级到版本11.2.0.3/11.2.0.4/12c时的升级检查。进行升级检查的目的是为了保障集群和RAC升级的过程尽量平稳和顺利,尽量避免遇到已知的问题。使用ORAchk进行升级检查的好处是它整合了许多升级方面常用的文档,这样就可以统一入口,用ORAchk这个工具进行检查就可以。
升级检查分两个阶段:
o 升级前:
$./orachk -u -o pre
o 升级后:
$./orachk -u -o post
5. 什么时候适宜运行ORAchk呢?
o 在系统负载较低的时候运行,这样对系统影响最小。ORAchk 不会对系统的性能状况进行分析,因此不需要在系统发生性能情况时执行。
o 另外在RAC环境部署完毕之后推荐运行一下来检查一下系统的配置健康情况。
o 还推荐在计划内的维护前和维护后都执行一下
o 在升级前和升级后也要执行
o 在日常维护中,至少每三个月执行一次,因为ORAchk每两到三个月会更新一次
默认方式执行 ./orachk 后只会执行一次,但是可以在执行ORAchk时以自动任务的方式让它定期执行,并发送邮件通知。具体的配置方式请参考ORAchk的用户手册章节“ORAchk Daemon Mode Operation”(也是在MOS文档 1268927.1中下载)。
比如:
首先设置自动任务的参数:
$ ./orachk -set "AUTORUN_INTERVAL=1d;AUTORUN_FLAGS= -o v;NOTIFICATION_EMAIL=firstname.lastname@company.com;PASSWORD_CHECK_INTERVAL=1"
用daemon方式启动ORAchk:
$ ./orachk –d start
ORAchk 不会像OSWatcher一样自动维护它的输出文件,需要手工清除或者设置cron等自动任务来清除。
另外,不可以在一台server上同时启动多个ORAchk,同一时刻只能执行一个ORAchk。
需要注意的是,ORAchk不是诊断工具,不是性能调优工具,也不是数据收集工具,比如RAC节点发生了重启,那么ORAchk不能告诉你重启的原因,它也不会收集诊断重启问题的数据,所以它不能代替RDA ,diagcollection等工具。另外,ORAchk也不能代替cluster verification utility,这是因为他们两个工具是互补的。CVU 是集群自带的工具,而且GI会定时调用CVU来进行一些RAC的检查,但是CVU的版本是确定的,不像ORAchk会定期被更新。
ORAchk的支持是通过MOS的社区来进行的,在RAC/Scalability 这个社区中有一个专门用于ORAchk的帖子RAC/Scalability Community Forum,如果有ORAchk的问题,可以在个帖子提问,注意这个帖子是英文的。还可以在中文数据库社区进行讨论。
我们在2013年11月份进行了一个关于RACcheck的网上讲座,讲座的录像和讲义可以在在MOS文档1456176.1=》“Archived 2013”=》使用RACCheck诊断和预防RAC问题=》点击Recording 或者 .pdf 进行下载.
附:我用目前最新的ORAchk版本进行了测试,单实例上也可以运行ORAchk,下面是在AIX上利用ORAchk对10.2.0.5的单实例数据库进行检查的过程:
$ ./orachk
This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S
raccheck did not find the RDBMS binaries on nascds5 from environment.
Please set RAT_ORACLE_HOME to ORACLE_HOME in current shell to override and re-run it.
eg export RAT_ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$export RAT_ORACLE_HOME=/opt/oracle/products/10.2.0.5
$ ./orachk
This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S
RDBMS binaries found at /opt/oracle/products/10.2.0.5 and ORACLE_HOME not set. Do you want to set ORACLE_HOME to
/opt/oracle/products/10.2.0.5?[y/n][y]y
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
nascds5 No ./raccheck: line 9508: [: -eq: unary operator expected
Yes Yes No No No
-------------------------------------------------------------------------------------------------------
raccheck did not find the Clusterware version from environment.
Please set RAT_DB to cluster version in current shell to override and re- run it.
eg export RAT_DB=112020
$ export RAT_DB=102050
$ ./orachk
This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S
RDBMS binaries found at /opt/oracle/products/10.2.0.5 and ORACLE_HOME not set. Do you want to set ORACLE_HOME to
/opt/oracle/products/10.2.0.5?[y/n][y]y
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS
. . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
nascds5 No ./raccheck: line 9508: [: -eq: unary operator expected
Yes Yes No No No
-------------------------------------------------------------------------------------------------------
Copying plug-ins
. . . . . . . . . . . . . . . . . .
. . . . . .
*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***
Collections and audit checks log file is
/home/oracle/raccheck/raccheck_nascds5_112113_094623/log/raccheck.log
Checking for prompts in /home/oracle/.profile on nascds5 for oracle user...
=============================================================
Node name - nascds5
=============================================================
Collecting - AIX Packages
Collecting - CPU Information
Collecting - Disk Information
Collecting - Memory Information
Collecting - Network tunable parameters
Collecting - OS fixes or patches
Collecting - Patches for RDBMS Home
Collecting - System and Kernel Settings
Data collections completed. Checking best practices on nascds5.
--------------------------------------------------------------------------------------
WARNING => OS Patch IZ97457 is recommended but not installed
FAIL => OS Patch IZ89165 is recommended but not installed
FAIL => OS Patch IZ41855 is recommended but not installed
FAIL => OS Patch IZ52319 is recommended but not installed
FAIL => OS Patch IZ51456 is recommended but not installed
WARNING => kernel paramater tcp_ephemeral_low is not set to recommended value of 9000
WARNING => kernel paramater udp_ephemeral_low is not set to recommended value of 9000
WARNING => kernel paramater maxuproc is not set to recommended value of 13684
WARNING => kernel paramater maxreqs is not set to recommended value of 65536
Best Practice checking completed.Checking recommended patches on nascds5.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
RDBMS homes patches summary report
---------------------------------------------------------------------------------
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Detailed report (html) - /home/oracle/raccheck/raccheck_nascds5_112113_094623/raccheck_nascds5_112113_094623.html
UPLOAD(if required) - /home/oracle/raccheck/raccheck_nascds5_112113_094623.zip