You Asked
Tom,
I'm required to produce a monthly report with shutdown/startup activity for a database
{hopefully there will be nothing to report:)}
I just want you to comment on my aproach, and maybe you'll have a better idea on how to
automate this. Basically, this is my aproach using alert log.
1. Create an external_table poitining 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 will read the log and try to find a reference to Shutdown
Instance or 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 only other way I know how to do this would be to use a startup/shutdown trigger, but
I would prefer not to go that route. Are you aware of anything else?
Thanks.
and we said...
short of using OEM or any other monitoring tool, this approach will work (i did not debug
the code or anything like that.
Although, this might be easier:
ops$tkyte@ORA9IR2> select last_time, start_time, start_time-last_time days
2 from (
3 select to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
4 to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
5 from (
6 select text_line,
7 lag(text_line,1) over (order by r) start_time,
8 lag(text_line,2) over (order by r) last_time
9 from (
10 select rownum r, text_line
11 from t
12 where text_line like '___ ___ __ __:__:__ 20__'
13 or text_line like 'Starting ORACLE instance %'
14 )
15 )
16 where text_line like 'Starting ORACLE instance %'
17 )
18 /
LAST_TIME START_TIME DAYS
-------------------- -------------------- ----------
18-sep-2004 18:17:50
19-sep-2004 09:35:09 19-sep-2004 09:35:13 .000046296
24-sep-2004 15:33:20 24-sep-2004 15:33:20 0
25-sep-2004 09:22:07 25-sep-2004 09:22:26 .000219907
25-sep-2004 09:22:31 25-sep-2004 09:22:35 .000046296
26-sep-2004 08:13:43 26-sep-2004 08:13:44 .000011574
26-sep-2004 10:19:16 26-sep-2004 11:19:40 .041944444
26-sep-2004 12:02:59 10-oct-2004 08:58:51 13.8721296
11-oct-2004 16:46:34 11-oct-2004 16:46:34 0
11-oct-2004 16:52:31 11-oct-2004 16:52:45 .000162037
11-oct-2004 16:52:50 11-oct-2004 16:53:01 .000127315
11-oct-2004 16:53:08 12-oct-2004 18:31:25 1.06825231
12-oct-2004 18:31:34 15-oct-2004 17:14:36 2.94655093
16-oct-2004 20:09:21 16-oct-2004 20:09:24 .000034722
16-oct-2004 20:12:05 16-oct-2004 20:12:08 .000034722
18-oct-2004 21:47:12 18-oct-2004 21:47:15 .000034722
18-oct-2004 21:47:24 22-oct-2004 14:25:53 3.6933912
23-oct-2004 14:04:40 23-oct-2004 14:54:16 .034444444
23-oct-2004 15:17:52 23-oct-2004 17:54:05 .108483796
23-oct-2004 18:24:42 23-oct-2004 21:08:15 .113576389
24-oct-2004 10:25:01 24-oct-2004 10:26:31 .001041667
27-oct-2004 06:39:10 27-oct-2004 06:39:11 .000011574
27-oct-2004 06:39:19 27-oct-2004 06:49:27 .007037037
28-oct-2004 07:37:23 05-nov-2004 09:35:25 8.08196759
05-nov-2004 14:51:01 05-nov-2004 14:51:01 0
05-nov-2004 15:00:51 05-nov-2004 15:00:51 0
05-nov-2004 15:23:08 05-nov-2004 15:23:10 .000023148
06-nov-2004 09:33:52 06-nov-2004 11:24:36 .076898148
06-nov-2004 11:26:36 06-nov-2004 11:26:36 0
06-nov-2004 11:26:42 07-nov-2004 14:25:06 1.12388889
09-nov-2004 07:35:58 09-nov-2004 07:35:58 0
09-nov-2004 07:39:58 09-nov-2004 07:40:06 .000092593
09-nov-2004 08:52:59 12-nov-2004 21:25:18 3.52244213
12-nov-2004 21:25:27 19-nov-2004 09:25:19 6.49990741
19-nov-2004 11:10:00 20-nov-2004 15:46:24 1.19194444
20-nov-2004 17:07:44 20-nov-2004 17:07:44 0
20-nov-2004 17:08:23 20-nov-2004 17:08:30 .000081019
37 rows selected.
