ORA-19566: exceeded limit of 0 corrupt blocks for file
When we try to the backup a data file or database in RMAN if any block's is corrupted then rman will throw this errorRMAN will not take the backup the corrupted data file . rman will take the backup of the non corrupted blocks only .. the difference between the user managed backup and rman backup is rman will take only used block's ..but if Any blocks are corrupted then it will Not backup that file ..
If you are backup up the full database you can exclude the particular corrupted data file from the backup script and then format the block's and then take the backup.. if the corrupted file is the important file like system data file then try to format first
Here In this scenario we don't have backup of the data file which the block had been corrupted ..as per the below example the system data file 9 has the block corruption.. and we need to recover the blocks which are corrupted with out backup of the data file
Step 1
when we try to backup the particular data file we are getting the error mentioned below
RMAN> backup datafile 352;
Starting backup at 29-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 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=00352 name=/p01/app/PROD/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/29/2012 12:25:04
ORA-19566: exceeded limit of 0 corrupt blocks for file /p01/app/PROD/db/apps_st/data/system09.dbf
Step 2
To verify the blocks corrupted we need to run the dbv command line utility
Syntax dbv file='location of the file'
oraprod@proddb ~]$ dbv file=/p01/app/PROD/db/apps_st/data/system09.dbf
DBVERIFY: Release 11.1.0.7.0 - Production on Mon Oct 29 15:58:05 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /p01/app/PROD/db/apps_st/data/system09.dbf
Page 165665 is marked corrupt
Corrupt block relative dba: 0x58028721 (file 352, block 165665)
Completely zero block found during dbv:
......................................
......................................
Page 191995 is marked corrupt
Corrupt block relative dba: 0x5802edfb (file 352, block 191995)
Completely zero block found during dbv:
Page 191996 is marked corrupt
Corrupt block relative dba: 0x5802edfc (file 352, block 191996)
Completely zero block found during dbv:
Page 191997 is marked corrupt
Corrupt block relative dba: 0x5802edfd (file 352, block 191997)
Completely zero block found during dbv:
Page 191998 is marked corrupt
Corrupt block relative dba: 0x5802edfe (file 352, block 191998)
Completely zero block found during dbv:
Page 191999 is marked corrupt
Corrupt block relative dba: 0x5802edff (file 352, block 191999)
Completely zero block found during dbv:
Page 192000 is marked corrupt
Corrupt block relative dba: 0x5802ee00 (file 352, block 192000)
Completely zero block found during dbv:
..................
..................
DBVERIFY - Verification complete
Total Pages Examined : 192000
Total Pages Processed (Data) : 131617
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 30473
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1908
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1666
Total Pages Marked Corrupt : 26336
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3721895981 (1388.3721895981)
Once you conform through dbv the blocks are corrupted then you need to run the Rman validating script
Rman script to validate
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
backup check logical validate database;
release channel d1;
release channel d2;
release channel d3;
}
it will produce the error in the alert log for all the corrupted blocks it will validate all the blocks corrupted and populate into the v$database_block_corruption Once the Rman command completes then you can proceed to the next step ..it is mandatory to complete this step
Step 3
Check the no of blocks corrupted
SQL> Select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTI
---------- ---------- ---------- ------------------ ---------
352 165660 26341 0 ALL ZERO
note the it is the data file number which is effected and the block number
Step 4
Check the corrupted block belongs to any object
SYNTEX :select segment_name, segment_type, owner
from dba_extents
where file_id = <data_file_name>
and < corrupted block number >between block_id
and block_id + blocks -1;
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = 352
and 165660 between block_id
and block_id + blocks -1;
It will return now rows selected and then proceed with the steps if it returns now row selected then the particular block does not belongs to any object and it is empty
To conform the empty block run the below query
SYNTAX
Select * from dba_free_space where file_id= < corrupted data file number>
and < corrupted block number>between block_id and block_id + blocks -1;
SQL>Select * from dba_free_space where file_id= 352
and 165660 between block_id and block_id + blocks -1;
Step 5
Run the below sql command to find the details of the Corrupted block here we save the below command as the block_curropt.sql
set lines 200 pages 10000
col segment_name format a30
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;
Once you conform the blocks are not belonging to any Object then proceed with the next step
Step 6
Create a table from the normal user [here we use the scott ]
SQL> create table om (
n number,
c varchar2(4000)
) nologging tablespace system ;
Table created.
Table should be created on the Corrupted data file's table space [here system data file's blocks are Corrupted so we use the data file as system ]
SQL> select segment_name,tablespace_name from user_segments
where segment_name='S' ; 2
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
OM SYSTEM
Step 7
Create the trigger for the table which we have created on the corrupted data file
SQL> CREATE OR REPLACE TRIGGER scott.corrupt_trigger
2 AFTER INSERT ON scott.om
3 REFERENCING OLD AS p_old NEW AS new_p
4 FOR EACH ROW
5 DECLARE
6 corrupt EXCEPTION;
7 BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
8 9 10 RAISE corrupt;
END IF;
11 12 EXCEPTION
13 WHEN corrupt THEN
14 RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
15 END;
16 /
Enter value for blocknumber: 165660
old 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=165660)
Enter value for filenumber: 352
old 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=352) THEN
Trigger created.
Step 8
Allocate the space to the newly create table
SYNTAX Select BYTES from dba_free_space where file_id=<file_number> and < corrupted block_number >between block_id and block_id + blocks -1;
SQL> Select BYTES from dba_free_space where file_id=352 and 192000 between block_id and block_id + blocks -1;
BYTES
----------
771612672
Make sure the data file is not in auto extend on if it is on then disable it allocate the above query's answer[free_space] to the newly created object
SQL> alter table scott.om
allocate extent (DATAFILE '/p01/app/PROD/db/apps_st/data/system09.dbf' SIZE 735M); 2
Table altered.
once the table is assigned to the particular data file check the detail's
SQL> select segment_name,tablespace_name from user_segments
where segment_name='OM' ;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
OM SYSTEM
Step 9
Inset values in to the table by using the below loop
The data is inserted in to the table for formatting the block
SQL> BEGIN
FOR i IN 1..100000000 LOOP
INSERT /*+ APPEND */ INTO scott.om select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
/
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SCOTT.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.CORRUPT_TRIGGER'
ORA-06512: at line 4
SQL> commit;
we need to commit the data which is inserted into the table
After committing the data switch log file's
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
Once the data is inserted in to table then check the block corruption
Step 10
[oraprod@proddb]$ dbv file = /p01/app/PROD/db/apps_st/data/system09.dbf
DBVERIFY: Release 11.1.0.7.0 - Production on Mon Oct 29 16:29:06 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /p01/app/PROD/db/apps_st/data/system09.dbf
DBVERIFY - Verification complete
Total Pages Examined : 97920
Total Pages Processed (Data) : 63873
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 30473
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1908
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1666
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3721899454 (1388.3721899454)
[oraprod@proddb trace]$ rman
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Oct 29 16:30:13 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: PROD (DBID=209199817)
RMAN> backup datafile 352;
Starting backup at 29-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 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=00352 name=/p01/app/PROD/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-12
channel ORA_DISK_1: finished piece 1 at 29-OCT-12
piece handle=/p01/app/PROD/db/tech_st/11.1.0/dbs/ndnovt7p_1_1 tag=TAG20121029T163153 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-OCT-12
Starting Control File Autobackup at 29-OCT-12
piece handle=/p01/app/PROD/db/tech_st/11.1.0/dbs/c-209199817-20121029-00 comment=NONE
Finished Control File Autobackup at 29-OCT-12
RMAN> exit
Recovery Manager complete.
[oraprod@proddb ]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 29 16:33:17 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$database_block_corruption;
no rows selected
Step 10
Drop the table and the trigger which we have created
NOTE
There are some other options which you can try
we can shrink the data file according to the free block's
SQL> Select BYTES from dba_free_space where file_id=352 and 192000 between block_id and block_id + blocks -1;
BYTES
----------
771612672
here we around 735 MB of free space and the original data file size is 1500 here we need to minus the original size for the of the file from the free space
so it will be 1500-735 =765
Resize the data file to the 765 and check
if you have the backup and the archive log then from the Rman you can issue the below command to recover the blog
blockrecover corruption list;
it will automatically recover the corrupted blocks
No comments:
Post a Comment