13_oracle_admin_ online redo log files and checkpoints

all files in the database can be lost, only Onlineredo Log files and Archive redolog files cannot be lost or the database will not be recoverable.

One, what is the online redo log file

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/5B/wKiom1Qkd-fTLNQeAAHv5YmPjLE530.jpg "title=" 1.png " alt= "Wkiom1qkd-ftlnqeaahv5ympjle530.jpg"/>

The online redo log file is primarily used for database backup and recovery, which records all changes to the data and provides a recovery mechanism for the data ( when Oracle operates on the data, it writes the online redo log first, which is organized into groups with at least two sets of logs.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/5D/wKioL1QkeB3AOfDzAAGFO47_s70057.jpg "title=" 2.PNG " alt= "Wkiol1qkeb3aofdzaagfo47_s70057.jpg"/>

Oracle writes the first group, when the first group is full, then the second group, and so on, if the third group is also full, re-write from the first group, each group to ensure that there are two online redo log files, and belong to different disks, to ensure backup and reliability.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4A/5C/wKiom1QkeAayahnBAALZ1tiVbzE003.jpg "title=" 3.png " alt= "Wkiom1qkeaayahnbaalz1tivbze003.jpg"/>

all copies of the online redo log are identical. LGWR This background process writes data to multiple copies of the online redo log at the same time, andthe Oracle server requires at least two sets of online redo logs.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4A/5E/wKioL1QkeDmR9II5AAKT7P_TMWU972.jpg "title=" 4.png " alt= "Wkiol1qkedmr9ii5aakt7p_tmwu972.jpg"/>

The members in the online redo log group have the same size and are assigned the same serial number, each time the log is written with a serial number, and the specific log can be determined based on the serial number.

Ii. multiplexing of online redo logs

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/5E/wKioL1QkeEmxKIvYAAO5tcN_Tzk147.jpg "title=" 5.png " alt= "Wkiol1qkeemxkivyaao5tcn_tzk147.jpg"/>

to prevent the online redo log file from being corrupted and the database unrecoverable, you can copy multiple online redo logs and place them on separate disks, and it is recommended that you store multiple backups on the same disk, even if you cannot put them on multiple disks, to avoid I/O failure. The multiplexing of online redo logs can affect performance, but the priority of the database is reliability.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4A/5E/wKioL1QkeFrBjY5TAAGfKL36Qqc284.jpg "title=" 6.png " alt= "Wkiol1qkefrbjy5taagfkl36qqc284.jpg"/>

Members of a group should be evenly distributed on different disks

Iii. how the online redo log works

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4A/5E/wKioL1QkeGmzQJ8hAAKSUMnpQCc002.jpg "title=" 7.png " alt= "Wkiol1qkegmzqj8haaksumnpqcc002.jpg"/>

The write of the online redo log file is a cyclic process, and when the first group is full, it will switch to the second group to continue writing, and the process of this switchover becomes a log switch. The log switchover raises a checkpoint (the checkpoint is the moment the dirty data is written to disk from the cache, and the process is dbwn), but it is not. Also, log switching writes information to Controlfile( which is done by the CKPT process).

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4A/5C/wKiom1QkeFKzqKOzAAKa4H9Jwkc016.jpg "title=" 8.png " alt= "Wkiom1qkefkzqkozaaka4h9jwkc016.jpg"/>

The time to write online redo logs includes

1. When a thing commits (commit does not write the data to the file, but writes the online redo log);

2. Write once every 3 seconds;

3. when Redo logbuffer( An important component of the SGA) reaches One-third full;

4.Redo Log Buffer with more than 1MB of data change information;

5. before dbwn This process writes the changed blocks from the cache to the data file (before the Checkpoint checkpoint occurs).

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/5E/wKioL1QkeIXzWwa9AAMOOSVUHbM061.jpg "title=" 9.png " alt= "Wkiol1qkeixzwwa9aamoosvuhbm061.jpg"/>

Four, Log Switch and the Checkpoint Switch

1. Log Switches

usually LS occurs when a set of Redo Log File is full, but you can also manually control the switchover.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/5C/wKiom1QkeG3BFkl_AAHCINyMudY206.jpg "title=" 10.png "alt=" Wkiom1qkeg3bfkl_aahcinymudy206.jpg "/>

[[Email protected]~]$ sqlplus/nolog

Sql*plus:release 11.2.0.1.0 Production on Fri 18:13:17 2014Copyright (c) 1982, Oracle. All rights reserved.

Sql> Conn/as SYSDBA

Connected.

sql> desc V$log;

 Name                                       Null?    Type ---------------------------------------- ------- --------------------- GROUP#                                               number thread#                                              NUMBER SEQUENCE#                                           NUMBER BYTES                                                NUMBER BLOCKSIZE                                            number members                                              number archived                                            VARCHAR2 (3)  STATUS                                               VARCHAR2 ( FIRST_CHANGE#        )                                NUMBER FIRST_TIME                           &nBsp;               date next_ change#                                         NUMBER NEXT_TIME                                            date

Sql> Select group#, thread#, sequence#,status from V$log;

group# thread# sequence# STATUS----------------------------------------------------------------------1 1 current--This set of redo log file is being written to 2 1 inactive--INACTIVE that the online redo log can be written to, or the data in it can be archived 3 1 INACTIVE

sql> alter system switch logfile;

--Log switching System altered.

Sql> Select group#, thread#, sequence#,status from V$log;

group# thread# sequence# STATUS----------------------------------------------1 1 ACTIVE 2 1 current--Switch to the second group, the serial number also becomes 3 1 INACTIVE

2. Check Point switching

when the online redo log occurs Logswitch, will cause checkpoint.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4A/5E/wKioL1QkeJ_BxcItAAFchgVlATY390.jpg "title=" 11.png "alt=" Wkiol1qkej_bxcitaafchgvlaty390.jpg "/>

The checkpoint switch can also be enforced by specifying the value of the Fast_start_mttr_target can be achieved:

This value is expressed every few seconds, The DBWN process writes the data file one time , reducing this value to allow for more complete preservation, but will affect performance accordingly.

Sql> Show Parameter Fast

NAME TYPE VALUE------------------------------------------------------------------- -fast_start_io_target integer 0fast_start_mttr_target integer 260--The current checkpoint interval is 260 seconds FAs T_start_parallel_rollback string Low

Sql> alter system setfast_start_mttr_target=600 Scope=both;

System altered.

Sql> Show Parameter Fast

NAME TYPE VALUE-------------------------------------------------------------------F Ast_start_io_target integer 0fast_start_mttr_target integer 600--checkpoint interval modified to 600 seconds, restart data The library will take effect fast_start_parallel_rollback string low

You can also trigger checkpoints manually, in addition to the interval between checkpoints:

Sql> alter system checkpoint;

--A checkpoint is raised, forcing Dbwn to write data to the FileSystem altered.

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1558312

13_oracle_admin_ online redo log files and checkpoints