A failed startup leaves a useful question: where is the evidence when a diagnostic SQL query cannot run? This lesson explains how to find the intended instance's alert records and relate their messages to the attempted operation.
Oracle DBA Lesson 29 Part 1 — Find the Alert Log When the Database Is Unavailable
Alert records and their locations
The alert log records important database events and errors chronologically, including startup and shutdown activity. Oracle stores diagnostic files in the Automatic Diagnostic Repository (ADR), a directory structure outside the database. Existing files can be inspected when the instance is stopped, provided the filesystem is available. See Oracle diagnostic infrastructure.
DIAGNOSTIC_DEST identifies the configured diagnostic destination. Under this ADR base, a database-instance home has this structure:
<ADR_BASE>/diag/rdbms/<DB_UNIQUE_NAME>/<SID>
Use the actual recorded database unique name and instance identifier. Listener and ASM homes belong to their own components. The parameter's default location depends on configuration. Verify the destination instead of guessing a fallback. See DIAGNOSTIC_DEST.
Inside a database home:
| Location | What to find |
|---|---|
trace/alert_<SID>.log | Text-formatted alert log; the directory also holds process traces |
alert/log.xml | XML alert log representation |
ADRCI displays XML alert messages with their tags omitted. These locations describe representations for the same instance. See ADR directory structure, including the cross-version structure.
Locate paths with SQL when available
Use SYS.V_$DIAG_INFO in the container database root:
SELECT name, value
FROM v$diag_info
WHERE name IN ('ADR Home', 'Diag Trace', 'Diag Alert');
ADR Home identifies the instance's diagnostic home. Diag Trace locates the directory containing the text alert file. Diag Alert locates the directory containing the XML log. These name/value rows describe current-instance ADR information. Record them while queries are possible. See V$DIAG_INFO.
Read through the operating system
On the database host, use the Oracle software environment. Launch the utility from the shell:
adrci
At its prompt:
SHOW BASE
Compare this base with the recorded configuration. If it differs, select the verified directory:
SET BASE <ADR_BASE>
SHOW HOMES
Choose the exact returned relative path matching the intended database and instance. For example, if the returned home is diag/rdbms/shop/shop1:
SET HOMEPATH diag/rdbms/shop/shop1
SHOW HOMES
SHOW ALERT -TAIL 50
EXIT
The second SHOW HOMES confirms the selected scope. -TAIL 50 requests the latest 50 entries. Widen the window when the attempted operation is older. Multiple current homes can cause an alert-log selection prompt. Selecting one intended home avoids that ambiguity. OS permissions govern access. ADRCI does not grant additional filesystem rights. See ADRCI access, selection and alert commands.
Interpret a failed operation
Startup begins at 09:10, required-file access fails with an associated permission-denied detail, and startup fails afterward. Keep the timestamp and timezone with the related messages. The Oracle error identifies the failing operation. Its file and OS details guide the investigation. In this example, investigate the identified file's permissions before deciding the next action. A file-open error can have other causes: Oracle's ORA-27041 guidance calls for inspecting the associated OS error. See ORA-27041.
The first displayed line need not be the root cause. Read the related error group and surrounding messages. Distinguish the failing access from later consequences, and preserve the relevant interval and instance identity before retention maintenance.
No comments:
Post a Comment