Oracle数据库巡检脚本（相对全面），输出html，支持多租户与非多租户.txt

weizhao.zhang （anbob）

2677

117页

221次

2022-03-17

5墨值下载

prompt

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

prompt | ORACLE Database Health Check

prompt

|------------------------------------------------------------------------------+

reserved.|

prompt |Purpose:

prompt |Creating database health check report.

prompt |Usage: sqlplus / as sysdba SQL>@odbhc.sql

prompt |Note:

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

prompt | If there is a command "not found error", it can be safely ignored

prompt | The v$session.module is 'odbhc' , you can check Which SQL is runing

now. |

prompt | This process can take several minutes to complete.please waiting ...

prompt

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

define fileName=oracle_database_hc_report

define versionNumber=6.7

whenever sqlerror exit sql.sqlcode;

declare

issysdba varchar2(10);

begin

select sys_context('userenv','ISDBA') into issysdba from dual;

if issysdba!='TRUE' then

raise_application_error(-20100,'This script must be run as a user with SYSDBA

privileges.');

end if;

end;

ho echo `date "+%Y-%m-%d %H:%M:%S"` Seting modeul...

BEGIN

DBMS_APPLICATION_INFO.set_module(module_name => 'odbhc',

action_name => 'DB health checking');

END;

clear buffer computes columns breaks

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

whenever sqlerror continue;

set pagesize 50000

set linesize 175

set long 2000000000

-- change date format session level

alter session set nls_date_format='yyyymmdd hh24:mi:ss';

alter session set nls_timestamp_format='yyyymmdd hh24:mi:ss';

clear buffer computes columns breaks

define reportHeader="<font size=+2 color=#d33><b>Oracle Database Health Check

Report</b></font><hr>"

href=""http://anbob.com"">www.anbob.com</a>)</center>"

-- if db version older than 12c, some SQL will not need to run.

col IS_COMMENT new_value _COMMENT noprint

select case WHEN to_number(regexp_substr(version,'[0-9].'))<12 then ' -- ' else

'' end IS_COMMENT from v$instance;

col IS_UNCOMMENT new_value _UNCOMMENT noprint

select case WHEN to_number(regexp_substr(version,'[0-9].'))<12 then '''non-

supported''' else '--' end IS_UNCOMMENT from v$instance;

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

COLUMN database_role NEW_VALUE _db_role NOPRINT

column log_mode new_value _log_mode noprint

column force_logging new_value _force_logging noprint

column flashback_on new_value _flashback_on

SELECT platform_name

platform_name,database_role,LOG_MODE,FORCE_LOGGING,FLASHBACK_ON FROM

v$database;

column headroom new_value _headroom noprint

select

of 117

5墨值下载

oracle数据库巡检

关注

评论