Skip to main content
AdministrationoracleScripts

Database Bounce History by Triggers

By March 16, 2012October 7th, 2016No Comments2 min read

Database Bounce History

Whenever Instance was started or shutdown, These information not stored in Oracle Views/Tables. In this case used to refer Alert Log file when it was bounced.
Instead of reviewing we can create a table, along with this have to create two tirggers, One trigger will be activated once startup time and another trigger will be activated on shutdown. at the same time whenever trigger activated table Will be updated. So that we can view from Table all the history after creating the table with trigger.

1. create table.

CREATE TABLE CKPT_DATABASE_ACTIVITY
(
NAME      VARCHAR2(20 BYTE),
TIME        DATE,
DESCRIPTION  VARCHAR2(30 BYTE)
)
TABLESPACE USERS;

2. create trigger after startup

CREATE OR REPLACE TRIGGER CKPT_TRIG_STARTUP
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO CKPT_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, 'STARTUP’);
END;
/

3. create trigger before shutdown

CREATE OR REPLACE TRIGGER CKPT_TRIG_SHUTDOWN
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO CKPT_DATABASE_ACTIVITY
VALUES (USER, SYSDATE, 'SHUTDOWN’);
END;
/

Perform Shutdown & Startup for several times and then check the view for the history of Startup & Shutdown.

 

 

 

 

 

 

Leave a Reply