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

Source: Internet
Author: User

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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.