 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
|
|
 |
Oracle Database Verify Utility dbvOracle Tips by Burleson |
Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.
dbv
DBV is a simplistic external command line utility which
performs a very critical task – it does either an offline or online check or
verification as to the validity of data files. It offers two basic modes of
operation: file level and segment level. The offline check is quicker when
referential integrity checks are involved. Here are the table level
verification mode’s parameters:
|
<
USERID |
<
Username/Password |
|
<
FILE |
<
File Name |
|
<
START |
<
Block Address |
|
<
END |
<
Block Address |
|
<
BLOCKSIZE |
<
Integer |
|
<
Feedback |
<
Integer |
|
<
PARFILE |
<
File Name |
And here is a simple check of the user’s tablespace’s data
file.
C:\Temp> dbv userid=bert/bert file=C:\Oracle\oradata\ORDB1\USERS.DBF
blocksize=4096
DBVERIFY: Release 11.1.0.6.0 - Production on Tue Jul 8
15:13:42 2008
Copyright (c) 1982, 2007, Oracle.
All rights reserved.
DBVERIFY - Verification starting : FILE = C:\Oracle\oradata\ORDB1\USERS.DBF
DBVERIFY - Verification complete
Total Pages Examined
: 51200
Total Pages Processed (Data) : 610
Total Pages Failing
(Data) : 0
Total Pages Processed (Index): 815
Total Pages Failing
(Index): 0
Total Pages Processed (Other): 362
Total Pages Processed (Seg)
: 52
Total Pages Failing
(Seg) : 0
Total Pages Empty
: 49361
Total Pages Marked Corrupt
: 0
Total Pages Influx
: 0
Total Pages Encrypted
: 0
Highest block SCN
: 1466473 (0.1466473)
The segment level check has fewer parameters, but the
SEGMENT_ID parameter is a little more complex, i.e. it requires a three-part
value to be specified which requires a data dictionary query to resolve. Here
are its parameters:
|
USERID |
Username/Password |
|
<
FILE |
<
File Name |
|
<
SEGMENT_ID |
<
Tablespace
Name.Segment File.Segment Block |
|
<
Feedback |
<
Integer |
|
<
PARFILE |
<
File Name |
The SEGMENT_ID requires a simple query as shown here
followed by the call to invoke DBVERIFY for those values. Note that this
verification mode requires SYSDBA privileges:
SQL> select tablespace_name, segment_name,
TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK
from sys.sys_user_segs
where tablespace_name='USERS' and SEGMENT_NAME like
'JUNK%';
TABLESPACE_NAME
SEGMENT_NAME
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
---------------- ---------------- -------------
----------- ------------
USERS
JUNK
4
1024
10278
USERS
JUNK2
4
1024
10534
C:\Temp> dbv userid=bert/bert segment_id=4.1024.10278
DBVERIFY: Release 11.1.0.6.0 - Production on Tue Jul 8
15:13:42 2008
Copyright (c) 1982, 2007, Oracle.
All rights reserved.
DBVERIFY - Verification starting: SEGMENT_ID =
4.1024.10278
DBVERIFY - Verification complete
Total Pages Examined
: 32
Total Pages Processed (Data) : 28
Total Pages Failing
(Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing
(Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)
: 3
Total Pages Failing
(Seg) : 1
Total Pages Empty
: 0
Total Pages Marked Corrupt
: 0
Total Pages Influx
: 0
Total Pages Encrypted
: 0
Highest block SCN
: 1466473 (0.1466473)
trcsess
The trcsess command-line utility
offers the DBA a way to combine or consolidate several trace files into a
single trace file based upon the following criteria:
-
Session Id
-
Client Id
-
Service name
-
Action name
-
Module name
This single resulting output trace
file can then be fed to tkprof or other trace file analysis tools, such as
the free Hotsos plug-in for SQL Developer, to debug a particular session.
When using dedicated server processes, monitoring only a single session and
not doing parallel operations, there is little need for the trcsess utility.
But when using shared server processes, monitoring several sessions
concurrently, and/or doing parallel DML, the workload can span multiple trace
files. In fact, it can be located on different nodes in a RAC environment if
the parallel operations cross nodes. The command syntax is as follows:
$ trcsess [options] trace_files
Where the options are:
|
<
output= |
<
Consolidated
output trace file |
|
<
session= |
<
Consolidates the
trace information by session id |
|
<
clientid= |
<
Consolidates the
trace information by client id |
|
<
service= |
< Consolidates the trace information by service name |
|
<
action= |
<
Consolidates the
trace information by action name |
|
<
module= |
<
Consolidates the
trace information by module name |
In the following example, all the trace files in the
temporary directory are consolidated into one big trace file and are doing so
just for SELECT statements:
C:\Temp>trcsess output=one_big.trc service=ORDB1 *.trc
Examining the contents of the
resulting one_big.trc file,
this shows that the trcsess utility
has consolidated five separate trace files into one.
one_big.trc consolidated trace file (
*** [ Windows thread id: 4860 ]
*** 2008-08-03 10:06:16.796
*** 2008-08-03 10:06:16.796
*** 2008-08-03 10:06:16.796
…
*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_4860.trc
***
…
*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_3412.trc
***
…
*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_1072.trc
***
…
*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_1716.trc
***
…
*** TRACE CONTINUED FROM FILE c:\oracle\diag\rdbms\ordb1\ordb1\trace\ordb1_ora_2432.trc
***
…
|
|
Copyright © 1996 - 2011 by
Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.