IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
Sometimes You can get ” ORA-19566: exceeded limit of 0 corrupt blocks for file error ” during RMAN Backup .
Details of error are as follows.
RMAN-03009: failure of backup command on ORA_DISK_3 channel at 07/22/2020 18:54:19 ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/MSDB/datafile/test_data.658.962642173 RMAN-03009: failure of backup command on ORA_DISK_2 channel at 07/22/2020 18:22:32 ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/MSDB/datafile/test_data.325.21959565 RMAN-03009: failure of backup command on ORA_DISK_8 channel at 07/22/2020 18:43:24 ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/MSDB/datafile/test_data.873.423432475
This error is related with the Corrupted blocks on database. You can read the following post to learn more details about Block corruption.
Use DBV Tool and Performing an DBV on the above file shows a corrupt block.
dbv file=/<path>/<datafilename>.dbf blocksize=16384
DBVERIFY: Release 9.2.0.4.0 - Production on Thu Aug 30 18:06:59 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = /<path>/<datafilename>.dbf Page 91880 is influx - most likely media corrupt *** Corrupt block relative dba: 0x048166e8 (file 18, block 91880) Fractured block found during dbv: Data in bad block - type: 0 format: 0 rdba: 0x00000000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 consistency value in tail: 0x00000001 check value in block header: 0x0, block checksum disabled spare1: 0x0, spare2: 0x0, spare3: 0x0 *** DBVERIFY - Verification complete Total Pages Examined : 128000 Total Pages Processed (Data) : 10475 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 1154 Total Pages Failing (Index): 0 Total Pages Processed (Other): 645 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 115725 Total Pages Marked Corrupt : 1 Total Pages Influx : 1
You can use the following SQL statement is executed to determine that there is no segment associated
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 18 and 91880 between block_id AND block_id + blocks - 1;
you can use the Block Media Recovery (BMR) method to repair the related blocks as follows.
BLOCKRECOVER DATAFILE 137 BLOCK 3464832; BLOCKRECOVER DATAFILE 137 block 3844717 DATAFILE 38 block 1490618;
You can recover the related block from Disk or Tape backup as follows.
run
{
ALLOCATE CHANNEL CH1 TYPE 'DISK';
BLOCKRECOVER DATAFILE 92 BLOCK 20737;
RELEASE CHANNEL CH1;
}
run
{
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
send 'NSR_ENV=(NSR_CLIENT=NSR_CLIENT_NAME,NSR_SERVER=NSR_SERVER_NAME) ';
BLOCKRECOVER DATAFILE 92 BLOCK 20737;
RELEASE CHANNEL CH1;
}
You can repair all physically corrupted blocks recorded in the view with the following command.
RMAN> RECOVER CORRUPTION LIST;
Now problem has been solved.
Do you want to learn more details about RMAN, then read the following articles.
https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/
