ITPub博客

首页 > 数据库 > Oracle > [20191009]文件头fuzzy.txt

[20191009]文件头fuzzy.txt

原创 Oracle 作者:lfree 时间:2019-10-09 21:26:40 0 删除 编辑

[20191009]文件头fuzzy.txt

--//查询视图v$datafile_header,仅仅能看到fuzzy两种状态YES.NO.
--//我在链接:http://blog.itpub.net/267265/viewspace-2134630/=>[20170302]fuzzy保存在那里.txt
--//提到保存fuzzy在文件头偏移138处.bbed 使用kcvfh.kcvfhsta表示.
--//是否在情况下具体数值不是很清楚,做一些测试验证看看。

1.环境:

SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SYS@test> column VIEW_DEFINITION format a100
SYS@test> select * from V$FIXED_VIEW_DEFINITION where view_name ='GV$DATAFILE_HEADER';
VIEW_NAME            VIEW_DEFINITION                                                                                          CON_ID
-------------------- ---------------------------------------------------------------------------------------------------- ----------
GV$DATAFILE_HEADER   select inst_id,hxfil,decode(hxons, 0, 'OFFLINE', 'ONLINE'),decode(hxerr, 0, NULL, 1,'FILE MISSING',2          0
                     ,'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', 'UNK
                     NOWN ERROR'),hxver,decode(hxnrcv, 0,'NO', 1,'YES', NULL),decode(hxifz, 0,'NO', 1,'YES', NULL),to_num
                     ber(fhcrs),to_date(fhcrt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),fhtnm,fhtsn,fhrfn,to_number
                     (fhrls),to_date(fhrlc,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),to_number(fhscn),to_date(fhtim
                     ,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),fhcpc,fhfsz*fhbsz,fhfsz,hxfnm, decode(hxlmdba, 0, N
                     ULL, hxlmdba), decode(hxlmld_scn, to_number('0'), NULL, hxlmld_scn), decode(hxuopc_scn, 0, NULL, hxu
                     opc_scn), con_id, decode(bitand(fhsparse, 1), 0, 'NO', 'YES'), decode(bitand(fhkeyflg, 1), 0, 'NO',
                     'YES') from x$kcvfh

--//格式化语句如下:
/* Formatted on 2019/10/9 19:45:33 (QP5 v5.227.12220.39754) */
SELECT inst_id
      ,hxfil
      ,DECODE (hxons, 0, 'OFFLINE', 'ONLINE')
      ,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
      ,DECODE (hxnrcv,  0, 'NO',  1, 'YES',  NULL)
      ,DECODE (hxifz,  0, 'NO',  1, 'YES',  NULL)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ,TO_NUMBER (fhcrs)
      ,TO_DATE (fhcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
      ,fhtnm
      ,fhtsn
      ,fhrfn
      ,TO_NUMBER (fhrls)
      ,TO_DATE (fhrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
      ,TO_NUMBER (fhscn)
      ,TO_DATE (fhtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
      ,fhcpc
      ,fhfsz * fhbsz
      ,fhfsz
      ,hxfnm
      ,DECODE (hxlmdba, 0, NULL, hxlmdba)
      ,DECODE (hxlmld_scn, TO_NUMBER ('0'), NULL, hxlmld_scn)
      ,DECODE (hxuopc_scn, 0, NULL, hxuopc_scn)
      ,con_id
      ,DECODE (BITAND (fhsparse, 1), 0, 'NO', 'YES')
      ,DECODE (BITAND (fhkeyflg, 1), 0, 'NO', 'YES')
  FROM x$kcvfh;
--//下划线显示的就是fuzzy.而x$kcvfh.hxifz仅仅2个值0,1对应NO,YES.很明显与实际文件头记录的fuzzy不一样.看看各种情况下该值的情况.

2.测试:
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    900      SYSTEM               YES     D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
2    210      PDB$SEED:SYSTEM      NO      D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF
3    1040     SYSAUX               NO      D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF
4    165      PDB$SEED:SYSAUX      NO      D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF
5    655      UNDOTBS1             YES     D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
6    205      PDB$SEED:UNDOTBS1    NO      D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF
7    5        USERS                NO      D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF
8    260      TEST01P:SYSTEM       YES     D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF
9    215      TEST01P:SYSAUX       NO      D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF
10   280      TEST01P:UNDOTBS1     YES     D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF
11   528      TEST01P:USERS        NO      D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
36   100      TEST01P:LFREE        NO      D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    62       TEMP                 32767       D:\APP\ORACLE\ORADATA\TEST\TEMP01.DBF
2    20       PDB$SEED:TEMP        32767       D:\APP\ORACLE\ORADATA\TEST\PDBSEED\TEMP01.DBF
3    200      TEST01P:TEMP         32767       D:\APP\ORACLE\ORADATA\TEST\TEST01P\TEMP01.DBF

SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ---------------
    1           11107573 2019-10-09 19:26:19                9                 1 ONLINE              3066 YES D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF            SYSTEM
    2            1102125 2018-10-06 23:24:44              227                 1 ONLINE                37 NO  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF    SYSTEM
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11111214 2019-10-09 19:39:54          3718607                 1 ONLINE              2986 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

--//通过bbed观察,说明windows bbed存在+1偏移.一般在linux下访问块头,执行dba N,1.

BBED> p dba 1,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

BBED> p dba 2,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2000 (NONE)

BBED> p dba 8,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

--//可以看出在system数据文件在open 读写的情况下,kcvfh.kcvfhsta=0x2004.而read onlt的情况下0x2000,
--//而其它数据文件在open 读写的情况下,kcvfh.kcvfhsta=0x0004.
--//system数据文件与普通数据文件kcvfh.kcvfhsta(16进制)前者前面2,后者是0.

3.测试在热备份的情况下:
SCOTT@test01p> alter tablespace lfree begin backup ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------ ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE             3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113518 2019-10-09 20:02:41          3718607                 1 ONLINE             2987 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)
--//可以发现在热备份模式下kcvfh.kcvfhsta= 0x0001.对应的v$datafile_header.fuzzy="YES".

SCOTT@test01p> alter tablespace lfree end backup ;
Tablespace altered.

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

4.测试表空间offline的情况下:

SCOTT@test01p> alter tablespace lfree offline ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36                  0                                    0                 0 OFFLINE                0

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0000 (NONE)

SCOTT@test01p> alter tablespace lfree online ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113676 2019-10-09 20:07:47          3718607                 1 ONLINE              2990 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

--//测试表空间offline immediate看看:
SCOTT@test01p> alter tablespace lfree offline immediate;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113676 2019-10-09 20:07:47          3718607                 1 OFFLINE             2990 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

SCOTT@test01p> recover tablespace lfree;
Media recovery complete.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113717 2019-10-09 20:08:45          3718607                 1 OFFLINE             2991 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0000 (NONE)

SCOTT@test01p> alter tablespace lfree online;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113866 2019-10-09 20:12:06          3718607                 1 ONLINE              2992 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

5.测试表空间read only的情况:
SCOTT@test01p> alter tablespace lfree read only ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113904 2019-10-09 20:13:02          3718607                 1 ONLINE              2993 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0000 (NONE)

SCOTT@test01p> alter tablespace lfree read write ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113934 2019-10-09 20:13:46          3718607                 1 ONLINE              2995 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

6.测试数据文件offline的情况:
SCOTT@test01p> alter database datafile 36 offline ;
Database altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11113934 2019-10-09 20:13:46          3718607                 1 OFFLINE             2995 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

SCOTT@test01p> recover datafile 36;
Media recovery complete.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11114396 2019-10-09 20:14:45          3718607                 1 OFFLINE             2996 NO  D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0000 (NONE)

SCOTT@test01p> alter database datafile 36 online ;
Database altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11111214 2019-10-09 19:39:54          1103550                 1 ONLINE              3136 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11114455 2019-10-09 20:16:12          3718607                 1 ONLINE              2997 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 36,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

7.总结:
--//可以大致总结如下:
--//对于普通数据文件:
fuzzy=Yes .kcvfh.kcvfhsta = 0x0004 ,0x0001(热备份模式)
fuzzy=NO   kcvfh.kcvfhsta = 0x0000

--//对于system数据文件,仅仅前面有0x2:
fuzzy=Yes .kcvfh.kcvfhsta = 0x2004 ,0x2001(热备份模式)
fuzzy=NO   kcvfh.kcvfhsta = 0x2000

--//补充测试system表空间热备份的情况:
SCOTT@test01p> alter tablespace system begin backup ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11114579 2019-10-09 20:20:43          1103550                 1 ONLINE              3137 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11114455 2019-10-09 20:16:12          3718607                 1 ONLINE              2997 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 8,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2001 (KCVFHHBP)

SCOTT@test01p> alter tablespace system end backup ;
Tablespace altered.

SCOTT@test01p> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status,CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,2,8,36);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- --------------------
    8           11114579 2019-10-09 20:20:43          1103550                 1 ONLINE              3138 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF    SYSTEM
   36           11114455 2019-10-09 20:16:12          3718607                 1 ONLINE              2997 YES D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF    LFREE

BBED> p dba 8,2 kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2659273/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291530