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