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
> Running shceduled job on a particular instance
Vidhu
post Oct 7 2017, 02:29 PM
Post #1


Advanced Member
***

Group: Members
Posts: 50
Joined: 11-March 15
From: India
Member No.: 50,402



Hi,

In one of our requirements I created a dynamic scheduled job, based on some logic, on a particular instance - say 1 using the INSATNCE_ID attribute. But when the job starts, most of the time it gets created on some other instance - say 6 instead of 1. However I wanted the job to get created on the same instance as i needed. Can you please suggest options for me how to force a dynamic scheduled job to get assigned on the same node that i need.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
burleson
post Oct 8 2017, 01:23 AM
Post #2


Advanced Member
***

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



Hi Vidhu, I'm confused!

A scheduled job originates from the data dictionary on only one instance.

Can you elaborate please?


--------------------
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
Vidhu
post Oct 8 2017, 03:36 AM
Post #3


Advanced Member
***

Group: Members
Posts: 50
Joined: 11-March 15
From: India
Member No.: 50,402



Hi Burleson,

Let me make it clear with an example code that i have written.

sys.DBMS_SCHEDULER.create_job ('TEST',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SP_TEST; END;',
enabled => TRUE);
sys.DBMS_SCHEDULER.set_attribute (name => 'TEST',
attribute => 'MAX_RUNS',
VALUE => 1);
sys.DBMS_SCHEDULER.set_attribute (name => 'TEST',
attribute => 'AUTO_DROP',
VALUE => TRUE);
sys.DBMS_SCHEDULER.set_attribute (name => 'TEST',
attribute => 'INSTANCE_ID',
VALUE => 1);

We have a 6 node cluster for our oracle database. I am trying to create a DBMS scheduler job (one time job) dynamically to run that on instance id 1. However when the job starts it gets assigned to instance id 6. No idea why this is happening. Actually i wanted it to start on the same instance that am assigning it. Can you please suggest options for me how t handle this.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
burleson
post Oct 8 2017, 06:26 AM
Post #4


Advanced Member
***

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



Hi Vidhu,

Okay, these are RAC instances!

The value must be enclosed in quotes!

Try this:

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_ID', value=>'1');
PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_attribute(name => 'USER.DBA_JOB_1' ,attribute=>'INSTANCE_STICKINESS', value=>FALSE);
PL/SQL procedure successfully completed.


--------------------
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
Vidhu
post Oct 8 2017, 10:20 AM
Post #5


Advanced Member
***

Group: Members
Posts: 50
Joined: 11-March 15
From: India
Member No.: 50,402



Hi Burleson,

I'll try this and let you know. However I have passed the instance id as a variable, since the job creation is dynamic. Will that be a problem ?. Please advise.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
burleson
post Oct 9 2017, 02:19 PM
Post #6


Advanced Member
***

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



Hi,

You should be able to pass the instance_mum as a parm!

Just note the call on instance_stickiness!


--------------------
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

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 October 2017 - 01:59 PM