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 '系統日誌' 25235255@163.com
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 '系統日誌' 25235255@163.com
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29065182/viewspace-1681138/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 搭建前端錯誤監控系統前端
- 部署Sentry日誌監控系統
- ELK前端日誌分析、監控系統前端
- 分散式系統監控(五)- 日誌分析分散式
- 日誌監控
- win10系統怎麼檢視系統錯誤日誌_win10如何檢視錯誤日誌Win10
- 實時監控儲存過程中應用的日誌資訊儲存過程
- 華納雲:linux系統中如何查詢oracle錯誤日誌LinuxOracle
- oracle日誌錯誤恢復(轉)Oracle
- Logstash實踐: 分散式系統的日誌監控分散式
- 使用zabbix監控oracle的後臺日誌Oracle
- ORACLE寫日誌過程存在缺陷Oracle
- 聊聊前端監控——錯誤監控篇前端
- mysql 日誌之錯誤日誌MySql
- Mysql事件監控日誌MySql事件
- 常見日誌、抓包和系統監控分析軟體
- 搭建一個前端監控系統,不再錯過BUG前端
- 【DataGuard】Oracle DataGuard 日誌傳輸狀態監控Oracle
- 排查錯誤日誌
- Linux系統最近重啟時間、錯誤日誌Linux
- 小程式日誌監控工具
- DG 日誌傳輸監控
- Python監控日誌程式Python
- 新增橋接連線狀態監控、重構日誌系統橋接
- Linux 系統中使用 logwatch 監控日誌檔案Linux
- 用 Logwatch 工具監控 Linux 系統 Log 日誌(zt)Linux
- 檢查Linux系統日誌error和mysql錯誤日誌的指令碼薦LinuxErrorMySql指令碼
- Oracle10g DML錯誤日誌表Oracle
- Apche日誌系列(2):錯誤日誌(轉)
- Oracle 自動化運維-Python監控Oracle告警日誌Oracle運維Python
- 透過shell指令碼監控日誌切換頻率指令碼
- 通過shell指令碼監控日誌切換頻率指令碼
- Mabatis配置錯誤日誌BAT
- net 日誌分析錯誤
- 日誌查詢錯誤
- 錯誤日誌檢視
- SQL Server 錯誤日誌SQLServer
- 案例二十每分鐘監控錯誤日誌併傳送郵件給相關人員