表空間郵件預警(luckyfriends)
表空間郵件預警
文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。
----某些資料庫可以單獨建立使用者級對應的表空間
create tablespace yw_cux_data datafile '/u02/lsdb/yw_cux_data01.dbf' size 100m;
create user lsdb_yw identified by ****** default tablespace yw_cux_data temporary tablespace temp;
grant connect,dba to lsdb_yw;
grant select on DBA_FREE_SPACE to lsdb_yw;
grant select on DBA_DATA_FILES to lsdb_yw;
grant execute on sys.UTL_TCP to lsdb_yw;
grant execute on sys.UTL_SMTP to lsdb_yw;
grant execute on SYS.UTL_INADDR to lsdb_yw;
grant execute on utl_mail to lsdb_yw
---建立存放郵件的表並插入接收人的郵件地址
conn lsdb_yw/*******
CREATE TABLE TAB_SEND_MAIL
(
NUM_ID NUMBER,
EMAIL_ADDRESS VARCHAR2(50)
);
insert into TAB_SEND_MAIL values (1,'TEST@163.com');
commit;
SQL> select comp_name from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rules Manager
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
17 rows selected
SQL> select global_name,utl_inaddr.get_host_address from global_name;
GLOBAL_NAME GET_HOST_ADDRESS
-------------------------------------------------------------------------------- --
LSDB 10.12.22.21
----如果沒有安裝XML database則執行如下指令碼：
SQL> @$ORACLE_HOME/rdbms/admin/utlsmtp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltcp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catqm.sql xdb sysaux temp YES
SQL> select comp_name , status from dba_registry;
COMP_NAME STATUS
-------------------------------------------------------------------------------- --
Oracle XML Database VALID
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
---授權查詢許可權
grant select on DBA_FREE_SPACE to haohao;
grant select on DBA_DATA_FILES to haohao;
----可能會碰到沒有訪問ACL的許可權，則需執行如下：
---建立一個ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl =>'cux_application_acl.xml',
description => 'ACL for users of my application.',
principal => 'HAOHAO',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
commit;
END;
/
---給使用者增加訪問網路的許可權
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'cux_application_acl.xml',
principal => 'HAOHAO',
is_grant => true,
privilege => 'resolve');
commit;
end;
/
---給使用者增加訪問網路的許可權connect
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'cux_application_acl.xml',
principal => 'HAOHAO',
is_grant => true,
privilege => 'connect');
commit;
end;
/
---指派ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'cux_application_acl.xml',
host => '10.12.22.23',
lower_port => 25,
upper_port => NULL);
COMMIT;
END;
/
SQL> select any_path from resource_view where any_path like '/sys/acls/%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/cux_application_acl.xml
/sys/acls/ro_all_acl.xml
SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL
-------------------------------------------------------------------------------- --
10.12.22.23 25 25 /sys/acls/cux_application_acl.xml
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
-------------------------------------------------------------------------------- --
/sys/acls/cux_application_acl.xml SYSADM connect true
/sys/acls/cux_application_acl.xml SYSADM resolve true
----建立傳送郵件儲存過程
CREATE OR REPLACE PROCEDURE CUX_SEND_MAIL(SUBJECT IN VARCHAR2,
CONTENTSED IN VARCHAR2
) IS
EMAIL_SERVER VARCHAR2(30) := '127.0.0.1';
SENDER_ADDRESS VARCHAR2(50) := 'root@test.com';--發件地址
RECEIVER_ADDRESS VARCHAR2(30); --收件人地址變數
PORT NUMBER := 25;
CONN UTL_SMTP.CONNECTION;
MESG VARCHAR2(4000);
cursor c_mail is select email_address from TAB_SEND_MAIL; --收件人地址表
BEGIN
-----------------------
for c_mail_v in c_mail
loop
RECEIVER_ADDRESS:=c_mail_v.email_address;
CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT);
UTL_SMTP.HELO(CONN,EMAIL_SERVER);
UTL_SMTP.MAIL(CONN,SENDER_ADDRESS);
UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS);
MESG:= 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
'DATE:' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || UTL_TCP.CRLF ||
'FROM:' || SENDER_ADDRESS || UTL_TCP.CRLF ||
'SUBJECT:' || SUBJECT || UTL_TCP.CRLF ||
'TO: '|| RECEIVER_ADDRESS || UTL_TCP.CRLF ||
'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || CONTENTSED || UTL_TCP.CRLF ;
UTL_SMTP.OPEN_DATA(CONN);
UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG));
UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);
end loop;
END;
----建立表空間使用率預警儲存過程
CREATE OR REPLACE PROCEDURE CUX_TBS_ALERT(v_percent in number default 85)
as
SUBJECT varchar2(100);
CONTENT varchar2(4000);
CONTENT1 varchar2(4000);
p_v2 number;
p_v1 number;
p_percent number;
BEGIN
p_percent:=v_percent;
------根據輸入判斷百分比
select count(*) into p_v2 from (select tablespace_name,sum(a.bytes) db from DBA_DATA_FILES a group by tablespace_name) d,
(select tablespace_name ,sum(b.bytes) fb from DBA_FREE_SPACE b group by tablespace_name) f where
d.tablespace_name=f.tablespace_name and
((d.db-f.fb)/d.db)*100>p_percent;
if p_v2>0 then
SUBJECT:='PROD-DB Tablespace Used Space Alert Info';
CONTENT1:='表空間超過預警值'||p_percent||'%'||chr(13)||chr(10)||'表空間名 佔用百分比';
for it1 in (
SELECT UPPER(F.TABLESPACE_NAME) name,
--D.TOT_GROOTTE_MB sized,
--D.TOT_GROOTTE_MB - F.TOTAL_BYTES used,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '99.99') pec
--F.TOTAL_BYTES ,
--F.MAX_BYTES
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and (D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100>p_percent
order by pec desc)
loop
CONTENT:=it1.name||it1.pec;
CONTENT1:=CONTENT1||chr(13)||chr(10)||CONTENT;
end loop;
cux_send_mail(SUBJECT,CONTENT1); ---傳送郵件過程
end if;
end;
----建立呼叫預警儲存過程的job，第一次執行為第二天8:30，然後每隔兩小時執行一次
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin CUX_TBS_ALERT(90); end;'
,next_date => to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 08:30:00','yyyy-mm-dd hh24:mi:ss')
,interval => 'SYSDATE+2/24'
,no_parse => TRUE
);
commit;
END;
/
文章版權所有Jusin Hao（luckyfriends），支援原創，轉載請註明。
----某些資料庫可以單獨建立使用者級對應的表空間
create tablespace yw_cux_data datafile '/u02/lsdb/yw_cux_data01.dbf' size 100m;
create user lsdb_yw identified by ****** default tablespace yw_cux_data temporary tablespace temp;
grant connect,dba to lsdb_yw;
grant select on DBA_FREE_SPACE to lsdb_yw;
grant select on DBA_DATA_FILES to lsdb_yw;
grant execute on sys.UTL_TCP to lsdb_yw;
grant execute on sys.UTL_SMTP to lsdb_yw;
grant execute on SYS.UTL_INADDR to lsdb_yw;
grant execute on utl_mail to lsdb_yw
---建立存放郵件的表並插入接收人的郵件地址
conn lsdb_yw/*******
CREATE TABLE TAB_SEND_MAIL
(
NUM_ID NUMBER,
EMAIL_ADDRESS VARCHAR2(50)
);
insert into TAB_SEND_MAIL values (1,'TEST@163.com');
commit;
SQL> select comp_name from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rules Manager
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
17 rows selected
SQL> select global_name,utl_inaddr.get_host_address from global_name;
GLOBAL_NAME GET_HOST_ADDRESS
-------------------------------------------------------------------------------- --
LSDB 10.12.22.21
----如果沒有安裝XML database則執行如下指令碼：
SQL> @$ORACLE_HOME/rdbms/admin/utlsmtp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltcp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catqm.sql xdb sysaux temp YES
SQL> select comp_name , status from dba_registry;
COMP_NAME STATUS
-------------------------------------------------------------------------------- --
Oracle XML Database VALID
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
---授權查詢許可權
grant select on DBA_FREE_SPACE to haohao;
grant select on DBA_DATA_FILES to haohao;
----可能會碰到沒有訪問ACL的許可權，則需執行如下：
---建立一個ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl =>'cux_application_acl.xml',
description => 'ACL for users of my application.',
principal => 'HAOHAO',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
commit;
END;
/
---給使用者增加訪問網路的許可權
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'cux_application_acl.xml',
principal => 'HAOHAO',
is_grant => true,
privilege => 'resolve');
commit;
end;
/
---給使用者增加訪問網路的許可權connect
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'cux_application_acl.xml',
principal => 'HAOHAO',
is_grant => true,
privilege => 'connect');
commit;
end;
/
---指派ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'cux_application_acl.xml',
host => '10.12.22.23',
lower_port => 25,
upper_port => NULL);
COMMIT;
END;
/
SQL> select any_path from resource_view where any_path like '/sys/acls/%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/cux_application_acl.xml
/sys/acls/ro_all_acl.xml
SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL
-------------------------------------------------------------------------------- --
10.12.22.23 25 25 /sys/acls/cux_application_acl.xml
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
-------------------------------------------------------------------------------- --
/sys/acls/cux_application_acl.xml SYSADM connect true
/sys/acls/cux_application_acl.xml SYSADM resolve true
----建立傳送郵件儲存過程
CREATE OR REPLACE PROCEDURE CUX_SEND_MAIL(SUBJECT IN VARCHAR2,
CONTENTSED IN VARCHAR2
) IS
EMAIL_SERVER VARCHAR2(30) := '127.0.0.1';
SENDER_ADDRESS VARCHAR2(50) := 'root@test.com';--發件地址
RECEIVER_ADDRESS VARCHAR2(30); --收件人地址變數
PORT NUMBER := 25;
CONN UTL_SMTP.CONNECTION;
MESG VARCHAR2(4000);
cursor c_mail is select email_address from TAB_SEND_MAIL; --收件人地址表
BEGIN
-----------------------
for c_mail_v in c_mail
loop
RECEIVER_ADDRESS:=c_mail_v.email_address;
CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT);
UTL_SMTP.HELO(CONN,EMAIL_SERVER);
UTL_SMTP.MAIL(CONN,SENDER_ADDRESS);
UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS);
MESG:= 'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
'DATE:' || TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') || UTL_TCP.CRLF ||
'FROM:' || SENDER_ADDRESS || UTL_TCP.CRLF ||
'SUBJECT:' || SUBJECT || UTL_TCP.CRLF ||
'TO: '|| RECEIVER_ADDRESS || UTL_TCP.CRLF ||
'CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk' || UTL_TCP.CRLF ||
'' || UTL_TCP.CRLF || CONTENTSED || UTL_TCP.CRLF ;
UTL_SMTP.OPEN_DATA(CONN);
UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG));
UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);
end loop;
END;
----建立表空間使用率預警儲存過程
CREATE OR REPLACE PROCEDURE CUX_TBS_ALERT(v_percent in number default 85)
as
SUBJECT varchar2(100);
CONTENT varchar2(4000);
CONTENT1 varchar2(4000);
p_v2 number;
p_v1 number;
p_percent number;
BEGIN
p_percent:=v_percent;
------根據輸入判斷百分比
select count(*) into p_v2 from (select tablespace_name,sum(a.bytes) db from DBA_DATA_FILES a group by tablespace_name) d,
(select tablespace_name ,sum(b.bytes) fb from DBA_FREE_SPACE b group by tablespace_name) f where
d.tablespace_name=f.tablespace_name and
((d.db-f.fb)/d.db)*100>p_percent;
if p_v2>0 then
SUBJECT:='PROD-DB Tablespace Used Space Alert Info';
CONTENT1:='表空間超過預警值'||p_percent||'%'||chr(13)||chr(10)||'表空間名 佔用百分比';
for it1 in (
SELECT UPPER(F.TABLESPACE_NAME) name,
--D.TOT_GROOTTE_MB sized,
--D.TOT_GROOTTE_MB - F.TOTAL_BYTES used,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '99.99') pec
--F.TOTAL_BYTES ,
--F.MAX_BYTES
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and (D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100>p_percent
order by pec desc)
loop
CONTENT:=it1.name||it1.pec;
CONTENT1:=CONTENT1||chr(13)||chr(10)||CONTENT;
end loop;
cux_send_mail(SUBJECT,CONTENT1); ---傳送郵件過程
end if;
end;
----建立呼叫預警儲存過程的job，第一次執行為第二天8:30，然後每隔兩小時執行一次
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin CUX_TBS_ALERT(90); end;'
,next_date => to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 08:30:00','yyyy-mm-dd hh24:mi:ss')
,interval => 'SYSDATE+2/24'
,no_parse => TRUE
);
commit;
END;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1074849/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 自動化運維-Python表空間郵件預警Oracle運維Python
- SQLServer郵件預警SQLServer
- 2 Day DBA-管理Oracle例項-練習：修改表空間空間使用預警闕值Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle
- zabbix郵件報警通知
- Linux發郵件磁碟空間監控Linux
- prometheus配置MySQL郵件報警PrometheusMySql
- 自動監控Oracle 表空間資訊併傳送郵件指令碼Oracle指令碼
- grafana的郵件報警AlertingGrafana
- zabbix 配置傳送郵件報警
- 三封報警郵件的分析
- Zabbix郵件預警-這個坑我跳了不止一次
- 使用Zabbix服務端本地郵箱賬號傳送報警郵件及指定報警郵件操作記錄服務端
- jenkins郵件報警機制配置Jenkins
- zabbix郵件報警功能的驗證
- oracle表空間傳輸的限制條件Oracle
- 將Outlook中的郵件儲存到本地磁碟，釋放郵箱空間
- 臨時表空間的建立、刪除，設定預設臨時表空間
- ORACLE預設的臨時表空間Oracle
- 備庫報警郵件的分析案例（一)
- 備庫報警郵件的分析案例（二)
- 備庫報警郵件的分析案例(三)
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 增加自動擴充套件臨時表空間及改變預設表空間套件
- Zabbix郵件預警-zabbix+grafana從零設計自己的監控平臺Grafana
- 2.5.9.2 重寫預設表空間型別型別
- 2.5.9.1 指定預設表空間的型別型別
- 11g 表空間extent預分配特性
- 細述zabbix郵件報警常見問題
- supervisor守護程式並配置郵件報警
- 一封備庫報警郵件的分析
- 分析表空間空閒率並收縮表空間
- Oracle資料庫設定預設表空間Oracle資料庫
- pinpoint-docker開啟郵件報警和整合釘釘報警推送Docker
- zabbix監控之同時向多人郵件報警
- Linux 下如何用 mutt 設定郵件報警Linux
- 一條看似平常的報警郵件所做的分析
- UNIX系統高負載郵件報警指令碼負載指令碼