[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天.