Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Information in this document applies to any platform.
Goal
This document describes a procedure to format a corrupt datablock using RMAN which is not part of any segment.
Typically, after a block corruption is found, we determine whether the corrupt block belongs to a table or index. If it's a index block, we can simply re-create the index. If it's a table block we can perform rman block recovery or datafile restore/recovery. If we do not have suitable backups, we salvage the table data into another table and then drop/truncate the corrupted table. So, if you have resolved the corruption using any means other then restore/recovery (or rman block media recovery), we will end up with the corrupt block which now does not belong to the original table/index i.e. it does not belong to any object.
In such situation, although the corrupt block does not belong to any object, it will still show as corrupt in DBV and you might want to format the same. For eg, say we found corruption as below:
SQL> select count(*) from myobj_demo;
select count(*) from myobj_demo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 150)
ORA-01110: data file 7: '/oradata/ora11gR2/demo01.dbf'
After salvaging the table data, we dropped the corrupt table. Now, DBA_EXTENTS will show that this block does not belong to any segment. This is also confirmed by DBA_FREE_SPACE:
SQL> drop table myobj_demo purge;
Table dropped.
SQL> SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 7 and 150 between block_id AND block_id + blocks - 1;
no rows selected
SQL> select TABLESPACE_NAME,RELATIVE_FNO,BYTES from DBA_FREE_SPACE where FILE_ID=7 and 150 between BLOCK_ID AND BLOCK_ID + BLOCKS -1;
TABLESPACE_NAME RELATIVE_FNO BYTES
------------------------------ ------------ ----------
DEMO 7 65536
However, the block will still be seen as corrupt in DBV:
$ dbv file=/oradata/ora11gR2/demo01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 21 12:23:09 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/ora11gR2/demo01.dbf
Page 150 is marked corrupt
Corrupt block relative dba: 0x01c00096 (file 7, block 150)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x00000096
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000006ff
check value in block header: 0x7fe2
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 356
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 151
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12292
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 775154 (0.775154)
In such situation, we can use this procedure to format the corrupt block. However, note that this procedure can ONLY be used if RMAN backup does not fail for this corrupt block. Normally we expect that RMAN will not read this block due to it's unused block optimization algorithm. However, this is not always true. RMAN reads the bitmaps stored in a locally-managed datafile header to deduce whether a particular extent is free or used. Based on how these bitmaps are managed and read by rman, its not always guaranteed that RMAN will skip all extents which are currently un-used. Some extents might still be read by rman for backups even though they are currently un-used. So we cannot be sure if RMAN will skip the extent beloging to the corrupt block or try to read it. If RMAN reads it, it will fail with error ORA-19566 due to the corrupt block which means we cannot use this procedure. Instead we'd have to use below document:
Document 336133.1 How to Format Corrupted Block Not Part of Any Segment
Fix
As per above section, we can use this procedure if RMAN backup has not failed while reading the corrupt block. To determine the same, run a backup on the datafile having the corrupt block:
RMAN> backup check logical datafile 7 format '/oradata/backup/%U' tag 'CORRUPT_BLK_FILE_BKP';
Starting backup at 21-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/ora11gR2/demo01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAY-12
channel ORA_DISK_1: finished piece 1 at 21-MAY-12
piece handle=/oradata/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAY-12
Ensure that the location where backup-piece will be created has sufficient space. You can change this destination using the FORMAT clause. Also, even though the backup seems successful, crosscheck using below query that the corrupt block is not contained in the backup:
SQL> select BP.HANDLE, BP.COMPLETION_TIME, BC.FILE#, BC.BLOCK#, BC.BLOCKS, BC.MARKED_CORRUPT, BC.CORRUPTION_TYPE
2 from V$BACKUP_PIECE BP, V$BACKUP_CORRUPTION BC
3 where BP.SET_COUNT = BC.SET_COUNT and
4 BP.SET_STAMP = BC.SET_STAMP and
5 BP.TAG = 'CORRUPT_BLK_FILE_BKP';
no rows selected
If the above query returns rows showing the corrupt block, we cannot use this procedure. In above case, since it has not returned rows, we can be sure that RMAN has skipped the corrupt block due to unused block optimization algorithm described above. Now, if the datafile is restored from this backup, RMAN will format and restore a empty copy of the corrupt block which can then be used for rman block recovery as below.
1. Restore the datafile to alternate location:
RMAN> run {
2> set newname for datafile 7 to '/oradata/ora11gR2/demo01_RESTORED.dbf';
3> restore datafile 7 from tag 'CORRUPT_BLK_FILE_BKP';
4> }
executing command: SET NEWNAME
Starting restore at 21-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oradata/ora11gR2/demo01_RESTORED.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/1jnbhl5c_1_1
channel ORA_DISK_1: piece handle=/oradata/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 21-MAY-12
2. Run DBV on the restored datafile to verify it is free from corruption:
$ dbv file=/oradata/ora11gR2/demo01_RESTORED.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 21 12:27:21 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/ora11gR2/demo01_RESTORED.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 12799
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 775154 (0.775154)
3. Run BLOCKRECOVER command as below to repair the corrupt block. The corrupt block will be replaced by the empty, formatted block from the restored datafile:
RMAN> blockrecover datafile 7 block 150 FROM DATAFILECOPY;
Starting recover at 21-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: restoring block(s) from datafile copy /oradata/ora11gR2/demo01_RESTORED.dbf
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-MAY-12
4. Run DBV on the original file to confirm that it is now free from corruption:
[oracle@vmOraLinux6 ~]$ dbv file=/oradata/ora11gR2/demo01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 21 12:30:15 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata/ora11gR2/demo01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 356
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 152
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12292
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 775154 (0.775154)
5. Additionally, you can run VALIDATE in RMAN to verify further:
RMAN> backup validate check logical datafile 7;
Starting backup at 21-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oradata/ora11gR2/demo01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 12292 12801 775154
File Name: /oradata/ora11gR2/demo01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 356
Index 0 0
Other 0 152
Finished backup at 21-MAY-12
Niciun comentariu:
Trimiteți un comentariu