How to Use RMAN to identify corrupted objects in the database

How to Use RMAN to identify corrupted objects in the database

How to Use RMAN to identify corrupted segments in the database.
 
Solution:

Step 1: identify bad Blocks

Run the following RMAN command to record all bad block information in the v $ database_block_partition uption View:

RMAN> backup validate check logical database;
 
Note:

This command only checks the Bad blocks of the database and does not actually perform backup. The backup clause can be omitted from 11 GB and the "validate check logical database" command can be used directly ".
 
If the command fails due to a missing file, you can add the 'skip INACCESSIBLE 'clause to avoid this problem.

To speed up the check, you can set PARALLELISM to specify multiple channels:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
 
OR

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;
}

Output

Bad block information is recorded in view V $ database_block_partition uption. 11g RMAN will generate a trace file to describe the bad block information in detail:

Rman validate screen output:

File Status Marked upt Empty Blocks Examined High SCN
-------------------------------------------------------------
6 failures 0 501 640 1950088
File Name:/Oracle/dbs/users. dbf
Block Type Blocks Failing Blocks Processed
----------------------------------------
Data 9 9 9
Index 0 0
Other 0 130
 
Validate found one or more than upt blocks
See trace file/oracle/log/diag/rdbms/orcl/trace/orcl_ora_282.16.trc for details
Finished validate at <Date>

The Trace file outputs bad block information. This example describes two bad blocks, one physical bad block (file 6 block 9) and one logical bad block (file 6 block 10 ):

Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
Type: 16 format: 2 rdba: 0x01000009
Last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
Spare1: 0x0 spare2: 0x0 spare3: 0x0
Consistency value in tail: 0x000010ff
Check value in block header: 0xb4e0
Computed block checksum: 0xa800
Reread of blocknum = 9, file =/oracle/dbs/users. dbf found same encrypt upt data

Block Checking: DBA = 25165834, Block Type = KTB-managed data block
Data header at 0x2b2deb49e07c
Kdbchk: fsbo (144) wrong, (hsz 78)
Error backing up file 6, block 10: logical upload uption

Bad block information is recorded in view V $ database_block_partition uption:

 

SQL> select * from V $ DATABASE_BLOCK_CORRUPTION;

FILE # BLOCK # BLOCKS upload uption_change # upload uptio
------------------------------------------------------------------------
6 10 1 8183236781662 LOGICAL
6 42 1 0 FRACTURED
6 34 2 0 CHECKSUM
6 50 1 8183236781952 LOGICAL
6 26 4 0 FRACTURED
 
5 rows selected.

 

Note:
• The check logical option checks both physical and LOGICAL Bad blocks.
• The alert Log updates the following information when a logical bad block is found:
 

Error backing up file <file #>, block <block #>: logical upload uption

A trace file is generated at 11 GB to describe the bad block information.
• The alert Log also updates the following information when a physical bad block is found:

Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
Type: 16 format: 2 rdba: 0x01000009
Last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
Spare1: 0x0 spare2: 0x0 spare3: 0x0
Consistency value in tail: 0x000010ff
Check value in block header: 0xb4e0
Computed block checksum: 0xa800
Reread of blocknum = 9, file =/oracle/dbs/users. dbf found same encrypt upt data
• Check a single data file or a specific data file by running the "check logical validate datafile 1, 2" command ".
• To monitor the running Progress of the VALIDATE command, run the following query:

Select sid, serial #, context, sofar, totalwork, round (sofar/totalwork * 100,2) "% _ complete"
From v $ session_longops
Where opname like 'rman %'
And opname not like '% aggregate %'
And totalwork! = 0
And sofar <> totalwork;
• For a 10g or earlier NOARCHIVELOG database, the database must run the VALIDATE command in the MOUNT state; otherwise, the ORA-19602 is reported. If the database cannot be shut down, use dbverify instead of RMAN. This limit is canceled at 11 GB.
• 11g or later: You can use the section clause to verify a separate data file in parallel.
RMAN splits the data file into multiple parts and processes each part in parallel. In the following example, Data File 5 is split into 1 gb. If multiple channels are configured or allocated, RMAN verifies each part in parallel. (Refer to The Oracle Database Backup and Recovery User's Guide for more information ):

Backup validate check logical datafile 5 section size 1024 M;
• 11 GB or later versions can use the block to clause TO check data blocks within a certain range. Run the following command to check data blocks 5 to 20 in data file 1:

Validate check logical datafile 1 BLOCK 5 TO 20;
• In the 8i version, if the RMAN validate Command finds a bad block, it will only be recorded in the alert Log. You must view the logs within the time range of the validate command in the alert Log. Bad block information is not returned to the RMAN interface. In 9i and later versions, if the RMAN validate Command finds a bad block, you can query the view V $ database_block_uption to obtain the bad block information.
• Every time you run the RMAN backup validate command, the bad block information in the view V $ database_block_partition uption will be updated. For more information about the view, see Oracle? Database Reference document.

Step 2: identify damaged segments

The following query can match the Bad blocks recorded in view v $ database_block_partition uption to the corresponding segments or idle blocks.

$ Sqlplus/as sysdba

Set pagesize 2000
Set linesize 250
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) 1__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 1__start_block #
, Header_block 1__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) 1__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 #;

Output example:

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION _ FILE # pai_start_block # pai_end_block # blocks_upted DESCRIPTION
---------------------------------------------------------------------------------------------------------------
Scott table emp 6 10 10 1
Scott table partition order ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
Scott table bonus 6 34 34 1
6 35 35 1 Free Block
Scott table dept 6 42 42 1 Segment Header
Scott table invoice 6 50 50 1
 
Note:
• If the broken block appears in the tablespace managed by the dictionary and the segment header block is damaged, the preceding query may display the same block twice.
• If the segment header block of the ASSM tablespace is damaged, the above query will display the segment header block, but other bad blocks on the same object may not be displayed.

-------------------------------------- Recommended reading --------------------------------------

RMAN: Configure an archive log deletion policy

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption

-------------------------------------- Split line --------------------------------------