Oracle監控系統錯誤記錄檔過程

Oracle監控系統錯誤記錄檔過程

--建立暫存資料表用來存放系統錯誤資訊
create table superflow(cust_id number(10),cust_name varchar2(100),d varchar(50),error_info varchar2(500),client_ip varchar2(50),protocol varchar2(100),t number(20),sj date) tablespace db_data;
   
 --drop table superflow
 
--建立資料寫入路徑 
create directory db_dir as '/home/Oracle';

--把目錄授權給使用者
grant read,write on directory db_dir to db;

監控系統錯誤記錄檔 
過程完成任務有
 把抽取出來錯誤記錄檔插入到superflow
錯誤記錄檔會寫入作業系統/home/oracle/sql.txt檔案下面
create or replace procedure pro_superflow as

 pro_name  VARCHAR2(50);
 err_info  VARCHAR2(1000);
 v_Sql    VARCHAR2(1000);
 sj        VARCHAR2(20);
 JL        NUMBER(10);
  --cursor record_t_sms_customer_errlog is select c.cust_id "客戶ID",cu.cust_name "客戶名稱",c.d "狀態",c.error_info "錯誤資訊",c.client_ip "客戶IP",c.protocol "協議",c.t "記錄數" from (select CUST_ID,case ACTION when '1' then '登入' else '下行' end d,ERROR_INFO,CLIENT_IP,PROTOCOL,COUNT(ERRLOG_ID) t from t_sms_customer_errlog where create_time > sysdate - interval '1' hour and err_code=8 group by cust_id, action, ERROR_INFO,client_ip, protocol) c，t_sms_customer cu where c.cust_id=cu.cust_id and t>5;
   
  cursor record_t_sms_customer_errlog is select c.cust_id "客戶ID",cu.cust_name "客戶名稱",c.d "狀態",c.error_info "錯誤資訊",c.client_ip "客戶IP",c.protocol "協議",c.t "記錄數" from (select CUST_ID,case ACTION when '1' then '登入' else '下行' end d,ERROR_INFO,CLIENT_IP,PROTOCOL,COUNT(ERRLOG_ID) t from t_sms_customer_errlog where err_code=8  group by cust_id, action, ERROR_INFO,client_ip, protocol) c，t_sms_customer cu where c.cust_id=cu.cust_id;
   
 --all_record t_sms_customer_errlog%rowtype;
  type table_type is record( cust_id_1 number(10),cust_name_1 varchar2(100),d varchar2(20),error_info_1 varchar2(1000),client_ip_1 varchar2(50),protocol_1 varchar2(1000),t_1 number(10));
 
  all_record table_type;
 
 
  vsfile  UTL_FILE.file_type;
 begin
  SELECT COUNT(1)INTO JL FROM superflow;
  IF JL >10000 THEN
   
    delete FROM SUPERFLOW where sj < sysdate - interval '7' day;
   
    END IF;
    vsfile :=                   
        UTL_FILE.fopen ('DB_DIR',  --->使用fopen開啟檔案，定義了檔案路徑，檔案名稱，讀寫方式以及每一行字元的最大長度，預設為1024
                        'sql.txt',
                        'W');
  for all_record in record_t_sms_customer_errlog
      loop
   
    insert into superflow values(all_record.客戶id,all_record.客戶名稱,all_record.狀態,all_record.錯誤資訊,all_record.客戶ip,all_record.協議,all_record.記錄數,sysdate);
    commit;
      UTL_FILE.put_line (vsfile, all_record.客戶id);
      UTL_FILE.put_line (vsfile, all_record.客戶名稱);
      UTL_FILE.put_line (vsfile, all_record.狀態);
      UTL_FILE.put_line (vsfile, all_record.錯誤資訊);
      UTL_FILE.put_line (vsfile, all_record.客戶ip);
      --UTL_FILE.put_line (vsfile, all_record.協議)
      UTL_FILE.put_line (vsfile, all_record.記錄數);
      UTL_FILE.put_line (vsfile, sysdate);
      -- 把輸入內容寫入檔案
    end loop;
    UTL_FILE.fflush (vsfile);
    UTL_FILE.fclose (vsfile);
    exception
  when others then
    --sg_log_err('manage_partition',sqlerrm);
    pro_name :='manage_partition';
    err_info :=sqlerrm;
    select sysdate into sj from dual;
    v_Sql := 'insert into err_log values('||'''PRO_SUPERFLOW'''||','''||err_info||''','''||sj||''')';
    execute immediate v_Sql;
    commit;
   
    dbms_output.put_line(sqlcode||sqlerrm);
    end pro_superflow;

把錯誤記錄檔以郵件形式發出去
echo '監控日誌'|mail -s '系統日誌' [email protected] </home/oracle/sql.txt