troubleshoot - scheduler job not running

troubleshoot - scheduler job not running

sqlplus  Scott/tiger

-- list scheduler job

set lines 120
col SCHEDULE_NAME format a25
col SCHEDULE_TYPE form   a10
prompt from dba_scheduler_jobs
prompt =============
select job_name, schedule_name, schedule_type, enabled from dba_scheduler_jobs where owner=upper('&schema') ;

-NOTE in the enabled column - TRUE means working, FALSE means not working

-- identify scheduler running jobs
 select
   rj.job_name,
   s.username,
   s.sid,
   s.serial#,
   p.spid,
   s.lockwait,
   s.logon_time
from 
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;



SELECT max(log_date) FROM user_scheduler_job_run_details where job_name='SCH_JOB_NAME_HERE'' 


-- to kill by sid


select 'alter system kill session '||chr(39)||s.sid||','||s.serial#||chr(39)||';'
from user_scheduler_running_jobs rj,
     v$session s,
     v$process p
where rj.session_id = s.sid
  and s.paddr = p.addr
order by rj.job_name;


-- to kill by by pid

select 'kill -9 '||p.spid
from
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;


useful commands
exec dbms_scheduler.disable('SCH_JOB_NAME_HERE');
exec dbms_scheduler.enable('"SCH_JOB_NAME_HERE"');

exec dbms_scheduler.stop_job('SCH_JOB_NAME_HERE');

date time in python

#!/usr/bin/env python

import io
import datetime
import traceback

AlertLogFile=r'test.dat'
EventDate=datetime.datetime(1, 1, 1, 0, 0)
OutputList=[]

try:
    with io.open(AlertLogFile,mode='r') as f:
        for line in f:
          EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%Y-%m-%d %H:%M:%S')
          # EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
          # OutputList.append([EventDate,line.rstrip('\n')])
          OutputList.append([EventDate,line.rstrip('\n')])
except:
    print(traceback.format_exc())

for o in OutputList:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
    print('[%s] %s' % (o[0],o[1]))
    # print('[%s]' % (o[0]))



Data
oracle@lnx21426> cat test.dat
2016-11-03 12:01:13
2017-04-05 12:03:28
oracle@lnx21426>

Use ADRCI or Support Workbench to package the incident.

step 1
adrci> show alert -p "message_text like '%incident%'"

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
Output the results to file: /ora01/oracle/temp/alert_33812_140499732489984_demo_1.ado


step 2
adrci> show problem

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 600 [ktbdchk1: bad dscn]                                80396                2018-08-19 16:39:16.523000 -04:00
1 rows fetched

step 3
adrci> show incident

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
80393                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:38:39.543000 -04:00
80394                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:14.585000 -04:00
80395                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:15.629000 -04:00
80396                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:16.523000 -04:00
4 rows fetched

step 4
show incident -mode detail -p "incident_id=80393"

step 5
First, create the package using the problem ID displayed by the SHOW PROBLEM command.
adrci > ips create package problem 1 correlate all

step 6
Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.
adrci > ips generate package 1 in "/tmp"

step 6
send the zip file to oracle

Step by Step: How to troubleshoot a slow running query in Oracle

 -- ----------------------------------------------------------------------

 # Step by Step: How to troubleshoot a slow running query in Oracle

 -  what are sessions doing

set lines 999
col osuser form a10
col username form a10
col spid form a10
select a.sid, a.serial#,
       spid ,
       sql_id,
       a.inst_id,
       a.osuser,
       a.username, status,
       substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
       substr(to_char(sql_exec_start,'dd-mon-yy hh24:mi'),1,18) sql_exec_start,
       substr(a.module,1,18) module ,
       substr(machine,1,17) machine,
       event
--       substr(a.program,1,16) program
from   gv$session a, gv$process b
where  a.username not in (' ','PATROL','SYS','DBSNMP')
and    b.addr=a.paddr
and    b.inst_id=a.inst_id
and    a.status='ACTIVE'
-- and    event='inactive transaction branch'
order by status desc, logon_time
/


--  blockers in standalone

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
       || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
       v$lock l1, v$session s1, v$lock l2, v$session s2
       where s1.sid=l1.sid and s2.sid=l2.sid
       and l1.block=1 and l2.request > 0
       and l1.id1=l2.id1
       and l2.id2=l2.id2;


--  blockers in rac
set pages 1000
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' Serial#= '||s1.serial# ||' Inst=' ||s1.INST_ID ||' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
   from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;
 
 
-- top
get pid

-- get sid using pid
select sid
from v$session s, v$process p
where p.spid = &pid
and s.paddr = p.addr;

-- get sqlId using sid
select sql_id
from v$session
where sid = &sid;

- get sql using sid
select sql_fulltext
from v$sql l, v$session s
where s.sid = &sid
and l.sql_id = s.sql_id;


# Check for the wait events:

-- Check for the particular user and session.
col "Description" format a50
select sid,
        decode(state, 'WAITING','Waiting',
                'Working') state,
        decode(state,
                'WAITING',
                'So far '||seconds_in_wait,
                'Last waited '||
                wait_time/100)||
        ' secs for '||event
        "Description"
from v$session
where username = 'GOS_USER';

select SID, osuser, machine, terminal, service_name,
       logon_time, last_call_et
from v$session
where username = 'GOS_USER';

--  Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
        decode(state, 'WAITING', 'Waiting',
                'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where sid = &sid
/


History of wait events in a specific session

set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
       average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &sid
/

-- check data access isue

select event
from v$session
where sid = &sid;

-- check data access waits
select SID, state, event, p1, p2
from v$session
where sid = &sid;


-- run sql advisor

1. Tuning task created for specific Sql id:


SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'bxa13by3718uw',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'bxa13by3718uw_tuning_task',
                          description => 'Tuning task for statement bxa13by3718uw.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


2. Executing the tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'bxa13by3718uw_tuning_task');

3. Displaying the recommendations:

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('bxa13by3718uw_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24


Based on the tuning advisor recommendation we have to take corrective actions. These recommendation could be:

1) Gather Statistics
2) Create Index
3) Drop Index
4) Join orders
5) Create sql profiles and many more

After corrective action from tuning advisor run the SQL again and see the improvement.

python program to check ocssd.log for error/issue/node eviction etc in last 48 hours

# python program to check ocssd.log for error/issue/node eviction etc

#!/usr/bin/env python

import io
import datetime
import traceback

DayList=['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
KeyWordList=['ORA-','Error','CRS-1013','Polling','Diskpingout']
# KeyWordList=['ORA-','Error','Starting ORACLE instance','Shutting down instance']
OutputList=[]

# customize to check last 48 hours in this case
SkipOldEventHours=48

# name and location of ocssd file . it has changed in 12c
AlertLogFile=r'/ora01/grid/11.2.0.4/grid/log/`hostname`/cssd/ocssd.log'

SkipOldEventDateTimeDelta=datetime.timedelta(hours=SkipOldEventHours)
EventDate=datetime.datetime(1, 1, 1, 0, 0)

try:
    with io.open(AlertLogFile,mode='r') as f:
        for line in f:
                for w in KeyWordList:
                    if w in line:
                        OutputList.append([EventDate,line.rstrip('\n')])
except:
    print(traceback.format_exc())

for o in OutputList:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
    print('[%s] %s' % (o[0],o[1]))

# the end

python program to check ocssd.log in oracle 11g for node eviction / issues

# python program to check ocssd.log in oracle 11g for node eviction / issues

# customize name/location of ocssd file: it has changed in 12g
infile = r'/ora01/grid/11.2.0.4/grid/log/`hostname`/cssd/ocssd.log'

important = []
keep_phrases = ["Diskpingout",
              "CRS-1013",
              "Polling"]

with open(infile) as f:
    f = f.readlines()

for line in f:
    for phrase in keep_phrases:
        if phrase in line:
            # important.append(line.rstrip('\n'))
            important.append([line.rstrip('\n')])
# OutputList.append([EventDate,line.rstrip('\n')])
            break

# print(important)
print infile
for o in important:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
    # print('[%s] %s' % (o[0],o[1]))
    print('[%s]' % (o[0]))

python program to check oracle alert log file for error in past 48 hours

# python program to check oracle alert log file for error in past 48 hours

#!/usr/bin/env python

import io
import datetime
import traceback

DayList=['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
KeyWordList=['ORA-','Error','alter']
# KeyWordList=['ORA-','Error','Starting ORACLE instance','Shutting down instance']
OutputList=[]

# customize hours to look for 
SkipOldEventHours=48

# customize name of alert.log
AlertLogFile=r'alert_cisdbc.log'

SkipOldEventDateTimeDelta=datetime.timedelta(hours=SkipOldEventHours)
EventDate=datetime.datetime(1, 1, 1, 0, 0)

try:
    with io.open(AlertLogFile,mode='r') as f:
        for line in f:
           if len(line)>3 and line[0:3] in DayList:
                # OutputList.append(line)              
                EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
                if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
                    continue        
           elif len(line)>3:
                if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
                    continue              
                for w in KeyWordList:
                    if w in line:
                        OutputList.append([EventDate,line.rstrip('\n')])
           else:
                continue
except:
    print(traceback.format_exc())

for o in OutputList:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format  
    print('[%s] %s' % (o[0],o[1]))

# the end

List TOP 10 by size directories

# List TOP 10 by size directories
du -m --max-depth 1 | sort -rn | head -11