Oracle RMAN backup and check logical bad blocks

1. During RMAN backup, the physical bad blocks are checked by default.

2. To check the logical bad blocks, use the following statement:

$ Rman target/
RMAN> backup check logical validate database;

Note that the preceding statements are only checked and will not be backed up.

3. If you want to check the logical bad blocks while backing up, you can use:

$ Rman target/
RMAN> backup check logical database;

4. If you find out how to deal with bad logic, I will add a tutorial below.

Use RMAN to detect bad database block scripts

Although we can also use the dbv (db file verify) tool to detect bad blocks of a single data file, we can directly use RMAN's "backup validate check logical database; it is much easier to combine the V $ DATABASE_BLOCK_CORRUPTION view.

1) $ rman target/nocatalog

2) RMAN> run {
Allocate channel d1 type disk;
Allocate channel d2 type disk;
Allocate channel d3 type disk;
Allocate channel d4 type disk;
Backup validate check logical database;
}

3) select * from V $ DATABASE_BLOCK_CORRUPTION;

REM www.askmaclean.com & www.askmaclean.com

4) If V $ database_block_partition uption contains rows please run this query
Find the objects that contains the specified upted blocks:

SELECT e. owner,
E. segment_type,
E. segment_name,
E. partition_name,
C. file #,
Greatest (e. block_id, c. block #) pai_start_block #,
Least (e. block_id + e. blocks-1, c. block # + c. blocks-1) pai_end_block #,
Least (e. block_id + e. blocks-1, c. block # + c. blocks-1 )-
Greatest (e. block_id, c. block #) + 1 blocks_upted,
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 pai_start_block #,
Header_block pai_end_block #,
1 blocks_upted,
'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 #) pai_start_block #,
Least (f. block_id + f. blocks-1, c. block # + c. blocks-1) pai_end_block #,
Least (f. block_id + f. blocks-1, c. block # + c. blocks-1 )-
Greatest (f. block_id, c. block #) + 1 blocks_upted,
'Free block' description
FROM dba_free_space f, v $ database_block_uption 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 #, pai_start_block #;

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = & fileid
And & blockid between block_id AND block_id + blocks-1;

Supplement:

Start backup from April 11 to April 04
Use channel ORA_DISK_1
Channel ORA_DISK_1: starting the full data file backup set
Channel ORA_DISK_1: specifies the data file in the backup set.
Input data file fno = 00028 name = E: ORACLEORADATAORABD02TS_TEST01.DBF
Channel ORA_DISK_1: Starting segment 1 from April 11 to April 04
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/28/2004 15: 45:
51
ORA-19566: exceeds corrupt block limit 0 (File E: ORACLEORADATAORABD02TS_TEST01.DBF)

>>>>>>>>>>>>>>>>>>>>>>>>>>>
Scan database physical errors and logical errors
>>>>>>>>>>>>>>>>>>>>>>>>>>>
RMAN> backup validate datafile 28;

Start backup from April 11 to April 04
Use channel ORA_DISK_1
Channel ORA_DISK_1: starting the full data file backup set
Channel ORA_DISK_1: specifies the data file in the backup set.
Input data file fno = 00028 name = E: ORACLEORADATAORABD02TS_TEST01.DBF
Channel ORA_DISK_1: Backup set completed. Elapsed time: 00: 00: 01
Backup completed from April 11 to April 04

Note: some text is generated during the rman validate operation, which looks like a backup set is created.

However, it is only an error scan operation. During The rman operation, no backup file slices will be generated.

RMAN>

SQL> select * from V $ backup_partition uption;

Recid stamp SET_STAMP SET_COUNT PIECE # FILE # BLOCK # BLOCKS Upload uption_change # MARKED
--------------------------------------------------------------------------------------------
1 543426383 543426382 31 1 28 20 1 0 YES FRACTURED

Used Time: 00: 00: 00.00
SQL> select * from v $ database_block_corruption;

FILE # BLOCK # BLOCKS corrcorruption_change # CORRUPTION_TYPE
------------------------------------------------------------------
28 20 1 0 FRACTURED

Used Time: 00: 00: 00.00
SQL>
 
Let's take a look at whether the bad block is on the table or the index.

SELECT segment_name, segment_type, extent_id, block_id, blocks
From dba_extents t
Where file_id = 6
AND 187 between block_id and (block_id + blocks-1 );

Good luck. You can simply re-build the index.
For example:
My database has a ORA-01578 error: data block corruption (File 8, block 36385)

Use the following statement to query:
SELECT segment_name, segment_type FROM dba_extents WHERE file_id = 8 AND 36385 BETWEEN block_id AND block_id + blocks-1;

Result:
Segment_name segment_type
-----------------------------------------------
PK_KC03 INDEX

Some articles describe that if segment_type is an INDEX, you only need to delete the INDEX and recreate it.
Usage:
Alter index pk_kc03 rebuild nologging;
Or
Alter index pk_kc03 rebuild

Dbms_repair.skip_corrupt_blocks
-------------------------------------------
 
 
 

April 16 16:30:19 ur_bak01: NetWorker savegroup: (alert) urmdborafull completed, total 2 client (s), 0 Hostname (s) Unresolved, 1 Failed, 1 Succeeded. (ur_mdb01 Failed)
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03009: failure of backup command on t2 channel at 03/20/2009 13:49:09
ORA-19566: exceeded limit of 0 blocks UPT blocks for file/dev/vg_mdb02/rdata_2g_050
ORA-000060: Deadlock detected. More info in file/oracle/app/oracle/admin/uradt/udump/uradt_ora_3035.trc.
***
Unzip UPT block relative dba: 0x1a43d4e3 (file 105, block 251107)
Fractured block found during backing up datafile
Data in bad block-
Type: 0 format: 0 rdba: 0x00000000
Last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
Consistency value in tail: 0x00000000
Check value in block header: 0x0, block checksum disabled
Spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of blocknum = 251107, file =/dev/vg_mdb02/rdata_2g_050. found same extends UPT data
Thu Apr 16 16:31:04 2009
 
 

Dbv check shows that there are at least 45 bad blocks:
 
[Oracle @ ur_mdb01/oracle $] dbv file =/dev/vg_mdb02/rdata_2g_050 block size = 8192
 
DBVERIFY: Release 9.2.0.6.0-Production on Mon Apr 20 09:55:07 2009
 
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
 
DBVERIFY-Verification starting: FILE =/dev/vg_mdb02/rdata_2g_050
Page 251107 is influx-most likely media UPT
***
Unzip UPT block relative dba: 0x1a43d4e3 (file 105, block 251107)
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: 0x00000000
Check value in block header: 0x0, block checksum disabled
Spare1: 0x0, spare2: 0x0, spare3: 0x0
***
 
Page 251108 is marked UPT
***
Unzip UPT block relative dba: 0x1a43d4e4 (file 105, block 251108)
Bad header found during dbv:
Data in bad block-
Type: 181 format: 6 rdba: 0x00000000
Last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
Consistency value in tail: 0x00000000
Check value in block header: 0x0, block checksum disabled
Spare1: 0x7, spare2: 0xc, spare3: 0x0
***
 
......
 
Unzip UPT block relative dba: 0x1a43d56f (file 105, block 251247)
Bad header found during dbv:
Data in bad block-
Type: 65 format: 5 rdba: 0x527002c2
Last change scn: 0x3131.02063033 seq: 0x30 flg: 0x31
Consistency value in tail: 0x3635032d
Check value in block header: 0x180, block checksum disabled
Spare1: 0x50, spare2: 0x72, spare3: 0x430.
***
 
 
 
DBVERIFY-Verification complete
 
Total Pages Examined: 262016
Total Pages Processed (Data): 60240
Total Pages Failing (Data): 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 568
Total Pages Processed (Seg): 0
Total Pages Failing (Seg): 0
Total Pages Empty: 201163
Total Pages Marked UPT: 45
Total Pages Influx: 11
Highest block SCN: 10816042273 (2.2226107681)
 
 

After inspection, we found that there were no data objects on these bad blocks:
Method 1. You can find the tablespace where the data file is located. All objects in the tablespace can be scanned once if they are all tables. No error is reported. If they are all indexes, you can use iff once. If no error is reported, it means that the objects are not in bad blocks.

Method 2. Use
Select * from dba_extents where file_id = bad block file_id
And block block_id between block_id and block_id + blocks-1;
If all the results of executing the SQL check for bad blocks are 0 rows, it means that no object is on the bad block.
 

We use the format to clear such bad blocks:
-- Estimate the remaining space:

SQL> select tablespace_name, file_id, sum (bytes)/1024/1024 size_m from dba_free_space
2 where file_id = 105 group by tablespace_name, file_id;
 
TABLESPACE_NAME FILE_ID SIZE_M
--------------------------------------------------
LOG_P8 105 1643
 
SQL>
 

 
-- Create a table in this tablespace for data block formatting:

SQL> create table for_fix (n number, c varchar2 (4000) nologging tablespace LOG_P8;
 
Table created.
 
SQL>
SQL>
SQL>
 
-- Fill the table with full fill:
SQL> alter table for_fix allocate extent (datafile '/dev/vg_mdb02/rdata_2g_050' size 1643 m );
 
Table altered.
 
SQL>
SQL> insert into for_fix select rownum, rpad ('destwedew', 3900) from dba_objects;
 
7285 rows created.
 
SQL>/
 
7285 rows created.
 
SQL>/
 
......
 
SQL> insert into for_fix select * from for_fix where rownum <= 1000000;
 
1000000 rows created.
 
Elapsed: 00:00:06. 96
SQL>/
 
1000000 rows created.
 
Elapsed: 00:00:03. 50
SQL>/
 
10000 rows created.
 
Elapsed: 00:00:14. 03
......
 
SQL> insert into for_fix select * from for_fix where rownum <= 50000;
 
50000 rows created.
 
Elapsed: 00:00:39. 28
SQL>
SQL>
 
......
 
SQL> insert into for_fix select * from for_fix where rownum <= 1;
 
1 row created.
 
Elapsed: 00:00:00. 00
SQL>/
 
1 row created.
 
Elapsed: 00:00:00. 00
SQL>/
 
1 row created.
 
Elapsed: 00:00:00. 00
SQL>/
 
1 row created.
 
Elapsed: 00:00:00. 00
SQL>/
 
1 row created.
 
Elapsed: 00:00:00. 01
SQL>/
Insert into for_fix select * from for_fix where rownum <= 1
*
ERROR at line 1:
ORA-01653: unable to extend table SYS. FOR_FIX by 128 in tablespace LOG_P8
 
 
Elapsed: 00:00:00. 00
SQL>
SQL>
SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00. 07
SQL>
SQL>
 
-- Remember, here we must do the checkpoint to refresh all the data to the data file. Otherwise, we cannot ensure that all bad blocks are formatted:

SQL> alter system checkpoint;
 
System altered.
 
Elapsed: 00:00:04. 25
SQL>/
 
System altered.
 
Elapsed: 00:00:00. 15
SQL>/
 
System altered.
 
Elapsed: 00:00:00. 14

After the table is fully filled, all blocks are formatted, including bad blocks. In this case, we use dbv to check again:
 
[Oracle @ ur_mdb01/oracle $]
Dbv file =/dev/vg_mdb02/rdata_2g_050 BLOCKSIZE = 8192
 
DBVERIFY: Release 9.2.0.6.0-Production on Mon Apr 20 13:30:43 2009
 
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
 
DBVERIFY-Verification starting: FILE =/dev/vg_mdb02/rdata_2g_050
 
 
DBVERIFY-Verification complete
 
Total Pages Examined: 262016
Total Pages Processed (Data): 260715
Total Pages Failing (Data): 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1181
Total Pages Processed (Seg): 0
Total Pages Failing (Seg): 0
Total Pages Empty: 120
Total Pages Marked success UPT: 0
Total Pages Influx: 0
Highest block SCN: 10816271785 (2.2226337193)
[Oracle @ ur_mdb01/oracle $]

The bad block has been processed and the rman backup is normal.