[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 [email protected]:/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
ORA-00341: log 1 of thread 1, wrong log # 4 in header
--//檔案頭識别錯誤,是否可以修改這個欺騙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
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'
--//使用using backup controlfile方式,但是注意最後ORA-01112: media recovery not started,也就是沒有恢複.必須寫成
SYS@book> recover database using backup controlfile until change 13276911099;
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,實際上已經恢複到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
--//現在一緻了,也就是恢複已經到底了,實際上你正常的/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
...
--//輸出略.
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
--//現在應該相當于冷備份,看看是否可以直接open.由于線上日志不存在,需要重建.注意執行順序.
SYS@book> alter database clear logfile group 2 ;
Database altered.
SYS@book> alter database clear logfile group 3 ;
SYS@book> alter database clear unarchived logfile group 1 ;
SYS@book> alter database clear logfile group 4 ;
--//現在是見證奇迹的時候^_^.
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
assign dba 1,1 kcvfhckp.kcvcpscn.kscnbas=392009212
assign dba 2,1 kcvfhckp.kcvcpscn.kscnbas=392009212
assign dba 3,1 kcvfhckp.kcvcpscn.kscnbas=392009212
assign dba 4,1 kcvfhckp.kcvcpscn.kscnbas=392009212
assign dba 5,1 kcvfhckp.kcvcpscn.kscnbas=392009212
assign dba 6,1 kcvfhckp.kcvcpscn.kscnbas=392009212
--//執行如下:
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=b.cmd
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
--//scn=13276911100
6.建立控制檔案看看:
SYS@book> alter database backup controlfile to trace as '/tmp/aaa1.txt' reuse;
--//關閉資料庫執行如上腳本:
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
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
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
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;
--//備庫執行:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
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;
SYS@bookdg> alter database open ;
SYS@bookdg> select rowid,t.* from scott.t where rownum<=3;
AAAWD4AAEAAAAIjAAB 2 BBBBBBBBBB
--//說明一切ok.
總結:
--//試驗N次,不存在那個方法更好,隻是自己在學習的過程中更加了解oracle的備份與回複.
--//這樣做就是不要修改備庫的scn-1.
--//實際上我測試方法許多比如提升scn好,也是不行.
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)