也请参照：http://www.itpub.net/showthread. ... 19&goto=newpost
我的执行过程：
1.备份操作表所在的数据文件[/COLOR]
SQL> alter tablespace users2 begin backup;
Tablespace altered.
备份数据文件......
SQL> alter tablespace users2 end backup;
Tablespace altered.
2.切换日志，在group2中记录3条insert操作，只提交第1条。[/COLOR]
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1
1
7104857600
2 YES ACTIVE
3887713 10-JUN-04
2
1
8104857600
2 NOCURRENT
3888820 10-JUN-04
3
1
6104857600
2 YES INACTIVE
3855055 10-JUN-04
SQL> insert into donny.d values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into donny.d values(2);
1 row created.
SQL> insert into donny.d values(3);
1 row created.
3.模拟崩溃，然后restore and recover。可以发现恢复过程记录在group 3中[/COLOR]
SQL> shutdown abort;
ORACLE instance shut down.
restore backuped datafile...
SQL> startup
ORACLE instance started.
Total System Global Area131144544 bytes
Fixed Size
453472 bytes
Variable Size
104857600 bytes
Database Buffers 25165824 bytes
Redo Buffers
667648 bytes
Database mounted.
Database opened.
SQL> select * from v$log;
GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1
1
7104857600
2 YES INACTIVE
3887713 10-JUN-04
2
1
8104857600
2 YES INACTIVE
3888820 10-JUN-04
3
1
9104857600
2 NOCURRENT
3908853 10-JUN-04
SQL> select * from donny.d;
ID
----------
1
SQL>
4.Logmnr logfile group 2(崩溃前的日志)[/COLOR]
SCN
SQL Redo
3888825
set transaction read write;
3888825
insert into "DONNY"."D"("ID"
values ('1');
3888827
commit;
3888830
set transaction read write;
3888830
insert into "DONNY"."D"("ID"
values ('2');（没有提交,可是3888832的commit;代表什么？）[/COLOR]
3888831
set transaction read write;
3888831
Unsupported
3888831
Unsupported
3888832
commit;
....
3888851
insert into "DONNY"."D"("ID"
values ('3'); (最后一条，没有提交)
5.Logmnr logfile group 3(记录恢复过程)[/COLOR]
SCN
SQL Redo
........(全是对回滚段信息的更新，实际操作了什么？)[/COLOR]
3908856
update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BLOCK#" = '9', "SCNBAS" = '3888842', "SCNWRP" = '0', "XACTSQN" = '1979', "UNDOSQN" = '142', "INST#" = '0', "STATUS$" = '2', "TS#" = '1', "SPARE1" = '1' where "NAME" = '_SYSSMU1$' and "USER#" = '1' and "FILE#" = '2' and "BLOCK#" = '9' and "SCNBAS" = '3867703' and "SCNWRP" = '0' and "XACTSQN" = '1978' and "UNDOSQN" = '142' and "INST#" = '0' and "STATUS$" = '3' and "TS#" = '1' and "SPARE1" = '1' and ROWID = 'AAAAAPAABAAAABqAAB';
3908857
commit;
......
3908906
set transaction read write;
3908906
update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "BLOCK#" = '153', "SCNBAS" = '3888840', "SCNWRP" = '0', "XACTSQN" = '1920', "UNDOSQN" = '94', "INST#" = '0', "STATUS$" = '3', "TS#" = '1', "SPARE1" = '1' where "NAME" = '_SYSSMU10$' and "USER#" = '1' and "FILE#" = '2' and "BLOCK#" = '153' and "SCNBAS" = '3888840' and "SCNWRP" = '0' and "XACTSQN" = '1920' and "UNDOSQN" = '94' and "INST#" = '0' and "STATUS$" = '2' and "TS#" = '1' and "SPARE1" = '1' and ROWID = 'AAAAAPAABAAAABqAAK';
3908907
commit;
3908908
delete from "DONNY"."D" where ROWID = 'AAAH05AAOAAABDcAAB';（只有这两条针对没有提交的事务的回滚操作）[/COLOR]
3908908
delete from "DONNY"."D" where ROWID = 'AAAH05AAOAAABDcAAC';
3908909
commit;
......
3908924
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '173' and "INTCOL#" = '5' and "RETENTION" = '10800' and ROWID = 'AAAAACAABAAAAAjAAA';
......
update "SYS"."LOB$" set...这是什么操作？[/COLOR]
我的问题：
1.recover是先前滚，后回滚。但是为什么第5步记录恢复过程的日志中没有对已经提交的第一条插入语句的前滚记录呢，而只是回滚了没有提交的2、3条语句。
2.第4步中，第2条插入语句是没有提交的，那么3888832的commit;代表什么？
3.第5步中，记录恢复过程的日志中，开始全是对回滚段信息的更新，实际操作了什么？
4.第5步中，oracle是根据什么知道要回滚这两条的？我可以查找到吗？
5.第5步中，最后的update "SYS"."LOB$" set...这是什么操作？
非常感谢能把这么长的帖子看完，如果能解决我的疑问，就在感谢一次！
|
