[20170322]实例crash恢复2.txt
如果发生了实例崩溃，只需要在日志文件中找到检查点位置(low cache rba),从此开始应用所有的重做日志文件， 就完成了前滚操作。
实例崩溃后，再次启动数据库，oracle会到控制文件中读取low cache rba，这就是检查点位置。 从此处开始应用重做日志，应用到on
disk rba的位置。on disk rba是磁盘中重做日志文件的最后一条重做记录的rba。 加快恢复速度，确定恢复日志的起点。
不管redo记录的事务提交还是非提交,都会应用,这个相当于前滚Roll forward (crash recovery),然后再根据undo做事务的恢复,这个叫
后滚Roll backward (transaction recovery)
参考链接; [20140507]实例crash恢复.txt=> http://blog.itpub.net/267265/viewspace-1156043/
以上过程完全无需dba参与,oracle自动完成修复启动数据库,除非在线日志损坏或者数据文件出现不一致异常等情况,前几天别人测试发现
无法重复我前面的测试结果,我自己重复做一次,发现确实这样,到底是什么原因呢?还是通过例子来说明:
1.建立测试环境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t ( id number,name varchar2(10));
insert into t select rownum,'ABCDEF' from dual connect by level<=10;
commit ;
alter system checkpoint ;
--//将脏块写盘.
SCOTT@book> select rowid,t.* from t where rownum<=1 ;
ROWID ID NAME
------------------ ---------- --------------------
AAAWEEAAEAAAAIkAAA 1 ABCDEF
SCOTT@book> @ &r/rowid AAAWEEAAEAAAAIkAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90372 4 548 0 0x1000224 4,548 alter system dump datafile 4 block 548 ;
SCOTT@book> update t set name='1234zzzzab' ;
10 rows updated.
--//注意name的字符串长度与原来不同,不提交,打开另外的会话.执行
SCOTT@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 YES INACTIVE 13276910949 2017-02-28 14:40:12 13276931102 2017-03-21 16:05:40
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 696 52428800 512 1 NO CURRENT 13276931102 2017-03-21 16:05:40 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES INACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前日志是/mnt/ramdisk/book/redo02.log.
$ strings /mnt/ramdisk/book/redo02.log | grep 1234zzzzab
$ strings /mnt/ramdisk/book/redo02.log | grep 1234zzzzab
$ strings /mnt/ramdisk/book/redo02.log | grep 1234zzzzab
$ strings /mnt/ramdisk/book/redo02.log | grep zzzz
--//执行多次毫无显示.
--//这样如果异常关机,自然没有相关记录.也就是无法演示我前面的测试.
--//等很久才出现:
$ strings /mnt/ramdisk/book/redo02.log | grep zzzz
1234zzzzab
XE1234zzzzab
1234zzzzab
L1234zzzzab
1234zzzzab
1234zzzzab]
1234zzzzab
1234zzzzab
1234zzzzab
1234zzzzab
--//说明日志内容延迟写入.最大的可能是IMU的特性.因为我在我以前的测试环境打开flashback功能.这样禁止了IMU特性.
2.打开flashback禁止IMU特性.
SYS@book> alter database flashback on ;
Database altered.
--//重复测试:
SYS@book> alter system archive log current ;
System altered.
drop table t purge;
create table t ( id number,name varchar2(10));
insert into t select rownum,'ABCDEF' from dual connect by level<=10;
commit ;
alter system checkpoint ;
SCOTT@book> update t set name='1234zzzzAB' ;
10 rows updated.
--//注意name的字符串长度与原来不同,不提交,打开另外的会话.执行
SCOTT@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 YES INACTIVE 13276910949 2017-02-28 14:40:12 13276931102 2017-03-21 16:05:40
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 696 52428800 512 1 YES INACTIVE 13276931102 2017-03-21 16:05:40 13276933814 2017-03-21 16:16:22
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 697 52428800 512 1 NO CURRENT 13276933814 2017-03-21 16:16:22 2.814750E+14
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前日志是/mnt/ramdisk/book/redo02.log.
--//马上查询,可以发现:
$ strings /mnt/ramdisk/book/redo03.log | grep zzzz
1234zzzzAB
1234zzzzAB
zzzzAB]
1234zzzzAB
1234zzzzAB
21234zzzzAB
1234zzzzAB
81234zzzzAB
1234zzzzAB
1234zzzzAB
SCOTT@book> update t set name='1234ZZZZAB' ;
10 rows updated.
$ strings /mnt/ramdisk/book/redo03.log | grep ZZZZ
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
1234ZZZZAB
N_ZZZZ,
--//再次证明自己的判断.是因为IMU导致测试问题.
3.继续测试:
--//也就是在关闭IMU的情况下应该模拟出我前面的测试:
--//drop table t purge;
create table t ( id number,name varchar2(10));
insert into t select rownum,'ABCDEF' from dual connect by level<=10;
commit ;
alter system checkpoint ;
--//将脏块写盘.
SCOTT@book> select rowid,t.* from scott.t where rownum=1;
ROWID ID NAME
------------------ ---------- --------------------
AAAWENAAEAAAAIkAAA 1 ABCDEF
SCOTT@book> @ &r/rowid AAAWENAAEAAAAIkAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90381 4 548 0 0x1000224 4,548 alter system dump datafile 4 block 548 ;
SCOTT@book> update t set name='1234ZZZZx' ;
10 rows updated.
--//注意name的字符串长度与原来不同,不提交,打开另外的会话.执行
SYS@book> shutdown abort ;
ORACLE instance shut down.
$ dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=548 count=1 2>/dev/null | strings -3 | uniq -c
9 ABCDEF,
1 ABCDEF
--//shutdown abort.块内记录原来信息.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> recover database ;
Media recovery complete.
$ dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=548 count=1 2>/dev/null | strings -3 | uniq -c
2 1234ZZZZx,
1 1234ZZZZx,
7 1234ZZZZx,
9 ABCDEF,
1 ABCDEF
--//前滚,应用日志.出现修改信息1234ZZZZx.注意这个信息没有提交就crash了.
SYS@book> alter database open ;
Database altered.
SYS@book> alter system checkpoint ;
System altered.
$ dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=548 count=1 2>/dev/null | strings -3 | uniq -c
10 ABCDEF,
2 1234ZZZZx,
1 1234ZZZZx,
7 1234ZZZZx,
9 ABCDEF,
1 ABCDEF
--//回滚,前面出现10个ABCDEF.这样完整模拟前面的测试.
BBED> dump /v dba 4 ,548 count 1024 offset 7680
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 548 Offsets: 7680 to 8191 Dba:0x01000224
-----------------------------------------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 2c000202 c1020641 l ........................,......A
42434445 462c0002 02c10306 41424344 45462c00 0202c104 06414243 4445462c l BCDEF,......ABCDEF,......ABCDEF,
000202c1 05064142 43444546 2c000202 c1060641 42434445 462c0002 02c10706 l ......ABCDEF,......ABCDEF,......
41424344 45462c00 0202c108 06414243 4445462c 000202c1 09064142 43444546 l ABCDEF,......ABCDEF,......ABCDEF
2c000202 c10a0641 42434445 462c0002 02c10b06 41424344 45462c02 0202c10b l ,......ABCDEF,......ABCDEF,.....
09313233 345a5a5a 5a782c02 0202c10a 09313233 345a5a5a 5a782c02 0202c109 l .1234ZZZZx,......1234ZZZZx,.....
09313233 345a5a5a 5a782c02 0202c108 09313233 345a5a5a 5a782c02 0202c107 l .1234ZZZZx,......1234ZZZZx,.....
09313233 345a5a5a 5a782c02 0202c106 09313233 345a5a5a 5a782c02 0202c105 l .1234ZZZZx,......1234ZZZZx,.....
09313233 345a5a5a 5a782c02 0202c104 09313233 345a5a5a 5a782c02 0202c103 l .1234ZZZZx,......1234ZZZZx,.....
09313233 345a5a5a 5a782c02 0202c102 09313233 345a5a5a 5a782c00 0202c102 l .1234ZZZZx,......1234ZZZZx,.....
06414243 4445462c 000202c1 03064142 43444546 2c000202 c1040641 42434445 l .ABCDEF,......ABCDEF,......ABCDE
462c0002 02c10506 41424344 45462c00 0202c106 06414243 4445462c 000202c1 l F,......ABCDEF,......ABCDEF,....
07064142 43444546 2c000202 c1080641 42434445 462c0002 02c10906 41424344 l ..ABCDEF,......ABCDEF,......ABCD
45462c00 0202c10a 06414243 4445462c 000202c1 0b064142 43444546 0a06b0da l EF,......ABCDEF,......ABCDEF....
<32 bytes per line>
--//在关闭IMU下:
SYS@book> SELECT INDX
,last_buf_kcrfa
,PNEXT_BUF_KCRFA_CLN nxtbufadr
,NEXT_BUF_NUM_KCRFA_CLN nxtbuf#
,BYTES_IN_BUF_KCRFA_CLN "B/buf"
,PVT_STRAND_STATE_KCRFA_CLN state
,STRAND_NUM_ORDINAL_KCRFA_CLN strand#
,PTR_KCRF_PVT_STRAND stradr
,INDEX_KCRF_PVT_STRAND stridx
,SPACE_KCRF_PVT_STRAND strspc
,TXN_KCRF_PVT_STRAND txn
,TOTAL_BUFS_KCRFA totbufs#
,STRAND_SIZE_KCRFA strsz
FROM X$KCRFSTRAND
WHERE LAST_BUF_KCRFA = '00';
INDX LAST_BUF_KCRFA NXTBUFADR NXTBUF# B/buf STATE STRAND# STRADR STRIDX STRSPC TXN TOTBUFS# STRSZ
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ----------
1 00 00 0 0 0 3735928559 00 0 0 0 249 132096
2 00 00 0 0 0 3735928559 00 0 0 0 249 132096
3 00 00 0 0 0 3735928559 00 0 0 0 249 132096
4 00 00 0 0 0 3735928559 00 0 0 0 249 132096
5 00 00 0 0 0 3735928559 00 0 0 0 249 132096
6 00 00 0 0 0 3735928559 00 0 0 0 249 132096
7 00 00 0 0 0 3735928559 00 0 0 0 249 132096
8 00 00 0 0 0 3735928559 00 0 0 0 249 132096
9 00 00 0 0 0 3735928559 00 0 0 0 249 132096
10 00 00 0 0 0 3735928559 00 0 0 0 249 132096
11 00 00 0 0 0 3735928559 00 0 0 0 249 132096
12 00 00 0 0 0 3735928559 00 0 0 0 249 132096
13 00 00 0 0 0 3735928559 00 0 0 0 249 132096
14 00 00 0 0 0 3735928559 00 0 0 0 249 132096
15 00 00 0 0 0 3735928559 00 0 0 0 249 132096
16 00 00 0 0 0 3735928559 00 0 0 0 249 132096
17 00 00 0 0 0 3735928559 00 0 0 0 249 132096
17 rows selected.
