|
 |
|
Oracle Concepts -
DB_VERIFY Utility
Oracle Tips by Burleson Consulting |
DB_VERIFY UTILITY
In the final section of this lesson I want to
cover the DB_VERIFY utility. The DB_VERIFY utility is an external
command line-based utility that is used to perform a physical
structure integrity check on an off-line (shutdown) database. The
utility can be used against backup files and on-line files or pieces
of on-line files. The utility is used to be sure a backup database or
data file is valid before recovery.
The db verify utility can also serve as a
diagnostic aid when corruption is suspected. Since it runs against a
shutdown database it can perform checks significantly faster than
export or other utilities. The utility is named differently on
different platforms; for example, it may be called dbv (on
SUN/Sequent) or something else on your system. Verify its name with
the system-specific documentation you should have received (if you
didn’t, call your Oracle rep and complain). The utility only verifies
cache-managed blocks.
The DB_VERIFY utility has the following
general syntax:
dbf|dbverify|dbverf80 keyword=value,keyword=value…
Where the keywords are shown in table 1.
|
Keyword |
Description |
(Default) |
|
FILE |
File to Verify |
(NONE) |
|
START |
Start Block |
(First Block of File) |
|
END |
End Block |
(Last Block of File) |
|
BLOCKSIZE |
Logical Block Size |
(2048) |
|
LOGFILE |
Output Log |
(NONE) |
|
FEEDBACK |
Display Progress |
(0) |
Table 1: DBVERIFY Commands
The following shows some example runs of the
DB_VERIFY against an Oracle 7.3 database:
The following example shows how to get on-line
help:
D:\Oracle2\Ortest1\Data>dbv help=y
DBVERIFY:
Release 8.1.5.0.0 - Production on Fri Feb 4 11:59:26 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Keyword
Description (Default)
----------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (2048)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
This is sample output of verification for the
file, sys1ortest1.dbf. The feedback parameter has been given the value
1000 to display one dot on screen for every 1000 blocks processed:
D:\Oracle2\Ortest1\Data>dbv file=sys1ortest1.dbf blocksize=8192
feedback=1000
DBVERIFY:
Release 8.1.5.0.0 - Production on Fri Feb 4 11:47:13 2000
(c)
Copyright 1999 Oracle Corporation. All rights reserved.
DBVERIFY -
Verification starting : FILE = sys1ortest1.dbf
.............
DBVERIFY -
Verification complete
Total Pages
Examined : 12800
Total Pages Processed (Data) : 3419
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1304
Total Pages Failing (Index): 0
Total Pages Processed (Other): 527
Total Pages Empty : 7550
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
The DBMS_REPAIR Utility
New in Oracle8i is the DBMS_REPAIR utility.
The DBMS_REPAIR utility consists of a stored package of procedures and
functions that allow the DBA to detect and repair corrupt blocks in
tables and indexes. This functionality has been sorely needed in
Oracle for a long time. Now, instead of having to go through a complex
recovery procedure should a single block become corrupted the DBA has
the option of attempting repair using the DBMS_REPAIR utility.
DBMS_REPAIR Enumeration Types
In Oracle you can define constants in a
package that can then be used throughout the package and database.
Oracle has recently taken to calling these constants "enumeration
types". Essentially an enumeration type is a global variable which
defines to a constant numeric value. Enumeration types are used to
assign values to specific function and procedure IN type variables.
The DBMS_REPAIR package has the following enumeration types as shown
in table 2.
|
Type |
Values |
|
Object_type |
TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECT |
|
Action |
CREATE_ACTION, DROP_ACTION, PURGE_ACTION |
|
Table_type |
REPAIR_TABLE, ORPHAN_TABLE |
|
Flags |
SKIP_FLAG, NOSKIP_FLAG |
Table 2: Enumeration Types For DBMS_REPAIR
Package
The default table_name is REPAIR_TABLE when
table_type is REPAIR_TABLE and will be ORPHAN_KEY_TABLE when
table_type is ORPHAN.
DBMS_REPAIR Exceptions
The DBMS_REPAIR package can raise several
self-declared exceptions. The DBMS_REPAIR self-declared exceptions are
all in the 24000 number range and are shown in table 3.
|
Exception |
Purpose |
|
24120 |
This is raised by the package procedures
if an invalid parameter is passed. |
|
24122 |
This is raised by the package if an
incorrect block range is specified in BLOCK_START or BLOCK_END. |
|
24123 |
An unimplemented feature was called. |
|
24124 |
An invalid action was specified. |
|
24125 |
The target object has been dropped or
truncated since DBMS_REPAIR.CHECK_OBJECT was last run. |
|
24127 |
The TABLESPACE parameter was specified
with an action other than CREATE_ACTION. |
|
24128 |
A partition name was specified for an
object that isn't partitioned. |
|
24129 |
A table name parameter was passed without
the appropriate prefix. |
|
24130 |
The orphan table specified for the repair
doesn't exist. |
|
24131 |
The orphan table specified doesn't have a
proper definition. |
|
24132 |
Table names are limited to 30 characters,
the name specified is longer than this. |
Exceptions For the DBMS_REPAIR
Package
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
