Monthly Report Shutdown/Startup activity of database

Monthly Report Shutdown/Startup activity of database

Sometimes we need to provide monthly shudown and startup activity of database to the Boss. For that we can use procedure that will read the log and try to find a reference to shutdown instance or startup from the current alert.log file into an external table then then query with that table to find the desired details. I really appreciate and Thanks to TOM for this important discussion.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Steps:
1. Create or Setup an external_table pointing to the copy of the alert log file for the past month.
CREATE TABLE drop_ext
(
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.dat'
    ) )
REJECT LIMIT unlimited
2. Create a procedure that read the log and try to find a reference to Shutdown/Startup  Instance and the grab the line that has the time.
DECLARE
action_line       drop_ext.text_line%TYPE            := NULL;
time_line          drop_ext.text_line%TYPE            := NULL;
s_ind               varchar2(10)                                :='N';
CURSOR line_cur is SELECT text_line from drop_ext;
BEGIN
execute immediate ('truncate table drop_me');
   FOR in_line in line_cur LOOP
        if s_ind = 'Y' then
        if in_line.text_line like 'Mon%' or   in_line.text_line like 'Tue%' or
               in_line.text_line like 'Wed%' or  in_line.text_line like 'Thu%' or
               in_line.text_line like 'Fri%' or  in_line.text_line like 'Sat%' or
               in_line.text_line like 'Sun%' then   time_line:= in_line.text_line;
            s_ind:='N';
           insert into drop_me (action, date_string)
              values (action_line, time_line);
            end if;
    end if;
    if in_line.text_line like '%Starting ORACLE%' or in_line.text_line like'%Shutting down instance (%' then
        action_line:= in_line.text_line;
        s_ind:='Y';
    end if;
   END LOOP;
commit;
# The Alternative or better way i found is that query with that table where you store the past alret.log contents
select last_time, start_time, start_time-last_time days
from ( select 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 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 t_alertlog
where text_line like '___ ___ __ __:__:__ 20__'
or text_line like 'Starting ORACLE instance %'
))
where text_line like 'Starting ORACLE instance %'
)
/
Note: The last date before the Starting ORACLE.... is the start up time, the last date record before that is the last *observed* timestamp

# Now this query will give uptime instead of downtime.
select START_TIME,RESTART_TIME,UPTIME
from (select lag(start_time,1) over (order by r) start_time,
       last_time restart_time,    
       last_time - lag(start_time,1) over (order by r) uptime
from (select rownum r,last_time, start_time, start_time-last_time days
    from (  select 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 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 drop_ext
   where text_line like '___ ___ __ __:__:__ 20__'
      or text_line like 'Starting ORACLE instance %'
             ) )
   where text_line like 'Starting ORACLE instance %'
         ))
         )
   where start_time is not null
   /
# Same with above with better alternative query
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 %'
)
/
To debug the error related to date conversion follow this link: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:28805759638277
Finally, any user can use that, regardless of international settings:
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', 'nls_date_language=''american''') last_time,
to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY', 'nls_date_language=''american''') 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 sys.alert
where text_line like '___ ___ __ __:__:__ 20__'
or text_line like 'Starting ORACLE instance %'
) )
where text_line like 'Starting ORACLE instance %'
);