RAC日常检查.docx
- 文档编号:17184913
- 上传时间:2023-04-24
- 格式:DOCX
- 页数:15
- 大小:20.09KB
RAC日常检查.docx
《RAC日常检查.docx》由会员分享,可在线阅读,更多相关《RAC日常检查.docx(15页珍藏版)》请在冰豆网上搜索。
RAC日常检查
出入境RAC日常检查
数据库名db_name:
orclDb_unique_name:
orcl
service_names:
orcl
实例名
主机
(1)
IP:
orcl1
实例名
主机
(2)
IP:
orcl2
主机名
主机
(1)
IP:
hporcl1
主机名
主机
(2)
IP:
hporcl2
查看实例数据库状态:
$./srvctlstatusdatabase-dorcl
Instanceorcl1isrunningonnodehporcl1
Instanceorcl2isrunningonnodehporcl2
查看数据库实例状态:
$./srvctlstatusinstance-dorcl-iorcl1,orcl2
Instanceorcl1isrunningonnodehporcl1
Instanceorcl2isrunningonnodehporcl2
查看hporcl1(主机
(1)IP)ASM实例状态
$./srvctlstatusasm-nhporcl1
ASMinstance+ASM1isrunningonnodehporcl1.
查看hporcl2(主机
(2)IP)ASM实例状态
$./srvctlstatusasm-nhporcl2
ASMinstance+ASM2isrunningonnodehporcl2.
Listener、ONS)的
、ONS)的状态:
查看节点hporcll(主机
(1)IP)应用程序(VIP、GSD、
状态:
$./srvctlstatusnodeapps-nhporcl1
VIPisrunningonnode:
hporcl1
GSDisrunningonnode:
hporcl1
Listenerisrunningonnode:
hporcl1
ONSdaemonisrunningonnode:
hporcl1
查看节点hporcl2(主机
(2)IP)应用程序(VIP、GSD、Listener
$./srvctlstatusnodeapps-nhporcl2
VIPisrunningonnode:
hporcl2
GSDisrunningonnode:
hporcl2
Listenerisrunningonnode:
hporcl2
ONSdaemonisrunningonnode:
hporcl2
用crsctl命令,检查crs相关服务的状态:
crsctlcheckcrs
查看crs及所有的service的状态:
crs_stat-1
crs_stat-ls
列出配置的所有数据库:
srvctlconfigdatabase
列出RAC数据库的配置:
srvctlconfigdatabase-dorcl
显示节点(IP:
主机
(1)IP,主机名:
hporcl1)应用程序的配置—(VIP、GSD、
听器):
srvctlconfignodeapps-nhporcl1-a-g-s-l
显示节点(IP:
主机
(2)IP,主机名:
hporcl2)应用程序的配置—(VIP、GSD、
听器):
srvctlconfignodeapps-nhporcl2-a-g-s-l
ORACL进程检查:
ps-ef|grepora_
CRS进程检查:
ps-ef|greporacm
查看监听程序状态:
lsnrctlstatus
ONS监
ONS监
listener日志检查(主机
(1)IP)
/oracle/app/product/10.2/db_1/network/log//oracle/app/product/10.2/db_1/network/log/listener日志检查(主机
(2)IP):
/oracle/app/product/10.2/db_1/network/log/
/oracle/app/product/10.2/db_1/network/log/
listener.log
listener_hporcl1.log
listener.log
listener_hporcl2.log
检查SGA和PGA
showsga
selectname,valuefromgv$sysstatwherenamelike'%pga%';selectname,valuefromv$sysstatwherenamelike'%pga%';
检查参数:
showparameter
集群中所有正在运行的实例:
SELECTinst_id,
)startup_time,
TIME
instance_numberinst_no,instance_nameinst_name,parallel,status,database_statusdb_status,active_statestate,host_namehostFROMgv$instanceORDERBYinst_id;
SELECTinst_id,instance_name,host_name,VERSION,
TO_CHAR(startup_time,'yyyy-mm-ddhh24:
mi:
ss'status,archiver,database_status
FROMgv$instance;
检查查询服务器的运行模式和数据库安装选项:
select*fromv$option;
检查用户:
selectusername,account_status,default_tablespace,temporary_tablespace,createdfromdba_users;
selecta.username,
a.temporary_tablespace"TemporaryTablespace",
b.contents
fromdba_usersa,dba_tablespacesb
wherea.temporary_tablespace=b.tablespace_name
andb.contents<>'TEMPORARY;'
控制文件检查:
select*fromv$controlfile;
无效对象检查:
SELECTowner,object_name,object_type,status,LAST_DDL
FROMdba_objects
WHERsEtatuslike'INVALID'
表空间和数据文件检查:
selectfile_id,file_name,tablespace_name,autoextensiblefromdba_data_files;
select
count(*)fromv$datafile;
select
namefromv$datafile
union
select
memberfromv$logfile
union
select
namefromv$controlfile
union
select
namefromv$tempfile;
SELECTfile#,ts#,NAME,status,BYTES/1024/1024size_mbFROMv$datafileUNIONALLSELECT
file#,ts#,NAME,status,BYTES/1024/1024size_mbFROMv$tempfile;
检查表空间使用情况:
SELECTupper(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(SELECTtablespace_name,
round(SUM(bytes)/(1024*1024),2)total_bytes,
round(MAX(bytes)/(1024*1024),2)max_bytes
FROMsys.dba_free_space
GROUPBYtablespace_name)f,
(SELECTdd.tablespace_name,
round(SUM(dd.bytes)/(1024*1024),2)Tot_grootte_MbFROMsys.dba_data_filesdd
GROUPBYdd.tablespace_name)d
WHERdE.tablespace_name=f.tablespace_name
ORDERBY4DESC;
SELECTdf.tablespace_name,
COUN(T*)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_freeFROMdba_data_filesdf,
(SELECTtablespace_name,
file_id,
SUM(BYTES)BYTES,
MAX(BYTES)maxbytes
FROMdba_free_space
GROUPBYtablespace_name,file_id)free
WHERdEf.tablespace_name=free.tablespace_name(+)
ANDdf.file_id=free.file_id(+)
GROUPBYdf.tablespace_name
ORDERBYdf.tablespace_name;
检查表空间可用性:
selecttablespace_name,statusfromdba_tablespaces;
临时表空间使用情况和性能检查:
SELECTtablespace_name,
extent_management,
block_size,
initial_extent,
next_extent,min_extents,max_extents,pct_increase
FROMdba_tablespaces
WHERECONTENT=S'TEMPORARY;'
SELECTusername,default_tablespace,temporary_tablespaceFROMdba_users;
selecttablespace_name,
initial_extent,
next_extent,
max_extents,
pct_increase,
extent_management,
status
fromdba_tablespaces
orderbyextent_management;
selecttablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENTfromdba_tablespaces;
表和索引分析信息:
SELECT'table',COUN(T*)
FROMdba_tables
WHERlEast_analyzedISNOTNULLGROUPBY'table'
UNIONALL
SELECT'index',COUN(T*)
FROMdba_indexes
WHERlEast_analyzedISNOTNULLGROUPBY'index';
未建索引的表:
SELECT/*+rule*/owner,segment_name,segment_type,tablespace_name,
TRUNC(BYTES/1024/1024,1)size_mbFROMdba_segmentst
WHERENOTEXISTS(SELECT'x'
FROMdba_indexesi
WHEREt.owner=i.table_owner
ANDt.segment_name=i.table_name)
ANDt.segment_typeIN('TABLE','TABLEPARTITION')ANDt.ownerNOTIN('SYS','SYSTEM')
ORDERBY5DESC;
sort_segment检查:
selecttablespace_name,
extent_sizedb_blocks_per_extent,total_extents,
used_extents,
free_extents
fromv$sort_segment;
数据库总大小:
selectround(sum(space))all_space_M
from(selectsum(bytes)/1024/1024space
fromdba_data_files
unionall
selectnvl(sum(bytes)/1024/1024,0)spacefromdba_temp_files
unionall
selectsum(bytes)/1024/1024spacefromv$log);
检测连接数情况:
selectSW.Sid,
S.Username,
SW.Event,
SW.Wait_Time,
SW.State,
SW.Seconds_In_WaitSEC_IN_WAIT
fromv$sessionS,v$session_waitSW
whereS.Usernameisnotnull
andSW.Sid=S.Sid
andSW.eventnotlike'%SQL*Net%'
orderbySW.Wait_TimeDesc;
selectcount(*)fromv$session;
selectsid,serial#,username,program,machine,statusfromv$session;
回滚段信息:
selectsegment_name,owner,tablespace_name,dba_rollback_segs.status
fromdba_rollback_segs,v$Datafile
wherefile_id=file#;
selectsegment_name,
initial_extent,
next_extent,
min_extents,
owner,
dba_rollback_segs.statusstatus,
optsize
fromdba_rollback_segs,v$rollstat
wheredba_rollback_segs.segment_id=v$rollstat.usn;
selectsubstr(V$rollname.substr(V$rollstat.v$rollstat.RSSIZE,v$rollstat.WRITES,substr(v$rollstat.XACTS,v$rollstat.GETS,substr(v$rollstat.WAITS,v$rollstat.HWMSIZE,v$rollstat.SHRINKS,substr(v$rollstat.WRAPS,
NAM,E1,20)"Rollback_Name
EXTENT,S1,6)"EXTENT",
1,6)"XACTS",
1,6)"WAITS",
1,6)"WRAPS",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
fromv$rollname,v$rollstat
wherev$rollname.USN=v$rollstat.USN
orderbyv$rollname.USN;
selectr.nameRollback_Name,
p.pidOracle_PID,
p.spidOS_PID,nvl(p.username,'NOTRANSACTION)'Transactionp.terminalTerminal
fromv$lockl,v$processp,v$rollnamerwherel.addr=p.addr(+)
andtrunc(l.id1(+)/65536)=r.usn
andl.type(+)='TX'
andl.lmode(+)=6orderbyr.name;
回滚段的争用情况:
selectname,waits,gets,waits/gets"Ratio"
fromv$rollstata,v$rollnamebwherea.usn=b.usn;
rollback信息:
selectsubstr(substr(substr(substr(substr(substr(
sys.dba_rollback_segs.SEGMENT_ID,1,5)"ID#",
sys.dba_segments.OWNER,1,8)"Owner",
sys.dba_segments.TABLESPACE_NAME,1,17)"TablespaceName",
sys.dba_segments.SEGMENT_NAME,1,12)"RollbackName",
sys.dba_rollback_segs.INITIAL_EXTENT,1,10)"INI_Extent",sys.dba_rollback_segs.NEXT_EXTENT,1,10)"NextExts",
substr(substr(substr(
sys.dba_segments.MIN_EXTENTS,1,5)"MinEx",
sys.dba_segments.MAX_EXTENTS,1,5)"MaxEx",
sys.dba_segments.PCT_INCREASE,1,5)"%Incr",
substr(substr(substr(
sys.dba_segments.BYTES,1,15)"Size(Bytes)",sys.dba_segments.EXTENT,S1,6)"Extent#",sys.dba_rollback_segs.STATUS,1,10)"Status"
fromsys.dba_segments,sys.dba_rollback_segs
wheresys.dba_segments.segment_name=sys.dba_rollback_segs.segment_nameandsys.dba_segments.segment_type='ROLLBACK'
orderbysys.dba_rollback_segs.segment_id;
Redolog信息检查:
RedoLog文件状态:
selectf.member"member",
f.
group#"group",
l.bytes/1024/1024"size",
l.status
fromv$logfilef,v$logl
wheref.group#=l.group#orderbyf.group#,f.member;
LogGroup信息:
SELECTgroup#,sequence#,bytes,members,statusfromv$log;
关于log_buffer:
selectname,value
fromv$sysstat
wherenamein('redoentries','redobufferallocationretries');
IO情况检查:
selectdf.namefile_name,
fs.phyrdsreads,
fs.phywrtswrites,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))readtime,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))writetime
fromv$datafiledf,v$filestatfs
wheredf.file#=fs.file#
orderbydf.name;
SharedPoolSize命中率:
selectround((sum(gets)-sum(reloads))/sum(gets)*100,1)"libiarycachehitratio%"fromv$librarycache
wherenamespacein('SQLAREA','TABLE/PROCEDURE,''BODY','TRIGGER');
数据字典命中率:
selectround((1-sum(getmisses)/sum(gets))*100,1)"datadict
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- RAC 日常 检查