Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » 内部原理

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)
  enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
 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)
  enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000


 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)
  enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn:  0x0000.00000000 
 thread:0 rba:(0x0.0.0)
  enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000

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
12c新增字段,为PDB所用
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;

For 12c
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


关键词:

相关文章

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