Maintaining Online Redo Log Files

Posted: 三月 9, 2011 in oracle
标签:

 

Using Online Redo Log Files

Online Redo log files have the following

characteristics:

Record all changes made to data

Provide a recovery mechanism

Can be organized into groups

At least two groups required

 

Every transaction is written synchronously to theRedo Log Buffer, then gets

flushed to the online redo log files in order toprovide a recovery mechanism in case of media failure. (Withexceptions such as direct load inserts in objects with theNOLOGGING clause enabled.) This includes transactions that have notyet been committed, undo segment information, and schema and objectmanagement statements. Online redo log files are used in asituation such as an instance failure to recover committed datathat has not been written to the data files. The online redo logfiles are used only for recovery.

 

 

Structure of Online Redo Log Files

 

 

 

Online Redo Log File Groups

• A set of identical copies of online redo logfiles is called an online redo log file group.

• The LGWR background process concurrently writesthe same information to all online redo log files in a group.

• The Oracle server needs a minimum of two onlineredo log file groups for the normal operation of a database.

同一组中的成员的内容一样,不同组不一样。成员是为了多路复用。

Online Redo Log File Members

• Each online redo log file in a group is called amember.

• Each member in a group has identical log sequencenumbers and are of the same size.The log sequence number(LSN) isassigned each time that the Oracle server writes to a loggroup to uniquely identify each online redo log file. The currentlog sequence number is stored in the control file and in the headerof all data files.

 

Creating Initial Online Redo Log Files

The initial set of online redo log file groups andmembers are created during the database creation.

The following parameters limit the number of onlineredo log files:

• The MAXLOGFILES parameter in the CREATE DATABASEcommand specifies the absolute maximum of online redo log filegroups.

• The maximum and default value for MAXLOGFILES isdependent on your operating system.

• The MAXLOGMEMBERS parameter used in the CREATEDATABASE command

determines the maximum number of members per group.The maximum and default value for MAXLOGMEMBERS is dependent onyour operating system.

 

How Online Redo Log Files Work

Online Redo log files are used in a cyclicfashion.

When a online redo log file is full, LGWR willmove

to the next log group.

Called a log switch

Checkpoint operation also occurs

Information written to the controlfile

 

 

 

 

LGWR writes under the following situations:

• When a transaction commits

•每3秒也发生一次

• When the Redo Log Buffer becomes one-thirdfull

• When there is more than a megabyte of changedrecords in the Redo Log Buffer

• Before the DBWn writes modified blocks in theDatabase Buffer Cache to the data files(也就是硬盘中)

 

 

Log Switches

LGWR writes to the online redo log filessequentially. When the current online redo log file group isfilled, LGWR begins writing to the next group. This is called a logswitch. When the last available online redo log file is filled,LGWR returns to the first online redo log file group and startswriting again.

 

 

Checkpoints

During a checkpoint:

• DBWn writes a number of dirty database buffers,which are covered by the log that is being checkpointed, to thedata files.

• The checkpoint background process CKPT updatesthe control file to reflect that it has completed a checkpointsuccessfully. If the checkpoint is caused by a log switch, CKPTalso updates the headers of the data files.

Checkpoints can occur for all data files in thedatabase or only for specific data files.

A checkpoint occurs, for example, in the followingsituations:

• At every log switch

• When an instance has been shut down with thenormal, transactional, or immediate option

• When forced by setting the FAST_START_MTTR_TARGETinitialization parameter

• When manually requested by the databaseadministrator

• When the ALTER TABLESPACE [OFFLINE NORMAL|READONLY|BEGIN

BACKUP]command causes checkpointing on specificdata files

Information about each checkpoint is recorded inthe alert_SID.log file if the

LOG_CHECKPOINTS_TO_ALERT initialization parameteris set to TRUE. The default value of FALSE for this parameter doesnot log checkpoints.

 

 

 

Forcing Log Switches and Checkpoints

Forcing a log switch:

ALTER SYSTEM SWITCH LOGFILE;

Checkpoints can be forced by:

Setting FAST_START_MTTR_TARGET parameter

FAST_START_MTTR_TARGET = 600

主要为了增加dbwn的次数,避免有时lgwr完毕之后,dbwn未发生而导致dbhung

ALTER SYSTEM CHECKPOINT command

ALTER SYSTEM CHECKPOINT;

 

 

FAST_START_MTTR_TARGET parameter replaces thedeprecated parameters:

•FAST_START_IO_TARGET

•LOG_CHECKPOINT_TIMEOUT

These deprecated parameters must not be used if theparameter

FAST_START_MTTR_TARGET is used.

 

 

Adding Online Redo Log File Groups

 

ALTER DATABASE ADD LOGFILE GROUP 3

(‘$HOME/ORADATA/u01/log3a.rdo’,

‘$HOME/ORADATA/u02/log3b.rdo’)

SIZE 1M;

 

 

ALTER DATABASE [database]

ADD LOGFILE [GROUP integer] filespec

[, [GROUP integer] filespec]…]

查看当前的组数:

Sql>desc v$logfile;也可以看到组号以及状态,然后用select * fromv$logfile;

Sql>desc v$log;然后select group#,thread#,sequence#from v$log;查看当前的组个数,序号等。

 

 

Adding Online Redo Log File Members

ALTER DATABASE ADD LOGFILE MEMBER

‘$HOME/ORADATA/u04/log1c.rdo’ TO GROUP1,

‘$HOME/ORADATA/u04/log2c.rdo’ TO GROUP2,

‘$HOME/ORADATA/u04/log3c.rdo’ TO GROUP3;

 

ALTER DATABASE [database]

ADD LOGFILE MEMBER

[ ‘filename’ [REUSE]

[, ‘filename’ [REUSE]]…

TO {GROUP integer

|(‘filename'[, ‘filename’]…)

}

]…

 

If the file already exists, it must have the samesize, and you must specify the REUSE option.

 

 

Dropping Online Redo

Log File Groups

 

ALTER DATABASE DROP LOGFILE GROUP 3;

 

ALTER DATABASE [database]

DROP LOGFILE {GROUP integer|(‘filename'[,

‘filename’]…)}

[,{GROUP integer|(‘filename'[,

‘filename’]…)}]…

 

Restrictions

• An instance requires at least two groups ofonline redo log files.

• An active or current group cannot be dropped.

• When an online redo log file group is dropped,the operating system files are not

deleted.

 

 

Dropping Online Redo

Log File Members

 

ALTER DATABASE DROP LOGFILE MEMBER

‘$HOME/ORADATA/u04/log3c.rdo’;

 

ALTER DATABASE [database]

DROP LOGFILE MEMBER ‘filename'[, ‘filename’]…

这个删除只是修改了控制文件的相关内容。

Restrictions

• If the member you want to drop is the last validmember of the group, you cannot drop that member.这时可以直接删除组。

• If the group is current, you must force a logfile switch before you can drop the

member.

• If the database is running in ARCHIVELOG mode andthe log file group to which the member belongs is not archived,then the member cannot be dropped.

• When an online redo log file member is dropped,the operating system file is not

deleted if you are not using the OMFfeature.可以用操作系统的命令再删除该文件。

 

Member的三种状态:

Valid,正常

Invalid,不可访问

Stale,可访问,db怀疑该文件不正确或已经损坏。

 

 

Relocating or Renaming

Online Redo LogFiles记得做之前要对db进行全备。

Relocate or rename online redo log files in oneof the

two following ways:

1 ALTER DATABASE RENAME FILEcommand

Shut down the database.

Copy the online redo log files to the newlocation.

Place the database in MOUNTmode.

Execute the command.

ALTER DATABASE RENAME FILE

‘$HOME/ORADATA/u01/log2a.rdo’

TO ‘$HOME/ORADATA/u02/log1c.rdo’;

Open database for normal operation.

Alter databade open;

2Add new members and drop old members.

 

 

 

Clearing Online Redo LogFiles(如果文件遭破坏时可进行修复)

ALTER DATABASE CLEAR LOGFILE commandcan

be used to reinitialize an online redo logfile.

ALTER DATABASE CLEAR LOGFILE GROUP 2;

Use the UNARCHIVED keyword toavoid archiving the

corrupted online redo log file.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP2;

 

 

Online Redo Log File Configuration

怎么规划需要根据具体情况。重要是将同一组的不同成员放在不同的磁盘上。组中只要有一个成员活着,该实例就是可用的。要避免lgwr和arcn向同一个磁盘进行写入和读取,提高性能。

For example, if messages in the LGWR trace file orin the alert file

indicate that LGWR frequently must wait for a groupbecause a checkpoint has not

completed or a group has not been archived, youshould add groups.

 

The minimum size of an online redo log file is 50KB, and the maximum size is specific to the operating system.

The following situations might influence theconfiguration of the online redo log files:

• Number of log switches and checkpoints

• Number and amount of redo entries

• Amount of space on the storage medium; forexample, on a tape if archiving is

enabled

 

 

 

Managing Online Redo Log Files with OMF

Define theDB_CREATE_ONLINE_LOG_DEST_n

parameter:

DB_CREATE_ONLINE_LOG_DEST_1

DB_CREATE_ONLINE_LOG_DEST_2

A group can be added with no filespecification:

ALTER DATABASE ADD LOGFILE;

Dropping a group:

ALTER DATABASE DROP LOGFILE GROUP 3;

 

 

 

Obtaining Group and Member Information

Information about a group and its members canbe

obtained by querying the followingviews:

V$LOG

V$LOGFILE

 

 

The following items are the most common values forthe STATUS column:

•UNUSED: Indicates that the online redo log filegroup has never been written to. This is the state of an onlineredo log file that was just added.

•CURRENT: Indicates the current online redo logfile group. This implies that the online redo log file group isactive.

•ACTIVE: Indicates that the online redo log filegroup is active but is not the current online redo log file group.It is needed for crash recovery. It may be in use for blockrecovery. It may or may not be archived.

•CLEARING: Indicates that the log is beingre-created as an empty log after an ALTER DATABASE CLEAR LOGFILEcommand. After the log is cleared, the status changes toUNUSED.

•CLEARING_CURRENT: Indicates that the current logfile is being cleared of a closed thread. The log can stay in thisstatus if there is some failure in the switch, such as aninput/output (I/O) error writing the new log header.

•INACTIVE: Indicates that the online redo log filegroup is no longer needed for

instance recovery. It may or may not bearchived.

 

 

The value of the STATUS column could be one of thefollowing:

•INVALID: Indicates that the file isinaccessible

•STALE: Indicates that contents of the file areincomplete

•DELETED: Indicates that the file is no longerused

• Blank indicates that the file is in use

 

 

 

 

Archived Redo Log Files

Filled online redo log files can bearchived.

There are two advantages in running thedatabase

in ARCHIVELOG mode and archivingonline redo log

files:

Recovery: A database backup together withonline and

archived redo log files can guarantee recoveryof all

committed transactions.

Backup: This can be performed while thedatabase is

open.

By default, the database is created in

NOARCHIVELOG mode.

 

 

NOARCHIVELOG Mode

In NOARCHIVELOG mode, the online redo log files areoverwritten each time an online redo log file is filled, and logswitches occur. LGWR does not overwrite an online redo log filegroup until the checkpoint for that group is completed.

ARCHIVELOG Mode

If the database is configured to run in ARCHIVELOGmode, inactive groups of filled online redo log files must bearchived. Because all changes made to the database are recorded inthe online redo log files, the database administrator can use thephysical backup and the archived online redo log files to recoverthe database without losing any committed data.

There are two ways in which online redo log filescan be archived:

• Manually

• Automatically (recommended method)

The LOG_ARCHIVE_START initialization parameterindicates whether archiving should be automatic or manual when theinstance starts up.

•TRUE: TRUE indicates that archiving is automatic.ARCn initiates archiving of the filled log group at every logswitch.

•FALSE: The default value, FALSE indicates that theDBA archives filled online redo log files manually. The DBA mustmanually execute a command each time you want to archive an onlineredo log file. All or specific online redo log files can bearchived manually.

 

查看db处于社么模式,用:select archiver from v$instance;或者select log_modefrom v$database

 

Archived Redo Log Files

Accomplished automatically by ARCn

Accomplished manually through SQLstatements

When successfully archived:

An entry in the control file is made

Records: archive log name, log sequencenumber, and

high and low system change number (SCN)

Filled online redo log files cannot be reuseduntil:

A checkpoint has taken place

File has been archived by ARCn

Can be multiplexed

Maintained by the DBA

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s