Friday, July 3, 2009

Oracle Scheduler Examples

1) Job that runs every minute available to start immediately

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_MIN"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

2) Job that runs every 10 minutes starting at 11:45am UTC

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

3) Job that runs every 2 hours starting at 11:45am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"MYSCHEMA"."MY_JOB_NAME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
MY_PROC;
end;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
start_date => to_timestamp_tz('2010-04-13 11:45:00 0:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/


4) Job that runs once a day every day and starts at 04/07/2009 03:00 am UTC.

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"TST"."TEST_JOB_PER_DAY"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
TST12.MY_PROC;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2009-07-04 UTC', 'YYYY-MM-DD TZR'),
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

5) Job that runs every day twice,at specific times UTC
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'MY_USER.JOB_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update mytable set x=1;
commit;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=16,22;BYMINUTE=10;BYSECOND=0',
start_date => systimestamp at time zone 'UTC',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

Thursday, July 2, 2009

ORA-12170 & SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT has been introduced in version 9i.This has to be configured in sqlnet.ora file specifies the time,in seconds, for a client to connect with the database server and provide the necessary authentication information.

If there are network latencies and the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file.
The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message. In version 10g and higher, the ORA-3136 errors may appear in the alert.log.


To protect both the database server and the listener for denial of service attacks
Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file.

TIPS

In 10gR2, the default setting for these parameters is 60 seconds.
So set the parameters to at least that value in pre-10gR2 releases because there is no default setting for this versions.

E.g

Set on sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=60

Set on listener.ora for each listener
INBOUND_CONNECT_TIMEOUT_[LISTENER_NAME]=60