Utiliser SQL pour lire le fichier ALERT.log - Use SQL to read the ALERT.log file

3 mars 2019

Utiliser SQL pour lire le fichier ALERT.log - Use SQL to read the ALERT.log file

Introduction
Le fichier dit ALERT.log est celui où un DBA doit aller en premier lorsqu'un problème sérieux survient sur une base. C'est ici que sont enregistrés les arrêts/relances de la base, la valeur des paramètres de l'instance qui ne sont pas ceux par défaut, toutes les opérations sur la base comme l'ajout d'un tablespace, la suppression d'un fichier de données, les erreurs internes Oracle de type ora-00600, les deadlocks et autres erreurs du SGBD; bref c'est le passage obligé en cas de coup dur!

Ce fichier existe dorénavant en deux exemplaires, l'un au format texte pour une rétro-compatibilité avec les anciennes versions d'Oracle et un nouveau au format xml. Le problème est que, si ce fichier est vital aux DBA, il leur est parfois inaccessible car il n'est pas géré directement par Oracle mais par l'OS; par exemple pas de compte pour se connecter sur le serveur Linux et lire ce fichier ou bien connexion possible au serveur mais pas de droit de lecture sur ce fichier, ou bien, c'est le pompom, serveur Linux géré dans le Cloud et donc aucun DBA ne peut s'y connecter.

En passant par des outils comme le Cloud Control ou Toad, il est possible de voir le contenu de ce fichier via une interface graphique mais, disons-le, c'est parfois lent et les recherches ne sont pas aussi puissantes que sous Linux.

Dieu merci, Oracle a multiplié les accès à ce fichier vital pour les DBA et maintenant on peut y accéder :
     1) directement depuis l'OS (Unix, Linux...)
     2) via des outils graphiques (Cloud Control, Toad...)
     3) via SQL*Plus et des objets (synonymes, vues) qui, je pense, sont utilisés par les deux outils graphiques ci-dessus : le synonyme ALERT_LOG, la vue dynamique de performances V$DIAG_ALERT_EXT et les structures mémoires X$DBGALERTEXT et X$DIAG_ALERT_EXT
4) via le package UTL_FILE

Accéder à ce fichier via les objets Oracle a un avantage incomparable par rapport aux autres méthodes : l'utilisation du langage SQL! En requêtant son contenu, on peut faire des Group By par date, des sommes des types d'erreurs etc etc :-)

Tiens, petite question, est-ce que Oracle utilise en interne une table externe pour lire ce fichier? A priori non, comme nous allons le voir plus bas, il utilise des structures mémoires X$ mais les programmes les remplissant passent-ils par une table externe, qui est la solution offerte par Oracle pour accéder, via le SGBD, au contenu d'un fichier de l'OS?

Dans la suite de cet article, j'utiliserai le terme ALERT.log mais les fichiers ont en réalité un nom différent : log.xml ou alert_<sid>.log.

Points d'attention
Aucun.

Base de tests
N'importe quelle base Oracle.

Exemples
============================================================================================
Les fichiers ALERT.log
============================================================================================
Le fichier ALERT au format xml
Le nouveau fichier ALERT, au format xml, se trouve dans le répertoire identifié par la ligne "Diag Alert" de la vue V$DIAG_INFO.
     SQL> select name, value from v$diag_info order by name;
     NAME                   VALUE
     ------------------------------ --------------------------------------
     ADR Base               /u01/app/oracle
     ADR Home               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c
     Active Incident Count           0
     Active Problem Count           0
     Default Trace File           /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4316.trc
     Diag Alert               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/alert
     Diag Cdump               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/cdump
     Diag Enabled               TRUE
     Diag Incident               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/incident
     Diag Trace               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace
     Health Monitor               /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm

     11 rows selected.

Le fichier s'appelle curieusement log.xml et pas alert_<sid>.xml.
     SQL> host ls -l /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/alert/*xml
     -rw-r----- 1 oracle oinstall 4296632 Feb 26 09:22 /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/alert/log.xml

Voyons son contenu (j'ajoute un saut de ligne entre chaque message).
C'est bien un fichier xml, avec ses multiples balises. Il y a le démarrage de la base : c'est donc bien notre fichier d'alerte.
     SQL> host more /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/alert/log.xml
     <msg time='2017-03-02T07:50:58.684-05:00' org_id='oracle' comp_id='rdbms'
      msg_id='opistr_real:1184:2538814769' type='NOTIFICATION' group='startup'
      level='16' host_id='vbgeneric' host_addr='127.0.0.1'
      pid='7940' version='1' con_uid='1'
      con_id='1' con_name='CDB$ROOT'>
      <txt>Starting ORACLE instance (normal) (OS id: 7940)
      </txt>
     </msg>

     <msg time='2017-03-02T07:50:58.686-05:00' org_id='oracle' comp_id='rdbms'
      type='UNKNOWN' level='16' host_id='vbgeneric'
      host_addr='127.0.0.1' pid='7940' con_uid='1'
      con_id='1' con_name='CDB$ROOT'>
      <txt>CLI notifier numLatches:3 maxDescs:519
      </txt>
     </msg>

     <msg time='2017-03-02T07:50:58.689-05:00' org_id='oracle' comp_id='rdbms'
      type='UNKNOWN' level='16' host_id='vbgeneric'
      host_addr='127.0.0.1' pid='7940' con_uid='1'
      con_id='1' con_name='CDB$ROOT'>
      <txt>**********************************************************************
      </txt>
     </msg>

     <msg time='2017-03-02T07:50:58.689-05:00' org_id='oracle' comp_id='rdbms'
      type='UNKNOWN' level='16' host_id='vbgeneric'
      host_addr='127.0.0.1' pid='7940' con_uid='1'
      con_id='1' con_name='CDB$ROOT'>
      <txt>Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
      </txt>
     </msg>

Le fichier ALERT au format texte
Le vieux fichier au format texte est dans le répertoire identifié par la ligne "Diag Trace"; allez savoir pourquoi puisque c'est le répertoire des traces.
Lui s'appelle bien alert_<sid>.log. On notera qu'il est moins complexe à lire que le fichier xml mais qu'il reprend bien les dates, heures et la balise "txt" de celui-ci. Nous avons donc bien identifié les bons fichiers.
     SQL> ! ls -l /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/*log
     -rw-r----- 1 oracle oinstall 929829 Feb 26 09:22 /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/alert_orcl12c.log

Quel est son contenu?
     SQL> ! more /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/alert_orcl12c.log
     2017-03-02T07:50:58.684102-05:00
     Starting ORACLE instance (normal) (OS id: 7940)
     2017-03-02T07:50:58.686507-05:00
     CLI notifier numLatches:3 maxDescs:519
     2017-03-02T07:50:58.689266-05:00
     **********************************************************************
     2017-03-02T07:50:58.689327-05:00
     Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

     2017-03-02T07:50:58.689543-05:00
      Per process system memlock (soft) limit = 128G
     2017-03-02T07:50:58.689585-05:00
      Expected per process system memlock (soft) limit to lock
      SHARED GLOBAL AREA (SGA) into memory: 802M
     2017-03-02T07:50:58.689651-05:00
      Available system pagesizes:
       4K, 2048K
     2017-03-02T07:50:58.689713-05:00
      Supported system pagesize(s):
     2017-03-02T07:50:58.689746-05:00
       PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
     2017-03-02T07:50:58.689780-05:00
             4K       Configured               4          204804        NONE
     2017-03-02T07:50:58.689887-05:00
          2048K                0             401               0        NONE
     2017-03-02T07:50:58.689921-05:00
     RECOMMENDATION:
     2017-03-02T07:50:58.689963-05:00
      1. For optimal performance, configure system with expected number
      of pages for every supported system pagesize prior to the next
      instance restart operation.
     2017-03-02T07:50:58.694507-05:00
     **********************************************************************
     LICENSE_MAX_SESSION = 0
     LICENSE_SESSIONS_WARNING = 0
     Initial number of CPU is 2
     Number of processor cores in the system is 2

============================================================================================
Les objets pour requêter avec SQL le fichier ALERT.log
============================================================================================
La vue V$DIAG_ALERT_EXT
Oracle met à notre disposition plusieurs objets pour accéder au fichier ALERT.log.
En premier, la vue dynamique de performance (en vrai un synonyme) : V$DIAG_ALERT_EXT avec de très nombreuses colonnes.
     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)

Ce synonyme pointe vers la vue V_$DIAG_ALERT_EXT qui elle même pointe vers la structure mémoire X$DIAG_ALERT_EXT.
     SQL> select SYNONYM_NAME, OWNER, TABLE_NAME from dba_synonyms where SYNONYM_NAME = 'V$DIAG_ALERT_EXT';
     SYNONYM_NAME   OWNER   TABLE_NAME
     ---------------------------------------------------
     V$DIAG_ALERT_EXT PUBLIC V_$DIAG_ALERT_EXT

     SQL> select VIEW_NAME, OWNER, TEXT_VC from dba_views where VIEW_NAME = 'V_$DIAG_ALERT_EXT';
VIEW_NAME OWNER TEXT_VC
     --------------------------------------------------------------------------------------------------
V_$DIAG_ALERT_EXT SYS select "ADDR","INDX","INST_ID","CON_ID","ADR_PATH_IDX","ADR_HOME","ORIGINATING_TIMESTAMP","NORMALIZED_TIMESTAMP", "ORGANIZATION_ID", "COMPONENT_ID", "HOST_ID", "HOST_ADDRESS", "MESSAGE_TYPE", "MESSAGE_LEVEL", "MESSAGE_ID", "MESSAGE_GROUP", "CLIENT_ID","MODULE_ID","PROCESS_ID","THREAD_ID","USER_ID","INSTANCE_ID","DETAILED_LOCATION","UPSTREAM_COMP_ID", "DOWNSTREAM_COMP_ID","EXECUTION_CONTEXT_ID","EXECUTION_CONTEXT_SEQUENCE","ERROR_INSTANCE_ID","ERROR_INSTANCE_SEQUENCE", "MESSAGE_TEXT","MESSAGE_ARGUMENTS","SUPPLEMENTAL_ATTRIBUTES","SUPPLEMENTAL_DETAILS","PARTITION","RECORD_ID","FILENAME", "LOG_NAME","PROBLEM_KEY","VERSION","CON_UID","CONTAINER_ID","CONTAINER_NAME" from x$diag_ALERT_EXT

     SQL> desc x$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)


Le synonyme ALERT_LOG
Autre objet, le synonyme ALERT_LOG, qui pointe vers X$DBGALERTEXT.
     SQL> select object_name, object_type, owner from dba_objects where object_name ='ALERT_LOG' order by 1;
     OBJECT_NAME OBJECT_TYPE OWNER
     -------------------------------------------------------
     ALERT_LOG SYNONYM PUBLIC

     SQL> select SYNONYM_NAME, TABLE_NAME from dba_synonyms where SYNONYM_NAME = 'ALERT_LOG';
     SYNONYM_NAME   TABLE_NAME
     ------------------------------------
ALERT_LOG X$DBGALERTEXT

C'est bizarre mais sa structure ressemble fortement à X$DIAG_ALERT_EXT : Oracle aurait créé deux objets identiques pour le même besoin? Peut-être pour des raisons historiques...
     SQL> desc X$DBGALERTEXT
      Name                                                           Null?    Type
      ------------------------------------------------------------------------------
      ADDR                                                                RAW(8)
      INDX                                                                NUMBER
      INST_ID                                                            NUMBER
      CON_ID                                                             NUMBER
      ORIGINATING_TIMESTAMP                                              TIMESTAMP(3) WITH TIME ZONE
      NORMALIZED_TIMESTAMP                                               TIMESTAMP(3) WITH TIME ZONE
      ORGANIZATION_ID                                                        VARCHAR2(64)
      COMPONENT_ID                                                            VARCHAR2(64)
      HOST_ID                                                            VARCHAR2(64)
      HOST_ADDRESS                                                            VARCHAR2(46)
      MESSAGE_TYPE                                                            NUMBER
      MESSAGE_LEVEL                                                            NUMBER
      MESSAGE_ID                                                            VARCHAR2(64)
      MESSAGE_GROUP                                                            VARCHAR2(64)
      CLIENT_ID                                                            VARCHAR2(64)
      MODULE_ID                                                            VARCHAR2(64)
      PROCESS_ID                                                            VARCHAR2(32)
      THREAD_ID                                                            VARCHAR2(64)
      USER_ID                                                            VARCHAR2(128)
      INSTANCE_ID                                                            VARCHAR2(64)
      DETAILED_LOCATION                                                        VARCHAR2(160)
      PROBLEM_KEY                                                            VARCHAR2(550)
      UPSTREAM_COMP_ID                                                        VARCHAR2(100)
      DOWNSTREAM_COMP_ID                                                        VARCHAR2(100)
      EXECUTION_CONTEXT_ID                                                        VARCHAR2(100)
      EXECUTION_CONTEXT_SEQUENCE                                                    NUMBER
      ERROR_INSTANCE_ID                                                        NUMBER
      ERROR_INSTANCE_SEQUENCE                                                    NUMBER
      VERSION                                                            NUMBER
      MESSAGE_TEXT                                                            VARCHAR2(2048)
      MESSAGE_ARGUMENTS                                                        VARCHAR2(512)
      SUPPLEMENTAL_ATTRIBUTES                                                    VARCHAR2(512)
      SUPPLEMENTAL_DETAILS                                                        VARCHAR2(4000)
      PARTITION                                                            NUMBER
      RECORD_ID                                                            NUMBER
      CON_UID                                                            NUMBER
      CONTAINER_NAME                                                         VARCHAR2(30)

     SQL> select count(*) from X$DBGALERTEXT;
       COUNT(*)
     ----------
           1010

============================================================================================
Utiliser le langage SQL sur le fichier ALERT.log
============================================================================================
On voit que V$DIAG_ALERT_EXT et ALERT_LOG pointent vers le même fichier d'alertes : les données sont identiques.
SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT where rownum <= 21;
     ORIGINATING_TIMESTAMP MESSAGE_TEXT
     ------------------------------------------------------------------------------------------
     02-MAR-17 07.57.32.623000000 AM -05:00   Endian type of dictionary set to little
     02-MAR-17 07.57.33.677000000 AM -05:00   Autotune of undo retention is turned on.
     02-MAR-17 07.57.34.468000000 AM -05:00   [9602] Successfully onlined Undo Tablespace 2.
     02-MAR-17 07.57.34.488000000 AM -05:00   Undo initialization finished serial:0 start:1055349 end:1055538 diff:189 ms (0.2 seconds)
     02-MAR-17 07.57.34.524000000 AM -05:00   Database Characterset for ORCL is AL32UTF8
     02-MAR-17 07.57.34.621000000 AM -05:00   JIT: pid 9602 requesting stop
     02-MAR-17 07.57.35.591000000 AM -05:00   Autotune of undo retention is turned on.
     02-MAR-17 07.57.35.673000000 AM -05:00   Endian type of dictionary set to little
     02-MAR-17 07.57.36.421000000 AM -05:00   [9602] Successfully onlined Undo Tablespace 2.
     02-MAR-17 07.57.36.439000000 AM -05:00   Undo initialization finished serial:0 start:1057031 end:1057490 diff:459 ms (0.5 seconds)
     02-MAR-17 07.57.36.544000000 AM -05:00   Deleting old file#5 from file$
     02-MAR-17 07.57.36.545000000 AM -05:00   Deleting old file#6 from file$
     02-MAR-17 07.57.36.547000000 AM -05:00   Deleting old file#8 from file$
     02-MAR-17 07.57.36.564000000 AM -05:00   Adding new file#9 to file$(old file#5)
     02-MAR-17 07.57.36.575000000 AM -05:00   Adding new file#10 to file$(old file#6)
     02-MAR-17 07.57.36.576000000 AM -05:00   Adding new file#11 to file$(old file#8)
     02-MAR-17 07.57.37.031000000 AM -05:00   Successfully created internal service orcl at open
     02-MAR-17 07.57.37.748000000 AM -05:00   Database Characterset for ORCL is AL32UTF8
     02-MAR-17 07.57.38.412000000 AM -05:00   Opatch validation is skipped for PDB ORCL (con_id=0)
     02-MAR-17 07.57.42.969000000 AM -05:00   Opening pdb with no Resource Manager plan active
     02-MAR-17 07.57.45.945000000 AM -05:00   Resize operation completed for file# 10, old size 337920K, new size 348160K
     21 rows selected.


     SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from ALERT_LOG where rownum <= 21;
     ORIGINATING_TIMESTAMP           MESSAGE_TEXT
     ------------------------------------------------------------------------------------
     02-MAR-17 07.57.32.623 AM -05:00   Endian type of dictionary set to little
     02-MAR-17 07.57.33.677 AM -05:00   Autotune of undo retention is turned on.
     02-MAR-17 07.57.34.468 AM -05:00   [9602] Successfully onlined Undo Tablespace 2.
     02-MAR-17 07.57.34.488 AM -05:00   Undo initialization finished serial:0 start:1055349 end:1055538 diff:189 ms (0.2 seconds)
     02-MAR-17 07.57.34.524 AM -05:00   Database Characterset for ORCL is AL32UTF8
     02-MAR-17 07.57.34.621 AM -05:00   JIT: pid 9602 requesting stop
     02-MAR-17 07.57.35.591 AM -05:00   Autotune of undo retention is turned on.
     02-MAR-17 07.57.35.673 AM -05:00   Endian type of dictionary set to little
     02-MAR-17 07.57.36.421 AM -05:00   [9602] Successfully onlined Undo Tablespace 2.
     02-MAR-17 07.57.36.439 AM -05:00   Undo initialization finished serial:0 start:1057031 end:1057490 diff:459 ms (0.5 seconds)
     02-MAR-17 07.57.36.544 AM -05:00   Deleting old file#5 from file$
     02-MAR-17 07.57.36.545 AM -05:00   Deleting old file#6 from file$
     02-MAR-17 07.57.36.547 AM -05:00   Deleting old file#8 from file$
     02-MAR-17 07.57.36.564 AM -05:00   Adding new file#9 to file$(old file#5)
     02-MAR-17 07.57.36.575 AM -05:00   Adding new file#10 to file$(old file#6)
     02-MAR-17 07.57.36.576 AM -05:00   Adding new file#11 to file$(old file#8)
     02-MAR-17 07.57.37.031 AM -05:00   Successfully created internal service orcl at open
     02-MAR-17 07.57.37.748 AM -05:00   Database Characterset for ORCL is AL32UTF8
     02-MAR-17 07.57.38.412 AM -05:00   Opatch validation is skipped for PDB ORCL (con_id=0)
     02-MAR-17 07.57.42.969 AM -05:00   Opening pdb with no Resource Manager plan active
     02-MAR-17 07.57.45.945 AM -05:00   Resize operation completed for file# 10, old size 337920K, new size 348160K

L'intérêt évident d'utiliser SQL pour interroger le fichier d'alertes, c'est la puissance de ce langage. Par exemple, si je veux voir les messages les plus fréquents dans ce fichier.
     SQL> select MESSAGE_TEXT, count(*) from V$DIAG_ALERT_EXT group by MESSAGE_TEXT having count(*) > 2 order by count(*) DESC;
     MESSAGE_TEXT                                                   COUNT(*)
     -----------------------------------------------------------------------------------------------
     Endian type of dictionary set to little                                      63
     Database Characterset for ORCL is AL32UTF8                                     63
     Autotune of undo retention is turned on.                                     63
     Opening pdb with no Resource Manager plan active                                 62
     Opatch validation is skipped for PDB ORCL (con_id=0)                                 62
     ALTER SYSTEM: Flushing buffer cache inst=0 container=3 global                             33
     Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter                         23
     qmhGetHTTPError:XML DB HTTP Server: Sending error response(HTTP error code = 401)                 19
     Closing Resource Manager plan via scheduler window                                 18
     Clearing Resource Manager plan via parameter                                     18
     Closing scheduler window                                             18
     ORA-1653: unable to extend table HR.ZZTEST_STRINGS by 128 in tablespace TEST_STRINGS [ORCL]             15
     Setting Resource Manager plan SCHEDULER[0x4AC3]:DEFAULT_MAINTENANCE_PLAN via scheduler window             12
     XDB installed.                                                     12
     CREATE TABLESPACE TEST_STRINGS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/test_strings.dbf' size         11
      10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

     XDB initialized.                                                 11
     Setting Resource Manager plan SCHEDULER[0x4AC2]:DEFAULT_MAINTENANCE_PLAN via scheduler window             10
     *****************************************************************                         10
     Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_s000_12853.trc:               9
     ORA-00942: table or view does not exist

     Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_s001_13375.trc:               8
     ORA-00942: table or view does not exist

     Completed: CREATE TABLESPACE TEST_STRINGS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/test_string          8
     s.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

     This usually happens following a specific internal error, when                              5
     Completed: drop tablespace TEST_STRINGS INCLUDING CONTENTS                              5
     redo dumps are required to help with the diagnosis.                                  5
     diagnosis.                                                      5
     analysis of the redo logs will help Oracle Support with the                              5
     all the instances) during the past 12 hours, in case additional                           5
     It is recommended that you retain all the redo logs generated (by                          5
     An internal routine has requested a dump of selected redo.                              5
     drop tablespace TEST_STRINGS INCLUDING CONTENTS                                   5
     Completed: ALTER PLUGGABLE DATABASE OPEN                                      4
     ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE                                      4
     ALTER PLUGGABLE DATABASE OPEN                                              4
     Completed: ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE                                  3
34 rows selected.

Autre exemple, avoir par jour le nombre d'entrée dans ce fichier, pour identifier quel jour la base a rencontré plus de problèmes que d'autres ou d'arrêts/relances.
     SQL> select to_char(ORIGINATING_TIMESTAMP,'DD/MM/YYYY'), count(*) from V$DIAG_ALERT_EXT group by to_char(ORIGINATING_TIMESTAMP,'DD/MM/YYYY') having count(*) > 1 order by count(*) DESC;
     TO_CHAR(OR   COUNT(*)
     ---------- ----------
     02/03/2017      328
     16/09/2018       78
     15/09/2018       49
     16/11/2018       37
     28/10/2018       34
     18/08/2018       34
     23/12/2018       26
     02/12/2018       25
     09/02/2019       21
     18/09/2018       20
     20/12/2018       18
     27/01/2019       17
     09/09/2018       16
     26/02/2019       14
     09/12/2018       14
     22/12/2018       13
     12/09/2018       13
     06/12/2018       13
     23/09/2018       13
     24/12/2018       13
     01/11/2018       12
     ...

X$ et user SYS
Dernier point, n'oubliez pas que les objets X$xxx ne sont accessibles qu'à SYS, et même pas aux users avec le rôle DBA. C'est pourquoi Oracle a créé les objets vus ci-dessus, pour que n'importe qui puisse y accéder car, sauf erreur de ma part, ils appartiennent au schéma PUBLIC.
     SQL> show user
     USER is "HR"

     SQL> select * from session_roles;
     ROLE
     ---------------------------
     DBA
     SELECT_CATALOG_ROLE
     EXECUTE_CATALOG_ROLE
     ...
     22 rows selected.

     SQL> desc X$DBGDIREXT
     ERROR:
     ORA-04043: object X$DBGDIREXT does not exist

     SQL> desc SYS.X$DBGDIREXT
     ERROR:
     ORA-04043: object SYS.X$DBGDIREXT does not exist