Skip to main content
Delphix

Locating the Oracle Alert Log Associated with a Virtual Database Provisioned through Delphix (KBA1354)

 

 

Note

Note:

This topic has been Archived, and is not intended for use in customer installations. 

Information on how to locate the Alert log can be found in KBA1763.

This document is maintained solely for historical reference.

Locating the Oracle Alert Log

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. Locating the alert log can be performed using these same methods.  Various methods are discussed 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.

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

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.

Oracle database parameter background_dump_dest 

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, customizing the alert log location is less common given Oracles practice of centralizing 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

Using ADRCI (Automatic Diagnostic Repository Command Interface) 

The brave new world of ADRCI can be used to locate ORACLE_BASE under which the centralized 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, use the show alert adrci command. By default, this will 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