使用HHDBCS对ORACLE 12CR2 RAC校验及巡检

1 SCAN IP连接测试

Oracle RAC集群搭建完成后需要对连接进行校验，判断数据库集群是否可以对外提供服务。本次校验客户端使用HHDBCS数据库管理工具进行验证。

HHDBCS数据库管理工具是一个跨平台的数据库管理工具，可以在WINDOWS、LINUX、MAC等操作系统运行，比如linux可以执行start_csadmin.sh文件启动。

填入集群SCAN IP对应的连接信息、账号和密码点击【测试连接】按钮测试是否连接成功。

2 RAC巡检

点击查询窗口，将巡检sql输入到查询窗口中，点击执行或者按快捷键F8执行。对于巡检的结果可以进行导出操作，可以导出成csv、excel等多种格式的文件。

下面是rac巡检工作中一些常用的sql：

locked_objects_rac.sql：Lists all locked objects for whole RAC.

SELECT b.inst_id,
       b.session_id AS sid,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name
FROM   dba_objects a,
       gv$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

longops_rac.sql：Displays information on all long operations for whole RAC.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.module,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   gv$session s,
       gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.inst_id = sl.inst_id
AND    s.serial# = sl.serial#;

monitor_memory_rac.sql：Displays memory allocations for the current database sessions for the whole RAC.

SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;

session_undo_rac.sql: Displays undo information on relevant database sessions.

SELECT s.inst_id,
       s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   gv$transaction t,
       gv$session s,
       gv$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    s.inst_id = t.inst_id
AND    t.xidusn = r.usn
AND    t.inst_id = r.inst_id
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

session_waits_rac.sql：Displays information on all database session waits for the whole RAC.

SELECT s.inst_id,
       NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   gv$session_wait sw,
       gv$session s
WHERE  s.sid     = sw.sid
AND    s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC;

sessions_rac.sql：Displays information on all database sessions for whole RAC.

SELECT NVL(s.username, '(oracle)') AS username,
       s.inst_id,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   gv$session s,
       gv$process p
WHERE  s.paddr   = p.addr
AND    s.inst_id = p.inst_id
ORDER BY s.username, s.osuser

 

小编把恒辉数据库CS端管理工具和产品社区的链接附在了下面，感兴趣的朋友可以进去了解一下！

[恒辉产品社区]

[恒辉数据库CS端管理工具下载地址]