Redo log buffer和Redo log file到底存储的是什么？请大家继续讨论

supersea

最初由 Cluster 发布
[B]Redo logs contain both the redo and undo associated to any changes against your database (one mini-transaction in the redo/arch file).
Every time commit a transaction, ORACLE guarantees that  the changes to the blocks are written to the redo logs.  Furthermore, undo information needed to roll back uncommitted transactions are also stored
in the redo logs/archive files.  That is why, during instance recovery,  oracle will roll forward by applying the redo/arch files and then roll back by applying the undo, make sure you have a consistent database,
after finish the recovery processing
Also in 8i the log miner can query at different version of data by rows in redo/arch files, this is another approve from different angle of view. [/B]

--------------------------------------------------------------------------
既然Redo logs contain both the redo and undo associated to any changes against your database,那么在instance恢复时，直接从Redo logs 恢复就行了，为什么还要Rollback Segment？

Cluster

The rollback in here is the 2nd phase of recovery processing where undo information from uncommitted changes in the redo log are undone,  not same as what you said rollback segment that is physical storage. They are total different things.

chao_ping

我想yonghuang得意思是：
“Redo log 只是记录执行的语句, not actual data. You keep forgetting this. ”  是对上面那个朋友说得，就是说他老是认为这样，实际上这样是错误得。

redo log记录了所有对数据库得Block得修改。rbs得修改也是记录在redo log里面得，因为rbs也是datafile里面得。

至于DDL，就是记录了对Oracle数据库字典得操作，那些确实是只记录语句（其实说语句也不准确，只能说是记录了对系统表空间的DML操作导致的系统表空间的Block的改变）。

最初由 supersea 发布
[B]论坛上有不少关于redo log buffer和redo log file中内容的疑惑，我看了一些资料和大家的发言，觉得不是很清楚，先总结书上和大家的发言如下，请大家继续讨论：
1。The redo log buffer is a circular buffer in the SGA that holds information on the changes made to the database data. The changes are known as redo entries or change vectors and are used to redo the changes in case of a failure.（sybex books)没有说明到底什么change
2。The redo log file contains both committed and ncommitted transactions. Whenever a transaction is committed, a system change number is assigned to the redo records to identify the committed transaction.
3。在log file中没有提交的事务仅仅有该事务的记录信息，没有诸如重做项（用于redo）等，故需rollback的事务无需在logfile中删除，当实例恢复时会找出没有提交的事务，利用rollback reg后向恢复。（网友回答）
４。LOG FILE中保留的是对数据库操作的真实情况，当执行一个DML时，LOGFILE中记录下操作的类型、事务的编号等信息，当事务ROLLBACK或COMMIT时，再向LOGFILE中写入一条信息，记录事务号和是ROLLBACK还是COMMIT。具体可以看一下数据库原理的书籍。。（网友回答）
５。Redo log 只是记录执行的语句, not actual data. You keep forgetting this. It records all operations done to the database (except nologging operations).

Yong Huang
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－我的疑问：
Redo log buffer 和file到底是仅仅记录执行的语句, not actual data而已，还是“before and after image copies of changed data”?
请大家讨论！！
谢谢！！






[/B]

Yong Huang

machao's words

在一个10万行的表上执行 delete from <tablename>
在archive log 打开的情况下,会写很多的log出来. 但语句只有一条.没有可能如此多的log里面就一条语句吧

reminds me that what I said earlier is quite inaccurate. Here's an experiment I just did. I created a 1-column table test and inserted 2 rows (12345 and -100) into it. Then I typed delete from test. Launching logmnr and looking at v$logmnr_contents.sql_redo, I see

delete from YONG.TEST where A = 12345 and ROWID = 'AAAAu1AAFAAAAA1AAA';
delete from YONG.TEST where A = -100 and ROWID = 'AAAAu1AAFAAAAA1AAB';

I suppose this means Oracle records every row operation in redo log. That is, "delete from 100krowtable" is translated into 100,000 "delete from 100krowtable where [column name/value]s and ROWID = '[rowid]'" statements. Since these row-level operations are recorded along with actual column values, saying redo only records operations, not actual data, is misleading at the best, or totally wrong if you view it in a different perspective. I apologize for this loose thinking.

Yong Huang

最初由 chao_ping 发布
[B]我想yonghuang得意思是：
“Redo log 只是记录执行的语句, not actual data. You keep forgetting this. ”  是对上面那个朋友说得，就是说他老是认为这样，实际上这样是错误得。

redo log记录了所有对数据库得Block得修改。rbs得修改也是记录在redo log里面得，因为rbs也是datafile里面得。

至于DDL，就是记录了对Oracle数据库字典得操作，那些确实是只记录语句（其实说语句也不准确，只能说是记录了对系统表空间的DML操作导致的系统表空间的Block的改变）。
[/B]

machao

Yong Huang兄,你做事的精神真是佩服.
就几句话没说清还专门apologize 一下.

我前面贴了一个贴子是和direct read有关
请这里的各位高手,帮我下忙
先谢了.