Re: Multiple log switches upon startup (Oracle7.3.4 in archivelog mod e on

Patrice,

Are there any jobs scheduled to run at that time or during the time when the db is shutdown for the cold backup?

Just as on Unix, if the log is manually switched before it is full, the archived log will be smaller than the online log.

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