Oracle's data block has a fixed format and structure, divided into three layers: Cache Layer, Transaction Layer and Data Layer. When reading and writing data blocks, do a consistency check: -block type-dba-scn -header and tail discovery Not consistent, marked as a bad block.
There are two bad blocks: physical bad blocks and logic bad blocks.
Effects of bad blocks: data dictionary tables, rollback segments, temporary sections, and user data tables and indexes. Application 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
Causes of bad blocks: Oracle calls standard C system functions, read and write data blocks: - BAD I / O, H / W, Firmware.- OPERATING SYSTEM I / O OR CACHING Problems.- Memory or Page 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.
Treatment method: (1). Collection 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) determining the affected objects: SELECT tablespace_name, segment_type. , owner, segment_name, partition_name FROM dba_extents WHERE file_id = and between block_id aND block_id blocks - 1; If on tempfile, no data return; (3) according to the object type determining processing method:. objects of sysrollbackTemporary SegmentIndIndex and index partitioncluster | partition | ===> Table | (4). Select the appropriate method Rescue data in the rescue table: Recover DataFileRecover Block Only (9i) Save Data with DBMS_REPAIR with Event by Rowid Range SCAN Method of processing method of bad blocks in the table: Recovery Data File Database As archive, complete physical backup OFFLINE The Affected Data file alter Database datafile 'name_file' offline; saves a file with bad blocks, RESTORE backup. if different from the old locationALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Recover the datafileRECOVER DATAFILE 'name_of_file'; Online the file / sALTER DATABASE DATAFILE 'name_of_file' ONLINE; treatment table bad block two: block recover requirements ( 1). Database 9.2 (2) .catalog and RMAN (3). The database is archived, there is a complete physical backup (4). Use RMAN's blockRecover command RMAN> Run {BlockRecover DataFile 3 block 4, 5;} can force Restore data blocks using a backup before an SCN number. Run> Run {BlockRecover DataFile 3 Block 4,5 Restore Until Sequence 7402;
Table of bad blocks in the table 3: Rowid Range scan uses dbms_rowid to determine bad block Range Low_rid Inside the Corrupt Block: Select DBMS_ROWID.ROWID_CREATE (1, , , , 0) from DUAL; Hi_rid after the corrupt block: dbms_rowid.rowid_create (1, , , 1, 0) from dual; built a temporary table create Table Salvage_table asselection * from corrupt_tab where 1 = 2; save Damaged Data INSERT INTO SALVAGE_TABLE SELECT / * ROWID (A) * / * from a where rowid <''; Insert Into Salvage_Table SELECT / * ROWID (A) * / * from A WHERE ROWID> = ''; Rebuilding Table, Index, Foreign Constrain Table. Treatment method of bad blocks in the table 4: add 10231 Event Set 10231 Event, do full table scanning, bad blocks may be skipped .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"
Working in the table 5: DBMS_REPAIR marks the table with bad blocks, when doing full menu, you can skip bad blocks .Execute dbms_repair.skip_corrupt_blocks ('', ''); save the table Data Export The Table. Create Table Salvage_emp as Select * from Corrupt_emp;
Processing method table bad block VI: index and primary key foreign key in the audit trail check table 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' ANDconstraint_type = 'P'; SELECT owner, constraint_name, constraint_type, table_name FROM dba_constraints WHERE r_owner = 'xxxx' AND r_constraint_name = ''; how pre found bad blocks: (1) .Export utilityexp system / manager full = y log = exp_db_chk.log file = / dev / null volsize = 100g does not detect disk corruptions above the high water markdoes not detect corruptions in indexesdoes not detect all corruptions in the data dictionaryANALYZE 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 into the user session trace file in USER_DUMP_DEST. periodically on some important Type check. (2) .dbv Check Data file show parameter db_block_size select bytes / 20 48 from v $ datafile where file # = 5; dbv file = / dev / rdsk / r1.dbf blocksize = 2048 end = 5120 dbv expects a filename extension. If on Raw DEVLN -S / DEV / RDSK / MyDevice / TMP / MyDevice .dbf now use dbv against /tmp/mydevice.dbf
