oracle数据库坏块恢复—存在rman备份

denglj

文档课题：oracle数据库坏块恢复—存在rman备份.

数据库：oracle19.12 多租户

1、坏块查询

SQL>set numw 20

SQL>set line 200

SQL>select * from v$database_block_corruption

               FILE#               BLOCK#               BLOCKS   CORRUPTION_CHANGE# CORRUPTIO               CON_ID

---------------------------------------- -------------------- -------------------- -----------------------------

                  12                  659                    1 18446744072549497550CORRUPT                      3

                  12                 1003                    1 18446744072549497550CORRUPT                      3

2、校验文件

使用backupvalidate datafile校验数据文件12.

RMAN>backup validate datafile 12;

Startingbackup at 15-JAN-23

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=28 device type=DISK

channelORA_DISK_1: starting full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

inputdatafile file number=00012 name=+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

Listof Datafiles

=================

FileStatus Marked Corrupt Empty Blocks Blocks Examined High SCN

---------- -------------- ------------ --------------- ----------

12   FAILED 0              763          3040            19014535  

  File Name:+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data      0              1427            

  Index     0              85              

  Other     2              765            

validatefound one or more corrupt blocks

Seetrace file /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_13324.trcfor details

Finishedbackup at 15-JAN-23

RMAN>list failure;

DatabaseRole: PRIMARY

Listof Database Failures

=========================

FailureID Priority Status    Time DetectedSummary

------------------ --------- ------------- -------

2182       HIGH    OPEN      14-JAN-23     Datafile 12:'+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667'contains one or more corrupt blocks

RMAN>advise failure;

DatabaseRole: PRIMARY

Listof Database Failures

=========================

FailureID Priority Status    Time DetectedSummary

------------------ --------- ------------- -------

2182       HIGH    OPEN      14-JAN-23     Datafile 12: '+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667'contains one or more corrupt blocks

analyzingautomatic repair options; this may take some time

usingchannel ORA_DISK_1

analyzingautomatic repair options complete

MandatoryManual Actions

========================

nomanual actions available

OptionalManual Actions

=======================

nomanual actions available

AutomatedRepair Options

========================

OptionRepair Description

------------------------

1      Recover multiple corrupt blocks indatafile 12  

  Strategy: The repair includes complete mediarecovery with no data loss

  Repair script:/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/hm/reco_4023960909.hm

3、恢复损坏块

使用rman备份对坏块进行恢复.

RMAN>recover datafile 12 block 659,1003;

Startingrecover at 15-JAN-23

usingchannel ORA_DISK_1

channelORA_DISK_1: restoring block(s)

channelORA_DISK_1: specifying block(s) to restore from backup set

restoringblocks of datafile 00012

channelORA_DISK_1: reading from backup piece+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/BACKUPSET/2023_01_14/nnndf0_tag20230114t180326_0.313.1126116417

channelORA_DISK_1: piecehandle=+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/BACKUPSET/2023_01_14/nnndf0_tag20230114t180326_0.313.1126116417tag=TAG20230114T180326

channelORA_DISK_1: restored block(s) from backup piece 1

channelORA_DISK_1: block restore complete, elapsed time: 00:00:25

startingmedia recovery

mediarecovery complete, elapsed time: 00:00:07

Finishedrecover at 15-JAN-23

4、验证数据

SQL>select * from v$database_block_corruption;

norows selected

SQL>select count(*) from ora1.emp;

            COUNT(*)

--------------------

                 140