《oracle10grac巡检脚本》由会员分享,可在线阅读,更多相关《oracle10grac巡检脚本(10页珍藏版)》请在金锄头文库上搜索。
1、=SRVCTL srvctl check= 1.列出配置的所有数据库数据库 srvctl config database 2.显示指定集群数据库的所有服务 srvctl config service -d GDTV 3.查看所有实例和服务的状态 srvctl status database -d GDTV4.查看单个实例的状态 srvctl status service -d GDTV -s 5.特定节点上节点应用程序的状态 srvctl status nodeapps -n DBSERVER1 srvctl status nodeapps -n DBSERVER2 6.列出 RAC 数据库的
2、配置 srvctl config database -d GDTV7.显示节点应用程序的配置 (VIP、GSD、ONS、监听器) srvctl config nodeapps -n DBSERVER1 -a -g -s -l srvctl config nodeapps -n DBSERVER2 -a -g -s -l =process check= 8. Oracle 进程检查 Ps ef |grep ora_ 9. CRS 进程检查 ps -ef | grep oracm $ps df |grep d.bin应有:crsd.bin ocssd.bin evmd.bin crsctl che
3、ck crs crs_stat t crs_stat ls=alert.log check= 8.查看各个 instance 的 alert.log =instance parameters check= 9.查看 spfile.ora 查看 profile=listener status check= 10. $hostname $export ORACLE_SID=GDTV1 $lsnrctl status $hostname $export ORACLE_SID=GDTV2 $lsnrctl statuslistener 日志检查 /u01/app/oracle/product/9.2.
4、0/network/log/listener.log /u01/app/oracle/product/9.2.0/network/log/listener.log =oracrs status check=11.1 crs 日志检查 ocssd.log $tail -20 cm.log =SQLcheck= $hostname $export ORACLE_SID=GDTV1 sqlplus “/as sysdba“ or sqlplus“/as sysdba“GDTV1 -run on dbserveras sysdba! -collect by lyf 200609 set pagesiz
5、e 1000 set linesize 120 set echo on COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE col tablespace_name format a15 host hostname1.集群中所有正在运行的实例 SELECT inst_id , instance_number inst_no , instance_name inst_name , parallel , status , da
6、tabase_status db_status , active_state state , host_name host FROM gv$instance ORDER BY inst_id; INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST - - - - - - - - 1 1 orcl1 YES OPEN ACTIVE NORMAL rac1 2 2 orcl2 YES OPEN ACTIVE NORMAL rac2SELECT inst_id, instance_name, host_name, VERSION, TO_
7、CHAR (startup_time, yyyy-mm-dd hh24:mi:ss) startup_time, status, archiver, database_status FROM gv$instance;2.检查参数 show paramter3.检查 SGA 和 PGA show sga select name ,value/1024/1024/1024 from gv$sysstat where name like %pga%; select name ,value/1024/1024/1024 from v$sysstat where name like %pga%; 4.检
8、查查询服务器服务器的运行模式和数据库安装安装选项set linesize 200select * from v$option; 5.用户检查 col temporary_tablespace for a21 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
9、_tablespaces b where a.temporary_tablespace=b.tablespace_name and b.contents TEMPORARY;6、控制文件检查 col name for a60 select * from v$controlfile; 7、无效对象检查col OBJECT_NAME for a24 SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like INVALID;8、表空间和数据文件检查 1)数据文件 c
10、ol file_name for a56 set linesize 300 select file_id,file_name,tablespace_name,autoextensible from dba_data_files; select count(*) from v$datafile; show parameter db_filesselect name from v$datafile union select member from v$logfile union select name from v$controlfile union select name from v$temp
11、file;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;2)表空间set linesize 300 col tablespace_name for a16 SELECT upper(f.tablespace_name) “tablespace_name“,d.Tot_grootte_Mb “tablespace(M)
12、“,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_
13、bytes,round(MAX(bytes)/(1024*1024),2) max_bytesFROM sys.dba_free_spaceGROUP BY tablespace_name) f,(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_MbFROM sys.dba_data_files ddGROUP BY dd.tablespace_name) d WHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC;表空间的空间使用
14、情况 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
15、.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 ; 表空间可用性检查 select