select * from alert.log – die View V$DIAG_ALERT_EXT
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.