parameters to change after redo log size increased

[Dave Chamberlin]

Hello,
I am increasing the size of my redo logs from 50M to 100M when I rebuild the database to increase blocksize from 4k to 8k. Currently in the initORA file log_checkpoint_timeout = 0, log_checkpoint_interval = 200000 and the log_buffer is 4M. Do I need to change the interval parameter (or any others) for the change in redo log size? On a related note, I want to make sure all pending changes are written to log files prior to exporting the original database. Will it be done if I do a log switch for each redo log file (I have 6)?

[Yogeeraj_1 · Accepted solution]

hi,

about log_checkpoint_interval:

say for example, log_checkpoint interval is set to 1000:

Oracle continually calculates the address of redo record that was written 1000 records (OS blocks) ago. In order to satisfy this parameter, the checkpoint position must advance at least as far as this redo record. Should the checkpoint position point to a redo record written earlier than this target position (written over 1000 records before the record at the end of the log), Oracle will write dirty buffers and advance the checkpoint until it points at a redo record written less than 1000 records ago. Should the checkpoint position point to a redo record newer than this target position (written less than 1000 records ago), Oracle
will do nothing to satisfy this target for it is already satisfied.

In most all cases you do not need to set such parameters -- it mostly takes care of itself these days. Parameters like fast_start_mttr_target are "better".

As for the export of the original database, will it not be preferrable that there are not transactions during that interval? Meaning that you may wish to:
a. shutdown the database
b. disable user connections (maybe stop the listener)
c. startup database
d. export the database

during that period.

You may also switch the log 7 times before doing the export, just in case your want to keep your database available for query only.

alter system switch logfile;

hope this helps!

regards
Yogeeraj

[Sanjay Kumar Suri]

Generally redo logs size is increased to imrpove the Redo log performance.

Init.ora parameters don't have any relation to the redo log file size.

log_buffer is geneerally sized as 128KB * the number of CPU's in the server or 512 KB whichever is greater.

Oracle 9i has a new parameter fast_start_mttr_target for tuning checkpoint activity. This parameter is used to specify a mean time (in seconds) to recover the instance following an instance failure.

So Oracle 8i parameters fast_start_io_target and log_checkpoint_interval are deprecated in favor of of Oracle 9i fast_start_mttr_target.

The momment you do clean/normal shutdown the system will call LGWR (to transfer all change from redo log buffer to redo log files) following by DBWn to write all the changed to data files. So there is no need to do log switching.

sks

[Eric Antunes]

Hi,

I agree with Yogeeraj: you must ensure there are no transactions in your database before making the export. So, you should shutdown the database and restart it in restricted mode:

SVRMGR>STARTUP OPEN EXCLUSIVE RESTRICT;

Best Regards,

Eric Antunes