Skip to main content
Delphix

KBA1354 How do you locate the Oracle Alert Log associated with a Virtual Database provisioned through Delphix?

 

Goal

There are a number of ways you can locate the Oracle alert log associated with an Oracle Database.  Virtual databases provisioned through Delphix's Dynamic Data Platform behave in exactly the same way as any other Oracle database from this perspective and locating the alert log can be performed using these same methods.  Various methods are discussed in this knowledge base article so Delphix Dynamic Data Platform Administrators can locate the Oracle VDB alert log to assist in troubleshooting database specific issues occurring in a Delphix provisioned VDB.

Option 1 - Using ORACLE_BASE

The VDB alert log does not depend on the database being available and it is not stored within Delphix.  It should be located by default in the $ORACLE_BASE/diag or the diagnostics destination predetermined by Oracle itself in Oracle 11.2 and higher. The environment variable ORACLE_BASE is set whenever an OS users environment is set through the Oracle utility oraenv. This method can be used even in cases where the Delphix VDB is not enabled or running.

For Example:

If you set your Oracle user's environment using oraenv on the node running the VDB the ORACLE_BASE environment variable would be set for you. This is dependent on an entry existing for the ORACLE_HOME used by the VDB in /etc/oratab or /var/opt/oracle/oratab depending on the OS platform in play.

[oracle@oelvbc1n1 trace]$ cat /etc/oratab | grep db112
db112:/u01/app/oracle/product/11.2.0/dbhome_1:N  # line added by Agent

Using the entry db112 which uses the same ORACLE_HOME as my VDB, vdb1, the location of the alert log will be

$ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<INSTANCE_NAME>/trace/alert_<INSTANCE_NAME>.log

[oracle@oelvbc1n1 trace]$ . oraenv
ORACLE_SID = ? db112
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oelvbc1n1]$ echo $ORACLE_BASE
/u01/app/oracle

[oracle@oelvbc1n1 vdb1]$ cd $ORACLE_BASE/diag/rdbms/vdb1/vdb1/trace

[oracle@oelvbc1n1 trace]$ ls -l alert*
-rw-r-----. 1 oracle asmadmin 175920 Jan 11 03:00 alert_vdb1.log

Option 2 - Dynamic Performance View v$diag_info

In cases where the VDB is up and running the alert log location can be determined through querying the Oracle dynamic performance view v$diag_info

For Example:

SQL> set lines 200
SQL> col name format a10
SQL> col value format a60
SQL> select inst_id,name,value from v$diag_info where name = 'Diag Trace';

   INST_ID NAME       VALUE
---------- ---------- ---------------------------------------------------
     1     Diag Trace /u01/app/oracle/diag/rdbms/vdb1/vdb1/trace

The alert log would be located in this example in /u01/app/oracle/diag/rdbms/vdb1/vdb1/trace and named alert_vdb1.log.

Option 3 - The Oracle database parameter background_dump_dest

In releases prior to Oracle 11g the location of the alert log is dictated by the database parameter background_dump_dest.  This parameter still exists in 11.2 and later releases as well however customising the alert log location is less common given Oracles practice of centralising and managing its diagnostics collection points. In VDB's running releases older than Oracle RDBMS 12.1 it can still be used to locate the alert log if the VDB is available and running.

For Example:

SQL> show parameter background_dump_dest
NAME                                  TYPE     VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest      string     /u01/app/oracle/diag/rdbms/vdb1/vdb1/trace

Option 4 - The brave new world of ADRCI (Automatic Diagnostic Repository Command Interface)

To Locate ORACLE_BASE under which the centralised diagnostics collection is placed.

For Example:

adrci> show base
ADR base is "/u01/app/oracle"

To locate the diagnostics location for a specific VDB

adrci> show homes
ADR Homes: 
diag/rdbms/vdb1/vdb1

To examine the alert log contents perform use the show alert adrci command. This will by default jump to the start of the alert log.

adrci> show alert

ADR Home = /u01/app/oracle/diag/rdbms/vdb1/vdb1:
*************************************************************************
Output the results to file: /tmp/alert_27093_1401_vdb1_2.ad

2018-01-08 16:30:11.654000 +11:00
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Shared memory segment for instance monitoring created
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: non-NUMA system
    cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
    Grp 0:
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

There are many more commands available to ADRCI for managing alert logs and trace files associated with the VDB, far too many options to go into in this article.  For a complete reference on adrci please consult the Oracle Documentation at:

https://docs.oracle.com/cd/E11882_01/server.112/e22490/adrci.htm#SUTIL700