How to easily find the Oracle database startup and shutdown time using sqlplus

Here are the steps required on How to easily find the Oracle database startup and shutdown time using sqlplus
step 1)  Create an database directory object

create or replace directory data_dir as 'Specify the Backgound dump Dest location'
/
Directory created.

CREATE TABLE alert_log
( text_line varchar2(255))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'alert_<SID>.log'
)
)
REJECT LIMIT unlimited
/
Table created.

step 2)  Use the below query to find out the timing

select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
round((start_time-last_time)*24*60,2) mins_down,
round((last_time-lag(start_time) over (order by r)),2) days_up,
case when (lead(r) over (order by r) is null )
then round((sysdate-start_time),2)
end days_still_up
from (
select r,
to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
from (
select r,
text_line,
lag(text_line,1) over (order by r) start_time,
lag(text_line,2) over (order by r) last_time
from (
select rownum r, text_line
from alert_log
where text_line like '___ ___ __ __:__:__ 20__'
or text_line like 'Starting ORACLE instance %'
)
)
where text_line like 'Starting ORACLE instance %'
)
/

Related Articles

Alert log file in Oracle And command to check for ORA error and startup time

11g Alert log new features

How to resolve ORA-29913 with external tables

How to use external tables in Oracle with example

Useful Unix/Linux command for Oracle DBA

Top 40 oracle dba interview questions and answers

Leave a Reply