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;