Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Multiple log switches upon startup (Oracle7.3.4 in archivelog
--0__=aVnkRyQJxJBAeWxG1cI1b54ztcwqc7VUSmPbHisKPzu1VFWmyg65E2Nc
Content-type: text/plain; charset=us-ascii
Content-Disposition: inline
Can't answer your question about what is happening, but from Oracle/NT experience at our site, NT will write a smaller archivelog after a forced switchlog, which is what you suspected.
There must be something happening on your database which is causing updates, and it appears to be running Monday thru Saturday at roughly 6:00am. The fact that it runs with such regularity suggest scheduled from somewhere. Verify there are no AT jobs scheduled. Maybe you are analyzing the database tables to support Cost Based Optimizer method? Just guessing, but sometimes when you randomly probe the haystack you find the needle.
Good luck.
BoivinP_at_mar.dfo-mpo.gc.ca on 06/02/2000 11:42:49 AM
Please respond to ORACLE-L_at_fatcity.com
From: BoivinP_at_mar.dfo-mpo.gc.ca on 06/02/2000 11:42 AM
To: ORACLE-L_at_fatcity.com
cc: (bcc: Allen R. Lucas/Plainfield/CBI)
Subject: Multiple log switches upon startup (Oracle7.3.4 in archivelog mod e on
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
----- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
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
TIA
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des syst
--0__=aVnkRyQJxJBAeWxG1cI1b54ztcwqc7VUSmPbHisKPzu1VFWmyg65E2Nc
Content-type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-transfer-encoding: quoted-printable =E8mes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | R=E9gion 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 Liststo: 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 - 16:28:19 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
![]() |
![]() |