v$database_block_corrup validate check logical

jnrjian

已于 2023-08-29 11:02:32 修改

阅读量739

点赞数

文章标签：数据库

于 2023-08-24 17:27:45 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/132477799

版权

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

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 ;