RAC日常检查.docx

《RAC日常检查.docx》由会员分享，可在线阅读，更多相关《RAC日常检查.docx（14页珍藏版）》请在三一办公上搜索。

1、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 ins

2、tance -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 n

3、ode 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: h

4、porcl2 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）应

5、用程序的配置 ： 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/produc

6、t/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

7、 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

8、_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 T

9、ablespace, 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; 表空间和数据文件检查： sele

10、ct 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 siz

11、e_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_M

12、b * 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_n

13、ame) 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

14、, 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_

15、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; 检查表空间可用性： se

16、lect 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

17、 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_t

18、ables 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 EX

19、ISTS (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, f

20、ree_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

21、, 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, machi

22、ne, 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$r

23、ollstat 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.HWM

24、SIZE, 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,

25、 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$r

26、ollname 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.

27、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), su

28、bstr(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_i

29、d; 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

30、 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

31、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) da

32、ta 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(M

33、emory), 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$syssta

34、t 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

35、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等待检查： s

36、elect 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;