[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;

--//檢查轉儲檔案:

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 [email protected]:/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-01112: media recovery not started

--//檔案/mnt/ramdisk/book/redo01.log已經删除.

--//還差sequence #695的日志.

--//不能手動指定恢複日志檔案.

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

/u01/backup/redostb01.log

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.也就是這種異常關閉資料庫的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-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'

--//還是要仔細看提示,實際上已經做到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.備庫情況:

--//前面我執行

--//看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-00280: change 13276911100 for thread 1 is in sequence #695

/mnt/ramdisk/book/redostb01.log

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'

--//注意看提示并沒有啟動恢複,僅僅說明在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;

    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:

--//^_^,不行.不知道為什麼實際上已經一緻了不知道為什麼不能打開.

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'

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

--//關閉資料庫,在使用spfile參數啟動資料庫,略.

Database closed.

Database opened.

8.現在備庫是否接收日志并應用:

--//主庫執行:

SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;

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 ;

--//檢查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

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

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

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

*** 2017-03-01 10:51:41.721 4329 krsh.c

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.

--//也就是必須将資料檔案的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

--//關閉主資料庫:

$ 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

    1        13276911099 2017-02-28 14:42:35                7            925702 ONLINE                  835 NO  /mnt/ramdisk/book/system01.dbf                     SYSTEM

--ok!!

--//現在看看備庫是否接收日志并應用:

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.

RFS           461 IDLE         ARCH     N/A            0          0          0          0          0

RFS           456 IDLE         LGWR     1              1          4        100          1          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;

SYS@bookdg> flashback database to scn 13276911200;

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;打開:

7.備庫資料檔案的scn要-1:

--//使用bbed修改,可以參考前面的操作.

--//采用主庫scn+1的方式甚至+2的方式都不行,v$database_incarnation記錄的RESETLOGS_CHANGE#都是13276911100.

8.一定要認真看提示與alert以及轉儲檔案資訊,這是我測試最大的收獲.

9.我這些測試重複了N次,往往出現問題時情況N複雜.僅僅提供一些恢複思路.累,測試3天.