This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Friday, January 1, 2010

DBV-00100 11gR1 bug

Recently I had to check physical consistency of one 11gR1.7 database on Windows 2008 x64. Here are the steps that I have done. Demo is taken from my local home database.

As always when I have to work with dbv, Oracle database verify utility, first I create script which allow me to run dbv automatically according all existing data files in database:
SQL> SELECT 'dbv file=' || file_name ||
  2  ' logfile=file' || ROWNUM ||
  3  '.log blocksize=8192' dbv_cmd
  4  FROM dba_data_files;

DBV_CMD
---------------------------------------------------------------------------------------
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF logfile=file1.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\SYSAUX01.DBF logfile=file2.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\UNDOTBS01.DBF logfile=file3.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\APEX.DBF logfile=file4.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\OWB.DBF logfile=file5.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\USERS01.DBF logfile=file6.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\TOOLS01.DBF logfile=file7.log blocksize=8192
dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\FLOW_1.DBF logfile=file8.log blocksize=8192

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Then I tried to run dbv utility for first file from the list:
C:\>dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF logfile=file1.log blocksize=8192

DBVERIFY: Release 11.1.0.7.0 - Production on Pon Sij 12 22:55:13 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBV-00100: Specified FILE (C:\/C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF) not accessible

C:\>

This is not a bug?

Without too many thoughts I grep Metalink knowledge base and find note which says in shortly:
DBV-00100: Specified FILE (XXXX) not accessible

Symptom:
Using the verification tool 'dbverfNN' produces the error:

DBV-00100: Specified FILE (XXXX) not accessible

Where ‘XXXX’ is the name of the file that ‘dbv’ was invoked on.

Possible Causes and Remedies
  • The file does not exist or was incorrectly specified. Check that the specified file exists.
  • The datafile to be verified must end with ‘.dbf’. Try renaming the file (be careful, the file must be renamed and Oracle informed that it has been renamed).
  • The file is in use, and is therefore locked and so the utility cannot open it. Shutdown Oracle and try again. (Possibly NT only.)
  • If the database has been shutdown then it is possible that that Oracle service has the file locked. Try stopping the service 'OracleService'. (NT only.)
  • DBV may spin when SEGMENT_ID & USERID specified if there are multiple files in a tablespace, and the first file is not the one in which extents exist for the segment.
  • DBV may not be able to scan data files larger than 2Gb and may report "DBV-100". This is reported in Bug:710888 for Unix and Bug:1372172 for 8.1.6 on NT.

Regardless NT is not mine OS (I have Win2k8) I checked size of data files:
C:\oracle\product\oradata\XE>dir
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of C:\oracle\product\oradata\XE

12.01.2010  22:43    <dir>          .
12.01.2010  22:43    <dir>          ..
12.01.2010  23:15       268.443.648 APEX.DBF
12.01.2010  23:15         9.781.248 CONTROL01.CTL
12.01.2010  23:15         9.781.248 CONTROL02.CTL
12.01.2010  23:15         9.781.248 CONTROL03.CTL
12.01.2010  22:55               738 file1.log
12.01.2010  23:15       104.931.328 FLOW_1.DBF
12.01.2010  23:15        67.117.056 OWB.DBF
12.01.2010  23:15        52.429.312 REDO01.LOG
12.01.2010  23:15        52.429.312 REDO02.LOG
12.01.2010  23:15        52.429.312 REDO03.LOG
12.01.2010  23:15       584.851.456 SYSAUX01.DBF
12.01.2010  23:15       754.982.912 SYSTEM01.DBF
12.01.2010  22:45       201.334.784 TEMP01.DBF
12.01.2010  23:15        20.979.712 TOOLS01.DBF
12.01.2010  23:15       754.982.912 UNDOTBS01.DBF
12.01.2010  23:15        62.791.680 USERS01.DBF
              16 File(s)  3.007.047.906 bytes
               2 Dir(s)  35.569.840.128 bytes free

C:\oracle\product\oradata\XE>
Sizes were OK as expected! Then I changed directory to one that holds data file and start again the same command:
C:\>cd C:\ORACLE\PRODUCT\ORADATA\XE\ 

C:\oracle\product\oradata\XE>dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF logfile=file1.log blocksize=8192 

DBVERIFY: Release 11.1.0.7.0 - Production on Pon Sij 12 22:55:13 2010 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBV-00100: Specified FILE (C:\oracle\product\oradata\XE/C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF) not accessible 

C:\oracle\product\oradata\XE>
Still nothing but now I realize very obvious thing: file has wrong path because it add a prefix that is got from the path of the current directory! Now it was easy to fix this. I change dir to one where data files are and repeat dbv without path in file declaration:
C:\oracle\product\oradata\XE>cd C:\ORACLE\PRODUCT\ORADATA\XE\ 

C:\oracle\product\oradata\XE>dbv file=SYSTEM01.DBF logfile=file1.log blocksize=8192

DBVERIFY: Release 11.1.0.7.0 - Production on Pon Sij 12 22:55:31 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

C:\oracle\product\oradata\XE>
All works like a charm. Here is the log file as well:
C:\oracle\product\oradata\XE>more file1.log

DBVERIFY: Release 11.1.0.7.0 - Production on Pon Sij 12 22:55:31 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


DBVERIFY - Verification starting : FILE = C:\oracle\product\oradata\XE/SYSTEM01.DBF

DBVERIFY - Verification complete

Total Pages Examined         : 92160
Total Pages Processed (Data) : 64990
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 11624
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2393
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 13153
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2145714 (0.2145714)

C:\oracle\product\oradata\XE>

This is a bug!?

Search on internet gave me no answer so I think this is long running bug (find post with all versions of Oracle). Then on Meatalink note 35512.1 I saw example of dbv command as oracle do that:
dbv file=users01.dbf blocksize=2048 
As you can see no path at all-this is it I thought! But few lines before in the same note, I noticed something different:
The following example shows a sample use of the command-line interface to this mode of DBVERIFY.  
DBV when the database is shutdown  

D:\oracle\Ora81\BIN>dbv file=D:\ORACLE\ORADATA\SRIDEVI\INDEXES.DBF blocksize=8192 
A-ha! Go to Oracle bin directory, shutdown database and try dbv from there and use full data file path ??!!! Looks stupid but let me try!

Because I always use short names for oracle installation paths, I knew that this could not be a problem. So I tried to produce the same example on mine server:
SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> exit 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options  

C:\>cd C:\oracle\product\11g\BIN  

C:\oracle\product\11g\BIN>dbv file=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF logfile=file1.log blocksize=8192  

DBVERIFY: Release 11.1.0.7.0 - Production on Pon Sij 12 23:17:10 2010  

Copyright (c) 1982, 2007, Oracle.  All rights reserved.   


DBV-00100: Specified FILE (C:\oracle\product\11g\BIN/C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF) not accessible  

C:\oracle\product\11g\BIN> 
Again nothing!

This is definitely a bug!

Then I return to Metalink again determined to search more deeply under bug section. Very soon I find out that Bug 8396246 has been raised for that issue. It says it will be fixed in is fixed in 11.2 version. For 11.1 version will be no patch for this bug and only workaround is explained before in this topic!

As many times before, it looks that here are things a little bit different on Windows against Linux...good old Linux!

This is mainly because Oracle doesn't test all version with same strength and precision. Linux on "ordinary" platform is always tested the best.

This is my first post in New 2010...so as ever and now even more-cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign