oracle| Data | database | questions
Oracle's data block has a fixed format and structure, divided into three layers: Cache layer, Transaction layer, and data layer.
Do a consistency check when reading and writing a block of data:
–block type
–dba
–scn
–header and tail
Found inconsistent, marked as bad block.
Bad blocks have two kinds: physical bad blocks and logical bad blocks.
The impact of bad blocks: data dictionary tables, rollback segment tables, temporary segments, and user data tables and indexes.
Apply an error:
–ora-1578
–ora-600 and trace file in Bdump directory
First parameter [2000]-[8000]
Range block Layer
-------------------------------------------
Cache Layer 2000–4000
Transaction Layer 4000–6000
Data Layer 6000-8000
The cause of the bad block:
Oracle invokes standard C's system functions to read and write data blocks:
-Bad I/O, h/w, firmware.
-Operating System I/O or caching problems.
-Memory or paging problems.
-Disk repair Utilities.
-part of a datafile being overwritten.
-Third part software incorrectly attempting to access Oracle used heap.
-Oracle or operating system bug.
How to handle bad blocks in a table:
(1). Collect relevant information:
Ora-1578 file# (RFN) block#
Ora-1110 file# (AFN) block#
ora-600 file# (AFN) block#
Select file_name,tablespace_name,file_id "AFN", Relative_fno "RFN" from Dba_data_files;
Select file_name,tablespace_name,file_id, Relative_fno "RFN" from Dba_temp_files;
9i tempfiles Afn=file_id+value of Db_files
(2). Determine the affected object:
SELECT Tablespace_name, Segment_type, owner, Segment_name, partition_name from dba_extents WHERE file_id = <AFN> <BL> between block_id and block_id + blocks-1;
If on Tempfile, no data return;
(3). Depending on the type of object, the processing method is determined:
Objects of Sys
Rollback
Temporary segment
Index and index partition
Cluster |
Partition | Treatment of bad block in ===> table
Table |
(4). Select the appropriate method to salvage the data in the table:
Recover datafile
Recover block only (9i)
Saving data through rowID RANGE SCAN
Using Dbms_repair
Use Event
How to handle bad blocks in tables one: Recover data files
The database is archived and has a full physical backup
OFFLINE the affected data file
ALTER DATABASE datafile ' name_file ' OFFLINE;
Save a file with a bad block, RESTORE backup.
If different from the old location
ALTER DATABASE RENAME FILE ' old_name ' to ' new_name ';
Recover the DataFile
RECOVER datafile ' Name_of_file ';
Online the FILE/S
ALTER DATABASE datafile ' name_of_file ' ONLINE;
How to handle bad blocks in a table two: block recover
Requirements
(1). Database 9.2
(2). Catalog and Rman
(3). The database is archived, with a full physical backup
(4). Using the Blockrecover command of Rman
Rman>run{blockrecover
DataFile 3 Block 4, 5;}
You can force a backup to be used before a certain SCN number to recover a block of data.
Rman>run{blockrecover
DataFile 3 block 4,5 restore until sequence 7402;}
How to deal with bad blocks in a table three: ROWID RANGE SCAN
Use Dbms_rowid to determine ROWID RANGE for bad blocks
Low_rid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create (1,<obj_id>,<rfn>,<bl>,0) from DUAL;
Hi_rid after the corrupt block:
Dbms_rowid.rowid_create (1,<obj_id>,<rfn>,<bl>+1,0) from DUAL;
Build a temporary table
CREATE TABLE salvage_table as SELECT * from Corrupt_tab Where 1=2;
Save data that is not corrupted
INSERT into salvage_table SELECT/*+ ROWID (a) * * from <owner.tablename> A WHERE ROWID < ' <low_rid> ';
INSERT into salvage_table SELECT/*+ ROWID (a)/* from <owner.tablename> A WHERE ROWID >= ' Rebuild Table,index,foreign Constrain table.
How to handle bad blocks in a table four: Add 10231 Event
10231 event is set at the session or database level, and the bad blocks can be skipped when making a full table scan.
Session level:
ALTER session SET EVENTS ' 10231 TRACE NAME context forever,level 10 ';
CREATE TABLE salvage_emp as SELECT * from Corrupt_emp;
Database level:
event= "10231 Trace name Context forever, Level 10"
How to deal with bad blocks in a table five: Dbms_repair
A table marked with a bad block can be skipped when the whole table is scanned.
Execute Dbms_repair. Skip_corrupt_blocks (' <schema> ', ' <tablename> ');
Save data in a table
EXPORT the table.
CREATE TABLE salvage_emp as SELECT * from Corrupt_emp;
How to handle bad blocks in a table six: Check the index
Check the index on the table and the primary key FOREIGN KEY constraint
SELECT Owner,index_name, index_type from dba_indexes WHERE table_owner= ' xxxx ' and table_name= ' xxxx ';
SELECT owner,constraint_name,constraint_type,table_name from dba_constraints WHERE owner= ' xxx ' and table_name= ' xxx ' and
Constraint_type= ' P ';
SELECT owner,constraint_name,constraint_type,table_name from dba_constraints WHERE r_owner= ' xxxx ' and R_constraint_ Name= ' <CONSTRAINT-NAME> ';
How to find bad blocks in advance:
(1). Export Utility
Exp System/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g
does not detect disk corruptions above the high water mark
does not detect corruptions in indexes
does not detect all corruptions in the data dictionary
ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE
Performs the block checks, but does not mark blocks as corrupt.
It also checks that table and index entries match.
Any problems found are reported to the user session trace file in User_dump_dest.
Some important tables can be inspected on a regular basis.
(2). DBV Check Data file
Show Parameter Db_block_size
Select bytes/2048 from V$datafile where file#=5;
DBV file=/dev/rdsk/r1.dbf blocksize=2048 end=5120
DBV expects a filename extension. If on Raw Dev
Ln-s/dev/rdsk/mydevice/tmp/mydevice.dbf
Now use DBV against/tmp/mydevice.dbf
