重做日志文件基本维护简析
重做日志文件最重要的用途就是用来恢复数据（其实你也可以用来logminer），它记录着system global area（sga）当中的database buffer cache（高速缓存区）的所有变更信息，记录到log buffer；不过在某些特殊情况下比如sqlldr direct等直接写入操作例外，这些例外将不会被记录。因此，使用redo log在实例崩溃情况下来恢复尚未写入数据文件的的数据。 www.2cto.com
针对每个实例，只有一个写进程LGWR；重做日志文件建议至少两个组，每个组至少两个成员文件，同一个组内的成员之间是内容相同的副本拷贝，同组内的成员大小保持一致，当然建议所有的成员大小都一致，为了避免相关的LGWR等待影响数据库性能，一个比较有用的建议是将成员文件在物理磁盘上分散存放。
提到重做日志文件不得不提到重做日志缓存区log buffer，以及一个重要的进程LGWR日志写进程，该进程负责建log buffer的内容写入到redo logfile，具体的触发条件如下：
◆ 当发出commit命令的时候
◆ 当log buffer的空间写满到1/3的时候或者当log buffer的空间写满1MB的记录的时候
◆ 当每3秒钟超时的时候
◆ 当DBWn需要写入数据文件的操作之前的时候
◆ 当切换日志文件的时候
0. 查看日志文件
SELECT group#, sequence#, bytes, members, status FROM v$log;--组号、序列号、大小、成员数量、状态【UNUSED-从未使用过；CURRENT-当前使用中；ACTIVE-活动的，进行恢复的使用将会被用到；INACTIVE-非活动的；CLEARING_CURRENT-正在清除当前日志文件中已经关闭的线程；CLEARING-类似的，在执行ALTER DATABASE CLEAR LOGFILE之后将被清空为空日志之后将转化为UNUSED】
select * from v$logfile;--状态【INVALID-该文件不可访问；STALE-该文件内容不完全，比如正在添加一个文件成员；DELETED-该文件不再被使用；空白-正在使用】
SELECT groups, current_group#, sequence# FROM v$thread;--组数量、当前组、序列号
1. 查看log buffer www.2cto.com
show parameter log_buffer
伴随着重做日志而来的还有一个重要的信息，那就是是否将日志文件进行归档。数据库可以运行在归档模式（archivelog）和非归档模式（noarchivelog）下。
2. 查看归档模式
archive log list;
select log_mode from v$database;
select archiver from v$instance;
3. 归档方式切换
为了简单，这里使用oracle 10g来演示。
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 8 15:49:39 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 158
Current log sequence 160
--目前未归档，我们要切换为归档，首先设置归档目录
SQL> alter system set log_archive_dest_1='location=/u01/oracle/10g/arch' scope=spfile;
System altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动实例mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 469762568 bytes
Database Buffers 1124073472 bytes
Redo Buffers 14680064 bytes
Database mounted.
--打开归档模式
SQL> alter database archivelog;
www.2cto.com
Database altered.
--打开数据库
SQL> alter database open;
Database altered.
--检查归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 158
Next log sequence to archive 160
Current log sequence 160
--手工切换日志文件
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 159
Next log sequence to archive 161
Current log sequence 161
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 160
Next log sequence to archive 162
Current log sequence 162
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 161
Next log sequence to archive 163
Current log sequence 163
--切换为非归档模式
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 469762568 bytes
Database Buffers 1124073472 bytes
Redo Buffers 14680064 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
www.2cto.com
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 158
Current log sequence 160
4. 日志文件维护
4.1. 增加日志组
sys@GT9I> select group# from v$log;
GROUP#
----------
1
2
3
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
现在增加一组包含2个成员的日志组：
sys@GT9I> alter database add logfile group 4 ('/u01/oracle/9i/oradata/gt9i/redo04a.log','/u01/oracle/9i/oradata/gt9i/redo04b.log') size 20M;
Database altered.
sys@GT9I> select group# from v$log;
GROUP#
----------
1
2
3
4
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
4 /u01/oracle/9i/oradata/gt9i/redo04a.log
4 /u01/oracle/9i/oradata/gt9i/redo04b.log
4.2. 增加日志成员
sys@GT9I> alter database add logfile member '/u01/oracle/9i/oradata/gt9i/redo04c.log' to group 4;
Database altered.
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
4 /u01/oracle/9i/oradata/gt9i/redo04a.log
4 /u01/oracle/9i/oradata/gt9i/redo04b.log
4 /u01/oracle/9i/oradata/gt9i/redo04c.log
www.2cto.com
可以看到在添加成员的时候没有使用文件大小的属性选项，这是因为同组内的成员应当保持大小一致。
4.3. 删除日志成员
sys@GT9I> alter database drop logfile member '/u01/oracle/9i/oradata/gt9i/redo04c.log';
Database altered.
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
4 /u01/oracle/9i/oradata/gt9i/redo04a.log
4 /u01/oracle/9i/oradata/gt9i/redo04b.log
4.4. 删除日志组
sys@GT9I> alter database drop logfile group 4
2 ;
Database altered.
sys@GT9I> select group# from v$log;
GROUP#
----------
1
2
3
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
5. 日志挖掘
日志挖掘也即是logminer。
首先创建字典文件。
sys@GT9I> alter system set utl_file_dir='/u01/oracle/9i/oradata/gt9i/dict' scope=spfile;
System altered.
sys@GT9I> startup force;
ORACLE instance started.
Total System Global Area 286752136 bytes
Fixed Size 740744 bytes
Variable Size 150994944 bytes
Database Buffers 134217728 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
sys@GT9I>
1 BEGIN
2 DBMS_LOGMNR_D.build (
3 dictionary_filename => 'mydict1.ora',
4 dictionary_location => '/u01/oracle/9i/oradata/gt9i/dict');
5 END; www.2cto.com
6 /
PL/SQL procedure successfully completed.
做些测试，我这里用另外一个用户执行创建表、插入、删除操作，还创建了一个过程，最后删除表和过程。
添加日志文件
查看下当前的日志文件：
sys@GT9I> select status,member from v$logfile;
STATUS MEMBER
---------- --------------------------------------------------
/u01/oracle/9i/oradata/gt9i/redo03.log
STALE /u01/oracle/9i/oradata/gt9i/redo02.log
STALE /u01/oracle/9i/oradata/gt9i/redo01.log
sys@GT9I> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 12 104857600 1 NO INACTIVE 3389635 16-MAY-12
2 1 10 104857600 1 NO INACTIVE 3099675 14-MAY-12
3 1 13 104857600 1 NO CURRENT 3414320 16-MAY-12
这里是group=3的组是当前组。
BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo03.log');
/*
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo01.log');
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo02.log');
*/
END;
/
开始挖掘
首先开启参数，否则无法挖掘到ddl语句。
alter database add supplemental log data;
BEGIN
DBMS_LOGMNR.start_logmnr (
dictfilename => '/u01/oracle/9i/oradata/gt9i/dict/mydict1.ora',
starttime => TO_DATE('20120516 16:20:00','yyyymmdd hh24:mi:ss'),
endtime => TO_DATE('20120516 16:28:00','yyyymmdd hh24:mi:ss'));
END;
/
接着可以查询挖掘到的内容，注意必须在同一个会话内进行查询：
Select Scn, Operation, Sql_Redo, Sql_Undo From V$logmnr_Contents;
最后停止挖掘：
BEGIN www.2cto.com
DBMS_LOGMNR.end_logmnr;
END;
/
以上测试在9i测试通过，但是9i当中无法找到DDL语句；相反，找10g当中可以看到insert、delete等DDL语句，但是在10g当中开启挖掘的语句和9i的不一样，9i的开始挖掘语句在10g当中执行报错：缺失日志文件，可以使用如下语句开始挖掘：
Declare
Begin
dbms_logmnr.start_logmnr(dictfilename =>'/u01/oracle/9i/oradata/gt9i/dict/mydict2.ora');
End;
-The End-
作者 gtlions
重做日志文件基本维护简析 
