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

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Fri, 02 Jun 2000 12:24:02 -0300
Message-Id: <10516.107567@fatcity.com>

I noticed that every morning, after the cold backup finishes and duri= ng the
same hour as the databases on this server restart, multiple log switc= hes 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 k= now
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 l= ike
that regularly at 6am, although a couple of them do one or two log sw= itches
as they come back up.

Has anyone seen something like this? Is it because the database is i= n
archivelog mode on NT, or is designer doing this somehow? The databa= se
can't be logging changes but then only archiving the logs upon startu= p, 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 sc= ript
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"

=66rom 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=E8mes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique=20
Maritimes Region, DFO      | R=E9gion des Maritimes, MPO

Received on Fri Jun 02 2000 - 10:24:02 CDT