oracle巡检工具,oracle审查工具

说明

Procwatcher是一种间隔检查和监视Oracle数据库或集群件进程的工具。该工具将使用Oracle工具（如oradebug short_stack）或OS调试器（如pstack，gdb，dbx或ladebug）收集这些进程的堆栈跟踪，并在指定时收集SQL数据。

可通过MOS文档：459694.1 下载

1.2. 解压并授权

[root@host1 software]# cd /[root@host1 /]# mkdir prw[root@host1 /]# chown oracle:oinstall prw[root@host1 /]# cd prw[root@host1 prw]# unzip prw_12.2.18.5.0.zipArchive: prw_12.2.18.5.0.zipinflating: prw.sh[root@host1 prw]# chown oracle:oinstall prw.sh

1.3. 要求

• $ PATH中必须有/bin和/usr/bin
• 在oratab中设置instance_name或db_name或设置$ ORACLE_HOME env变量。（PRW在oratab中搜索它找到的SID，如果在oratab中找不到SID，它将默认为$ORACLE_HOME）。如果找不到要使用的$ORACLE_HOME，则Procwatcher无法正常运行。
• 如果你只是为该用户的homes/instances进行故障排除，请将Procwatcher作为oracle软件所有者运行。如果要对集群件进程进行故障排除（EXAMINE_CLUSTER = true或正在为多个oracle用户进行故障排除），请以root用户身份运行。

如果要监视集群件，则必须在平台上安装相关的OS调试程序; PRW寻找：

Linux - /usr/bin/gdbHP-UX and HP Itanium - /opt/langtools/bin/gdb64 or /usr/ccs/bin/gdb64Sun - /usr/bin/pstackIBM AIX - /bin/procstack or /bin/dbxHP Tru64 - /bin/ladebug

1.4. 查看是否符合安装条件

因为当前数据库安装在Linux系统上，使用Procwatcher是需要操作系统环境检查，首先要求执行用户PATH中包括/usr/bin和/bin目录。

[oracle@host1 ~]$ env | grep PATHLD_LIBRARY_PATH=/u01/app/oracle/product/18.1.0/dbhome_1/lib:/u01/app/oracle/product/18.1.0/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/libPATH=.:/usr/local/java/bin:/usr/lib/oracle/11.2/client64/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/18.1.0/dbhome_1/bin:/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/binCLASSPATH=/u01/app/oracle/product/18.1.0/dbhome_1/JRE:/u01/app/oracle/product/18.1.0/dbhome_1/jlib:/u01/app/oracle/product/18.1.0/dbhome_1/rdbms/jlib:/u01/app/oracle/product/18.1.0/dbhome_1/network/jlib

环境变量中包括标准的$ORACLE_系列。

[oracle@host1 ~]$ env | grep ORAORACLE_UNQNAME=pdbcndba_pORA_NLS11=/u01/app/oracle/product/18.1.0/dbhome_1/nls/dataORACLE_SID=cndbaORACLE_BASE=/u01/app/oracleORACLE_TERM=xtermORACLE_HOME=/u01/app/oracle/product/18.1.0/dbhome_1

执行用户要求是Oracle owner用户。如果是cluster（RAC）或者多个oracle user的情况，可以使用root来执行。

操作系统中必须包括系统调试工具。列表如下：

[oracle@host1 ~]$ rpm -qa | grep gdbgdbm-1.10-8.el7.x86_64gdb-7.6.1-94.el7.x86_64

1.5. 启动Procwatcher

[oracle@host1 prw]$ ./prw.sh startMon Jul 23 17:54:30 CST 2018: Starting Procwatcher as user oracleMon Jul 23 17:54:30 CST 2018: Thank you for using Procwatcher. :-)Mon Jul 23 17:54:30 CST 2018: Please add a comment to Oracle Support Note 459694.1Mon Jul 23 17:54:30 CST 2018: if you have any comments, suggestions, or issues with this tool.Procwatcher files will be written to: /prwMon Jul 23 17:54:30 CST 2018: Started Procwatcher[oracle@host1 prw]$ ./prw.sh statMon Jul 23 17:41:12 CST 2018: PROCWATCHER VERSION: 12.2.18.5.0Mon Jul 23 17:41:12 CST 2018: ### Parameters ###Mon Jul 23 17:41:12 CST 2018: Procwatcher Directory (PRWDIR): /prwMon Jul 23 17:41:12 CST 2018: EXAMINE_CLUSTER=falseMon Jul 23 17:41:12 CST 2018: EXAMINE_BG=trueMon Jul 23 17:41:12 CST 2018: PRWPERM=744Mon Jul 23 17:41:12 CST 2018: RETENTION=7Mon Jul 23 17:41:12 CST 2018: WARNINGEMAIL=Mon Jul 23 17:41:12 CST 2018: INTERVAL=60Mon Jul 23 17:41:12 CST 2018: THROTTLE=5Mon Jul 23 17:41:12 CST 2018: IDLECPU=3Mon Jul 23 17:41:12 CST 2018: SIDLIST=Mon Jul 23 17:41:12 CST 2018: ### Advanced Parameters (non-default) ###Mon Jul 23 17:41:12 CST 2018: ### End Parameters ###Mon Jul 23 17:41:12 CST 2018: Procwatcher is not running on local node host1Mon Jul 23 17:41:12 CST 2018: Procwatcher files are be written to: /prw

1.6. 停止Procwatcher

[oracle@host1 prw]$ ./prw.sh stopMon Jul 23 17:57:49 CST 2018: Stopping ProcwatcherMon Jul 23 17:57:49 CST 2018: Checking for stray debugging sessions...(waiting 1 second)Mon Jul 23 17:57:50 CST 2018: No debugging sessions found, all good, exiting...Mon Jul 23 17:57:50 CST 2018: Thank you for using Procwatcher. :-)Mon Jul 23 17:57:50 CST 2018: Please add a comment to Oracle Support Note 459694.1Mon Jul 23 17:57:50 CST 2018: if you have any comments, suggestions, or issues with this tool.Mon Jul 23 17:57:50 CST 2018: Procwatcher Stopped

1.7. 查看Procwatcher状态

[oracle@host1 prw]$ ./prw.sh statMon Jul 23 17:56:07 CST 2018: PROCWATCHER VERSION: 12.2.18.5.0Mon Jul 23 17:56:07 CST 2018: ### Parameters ###Mon Jul 23 17:56:07 CST 2018: Procwatcher Directory (PRWDIR): /prwMon Jul 23 17:56:07 CST 2018: EXAMINE_CLUSTER=falseMon Jul 23 17:56:07 CST 2018: EXAMINE_BG=trueMon Jul 23 17:56:07 CST 2018: PRWPERM=744Mon Jul 23 17:56:07 CST 2018: RETENTION=7Mon Jul 23 17:56:07 CST 2018: WARNINGEMAIL=Mon Jul 23 17:56:07 CST 2018: INTERVAL=60Mon Jul 23 17:56:07 CST 2018: THROTTLE=5Mon Jul 23 17:56:07 CST 2018: IDLECPU=3Mon Jul 23 17:56:07 CST 2018: SIDLIST=Mon Jul 23 17:56:07 CST 2018: ### Advanced Parameters (non-default) ###Mon Jul 23 17:56:07 CST 2018: ### End Parameters ###Mon Jul 23 17:56:07 CST 2018: Procwatcher is currently running on local node host1Mon Jul 23 17:56:07 CST 2018: Procwatcher files are be written to: /prwMon Jul 23 17:56:07 CST 2018: There are 0 concurrent debug sessions running...

1.8. 查看Procwatcher帮助信息

执行脚本prw.sh，可以自动的进行进程级别的数据收集。可以调用参数help来进行帮助信息。

[oracle@host1 ~]$ cd /prw/[oracle@host1 prw]$ ./prw.sh helpMon Jul 23 17:38:56 CST 2018: Building default prwinit.ora at /prw/prwinit.oraUsage: prw.sh <verb>TFA Syntax: tfactl prw <verb>Verbs are:start [all] - Start Procwatcher on local node, if 'all' is specified, start on all nodesstop [all] - Stop Procwatcher on local node, if 'all' is specified, stop on all nodesstat - Check the current status of Procwatcherpack - Package up Procwatcher files (on all nodes) to upload to supportparam - Check current Procwatcher parametersdeinstall [clean] - Stop Procwatcher and remove the Procwatcher directory (clean)log [number] - See the last [number] lines of the procwatcher log filelog [runtime] - See contiuous procwatcher log file info - use Cntrl-C to breakinit [directory] - Create a default prwinit.ora filedir - Display Procwatcher directoryhelp - What you are looking at...

1.9. Procwatcher参数

从Procwatcher版本12.1.14.12开始，这些参数在Procwatcher目录的prwinit.ora文件中设置。如果没有看到prwinit.ora文件，则可以在集群环境中使用“prw.sh init ”或“prw.sh deploy”生成一个文件。

[oracle@host1 prw]$ cat prwinit.ora## PROCWATCHER PARAMETERS - REVIEW CAREFULLY:########################## CONFIG SETTINGS ############################## Set EXAMINE_CLUSTER variable if you want to examine clusterware processes (default is false - or set to true):# Note that if this is set to true you must deploy/run procwatcher as root unless using oracle restartEXAMINE_CLUSTER=false# Set EXAMINE_BG variable if you want to examine all BG processes (default is true - or set to false):EXAMINE_BG=true# Set permissions on Procwatcher files and directories (default: 744):PRWPERM=744# Set RETENTION variable to the number of days you want to keep historical procwatcher data (default: 7)RETENTION=7……

1.10. 模拟阻塞

会话1执行更新表

[oracle@host1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 24 15:02:45 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> create table test (id number);Table created.SQL> insert into test values(1);1 row created.SQL> commit;Commit complete.SQL> update test set id=4;1 row updated.

会话2更新相同的表模拟阻塞

SQL> update test set id=4;1 row updated.

会话2 hang住

现在演示如何使用Procwatcher对此进行故障排除，将启动Procwatcher：

[oracle@host1 prw]$ ./prw.sh start

现在将检查Procwatcher日志以确保它正在收集数据：

[oracle@host1 prw]$ cat prw_host1.log…...Tue Jul 24 15:02:43 CST 2018: Collecting process specific SQLs for SID cndbaTue Jul 24 15:02:46 CST 2018: ..SQL: Running SQLsqltext.sql on SID cndbaTue Jul 24 15:02:46 CST 2018: ..SQL: Running SQLash.sql on SID cndbaTue Jul 24 15:02:49 CST 2018: SQL collection complete after 17 seconds (6 SQLs - average seconds: 2)Tue Jul 24 15:02:49 CST 2018: Was going to debug process 13740 but it vanished...Tue Jul 24 15:02:50 CST 2018: Getting stack for ora_fg_cndba 13918 using short_stack in /prw/PRW_DB_cndba/prw_ora_fg_cndba_13918_07-24-18.outTue Jul 24 15:02:51 CST 2018: Getting stack for ora_pmon_cndba 4479 using short_stack in /prw/PRW_DB_cndba/prw_ora_pmon_cndba_4479_07-24-18.outTue Jul 24 15:02:51 CST 2018: Getting stack for ora_lgwr_cndba 4517 using short_stack in /prw/PRW_DB_cndba/prw_ora_lgwr_cndba_4517_07-24-18.outTue Jul 24 15:02:52 CST 2018: Getting stack for ora_ckpt_cndba 4519 using short_stack in /prw/PRW_DB_cndba/prw_ora_ckpt_cndba_4519_07-24-18.outTue Jul 24 15:02:55 CST 2018: Getting stack for ora_smon_cndba 4521 using short_stack in /prw/PRW_DB_cndba/prw_ora_smon_cndba_4521_07-24-18.outTue Jul 24 15:02:57 CST 2018: WARNING: CPU is 0 % idle - less than 3 % idle, sleeping 5 secondsTue Jul 24 15:03:03 CST 2018: Getting stack for ora_arc0_cndba 4569 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc0_cndba_4569_07-24-18.outTue Jul 24 15:03:03 CST 2018: Getting stack for ora_arc1_cndba 4573 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc1_cndba_4573_07-24-18.outTue Jul 24 15:03:04 CST 2018: Getting stack for ora_arc2_cndba 4575 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc2_cndba_4575_07-24-18.outTue Jul 24 15:03:07 CST 2018: Getting stack for ora_arc3_cndba 4577 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc3_cndba_4577_07-24-18.outTue Jul 24 15:03:10 CST 2018: Waiting for these debug procs to finish:ksh /prw/prw.sh shortstack start 4575 oracle cndba /u01/app/oracle/product/12.2.0/db_1 /prw/PRW_DB_cndba/prw_ora_arc2_cndba_4575_07-24-18ksh /prw/prw.sh shortstack start 4577 oracle cndba /u01/app/oracle/product/12.2.0/db_1 /prw/PRW_DB_cndba/prw_ora_arc3_cndba_4577_07-24-18……

Preacher的一个重要功能在于分析。经过分析后，它会自动将分析结果作为一系列的结果文件，直接告诉我们哪个进程出现问题。所以我们对于结果分析，可以直接从这个部分入手。

[oracle@host1 prw]$ cd PRW_DB_cndba/[oracle@host1 PRW_DB_cndba]$ lltotal 96-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc0_cndba_4569_07-24-18.out-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc1_cndba_4573_07-24-18.out-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc2_cndba_4575_07-24-18.out-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc3_cndba_4577_07-24-18.out-rwxr--r--. 1 oracle oinstall 4281 Jul 24 15:03 prw_ora_ckpt_cndba_4519_07-24-18.out-rwxr--r--. 1 oracle oinstall 5540 Jul 24 15:02 prw_ora_fg_cndba_13740_07-24-18.out-rwxr--r--. 1 oracle oinstall 17960 Jul 24 15:02 prw_ora_fg_cndba_13918_07-24-18.out-rwxr--r--. 1 oracle oinstall 4281 Jul 24 15:03 prw_ora_lgwr_cndba_4517_07-24-18.out-rwxr--r--. 1 oracle oinstall 4355 Jul 24 15:02 prw_ora_pmon_cndba_4479_07-24-18.out-rwxr--r--. 1 oracle oinstall 3702 Jul 24 15:03 prw_ora_smon_cndba_4521_07-24-18.out-rwxr--r--. 1 oracle oinstall 250 Jul 24 15:02 pw_latchholder_cndba_07-24-18.out-rwxr--r--. 1 oracle oinstall 1545 Jul 24 15:02 pw_lock_cndba_07-24-18.out-rwxr--r--. 1 oracle oinstall 1547 Jul 24 15:02 pw_sessionwait_cndba_07-24-18.out-rwxr--r--. 1 oracle oinstall 1714 Jul 24 15:08 pw_sqltimings_cndba_07-24-18.out-rwxr--r--. 1 oracle oinstall 4904 Jul 24 15:08 pw_waitchains_cndba_07-24-18.out

因此，对于上述所有数据，我们应该拥有所需的一切（大多数情况下）来解决争用问题。另外一个好处是，Procwatcher将自动收集这些数据而无需任何用户干预（一旦Procwatcher启动）。