Log Review and Response Guide When Trouble Occurs

When an issue hits your oracle environment, the quickest recoveries start with a fixed plan for which logs to check and in what order. This article targets 19c on Oracle Linux and organizes must-see log locations, how to read them, and typical causes/mitigations—with command examples and concrete path examples included.

Big picture (lock in your “check order” first)
1. Quick “must-use” commands
The log order to check first in an oracle environment
Typical “cause ↔ remedy” crib notes
1. UNDO checks/tuning (entry point for ORA-01555)
Handy helper SQL for operations (safe reads only)
Appendix: Lab-only user (and notes on IDENTITY columns)
1. Appendix: Practice table (to keep investigation notes)
  1. (Supplement) If you still get ORA-01031 when creating a table
Reference: frequently used directories (19c defaults + concrete examples)
Summary

Big picture (lock in your “check order” first)

[Incident detected]
     │  (monitoring / user inquiry / app exception)
     ▼
【1】Check the Alert Log immediately (recent events overview)
     │→ Any critical/repeated/ORA- errors?
     ▼
【2】Drill into the related process/session trace
     │→ Call stack, SQL, runtime info
     ▼
【3】Use ADRCI to list incident/problem
     │→ Get a full picture and prepare SR materials
     ▼
【4】For connectivity, also check the listener log
     │→ TNS-12xxx, client origin, timestamp correlation
     ▼
【5】Check OS logs for resource/device anomalies
     │→ I/O / memory / network / filesystem
     ▼
【Action】Tune settings, recover, or raise SR (aim at root cause)

Quick “must-use” commands

Follow the latest entries in the Alert Log (see concrete paths below): cd $ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace tail -F alert_<DB_NAME>.log | egrep "ORA-|ERROR|WARNING"
Get the authoritative diagnostic directories (SQL): -- Fetch diagnostic (ADR) paths with certainty SELECT name, value FROM v$diag_info WHERE name IN ('ADR Base','ADR Home','Diag Trace','Diag Alert') ORDER BY name; Beginner tip: V$DIAG_INFO tells you the ADR paths. Start here so you don’t chase the wrong files.

The log order to check first in an oracle environment

1. Alert Log (alert_<DB_NAME>.log)

Purpose: Time-series overview of major events—instance state, background issues, ORA- errors.

Default path
$ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace/alert_<DB_NAME>.log

(Concrete path examples for the specified environment)
Assumptions:
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

Example A: DB_NAME=orcl, INSTANCE_NAME=orcl
- Directory: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
- Alert Log: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
- Follow recent entries: tail -F /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log | egrep "ORA-|ERROR|WARNING"
Example B: DB_NAME=PROD, INSTANCE_NAME=orcl (typical DB_NAME ≠ SID)
- Directory: /u01/app/oracle/diag/rdbms/PROD/orcl/trace/
- Alert Log: /u01/app/oracle/diag/rdbms/PROD/orcl/trace/alert_PROD.log

If DB name is unknown (safe read):

SELECT name FROM v$database;   -- Identify <DB_NAME> here

Typical cases and initial actions

ORA-00600 (internal error)
Approach: Capture the arguments in brackets and the same-time trace. Check reproducibility. Prepare for SR.
ORA-04031 (shared pool or similar memory shortage)
Temporary relief: ALTER SYSTEM FLUSH SHARED_POOL; SELECT name, value FROM v$parameter WHERE name IN ('sga_target','memory_target','shared_pool_size') ORDER BY name; Beginner tip: Flushing frees shared pool short-term. The root fix is SGA/pool design (size and what’s bloating the library cache).

2. Trace files (process/session details)

Purpose: Details behind the Alert Log event—call stack, executing SQL, error arguments.

Default path
$ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace/

(Concrete path examples for the specified environment)

Example A: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
Example B: /u01/app/oracle/diag/rdbms/PROD/orcl/trace/

Common pattern

ORA-07445 (OS exception / core dump)
Approach: Note function name and timestamp in the trace; correlate with OS logs (I/O, filesystem). Attach the trace when filing SR.

3. ADRCI (Automatic Diagnostic Repository Command Interpreter)

Purpose: Cross-view of incidents/problems and collection.

Basic usage

adrci
ADR base = /u01/app/oracle
adrci> show home
adrci> set home diag/rdbms/<DB_NAME>/<INSTANCE_NAME>
adrci> show incident -last 10
adrci> show problem
adrci> show alert -tail 100
adrci> help ips   -- Help for SR packaging commands

(Concrete path examples for the specified environment)

Example A (DB=orcl, SID=orcl) adrci> set home diag/rdbms/orcl/orcl
Example B (DB=PROD, SID=orcl) adrci> set home diag/rdbms/PROD/orcl

Beginner tip: ADR (11g+) is the diagnostic repository. With adrci, you can view alert/trace/incidents across the board.

4. Listener log (listener.log)

Purpose: Make connectivity issues visible—who, when, and what failed.

Default path
$ORACLE_BASE/diag/tnslsnr/<HOST>/<LISTENER>/trace/listener.log

(Concrete path examples for the specified environment)

Example: HOST=dbhost, LISTENER=LISTENER
- Log: /u01/app/oracle/diag/tnslsnr/dbhost/LISTENER/trace/listener.log
Listener binaries (full path): /u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl status /u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl services /u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl start

Typical errors

TNS-12541: no listener
Check: lsnrctl status
ORA-12514: listener does not currently know of service
What to verify: Wrong/missing SERVICE_NAME. Check lsnrctl services and ensure LOCAL_LISTENER/SERVICE_NAMES are consistent.

5. OS logs (resources/devices)

Purpose: Rule out non-DB causes (disk failures, memory pressure, network issues).

Oracle Linux examples

# Key logs
sudo tail -n 200 /var/log/messages
sudo journalctl -xe --since "2025-08-01" --until "2025-08-31"

# I/O/FS signs (examples)
dmesg -T | egrep -i "error|ext4|nvme|sda|dma"

Beginner tip: The database isn’t always the culprit. Storage or network problems often surface as DB errors. Correlate timestamps across DB and OS logs.

Typical “cause ↔ remedy” crib notes

Symptom	First actions	Direction for root fix
ORA-00600 / ORA-07445	Gather same-time alert & trace, check reproducibility	Bug/data corruption/external. Analyze via SR, consider applicable RUs
ORA-04031	Temporary FLUSH; observe library cache at runtime	SGA/pool sizing, cursor sharing, reduce hard parses
ORA-01555 (snapshot too old)	Understand consistency need & time of occurrence	Revisit UNDO size / `UNDO_RETENTION`; redesign long-running SQL
TNS-12541 / 12514	Check `lsnrctl status/services` and service name consistency	Naming/distribution hygiene; monitoring thresholds and restart runbooks

UNDO checks/tuning (entry point for ORA-01555)

-- Current UNDO retention
SELECT name, value FROM v$parameter WHERE name='undo_retention';

-- UNDO tablespace overview
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces WHERE contents='UNDO';

-- Example: increase retention (evaluate impact)
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

Beginner tip: Retention is a “make it harder to reuse” target, not an absolute. If you have many long queries or heavy DML, consider increasing UNDO size as well.

Handy helper SQL for operations (safe reads only)

-- If adrci can't be used, start by confirming diagnostic paths.
-- (Reading the alert text itself is via file grep/tail.)
SELECT name, value
FROM   v$diag_info
WHERE  name IN ('Diag Alert','Diag Trace')
ORDER  BY name;

Beginner tip: The alert body is a file on disk. In practice, grep/tail is fastest for scanning.

Appendix: Lab-only user (and notes on IDENTITY columns)

Production requires least-privilege. The following is for lab use only.
The password has been changed to exclude @.

-- Execute as SYS (lab)
CREATE USER logviewer IDENTIFIED BY Str0ngPass1;

-- Minimal connection + data dictionary read
GRANT CREATE SESSION TO logviewer;
GRANT SELECT_CATALOG_ROLE TO logviewer;

-- To allow CREATE TABLE and ensure space
GRANT CREATE TABLE TO logviewer;
ALTER USER logviewer DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER logviewer QUOTA 50M ON USERS;

-- Important: IDENTITY columns create an internal SEQUENCE (ISEQ$$_…).
-- The user therefore needs CREATE SEQUENCE as well.
GRANT CREATE SEQUENCE TO logviewer;

Beginner tip:

GENERATED BY DEFAULT AS IDENTITY implicitly creates an internal sequence. Without CREATE SEQUENCE, ORA-01031 can occur.
Troubleshooting order:
1. Try CREATE TABLE without IDENTITY to see if it succeeds.
2. If it does, grant CREATE SEQUENCE, then retry with IDENTITY.

Appendix: Practice table (to keep investigation notes)

-- Run as logviewer
CONNECT logviewer/Str0ngPass1

-- (A) Version with IDENTITY (requires: CREATE SEQUENCE)
CREATE TABLE log_notes (
  note_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  occurred_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  component   VARCHAR2(30),
  message     VARCHAR2(4000)
);

-- (B) If you try before granting CREATE SEQUENCE, test without IDENTITY:
-- CREATE TABLE log_notes (
--   note_id     NUMBER PRIMARY KEY,
--   occurred_at TIMESTAMP DEFAULT SYSTIMESTAMP,
--   component   VARCHAR2(30),
--   message     VARCHAR2(4000)
-- );

INSERT INTO log_notes(component, message)
VALUES ('ALERT', 'Detected ORA-04031; applied temporary FLUSH');

COMMIT;

SELECT * FROM log_notes ORDER BY occurred_at DESC;

(Supplement) If you still get ORA-01031 when creating a table

Without changing the article’s content, here is the minimal troubleshooting for the observed issue.

Does the table include an IDENTITY column? (→ requires CREATE SEQUENCE)
Target schema check (CURRENT_SCHEMA other than yourself requires CREATE ANY TABLE)

SELECT
  SYS_CONTEXT('USERENV','SESSION_USER')   AS session_user,
  SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,
  SYS_CONTEXT('USERENV','CON_NAME')       AS con_name
FROM dual;

If needed: ALTER SESSION SET CURRENT_SCHEMA = LOGVIEWER; -- Or specify schema explicitly: -- CREATE TABLE LOGVIEWER.log_notes ( ... );

PDB open mode (DDL not allowed in READ ONLY; check via v$pdbs)
Tablespace state (READ ONLY/OFFLINE blocks DDL; set to READ WRITE)

Reference: frequently used directories (19c defaults + concrete examples)

# Default pattern
$ORACLE_BASE/diag/rdbms/<DB>/<INST>/trace/                   ... Alert/trace
$ORACLE_BASE/diag/tnslsnr/<HOST>/<LISTENER>/trace/           ... Listener logs/trace

# Examples for the specified environment (ORACLE_SID=orcl, ORACLE_BASE=/u01/app/oracle)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/                  ... Alert/trace (DB=orcl, SID=orcl)
/u01/app/oracle/diag/rdbms/PROD/orcl/trace/                  ... Alert/trace (DB=PROD, SID=orcl)
/u01/app/oracle/diag/tnslsnr/dbhost/LISTENER/trace/          ... listener.log (HOST=dbhost, LISTENER=LISTENER)
/u01/app/oracle/product/19.0.0/dbhome_1/bin/lsnrctl           ... Listener binaries
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus           ... SQL*Plus

Summary

Fix your check order (Alert → Trace → ADRCI → Listener → OS).
Use timestamp correlation to narrow root causes; separate temporary workarounds from permanent fixes.
Reproduce and script in a lab to speed up your next incident response.

This article targets Oracle Database 19c (other versions may differ in screens and defaults).

[reference]
Database Error Messages