RMAN自动化恢复数据文件

数据文件丢失，RMAN自动化恢复数据文件

## 重启数据库：报错 ORA-01110: data file 5: '/oradb/app/oracle/oradata/orcl/nnl_01.dbf' 数据文件未找到；
SQL> startup force;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size             771752256 bytes
Database Buffers         2432696320 bytes+
Redo Buffers                8151040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oradb/app/oracle/oradata/orcl/nnl_01.dbf'

SQL> SP2-0223: No lines in SQL buffer.

## 使用RMAN 命令 自动定位数据库的错误；
## 登录RMAN 
[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Aug 31 09:56:15 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1540272869, not open)

## 自动定位数据库的错误；  Repair script: /oradb/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1069237087.hm
RMAN> advise failure all;

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

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
28         HIGH     OPEN      31-AUG-19     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=42 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file /oradb/app/oracle/oradata/orcl/nnl_01.dbf was unintentionally renamed or moved, restore it
2. If file /oradb/app/oracle/oradata/orcl/nnl_02.dbf was unintentionally renamed or moved, restore it
3. If file /oradb/app/oracle/oradata/orcl/nnl_03.dbf was unintentionally renamed or moved, restore it
4. If file /oradb/app/oracle/oradata/orcl/nnl_04.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5; Restore and recover datafile 8; Restore and recover datafile 9; ...
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /oradb/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1069237087.hm

RMAN> 

# 切换到：在服务器中查看恢复脚本命令： 
# 注意：这里面没有给数据文件 OFFLINE 的命令，在恢复脚本里要自己加上；
[oracle@ora12c orcl]$ cat /oradb/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1069237087.hm
   # restore and recover datafile
   restore ( datafile 5, 8, 9, 10 );
   recover datafile 5, 8, 9, 10;
   sql 'alter database datafile 5, 8, 9, 10 online';
   

# 数据文件恢复 标准作业流程 
# restore and recover datafile
alter database datafile 5, 8, 9, 10 offline;
restore ( datafile 5, 8, 9, 10 );
recover datafile 5, 8, 9, 10;
alter database datafile 5, 8, 9, 10 online;
   

## 切换到 RMAN 命令中执行；
## 第一步 将数据文件离线
RMAN>    alter database datafile 5, 8, 9, 10 offline  ;

Statement processed

## 第二步 恢复数据文件
RMAN> restore ( datafile 5, 8, 9, 10 );

Starting restore at 31-AUG-19
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 00005 to /oradb/app/oracle/oradata/orcl/nnl_01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradb/app/oracle/oradata/orcl/nnl_02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /oradb/app/oracle/oradata/orcl/nnl_03.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradb/app/oracle/oradata/orcl/nnl_04.dbf
channel ORA_DISK_1: reading from backup piece /oradb/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2019_08_24/o1_mf_nnndf_TAG20190824T221621_gp2koon1_.bkp
channel ORA_DISK_1: piece handle=/oradb/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2019_08_24/o1_mf_nnndf_TAG20190824T221621_gp2koon1_.bkp tag=TAG20190824T221621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-AUG-19

## 第三步 将数据文件应用归档日志
RMAN>  recover datafile 5, 8, 9, 10;

Starting recover at 31-AUG-19
using channel ORA_DISK_1

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

Finished recover at 31-AUG-19

## 第四步 将数据文件上线
RMAN> alter database datafile 5, 8, 9, 10 online;

Statement processed

RMAN> 

   
   

## 切换到：SQLPLUS 命令中

## 检查数据库情况； MOUNTED 状态；
SQL> select dbid ,name ,open_mode,log_mode,flashback_on from v$database;

      DBID NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
---------- --------- -------------------- ------------ ------------------
1540272869 ORCL      MOUNTED              ARCHIVELOG   NO

  

## 打开数据库
SQL> alter database open ;
Database altered.

## 再次检查数据库情况； READ WRITE 状态；
SQL>  select dbid ,name ,open_mode,log_mode,flashback_on from v$database;

      DBID NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON
---------- --------- -------------------- ------------ ------------------
1540272869 ORCL      READ WRITE           ARCHIVELOG   NO