in my application I'm starting jobs via dbms_scheduler for background activity on user action.
I know from my logs that the procedure executed in the job finishes within 2 seconds.
But in the AWR I find execution time for the job of 360 seconds (single execution because of unique job id).
How do I find out why the job takes so long?
And more important: how do I speed up the job?
(details will follow...)
How to stop all jobs(dbms_scheduler) when ever database down or power failure and I need to restart all jobs when ever database is up.
View 3 Replies View RelatedI have scheduled few jobs (scheduled for each day) which has not run today, which was running successfully till yesterday. When I query dba_scheduler_jobs, i could see last_run_date as yesterday's date 6am and next_run_date is still showing today's date and time (it should show tomorrow's date/time).
View 3 Replies View RelatedWhen performing test recoveries of a database we often wish to prevent database jobs from running application processes such as (a) sending emails; or (b) logging events to a central logging database. In order to accomplish this, we set the job_queue_processes to zero before opening the database.
This means that no dbms_jobs run on the open instance, but it does not prevent dbms_scheduler jobs from running.How can we prevent any dbms_scheduler jobs from running?
I have looked at setting the MAX_JOB_SLAVE_PROCESSES attribute using DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE, but this cannot be set to zero.
How to find currently running scheduled jobs using query !?
View 3 Replies View RelatedWe have a scheduler chain that calls 2 scheduler programs.The chains are controlled by a scheduler job that has job_type as CHAIN.My challenge is how to add email notifications on the failure or success of the programs.
I know about this procedure DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION, but this is only applicable to jobs and not programs(i believe). Is there an alternative way to trigger off emails for scheduler programs ?
we have a problem with forms containing webutil - they start very slow, after like a timeout, while forms without start ok. since i think it has something to do with name resolution i'll first describe our environment.
we have an external net with users, ip range 10.48.x.x, connected to a cisco router which is resolved in that network with the name 'cro'. so the users from that external net access us via 'http: //cro/.....'.
behind that router we have our production net, ip range 192.168.x.x, with forms-, reports- and http-servers. external users accessing our forms server will have to klick a link "http: //cro:8888/forms......'. that way they acces the cisco router which, depending on the port (here 8888) will NAT them to the correct forms server, say http: //forms1:7778/forms....' - up to here everything works well and fast with 'normal' forms (eg. without webutil).
forms *with* webutil on the other hand take like 30-60 seconds to load from that external net (via the router) while they load fast when called from a test-client in the internal 192.168.x.x net. after searching i found [URL] with a hint to alter the clients hosts file.
what i've found out so far is that the clients from the external 10.18.x.x net try to name resolve the *forms servers* in the internal net 192.168.x.x - i can cheat them by providing a fake hosts file which resolves the server named forms1 to the ip of the cisco router cro, but thats a temporarily and bad solution since i cant do that to real clients because i have no access to them.
so... how can i tell a webutil loaded form not to try to connect to the server they are started from (forms1) but to use the host they have been given to start from (cro)? or is there any other solution (which ofc cant include fiddling with the external net or external clients)?
I am using 11.2.0.2 Data Guard.
I had set Delay=60 for Standby Database in init parameters of Primary Database and bounced both Primary and Standby DB.
But as soon as i perform log switch in Primary DB it is being applied in Standby DB immediately ignoring my Delay parameter.
The Physical Standby is mounted and redo apply is enabled.
Parameters:
-----------------
LOG_ARCHIVE_DEST_1=
'LOCATION=/data/dg/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC DELAY=60
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
Standby:
-------------
SEQUENCE# APPLIED
---------- ---------
1208 YES
1209 YES
1210 YES
1211 YES
1212 YES
1213 YES
1214 YES
1215 YES
1216 IN-MEMORY
I have written a stand alone (Java SE 1.6) JMS client program to consume AQ's messages via Oracle JMS API (aqapi.jar). The queue is a multiple consumer queue, and i just created one subscriber on it. My JMS client program receives messages asynchronously by setting the MessageListener using the setMessageListener method.
Watching the work of the program, I found significant delays in receiving messages that are up to several seconds. When I turned on the diagnostic trace, I found that in the absence of messages listener (AQjmsSimpleScheduler) gradually increases the delay time up to 15 seconds:
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a non null message, the sleep time is reset to 0
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 0 millisecond.
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a null message, the sleep time is doubled to 1000
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 1000 millisecond.
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.doSleep: try to wait for 1000 milliseconds
[code]....
Thus, in the worst case, the delay between placing the message in the queue and receiving it by the JMS client is 15 seconds.
Can I control this latency? For example, I would like to explicitly set the levels of the time delays.
What could be the reasons that some queries execute fast when executed on sqlplus on server, whereas the same queries run slower with same input values fed from application screen?
One issue I guess would be bind variable peeking while using application whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"
If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?
I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI
finally how to set arraysize in jdbc to improve performance by reducing roundtrips?
I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._
Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.
*1, Using OPENQUERY:*
SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
*2, Using Exec:*
DECLARE @sql NVARCHAR(MAX);
SET @sql =(’Select * from OracleTableName ‘);
EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
how to execute one stored procedure for every 10 minutes using oracle jobs
sp name: abc
using anonymous block with sample code.
I want to run a procedure every day at 9 AM ,so how can i schedule it.
View 3 Replies View Relatedthe below issue.
I want to schedule the procedure - SMTP_PROCEDURE2. Which should run every day at 5 PM. The owner of this procedure is 'bkpusr'.
In SYS login. i am running the below script, but it is not working out. what could be the problem.
DECLARE
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TRIGGERING_THE_SMTP_PROCEDURE',
job_type => 'STORED_PROCEDURE',
job_action => 'BKPUSR.SMTP_PROCEDURE2',
[Code]....
I am getting problem while starting the CRS. Below is the necessary details:
1) I am using oracle 11g R2 RAC, with 2 node named RAC1 and RAC2
2) I have 3 diskgroup named DATA, DATA1, DATA2
3) OCR and voting disks are stored on ASM diskgroup
On node RAC1, CRS is started and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+DATA1
On node RAC2, CRS is stopped and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+DATA2
note the difference in "ocrmirrorconfig_loc". The difference was occured because I updated the OCR location on RAC1 when RAC2 was down.
Now I am trying to repair the RAC2 using below command. This command must be run as root user when HA service is started but CRS must be stopped.
ocrconfig -repair -replace +DAT2 -replacement +DATA1
but this showing error "PROT-21: Invalid parameter", because to run this command diskgroup must be mounted. But diskgroup cannot be mounted until the CRS start. SO this is a conflicting situation.
I am getting problem while starting the CRS. Below is the necessary details:
1) I am using oracle 11g R2 RAC, with 2 node named RAC1 and RAC2
2) I have 3 diskgroup named DATA, DATA1, DATA2
3) OCR and voting disks are stored on ASM diskgroup
On node RAC1, CRS is started and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+DATA1
On node RAC2, CRS is stopped and /etc/oracle/ocr.loc is having the below entry:
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+DATA2
Please note the difference in "ocrmirrorconfig_loc". The difference was occured because I updated the OCR location on RAC1 when RAC2 was down.
Now I am trying to repair the OCR on the RAC2 using below command. This command must be run as root user when HA service is started but CRS must be stopped.
ocrconfig -repair -replace +DAT2 -replacement +DATA1
but this showing error "PROT-21: Invalid parameter", because to run this command diskgroup must be mounted. But diskgroup cannot be mounted until the CRS start. SO this is a conflicting situation.
I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.
My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..
SQL> select job_name, next_run_date, last_start_date, last_run_duration from user_scheduler_jobs;
HISASAV_JOB
20-AUG-11 12.30.41.000000 PM ASIA/CALCUTTA
20-AUG-11 12.20.41.122959 PM ASIA/CALCUTTA
+000000000 00:00:01.307071
[code]....
We have a job table, and whenever jobs are added to this table the job processing procedure should be started.
In unix we have crontab facility where we can schedule to run for 24/7.
similarly do we have any option in oracle daatbase, to keep on running a prcedure in loop.
I am trying to find a way to identify what are the user_jobs currently running. I have found 1 way which requires DBA User ID to execute (e.g. DBA_JOBS and DBA_JOBS_RUNNING) which I don't want.
I believe there is NO equivalent User table for DBA_JOBS_RUNNING table.
I have done my 20 mins search in this forum and unable to find.
I have a procedure that needs to be scheduled as a job. This procedure takes an average of 20-25 minutes to complete. The constraint is that these jobs are submitted using dbms_job.submit with an interval of 5 minutes.
For Example:
Job1 Procedure_B 20-Nov-12 21:05:00
Job2 Procedure_B 20-Nov-12 21:10:00
Job3 Procedure_B 20-Nov-12 21:15:00
Job4 Procedure_B 20-Nov-12 21:20:00
So, will Job2 execute only after Job1 completes [even though Job1 takes 25 minutes to complete] or is there any other mechanism for setting incompatibility for a job with itself.
I have created two jobs in sys database as follows,
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'JOB_FCDB_CHARGES',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FCDBCHARGES; END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=19',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job to process Internet Banking Charges.',
auto_drop => FALSE,
enabled => FALSE);
[code]....
It has been successfully created.Even i get the entry in dba_objects table as follows,
select owner, object_name, object_type from dba_objects where owner = 'SYS' and object_name like '%JOB_FCDB_CHARGES%';
But, my problem is when i log in to PL/SQL developer i am not able to see anything under job.
am facing an issue with scheduled jobs. The below script will create a scheduled job to insert a record for every 2 mins into the table "job_table".
--Table script
CREATE TABLE job_table (now DATE);
--Stored procedure run by scheduled job
CREATE OR REPLACE PROCEDURE test1 IS
BEGIN
[code]...
The scheduled job does not require any special grant privillege as the PUBLIC has access to run it. Anyhow the same set of instruction working for my friend on his machine but not on my environment.
There is one maintenance job owned by SYSMAN running continuously. I want to stop that scheduler job .How ?
IS this the correct syntax to stop the running job ?
exec dbms_scheduler.stop_job(job_name=>'jobname');
How to pass parameter value to a procedure in schedule jobs ?
--procedure :
create or replace procedure updating_temptable(empcode in varchar2) is
begin
update ts_employee_master mas
set mas.last_accessed = sysdate
where mas.employee_code = empcode;
end;
--scheduler
begin
sys.dbms_job.submit(job => :job,
what => 'updating_temptable;',
next_date => to_date('24-04-2012 00:30:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE)+1+30/1440');
commit;
end;
/
How to pass employee_code in the procedure[updating_temptable ] in dbms job scheduler ?
why jobs in oracle having problem while dropping them. The problem is like following
If am going to stop some jobs it show the error 'Job not running' and when m trying to drop that job it shows the error 'job is currently running'.
why the Scheduler is not sending out notifications for a job I am running.I set up a small job that calls a programm which references a stored procedure. The procedure executes a manual refresh of a materialized view.
I did set up the SMTP parameters EMAIL_SENDER and EMAIL_SERVER, restarted the instance afterwards and verified that the parameters are set by logging into the Enterprise Manager web console and looking at the page with the email server parameters.
I did verify that the job did actually start and complete successfully by checking the logs in ALL_SCHEDULER_JOB_RUN_DETAILS
I did verify that I actually do have the notifications activated by checking ALL_SCHEDULER_NOTIFICATIONS...There are notifications for all events (JOB_STARTED, JOB_COMPLETED, JOB_SUCCEEDED, JOB_FAILED, etc.) for my job
I also verified that the materialized view has actually been refreshed by checking in ALL_MVIEW_REFRESH_TIMES
I also verified that the email server I am using is sending out emails by sending a test email from the shell using the same sender and recipient addresses as I am using in the notification settings.What could the reason be for the scheduler not sending out emails?
I am unable to start the listener.When I start it I get this error
LISTENER2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.20.221)(PORT = 1522))
)
[Code]....
When I try to start it is showing
LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 25-APR-2011 10:55
:31
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start listener1
Starting tnslsnr: please wait...
Failed to start service, error 0.
I cloned the oracle VM, was able to recreate the repository for em.
But its failing when i try to start it with following trace of errors
2011-09-02 17:22:59,502 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.sysman.ias.ias.IASIntegration
2011-09-02 17:22:59,753 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.sysman.eml.target.slb.common.SLBIntegration
2011-09-02 17:22:59,830 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.sysman.eml.ssl.intg.SSLIntegration
2011-09-02 17:22:59,898 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.tip.oem.central.domain.ProcessConnectDomainIntg
2011-09-02 17:22:59,908 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.tip.oem.central.instance.ProcessConnectInstanceIntg
2011-09-02 17:22:59,910 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.webdb.admin.em.PortalIntegration
2011-09-02 17:22:59,911 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.webdb.admin.em.SSOIntegration
2011-09-02 17:22:59,912 [Orion Launcher] ERROR app.ContextInitializer contextInitialized.272 - Integration Class not found: oracle.reports.em.RepIntg
Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees
we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )
tables
emp
emp_no
name
manager
hiredate
salary
job
job_no
job
you can add tables or attributes to tables to complete you business.
Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?