Use external tables to query for Ora errors in alert logs

Sql> Show parameter Dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Background_core_dump string Partial
Background_dump_dest String/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace
Core_dump_dest String/oracle/oracle/diag/rdbms/orcldb/orcldb1/cdump
Max_dump_file_size String Unlimited
Shadow_core_dump string Partial
User_dump_dest String/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace

Sql> Create directory alert as '/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace ';
Sql> Grant Read,write on directory alert to public;
Grant succeeded.

Sql> desc dba_directories
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER not NULL VARCHAR2 (30)
Directory_name not NULL VARCHAR2 (30)
Directory_path VARCHAR2 (4000)

Sql> Col OWNER for A30
Sql> Col directory_name for A30
Sql> Col Directory_path for A100
Sql> SELECT * from dba_directories where directory_name= ' ALERT ';

OWNER Directory_name Directory_path

SYS Alert/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace

Sql>!
[Email protected] ~]$ Cd/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace
[[email protected] trace]$ ls
Alert_orcldb1.log orcldb1_gen0_4646.trc ORCLDB1_LMS0_4701.TRM orcldb1_p000_4882.trc ORCLDB1_RSMN_4802.TRM
ORCLDB1_ASMB_4737.TRC ORCLDB1_GEN0_4646.TRM orcldb1_lms1_4666.trc ORCLDB1_P000_4882.TRM orcldb1_vkrm_10819.trc
ORCLDB1_ASMB_4737.TRM orcldb1_j000_22025.trc ORCLDB1_LMS1_4666.TRM orcldb1_p001_4884.trc ORCLDB1_VKRM_10819.TRM
ORCLDB1_CJQ0_4949.TRC ORCLDB1_J000_22025.TRM orcldb1_lms1_4705.trc ORCLDB1_P001_4884.TRM orcldb1_vkrm_18720.trc
ORCLDB1_CJQ0_4949.TRM orcldb1_j005_4968.trc ORCLDB1_LMS1_4705.TRM orcldb1_p002_4886.trc ORCLDB1_VKRM_18720.TRM
ORCLDB1_DBRM_4650.TRC ORCLDB1_J005_4968.TRM orcldb1_mark_4747.trc ORCLDB1_P002_4886.TRM orcldb1_vkrm_18893.trc
ORCLDB1_DBRM_4650.TRM orcldb1_lck0_4787.trc ORCLDB1_MARK_4747.TRM orcldb1_ping_4685.trc ORCLDB1_VKRM_18893.TRM
ORCLDB1_DBRM_4683.TRC ORCLDB1_LCK0_4787.TRM orcldb1_mman_4715.trc ORCLDB1_PING_4685.TRM orcldb1_vktm_4675.trc
ORCLDB1_DBRM_4683.TRM orcldb1_lmd0_4695.trc ORCLDB1_MMAN_4715.TRM orcldb1_q001_4912.trc ORCLDB1_VKTM_4675.TRM
ORCLDB1_DBW0_4717.TRC ORCLDB1_LMD0_4695.TRM orcldb1_mmon_4690.trc ORCLDB1_Q001_4912.TRM orcldb1_w000_17178.trc
ORCLDB1_DBW0_4717.TRM orcldb1_lmhb_4711.trc ORCLDB1_MMON_4690.TRM orcldb1_qmnc_4908.trc ORCLDB1_W000_17178.TRM
ORCLDB1_DIA0_4691_BASE_1.TRC orcldb1_lmhb_4711.trm orcldb1_mmon_4739.trc orcldb1_qmnc_4908.trm orcldb1_w001_20637. Trc
ORCLDB1_DIA0_4691_BASE_1.TRM orcldb1_lmon_4693.trc orcldb1_mmon_4739.trm orcldb1_rcbg_4906.trc orcldb1_w001_20637. Trm
ORCLDB1_DIA0_4691.TRC ORCLDB1_LMON_4693.TRM orcldb1_ora_4610.trc ORCLDB1_RCBG_4906.TRM
ORCLDB1_DIA0_4691.TRM orcldb1_lms0_4662.trc ORCLDB1_ORA_4610.TRM ORCLDB1_RMS0_4709.TRC
ORCLDB1_DIAG_4681.TRC ORCLDB1_LMS0_4662.TRM orcldb1_ora_4804.trc ORCLDB1_RMS0_4709.TRM
ORCLDB1_DIAG_4681.TRM orcldb1_lms0_4701.trc ORCLDB1_ORA_4804.TRM ORCLDB1_RSMN_4802.TRC

[[Email protected] trace]$ exit
Sql> CREATE TABLE Db_alert
2 (log varchar2 (4000))
3 Organization External
4 (Type Oracle_loader
5 Default directory Alert
6 Access Parameters
7 (Records delimited by newline
8 Nobadfile
9 Nodiscardfile
Ten Nologfile
11)
Location (' Alert_orcldb1.log ')
Reject Limit unlimited;

Table created.
Sql> SELECT * from Db_alert where log like '%ora-% ';

LOG

ORA-1109 signalled During:alter DATABASE CLOSE NORMAL ...
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00313:open failed for members of log Group 2 of thread 1
Non Critical Error ORA-48113 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_ Dbrm_7968.trc "
Non Critical Error ORA-48181 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_ Ora_32461.trc "
Non Critical Error ORA-48181 caught while writing to trace file "/oracle/oracle/diag/rdbms/orcldb/orclmon Mar 21 21:59:57 2016

Rows selected.

Use external tables to query for Ora errors in alert logs