Oracle Online redo log files (online redo log file)

Oracle Online redo log files (online redo log file
Almost all of the internal changes that occur in Oracle are recorded in the online redo log file, and Oracle uses these redo log groups to recover the database, so they are very important.
The main work for online redo log files is:
Back up your data,
1: Record all data changes
2: Provides a recovery mechanism
3: Must have been organized into groups
4: There are at least two sets of online redo log files

Each redo log is assigned to a group, Oralce writes one online redo log group at a time, and once the online redo logs in this group are filled, Oracle writes the conversion to the next online redo log group so that Oracle writes between groups is a looping operation.
Each online redo log file is assigned a unique serial number (sequence numbers). The serial number of the online redo log file does not recur unless you rebuild the database.
We can have multiple online recombination even log files in a group, which means that each online redo log group contains at least two online redo log files, each file in the group becomes a member, each member should be assigned on a different disk, in order to contain these group files missing, Oracle A group of online redo log files are identical to the member in the group, and the LGWR process writes information to all the online redo log file groups.
Each member in the group has the same size, and the Oracle data is first assigned a log sequence numbers when writing information to the Redo log file group, multiplexing: at least two member in a group, avoiding the redo log files Single point of failure, redo log files contains multiple member in a group and each group has a number.
When the database is created, the online redo log file is created, the database cannot be missing online redo log files, if all the member in a group are lost, the database is corrupted, so it is important to save and back up these files, there are two redo logs, the first: online redo log The second type: Archive logs.

When does LGWR write redo log file?
1: When a transaction commits
2: Write every three seconds
3: Write when redo log buffer becomes 1/3 full
4: When redo log buffer size operation 1M information to perform a write operation
5: Write before dbwn a checkpoints executes.
Dbwn the function of this process (write the information of redo log files back to datafiles)
Must write redo log first, and then write to the data file.

How the online Redo days file works:
Use the loop to understand the definition of log switch. (just switch different groups to write), log switch raises checkpoints to write the dirty data in memory back to the database file called a checkpoints
Forced log switchover (log switches) and checkpoints

V$log; This view is a view with an online redo log file.

1. Find the Redo log file and group of the database through V$logfile;
[SQL]
Sql> Col member format A50;
Sql> select Group#,type,member from V$logfile;

group# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO03. LOG
2 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO02. LOG
1 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO01. LOG

2, manually switch the log command
For example: The following example first queries group number 2 for the status of the group is current, the following executes the ALTER system switch logfile command after querying again Oracle is currently writing a group number of 3, indicating that the log SW Itch The log group switch operation, which is done manually by switching.
[SQL]
Sql> select Group#,thread#,sequence#,status from V$log;

group# thread# sequence# STATUS
---------- ---------- ---------- ----------------
1 1 442 INACTIVE
2 1 443 current
3 1 441 INACTIVE

sql> alter system switch logfile;

The system has changed.

Sql> select Group#,thread#,sequence#,status from V$log;

group# thread# sequence# STATUS
---------- ---------- ---------- ----------------
1 1 442 INACTIVE
2 1 443 ACTIVE
3 1 444 Current

3, control the behavior of checkpoints (online redo log file data information timed to write to the data file)

To reference to find this parameter: fast_start_mttr_target specifies the maximum value of fast recovery, the maximum number of times, the DBWN process for how long must be the redo log file information written to the data file, this parameter affects the performance of an important value, Because it can touch I/O Write

[SQL]
Sql> Show parameter fast_start_mttr_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Fast_start_mttr_target integer 0

This means that the DBWN process is required to write the information in the Redo log file to the data file for the length of time it needs to be modified by:
[SQL]
Sql> alter system Set Fast_start_mttr_target = $ scope = both;

The system has changed.

Sql> Show parameter fast_start_mttr_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Fast_start_mttr_target Integer 60


After the alter system set Fast_start_mttr_target =100 scope = both; Indicates that the DBWN process must write the information in the Redo log file to the data file within 60 seconds of the modification

4, manually write back the online redo log information to the data file
The following commands need to be executed manually:
[SQL]
Sql> alter system checkpoint;

The system has changed.

Sql>

For operations on groups and member see click Me

5, add the online redo log group can use the following command

The following operation adds the Redo log group number to 4

[SQL]
Sql> select Group#,type,member from V$logfile;

group# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO03. LOG
2 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO02. LOG
1 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO01. LOG

sql> ALTER DATABASE Add logfile Group 4 (' D:\APP\TOPWQP\ORADATA\ORCL\REDO04.LO
G ') size 52429312;

The database has changed.

Sql> select Group#,type,member from V$logfile;

group# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO03. LOG
2 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO02. LOG
1 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO01. LOG
4 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO04. LOG

Sql>

6. Add member command to a redo log group

[SQL]
sql> ALTER DATABASE ADD LOGFILE MEMBER ' D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG ' T
O GROUP 4;

The database has changed.

Sql> select Group#,type,member from V$logfile;

group# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO03. LOG
2 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO02. LOG
1 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO01. LOG
4 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO04. LOG
4 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG

As shown above: The Redo log file with group number 4 has two member, respectively:

This is the two group number 4 of two member the two log files are exactly the same size and content. You can put two member on a different disk.
D:\APP\TOPWQP\ORADATA\ORCL\REDO04. LOG
D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG
Shows the concept of group and member and shows the disk on different disks.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/86/76/wKioL1e_sJTAQmCmAAB1CpcvYPM012.png-wh_500x0-wm_3 -wmp_4-s_2133648213.png "title=" Group.png "alt=" Wkiol1e_sjtaqmcmaab1cpcvypm012.png-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/76/wKioL1e_sQSCKBruAACCgStE9Dc969.png-wh_500x0-wm_3 -wmp_4-s_3913192410.png "title=" Disk.png "alt=" Wkiol1e_sqsckbruaaccgste9dc969.png-wh_50 "/>

7. Delete Redo File member

Note The main points:
Ensure that there are at least two redo log file (at least two member) in a group before deletion

If you want to delete this (there is only one member in a group), it is deleted member

When the deletion is complete, the file is not deleted, but it is registered in the control file.
Redo file member does not exist, and if you want to remove it further, you can use the operating system command to delete it.
First query the following, then delete, and then query the comparison
The command is as follows: ALTER DATABASE DROP LOGFILE MEMBER ' D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG ';
[SQL]
Sql> select Group#,type,member from V$logfile;

group# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO03. LOG
2 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO02. LOG
1 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO01. LOG
4 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO04. LOG
4 ONLINE D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG

sql> ALTER DATABASE DROP LOGFILE MEMBER ' D:\APP\TOPWQP\ORADATA\ORCL\REDO05. LOG ';


The database has changed.

Sql> select Group#,type,member from V$logfile;

The following Linux tests:

Manage online log files:
Online log files work in groups
Database requires at least 2 groups of logs to work properly
The online log records all data block changes, used to do the instance recover
You can have multiple members under the same group
Between groups is to switch between running
Mirror relationship between members under the same group
Switchover occurs by default when a log member is full of redo
sequence# minimum group overrides on log switchover
The position and number of the member, as determined by the pointer in the control file

To view the working status of a log group:
SELECT * from V$log;

To view the physical information for a log:
SELECT * from V$logfile;

Manually switch logs:
alter system switch logfile;
Manually generate checkpoints:
Alter system checkpoint;

Monitor Log Switching frequency:
Select To_char (first_time, ' yyyymmddhh24 ') first_time,count (*) from V$log_history GROUP by To_char (First_time, ' Yyyymmddhh24 ') Order by 1;

Size of enlarged Members:
ALTER DATABASE Add logfile Group 4 '/u01/app/oracle/oradata/madrid/redo04.log ' size 100m;
ALTER DATABASE Add logfile Group 5 '/u01/app/oracle/oradata/madrid/redo05.log ' size 100m;

To delete a useless group:
ALTER DATABASE drop logfile Group 1;
ALTER DATABASE drop logfile Group 2;
ALTER DATABASE drop logfile Group 3;

To move a log file:
1. Database to mount
Shut immediate
Startup Mount
2. target file to exist
Mv/u01/app/oracle/oradata/madrid/redo04.log/home/oracle/redo04.log
3. Modify the pointer in the control file
Alter DATABASE rename file '/u01/app/oracle/oradata/madrid/redo04.log ' to '/home/oracle/redo04.log ';
4. Open the Database
ALTER DATABASE open;

Log file multiplexing: Multiple members are used under the same group, and only one member is available in each group, and the database works as expected.
ALTER DATABASE add LogFile Member
'/u01/app/oracle/oradata/madrid/redo04a.log ' to Group 4,
'/home/oracle/redo05a.log ' to group 5;

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1842849

Oracle Online redo log files (online redo log file)