数据文件丢失,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