Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, daniel.

Asked: November 20, 2004 - 4:54 pm UTC

Last updated: May 16, 2012 - 1:02 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

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 Tom 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.


The last date before the Starting ORACLE.... is the start up time, the last date record before that is the last *observed* timestamp



Analytics Rock, they roll, they are the coolest things ever.... (don't look for shutdown -- look for the prior two dates and use them...)

Rating

  (25 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great:)

daniel, November 20, 2004 - 5:23 pm UTC

Thank you for the quick response and the tip.

A reader, November 20, 2004 - 5:57 pm UTC


Brilliant!

A reader, November 20, 2004 - 8:51 pm UTC


greate idea

reader, November 20, 2004 - 9:03 pm UTC 

I never thought of using external table concept to read alert log file. It is cool.

I tried your query on my database. Why don't I see the time component in my result? Thanks.

SQL> 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_alertlog
 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_TIM       DAYS
--------- --------- ----------
13-NOV-04 13-NOV-04          0
13-NOV-04 13-NOV-04 .000069444
13-NOV-04 13-NOV-04 .000034722
14-NOV-04 14-NOV-04 .000011574
15-NOV-04 15-NOV-04          0
16-NOV-04 16-NOV-04          0
17-NOV-04 17-NOV-04          0
18-NOV-04 18-NOV-04          0
19-NOV-04 19-NOV-04          0
20-NOV-04 20-NOV-04          0
20-NOV-04 20-NOV-04          0

11 rows selected.

Tom Kyte
November 21, 2004 - 8:59 am UTC 

i did an 

ops$tkyte@ORA9IR2> @bigdate
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; 
Session altered.
 

alter session.

what os are you on?

A reader, November 20, 2004 - 11:08 pm UTC

I've compared alert log on sun solaris and windows and they were a little bit different.

Tom Kyte
November 21, 2004 - 9:08 am UTC

i did it on linux, but this:


Mon Nov 08 10:07:35 2004
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Nov 08 10:07:41 2004
Starting ORACLE instance (normal)


is from my 9ir2 on windows. and 817 looked the same.


But, if you see minor tweaks -- make em' post the info.

Can this be done against the real alert log?

Bob Maggio, November 22, 2004 - 10:37 am UTC

Just curious as to whether this can or should be done against the actual alert log? I tried it with a copy and it was great, and this led to dicussion on being able to query the alert log itself for notification jobs, error trends, unknown issues etc. Is there a reason this shouldn't be done against the active alert log? Could it cause any problems that are not apparent?
Thanks
Bob

Tom Kyte
November 22, 2004 - 3:23 pm UTC

I have not heard of, nor could I forsee any issues with reading the alert log (i frequently tail -f it, even on windows)

most tools read it (enterprise manager, quest, whatever)

Error Handling

Jairo Ojeda, December 27, 2004 - 7:52 pm UTC

Tom, can you help me with the next case:
DROP TABLE T1 CASCADE CONSTRAINT;
CREATE TABLE T1
( a INT NOT NULL,
b INT NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (a,b) );
DROP TABLE T2;
CREATE TABLE T2
( x INT NOT NULL,
y INT NOT NULL,
a INT NOT NULL,
b INT NOT NULL,
CONSTRAINT pk_t2 PRIMARY KEY (x,y,a),
CONSTRAINT fk01_t1 FOREIGN KEY (a,b) REFERENCES T1(a,b) );
--TRG_AI_T1
CREATE OR REPLACE TRIGGER TRG_AI_T1
AFTER INSERT ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE T2 SET b = :NEW.b WHERE a = :NEW.a;
END;
/
--TRG_BD_T1
CREATE OR REPLACE TRIGGER TRG_BD_T1
BEFORE DELETE ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
no_parent EXCEPTION;
PRAGMA EXCEPTION_INIT(no_parent, -2291);
BEGIN
UPDATE T2 SET b = :OLD.b-1 WHERE a = :OLD.a;
EXCEPTION
WHEN no_parent THEN
DELETE T2 WHERE a = :OLD.a;
END;
/

INSERT INTO T1 VALUES(1,1);
INSERT INTO T1 VALUES(2,1);
INSERT INTO T2 VALUES(1,1,1,1);
INSERT INTO T2 VALUES(1,2,2,1);
INSERT INTO T1 VALUES(1,2);
INSERT INTO T1 VALUES(2,2);
COMMIT;

DELETE T1 WHERE a=2 AND b=2;
DELETE T1 WHERE a=2 AND b=1;
--It seems that trigger error handing not works
ORA-02291: integrity constraint (BNFINVERSION.FK01_T1) violated - parent key not found

but if i do the same with a pl/sql block it works:
DECLARE
no_parent EXCEPTION;
PRAGMA EXCEPTION_INIT(no_parent, -2291);
BEGIN
UPDATE T2 SET b = 0 WHERE a = 2;
EXCEPTION
WHEN no_parent THEN
DELETE T2 WHERE a = 2;
END;
PL/SQL procedure successfully completed

How can I do that with the trigger "TRG_BD_T1"?

Tom Kyte
December 27, 2004 - 8:02 pm UTC

right after you explain how debugging trigger code relates to shutdown/startup activity....

(especially since at the time this was entered, I was actually accepting new questions.....)

A reader, December 28, 2004 - 10:45 am UTC

Ha Ha Ha.

Uptime - Downtime

Marc-Andre, January 10, 2005 - 2:58 pm UTC

Hi Tom,

using your SQL statement, i created another query. Just to be sure that I am correct in my understanding, what you wrote gave us downtime and my query gives the uptime. Are my statements right?

Thx,

Marc-Andre

select last_time, start_time, start_time-last_time downtime
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 %'
)
/

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
/


Tom Kyte
January 10, 2005 - 4:20 pm UTC 

I just did this one for the book :)

so, i didn't really verify yours -- just supplying my "uptime downtime" report

but yours looks right - just another layer with another lag or lead.

ops$tkyte@ORA10G> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3         round((start_time-last_time)*24*60,2) mins_down,
  4         round((last_time-lag(start_time) over (order by r)),2) days_up,
  5         case when (lead(r) over (order by r) is null )
  6              then round((sysdate-start_time),2)
  7          end days_still_up
  8    from (
  9  select r,
 10         to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 11         to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 12    from (
 13  select r,
 14         text_line,
 15         lag(text_line,1) over (order by r) start_time,
 16         lag(text_line,2) over (order by r) last_time
 17    from (
 18  select rownum r, text_line
 19    from alert_log
 20   where text_line like '___ ___ __ __:__:__ 20__'
 21      or text_line like 'Starting ORACLE instance %'
 22             )
 23             )
 24   where text_line like 'Starting ORACLE instance %'
 25         )
 26  /
 
SHUTDOWN          STARTUP            MINS_DOWN    DAYS_UP DAYS_STILL_UP
----------------- ----------------- ---------- ---------- -------------
                  06-may-2004 14:00
06-may-2004 14:24 06-may-2004 14:24        .25        .02
10-may-2004 17:18 10-may-2004 17:19        .93       4.12
26-jun-2004 13:10 26-jun-2004 13:10        .65      46.83
07-sep-2004 20:13 07-sep-2004 20:20       7.27      73.29        116.83

You might get "ORA-01846: not a valid day of the week"

Winston, April 09, 2005 - 3:24 am UTC 

I used the above query in my 10g database on SunOS, I got the above error. I figured out the issue by running the step by step. At one point, the last_time result set was like this:

  1* select last_time from tmp8  where text_line like 'Starting ORACLE instance %'
SQL> /

LAST_TIME
-------------------------------------------------------------------------------------------------------------------------

Wed Dec  1 16:54:29 2004
Wed Dec  1 16:56:16 2004
Wed Dec  1 21:25:46 2004
Wed Dec  1 21:29:41 2004
Wed Dec  1 22:56:09 2004
Thu Dec  2 07:51:48 2004
Tue Feb  8 10:00:41 2005
Thu Feb 17 09:03:05 2005
Fri Feb 18 12:09:52 2005
Thu Mar 10 08:31:18 2005

LAST_TIME
-------------------------------------------------------------------------------------------------------------------------
Fri Mar 11 08:20:47 2005
Fri Mar 11 08:22:10 2005
Fri Mar 11 08:24:34 2005
Fri Mar 11 08:33:38 2005
Fri Mar 11 08:34:59 2005
Wed Mar 16 16:21:40 2005
Wed Mar 16 16:21:49 2005
Wed Mar 16 17:15:37 2005
Thu Mar 17 10:00:46 2005
<b>Starting ORACLE instance (normal)</b>
Fri Mar 18 21:28:47 2005

LAST_TIME
-------------------------------------------------------------------------------------------------------------------------
Sat Mar 19 13:10:59 2005
Thu Mar 31 06:38:29 2005
Thu Mar 31 06:38:44 2005

25 rows selected.


Line 16 lag(text_line,2) over (order by r) last_time  is broken for my specific alert file.

I changed the query a little bit to work around that error. Basically I just captured all the point of time (t0, t1, .., tn) when "Starting ORACLE instance" happened and I assume the time between two  "Starting ORACLE instance" events (t2-t1, t3-t2,...) are instance up-time. 


Do we have a reliable way to capture the database SHUTDOWN          time?

Tom Kyte
April 09, 2005 - 7:44 am UTC

not if the database was abnormally terminated.

How about Checkpoints

Getachew G., February 15, 2007 - 10:57 pm UTC

Tom,
I really tried to come up with a SQL to get the time it took to complete checkpoints. Unless I do it procedurally, how can I do it using analytics, especially when the complete time for two or more checkpoints is the same as this one:
Thu Feb 1 03:28:50 2007
Completed checkpoint up to RBA [0x18850.2.10], SCN: 48860799836
Completed checkpoint up to RBA [0x18851.2.10], SCN: 48860901514

I looked all over this site to get an idea but for no avail. If not, I will proceed to do it procedurally.

Thank you!
Tom Kyte
February 16, 2007 - 1:54 pm UTC

if you get all of the "records of interest", you can use lag and lead to look backwards or forwards a row in a result set.

Baskar, April 09, 2010 - 1:22 am UTC 

Tom,
Recently from your book i was trying to execute the below query and getting the error
SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3         round((start_time-last_time)*24*60,2)mins_down,
  4         round((last_time-lag(start_time) over (order by r)),2) days_up,
  5         case when (lead(r) over (order by r) is null)
  6              then round((sysdate-start_time),2)
  7         end days_still_up
  8    from (
  9   select r,
 10         to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
 11         to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 12    from (
 13   select r,
 14          text_line,
 15         lag(text_line,1) over (order by r) start_time,
 16         lag(text_line,2) over (order by r) last_time
 17    from (
 18   select rownum r,text_line
 19    from alert_log_steeld6
 20   where text_line like '__ __ __ __:__:__20__'
 21       or text_line like 'Starting ORACLE instance %'
 22            )
 23            )
 24   where text_line like 'Starting ORACLE instance %'
 25            )
 26  /
ERROR:
ORA-01846: not a valid day of the week



no rows selected

DB version is 10.2.0.4

please suggest on the same.

thanks,
baskar.l

Tom Kyte
April 13, 2010 - 8:35 am UTC

what debugging have you done?

I would start by remove the to_dates - leaving them as strings and see what you see?

still erroring out

baskar, May 04, 2010 - 12:04 am UTC 

hi,


SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3         round((start_time-last_time)*24*60,2)mins_down,
  4         round((last_time-lag(start_time) over (order by r)),2) days_up,
  5         case when (lead(r) over (order by r) is null)
  6              then round((sysdate-start_time),2)
  7         end days_still_up
  8    from (
  9   select r,
 10          last_time,
 11          start_time
 12    from (
 13   select r,
 14          text_line,
 15         lag(text_line,1) over (order by r) start_time,
 16         lag(text_line,2) over (order by r) last_time
 17    from (
 18   select rownum r,text_line
 19    from alert_log_steeld6
 20   where text_line like '__ __ __ __:__:__20__'
 21       or text_line like 'Starting ORACLE instance %'
 22            )
 23            )
 24   where text_line like 'Starting ORACLE instance %'
 25            )
 26  /
            then round((sysdate-start_time),2)
                                            *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER


SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3         round((start_time-last_time)*24*60,2)mins_down,
  4         round((last_time-lag(start_time) over (order by r)),2) days_up,
  5         case when (lead(r) over (order by r) is null)
  6              then round((sysdate-start_time))
  7         end days_still_up
  8    from (
  9   select r,
 10          last_time,
 11          start_time
 12    from (
 13   select r,
 14          text_line,
 15         lag(text_line,1) over (order by r) start_time,
 16         lag(text_line,2) over (order by r) last_time
 17    from (
 18   select rownum r,text_line
 19    from alert_log_steeld6
 20   where text_line like '__ __ __ __:__:__20__'
 21       or text_line like 'Starting ORACLE instance %'
 22            )
 23            )
 24   where text_line like 'Starting ORACLE instance %'
 25            )
 26  /
ERROR:
ORA-01722: invalid number



no rows selected


thanks,
baskar.l

Tom Kyte
May 06, 2010 - 12:50 pm UTC

and what exactly have YOU done to debug this?



You have entirely missed the point of:

I would start by remove the to_dates - leaving them as strings and see what you see?

by still doing this:

round((sysdate-start_time),2)


umm, you are still converting the string into a DATE. My suggestion was (further clarified)


stop converting the strings into dates, just select out the data and VISUALLY inspect it to see why the conversion error is happening

You know - debug it.

A reader, May 09, 2010 - 11:14 pm UTC

Hi, Still am not able to debug this.
Tom Kyte
May 10, 2010 - 7:04 pm UTC

sorry, do you work with a programmer?

baskar, May 12, 2010 - 1:09 am UTC 

Am a dba..tried now too..and getting the same error..any NLS format i have to change

SQL> CREATE TABLE alert_STEELD6
  2  (
  3  text_line varchar2(4000)
  4  )
  5  organization external (
  6  type oracle_loader
  7  default directory DATA_DIR_STEELD6
  8  access parameters (
  9  records delimited by newline
 10  nobadfile
 11  nologfile
 12  nodiscardfile
 13  )
 14  location ('alert_STEELD6.log')
 15  )
 16  reject limit unlimited
 17  /


Table created.

SQL> SQL>
SQL>
SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
  2  to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
  3  round((start_time-last_time)*24*60,2) mins_down,
  4  round((last_time-lag(start_time) over (order by r)),2) days_up,
  5  case when (lead(r) over (order by r) is null )
  6  then round((sysdate-start_time),2)
  7  end days_still_up
  8  from (
  9  select r,
 10  to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 11  to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 12  from (
 13  select r,
 14  text_line,
 15  lag(text_line,1) over (order by r) start_time,
 16  lag(text_line,2) over (order by r) last_time
 17  from (
 18  select rownum r, text_line
 19  from alert_STEELD6
 20  where text_line like '___ ___ __ __:__:__ 20__'
 21  or text_line like 'Starting ORACLE instance %'
 22  )
 23  )
 24  where text_line like 'Starting ORACLE instance %'
 25  )
 26  /
ERROR:
ORA-01846: not a valid day of the week



no rows selected

thanks,
baskar.l

to_char & to_date

ogan ozdogan, May 19, 2010 - 8:32 am UTC

To whom that has got the date format error, please use tkyte's solution rather than using to_char function. using to_char function like to_date function without using the to_date function may give you a conversion error. please bear in mind.

A tweak when reading date from alert.log

A reader, December 01, 2010 - 11:52 am UTC

Tom,

I've set up the external table ok.

Selecting * from it is showing me the alert log ok.

But... When I run your analitics query...

I get an:
ORA-01846: not a valid day of the week
01846. 00000 - "not a valid day of the week"
*Cause:
*Action:

I see a few people are getting that too.

There's a difference in our alert log formats:

Mine -> Thu Jan 7 19:05:52 2010
(10gR2 / Red Hat)

Yours -> Mon Nov 08 10:07:35 2004

The day format "_7" instead of "07" is the only cause I see, so far.

How could I fix this ?

Thank you, Tom

=D
Tom Kyte
December 07, 2010 - 8:54 am UTC 

ops$tkyte%ORA11GR2> select to_date('Thu Jan  7 19:05:52 2010', 'Dy Mon DD HH24:MI:SS YYYY') from dual;

TO_DATE('
---------
07-JAN-10



but that converts just fine, the leading zero is not relevant.

Turn the to_date into a plsql function call:

ops$tkyte%ORA11GR2> create or replace function my_to_date( p_str in varchar2, p_fmt in varchar2 ) return date
  2  as
  3  begin
  4          return to_date( p_str, p_fmt );
  5  exception
  6          when others
  7          then
  8                  raise_application_error( -20000, 'offending date = "' || p_str || '"' || sqlerrm );
  9  end;
 10  /

Function created.


that'll help us nail it down.

same

same, December 08, 2010 - 2:50 pm UTC

Here it is, verbatim, copy&paste...

ORA-20000: offending date = "Thu Jan 7 19:05:51 2010"ORA-01846: not a valid day of the week
ORA-06512: at "SYSTEM.MY_TO_DATE", line 9
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.


By the way, how can I make sure my language settings match yours ? (Maybe I missed that above)

=D
Tom Kyte
December 08, 2010 - 3:20 pm UTC 

ops$tkyte%ORA11GR2> select to_date('Thu Jan  7 19:05:51 2010', 'Dy Mon DD HH24:MI:SS YYYY') from dual;

TO_DATE('THUJAN719:0
--------------------
07-jan-2010 19:05:51


but that should work ok - can you add the format to the error message?

the NLS stuff shouldn't matter here, I'm using an explicit format.

It's probably the language setting

Günter, December 09, 2010 - 2:19 am UTC 

Hi Tom,

after lurking (and learning) a lot on your site, I'm trying to contribute.

The error is probably caused by a different language setting:

$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
$ sqlplus
>  select to_date('Thu Jan  7 19:05:51 2010', 'Dy Mon DD HH24:MI:SS YYYY') from
dual;  2

TO_DATE('T
----------
07.01.2010

> exit

$ export NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15
$ sqlplus

> select to_date('Thu Jan  7 19:05:51 2010', 'Dy Mon DD HH24:MI:SS YYYY') from
dual;
                *
FEHLER in Zeile 1:
ORA-01846: Kein gültiger Wochentag

Regards

Günter

Tom Kyte
December 09, 2010 - 10:36 am UTC

doh, that language :) yes, if the alert is in english but your session is in german - that would do it.

I was thinking purely in terms of NLS date settings - sorry about that.

not explicit enough

Sokrates, December 09, 2010 - 2:46 am UTC

"... the NLS stuff shouldn't matter here, I'm using an explicit format. ..."

I think Günter is right, you are explicit, but not explicit enough:

select to_date('Thu Jan  7 19:05:51 2010', 'Dy Mon DD HH24:MI:SS YYYY', 'nls_date_language=''american''') from dual


should also work for Günter (as for "A reader" and "same")

Tom Kyte
December 09, 2010 - 10:45 am UTC

you are correct, I was forgetting about the language - was thinking only about the date settings.

Ok, now it's working fine.

Andre, December 09, 2010 - 8:16 am UTC

Finally, any user can use that, regardless of international settings. Thanks Tom. =D

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 %'
);

07-jan-2010 19:05   
07-jan-2010 19:06 07-jan-2010 19:06 0,05 0 
07-jan-2010 19:06 07-jan-2010 19:06 0,03 0 
07-jan-2010 19:31 07-jan-2010 19:44 12,62 0,02 
07-jan-2010 19:44 07-jan-2010 19:46 1,68 0 
07-jan-2010 19:46 07-jan-2010 19:48 1,88 0 
07-jan-2010 19:48 07-jan-2010 19:48 0,1 0 
07-jan-2010 19:48 07-jan-2010 19:50 1,65 0 
07-jan-2010 19:51 07-jan-2010 19:58 6,25 0 
07-jan-2010 19:59 07-jan-2010 19:59 0,03 0 
07-jan-2010 19:59 07-jan-2010 19:59 0,03 0 
07-jan-2010 20:12 07-jan-2010 20:12 0,03 0,01 
07-jan-2010 20:17 07-jan-2010 20:18 0,98 0 
07-jan-2010 20:24 08-jan-2010 12:04 940,47 0 
08-jan-2010 12:05 08-jan-2010 15:08 183,52 0 
08-jan-2010 15:08 08-jan-2010 15:09 0,25 0 
12-jan-2010 17:49 12-jan-2010 20:16 146,67 4,11 
18-jan-2010 17:16 18-jan-2010 17:40 24,25 5,87 
18-jan-2010 17:40 18-jan-2010 18:01 20,73 0 
20-jan-2010 13:27 20-jan-2010 13:29 1,68 1,81 
20-jan-2010 13:32 20-jan-2010 13:33 0,35 0 
22-jan-2010 16:48 22-jan-2010 16:48 0,48 2,14 
10-fev-2010 19:32 11-fev-2010 07:09 696,98 19,11 
22-fev-2010 13:49 22-fev-2010 15:32 102,33 11,28 
28-fev-2010 17:48 01-mar-2010 09:28 939,5 6,09 
14-mar-2010 20:56 14-mar-2010 20:56 0 13,48 
30-mar-2010 14:53 30-mar-2010 14:53 0,17 15,75 
05-abr-2010 23:45 06-abr-2010 10:35 650,8 6,37 
06-abr-2010 10:36 06-abr-2010 11:30 54,27 0 
06-abr-2010 23:00 07-abr-2010 09:13 612,95 0,48 
07-abr-2010 09:19 07-abr-2010 09:19 0,25 0 
06-jun-2010 07:37 06-jun-2010 16:52 555,33 59,93 
23-jul-2010 23:51 23-jul-2010 23:51 0,82 47,29 
29-set-2010 14:14 29-set-2010 14:14 0,38 67,6 
29-set-2010 14:20 29-set-2010 14:20 0,28 0 
29-set-2010 14:22 29-set-2010 14:22 0,53 0 
29-set-2010 14:28 29-set-2010 14:28 0,25 0 
29-set-2010 14:29 29-set-2010 14:29 0,37 0 
29-set-2010 14:36 29-set-2010 14:36 0,18 0 
29-set-2010 14:41 29-set-2010 14:41 0,12 0 
12-nov-2010 19:19 12-nov-2010 19:57 37,97 44,19 
12-nov-2010 20:23 12-nov-2010 20:42 19,45 0,02 26,6

How to break up %uptime per month and per year ?

Andre, December 09, 2010 - 9:10 am UTC

Tom,

How could I extend your query so it shows %uptime per month and per year, like that:

SHUTDOWN          STARTUP            MINS_DOWN    DAYS_UP DAYS_STILL_UP MONTH_UPTIME YEAR_UPTIME
----------------- ----------------- ---------- ---------- ------------- ------------ -----------
                  06-may-2004 14:00
06-may-2004 14:24 06-may-2004 14:24        .25        .02
10-may-2004 17:18 10-may-2004 17:19        .93       4.12                       ?.??                  
26-jun-2004 13:10 26-jun-2004 13:10        .65      46.83                       ?.??
07-sep-2004 20:13 07-sep-2004 20:20       7.27      73.29        116.83         ?.??        ?.??




As to the query above.

Andre, December 10, 2010 - 6:22 pm UTC

Continued... I tried to write the query from the post above using GROUP BY ROLLUP. But the output wasn't what I expected (like the layout I created above). Maybe analytics will work better ? Please give me some directions.

Thanks, Tom.
Tom Kyte
December 10, 2010 - 6:36 pm UTC

show us your work and then we can comment.

test

Max, May 15, 2012 - 3:40 pm UTC

Hello,

Is there a way to do something similar but instead using DBA_HIST_DATABASE_INSTANCE....

The reason behind that is, If we get the query working we would like to run this thru grid, so it can run against multiple DB. And we didnt wanted to create any new objects in the db.

I treid doing something similar but the results seems very much so wrong....

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, startup_time,
lag(startup_time,1) over (order by r) start_time,
lag(startup_time,2) over (order by r) last_time
from (
select rownum r, to_char(startup_time, 'Dy Mon DD HH24:MI:SS YYYY') startup_time From DBA_HIST_DATABASE_INSTANCE)));
Tom Kyte
May 16, 2012 - 1:02 am UTC

that view only has startup times, you would not be able to figure out shutdown times from that.

Field separator

Erix, June 17, 2015 - 6:59 pm UTC

I was using this approach on 11GR2 to chase ORA-01555 with particular interest on those with Query Duration=0 sec. These lines,
ORA-01555 caused by SQL statement below (SQL ID: 0ssfccf1r55kw, Query Duration=0 sec, SCN: 0x0876.c3bd81fd):

But the query was returning them like this,
ORA-01555 caused by SQL statement below (SQL ID: 0ssfccf1r55kw

I ended up indicating an "improvable" field separator in the external table,

fields terminated by "~!@"

It worked but I suspect there is a more elegant way of doing it... is there?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.