作者：YongJing(敬勇)

一业务系统在白天业务时间出现了严重了IO性能问题,下面是下午业务高峰时间(3-5)的awr报告

从等待事件来看主要都是与IO相关

从上面可以看到除了几个语句的逻辑读很高，其实物理不是很高,每秒产生的重做日志以及物理读也不高.

检查磁盘IO

rx6600-1:[/]#sar -d 1 10

HP-UX rx6600-1 B.11.23 U ia64    07/15/14

16:18:45   device   %busy   avque   r+w/s  blks/s  avwait  avserv
16:18:46  c39t0d3  100.00    0.50      18    1130    0.00  132.11
          c41t0d3   83.50    0.50       6     450    0.00  290.78
16:18:47   c3t0d0    0.99    0.50       2      63    0.00    7.12
          c39t0d3   91.09    0.50      10     982    0.00  115.53
          c41t0d3  100.00    0.50      12     586    0.00  291.67
16:18:48   c3t0d0    3.03    0.50       2      32    0.00   15.93
          c39t0d3  100.00    0.50       9    1034    0.00  139.76
          c41t0d3   92.93    0.50       7     388    0.00  310.07
16:18:49   c3t0d0    2.00    0.50       4      64    0.00   19.59
          c39t0d3  100.00    0.50      12    1088    0.00  127.33
          c41t0d3   86.00    0.50       8     416    0.00  251.32
16:18:50   c3t0d0    1.01    0.50       1       2    0.00    8.99
          c39t0d3  100.00    0.50      16     954    0.00  117.10
          c41t0d3  100.00    0.50       9     614    0.00  295.52
16:18:51   c3t0d0    0.99    0.50       1       8    0.00   10.60
          c39t0d3   93.07    0.50      17     913    0.00  110.59
          c41t0d3  100.00    0.50       9     350    0.00  326.92
16:18:52  c39t0d3  100.00    0.50      21    1168    0.00  127.22
          c41t0d3   88.00    0.50      11     544    0.00  252.08
16:18:53   c3t0d0    2.02    0.50       3      48    0.00   18.51
          c39t0d3   88.89    0.50      19    1164    0.00   98.25
          c41t0d3  100.00    0.50      11     630    0.00  324.39
16:18:54   c3t0d0    3.00    0.50       3      20    0.00   12.39
          c39t0d3   95.00    0.50      20     954    0.00  131.90
          c41t0d3   81.00    0.50       9     610    0.00  289.05
16:18:55   c3t0d0    9.00    0.50      11     134    0.00    8.62
          c39t0d3  100.00    0.50      19    1090    0.00  137.20
          c41t0d3  100.00    0.50      11     512    0.00  327.16

Average   c39t0d3   99.50    0.50      16    1048    0.00  123.38
Average   c41t0d3  100.00    0.50       9     510    0.00  296.44
Average    c3t0d0    2.20    0.50       3      37    0.00   12.28
rx6600-1:[/]#sar -d 1 10

HP-UX rx6600-1 B.11.23 U ia64    07/15/14

16:20:04   device   %busy   avque   r+w/s  blks/s  avwait  avserv
16:20:05   c3t0d0    1.00    0.50       1      16    0.00    8.33
          c39t0d3   98.00    0.50      16     928    0.00  114.86
          c41t0d3   98.00    0.50      10     684    0.00  266.43
16:20:06   c3t0d0    1.98    0.50       4      81    0.00    8.57
          c39t0d3   93.07    0.50      19    1251    0.00  128.81
          c41t0d3   91.09    0.50       6     475    0.00  365.83
16:20:07   c3t0d0    2.00    0.50       3      48    0.00    5.87
          c39t0d3   98.00    0.50      23    1216    0.00  113.66
          c41t0d3   98.00    0.50       8     576    0.00  307.92
16:20:08   c3t0d0    1.00    0.50       2      32    0.00    5.36
          c39t0d3  100.00    0.50      21    1132    0.00  118.47
          c41t0d3  100.00    0.50       7     592    0.00  300.71
16:20:09   c3t0d0    6.00    0.58      13     194    2.22   26.05
          c39t0d3   89.00    0.50      17    1152    0.00  123.54
          c41t0d3   87.00    0.50       8     512    0.00  298.26
16:20:10   c3t0d0    3.00    0.50       6      96    0.00   22.78
          c39t0d3   85.00    0.50      17    1136    0.00  114.79
          c41t0d3   98.00    0.50       9     592    0.00  252.52
16:20:11   c3t0d0    1.00    0.50       1       2    0.00    8.04
          c39t0d3  100.00    0.50      17    1216    0.00  138.04
          c41t0d3  100.00    0.50      12     672    0.00  291.69
16:20:12   c3t0d0    2.00    0.50       3      34    0.00    9.24
          c39t0d3   99.00    0.50      16    1024    0.00  122.11
          c41t0d3   88.00    0.50       9     476    0.00  299.79
16:20:13  c39t0d3   91.00    0.50      18    1024    0.00  111.77
          c41t0d3   92.00    0.50       3     384    0.00  396.25
16:20:14  c39t0d3   99.00    0.50      17     892    0.00  132.15
          c41t0d3  100.00    0.50      10     608    0.00  233.54

Average    c3t0d0    1.80    0.53       3      50    0.87   17.64
Average   c39t0d3   96.00    0.50      18    1097    0.00  121.54
Average   c41t0d3  100.00    0.50       8     557    0.00  290.35

在业务人员下班后重启的双机软件，但在启动数据库时停在了Completed redo application这一步

SQL> startup
ORACLE instance started.

Total System Global Area 1.0318E+10 bytes
Fixed Size                  2073176 bytes
Variable Size            3238006184 bytes
Database Buffers         7063207936 bytes
Redo Buffers               14700544 bytes
Database mounted.

从alert.log文件中可以看到如下信息:

Tue Jul 15 22:23:29 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =61
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 500
  sessions                 = 555
  __shared_pool_size       = 3154116608
  __large_pool_size        = 16777216
  __java_pool_size         = 33554432
  __streams_pool_size      = 33554432
  sga_target               = 10317987840
  control_files            = /sx_data/ORCL/control01.ctl, /sx_data/ORCL/control02.ctl, /sx_data/ORCL/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 7063207936
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = LOCATION=/sx_data/arch_ORCL/
  log_archive_format       = %t_%s_%r.dbf
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /oracle/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 39600
  fast_start_parallel_rollback= FALSE
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  dispatchers              = (PROTOCOL=TCP) (SERVICE=ORCLXDB)
  local_listener           = ORCL
  job_queue_processes      = 10
  background_dump_dest     = /oracle/admin/ORCL/bdump
  user_dump_dest           = /oracle/admin/ORCL/udump
  core_dump_dest           = /oracle/admin/ORCL/cdump
  audit_file_dest          = /oracle/admin/ORCL/adump
  db_name                  = ORCL
  open_cursors             = 2000
  optimizer_index_cost_adj = 20
  optimizer_index_caching  = 90
  pga_aggregate_target     = 2576351232
PMON started with pid=2, OS id=13613
PSP0 started with pid=3, OS id=13615
MMAN started with pid=4, OS id=13617
DBW0 started with pid=5, OS id=13619
LGWR started with pid=6, OS id=13621
CKPT started with pid=7, OS id=13623
SMON started with pid=8, OS id=13625
RECO started with pid=9, OS id=13627
CJQ0 started with pid=10, OS id=13629
MMON started with pid=11, OS id=13631
Tue Jul 15 22:23:30 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=13635
Tue Jul 15 22:23:30 2014
starting up 1 shared server(s) ...
Tue Jul 15 22:23:31 2014
ALTER DATABASE   MOUNT
Tue Jul 15 22:23:39 2014
Setting recovery target incarnation to 2
Tue Jul 15 22:23:42 2014
Successful mount of redo thread 1, with mount id 1380841571
Tue Jul 15 22:23:42 2014
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Jul 15 22:23:42 2014
ALTER DATABASE OPEN
Tue Jul 15 22:23:47 2014
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Tue Jul 15 22:23:50 2014
Started redo scan
Tue Jul 15 22:23:52 2014
Completed redo scan
 336597 redo blocks read, 78835 data blocks need recovery
Tue Jul 15 22:23:52 2014
Started redo application at
 Thread 1: logseq 2270, block 29
Tue Jul 15 22:23:53 2014
Recovery of Online Redo Log: Thread 1 Group 4 Seq 2270 Reading mem 0
  Mem# 0: /sx_data/ORCL/redo04.log
Tue Jul 15 22:23:58 2014
Completed redo application

一直停在Completed redo application这，而这时的等待事件是checkpoint complete开始以为是并行恢复慢造成的，就查询了v$transaction，v$fast_start_transactions但视图中并没有进行恢复操作的事务存在.后来咨询了老熊，老熊说检查一下IO情况看是不是存储出问题了，如是再次检查存储IO性能:

rx6600-1:[/]#sar 1 10

HP-UX rx6600-1 B.11.23 U ia64    07/15/14

22:36:41    %usr    %sys    %wio   %idle
22:36:42       2       2      12      84
22:36:43       1       0      12      87
22:36:44       0       0      17      83
22:36:45       0       0      13      87
22:36:46       0       1      12      87
22:36:47       2       1      13      84
22:36:48       1       1      16      82
22:36:49       0       0      12      88
22:36:50       0       0      12      88
22:36:51       0       0      22      78

Average        1       0      14      85

从上面可以看到现在实际上并没有业务在跑居然还存在IO等待这是不正常的

rx6600-2:[/]#bdf
Filesystem          kbytes    used   avail %used Mounted on
/dev/vg00/lvol3     983040  422504  556176   43% /
/dev/vg00/lvol1    1835008  135048 1686776    7% /stand
/dev/vg00/lvol8    8912896 8535352  374824   96% /var
/dev/vg00/lvol7    7962624 2762312 5159704   35% /usr
/dev/vg00/lvol4     524288   83192  437784   16% /tmp
/dev/vg00/tmplv    2064384   93512 1847942    5% /oratmp
/dev/vg00/orasoft  10256384 3144652 6668390   32% /orasoft
/dev/vg00/oracle   20480000 5480497 14062042   28% /oracle
/dev/vg00/lvol6    9076736 5206384 3840128   58% /opt
/dev/vg00/lvol5     131072   25472  104824   20% /home
/dev/cwjcvg/cwjc_datalv
                   414973952 134188114 263239842   34% /cwjc_data
/dev/sxvg/sx_datalv
                   624689152 298665485 305654147   49% /sx_data

rx6600-2:[/]#time dd if=/dev/zero of=/var/test bs=8k count=100000

下面对小机自身的磁盘进行IO测试写800M的数据只要12秒左右

msgcnt 2 vxfs: mesg 001: vx_nospace - /dev/vg00/lvol8 file system full (1 block extent)
I/O error 
47185+0 records in
47184+1 records out

real       11.7
user        0.0
sys         0.8

但是对EMC存储进行IO测试写800M的数据只要30多分还没有完成

rx6600-2:[/]#time dd if=/dev/zero of=/sx_data/test bs=8k count=100000
711856+0 records in
711855+0 records out

real    30:58.4
user        0.5
sys        13.0

这明显的是存储出了问题,后面得知管理人员早上10点多发现了存储有一个磁盘损坏了，存储做的raid 5,有热备盘.而且还有上百G的数据进行存储级的同步.与出现性能问题的时间一至。

到此问题原因找到了解决起来也就简单了.幸亏问题解决了，第二天有大领导来检查要不就…….哈哈

由于监听程序原来是使用的是端口1532.现在修改成1521,结果不能启动说监听已经启动了.于是停止监听报错
TNS-01190: The user is not authorized to execute the requested listener command

[oracle@jyrac1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUL-2014 11:23:07

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started

[oracle@jyrac1 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUL-2014 11:23:24

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jyrac1)(PORT=1521)))
TNS-01190: The user is not authorized to execute the requested listener command

查看监听状态:

[grid@jyrac1 ~]$ ps -ef | grep -i listener
grid      4180     1  0 11:28 ?        00:00:00 /grid/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      4517  4138  0 11:56 pts/1    00:00:00 grep -i listener
[oracle@jyrac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUL-2014 11:24:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jyrac1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                03-JUL-2014 10:40:26
Uptime                    0 days 0 hr. 43 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/11.2.0/grid/network/admin/listener.ora
Listener Log File         /grid/11.2.0/grid/log/diag/tnslsnr/jyrac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jycs" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
Service "jycs (ORACLE_HOME =/u01/app/oracle/11.2.0/db" has 1 instance(s).
  Instance "jycs", status UNKNOWN, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs", status READY, has 1 handler(s) for this service...
The command completed successfully

其中:Security ON: Local OS Authentication 此条提示信息表明监听处于Local OS Authentication认证模式
Oracle 10g版本以及之后的版本中推出了监听的本地操作系统认证安全特性.若监听程序是在当前用户下启动的，则当前用户具有
管理监听的所有权利，其他用户对监听的管理将受到限制

因为数据库是11.2.0.1而且使用了oracle restart特性且用户为grid.注册了listener服务且只对默认端口1521有效.之前是1532所以
oracle restart不会自动重启监听.由于将端口修改成了1521所以oracle restart自动重启了listener

[grid@jyrac1 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): jyrac1

由于oracle restart 以grid用户自动启动了监听所以oracle用户不能重动由grid用户所启动的监听.

配置静态监听时SID_NAME名字大小写造成登录失败.对于oracle数据库来说同样的名字不一样的大小写表示完全不同的数据库实例。一旦静态监听的实例名字与对应的数据库实例不一致时，便会出现无法连接数据库的问题。

由于原来的1521端口要给另一个实例使用,现在的这个实例要使用另外的端口客户的就使用静态监听在设置完重启监听后登录出错.

SQL> conn test/test@127
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

查看oracle home目录和oracle_sid

[oracle@ggfwweb admin]$ echo $ORACLE_HOME
/u01/app/oracle/10gR2/db
[oracle@ggfwweb admin]$ echo $ORACLE_SID
hygeia
[oracle@ggfwweb admin]$ ps -ef | grep pmon
oracle   25830     1  0 09:39 ?        00:00:00 ora_pmon_hygeia
oracle   25950 25585  0 09:59 pts/1    00:00:00 grep pmon

查看监听文件文件

[oracle@ggfwweb admin]$ cd $ORACLE_HOME/network/admin
[oracle@ggfwweb admin]$ ls
listener.ora  listener.ora.bak  samples  shrept.lst  tnsnames.ora
[oracle@ggfwweb admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
  )
    (SID_DESC = 
      (GLOBAL_DBNAME = HYGEIA)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (SID_NAME = HYGEIA)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.142.11.108)(PORT = 1568))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

可以看到监听文件中使用的是HYGEIA,而ORACLE_SID是hygeia

将SID_NAME=HYGEIA修改为SID_NAME=hygeia后重启监听

[oracle@ggfwweb admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 03-JUL-2014 10:03:32

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.11.108)(PORT=1568)))
The command completed successfully

[oracle@ggfwweb admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 03-JUL-2014 10:03:53

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.142.11.108)(PORT=1568)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.11.108)(PORT=1568)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                03-JUL-2014 10:03:53
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.142.11.108)(PORT=1568)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hygeia" has 1 instance(s).
  Instance "hygeia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ggfwweb admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 3 10:04:12 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
SQL> conn test/test@127
Connected.

在配置静态监听时要注意数据库实例名本身是区分大小写的，因此在配置静态监听配置SID_NAME时一定要注意大小写

health monitor健康监控
从oracle 11g开始,oracle数据库包含对数据库运行诊断检查的健康监控框架

health check健康检查是对数据库的各层和组件乾地检查.健康检查会检测文件错误,物理和逻辑块错误,undo和redo错误,数据字典错误等等.健康检查会生成一个检查报告在许多情况下包含了问题解决的建议.健康检查有两种运行方式:
.reactive—故障诊断架构为了响应一个严重错误会自动运行健康检查.

.manual—–作为一个dba,可以使用dbms_hm包或企业管理器接口来手工运行健康检查.

健康检查会存储调查结果,建议并在ADR中存储其它的信息

健康检查能以以下两种方式来运行:
DB-online模式意味着当数据库处于打开状态(open或mount)时可以运行健康检查

DB-offline模式意味着当实例不可用且数据库处于关闭状态(nomount)时也能运行健康检查

所有的健康检查都能在DB-online模式下运行.只有重做日志完整性检查和数据库结构完整检查可以在DB-offline模式下运行.

健康检查的类型
健康监控运行以下检查:
DB Structure Integrity Check(数据库结构完整性检查)–这种检查验证数据文件的完整性,如果这些文件不能访问,文件错误
或者不一致时会报告这些错误信息.如果数据库在mount或者open状态,这种检查会检查控制文件中所列出的重做日志文件和数据文件.如果数据库在nomount状态,只会检查控制文件.

Data Block Integrity Check(数据块完整性检查)–这种检查会检查磁盘镜像块错误比如checksum故障,head/tail mismatch和数据块的逻辑不一致性.大多数的错误可以通过使用block media recovery来进行修复.错误块信息也会被v$database_block_corruption视图所捕获.这种检查不会检测inter-block或inter-segment错误.

Redo Integrity Check(重做完整性检查)–这种检查将会扫描重做日志内容的可访问性和错误信息,也能对归档日志文件进行检查.
这种检查会报告归档日志或重做日志的错误信息.

Undo Segment Integrity Check(回滚段完整性检查)–这种检查会发现逻辑undo错误.在定位一个undo错误之后,这种检查将使用PMON和SMON来尝试恢复这个错误事务.如果恢复失败,health monitor将会存储关于这个错误的信息到v$corrupt_xid_list中.大多数undo错误都可以通过强制提交来解决.

Transaction Interity Check–这种检查与undo segment integrity check是相同的只是它只检查一特定的事务.

Dictionary Integrity Check–这种检查会检查核心字典对象比如tab$或col$的完整性.它将执行以下操作:
.验证每一个字典对象的字典条目内容

.执行cross-row级别的检查.它将对字典中的行强制执行逻辑约束验证

.执行对象的关系检查,它将在字典对象之间强制执行父子关系验证

Dictionary Integrity Check操作会检查以下字典对象:
tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$,
con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_
view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$,
dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$,ecol$.

手工运行health check
健康监控提供了两种方式来手工运行健康检查:
.使用dbms_hm包

.使用企业管理器接口

使用dbms_hm来运行健康检查
dbms_hm包中有一个run_check过程用来运行健康检查.为了调用run_check需要提供检查的名称和运行的名字比如:

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','jy_dict_run');

PL/SQL procedure successfully completed.

为了获得一个健康检查名称列表执行以下查询:

SQL> select name from v$hm_check where internal_check='N';
NAME
----------------------------------------------------------------
DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

大多数的健康检查接受输入参数.可以通过查询v$hm_check_param来查看参数名和描述.一些参数是强制性的其它的是可选的.
如果一个可选参数被忽略将会使用缺省值.下面的查询将会显示所有健康检查的参数信息:

SQL> SELECT c.name check_name, p.name parameter_name, p.type,
  2  p.default_value, p.description
  3  FROM v$hm_check_param p, v$hm_check c
  4  WHERE p.check_id = c.id and c.internal_check = 'N'
  5  ORDER BY c.name;

CHECK_NAME                       PARAMETER_NAME       TYPE                 DEFAULT_VALUE    DESCRIPTION
-------------------------------- -------------------- -------------------- ---------------- ------------------------------
ASM Allocation Check             ASM_DISK_GRP_NAME    DBKH_PARAM_TEXT                       ASM 组名
CF Block Integrity Check         CF_BL_NUM            DBKH_PARAM_UB4                        控制文件块号
Data Block Integrity Check       BLC_DF_NUM           DBKH_PARAM_UB4                        文件号
Data Block Integrity Check       BLC_BL_NUM           DBKH_PARAM_UB4                        块号
Dictionary Integrity Check       CHECK_MASK           DBKH_PARAM_TEXT      ALL              检查掩码
Dictionary Integrity Check       TABLE_NAME           DBKH_PARAM_TEXT      ALL_CORE_TABLES  表名
Redo Integrity Check             SCN_TEXT             DBKH_PARAM_TEXT      0                最新良好重做的 SCN (如果已知)
Transaction Integrity Check      TXN_ID               DBKH_PARAM_TEXT                       事务处理 ID
Undo Segment Integrity Check     USN_NUMBER           DBKH_PARAM_TEXT                       还原段号

输入参数通过input_params参数以name/value对用分号来分隔进行传递.下面的例子用事务ID作为参数进行事务完整性检查:

SQL>BEGIN
DBMS_HM.RUN_CHECK (
check_name => 'Transaction Integrity Check',
run_name => 'my_trans_run',
input_params => 'TXN_ID=8.31.4');
END;
/

PL/SQL procedure successfully completed.

查看检查报告
在一个检查运行完后可以查看它的执行报告.这个可报告包括了调查结果,建议和其它的信息.也可以使用企业管理器,ADRCI工具,或者
dbms_hm包.其中企业管理器查看的报告格式为html,dbms_hm包查看的报告格式为html,xml和text,检查DRCL工个查看报告的格式为XML

检查运行的结果被存储在ADR,但报告不会立即生成.当你请求查看报告时可以使用dbms_hm或企业管理器来生成.如果报告不存在,首先
得用ADR中的检查结果数据来生成并以xml或html格式来存储.如果使用ADRCI工具如果报告文件不存必须首先运行命令来生成报告文件然后运行其它的命令来显示它的内容.

使用dbms_hm来查看检查报告
使用dbms_hm.get_run_report函数可以查看健康检查报告.这个函数可以以html,xml或text格式来显示.缺省格式为text:

SQL> SET LONG 100000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 512
SQL> select dbms_hm.get_run_report('jy_dict_run') from dual;

DBMS_HM.GET_RUN_REPORT('JY_DICT_RUN')
--------------------------------------------------------------------------------
Run Name : JY_DICT_RUN
Run Id : 1061
Check Name : Data Block Integrity Check
Mode : REACTIVE
Status : COMPLETED
Start Time : 2007-05-12 22:11:02.032292 -07:00
End Time : 2007-05-12 22:11:20.835135 -07:00
Error Encountered : 0
Source Incident Id : 7418
Number of Incidents Created : 0
Input Paramters for the Run
BLC_DF_NUM=1
BLC_BL_NUM=64349
Run Findings And Recommendations
Finding
Finding Name : Media Block Corruption
Finding ID : 1065
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Block 64349 in datafile 1:
'/u01/app/oracle/dbs/t_db1.f' is media corrupt
Message : Object BMRTEST1 owned by SYS might be unavailable
Finding
Finding Name : Media Block Corruption
Finding ID : 1071
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Block 64351 in datafile 1:
'/u01/app/oracle/dbs/t_db1.f' is media corrupt
Message : Object BMRTEST2 owned by SYS might be unavailable

使用ADRCI工具来查看检查报告
1.确保操作系统环境变量(比如 ORACLE_HOME)已经被设置.并输入以下命令:

[oracle@jyrac1 ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Jun 11 17:18:53 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"

工具启动后会显示以下提示符
adrci>

可以改变当前的ADR home目录.使用show homes命令可以列出所有的ADR home目录,set homepath命令用来改变当前ADR home目录.

adrci> show homes
ADR Homes:
diag/tnslsnr/jyrac1/listener
diag/rdbms/cs/cs
diag/rdbms/jytest/jytest
diag/rdbms/jy/jy
diag/rdbms/jycs/jycs
adrci> set homepath diag/rdbms/jycs/jycs

2.输入以下命令:
show hm_run
这个命令会列出所有运行过并（存储在v$hm_run)注册到ADR档案库中的检查

adrci> show hm_run

ADR Home = /u01/app/oracle/diag/tnslsnr/jyrac1/listener:
*************************************************************************
0 rows fetched
<adr_relation name="">
<adr_home name="/u01/app/oracle/diag/tnslsnr/jyrac1/listener">

ADR Home = /u01/app/oracle/diag/rdbms/cs/cs:
*************************************************************************
0 rows fetched

<adr_home name="/u01/app/oracle/diag/rdbms/cs/cs">

ADR Home = /u01/app/oracle/diag/rdbms/jytest/jytest:
*************************************************************************
0 rows fetched

<adr_home name="/u01/app/oracle/diag/rdbms/jytest/jytest">

ADR Home = /u01/app/oracle/diag/rdbms/jy/jy:
*************************************************************************
0 rows fetched

<adr_home name="/u01/app/oracle/diag/rdbms/jy/jy">

ADR Home = /u01/app/oracle/diag/rdbms/jycs/jycs:
*************************************************************************

**********************************************************
HM RUN RECORD 1
**********************************************************
   RUN_ID                        1
   RUN_NAME                      HM_RUN_1
   CHECK_NAME                    DB Structure Integrity Check
   NAME_ID                       2
   MODE                          2
   START_TIME                    2014-03-24 13:32:58.872509 +08:00
   RESUME_TIME                   
   END_TIME                      2014-03-24 13:33:01.710518 +08:00
   MODIFIED_TIME                 2014-03-24 13:33:01.710518 +08:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   

**********************************************************
HM RUN RECORD 2
**********************************************************
   RUN_ID                        61
   RUN_NAME                      HM_RUN_61
   CHECK_NAME                    DB Structure Integrity Check
   NAME_ID                       2
   MODE                          2
   START_TIME                    2014-04-08 10:22:43.295203 +08:00
   RESUME_TIME                   
   END_TIME                      2014-04-08 10:22:43.723241 +08:00
   MODIFIED_TIME                 2014-04-08 10:22:43.723241 +08:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   

**********************************************************
HM RUN RECORD 3
**********************************************************
   RUN_ID                        81
   RUN_NAME                      jy_dict_run
   CHECK_NAME                    Dictionary Integrity Check
   NAME_ID                       24
   MODE                          0
   START_TIME                    2014-06-11 16:42:00.675293 +08:00
   RESUME_TIME                   
   END_TIME                      2014-06-11 16:42:02.950141 +08:00
   MODIFIED_TIME                 2014-06-11 17:14:33.658642 +08:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   /u01/app/oracle/diag/rdbms/jycs/jycs/hm/HMREPORT_jy_dict_run.hm

3.定位你要创建报告的检查并注意检查运行的名称.如果检查报告已经存在那么report_file字段就会包含一个文件名比如上面的
/u01/app/oracle/diag/rdbms/jycs/jycs/hm/HMREPORT_jy_dict_run.hm.否则使用下面的命令来生成一个报告.

adrci> create report hm_run jy_dict_run_1
DIA-48448: This command does not support multiple ADR homes

这是因为当前存在多个ADR home目录

adrci> show homes
ADR Homes:
diag/tnslsnr/jyrac1/listener
diag/rdbms/cs/cs
diag/rdbms/jytest/jytest
diag/rdbms/jy/jy
diag/rdbms/jycs/jycs
adrci> set homepath diag/rdbms/jycs/jycs
adrci> create report hm_run jy_dict_run_1

4.执行以下命令来查看报告:

adrci> show report hm_run jy_dict_run_1
< ?xml version="1.0" encoding="US-ASCII"?>HM Report: jy_dict_run_1
    <run_info>
        <check_name>Dictionary Integrity Check
        <run_id>101
        <run_name>jy_dict_run_1
        <run_mode>MANUAL
        <run_status>COMPLETED
        <run_error_num>0
        <source_incident_id>0
        <num_incidents_created>0
        <run_start_time>2014-06-11 17:27:13.477462 +08:00
        <run_end_time>2014-06-11 17:27:14.734166 +08:00
    
    <run_parameters>
        <run_parameter>TABLE_NAME=ALL_CORE_TABLES
        <run_parameter>CHECK_MASK=ALL
    
    

health monitor视图
可以用视图来代替检查报告来查看特定检查的结果.可用的视图有v$hm_run,v$hm_finding,v$hm_recommendation
下面查询v$hm_run来判断运行检查的确良历史信息:

SQL> SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

    RUN_ID NAME                             CHECK_NAME                       RUN_MODE SRC_INCIDENT
---------- -------------------------------- -------------------------------- -------- ------------
        61 HM_RUN_61                        DB Structure Integrity Check     REACTIVE            0
        81 jy_dict_run                      Dictionary Integrity Check       MANUAL              0
       101 jy_dict_run_1                    Dictionary Integrity Check       MANUAL              0
         1 HM_RUN_1                         DB Structure Integrity Check     REACTIVE            0

下面查询v$hm_finding来获得RUN_ID 61的详细信息:

SELECT type, description FROM v$hm_finding WHERE run_id = 61;
TYPE          DESCRIPTION
------------- -----------------------------------------------------------------------------
FAILURE      Block 64349 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt
FAILURE      Block 64351 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt

health check参数

Table 9–6 Parameters for Data Block Integrity Check
------------------------------------------------------------------------------------------------------------------
Parameter Name                Type                  Default Value           Description
------------------------------------------------------------------------------------------------------------------
BLC_DF_NUM                    Number                (none)                  Block datafile number
BLC_BL_NUM                    Number                (none)                  Data block number
------------------------------------------------------------------------------------------------------------------


Table 9–7 Parameters for Redo Integrity Check
------------------------------------------------------------------------------------------------------------------
Parameter Name                Type                   Default Value          Description
------------------------------------------------------------------------------------------------------------------
SCN_TEXT                      Text                   0                      SCN of the latest good redo (if known)
------------------------------------------------------------------------------------------------------------------


Table 9–8 Parameters for Undo Segment Integrity Check
------------------------------------------------------------------------------------------------------------------
Parameter Name                Type                   Default Value          Description
------------------------------------------------------------------------------------------------------------------
USN_NUMBER                    Text                   (none)                 Undo segment number
------------------------------------------------------------------------------------------------------------------


Table 9–9 Parameters for Transaction Integrity Check
------------------------------------------------------------------------------------------------------------------
Parameter Name                Type                   Default Value          Description
------------------------------------------------------------------------------------------------------------------              
TXN_ID                        Text                   (none)                 Transaction ID
------------------------------------------------------------------------------------------------------------------

Table 9–10 Parameters for Dictionary Integrity Check
------------------------------------------------------------------------------------------------------------------
Parameter Name                Type                   Default Value          Description
------------------------------------------------------------------------------------------------------------------
CHECK_MASK                    Text                   ALL                    Possible values are:
                                                                            COLUMN_CHECKS—Run column
                                                                            checks only. Verify column-level
                                                                            constraints in the core tables.

                                                                            ROW_CHECKS—Run row checks only.
                                                                            Verify row-level constraints in the
                                                                            core tables.

                                                                            REFERENTIAL_CHECKS—Run
                                                                            referential checks only. Verify
                                                                            referential constraints in the core
                                                                            tables.
                       
                                                                            ALL—Run all checks.

TABLE_NAME                    Text                   ALL_CORE_TABLES        Name of a single core table to check. If
                                                                            omitted, all core tables are checked.
------------------------------------------------------------------------------------------------------------------

oracle restart性特
在oracle 11g r2 以前对于单实例一般都是写脚本为自动启动oracle,在oracle 11g r2中使用oracle restart功能来配置在硬件或软件出现故障或者数据库所在主机重启之后自动重新启动数据库,监听和其它oracle组件对于非集群环境，只需要安装OracleGrid Infrastructure，在安装的时候选择“仅安装网格基础结构软件”，然后运行如下脚本
来安装Oracle Restart：$GRID_HOME/crs/install/roothas.pl

[root@jyrac1 install]# ./roothas.pl
2014-05-28 12:11:54: Checking for super user privileges
2014-05-28 12:11:54: User has super user privileges
2014-05-28 12:11:54: Parsing the host name
Using configuration parameter file: ./crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node jyrac1 successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting

jyrac1     2014/05/28 12:12:47     /grid/11.2.0/grid/cdata/jyrac1/backup_20140528_121247.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
[root@jyrac1 install]# srvctl
-bash: srvctl: command not found
[root@jyrac1 install]# su - grid
[grid@jyrac1 ~]$ srvctl
Usage: srvctl  

oracel restart提高了数据库的可用性.当你安装oracle restart之后在硬件或软件出现故障或者数据库所在主机重启之后各种
oracle组件能够自动重启.
表: 通过oracle restart自动重启的oracle组件

------------------------------------------------------------------------------------------------------------
组件                           说明
------------------------------------------------------------------------------------------------------------
实例                           oracle restart能够用于单主机上的多个数据库实例

监听程序

数据库服务                     不包括默认创建的缺省服务因为它是由oracle数据库来管理的且不包括在数据库创建时
                               所创建的缺省服务

oracle asm实例

oracle asm磁盘组               重新启动磁盘组并加载磁盘组

oracle ONS通知服务             在单独的环境中,ONS在data guard安装中被用来在主库和备库之间通过
                               fast application notification(fan)来自动进行故障切换.ONS是一个服务用来发送
                               FAN事件被集成到客户端的故障转移功能中.

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

oracle restart会定期运行检查操作来监控这些组件的健康.如果对一个组件的检查操作失败,那么这个组件会被关闭然后再重新启动.

oracle restart只能用于独立的服务器环境(非集群).对于oracle rac来说是由oracle clusterware来提供自动重启组件的功能.

oracle restart是在oracle grid 架构目录之外的目录运行的它的安装目录是与oracle数据库的home目录分开的.

关于启动的依赖性
oracle restart能确保oracle组件根据组件之间的依赖性以合适的顺序被启动.例如,如果数据文件被存储在oracle asm磁盘组中,那么在启动数据库实例之前,oracle restart会确保oracle asm实例被启动且被要求的磁盘组会被加载.同样的如果一个组件必须被关闭,oracle restart会确保依赖组件首先会被关闭.

oracle restart也管理数据库实例和oracle监听之间的弱依赖:当一个数据库实例被启动时,oracle restart会试图启动监听.如果监听启动失败,数据库仍然处于启动状态.如果监听后启动失败,oracle restart不会关闭实例然后再重启实例.

并于使用oracle restart启动和停止组件
当有需要时oracle restart会自动重启各种oracle组件,当你手动关闭你的操作系统时oracle restart会以合理的顺序来停止oracle组件.可能有时候你想手动启动或者停止单个的oracle组件.oracle restart包括服务控制工具(srvctl)可以用它来手动启动和停止由oracle restart管理的组件.当使用oracle restart时,oracle强烈建议使用srvctl来手动启动和停止组件.

在你使用srvctl停止一个组件,如果出现故障oracle restart不会自动重启这个组件.如果你使用srvctl启动这个组件,那么这个组件对于自动重启又再次可以使用.

oracle工具象SQL*Plus,监听控制工具(LSNRCTL)和ASMCMD都被集成到oracle restart中.如果使用SQL*Plus关闭数据库,oracle restart不会把数据库看作是故障不会试图重新启动数据库.类似地如果你使用SQL*Plus或ASMCMD关闭oracle asm实例,oracle restart不会试图重启它.

使用srvctl启动一个组件和使用SQL*Plus(或者其它工具)启动有以下重要的不同:
.当你使用srvctl启动一个组件时,这个组件所依赖的任何组件都会首先以合理的顺序自动启动.

.当你使用SQL*Plus(或其它工具)启动一个组件该组件所依赖的其它组件是不会自动启动的,你必须确保它所依赖的组件已经被启动.

另外通过在指定的oracle home目录中oracle restart使用单个命令能够让你启动和停止所有的组件.这个oracle home目录可以是一个数据库home目录或者oracle grid infrastructure home目录.这个功能在打补丁时是非常有用的.

关于启动和停止oracle restart
crsctl工具用来启动和停止oracle restart.也可以使用crsctl工具来启用或禁用oracle高可用服务.oracle restart使用高可用服务来自动启动和停止由oracle restart管理的组件.例如,oracle高可用服务守护进程会自动启动数据库,监听和oracle asm实例.当oracle高可用服务被禁用时,当一个节点被重启时不会有通过oracle restart来管理的组件被自动启动.

通常来说在oracle安装时当要停止所有运行的oracle软件时可以使用crsctl工具.例如,录正在打补丁或者执行操作系统维护操作时可能需要停止oracle restart.当维护操作完成后可以使用crsctl工具启动oracle restart.

oracle restart配置
oracle restart维护了一个由它管理的所有oracle组件的一个组件列表和每一个组件的配置信息.所有这些配置信息是一个集合被称作oracle restart配置.当oracle restart启动一个组件时它将根据这个组件的配置信息来启动这个组件.例如,oracle restart配置包含数据库的一个本地服务器参数文件(spfile)和监听程序的监听端口.

如果你先安装oracle restart然后再使用DBCA创建数据库那么DBCA会自动将数据库添加到oracle restart配置中.当DBCA启动 数据库时,在数据库和其它组件之间要求的依赖关系(例如存储数据的磁盘组)会被创建,oracle restart将开始管理数据库.

可以使用srvctl命令来向oracle restart配置中手动增加或删除组件.例如,如果你在运行数据库的主机上安装了oracle restart 可以使用srvctl来向oracle restart配置增加一个数据库.当你手动向oracle restart配置中增加一个组件后可以使用srvctl 来启动它.oracle restart就会开始管理这个组件当有需要时重启这个组件.

注意:向oracle restart配置增加一个组件也可以称作使用oracle restart注册一个组件

其它的srvctl命令可以用来查看oracle restart管理组件的状态和配置信息,如临时禁用和重新启用组件管理等等.

当安装oracle restart后许多创建oracle组件的操作会自动将组件增加到oracle restart配置中.

表:创建操作和oracle restart配置

---------------------------------------------------------------------------------------------------------
创建操作                                                     创建组件并自动增加到oracle restart配置中?
---------------------------------------------------------------------------------------------------------
使用OUI或DBCA创建数据库                                          yes

使用create database语句创建数据库                                no

使用OUI,DBCA或ASMCA创建oracle asm实例                            yes

使用任何方法创建磁盘组                                           yes

使用netca添加一个监听                                            yes

使用srvctl创建一个数据库服务                                     yes

通过修改service_name初始化参数创建一个数据库服务                 no

使用dbms_service.create_service创建一个数据库服务                no

创建一个备份数据库                                               no
---------------------------------------------------------------------------------------------------------

下在的表格列出了一些delete/drop/remove操作是否会自动从oracle restart配置中删除组件
表: Delete/Drop/Remove Operations and the Oracle Restart Configuration

---------------------------------------------------------------------------------------------------------
操作                                                                自动从oracle restart配置中删除组件?
---------------------------------------------------------------------------------------------------------
使用DBCA删除一个数据库                                               yes

使用操作系统命令删除数据文件来删除数据库                             no

使用netca删除监听                                                    yes

使用任何方法来删除一个磁盘组                                         yes

使用srvctl删除数据库服务                                             yes

通过其它方法来删除数据库服务                                         no
---------------------------------------------------------------------------------------------------------

配置oracle restart
如果对单机环境通过安装oracle grid infrastructure安装了oracle restart然后再创建数据库,数据库会自动被增加到oracle restart配置中,然后在需要时自动重启.然而如果在创建数据库之后才安装oracle restart,那么就需要手动向oracle restart配置中增加数据库,监听,oracle asm管理实例,和其它要用的组件.

在配置oracle restart来管理数据库后能够做以下事情:
.向oracle restart配置中增加组件

.从oracle restart配置中删除组件

.临时暂停oracle restart对一个或多个组件管理

.对单个组件修改oracle restart的配置选项.

准备运行srvctl
要确保从正确的oracle home目录中运行srvctl,且使用正确的用户登录主机.表4-6列出了使用srvctl能配置的组件列表.对于每一个组件列出了运行srvctl所要求的oracle home目录.

表: 判断从哪个oracle home目录中运行srvctl

-------------------------------------------------------------------------------------------------------------
被配置的组件                                      运行srvctl的oracle home目录
-------------------------------------------------------------------------------------------------------------
database,database service                         database home
oracle asm instance,disk group,                   oracle grid infrastructure home
listener,ONS
-------------------------------------------------------------------------------------------------------------

假设监听不是从oracle grid infrastructure home目录中启动的.如果你对一个存在的数据库安装oracle restart,那么监听可能要从database home目录中启动,在这种情况下从database home目录中运行srvctl

为了运行srvctl
1.判断应该从哪个oracle home目录运行srvctl

2.如果打算运行srvctl命令来修改oracle restart配置(add,remove,enable,disable等等),那么可以按以下步骤来操作:
.在unix和linux中,使用安装你所判断运行srvctl命令的oracle home目录用户登录到数据库所在主机

.在windows上使用管理员登录系统
否则使用任何用户登录到系统

3.打开命令窗口输入要使用的srvctl命令.为了输入命令,要确保在path环境变理中设置了srvctl程序.否则要输入srvctl程序
的完全路径.


获取srvctl帮助
srvctl工具的联机帮助文档是可用的:
为了获取srvctl的帮助:
1.准备运行srvctl

2.输入以下命令:
srvctl

为了获取更详细的帮助输入以下命令:
srvctl -h

为了获取特定命令的帮助输入以下命令:
srvctl command -h

例如为了获取每一个组件类型的不同选项和add命令的帮助信息输入:
srvctl add -h

为了获取特定组件的组件类型的特定命令输入:
srvctl command object -h

为了获取关于增加一个数据库服务的帮助信息输入以下命令:
srvctl add service -h

向oracle restart配置中增加组件
在大多数情况下,在正在运行oracle restart的主机上创建一个oracle组件会自动向oracle restart配置中增加这个组件

下面是你要使用srvctl手动向oracle restart配置增加组件的情况:
.在创建数据库之后才安装oracle restart

.在相同主机上使用create database语句创建另外的数据库.

.使用dbms_service.create_service过程创建一个数据库服务

注意:向oracle restart配置中增加一个组件也叫作使用oracle restart注册一个组件

向oracle restart配置中增加组件后不会启动这个组件.必须使用srvctl start命令来启动它.

你也可以使用oracle企业管理数据库控制台来向oracle restart配置增加一个数据库或监听.

注意:当你手动向oracle restart配置增加一个数据库时,必须将oracle grid infrastructure software的所有者(用户)
加到数据库的osdba组中.这是因为grid infrastructure组件必须能够以sysdba角色连接到数据库启动和停止数据库.

例如,如果安装grid infrastructure软件的用户是grid,数据库的osdba组是dba,那么用户grid必须是dba组的一员.

使用srvctl增加组件
当使用srvctl向oracle restart配置中增加一个组件时,你能对这个组件指定配置选项.
1.准备运行srvctl的环境

2.输入下面的命令:
srvctl add object options
这里的object是一个组件.

增加一个数据库
这个例子使用db_unique_name=jycs来增加一个数据库组件.这个强制的-o选项用来指定oracle home目录的位置
oracle@jyrac1 ~]$ srvctl add database -d jycs -o /u01/app/oracle/11.2.0/db


增加一个数据库服务
对于db_unique_name=jycs的数据库创建一个新的数据库服务名jytest并将这个数据库服务增加到oracle restart配置中
[oracle@jyrac1 ~]$ srvctl add service -d jycs -s jytest

增加缺省的监听程序
向oracle restart配置中增加一个缺省的监听程序:(注意增加监听程序时要使用grid_home目录
那么在添加监听的时候应该指定GI_HOME，而非ORACLE_HOME)
[grid@jyrac1 ~]$ srvctl add listener -o /grid/11.2.0/grid/

现在来验证数据库会不会在主机重启时自动启动

[root@jyrac1 ~]# reboot
Broadcast message from root (pts/2) (Wed Jun  4 10:30:32 2014):

The system is going down for reboot NOW!

在主机重启之后通过下面的命令来检查发现数据库和监听程序也自动启动了

[root@jyrac1 ~]# ps -ef | grep pmon
oracle    3451     1  0 10:32 ?        00:00:00 ora_pmon_jycs
root      3563  3530  0 10:32 pts/1    00:00:00 grep pmon
[root@jyrac1 ~]# ps -ef | grep tns
grid      3438     1  0 10:32 ?        00:00:00 /grid/11.2.0/grid/bin/tnslsnr LISTENER -inherit
root      3565  3530  0 10:33 pts/1    00:00:00 grep tns

[grid@jyrac1 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....ER.lsnr ora....er.type ONLINE    ONLINE    jyrac1
ora.cssd       ora.cssd.type  OFFLINE   OFFLINE
ora.diskmon    ora....on.type OFFLINE   OFFLINE
ora.jycs.db    ora....se.type ONLINE    ONLINE    jyrac1

从oracle restart配置中删除组件
当使用oracle推荐的方法来删除一个组件时这个组件会自动从oracle restart配置中删除.例如,如果使用DBCA来删除一个数据库,
DBCA会从oracle restart配置中删除数据库.同样的如果使用netca来删除监听,netca会从oracle restart配置中删除监听.

如果使用非建议的或手动删除方法来删除组件,必须首先使用srvctl从oracle restart配置中删除组件.不这样做可能会出现错误

从oracle restart配置中删除一个组件:
srvctl remove object [options]

例如下面删除一个db_unique_name为dbcrm的数据库
srvctl remove database -d dbcrm

对一个组件禁用或启用oracle restart配置
可以临时对一个组件禁用oracle restart配置.一种原因就是当对组件执行维护任务时.例如,如果一个组件必须被修复,那么可能想让它在出现故障或者主机重启时不自动启动.

当维护任务完成后可以重新对组件启用管理

当禁用一个组件时:
.它将不再自动重启
.通过依赖组件它将不再自动启动
.使用srvctl不能启动
.任何依赖于这个资源的组件不再自动启动或者自动重启

禁用或启动一个组件的自动重启执行以下操作之一:
.禁用一个组件输入以下命令:
srvctl disable object [options]

启动一个组件输入以下命令:
srvctl enable object [options]

例如:对一个db_unique_name为dbcrm的数据库禁用自动重启
srvctl disable database -d dbcrm

对一个asm磁盘组名为recovery的磁盘组禁用自动重启
srvctl disable diskgroup -g recovery

查看组件状态
可以使用srvctl来查看任命由oracle restart管理的组件的运行状态.对于有些组件还会显示额外的信息.

查看组件的状态:
srvctl status object [options]

例如查看db_unique_name为jycs的数据库的状态
[oracle@jyrac1 ~]$ srvctl status database -d jycs
Database is running.

查看一个组件的oracle restart配置信息
可以使用srvctl来查看任何组件的oracle restart配置.oracle restart对于每种组件类型维护不同的配置信息.使用srvctl 命令
可以获得由oracle restart所管理的组件列表.

查看组件配置:
srvctl config object options

例如:查看由oracle restart所管理的所有数据库列表
[oracle@jyrac1 ~]$ srvctl config database
jycs

查看一个特定数据库的配置信息:
下面的例子查看db_unique_name为jycs的数据库的配置:
[oracle@jyrac1 ~]$ srvctl config database -d jycs
Database unique name: jycs
Database name:
Oracle home: /u01/app/oracle/11.2.0/db
Oracle user: grid
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups:
Services:

修改组件的oracle restart配置
可以使用srvctl来修改组件的oracle restart配置.例如可以修改当oracle重启动时的监听程序的监听端口或者在启动数据库时oracle restart所指示的服务器参数文件(SPFILE)

修改组件的oracle restart配置
srvctl modify object options

例如:对于db_unique_name为dbcrm的数据库使用下面的命令将管理策略修改为manual启动选项修改为nomount:
srvctl modify database -d dbcrm -y manual -s nomount

使用manual管理策略,当数据库主机重启时数据库不会自动重启.然而,oracle restart将会继续监控数据库如果出现故障将会重启.

[oracle@jyrac1 ~]$ srvctl modify database -h

Modifies the configuration for the database.

Usage: srvctl modify database -d [-n ] [-o ] [-u ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s ] [-t ] [-y {AUTOMATIC | MANUAL}] [-a ““|-z]
-d Unique name for the database
-n Database name (DB_NAME), if different from the unique name given by the -d option
-o ORACLE_HOME path
-u Oracle user
-m Domain for database. Must be set if database has DB_DOMAIN set.
-p Server parameter file path
-r Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s Startup options for the database. Examples of startup options are open, mount, or nomount.
-t Stop options for the database. Examples of shutdown options are normal, transactional, immediate, or abort.
-y Management policy for the database (AUTOMATIC or MANUAL)
-a “” Comma separated list of disk groups
-z To remove database’s dependency upon disk groups
-h Print usage

管理oracle restart配置中的环境变量.
在oracle restart配置中可以存储环境变量的名值对.如果你通常在启动数据库之前设置环境变量(除了oracle_home和oracle_sid
之外),你可以在oracle restart配置中设置其它的环境变量.可以在下面的组件的单个配置中存储任何数量的环境变量.
.database实例
.监听
.oracle asm实例

当oracle restart启动一个组件时它首先会使用存储在组件配置中的的值来设置组件的环境变量.尽管可以通过oracle组件这种方式
来设置环境变量,这个功能主要是倾向于设置操作系统环境变量.

设置和取消环境变量
使用srvctl来对oracle restart配置中的组件来进行设置或者取消环境变量.

对组件设置或取消环境变量:
.为了设置环境变理使用以下命令:
srvctl setenv {asm|database|listener} options

从配置中删除环境变量输入以下命令:
srvctl unsetenv {asm|database|listener} options

例如设置数据库环境变量
对db_unique_name为dbcrm的数据库在oracle restart配置中设置NLS_LANG和AIX AIXTHREAD_SCOPE环境变量:
srvctl setenv database -d dbcrm -t “NLS_LANG=AMERICAN_AMERICA.AL32UTF8,AIXTHREAD_SCOPE=S”

查看环境变量
使用srvctl来查看oracle restart配置中组件的环境变量.
查看环境变量的配置:
srvctl getenv {database|listener|asm} options

例如查看数据库的所有环境变量
查看db_unique_name为dbcrm的数据库在oracle restart配置中的环境变量:
srvctl getenv database -d dbcrm
dbcrm:
NLS_LANG=AMERICAN_AMERICA
AIXTHREAD_SCOPE=S
GCONF_LOCAL_LOCKS=1

例如查看数据库的特定环境变量
查看数据库的NLS_LANG和AIXTHREAD_SCOPE环境变量:
srvctl getenv database -d dbcrm -t “NLS_LANG,AIXTHREAD_SCOPE”
dbcrm:
NLS_LANG=AMERICAN_AMERICA
AIXTHREAD_SCOPE=S

使用srvctl来创建和删除数据库服务
当使用oracle restart来管理数据库时,oracle建议使用srvctl来创建来删除数据库服务.当使用srvctl来增加一个数据库服务时,
这个服务会自动增加到oracle restart配置中,而且在服务与数据库之间的依赖关系会被建立.因此如果启动服务当数据库没有
启动时oracle restart首先会启动数据库.

当使用srvctl删除一个数据库服务时这个服务也会从oracle restart配置中删除

使用srvctl来创建一个数据库服务:
srvctl add service -d db_unique_name -s service_name [options]

例如创建一个数据库服务

对db_unique_name为dbcrm的数据库创建一个名叫crmbatch的数据库服务名

srvctl add service -d dbcrm -s crmbatch

例如创建一个基于角色的数据库服务
创建一个名叫crmbatch的数据库服务并指定它的data guard角色为physical_standby.只有dbcrm数据库的当前角色为物理备库时
这个服务才会自动启动.

srvctl add service -d dbcrm -s crmbatch -l PHYSICAL_STANDBY

使用srvctl删除数据库服务:
srvctl remove service -d db_unique_name -s service_name [-f]

数据库服务将会从oracle restart配置中被删除掉.如果-f强制标示出现即使服务正在运行也会被删除.如果没有-f标示如果服务
正在运行会报错.

与oracle restart相关的crsctl命令

crsctl命令的语法如下:
crsctl command has
这里command是start,stop或enable,disable等命令的简称.而has对象是指示oracle高可用服务.

check显示oracle restart的状态.
[grid@jyrac1 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online

config显示oracle restart配置
CRS-4622: Oracle High Availability Services autostart is enabled.

disable禁用oracle restart的自动重启
[grid@jyrac1 ~]$crsctl disable has

enable启用oracle restart的自动重启
[grid@jyrac1 ~]$crsctl enable has

start启动oracle restart
[grid@jyrac1 ~]$crsctl start has

stop停止oracle restart
[grid@jyrac1 ~]$crsctl stop has [-f]

-f选项:如果任何由oracle restart管理的资源仍然在运行.然后试图停止这些资源.如果资源不能被停止那么试图强制停止.

个人觉得对于单个实例使用oracle restart与写脚本来启动也没有什么优势

操作系统sun,oracle 9.2.0.8.一开始维护人员新建一个用户只授予connect权限.用新建用户登录后发现能查询生产用户的数据.然后从dba_sys_privs视图将public用户给删除了.然后应用程序执行sql语句就报ORA-06553：PLS-213：Standard包不可访问的问题,解决这个问题的方法是执行catalog.sql和catproc.sql重建数据字典.
SQL>sqlplus /nolog
SQL> conn sys/密码 as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmsutil.sql
SQL>alter package standard compile
SQL>alter package dbms_standard compile
ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE
在执行alter package dbms_standard compile语句时出错了

在MOS上有一篇关于oracle 9.2.0.8中关于ORA-04020错误的bug,信息如下:
Researching the issue on ora-4020 and SYS.CDC_CREATE_CTABLE_BEFORE lead to BUG 3228083 which was
experiencing similar problems on the same object. This bug was closed as a duplicate of bug 3017048.
Internal BUG:3228083 – Appsst10g:R8:Utlrcmp Error: Ora-04045: Sys.Cdc_Create_Ctable_Before

解决方法， 用spfile 创建pfile, 在pfile里添加如下内容，然后用修改之后的pfile启动数据库，在执行脚本。
_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0
脚本执行完后，在去掉这些参数，正常启动数据库即可

在成功执行alter package dbms_standard compile语句后再执行下面的脚本来编译无效对象:
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
然而再次遇到bug
ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []

在mos上ORA-00600 [4406] or ORA-00600 [4412] in alert Log (文档 ID 742118.1)是关于这个bug的描述
In this Document
Symptoms
Cause
Solution
References
APPLIES TO:

Oracle Server – Enterprise Edition – Version 9.2.0.1 to 9.2.0.8 [Release 9.2]
Information in this document applies to any platform.
***Checked for relevance on 10-Aug-2012***
SYMPTOMS

On Oracle 9i, the following errors could be reported in the alert log

ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []
ORA-00600: internal error code, arguments: [4406], [0x8BAA483C], [0x0], [], [], [], [], []

The Call Stack should look something like:

ktcrcm ktcsod kssdch_stage ksuxds ksudel opilof opiodr ttcpip opitsk opiino opiodr opidrv sou2o

CAUSE

Unpublished Bug 2628920 ORA-600 [4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA.

The bug explains that the problem may occur when the user terminates or cancels a ‘Delete schema’ operation using CTRL + C for example. The function opilof in the Call Stack above is an indication that the error is only seen at session log off.

SOLUTION

1. Upgrade to 10g where the bug is fixed.

OR

2. Ignore the error as it is completely harmless, the error is generated at session log off.

Note that in some cases the errors have also been encountered on Oracle 10.2 releases. These may be related to a different bug, but remain harmless.
REFERENCES

@ BUG:2628920 – ORA-600:[4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA

解决方法是升级到oracle 10g.这里选择手工对无效对象进行编译.

什么是high version cursor(高版本游标)?
对于一个特定的游标有多少个版本就属于高版本游标是没有明确定义的.对于不同的系统有不同的数量界定.然而在awr报告中对于一个父游标超过20个子游标个数时就会被报告出来

然而当一个游标的版本数据达到成百上千,那么这些绝对是高版本游标.所以要检查这些sql有高版本的原因要尽量使用这些sql能够被共享.

什么是共享sql?
首先要记住的是所有sql语句都是式共享的.当一个sql语句被输入时,oracle将会对一个语句的文本创建一个hash value,oracle将使用这个hash value很容易地在共享池中查找是否已经存在有相同hash value的sql存在.

例如:select count*) from emp语句有一个hash value为 4085390015
那么oracle就会对这个sql语句创建一个父游标和一个子游标.如果一个sql语句永远也不会被共享也没关系-当它第一次被解析时会创建一个父游标和一个子游标.可以简单地认为这个父游标代表这个hash value,子游标代表sql的元数据.

什么是元数据?
元数据是能让sql语句运行的所有信息.例如,在上面的例子中给定的emp表属于scott用户,因此它有一个object_id来指示这个emp表.当scott用户登录时,对于运行这个语句的会话优化器参数会被初始化,所以优化器的使用也属于元数据.

当scott用户重新登录后运行相同的命令(相同的sql语句),这时在共享池中已经存在相同的sql,(但是我们是不知道的),将对这个sql生成hash value并且在共享池搜索这个hash value.如果找到这个hash value,就会通过子游标来进行搜索来判断是否存在子游标可以被重用(元数据相同).如果是那么就可以共享这个sql语句.

现在在共享池中这个sql语句只有一个子游标,因为元数据相同能让我们使用已经存在的子游标来共享sql语句.父游标不是判断能不能共享的基础子游标才能决定是否共享.

现在如果另一个用户test也有一个emp表.如果这个用户也运行上面的查询语句将会发生什么:
1. 会对这个语句创建一个hash value.它的hash value为4085390015
2. 这个sql在共享池中被找到
3. 搜索子游标(在这时已经有一个子游标了)
4. 因为test用户的emp表的object_id与scott用户的emp表的object_id是不同的所以会有一个’mismatch’
(本质上这里会依次搜索子游标链表,使用所有的子游标与当前sql的元数据进行比较.如果已经搜索了100个子游标直到找到一个可以共享的子游标为止.如果没有找到可以共享的子游标那么就会重新创建一个子游标

5. 因此创建一个新的子游标所以现在有1个父游标和2个子游标.

为什么要关注high version cursor(高版本游标)
可以共享但没有被共享的sql和合成版本的sql是造成library cache竞争的主要原因.竞争会降低数据库的性能.在极端情况下会使用数据库hang住.当一个游标有太多个不必要的版本时,每次游标被执行时,这个解析引擎为了找到你所想要的游标不得不搜索整个游标链表.这是非常消耗CPU资源的.

怎样查看high version cursor(高版本游标)以及为何不能被共享
一种最简单查看high version cursor(高版本游标)的方法是使用脚本High SQL Version Counts – Script to determine reason(s) (文档 ID 438755.1)
现在这个脚本的版本为 version_rpt3_23.sql
下载这个脚本后需要进行安装
SQ>conn / as sysdba
SQL>@F:\ version_rpt3_23.sql
使用方法如下:
对于10g及以后的版本来收集version超过100的所有游标

SQL> set pages 2000 lines 100
SQL> 
SQL> SELECT b.*
  2  FROM v$sqlarea a ,
  3    TABLE(version_rpt(a.sql_id)) b
  4  WHERE loaded_versions >=100;
 
COLUMN_VALUE
---------------------------------------------------------------------

根据hash value来收集version超过100的所有游标

SQL> set pages 2000 lines 100
SQL> SELECT b.*
  2  FROM v$sqlarea a ,
  3    TABLE(version_rpt(NULL,a.hash_value)) b
  4  WHERE loaded_versions>=100;
 
COLUMN_VALUE
---------------------------------------------------------------------

使用sql_id来收集游标报告

SQL> set pages 2000 lines 100
SQL> SELECT * FROM TABLE(version_rpt('g7vpupcuqd9zz'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 22-4月 -14 15:44
RDBMS Version :10.2.0.4.0 Host: IBMP740-1 Instance 1 : RLZY
==================================================================
Addr: 070000066F6659E8  Hash_Value: 895920127  SQL_ID g7vpupcuqd9zz
Sharable_Mem: 135775 bytes   Parses: 110924   Execs:621954
Stmt:
0 insert into mt_fee( hospital_id,serial_no,serial_fee,stat_type,f
1 ee_batch,medi_item_type,item_code,item_name,his_item_code,his_it
2 em_name,serial_apply,fee_date,model,factory,standard,unit,price,
3 dosage,money,reduce_money,usage_flag,usage_days,opp_serial_fee,i
4 nput_staff,input_man,input_date,calc_flag,frozen_flag,frozen_ser
5 ial_fee,trans_date,recipe_no,hos_serial,doctor_no,doctor_name,au
6 dit_flag,trans_flag,defray_type ) values ( :1,:2,:3,:4,:5,:6,:7,
7 :8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:2
8 4,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37)
9
Versions Summary
----------------
AUTH_CHECK_MISMATCH :1
BIND_MISMATCH :13
TRANSLATION_MISMATCH :1
ROLL_INVALID_MISMATCH :3
Total Versions:12
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = exact
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
              0 8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for AUTH_CHECK_MISMATCH :
  # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
========== =============== ================= ===================
         8              211               211 INSUR_CHANGDE
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
======== ======== =============== =============== ======== =============== =====
      13        1              32              32        1     No           (,)
      13        2              32              32        1     No           (,)
      13        3              32              32        1     No           (,)
      13        4              32              32        1     No           (,)
      13        5              32              32        1     No           (,)
      13        6              32              32        1     No           (,)
      13        7              32              32        1     No           (,)
      13        8              32             128        1     Yes          (,)
      13        9              32             128        1     Yes          (,)
      13       10              32             128        1     Yes          (,)
      13       11              32              32        1     No           (,)
      13       12              11              11      180     No           (,)
      13       13              32              32        1     No           (,)
      13       14              32             128        1     Yes          (,)
      13       15              32             128        1     Yes          (,)
      13       16              32             128        1     Yes          (,)
       9       17              32             128        1     Yes          (,)
       4       17              22              22        2     No           (,)
      13       18              32             128        1     Yes          (,)
      13       19              32              32        1     No           (,)
      13       20              32              32        1     No           (,)
      13       21              32              32        1     No           (,)
      13       22              32              32        1     No           (,)
      13       23              32              32        1     No           (,)
      13       24              32              32        1     No           (,)
      13       25              32              32        1     No           (,)
      13       26              11              11      180     No           (,)
      13       27              32              32        1     No           (,)
      13       28              32              32        1     No           (,)
      13       29              32              32        1     No           (,)
      13       30               7               7       12     No           (,)
      13       31              32              32        1     No           (,)
      13       32              32             128        1     Yes          (,)
      13       33              32              32        1     No           (,)
      13       34              32              32        1     No           (,)
      13       35              32              32        1     No           (,)
      13       36              32              32        1     No           (,)
      13       37              32              32        1     No           (,)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for TRANSLATION_MISMATCH :
No objects in the plans with same name and different owner were found.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for ROLL_INVALID_MISMATCH :
No details available
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 895920127, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

如果不能使用这个脚本可以使用下面的方法从基本视图中来查询相同的信息
下面使用scott用户来运行select count(*) from emp 语句,并运行下面的查询来查看这个语句的父游标和它的hash value和address

SQL>select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%'; 

SQL_TEXT                 HASH_VALUE    ADDRESS 
------------------------ ------------ ---------------- 
select count(*) from emp 4085390015   0000000386BC2E58

为了查看子游标:
对于oracle 9.2.x.x及以下版本
SQL>select * from v$sql_shared_cursor where kglhdpar = ‘0000000386BC2E58’;
对于oracle 10.0.x.x及以上版本
SQL> select * from v$sql_shared_cursor where address = ‘0000000386BC2E58’;
对于oracle 9.2.x.x及以下版本查询的输出如下:

ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F 
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 

可以看到有一个子游标(address: 0000000386BC2D080).mismatch信息都为N因为这是第一个子游标.如果另一个用户运行相同的语句(select count(*) from emp)再次执行上面查询输出如下:

ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F 
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N 

现在可以看到第二个子游标(address: 0000000386A91AA0)且为什么与第一个子游标不能共享(‘Y’表示不匹配).原因如下:
(1) AUTH_CHECK_MISMATCH and (2) TRANSLATION_MISMATCH
这是因为新用户下的emp对象与scott用户下的emp对象不匹配.当不能访问scott用户的对象时且因为在每个用户方案下有一个emp对象而object_id不同所以翻译失败发生了一次mismatch.

在v$SQL_SHARED_CURSOR中给出了不能共享游标的原因
下面介绍一些游标不能共享的原因:
.UNBOUND_CURSOR–现有的子游标没有完全创建(换句话说不能被优化)
.SQL_TYPE_MISMATCH—sql类型与现有的子游标不匹配
.OPTIMIZER_MISMATCH—优化器环境与现有的子游标不匹配
例如:
SQL>select count(*) from emp; ->> 1 PARENT, 1 CHILD
SQL>alter session set optimizer_mode=ALL_ROWS
SQL>select count(*) from emp; ->> 1 PARENT, 2 CHILDREN
因为 optimizer_mode被改变,因为现有的子游标不能被共享
如果使用10046跟踪事件将会得到optimizer_mismatch和第三个子游标
使用cursortrace将会看到更详细的原因比如:
Optimizer mismatch(12)
其中括号内的数字给出了原因

1 = Degree used is not the default DOP
2 = In (RAC) cases where instance count is not the same, or session CPU count is not the same, or thread count is not the same
3 =  _parallel_syspls_obey_force is FALSE
4 = The PQ mode does not match.
5 = The degree does not match.
6 = The parallel degree policy does not match.
7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.
8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit
9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit
10 = Optimizer mode difference
11 =  Materialized View mismatch
12 = Optimizer environment mismatch (ie an optimizer parameter is different)
13 = Cardinality Feedback is use

.OUTLINE_MISMATCH—The outlines do not match the existing child cursor 

If my user had created stored outlines previously for this command and they were stored in seperate categories (say "OUTLINES1" and "OUTLINES2") running:-
SQL>alter session set use_stored_outlines = OUTLINES1; 
SQL>select count(*) from emp; 
SQL>alter session set use_stored_oulines= OUTLINES2; 
SQL>select count(*) from emp;

.STATS_ROW_MISMATCH—The existing statistics do not match the existing child cursor. Check that 10046/sql_trace is not set on all sessions as this can cause this.
.LITERAL_MISMATCH—Non-data literal values do not match the existing child cursor
.SEC_DEPTH_MISMATCH—Security level does not match the existing child cursor
.EXPLAIN_PLAN_CURSOR—The child cursor is an explain plan cursor and should not be shared. Explain plan statements will generate a new child by default - the mismatch will be this
.BUFFERED_DML_MISMATCH—Buffered DML does not match the existing child cursor
.PDML_ENV_MISMATCH—PDML environment does not match the existing child cursor
.INST_DRTLD_MISMATCH—Insert direct load does not match the existing child cursor
.SLAVE_QC_MISMATCH—The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor).
.TYPECHECK_MISMATCH—The existing child cursor is not fully optimized
.AUTH_CHECK_MISMATCH— Authorization/translation check failed for the existing child cursor 
The user does not have permission to access the object in any previous version of the cursor. A typical example would be where each user has it's own copy of a table
.BIND_MISMATCH—The bind metadata does not match the existing child cursor. For example:
SQL>variable a varchar2(100); 
SQL>select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD 
SQL>variable a varchar2(400); 
SQL>select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN 

.DESCRIBE_MISMATCH—The typecheck heap is not present during the describe for the child cursor
.LANGUAGE_MISMATCH—The language handle does not match the existing child cursor
.TRANSLATION_MISMATCH—The base objects of the existing child cursor do not match. 
The definition of the object does not match any current version. Usually this is indicative of the same issue as "AUTH_CHECK_MISMATCH" where the object is different.
.ROW_LEVEL_SEC_MISMATCH—The row level security policies do not match
.INSUFF_PRIVS— Insufficient privileges on objects referenced by the existing child cursor
.INSUFF_PRIVS_REM-- Insufficient privileges on remote objects referenced by the existing child cursor
.REMOTE_TRANS_MISMATCH—The remote base objects of the existing child cursor do not match  
USER1: select count(*) from table@remote_db 
USER2: select count(*) from table@remote_db 

  (Although the SQL is identical, the dblink pointed to 
   by remote_db may be a private dblink which resolves 
   to a different object altogether)
.LOGMINER_SESSION_MISMATCH
.INCOMP_LTRL_MISMATCH
.OVERLAP_TIME_MISMATCH—error_on_overlap_time_msimatch
.SQL_REDIRECT_MISMATCH—sql redirection mismatch
.MV_QUERY_GEN_MISMATCH—materialized view query generation
.USER_BIND_PEEK_MISMATCH—user bind peek mismatch
.TYPCHK_DEP_MISMATCH—cursor has typecheck dependencies
.NO_TRIGGER_MISMATCH— no trigger mismatch
.FLASHBACK_CURSOR—No cursor sharing for flashback
.ANYDATA_TRANSFORMATION - anydata transformation change
.INCOMPLETE_CURSOR - incomplete cursor.
When bind length is upgradeable (i.e. we found a child cursor that matches everything
else except that the bind length is not long enough), we mark the old cursor is not usable
and build a new one.  This means the version can be ignored.
.TOP_LEVEL_RPI_CURSOR - top level/rpi cursor 
In a Parallel Query invocation this is expected behaviour (we purposely do not share)
.DIFFERENT_LONG_LENGTH - different long length
.LOGICAL_STANDBY_APPLY - logical standby apply mismatch
.DIFF_CALL_DURN - different call duration
.BIND_UACS_DIFF - bind uacs mismatch
.PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch
.CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch
.STB_OBJECT_MISMATCH - STB object different (now exists)
.ROW_SHIP_MISMATCH - row shipping capability mismatch
.PQ_SLAVE_MISMATCH - PQ slave mismatch 
Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting Bug:4367986
.TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor
.MULTI_PX_MISMATCH - multi-px and slave-compiled cursor
.BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor
.MV_REWRITE_MISMATCH - MV rewrite cursor
.ROLL_INVALID_MISMATCH - rolling invalidation window exceeded
.OPTIMIZER_MODE_MISMATCH - optimizer mode mismatch
.PX_MISMATCH - parallel query mismatch 
If running 11.1.0.6 and RAC see Bug:7352775. Check that if (on each instance) parallel_instance_groups is set then instance_groups is set to the same.
.MV_STALEOBJ_MISMATCH - mv stale object mismatch
.FLASHBACK_TABLE_MISMATCH - flashback table mismatch
.LITREP_COMP_MISMATCH - literal replacement compilation mismatch 
New in 11g :
PLSQL_DEBUG - debug mismatch Session has debugging parameter plsql_debug set to true
.LOAD_OPTIMIZER_STATS  - Load optimizer stats for cursor sharing
.ACL_MISMATCH   -  Check ACL mismatch
.FLASHBACK_ARCHIVE_MISMATCH  - Flashback archive mismatch
.LOCK_USER_SCHEMA_FAILED  - Failed to lock user and schema
.REMOTE_MAPPING_MISMATCH  - Remote mapping mismatch
.LOAD_RUNTIME_HEAP_FAILED  - Runtime heap mismatch
.HASH_MATCH_FAILED  - Hash mismatch
Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)
New in 11.2  :
PURGED_CURSOR - cursor marked for purging
         The cursor has been marked for purging with dbms_shared_pool.purge

.BIND_LENGTH_UPGRADEABLE - bind length upgradeable
          Could not be shared because a bind variable size was smaller than the new value beiing inserted    (marked as BIND_MISMATCH in earlier versions).

.USE_FEEDBACK_STATS - cardinality feedback
         Cardinality feedback is being used and therefore a new plan could be formed for the current execution.

.BIND_EQUIV_FAILURE - The bind value's selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan. For an example, see Document 836256.1. After each execution in the example, run:
select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';
         ... once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:

SQL_ID         ADDRESS          CHILD_ADDRESS    CHILD_NUMBER     B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0    N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1    Y

       As can be seen, the new version is created due to BIND_EQUIV_FAILURE

There is no longer  ROW_LEVEL_SEC_MISMATCH in 11.2.

可以进一步跟踪
在oracle10g及以上版本中可以使用cursortrace来查找游标不能被共享的原因.

SQL>alter system set events 
'immediate trace name cursortrace level 577, address hash_value';

其中可以使用三个level,level 1为577,level 2为578,level 3为580
当重用这个游标时将会向user_dump_dest目录中写一个跟踪文件.
关闭cursortrace:

SQL>alter system set events 
'immediate trace name cursortrace level 2147483648, address 1';

注意在10.2.0.4以下版本存在Bug 5555371导致cursortrace无法彻底关闭的情况.最终导致其trace文件不停的增长,从而可能导致oracle文件系统被撑爆的现象

在11.2中有了cursordump可以使用如下方式进行cursor dump:

SQL>alter system set events ‘immediate trace name cursordump level 16’;

这种方式收集的信息比较全:例如它可以采集部分别的方式无法看到的px_mismatch以及它会进一步展开optimizer_mismatch的信息等

尽管使用绑定变量还是会存在high version cursor
当cursor_sharing为similar时
select /* TEST */ * from emp where sal > 100;
select /* TEST */ * from emp where sal > 101;
select /* TEST */ * from emp where sal > 102;
select /* TEST */ * from emp where sal > 103;
select /* TEST */ * from emp where sal > 104;

SELECT sql_text,version_count,address
FROM V$SQLAREA
WHERE sql_text like ‘select /* TEST */%’;

SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = ‘&my_addr’;
将会出现多个子游标
Cursor_sharing设置为similar或force都可能导致high version count可以参考:
High Version Count with CURSOR_SHARING=SIMILAR or FORCE(文档ID 261020.1)

在11g中引入的adaptive cursor sharing特性很容易导致high version count的问题
可以参考:Bug 12334286 High version count with CURSOR_SHARING=FORCE(BIND_MISMATCH and INCOMP_LTRL_MISMATCH)
Document 740052.1 Adaptive Cursor Sharing Overview
Document 7213010.8 Bug 7213010 – Adaptive cursor sharing generates lots of child cursors

Document 8491399.8 Bug 8491399 – Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype

在oracle 11g中可以通过其它的一些手段限制child cursor的数量
Document 10187168.8 Enhancement to obsolete parent cursors if Version Count exceeds a threshold
引入了一个隐含参数_cursor_obsolete_threshold该参数用来限制单个parent cursor下的child cursor的数量,默认值为100.如果child cursor的数量超过这个阈值就会触发cursor obsolescence特性.这个时候parent cursor就会被丢弃并同时重新创建一个新的parent cursor.

1. If 11.2.0.3 and above, set the following parameters:
“_cursor_obsolete_threshold” to 100 (this is the number of child cursor after which we obsolete it)
2. If 11.2.0.2.2, then set:
SQL>alter system set “_cursor_features_enabled”=1026 scope=spfile;
SQL>alter system set event=’106001 trace name context forever,level 1024’ scope=spfile;

3.If 11.2.0.1:
SQL>alter system set “_cursor_features_enabled”=34 scope=spfile;
SQL>alter system set event=’106001 trace name context forever,level 1024’ scope=spfile;