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

数据库：oracle 19.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 18446744072549497550 CORRUPT                      3

                  12                 1003                    1 18446744072549497550 CORRUPT                      3

2、校验文件

使用backup validate datafile校验数据文件12.

RMAN> backup validate datafile 12;

 

Starting backup at 15-JAN-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

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

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

List of Datafiles

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

File Status 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            

 

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_13324.trc for details

Finished backup at 15-JAN-23

 

RMAN> list failure;

 

Database Role: PRIMARY

 

List of Database Failures

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

 

Failure ID Priority Status    Time Detected Summary

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

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

 

RMAN> advise failure;

 

Database Role: PRIMARY

 

List of Database Failures

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

 

Failure ID Priority Status    Time Detected Summary

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

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

 

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

 

Mandatory Manual Actions

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

no manual actions available

 

Optional Manual Actions

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

no manual actions available

 

Automated Repair Options

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

Option Repair Description

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

1      Recover multiple corrupt blocks in datafile 12 

  Strategy: The repair includes complete media recovery 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;

 

Starting recover at 15-JAN-23

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00012

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

channel ORA_DISK_1: piece handle=+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/BACKUPSET/2023_01_14/nnndf0_tag20230114t180326_0.313.1126116417 tag=TAG20230114T180326

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:25

 

starting media recovery

media recovery complete, elapsed time: 00:00:07

 

Finished recover at 15-JAN-23

4、验证数据

SQL> select * from v$database_block_corruption;

 

no rows selected

 

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

 

            COUNT(*)

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

                 140