oracle磁碟分析

-- 你可以定時用 df -h 的結果打入一個外部記錄檔，然後，利用Oracle的“外部表格”去訪問這個“外部記錄檔”就可以了，例如： -- Step 1：寫一指令碼，定時將磁碟資訊打入記錄檔： [oracle@localhost ~]$ more /data/obase/tbs_info/disk_info.sh #!/bin/bash # source /home/oracle/.bash_profile cd /data/obase/tbs_info/ echo "-------- Disk Used Info of Server: 10.4.1.12 --------"
> /data/obase/tbs_info/disk_info.log echo "-------- DateTime: `date` -------"
>> /data/obase/tbs_info/disk_info.log df -h >> /data/obase/tbs_info/disk_info.log -- Step 2：用crontab 定時執行指令碼(例如：每個小時的59分的時候執行一次（每小時執行一次)： [oracle@localhost ~]$ crontab -e 59 * * * * /data/obase/tbs_info/disk_info.sh > /data/obase/tbs_info/disk_info.log -- Step 2：建立TBS_INFO目錄,並授權lym使用者讀、寫入權限。 CREATE
DIRECTORY TBS_INFO AS
'/data/obase/tbs_info/' ; GRANT
READ ,WRITE ON
DIRECTORY TBS_INFO TO
LYM; -- Step 3：監控各資料庫伺服器磁碟空間使用方式： select
dbms_metadata.get_ddl( 'TABLE' , 'DISK_INFO' )
FROM DUAL; ---------------------------------------------------------------------------- -- 3.1 外部表格的建立語句： CREATE
TABLE "LYM" . "DISK_INFO" (
"TEXT" VARCHAR2(150) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT
DIRECTORY "TBS_INFO" ACCESS PARAMETERS ( records delimited
by newline nobadfile nodiscardfile nologfile ) LOCATION (
'disk_info.log' ) ) REJECT LIMIT UNLIMITED -- 3.2. 建立監控各資料庫伺服器磁碟空間的函數 CREATE
OR REPLACE FUNCTION
disk_info_func RETURN
VARCHAR2 AS /****************************************************************************** ** 功能：監控Oracle資料庫伺服器(10.4.1.12、10.4.1.13、10.4.1.21) ** 各磁碟空間的使用方式，如果其佔用空間超過其總空間的80%，將擷取其磁碟空間資訊! ** 建立者：羅友謀 ** 建立時間：2011.12.14 ****************************************************************************/ v_cnt12 NUMBER(18,0); v_cnt13 NUMBER(18,0); v_cnt21 NUMBER(18,0); v_disk_info VARCHAR2(4000); v_disk_info_all VARCHAR2(4000); BEGIN SELECT
count (*) AS
cnt INTO
v_cnt12 FROM
disk_info@tdw12_lym WHERE
( ( text LIKE
'%8_\%%' escape
'\' OR text LIKE ' %9_\%% ' escape ' \ ' OR text LIKE ' %100\%% ' escape ' \ '
) AND text LIKE ' %/dev/sdb% ' ) OR ( ( text like ' %9_\%% ' escape ' \ '
OR text LIKE ' %100\%% ' escape ' \ ' ) AND text NOT LIKE ' %/dev/sdb% ' ); SELECT count(*) AS cnt INTO v_cnt13 FROM disk_info@tdw13_lym WHERE ( ( text LIKE ' %8_\%% ' escape ' \ '
OR text LIKE ' %9_\%% ' escape ' \ ' OR text LIKE ' %100\%% ' escape ' \ '
) AND text LIKE ' %/dev/sdb% ' ) OR ( ( text like ' %9_\%% ' escape ' \ '
OR text LIKE ' %100\%% ' escape ' \ ' ) AND text NOT LIKE ' %/dev/sdb% ' ); SELECT count(*) AS cnt INTO v_cnt21 FROM disk_info WHERE ( ( text LIKE ' %8_\%% ' escape ' \ '
OR text LIKE ' %9_\%% ' escape ' \ ' OR text LIKE ' %100\%% ' escape ' \ '
) AND text LIKE ' %/dev/sdb% ' ) OR ( ( text like ' %9_\%% ' escape ' \ '
OR text LIKE ' %100\%% ' escape ' \ ' ) AND text NOT LIKE ' %/dev/sdb% ' ); v_disk_info := ' '; v_disk_info_all := ' '; IF v_cnt12 > 0 THEN FOR i12 IN ( SELECT text as disk_info FROM disk_info@tdw12_lym ORDER BY rowid ) LOOP v_disk_info := i12.disk_info; v_disk_info_all := v_disk_info_all||v_disk_info||chr(10); END LOOP; END IF; IF v_cnt13 > 0 THEN FOR i13 IN ( SELECT text as disk_info FROM disk_info@tdw13_lym ORDER BY rowid ) LOOP v_disk_info := i13.disk_info; v_disk_info_all := v_disk_info_all||v_disk_info||chr(10); END LOOP; END IF; IF v_cnt21 > 0 THEN FOR i21 IN ( SELECT text as disk_info FROM disk_info ORDER BY rowid ) LOOP v_disk_info := i21.disk_info; v_disk_info_all := v_disk_info_all||v_disk_info||chr(10); END LOOP; END IF; IF v_disk_info_all IS NULL THEN v_disk_info_all := ' All
Disk '' s
space are OK! '; END IF; RETURN v_disk_info_all; END; / ---------------------------------------------------------------------------- -- *3.3. 建立預存程序調用函數，得到磁碟空間使用資訊 CREATE OR REPLACE PROCEDURE get_disk_info_proc AS /****************************************************************************** ** 功能：監控Oracle資料庫伺服器(10.4.1.12、10.4.1.13、10.4.1.21) ** 各磁碟空間的使用方式，如果其佔用空間超過其總空間的80%，將郵件警示! ** 建立者：羅友謀 ** 建立時間：2011.12.14 ****************************************************************************/ v_disk_info varchar2(4000); BEGIN SELECT DISK_INFO_FUNC() INTO v_disk_info FROM dual; IF v_disk_info <> ' All
Disk '' s
space are OK! ' THEN PROCSENDEMAIL(v_disk_info,' Disk
Space Emergency!!! ',' ymluo@corp.tudou.com ',' 13691147539@139.com ',' 10.5.101.1 ',' 25 ',' 0 ',NULL,NULL,NULL,' bit
7 '); END IF; -- dbms_output.put_line(v_disk_info); END; / ---------------------------------------------------------------------------- -- *3.4. 建立 Job 定時監控 -- job 1.1 -- 每15分鐘執行一次 variable job_disk_info_proc number; begin dbms_job.submit(:job_disk_info_proc,' get_disk_info_proc; ',sysdate,' SYSDATE+1/96'); end ;