Oracle数据库巡检SQL

-- |----------------------------------------------------------------------------|

-- |

| FILE : Datebase_XunJian.sql

-- +----------------------------------------------------------------------------+

prompt

prompt +-----------------------------------------------------------------------------------------+

prompt | Database XunJian |

prompt |-----------------------------------------------------------------------------------------+

prompt |

|

prompt +-----------------------------------------------------------------------------------------+

prompt

prompt Creating database report.

prompt This script must be run as a user with SYSDBA privileges.

prompt This process can take several minutes to complete.

set termout off

prompt

set pagesize 50000

set linesize 80

define fileName=Database_XunJian

define versionNumber=1.0

COLUMN tdate NEW_VALUE _date NOPRINT

SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual;

COLUMN time NEW_VALUE _time NOPRINT

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;

COLUMN date_time NEW_VALUE _date_time NOPRINT

SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;

COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT

SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') ||

TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') \"at\" HH:MI:SS AM') || TO_CHAR(systimestamp, ' \"in Timezone\" TZR') date_time_timezone

FROM dual;

COLUMN spool_time NEW_VALUE _spool_time NOPRINT

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;

COLUMN dbname NEW_VALUE _dbname NOPRINT

SELECT name dbname FROM v$database;

COLUMN dbid NEW_VALUE _dbid NOPRINT

SELECT dbid dbid FROM v$database;

COLUMN platform_id NEW_VALUE _platform_id NOPRINT

SELECT platform_id platform_id FROM v$database;

COLUMN platform_name NEW_VALUE _platform_name NOPRINT

SELECT platform_name platform_name FROM v$database;

COLUMN global_name NEW_VALUE _global_name NOPRINT

SELECT global_name global_name FROM global_name;

COLUMN blocksize NEW_VALUE _blocksize NOPRINT

SELECT value blocksize FROM v$parameter WHERE name='db_block_size';

COLUMN startup_time NEW_VALUE _startup_time NOPRINT

SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;

COLUMN host_name NEW_VALUE _host_name NOPRINT

SELECT host_name host_name FROM v$instance;

COLUMN instance_name NEW_VALUE _instance_name NOPRINT

SELECT instance_name instance_name FROM v$instance;

COLUMN instance_number NEW_VALUE _instance_number NOPRINT

SELECT instance_number instance_number FROM v$instance;

COLUMN thread_number NEW_VALUE _thread_number NOPRINT

SELECT thread# thread_number FROM v$instance;

COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT

SELECT value cluster_database FROM v$parameter WHERE

name='cluster_database';

COLUMN cluster_database_instances NEW_VALUE

_cluster_database_instances NOPRINT

SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';

COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT

SELECT user reportRunUser FROM dual;

spool &FileName._&_instance_name._&_spool_time..txt

prompt

prompt -------------------------------------report_header-------------------

prompt

prompt Report Name:

&FileName._&_dbname._&_spool_time..txt

prompt Snapshot Database Version:&versionNumber

prompt Run Date / Time / Timezone:&_date_time_timezone

prompt Host Name:&_host_name

prompt Database Name:&_dbname

prompt Database ID:&_dbid

prompt Global Database Name:&_global_name

prompt Platform Name / ID:&_platform_name / &_platform_id

prompt Clustered Database?:&_cluster_database

prompt Clustered Database Instances:&_cluster_database_instances

prompt Instance Name:&_instance_name

prompt Instance Number:&_instance_number

prompt Thread Number:&_thread_number

prompt Database Startup Time:&_startup_time

prompt Database Block Size:&_blocksize

prompt Report Run User:&_reportRunUser

prompt

prompt

prompt -------------------Version--------------------------

prompt

Select * from v$version;

prompt

prompt -------------------opatch----------------------

prompt

col comp_name form a30 ENTMAP off

Select comp_name,version from dba_registry;

prompt

prompt -------------------psu----------------------

prompt

col comments format a50

Select id,version,comments,action_time,action from dba_registry_history;

prompt -------------------SGA----------------------

prompt

col name form a30 ENTMAP off

col value form 99999999999999999 ENTMAP off

prompt

show sga;

prompt

show parameter sga;

prompt

show parameter pga;

prompt

col name form a30 ENTMAP off

col value form a30 ENTMAP off

Select name,value from v$parameter where name like '%ga%';

prompt

prompt -------------------Controlfile---------------------

prompt

col name form a60 ENTMAP off

Select name from v$controlfile;

prompt -------------------Logfile--------------------------

prompt

col status form a10 ENTMAP off

select from v$log;

GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS

prompt

select group#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$log;

prompt

col member form a53 ENTMAP off

prompt

select group#,status,member from v$logfile;

prompt

prompt -------------------archive mode-------------------

archive log list;

prompt

prompt -------------------Tablespace user-------------------

prompt

prompt

COLUMN TABLESPACE_NAME FORMAT A28;

COLUMN SUM_M FORMAT A12;

COLUMN USED_M FORMAT A12;

COLUMN FREE_M FORMAT A12;

COLUMN PTUSED FORMAT 99.99;

SET PAGES 200 LINES 200;

prompt

SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M'

SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M'

USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' round(SUM(S.USEDSPACE)/SUM(S.BYTES)*100,2) PTUSED

FREE_M,

FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-

SUM(NVL(A.BYTES,0))) USEDSPACE,

SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO

FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE

A.FILE_ID(+)=B.FILE_ID GROUP BY

B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S

GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) asc;

prompt -------------------TEMP Tablespace user-------------------

prompt

SELECT d.tablespace_name \"Name\

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') \"Size (M)\

TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') \"HWM (M)\

TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') \"HWM % \" ,

TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') \"Using (M)\

TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') \"Using %\"

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) hwm,

sum(bytes_used) 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.extent_management like 'LOCAL'

AND d.contents like 'TEMPORARY'

/

prompt -------------------Autoextensible------------------------

prompt

Col file_name format a60 ENTMAP off

prompt

select file_name,autoextensible from dba_data_files autoextensible='YES';

prompt -------------------Users---------------------------------

prompt

col default_tablespace form a25 ENTMAP off

col temporary_tablespace form a10 ENTMAP off

col username form a20 ENTMAP off

where

col account_status form a18 ENTMAP off

prompt

select username,default_tablespace,temporary_tablespace,account_status

from dba_users;

prompt

prompt -------------------Jobs----------------------------------

prompt

col NEXT_SEC format a10 ENTMAP off

col job format 9999 ENTMAP off

col what format a50 ENTMAP off

select job, next_date,next_sec,failures,what from dba_jobs where failures !=0;

prompt

prompt -------------------Invalid index-------------------------

prompt

Col index_name form a20 ENTMAP off

Col owner form a10 ENTMAP off

Col table_name form a20 ENTMAP off

Col tablespace_name form a20 ENTMAP off

prompt

select index_name,owner,table_name,tablespace_name

from dba_indexes

where owner not in ('SYS','SYSTEM')

and status != 'VALID';

prompt

prompt -------------------Invalid object---------------------------------

prompt

COL OBJECT_NAME FORM A40 HEADING 'OBJECT_NAME' ENTMAP off

select object_name, object_type, owner,status

from dba_objects

where status !='VALID'

and owner not in ('SYS','SYSTEM')

and object_type in

('TRIGGER','VIEW','PROCEDURE','FUNCTION');

spool off;

prompt

prompt --------------------OS----------------------------

prompt

prompt --------------------/etc/hosts----------------------------

prompt

host more /etc/hosts >> &FileName._&_dbname._&_spool_time..txt

prompt

prompt

prompt ---------------------disk -----------------------

prompt

host df -k >> &FileName._&_dbname._&_spool_time..txt

host bdf >> &FileName._&_dbname._&_spool_time..txt

prompt

prompt

prompt ------------------------cpu ---------------------

prompt

host vmstat 2 10 >> &FileName._&_dbname._&_spool_time..txt

prompt

prompt

prompt -------------------------crs-----------------------

prompt

host crs_stat -t >> &FileName._&_dbname._&_spool_time..txt

prompt ------------------------ora_ ---------------------

prompt

host ps -ef|grep ora_ >> &FileName._&_dbname._&_spool_time..txt

prompt

prompt

exit;