RE: (personal reply) Re: Multiple log switches upon startup (Orac

Well, *I* learned about it in the Data Internals seminars that I just took... check the education website for them

Rachel

>From: "Boivin, Patrice J" <BoivinP_at_mar.dfo-mpo.gc.ca>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: (personal reply) Re: Multiple log switches upon startup (Orac
>Date: Mon, 05 Jun 2000 05:43:57 -0800
>
>Where did you learn about this use of alter system? (you don't have to tell
>me, I'm just surprised).
>
>Everything is in code, would TKPROF be able to reconstruct what is in the
>log file?
>
>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>
>
>Ph: (902) 426-4774
>
>
> -----Original Message-----
> From: A. Bardeen [SMTP:abardeen1_at_yahoo.com]
> Sent: Friday, June 02, 2000 6:58 PM
> To: Multiple recipients of list ORACLE-L
> Subject: (personal reply) Re: Multiple log switches upon
>startup (Oracle7.3.4 in archivelog mod e on
>
> 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
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
> --
> Author: A. Bardeen
> INET: abardeen1_at_yahoo.com
>
> 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
> also send the HELP command for other information (like subscribing).
>--
>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
Received on Mon Jun 05 2000 - 08:20:46 CDT