更改重做日志（redolog）檔案的大小

更改重做日志（redolog）檔案的大小

從原來每個100M更改成1M

SQL> show user;

USER is "SYS"

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 CURRENT

2 INACTIVE

3 INACTIVE

SQL> alter database add logfile group 4 ('D:\oracle\oradata\PRACTICE\REDO04.LOG'

) size 100M;

Database altered.

SQL> alter database add logfile group 5 ('D:\oracle\oradata\PRACTICE\REDO05.LOG'

SQL> alter system switch logfile;

System altered.

1 INACTIVE

4 INACTIVE

5 CURRENT

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 1 ('D:\oracle\oradata\PRACTICE\REDO01.LOG'

) size 1M;

alter database add logfile group 1 ('D:\oracle\oradata\PRACTICE\REDO01.LOG') siz

e 1M

*

ERROR at line 1:

ORA-00301: error in adding log file 'D:\oracle\oradata\PRACTICE\REDO01.LOG' -

file cannot be created

ORA-27038: skgfrcre: file exists

OSD-04010: <create> option specified, file already exists

注意：每一步删除drop操作，都需要手工删除os中的實體檔案。如上錯誤是因為忘了手工删除原來的REDO01.LOG

SQL> alter database add logfile group 2 ('D:\oracle\oradata\PRACTICE\REDO02.LOG'

SQL> alter database add logfile group 3 ('D:\oracle\oradata\PRACTICE\REDO03.LOG'

1 UNUSED

2 UNUSED

3 UNUSED

2 CURRENT

5 INACTIVE

3 CURRENT

SQL> alter database drop logfile group 4;

SQL> alter database drop logfile group 5;

SQL> select group#,status,bytes from v$log;

GROUP# STATUS BYTES

---------- ---------------- ----------

1 INACTIVE 1048576

2 INACTIVE 1048576

3 CURRENT 1048576

===========================參考================================

假設現有三個日志組，每個組内有一個成員，每個成員的大小為1MB，現在想把此三個日志組的成員大小都改為10MB

1、建立2個新的日志組

alter database add logfile group 4 ('D:\ORACLE\ORADATA\ORADB\REDO04_1.LOG') size 1024k;

alter database add logfile group 5 ('D:\ORACLE\ORADATA\ORADB\REDO05_1.LOG') size 1024k;

2、切換目前日志到新的日志組

alter system switch logfile;

3、删除舊的日志組

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

4、作業系統下删除原日志組1、2、3中的檔案

5、重建日志組1、2、3

alter database add logfile group 1 ('D:\ORACLE\ORADATA\ORADB\REDO01_1.LOG') size 10M;

alter database add logfile group 2 ('D:\ORACLE\ORADATA\ORADB\REDO02_1.LOG') size 10M;

alter database add logfile group 3 ('D:\ORACLE\ORADATA\ORADB\REDO03_1.LOG') size 10M;

6、切換日志組

7、删除中間過渡用的日志組4、5

alter database drop logfile group 4;

alter database drop logfile group 5;

8、到作業系統下删除原日志組4、5中的檔案

9、備份目前的最新的控制檔案

SQL>; connect internal

SQL>; alter database backup controlfile to trace resetlogs

(注意：如果為安全考慮每個group可以多加幾個檔案）

==============================

跟redo logfile有關的其它資料庫參數

1、log_buffer

log_buffer是ORACLE SGA的一部分, 所有DML指令修改的資料塊先放在log_buffer裡, 如果滿了或者到了check_point時候通過lgwr背景程序寫到redo logfile裡去。它不能設得太大，這樣在意外發生時會丢失很多改變過的資料。它最好不要大于512K或者128K*CPU個數。

我們可以用下面的SQL語句檢測log_buffer使用情況:

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';

這個比率小于1%才好，否則增加log_buffer的大小

2、log_checkpoint_interval

Oracle8.1 版本後log_checkpoint_interval指的是兩次checkpoint之間作業系統資料塊的個數。

checkpoint時Oracle把記憶體裡修改過的資料塊用DBWR寫到實體檔案，用LGWR寫到日志和控制檔案。

一般UNIX作業系統的資料塊為 512 bytes。

從性能優化來說 log_checkpoint_interval = redo logfile size bytes / 512 bytes

3、log_checkpoint_timeout

Oracle8.1 版本後log_checkpoint_timeout指的是兩次checkpoint之間時間秒數。

Oracle建議不用這個參數來控制，因為事務(transaction)大小不是按時間等量分布的。

log_checkpoint_timeout = 0

log_checkpoint_timeout = 900

========================================

How Redo Log Files Work

The Oracle server sequentially records all changes made to the database in the Redo Log Buffer. The redo entries are written from the Redo Log Buffer to one of the online redo log file groups called the current online redo log file group by the LGWR process. LGWR writes under the following situations:

When a transaction commits

When the Redo Log Buffer becomes one-third full

When there is more than a megabyte of changed records in the Redo Log Buffer

Before the DBWn writes modified blocks in the Database Buffer Cache to the datafiles

Redo log files are used in a cyclic fashion. Each redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

Log switches:

LGWR writes to the online redo log files sequentially. When the current online redo log file group is filled, LGWR begins writing to the next group. This is called a log switch.

When the last available online redo log file is filled, LGWR returns to the first online redo log file group and starts writing again.

Checkpoints:

During a checkpoint:

DBWn writes a number of dirty database buffers, that are covered by the log that is being checkpointed, to the datafiles. The number of buffers that DBWn writes is determined by the FAST_START_MTTR_TARGET parameter, if specified. The default is zero.

Note: The FAST_START_MTTR_TARGET parameter is covered in detail in the Oracle9i Database Administration Fundamentals II course.

The checkpoint background process CKPT updates the control file to reflect that it has completed a checkpoint successfully. If the checkpoint is caused by a log switch, CKPT also updates the headers of the datafiles.

Checkpoints can occur for all datafiles in the database or only for specific datafiles.

A checkpoint occurs, for example, in the following situations:

At every log switch

When an instance has been shut down with the normal, transactional, or immediate option

When forced by setting the initialization parameter FAST_START_MTTR_TARGET

When manually requested by the database administrator

When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP]command causes checkpointing on specific datafiles

Information about each checkpoint is recorded in the alert_SID.log file if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE. The default value of FALSE for this parameter does not log checkpoints.

Redo Log Buffer

redo log buffer 可以循環使用，存放資料庫改變牽涉的資訊，其内部放的是redo entry。

redo entry存放的是INSERT, UPDATE, DELETE, CREATE, ALTER, DROP 等操作對資料庫的改變資訊,這些資訊在recovery的時候是很重要。這些redo entry 由oracle server程序從使用者記憶體放到redo buffer。

LGWR(Log writer)

LGWR(Log writer)是oracle啟動時候必須啟動的程序，它負責把redo log buffer中的redo entry寫到redo log檔案中。

在如下情況下寫檔案：

- redo log buffer 三分之一滿了

- 達到某時間點

- 在 DBWR w把修改的資料塊寫到資料檔案之前寫redo log file

- 事務 commit的時候

- 在資料庫做歸檔redo log 檔案的時候

Redo Log file

redo log就是存放redo log 資訊的檔案了，至少有兩個redo log 組，oracle循環使用之。當然推薦又更多的redo log 組。既然是循環使用redo log，必然會有一個log switch的過程。

log switch發生在：

- log file 已經寫滿了

- 使用了ALTER SYSTEM SWITCH LOGFILE 指令

在log switch 的時候自動會做checkpoint。

隻要redo log組裡面有一個redo log file可以用，就能進行log switch。當然如果redo log file壞了，LGWR肯定會記錄到 trace和alert檔案.

關于redo log的設定

一般至少兩組redo log 檔案，

每組中的redo log檔案最好在不同的磁盤上，防止一起損壞。

每組中的redo log檔案必須大小一緻,它們是同時修改的。

不同組的redo log檔案大小不一定一緻。

每組的redo log檔案數目必須一緻。

修改redo log的位置

- 看看目前的redo log檔案是不是正在使用，如果在使用如下指令

ALTER SYSTEM SWITCH LOG FILE;

- 把該檔案copy到你想要放到位置

- ALTER DATABASE RENAME FILE ’filename’ TO filename’

增加redo log 檔案

ALTER DATABASE [database]

ADD LOGFILE MEMBER

[ ’filename’ [REUSE]

[, ’filename’ [REUSE]]...

TO {GROUP integer

|(’filename’[, ’filename’]...)

}

删除redo log 檔案

如果redo log 檔案是invalid狀态，可以删除之

ALTER DATABASE [database] DROP LOGFILE MEMBER ’filename’[, ’filename’]...

相關資料字典

察看

v$log

v$logfile

v$log_history