Alert.log is showing lots of messages like:
Recovery is repairing media corrupt block <block#> of file <file#>
RMAN> backup validate check logical database;
will show for that files blocks marked corrupt:
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ---------------------
33 OK 3997 225459 908800 8302670026006
SQL> select * from V$database_block_corruption;
doesn't return any rows.
SQL> select * from v$nonlogged_block;
will return corrupted blocks because of nologging
FILE# BLOCK# BLOCKS NONLOGGED_START_CHANGE# NONLOGGED
---------- ---------- ---------- ----------------------- ---------
NONLOGGED_END_CHANGE# NONLOGGED RESETLOGS_CHANGE# RESETLOGS
--------------------- --------- ----------------- ---------
OBJECT# REASON CON_ID
---------------------------------------- ------- ----------
33 682262 42 8302647413103
8302647413103
UNKNOWN 0 ....
SQL> 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$nonlogged_block 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$nonlogged_block 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$nonlogged_block 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#;
will return FREE CORRUPTED BLOCKS
OWNER
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- ----------------- --------------- ---------------- --------------
33 682262 682303 42 Free Block
On Primary database
Select * from dba_free_space where file_id=172 and &block_id between block_id and block_id + blocks -1 ;
Returns rows indicating the block is in free space.
CHANGES
No changes.
CAUSE
These messages can appear in standby or standalone database after recovery for objects created with NOLOGGING option.
They are generated for nologging free corrupt blocks that are repaired by RMAN.
SOLUTION
There are few unpublished bugs related to these messages.
These messages are just warnings, and can be safely ignored.
However, their big number and the word "corrupt block" are unnecessarily
alarming careful DBAs.
ADG :
Option 2:- Restore datafile from Service
On Standby Stop managed recovery.
SQL>Alter database recover managed standby database cancel ;
Now restore the datafile using service option
Rman> Restore datafile 9 from service '<service name of Primary>' ;
If file is already in sync the above command might report
skipping datafile 9; already restored to SCN 15230005929211
restore not done; all files read only, offline, or already restored
Finished restore at 22-MAR-18
In that case you can use FORCE option
Rman> Restore datafile 9 FORCE from service '<service name of Primary>' ;
Once the file gets restored.
Start Managed recovery.
Check v$database_block_corruption .
Option 3 :- Do block recovery on Standby
If valid backup exist on Standby database.
Stop managed recovery
SQL>Alter database recover managed standby database cancel ;
rman> Connect target /
Rman> recover datafile 172 block 1354116 to 1354117;
This section explains the basic concepts and utilities in 11g which populate v$database_block_corruption and RMAN Validate Command.
The Note covers the following :
1) Utilities which populate v$database_block_corruption
2) RMAN Validate Command and New features with respect to it.
SCOPE
DBA's with prior knowledge of RMAN and the functionality of view V$database_block_corruption.
DETAILS
You can use the VALIDATE or BACKUP VALIDATE command to manually check for physical and logical corruptions in database files.
Behavior in 9i and 10g
In 9i and 10g the view v$database_block_corruption used to get populated only when
RMAN Backup validate /check logical validate command was run.
The populated information used to get refreshed only once the corruption was repaired(media recovery/Object dropped) and on re-run of the Rman Backup validate /check logical validate command on the database or the affected datafile.
From 11g this behavior has Changed.
When any database utility or process encounters an intrablock corruption, it automatically records it in
V$DATABASE_BLOCK_CORRUPTION.
A repair removes metadata about corrupt blocks from the view.
Repair techniques include: block media recovery, restoring datafiles, recovering by means of incremental backups, and block newing.
Block media recovery can repair physical corruptions, but not logical corruptions.
Database utilities which populates V$DATABASE_BLOCK_CORRUPTION on detecting corruption:
- Analyze table .. Validate structure
- CTAS(Create table as Select)
- Export
For example :-
Analyze command :
SQL> select count(*) from v$database_block_corruption ;
COUNT(*)
----------
0
SQL> Analyze table sh.cor validate structure cascade ;
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 23)
ORA-01110: data file 6: '<Driver:>\path\<datafilename>.dbf'
SQL> select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION
---------- ---------- ---------- ------------------ ---------
6 23 1 0 CORRUPT
As seen above Analyze on detecting corruption populates the view v$Database_block_corruption
Export utility :
Before Export :
SQL> select count(*) from v$database_block_corruption ;
COUNT(*)
----------
0
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table COR
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 6, block # 28)
ORA-01110: data file 6: '<Driver:>\path\<datafilename>.dbf'
Export terminated successfully with warnings.
After export :
SQL> select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 28 1 0 CORRUPT
The information in the view is automatically refreshed by block media recovery, restoring datafiles, recovering by means of incremental backups, and block newing.
RMAN VALIDATE
The main purpose of RMAN validation is to check for corrupt blocks and missing files.
You can also use RMAN to determine whether backups can be restored.
You can use the following RMAN commands to perform validation:
VALIDATE
BACKUP ... VALIDATE
RESTORE ... VALIDATE
Checking for Block Corruption with the VALIDATE Command
You can use the VALIDATE command to manually check for physical and logical
corruptions in database files.
This command performs the same types of checks as BACKUP VALIDATE, but VALIDATE can check a larger selection of objects.
For example, you can validate individual blocks with the VALIDATE DATAFILE ... BLOCK command.
In 11g both these commands gives detailed information about the block.
Syntax for Rman Validate Command :-
Database :-
RMAN > Validate database
Datafile :-
RMAN > Validate datafile <file no>,<file no> ;
Data block :-
RMAN > Validate datafile <file no> block <Block no> ;
Detailed information are listed from 11g
RMAN> validate datafile 1;
Starting validate at 20-DEC-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=127 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=<Driver:>\path\<datafilename>.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
input datafile file number=00001 name=<Driver:>\path\<datafilename>.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 13168 96000 3003942
File Name: <Driver:>\path\<datafilename>.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 66276
Index 0 14257
Other 0 2299
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 600
Finished validate at 20-DEC-07
ORA-01578: ORACLE data block corrupted (file # 2215, block # 55)
ORA-01110: data file 2215: '/u01/oradata/orcl/user.dbf'
Receiving the above corruption related error.
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
2215 55 1 0 CHECKSUM ------physical?
MAN> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
77 1296 1 14964156 LOGICAL 7
77 26705 3 14964224 LOGICAL 7
77 1298 6 14964156 LOGICAL 7
RMAN>
Below query returns no rows. This shows that there is no free block that is corrupted and there is no corrupted segment
SQL> 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
, corruption_type description,'NONCDB'
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
, corruption_type||' Segment Header' description,'NONCDB'
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,'NONCDB'
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#;
no rows selected
CAUSE
The corruption is on file space bitmap
SOLUTION
sqlplus / as sysdba;
SQL>alter session set "_sga_clear_dump"=TRUE;
SQL>oradebug setmypid;
SQL>alter system dump datafile 2215 block 55;
SQL>oradebug tracefile_name;
Sample Trace file output:
*** 2022-01-05 15:04:54.839
Start dump data blocks tsn: 17 file#:2215 minblk 55 maxblk 55
Block dump from cache:
Dump of buffer cache at level 4 for tsn=17 rdba=708837431
Block dump from disk:
buffer tsn: 17 rdba: 0x2a400037 (169/55)
scn: 0x0ae8.95ffcbb9 seq: 0x01 flg: 0x04 tail: 0xcbb91e01
frmt: 0x02 chkval: 0x4d7d type: 0x1e=KTFB Bitmapped File Space Bitmap================>>> Bitmapped File Space Bitmap
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x9FFFFFFFBF406A00 to 0x9FFFFFFFBF406A14
The corruption is on file space bitmap which has to be fixed by block recover using backup.
If the block recovery is not possible, then the only option is to create new tablespace, move all the objects from the current tablespace to new tablespace and then drop the tablespace having the corruption.
block recover using backup
SOLUTION
In below example assuming file 7 and block 201 and 202 are corrupted
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 201 2 0 CORRUPT
New syntax is :-
For Single block recovery
Rman> Recover datafile <fileno> block <block number reported corrupt> ;
Rman> Recover datafile 7 block 201 ;
Range block recovery
If there is a range of blocks corrupted say block number 200 to 201.
Rman> Recover datafile <fileno> block <block number > to <block number>
Rman> Recover datafile 7 block 200 to 201 ;
To recover complete corruption list
Rman> recover corruption list ;
Block recovery using tablepace number/name
Specifies the tablespace name or number containing the corrupt blocks and the data block address (DBA) of the corrupt block. You can only perform block media recovery on corrupt blocks.
First convert the file number and block number to DBA using dbms_utility package
Taking file number 7 and block number 201
文章来源：https://www.toymoban.com/news/detail-680009.html
SQL>SELECT DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(<file#>,<block#>) FROM DUAL;
SQL> SELECT DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,201) FROM DUAL;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,201)
-------------------------------------------
29360329
Rman> Recover tablespace <name> dba <integer value> ;
Rman> Recover tablespace testing dba 29360329 ;文章来源地址https://www.toymoban.com/news/detail-680009.html
到了这里，关于v$database_block_corrup validate check logical的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
