Runbook8 steps

Users can't connect

For ORA-12514, ORA-12519, ORA-01017 and friends, from a local SYSDBA session: is the instance open, is the PDB open, is the service registered, are there free processes, and is the account locked or expired. Also run Listener status and services (lsnrctl) at the OS prompt, and try the Connection string builder tool to rule out a bad connect string.

Instance status on every nodeHost, instance, version, status, whether logins are allowed, startup time and archiver state for every instance. A quick RAC-wide check after a restart.
Pluggable databases and their stateEvery PDB with its open mode, whether it's restricted, when it opened and its size. RESTRICTED = YES usually means a plug-in violation.
Listener registration and servicesWhich listeners the instance registers with and which services it offers on every node. When clients get ORA-12514, the service they ask for is missing from…
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.
Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
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.
Failed logins in the last 24 hoursFrom the unified audit trail, which records failed logons by default through the ORA_LOGON_FAILURES policy. Return code 1017 is a wrong password; 28000 is a…
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.

Open in denrepo

The whole runbook as one file: SQL> @connect

connect.sql

1-- denrepo runbook: Users can't connect2-- For ORA-12514, ORA-12519, ORA-01017 and friends, from a local SYSDBA session: is the instance open, is the PDB open, is the service registered, are there free processes, and is the account locked or expired. Also run Listener status and services (lsnrctl) at the OS prompt, and try the Connection string builder tool to rule out a bad connect string.3-- Source: https://denrepo.com/runbooks/connect/4 5SET ECHO OFF6 7-- Tested on 12c, 19c · verified Sep 20268PROMPT9PROMPT ==== 1 of 8: Instance status on every node ====10--check status of instance:11CLEAR COLUMNS12SET PAGESIZE 100 TRIMOUT ON TAB OFF13set lines 70014column HOST_NAME format a5015select HOST_NAME, instance_name, version, status,logins, to_char(STARTUP_TIME,'MM/DD/YYYY HH24:MI:SS') as STARTUP_TIME, archiver from gv$instance order by instance_name;16 17-- Not yet verified18PROMPT19PROMPT ==== 2 of 8: Pluggable databases and their state ====20PROMPT (run on CDB root)21CLEAR COLUMNS22SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF23COLUMN con_id     HEADING "CON" FORMAT 99924COLUMN name       HEADING "PDB" FORMAT A2525COLUMN open_mode  HEADING "OPEN MODE"  FORMAT A1226COLUMN restricted HEADING "RESTRICTED" FORMAT A1027COLUMN opened     FORMAT A1628COLUMN size_gb    HEADING "SIZE GB"    FORMAT 99,990.929 30SELECT con_id, name, open_mode, restricted,31       TO_CHAR(open_time, 'YYYY-MM-DD HH24:MI') AS opened,32       ROUND(total_size / POWER(1024, 3), 1) AS size_gb33FROM v$pdbs34ORDER BY con_id;35 36-- Not yet verified37PROMPT38PROMPT ==== 3 of 8: Listener registration and services ====39CLEAR COLUMNS40SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF41COLUMN name  FORMAT A2042COLUMN value FORMAT A120 WORD_WRAPPED43 44SELECT name, value45FROM v$parameter46WHERE name IN ('local_listener', 'remote_listener', 'service_names')47ORDER BY name;48 49COLUMN service      FORMAT A4050COLUMN network_name FORMAT A6051SELECT inst_id, name AS service, network_name52FROM gv$active_services53ORDER BY inst_id, name;54 55-- Not yet verified56PROMPT57PROMPT ==== 4 of 8: Processes and sessions against their limits ====58CLEAR COLUMNS59SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF60COLUMN resource_name HEADING "RESOURCE" FORMAT A2261COLUMN current_util  HEADING "CURRENT"  FORMAT 999,99062COLUMN max_util      HEADING "PEAK"     FORMAT 999,99063COLUMN limit_value   HEADING "LIMIT"    FORMAT A1064COLUMN max_pct       HEADING "PEAK %"   FORMAT 990.965 66SELECT resource_name,67       current_utilization AS current_util,68       max_utilization AS max_util,69       TRIM(limit_value) AS limit_value,70       CASE WHEN TRIM(limit_value) NOT IN ('UNLIMITED', '0')71            THEN ROUND(100 * max_utilization / TO_NUMBER(TRIM(limit_value)), 1) END AS max_pct72FROM v$resource_limit73WHERE resource_name IN ('processes', 'sessions', 'transactions', 'parallel_max_servers')74ORDER BY resource_name;75 76-- Not yet verified77PROMPT78PROMPT ==== 5 of 8: Session count by user and machine ====79CLEAR COLUMNS80SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF81COLUMN inst_id  HEADING "INST"     FORMAT 99982COLUMN username FORMAT A2083COLUMN machine  FORMAT A35 TRUNCATE84COLUMN total    HEADING "SESSIONS" FORMAT 99,99085COLUMN active   HEADING "ACTIVE"   FORMAT 99,99086 87SELECT inst_id, username, machine,88       COUNT(*) AS total,89       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active90FROM gv$session91WHERE type = 'USER'92GROUP BY inst_id, username, machine93ORDER BY total DESC;94 95-- Not yet verified96PROMPT97PROMPT ==== 6 of 8: Accounts locked, expired or expiring soon ====98CLEAR COLUMNS99SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF100COLUMN username       FORMAT A25101COLUMN account_status HEADING "STATUS"  FORMAT A25102COLUMN expires        FORMAT A10103COLUMN locked         FORMAT A10104COLUMN profile        FORMAT A20105 106SELECT username, account_status,107       TO_CHAR(expiry_date, 'YYYY-MM-DD') AS expires,108       TO_CHAR(lock_date, 'YYYY-MM-DD') AS locked,109       profile110FROM dba_users111WHERE oracle_maintained = 'N'112  AND (account_status <> 'OPEN' OR expiry_date < SYSDATE + 14)113ORDER BY expiry_date NULLS LAST, username;114 115-- Not yet verified116PROMPT117PROMPT ==== 7 of 8: Failed logins in the last 24 hours ====118CLEAR COLUMNS119SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF120COLUMN at_time     HEADING "TIME"    FORMAT A19121COLUMN dbusername  HEADING "DB USER" FORMAT A20122COLUMN os_username HEADING "OS USER" FORMAT A20123COLUMN userhost    HEADING "HOST"    FORMAT A30 TRUNCATE124COLUMN return_code HEADING "ORA-"    FORMAT 99999125 126SELECT TO_CHAR(event_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,127       dbusername, os_username, userhost, return_code128FROM unified_audit_trail129WHERE action_name = 'LOGON'130  AND return_code <> 0131  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY132ORDER BY event_timestamp DESC;133 134-- Not yet verified135PROMPT136PROMPT ==== 8 of 8: Alert log errors in the last 24 hours ====137CLEAR COLUMNS138SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF139COLUMN at_time HEADING "TIME" FORMAT A19140COLUMN message FORMAT A150 WORD_WRAPPED141 142SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,143       SUBSTR(message_text, 1, 300) AS message144FROM v$diag_alert_ext145WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY146  AND (message_text LIKE '%ORA-%'147       OR message_text LIKE '%TNS-%'148       OR message_text LIKE '%Checkpoint not complete%')149ORDER BY originating_timestamp DESC;150

-- denrepo runbook: Users can't connect
-- For ORA-12514, ORA-12519, ORA-01017 and friends, from a local SYSDBA session: is the instance open, is the PDB open, is the service registered, are there free processes, and is the account locked or expired. Also run Listener status and services (lsnrctl) at the OS prompt, and try the Connection string builder tool to rule out a bad connect string.
-- Source: https://denrepo.com/runbooks/connect/

SET ECHO OFF

-- Tested on 12c, 19c · verified Sep 2026
PROMPT
PROMPT ==== 1 of 8: Instance status on every node ====
--check status of instance:
CLEAR COLUMNS
SET PAGESIZE 100 TRIMOUT ON TAB OFF
set lines 700
column HOST_NAME format a50
select HOST_NAME, instance_name, version, status,logins, to_char(STARTUP_TIME,'MM/DD/YYYY HH24:MI:SS') as STARTUP_TIME, archiver from gv$instance order by instance_name;

-- Not yet verified
PROMPT
PROMPT ==== 2 of 8: Pluggable databases and their state ====
PROMPT (run on CDB root)
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN con_id     HEADING "CON" FORMAT 999
COLUMN name       HEADING "PDB" FORMAT A25
COLUMN open_mode  HEADING "OPEN MODE"  FORMAT A12
COLUMN restricted HEADING "RESTRICTED" FORMAT A10
COLUMN opened     FORMAT A16
COLUMN size_gb    HEADING "SIZE GB"    FORMAT 99,990.9

SELECT con_id, name, open_mode, restricted,
       TO_CHAR(open_time, 'YYYY-MM-DD HH24:MI') AS opened,
       ROUND(total_size / POWER(1024, 3), 1) AS size_gb
FROM v$pdbs
ORDER BY con_id;

-- Not yet verified
PROMPT
PROMPT ==== 3 of 8: Listener registration and services ====
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN name  FORMAT A20
COLUMN value FORMAT A120 WORD_WRAPPED

SELECT name, value
FROM v$parameter
WHERE name IN ('local_listener', 'remote_listener', 'service_names')
ORDER BY name;

COLUMN service      FORMAT A40
COLUMN network_name FORMAT A60
SELECT inst_id, name AS service, network_name
FROM gv$active_services
ORDER BY inst_id, name;

-- Not yet verified
PROMPT
PROMPT ==== 4 of 8: Processes and sessions against their limits ====
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN resource_name HEADING "RESOURCE" FORMAT A22
COLUMN current_util  HEADING "CURRENT"  FORMAT 999,990
COLUMN max_util      HEADING "PEAK"     FORMAT 999,990
COLUMN limit_value   HEADING "LIMIT"    FORMAT A10
COLUMN max_pct       HEADING "PEAK %"   FORMAT 990.9

SELECT resource_name,
       current_utilization AS current_util,
       max_utilization AS max_util,
       TRIM(limit_value) AS limit_value,
       CASE WHEN TRIM(limit_value) NOT IN ('UNLIMITED', '0')
            THEN ROUND(100 * max_utilization / TO_NUMBER(TRIM(limit_value)), 1) END AS max_pct
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions', 'transactions', 'parallel_max_servers')
ORDER BY resource_name;

-- Not yet verified
PROMPT
PROMPT ==== 5 of 8: Session count by user and machine ====
CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN inst_id  HEADING "INST"     FORMAT 999
COLUMN username FORMAT A20
COLUMN machine  FORMAT A35 TRUNCATE
COLUMN total    HEADING "SESSIONS" FORMAT 99,990
COLUMN active   HEADING "ACTIVE"   FORMAT 99,990

SELECT inst_id, username, machine,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active
FROM gv$session
WHERE type = 'USER'
GROUP BY inst_id, username, machine
ORDER BY total DESC;

-- Not yet verified
PROMPT
PROMPT ==== 6 of 8: Accounts locked, expired or expiring soon ====
CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN username       FORMAT A25
COLUMN account_status HEADING "STATUS"  FORMAT A25
COLUMN expires        FORMAT A10
COLUMN locked         FORMAT A10
COLUMN profile        FORMAT A20

SELECT username, account_status,
       TO_CHAR(expiry_date, 'YYYY-MM-DD') AS expires,
       TO_CHAR(lock_date, 'YYYY-MM-DD') AS locked,
       profile
FROM dba_users
WHERE oracle_maintained = 'N'
  AND (account_status <> 'OPEN' OR expiry_date < SYSDATE + 14)
ORDER BY expiry_date NULLS LAST, username;

-- Not yet verified
PROMPT
PROMPT ==== 7 of 8: Failed logins in the last 24 hours ====
CLEAR COLUMNS
SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN at_time     HEADING "TIME"    FORMAT A19
COLUMN dbusername  HEADING "DB USER" FORMAT A20
COLUMN os_username HEADING "OS USER" FORMAT A20
COLUMN userhost    HEADING "HOST"    FORMAT A30 TRUNCATE
COLUMN return_code HEADING "ORA-"    FORMAT 99999

SELECT TO_CHAR(event_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,
       dbusername, os_username, userhost, return_code
FROM unified_audit_trail
WHERE action_name = 'LOGON'
  AND return_code <> 0
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC;

-- Not yet verified
PROMPT
PROMPT ==== 8 of 8: Alert log errors in the last 24 hours ====
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN at_time HEADING "TIME" FORMAT A19
COLUMN message FORMAT A150 WORD_WRAPPED

SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,
       SUBSTR(message_text, 1, 300) AS message
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
  AND (message_text LIKE '%ORA-%'
       OR message_text LIKE '%TNS-%'
       OR message_text LIKE '%Checkpoint not complete%')
ORDER BY originating_timestamp DESC;