How to read XML database alert log?

Since Oracle 11g, Oracle maintains two copies of the database’s alertlog in ADR: a flat text file in the sub-directory trace and an XML like in the folder alert. I had a case recently at a customer where the log.xml was moved to another place and compressed for archiving reason. As the regular text file was not containing old data, the goal was to exploit the archived XML -like file.

When the file is still located in its normal location, it’s very easy to read it using the command “show alert” in ADRCI.

oracle@vmtestol6:/home/oracle/ [DB121] adrci
ADRCI: Release 12.1.0.2.0 - Production on Thu Dec 3 16:20:31 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u00/app/oracle"
adrci> show homes
ADR Homes: 
diag/rdbms/db121_site1/DB121
diag/tnslsnr/vmtestol6/listener
adrci> set home diag/rdbms/db121_site1/DB121
adrci> show alert

ADR Home = /u00/app/oracle/diag/rdbms/db121_site1/DB121:
*************************************************************
Output the results to file: /tmp/alert_3268_13985_DB121_1.ado

So ADRCI is able to parse all the <msg> tag and convert it into something readable, there is no need to find a parser.

To avoid loosing information by replacing the current file, it’s not possible to put back the file into its original location.
The trick is to create a temporary diagnostic directory and use ADRCI to view the alertlog.
There is no need to use the same DB name but it’s important to re-create a diagnostic folder hierarchy otherwise you’ll get an error when trying to set the ADR base.

oracle@vmtestol6:/tmp/ [DB121] adrci
ADRCI: Release 12.1.0.2.0 - Production on Thu Dec 3 22:21:52 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u00/app/oracle"
adrci> set base /tmp
DIA-48447: The input path [/tmp] does not contain any ADR homes

Let’s create the hierarchy expected by ADRCI:

oracle@vmtestol6:/tmp/ [DB121] ls -l log_20151203.zip
-rw-r--r--. 1 oracle oinstall 162357  3 déc.  16:28 log_20151203.zip
oracle@vmtestol6:/tmp/ [DB121] mkdir -p diag/rdbms/db1/db1/alert
oracle@vmtestol6:/tmp/ [DB121] unzip log_20151203.zip -d diag/rdbms/db1/db1/alert
Archive:  log_20151203.zip
  inflating: diag/rdbms/db1/db1/alert/log.xml  
oracle@vmtestol6:/tmp/ [DB121] adrci
ADRCI: Release 12.1.0.2.0 - Production on Thu Dec 3 17:04:27 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/tmp"
adrci> show alert
...
2015-12-02 16:00:26.469000 +01:00
Instance shutdown complete
:w alert_DB121.log
"alert_DB121.log" [New] 7676L, 321717C written

Then it’s easy to save the file back to a flat text format! ADRCI also allows to run some commands to look for error and so on…