RAC日常检查.docx

RAC日常检查出入境RAC日常检查 数据库名db_name：orcl Db_unique_name：orcl service_names：orcl 实例名 主机IP：orcl1 实例名 主机IP：orcl2 主机名 主机IP：hporcl1 主机名 主机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 查看hporcl1IP）ASM实例状态 $ ./srvctl status asm -n hporcl1 ASM instance +ASM1 is running on node hporcl1. 查看hporcl2IP）ASM实例状态 $ ./srvctl status asm -n hporcl2 ASM instance +ASM2 is running on node hporcl2. 查看节点hporcl1IP）应用程序的状态： $ ./srvctl status nodeapps -n hporcl1 VIP is running on node: hporcl1 GSD is running on node: hporcl1 Listener is running on node: hporcl1 ONS daemon is running on node: hporcl1 查看节点hporcl2IP）应用程序的状态： $ ./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，主机名：hporcl1）应用程序的配置 ： srvctl config nodeapps -n hporcl1 -a -g -s l 显示节点IP，主机名：hporcl2）应用程序的配置 ： srvctl config nodeapps -n hporcl2 -a -g -s l ORACLE进程检查： ps -ef|grep ora_ CRS进程检查： ps -ef|grep oracm 查看监听程序状态： lsnrctl status listener日志检查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日志检查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, 2) "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, 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, temporary_tablespace FROM dba_users; select tablespace_name, initial_extent, next_extent, max_extents, pct_increase, extent_management, status from dba_tablespaces order by extent_management; 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)", b.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;