脚本内容
新建文本文件 dbcheck.sql，写入如下内容：
define fileName=Oracle_Check
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') spool_time FROM dual;
COLUMN hostname NEW_VALUE _host_name NOPRINT
select host_name hostname from v$instance;
COLUMN instance_name NEW_VALUE _SID NOPRINT
select instance_name from v$instance;
define pfileName=&_SID._&_spool_time.
prompt +----------------------------------------------------------------------------+
prompt | Oracle Database Check Result |
prompt |----------------------------------------------------------------------------+
prompt
prompt Note: Oracle DB Health Check is starting...
prompt Please hit 'Enter'
--&FileName._&_host_name._&_SID._&_spool_time..html的中文解释Oracle_Check_主机名_实例名_巡检时间.html
set termout off
set echo off
set feedback off
set verify off
set wrap on
set trimspool on
set serveroutput on
set escape on
set pagesize 50000
set long 2000000000
set numw 16
col error format a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set markup html on spool on preformat off entmap on -
head ' -
<title>Oracle Database Check result</title> -
<style type="text/css"> -
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
th {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
h1 {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:#0066cc; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
a {font:10pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style>' -
body 'BGCOLOR="#C0C0C0"' -
table 'WIDTH="90%" BORDER="1"'
spool &FileName._&_host_name._&_SID._&_spool_time..html
set markup html on entmap off
prompt &reportHeader
SET MARKUP HTML ON
prompt <font size="+3" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b>Oracle Daily Inspection</b></font>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>1.Check Time</b></font>
select sysdate as current_date from dual;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>2.DB Version</b></font>
select * from v$version;
select * from dba_registry_history;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>3.Character Set</b></font>
select userenv('language') from dual;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>4.Database Size</b></font>
select sum(bytes)/1024/1024/1024 as gb from Dba_Segments;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>5.User Size</b></font>
select owner, count(*),trunc(sum(bytes)/1024/1024/1024,2) as GB from dba_segments group by owner order by GB desc;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>6.Tablespace Usage</b></font>
prompt <font><B>Follow result include autoextend space:</B></font>
select f.tablespace_name tablespace_name,
round((d.sumbytes / 1024 / 1024 / 1024), 2) total_without_extend_GB,
round(((d.sumbytes + d.extend_bytes) / 1024 / 1024 / 1024), 2) total_with_extend_GB,
round((f.sumbytes + d.Extend_bytes) / 1024 / 1024 / 1024, 2) free_with_extend_GB,
round((d.sumbytes - f.sumbytes) / 1024 / 1024 / 1024, 2) used_GB,
round((d.sumbytes - f.sumbytes) * 100 /
(d.sumbytes + d.extend_bytes),
2) used_percent_with_extend
from (select tablespace_name, sum(bytes) sumbytes
from dba_free_space
group by tablespace_name) f,
(select tablespace_name,
sum(aa.bytes) sumbytes,
sum(aa.extend_bytes) extend_bytes
from (select nvl(case
when autoextensible = 'YES' then
(case
when (maxbytes - bytes) >= 0 then
(maxbytes - bytes)
end)
end,
0) Extend_bytes,
tablespace_name,
bytes
from dba_data_files) aa
group by tablespace_name) d
where f.tablespace_name = d.tablespace_name
order by used_percent_with_extend desc;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>7.Datafile Info</b></font>
select file_id,tablespace_name,file_name,bytes/1024/1024,status,autoextensible,maxbytes/1024/1024 from dba_data_files;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>8.Temp Tablespace Usage</b></font>
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 <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>9.Temp Datafile Info</b></font>
select tablespace_name,file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>10.Session Info</b></font>
select inst_id, sessions_current,sessions_highwater from gv$license;
show parameter processes
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>11.Archivelog Switch Info</b></font>
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM V$log_history where to_date(first_time)>to_date(sysdate-15)
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;
prompt <font><B>Note:</B></font>
prompt <br>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>12.RMAN Backup Info</b></font>
select session_key,autobackup_done,output_device_type,input_type,status,elapsed_seconds/3600 hours,to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
output_bytes_display out_size,output_bytes_per_sec_display,input_bytes_per_sec_display from v$rman_backup_job_details order by start_time;
prompt <font><B>Note:</B></font>
prompt <br>
spool off
prompt
quit
使用方法
将脚本上传至数据库服务器，用数据库管理员用户调用执行后，将生成的.html文件下载至本地环境即可。（linux，win平台通用）
[oracle@dj ~]$ ll
total 24
-rw-r--r-- 1 oracle oinstall 362 Jan 25 2021 a.sql
-rw-r--r-- 1 oracle oinstall 183 Jan 25 2021 b.sql
-rw-r--r-- 1 oracle oinstall 1112 Jan 26 2021 c.sql
-rw-r--r-- 1 oracle oinstall 9427 Aug 3 16:11 dbcheck.sql
drwxr-xr-x 2 oracle oinstall 87 Jul 7 09:59 expdp
drwxr-xr-x 2 oracle oinstall 81 Jan 26 2021 impdp
[oracle@dj ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 3 16:31:52 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbcheck.sql
+----------------------------------------------------------------------------+
| Oracle Database Check Result |
|----------------------------------------------------------------------------+
Note: Oracle DB Health Check is starting...
Please hit 'Enter'
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dj ~]$ ll
total 40
-rw-r--r-- 1 oracle oinstall 362 Jan 25 2021 a.sql
-rw-r--r-- 1 oracle oinstall 183 Jan 25 2021 b.sql
-rw-r--r-- 1 oracle oinstall 1112 Jan 26 2021 c.sql
-rw-r--r-- 1 oracle oinstall 9427 Aug 3 16:11 dbcheck.sql
drwxr-xr-x 2 oracle oinstall 87 Jul 7 09:59 expdp
drwxr-xr-x 2 oracle oinstall 81 Jan 26 2021 impdp
-rw-r--r-- 1 oracle oinstall 15505 Aug 3 16:32 Oracle_Check_dj_orcl_2021-08-03.html
检查结果查看
用浏览器打开查看
