Oracle 简易巡检报告脚本

-- +----------------------------------------------------------------------------+ -- | DATABASE : Oracle | -- | FILE : database_Health_check.sql | -- | CLASS : Database Administration | -- | PURPOSE : This SQL script provides a detailed report (in HTML format) on | -- | all database metrics including installed options, storage, | -- | performance data, and security. | -- | USAGE : | -- | | -- | sqlplus -s <dba>/<password>@<TNS string> @database_health_check.sql | -- | | -- | TESTING : This script has been successfully tested on the following | -- | platforms: | -- | | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ --alter session set nls_language='SIMPLIFIED CHINESE'; --alter session set nls_language='AMERICAN_AMERICA.AL32UTF8'; prompt prompt +-----------------------------------------------------------------------+ prompt | Oracle Database Health Check | prompt +-----------------------------------------------------------------------+ prompt prompt Run this script need the user have sysdba authority. prompt please wait for a minute. prompt --prompt Set the NLS_LANG on the Unix and Linux --prompt export NLS_LANG="SIMPLIFIED CHINESE_AMERICA.ZHS16GBK" --prompt export NLS_LANG="AMERICAN_AMERICA.AL32UTF8" prompt The script is collecting some message,please wait for a minute........... -- define reportHeader="<font size=+3 color=darkgreen><b>Oracle数据库健康检查报告</b></font><hr> " -- +----------------------------------------------------------------------------+ -- | SCRIPT SETTINGS | -- +----------------------------------------------------------------------------+ set termout off set echo off set feedback off set heading off set verify off set wrap on set trimspool on set serveroutput on set escape on set pagesize 50000 set linesize 175 set long 2000000000 clear buffer computes columns breaks define fileName=Oracle_Database_HealthCheck define versionNumber=8.0 -- +----------------------------------------------------------------------------+ -- | GATHER DATABASE REPORT INFORMATION | -- +----------------------------------------------------------------------------+ define _date="1" COLUMN tdate NEW_VALUE _date NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual; define _chkdate="1" COLUMN chkdate NEW_VALUE _chkdate NOPRINT SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') chkdate FROM dual; define _time="1" COLUMN time NEW_VALUE _time NOPRINT SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual; define _date_time="1" COLUMN date_time NEW_VALUE _date_time NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual; define _date_time_timezone="1" 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; define _spool_time="1" COLUMN spool_time NEW_VALUE _spool_time NOPRINT SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual; define _dbname="1" COLUMN dbname NEW_VALUE _dbname NOPRINT SELECT lower(name) dbname FROM v$database; define _dbid="1" COLUMN dbid NEW_VALUE _dbid NOPRINT SELECT dbid dbid FROM v$database; define _platform_id="1" COLUMN platform_id NEW_VALUE _platform_id NOPRINT SELECT platform_id platform_id FROM v$database; define _platform_name="1" COLUMN platform_name NEW_VALUE _platform_name NOPRINT SELECT platform_name platform_name FROM v$database; define _global_name="1" COLUMN global_name NEW_VALUE _global_name NOPRINT SELECT global_name global_name FROM global_name; define _blocksize="1" COLUMN blocksize NEW_VALUE _blocksize NOPRINT SELECT value blocksize FROM v$parameter WHERE name='db_block_size'; define _startup_time="1" COLUMN startup_time NEW_VALUE _startup_time NOPRINT SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance; define _host_name="1" COLUMN host_name NEW_VALUE _host_name NOPRINT SELECT host_name host_name FROM v$instance; define _instance_name="1" COLUMN instance_name NEW_VALUE _instance_name NOPRINT SELECT instance_name instance_name FROM v$instance; define _instance_number="1" COLUMN instance_number NEW_VALUE _instance_number NOPRINT SELECT instance_number instance_number FROM v$instance; define _thread_number="1" COLUMN thread_number NEW_VALUE _thread_number NOPRINT SELECT thread# thread_number FROM v$instance; define _cluster_database="1" COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT SELECT value cluster_database FROM v$parameter WHERE name='cluster_database'; define _cluster_database_instances="1" COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances'; define _reportRunUser="1" COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT SELECT user reportRunUser FROM dual; define _backgroundtrace="1" COLUMN VALUE NEW_VALUE _backgroundtrace NOPRINT SELECT value FROM v$parameter WHERE name='background_dump_dest'; define _oracleversion="1" COLUMN VERSION NEW_VALUE _oracleversion NOPRINT select VERSION from v$instance; --COLUMN alf NEW_VALUE _alf NOPRINT --select ((select VALUE from v$diag_info where name ='Diag Trace')||'/alert_'||(SELECT value FROM v$parameter WHERE name='instance_name')||'.log') as alf from dual; --数据库属性 define _alf="1" COLUMN alf NEW_VALUE _alf NOPRINT select 'alert_'||(SELECT value FROM v$parameter WHERE name='instance_name')||'.log' alf from dual; define _NLS_LANG_ENV="1" COLUMN NLS_LANG_ENV NEW_VALUE _NLS_LANG_ENV NOPRINT select '"'||(select value from v$nls_parameters where upper(trim(PARAMETER))='NLS_LANGUAGE')||'_' ||(select value from v$nls_parameters where upper(trim(PARAMETER))='NLS_TERRITORY') ||'.' ||(select value from v$nls_parameters where upper(trim(PARAMETER))='NLS_CHARACTERSET')||'"' NLS_LANG_ENV from dual; define _total_size="1" COLUMN TOTAL_SIZE NEW_VALUE _total_size NOPRINT SELECT 'Total Storages' ,ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2)||'GB' TOTAL_SIZE FROM DBA_SEGMENTS; define _pdbname="NoCDB" COLUMN pdb_name NEW_VALUE _pdbname NOPRINT select decode(name,'PDB$SEED','CDB',name) pdb_name from v$pdbs where rownum=1; define _V_version="未知版本号" COLUMN version NEW_VALUE _V_version NOPRINT SELECT BANNER as version FROM v$version where banner like '%Oracle Database%'; define _V_tbscnt="0" COLUMN tbscnt NEW_VALUE _V_tbscnt NOPRINT SELECT TO_CHAR(COUNT(*)) as tbscnt FROM V$TABLESPACE ; define _V_filecnt="0" COLUMN filecnt NEW_VALUE _V_filecnt NOPRINT SELECT TO_CHAR(COUNT(*)) as filecnt FROM V$DATAFILE; define _V_tbcnt="0" COLUMN tbcnt NEW_VALUE _V_tbcnt NOPRINT SELECT TO_CHAR(count(*)) as tbcnt FROM dba_tables; define _V_idxcnt="0" COLUMN idxcnt NEW_VALUE _V_idxcnt NOPRINT SELECT TO_CHAR(count(*)) as idxcnt FROM dba_Indexes; define _V_idxcnt="0" COLUMN idxcnt NEW_VALUE _V_idxcnt NOPRINT SELECT TO_CHAR(count(*)) as idxcnt FROM dba_Indexes;