[20170307]dg环境下在线日志损坏12.txt
http://blog.itpub.net/267265/viewspace-2134665/
http://blog.itpub.net/267265/viewspace-2134481/
--//前面的链接我测试了如果日志实时传输与应用的情况下,主库的崩溃并且在线日志删除的情况下(包括主机的备用日志)情况下,
--//利用备库接收日志来恢复主库的情况.做一点点总结:
1.将备用日志拷贝过来,必须执行如下命令,加入最后应用的scn号.
recover database using backup controlfile until change 13276911099;
2.采用这样的恢复的数据库最后scn存在差异,这样导致open resetlogs时,备库无法在应用日志.我的测试采用备库scn-1的方式解决.
--//当采用如下恢复方式时:
SYS@book> recover database until change 13276911099;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01112: media recovery not started
--//要获取/mnt/ramdisk/book/redo01.log由于这个文件已经删除.无法获取.
--//由于我备库日志,在线日志大小一致的,是否可以使用备库当时的接收日志来代替/mnt/ramdisk/book/redo01.log,欺骗oracle完成recover
--//database 过程呢?本测试仅仅就这样的情况做一下尝试:
1.环境:
--//首先还原环境,我包括异常关机的冷备份,拷贝回来,删除主库的在线日志以及备用日志.
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ /bin/cp -ar /u01/backup/20170301B/* /mnt/ramdisk/book/ ==>/* 使用冷备库恢复 */
$ ls /mnt/ramdisk/book/r*.log -1
/mnt/ramdisk/book/redo01.log
/mnt/ramdisk/book/redo02.log
/mnt/ramdisk/book/redo03.log
/mnt/ramdisk/book/redostb01.log
/mnt/ramdisk/book/redostb02.log
/mnt/ramdisk/book/redostb03.log
/mnt/ramdisk/book/redostb04.log
$ /bin/rm /mnt/ramdisk/book/r*.log ==>/* 删除日志 */
--//清除前面测试中归档日志,仅仅保留seq#=694的归档日志,因为recover时需要(在我的测试里面).
$ cd /u01/app/oracle/archivelog/book/
$ rm ....
2.确定备库当时的接收日志:
--//备库执行:
SYS@bookdg> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------ ---------- ------- ------------ -------- ------------ ----- --- ---------- ------------- ------------------- ------------ --------- ------------ -------------------
4 1337401710 1 695 52428800 512 78848 YES ACTIVE 13276910949 2017-02-28 14:40:12 13276911100 2017-02-28 14:42:35
5 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
6 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SYS@bookdg> @ &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 CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276889179 2017-02-27 08:59:01 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 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.
--//确定接收日志的备用日志是group#=4,/mnt/ramdisk/book/redostb01.log.
3.拷贝到主机:
$ scp /mnt/ramdisk/book/redostb01.log oracle@192.168.100.78:/mnt/ramdisk/book/redo01.log
oracle@192.168.100.78's password:
redostb01.log 100% 50MB 25.0MB/s 00:02
--//注意这样因为redo的文件头不一样,oracle不会认为那个文件group#1的.
4.先恢复看看:
SYS@book> recover database ;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00341: log 1 of thread 1, wrong log # 4 in header
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-01112: media recovery not started
--//很明显文件头不对.
--//补充说明一点,如果使用好的日志文件头替换备用日志的文件头,测试可以通过的,以下是当时的测试:
$ dd if=/u01/backup/20170301B/redo01.log skip=1 bs=512 count=1 of=/mnt/ramdisk/book/redo01.log conv=notrunc
--//说明实际上redo文件也与oracle的数据文件一样有1个OS的头,从os看文件大小:redo大小+512K.一般redo文件的块大小512字节.
$ ls -l /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-28 14:42:35 /mnt/ramdisk/book/redo01.log
--//建立redo大小50M, 而从os看50*1024*1024+512=52429312.正好对上.
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--//这里不报错,应用日志正常.说明差别在redo文件头的位置.另外我一直以为仅仅文件头不同,后面的信息都是一一对应的,实际上不是,
--//大家可以使用md5sum验证.
--//也就是讲通过修改备用日志文件头这种方式是可行的.
5.两者不同类型日志对比分析:
SYS@book> alter system dump logfile '/u01/backup/20170301B/redo01.log' validate;
System altered.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//分别保存不同的文件,对比看看:
$ diff a2 a1
1c1
< DUMP OF REDO FROM FILE '/u01/backup/20170301B/redo01.log'
---
> DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
11c11
< Control Seq=36119=0x8d17, File size=102400=0x19000
---
> Control Seq=36141=0x8d2d, File size=102400=0x19000
14c14
< thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
---
> thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x2 eot: 0 dis: 0
21c21
< Disk cksum: 0x95a3 Calc cksum: 0x95a3
---
> Disk cksum: 0xb59c Calc cksum: 0xb59c
27,28c27,28
< Unprotected mode
< Miscellaneous flags: 0x800000
---
> Maximize performance mode
> Miscellaneous flags: 0x822000
--//Control Seq=36119=0x8d17感觉在丢失redo文件后修改有点难度.先不管这些.
--//为了修改方便建立别名bvi80,这样每行80列,对应16个字符:
$ alias bvi80='env COLUMNS=80 bvi '
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo01.log => 当前是备用文件日志
$ bvi80 -b 512 -s 512 /u01/backup/20170301B/redo01.log => 好的日志文件
--//打开拷贝并且粘贴:
$ diff a1.txt a2.txt
1c1
< 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 9C B5 ................
--- ~~~~~
> 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 A3 95 .,..............
3,4c3,4
< 00000220 00 00 00 00 2D 8D 00 00 00 90 01 00 00 02 00 00 ....-...........
~~~~
< 00000230 04 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
--- ~~
> 00000220 00 00 00 00 17 8D 00 00 00 90 01 00 00 02 00 00 ................
> 00000230 01 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
11c11
< 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 02 00 00 00 ..q5. ..........
--- ~~
> 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 01 00 00 00 ..q5. ..........
13c13
< 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 00 00 00 02 ................
---
> 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 01 00 00 02 ................
15c15 ~~
< 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 20 82 00 ....L..7..... ..
---
> 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 00 80 00 ....L..7........
--//注意看下划线的地方. 偏移14-15字节(从0算起)是检查和,0x230对应redo gruop#(我这里从备用日志拷贝过来,group#=4,要修改为1).
--//这样就可以欺骗oracle认为group#=1.
--//0x224-0x225对应的是Control Seq(0x2d8d) ,后面2个我猜测 偏移0x2AC,0x2CC位置.
--//对应的是hws,eot,这个什么意思不懂,仅仅找到这个链接http://mbouayoun.developpez.com/dumpredo/提到
--//hws: 0x4 c'est header write seq# ,法文不知道c'est表示什么?
--//这样就知道要修改的位置:
--//group# 一定要修改
group# 0x0400 => 0x0100. 偏移0x230
hws 0x0200 => 0x0100 偏移0x2AC
eot 0x0000 => 0x0100 偏移0x2CC
cksum 0x9CB5 => ???
--//说明一点,如果仅仅改group#,检查和,使用recover database until最后一样报错,只有改hws,eot才ok.测试不再贴出.
--//计算检查和,很简单就是把改动再次做一次异或操作.我这里不考虑大小头问题^_^.因为我算出来直接就修改了.
9cb5
0400
0100
0200
0100
0000
0100
----
9bb5
--//也就是修改检查和为9bb5 (注:不考虑大小头问题)>
--//注:我使用微软自带的计算器算的.不知道有什么好方法,再多一点很麻烦的.
--//我使用bvi修改,完成后检查:
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//检查完整性ok.
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=36141=0x8d2d, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Disk cksum: 0xb59b Calc cksum: 0xb59b
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Maximize performance mode
Miscellaneous flags: 0x822000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 12e926f77b40c080dc716e8a264c329f
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 76Kb in 0.01s => 7.47 Mb/sec
Total redo bytes: 1023Kb Longest record: 2Kb, moves: 0/10 moved: 0Mb (0%)
Longest LWN: 2Kb, reads: 142
Last redo scn: 0x0003.175d95fb (13276911099)
Change vector header moves = 0/23 (0%)
----------------------------------------------
--//注意看~,说明修改正确.
6.开始恢复:
--//万事具备只欠东方^_^.
SYS@book> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
SYS@book> recover database until cancel;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------ ---------------
1 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//scn =13276911100(不再是13276911099) , fuzzy = No.视乎redo文件头记录的这些控制某种行为.
--//尝试open看看,因为有现在日志,可以尝试noresetlogs.
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--// /mnt/ramdisk/book/redo03.log不存在.
--//在备库执行:
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redostb02.log' validate;
System altered.
--//确认这个文件记录的是seq#=694的日志文件.
$ scp /mnt/ramdisk/book/redostb02.log oracle@192.168.100.78:/mnt/ramdisk/book/redo03.log
oracle@192.168.100.78's password:
redostb02.log 100% 50MB 50.0MB/s 00:01
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo03.log
--//仅仅需要修改0x230处 0x0500 => 0x0300,重新计算检查和.
2B19
0500
0300
----
2D19
--//这个过程略.
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 63227
Session ID: 232 Serial number: 3
--//alert.log:
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58093.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
USER (ospid: 58093): terminating the instance due to error 313
--//group#=2 被删除,不要紧clear看看.
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> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database open ;
Database altered.
--//OK,终于打开了,验证看看是否传输正常.
SYS@book> alter database clear logfile group 4 ;
Database altered.
SYS@book> alter database clear logfile group 5 ;
Database altered.
SYS@book> alter database clear logfile group 6 ;
Database altered.
SYS@book> alter database clear logfile group 7 ;
Database altered.
7.检查备库是否正常.
--//主库执行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
--//备库执行:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 17272 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 17270 IDLE LGWR 3 1 697 40 1 0
ARCH 17231 CLOSING ARCH 4 1 695 1 154 0
MRP0 17274 APPLYING_LOG N/A N/A 1 697 40 102400 0
--//seq#=697都传输应用过来,没有问题.
总结:
--//如果主库的异常关闭,可以使用备库的日志来恢复是不成问题的
1.确定那个备库日志接收当前.
2.拷贝过来:
--//修改组号 0x0400 => 0x0100.
hws 0x0200 => 0x0100
eot 0x0000 => 0x0100
http://blog.itpub.net/267265/viewspace-2134665/
SYS@book> recover database until change 13276911099;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01112: media recovery not started
--//问题依旧.无法继续下去,缺少seq#=695.拷贝到/mnt/ramdisk/book/redo01.log看看:
$ cp /u01/app/oracle/archivelog/book/1_695_896605872.dbf /mnt/ramdisk/book/redo01.log
SYS@book> recover database until change 13276911099;
ORA-00283: recovery session canceled due to errors
ORA-00341: log 1 of thread 1, wrong log # 4 in header
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
--//文件头识别错误,是否可以修改这个欺骗oracle,留下下次测试.
$ mv 1_695_896605872.dbf 1_695_896605872.dbf_yyy
--//我前面的测试recover database until change 13276911099;要找/mnt/ramdisk/book/redo01.log文件.而我拷贝备库的standby 对
--//应日志覆盖/mnt/ramdisk/book/redo01.log,提示ORA-00341: log 1 of thread 1, wrong log # 4 in header.
--//是否修改文件头告之文件是log 1 of thread 1.
--//另外重新测试移除归档目录的1_695_896605872.dbf文件,因为有时候测试open resetlogs打开.
1.环境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//启动到mount.
SYS@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 NO CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 693 52428800 512 1 YES INACTIVE 13276889179 2017-02-27 08:59:01 13276910486 2017-02-28 14:40:06
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES ACTIVE 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.
2.先测试使用原来的文件头覆盖备用库的文件头:
$ dd if=/u01/backup/20170301B/redo01.log skip=1 bs=512 count=1 of=/mnt/ramdisk/book/redo01.log conv=notrunc
--//说明实际上redo文件也与oracle的数据文件一样有1个OS的头,从os看文件大小:redo大小+512K.一般redo文件的块大小512字节.
$ ls -l /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-28 14:42:35 /mnt/ramdisk/book/redo01.log
--//建立redo大小50M, 而从os看50*1024*1024+512=52429312.正好对上.
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--//这里不报错,说明差别在redo文件头的位置.另外我一直以为仅仅文件头不同,后面的信息都是一一对应的,实际上不是,大家可以使用md5sum验证.
3.对比分析:
SYS@book> alter system dump logfile '/u01/backup/20170301B/redo01.log' validate;
System altered.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//分别保存不同的文件,对比看看:
$ diff a2 a1
1c1
< DUMP OF REDO FROM FILE '/u01/backup/20170301B/redo01.log'
---
> DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
11c11
< Control Seq=36119=0x8d17, File size=102400=0x19000
---
> Control Seq=36141=0x8d2d, File size=102400=0x19000
14c14
< thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
---
> thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x2 eot: 0 dis: 0
21c21
< Disk cksum: 0x95a3 Calc cksum: 0x95a3
---
> Disk cksum: 0xb59c Calc cksum: 0xb59c
27,28c27,28
< Unprotected mode
< Miscellaneous flags: 0x800000
---
> Maximize performance mode
> Miscellaneous flags: 0x822000
--//Control Seq=36119=0x8d17感觉在丢失redo文件后修改有点难度.先不管这些.
--//为了修改方便建立别名bvi80,这样每行80列,对应16个字符:
$ alias bvi80='env COLUMNS=80 bvi '
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo01.log => 当前是备用文件日志
$ bvi80 -b 512 -s 512 /u01/backup/20170301B/redo01.log => 好的日志文件
--//打开拷贝并且粘贴:
$ diff a1.txt a2.txt
1c1
< 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 9C B5 ................
--- ~~~~~
> 00000200 01 22 00 00 01 00 00 00 B7 02 00 00 00 80 A3 95 .,..............
3,4c3,4
< 00000220 00 00 00 00 2D 8D 00 00 00 90 01 00 00 02 00 00 ....-...........
~~~~
< 00000230 04 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
--- ~~
> 00000220 00 00 00 00 17 8D 00 00 00 90 01 00 00 02 00 00 ................
> 00000230 01 00 02 00 6E D8 B7 4F 00 00 00 00 00 00 00 00 ....n..O........
11c11
< 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 02 00 00 00 ..q5. ..........
--- ~~
> 000002A0 B0 1E 71 35 06 20 0E 00 00 00 00 00 01 00 00 00 ..q5. ..........
13c13
< 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 00 00 00 02 ................
---
> 000002C0 FF FF FF FF FF FF 00 00 00 00 00 00 01 00 00 02 ................
15c15 ~~
< 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 20 82 00 ....L..7..... ..
---
> 000002E0 03 00 00 00 4C BB DB 37 00 00 00 00 00 00 80 00 ....L..7........
--//注意看下划线的地方. 偏移14-15字节(从0算起)是检查和,0x230对应redo gruop#(我这里从备用日志拷贝过来,group#=4,要修改为1).
--//0x224-0x225对应的是Control Seq(0x2d8d) ,后面2个我猜测 偏移0x2AC,0x2CC位置.
--//group# 一定要修改 0x0400 => 0x0100.
hws 0x0200 => 0x0100
eot 0x0000 => 0x0100
--//计算检查和,很简单就是把改动再次做一次异或操作.
9cb5
0400
0100
0200
0100
0000
0100
----
9bb5 =>可以使用微软自带的计算器算.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.
--//检查完整性ok.
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
Compatibility Vsn = 186647552=0xb200400
Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
Activation ID=1337448558=0x4fb7d86e
Control Seq=36141=0x8d2d, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.175d9565 (13276910949) 02/28/2017 14:40:12
Disk cksum: 0xb59b Calc cksum: 0xb59b
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Maximize performance mode
Miscellaneous flags: 0x822000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 12e926f77b40c080dc716e8a264c329f
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 76Kb in 0.01s => 7.47 Mb/sec
Total redo bytes: 1023Kb Longest record: 2Kb, moves: 0/10 moved: 0Mb (0%)
Longest LWN: 2Kb, reads: 142
Last redo scn: 0x0003.175d95fb (13276911099)
Change vector header moves = 0/23 (0%)
----------------------------------------------
SYS@book> recover database until cancel ;
ORA-00279: change 13276910487 generated at 02/28/2017 14:40:06 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_694_896605872.dbf
ORA-00280: change 13276910487 for thread 1 is in sequence #694
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_694_896605872.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_695_896605872.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> recover database until cancel ;
ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276910949 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.
--OK!!
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//scn =13276911100(不再是13276911099) , fuzzy = YES.视乎redo文件头记录的这些控制某种行为.
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 dis: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//说明我的测试必须要改这里,不然使用recover database until cancel ;最后会报错.
4.继续:
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@book> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//这个是因为shutdown abort,redo03.log中记录的检查点状态还是STATUS=ACTIVE.
SYS@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 NO CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 693 52428800 512 1 YES INACTIVE 13276889179 2017-02-27 08:59:01 13276910486 2017-02-28 14:40:06
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES ACTIVE 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.
--//在备库执行:
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redostb02.log' validate;
System altered.
--//确认这个文件记录的是seq#=694的日志文件.
$ scp /mnt/ramdisk/book/redostb02.log oracle@192.168.100.78:/mnt/ramdisk/book/redo03.log
oracle@192.168.100.78's password:
redostb02.log 100% 50MB 50.0MB/s 00:01
$ bvi80 -b 512 -s 512 /mnt/ramdisk/book/redo03.log
--//仅仅需要修改0x230处 0x0500 => 0x0300,重新计算检查和.
2B19
0500
0300
----
2D19
--//其他不改也可以.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 58093
Session ID: 232 Serial number: 3
--//alert.log:
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_58093.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
USER (ospid: 58093): terminating the instance due to error 313
--//group#=2 被删除,不要紧!! alter database clear logfile group 2 ;看看.
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> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database open ;
Database altered.
--//OK,终于打开了,验证看看是否传输正常.
SYS@book> alter database clear logfile group 4 ;
Database altered.
SYS@book> alter database clear logfile group 5 ;
Database altered.
SYS@book> alter database clear logfile group 6 ;
Database altered.
SYS@book> alter database clear logfile group 7 ;
Database altered.
5.检查备库是否正常.
--//主库执行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
--//备库执行:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 15403 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 15405 IDLE LGWR 3 1 697 45 1 0
ARCH 14998 CLOSING ARCH 4 1 695 1 154 0
MRP0 15410 APPLYING_LOG N/A N/A 1 697 45 102400 0
--//seq#=697都传输应用过来,没有问题.
总结:
--//如果主库的异常关闭,可以使用备库的日志来恢复是不成问题的
1.确定那个备库日志接收当前.
2.备库拷贝过来:
--//这样就知道要修改的日志文件头位置:
--//group# 一定要修改!!
group# 0x0400 => 0x0100. 偏移0x230
hws 0x0200 => 0x0100 偏移0x2AC
eot 0x0000 => 0x0100 偏移0x2CC
cksum 0x9CB5 => ???
--//计算检查和,很简单就是把改动再次做一次异或操作.
3.虽然成功了,不具有普遍性,hws,eot表示什么不懂,而且我看到hws有不是0x0200的情况.
