When one session reports an internal Oracle error while other sessions keep working, identify the server process for that session and examine its diagnostic evidence.
Oracle DBA Lesson 29B — Finding the Trace for a Session Error
A server process performs database work for a session. Its trace file records detailed process diagnostics. The alert log provides the broader instance event sequence. When one session fails while others keep working, start with that session's process evidence.
Find the current process's location
In the session being investigated:
SELECT name, value
FROM v$diag_info
WHERE name IN ('Default Trace File','Diag Trace');Default Trace File returns the current server process's expected full trace path. Diag Trace returns the trace directory for the instance. Check file existence and whether its entries match the event. A new connection can use a different process. If the failing session has disconnected, use its previously captured process or incident identity to locate its evidence. Oracle 19c V$DIAG_INFO
The video's /example/diag/trace/cdb1_ora_4321.trc, 10:15 UTC, process 4321 and simplified ORA-00600 records are authored examples. They have not been executed or captured from a host. Real paths and exact log formats vary. The picture assumes a dedicated server connection.
Match the event
Record the date/time and timezone, host/instance/container, session or process identifier, operation, full error and any incident number. Use these together to select the relevant interval. Typical server trace names contain the OS process number, which is a correlation clue. Match the trace header and record content. ORA-00600 alone does not identify a repair. Oracle 19c diagnostic guide
| Question | Evidence to examine |
|---|---|
| What happened during a startup or an instance event? | Relevant alert-log chronology |
| What diagnostic details surround one process's failing work? | Correlated process trace, plus incident dumps where available |
| What related evidence is needed for support investigation? | Reviewed incident package |
| Who performed a selected change, when, and with what outcome? | Audit records covered by an enabled policy |
An incident is a single occurrence of a critical problem. A package groups related diagnostic records for investigation. Its contents need review before transfer. The lesson does not create or upload a package. Oracle 19c ADRCI
DDL logging and accountability
DDL means data definition language; CREATE TABLE is an example. ENABLE_DDL_LOGGING controls logging of a subset of schema statements, and their text may be truncated. Read the current session's setting with:
SELECT name, value
FROM v$parameter
WHERE name = 'enable_ddl_logging';This query reads a setting. The exercise does not change it. Using DDL logging requires the applicable Database Lifecycle Management Pack entitlement in Oracle 19c; confirm the exact deployment and contract, including features included in a qualifying subscription. A returned value or an available parameter does not establish entitlement. Oracle 19c parameter reference, Oracle 19c licensing information
An audit policy selects activities to record for accountability. Confirm enabled coverage and inspect user, object, action, time and result for the event. Unified audit policy design and administration are covered later in the course. Oracle 19c introduction to auditing
Designated-lab conditions and exercise
Use the instructor's disposable Oracle 19c Linux CDB/PDB lab and SQL*Plus. Record RU/edition/platform and confirm identity first:
SHOW USER
SHOW CON_NAMEThe instructor provisions CREATE SESSION and delegated SELECT on SYS.V_$DIAG_INFO and SYS.V_$PARAMETER for the selected container. Files require separately authorized OS read access. Follow the course lab guide; no production access is authorized by this teaching lesson.
Run the two read-only lookups, record the actual returned current path/directory and setting, and classify the four questions in the table using a new scenario. If authorized existing diagnostic evidence is available, match its real time/process/error. Otherwise record that correlation practice is unexecuted. Do not induce errors, enable tracing or DDL logging, create packages, or delete/purge files for this exercise.
Trace/dump data can include SQL text, bind values, file paths and sensitive context. Read and preserve only authorized relevant evidence in protected storage. Keep identity/time metadata and provenance; review package contents before any separately approved transfer. The author executed no database commands. Success requires the learner's real evidence and explanation, not video completion.
Recap: Locate the current process path; correlate time, process and error; choose and preserve relevant diagnostic evidence; use appropriately covered audit records for accountability.
No comments:
Post a Comment