select * from alert.log – die View V$DIAG_ALERT_EXT

28. April 2014 Aus Von Markus Flechtner

Seit einiger Zeit bietet Oracle die Möglichkeit, die Informationen der alert.log-Dateien mit SQL auszulesen. Früher versteckt über diew X$-View X$DBGALERTEXT, jetzt – etwas weniger versteckt – über die (undokumentierte) V$-View V$DIAG_ALERT_EXT.

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(67)
 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

Möglich wird dies durch das Automatic Diagnostic Repository (ADR), durch das Oracle genau weiß, wo die Log-Dateien liegen und wie sie heißen.

Interessanterweise kann man mittels V$DIAG_ALERT_EXT nicht nur die alert.log-Datei der Datenbank-Instanz abfragen, sondern auch andere Log-Dateien:

SQL> select distinct component_id,filename
  2  from v$diag_alert_ext;

COMPONENT_ID FILENAME
------------ ---------------------------------------------------------------------------
rdbms	     /u00/app/oracle/diag/rdbms/cdbgi/CDBGI/alert/log.xml
clients      /u00/app/oracle/diag/clients/user_oracle/host_2294226451_80/alert/log.xml
tnslsnr      /u00/app/oracle/diag/tnslsnr/gisi/listener/alert/log.xml
asm	     /u00/app/oracle/diag/asm/+asm/+ASM/alert/log.xml

Aber Achtung, wer jetzt probiert, mittels

SQL> select ORIGINATING_TIMESTAMP,message_text
  2  from v$diag_alert_ext
  3  where component_id='rdbms';

no rows selected

das alert.log der Datenbank auszulesen kommt nicht ans Ziel. Obwohl es bei der Spalte COMPONENT_ID keine versteckten Leerzeichen gibt, hilft nur ein „where component_id LIKE ‚%rdbms%'“ oder ein „where TRIM(COMPONENT_ID)=’rdbms'“

SQL> select ORIGINATING_TIMESTAMP,message_text
  2  from v$diag_alert_ext
  3  where component_id like '%rdbms%'
  4  order by originating_timestamp;
  
ORIGINATING_TIMESTAMP			 MESSAGE_TEXT
---------------------------------------- ----------------------------------------------------------------------------------------------------
...
28-APR-14 10.31.02.703000000 AM +02:00	 Starting background process TMON
28-APR-14 10.31.02.718000000 AM +02:00	 TMON started with pid=31, OS id=3009
28-APR-14 10.31.02.807000000 AM +02:00	 Thread 1 advanced to log sequence 11 (thread open)
28-APR-14 10.31.02.847000000 AM +02:00	 Thread 1 opened at log sequence 11
28-APR-14 10.31.02.847000000 AM +02:00	   Current log# 2 seq# 11 mem# 0: +DATA/CDBGI/ONLINELOG/group_2.260.845592309
28-APR-14 10.31.02.847000000 AM +02:00	   Current log# 2 seq# 11 mem# 1: +FRA/CDBGI/ONLINELOG/group_2.257.845592309
28-APR-14 10.31.02.848000000 AM +02:00	 Successful open of redo thread 1
28-APR-14 10.31.02.849000000 AM +02:00	 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
28-APR-14 10.31.02.882000000 AM +02:00	 SMON: enabling cache recovery
28-APR-14 10.31.03.237000000 AM +02:00	 [2979] Successfully onlined Undo Tablespace 2.
28-APR-14 10.31.03.261000000 AM +02:00	 Undo initialization finished serial:0 start:4294908620 end:4294908850 diff:230 ms (0.2 seconds)
28-APR-14 10.31.03.261000000 AM +02:00	 Verifying file header compatibility for 11g tablespace encryption..
28-APR-14 10.31.03.262000000 AM +02:00	 Verifying 11g file header compatibility for tablespace encryption completed
28-APR-14 10.31.03.262000000 AM +02:00	 SMON: enabling tx recovery
28-APR-14 10.31.03.269000000 AM +02:00	 Starting background process SMCO
28-APR-14 10.31.03.284000000 AM +02:00	 SMCO started with pid=33, OS id=3013
28-APR-14 10.31.03.287000000 AM +02:00	 Database Characterset is WE8MSWIN1252
28-APR-14 10.31.03.446000000 AM +02:00	 No Resource Manager plan active
28-APR-14 10.31.03.801000000 AM +02:00	 replication_dependency_tracking turned off (no async multimaster replication found)
28-APR-14 10.31.04.123000000 AM +02:00	 Starting background process AQPC
28-APR-14 10.31.04.136000000 AM +02:00	 AQPC started with pid=34, OS id=3015
28-APR-14 10.31.05.729000000 AM +02:00	 Opening pdb PDB$SEED (2) with no Resource Manager plan active
28-APR-14 10.31.06.449000000 AM +02:00	 db_recovery_file_dest_size of 4800 MB is 3.58% used. This is a
28-APR-14 10.31.06.449000000 AM +02:00	 user-specified limit on the amount of space that will be used by this
28-APR-14 10.31.06.450000000 AM +02:00	 database for recovery-related files, and does not reflect the amount of
28-APR-14 10.31.06.450000000 AM +02:00	 space available in the underlying filesystem or ASM diskgroup.
28-APR-14 10.31.06.946000000 AM +02:00	 Starting background process CJQ0
28-APR-14 10.31.07.000000000 AM +02:00	 Completed: ALTER DATABASE OPEN /* db agent *//* {0:0:2} */

Hilfreich ist auch die Möglichkeit, mittels V$_DIAG_ALERT_EXT, den zeitlichen Ablauf der Meldungen der einzelnen Komponenten mit einem Befehl darzustellen:

SQL> alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';

Session altered.

SQL> select originating_timestamp,component_id,message_text from v$diag_alert_ext order by originating_timestamp; 

ORIGINATING_TIMESTAMP			 COMPONENT_ID MESSAGE_TEXT
---------------------------------------- ------------ ---------------------------------------------------------------
..
28-APR-14 10.30.54.219000000 AM +02:00	 rdbms	      Starting background process ASMB
28-APR-14 10.30.54.265000000 AM +02:00	 rdbms	      ASMB started with pid=20, OS id=2965
28-APR-14 10.30.54.265000000 AM +02:00	 rdbms	      Starting background process MMON
28-APR-14 10.30.54.306000000 AM +02:00	 rdbms	      Starting background process MMNL
28-APR-14 10.30.54.307000000 AM +02:00	 rdbms	      MMON started with pid=21, OS id=2967
28-APR-14 10.30.54.388000000 AM +02:00	 rdbms	      starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
28-APR-14 10.30.54.392000000 AM +02:00	 rdbms	      MMNL started with pid=22, OS id=2969
28-APR-14 10.30.54.432000000 AM +02:00	 rdbms	      NOTE: ASMB registering with ASM instance as client 0xffffffffffffffff (reg:934729091)
28-APR-14 10.30.54.433000000 AM +02:00	 rdbms	      starting up 1 shared server(s) ...
28-APR-14 10.30.54.461000000 AM +02:00	 asm	      NOTE: Standard client CDBGI:CDBGI registered, osid 2971, mbr 0x1 (reg:934729091)
28-APR-14 10.30.54.462000000 AM +02:00	 rdbms	      NOTE: ASMB connected to ASM instance +ASM (Standard mode; client id 0xffffffffffffffff)
28-APR-14 10.30.54.470000000 AM +02:00	 rdbms	      NOTE: initiating MARK startup
28-APR-14 10.30.54.495000000 AM +02:00	 tnslsnr      Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gisi.markusflechtner.local)(PORT=1521)))
28-APR-14 10.30.54.495000000 AM +02:00	 tnslsnr      28-APR-2014 10:30:54 * service_register * CDBGI * 0
28-APR-14 10.30.54.506000000 AM +02:00	 rdbms	      Starting background process MARK
28-APR-14 10.30.54.509000000 AM +02:00	 rdbms	      Instance started by oraagent
28-APR-14 10.30.54.513000000 AM +02:00	 rdbms	      ORACLE_BASE from environment = /u00/app/oracle
28-APR-14 10.30.54.558000000 AM +02:00	 rdbms	      MARK started with pid=25, OS id=2977
28-APR-14 10.30.54.756000000 AM +02:00	 rdbms	      NOTE: MARK has subscribed
28-APR-14 10.30.55.111000000 AM +02:00	 rdbms	      ALTER DATABASE MOUNT /* db agent *//* {0:0:2} */
28-APR-14 10.30.57.477000000 AM +02:00	 tnslsnr      28-APR-2014 10:30:57 * service_update * CDBGI * 0
28-APR-14 10.30.57.887000000 AM +02:00	 rdbms	      NOTE: ASMB mounting group 1 (DATA)
28-APR-14 10.30.57.891000000 AM +02:00	 rdbms	      NOTE: Loaded library: System

Wir können auch alle ORA-Meldungen der letzten Zeit anzeigen lassen (im Beispiel: die letzten 15 Minuten):

SQL> create tablespace ORA_ERROR_TEST datafile '+NONEXISTINGDG';
create tablespace ORA_ERROR_TEST datafile '+NONEXISTINGDG'
*
ERROR at line 1:
ORA-01119: error in creating database file '+NONEXISTINGDG'
ORA-17502: ksfdcre:4 Failed to create file +NONEXISTINGDG
ORA-15001: diskgroup "NONEXISTINGDG" does not exist or is not mounted
ORA-15001: diskgroup "NONEXISTINGDG" does not exist or is not mounted


SQL select originating_timestamp,component_id,message_text from v$diag_alert_ext
  2 where message_text like '%ORA-%' and originating_timestamp>sysdate-1/96
  3 order by originating_timestamp;

ORIGINATING_TIMESTAMP			 COMPONENT_ID MESSAGE_TEXT
---------------------------------------- ------------ ----------------------------------------------------------------------------------------------------
28-APR-14 12.44.11.762000000 PM +02:00	 rdbms	      ORA-1119 signalled during: create tablespace ORA_ERROR_TEST datafile '+NONEXISTINGDG'...

Wie das Beispiel zeigt, wird in der V$DIAG_ALERT_EXT leider nicht der komplette Errorstack dargestellt.

Insgesamt ist die V$DIAG_ALERT_EXT eine hilfreiche View, wenn man ohne Zugriff auf den Server die alert.log-Dateien auslesen möchte. Sie kann den direkten Zugriff auf die Log-Dateien allerdings nicht ersetzen, denn die DB-Instanz muss natürlich laufen, damit man sie nutzen kann. Im Falle eines Instanz-Absturzes hilft also nur der Login auf den Server.