Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Whats Fuzzy in v$datafile_header
On 14 Feb 2006 22:47:29 -0800, "jackal" <jackal_on_work_at_yahoo.com>
wrote:
>Hi,
> I have a db in which all tablespaces are ONLINE. I ve not issued
>ALTER TABLESPACE BEGIN BACKUP command on any of the tablespaces. But
>still when i query the v$datafile_header table i get a YES value in the
>FUZZY column for all datafiles.
>
> Is there something wrong with the datafiles?
>
>Thanks in advance
>Jackal
>
I believe that the FUZZY column will only display "NO" when Oracle is absolutely sure that the filet is consistent, that is, it has been completely checkpointed and verified as such. Which means that any online datafile will display "YES" in that column :
SQL> startup mount;
ORACLE-instans er startet.
Total System Global Area 101785428 bytes
Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytesDatabase er monteret.
FUZ
--- NO NO NO SQL> alter database open; Database er µndret. SQL> select fuzzy from v$datafile_header; FUZ --- YES YES YES So opening the datafiles for transactions will mark the datafiles "FUZZY".. When opening the database read only, Oracle knows that the datafiles are consisten and can't be written to, so : SQL> startup mount; ORACLE-instans er startet. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database er monteret. SQL> alter database open read only; Database er µndret. SQL> select fuzzy from v$datafile_header; FUZ --- NO NO NO However, if you "shutdown abort" from an open read/write database, oracle has not made instance recovery before shutdown and cannot ensure consistency , thus : SQL> shutdown abort; ORACLE-instans er lukket ned. SQL> startup mount; ORACLE-instans er startet. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database er monteret. SQL> select fuzzy from v$datafile_header; FUZ --- YES YES YES Here the datafiles are recorded as fuzzy even i mount mode. When you open the database, instance recovery will be made, and when you shutdown (not abort) and mount it, the FUZZY column will show "NO" again. - Kenneth KoenraadtReceived on Wed Feb 15 2006 - 09:41:50 CST