Oracle中联机日志文件(online redo log)在大多平台上以512 字节为一个标准块。
(HPUX,Tru64 Unix上是1024bytes,SCO UNIX,Reliant UNIX上是2048bytes,而MVS,MPE/ix上是4096bytes,虽然以上许多UNIX已经不再流行,实际情况可以通过
select max(l.lebsz) log_block_size_kccle
from sys.x$kccle l
where l.inst_id = userenv(‘Instance’) 语句查询到)
LGWR后台进程写出REDO时未必能填满最后的当前日志块。举例而言，假设redo buffer中有1025字节的内容需要写出，则1025=512+512+1 共占用三个重做日志标准块，前2个标准块被填满而第三个标准块只使用了1个字节。在LGWR完成写出前，需要释放”redo allocation”闩，在此之前SGA中索引”redo buffer”信息的变量将指向未被填满块后面的一个重做块，换而言之有511字节的空间被LGWR跳过了，这就是我们说的redo wastage；我们可以通过分析v$sysstat动态视图中的redo wastage统计信息了解实例生命周期中的重做浪费量。
SQL> col name for a25 SQL> select name,value from v$sysstat where name like '%wastage%'; NAME VALUE ------------------------- ---------- redo wastage 132032
redo wastage的一个图示:
为什么要浪费这部分空间呢？实际上，这种做法十分有益于LGWR的串行I/O模式。redo wastage并不是问题或者Bug，而是Oracle故意为之的。当然过量的redo wastage也不是什么好事，一般是LGWR写出过于频繁的症状表现。9i以后很少有因为隐式参数_log_io_size过小而导致的LGWR过载了，如果在您的系统中发现redo wastage的问题不小，那么无限制地滥用commit操作往往是引起问题的罪魁祸首，减少不必要的commit语句，把commit从循环中移除都将利于减少redo wastage。
我们来看一下关于redo wastage的演示:
SQL> select distinct bytes/1024/1024 from v$log;
BYTES/1024/1024
---------------
50 /*确认联机日志文件大小为50MB*/
SQL> archive log list; /*确认数据库处于归档状态*/
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /s01/arch
SQL> set time on;
19:49:45 SQL> alter system switch logfile; /*切换日志，清理现场*/
System altered.
19:51:07 SQL> col name for a25
19:51:16 SQL> select name,value from v$sysstat where name in ('redo size','redo wastage');
NAME VALUE
------------------------- ----------
redo size 1418793324
redo wastage 88286544 /*演示开始时的基础统计值*/
19:51:19 SQL> begin
19:52:10 2 for i in 1..550000 loop
19:52:10 3 insert into tv values(1,'a');
19:52:10 4 commit;
19:52:10 5 end loop;
19:52:10 6 end;
19:52:11 7 /
/*匿名块中commit操作位于loop循环内，将导致大量redo wastage*/
PL/SQL procedure successfully completed.
19:53:07 SQL> select name,value from v$sysstat where name in ('redo size','redo wastage');
NAME VALUE
------------------------- ----------
redo size 1689225404
redo wastage 112011352
/*频繁提交的匿名块产生了 1689225404-1418793324=257MB的redo,其中存在112011352-88286544=22MB的redo wastage*/
19:53:14 SQL> begin
19:53:33 2 for i in 1..550000 loop
19:53:33 3 insert into tv values(1,'a');
19:53:33 4 end loop;
19:53:33 5 commit;
19:53:33 6 end;
19:53:34 7 /
/* 此匿名块中commit操作被移除loop循环中，批量修改数据后仅在最后提交一次*/
PL/SQL procedure successfully completed.
19:53:59 SQL> select name,value from v$sysstat where name in ('redo size','redo wastage');
NAME VALUE
------------------------- ----------
redo size 1828546240
redo wastage 112061296
/*稀疏提交的匿名块最后产生了1828546240-1689225404=132MB的重做，而redo wastage为112061296-112011352=48k*/
可能您会很奇怪前者不是只比后者多出22MB的redo浪费吗,为什么总的redo量差了那么多？
我们需要注意到commit本身也是要产生redo的，而且其所产生的还不少！就以上演示来看频繁提交的过程中，commit所占用的redo空间几乎接近一半(257-132-22)/257=40%,而每次commit的平均redo量为(257-132-22)*1024*1024/550000=196 bytes。
commit操作是事务ACID的基础之一，合理运用commit可以帮我们构建健壮可靠的应用，而滥用它必将是另一场灾难！
How to Dump Redo Log File Information
PURPOSE
This article explain how to obtain a dump of the header information in the
online redo log file(s), as well as obtaining selected information from the
online or archived redo log files.
SCOPE & APPLICATION
Informational
You are working with Oracle Technical Support. As part of the diagnostic
process, you have been asked to take a dump of the redo log files. The
information in the logs is often used to help diagnose corruption issues.
The following commands will be used in this process:
1. The ‘alter session’ command is used to dump redo headers.
2. Use the ‘alter system dump logfile’ to dump log file contents.
This command requires ‘ALTER SYSTEM’ system privilege. The database can be in
mount, nomount or open state when the command is issued. An online log file
or an archived log file can be dumped. It is even possible to dump a
file from another database, as long as the operating systems are the same.
Output from the command is put into the session’s trace file.
The following ways of dumping a redo log file are covered:
1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. To dump records based on layer and opcode
6. Dump the file header information
7. Dump an entire log file:
1. To dump records based on DBA (Data Block Address)
————————————————–
This will dump all redo records for the range of data
blocks specified for a given file # and block # range.
From sqlplus (sqldba or svrmgr for older versions), issue the following command:
ALTER SYSTEM DUMP LOGFILE ‘filename’
DBA MIN fileno . blockno
DBA MAX fileno . blockno;
Example:
========
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
DBA MIN 5 . 31125
DBA MAX 5 . 31150;
This will cause all the changes to the specified range of data blocks to be
dumped to the trace file. In the example given, all redo records for file #5,
blocks 31125 thru 31150 are dumped.
Note
====
For 10g:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
DBA MIN 5 . 31125 DBA MAX 5 . 31150;
will raise:
ORA-01963: Must specify a block number
In 10g we need to skip the dot ‘.’ while doing the redo dumps
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
DBA MIN 5 31125 DBA MAX 5 31150;
2. To dump records based on RBA (Redo Block Address)
————————————————-
This will dump all redo records for the range of redo
addresses specified for the given sequence number and block number.
Syntax:
ALTER SYSTEM DUMP LOGFILE ‘filename’
RBA MIN seqno . blockno
RBA MAX seqno . blockno;
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
RBA MIN 2050 . 13255
RBA MAX 2255 . 15555;
3. To dump records based on SCN
—————————-
Using this option will cause redo records owning changes within the SCN range
specified to be dumped to the trace file.
ALTER SYSTEM DUMP LOGFILE ‘filename’
SCN MIN minscn
SCN MAX maxscn;
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
SCN MIN 103243
SCN MAX 103294;
If the purpose is to check the dumpfile you can rather do the following,
SQL> ALTER SYSTEM DUMP LOGFILE ‘filename’ SCN MIN 1 SCN MAX 1;
If the above completes sucessfully it ensures no issues with the archivelog.
4. To dump records based on time.
——————————
Using this option will cause redo records created within the time range
specified to be dumped to the trace file.
From sqlplus (sqldba or svrmgr for older versions), issue the following command:
ALTER SYSTEM DUMP LOGFILE ‘filename’
TIME MIN value
TIME MAX value;
Example:
========
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
TIME MIN 299425687
TIME MAX 299458800;
Please Note: the time value is given in REDO DUMP TIME
5. To dump records based on layer and opcode.
——————————————
LAYER and OPCODE are used to dump all log records for a particular type of
redo record, such as all dropped row pieces.
From sqlplus (sqldba or svrmgr for older versions), issue the following command:
ALTER SYSTEM DUMP LOGFILE ‘filename’
LAYER value
OPCODE value;
Example:
========
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
LAYER 11
OPCODE 3;
6. Dump the file header information:
———————————
This will dump file header information for every
online redo log file.
From sqlplus (sqldba or svrmgr for older versions), issue the following command:
alter session set events ‘immediate trace name redohdr level 10’;
For dumping archivelog header,issue the following command:
ALTER SYSTEM DUMP LOGFILE ‘filename’ RBA MIN 1 1 RBA MAX 1 1;
7. Dump an entire log file:
————————
From sqlplus (sqldba or svrmgr for older versions), issue the following command:
ALTER SYSTEM DUMP LOGFILE ‘filename’;
Please note:
Fully qualify the filename, and include the single quotes.
Example:
========
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’;
The dump of the logfile will be written into a trace file in the udump destination.
Use the command ‘show parameters dump’ within an sqlplus session.
The ouput will show the location for the udump destination where
the trace file exists.