When startup fails, the alert log still has the sequence. Find that instance's log, including while the instance is stopped, and read the timestamps around the failure.
Oracle DBA Lesson 29A — Finding the Alert Log
The alert log is the ordered record of instance events and errors. For a failed startup, use it to connect three things: the operation that was attempted, the first useful error, and the failure that followed.
The diagnostic directory
Oracle keeps diagnostic files in the Automatic Diagnostic Repository (ADR). ADR is a directory tree outside the database, so the files stay on disk when the instance is stopped. DIAGNOSTIC_DEST is the ADR base. Each database instance, listener, and Automatic Storage Management (ASM) instance has its own home under that base.
A database-instance home is diag/rdbms/<DB_UNIQUE_NAME>/<SID>. In the video, the unique name is course and the instance is course1.
ADR base: /diagbase
└── diag
├── rdbms/course/course1 database instance
│ ├── trace text alert log and trace files
│ └── alert XML alert log
├── tnslsnr/dbhost/listener listener
└── asm/+asm/+ASM ASM instance, if configuredThe listener has its own home and its own history. Read the database-instance home for a startup failure, not the listener home and not the ASM home. Oracle 19c diagnostic infrastructure documents the component homes and what the alert and trace directories hold.
Find the locations through SQL
When the database accepts SQL, run this from the CDB root. The account needs SELECT on SYS.V_$DIAG_INFO, or an administrator role that already has it. Confirm the database and CDB$ROOT before you treat the paths as this instance's.
SHOW USER
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
SYS_CONTEXT('USERENV','CON_NAME') AS container_name
FROM dual;
SELECT name, value
FROM v$diag_info
WHERE name IN ('ADR Home', 'Diag Trace', 'Diag Alert');SHOW is a SQL*Plus command. SELECT runs in the database.
| Name | What it is | Video path |
|---|---|---|
| ADR Home | This instance's diagnostic home | /diagbase/diag/rdbms/course/course1 |
| Diag Trace | Text alert log and process trace files | /diagbase/diag/rdbms/course/course1/trace |
| Diag Alert | XML alert log | /diagbase/diag/rdbms/course/course1/alert |
The text alert file in the trace directory is alert_<SID>.log. For this instance that is alert_course1.log. The alert directory holds the XML records, including log.xml. On your lab, use the three paths V$DIAG_INFO returns. V$DIAG_INFO lists the rows.
Read the files with ADRCI
ADRCI is the ADR Command Interpreter. Use it when SQL is not available, including when the instance is stopped. ADRCI reads the diagnostic files from disk. It does not start the instance, and it does not grant extra permission: the operating-system account still needs read access to those files. Use the Oracle software owner, or an account the instructor has given that read access, with the Oracle environment already set.
adrciAt the adrci> prompt, check the base, then list the homes under it:
SHOW BASE
SHOW HOMESSHOW BASE prints the ADR base ADRCI is using. If that is not the base for the instance you are investigating, set the confirmed absolute directory and list homes again. In the video the base is /diagbase. On the lab, use the base you were given.
SET BASE /diagbase
SHOW HOMESA fresh ADRCI session selects every home under the current base. SHOW HOMES prints that selection. A listing can contain both the database and the listener:
ADR Homes:
diag/rdbms/course/course1
diag/tnslsnr/dbhost/listenerSelect the database-instance home exactly as returned. The path is relative to the current ADR base. Then read the recent alert entries.
SET HOMEPATH diag/rdbms/course/course1
SHOW HOMES
SHOW ALERT -TAIL 50SHOW ALERT -TAIL 50 prints the last 50 alert entries. If more than one home is still current, SHOW ALERT can ask you to pick one. SET HOMEPATH keeps the output on the instance you meant. Fifty entries is a start. For an older or busy interval, take a wider tail, or filter with the documented SHOW ALERT -P timestamp predicate. Keep the time zone, and keep the messages just before the error. ADRCI documents base and home selection, the tail option, and timestamp predicates.
Read a startup chronology
Match the time of the startup attempt, including the offset. In the video the attempt is 10:04:11 through 10:04:13, offset +00:00:
10:04:11 +00:00 ALTER DATABASE MOUNT
10:04:12 +00:00 ORA-00210: cannot open the specified control file
ORA-00202: control file: '/labdata/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
10:04:13 +00:00 ORA-00205: error in identifying control file,
check alert log for more infoThe attempted operation is ALTER DATABASE MOUNT. The first useful failure is ORA-00210: Oracle could not open a control file. ORA-00202 names the path, /labdata/control01.ctl. ORA-27037 with Linux error 2 says that path is not there. That is the file-access problem to investigate: the named file and its parent directory.
ORA-00205 one second later is the consequence. Mount did not complete because the control file could not be identified. Read the stack together. Do not stop at the last line, and do not treat the count of ORA- messages as the cause.
An earlier error is the lead. Confirm it against the named file, the operating-system detail, and the messages around it before you change anything. This read does not create, delete, restore, or edit a control file. Oracle 19c error messages define the control-file errors. ORA-27037 points at the operating-system errno that follows it.
Text, XML, and what to keep
The text alert log in the trace directory is plain text. Open alert_course1.log directly when you want to read it that way. ADRCI reads the XML alert log and prints the same events as text, with the XML tags removed. The XML file is the structured copy used for programmatic parsing.
Before any retention cleanup, copy out the evidence for this failure:
- Instance identity: database unique name and SID, or the ADR home you selected.
- The time interval and its time zone.
- The complete error stack and the records immediately around it.
This exercise is read-only. Do not purge or delete ADR files. Finding the log is not the same as checking whether the instance is healthy now. Check current instance state separately when you need it.
Practice
On the instructor's Oracle 19c lab, single-instance CDB on Linux, find that instance's ADR home twice: once with V$DIAG_INFO while SQL works, and once with ADRCI. Read the latest real startup interval. Do not cause a new failure to produce one.
Write three lines from the records you actually read: the operation that was attempted, the significant result or error, and the outcome. Include timestamps and the time zone. State why those lines belong to this instance and not to the listener or another database home.
Success is the correct home and a cause-versus-consequence reading of the real interval. Record the database version or release update, the identity you confirmed, whether you used SQL or ADRCI, and what you observed. No parameter change, file change, or cleanup is part of this practice. The read does not require a management pack.
No comments:
Post a Comment