When does the 'shutdown database' trigger fire?

The chances of this being useful to anybody are fairly remote, but I was reading through my ‘Bumper Book of Oracle Certification’ and thought I’d check in what circumstances the ‘before shutdown’ trigger fires.

This summarizes what I found:

Shutdown normal Yes
Shutdown immediate Yes
Shutdown transactional Yes
Shutdown abort No
Shutdown of a startup-nomounted database No
Shutdown of a mounted database No

To be fair, this is exactly what I would have expected. I wasn’t entirely sure about shutdown immediate, but you would expect a) a trigger to only fire when the database is open and b) shutdown abort to do nothing other than shutdown.

Demo follows

Create a trigger
First create a table to hold the messages and a trigger

SQL> create table my_messages(message_date date, message_text varchar2(1000));

Table created.

SQL> save ctmm
Created file ctmm.sql
SQL> host gvim ctrbshut.sql

SQL> @ctrbshut.sql

Trigger created.

…where the script is

SQL> get ctrbshut.sql
1 create trigger before_shutdown before shutdown on database
2 begin
3 insert into my_messages(message_date, message_text)
4 select sysdate, 'Database shutting down' from dual;
5* end;

Check the shutdown trigger works with a normal shutdown

SQL> set head off
SQL> col message_text format a30
SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;

no rows selected

SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:28:30

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;

12-FEB-2010 11:29:05 Database shutting down <---the shutdown normal


Does the shutdown trigger fire for a shutdown transactional?


SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:30:00

SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;
12-FEB-2010 11:29:05 Database shutting down <---the shutdown normal
12-FEB-2010 11:30:25 Database shutting down <---the shutdown transactional


Does the shutdown trigger fire for a shutdown immediate?


SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:31:44

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;
12-FEB-2010 11:32:05 Database shutting down <---the shutdown immediate
12-FEB-2010 11:29:05 Database shutting down <---the shutdown normal
12-FEB-2010 11:30:25 Database shutting down <---the shutdown transactional


Does the shutdown trigger fire for a shutdown abort?


SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:32:52

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;
12-FEB-2010 11:32:05 Database shutting down <---the shutdown immediate
12-FEB-2010 11:29:05 Database shutting down <---the shutdown normal
12-FEB-2010 11:30:25 Database shutting down <---the shutdown transactional

No!


Shutdown to do a startup mount

This isn’t relevant to the discussion of the shutdown trigger – I did a shutdown here just to get the database down and then up to a nomount state

SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:34:23

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.


Does the shutdown trigger fire if the database is shutdown from a nomount or mounted state?

In this passage, I do a startup nomount and shutdown, then a startup mount and shutdown.

The trigger does not fire.


SQL> startup nomount
ORACLE instance started.

SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:35:15

SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

12-FEB-2010 11:36:16

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'),
message_text
from my_messages;
12-FEB-2010 11:32:05 Database shutting down <---the shutdown immediate
12-FEB-2010 11:29:05 Database shutting down <---the shutdown normal
12-FEB-2010 11:30:25 Database shutting down <---the shutdown transactional
12-FEB-2010 11:34:32 Database shutting down


The ‘after shutdown’ trigger

I couldn’t resist seeing whether Oracle would let me create an ‘after shutdown’ trigger. I was pleased to see there’s a special error message for it 🙂

SQL> create trigger after_shutdown after shutdown on database
2 begin
3 insert into my_messages(message_date, message_text)
4 select sysdate, 'Database shutting down' from dual;
5 end;
6 /
create trigger after_shutdown after shutdown on database
*
ERROR at line 1:
ORA-30501: instance shutdown triggers cannot have AFTER type

Advertisements