巡檢樣例如 http://blog.itpub.net/29487349/viewspace-2845000/

巡檢指令碼如下，主要用於參考，可根據自身需求，整理其他。

``` sql
-- Copyright (c) 2021-2022 . Firsouler All rights reserved.
define fileName=Oracle_DB_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 DB Health Check Starting...
prompt

define reportHeader="<font size="+3" color="darkgreen"><b>Oracle Database Check Result</b></font>"

set termout       off
set echo          off
set feedback      off
set verify        off
set wrap          on
set trimspool     on
set escape        on
set pagesize 50000
set long     20000
set lines 2000
set numw 16
set serveroutput on size  unlimited format wrapped
col error format a30

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool &FileName._&_host_name._&_SID._&_spool_time..html
prompt &reportHeader

prompt 
prompt 
prompt 
prompt <title>Oracle check </title>
prompt <style type="text/css">
prompt a.awr {font:bold 10pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}
prompt li.awr {font: 12pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;margin-left:24pt;}
prompt h1.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
prompt h4.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
prompt h5.awr {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;margin-left:2pt;}
prompt h6.awr {font:bold 12pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
prompt th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt th.awrbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
prompt td.awrc    {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
prompt th         {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
prompt table.tdiff {  border_collapse: collapse; }
prompt body {padding: 0 5%;width: 90%;}
prompt  </style>
prompt      <div></div>
prompt  
prompt

prompt  <div id="main_report" style="width:100%;height:200px;">
prompt  <ul>
prompt <div id="left" style="width:25%;float:left;">
prompt  <h5 class="awr"> 1. Database base info </h5>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_1_1" rel="nofollow"> 1.1 Check Time </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_1_2" rel="nofollow"> 1.2 DB info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_1_3" rel="nofollow"> 1.3 Instance info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_1_4" rel="nofollow"> 1.4 Data increase stat </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_1_6" rel="nofollow"> 1.5 PDB info </a></li>
prompt  <h5 class="awr"> 2. Tablespace info</h5>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_2_1" rel="nofollow"> 2.1 Tablespace user_top20</a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_2_2" rel="nofollow"> 2.2 Tablespace all </a></li>
prompt </div>

prompt <div id="centor1" style="width:25%;float:left;">
prompt  <h5 class="awr"> 3. Redo(arch) info </h5>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_3_1" rel="nofollow"> 3.1 Redo log info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_3_2" rel="nofollow"> 3.2 arch info</a></li>
prompt </div>

prompt <div id="centor2" style="width:25%;float:left;">
prompt  <h5 class="awr"> 4. Performance info </h5>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_4_1" rel="nofollow"> 4.1 User session info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_4_2" rel="nofollow"> 4.2 Transactions info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_4_3" rel="nofollow"> 4.3 Dbtime info </a></li>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_4_4" rel="nofollow"> 4.4 Wait event </a></li>
prompt </div>

prompt <div id="right" style="width:25%;float:left;">
prompt  <h5 class="awr"> 5. Log info </h5>
prompt  <li class="awr"><a class="awr" href="http://blog.itpub.net/29487349/viewspace-2845340/#1_1_10_1" rel="nofollow"> 5.1 Alert_log info </a></li>
prompt </div>
prompt  </ul>
prompt  </div>

SET MARKUP HTML ON preformat off entmap off

prompt <a name="1_1_1_1" rel="nofollow"></a>
prompt <p></p><h6 class="awr">1.1 Check Time</h6>
select sysdate as chk_date,(select banner from v$version) as db_version from dual;

prompt <a name="1_1_1_2" rel="nofollow"></a>
prompt <p></p><h6 class="awr">1.2 DB info</h6>
col PLATFORM_NAME for a20
col FORCE_LOG for a9
select cdb,name db_name,
to_char(created,'yyyy-mm-dd') db_created,
LOG_MODE,
OPEN_MODE,
FORCE_LOGGING FORCE_LOG,
PLATFORM_NAME,
supplemental_log_data_min,supplemental_log_data_all,flashback_on,
(select round(sum(bytes/1024/1024/1024),2) from cdb_segments) as db_size_GB 
from v$database;

prompt <a name="1_1_1_3" rel="nofollow"></a>
prompt <p></p><h6 class="awr">1.3.1 Instance info </h6>
col host_name for a15
select INST_ID,instance_name,host_name,
to_char(startup_time,'yyyy/mm/dd hh24:mi:ss'),
status
from gv$instance;

prompt <p></p><h6 class="awr">1.3.2 Portion parameter info </h6>
select name,round(value/1024/1024/1024,2)||'GB' as value from v$parameter where name in ('sga_max_size','memory_max_target','pga_aggregate_target','pga_aggregate_target')
union all
select name,value from v$parameter where name in ('db_files','audit_trail');

prompt <a name="1_1_1_4" rel="nofollow"></a>
prompt <p></p><h6 class="awr">1.4 data increase stat</h6>
prompt      <div id="db_dbsize" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_1_5" rel="nofollow"></a>
prompt <p></p><h6 class="awr">1.5 PDB info</h6>
col name for a15
col open_mode for a10
select name,open_mode,to_char(open_time,'yyyy/mm/dd') open_time,to_char(CREATION_TIME,'yyyy/mm/dd') CREATE_TIME,round(TOTAL_SIZE/1024/1024/1024,2) as "disk_size(GB)",
(select pdb_size  from 
(select con_id,round(sum(bytes/1024/1024/1024),2) pdb_size from cdb_segments group by con_id) t where p.con_id=t.con_id) as "pdb_size(GB)"
from v$pdbs p order by open_time;

prompt <a name="1_1_2_1" rel="nofollow"></a>
prompt <p></p><h6 class="awr">2.1 Tablespace user_top20</h6>
prompt      <div id="db_tbssize" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_2_2" rel="nofollow"></a>
prompt <p></p><h6 class="awr">2.2 Tablespace all</h6>
select c.name name,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
from (select con_id,tablespace_name,sum(bytes) sumbytes from cdb_free_space group by con_id,tablespace_name) f, 
(select con_id,tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from
 (select  con_id,nvl(case  when autoextensible ='YES' then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes
,tablespace_name,bytes  from cdb_data_files) aa group by con_id,tablespace_name) d,v$containers c
where  (f.con_id=d.con_id and f.tablespace_name=d.tablespace_name) and f.con_id=c.con_id
order by  name,used_percent desc;

prompt <a name="1_1_3_1" rel="nofollow"></a>
prompt <p></p><h6 class="awr">3.1 Redo log info</h6>
select thread#, GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS,FIRST_TIME from v$log;
select * from v$logfile;

prompt <a name="1_1_3_2" rel="nofollow"></a>
prompt <p></p><h6 class="awr">3.2 arch info</h6>
prompt      <div id="db_archivelog" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_4_1" rel="nofollow"></a>
prompt <p></p><h6 class="awr">4.1 Session info </h6>
prompt      <div id="db_pdb_session" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_4_2" rel="nofollow"></a>
prompt <p></p><h6 class="awr">4.2 Transactions info  </h6>
prompt      <div id="db_trans" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_4_3" rel="nofollow"></a>
prompt <p></p><h6 class="awr">4.3 Dbtime info </h6>
prompt      <div id="db_cpumain" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_4_4" rel="nofollow"></a>
prompt <p></p><h6 class="awr">4.4 Wait event  </h6>
prompt      <div id="db_for_wait_class" style="width: 90%;height:450px;"></div>
prompt      <a name="1_1_5_1" rel="nofollow"></a>
prompt <p></p><h6 class="awr">5.1 Alert_log(last 300) info  </h6>

set markup html off
exec dbms_output.put_line('');

exec dbms_output.put_line('');
spool off 
!sed -i '/<br>/d' &&FileName._&&_host_name._&&_SID._&&_spool_time..html
column alertfile new_value alertfiledr
select value as alertfile from V$DIAG_INFO where name='Diag Trace';
!tail -500 &&alertfiledr/alert_&&_SID..log | sed 's/^/<p>/g' >> &&FileName._&&_host_name._&&_SID._&&_spool_time..html
quit

```</p>

SCRIPT】Oracle巡檢報告html格式樣例指令碼，帶趨勢圖

相關文章