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

REDO ( 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            0

FULL 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.00

v$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.dbf

6 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.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        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 #694

Specify 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.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        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.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        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 ):
PROMPT

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;

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;