How to find startup & shutdown time of Oracle Database

Sometimes, we have to determine the startup and shutdown history of a database . There is no any data-dictionary  tables which contains the history of startup and shutdown time . Sometimes a system administrator reboot server in such cases we can determines the startup and shutdown time by checking the alert logfile. Since alert logfile keeps on increasing and we manages the alert logfile either by truncate or deleting its contains . 

Instead of depending on alert logfile , we can create table which contains the history of startup and shutdown by using the triggers . Here we will create two triggers i.e, first trigger will fired once the database is startup and second trigger is fired when database is shutdown . Let's have a  look . 

1.) Create a table to store history
SQL> create table db_history ( time date , event  varchar2(12)) ;
Table created.

2.) Create trigger for catching startup time 
SQL>create or replace trigger dbhist_start_trigr
after startup on database 
begin
insert into db_history values (sysdate , 'StartUp' ) ;
end ; 
/
Trigger created.

3.) Create Trigger to catch shutdown time 
SQL> create or replace trigger dbhist_shut_trigr
before shutdown on database
begin
insert into db_history values (sysdate, 'ShutDown' ) ;
end;
/
Trigger created.


Enjoy      :-)