【RMAN】利用ADR诊断和修复数据库

　　利用DRA(Data Recovery Adviser)诊断和修复数据库案例

 

一、创建一个测试表空间

SQL> create tablespace test datafile '/u01/app/oracle/oradata/T1/test.dbf' size 10m autoextend off;

Tablespace created.

二、RMAN下备份数据库

# 这里使用的是CDB进行测试，所以只备份了CDB
RMAN> backup database root;

Starting backup at 26-JUL-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/T1/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/T1/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/T1/undotbs01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/T1/test.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/T1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUL-21
channel ORA_DISK_1: finished piece 1 at 26-JUL-21
piece handle=/u01/app/backup/backupset/T1_20210726_1078934694.bak tag=TAG20210726T160454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUL-21

Starting Control File and SPFILE Autobackup at 26-JUL-21
piece handle=/u01/app/backup/backupset/c-2888150031-20210726-06.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUL-21

三、关闭实例，OS下删除test表空间

# 这时启动数据库会报错
SQL> startup
ORACLE instance started.

Total System Global Area 2516582152 bytes
Fixed Size            9137928 bytes
Variable Size          536870912 bytes
Database Buffers     1962934272 bytes
Redo Buffers            7639040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '/u01/app/oracle/oradata/T1/test.dbf'

四、进入RMAN诊断问题

RMAN>  list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1142       HIGH     OPEN      26-JUL-21     One or more non-system datafiles are missing

五、生成有关故障建议

RMAN>  advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1142       HIGH     OPEN      26-JUL-21     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/T1/test.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 13  
  Strategy: The repair includes complete media recovery with no data loss            ----------修复策略
  Repair script: /u01/app/oracle/diag/rdbms/t1/T1/hm/reco_1743182304.hm              ----------修复脚本

六、系统下查看脚本

 # restore and recover datafile
 restore ( datafile 13 );
 recover datafile 13;
 sql 'alter database datafile 13 online';

七、RMAN下执行脚本中的命令

RMAN> restore ( datafile 13 );

Starting restore at 26-JUL-21
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/T1/test.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/backupset/T1_20210726_1078934694.bak
channel ORA_DISK_1: piece handle=/u01/app/backup/backupset/T1_20210726_1078934694.bak tag=TAG20210726T160454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-JUL-21

RMAN> recover datafile 13;

Starting recover at 26-JUL-21
using channel ORA_DISK_1

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

Finished recover at 26-JUL-21

RMAN> sql 'alter database datafile 13 online';

sql statement: alter database datafile 13 online

八、打开数据库确认是否恢复成功

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN