[20170302]什么是fuzzy.txt
--如果你数据库正常打开的情况下,查询v$datafile_header视图:
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ------------------------------
1 13276944334 2017-03-02 09:35:34 7 13276911100 ONLINE 862 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276944334 2017-03-02 09:35:34 1834 13276911100 ONLINE 851 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276944334 2017-03-02 09:35:34 923328 13276911100 ONLINE 772 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276944334 2017-03-02 09:35:34 16143 13276911100 ONLINE 857 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276944334 2017-03-02 09:35:34 952916 13276911100 ONLINE 768 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276944357 2017-03-02 09:35:48 13276257767 13276911100 ONLINE 242 YES /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//数据库正常运行一般都是Yes.视乎表示某种状态.
--这里的fuzzy表示什么?查询如下文章
Fuzzy File Warning When Recovering From Cold Backup [ID 103100.1]：
You are doing a restore from a cold backup using a backup controlfile. On every file you receive the following message:
WARNING! Recovering data file <fno> from a fuzzy file. If not the current file it might be an online backup taken
without entering the begin backup command.
This message does not make sense as this is a cold backup.
This message is issued when you are performing recovery after restoring from a fuzzy backup due to the online fuzzy bit
is set in the file headers.Although this can be set when you do an online backup without issuing begin/end backup, it
can also be set when the system crashes. This is the case here; a shutdown abort had been performed prior
to the cold backup.
http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1102.htm#REFRN30052
UZZY VARCHAR2(3) File is fuzzy (YES | NO)
--//不好理解!!自己先探索一下:
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.热备份模式:
SYS@book> ALTER TABLESPACE tea BEGIN BACKUP;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------- ---------------
6 13276944908 2017-03-02 09:44:21 13276257767 13276911100 ONLINE 245 YES /mnt/ramdisk/book/tea01.dbf TEA
--//依旧是fuzzy=yes.
--//视乎现在的版本与热备份无关.
SYS@book> ALTER TABLESPACE tea END BACKUP;
Tablespace altered.
3.设置表空间只读呢?
SYS@book> ALTER TABLESPACE tea read only;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- --------------------------- ---------------
6 13276945185 2017-03-02 09:48:30 13276257767 13276911100 ONLINE 247 NO /mnt/ramdisk/book/tea01.dbf TEA
--//fuzzy=NO
SYS@book> ALTER TABLESPACE tea read write;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- --------------------------- ---------------
6 13276945259 2017-03-02 09:49:24 13276257767 13276911100 ONLINE 249 YES /mnt/ramdisk/book/tea01.dbf TEA
4.设置表空间offline:
SYS@book> ALTER TABLESPACE tea offline ;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------- ---------------
6 0 0 0 OFFLINE 0
--//offline , fuzzy=null
SYS@book> ALTER TABLESPACE tea online ;
Tablespace altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------- ---------------
6 13276945505 2017-03-02 09:52:26 13276257767 13276911100 ONLINE 251 YES /mnt/ramdisk/book/tea01.dbf TEA
5.设置数据文件6 offline:
SYS@book> alter database datafile 6 offline ;
Database altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- --------------------------- ---------------
6 13276945505 2017-03-02 09:52:26 13276257767 13276911100 OFFLINE 251 YES /mnt/ramdisk/book/tea01.dbf TEA
--//依旧是fuzzy=yes.
SYS@book> recover datafile 6;
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------- ---------------
6 13276945602 2017-03-02 09:53:44 13276257767 13276911100 OFFLINE 252 NO /mnt/ramdisk/book/tea01.dbf TEA
--//恢复数据文件后,fuzzy=NO.
SYS@book> alter database datafile 6 online ;
Database altered.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------- ---------------
6 13276945744 2017-03-02 09:55:57 13276257767 13276911100 ONLINE 253 YES /mnt/ramdisk/book/tea01.dbf TEA
--//数据文件online后,fuzzy=YES.
6.shutdown abort关闭数据库后:
SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
1 13276944334 2017-03-02 09:35:34 7 13276911100 ONLINE 862 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276944334 2017-03-02 09:35:34 1834 13276911100 ONLINE 851 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276944334 2017-03-02 09:35:34 923328 13276911100 ONLINE 772 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276944334 2017-03-02 09:35:34 16143 13276911100 ONLINE 857 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276944334 2017-03-02 09:35:34 952916 13276911100 ONLINE 768 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276945744 2017-03-02 09:55:57 13276257767 13276911100 ONLINE 253 YES /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//异常关闭数据库,到mount状态fuzzy=YES.
SYS@book> recover database;
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13276965838 2017-03-02 09:57:27 7 13276911100 ONLINE 863 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276965838 2017-03-02 09:57:27 1834 13276911100 ONLINE 852 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276965838 2017-03-02 09:57:27 923328 13276911100 ONLINE 773 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276965838 2017-03-02 09:57:27 16143 13276911100 ONLINE 858 NO /mnt/ramdisk/book/users01.dbf USERS
5 13276965838 2017-03-02 09:57:27 952916 13276911100 ONLINE 769 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276965838 2017-03-02 09:57:27 13276257767 13276911100 ONLINE 254 NO /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//recover后,FUZZY=NO.从这里也可以看出如果正常关闭数据库,在status=online情况下fuzzy=NO.
7.shutdown immediate关闭数据库后:
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount;
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13276966464 2017-03-02 10:01:38 7 13276911100 ONLINE 867 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276966464 2017-03-02 10:01:38 1834 13276911100 ONLINE 856 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276966464 2017-03-02 10:01:38 923328 13276911100 ONLINE 777 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276966464 2017-03-02 10:01:38 16143 13276911100 ONLINE 862 NO /mnt/ramdisk/book/users01.dbf USERS
5 13276966464 2017-03-02 10:01:38 952916 13276911100 ONLINE 773 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276966464 2017-03-02 10:01:38 13276257767 13276911100 ONLINE 258 NO /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//fuzzy=NO.
8.概括:
--//以上测试可以大概猜测fuzzy表示数据文件某种状态.
A.与是否热备无关
B.数据文件为read write ,status=online时，fuzzy=yes
C.数据文件为read only ，status=online时, fuzzy=no
D.数据文件为offline,
alter database datafile 6 offline; 则fuzzy为YES
alter tablespace USERS offline; 则fuzzy为NULL
--//可以这么理解FuzzY=NO表示一致的状态.这也就好理解昨天我恢复时:
--//链接http://blog.itpub.net/267265/viewspace-2134481/,最后为什么不能使用open resetlogs打开.因为我使用恢复时指定scn.
--//recover database using backup controlfile until change 13276911099;
--//oracle并不知道是否恢复结束.也就是有可能FUZZY还是YES.
9.继续测试:
SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount ;
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
1 13276966467 2017-03-02 10:13:04 7 13276911100 ONLINE 868 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276966467 2017-03-02 10:13:04 1834 13276911100 ONLINE 857 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276966467 2017-03-02 10:13:04 923328 13276911100 ONLINE 778 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276966467 2017-03-02 10:13:04 16143 13276911100 ONLINE 863 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276966467 2017-03-02 10:13:04 952916 13276911100 ONLINE 774 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13276966467 2017-03-02 10:13:04 13276257767 13276911100 ONLINE 259 YES /mnt/ramdisk/book/tea01.dbf TEA
6 rows selected.
--//shut abort关闭数据库,fuzzy=YES.
SYS@book> @ &r/checkpoint
REDO:
检查点队列
检查点队列 on disk rba 检查点队列
脏块数量 时间戳 当前时间 on disk rba scn 检查点心跳
CPDRT low_rba on_disk_rba CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
53 20.531.0 20.666.0 2017-03-02 10:13:10 2017-03-02 10:15:01 111.00 13276966781 937603690 0 -13276966781 0
--//理论on disk rba scn=13276966781.
SYS@book> recover database until change 13276966700;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------ ---------------
1 13276966700 2017-03-02 10:13:07 7 13276911100 ONLINE 868 YES /mnt/ramdisk/book/system01.dbf SYSTEM
--//fuzzy=YES.
SYS@book> recover database until change 13276966780;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> recover database until change 13276966781;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------ ---------------
1 13276966781 2017-03-02 10:13:11 7 13276911100 ONLINE 868 YES /mnt/ramdisk/book/system01.dbf SYSTEM
--//fuzzy=YES.实际上我现在已经到最后的scn.
--//选择until cancel呢?
SYS@book> recover database until cancel;
ORA-00279: change 13276966781 generated at 03/02/2017 10:13:11 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_20_937478950.dbf
ORA-00280: change 13276966781 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo02.log
Log applied.
Media recovery complete.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------ ---------------
1 13276966782 2017-03-02 10:13:11 7 13276911100 ONLINE 869 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//fuzzy=NO,这时scn=13276966782.也就是将只有恢复到结束,oracle才会认为数据文件一致的.
