denrepo

Runbook10 steps

Morning health check

The daily pass: instance state, space, FRA, last night's backups, alert log, failed jobs, invalid objects and accounts about to expire. Read-only and needs no extra license.

  1. Database and instance overviewOne row per instance: host, version, uptime, role, open mode, archive log mode and whether it's a CDB. The first thing to run when you land on an unfamiliar…
  2. Processes and sessions against their limitsCurrent and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
  3. Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
  4. Fast Recovery Area usageFRA size, used and reclaimable space, then what's using it by file type. Real used % is the number that matters: at 100% the database stops archiving and hangs.
  5. RMAN backup jobs in the last 7 daysStatus, duration and output size of each RMAN job, newest first.
  6. Datafiles without a recent backupDatafiles with no backup of any kind in the last two days, or none at all. A new datafile added after the last full backup shows up here first.
  7. Alert log errors in the last 24 hoursORA- and TNS- errors plus checkpoint warnings from the alert log, read through SQL. It can be slow when the alert log is very large.
  8. Scheduler job failures in the last 24 hoursEvery job run that didn't succeed, with the error number and the start of the error text.
  9. Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
  10. Accounts locked, expired or expiring soonApplication accounts that aren't OPEN, or whose passwords expire in the next 14 days. Catch service accounts before they break an application.

Open in denrepo

The whole runbook as one file: SQL> @morning
morning.sql 
1-- denrepo runbook: Morning health check2-- The daily pass: instance state, space, FRA, last night's backups, alert log, failed jobs, invalid objects and accounts about to expire. Read-only and needs no extra license.3-- Source: https://denrepo.com/runbooks/morning/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 10: Database and instance overview ====10CLEAR COLUMNS11SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF12COLUMN inst_name HEADING "INSTANCE"  FORMAT A1213COLUMN host_name HEADING "HOST"      FORMAT A25 TRUNCATE14COLUMN version   FORMAT A1115COLUMN status    FORMAT A816COLUMN started   FORMAT A1617COLUMN up_days   HEADING "UP DAYS"   FORMAT 9990.918COLUMN db_name   HEADING "DB"        FORMAT A919COLUMN role      FORMAT A1620COLUMN open_mode HEADING "OPEN MODE" FORMAT A2021COLUMN log_mode  HEADING "LOG MODE"  FORMAT A1222COLUMN cdb       FORMAT A323 24SELECT i.instance_name AS inst_name, i.host_name, i.version, i.status,25       TO_CHAR(i.startup_time, 'YYYY-MM-DD HH24:MI') AS started,26       ROUND(SYSDATE - i.startup_time, 1) AS up_days,27       d.name AS db_name, d.database_role AS role,28       d.open_mode, d.log_mode, d.cdb29FROM gv$instance i30CROSS JOIN v$database d31ORDER BY i.inst_id;32 33-- Not yet verified34PROMPT35PROMPT ==== 2 of 10: Processes and sessions against their limits ====36CLEAR COLUMNS37SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF38COLUMN resource_name HEADING "RESOURCE" FORMAT A2239COLUMN current_util  HEADING "CURRENT"  FORMAT 999,99040COLUMN max_util      HEADING "PEAK"     FORMAT 999,99041COLUMN limit_value   HEADING "LIMIT"    FORMAT A1042COLUMN max_pct       HEADING "PEAK %"   FORMAT 990.943 44SELECT resource_name,45       current_utilization AS current_util,46       max_utilization AS max_util,47       TRIM(limit_value) AS limit_value,48       CASE WHEN TRIM(limit_value) NOT IN ('UNLIMITED', '0')49            THEN ROUND(100 * max_utilization / TO_NUMBER(TRIM(limit_value)), 1) END AS max_pct50FROM v$resource_limit51WHERE resource_name IN ('processes', 'sessions', 'transactions', 'parallel_max_servers')52ORDER BY resource_name;53 54-- Not yet verified55PROMPT56PROMPT ==== 3 of 10: Tablespace usage ====57CLEAR COLUMNS58SET LINESIZE 130 PAGESIZE 100 TRIMOUT ON TAB OFF59COLUMN tablespace_name HEADING "TABLESPACE" FORMAT A2560COLUMN contents HEADING "TYPE" FORMAT A961COLUMN used_gb  HEADING "USED GB"  FORMAT 999,990.9962COLUMN free_gb  HEADING "FREE GB"  FORMAT 999,990.9963COLUMN max_gb   HEADING "MAX GB"   FORMAT 999,990.9964COLUMN used_pct HEADING "USED %"   FORMAT 990.965 66SELECT m.tablespace_name,67       t.contents,68       ROUND(m.used_space * t.block_size / POWER(1024, 3), 2) AS used_gb,69       ROUND((m.tablespace_size - m.used_space) * t.block_size / POWER(1024, 3), 2) AS free_gb,70       ROUND(m.tablespace_size * t.block_size / POWER(1024, 3), 2) AS max_gb,71       ROUND(m.used_percent, 1) AS used_pct72FROM dba_tablespace_usage_metrics m73JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name74ORDER BY m.used_percent DESC;75 76-- Not yet verified77PROMPT78PROMPT ==== 4 of 10: Fast Recovery Area usage ====79CLEAR COLUMNS80SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF81COLUMN name           HEADING "LOCATION"       FORMAT A3082COLUMN limit_gb       HEADING "LIMIT GB"       FORMAT 999,990.983COLUMN used_gb        HEADING "USED GB"        FORMAT 999,990.984COLUMN reclaimable_gb HEADING "RECLAIMABLE GB" FORMAT 999,990.985COLUMN real_used_pct  HEADING "REAL USED %"    FORMAT 990.986COLUMN files          FORMAT 999,99087COLUMN file_type      HEADING "FILE TYPE"      FORMAT A2588COLUMN used_pct       HEADING "USED %"         FORMAT 990.9989COLUMN reclaim_pct    HEADING "RECLAIM %"      FORMAT 990.9990 91SELECT name,92       ROUND(space_limit / POWER(1024, 3), 1) AS limit_gb,93       ROUND(space_used / POWER(1024, 3), 1) AS used_gb,94       ROUND(space_reclaimable / POWER(1024, 3), 1) AS reclaimable_gb,95       ROUND(100 * (space_used - space_reclaimable) / NULLIF(space_limit, 0), 1) AS real_used_pct,96       number_of_files AS files97FROM v$recovery_file_dest;98 99SELECT file_type,100       percent_space_used AS used_pct,101       percent_space_reclaimable AS reclaim_pct,102       number_of_files AS files103FROM v$recovery_area_usage104ORDER BY percent_space_used DESC;105 106-- Not yet verified107PROMPT108PROMPT ==== 5 of 10: RMAN backup jobs in the last 7 days ====109CLEAR COLUMNS110SET LINESIZE 150 PAGESIZE 100 TRIMOUT ON TAB OFF111COLUMN session_key HEADING "KEY"      FORMAT 999999112COLUMN input_type  HEADING "TYPE"     FORMAT A13113COLUMN status      FORMAT A23114COLUMN started     FORMAT A16115COLUMN ended       FORMAT A16116COLUMN size_out    HEADING "OUTPUT"   FORMAT A10117COLUMN duration    HEADING "DURATION" FORMAT A10118 119SELECT session_key, input_type, status,120       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS started,121       TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI') AS ended,122       output_bytes_display AS size_out,123       time_taken_display AS duration124FROM v$rman_backup_job_details125WHERE start_time > SYSDATE - 7126ORDER BY start_time DESC;127 128-- Not yet verified129PROMPT130PROMPT ==== 6 of 10: Datafiles without a recent backup ====131CLEAR COLUMNS132SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF133COLUMN file#       HEADING "FILE#"       FORMAT 99999134COLUMN name        HEADING "DATAFILE"    FORMAT A75135COLUMN last_backup HEADING "LAST BACKUP" FORMAT A16136 137SELECT f.file#, f.name,138       NVL(TO_CHAR(MAX(b.completion_time), 'YYYY-MM-DD HH24:MI'), 'never') AS last_backup139FROM v$datafile f140LEFT JOIN v$backup_datafile b ON b.file# = f.file#141GROUP BY f.file#, f.name142HAVING MAX(b.completion_time) IS NULL143    OR MAX(b.completion_time) < SYSDATE - 2144ORDER BY f.file#;145 146-- Not yet verified147PROMPT148PROMPT ==== 7 of 10: Alert log errors in the last 24 hours ====149CLEAR COLUMNS150SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF151COLUMN at_time HEADING "TIME" FORMAT A19152COLUMN message FORMAT A150 WORD_WRAPPED153 154SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,155       SUBSTR(message_text, 1, 300) AS message156FROM v$diag_alert_ext157WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY158  AND (message_text LIKE '%ORA-%'159       OR message_text LIKE '%TNS-%'160       OR message_text LIKE '%Checkpoint not complete%')161ORDER BY originating_timestamp DESC;162 163-- Not yet verified164PROMPT165PROMPT ==== 8 of 10: Scheduler job failures in the last 24 hours ====166CLEAR COLUMNS167SET LINESIZE 180 PAGESIZE 200 TRIMOUT ON TAB OFF168COLUMN at_time  HEADING "TIME"  FORMAT A16169COLUMN owner    FORMAT A15170COLUMN job_name HEADING "JOB"   FORMAT A30171COLUMN status   FORMAT A10172COLUMN error#   HEADING "ORA-"  FORMAT 99999173COLUMN info     HEADING "DETAIL" FORMAT A70 WORD_WRAPPED174 175SELECT TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI') AS at_time,176       owner, job_name, status, error#,177       DBMS_LOB.SUBSTR(additional_info, 200, 1) AS info178FROM dba_scheduler_job_run_details179WHERE status <> 'SUCCEEDED'180  AND log_date > SYSTIMESTAMP - INTERVAL '1' DAY181ORDER BY log_date DESC;182 183-- Tested on 12c, 19c · verified Sep 2026184PROMPT185PROMPT ==== 9 of 10: Invalid objects ====186CLEAR COLUMNS187SET LINESIZE 150 PAGESIZE 200 TRIMOUT ON TAB OFF188COLUMN owner FORMAT A20189COLUMN object_type FORMAT A20190COLUMN object_name FORMAT A30191SELECT owner,192       object_type,193       object_name,194       status195FROM   dba_objects196WHERE  status = 'INVALID'197ORDER BY owner, object_type, object_name;198 199-- Not yet verified200PROMPT201PROMPT ==== 10 of 10: Accounts locked, expired or expiring soon ====202CLEAR COLUMNS203SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF204COLUMN username       FORMAT A25205COLUMN account_status HEADING "STATUS"  FORMAT A25206COLUMN expires        FORMAT A10207COLUMN locked         FORMAT A10208COLUMN profile        FORMAT A20209 210SELECT username, account_status,211       TO_CHAR(expiry_date, 'YYYY-MM-DD') AS expires,212       TO_CHAR(lock_date, 'YYYY-MM-DD') AS locked,213       profile214FROM dba_users215WHERE oracle_maintained = 'N'216  AND (account_status <> 'OPEN' OR expiry_date < SYSDATE + 14)217ORDER BY expiry_date NULLS LAST, username;218