Alert Log, ADR, Trace Files, Dynamic Performance Views, and Startup Troubleshooting

Learning outcomes

The ServiceHub Free instance fails to start after a configuration experiment. The worst response is to delete logs, edit multiple files, and repeatedly force startup until the original evidence is gone. Oracle’s diagnostic architecture gives you a safer order: classify the startup state, locate the Automatic Diagnostic Repository (ADR), read the alert/attention/trace evidence, correlate it with parameter/file/listener state, make one reversible correction, then verify from database and application perspectives.

1. ADR is the diagnostic filesystem outside the database

The Automatic Diagnostic Repository (ADR) is a file-based repository for diagnostic data. Database instances, listeners, ASM, Clusterware, and other Oracle components can have separate ADR homes under an ADR base. Because ADR is outside the database, it remains available when SQL connections cannot be established—exactly when startup troubleshooting needs it most.

Database ADR content includes the XML alert log, text-formatted alert/attention logs, foreground/background trace files, incidents, dumps, health monitor output, and support packages. Do not delete this evidence as a “cleanup” step during an incident.

SELECT name, valueFROM   v$diag_infoORDER  BY name;

Important rows include ADR Base, ADR Home, Diag Trace, Diag Alert, and Default Trace File. Exact paths depend on the installation/container and should be discovered, not copied from documentation examples.

2. Alert log, attention log, and trace files answer different questions

The alert log is a chronological database-instance record of important messages and errors, including startup/shutdown and nondefault initialization parameters. The attention log is a more focused record of critical events requiring administrator attention. Trace files contain process-specific diagnostic information and can be much more detailed. A foreground/server process and a background process can each write its own trace.

SELECT pid, pname, program, spid, tracefileFROM   v$processWHERE  tracefile IS NOT NULLORDER  BY pname NULLS LAST, pid;SELECT value AS current_session_traceFROM   v$diag_infoWHERE  name = 'Default Trace File';

A single ORA error copied from a client is often insufficient. Pair the client timestamp/operation with the alert log and the responsible process trace when one exists. For listener connection failures, inspect the listener’s own ADR/log evidence as well as database service registration.

3. ADRCI gives structured access when SQL cannot

ADRCI is Oracle’s command-line interface to ADR. It can list ADR homes, show/tail alert data, inspect incidents/problems, and package diagnostics. It is especially useful after the database has failed before MOUNT/OPEN and dynamic views are unavailable.

adrcishow baseshow homes

After SHOW HOMES, select the database ADR home it actually returns. A typical Free container can use a path shaped like diag/rdbms/free/FREE, but do not assume that exact value.

set home diag/rdbms/free/FREEshow alert -tail 50exit

Database, listener, and ASM homes are separate. If your SHOW HOMES output differs from the example, use the returned database home instead. Choosing the wrong ADR home can make the relevant failure evidence appear to be missing.

4. Evidence order for a failed startup

This order prevents category errors. A listener ORA-12514 is not fixed by restoring a datafile; a malformed initialization parameter is not fixed by rebuilding a control file; a closed PDB is not proof that the instance crashed.

5. Deliberate failure: bad temporary PFILE, untouched SPFILE

First create a known-good text PFILE from the current SPFILE while the database is healthy. Then shut down cleanly. Append one deliberately unknown parameter to the temporary PFILE only. Attempt startup with that PFILE; Oracle should reject parameter processing. Finally, start normally from the untouched SPFILE.

CREATE PFILE='/tmp/servicehub_ch02_good.ora' FROM SPFILE;SHUTDOWN IMMEDIATE

cp /tmp/servicehub_ch02_good.ora /tmp/servicehub_ch02_bad.oraprintf "*.servicehub_fake_parameter=1" >> /tmp/servicehub_ch02_bad.oratail -n 5 /tmp/servicehub_ch02_bad.ora

STARTUP PFILE='/tmp/servicehub_ch02_bad.ora'-- Expected: startup fails during parameter processing (for example ORA-01078/LRM unknown parameter).STARTUPSHOW PDBS

The correction is to stop using the bad test PFILE, not to modify the SPFILE or delete diagnostics. If normal STARTUP succeeds, you proved the failure was isolated to the temporary parameter file. Preserve the failed startup timestamp and relevant alert/ADRCI output before deleting the temporary test file.

6. Listener/service problems have a separate diagnostic boundary

After the database is open, a client can still fail because the listener is stopped, the requested service is unknown, the PDB is closed, the endpoint is unreachable, or registration has not completed. LREG normally registers service information dynamically. Use both database and listener evidence.

SELECT name, network_name, pdb, con_idFROM   v$servicesORDER  BY name;SELECT name, open_modeFROM   v$pdbsORDER  BY con_id;ALTER SYSTEM REGISTER;

lsnrctl statuslsnrctl services

If the listener was just started, LREG can register automatically after a delay; ALTER SYSTEM REGISTER requests immediate registration. Do not add static listener entries or change LOCAL_LISTENER blindly until you know what service/endpoint is missing and why.

7. Dynamic performance views are evidence, not a license to query everything

When the instance is running, V$ dynamic performance views give live state: sessions, processes, memory, parameters, files, services, waits, and more. They are invaluable but scope-sensitive and transient. Many require administrative catalog privileges, and later AWR/ASH/ADDM features are governed by Diagnostics/Tuning Pack licensing depending on offering. Do not grant application users broad catalog roles, and do not infer production entitlement because a view exists.

SELECT instance_name, status, database_status, logins, startup_timeFROM   v$instance;SELECT name, open_mode, database_roleFROM   v$database;SELECT con_id, name, open_mode, restrictedFROM   v$pdbsORDER  BY con_id;SELECT name, valueFROM   v$diag_infoWHERE  name IN ('ADR Base','ADR Home','Diag Trace','Diag Alert','Default Trace File')ORDER  BY name;

8. Hands-on lab: produce a troubleshooting packet

Perform the bad-PFILE failure and recovery once. Your packet should contain:

• exact Oracle AI Database product/RU evidence (V$VERSION/V$INSTANCE.VERSION_FULL);
• SPFILE/PFILE provenance and the diff showing the single injected fake parameter;
• the failed startup client error with timestamp;
• ADR home/base and the relevant alert/attention/trace excerpt around that timestamp;
• successful normal startup evidence from V$INSTANCE/V$DATABASE;
• FREEPDB1 open state, service registration, and lsnrctl services evidence;
• a final least-privilege SERVICEHUB_APP query proving application access.

SELECT COUNT(*) AS work_ordersFROM servicehub_owner.work_orders;SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name,       SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_nameFROM dual;

Cleanup: after preserving the troubleshooting packet, remove only the two temporary PFILE copies you created. Do not purge ADR or alert history as part of the lab.

9. Production judgment

Production troubleshooting should minimize simultaneous changes. Preserve timestamps and diagnostics, classify the failed state, form one mechanism-based hypothesis, make the smallest reversible correction, and verify the whole path. If the issue involves control/data/redo corruption, patching, clusterware, Data Guard, or security keys, stop before improvising and use the relevant runbook/Oracle Support path.

ADR can contain sensitive SQL, bind, path, host, and diagnostic information. Treat incident packages and trace files as controlled operational data. Oracle AI Database Free has lower trace-file size limits than supported editions, so evidence retention behavior can also differ from production.

10. Chapter checkpoint and bridge to Chapter 03

Chapter 02 moved from “Oracle is running” to an evidence model: instance memory and background processes, session/process/PGA mappings, parameter provenance and persistence, startup state transitions, and ADR-driven failure diagnosis. Chapter 03 now descends from these runtime structures into blocks, extents, segments, tablespaces, datafiles, space management, and ASM concepts.

Authoritative references

• Oracle AI Database Administrator’s Guide — Diagnosing and Resolving Problems — ADR, alert/attention logs, trace files, and incident workflow
• Oracle AI Database Administrator’s Guide — Monitoring the Database — alert, attention, trace, and monitoring evidence
• Oracle AI Database Reference — V$DIAG_INFO — actual ADR locations for the current instance
• Oracle AI Database Utilities — ADRCI — command-line ADR inspection and packaging
• Oracle Net Services Administrator’s Guide — Service Registration — LREG/listener registration and handlers