Simulate and repair data block corruption in Oracle

In Oracle databases, we may encounter a lot of data damages. In these cases, it is very important to recover data.
In many cases, recovery is extremely complex and requires caution. Rash operations and attempts may cause permanent damage to the database.
Therefore, we recommend that you back up data before trying to recover.
For important databases, if the recovery steps and operational consequences cannot be determined, it is recommended that you consult professionals first to avoid unnecessary data losses.

This article simulates and solves the data block damage problem through experiments, and explores the restoration of a type of database damage.

1. Insert data

E: \ Oracle \ ora92 \ bin> sqlplus "/as sysdba" SQL * Plus: Release 9.2.0.4.0-Production on Monday March 8 20:27:15 2004 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. Connect: Oracle9i Enterprise Edition Release 9.2.0.4.0-Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0-Production SQL> select name from v $ datafile; NAME -------------------------------------------------------------------------------- E: \ ORACLE \ ORADATA \ EYGLE \ SYSTEM01.DBF E: \ ORACLE \ ORADATA \ EYGLE \ UNDOTBS01.DBF E: \ ORACLE \ ORADATA \ EYGLE \ EYGLE01.DBF SQL> create tablespace block 2 datafile 'e: \ oracle \ oradata \ eygle \ block. dbf' 3 size 1 M 4 extent management local; The tablespace has been created. SQL> alter user eygle default tablespace block; The user has changed. SQL> alter user eygle quota unlimited on block; The user has changed. SQL> connect eygle/eygle Connected. SQL> create table t as select * from dba_users; The table has been created. SQL> insert into t select * from t; Eight rows have been created. SQL>/ You have created 16 rows. SQL>/ 32 rows have been created. SQL>/ 64 rows have been created. SQL>/ You have created 128 rows. SQL>/ You have created 256 rows. SQL>/ You have created 512 rows. SQL>/ You have created 1024 rows. SQL>/ You have created 2048 rows. SQL>/ You have created 4096 rows. SQL>/ Insert into t select * from t * ERROR is located in row 1st: ORA-01653: Table EYGLE. T cannot be extended through 8 (in tablespace BLOCK) SQL> commit; Submitted. SQL> alter system checkpoint; The system has been changed. SQL> select count (*) from t; COUNT (*) ---------- 8192 SQL> connect/as sysdba Connected. SQL> shutdown immediate The database has been closed. The database has been detached. The ORACLE routine has been disabled.

2. corrupt data files

After the database is closed, use Ultredit to edit the data file and change the character. Then, start the database.

SQL> startup The ORACLE routine has been started. Total System Global Area 101785252 bytes Fixed Size 454308 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes The database has been loaded. The database has been opened. SQL> select count (*) from eygle. t; Select count (*) from eygle. t * ERROR is located in row 1st: ORA-01578: ORACLE data block corruption (file number 4, block number 35) ORA-01110: data file 4: 'e: \ ORACLE \ ORADATA \ EYGLE \ BLOCK. dbf' SQL>

If you query the table, an error occurs, and the data block is damaged and cannot be read.

An internal tool, BBED, can also be used to edit data block content.

3. Use DBV to check data files

E: \ Oracle \ oradata \ eygle> dbv file = block. dbf blocksize = 8192 DBVERIFY: Release 9.2.0.4.0-Production on Monday March 8 20:48:50 2004 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. DBVERIFY-verification is starting: FILE = block. dbf Page 35 marked as corrupt *** Corrupt block relative dba: 0x01000023 (file 4, block 35) Bad check value found during dbv: Data in bad block- Type: 6 format: 2 rdba: 0x01000023 Last change scn: 0x0000.00049097 seq: 0x1 flg: 0x06 Consistency value in tail: 0x90970601 Check value in block header: 0xd6cb, computed block checksum: 0x2c0a Spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Page 69 marked as corrupt *** Corrupt block relative dba: 0x01000045 (file 4, block 69) Bad check value found during dbv: Data in bad block- Type: 6 format: 2 rdba: 0x01000045 Last change scn: 0x0000.00049097 seq: 0x1 flg: 0x06 Consistency value in tail: 0x90970601 Check value in block header: 0x33d1, computed block checksum: 0x653 Spare1: 0x0, spare2: 0x0, spare3: 0x0 *** DBVERIFY-verification completed Total number of checked pages: 128 Total number of processed pages (data): 117 Total number of failed pages (data): 0 Total number of processed pages (INDEX): 0 Total number of failed pages (INDEX): 0 Total number of processed pages (Others): 9 Total number of processed pages (segments): 0 Total number of failed pages (segments): 0 Total number of blank pages: 0 Total number of pages marked as damaged: 2 Total number of imported pages: 0

Dbv detected Bad blocks.

4. seek recovery

In this case, if a backup exists, it must be recovered from the backup.
If there is no backup, the data in the bad parts will be lost.

Export is not allowed at this time:

E: \> exp eygle/eygle file = t. dmp tables = t Export: Release 9.2.0.4.0-Production on Monday March 8 20:54:15 2004 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. Connect to: Oracle9i Enterprise Edition Release 9.2.0.4.0-Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0-Production The ZHS16GBK and AL16UTF16 NCHAR character sets have been exported. To export the specified table, use the regular path... ... Exporting table T... EXP-00056: ORACLE error 1578 ORA-01578: ORACLE data block corruption (file number 4, block number 35) ORA-01110: data file 4: 'e: \ ORACLE \ ORADATA \ EYGLE \ BLOCK. dbf' The export is successfully terminated, but a warning is displayed.

5. Recovery steps

Of course, different situations need to be treated differently.

First, you need to check the damaged object and use the following SQL:

SQL> SELECT tablespace_name, segment_type, owner, segment_name 2 FROM dba_extents 3 WHERE file_id = 4 4 and 35 between block_id AND block_id + blocks-1 5; TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME --------------------------------------------------------------------------- BLOCK TABLE EYGLE T

If data is lost, OK
We can set internal events so that exp can skip these damaged blocks.

SQL> ALTER SYSTEM SET EVENTS = '10231 trace name context forever, level 10 '; The system has been changed.

Note:

Alter system set events = '10231 trace name context forever, level 10 ';

Internal event, set to skip damaged data blocks during full table scan.

Then we can export the data that is not damaged.

SQL> host Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. E :\ E: \> exp eygle/eygle file = t. dmp tables = t Export: Release 9.2.0.4.0-Production on Monday March 8 20:57:13 2004 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. Connect to: Oracle9i Enterprise Edition Release 9.2.0.4.0-Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0-Production The ZHS16GBK and AL16UTF16 NCHAR character sets have been exported. To export the specified table, use the regular path... ... Exporting table T 8036 rows The export is successfully terminated without warning.

At this time, the data is exported successfully.
Then we can drop table, recreate, and import data.

In this example
We lost

8192-8036 = 156 rows of data

SQL> connect eygle/eygle Connected. SQL> drop table t; The table is discarded. SQL> host Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. E: \ Oracle \ ora92 \ bin> cd \ E: \> imp eygle/eygle file = t. dmp tables = t Import: Release 9.2.0.4.0-Production on Monday March 8 21:12:38 2004 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. Connect to: Oracle9i Enterprise Edition Release 9.2.0.4.0-Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0-Production EXPORT files created by EXPORT: V09.02.00 in the normal path The ZHS16GBK Character Set and AL16UTF16 NCHAR character set have been imported. . Importing the EYGLE object to EYGLE .. Importing table "T" 8036 rows Import is terminated successfully, but a warning is displayed. E: \> exit SQL> select count (*) from t; COUNT (*) ---------- 8036

Complete data recovery

Author: eygle | English Version [During reprinting, please be sure to mark the original source and author information of the article as hyperlinks and this statement]
Link: http://www.eygle.com/archives/2004/06/oracle_block_recovery.html