关于10g中的X$KCVFH

By cui hua on May 12, 2009 2:07 PM | No Comments

有朋友问我X$KCVFH的详细结构，我找遍了也没有发现任何关于X$KCVFH的现成的资料。只好自己动手来分析一下。

The fixed X$ tables are no real tables; you will not find them in any database schema.

These virtual tables provide a SQL interface to Oracle memory structures; that is, you

can retrieve (real-time) information from memory structures using SQL queries.

X$表在oracle的不同版本里很可能是不一样的，这里是以 10.2.0.1为例来研究一下X$KCVFH的结构，X$KCVFH中各个字段的含义大多数都有据可寻，下文中凡是为空白的地方就代表我也不清楚其含义是什么。

 

10g中X$KCVFH的结构：

Version 10

Column Name Data Type          Description

ADDR RAW(4)                    ADDRESS

INDX NUMBER                   INDEX

INST_ID NUMBER                INSTANCE ID

HXFIL NUMBER                  FILE#，Datafile number (from control file)

HXONS NUMBER                 ONLINE | OFFLINE (from control file),HXONS为 0表示 'OFFLINE',为其他值表示 'ONLINE'

HXSTS VARCHAR2(16)            

HXERR NUMBER        ERROR，decode(HXERR, 0, NULL, 1,'FILE MISSING',2,'OFFLINE NORMAL', 3,'NOT VERIFIED', 4,'FILE NOT FOUND',5,'CANNOT OPEN FILE', 6,'CANNOT READ HEADER', 7,'CORRUPT HEADER',8,'WRONG FILE TYPE', 9,'WRONG DATABASE', 10,'WRONG FILE NUMBER',11,'WRONG FILE CREATE', 12,'WRONG FILE CREATE', 16,'DELAYED OPEN',14, 'WRONG RESETLOGS', 15,'OLD CONTROLFILE', 'UNKNOWN ERROR')

HXVER NUMBER        FORMAT，Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6  - indicates Oracle Version 6；7 - indicates Oracle Version 7；8 - indicates Oracle Version 8；0  - indicates the format could not be determined (for example, the header could not be read)

FHSWV NUMBER                 

FHCVN NUMBER

FHDBI NUMBER                 DBID

FHDBN VARCHAR2(9)            DB NAME

FHCSQ NUMBER        controlfile sequence number

FHFSZ NUMBER        BLOCKS, Current datafile size in blocks

FHBSZ NUMBER        datafile block size

FHFNO NUMBER        Tablespace datafile number

FHTYP NUMBER         Type

FHRDB NUMBER

FHCRS VARCHAR2(16)  CREATION_CHANGE#，Datafile creation change#

FHCRT VARCHAR2(20)  CREATION_TIME，Datafile creation timestamp

FHRLC VARCHAR2(20)  RESETLOGS_TIME, Resetlogs timestamp

FHRLC_I NUMBER

FHRLS VARCHAR2(16)  RESETLOGS_CHANGE#, Resetlogs change#

FHPRC VARCHAR2(20)  

FHPRC_I NUMBER

FHPRS VARCHAR2(16)

FHBTI VARCHAR2(20)    Time the backup started

FHBSC VARCHAR2(16)   System change number when backup started

FHBTH NUMBER

FHSTA NUMBER          The value for the column X$KCVFH.FHSTA (file header status) for an open database with an online datafiles in versions prior to version 10 were all 4, indicating an online fuzzy status.  With version 10 of Oracle the first system tablespace datafile will have a different status of 8196 if the datafile is online and the database is open and not in backup mode. In Oracle 10g, the X$KCVFH.FHSTA column will show 8196 for system data file if COMPATIBLE is set to 10.0.0.0 or higher.  The value of 8196 is a value of 0x04, as in previous releases, plus an AND'd value of 0x2000 (8192) for internal uses. If COMPATIBLE is set to 9.2.0 (lowest possible value for Oracle 10g), the FHSTA column for system datafile will have a value of 4.   In Oracle10g, the COMPATIBLE value is irreversible if advanced to a higher value. So the value of 8196 for the fhsta (status) column for the first system tablespace datafile is normal.

FHSCN VARCHAR2(16)    CHECKPOINT_CHANGE#, Datafile checkpoint change#

FHTIM VARCHAR2(20)     CHECKPOINT_TIME, Datafile checkpoint timestamp

FHTHR NUMBER          THREAD#

FHRBA_SEQ NUMBER     SEQUENCE，即Redo log sequence number

FHRBA_BNO NUMBER

FHRBA_BOF NUMBER

FHETB RAW(132)

FHCPC NUMBER         CHECKPOINT_COUNT, Datafile checkpoint count

FHRTS VARCHAR2(20)

FHCCC NUMBER

FHBCP_SCN VARCHAR2(16)

FHBCP_TIM VARCHAR2(20)

FHBCP_THR NUMBER

FHBCP_RBA_SEQ NUMBER

FHBCP_RBA_BNO NUMBER

FHBCP_RBA_BOF NUMBER

FHBCP_ETB RAW(132)

FHBHZ NUMBER

FHXCD RAW(16)

FHTSN NUMBER            TS#，Tablespace number

FHTNM VARCHAR2(30)      TABLESPACE_NAME, Tablespace name

FHRFN NUMBER            RFILE#, Tablespace relative datafile number

FHAFS VARCHAR2(16)       absolute fuzzy scn, 即Minimum PITR SCN

FHRFS VARCHAR2(16)

FHRFT VARCHAR2(20)

HXIFZ NUMBER            File is fuzzy (YES | NO)，decode(hxifz, 0,'NO', 1,'YES', NULL)

HXNRCV NUMBER         File needs media recovery (YES | NO)，decode(hxnrcv, 0,'NO', 1,'YES', NULL)

HXFNM VARCHAR2(513)    NAME, Datafile name

FHPOFB NUMBER

FHPNFB NUMBER

FHPRE10 NUMBER

FHFIRSTUNRECSCN VARCHAR2(16)  UNRECOVERABLE_CHANGE#, Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.

FHFIRSTUNRECTIME VARCHAR2(20)   UNRECOVERABLE_TIME, Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

HXLMDBA NUMBER        SPACE_HEADER, The amount of space currently being used and the amount that is free, as identified in the space header. decode(hxlmdba, 0, NULL, hxlmdba)

HXLMLD_SCN VARCHAR2(16)  LAST_DEALLOC_SCN, Last deallocated SCN

 

我们现在来看一下X$KCVFH的常见用法：

1、判断datafile是否需要recover，如果需要recover，那么需要至少需要recover到什么SCN:

即执行如下查询：

SQL> select max(fhafs) from x$kcvfh;

 

MAX(FHAFS)

----------------

0

如果返回返回结果大于0，则表示数据库处于不一致的状态，需要recover到上述查询结果中的SCN。

 

2、判断datafile做recover需要的archive log的sequence是多少，也就是说做recover到这个sequence，那么control file和datafile header中的记录就一致了。

即执行如下查询：

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHRBA_SEQ Sequence from X$KCVFH;

......显示结果省略

Categories:

Leave a comment