How to Find Alert Log File Location in Oracle

In newer versions of Oracle Database it is not obvious where is the location of the alert file to investigate any potential issues.

If you want to know how to check alert log errors in Oracle, then keep reading.

You have a couple of ways to know where your Oracle alert file is located.

1. If the Oracle database is opened

You can use this query after connecting with: sqlplus / as sysdba

select * 
from v$diag_info 
where NAME = 'Diag Trace';

2. If the Oracle database is not opened

You can use adrci from the command line

# adrci

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/yp001p/YP001P1
diag/rdbms/azamoa/azamoa1
diag/clients/user_oracle/host_2133002082_110
diag/tnslsnr/xm1iddb21/listener_dg
adrci>

You can see there are several homes.

If you want to check the alert file for database YP001P1 then you should use the ADR base followed by the ADR home (from the output above) and then add the trace folder at the end

cd /u01/app/oracle/diag/rdbms/yp001p/YP001P1/trace

Once in that folder you can check the alert file

ls -lrt alert*

How to Find Primary Database From Standby in Oracle

If you have an Oracle data guard and you want to know the role of each database then you can follow this guide to get more details about the environment.

You can run these queries in the primary or the standby database indistinctively.

This first query gives a general overview of both primary and standy databases.

set lines 132
column current_scn format 99999999999999999999

select * from v$dataguard_config;

DB_UNIQUE_NAME                 PARENT_DBUN                    DEST_ROLE                   CURRENT_SCN     CON_ID
------------------------------ ------------------------------ ----------------- --------------------- ----------
XT001NFB                       NONE                           PRIMARY DATABASE          4686633244816          0
XT001NSB                       XT001NFB                       PHYSICAL STANDBY          4686633244861          0

If you want to know the role of the database you are connected to:

select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
XT001NFB  READ WRITE           PRIMARY

If you want to get even more info, then show this parameter:

show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(XT001NSB,XT001NFB)

If you have data guard broker installed for your data guard (which I recommend), then you can check the configuration like that.

dgmgrl /

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - DG_XT001

  Protection Mode: MaxAvailability
  Members:
  XT001NFB - Primary database
    XT001NSB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

I hope this was helpful.