28 November, 2008

Database Event Trigger and SYSOPER

Responding to a forums.oracle.com query on SYSOPER and ALTER SYSTEM, I quickly wrote a Database Event trigger.
Here I demonstrate the Database Event Trigger which has been written to execute certain commands on shutdown. The Trigger is written as a BEFORE SHUTDOWN trigger.
Also, I demonstrate how the SYSOPER privilege is to be used.

This is the Trigger code :

SQL> drop trigger shutdown_trigger;

Trigger dropped.

SQL> create trigger shutdown_trigger before shutdown on database
2 begin
3 dbms_system.ksdwrt(2,'Database Shutdown manually initiated');
4 execute immediate 'alter system switch logfile ';
5 end;
6 /

Trigger created.

SQL>


This is how I create an account with SYSOPER privileges so that the account can be used for shutdowns.
Note how the account had to explicitly connect AS SYSOPER in order to be able to effect a shutdown.

SQL> create user shutdown_user identified by shutdown_user;

User created.

SQL> grant create session, sysoper to shutdown_user;

Grant succeeded.

SQL> connect shutdown_user/shutdown_user
Connected.
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> shutdown
ORA-01031: insufficient privileges
SQL> connect shutdown_user/shutdown_user as sysoper
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


And here, in the alert.log, we see evidence of the Trigger :

Shutting down instance: further logons disabled
Fri Nov 28 22:05:53 2008
Stopping background process CJQ0
Fri Nov 28 22:05:53 2008
Stopping background process QMNC
Fri Nov 28 22:05:56 2008
Stopping background process MMNL
Fri Nov 28 22:05:57 2008
Stopping background process MMON
Database Shutdown manually initiated
Fri Nov 28 22:05:58 2008
Shutting down instance (immediate)
License high water mark = 8
Fri Nov 28 22:05:58 2008
Stopping Job queue slave processes, flags = 7
Fri Nov 28 22:05:58 2008
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
Fri Nov 28 22:05:58 2008
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Fri Nov 28 22:06:00 2008
All dispatchers and shared servers shutdown
Fri Nov 28 22:06:00 2008
ALTER DATABASE CLOSE NORMAL


The DBMS_SYSTEM.KSDWRT is not a supported call. So use it at your own risk.

No comments: