Locating the Oracle Alert Log Associated with a Virtual Database Provisioned through Delphix (KBA1354)
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.
- ORACLE_BASE
- Dynamic Performance View v$diag_info
- Oracle database parameter background_dump_dest
- ADRCI
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