[20200309]数据库异常关闭恢复的终点.txt

--//在链接:http://blog.itpub.net/267265/viewspace-2638170/=>[20190312]关于增量检查点的疑问(补充).txt
--//我在里面提到数据库异常关闭恢复的起点是low_rba到on_disk_rba作为恢复终点.

--//实际上可能更新这些信息可能慢于写入日志,这样可能出现恢复终点大于on_disk_rba的情况.特别是生产系统出现异常关闭的情况
--//通过例子验证:

1.环境:
SYS@book> @ 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


2.测试:
--//以sys用户执行:
$ cat ac.txt
@check.sql
create table scott.t tablespace users as select * from all_objects;
shutdown abort

SYS@book> @ ac.txt
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE                    CPDRT
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
822.802.0            822.904.0            13277803650      2020-03-09 09:39:23 822.801.16            13277803347      2020-03-09 09:39:11                          303             2 2020-03-09 09:39:26           32


Table created.
ORACLE instance shut down.


3.测试:

SYS@book> startup mount;
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.

SYS@book> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE                    CPDRT
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
822.802.0            822.904.0            13277803650      2020-03-09 09:39:23 822.801.16            13277803347      2020-03-09 09:39:11                          303             2 2020-03-09 09:40:05           32

--//on_disk_rba=822.904.0.明显不可能，因为我在关闭前建立表t在users表空间里面。

SYS@book> alter database open ;
Database altered.

SYS@book> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(CP full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group SYSDATE                    CPDRT
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
823.3.0              823.123.0            13277823928      2020-03-09 09:41:02 823.2.16              13277823734      2020-03-09 09:41:01                          194             3 2020-03-09 09:41:05           41

--//检查alert*.log:
[2020-03-09 09:41:00] Mon Mar 09 09:41:00 2020
[2020-03-09 09:41:00] alter database open
[2020-03-09 09:41:00] Beginning crash recovery of 1 threads
[2020-03-09 09:41:01]  parallel recovery started with 23 processes
[2020-03-09 09:41:01] Started redo scan
[2020-03-09 09:41:01] Completed redo scan
[2020-03-09 09:41:01]  read 10237 KB redo, 90 data blocks need recovery
[2020-03-09 09:41:01] Started redo application at
[2020-03-09 09:41:01]  Thread 1: logseq 822, block 802
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//等于low_rba=822.802.0.恢复起点。
[2020-03-09 09:41:01] Recovery of Online Redo Log: Thread 1 Group 2 Seq 822 Reading mem 0
[2020-03-09 09:41:01]   Mem# 0: /mnt/ramdisk/book/redo02.log
[2020-03-09 09:41:01] Completed redo application of 0.12MB
[2020-03-09 09:41:01] Completed crash recovery at
[2020-03-09 09:41:01]  Thread 1: logseq 822, block 21276, scn 13277823731
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[2020-03-09 09:41:01]  90 data blocks read, 90 data blocks written, 10237 redo k-bytes read
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--// 21276-802 = 20474,20474*512/1024 = 10237 K。
--//可以发现恢复到 822.21276.0. 也就是恢复尽可能的读取日志，直到不能读为止。而不是到on_disk_rba=822.904.0结束。
[2020-03-09 09:41:01] Mon Mar 09 09:41:00 2020
[2020-03-09 09:41:01] LGWR: STARTING ARCH PROCESSES
[2020-03-09 09:41:01] Mon Mar 09 09:41:00 2020
[2020-03-09 09:41:01] ARC0 started with pid=45, OS id=2352
[2020-03-09 09:41:02] ARC0: Archival started
[2020-03-09 09:41:02] LGWR: STARTING ARCH PROCESSES COMPLETE
[2020-03-09 09:41:02] ARC0: STARTING ARCH PROCESSES
[2020-03-09 09:41:02] Thread 1 advanced to log sequence 823 (thread open)
[2020-03-09 09:41:02] Thread 1 opened at log sequence 823
--//切换使用新的日志seq=823，oracle这样做的好处是避免可能的日志覆盖情况。
[2020-03-09 09:41:02]   Current log# 3 seq# 823 mem# 0: /mnt/ramdisk/book/redo03.log
[2020-03-09 09:41:02] Successful open of redo thread 1
[2020-03-09 09:41:02] MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
[2020-03-09 09:41:02] Mon Mar 09 09:41:01 2020
[2020-03-09 09:41:02] SMON: enabling cache recovery
[2020-03-09 09:41:02] Mon Mar 09 09:41:01 2020
[2020-03-09 09:41:02] ARC1 started with pid=46, OS id=2354
[2020-03-09 09:41:02] [2304] Successfully onlined Undo Tablespace 2.
[2020-03-09 09:41:02] Undo initialization finished serial:0 start:2332169638 end:2332169688 diff:50 (0 seconds)
[2020-03-09 09:41:02] Verifying file header compatibility for 11g tablespace encryption..
[2020-03-09 09:41:02] Verifying 11g file header compatibility for tablespace encryption completed
[2020-03-09 09:41:02] SMON: enabling tx recovery
[2020-03-09 09:41:02] Database Characterset is ZHS16GBK
[2020-03-09 09:41:02] No Resource Manager plan active
[2020-03-09 09:41:02] replication_dependency_tracking turned off (no async multimaster replication found)
[2020-03-09 09:41:02] WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
[2020-03-09 09:41:03] Completed: alter database open
[2020-03-09 09:41:03] Mon Mar 09 09:41:02 2020
[2020-03-09 09:41:03] db_recovery_file_dest_size of 61440 MB is 0.00% used. This is a
[2020-03-09 09:41:03] user-specified limit on the amount of space that will be used by this
[2020-03-09 09:41:03] database for recovery-related files, and does not reflect the amount of
[2020-03-09 09:41:03] space available in the underlying filesystem or ASM diskgroup.
[2020-03-09 09:41:03] Mon Mar 09 09:41:02 2020
[2020-03-09 09:41:03] Starting background process CJQ0
[2020-03-09 09:41:03] Mon Mar 09 09:41:02 2020
[2020-03-09 09:41:03] CJQ0 started with pid=48, OS id=2370
[2020-03-09 09:41:03] ARC1: Archival started
[2020-03-09 09:41:03] ARC0: STARTING ARCH PROCESSES COMPLETE
[2020-03-09 09:41:03] ARC0: Becoming the 'no FAL' ARCH
[2020-03-09 09:41:03] ARC0: Becoming the 'no SRL' ARCH
[2020-03-09 09:41:03] ARC1: Becoming the heartbeat ARCH
[2020-03-09 09:41:04] Archived Log entry 1487 added for thread 1 sequence 822 ID 0x4fb7d86e dest 1:
[2020-03-09 09:41:04] Mon Mar 09 09:41:03 2020
[2020-03-09 09:41:04] Incremental checkpoint up to RBA [0x337.3.0], current log tail at RBA [0x337.7b.0]
--// 0x7b = 123

SYS@book> select count(*) from scott.t;
    COUNT(*)
------------
       85032

SYS@book> select count(*) from all_objects;
    COUNT(*)
------------
       85032

--//可以发现建立的表T存在。

4.总结：
--//数据库异常关闭恢复的起点是low_rba，终点至少到on_disk_rba,特别在生产系统完成可能超过on_disk_rba，oracle尽量的读取满足需求的日志，
--//日志文件里面记录seq号。最后都是切换使用新的日志。
--//大家可以对比http://blog.itpub.net/267265/viewspace-2638170/的测试。

5.脚本check.sql:
$ cat check.sql
column "full checkpoint_rba" format a21
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 9999999.99
rem 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
SELECT b.cplrba_seq || '.' || b.cplrba_bno || '.' || b.cplrba_bof "low_rba"
      ,b.cpodr_seq || '.' || b.cpodr_bno || '.' || b.cpodr_bof "on_disk_rba"
      ,b.CPODS "on_disk_rba_scn(CPODS)"
      ,TO_DATE (b.CPODT, 'MM-DD-YYYY HH24:MI:SS') "on_disk_rba_time(CPODT)"
      ,a.rtckp_rba_seq || '.' || a.rtckp_rba_bno || '.' || a.rtckp_rba_bof
          "full checkpoint_rba"
      ,a.rtckp_scn "full_checkpoint(rtckp_scn)"
      ,TO_DATE (a.rtckp_tim, 'MM-DD-YYYY HH24:MI:SS')
          "full_checkpoint_time_rtckp_tim"
      ,b.CPODS - a.rtckp_scn "diff_scn(on_disk_rdb-ch_scn)"
      ,a.rtcln "current_group"
      ,sysdate current_sysdate
      ,CPDRT
  FROM x$kccrt a, x$kcccp b
 WHERE a.rtnum = b.cptno AND A.INST_ID = b.inst_id;