[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特性以及具体位置,对应kcvfhsta=0x0(标识NO).测试是否修改标识,来欺骗oracle保持一致,而不需要open resetlogs打开.
1.环境:
SYS@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//测试前最好清除归档以及备份.注意seq#=694的归档在我的测试要保留下来.恢复需要.
--//主库:
$ /bin/rm /mnt/ramdisk/book/r*.log
2.前面的测试:
--//备库
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> 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
--//LAST_CHANGE#=13276911100.而是可以确定最新的接收日志的文件是/mnt/ramdisk/book/redostb01.log.
3.拷贝到主库恢复看看.
--//备库,这次我直接拷贝到归档目录,按照格式命令为1_695_896605872.dbf.
$ scp /mnt/ramdisk/book/redostb01.log oracle@192.168.100.78:/u01/app/oracle/archivelog/book/1_695_896605872.dbf
--//尝试在主库恢复看看.
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,留下下次测试.
$ rm /mnt/ramdisk/book/redo01.log
SYS@book> recover database using backup controlfile until change 13276911100;
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}
auto
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 155 change 13276911099 time 02/28/2017 14:42:35
ORA-00334: archived log: '/u01/app/oracle/archivelog/book/1_695_896605872.dbf'
ORA-01112: media recovery not started
--//使用using backup controlfile方式,但是注意最后ORA-01112: media recovery not started,也就是没有恢复.必须写成
SYS@book> recover database using backup controlfile until change 13276911099;
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}
auto
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'
ORA-01112: media recovery not started
--//还是注意看提示,虽然后面提示ORA-01112: media recovery not started,实际上已经恢复到13276911099.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ---------- --------------------------------------------------
1 13276911099 2017-02-28 14:42:35 0 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/users01.dbf
5 13276911099 2017-02-28 14:42:35 13274819965 2017-01-16 22:00:05 952916 952921 ONLINE /mnt/ramdisk/book/example01.dbf
6 13276911099 2017-02-28 14:42:35 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf
6 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- --------------------------------- ------------------------------
1 13276911099 2017-02-28 14:42:35 7 925702 ONLINE 838 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276911099 2017-02-28 14:42:35 1834 925702 ONLINE 827 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276911099 2017-02-28 14:42:35 923328 925702 ONLINE 748 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276911099 2017-02-28 14:42:35 16143 925702 ONLINE 833 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276911099 2017-02-28 14:42:35 952916 925702 ONLINE 744 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276911099 2017-02-28 14:42:35 13276257767 925702 ONLINE 212 YES /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//现在一致了,也就是恢复已经到底了,实际上你正常的/mnt/ramdisk/book/redo01.log,完成后有一个日志的切换过程.而这里没有.
4.使用bbed修改fuzzy=NO,欺骗数据库是干净的数据库看看.
--//注意1点system文件kcvfh.kcvfhsta=0x2004.
BBED> p dba 1,1 kcvfh.kcvfhsta
ub2 kcvfhsta @138 0x2004 (KCVFHOFZ)
--//其他是
BBED> p dba 2,1 kcvfh.kcvfhsta
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
--//建立脚本:
$ cat a.cmd
set count 8192
set width 210
assign dba 1,1 kcvfh.kcvfhsta=0x2000
sum dba 1,1 apply
assign dba 2,1 kcvfh.kcvfhsta=0x0
sum dba 2,1 apply
assign dba 3,1 kcvfh.kcvfhsta=0x0
sum dba 3,1 apply
assign dba 4,1 kcvfh.kcvfhsta=0x0
sum dba 4,1 apply
assign dba 5,1 kcvfh.kcvfhsta=0x0
sum dba 5,1 apply
assign dba 6,1 kcvfh.kcvfhsta=0x0
sum dba 6,1 apply
quit
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=a.cmd
...
--//输出略.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ---------- --------------------------------------------------
1 13276911099 2017-02-28 14:42:35 0 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/users01.dbf
5 13276911099 2017-02-28 14:42:35 13274819965 2017-01-16 22:00:05 952916 952921 ONLINE /mnt/ramdisk/book/example01.dbf
6 13276911099 2017-02-28 14:42:35 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf
6 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276911099 2017-02-28 14:42:35 7 925702 ONLINE 838 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276911099 2017-02-28 14:42:35 1834 925702 ONLINE 827 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276911099 2017-02-28 14:42:35 923328 925702 ONLINE 748 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276911099 2017-02-28 14:42:35 16143 925702 ONLINE 833 NO /mnt/ramdisk/book/users01.dbf USERS
5 13276911099 2017-02-28 14:42:35 952916 925702 ONLINE 744 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276911099 2017-02-28 14:42:35 13276257767 925702 ONLINE 212 NO /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//现在应该相当于冷备份,看看是否可以直接open.由于在线日志不存在,需要重建.注意执行顺序.
SYS@book> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database clear logfile group 3 ;
Database altered.
SYS@book> alter database clear unarchived logfile group 1 ;
Database altered.
SYS@book> alter database clear logfile group 4 ;
Database altered.
--//现在是见证奇迹的时候^_^.
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--//还是不行.哈哈,采用建立控制文件的方法.
5.修改文件头加1看看.
select 13276911100,trunc(13276911100/power(2,32)) scn_wrap,mod(13276911100,power(2,32)) scn_base from dual
13276911100 SCN_WRAP SCN_BASE
------------ ------------ ------------
13276911100 3 392009212
$ cat b.cmd
set count 8192
set width 210
assign dba 1,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 1,1 apply
assign dba 2,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 2,1 apply
assign dba 3,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 3,1 apply
assign dba 4,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 4,1 apply
assign dba 5,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 5,1 apply
assign dba 6,1 kcvfhckp.kcvcpscn.kscnbas=392009212
sum dba 6,1 apply
quit
--//执行如下:
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=b.cmd
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
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 838 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276911100 2017-02-28 14:42:35 1834 925702 ONLINE 827 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276911100 2017-02-28 14:42:35 923328 925702 ONLINE 748 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276911100 2017-02-28 14:42:35 16143 925702 ONLINE 833 NO /mnt/ramdisk/book/users01.dbf USERS
5 13276911100 2017-02-28 14:42:35 952916 925702 ONLINE 744 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276911100 2017-02-28 14:42:35 13276257767 925702 ONLINE 212 NO /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//scn=13276911100
6.建立控制文件看看:
SYS@book> alter database backup controlfile to trace as '/tmp/aaa1.txt' reuse;
Database altered.
--//关闭数据库执行如上脚本:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BOOK" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/mnt/ramdisk/book/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/mnt/ramdisk/book/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/mnt/ramdisk/book/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/mnt/ramdisk/book/system01.dbf',
'/mnt/ramdisk/book/sysaux01.dbf',
'/mnt/ramdisk/book/undotbs01.dbf',
'/mnt/ramdisk/book/users01.dbf',
'/mnt/ramdisk/book/example01.dbf',
'/mnt/ramdisk/book/tea01.dbf'
CHARACTER SET ZHS16GBK
;
--//需要使用RESETLOGS,使用NORESETLOGS报如下错误.
CREATE CONTROLFILE REUSE DATABASE "BOOK" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb01.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb02.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb03.log' SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/mnt/ramdisk/book/redostb04.log' SIZE 50M BLOCKSIZE 512 REUSE;
SYS@book> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ---------- ------------ ------------------ --------------------------
1 925702 2015-11-24 09:11:12 1 2013-08-24 11:37:30 PARENT 896605872 0 NO
2 13276911101 2017-03-03 15:04:35 925702 2015-11-24 09:11:12 CURRENT 937667075 1 NO
--//其他的INCARNATION#被清除了.
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 5581 IDLE ARCH N/A 0 0 0 0 0
RFS 5583 IDLE LGWR 2 1 2 39 1 0
ARCH 5273 CLOSING ARCH 6 1 1 1 419 0
MRP0 5579 APPLYING_LOG N/A N/A 1 2 39 102400 0
--//OK这样可以传输与应用.
SYS@book> select rowid,t.* from scott.t where rownum<=3;
ROWID ID NAME
------------------ ------------ -----------
AAAWD4AAEAAAAIjAAA 1 AAAAAAAAAA
AAAWD4AAEAAAAIjAAB 2 bbbbbbbbbb
AAAWD4AAEAAAAIjAAC 3 cccccccccc
SYS@book> update scott.t set name=upper(name) where id=2;
1 row updated.
SYS@book> commit;
Commit complete.
--//看看备库的情况:
SYS@bookdg> alter database recover managed standby database cancel;
Database altered.
SYS@bookdg> alter database open ;
Database altered.
SYS@bookdg> select rowid,t.* from scott.t where rownum<=3;
ROWID ID NAME
------------------ ------------ -----------
AAAWD4AAEAAAAIjAAA 1 AAAAAAAAAA
AAAWD4AAEAAAAIjAAB 2 BBBBBBBBBB
AAAWD4AAEAAAAIjAAC 3 cccccccccc
--//说明一切ok.
总结:
--//试验N次,不存在那个方法更好,只是自己在学习的过程中更加理解oracle的备份与回复.
--//这样做就是不要修改备库的scn-1.
--//实际上我测试方法许多比如提升scn好,也是不行.
