Control Services and Scheduled Jobs at Startup?
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A solution using a System Event Trigger
Solutions need to address problems or as they are more euphemistically called: Challenges. The issue we are addressing here is how to prevent a scheduled job from immediately restarting after a shutdown.

From time-to-time it may be necessary to a take production server down for an extended period of time. These servers often have jobs created using DBMS_SCHEDULER that perform maintenance activities that intentionally destructive: For example a job that drops partitions older than 30 days.

If the server has been down for one week the first thing that may be required is to catch up on loading and the last thing required is dropping data that may be outside the 30 day window but still required for some period of time while the system returns to normal operations.

With the older and less capable DBMS_JOB package we had the option, during startup to do the following:

STARTUP MOUNT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0 SCOPE=MEMORY;
ALTER DATABASE OPEN;


and prevent the jobs from running until we can manually disable them.

Unfortunately due to an oversight at Oracle no such capability exists for the scheduler so this demo is going to show you how you can create it for yourself. The key to this How Can I is the use of an AFTER STARTUP system event trigger that reads entries in a file modeled after oratab. We will start by creating the file. You can store it anywhere on your hard drive: This demo assumes a directory /etc.

We will start by looking around a database at the resources we have as they currently exist and create, start, and stop, a new service.
conn / as sysdba

set linesize 121
col name format a30
col network_name format a30

SELECT service_id, name,network_name, creation_date
FROM dba_services;

SELECT service_id, name, network_name
FROM gv$active_services;

exec dbms_service.create_service('uw_loader', 'loader service');

SELECT service_id, name,network_name, creation_date
FROM dba_services;

SELECT service_id, name, network_name
FROM gv$active_services;

exec dbms_service.start_service('uw_loader', 'orabase');

SELECT service_id, name, network_name
FROM gv$active_services;

exec dbms_service.stop_service('uw_loader', 'orabase');

SELECT service_id, name, network_name
FROM gv$active_services;

SELECT service_id, name,network_name, creation_date
FROM dba_services;
We will start by creating the file. You can store it anywhere on your hard drive: This demo assumes the directory $ORACLE_HOME/dbs and the file named servtab. If you do a DESC on dba_services you will see that tha maximum length of a service name is 64 bytes and a similar DESC of v$instance shows that the maximum length of an instance name is 16 bytes for a total possible length of 83 bytes for a file entry.
# This file is has been created by the DBA to allow for instance by
# instance management of services and scheduled jobs at startup.
#
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# <instance_name>:<service_name>:<N|Y>:
#
# The first and second fields are the instance name and service name
# respectively. The third field indicates that the database should, "Y",
# or should not, "N", start the named service at startup. Save this file
# with the name servtab at $ORACLE_HOME/dbs.
#

orabase:uw_loader:Y
The file we just created and saved indicates our instance is named "orabase" and that it contains a service named "uw_loader" and that uw_loader should be started at instance startup. Lets test normal behavior of our service.
conn / as sysdba

shutdown immediate;

startup;

SELECT service_id, name, network_name
FROM gv$active_services;
The service does not automatically start at startup so any jobs tied to it will, by default, not run. Time now to use our file to control service activation.

The first step in doing so is to create an external table that can be used to read the file and test it.
conn / as sysdba

CREATE OR REPLACE DIRECTORY servtab_dir AS '/oracle/product/11.2.0/db_1/dbs';

GRANT read ON DIRECTORY servtab_dir TO uwclass;
GRANT execute ON dbms_service TO uwclass;

CREATE TABLE uwclass.service_tab (listing VARCHAR2(83))
ORGANIZATION EXTERNAL (
 TYPE oracle_loader
 DEFAULT DIRECTORY servtab_dir
 ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  NOBADFILE NODISCARDFILE NOLOGFILE
  FIELDS TERMINATED BY '0x0A'
  MISSING FIELD VALUES ARE NULL)
 LOCATION ('servtab'))
REJECT LIMIT unlimited;

SELECT * FROM uwclass.service_tab;
We have proven that we can read the file by selecting from our external table so the only thing left is the system event trigger that, when fired at startup, will read the file and enable, or disable, the named service in the named instance. The following shows the trigger being created by SYS but owned by a schema that is not SYS. This is done for security reasons. No one should be granted the ADMINISTER DATABASE TRIGGER privilege but a system event trigger can be owned by anyone even if they do not have the CREATE TRIGGER privilege.
conn / as sysdba

CREATE OR REPLACE TRIGGER uwclass.services_startup
AFTER STARTUP ON DATABASE
DECLARE
 CURSOR servtab_cur IS
 SELECT listing FROM aircom.service_tab;

 firstChar cHAR(1);
 inst_name VARCHAR2(30);
 serv_name VARCHAR2(64);
 yn_flag CHAR(1);
 bad_flag EXCEPTION;
BEGIN
  FOR servtab_rec IN servtab_cur LOOP
    firstChar := SUBSTR(servtab_rec.listing,1,1);
    IF <> '#' THEN
      inst_name := SUBSTR(servtab_rec.listing, 1, INSTR(servtab_rec.listing,':', 1, 1)-1);
      serv_name := SUBSTR(servtab_rec.listing, INSTR(servtab_rec.listing,':', 1, 1)+1, (INSTR(servtab_rec.listing,':', 1,2)-instr(servtab_rec.listing,':',1,1)-1));
      yn_flag := UPPER(SUBSTR(servtab_rec.listing, -1, 1));

      IF yn_flag NOT IN ('N','Y') THEN
      RAISE bad_flag;
    END IF;

    IF yn_flag = 'Y' THEN
      dbms_service.start_service(serv_name, inst_name);
    ELSE
      dbms_service.stop_service(serv_name, inst_name);
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    NULL; -- in the real-world should write to an application error log
END services_startup;
/
Time to test it by changing the Y/N parameter in the file during some shutdown/startup exercises. Here are the pieces you need. Have some fun.
shutdown immediate;

startup;

SELECT service_id, name, network_name
FROM gv$active_services;
The last step to making this work in the real world is to make sure that when you create scheduled jobs you always tie them to a service. A good practice anyway but one that is required here to achieve the desired functionality. To learn more about this use the link below to view the DBMS_SCHEDULER page and check out the Oracle docs at tahiti. What you want to work with is the SERVICE parameter of the procedure CREATE_JOB_CLASS.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved