[20170307]dg环境下在线日志损坏12.txt

[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的情况.

时间： 2024-10-27 17:27:10

[20170307]dg环境下在线日志损坏12.txt的相关文章

[20170309]dg环境下在线日志损坏13.txt

[20170309]dg环境下在线日志损坏13.txt http://blog.itpub.net/267265/viewspace-2134665/ http://blog.itpub.net/267265/viewspace-2134481/ --//按照如下链接,拷贝备用日志到主库,修改文件头偏移0x230 日志组号.以及hws,eot对应位置,欺骗oracle是正常的日志文件. http://blog.itpub.net/267265/viewspace-2134816/ --//看了一

[20170303]dg环境下在线日志损坏8.txt

[20170303]dg环境下在线日志损坏8.txt --前面的测试,链接http://blog.itpub.net/267265/viewspace-2134481/ --前面的测试必须使用recover database using backup controlfile until change 13276911099; 才能恢复到结尾. --但是由于主备库scn相差1,在open resetlog时备库的数据文件头scn号减1,采用应用日志. --前面学习了解文件头fuzzy特性以及具体位

视角 | 多容器环境下的日志管理难？有人做起了这个生意

本文讲的是视角 | 多容器环境下的日志管理难?有人做起了这个生意,[编者的话]本文介绍了一个新的工具SPM,它用于解决在多容器环境下日志管理所遇到的问题,同时它整合了多种功能,避免了以往需要安装多种工具的麻烦,配合Kibana的展示功能,使得该工具还是值得一试的. 在微服务流行的今天,日志路由和解析的传统静态配置方法已经有点吃力:事实上,它还带来了额外的复杂度和资源消耗.相对的,这使得不能运行在单机上的微服务的数量降低了. 在SPM for Docker整合的日志管理功能中,对微服务进行了支持,

lnmp环境下网站日志进行分割配置

这个教程适用于Nginx用户及lnmp用户参考的参考,其他环境下,大叔不鸡道哈! 首先我们得用root 登陆执行 #!/bin/bash #function:cut nginx log files for lnmp v0.5 and v0.6 #set the path to nginx log files log_files_path="/home/wwwlogs/" log_files_dir=${log_files_path}$(date -d "yesterday&q

【DG】DG环境的日常巡检

DG环境的日常巡检 目录 1.DG环境的日常巡检4 1.1.主库环境检查4 1.1.1.主库实例启动状态检查4 1.1.2.主库启动模式检查4 1.1.3.主库DG环境的保护模式检查4 1.1.4.主库用于控制日志同步的参数检查4 1.1.5.主库查看是否开启强制日志功能5 1.1.6.主库上查看设置的归档日志路径是否可用5 1.1.7.主库上查询归档日志的应用情况6 1.1.8.主库上查看DG环境进程的状态6 1.1.9.主库上查看DG的状态信息7 1.1.10.主库SWITCH OVER角色

如何使用线程局部存储实现多线程下的日志系统

概述 通常来说,在应用程序中需要日志来记录程序运行的状态,以便后期问题的跟踪定位.在日志系统的设计中,通常会有一个总的日志系统来统一协调这些日志的设置如位置.输出级别和内容等.在多线程编程中,当每个线程都需要输出日志时,因为要考虑线程间的同步,日志系统的设计更加复杂. 在单线程应用程序中,通常使用一个日志单例向某个文件输出应用运行过程中的重要日志信息,但是在多线程环境中,这样做显然不好,因为各个线程打印出的日志会错综复杂从而使得日志文件不容易阅读和跟踪.比较好的办法是主线程记录自己的日志,各个子

【故障处理】DG环境主库丢失归档情况下数据文件的恢复

[故障处理]DG环境主库丢失归档情况下数据文件的恢复 1  BLOG文档结构图     2  前言部分   2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① BBED的编译 ② BBED修改文件头让其跳过归档从而可以ONLINE(重点) ③ OS命名格式转换为ASM的命名格式 ④ DG环境中备库丢失数据文件的情况下的处理过程(重点) ⑤ 数据文件OFFLINE后应立即做一次RECOVER操作 ⑥ BBED环境

link环境下制作一款《订餐软件》，在线订餐怎么直接实现在线付款功能呢？

问题描述 link环境下制作一款<订餐软件>,在线订餐怎么直接实现在线付款功能呢? link环境下制作一款<订餐软件>,在线订餐怎么直接实现在线付款功能呢? 解决方案 自己找第三方的支付,一般都会有api使用说明的,但是也需要手续费什么的

在PHP环境下配置在线编辑器FCKeditor

在线 [摘 要] 在线编辑器2.0版确实比1.6版有了许多的改进.首先是FCKeditor的文件结构更加清晰,可以更方便地将其部署在自己的系统中.另外2.0版终于支持了Firefox 1.0浏览器,这将为FCKeditor赢得更多的使用者. 在线编辑器FCKeditor 2.0PHP环境下试用小记 一.简介 2004年11月30日推出了FCKeditor 2.0 RC1版,据其官方网站称:这是FCKeditor 2.0版的第一个稳定版本.大家现在可以考虑正式使用它了.目前支持的后台语言有ASP.