이 alert 내용을 mail로 delivery 할수 있고 user PL/SQL을 이용해 다른 여러 응용도 가능합니다.
다음의 SQL script는 EM에서 발생한 alert message와 alert의 상태, 그리고 이에 대한 delivery 수행이 잘 되었는지 확인하는 방법입니다.
* Oracle 11g Release 1에 Oracle Grid conrol 10.2.0.5 환경에서 테스트 되었습니다.
SQL> select TARGET_NAME||' /'||METRIC_NAME||' /'||KEY_VALUE METRIC_NAME,TIMESTAMP,DELIVERED,substr(b.MESSAGE,1,100) message,ALERT_STATE,substr(b.DELIVERY_MESSAGE,1,50) DELIVERY_MESSAGE
from sysman.mgmt_notification_log a, SYSMAN.MGMT$ALERT_NOTIF_LOG b
where a.SOURCE_OBJ_GUID(+) =b.SOURCE_OBJ_GUID
and to_char(TIMESTAMP,'MMDD') = '0311'
-- and target_name = 'LISTENER_PMGT_FLPEME01'
-- and DELIVERED ='Y'
order by TIMESTAMP
/
METRIC_NAME TIMESTAMP DEL MESSAGE ALERT_STAT DELIVERY_MESSAGE
--------------------------------------- -------------- --- -------------------------------------------------- ---------- --------------------------------------------------
TEST /problemTbsp /TS_TEST 03/11 02:07:51 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /TS_TEST 03/11 02:09:22 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:57:45 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:59:16 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
from sysman.mgmt_notification_log a, SYSMAN.MGMT$ALERT_NOTIF_LOG b
where a.SOURCE_OBJ_GUID(+) =b.SOURCE_OBJ_GUID
and to_char(TIMESTAMP,'MMDD') = '0311'
-- and target_name = 'LISTENER_PMGT_FLPEME01'
-- and DELIVERED ='Y'
order by TIMESTAMP
/
METRIC_NAME TIMESTAMP DEL MESSAGE ALERT_STAT DELIVERY_MESSAGE
--------------------------------------- -------------- --- -------------------------------------------------- ---------- --------------------------------------------------
TEST /problemTbsp /TS_TEST 03/11 02:07:51 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /TS_TEST 03/11 02:09:22 Y Tablespace [TS_TEST] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:57:45 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
TEST /problemTbsp /UNDOTBS1 03/11 02:59:16 Y Tablespace [UNDOTBS1] is [97 percent ] full Critical Called PL/SQL procedure SYSMAN.LOGGING_ALERT
'Oracle Database' 카테고리의 다른 글
| Oracle FLASHBACK TABLE - sample (2) | 2010.03.30 |
|---|---|
| oracle10g rac new feature - DRM (Dynamic Resource Mastering) (0) | 2010.03.25 |
| SQL Script : find HOT block (0) | 2010.03.23 |
| Oracle alert log file과 trace file을 관리하는 새로운 방법 - ADRCI : IPS (0) | 2010.03.10 |
| Oracle Global Temporary Table (0) | 2010.03.09 |