Lesson 4	Finding the alert log
Objective	Locate the text file containing the alert log for your database.

Finding Oracle alert log

To locate the alert log file for an Oracle 11g R2 database running on **Ubuntu 24.10**, follow these steps:

Identify the Diagnostic Destination** Oracle 11g R2 uses the **Automatic Diagnostic Repository (ADR)** to store diagnostic information, including the alert log. The alert log is located in the `trace` directory under the database's diagnostic destination.
Steps:
1. Connect to your database as a privileged user (e.g., sysdba).
```
      sqlplus / as sysdba
    
```
2. Query the diagnostic destination using the following SQL command:
```
      SHOW PARAMETER diagnostic_dest;

      Example output:

      NAME                TYPE    VALUE
      -------------------- ------- -------------------------
      diagnostic_dest     string  /u01/app/oracle
    
```
  The value (/u01/app/oracle) is the root directory for the ADR.
Locate the Alert Log File** The alert log is stored in the `trace` directory of the ADR path for your database instance. Follow these steps:
1. Navigate to the ADR base directory:
```
      cd /u01/app/oracle/diag/rdbms/<db_name>/<instance_name>/trace
    
```
  Replace <db_name> with your database name and <instance_name> with your instance name.
2. Look for the alert log file, typically named:
```
      alert_<db_name>.log
    
```
  Example:
```
      alert_orcl.log
    
```
3. View the alert log:
```
      less alert_<db_name>.log
    
```
If Diagnostic Destination is Not Set** If the `DIAGNOSTIC_DEST` parameter is not set, Oracle defaults to the `$ORACLE_BASE` directory. - Example default location:
```
  /u01/app/oracle/admin//bdump/alert_.log
  
```
Using ADRCI to Locate the Alert Log** The **ADR Command Interpreter (ADRCI)** is a tool for managing and querying diagnostic files, including the alert log. # Steps:
1. Open ADRCI:
```
      adrci
    
```
2. Set the ADR base path:
```
      set base /u01/app/oracle
    
```
3. List available homes to find your database instance:
```
      show homes
    
```
  Example output:
```
      diag/rdbms/orcl/orcl
    
```
4. Set the home path:
```
      set home diag/rdbms/<db_name>/<instance_name>
    
```
5. View the alert log:
```
      show alert -tail
    
```
  To see the entire alert log:
```
      show alert -p "MESSAGE_TEXT LIKE '%'"
    
```
Verify Permissions: Ensure that the user running Oracle has the necessary permissions to access the alert log file. If you're unable to locate the file, verify that the database is running and you have the correct paths.

Oracle writes the alert log in the directory pointed to by the

background_dump_dest

initialization parameter. On UNIX systems and Windows systems running Oracle 8.1 or later, that directory is under the admin directory tree. The following is a typical path to the alert log on a UNIX system:

$ORACLE_BASE/admin/sid_name/bdump

Replace sid_name with the name of the Oracle instance.
To find out with certainty which directory to look in, connect using SQL*Plus and issue a

SHOW PARAMETER background_dump_dest

command. Here's an example:

SQL> show parameter background_dump_dest

NAME                 TYPE    VALUE

-------------------- ------- ------------------------------

background_dump_dest string  E:\Oracle\admin\jonathan\bdump

The alert log filename always includes the SID name, and takes the following form:

sid_nameALRT.LOG

The alert log for the COIN database, for example, will be in the file named COINALRT.LOG.
In the next lesson, you will identify the different types of content in the alert log.