10g,11g,12c X$KCVFH(二)

SQL> select file#,ts#,creation_time,creation_change#,name from v$datafile;

     FILE#        TS# CREATION_TIME   CREATION_CHANGE# NAME
---------- ---------- --------------- ---------------- --------------------------------------------------
         1          0 20140707 053857                7 /oradata/cdb1/system01.dbf
         3          1 20140707 053905             4180 /oradata/cdb1/sysaux01.dbf
         4          2 20140707 070358          1591076 /oradata/cdb1/undotbs01.dbf
         5          0 20160310 102859          1594411 /oradata/cdb1/pdbseed/system01.dbf
         6          4 20140707 053938            24908 /oradata/cdb1/users01.dbf
         7          1 20160310 102859          1594414 /oradata/cdb1/pdbseed/sysaux01.dbf
         8          0 20160310 103700          1742849 /oradata/cdb1/pdb11/system01.dbf
         9          1 20160310 103700          1742852 /oradata/cdb1/pdb11/sysaux01.dbf
        10          3 20160310 103704          1743324 /oradata/cdb1/pdb11/pdb11_users01.dbf
        11          0 20160310 103716          1743411 /oradata/cdb1/pdb12/system01.dbf
        12          1 20160310 103716          1743414 /oradata/cdb1/pdb12/sysaux01.dbf
        13          3 20160310 103722          1744146 /oradata/cdb1/pdb12/pdb12_users01.dbf
        14          6 20160314 084129          2156053 /oradata/cdb1/ttbs01.dbf
13 rows selected.
SQL>

print_table(from asktom)

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
begin
    execute immediate 'alter session set  nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_columnValue, 4000);
    end loop;
    l_status := dbms_sql.execute(l_theCursor);
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue );
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
    execute immediate  'alter session set nls_date_format=''yyyy-mm-dd'' ';
exception
    when others then
      execute immediate  'alter session set nls_date_format=''yyyy-mm-dd'' ';
      raise;
end;
/

SQL> exec print_table('select * from x$kcvfh where hxfil=14');

ADDR                          : 00007F1495793430
INDX                          : 0
INST_ID                       : 1
CON_ID                        : 1
HXFIL                         : 14
HXONS                         : 1
HXSTS                         : 2156078
HXERR                         : 0
HXVER                         : 10
FHSWV                         : 0
FHCVN                         : 202375680
FHDBI                         : 879831934
FHDBN                         : CDB1
FHCSQ                         : 2720
FHFSZ                         : 1280
FHBSZ                         : 8192
FHFNO                         : 14
FHTYP                         : 3
FHRDB                         : 0
FHCRS                         : 2156053
FHCRT                         : 03/14/2016 08:41:29
FHRLC                         : 03/10/2016 10:28:16
FHRLC_I                       : 906114496
FHRLS                         : 1594143
FHPRC                         : 07/07/2014 05:38:47
FHPRC_I                       : 852269927
FHPRS                         : 1
FHBTI                         :
FHBSC                         : 0
FHBTH                         : 0
FHSTA                         : 0
FHSCN                         : 2156078
FHTIM                         : 03/14/2016 08:41:29
FHTHR                         : 1
FHRBA_SEQ                     : 27
FHRBA_BNO                     : 13936
FHRBA_BOF                     : 16
FHETB                         :020000000000000000000000
FHCPC                         : 4
FHRTS                         :
FHCCC                         : 3
FHBCP_SCN                     : 0
FHBCP_TIM                     :
FHBCP_THR                     : 0
FHBCP_RBA_SEQ                 : 0
FHBCP_RBA_BNO                 : 0
FHBCP_RBA_BOF                 : 0
FHBCP_ETB                     :0000000000000000000000000000000000000000
FHBHZ                         : 0
FHXCD                         : 00000000000000000000000000000000
FHTSN                         : 6
FHTNM                         : TTBS
FHRFN                         : 6
FHAFS                         : 0
FHRFS                         : 0
FHRFT                         :
HXIFZ                         : 0
HXNRCV                        : 0
HXFNM                         : /oradata/cdb1/ttbs01.dbf
FHPOFB                        : 0
FHPNFB                        : 0
FHPRE10                       : 0
FHFIRSTUNRECSCN               : 0
FHFIRSTUNRECTIME              :
HXLMDBA                       : 25165826
HXLMLD_SCN                    : 0
FHFCRS                        : 988900
FHFCRT                        : 20-oct-2014 23:15:52
FHFCPS                        : 989507
FHFCPT                        : 20-oct-2014 23:26:08
FHPLUS                        : 2155988
FHFDBI                        : 1512727797
FHPIDI                        : 879831934
FHPIFN                        : 14
FHPRLS                        : 1594143
FHPRLT                        : 10-mar-2016 10:28:16
FHPTSN                        : 6
FHBSSZ                        : 0
FHBSFMT                       : 0
FHBSEOFSCN                    : 0
FHBSMAP                       : 000000000000000000000000
HXUOPC_SCN                    : 0
FHPDBI                        : 1
FHPDBDBI                      : 879831934
FHPDBIDN                      : ???d??C???/
FHPIN_SCN                     : 0
FHPIN_time                    :
FHPBR_SCN                     : 0
FHPBR_time                    :
FHPER_SCN                     : 0
FHPER_time                    :
FHPIC                         : 0
-----------------
PL/SQL procedure successfully completed.
SQL> 

12c datafile header dump

DATA FILE #14: 
  name #20: /oradata/cdb1/ttbs01.dbf
creation size=1280 block size=8192 status=0x86 flg=0x1 head=20 tail=20 dup=1
 pdb_id 1, tablespace 6, index=17 krfil=6 prev_file_in_ts=0 prev_file_in_pdb=3
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:4 scn: 0x0000.0020e62e 03/14/2016 08:41:29
 Stop scn: 0x0000.0020e62e 03/14/2016 08:41:29
 Creation Checkpointed at scn:  0x0000.0020e615 03/14/2016 08:41:29
 thread:1 rba:(0x1b.364e.10)

 Offline scn: 0x0000.0020e615 prev_range: 0
 Online Checkpointed at scn:  0x0000.0020e61a 03/14/2016 08:41:29
 thread:1 rba:(0x1b.3652.10)

 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED 
 Plugged readony: NO

11g新增字段，为传输表空间所用

 Plugin scnscn: 0x0000.0020e5d4
 Plugin resetlogs scn/timescn: 0x0000.0018531f 03/10/2016 10:28:16
 Foreign creation scn/timescn: 0x0000.000f16e4 10/20/2014 23:15:52
 Foreign checkpoint scn/timescn: 0x0000.000f1943 10/20/2014 23:26:08

 Online move state: 0
 V10 STYLE FILE HEADER:

Compatibility Vsn = 202375680=0xc100200
Db ID=879831934=0x34712b7e, Db Name='CDB1'
Activation ID=0=0x0
Control Seq=2720=0xaa0, File size=1280=0x500
File Number=14, Blksiz=8192, File Type=3 DATA
Tablespace #6 - TRANS_TBS  rel_fn:6 
Creation   at   scn: 0x0000.0020e615 03/14/2016 08:41:29
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x360235c0 scn: 0x0000.0018531f
 prev reset logs count:0x32cc9b67 scn: 0x0000.00000001
 recovered at 01/01/1988 00:00:00
 status:0x0 root dba:0x00000000 chkpt cnt: 4 ctl cnt:3
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.0020e62e 03/14/2016 08:41:29
 thread:1 rba:(0x1b.3670.10)

Backup Checkpointed at scn:  0x0000.00000000 
 thread:0 rba:(0x0.0.0)

External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp  01/01/1988 00:00:00

PDB ID=1, PDB Db ID=879831934=0x34712b7e
Platform Information: Creation Platform ID: 10
Current Platform ID: 13 Last Platform ID: 13
PDB incarnation 0: inc_scn 0x0000,00000000 inc_time 0, br_scn 0x0000.00000000 br_time 0, er_scn 0x0000.00000000 er_time 0

Useful SQL

For 11g

col filename for a60
select hxfil file#, hxfnm filename,fhsta status,fhscn scn,fhrba_seq redo_seq,fhafs,fhrfs from x$kcvfh order by fhsta;

col filename for a40
col container for a8

select hxfil file#,hxfnm filename , c.name container, fhsta status, fhscn scn, fhrba_seq seq , fhafs abs,fhrfs from v$containers c,x$kcvfh k where c.con_id=k.con_id order by fhsta;

select current_scn, checkpoint_change#, controlfile_change#,controlfile_time from v$database;
select hxfil filenumber, fhsta status, fhscn scn, fhrba_seq sequence from x$kcvfh; 

Reference
v$datafile_header

http://kundun-oracle.blogspot.com/2014/12/how-to-know-at-which-point-you-can-open.html

http://www.dbsnake.net/10g-x-kcvfh-continue.html