OracleDiagnostics

Alert log errors in the last 24 hours

ORA- and TNS- errors plus checkpoint warnings from the alert log, read through SQL. It can be slow when the alert log is very large.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN at_time HEADING "TIME" FORMAT A194COLUMN message FORMAT A150 WORD_WRAPPED5 6SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,7       SUBSTR(message_text, 1, 300) AS message8FROM v$diag_alert_ext9WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY10  AND (message_text LIKE '%ORA-%'11       OR message_text LIKE '%TNS-%'12       OR message_text LIKE '%Checkpoint not complete%')13ORDER BY originating_timestamp DESC;

Save it as ora-alert-errors.sql and run it with SQL> @ora-alert-errors.

Open in denrepoRelated tool: Oracle error lookup

Helps with

• ORA-00060: deadlock detected while waiting for resource
• ORA-00600: internal error code, arguments: [%s], [%s], ...
• ORA-00604: error occurred at recursive SQL level %s
• ORA-01033: ORACLE initialization or shutdown in progress
• ORA-01089: immediate shutdown or close in progress - no operations are permitted
• ORA-01157: cannot identify/lock data file %s - see DBWR trace file
• ORA-04030: out of process memory when trying to allocate %s bytes (%s,%s)

Part of these runbooks

• Morning health check
• After patching
• Users can't connect

More Oracle scripts: Diagnostics

• Where the alert log and trace files areThe ADR home, alert log directory, trace directory and this session's own trace file.
• Trace another session with waits and bindsPrompts for a SID and serial number, turns on extended SQL trace, and shows the trace file name. Run the disable line when you've captured enough,…
• Review problems and incidents (ADRCI)ADRCI commands for critical errors like ORA-00600 and ORA-07445, and for packaging an incident to send to Oracle Support.