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