oracle表空间可用性检查,Oracle 10G RAC巡检脚本

================oracm状态检查===================================

11.1 crs日志检查

ocssd.log

$tail -20  cm.log

======================SQL下检查==============================

$hostname

$export ORACLE_SID=GDTV1

sqlplus "/as sysdba"

or sqlplus "/as sysdba"@GDTV1

--run on db server as sysdba!!!

--collect by lyf 200609

set pagesize 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 hostname

1.集群中所有正在运行的实例

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;

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 rac2

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;

2.检查参数

show paramter

3.检查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.检查查询服务器的运行模式和数据库安装选项

set linesize 200

select * 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_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)数据文件

col 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_files

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;

2)表空间

set linesize 300

col tablespace_name for a16

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 ；

表空间可用性检查

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;