Oracle: 关于 redo log

最新推荐文章于 2023-12-21 17:46:31 发布

sopost

最新推荐文章于 2023-12-21 17:46:31 发布

阅读量1.2k

点赞数

分类专栏： Oracle 文章标签： oracle database 数据库 file intervals initialization

Oracle 专栏收录该内容

197 篇文章 0 订阅

订阅专栏

Redo log

一、What Is the Redo Log?

阅读过后您就会知道答案。

How Oracle Database Writes to the Redo Log

1.在非归档模式下

一个写满日志的日志文件要被reuse，必须等待这个写满日志的日志文件的重做日志被写入磁盘的数据文件中

2.在归档模式下

一个写满日志的日志文件要被reuse，必须等待这个写满日志的日志文件的重做日志被写入磁盘的数据文件中，并且这个写满日志的日志文件必须被归档

3. Whenever atransaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction（当事务提交时候，oracle将分配一个系统改变号，即SCN，目的是标示每一个提交的事务。）

Active (Current) and Inactive Redo Log Files

日志状态：

Active：这个状态的日志文件，实例恢复的时候使用要求使用

Current：当前日志文件，就是LGWR进程写的日志文件

Inacitve：这个状态的日志文件，实例恢复的时候不需要使用

Log Switches and Log Sequence Numbers

日志切换的概念：

日志切换处于这个点上：数据库停止写一个日志文件，而开始写另外一个日志文件，称日志切换。

一般情况下，当前日志文件满了之后，就开始写下一个日志文件；但是你也可以手工进行日志切换，强迫日志切换，即当前日志文件还没有满的时候强迫进行日志切换。

手工切换日志组：Alter system switch logfile

（Oracle Database assigns each redo log file a new log sequence number every time a

log switch occurs and LGWR begins writing to it. When the database archives redo log

files, the archived log retains its log sequence number. A redo log file that is cycled

back for use is given the next available log sequence number.当进行日志切换时候，数据库分配一个新的日志序列号给当前日志文件，LGWR开始写入redo logfile里面，归档日志文件保留了它的序列号）

Planning the Redo Log

1.Multiplexing Redo Log Files

说明如

In Figure 6–2, A_LOG1 and B_LOG1 are both members of Group 1, A_LOG2 and B_LOG2 are both members of Group 2, and so forth. Each member in a group must be exactly the same size. Each member of a log file group is concurrently active—that is, concurrently written to by LGWR—as indicated by the identical log sequence numbers assigned by LGWR.

In Figure 6–2, first LGWR writes concurrently to both A_LOG1 and B_LOG1. Then it writes concurrently to both A_LOG2 and B_LOG2, and so on. LGWR never writes

concurrently to members of different groups (for example, to A_LOG1 and B_LOG2).

注意：如果日志文件丢失，将对数据恢复带来的是灾难性的；多工日志文件将提高数据库的I/O，有可能将影响数据库的整体性能。

清除归档日志：

Alter database clear unarchived log

如果你不能归档日志文件，在你删除日志文件之前，清除归档日志文件

Legal and Illegal Configurations

The only requirement for an instance redo log is that it have at least two groups.

正确配置日志文件(图不能显示，参考oracle官方文档)

错误配置日志文件（图不能显示，参考oracle官方文档）

说明：日志文件组最少要有两个，成员最少要有一个。

Placing Redo Log Members on Different Disks

1.确保单点故障导致的数据库挂起。

2.确保LGWR和ARCn不竞争系统资源，列如：有两组日志文件组，分别有两个成员，分别放在四个磁盘上，把归档日志文件放在第五个磁盘上。有效地避免了LGWR与ARCn的竞争。同理，把数据文件放在第六个磁盘上。

Setting the Size of Redo Log Members

同一个日志组的成员的大小必须相同，不同日志组的的成员大小可以不相同，但没有多大意义。

（If checkpoints are not set to occur between log switches,

make all groups the same size to guarantee that checkpoints occur at regular intervals）

Choosing the Number of Redo Log Files

两个参数文件控制日志文件数量

1.MAXLOGFILES

控制日志组的数量

2.MAXLOGMEMBERS

控制日志组成员的数量

3.如何得到这两个参数的值：

Alter database backup controlfile to trace

D:/oracle/product/admin/cisdb/udump/ cisdb_ora_3632.trc

Controlling Archive Lag

在DataGuard环境中，为了减少故障时数据损失，我们可以设置ARCHIVE_LAG_TARGET参数，强制进行日志切换。

The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).

ARCHIVE_LAG_TARGET = 1800

该参数控制日志切换的时间间隔，range（1800秒-7200秒）之间

典型的手工切换日志的时间间隔1800.

测试：

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col name for a60
SQL> select name,COMPLETION_TIME from v$archived_log where name is not null;

NAME COMPLETION_TIME
------------------------------------------------------------ -------------------
/data2/oradata/STAT/archive/1_5441_593258512.dbf 2006-11-20 10:49:57
/data2/oradata/STAT/archive/1_5442_593258512.dbf 2006-11-20 15:49:50

该日志切换时间间隔5小时，估计是数据库很空闲所致。

注意：在RAC环境该值在所有实例必须是相同的。

Creating Redo Log Groups and Members

两种方式建立日志组

1.ALTER DATABASE

ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

2. ALTER DATABASE

ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')

SIZE 500K;

两种方式建立日志组成员members

1.ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

2.ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'

TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Relocating and Renaming Redo Log Members（迁移和重命名日志成员）

假设有这样一个场景：

■ The log files are located on two disks: diska and diskb.

■ The redo log is duplexed: one group consists of the members

/diska/logs/log1a.rdo and /diskb/logs/log1b.rdo, and the second

group consists of the members /diska/logs/log2a.rdo and

/diskb/logs/log2b.rdo.

■ The redo log files located on diska must be relocated to diskc. The new

filenames will reflect the new location: /diskc/logs/log1c.rdo and

/diskc/logs/log2c.rdo.

下面是操作过程：

1. Shut down the database.

SHUTDOWN

2. 在unix环境下，

mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo

mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo

3.启动数据库到mount状态

CONNECT / as SYSDBA

STARTUP MOUNT

4.在oracle界面，即SQL>界面下重命名日志文件：

ALTER DATABASE

RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'

TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

5.打开数据库，就完成了数据库日志文件的重命名和迁移。

ALTER DATABASE OPEN;

Dropping Redo Log Groups and Members

Dropping Log Groups

ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Redo Log Members

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

（注意：To drop a member of an active group, you must first force a log switch.）

Forcing Log Switches

Oracle是这样定义日志切换的：

A log switch occurs when LGWR stops writing to one redo log group and starts

writing to another. By default, a log switch occurs automatically when the current redo log file group fills.

ALTER SYSTEM SWITCH LOGFILE;

Verifying Blocks in Redo Log Files

关于这个参数：DB_BLOCK_CHECKSUM

这个参数一般设置值为true

Clearing a Redo Log File

Oracle是这样描述它的用途的：

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

ALTER DATABASE CLEAR LOGFILE GROUP 3;

这样做有两个好处：

■ If there are only two log groups

■ The corrupt redo log file belongs to the current group

另外：

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Viewing Redo Log Information

View Description

V$LOG Displays the redo log file information from the control file

V$LOGFILE Identifies redo log groups and members and member status

V$LOG_HISTORY Contains log history information