The alert log store information about block corruption errors, internal errors, and the nondefault initialization
parameters used at instance start-up. The alert log also records information about database start-up, shutdown,
archiving, recovery, tablespace modifications, rollback segment modifications, and the data file modifications,
Create, alter and drop SQL statements on databases.
Oracle 11g stores the alert log in two formats, both in the ADR. The Oracle 10g text format is still used. Additionally a second copy of the alert log is maintained formatted with XML tags. The XML tags make it easier for tools to process the alert log more efficiently and gather more detailed information. The XML based alert log is stored in the ADR directory called alert.The text copy of the alert log is in the ADR directory called cdump.
You can access the XML alert log with the XML tags stripped out of it using a tool called the ADR Command Interpreter (adrci). This command-line utility provides quite a bit of functionality including providing the ability to strip out the XML tags from the alert log.Another feature of adrci is the ability to view the alert log using the tail option, as shown in the following example.
u01/oracle/product/db11g/db_01/bin>adrci
Copyright (c) 1982, 2006, Oracle. All rights reserved.
ADR base = “u01/oracle/product”
adrci>>show alert -tail
ADR Home = u01/oracle/product/diag/rdbms/db11g/db11g:
2007-11-02 00:42:47.398000 -06:00
Logminer Bld: Lockdown Complete. DB_TXN_SCN is
UnwindToSCN (LockdownSCN) is 1832443
2007-11-02 00:42:48.929000 -06:00
db_recovery_file_dest_size of 2048 MB is 83.44% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2007-11-02 00:43:45.586000 -06:00
SYS_AUTO_SQL_TUNING_TASK created SQL Profile named
“SYS_SQLPROF_0144bacb369c0000” for sql_id g0jvz8csyrtcf
during execution “EXEC_1_7”.
2007-11-02 00:45:20.916000 -06:00
SYS_AUTO_SQL_TUNING_TASK created SQL Profile named “SYS_SQLPROF_0144bacb94b60001”
for sql_id d89c1mh5pvbkz during execution “EXEC_1_7”.
2007-11-02 00:55:09.970000 -06:00
The Oracle 11g change to the way the alert logs are stored and managed within ADR may pose some interesting problems for the uninitiated. This is because there are numerous ADR_HOMES that potentially could be used. Consider a case where there are two Oracle SIDs on the host running from the same Oracle Home. Each database will be creating it’s own alert log. Since the databases are on the same Oracle Home, you will need to call the adrci command from the same $ORACLE_HOME/bin. When we try to issue the show alert command we run into a problem as seen in this example:
adrci> show alert -tail
DIA-48449: Tail alert can only apply to single ADR home
This error occurs because we have multiple ADR homes (because we have multiple databases) and adrci isn’t sure which one it needs to be looking at. We fix this by first figuring out which ADR homes are available to us by using the show homes command. Then we define the appropriate homepath using the ADRCI command set homepath.
In this example we first find the available homes:
adrci> show homes
ADR Homes:
diag/rdbms/MYDB2/MYDB2
diag/rdbms/MYDB1/MYDB1
diag/asm/+asm/+ASM11
diag/tnslsnr/duke2/listener
Note here the homes ADRCI can access. To see the alert log of the SID MYDB2, you will point ADRCI to that home:
adrci> set homepath diag/rdbms/MYDB2/MYDB2
Now, when you issue show alert, the alert log of the MYDB2 instance is shown.
We can verify the current ADR home by issuing the show homes command again as seen here:
adrci> show homes
ADR Homes:
diag/rdbms/MYDB2/MYDB2
You can change the home at any time by issuing the set homepath command.
To make all this easier, you can save typing the set homepath command every time by creating an ADRCI script. This is a small text file with contents as shown in the following example:
set homepath diag/rdbms/MYDB2/MYDB2
show alert -tail ñf
You save the file and name it something like showmydb2alert.cmd. Having created the script, you can call the script from the adrci command line.
$ adrci ñscript=adrci.cmd
When called in this manner, adrci will execute the commands inside the file automatically.
The listener log is also in XML in Oracle 11g. Just like the alert log then, you will not find a file called listener.log under $OH/network/log. You will find the listener log file in the ADR location $ORACLE_BASE/diag/tnslsnr/duke2/listener/alert/log.xml. Using the adrci utility we can look at that log file too:
adrci> set homepath diag/tnslsnr/duke2/listener
adrci > show alert ñtail -f
11g New Features – Incident packaging using “adrci” utility
As an Oracle DBA, you interface with Oracle support many a times; specially when you are faced with some critical errors such ORA-600 or ORA-7445 etc. And each time you interact with Oracle support, you have to provide quite few logs (alert log, trace files etc.) so that support can assist you in resolving the problem. At times the SR goes back and forth just to additional information – such as providing additional trace files etc. since you may have missed it earlier.
But starting 11g, your life will become a little easy thanks to a new utility called “ADRCI” – ADR Command Interpreter. Now you don’t have to dig around trace files and other files to determine the files that Support would require for analysis. You can simply use “adrci” to package all the files that would be required for analysis with a few simple commands.
Apart from the packaging ability, one can use adrci to :-
a. view alert log (show alert)
b. view other trace files
c. view health check reports
Viewing alert log and trace files is easy but as I said, the best use of this utility is to package incident / problems encountered so that can the packaged file can be easily be uploaded to Oracle support when needed. You do not have to search through trace files and other files to determine the files that are required for analysis.
Here is a quick demonstration of how useful this utility can be when you have to upload files ( required to analyze a specific problem) to Oracle support :-
- Launch adrci
$ adrci
ADRCI: Release 11.1.0.6.0 – Beta on Mon Apr 7 16:11:06 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = “/app/oracle”
- Check the incidents reported in alert log
adrci> show incident
ADR Home = /app/oracle/diag/rdbms/11GLAB:
INCIDENT_ID PROBLEM_KEY CREATE_TIME
21746 ORA 4031 2008-04-07 16:57:11.039525 +05:30
21745 ORA 4031 2008-04-07 16:57:00.356082 +05:30
21715 ORA 4031 2008-04-07 16:57:16.796655 +05:30
21714 ORA 4031 2008-04-07 16:57:07.883365 +05:30
21713 ORA 4031 2008-04-07 16:57:00.694116 +05:30
5 rows fetched
- Identify the specific incident for which you want to create a package so that you can upload it to Oracle support
adrci> IPS CREATE PACKAGE INCIDENT 21713
Created package 2 based on incident id 21713, correlation level typical
This creates a pacakge of the incident 21713 in “incpkg” directory; you can then add diagnostic data to the package.
- Finally generate the package for the incident, which then can be uploaded to metalink while seeking support from Oracle.
adrci> ips generate package 3
Generated package 3 in file /app/oracle/product/11.1.0/db_1/dbs/ORA4031_20080407170431_COM_1.zip, mode complete
- As reported above a zip file has been created with all relevant logs. Now you can upload this zip file to Oracle support and seek their help in resolving your problem.
You can also view the information generated in “incpkg” directory
$cd /app/oracle/diag/rdbms/11GLAB/incpkg/pkg_3/seq_1
$ ls -ltr
-rw-r—– 1 oracle oinstall 499 Apr 7 17:14 metadata.xml
-rw-r—– 1 oracle oinstall 21968 Apr 7 17:14 manifest_3_1.xml
-rw-r—– 1 oracle oinstall 26270 Apr 7 17:14 manifest_3_1.txt
-rw-r—– 1 oracle oinstall 20064 Apr 7 17:14 manifest_3_1.html
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 export
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 crs
-rw-r—– 1 oracle oinstall 62789 Apr 7 17:14 config.xml
We used to achieve the same earlier by running some scripts or collecting the logs/trace files manually. But with adrci, this task is pretty simplified; I think this is surely going to reduce the time to diagnose and resolve any problem.
Automatic Diagnostic Repository
It is introduced in Oracle 11g.Within the ADR, different Oracle components
(such as individual database instances) store data in their own ADR home.
The ADR provides for standardization of the location for files that Oracle is
required to support. This standardized file structure also makes it easy for
Oracle to package these files so that they can be sent to Oracle as a part of
a Service Request. Associated with the ADR is the new diagnostic_dest parameter.
This parameter defines the root of the ADR.The diagnostic_dest parameter deprecates
the user_dump_dest, core_dump_dest, and background_dump_dest parameters. Any
Oracle 11g database will ignore these parameters and will use the diagnostic_
dest parameter. This can be an upgrade issue, because if you do not define the
correct diagnostic destination directory, then the default values will be used,
which may not be your intent. Additionally, if the background_dump_dest parameter
is set, a warning will appear during the startup of the database. The database
will start using the default diagnostic directory location. Additionally, Oracle
will create a small alert log entry in the background_dump_dest location with
just a few lines indicating that the background_dump_dest parameter is obsolete
and indicating the new location where Oracle will be creating the alert log.
The diagnostic_dest parameter defaults to a setting of $ORACLE_BASE. Should $ORACLE_BASE not be set, then diagnostic_dest will default to the value of $ORACLE_HOME. The root directory of the ADR directory structure builds from $ORACLE_BASE with a directory called diag. Under the diag directory is a subdirectory that references the product type, such as rdbms. Specific to the database, under rdbms is a directory for each database, and then a directory for each individual instance. Oracle will create all of these directories for you, except the $ORACLE_BASE directory.
As an example assume $ORACLE_BASE is /u01/oracle and the database name is mydb and that the database is a two node RAC instance. The structure of the ADR directory for that instance will be /u01/oracle/diag/rdbms/mydb/mydb1, and this would be the ADR Home directory for that database instance. Each Oracle component within the ADR infrastructure (instances, ASM, networking) will have it’s own ADR home. ADR supports the use of shared storage if you are using RAC or you can use individual storage on each node. Shared storage in a RAC environment provides the ability to see the aggregate diagnostic data from any node. Also a shared ADR allows for more robust recovery options for the data recovery advisor.
Under the ADR home for a given Oracle component will be a number of other directories. For the Oracle database, some of the most common directories include the following:
alert – This is the location of the XML-formatted alert log..
cdump – This is the location of the core dumps for the database.
trace – This contains trace files generated by the system, as well as a text copy of the alert log.
incident – This directory contains multiple subdirectories, one for each incident.
The figure below provides a diagram of the ADR base structure.
ADR 141.gif
There is a lot of Metadata to be stored with regards to ADR. Each Oracle database (and ASM instance) has a V$DIAG_INFO view that provides information on the various ADR directories and other metadata related to ADR, such as active incidents. Here is an example of a query against the V$DIAG_INFO view:
SQL> SELECT * FROM v$diag_info;
INST_ID NAME VALUE
1 Diag Enabled TRUE
1 ADR Base C:\ORACLE\PRODUCT
1 ADR Home C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g
1 Diag Trace C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\trace
1 Diag Alert C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\alert
1 Diag Incident C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\incident
1 Diag Cdump C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\cdump
1 Health Monitor C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\hm
1 Default Trace File C:\ORACLE\PRODUCT\diag\rdbms\db11g\
db11g\trace\db11g_ora_7832.trc
1 Active Problem Count 1
1 Active Incident Count 111 rows selected.
