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

Get max value in partition

Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_MAX_LOG_TS(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/




CREATE  or replace FUNCTION GET_MAX_LOG_TS (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_DateVal Date;
   v_string varchar2(400);
BEGIN      
       v_string := 'SELECT max(log_ts)  v_DateVal FROM MESSAGE_LOG PARTITION('||p_PatitionName||')';

dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string into into v_DateVal;;

    RETURN v_DateVal;
END GET_max_log_ts;
/

list max value in partition

function

CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_LongVal LONG;
BEGIN
    SELECT HIGH_VALUE INTO v_LongVal
      FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = p_TableName
       AND PARTITION_NAME = p_PatitionName;

    RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/

calls function
Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='BOMR_MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_HIGH_VALUE_AS_DATE(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/

rebuild unusable indexes

Set serverout on size 1000000
declare
    cursor idx_cur IS
      select owner, index_name  from dba_indexes where status = 'UNUSABLE';
    v_string varchar2(400);
   
begin
    dbms_output.put_line('These indexes are in unusable.');
    for idx_rec in idx_cur
    loop
        exit when idx_cur%notfound;
           dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
           v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
           dbms_output.put_line(v_string);
           dbms_output.put_line(chr(10));
           execute immediate v_string ;
    end loop;
end;
/

stream_pool_size

check the spilling status and pool size recommendation of your Streams pool with following query.

select inst_id,STREAMS_POOL_SIZE_FOR_ESTIMATE,estd_spill_time, estd_unspill_time, streams_pool_size_factor

from gv$streams_pool_advice order by 1,5;

PERFORMANCE BOTTLENECK CHECK USING OS TOOLS

A.REAL TIME PERFORMANCE TUNING USING OS TOOLS


TOOL1:-TOP UTILITY

check in TOP with column S where it shows ‘R’ means running and ‘S’ means sleeping.


TOOL 2:-SAR UTILITY WITH -U OPTION TO CHECK CPU AND IO BOTTLENECK
sar -u 10 8

TOOL 3:VMSTAT REPORT
vmstat 1 10
r column is runnable processes

TOOL 4:TO IDENTIFY DISK BOTTLENECK
sar -d 5 2

TOOL 5:-SAR -B TO REPORT DISK USAGE

TOOL 6:-SAR -Q TO FIND PROCESSES UNDER QUEUE.WE NEED TO LOOK BLOCKED SECTION

TOOL 7:-TO IDENTIFY MEMORY USAGE USING SAR -R


TOOL 8. REAL TIME PERFORMANCE TUNING USING ORATOP REPORT
./oratop -f -d -i 10 / as sysdba

How to get all the alert.log file entry for a date

grep -B1 -A1 'Jan 19' alert.log

awk '/Jan 19/{c=6}c&&c --' alert.log