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;