RAC日常检查

出入境RAC日常检查数据库名db_name：orclDb_unique_name：orclservice_names：orcl实例名主机（1）IP：orcl1实例名主机（2）IP：orcl2主机名主机（1）IP：hporcl1主机名主机（2）IP：hporcl2查看实例数据库状态：$./srvctlstatusdatabase-dorclInstanceorcl1isrunningonnodehporcl1Instanceorcl2isrunningonnodehporcl2查看数据库实例状态：$./srvctlstatusinstance-dorcl-iorcl1,orcl2Instanceorcl1isrunningonnodehporcl1Instanceorcl2isrunningonnodehporcl2查看hporcl1（主机（1）IP）ASM实例状态$./srvctlstatusasm-nhporcl1ASMinstance+ASM1isrunningonnodehporcl1.查看hporcl2（主机（2）IP）ASM实例状态$./srvctlstatusasm-nhporcl2ASMinstance+ASM2isrunningonnodehporcl2.查看节点hporcl1（主机（1）IP）应用程序（VIP、GSD、Listener、ONS）的状态：$./srvctlstatusnodeapps-nhporcl1VIPisrunningonnode:hporcl1GSDisrunningonnode:hporcl1Listenerisrunningonnode:hporcl1ONSdaemonisrunningonnode:hporcl1查看节点hporcl2（主机（2）IP）应用程序（VIP、GSD、Listener、ONS）的状态：$./srvctlstatusnodeapps-nhporcl2VIPisrunningonnode:hporcl2GSDisrunningonnode:hporcl2Listenerisrunningonnode:hporcl2ONSdaemonisrunningonnode:hporcl2用crsctl命令，检查crs相关服务的状态：crsctlcheckcrs查看crs及所有的service的状态：crs_stat–tcrs_stat-ls列出配置的所有数据库：srvctlconfigdatabase列出RAC数据库的配置：srvctlconfigdatabase-dorcl显示节点（IP：主机（1）IP，主机名：hporcl1）应用程序的配置—（VIP、GSD、ONS、监听器）：srvctlconfignodeapps-nhporcl1-a-g-s–l显示节点（IP：主机（2）IP，主机名：hporcl2）应用程序的配置—（VIP、GSD、ONS、监听器）：srvctlconfignodeapps-nhporcl2-a-g-s–lORACLE进程检查：ps-ef|grepora_CRS进程检查：ps-ef|greporacm查看监听程序状态：lsnrctlstatuslistener日志检查（主机（1）IP）：/oracle/app/product/10.2/db_1/network/log/listener.log/oracle/app/product/10.2/db_1/network/log/listener_hporcl1.loglistener日志检查（主机（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：showsgaselectname,valuefromgv$sysstatwherenamelike'%pga%';selectname,valuefromv$sysstatwherenamelike'%pga%';检查参数：showparameter集群中所有正在运行的实例：SELECTinst_id,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')startup_time,status,archiver,database_statusFROMgv$instance;检查查询服务器的运行模式和数据库安装选项：select*fromv$option;检查用户：selectusername,account_status,default_tablespace,temporary_tablespace,createdfromdba_users;selecta.username,a.temporary_tablespaceTemporaryTablespace,b.contentsfromdba_usersa,dba_tablespacesbwherea.temporary_tablespace=b.tablespace_nameandb.contents'TEMPORARY';控制文件检查：select*fromv$controlfile;无效对象检查：SELECTowner,object_name,object_type,status,LAST_DDL_TIMEFROMdba_objectsWHEREstatuslike'INVALID';表空间和数据文件检查：selectfile_id,file_name,tablespace_name,autoextensiblefromdba_data_files;selectcount(*)fromv$datafile;selectnamefromv$datafileunionselectmemberfromv$logfileunionselectnamefromv$controlfileunionselectnamefromv$tempfile;SELECTfile#,ts#,NAME,status,BYTES/1024/1024size_mbFROMv$datafileUNIONALLSELECTfile#,ts#,NAME,status,BYTES/1024/1024size_mbFROMv$tempfile;检查表空间使用情况：SELECTupper(f.tablespace_name)tablespace_name,d.Tot_grootte_Mbtablespace(M),d.Tot_grootte_Mb-f.total_bytesused(M),round((d.Tot_grootte_Mb-f.total_bytes)/d.Tot_grootte_Mb*100,2)use%,f.total_bytesfree_space(M),round(f.total_bytes/d.Tot_grootte_Mb*100,2)free%,f.max_bytesmax_block(M)FROM(SELECTtablespace_name,round(SUM(bytes)/(1024*1024),2)total_bytes,round(MAX(bytes)/(1024*1024),2)max_bytesFROMsys.dba_free_spaceGROUPBYtablespace_name)f,(SELECTdd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2)Tot_grootte_MbFROMsys.dba_data_filesddGROUPBYdd.tablespace_name)dWHEREd.tablespace_name=f.tablespace_nameORDERBY4DESC;SELECTdf.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_freeFROMdba_data_filesdf,(SELECTtablespace_name,file_id,SUM(BYTES)BYTES,MAX(BYTES)maxbytesFROMdba_free_spaceGROUPBYtablespace_name,file_id)freeWHEREdf.tablespace_name=free.tablespace_name(+)ANDdf.file_id=free.file_id(+)GROUPBYdf.tablespace_nameORDERBYdf.tablespace_name;检查表空间可用性：selecttablespace_name,statusfromdba_tablespaces;临时表空间使用情况和性能检查：SELECTtablespace_name,extent_management,block_size,initial_extent,next_extent,min_extents,max_extents,pct_increaseFROMdba_tablespacesWHERECONTENTS='TEMPORARY';SELECTusername,default_tablespace,temporary_tablespaceFROMdba_users;selecttablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,statusfromdba_tablespacesorderbyextent_management;selecttablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENTfromdba_tablespaces;表和索引分析信息：SELECT'table',COUNT(*)FROMdba_tablesWHERElast_analyzedISNOTNULLGROUPBY'table'UNIONALLSELECT'index',COUNT(*)FROMdba_indexesWHERElast_analyzedISNOTNULLGROUPBY'index';未建索引的表：SELECT/*+rule*/owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES/1024/1024,1)size_mbFROMdba_segmentstWHERENOTEXISTS(SELECT'x'FROMdba_indexesiWHEREt.owner=i.table_ownerANDt.