本文共 15328 字，大约阅读时间需要 51 分钟。
[20170302]异常恢复scn到那里3.txt
--//如果oracle数据库异常关闭,打开数据库自动执行实例恢复,这个恢复scn到那里呢?
--//通过例子说明:实际上http://blog.itpub.net/267265/viewspace-2134551/链接已经提到,重复测试:1.环境:
SYS@book> @ &r/ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production--//异常关闭数据库,做好冷备份,启动到mount状态.
SYS@book> @ &r/checkpoint
REDO:检查点队列
检查点队列 on disk rba 检查点队列 脏块数量 时间戳 当前时间 on disk rba scn 检查点心跳 CPDRT low_rba on_disk_rba CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX ------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------ 91 694.3.0 695.154.0 2017-02-28 14:42:34 2017-03-02 16:03:50 177676.00 13276911099 937571354 0 -13276911099 0REDO ( Hexadecimal ):
检查点队列
检查点队列 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-02 16:03:50 177676.00 13276911099 937571354 0 -13276911099 0FULL CHECKPOINT:
rtckp_rba RTCKP_SCN CURRENT_SCN DIFF_SCN RTCKP_TIM SYSDATE DIFF_DATE
-------------------- ---------------- ------------ ------------ ------------------- ------------------- ---------- 694.2.16 13276910487 0 -13276910487 2017-02-28 14:40:06 2017-03-02 16:03:50 177824.00v$instance_recovery:
INST_ID ACTUAL_REDO_BLKS TARGET_REDO_BLKS 90%_blks TIMEOUT_BLKS TARGET_MTTR ESTIMATED_MTTR
------------ ---------------- ---------------- ------------ ------------ ------------ -------------- 1 0 0 0--//我前面提到仅仅能恢复到scn=13276911099.
2.转储在线日志观察:
SYS@book> @ &r/logfile GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ------ ------ ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- 1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 695 52428800 512 1 NO CURRENT 13276910949 2017-02-28 14:40:12 2.814750E+14 2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 693 52428800 512 1 YES INACTIVE 13276889179 2017-02-27 08:59:01 13276910486 2017-02-28 14:40:06 3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES ACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12 4 STANDBY /mnt/ramdisk/book/redostb01.log NO 5 STANDBY /mnt/ramdisk/book/redostb02.log NO 6 STANDBY /mnt/ramdisk/book/redostb03.log NO 7 STANDBY /mnt/ramdisk/book/redostb04.log NO 7 rows selected.--//STATUS=CURRENT 是/mnt/ramdisk/book/redo01.log.
SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.--//转储内容:
DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~====> 说明异常关闭,scn无穷大. 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=36119=0x8d17, File size=102400=0x19000 File Number=1, Blksiz=512, File Type=2 LOG descrip:"Thread 0001, Seq# 0000000695, SCN 0x0003175d9565-0xffffffffffff" thread: 1 nab: 0xffffffff seq: 0x000002b7 hws: 0x1 eot: 1 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: 0x95a3 Calc cksum: 0x95a3 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 Unprotected mode Miscellaneous flags: 0x800000 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) => 这里也说明最后的scn=13276911099. Change vector header moves = 0/23 (0%) ----------------------------------------------3.观察恢复是scn变化:
SYS@book> recover database 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'SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME ----- ------------------ ------------------- --------------------- ------------------- ------------ --------- --------------- -------------- ---------- -------------------------------- 1 13276911099 2017-02-28 14:42:35 0 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbf 2 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/sysaux01.dbf 3 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/undotbs01.dbf 4 13276911099 2017-02-28 14:42:35 0 925701 925702 ONLINE /mnt/ramdisk/book/users01.dbf 5 13276911099 2017-02-28 14:42:35 13274819965 2017-01-16 22:00:05 952916 952921 ONLINE /mnt/ramdisk/book/example01.dbf 6 13276911099 2017-02-28 14:42:35 0 0 0 ONLINE /mnt/ramdisk/book/tea01.dbf6 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME ----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------- ------------------------------ 1 13276911099 2017-02-28 14:42:35 7 925702 ONLINE 838 YES /mnt/ramdisk/book/system01.dbf SYSTEM 2 13276911099 2017-02-28 14:42:35 1834 925702 ONLINE 827 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX 3 13276911099 2017-02-28 14:42:35 923328 925702 ONLINE 748 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1 4 13276911099 2017-02-28 14:42:35 16143 925702 ONLINE 833 YES /mnt/ramdisk/book/users01.dbf USERS 5 13276911099 2017-02-28 14:42:35 952916 925702 ONLINE 744 YES /mnt/ramdisk/book/example01.dbf EXAMPLE 6 13276911099 2017-02-28 14:42:35 13276257767 925702 ONLINE 212 YES /mnt/ramdisk/book/tea01.dbf TEA 6 rows selected. --//FUZZY=YES,也就是恢复没有结束.SYS@book> recover database until change 13276911100;
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'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 13276911100 2017-02-28 14:42:35 0 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbfSYS@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 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 838 YES /mnt/ramdisk/book/system01.dbf SYSTEM--//scn=13276911100.
--///执行看提示实际上已经到底了,oracle依旧提示无法OPEN RESETLOGS. FUZ=YES.SYS@book> recover database until cancel;
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 #694Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 13276910949 generated at 02/28/2017 14:40:12 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_695_896605872.dbf ORA-00280: change 13276910949 for thread 1 is in sequence #695 ORA-00278: log file '/u01/app/oracle/archivelog/book/1_694_896605872.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_695_896605872.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 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'
--//缺seq=695继续.
SYS@book> recover database 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} /mnt/ramdisk/book/redo01.log Log applied. Media recovery complete.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 13276911100 2017-02-28 14:42:35 0 13276911100 2017-02-28 14:42:35 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbfSYS@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 13276911100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM--//scn=13276931100. FUZ = NO.
4.重复测试:
SYS@book> recover database ;
Media recovery complete.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 13276931100 2017-02-28 14:42:35 0 13276931100 2017-02-28 14:42:35 925701 925702 SYSTEM /mnt/ramdisk/book/system01.dbfSYS@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 13276931100 2017-02-28 14:42:35 7 925702 ONLINE 839 NO /mnt/ramdisk/book/system01.dbf SYSTEM--//SCN=13276931100,也是异常关闭恢复最后on disk rba scn+1,或者就是看当前日志最后的scn+1.
5.在dg环境下在线日志损坏的测试中,链接http://blog.itpub.net/267265/viewspace-2134481/
--//使用传输的备用日志仅仅恢复到13276911099.这样导致主备数据库的scn差1,导致open resetlogs,无法继续应用日志.--//附上:checkpoint.sql脚本: $ cat checkpoint.sql column low_rba format a20 column low_rba16 format a20 column on_disk_rba format a20 column on_disk_rba16 format a20 column rtckp_rba format a20 column diff_date format 999999.99 column CPOSD_ono_disk_rba_scn format 99999999999999999999999999999999 column cpdrt heading "检查点队列|脏块数量|CPDRT" column cpodt_on_disk_rba heading "检查点队列|on disk rba|时间戳|CPODT" column cpods heading "检查点队列|on disk rba scn|CPODS" column cphbt heading "检查点心跳|CPHBT" column current_sysdate heading "当前时间|SYSDATE" set num 12 PROMPT PROMPT REDO: PROMPT SELECT cpdrt , cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba", 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;
PROMPT
PROMPT REDO ( Hexadecimal ): PROMPTSELECT 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;PROMPT
PROMPT FULL CHECKPOINT: PROMPT SELECT rtckp_rba_seq || '.' || rtckp_rba_bno || '.' || rtckp_rba_bof "rtckp_rba", rtckp_scn, current_scn, current_scn - rtckp_scn diff_scn, TO_DATE (rtckp_tim, 'MM-DD-YYYY HH24:MI:SS') rtckp_tim, SYSDATE, ROUND ( (SYSDATE - TO_DATE (rtckp_tim, 'MM-DD-YYYY HH24:MI:SS')) * 86400, 2) diff_date FROM x$kccrt, v$database;PROMPT PROMPT v$instance_recovery: PROMPT SELECT inst_id, actual_redo_blks, target_redo_blks, LOG_FILE_SIZE_REDO_BLKS AS "90%_blks", LOG_CHKPT_TIMEOUT_REDO_BLKS AS timeout_blks, target_mttr, estimated_mttr FROM gv$instance_recovery;
转载地址：http://luuix.baihongyu.com/