Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to retrigger an Oracle external remote Job ?
fedec
post Sep 5 2017, 03:02 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 5-September 17
Member No.: 52,149



Hi to all,

My External, Remote, and Recurrent Oracle Job fires only once,
and then it remains RUNNING.
Then,
• If I stop the job from SQLDeveloper using STOP_JOB() it fires again,
• but if I stop the job on the last job instruction, it remains RUNNING and doesn’t fire again

How can I automatically re-trigger this External, Remote, and Recurrent job after it worked well the first time ?

Thanks for your help,
Federico


DESCRIPTION: I need to create an Oracle job to create report files, in the future, and recurrently. For example every day at 10hs.

To do this: -I created an Oracle 11.2.0.4.0 Job using DBMS_SCHEDULER.create_job() -that Job triggers a Linux script on another server, where the Weblogic server is located.

The job is triggered and everything works correctly the FIRST TIME, but it is never triggered again.

This happens because the Job remains RUNNING and then does not fire again, since that should be in SCHEDULED status to be fired.

If I stop it by hand, using DBMS_SCHEDULER.stop_job(), on SQLDeveloper, it works fine and re-shoots (since when Oracle receives the stop then passes it to SCHEDULED status, and when it arrives the moment it re-shoots the Job)

In this point: 1 - I think it is the programmer who should be in charge of passing the job to STOP when it finishes, for being a job of external type, remote and recurrent. 2 - But in the case that Oracle is the one that has to pass to it to STOP, which I am not sure, the fact that Oracle do not do so may be indicating that the Agent program that runs the Linux script on the other server fails to communicate to the Oracle server that the job has finished, and therefore it remains RUNNING.

Thinking that the most probable case is 1, I added the DBMS_SCHEDULER.stop_job() inside the Linux script, executing a sql script with sqlplus. Then, -when I launch this script from a Linux console: it works perfectly (creates the files, stops the job, and re-triggered when appropriate), but -when I trigger the script FROM THE JOB it does everything ok EXCEPT THE STOP of the Job (creates the files, DOES NOT stop the job, and therefore does NOT fire again when appropriate).

To verify that there are no errors and that the script really triggers, within the sql script I added an update on a table before the stop and an update after the stop, and BOTH work fine, therefore the sql script was executed, the stop_job was executed but it could not change the status of the Job.

WHY ???

If I stop another Job it works.

With the job I also created the "credential", to connect to the Linux server and I configured AGENT_NAME in the Agent configuration file to use it in the "destination" of the Job to connect Oracle SQL to the Agent running on the Linux server.


Go to the top of the page
 
+Quote Post
burleson
post Sep 6 2017, 12:32 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi Federico, and welcome to the forum!

To understand your issue, we will need to see the data on you job from the dba_jobs table:


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
fedec
post Sep 6 2017, 02:01 PM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 5-September 17
Member No.: 52,149



QUOTE (burleson @ Sep 6 2017, 01:32 PM) *
Hi Federico, and welcome to the forum!

To understand your issue, we will need to see the data on you job from the dba_jobs table:


I use the DBMS_SCHEDULER package, then

SELECT * FROM all_scheduler_jobs
WHERE job_name = 'TEST_CCYL_MEJUVE_1030'
;.

You can open the file using Excel.

Thanks!

More information:

CODE:
-----------------------------------------------
Oracle version
-----------------------------------------------
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production



-----------------------------------------------
Agent version
-----------------------------------------------
SELECT sys.DBMS_SCHEDULER.GET_AGENT_VERSION('AGENT_WEBLOGIC_13') FROM DUAL;
11.2.0.3.1


-----------------------------------------------
Linux version
-----------------------------------------------
cat /etc/*release
NAME="Red Hat Enterprise Linux Server"
VERSION="7.2 (Maipo)"

uname –a
Linux weblogic12clinux.org 3.10.0-327.28.3.el7.x86_64 #1 SMP Fri Aug 12 13:21:05 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux


-----------------------------------------------
Create Job and Related Objects
-----------------------------------------------
Create "credential"
BEGIN
--Optional
dbms_scheduler.drop_job(
job_name => 'TEST_JOB'
);
--Optional
dbms_scheduler.drop_credential(
credential_name => 'MY_CREDENTIAL'
);
dbms_scheduler.create_credential(
username => 'oracle',
password => 'mypass123',
database_role => NULL,
windows_domain => NULL,
comments => 'Credential for TEST_JOB',
credential_name => 'MY_CREDENTIAL'
);
END;


Configure Agent on file "schagent.conf"
PORT = 1025
HOST_NAME = 192.168.0.13
AGENT_NAME= AGENT_WEBLOGIC_13
MAX_RUNNING_JOBS= 99
LOGGING_LEVEL= ALL


Create Job
--Optional
dbms_scheduler.drop_job(
job_name => 'TEST_JOB'
);
DBMS_SCHEDULER.create_job(
job_name => 'TEST_JOB',
job_type => 'EXECUTABLE',
number_of_arguments => 0,
job_action => '/u01/projects/MY_SCRIPT.sh',
credential_name => 'MY_CREDENTIAL',
repeat_interval => 'FREQ= DAILY; BYDAY= MON, TUE, WED, THU, FRI; BYHOUR= 10; BYMINUTE= 0; BYSECOND= 0',
auto_drop => false,
enabled => false
);

Assign 'Destination_name' to the Job
DBMS_SCHEDULER.set_attribute(
'TEST_JOB',
'destination_name',
'AGENT_WEBLOGIC_13'
);


Enable Job once completed
DBMS_SCHEDULER.enable(
'TEST_JOB'
);



-----------------------------------------------
Script Linux /u01/projects/MY_SCRIPT.sh
-----------------------------------------------
#Create files
#/u01/projects/MY_EXE
#Stop Job and updates table
sqlplus user_test/user_test @/u01/projects/MY_STOP_JOB.sql
exit 0
-----------------------------------------------


Script sql /u01/projects/MY_STOP_JOB.sql
----------------------------------------
EXEC my_STOP_JOB_proc;
EXIT;
-----------------------------------------------


Create table for updates
------------------------
CREATE TABLE RPTSUB_PARA
(
JOB_NAME VARCHAR2(30 BYTE) NOT NULL
, NO_RPTSUB NUMBER(10, 0) NOT NULL
, PRGQID NUMBER(10, 0)
, RUN_COMMENT VARCHAR2(20 BYTE)
, DEVELOP_COMMENT VARCHAR2(100 BYTE)
) ;

INSERT INTO RPTSUB_PARA
(
JOB_NAME
, NO_RPTSUB
, PRGQID
, RUN_COMMENT
, DEVELOP_COMMENT
)
VALUES (
'TEST_JOB'
, 439
, 97135
, ''
, ''
)
;


Procedure my_STOP_JOB_proc
--------------------------
create or replace PROCEDURE my_STOP_JOB_proc
IS
BEGIN
UPDATE
RPTSUB_PARA
SET
RUN_COMMENT = to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss')
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;

--Try to stop Job
DBMS_SCHEDULER.stop_job('TEST_JOB' /*, force => true*/);


UPDATE
RPTSUB_PARA
SET
DEVELOP_COMMENT = to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') || '-QUEST-'
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;
COMMIT;

END;
-----------------------------------------------


---------------------------------------------------------------
TEST procedure
---------------------------------------------------------------

--Stop job
BEGIN
dbms_scheduler.stop_job(
job_name => 'TEST_JOB'
);
END;

--Check actual time
SELECT to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') FROM DUAL;

--Change job's time to next minute
BEGIN
DBMS_SCHEDULER.set_attribute(
'TEST_JOB',
'repeat_interval',
'FREQ= DAILY; BYDAY= MON, TUE, WED, THU, FRI; BYHOUR= 15; BYMINUTE= 40; BYSECOND= 0'
);
END;

--Check job status BEFORE execution
select state, run_count, job_name, last_start_date, next_run_date,repeat_interval
from user_SCHEDULER_JOBS
where job_name='TEST_JOB';
--SCHEDULED, 3, 15:40

--Wait until the job is triggered
SELECT to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') FROM DUAL;


--Check job status AFTER JOB execution
SELECT job_name, status, error#, additional_info
FROM user_scheduler_job_run_details WHERE job_name='TEST_JOB'
order by log_date desc;
--RUNNING, 3 ==> wrong, not stopped

--Check updates table
SELECT
job_name,
PRGQID,
RUN_COMMENT,
DEVELOP_COMMENT
FROM
MISC.RPTSUB_PARA
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;
--Date was updated before and after STOP_JOB


--ReExecute script manualy
sh /u01/projects/MY_SCRIPT.sh

--ReCheck job status AFTER manual execution
SELECT job_name, status, error#, additional_info
FROM user_scheduler_job_run_details WHERE job_name='TEST_JOB'
order by log_date desc;
--SCHEDULED, 3 ==> Ok !!!


Attached File(s)
Attached File  table_all_scheduler_jobs_.txt ( 1.9K ) Number of downloads: 1
 
Go to the top of the page
 
+Quote Post
burleson
post Sep 7 2017, 10:07 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi Federico,

At first glance, I cannot see the issue.

You say that if you stop another job, then it starts.

Check on the max number of running jobs allowed?

Check the value of job_queue_processes.

Since you want a recurring job, see these notes:

http://www.dba-oracle.com/real_application...id/drop_job.htm

Also, consider using a crontab job, the scheduling can be more robust:

http://www.dba-oracle.com/t_advanced_cron_...hedule_jobs.htm

This is looking like more of a request for service than a question, I would open a service request on MOSC:

Http://support.oracle.com

Please let us know your solution!

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
fedec
post Sep 7 2017, 11:49 AM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 5-September 17
Member No.: 52,149



QUOTE (burleson @ Sep 7 2017, 11:07 AM) *
Hi Federico,

At first glance, I cannot see the issue.

You say that if you stop another job, then it starts.

Check on the max number of running jobs allowed?

Check the value of job_queue_processes.

Since you want a recurring job, see these notes:

http://www.dba-oracle.com/real_application...id/drop_job.htm

Also, consider using a crontab job, the scheduling can be more robust:

http://www.dba-oracle.com/t_advanced_cron_...hedule_jobs.htm

This is looking like more of a request for service than a question, I would open a service request on MOSC:

Http://support.oracle.com

Please let us know your solution!

Good luck!



Thanks Donald !

The problem is that this external, recurring job runs only once,
then it remains running and is not retriggered,

if I stop the job manually from SQLPLUS it is retriggered later,
but
if I try to stop it inside the script fired --BY THE JOB-- it doesn't work !!
(although it is possible to stop another job, but it is NOT possible to stop the SAME job where the script is working...)


As a work around I have created another (local) job that runs every 5 minutes to stop the external job, and it runs fine !!
but I don't like this solution...

My job is very similar to your example 4 in
http://www.dba-oracle.com/real_application...id/drop_job.htm
but my Job is EXTERNAL,

YOUR EXAMPLE:
The following can be used to create a job without a predefined Program and Schedule:
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => `BACKUP_JOB_04`,
job_type => `EXECUTABLE`,
job_action => `/dba/scripts/weekly_backup.sh`,
start_date => `TRUNC(SYSDATE)+23/24`
repeat_interval => `FREQ=WEEKLY; BYDAY=SUN`
);
END;

MY JOB:
BEGIN
DBMS_SCHEDULER.create_job
(
job_name => 'TEST_JOB',
job_type => 'EXECUTABLE',
number_of_arguments => 0,
job_action => '/gers/live/std/MY_SCRIPT.sh',

credential_name => 'MY_CREDENTIAL',

repeat_interval => 'FREQ= DAILY; BYDAY= MON, TUE, WED, THU, FRI; BYHOUR= 10; BYMINUTE= 0; BYSECOND= 0',
auto_drop => false,
enabled => false
);
DBMS_SCHEDULER.set_attribute(
'TEST_JOB',
'destination_name',
'AGENT_WEBLOGIC_13'
);
DBMS_SCHEDULER.enable(
'TEST_JOB'
);
END;


then:
How can I stop this Recurrent, external, remote job
automatically(=inside the job)
to get the job fired again by the Oracle scheduler ?


I think that is not a bug, but I will ask Oracle also.


Thanks !!
Federico


Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:17 PM