Transcript Document

Dealing with Database Corruption
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Describe causes of database corruption:
– Hardware
– Software
•
Detect database corruption using:
–
–
–
–
•
11-2
ANALYZE
dbverify
DB_BLOCK_CHECKING
DBMS_REPAIR
Repair corruptions using RMAN
Copyright © 2004, Oracle. All rights reserved.
What Is Block Corruption?
•
Whenever a block is read or written, a consistency
check is performed:
– Block version
– DBA (data block address) value in cache as
compared to the DBA value in the block buffer
– Block-checksum, if enabled
•
There are two types of block corruption:
– Media corrupt
– Soft corrupt
11-3
Copyright © 2004, Oracle. All rights reserved.
Block Corruption Symptoms: ORA-1578
ORA-01578: “ORACLE data block corrupted
(file # %s, block # %s)”
• This error is generated when a corrupted data
block is found.
• The error always returns the absolute file number
and block number.
• Check the alert.log file.
11-4
Copyright © 2004, Oracle. All rights reserved.
DBVERIFY Utility
•
•
•
Only works on data files; redo log files cannot be
checked.
Checks block consistency.
Can be used while the database is open.
$ dbv file=/u01/oradata/users01.dbf \
blocksize=8192
11-5
Copyright © 2004, Oracle. All rights reserved.
Interpreting DBVERIFY
•
•
Page number is the block number in the data file.
If head and tail do not match, DBVERIFY re-reads
the block. If they match, an influx block is
reported, otherwise a corruption is signaled.
DBVERIFY - Verification complete
Total Pages Examined
: 640
Total Pages Processed (Data) : 88
Total Pages Failing
(Data) : 0
Total Pages Processed (Index): 18
Total Pages Failing
(Index): 0
...
Total Pages Empty
: 406
Total Pages Marked Corrupt
: 0
Total Pages Influx
: 0
11-6
Copyright © 2004, Oracle. All rights reserved.
The ANALYZE Command
•
•
•
Performs a logical block check
Does not mark blocks as soft corrupt; only reports
them
Validates index and table entries
SQL> ANALYZE TABLE table_name VALIDATE
2 STRUCTURE CASCADE;
SQL> ANALYZE INDEX index_name VALIDATE
2 STRUCTURE;
11-8
Copyright © 2004, Oracle. All rights reserved.
Initialization Parameter
DB_BLOCK_CHECKING
•
Can be set by using the ALTER SESSION or ALTER
SYSTEM DEFERRED command
•
Controls whether the database performs block
checking for data blocks
Default value is FALSE
•
•
11-9
Is helpful in preventing memory and data
corruption
Copyright © 2004, Oracle. All rights reserved.
How to Handle Corruptions
•
•
•
•
11-10
Check the alert log and operating system log file.
Use available diagnostic tools to find out the type
of corruption.
Determine whether the error persists by running
checks multiple times.
Recover data from the corrupted object if
necessary.
Copyright © 2004, Oracle. All rights reserved.
How to Handle Corruptions
•
Resolve any hardware issues:
– Memory boards
– Disk controllers
– Disks
•
11-11
Recover or restore data from the corrupt object
if necessary.
Copyright © 2004, Oracle. All rights reserved.
Using Flashback for Logical Corruption
Flashback
Versions
Query
Flashback
Transaction
Query
DBA
Corrupt
data found
USER
11-12
Copyright © 2004, Oracle. All rights reserved.
Undo SQL
or
Flashback
Table
The DBMS_REPAIR Package
Available procedures:
• CHECK_OBJECT
• FIX_CORRUPT_BLOCKS
• DUMP_ORPHAN_KEYS
• REBUILD_FREELISTS
• SEGMENT_FIX_STATUS
• SKIP_CORRUPT_BLOCKS
• ADMIN_TABLES
11-13
Copyright © 2004, Oracle. All rights reserved.
Using DBMS_REPAIR
1. Detect and report corruptions.
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
schema_name => ‘HR',
object_name => 'DEPARTMENTS',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
END;
2. Evaluate the costs and benefits of DBMS_REPAIR.
11-14
Copyright © 2004, Oracle. All rights reserved.
Using DBMS_REPAIR
3. Make objects usable.
SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
schema_name => 'HR',
object_name => 'DEPARTMENTS',
object_type => DBMS_REPAIR.TABLE_OBJECT,
repair_table_name => 'REPAIR_TABLE',
fix_count => num_fix);
END;
11-16
Copyright © 2004, Oracle. All rights reserved.
Using DBMS_REPAIR
4. Repair corruptions and rebuild lost data.
SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
schema_name => 'SCOTT',
object_name => 'PK_DEPT',
object_type => DBMS_REPAIR.INDEX_OBJECT,
repair_table_name => 'REPAIR_TABLE',
orphan_table_name => 'ORPHAN_KEY_TABLE',
key_count => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' ||
TO_CHAR(num_orphans));
END;
11-17
Copyright © 2004, Oracle. All rights reserved.
Block Media Recovery (BMR)
•
•
BMR lowers the mean time to recover (MTTR).
BMR increases availability during media recovery.
– The data file remains online during recovery
– Only blocks being recovered are inaccessible
•
BMR is invoked through RMAN via the
BLOCKRECOVER command.
– Restores individual blocks from available backups
– Coordinates with the server to have them recovered
Tape
11-18
Copyright © 2004, Oracle. All rights reserved.
The BLOCKRECOVER Command
•
•
•
•
BMR is implemented through the RMAN
BLOCKRECOVER command.
BLOCKRECOVER identifies the backups containing
the blocks to recover.
The command reads the backups and
accumulates requested blocks into in-memory
buffers.
BLOCKRECOVER manages the block media recovery
session by reading the archive logs from backup if
necessary.
RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 3;
11-19
Copyright © 2004, Oracle. All rights reserved.
RMAN BMR Interface
•
•
The list of corrupted database blocks is stored in
the V$DATABASE_BLOCK_CORRUPTION view.
The CORRUPTION LIST clause specifies the
recovery of all blocks that are listed in this view.
RMAN> BLOCKRECOVER CORRUPTION LIST
2> RESTORE UNTIL TIME 'sysdate – 10';
•
RMAN lists corruptions in backup sets and proxy
copies in two views:
– V$BACKUP_CORRUPTION
– V$COPY_CORRUPTION
11-20
Copyright © 2004, Oracle. All rights reserved.
Examples of BLOCKRECOVER
•
•
•
•
11-21
Recovering a group of corrupt blocks
Limiting block media recovery by type of restore
Limiting block media recovery by backup tag
Limiting block media recovery by time, SCN, or log
sequence
Copyright © 2004, Oracle. All rights reserved.
Which Object Is Corrupted?
•
Table: The data in the corrupted block is lost
– Drop the table and re-create it, and import data from
an export dump
– Use SQL or PL/SQL to pull data out of the table into
a newly created table
•
11-23
Index: Drop and recreate the index
Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe causes of database corruption:
– Hardware
– Software
•
Detect database corruption using:
–
–
–
–
•
11-24
ANALYZE
dbverify
DB_BLOCK_CHECKING
DBMS_REPAIR
Repair corruptions using RMAN
Copyright © 2004, Oracle. All rights reserved.
Practice 11 Overview:
Dealing with Database Corruption
This practice covers the following topics:
• Using dbverify to detect database corruption
•
11-25
Performing block media recovery with RMAN
Copyright © 2004, Oracle. All rights reserved.