Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).
This month’s question comes to us from Kalyana in Sri Lanka: Is there any tool to check the integrity of data in an Oracle database? I know that in SQL Server this is possible. Or else how can I know that there’s no corruption in data that has been entered, say, long ago—something like a pointer corruption—or is this not an issue with Oracle?
Terry Sutton of the Database Specialists team responds: The primary tool for checking for corruption in an Oracle database is DBVERIFY. It can be used to perform a physical data structure integrity check on data files whether the database is online or offline. The big benefit of this is that DBVERIFY can be used to check backup data files without adding load to the database server. You invoke DBVERIFY from the operating system command line like this:
$ dbv file=data01.dbf logfile=verify.log blocksize=8192 feedback=100
In this example data01.dbf is the data file to check, and the tablespace this file belongs to has a block size of 8192 bytes. The feedback parameter tells DBVERIFY to draw a period on the screen after every 100 pages (blocks) of the file are verified.
In the log file you’ll see output like this:
DBVERIFY - Verification starting : FILE = data01.dbf DBVERIFY - Verification complete Total Pages Examined : 640 Total Pages Processed (Data) : 631 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 9 Total Pages Empty : 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0
The Total Pages Failing values show the number of blocks that failed either the data block or index block checking routine. The Total Pages Marked Corrupt figure shows the number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type. And the Total Pages Influx is the number of blocks for which DBVERIFY could not get a consistent image. (This could happen if the database is open when DBVERIFY is run. DBVERIFY reads blocks multiple times to try to get a consistent image, but DBVERIFY cannot get a consistent image of pages that are in flux.)
If you want to verify only a portion of a data file, you can specify a starting and ending block when running DBVERIFY. If you want to verify the entire database, you can generate a short shell script to run DBVERIFY on every data file in the database. You can do this easily using SQL*Plus:
SQL> SPOOL dbv_on_all_files.sh SQL> SELECT 'dbv file=' || file_name || 2 ' logfile=file' || ROWNUM || 3 '.log blocksize=8192' 4 FROM dba_data_files; SQL> SPOOL OFF
After running the shell script you can quickly scan all of the DBVERIFY log files with Unix commands like:
$ grep Failing file*.log $ grep Corrupt file*.log $ grep Influx file*.log
You can also use DBVERIFY to validate a single data or index segment. To do this you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.
There are other ways to check for database corruption besides DBVERIFY. You can take a full database export, with the dump file optionally specified as a null device. This will read every row in every user table in the database, discovering any corrupted data blocks along the way. However, this technique does not access every index entry or the entire data dictionary.
If you want to check one table and all of its indexes, you can use the ANALYZE statement to read every row of the table, read every entry in each of the table’s indexes, and make sure the table and index data are consistent with each other:
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
This will lock the table, preventing DML on the table, unless you specify the ONLINE keyword. Online validation reduces the amount of validation performed to allow for concurrency.
There are several ways to check for corruption in an Oracle database, but the DBVERIFY tool is the most versatile. DBVERIFY does not limit concurrency or DML while it is running, and it can be run against a database backup. Just remember that if DBVERIFY detects corruption in your database and you are planning to recover the corrupt file from a backup, you should perform a DBVERIFY validation on the backup file before beginning the recovery. This will tell you if the corruption exists in the backup also.
The Fall Conference of the Northern California Oracle Users Group will feature Oracle Corporation’s Tom Kyte as the keynote speaker. Tom is the author of “Effective Oracle by Design” from Oracle Press, and he writes the popular AskTom column online and in print. Historically one of NoCOUG’s most popular speakers, Tom will also present a technical session entitled, “All About Binds.”
The one-day conference will take place at the Computer History Museum in Mountain View, California on Thursday, November 10. There will also be a full day of technical sessions including one from Hamid Minoui of Database Specialists on the topic of Practical Space Management in Data Warehouse Environments.
For more information, including the full schedule, check out http://www.nocoug.org/next.html.
If you missed Oracle OpenWorld 2005 in San Francisco last month, check out the resources still available on Oracle’s website. Even if you didn’t attend the conference, you can access a catalog listing of dozens of sessions where you can search by topic, author, or company. It’s handy to note that many of the session presentations are available for free viewing or download. You’ll find this information on the main Oracle OpenWorld page at http://www.oracle.com/openworld/sanfrancisco/conference/index.html.
Just click on the bullet item “View Session Presentations” and look up your favorite speakers and/or topics! (Don’t miss the red text that provides a username and password to access these presentations.)
Check out this two-part article on Oracle performance tuning in the online magazine Database Journal. The article is written by Steve Callan and is a basic guide for beginners on how to approach performance tuning in Oracle. Part 1 covers Oracle’s performance tuning methodologies and the progression and changes from version 8i to 10g. The article includes basic checklists to follow when tuning, tips for avoiding problematic queries, and general information—again from a beginner’s perspective. Part 2 of the article drills down into more details and covers what you can do to review and tune SQL statements. This includes reviewing execution plans, using bind variables, and efficient SQL.
You’ll find it at http://www.databasejournal.com/features/oracle/article.php/3548291
While you’re visiting the site, don’t miss the complete list of Oracle-related articles at http://www.databasejournal.com/features/oracle/archives.php.