Google it ....

Showing posts with label JOBS. Show all posts
Showing posts with label JOBS. Show all posts

Thursday, October 26, 2017

DBA_JOBS_RUNNING view is not showing all running DBMS_JOBS across the nodes in RAC

If you have Real Application Clusters Database (RAC) and you want to see currently running DBMS_JOBS across all nodes DBA_JOBS_RUNNING view in RAC is not showing all running jobs in Oracle 11g, It shows running jobs on instance where you querying DBA_JOBS_RUNNING, because it uses v$lock instead of gv$lock. It Seems like that:

create or replace view dba_jobs_running as
select v.SID, v.id2 JOB, j.FAILURES,
    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
    j.field1 INSTANCE
  from sys.job$ j, v$lock v
  where v.type = 'JQ' and j.job (+)= v.id2;
comment on column DBA_JOBS_RUNNING.SID is 'Identifier of process which is executing the job.  See v$lock.';
comment on column DBA_JOBS_RUNNING.JOB is 'Identifier of job.  This job is currently executing.';
comment on column DBA_JOBS_RUNNING.FAILURES is 'How many times has this job started and failed since its last success?';
comment on column DBA_JOBS_RUNNING.LAST_DATE is 'Date that this job last successfully executed';
comment on column DBA_JOBS_RUNNING.LAST_SEC is 'Same as LAST_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS_RUNNING.THIS_DATE is 'Date that this job started executing (usually null if not executing)';
comment on column DBA_JOBS_RUNNING.THIS_SEC is 'Same as THIS_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS_RUNNING.INSTANCE is 'The instance number restricted to run the job';

In RAC we can use next script to find all running jobs across all nodes:

select v.SID,
       v.id2 JOB,
       j.FAILURES,
       LAST_DATE,
       substr(to_char(last_date, 'HH24:MI:SS'), 1, 8) LAST_SEC,
       THIS_DATE,
       substr(to_char(this_date, 'HH24:MI:SS'), 1, 8) THIS_SEC,
       v.INST_ID instance
  from sys.job$ j, gv$lock v
 where v.type = 'JQ'
   and j.job(+) = v.id2;

Also you can create some view according to this query and call it dba_jobs_running_rac or something like that and in RAC database use that view to find all running jobs across all nodes.
For example :
create or replace view dba_jobs_running_rac as
select v.SID,
       v.id2 JOB,
       j.FAILURES,
       LAST_DATE,
       substr(to_char(last_date, 'HH24:MI:SS'), 1, 8) LAST_SEC,
       THIS_DATE,
       substr(to_char(this_date, 'HH24:MI:SS'), 1, 8) THIS_SEC,
       v.INST_ID instance
  from sys.job$ j, gv$lock v
 where v.type = 'JQ'
   and j.job(+) = v.id2;
select * from dba_jobs_running_rac

Wednesday, January 21, 2015

ORA-27369: job of type EXECUTABLE failed with exit code: 255

When I was trying to execute backup script from dbms_scheduler job, appears next error:
ORA-27369: job of type EXECUTABLE failed with exit code: 255
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2
job looks like
begin
  sys.dbms_scheduler.create_job(job_name            => 'BACKUP_DB',
                                job_type            => 'EXECUTABLE',
                                job_action          => '/fra/backup_script/backup_full_disk.sh',
                                schedule_name       => 'BACKUP_DATABASE',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;

schedule is next
begin
  sys.dbms_scheduler.create_schedule(
schedule_name   => 'BACKUP_DATABASE',
start_date      => SYSDATE,
repeat_interval =>'Freq=Daily;
Interval=1;ByHour=09;ByMinute=00;
BySecond=00',
end_date        => to_date(null),
comments        => '');
end;

manual execution script:
begin
dbms_scheduler.run_job('BACKUP_DB');
end;

solution is very simple:
The script I was trying to execute was missing the
#!/bin/sh

as the first line of the script. This lets the exec function being called internally by the scheduler know that the script is a script, and not a binary executable. While this isn't required to run a script from the command line, it is required to run it through the DBMS_SCHEDULER function on a Linux system.

So I added #!/bin/sh in my executable script at the first line and it worked.