Block Recovery
Example scenario:
The data blocks contained in files can occasionally become corrupt, this scenario describes how to recover from this situation.
– The database is currently in Archivelog mode and there is a backup that can be used.
To simulate corruption of a datablock (or of bad sectors) use a hex editor to overwrite the numbers within the dbf with 0s.
RMAN (Recovery Manager) will then be used to try to fix this problem.
NOTE: A rman-03002 failure of backup plus archive log command indicates that archivelogs aren’t present in a system, this error message usually appears after the archivelogs have manually been deleted.
|
1 2 3 |
RMAN> crosscheck archivelog all RMAN> delete noprompt expired archivelog all |
The following commands solve the issue.
|
1 2 3 |
SQL> alter system switch log file ; RMAN> sql 'alter system switch log file'; |
|
1 |
RMAN> backup archivelog all delete input ; |
First take a backup of the archivelogs and delete the originals. This prevents archivelogs from becoming too large.
This method only makes backups of the archives, the backups can be saved on portable drives such as usb flash disks.
|
1 |
RMAN> backup database ; |
The previous command backups the dbf files and prevents the archivelogs from becoming obsolete.
On the weekend, run the following
|
1 |
RMAN> backup database plus archivelog; |
This way backups can be between weekends and the original files can then be deleted.
Creating a tablespace
|
1 2 3 4 5 6 7 8 |
SQL> CREATE TABLESPACE test DATAFILE 2 '/oracle/oradata/data/test01.dbf' SIZE 512K AUTOEXTEND ON NEXT 100K MAXSIZE 1M 3 LOGGING 4 PERMANENT 5 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 6 BLOCKSIZE 8K 7 SEGMENT SPACE MANAGEMENT MANUAL 8 FLASHBACK ON; |
Creating a table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> CREATE TABLE HR.personel 2 ( 3 name VARCHAR2(20), 4 "surname" VARCHAR2(20), 5 adress VARCHAR2(200), 6 tel NUMBER(15), 7 email VARCHAR2(20) 8 ) 9 TABLESPACE TEST 10 LOGGING 11 NOCOMPRESS 12 NOCACHE 13 NOPARALLEL 14 NOMONITORING; |
Entering information into a table
|
1 2 3 |
SQL> insert into hr.personel values('mehmet','derviş','Kadikoy',2165555555,'mehmet at sysdba.org'); SQL> insert into hr.personel select * from hr.personel; (repeatedly running these types of commands will fill the tablespace) |
Shutdown the database after the data entry. Using a hex editor open test01.dbf and overwrite numeric data with 0s (to simulate a corrupt file).
Restart the database
|
1 |
SQL> select * from HR.personel ; |
The database is able to retrieve the data where it can read it and where it can’t retrieve the data, it displays the following:
|
1 2 3 |
ERROR: ORA-01578: ORACLE data block corrupted (file # 6, block # 33) ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf' |
As the output shows, the 33rd block in file no. 6 is corrupt
|
1 |
SQL> select count(*) from hr.personel; |
Running the above command outputs a more specific error
|
1 2 3 |
ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 33) ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf' |
This error could also be due to a bad sector (an actual physical error on the disk), alternatively could be due to 2 background processes simultaneously attempting to write to the same location.
Reviewing the alertlog would reveal more details about the error
The error in the alertORATR.log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Wed Jun 1 14:14:42 2011 Hex dump of (file 6, block 33) in trace file /oracle/admin/oratr/udump/oratr_ora_227.trc Corrupt block relative dba: 0x01800021 (file 6, block 33) Bad check value found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x01800021 last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x654d0601 check value in block header: 0x7df7 computed block checksum: 0xd052 Reread of rdba: 0x01800021 (file 6, block 33) found same corrupted data Wed Jun 1 14:14:49 2011 Corrupt Block Found TSN = 8, TSNAME = TEST RFN = 6, BLK = 33, RDBA = 25165857 OBJN = 53626, OBJD = 53626, OBJECT = PERSONNEL, SUBOBJECT = SEGMENT OWNER = HR, SEGMENT TYPE = Table Segment |
|
1 |
SQL> analyze table hr.personnel validate structure ; |
Running the previous code would output the same error
|
1 2 3 4 5 |
SQL> analyze table hr.personel validate structure * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 33) ORA-01110: data file 6: '/oracle/oradata/data/test01.dbf' |
|
1 |
dbv file=test01.dbf logfile=test01.txt |
The contents of test01.txt
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DBVERIFY: Release 10.2.0.2.0 - Production on Wed Jun 1 14:25:17 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = test01.dbf Page 33 is marked corrupt Corrupt block relative dba: 0x01800021 (file 6, block 33) ''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x01800021 last change scn: 0x0000.0024654d seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x654d0601 check value in block header: 0x7df7 computed block checksum: 0xd052 DBVERIFY - Verification complete Total Pages Examined : 256 Total Pages Processed (Data) : 117 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 18 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 120 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 2391554 (0.2391554) |
Total Pages Marked Corrupt being 0 means that there are no issues on the database.
To repair the blocks. Note: the following blockrecover code is only applicable when there is an available backup.
|
1 |
RMAN> blockrecover datafile 6 block 33; |
|
1 |
RMAN> blockrecover datafile 6 block 33; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Starting blockrecover at 01-JUN-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00006 channel ORA_DISK_1: reading from backup piece /oracle/rman2/dbf_ORATR_752680336_108.rman channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/oracle/rman2/dbf_ORATR_752680336_108.rman tag=DBF_MANUAL_01062011 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:05 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished blockrecover at 01-JUN-11 |
|
1 2 3 4 |
SQL> select count(*) from hr.personel; COUNT(*) ---------- 12288 |
