The original three redolog files in the database are configured. Due to frequent switching, you want to increase the size.

Assume that there are three log groups, each of which has one member. The size of each member is 1 MB. Now we want to change the size of all three log groups to 10 MB.

1. create two new log groups
Alter database add logfile Group 4 ('d:/Oracle/oradata/oradb/redo04_1.log ') size 1024 K;
Alter database add logfile group 5 ('d:/Oracle/oradata/oradb/redo05_1.log ') size 1024 K;

2. Switch the current log to a new log group.
Alter system switch logfile;
Alter system switch logfile;

3. Delete the old log Group
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;

4. delete files in the original log group 1, 2, and 3 in the operating system

5. Rebuild log groups 1, 2, and 3
Alter database add logfile group 1 ('d:/Oracle/oradata/oradb/redo01_1.log ') Size 10 m;
Alter database add logfile group 2 ('d:/Oracle/oradata/oradb/redo02_1.log ') Size 10 m;
Alter database add logfile group 3 ('d:/Oracle/oradata/oradb/redo03_1.log ') Size 10 m;

6. Switch the log Group
Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

7. Delete the log groups for intermediate transition. 4 and 5
Alter database drop logfile group 4;
Alter database drop logfile group 5;

8. delete files in log group 4 and 5 under the Operating System

9. Back up the latest Control File
SQL>; Connect internal
SQL>; Alter Database Backup controlfile to trace resetlogs

(Note: You can add several more files to each group for security considerations)

 

Reference:Other database parameters related to redo logfile

1. log_buffer

Log_buffer is a part of Oracle SGA. All data blocks modified by the DML command are first placed in log_buffer. If it is full or
When check_point is reached, the lgwr background process is written to redo logfile. It cannot be too large.
A lot of changed data will be lost in the event of external attacks. It is better not to exceed 128 K or K * Number of CPUs.

We can use the following SQL statement to check log_buffer usage:

Svrmgrl>; select rbar. Name, rbar. Value, re. Name, re. Value, (rbar. Value * 100)/Re. Value | '%' "radio"
From v $ sysstat rbar, V $ sysstat re
Where rbar. Name = 'redo Buffer Allocation retries'
And re. Name = 'redo entries ';

This ratio is less than 1%, otherwise the size of log_buffer is increased.

2. log_checkpoint_interval

Log_checkpoint_interval after oracle8.1 indicates the number of operating system data blocks between two checkpoints.

At checkpoint, Oracle writes the modified data blocks in the memory to physical files using dbwr and writes them to logs and control files using lgwr.

Generally, the data block of a UNIX operating system is 512 bytes.

For performance optimization, log_checkpoint_interval = redo Logfile size Bytes/512 bytes


3. log_checkpoint_timeout

Log_checkpoint_timeout after oracle8.1 indicates the time in seconds between two checkpoints.
Oracle recommends that you do not use this parameter for control, because the transaction size is not distributed by the same amount of time.
Log_checkpoint_timeout = 0
Log_checkpoint_timeout = 900