oracle健康检查巡检

spool healthcheck_%U.log
set echo off
whenever sqlerror continue;
set lines 1000
set pages 1000
prompt
prompt
prompt ################################################################
prompt Oracle ASM:
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;
prompt ################################################################
prompt
prompt ################################################################
prompt Instance Name:
select instance_name from v$instance;
prompt ################################################################
prompt Server Name:
show parameter db_name;
show parameter service_names;
prompt
prompt ################################################################
prompt Number of Datafiles:
select count(*) from dba_data_files;
prompt
prompt ################################################################
prompt Number of Tablespaces:
select count(*) from v$tablespace;
prompt
prompt ################################################################
prompt Disk Space (of all dbfiles):
select sum(bytes)/1024/1024/1024 as "DATAFILE(GB)" from dba_data_files;
prompt
prompt ################################################################
prompt Number of control files:
show parameter control_files;
prompt
prompt ################################################################
prompt Redo Log Size:
select group#,bytes/1024/1024,members from v$log;
prompt
prompt ################################################################
prompt Archiving Enabled:
Archive log list;
prompt
prompt ###############################################################
prompt ORACLE VERSION:
select * from v$version;
prompt
prompt ################################################################
prompt SGA Size,Shared Pool Size,DB_BLOCK_BUFFERS:
show sga
prompt
show parameter shared_pool_size;
show parameter db_cache_size;
prompt
prompt ################################################################
prompt INSTALL OPTION:
Col parameter for a50
Col value for a10
Select * from v$option where value='TRUE';
prompt
prompt ################################################################
prompt ORACLE parameter:
show parameter;
prompt
prompt ################################################################
prompt #resource limited:#
select resource_name,current_utilization,max_utilization,initial_allocation,limit_value
from v$resource_limit;
prompt
prompt ################################################################
prompt EACH POOL OF SGA:
SELECT pool, round( pool_bytes / 1048576 ),round( 100 * pool_bytes / total_sga, 2 ) percent
FROM
( SELECT sum( bytes ) total_sga FROM v$sgastat ),
( SELECT nvl( pool, name ) pool, SUM( bytes ) pool_bytes
FROM v$sgastat
GROUP BY nvl( pool, name ) )
ORDER BY 3 DESC;
prompt
prompt ################################################################
prompt SGA used:
select pool,name,bytes from v$sgastat;
prompt
prompt ################################################################
prompt name of controlfile:
select name,status from v$controlfile;
prompt
prompt ################################################################
prompt Number of redo log members per group:
col member format a50
select b.group#,a.members,a.bytes/1024/1024 "LOG SIZE MB",b.status,b.member from v$log a,v$logfile b where a.group#=b.group#
;
prompt
prompt ################################################################
prompt #The information of temporary#
col "BYTES_M" for a15
col "USED_%" for a15
col "NAME" for a15
col "USED_M" for a15
SELECT d.status "STATUS",d.tablespace_name "NAME",d.contents "LEIXING",d.extent_management "MANAGEMENT",
TO_CHAR(NVL(a.bytes/1024/1024,0),'99,999,990.900') "BYTES_M",
NVL(t.bytes,0)/1024/1024 || '/' || NVL(a.bytes/1024/1024,0) "USED_M",
TO_CHAR(NVL(t.bytes/a.bytes*100,0),'990.00') "USED_%"
FROM sys.dba_tablespaces d,
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name=a.tablespace_name(+)
AND d.tablespace_name=t.tablespace_name(+)
AND d.contents like 'TEMPORARY';
prompt
prompt ################################################################
prompt USER'S temporary_tablespace is SYSTEM:
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE temporary_tablespace = 'SYSTEM';
prompt
prompt ################################################################
prompt user's Tablespaces is SYSTEM:
set line 500
SELECT username, default_tablespace,ACCOUNT_STATUS
FROM dba_users
WHERE default_tablespace = 'SYSTEM'
AND username not in ( 'SYS', 'SYSTEM' );
prompt
prompt ################################################################
prompt MANAGE MODE OF TABLESPACE:
select STATUS,tablespace_name,extent_management,segment_space_management,CONTENTS
from dba_tablespaces;
prompt
prompt ################################################################
prompt TABLESPACE USAGE:
set linesize 1000
col f.tablespace_name format a15
col d.tot_grootte_mb format a10
col ts-per format a15
select upper(f.tablespace_name) "ts-name",
d.tot_grootte_mb "ts-bytes(m)",
d.tot_grootte_mb - f.total_bytes "ts-used (m)",
f.total_bytes "ts-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "ts-per"
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 5 desc;
prompt
prompt #datafiles AUTOEXTENSIBLE:#
select file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files where AUTOEXTENSIBLE='YES';
prompt
prompt ################################################################
prompt table and index is same tablespace:
SELECT DISTINCT (a.tablespace_name)
FROM dba_tables a, dba_indexes b
WHERE a.owner = b.table_owner
AND a.table_name = b.table_name
AND a.tablespace_name = b.tablespace_name
AND a.tablespace_name not in ( 'SYSTEM', 'SYSAUX' );
prompt
prompt################################################################
prompt Rollback Segment Extent Size:
select r.usn,
d.segment_name,
r.status,
initial_extent,
next_extent,
r.rssize,
r.extents
from v$rollstat r,
dba_rollback_segs d,
v$rollname n
where n.name = d.segment_name and n.usn = r.usn;
prompt
prompt ################################################################
prompt UNDO TABLESPACE USAGE:
select max(UNDOBLKS),avg(UNDOBLKS),max(maxquerylen),sum(SSOLDERRCNT),sum(NOSPACEERRCNT)
from v$undostat;
prompt
prompt ################################################################
prompt Tablespaces Fragmentation:
select tablespace_name,
sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
where tablespace_name in (select tablespace_name
from dba_tablespaces
where extent_management != 'LOCAL')
group by tablespace_name
order by 2 desc;
prompt
prompt ################################################################
prompt index level:
SELECT owner || '.' || index_name AS "OWNER.INDEX_NAME", blevel
FROM dba_indexes
WHERE blevel >= 3
ORDER BY blevel DESC;
prompt
prompt ################################################################
prompt #not system object of SYSTEM:#
set linesize 600
col owner for a15
col segment_name for a20
col tablespace_name for a30
col segment_type for a15
select owner,segment_name,segment_type,bytes/1024/1024,tablespace_name
from dba_segments where tablespace_name='SYSTEM'
and owner IN (select username from dba_users where account_status='OPEN'
and username NOT IN ('SYS','SYSTEM','MGMT_VIEW','DBSNMP','SCOTT','SYSMAN'));
prompt
prompt ################################################################
prompt #table chain:#
select a.owner,a.table_name,count(a.chain_cnt) from dba_tables a
where chain_cnt>0
group by a.owner,a.table_name;
prompt
prompt #invalid OBJECT name:#
col owner format a20
col OBJECT_NAME format a30
select owner,
object_type,
object_name
from dba_objects where status='INVALID' order by owner,object_type;
prompt
prompt #table and index is more than 2G not partition:#
col segment_name for a60
select owner, segment_name,bytes/1024/1024/1024 "GB size" from dba_segments
where bytes>2048000000 and partition_name IS NULL;
prompt
prompt ################################################################
prompt #2pc trancation:#
SELECT local_tran_id FROM dba_2pc_pending;
prompt
prompt################################################################
prompt #SECURITY The user of having the role 'DBA'#
select * from dba_role_privs where granted_role='DBA';
prompt
prompt################################################################
prompt PGA:
show parameter workarea_size_policy;
show parameter pga_aggregate_target;
prompt
prompt################################################################
prompt INVALID INDEX:
COL OWNER FORMAT A9;
COL OBJECT_NAME FORMAT A30;
select OWNER,OBJECT_NAME from dba_objects where object_type='INDEX' and
status='INVALID';
prompt
prompt ################################################################
prompt Redo Usage:
show parameter log_buffer
col name format a30
col value for 9999999999
select rbar.name,
rbar.value,
re.name,
re.value,
(rbar.value*100)/re.value||'%' "radio"
from v$sysstat rbar,v$sysstat re
where rbar.name='redo buffer allocation retries'
and re.name='redo entries';
prompt
prompt ################################################################
prompt BACKUP MODE:
select set_stamp,
backup_type,
to_char(start_time,'yyyymmdd hh24:mi:ss')
from v$backup_set where start_time > sysdate - 5 order by start_time;
prompt

spool off