| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: redo logs: instantaneous switches
Check the size of the archives being produced. If the log switches are a
result of the logs filling up through normal transactional activity, then
each archive will bethe same size as each online log. But if you've got
something or someone issuing spurious 'alter system switch logfile'
commands, then the archive produced will be much, much smaller than the
online log.
For example:
SQL> archive log list
Database log mode Archive Mode Automatic archival Enabled Archive destination D:\oracle\ora92\RDBMS Oldest online log sequence 8 Next log sequence to archive 11 Current log sequence 11
System altered.
SQL> select group#, bytes from v$log;
GROUP# BYTES
---------- ----------
1 104857600
2 104857600
3 104857600
SQL> exit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\Howard>dir d:\oracle\ora92\rdbms\*.*
Volume in drive D is LARGE_DATA
Volume Serial Number is E8CB-D350
Directory of d:\oracle\ora92\rdbms
20/01/2003 03:54 PM 104,857,088 ARC00009.001 21/01/2003 06:17 PM 104,857,088 ARC00010.001 24/01/2003 06:56 AM 28,335,616 ARC00011.001
So, usually each archive is 100M, just as my online logs are sized. But the last one generated, forced with a log switch, is much smaller.
Anyway, that would indicate whether you've got a rogue log switcher on your team. If they're all the same size, then you'd have to start delving into log miner to see what was happening on the database at the time the log switch rate sky-rocketed.
Regards
HJR
"dsmcd" <lookingglass_at_stopspammdormouse.net> wrote in message
news:0AXX9.991$H14.42735_at_news.uswest.net...
> Hello...
>
> 817 on Win2k.
>
> I'm archiving & duplexing my redo logs. Certain days the logs
> appear to be instantaneously switching, though if I look at a
> log's first change # and last change #, they are different so
> perhaps they're not instantaneous. I can't be sure since times
> are in minutes not seconds. Either way, it's unexpected and
> worrisome.
>
> In fact, there was one full day when I had dozens of switches
> every minute, all day long.
>
> Usually, logs would switch at most two or three times on hour.
> There's been no added load to the system that would come close
> to accounting for such fast switching.
>
> I shutdown and startup the instance each day, which may point to
> something in the startup or shutdown effecting the logs.
>
> I have three groups, each with one log.
>
> Any suggestions? This has me quite concerned.
>
> TIA,
> D.
> Here's some data:
>
> Browsing the archive folder, I'll see:
> POS4T001S12148.arc 1,025KB 01/22/03 8:19pm
> POS4T001S12149.arc 1,025KB 01/22/03 8:46pm
> POS4T001S12150.arc 1,025KB 01/22/03 8:46pm
> POS4T001S12151.arc 1,025KB 01/22/03 9:29pm
> POS4T001S12152.arc 1,025KB 01/22/03 9:29pm
> POS4T001S12153.arc 1,025KB 01/22/03 9:30pm
> POS4T001S12154.arc 1,025KB 01/22/03 9:30pm
> POS4T001S12155.arc 1,025KB 01/22/03 9:31pm
> POS4T001S12156.arc 1,025KB 01/22/03 9:31pm
> POS4T001S12157.arc 1,025KB 01/22/03 9:49pm
> POS4T001S12148.arc 1,025KB 01/22/03 9:53pm
>
> Looking at alert logs shows
> Wed Jan 22 20:03:37 2003
> ARC1: Completed archiving log# 3 seq# 12147
> Wed Jan 22 20:19:02 2003
> Thread 1 advanced to log sequence 12149
> Current log# 2 seq# 12149 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO02.LOG
> Wed Jan 22 20:19:02 2003
> ARC0: Beginning to archive log# 1 seq# 12148
> ARC0: Completed archiving log# 1 seq# 12148
> Wed Jan 22 20:46:43 2003
> Thread 1 advanced to log sequence 12150
> Current log# 3 seq# 12150 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 20:46:43 2003
> ARC0: Beginning to archive log# 2 seq# 12149
> Wed Jan 22 20:46:44 2003
> Thread 1 cannot allocate new log, sequence 12151
> Checkpoint not complete
> Current log# 3 seq# 12150 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 20:46:44 2003
> ARC0: Completed archiving log# 2 seq# 12149
> Wed Jan 22 20:46:46 2003
> Thread 1 advanced to log sequence 12151
> Current log# 1 seq# 12151 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO03.LOG
> Wed Jan 22 20:46:46 2003
> ARC0: Beginning to archive log# 3 seq# 12150
> ARC0: Completed archiving log# 3 seq# 12150
> Wed Jan 22 21:29:33 2003
> Thread 1 advanced to log sequence 12152
> Current log# 2 seq# 12152 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO02.LOG
> Wed Jan 22 21:29:33 2003
> ARC0: Beginning to archive log# 1 seq# 12151
> Wed Jan 22 21:29:35 2003
> Thread 1 advanced to log sequence 12153
> Current log# 3 seq# 12153 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 21:29:35 2003
> ARC0: Completed archiving log# 1 seq# 12151
> ARC0: Beginning to archive log# 2 seq# 12152
> Wed Jan 22 21:29:35 2003
> ARC1: Beginning to archive log# 2 seq# 12152
> ARC1: Failed to archive log# 2 seq# 12152
> ARC1: Beginning to archive log# 2 seq# 12152
> ARC1: Failed to archive log# 2 seq# 12152
> Wed Jan 22 21:29:35 2003
> ARC0: Completed archiving log# 2 seq# 12152
> Wed Jan 22 21:30:20 2003
> Thread 1 advanced to log sequence 12154
> Current log# 1 seq# 12154 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO03.LOG
> Wed Jan 22 21:30:20 2003
> ARC0: Beginning to archive log# 3 seq# 12153
> Wed Jan 22 21:30:21 2003
> Thread 1 cannot allocate new log, sequence 12155
> Checkpoint not complete
> Current log# 1 seq# 12154 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO03.LOG
> Wed Jan 22 21:30:21 2003
> ARC0: Completed archiving log# 3 seq# 12153
> Wed Jan 22 21:30:24 2003
> Thread 1 advanced to log sequence 12155
> Current log# 2 seq# 12155 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO02.LOG
> Wed Jan 22 21:30:24 2003
> ARC0: Beginning to archive log# 1 seq# 12154
> ARC0: Completed archiving log# 1 seq# 12154
> Wed Jan 22 21:31:24 2003
> Thread 1 advanced to log sequence 12156
> Current log# 3 seq# 12156 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 21:31:24 2003
> ARC0: Beginning to archive log# 2 seq# 12155
> Wed Jan 22 21:31:25 2003
> Thread 1 cannot allocate new log, sequence 12157
> Checkpoint not complete
> Current log# 3 seq# 12156 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 21:31:25 2003
> ARC0: Completed archiving log# 2 seq# 12155
> Wed Jan 22 21:31:26 2003
> Thread 1 advanced to log sequence 12157
> Current log# 1 seq# 12157 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO03.LOG
> Wed Jan 22 21:31:26 2003
> ARC0: Beginning to archive log# 3 seq# 12156
> ARC0: Completed archiving log# 3 seq# 12156
> Wed Jan 22 21:49:12 2003
> Thread 1 advanced to log sequence 12158
> Current log# 2 seq# 12158 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO02.LOG
> Wed Jan 22 21:49:12 2003
> ARC0: Beginning to archive log# 1 seq# 12157
> ARC0: Completed archiving log# 1 seq# 12157
> Wed Jan 22 21:53:57 2003
> Thread 1 advanced to log sequence 12159
> Current log# 3 seq# 12159 mem# 0:
> D:\ORACLE\ORADATA\POS4\REDO01.LOG
> Wed Jan 22 21:53:57 2003
> ARC0: Beginning to archive log# 2 seq# 12158
> ARC0: Completed archiving log# 2 seq# 12158
>
>
Received on Thu Jan 23 2003 - 14:05:47 CST
 |
 |