重庆思庄Oracle、Redhat认证学习论坛

标题: oracle数据库坏块恢复—存在rman备份 [打印本页]

作者: denglj 时间: 2023-1-17 11:45
标题: oracle数据库坏块恢复—存在rman备份

文档课题：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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)