How to get SCN ,TIMESTAMP ,CHECKPOINT

select dbms_flashback.get_system_change_number ,timestamp_to_scn(sysdate) from dual;

select a as scn_dec ,'0x'||lpad(ltrim(to_char(a,'xxxxxxxx')),8,'0') as scn_hex
from (
select dbms_flashback.get_system_change_number a from dual);

--timestamp(current_timestamp,scn_to_timestamp(),cast(),to_timestamp()...)
--各自之間有差別,sysdate函式不支援小數秒的,timestamp最多支援9位精度小數秒,通過scn轉換的timestamp差的略遠

select sysdate ,to_timestamp(to_char(sysdate,'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') as to_stmp ,
cast(sysdate as timestamp) as cast_stmp ,scn_to_timestamp(dbms_flashback.get_system_change_number) as scn_stmp ,current_timestamp from dual;

SYSDATE    TO_STMP                          CAST_STMP                        SCN_STMP                         CURRENT_TIMESTAMP
---------- -------------------------------- -------------------------------- -------------------------------- -----------------------------------
09-NOV-13  09-NOV-13 09.01.34.000000000 PM  09-NOV-13 09.01.34.000000 PM     09-NOV-13 09.01.32.000000000 PM  09-NOV-13 09.01.34.219414 PM +08:00

/*重要的4種scn
  系統檢查點SCN (System Checkpoint SCN)
    v$database.CHECKPOINT_CHANGE# ;
  檔案檢查點SCN (Datafile Checkpoint SCN)
    v$datafile.CHECKPOINT_CHANGE#
  結束SCN      (Stop SCN)
    v$datafile.last_change#
  資料檔案頭SCN (Start SCN)
    v$datafile_header.CHECKPOINT_CHANGE#
*/

select dbid,checkpoint_change# from v$database;
select file#,checkpoint_change#,last_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile_header;

--redo scn
select group#,sequence#,status,first_change#,next_change# from v$log;
select name,sequence#,first_change#,next_change#,first_time,next_time from v$archived_log a;

--block scn
select ora_rowscn,dbms_rowid.rowid_block_number(a.rowid),a.* from xujh.t6 a;

--手動控制發生ckpt,下面這兩個都是發生 normal ckpt
alter system switch logfile; --優先順序較低
alter system checkpoint;     
alter system archive log current/all; 

/*X$BH用於檢視髒塊的LRBA和HRBA
(There is also a recovery RBA which is used to record the progress of partial block recovery by PMON.)
X$TARGETRBA檢視增量checkpoint RBA，target RBA和on-disk RBA。
X$KCCCP這裡面也有增量checkpoint RBA，target RBA的資訊。
X$KCCRT完全checkpoint（full thread checkpoint）RBA資訊。
*/

--normal
select * from X$KCCRT where indx=0;

SELECT addr,indx ,rtckp_scn,rtckp_tim ,rtckp_rba_seq,rtckp_rba_bno
FROM x$kccrt;

--incremental
select * from X$KCCCP where indx=0;

/*          --CPDRT 列是檢查點佇列中的髒塊數目 .
            --CPODS 列是on disk rba的 scn
            --CPODT 列是on disk rba的時間戳
            --CPHBT 列是心跳
*/
select CPDRT,CPLRBA_SEQ||'.'||CPLRBA_BNO||'.'||CPLRBA_BOF "Low RBA",
CPODR_SEQ||'.'||CPODR_BNO||'.'||CPODR_BOF "On disk RBA",CPODS,CPODT,CPHBT
from x$kcccp where indx = 0;

select * from X$TARGETRBA;

--V$INSTANCE_RECOVERY檢視fast_start_mttr_target設定以及系統MTTR相關資訊。
select * from v$instance_recovery;