Oracle disk Analysis

-- You can periodically use the results of DF-h to input an External Log File, and then use Oracle's "External table" to access this "External Log File". For example: -- Step 1: write a script to regularly insert disk information into the log file: [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: Use crontab to regularly execute the script (for example, execute the script once at every hour ): [oracle@localhost ~]$ crontab -e 59 * * * * /data/obase/tbs_info/disk_info.sh > /data/obase/tbs_info/disk_info.log -- Step 2: Create the tbs_info directory and grant the lym User read and write permissions. CREATE
DIRECTORY TBS_INFO AS
'/data/obase/tbs_info/' ; GRANT
READ ,WRITE ON
DIRECTORY TBS_INFO TO
LYM; -- Step 3: monitor disk space usage of each database server: select
dbms_metadata.get_ddl( 'TABLE' , 'DISK_INFO' )
FROM DUAL; ---------------------------------------------------------------------------- -- 3.1 statement for creating an external table: 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 a function to monitor disk space of each Database Server CREATE
OR REPLACE FUNCTION
disk_info_func RETURN
VARCHAR2 AS /****************************************************************************** ** Function: monitors Oracle database servers (10.4.1.12, 10.4.1.13, and 10.4.1.21) ** If the usage of each disk space exceeds 80% of its total space, the disk space information is obtained! ** Creator: Luo youmou ** Creation Time: 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 a stored procedure and call the function to obtain disk space usage information. CREATE OR REPLACE PROCEDURE get_disk_info_proc AS /****************************************************************************** ** Function: monitors Oracle database servers (10.4.1.12, 10.4.1.13, and 10.4.1.21) ** The usage of each disk space. If the occupied space exceeds 80% of the total space, an email will be reported! ** Creator: Luo youmou ** Creation Time: 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. Create job timed monitoring -- Job 1.1 -- run once every 15 minutes variable job_disk_info_proc number; begin dbms_job.submit(:job_disk_info_proc,' get_disk_info_proc; ',sysdate,' SYSDATE+1/96'); end ;