srdc_scheduler_job.sql as per Doc ID 2077182.1

 REM SRDC_SCHEDULER_JOB.sql 
REM collect Job Queue parameters,Notification configuration for troubleshooting scheduler notification issues
Rem
Rem Copyright (c) 2006, 2015 Oracle. All rights reserved.
Rem
Rem   NAME
Rem      srdc_scheduler_job.sql - script to collect diagnostic details for troubleshooting scheduler notification issues
Rem
Rem   NOTES
Rem      * This script collects the data related to Scheduler & AQ  notification 
Rem * The script creates a spool output. Upload it to the Service Request
Rem      * This script contains some checks which might not be relevant for all versions.
Rem      * This script will *not* update any data.
Rem      * This script must be run using SQL*PLUS.
Rem      * You must be connected AS SYSDBA to run this script.
Rem
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    slabraha   12/17/19 - updated the header
Rem    slabraha   12/17/19 - updated the script
Rem    gokamala   12/17/19 - created the script
Rem
Rem
Rem
define SRDCNAME='DB_JobScheduler'
set pagesize 200 verify off sqlprompt "" term off entmap off echo off
set markup html on spool on
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'|| to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
spool &&SRDCSPOOLNAME..htm
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'|| to_char(sysdate,'YYYYMMDD_HH24MISS') title from v$instance;
select 'Diagnostic-Name  ' "Diagnostic-Name ", '&&SRDCNAME' "Report Info" from dual
union all
select 'Time  ' , to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
union all
select 'Machine  ' , host_name from v$instance
union all
select 'Version  ',version from v$instance
union all
select 'DBName  ',name from v$database
union all
select 'Instance  ',instance_name from v$instance
/
set echo on
set serveroutput on

--********************** Parameters & Configuration **********************
SELECT name, value FROM v$parameter WHERE upper(name) in ('COMPATIBLE','JOB_QUEUE_PROCESSES','RESOURCE_LIMIT','STATISTICS_LEVEL','RESOURCE_MANAGER_PLAN','AQ_TM_PROCESS');

--SESSIONTIMEZONE
SELECT SESSIONTIMEZONE FROM DUAL;

--DBMS_SCHEDULER.STIME
SELECT DBMS_SCHEDULER.STIME FROM DUAL;



--DBA_SCHEDULER_GLOBAL_ATTRIBUTE
SELECT * FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

--DBA_OBJECTS
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'DBMS_SCHEDULER';


---********************** DBA Jobs and Running Details *******************
SELECT job, What,priv_user,schema_user, last_date, Next_Date, Interval,Failures FROM DBA_JOBS ORDER BY LAST_DATE DESC;

SELECT SID as Session_ID, job, this_Date, instance, failures from DBA_JOBS_RUNNING;

---********************** External Job and Scheduler Run Details  *******************

SELECT OWNER,JOB_NAME,JOB_ACTION,PROGRAM_NAME,STATE FROM DBA_SCHEDULER_JOBS WHERE job_type = 'EXECUTABLE';

SELECT STATUS, JOB_NAME, ERROR#, ADDITIONAL_INFO, TO_CHAR(LOG_DATE, 'MM/DD/YYYY HH24:MI:SS') LOG_DATE FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME IN (SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_TYPE = 'EXECUTABLE') ORDER BY LOG_DATE;

---**********************External Job Credential  ******************* 

SELECT OWNER,JOB_NAME,JOB_ACTION,PROGRAM_NAME,STATE,CREDENTIAL_NAME FROM DBA_SCHEDULER_JOBS WHERE job_type = 'EXECUTABLE';

SELECT * FROM DBA_SCHEDULER_CREDENTIALS WHERE CREDENTIAL_NAME in (SELECT CREDENTIAL_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_TYPE = 'EXECUTABLE');



--********************** AQ SCHEDULER JOBS & EVENTS **********************

SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'AQ$_PLSQL_NTFN_%'
/

SELECT COUNT(*), MSG_STATE, QUEUE, CONSUMER_NAME FROM AQ$SCHEDULER$_EVENT_QTAB GROUP BY MSG_STATE, QUEUE, CONSUMER_NAME
/

SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE '%AQ_SRVNTFN_TABLE%'
/

SELECT COUNT(*), STATE, Q_NAME FROM AQ_SRVNTFN_TABLE_1 GROUP BY STATE, Q_NAME
/

SELECT COUNT(*), STATE, Q_NAME FROM AQ_SRVNTFN_TABLE_2 GROUP BY STATE, Q_NAME
/

SELECT COUNT(*) from SCHEDULER$_EVENT_QTAB
/

--********************** QUEUE & SUBSCRIBERS **********************

SELECT QUEUE_TABLE, MAX_RETRIES, RETRY_DELAY, RETENTION FROM DBA_QUEUES WHERE NAME = 'SCHEDULER$_EVENT_QUEUE'
/

SELECT CONSUMER_NAME FROM DBA_QUEUE_SUBSCRIBERS WHERE QUEUE_NAME = 'SCHEDULER$_EVENT_QUEUE'
/

SELECT * FROM DBA_QUEUES
/
--********************** SCHEDULER JOB & NOTIFICATIONS  **********************

SELECT OWNER, JOB_NAME, EVENT_QUEUE_NAME, EVENT_CONDITION, STATE,FAILURE_COUNT, LAST_START_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME IN (SELECT JOB_NAME FROM DBA_SCHEDULER_NOTIFICATIONS)
/

SELECT B.USERNAME, A.SUBSCRIPTION_NAME FROM DBA_SUBSCR_REGISTRATIONS A, DBA_USERS B WHERE SUBSCRIPTION_NAME LIKE '%SCHED$_AGT2%' AND A.USER#=B.USER_ID
/

SELECT * FROM DBA_SCHEDULER_NOTIFICATIONS
/

--********************** AUTOTASK JOB DETAILS **********************
SELECT * FROM DBA_AUTOTASK_CLIENT;
SELECT * FROM DBA_AUTOTASK_CLIENT_HISTORY;
SELECT * FROM DBA_AUTOTASK_CLIENT_JOB;
SELECT * FROM DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
SELECT * FROM DBA_AUTOTASK_OPERATION;
SELECT * FROM DBA_AUTOTASK_SCHEDULE order by START_TIME;
SELECT * FROM DBA_AUTOTASK_TASK;
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
SELECT * FROM DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;


---********************** DBA_SCHEDULER_RUNNING_JOBS DETAILS *******************

SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS;
SELECT * FROM DBA_SCHEDULER_JOBS;
SELECT * FROM DBA_SCHEDULER_WINDOWS;
SELECT * FROM DBA_SCHEDULER_WINDOW_DETAILS WHERE LOG_DATE >= SYSDATE-7 ORDER BY LOG_DATE DESC; 
SELECT * FROM DBA_SCHEDULER_WINDOW_LOG ORDER BY LOG_DATE DESC; 
SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE LOG_DATE >= SYSDATE-7 ORDER BY LOG_DATE DESC;
SELECT * FROM DBA_SCHEDULER_JOB_LOG WHERE LOG_DATE >= SYSDATE-7 ORDER BY LOG_DATE DESC;
SELECT program_name, program_action, enabled FROM DBA_SCHEDULER_programs;

--DBA_SCHEDULER_JOBS and DBA_SCHEDULER_PROGRAMS
select a.owner "job Owner",a.JOB_NAME, a.PROGRAM_OWNER,a.PROGRAM_NAME,b.program_type,b.program_action,b.enabled from dba_scheduler_jobs a, dba_scheduler_programs b where a.PROGRAM_NAME=b.PROGRAM_NAME and a.PROGRAM_OWNER=b.owner;

--DBA_SCHEDULER_WINGROUP_MEMBERS
SELECT WINDOW_GROUP_NAME, WINDOW_NAME FROM DBA_SCHEDULER_WINGROUP_MEMBERS group by WINDOW_GROUP_NAME, WINDOW_NAME;

--DBA_SCHEDULER_JOB_CLASSES
SELECT JOB_CLASS_NAME , RESOURCE_CONSUMER_GROUP, SERVICE FROM DBA_SCHEDULER_JOB_CLASSES;

--DBA_SCHEDULER_JOBS_AND_DBA_SCHEDULER_JOB_CLASSES
SELECT J.JOB_NAME, J.SCHEDULE_NAME, JC.JOB_CLASS_NAME, JC.RESOURCE_CONSUMER_GROUP,JC.SERVICE FROM DBA_SCHEDULER_JOBS J, DBA_SCHEDULER_JOB_CLASSES JC WHERE J.JOB_CLASS = JC.JOB_CLASS_NAME;

--DBA_SCHEDULER_RUNNING_JOBS_SLAVE_OS_PROCESS_ID
SELECT JOB_NAME,SLAVE_OS_PROCESS_ID FROM DBA_SCHEDULER_RUNNING_JOBS WHERE SLAVE_OS_PROCESS_ID NOT IN (SELECT SPID FROM V$PROCESS);

--DBA_PROFILES
SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME ='SESSIONS_PER_USER' AND 
PROFILE IN(SELECT DISTINCT PROFILE FROM DBA_USERS WHERE USERNAME IN (SELECT DISTINCT OWNER FROM DBA_SCHEDULER_JOBS));

--DBA_SCHEDULER_CHAINS
SELECT * FROM DBA_SCHEDULER_CHAINS;

--USER_SCHEDULER_NOTIFICATIONS
SELECT JOB_NAME, RECIPIENT, EVENT, FILTER_CONDITION FROM USER_SCHEDULER_NOTIFICATIONS;

---DBA_SCHEDULER_RUNNING_CHAINS
SELECT OWNER,JOB_NAME,CHAIN_OWNER,CHAIN_NAME,STEP_NAME,STATE,ERROR_CODE,COMPLETED,START_DATE,END_DATE FROM DBA_SCHEDULER_RUNNING_CHAINS 
ORDER BY OWNER, JOB_NAME, CHAIN_NAME, STEP_NAME;

--ALL_SCHEDULER_WINDOW_DETAILS
SELECT * FROM ALL_SCHEDULER_WINDOW_DETAILS WHERE ((REQ_START_DATE != ACTUAL_START_DATE ) OR (WINDOW_DURATION != ACTUAL_DURATION)) and LOG_DATE > sysdate-7;

Rem===========================================================================================================================================
spool off
set markup html off spool off
set sqlprompt "SQL> " term on  echo off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set verify on echo on
Rem===========================================================================================================================================

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more