RAC日常检查

出入境RAC日常检查

数据库名db_name：orcl

Db_unique_name：orcl

service_names：orcl

实例名 主机（1）IP：orcl1

实例名 主机（2）IP：orcl2

主机名 主机（1）IP：hporcl1

主机名 主机（2）IP：hporcl2

查看实例数据库状态：

$ ./srvctl status database -d orcl

Instance orcl1 is running on node hporcl1

Instance orcl2 is running on node hporcl2

查看数据库实例状态：

$ ./srvctl status instance -d orcl -i orcl1,orcl2

Instance orcl1 is running on node hporcl1

Instance orcl2 is running on node hporcl2

查看hporcl1（主机（1）IP）ASM实例状态

$ ./srvctl status asm -n hporcl1

ASM instance +ASM1 is running on node hporcl1.

查看hporcl2（主机（2）IP）ASM实例状态

$ ./srvctl status asm -n hporcl2

ASM instance +ASM2 is running on node hporcl2.

查看节点hporcl1（主机（1）IP）应用程序（VIP、GSD、态：

$ ./srvctl status nodeapps -n hporcl1

VIP is running on node: hporcl1

GSD is running on node: hporcl1

、ONS）的状Listener

Listener is running on node: hporcl1

ONS daemon is running on node: hporcl1

查看节点hporcl2（主机（2）IP）应用程序（VIP、GSD、Listener、ONS）的状态：

$ ./srvctl status nodeapps -n hporcl2

VIP is running on node: hporcl2

GSD is running on node: hporcl2

Listener is running on node: hporcl2

ONS daemon is running on node: hporcl2

用crsctl命令，检查crs相关服务的状态：

crsctl check crs

查看crs及所有的service的状态：

crs_stat –t

crs_stat -ls

列出配置的所有数据库：

srvctl config database

列出RAC数据库的配置：

srvctl config database -d orcl

显示节点（IP：主机（1）IP，主机名：hporcl1）应用程序的配置 —（VIP、GSD、ONS、）：

srvctl config nodeapps -n hporcl1 -a -g -s –l

显示节点（IP：主机（2）IP，主机名：hporcl2）应用程序的配置 —（VIP、GSD、ONS、）：

srvctl config nodeapps -n hporcl2 -a -g -s –l

ORACLE进程检查：

ps -ef|grep ora_

CRS进程检查：

ps -ef|grep oracm

查看监听程序状态：

lsnrctl status

listener日志检查（主机（1）IP）：

/oracle/app/product/10.2/db_1/network/log/ listener.log

/oracle/app/product/10.2/db_1/network/log/ listener_hporcl1.log

listener日志检查（主机（2）IP）：

/oracle/app/product/10.2/db_1/network/log/ listener.log

/oracle/app/product/10.2/db_1/network/log/ listener_hporcl2.log

检查SGA和PGA：

show sga

select name ,value from gv$sysstat where name like '%pga%';

select name ,value from v$sysstat where name like '%pga%';

检查参数：

show parameter

集群中所有正在运行的实例：

SELECT inst_id,

instance_number inst_no,

instance_name inst_name,

parallel,

status,

database_status db_status,

active_state state,

host_name host

FROM gv$instance

ORDER BY inst_id;

SELECT inst_id,

instance_name,

host_name,

VERSION,

TO_CHAR(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time,

status,

archiver,

database_status

FROM gv$instance;

检查查询服务器的运行模式和数据库安装选项：

select * from v$option;

检查用户：

select username,

account_status,

default_tablespace,

temporary_tablespace,

created

from dba_users;

select a.username,

a.temporary_tablespace \"Temporary Tablespace\

b.contents

from dba_users a, dba_tablespaces b

where a.temporary_tablespace = b.tablespace_name

and b.contents <> 'TEMPORARY';

控制文件检查：

select * from v$controlfile;

无效对象检查：

SELECT owner, object_name, object_type, status, LAST_DDL_TIME

FROM dba_objects

WHERE status like 'INVALID';

表空间和数据文件检查：

select file_id, file_name, tablespace_name, autoextensible

from dba_data_files;

select count(*) from v$datafile;

select name from v$datafile

union

select member from v$logfile

union

select name from v$controlfile

union

select name from v$tempfile;

SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT

file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;

检查表空间使用情况：

SELECT upper(f.tablespace_name) \"tablespace_name\

d.Tot_grootte_Mb \"tablespace(M)\

d.Tot_grootte_Mb - f.total_bytes \"used(M)\

round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100, \"use%\

f.total_bytes \"free_space(M)\

round(f.total_bytes / d.Tot_grootte_Mb * 100, 2) \"free%\

f.max_bytes \"max_block(M)\"

FROM (SELECT tablespace_name,

2)

round(SUM(bytes) / (1024 * 1024), 2) total_bytes,

round(MAX(bytes) / (1024 * 1024), 2) max_bytes

FROM sys.dba_free_space

GROUP BY tablespace_name) f,

(SELECT dd.tablespace_name,

round(SUM(dd.bytes) / (1024 * 1024), 2) Tot_grootte_Mb

FROM sys.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name

ORDER BY 4 DESC;

SELECT df.tablespace_name,

COUNT(*) datafile_count,

ROUND(SUM(df.BYTES) / 1048576) size_mb,

ROUND(SUM(free.BYTES) / 1048576, 2) free_mb,

ROUND(SUM(df.BYTES) / 1048576 - SUM(free.BYTES) / 1048576, 2) used_mb,

ROUND(MAX(free.maxbytes) / 1048576, 2) maxfree,

100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,

ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free

FROM dba_data_files df,

(SELECT tablespace_name,

file_id,

SUM(BYTES) BYTES,

MAX(BYTES) maxbytes

FROM dba_free_space

GROUP BY tablespace_name, file_id) free

WHERE df.tablespace_name = free.tablespace_name(+)

AND df.file_id = free.file_id(+)

GROUP BY df.tablespace_name

ORDER BY df.tablespace_name;

检查表空间可用性：

select tablespace_name,status from dba_tablespaces;

临时表空间使用情况和性能检查：

SELECT tablespace_name,

extent_management,

block_size,

initial_extent,

next_extent,

min_extents,

max_extents,

pct_increase

FROM dba_tablespaces

WHERE CONTENTS = 'TEMPORARY';

SELECT username, default_tablespace, dba_users;

select tablespace_name,

initial_extent,

next_extent,

max_extents,

pct_increase,

extent_management,

status

from dba_tablespaces

order by extent_management;

temporary_tablespace FROM

select tablespace_name, EXTENT_MANAGEMENT,

SEGMENT_SPACE_MANAGEMENT

from dba_tablespaces;

表和索引分析信息：

SELECT 'table', COUNT(*)

FROM dba_tables

WHERE last_analyzed IS NOT NULL

GROUP BY 'table'

UNION ALL

SELECT 'index', COUNT(*)

FROM dba_indexes

WHERE last_analyzed IS NOT NULL

GROUP BY 'index';

未建索引的表：

SELECT /*+ rule */

owner,

segment_name,

segment_type,

tablespace_name,

TRUNC(BYTES / 1024 / 1024, 1) size_mb

FROM dba_segments t

WHERE NOT EXISTS (SELECT 'x'

FROM dba_indexes i

WHERE t.owner = i.table_owner

AND t.segment_name = i.table_name)

AND t.segment_type IN ('TABLE', 'TABLE PARTITION')

AND t.owner NOT IN ('SYS', 'SYSTEM')

ORDER BY 5 DESC;

sort_segment检查：

select tablespace_name,

extent_size db_blocks_per_extent,

total_extents,

used_extents,

free_extents

from v$sort_segment;

数据库总大小：

select round(sum(space)) all_space_M

from (select sum(bytes) / 1024 / 1024 space

from dba_data_files

union all

select nvl(sum(bytes) / 1024 / 1024, 0) space

from dba_temp_files

union all

select sum(bytes) / 1024 / 1024 space from v$log);

检测连接数情况：

select SW.Sid,

S.Username,

SW.Event,

SW.Wait_Time,

SW.State,

SW.Seconds_In_Wait SEC_IN_WAIT

from v$session S, v$session_wait SW

where S.Username is not null

and SW.Sid = S.Sid

and SW.event not like '%SQL*Net%'

order by SW.Wait_Time Desc;

select count(*) from v$session;

select sid, serial#, username, program, machine, status from v$session;

回滚段信息：

select segment_name, owner, tablespace_name, dba_rollback_segs.status

from dba_rollback_segs, v$Datafile

where file_id = file#;

select segment_name,

initial_extent,

next_extent,

min_extents,

owner,

dba_rollback_segs.status status,

optsize

from dba_rollback_segs, v$rollstat

where dba_rollback_segs.segment_id = v$rollstat.usn;

select substr(V$rollname.NAME, 1, 20) \"Rollback_Name\

substr(V$rollstat.EXTENTS, 1, 6) \"EXTENT\

v$rollstat.RSSIZE,

v$rollstat.WRITES,

substr(v$rollstat.XACTS, 1, 6) \"XACTS\

v$rollstat.GETS,

substr(v$rollstat.WAITS, 1, 6) \"WAITS\

v$rollstat.HWMSIZE,

v$rollstat.SHRINKS,

substr(v$rollstat.WRAPS, 1, 6) \"WRAPS\

substr(v$rollstat.EXTENDS, 1, 6) \"EXTEND\

v$rollstat.AVESHRINK,

v$rollstat.AVEACTIVE

from v$rollname, v$rollstat

where v$rollname.USN = v$rollstat.USN

order by v$rollname.USN;

select r.name Rollback_Name,

p.pid Oracle_PID,

p.spid OS_PID,

nvl(p.username, 'NO TRANSACTION') Transaction,

p.terminal Terminal

from v$lock l, v$process p, v$rollname r

where l.addr = p.addr(+)

and trunc(l.id1(+) / 65536) = r.usn

and l.type(+) = 'TX'

and l.lmode(+) = 6

order by r.name;

回滚段的争用情况：

select name, waits, gets, waits / gets \"Ratio\"

from v$rollstat a, v$rollname b

where a.usn = b.usn;

rollback信息：

select substr(sys.dba_rollback_segs.SEGMENT_ID, 1, 5) \"ID#\

substr(sys.dba_segments.OWNER, 1, 8) \"Owner\

substr(sys.dba_segments.TABLESPACE_NAME, 1, 17) \"Tablespace Name\

substr(sys.dba_segments.SEGMENT_NAME, 1, 12) \"Rollback Name\

substr(sys.dba_rollback_segs.INITIAL_EXTENT, 1, 10) \"INI_Extent\

substr(sys.dba_rollback_segs.NEXT_EXTENT, 1, 10) \"Next Exts\

substr(sys.dba_segments.MIN_EXTENTS, 1, 5) \"MinEx\

substr(sys.dba_segments.MAX_EXTENTS, 1, 5) \"MaxEx\

substr(sys.dba_segments.PCT_INCREASE, 1, 5) \"%Incr\

substr(sys.dba_segments.BYTES, 1, 15) \"Size (Bytes)\

substr(sys.dba_segments.EXTENTS, 1, 6) \"Extent#\

substr(sys.dba_rollback_segs.STATUS, 1, 10) \"Status\"

from sys.dba_segments, sys.dba_rollback_segs

where sys.dba_segments.segment_name sys.dba_rollback_segs.segment_name

and sys.dba_segments.segment_type = 'ROLLBACK'

=

order by sys.dba_rollback_segs.segment_id;

Redo log信息检查：

Redo Log 文件状态：

select f.member \"member\

f.group# \"group\

l.bytes / 1024 / 1024 \"size\

l.status

from v$logfile f, v$log l

where f.group# = l.group#

order by f.group#, f.member;

LogGroup信息：

SELECT group#, sequence#, bytes, members, status from v$log;

关于log_buffer：

select name, value

from v$sysstat

where name in ('redo entries', 'redo buffer allocation

retries');

IO情况检查：

select df.name file_name,

fs.phyrds reads,

fs.phywrts writes,

(fs.readtim / decode(fs.phyrds, 0, -1, fs.phyrds)) readtime,

(fs.writetim / decode(fs.phywrts, 0, -1, fs.phywrts)) writetime

from v$datafile df, v$filestat fs

where df.file# = fs.file#

order by df.name;

Shared Pool Size 命中率：

select round((sum(gets) - sum(reloads)) / sum(gets) * 100, 1) \"libiary cache hit ratio %\"

from v$librarycache

where namespace in ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

数据字典命中率：

select round((1 - sum(getmisses) / sum(gets)) * 100, 1) \"data dictionary hit ratio %\"

from v$rowcache;

锁竞争：

select substr(ln.name, 1, 25) Name,

l.gets,

l.misses,

100 * (l.misses / l.gets) \"% Ratio (STAY UNDER 1%)\"

from v$latch l, v$latchname ln

where ln.name in ('cache buffers lru chain')

and ln.latch# = l.latch#;

排序命中率：

select a.value \"Sort(Disk)\value \"Sort(Memory)\

round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) \"% Ratio (STAY UNDER

5%)\"

from v$sysstat a, v$sysstat b

where a.name = 'sorts (disk)'

and b.name = 'sorts (memory)';

数据缓冲区命中率：

select round((1 - (phy.value / (cur.value + con.value))) * 100, 1) || '%' ratio

from v$sysstat phy, v$sysstat cur, v$sysstat con

where phy.name = 'physical reads'

and cur.name = 'db block gets'

and con.name = 'consistent gets';

Miss LRU Hit命中率：

select name, (sleeps / gets) \"Miss LRU Hit%\"

from v$latch

where name = 'cache buffers lru chain';

检查内存排序性能：

select a.name, to_char(value)

from v$statname a, v$sysstat

where a.statistic# = v$sysstat.statistic#

and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');

redo log buffer retry ratio：

select to_char(r.value / e.value) \"redo log buffer retry ratio\"

from v$sysstat r, v$sysstat e

where r.name = 'redo buffer allocation retries'

and e.name = 'redo entries';

wait等待检查：

select count(*) total_in_wait from v$session_wait

where event='log buffer space';

select event, total_waits, time_waited, average_wait

from v$system_event

where event like '%undo%';

select sid, seq#, event, WAIT_TIME, SECONDS_IN_WAIT

from v$session_wait

where event not like 'SQL%'

and event not like 'rdbms%';

查询lock锁：

SELECT lpad(' ', DECODE(request, 0, 0, 1)) || sid sess,

id1,

id2,

lmode,

request,

type

FROM V$LOCK

WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)

ORDER BY id1, request;