Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vijay.

Asked: November 14, 2017 - 9:45 pm UTC

Last updated: November 15, 2017 - 2:19 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi,

Could you please anyone help me on the script for Alert log monitoring for every one 1 hour need to fetch the errors and send it mail.

Thanks

and Connor said...

Plenty of options already out there

eg Using ADRCI

See this link for a nice example

https://coskan.wordpress.com/2010/12/06/alert-log-monitoring-script-via-adrci/

Also, if you want, you can have the full power of SQL to access the alert log, eg

SQL> desc V$diag_alert_ext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 ADR_PATH_IDX                                       VARCHAR2(445)
 ADR_HOME                                           VARCHAR2(445)
 ORIGINATING_TIMESTAMP                              TIMESTAMP(9) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(9) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(67)
 COMPONENT_ID                                       VARCHAR2(67)
 HOST_ID                                            VARCHAR2(67)
 HOST_ADDRESS                                       VARCHAR2(49)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(67)
 MESSAGE_GROUP                                      VARCHAR2(67)
 CLIENT_ID                                          VARCHAR2(67)
 MODULE_ID                                          VARCHAR2(67)
 PROCESS_ID                                         VARCHAR2(35)
 THREAD_ID                                          VARCHAR2(67)
 USER_ID                                            VARCHAR2(131)
 INSTANCE_ID                                        VARCHAR2(67)
 DETAILED_LOCATION                                  VARCHAR2(163)
 UPSTREAM_COMP_ID                                   VARCHAR2(103)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(103)
 EXECUTION_CONTEXT_ID                               VARCHAR2(103)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2051)
 MESSAGE_ARGUMENTS                                  VARCHAR2(515)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(515)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(515)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 FILENAME                                           VARCHAR2(515)
 LOG_NAME                                           VARCHAR2(67)
 PROBLEM_KEY                                        VARCHAR2(553)
 VERSION                                            NUMBER
 CON_UID                                            NUMBER
 CONTAINER_ID                                       NUMBER
 CONTAINER_NAME                                     VARCHAR2(33)

SQL> select originating_timestamp, message_text
  2  from V$diag_alert_ext
  3  where originating_timestamp > cast(sysdate-3/24 as timestamp);

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
---------------------------------------------------------------------------
04-AUG-17 12.00.04.965000000 PM +08:00
Thread 1 advanced to log sequence 527 (LGWR switch)

04-AUG-17 12.00.04.971000000 PM +08:00
  Current log# 2 seq# 527 mem# 0: C:\ORACLE\ORADATA\DB122\REDO02.LOG

04-AUG-17 12.00.05.350000000 PM +08:00
Archived Log entry 526 added for T-1.S-526 ID 0x33fe90fc LAD:1



but this depends on your version of Oracle

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database