Oracle 9.2.0.6->10.2.0.2 Upgrade : archivelogs now smaller

[Volker Borowski]

Hi,

I have upgraded three systems (E/Q/P) from Oracle 9.2.0.6 to 10.2.0.2 !

While E/Q still write archivelogs of the same size as the online redologs are, no matter if I have low or high system activity, the production system writes smaller archivelogs.

DB-Parameters are nearly the same, beside some memory settings for production are higher.

All 3 systems have 20MB onlinelogs (old SAP standard :-) and the production now writes archivelogs of ~13MB sometimes 14MB.

Strange:
While the parameter log_buffer has been explictly set to 1048576 (1M) on all three systems, all three do write this value back to init.ora, where I do a "create pfile from spfile".

But
show parameter log_buffer

on the running instances show
E/Q = 14258688 {=27840 Blocks}
P = 14254080 {=27849 Blocks}

I have LGWR logging on all systems
LGWR: Archivelog for thread 1 sequence 64715 will NOT be compressed

so log-compression should not be an issue.


According to V$ARCHIVED_LOG, oracle knows about the current archivelogs being smaller, as a lower value for "blocks" is recorded there, and confirms, that the logs are not compressed.

SQL> host ls -l /oracle/.../_64715_.dbf
-rw-r----- 1 orasid dba 13584384 /oracle/.../_64715_.dbf

SQL> select (BLOCKS+1)*BLOCK_SIZE from v$archived_log where SEQUENCE#=64715;

(BLOCKS+1)*BLOCK_SIZE
---------------------
13584384

This gives me the feeling, that everything should be ok, but I'd really like to know why the DB is switching before the logfile is completely filled.

I have other systems with 50MB logfiles, that have been upgraded by someone else and the archivelogs have an average size of 41-43MB there. This again lets me assume, that oracle is flushing the onlinelogs in multiples of the logbuffer (~14M, my size / and 3*14=42M the 50MB systems)... but this does not explain, why my other 2 systems still write 20MB logfiles.

Has anyone more detailed information about this behavior ? My colleague with 50MB logs is about to do a test-recovery to ensure, that the redologs are not corrupted during archival.

Thanks for feedback
Volker

[TwoProc]

Just a guess, but,
what are the checkpoints set to?

[Jonathan Fife]

Is anything showing up in the alert log at around the time the logs switch and the archived logs are written?

[Steven E. Protter]

Shalom Volker,

Oracle decided their database was smarter than the DBA and sysadmins. Probably one of these features to improve performance decided that smaller archive logs are more efficient, which may be right, but you should be notified in some way of the change.

SEP

[Volker Borowski]

Hi,
Checkpoints should be no issue.
A logswitch causes a checkpoint, but
a checkpoint does not cause a logswitch.

Alter system checkpoint;

Wed Aug 16 16:25:43 2006
Beginning global checkpoint up to RBA [0xfcd2.2dd3.10], SCN: 456652144
Completed checkpoint up to RBA [0xfcd2.2dd3.10], SCN: 456652144
Completed checkpoint up to RBA [0xfcd2.2.10], SCN: 456649406

alter system switch logfile;

Wed Aug 16 16:25:59 2006
Beginning log switch checkpoint up to RBA [0xfcd3.2.10], SCN: 456652322
Thread 1 advanced to log sequence 64723
Current log# 12 seq# 64723 mem# 0: /oracle/.../log_g12m1.dbf
Current log# 12 seq# 64723 mem# 1: /oracle/.../log_g12m2.dbf
Wed Aug 16 16:26:16 2006
Incremental checkpoint up to RBA [0xfcd2.2dee.0], current log tail at RBA [0xfcd3.266.0]

the "normal" logswitches during operation list the same action as the manual issued one, with the exception that a real checkpoint is triggerd and not an incremental one.

Volker

[TwoProc]

You're correct. Checkpoint flushes to disk, but apparently only from the buffer_log to the redo_log (from what I've just read) - I've always thought (apparently incorrectly) that it went all the way down to archive log. Reason is that I've read that the purpose of the checkpoint is to give you a clean recovery point. Well, I read into it that it meant that archive logs would be flushed, which is what I'd want from a recovery point.
Excuse the post.

[Yogeeraj_1]

hi volker,

please allow me to comment and propose something.

Something is definitely wrong here. if your log switches are ocurring normally and no error messages in the alert.log, there must be something going wrong.

Normally, the log_checkpoint (and/or fast_start_*) parameters control dbwr's checkpointing of data. If you set them aggresively, it'll incrementally checkpoint to ensure the documented meaning of those parameters is satistifed.

Also, the documentations say that a checkpoint is realized on five types of events:
- At each switch of the redo log files.
- When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
- When the size in bytes corresponding to :
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
is written on the current redo log file.
- Directly by the ALTER SYSTEM SWITCH LOGFILE command.
- Directly with the ALTER SYSTEM CHECKPOINT command.

If any of the above does not apply to your context, I would propose that you rebuild your redolog files.

Allow me to also quote the following:
Log file switches will always override checkpoints caused by following parameters:
- FAST_START_MTTR_TARGET
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINTS_TO_ALERT


good luck!

kind regards
yogeeraj

[Volker Borowski]

John, no excuse needed :-)
This forum is for discussion.
Don't worry.

Yogeeraj, since I currently do not have any errors nor any messages in the upgradelogs, I do not like to change something right now.
And again, I do not have a checkpoint problem, but just an anoying size-mismatch with the archivelogs.

I did more internet research and found someone with the same problem. I was able to contact this guy and he mailed me, that when he logged a TAR for this behavior, he has been told, that this is 10g normal behavior.

So currently I am a bit less concernd about this, but I still like to know, why I still have systems that write logs the old way with onlinelogsize=archivelogsize.

But I still like to have a reason/explaination for this and I think I will contact support about this.

Did anybody else noticed this behavior after the upgrade to 10.2 ? Esp. after you changed the "compatible" from 9.2.0 to 10.2.0. ?

Volker

[Yogeeraj_1]

hi volker,

i do agree with you that you must not make changes to something which is not causing you any trouble.

Concerning upgrade to 10.2, in fact we did the long jump from 8.1.7 to 10.2. Quite a lot of changes but fortunately we had little complex stuffs.

Two problems to be reported for the moment are:
1. When using data pump across the network (network_link), tables with long fields cause it to fail. Same if you have "sources" that are too long (i don't remember the exact size) you cannot use expdp...

2. Auto tuning of the SGA, this is the last thing that caused us problem and we are still looking into it....

kind regards
yogeeraj