[20170301]dg環境下線上日誌損壞5.txt
[20170301]dg環境下線上日誌損壞5.txt
--//星期天跟別人聊天時提到的恢復方法,如果資料庫的線上日誌損壞,而日誌已經傳輸到dg上,是否可以將dg的日誌傳輸過來用於恢復.
--//問:使用備庫切換為主庫不就ok了嗎?
--//答:備庫的效能無法應付正常的業務.
--//^_^,這種情況倒是很常見,備庫僅僅起一個備份的作用.真正出了問題無法作為業務庫使用.透過測試驗證可行性:
1.環境:
--//檢查dg日誌傳輸與應用正常.這很關鍵,不然dg沒有最新的應用日誌.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//測試前最好清除歸檔以及備份.
SCOTT@book> create table t as select rownum id , lpad(chr(96+rownum),10,chr(96+rownum)) name from dual connect by level<=20;
Table created.
--session 1:
update t set name=upper(name) where id=1;
commit ;
SYS@book> select rowid,t.* from scott.t where rownum=1;
ROWID ID NAME
------------------ ---------- --------------------
AAAWD4AAEAAAAIjAAA 1 AAAAAAAAAA
SYS@book> @ &r/rowid AAAWD4AAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90360 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;
--//記錄在那個資料塊.
--session 2,修改不提交:
update t set name=upper(name) where id=2;
--session 3,選擇異常關機便於模擬增加恢復難度.
shutdown abort.
2.在主庫上與備庫做一個冷備份:(正常不要選擇這樣模式關機作為冷備份)
$ mkdir /u01/backup/20170301B
$ cp -ar /mnt/ramdisk/book/* /u01/backup/20170301B/ /* 複製檔案的目錄/u01/backup/20170301B. */
$ ll /mnt/ramdisk/book/r*.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:23:33 /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:08:17 /mnt/ramdisk/book/redo02.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:08:22 /mnt/ramdisk/book/redo03.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:02:16 /mnt/ramdisk/book/redostb01.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:02:16 /mnt/ramdisk/book/redostb02.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:02:16 /mnt/ramdisk/book/redostb03.log
-rw-r----- 1 oracle oinstall 52429312 2017-02-27 11:02:16 /mnt/ramdisk/book/redostb04.log
--//刪除日誌.
$ /bin/rm /mnt/ramdisk/book/r*.log
--//備庫的冷備份不再貼出.
3.開始恢復測試:
SYS@book> startup
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.
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
--//暫時禁止傳輸日誌.
SYS@book> alter system set log_archive_dest_state_2=defer;
System altered.
4.首先確定要恢復到scn是多少:
--//開啟備庫到mount:
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.
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.
--//我建議在備庫開啟flashback 功能.
SYS@bookdg> alter database flashback on;
Database altered.
SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redostb01.log' validate;
System altered.
--//檢查轉儲檔案:
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redostb01.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=4, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x2 eot: 0 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: 0xb59c Calc cksum: 0xb59c
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%)
----------------------------------------------
--//最後的Last redo scn: 0x0003.175d95fb (13276911099).也就是講恢復就是使用這個檔案.
4.複製到主庫恢復看看.
--//備庫
$ scp /mnt/ramdisk/book/redostb01.log oracle@192.168.100.78:/u01/backup
--//嘗試在主庫恢復看看.
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已經刪除.
--//還差sequence #695的日誌.
SYS@book> recover database until change 13276911099;
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
--//不能手動指定恢復日誌檔案.
SYS@book> recover database using backup controlfile 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}
/u01/backup/redostb01.log
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/backup/redostb01.log'
ORA-01112: media recovery not started
--//使用這樣恢復,注意要看提示,ORA-01112: media recovery not started.也就是這種異常關閉資料庫的redo,oracle根本不選擇恢復.
--//我在這裡測試N次,發現只能使用如下命令recover database using backup controlfile until change 13276911099;
--//要使用引數using backup controlfile,並且要使用change NNNN,指定scn!!!
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}
/u01/backup/redostb01.log
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
--//還是要仔細看提示,實際上已經做到scn=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 where file#=1;
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
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 13276911099 2017-02-28 14:42:35 7 925702 ONLINE 839 YES /mnt/ramdisk/book/system01.dbf SYSTEM
--//我僅僅查詢file#=1,主要全部查詢篇幅太大,而查詢file#=1也能說明問題.
--//如果執行recover database using backup controlfile until change 13276911100;(根本不執行恢復).
--//如果open resetlogs會報錯,但是實際上基本沒有問題,後面要提到加引數_allow_resetlogs_corruption=true;先放一下看看備庫.
5.備庫情況:
--//前面我執行
SYS@bookdg> alter database flashback on;
Database altered.
--//看alert檔案:
alter database flashback on
Starting background process RVWR
Wed Mar 01 08:38:19 2017
RVWR started with pid=21, OS id=32023
Allocated 3981120 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 13276911100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Completed: alter database flashback on
--//注意看當前備庫的scn=13276911100,比主庫的scn大1.
--//說明:在沒開啟flashback前我也做了恢復測試,內容如下:
--//啟動備庫到mount狀態,執行:(備庫要加standby引數)
SYS@bookdg> recover standby database until cancel;
ORA-00279: change 13276911100 generated at 02/28/2017 14:42:35 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf
ORA-00280: change 13276911100 for thread 1 is in sequence #695
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redostb01.log
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 155 change 13276911100 time 02/28/2017 14:42:35
ORA-00334: archived log: '/mnt/ramdisk/book/redostb01.log'
ORA-01112: media recovery not started
--//注意看提示並沒有啟動恢復,僅僅說明在block 155 change 13276911100中斷.
SYS@bookdg> recover standby database until change 13276911099;
Media recovery complete.
--//從這裡說明僅僅能恢復到scn=13276911099.而你實際檢視檢視v$v$datafile_header;
SYS@bookdg> 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 835 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//實際上現在資料檔案記錄的scn是13276911100.
6.主備庫差異:
--//從這裡可以看出主備庫檔案scn相差1,備庫的scn大於主庫的scn.如果主庫使用引數_allow_resetlogs_corruption=true;必須使用open resetlogs開啟,
--//因為redo檔案已經不存在.這樣會生成新的incarnation,而新的scn是當前主庫的scn+1開始.
--//視乎這個切換是從on disk rba scn+1開始.並不以資料檔案記錄的scn開始.
SELECT cpdrt ,
'0x'||to_char(cplrba_seq,'FMxxxxxxxx') || '.' || to_char(cplrba_bno,'FMxxxxxxxx')|| '.' || to_char(cplrba_bof,'FMxxxx') "low_rba16",
'0x'||to_char(cpodr_seq,'FMxxxxxxxx') || '.' || to_char(cpodr_bno,'FMxxxxxxxx') || '.' || to_char(cpodr_bof,'FMxxxx') "on_disk_rba16",
TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS') cpodt_on_disk_rba,
SYSDATE current_sysdate,
ROUND ( (SYSDATE - TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS')) * 86400,
2)
diff_date,
CPODS ,
CPHBT,
current_scn,
current_scn - cpods diff_scn,
indx
FROM x$kcccp, v$database
WHERE CPLRBA_SEQ <> 0;
檢查點佇列 on disk rba 檢查點佇列
髒塊數量 時間戳 當前時間 on disk rba scn 檢查點心跳
CPDRT low_rba16 on_disk_rba16 CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
91 0x2b6.3.0 0x2b7.9a.0 2017-02-28 14:42:34 2017-03-01 10:42:31 71997.00 13276911099 937460161 0 -13276911099 0
--//而備庫的這個scn依舊屬於前面的incarnation,無法繼續應用日誌(傳輸沒有問題).
--//要解決這個問題有可以修改備庫資料檔案的scn-1.再執行reset database to incarnation N;
--//注採用主庫scn+1的方式,不行.我測試多次,也許要修改on disk rba scn,也就是控制檔案的scn.不做這個測試.
7.open resetlogs開啟主庫看看:
SYS@book> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
--//^_^,不行.不知道為什麼實際上已經一致了不知道為什麼不能開啟.
SYS@book> create pfile='/tmp/book.ora' from spfile;
File created.
--加入1行:
*._allow_resetlogs_corruption=true;
SYS@book> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--//使用pfile引數檔案啟動:
SYS@book> startup mount pfile='/tmp/book.ora'
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 open resetlogs;
Database altered.
--//關閉資料庫,在使用spfile引數啟動資料庫,略.
SYS@book> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
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.
Database opened.
8.現在備庫是否接收日誌並應用:
--//主庫執行:
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.
--//檢查alert:
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (bookdg)
Wed Mar 01 10:51:35 2017
MRP0 started with pid=22, OS id=349
MRP0: Background Managed Standby Recovery process started (bookdg)
Wed Mar 01 10:51:36 2017
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archivelog/book
Wed Mar 01 10:51:37 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process 353
RFS[1]: Selected log 6 for thread 1 sequence 2 dbid 1337401710 branch 937478950
Wed Mar 01 10:51:37 2017
RFS[2]: Assigned to RFS process 355
RFS[2]: New Archival REDO Branch: 937478950 Current: 896605872
RFS[2]: Selected log 7 for thread 1 sequence 1 dbid 1337401710 branch 937478950
RFS[2]: Standby in the future of new recovery destinationBranch(resetlogs_id) 937478950
Incomplete Recovery SCN: 13276911100
Resetlogs SCN: 13276911100
Flashback database to SCN 13276911099 to follow new branch
Flashback database to SCN 13276911099 to follow new branch
RFS[2]: New Archival REDO Branch(resetlogs_id): 937478950 Prior: 896605872
RFS[2]: Archival Activation ID: 0x522677de Current: 0x4fb7d86e
RFS[2]: Effect of primary database OPEN RESETLOGS
RFS[2]: Managed Standby Recovery process is active
RFS[2]: Incarnation entry added for Branch(resetlogs_id): 937478950 (bookdg)
Wed Mar 01 10:51:38 2017
Setting recovery target incarnation to 3
Wed Mar 01 10:51:38 2017
Archived Log entry 3 added for thread 1 sequence 1 ID 0x522677de dest 1:
started logmerger process
Wed Mar 01 10:51:41 2017
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 13276911100) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_pr00_357.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Completed: alter database recover managed standby database using current logfile disconnect
$ cat /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_pr00_357.trc
....
*** 2017-03-01 10:51:41.698 4329 krsh.c
MRP0: Detected orphaned datafiles!
*** 2017-03-01 10:51:41.698 4329 krsh.c
Recovery will possibly be retried after flashback...
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
*** 2017-03-01 10:51:41.721 4329 krsh.c
Managed Standby Recovery not using Real Time Apply
RMAN> list incarnation ;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 CURRENT 13276911100 2017-03-01 10:49:10
--//可以看出reset scn=13276911100.而資料檔案scn=13276911100.
SYS@bookdg> 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 835 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//也就是必須將資料檔案的scn-1. 13276911100-1=13276911099.
8.使用bbed修改備庫的scn=13276911099.
SYS@bookdg> @ &r/tx 13276911099
select 13276911099,trunc(13276911099/power(2,32)) scn_wrap,mod(13276911099,power(2,32)) scn_base from dual
13276911099 SCN_WRAP SCN_BASE
------------ ------------ ------------
13276911099 3 392009211
--//關閉主資料庫:
SYS@book> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
$ cat a.cmd
set count 8192
set width 210
assign dba 1,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 1,1 apply
assign dba 2,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 2,1 apply
assign dba 3,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 3,1 apply
assign dba 4,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 4,1 apply
assign dba 5,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 5,1 apply
assign dba 6,1 kcvfhckp.kcvcpscn.kscnbas=392009211
sum dba 6,1 apply
quit
$ cat bbed.par
blocksize=8192
listfile=$HOME/bbed/filelist.txt
mode=edit
PASSWORD=blockedit
--//filelist.txt內容有可以執行生成:
SYS@bookdg> select file#||' '||name c60 from v$dbfile order by file#;
C60
------------------------------------------------------------
1 /mnt/ramdisk/book/system01.dbf
2 /mnt/ramdisk/book/sysaux01.dbf
3 /mnt/ramdisk/book/undotbs01.dbf
4 /mnt/ramdisk/book/users01.dbf
5 /mnt/ramdisk/book/example01.dbf
6 /mnt/ramdisk/book/tea01.dbf
6 rows selected.
--//關於bbed的相關內容可以參看我以前的文章.
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=a.cmd
..
--//執行過程略.
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> 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 13276911099 2017-02-28 14:42:35 7 925702 ONLINE 835 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--ok!!
RMAN> list incarnation ;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 CURRENT 13276911100 2017-03-01 10:49:10
--//現在看看備庫是否接收日誌並應用:
--//主庫執行:
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 454 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 456 IDLE LGWR 1 1 4 8 1 0
ARCH 428 CLOSING ARCH 6 1 3 1 117 0
MRP0 433 WAIT_FOR_LOG N/A N/A 1 4 0 0 0
--//MRP0已經應用過去,SEQ#=4.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ---------------- ---------- ---------- ---------- ----------
RFS 461 IDLE ARCH N/A 0 0 0 0 0
RFS 456 IDLE LGWR 1 1 4 100 1 0
ARCH 428 CLOSING ARCH 6 1 3 1 117 0
MRP0 433 APPLYING_LOG N/A N/A 1 4 100 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
--//結果也正確.
總結:
1.不推薦這樣的恢復方式,僅僅測試與學習.
2.首先確定備庫是那個接收日誌的檔案.
3.推薦備庫開啟flashback on(而不是在出問題時開啟),這樣可以回滾到特定的scn,注意一個細節,這個我也測試(不再貼出),另外注意閃
回區設定大一些,僅僅保留長時間的閃回日誌. 另外注意一個細節
SYS@bookdg> flashback database to scn 13276911099;
Flashback complete.
--//看資料檔案記錄的scn是13276911100. 比閃回的scn多1.也就是前面的例子如果要到scn=13276911099,實際上執行的是:
flashback database to scn 13276911098;
--//oracle 真奇怪....例子:
SYS@bookdg> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
13276911100 2017-03-01 10:59:27 1440 104857600 310886400
SYS@bookdg> alter database recover managed standby database cancel;
Database altered.
SYS@bookdg> flashback database to scn 13276911200;
Flashback complete.
SYS@bookdg> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
1 13276911201 2017-03-01 10:49:12 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
SYS@bookdg> 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 13276911201 2017-03-01 10:49:12 7 13276911100 ONLINE 835 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//flashback scn=13276911200,而控制檔案與資料檔案記錄的scn=13276911201.
4.檢查日誌完成性,避免問題擴大:
alter system dump logfile '/mnt/ramdisk/book/redostb01.log' validate;
5.要在主庫應用日誌,必須執行:
recover database using backup controlfile until change 13276911099;
--其他方式都是不行的.
recover database using backup controlfile until change cancel;
recover database until change 13276911099;
recover database using backup controlfile until change NNNN;
--NNN 大於提示ORA-00353: log corruption near block 155 change 13276911099 time 02/28/2017 14:42:35裡面提到scn,都不執行.
6.我的測試主庫一定要用_allow_resetlogs_corruption=true;開啟:
*._allow_resetlogs_corruption=true;
7.備庫資料檔案的scn要-1:
--//使用bbed修改,可以參考前面的操作.
--//採用主庫scn+1的方式甚至+2的方式都不行,v$database_incarnation記錄的RESETLOGS_CHANGE#都是13276911100.
8.一定要認真看提示與alert以及轉儲檔案資訊,這是我測試最大的收穫.
9.我這些測試重複了N次,往往出現問題時情況N複雜.僅僅提供一些恢復思路.累,測試3天.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2134481/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170303]dg環境下線上日誌損壞8.txt
- [20170307]dg環境下線上日誌損壞12.txt
- [20170309]dg環境下線上日誌損壞13.txt
- [20170310]dg環境下線上日誌損壞14.txt
- 線上日誌損壞
- 線上日誌檔案損壞恢復方法
- 前線上日誌檔案損壞與ora-600 [4000]處理
- redo日誌損壞
- 物理DG從庫損壞後的重建
- rac+dg環境刪除歸檔日誌
- ASM 仲裁盤OCR DG損壞，重建步驟ASM
- 當前日誌損壞的案例
- 當前日誌損壞的案例(轉)
- 損壞聯機日誌 恢復
- 非歸檔模式下線上日誌檔案破壞後例項恢復案例模式
- 重做日誌檔案損壞測試
- 聯機日誌檔案損壞問題
- INACTIVE日誌組損壞的修復
- 聯機日誌損壞恢復實驗
- 損壞聯機日誌的恢復方法
- 當前日誌組全部損壞的恢復
- 當前聯機日誌檔案損壞
- 每天日誌上T的環境用duplicate做DG的注意事項
- Oracle DG環境中的管理操作Oracle
- RAC環境下的redo日誌組重建
- DG環境下重新構建物理備庫oracle12COracle
- linux下修復磁碟損壞Linux
- Oracle 線上日誌管理Oracle
- Oracle RAC 環境下的連線管理Oracle
- downstream環境下archive程式停止傳輸日誌Hive
- 控制檔案損壞重建實驗（下）
- win下oracle9201當前日誌組損壞的修復過程Oracle
- 03【線上日誌分析】之hadoop-2.7.3編譯和搭建叢集環境(HDFS HA,Yarn HA)Hadoop編譯Yarn
- 非線上日誌丟失
- RAC 線上日誌的管理
- Window離線環境下如何安裝pyhanlpHanLP
- 【RAC】RAC環境下歸檔日誌格式約定
- windows環境下Django環境搭建WindowsDjango