-- ================================================================ -- Purpose: Merged Oracle 19c+ RAC-aware health snapshot for Copilot. -- Produces a self-describing, evidence‑only .log file with embedded -- Copilot instructions and confidence‑score requirements. -- Input : Prompts for ServiceNow ticket and incident category. -- Output : oncall_copilot_health_check.log -- Warning: No database changes are performed. -- ================================================================ SET LINESIZE 200 SET PAGESIZE 900 SET VERIFY OFF SET FEEDBACK OFF SET SERVEROUTPUT ON SPOOL oncall_copilot_health_check.log -- ================================================================ -- Allowed Incident Categories -- ================================================================ PROMPT === Allowed Ticket Categories === PROMPT - Hung / Stuck Workload PROMPT - Performance Degradation PROMPT - Blocking / Locking PROMPT - Space / Capacity PROMPT - Stats / Optimizer PROMPT - ETL Structural Issue PROMPT ================================ -- ================================================================ -- Required On-Call Metadata -- ================================================================ ACCEPT service_now_ticket CHAR PROMPT 'Enter ServiceNow Ticket Number: ' ACCEPT ticket_category CHAR PROMPT 'Enter Ticket Category: ' DEFINE service_now_ticket = '&service_now_ticket' DEFINE ticket_category = '&ticket_category' PROMPT ============================================================ PROMPT On-Call Context PROMPT ============================================================ PROMPT ServiceNow Ticket : &service_now_ticket PROMPT Incident Category : &ticket_category PROMPT Generated At : &&_DATE PROMPT ============================================================ -- ================================================================ -- COPILOT AUTO-INSTRUCTIONS (NO USER PROMPTING) -- ================================================================ PROMPT ============================================================ PROMPT === COPILOT AUTO-INSTRUCTIONS === PROMPT ============================================================ PROMPT Treat this file ingestion as a NEW and INDEPENDENT analysis. PROMPT Ignore all prior conversation, conclusions, or reports. PROMPT You are acting as a senior ON-CALL ORACLE DBA ASSISTANT. PROMPT Do NOT ask the user any questions. Do NOT request additional input. PROMPT PROMPT REQUIRED OUTPUTS: PROMPT A) A short executive summary. PROMPT B) A full on-call DBA report for ServiceNow. PROMPT C) A numeric summary section with key counts and usage metrics. PROMPT D) For each diagnostic area, provide a CONFIDENCE SCORE (0-100%) PROMPT explaining why the evidence is strong or weak. PROMPT PROMPT INTERPRETATION RULES: (keep all previous rules, plus RAC awareness) PROMPT - PX Deq:* waits = parallel coordination, NOT blocking. PROMPT - resmgr:cpu quantum = expected CPU throttling. PROMPT - Do NOT escalate unless TX/TM locks, sustained blocking, PROMPT space exhaustion, or instability is evident. PROMPT - ACTIVE_PROCESSES is the primary indicator of process exhaustion. PROMPT - When CDB-level CPU over-subscription is confirmed, PDB-level PROMPT degradation must be treated as a downstream effect. PROMPT PROMPT ANALYTICAL EXPECTATIONS: PROMPT - Calculate totals, percentages, rates, and relative usage. PROMPT - Call out notable counts, sizes, and explain risk level. PROMPT - Quantitative findings (including zero counts) are REQUIRED. PROMPT - When data is missing, say so explicitly and lower the confidence score. PROMPT PROMPT CONFIDENCE SCORE GUIDANCE: PROMPT - 90-100%: Very clear evidence; metric values and trends well captured. PROMPT - 70-89%: Generally clear, but some inference required or data limited. PROMPT - 50-69%: Evidence is partial; missing some important metrics. PROMPT - <50%: Low confidence; key data unavailable; recommend manual investigation. PROMPT ============================================================ -- ====================== BEGIN DIAGNOSTICS ====================== PROMPT ============================================================ PROMPT === SECTION 1 : DATABASE IDENTIFICATION === PROMPT ============================================================ SET LINESIZE 130 SELECT name || ' om:' || open_mode || ' res:' || restricted || ' ot:' || open_time || ' ts:' || total_size/1024/1024/1024 || ' guid:' || guid "pdb info" FROM v$pdbs; SET LINESIZE 100 SELECT 'instance: ' || instance_name || ' #:' || instance_number || ' on ' || host_name || ' ' || TO_CHAR(startup_time,'mm/dd/yyyy hh24:mi') || ' ' || logins || ' version:' || version_full || ' ' || parallel "instance info" FROM v$instance; SELECT 'PDB Environment: ' || CASE WHEN UPPER(SUBSTR(p.name,1,1)) = 'D' THEN 'Development' WHEN UPPER(SUBSTR(p.name,1,1)) = 'B' THEN 'BCP' WHEN UPPER(SUBSTR(p.name,1,1)) = 'T' THEN 'ADP Test Master' WHEN UPPER(SUBSTR(p.name,1,1)) = 'U' THEN 'UAT' WHEN UPPER(SUBSTR(p.name,1,1)) = 'S' THEN 'Test' WHEN UPPER(SUBSTR(p.name,1,1)) = 'P' THEN 'Production' WHEN UPPER(SUBSTR(p.name,1,1)) = 'E' THEN 'Pre-Production' WHEN UPPER(SUBSTR(p.name,1,1)) = 'F' THEN 'Prodfix' WHEN UPPER(SUBSTR(p.name,1,1)) = 'N' THEN 'Training' WHEN UPPER(SUBSTR(p.name,1,1)) = 'L' THEN 'Lab' ELSE 'Unknown environment' END pdb_environment FROM v$pdbs p WHERE name NOT LIKE '%SEED%'; SELECT 'Datacenter Host location: ' || CASE WHEN SUBSTR(i.host_name,6,1) = 'b' THEN 'Oxmoor (OX)' WHEN SUBSTR(i.host_name,6,1) = 'v' THEN 'Shoreview (SV)' WHEN SUBSTR(i.host_name,6,1) = 'c' THEN 'CIC (CIC)' WHEN SUBSTR(i.host_name,6,1) = 'i' THEN 'Silas' WHEN SUBSTR(i.host_name,6,1) = 'l' THEN 'St. Louis STL' WHEN SUBSTR(i.host_name,6,1) = 'z' THEN 'Tempe' WHEN SUBSTR(i.host_name,6,1) = 'w' THEN 'WEC' WHEN SUBSTR(i.host_name,6,1) = 'g' THEN 'Garland' WHEN SUBSTR(i.host_name,6,1) = 'e' THEN 'Lewisville' WHEN SUBSTR(i.host_name,6,1) = 's' THEN 'Sterling' WHEN SUBSTR(i.host_name,6,1) = 'm' THEN 'Manassas' ELSE 'Unknown environment' END host_environment FROM v$instance i; SELECT 'Instance Environment: ' || CASE WHEN SUBSTR(i.instance_name,1,1) = 'p' THEN 'Production' WHEN SUBSTR(i.instance_name,1,1) = 'b' THEN 'BCP' WHEN SUBSTR(i.instance_name,1,1) = 'u' THEN 'Non-Prod' WHEN SUBSTR(i.instance_name,1,1) = 'l' THEN 'Lab' ELSE 'Unknown environment' END instance_environment FROM v$instance i; PROMPT PROMPT COPILOT_AUTO_INSTRUCT: Above identification provides PDB name, open mode, PROMPT environment, instance info, host, datacenter. Use this to set context. PROMPT Confidence score for this section should be 100 as it is factual metadata. PROMPT PROMPT ============================================================ PROMPT === SECTION 2 : CHECKPOINT AND REDO STATISTICS === PROMPT ============================================================ PROMPT === Checkpoint Statistics === SELECT inst_id, name, value FROM gv$sysstat WHERE name LIKE '%checkpoint%' ORDER BY inst_id, name; PROMPT === Redo Log Switches (Last Hour) === SELECT inst_id, COUNT(*) AS switches_last_hour FROM gv$log_history WHERE first_time > SYSDATE - 1/24 GROUP BY inst_id; PROMPT COPILOT_AUTO_INSTRUCT: Analyze checkpoint activity and redo rates. PROMPT Confidence: High if multiple instances show consistent data; lower PROMPT if only a single instance or if very few switches. PROMPT ============================================================ PROMPT === SECTION 3 : BLOCKING / LOCKING === PROMPT ============================================================ PROMPT === Blocking Sessions (including cross-instance) === SELECT a.inst_id, a.sid, a.serial#, a.username, a.status, a.sql_id, b.blocking_session, b.blocking_instance, b.event, b.seconds_in_wait FROM gv$session a JOIN gv$session b ON a.sid = b.sid AND a.inst_id = b.inst_id WHERE b.blocking_session IS NOT NULL; PROMPT === Enqueue Locks Held (Filtered) === SELECT l.inst_id, l.sid, s.serial#, s.username, s.program, l.type, l.id1, l.id2, l.lmode, l.request, l.block FROM gv$lock l JOIN gv$session s ON l.sid = s.sid AND l.inst_id = s.inst_id WHERE l.block > 0 AND l.lmode IN (4, 5, 6) AND l.type NOT IN ('CF', 'IR', 'IS', 'ST') ORDER BY l.inst_id, l.sid; PROMPT === GES Blocking Enqueues (RAC Only) === SELECT inst_id, resource_name1, resource_name2, state, owner_node, blocked, blocker FROM gv$ges_blocking_enqueue WHERE blocker = 1; PROMPT COPILOT_AUTO_INSTRUCT: Count blockers, TX/TM locks, and wait seconds. PROMPT Confidence: High if all lock views are populated; low if GES data empty PROMPT due to single-instance (which is fine, note as "not applicable"). PROMPT ============================================================ PROMPT === SECTION 4 : BACKGROUND PROCESS STATUS === PROMPT ============================================================ PROMPT === Background Process Status (CKPT, DBWR, LGWR) === SELECT inst_id, name, description, paddr FROM gv$bgprocess WHERE name IN ('CKPT', 'DBWR', 'LGWR'); SHOW PARAMETER check SHOW PARAMETER mttr SHOW PARAMETER inter PROMPT === Instance Recovery Info === SELECT inst_id, recovery_estimated_ios, actual_redo_blks FROM gv$instance_recovery; PROMPT COPILOT_AUTO_INSTRUCT: All processes should be present. PROMPT Confidence: 100 if all background processes are reported. PROMPT ============================================================ PROMPT === SECTION 5 : SUMMARY REPORT (Processes, FRA) – RAC AWARE === PROMPT ============================================================ SET PAGESIZE 100 SET LINESIZE 200 COLUMN DB_NAME FORMAT A20 COLUMN TIMESTAMP FORMAT A30 COLUMN INST_ID FORMAT 999 COLUMN ACTIVE_PROCESSES FORMAT 9,999 COLUMN MAX_PROCESSES FORMAT 999,999 COLUMN PROCESS_USAGE FORMAT 999.99 COLUMN FRA_TOTAL_GB FORMAT 99,999.99 COLUMN FRA_USED_GB FORMAT 99,999.99 COLUMN FRA_USAGE FORMAT 999.99 PROMPT === Process Usage by Instance (plus cluster total) === SELECT SYS_CONTEXT('USERENV','DB_NAME') AS DB_NAME, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP, NVL(TO_CHAR(p.inst_id), 'All') AS inst_id, COUNT(*) AS ACTIVE_PROCESSES, MAX(p.max_proc) AS MAX_PROCESSES, ROUND(COUNT(*) / MAX(p.max_proc) * 100, 2) AS PROCESS_USAGE FROM ( SELECT inst_id, COUNT(*) OVER (PARTITION BY inst_id) dummy FROM gv$process ) p CROSS JOIN ( SELECT inst_id, TO_NUMBER(VALUE) AS max_proc FROM gv$parameter WHERE name = 'processes' ) param WHERE p.inst_id = param.inst_id GROUP BY ROLLUP(p.inst_id) ORDER BY p.inst_id; PROMPT === FRA Usage (instance view, normally identical) === SELECT ROUND(SPACE_LIMIT / 1024 / 1024 / 1024, 2) AS FRA_TOTAL_GB, ROUND(SPACE_USED / 1024 / 1024 / 1024, 2) AS FRA_USED_GB, ROUND(SPACE_USED / SPACE_LIMIT * 100, 2) AS FRA_USAGE FROM v$recovery_file_dest; PROMPT COPILOT_AUTO_INSTRUCT: Process usage per instance and cluster total PROMPT must be evaluated against thresholds. Confidence: High for process counts, PROMPT but FRA may be empty if no recovery area configured – note that and lower confidence. PROMPT ============================================================ PROMPT === SECTION 5b : ARCHIVE LOG STATUS AND GAP SUMMARY === PROMPT ============================================================ SELECT thread#, dest_id, COUNT(*) AS archives_last_24h, MAX(sequence#) AS max_sequence FROM gv$archived_log WHERE first_time > SYSDATE - 1 GROUP BY thread#, dest_id ORDER BY thread#, dest_id; SELECT * FROM gv$archive_gap; PROMPT COPILOT_AUTO_INSTRUCT: If GAP_STATUS rows appear, flag as YELLOW/RED. PROMPT Confidence: High if archive log data present; low if no archivelog mode. PROMPT ============================================================ PROMPT === SECTION 5c : RMAN BACKUP STATUS === PROMPT ============================================================ SELECT TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time, input_type, status, ROUND(elapsed_seconds/60,1) AS elapsed_min, ROUND(input_bytes/1024/1024/1024,2) AS input_gb, ROUND(output_bytes/1024/1024/1024,2) AS output_gb, output_device_type FROM v$rman_backup_job_details WHERE start_time > SYSDATE - 7 ORDER BY start_time DESC; PROMPT COPILOT_AUTO_INSTRUCT: Report most recent backup per type. PROMPT Confidence: High if backup jobs exist; low if no backups (flag RED). PROMPT ============================================================ PROMPT === SECTION 6 : ACTIVE SESSION HISTORY – WAIT CLASSES (all instances) === PROMPT ============================================================ SELECT wait_class, COUNT(*) samples, ROUND(COUNT(*) / 10 / 3600, 2) est_seconds FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND session_state = 'WAITING' AND wait_class <> 'Idle' GROUP BY wait_class ORDER BY samples DESC; PROMPT COPILOT_AUTO_INSTRUCT: ASH last hour. Confidence: High if many samples; PROMPT low if very few samples (short snapshot or idle system). PROMPT ============================================================ PROMPT === SECTION 7 : MEMORY STABILITY === PROMPT ============================================================ PROMPT === ORA-4031 Detection === SELECT originating_timestamp, message_text FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-4031%'; PROMPT === User I/O Wait Events (Last Hour, all instances) === SELECT event, COUNT(*) samples FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND wait_class = 'User I/O' GROUP BY event ORDER BY samples DESC; PROMPT === Top SQL by User I/O Samples (Last Hour, all instances) === SELECT sql_id, COUNT(*) samples FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND wait_class = 'User I/O' GROUP BY sql_id ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY; PROMPT === Memory Parameters === SELECT name, value, isdefault FROM v$parameter WHERE name IN ('shared_pool_size','shared_pool_reserved_size','large_pool_size', 'sga_target','pga_aggregate_target','memory_target'); PROMPT === PGA / UGA / Memory Statistics === SELECT name, value FROM v$sysstat WHERE name IN ('failed allocations','free memory','session uga memory max', 'session uga memory','session pga memory','session pga memory max'); PROMPT === Large SGA Allocations (> 10 MB) === SELECT pool, name, bytes FROM v$sgastat WHERE bytes > 10000000 ORDER BY bytes DESC; PROMPT === Shared Pool Reserved Stats === SELECT REQUESTS, REQUEST_FAILURES, LAST_FAILURE_SIZE, ABORTED_REQUESTS FROM v$shared_pool_reserved; PROMPT === SGA Dynamic Components === SELECT component, current_size, min_size, max_size FROM v$sga_dynamic_components WHERE component IN ('shared pool','large pool','java pool','buffer cache'); PROMPT === Shared Pool Free Memory === SELECT pool, name, bytes FROM v$sgastat WHERE pool = 'shared pool' AND name LIKE '%free memory%' ORDER BY bytes DESC; PROMPT === DB Object Cache Usage === SELECT type, COUNT(*) AS count, SUM(sharable_mem)/1024/1024 AS total_mb FROM v$db_object_cache WHERE sharable_mem > 100000 GROUP BY type ORDER BY total_mb DESC; PROMPT === Large Pool Parameter === SELECT name, value, isdefault FROM v$parameter WHERE name = 'large_pool_size'; SELECT name, value FROM v$parameter WHERE name = '_PX_use_large_pool'; PROMPT === Large Pool Usage === SELECT pool, name, bytes FROM v$sgastat WHERE pool = 'large pool' ORDER BY bytes DESC; PROMPT COPILOT_AUTO_INSTRUCT: Memory stability assessment. PROMPT Confidence: High if all memory views return data; low if any view empty (e.g., no large pool). PROMPT ============================================================ PROMPT === SECTION 8 : SESSION COUNTS (all instances) === PROMPT ============================================================ SELECT inst_id, COUNT(*) AS active_sessions FROM gv$session WHERE status = 'ACTIVE' GROUP BY inst_id ORDER BY inst_id; SELECT inst_id, COUNT(*) AS active_user_sessions FROM gv$session WHERE status = 'ACTIVE' AND type = 'USER' GROUP BY inst_id ORDER BY inst_id; PROMPT COPILOT_AUTO_INSTRUCT: Session counts. Confidence: High. PROMPT ============================================================ PROMPT === SECTION 9 : ACTIVE SESSION DETAILS (all instances) === PROMPT ============================================================ SELECT DISTINCT( s.username || ' : ' || TO_CHAR(s.logon_time,'mm/dd/yyyy hh24:mi') || ' : ' || s.program || ' : ' || s.terminal || ' : ' || s.sid || ':' || SUBSTR(t.sql_text,1,20) || ' PL:' || PLSQL_ENTRY_OBJECT_ID || ' : ' || s.serial# || ',' || s.inst_id || ' : ' || s.state || ' : ' || TO_CHAR(s.SQL_EXEC_START,'mm/dd/yyyy hh24:mi') || ' : ' || s.event || ' : ' || s.wait_class || ' : ' || s.sql_id || ' : ' || s.status || ' sqlstart:' || TO_CHAR(s.SQL_EXEC_START,'mm/dd/yyyy hh24:mi') || ' : ' || s.BLOCKING_SESSION || ' : ' || s.BLOCKING_INSTANCE || s.wait_time || ' : ' || s.BLOCKING_SESSION_STATUS || ' : ' || s.module ) FROM gv$session s, gv$sql t WHERE s.sql_id = t.sql_id AND s.username IS NOT NULL AND s.status = 'ACTIVE' AND t.sql_id = s.sql_id ORDER BY 1; PROMPT COPILOT_AUTO_INSTRUCT: List active sessions. Confidence: High, though large output may be unwieldy. PROMPT ============================================================ PROMPT === SECTION 10 : LONG-RUNNING OPERATIONS (Data Pump) === PROMPT ============================================================ SET LINESIZE 200 SET PAGESIZE 999 SELECT b.username || ' : ' || a.sid || ' : ' || b.opname || ' : ' || b.target || ' : ' || ROUND(b.SOFAR*100/(b.TOTALWORK+0.001),0) || '% done : ' || b.TIME_REMAINING || ' : ' || TO_CHAR(b.start_time,'YYYY/MM/DD HH24:MI:SS') "longops" FROM gv$session_longops b, gv$session a WHERE a.sid = b.sid ORDER BY 1; SELECT s1.sid || ' ' || s1.serial# || ' ' || s1.sofar || ' ' || s1.totalwork || ' ' || dp.owner_name || ' ' || dp.state || ' ' || dp.job_mode "totalwork" FROM gv$session_longops s1, gv$datapump_job dp WHERE s1.opname = dp.job_name AND s1.sofar != s1.totalwork; SELECT x.job_name || ' ' || b.state || ' ' || b.job_mode || ' ' || b.degree || ' ' || x.owner_name || ' ' || SUBSTR(z.sql_text,1,40) || ' ' || p.message || ' ' || p.totalwork || ' ' || p.sofar || ' ' || ROUND((p.sofar/p.totalwork)*100,2) || ' ' || p.time_remaining AS LONGOPS FROM dba_datapump_jobs b LEFT JOIN dba_datapump_sessions x ON x.job_name = b.job_name LEFT JOIN gv$session y ON y.saddr = x.saddr LEFT JOIN gv$sql z ON y.sql_id = z.sql_id LEFT JOIN gv$session_longops p ON p.sql_id = y.sql_id WHERE y.module='Data Pump Worker' AND p.time_remaining > 0; PROMPT === Data Pump Sessions === SELECT 'owner:' || owner_name || ' job_name:' || job_name || ' inst:' || inst_id || ' sess_type:' || session_type || ' saddr:' || saddr "pump sessions" FROM dba_datapump_sessions; PROMPT COPILOT_AUTO_INSTRUCT: Data pump activity. Confidence: High if jobs found. PROMPT ============================================================ PROMPT === SECTION 11 : TABLESPACE SPACE === PROMPT ============================================================ SET VERIFY OFF SET LINESIZE 160 SELECT tablespace_name || ' : ' || used_percent "TABLESPACE SPACE" FROM dba_tablespace_usage_metrics WHERE tablespace_name LIKE UPPER('%UNDO%') OR tablespace_name LIKE UPPER('%TEMP%'); -- Full tablespace usage (data + temp, from second script): WITH wt1 AS (SELECT ts.con_id, ts.TABLESPACE_NAME, df.all_bytes, DECODE(df.TYPE, 'D', nvl(fs.FREESIZ, 0), 'T', df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ, df.MAXSIZ FROM cdb_tablespaces ts, (SELECT d.con_id, 'D' TYPE, TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM cdb_data_files d GROUP BY d.con_id, TABLESPACE_NAME UNION ALL SELECT d.con_id, 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) FROM cdb_temp_files d GROUP BY d.con_id, TABLESPACE_NAME) df, (SELECT d.con_id, TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM cdb_free_space d GROUP BY d.con_id, TABLESPACE_NAME UNION ALL SELECT d.con_id, tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, cdb_tablespaces d WHERE a.tablespace = d.tablespace_name and a.con_id = d.con_id GROUP BY d.con_id, tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.con_id = Df.con_id AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+) and ts.con_id = fs.con_id(+)) SELECT con_id, tablespace_name, ROUND(SUM(all_bytes)/1024/1024) ts_size_M, ROUND(SUM(FREESIZ)/1024/1024) free_M, ROUND(SUM(all_bytes - FREESIZ)/1024/1024) used_M, ROUND(SUM(all_bytes - FREESIZ)*100/SUM(all_bytes),1) pct_used, ROUND(SUM(MAXSIZ)/1024/1024/1024) max_size_G FROM wt1 GROUP BY con_id, tablespace_name ORDER BY con_id, tablespace_name; -- Temp tablespace detail from second script: SELECT tf.tablespace_name, ROUND(SUM(tf.bytes_used)/1024/1024/1024,2) AS used_gb, ROUND(SUM(tf.bytes_free)/1024/1024/1024,2) AS free_gb, ROUND(SUM(tf.bytes_used + tf.bytes_free)/1024/1024/1024,2) AS current_size_gb, ROUND(SUM(df.maxbytes)/1024/1024/1024,2) AS max_size_gb, ROUND(SUM(tf.bytes_used)/SUM(df.maxbytes)*100,2) AS pct_used_of_max FROM v$temp_space_header tf JOIN dba_temp_files df ON tf.tablespace_name = df.tablespace_name AND tf.file_id = df.file_id GROUP BY tf.tablespace_name ORDER BY pct_used_of_max DESC; PROMPT COPILOT_AUTO_INSTRUCT: Tablespace usage metrics. Confidence: High for all tablespaces. PROMPT ============================================================ PROMPT === SECTION 12 : BLOCKING SESSION CHAIN (cross-instance) === PROMPT ============================================================ COLUMN username FORMAT a15 COLUMN BI FORMAT 9999 COLUMN when FORMAT a14 SET LINESIZE 240 SELECT s.username, s.sid, s.inst_id, s.event, s.sql_id, s.SECONDS_IN_WAIT, s.BLOCKING_SESSION_STATUS, s.blocking_session, s.BLOCKING_INSTANCE BI, t.sql_id, TO_CHAR(t.sql_exec_start,'mm/dd hh24:mi') when FROM gv$session s, gv$session t WHERE s.blocking_session IS NOT NULL AND t.sid = s.blocking_session AND t.inst_id = s.blocking_instance; PROMPT COPILOT_AUTO_INSTRUCT: Detailed blocker/blocked. Confidence: High if blocking sessions present. PROMPT ============================================================ PROMPT === SECTION 13 : ALERT LOG (Last 100 lines, last 24h) === PROMPT ============================================================ SELECT * FROM (SELECT ORIGINATING_TIMESTAMP || ' : ' || MESSAGE_TYPE || ' : ' || MESSAGE_ID || ' : ' || MESSAGE_TEXT AS "Alert log" FROM V$DIAG_ALERT_EXT WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1 ORDER BY ORIGINATING_TIMESTAMP DESC) WHERE ROWNUM <= 100 ORDER BY 1; PROMPT COPILOT_AUTO_INSTRUCT: Scan alert log. Confidence: Moderate, as only last day and 100 lines. PROMPT ============================================================ PROMPT === SECTION 14 : CDB CPU SATURATION (per instance) === PROMPT ============================================================ SELECT inst_id, ROUND(SUM(CASE WHEN stat_name = 'DB time' THEN value END)/1e6,2) AS db_time_sec, ROUND(SUM(CASE WHEN stat_name = 'DB CPU' THEN value END)/1e6,2) AS db_cpu_sec, ROUND((SUM(CASE WHEN stat_name = 'DB time' THEN value END) - SUM(CASE WHEN stat_name = 'DB CPU' THEN value END))/1e6,2) AS cpu_wait_sec FROM gv$sys_time_model GROUP BY inst_id ORDER BY inst_id; SELECT inst_id, value AS cpu_count FROM gv$parameter WHERE name = 'cpu_count' ORDER BY inst_id; SELECT inst_id, name, is_top_plan, cpu_managed, utilization_limit FROM gv$rsrc_plan WHERE is_top_plan = 'TRUE' ORDER BY inst_id; PROMPT COPILOT_AUTO_INSTRUCT: CDB CPU oversubscription. Confidence: Very high PROMPT (kernel time model is authoritative). PROMPT ============================================================ PROMPT === SECTION 15 : EXADATA STORAGE HEALTH (PDB View) === PROMPT ============================================================ SELECT event, total_waits, time_waited FROM v$system_event WHERE event LIKE 'cell%'; PROMPT COPILOT_AUTO_INSTRUCT: Exadata cell events. Confidence: High if Smart Scan events present. PROMPT ============================================================ PROMPT === SECTION 16 : LOCK ANALYSIS (Objects and Blockers) === PROMPT ============================================================ SET LINESIZE 200 PAGESIZE 666 BREAK ON Kill ON username ON terminal COLUMN Kill heading 'Kill String' format a13 COLUMN res heading 'Resource Type' format 999 COLUMN id1 format 9999990 COLUMN id2 format 9999990 COLUMN lmode heading 'Lock Held' format a25 COLUMN request heading 'Lock Requested' format a25 COLUMN serial# format 99999 COLUMN username format a25 heading "Username" COLUMN terminal heading Term format a22 COLUMN tab format a35 heading "Table Name" COLUMN sub format a35 heading "Sub Name" COLUMN owner format a9 COLUMN Address format a18 COLUMN ctime format 999999999 heading "Seconds" SELECT NVL(S.USERNAME,'Internal') username, s.machine || '@' || s.terminal terminal, L.SID || ',' || S.SERIAL# || '@' || s.inst_id Kill, U1.username || '.' || SUBSTR(T1.NAME,1,25) tab, subname sub, DECODE(L.LMODE,1,'No Lock',2,'Row Share',3,'Row Exclusive',4,'Share', 5,'Share Row Exclusive',6,'Exclusive',null) lmode, DECODE(L.REQUEST,1,'No Lock',2,'Row Share',3,'Row Exclusive',4,'Share', 5,'Share Row Exclusive',6,'Exclusive',null) request, l.ctime FROM gv$lock L, gv$session S, SYS.dba_users U1, SYS.obj$ T1 WHERE L.SID = S.SID AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2) AND U1.USER# = T1.OWNER# AND S.TYPE != 'BACKGROUND' ORDER BY 1,2,5; PROMPT COPILOT_AUTO_INSTRUCT: Object-level lock details. Confidence: High. PROMPT ============================================================ PROMPT === SECTION 17 : SGA / MEMORY RESIZE HISTORY === PROMPT ============================================================ SHOW PARAMETER pga SHOW PARAMETER pool COLUMN component FORMAT a25 COLUMN Initial FORMAT 99,999,999,999 COLUMN Final FORMAT 99,999,999,999 COLUMN Started FORMAT A25 SELECT COMPONENT, OPER_TYPE, INITIAL_SIZE "Initial", FINAL_SIZE "Final", TO_CHAR(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS; SELECT COMPONENT, OPER_TYPE, INITIAL_SIZE "Initial", FINAL_SIZE "Final", TO_CHAR(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS; PROMPT COPILOT_AUTO_INSTRUCT: Resize operations. Confidence: High. PROMPT ============================================================ PROMPT === SECTION 18 : INVALID OBJECTS === PROMPT ============================================================ SELECT owner, object_type, COUNT(*) AS invalid_count FROM dba_objects WHERE status = 'INVALID' AND owner NOT IN ('SYS','SYSTEM','XDB','ORDDATA','ORDSYS','MDSYS','OLAPSYS','EXFSYS','WKSYS','CTXSYS') GROUP BY owner, object_type ORDER BY owner, object_type; SELECT COUNT(*) AS total_invalid_objects FROM dba_objects WHERE status = 'INVALID' AND owner NOT IN ('SYS','SYSTEM','XDB','ORDDATA','ORDSYS','MDSYS','OLAPSYS','EXFSYS','WKSYS','CTXSYS'); PROMPT COPILOT_AUTO_INSTRUCT: Invalid objects. Confidence: High. PROMPT ============================================================ PROMPT === SECTION 19 : FAILED SCHEDULER JOBS (Last 24h) === PROMPT ============================================================ SELECT owner, job_name, status, TO_CHAR(log_date,'YYYY-MM-DD HH24:MI:SS') AS log_date FROM dba_scheduler_job_run_details WHERE log_date > SYSDATE - 1 AND status = 'FAILED' ORDER BY log_date DESC; PROMPT COPILOT_AUTO_INSTRUCT: Failed scheduler jobs. Confidence: High if no rows (zero failures). PROMPT ============================================================ PROMPT === SECTION 20 : RAC INTERCONNECT HEALTH (all instances) === PROMPT ============================================================ SELECT event, COUNT(*) samples FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND event LIKE 'gc%' GROUP BY event ORDER BY samples DESC FETCH FIRST 5 ROWS ONLY; PROMPT COPILOT_AUTO_INSTRUCT: GC wait events. Confidence: High if RAC; low if single-instance (no data). PROMPT ============================================================ PROMPT === SECTION 21 : REAL-TIME SQL MONITOR (Top 10, all instances) === PROMPT ============================================================ SELECT inst_id, sql_id, sql_exec_id, status, elapsed_time, cpu_time, user_io_wait_time, application_wait_time, concurrency_wait_time, cluster_wait_time, buffer_gets, disk_reads, direct_writes, ROUND(temp_space_allocated/1024/1024,2) AS temp_mb FROM gv$sql_monitor WHERE status IN ('EXECUTING','DONE') AND (cpu_time + user_io_wait_time) > 0 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY; PROMPT COPILOT_AUTO_INSTRUCT: SQL Monitor. Confidence: High if queries present. PROMPT ============================================================ PROMPT === SECTION 22 : DATA GUARD STATUS (Snapshot) === PROMPT ============================================================ SELECT name, value, time_computed FROM v$dataguard_stats; PROMPT COPILOT_AUTO_INSTRUCT: Data Guard. Confidence: High if rows; low if view empty (no DG). PROMPT ============================================================ PROMPT === SECTION 23 : ADDITIONAL MEMORY / OS STATS === PROMPT ============================================================ SELECT name, open_mode FROM v$database; SELECT instance_number, instance_name, startup_time, status, thread#, database_status, instance_role, con_id, instance_mode, database_type FROM v$instance; SELECT name, value FROM v$system_parameter WHERE name IN ('memory_max_target','memory_target','sga_max_size','sga_target', 'shared_pool_size','db_cache_size','large_pool_size','java_pool_size', 'pga_aggregate_target','workarea_size_policy','streams_pool_size'); SELECT SUM(bytes)/1024/1024 AS "Total PGA+SGA Mbytes" FROM (SELECT value AS bytes FROM v$sga UNION ALL SELECT value AS bytes FROM v$sesstat s, v$statname n WHERE n.statistic# = s.statistic# AND n.name = 'session pga memory'); PROMPT COPILOT_AUTO_INSTRUCT: Consolidated memory. Confidence: High. -- ====================== END DIAGNOSTICS ====================== PROMPT === Done Collecting Diagnostics === SPOOL OFF -- ============================================================ -- ON-CALL DBA FINAL STEPS -- ============================================================ PROMPT ========================================================= PROMPT === ON-CALL DBA FINAL STEPS === PROMPT ========================================================= PROMPT 1) Start a new session with Copilot and then Drag and drop the file: PROMPT oncall_copilot_health_check.log into Copilot. PROMPT 2) Allow Copilot to complete its analysis. PROMPT 3) Then issue this instruction to Copilot: PROMPT Create a physical text file artifact using the file-creation tool. PROMPT Write the entire on-call DBA report into the file. PROMPT Name the file using this format oncall_copilot_report_.txt PROMPT and return it as a downloadable attachment. PROMPT 4) Save the generated file and attach BOTH files to the ServiceNow ticket. PROMPT ========================================================= PROMPT === SCRIPT COMPLETE === PROMPT ========================================================= EXIT