Patrice,
If you still don't have an explanation for what's
causing the redo and want to see what's in the
archived logs, you can dump the contents of a log with
the following command:
ALTER SYSTEM DUMP LOGFILE '<path\filename>';
This will generate a trace file in udump.
If you need help interpreting it, compress the trace
file and send it to me.
HTH,
- Anita
- "Boivin, Patrice J" <BoivinP_at_mar.dfo-mpo.gc.ca>
wrote:
> I noticed that every morning, after the cold backup
> finishes and during the
> same hour as the databases on this server restart,
> multiple log switches are
> taking place on one of the five instances.
>
> The pattern looks like this:
>
> Redo Log Switches per Hour
> DAY 00 01 02 03 04 05 06 07 08 09 10 11
> 12 13 14 15 16 17
> 18 19 20 21 22 23
> ----- --- --- --- --- --- --- --- --- --- --- ---
> --- --- --- --- --- ---
> --- --- --- --- --- --- ---
> 05/07 0 0 1 0 0 0 0 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/08 0 0 1 0 0 0 14 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/09 0 0 0 0 0 0 15 0 0 1 1
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/10 0 0 0 0 0 0 14 0 0 0 1
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/11 0 0 0 0 0 0 14 0 0 0 1
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/12 0 0 0 0 0 0 15 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/13 0 0 0 0 0 0 14 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/14 0 0 1 0 0 0 0 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/15 0 0 1 0 0 0 14 0 1 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/16 0 0 0 0 0 0 15 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/17 0 0 0 0 0 0 14 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/18 0 0 0 0 0 0 14 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/19 0 0 0 0 0 0 15 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
> 05/20 0 0 0 0 0 0 14 0 0 0 0
> 0 0 0 0 0 0
> 0 0 0 0 0 0 0
>
> The database is small, and the only thing running on
> it is Designer. As you
> can see there aren't many writes taking place.
>
> I checked this database's archived logs, they are
> all full-size. I know
> that in UNIX if the log switch is forced, the
> archived log is smaller.. I
> don't know if this is the case on NT.
>
> The other four databases on this server do not
> perform log switches like
> that regularly at 6am, although a couple of them do
> one or two log switches
> as they come back up.
>
> Has anyone seen something like this? Is it because
> the database is in
> archivelog mode on NT, or is designer doing this
> somehow? The database
> can't be logging changes but then only archiving the
> logs upon startup, that
> wouldn't make any sense. Also I don't have 14 or 15
> log members per group.
>
> I asked the developers who use this database if they
> are running a script
> early in the morning, they say no.
>
> The query I used to get the above output is
> prompt
> rem show amount of redo log switches per hour
> ttitle left 'Redo Log Switches per Hour'
> select substr(time, 1, 5) day,
>
to_char(sum(decode(substr(time,10,2),'00',1,0)),'99')
> "00",
>
to_char(sum(decode(substr(time,10,2),'01',1,0)),'99')
> "01",
>
to_char(sum(decode(substr(time,10,2),'02',1,0)),'99')
> "02",
>
to_char(sum(decode(substr(time,10,2),'03',1,0)),'99')
> "03",
>
to_char(sum(decode(substr(time,10,2),'04',1,0)),'99')
> "04",
>
to_char(sum(decode(substr(time,10,2),'05',1,0)),'99')
> "05",
>
to_char(sum(decode(substr(time,10,2),'06',1,0)),'99')
> "06",
>
to_char(sum(decode(substr(time,10,2),'07',1,0)),'99')
> "07",
>
to_char(sum(decode(substr(time,10,2),'08',1,0)),'99')
> "08",
>
to_char(sum(decode(substr(time,10,2),'09',1,0)),'99')
> "09",
>
to_char(sum(decode(substr(time,10,2),'10',1,0)),'99')
> "10",
>
to_char(sum(decode(substr(time,10,2),'11',1,0)),'99')
> "11",
>
to_char(sum(decode(substr(time,10,2),'12',1,0)),'99')
> "12",
>
to_char(sum(decode(substr(time,10,2),'13',1,0)),'99')
> "13",
>
to_char(sum(decode(substr(time,10,2),'14',1,0)),'99')
> "14",
>
to_char(sum(decode(substr(time,10,2),'15',1,0)),'99')
> "15",
>
to_char(sum(decode(substr(time,10,2),'16',1,0)),'99')
> "16",
>
to_char(sum(decode(substr(time,10,2),'17',1,0)),'99')
> "17",
>
to_char(sum(decode(substr(time,10,2),'18',1,0)),'99')
> "18",
>
to_char(sum(decode(substr(time,10,2),'19',1,0)),'99')
> "19",
>
to_char(sum(decode(substr(time,10,2),'20',1,0)),'99')
> "20",
>
to_char(sum(decode(substr(time,10,2),'21',1,0)),'99')
> "21",
>
to_char(sum(decode(substr(time,10,2),'22',1,0)),'99')
> "22",
>
to_char(sum(decode(substr(time,10,2),'23',1,0)),'99')
> "23"
> from v$log_history
> group by substr(time,1,5)
> /
>
>
> TIA
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)
>
> Systems Admin & Operations | Admin. et Exploit. des
> systèmes
> Technology Services | Services technologiques
> Informatics Branch | Direction de
> l'informatique
> Maritimes Region, DFO | Région des Maritimes,
> MPO
>
> E-Mail: boivinp_at_mar.dfo-mpo.gc.ca
> <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
>
>
> --
> Author: Boivin, Patrice J
> INET: BoivinP_at_mar.dfo-mpo.gc.ca
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
Received on Fri Jun 02 2000 - 15:47:37 CDT
