目录
一、归档模式
示例一：所有数据文件丢失，控制文件也丢失
示例二：新建表空间对应的数据文件丢失
示例三：某数据文件丢失，且所在的磁盘损坏
二、非归档模式
示例四：某数据文件丢失，日志未覆盖情况下做RMAN完全恢复
示例五：某数据文件丢失，日志被覆盖情况下做RMAN不完全恢复
一、归档模式
首先，使用RMAN对数据库做一个数据库全备份
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database filesperset 1 format '/u01/app/oracle/RMAN_Backup/hot/full_%n_%T_%t_%s_%p.bak';
5> backup spfile format='/u01/app/oracle/RMAN_Backup/hot/spfile_%n_%U_%T.bak';
6> sql 'alter system archive log current';
7> backup archivelog all format '/u01/app/oracle/RMAN_Backup/hot/arch_%d_%T_%s_%p.bak' delete input;
8> backup current controlfile format '/u01/app/oracle/RMAN_Backup/hot/ctl_%d_%T_%s_%p.bak';
9> release channel c1;
10> release channel c2;
11> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=36 device type=DISK
allocated channel: c2
channel c2: SID=31 device type=DISK
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/OraDB11g/system01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak tag=TAG20170808T153703 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:35
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/OraDB11g/example01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak tag=TAG20170808T153703 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:35
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak tag=TAG20170808T153703 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak tag=TAG20170808T153703 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:07
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak tag=TAG20170808T153703 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/OraDB11g/users01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak tag=TAG20170808T153703 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 08-AUG-17
including current control file in backup set
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak tag=TAG20170808T153703 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak tag=TAG20170808T153703 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak tag=TAG20170808T153749 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
sql statement: alter system archive log current
Starting backup at 08-AUG-17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=4 STAMP=949239430
input archived log thread=1 sequence=9 RECID=5 STAMP=951418831
input archived log thread=1 sequence=10 RECID=6 STAMP=951429659
channel c1: starting piece 1 at 08-AUG-17
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=7 STAMP=951458673
input archived log thread=1 sequence=12 RECID=8 STAMP=951474337
input archived log thread=1 sequence=13 RECID=9 STAMP=951493070
input archived log thread=1 sequence=14 RECID=10 STAMP=951493070
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak tag=TAG20170808T153750 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/arch/arch_1_949237404_8.log RECID=4 STAMP=949239430
archived log file name=/u01/app/oracle/arch/arch_1_949237404_9.log RECID=5 STAMP=951418831
archived log file name=/u01/app/oracle/arch/arch_1_949237404_10.log RECID=6 STAMP=951429659
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak tag=TAG20170808T153750 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:03
channel c2: deleting archived log(s)
archived log file name=/u01/app/oracle/arch/arch_1_949237404_11.log RECID=7 STAMP=951458673
archived log file name=/u01/app/oracle/arch/arch_1_949237404_12.log RECID=8 STAMP=951474337
archived log file name=/u01/app/oracle/arch/arch_1_949237404_13.log RECID=9 STAMP=951493070
archived log file name=/u01/app/oracle/arch/arch_1_949237404_14.log RECID=10 STAMP=951493070
Finished backup at 08-AUG-17
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/hot/ctl_ORADB11G_20170808_23_1.bak tag=TAG20170808T153754 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
released channel: c1
released channel: c2
--查看备份的内容
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
12 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
13 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
14 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
15 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
16 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
17 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
18 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
19 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153703
20 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153749
21 B A A DISK 08-AUG-17 1 1 NO TAG20170808T153750
22 B A A DISK 08-AUG-17 1 1 NO TAG20170808T153750
23 B F A DISK 08-AUG-17 1 1 NO TAG20170808T153754
************************************************************************************************************************************
示例一：所有数据文件丢失，控制文件也丢失
场景：在seiang用户下，创建一张wjq1表，并插入三条数据（第一条数据提交归档、第二条数据只提交不归档，第三条数据不提交不归档），之后突然断电，然后删除所有的控制文件及数据文件，在此情况，对数据库做RMAN完全恢复；
SYS@seiang11g>select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/OraDB11g/system01.dbf
/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
/u01/app/oracle/oradata/OraDB11g/users01.dbf
/u01/app/oracle/oradata/OraDB11g/example01.dbf
/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
--创建表wjq1
SYS@seiang11g>create table seiang.wjq1(id number,name varchar2(30)) tablespace seiang;
Table created.
--第一条数据，提交，归档
SYS@seiang11g>insert into seiang.wjq1 values(1,'wjq1');
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--第二条数据，提交，不归档
SYS@seiang11g>insert into seiang.wjq1 values(2,'wjq2');
1 row created.
SYS@seiang11g>commit;
Commit complete.
--第三条数据，不提交，不归档
SYS@seiang11g>insert into seiang.wjq1 values(3,'wjq3');
1 row created.
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 CURRENT
2 14 INACTIVE
3 15 ACTIVE
--模拟断电，手动删除所有的数据文件和控制文件
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/*.dbf
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/*.ctl
SYS@seiang11g>host rm /u01/app/oracle/fast_recovery_area/OraDB11g/*.ctl
--尝试启动数据库
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
ORA-00205: error in identifying control file, check alert log for more info
--登录到RMAN，开始执行恢复
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 15:51:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB11G (not mounted)
RMAN> restore controlfile;
Starting restore at 08-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/08/2017 15:52:18
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
RMAN> restore controlfile from '/u01/app/oracle/RMAN_Backup/hot/ctl_ORADB11G_20170808_23_1.bak';
Starting restore at 08-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/OraDB11g/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
Finished restore at 08-AUG-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> restore database;
5> recover database;
6> alter database open;
7> }
allocated channel: c1
channel c1: SID=21 device type=DISK
allocated channel: c2
channel c2: SID=17 device type=DISK
Starting restore at 08-AUG-17
Starting implicit crosscheck backup at 08-AUG-17
Crosschecked 11 objects
Finished implicit crosscheck backup at 08-AUG-17
Starting implicit crosscheck copy at 08-AUG-17
Finished implicit crosscheck copy at 08-AUG-17
searching for all files in the recovery area
cataloging files...
no files cataloged
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/OraDB11g/system01.dbf
channel c2: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak
channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak tag=TAG20170808T153703
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak
channel c2: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak tag=TAG20170808T153703
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:35
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00005 to /u01/app/oracle/oradata/OraDB11g/example01.dbf
channel c2: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak
channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak tag=TAG20170808T153703
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:04
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak
channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak tag=TAG20170808T153703
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf
channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak
channel c2: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak tag=TAG20170808T153703
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:11
channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak tag=TAG20170808T153703
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished restore at 08-AUG-17
Starting recover at 08-AUG-17
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo01.log
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=13
channel c1: reading from backup piece /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak
channel c1: piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak tag=TAG20170808T153750
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/arch/arch_1_949237404_13.log thread=1 sequence=13
archived log file name=/u01/app/oracle/oradata/OraDB11g/redo02.log thread=1 sequence=14
archived log file name=/u01/app/oracle/oradata/OraDB11g/redo03.log thread=1 sequence=15
archived log file name=/u01/app/oracle/oradata/OraDB11g/redo01.log thread=1 sequence=16
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-AUG-17
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/08/2017 15:55:34
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--只能使用resetlogs打开数据库
RMAN> alter database open resetlogs;
database opened
SYS@seiang11g>select * from seiang.wjq1;
ID NAME
---------- --------------------------------------------------
1 wjq1
2 wjq2
由恢复的结果可以发现，恢复到最后一次提交。
总结：
当所有数据都丢失（控制文件、数据文件）时，采用RMAN完全恢复的步骤为：
1、通过restore controlfile from ”备份控制文件的路径”
2、alter database mount
3、restore database
4、recover databse
5、alter database open resetlogs；
************************************************************************************************************************************
示例二：新建表空间对应的数据文件丢失
场景：新建表空间good，然后在其上新建表wjq2，并插入三条数据，然后模拟断电，删除good表空间所对应的数据文件，在此情况下，做RMAN的完全恢复（因是新建的表空间，故没有相关的数据文件备份）。
SYS@seiang11g>create tablespace good datafile '/u01/app/oracle/oradata/OraDB11g/good01.dbf' size 50M;
Tablespace created.
SYS@seiang11g>create table seiang.wjq2(id number,age number) tablespace good;
Table created.
--第一条数据，提交，归档
SYS@seiang11g>insert into seiang.wjq2 values(1,20);
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 0 UNUSED
3 0 UNUSED
SYS@seiang11g>alter system switch logfile;
System altered.
--第二条数据，提交，不归档
SYS@seiang11g>insert into seiang.wjq2 values(2,30);
1 row created.
SYS@seiang11g>commit;
Commit complete.
--第三条数据，不提交，不归档
SYS@seiang11g>insert into seiang.wjq2 values(3,40);
1 row created.
SYS@seiang11g>select * from seiang.wjq2;
ID AGE
---------- ----------
1 20
2 30
3 40
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 2 CURRENT
3 0 UNUSED
--模拟断电，手工删除good表空间所对应的数据文件
SYS@seiang11g>shutdown abort
ORACLE instance shut down.
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf
--尝试启动数据库
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE FILE NOT FOUND 0
--登陆RMAN，执行恢复操作
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 16:20:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB11G (DBID=3377212249, not open)
RMAN>
RMAN> run{
2> sql 'alter database datafile 7 offline';
3> alter database open;
4> restore datafile 7;
5> recover datafile 7;
6> sql 'alter database datafile 7 online';
7> }
using target database control file instead of recovery catalog
sql statement: alter database datafile 7 offline
database opened
Starting restore at 08-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
creating datafile file number=7 name=/u01/app/oracle/oradata/OraDB11g/good01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 08-AUG-17
Starting recover at 08-AUG-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-AUG-17
sql statement: alter database datafile 7 online
从恢复过程可以发现，虽然没有新建的good01.dbf的备份信息，但使用restore datafile语句时，RMAN实际上是做了create datafile 操作
SYS@seiang11g>select * from seiang.wjq2;
ID AGE
---------- ----------
1 20
2 30
总结：
当新建表空间对应数据文件丢失时，采用RMAN恢复步骤：
1、sql 'alter database datafile 数据文件号 offline'；
2、alter database open；
3、restore datafile 数据文件号；
4、recover datafile 数据文件号；
5、sql 'alter database datafile 数据文件号 online'；
************************************************************************************************************************************
示例三：数据文件丢失，且所在的磁盘损坏
场景：在seiang用户下，创建wjq3表，隶属于good表空间，并插入三条数据，之后突然断电，且其所在碰盘坏掉了。
SYS@seiang11g>select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/OraDB11g/system01.dbf
/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
/u01/app/oracle/oradata/OraDB11g/users01.dbf
/u01/app/oracle/oradata/OraDB11g/example01.dbf
/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
/u01/app/oracle/oradata/OraDB11g/good01.dbf
SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables
2 where owner='SEIANG';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SEIANG WJQ1 SEIANG
SEIANG WJQ2 GOOD
--创建表wjq3
SYS@seiang11g>create table seiang.wjq3(id number,address varchar2(23)) tablespace good;
Table created.
--第一条数据，提交，归档
SYS@seiang11g>insert into seiang.wjq3 values(1,'beijing');
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 2 INACTIVE
3 3 CURRENT
SYS@seiang11g>alter system switch logfile;
System altered.
--第二条数据，提交，不归档
SYS@seiang11g>insert into seiang.wjq3 values(2,'lanzhou');
1 row created.
SYS@seiang11g>commit;
Commit complete.
--第三条数据，不提交，不归档
SYS@seiang11g>insert into seiang.wjq3 values(3,'tianjin');
1 row created.
SYS@seiang11g>select * from seiang.wjq3;
ID ADDRESS
---------- -----------------------
1 beijing
2 lanzhou
3 tianjin
--突然断电，good表空间对应的数据文件丢失，并且所在的磁盘损坏
SYS@seiang11g>shutdown abort
ORACLE instance shut down.
SYS@seiang11g>
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf
--尝试启动数据库
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE FILE NOT FOUND 0
假设磁盘介质损坏了，更换需要时间，先把数据文件恢复到一个新的目录下（不同的物理位置）
RMAN> run{
2> sql 'alter database datafile 7 offline';
3> alter database open;
4> set newname for datafile 7 to '/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf';
5> restore datafile 7;
6> switch datafile 7;
7> recover datafile 7;
8> sql 'alter database datafile 7 online';
9> }
using target database control file instead of recovery catalog
sql statement: alter database datafile 7 offline
database opened
executing command: SET NEWNAME
Starting restore at 08-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
creating datafile file number=7 name=/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 08-AUG-17
datafile 7 switched to datafile copy
input datafile copy RECID=3 STAMP=951496524 file name=/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf
Starting recover at 08-AUG-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_1.log
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_2.log
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_3.log
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/arch/arch_1_951494153_4.log
archived log file name=/u01/app/oracle/arch/arch_1_951494153_1.log thread=1 sequence=1
archived log file name=/u01/app/oracle/arch/arch_1_951494153_2.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-AUG-17
sql statement: alter database datafile 7 online
说明：
①set newname for 告诉RMAN还原数据文件的新位置在哪里。这个命令在restore前出现。
②switch datafile 更新controlfile，让控制文件使用这个新位置恢复数据。这个命令要在recover前出现。
--恢复完成
SYS@seiang11g>select * from seiang.wjq3;
ID ADDRESS
---------- -----------------------
1 beijing
2 lanzhou
SYS@seiang11g>select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/OraDB11g/system01.dbf
/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
/u01/app/oracle/oradata/OraDB11g/users01.dbf
/u01/app/oracle/oradata/OraDB11g/example01.dbf
/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf
--损坏的磁盘介质更换完成后，将表空间移回原来的位置
SYS@seiang11g> alter tablespace good offline;
Tablespace altered.
SYS@seiang11g>host mv /u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf /u01/app/oracle/oradata/OraDB11g
--更新控制文件中数据文件位置的信息
SYS@seiang11g>alter tablespace good rename datafile '/u01/app/oracle/oradata/OraDB11g/datadir/good01.dbf' to '/u01/app/oracle/oradata/OraDB11g/good01.dbf';
Tablespace altered.
SYS@seiang11g>alter tablespace good online;
Tablespace altered.
SYS@seiang11g>select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/OraDB11g/system01.dbf
/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
/u01/app/oracle/oradata/OraDB11g/users01.dbf
/u01/app/oracle/oradata/OraDB11g/example01.dbf
/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
/u01/app/oracle/oradata/OraDB11g/good01.dbf
总结：
当被破坏的数据文件其所在磁盘都坏掉时，采用rman恢复步骤为：
1、sql 'alter database datafile 数据文件号 offline'；
2、alter database open；
3、set newname for datafile 数据文件号 to ’数据文件新存放路径‘；
4、restore datafile 数据文件号；
5、switch datafile 数据文件号；
4、recover datafile 数据文件号；
5、sql 'alter database datafile 数据文件号 online'；
************************************************************************************************************************************
二、非归档模式
先删除原来的RMAN备份，然后切换成非归档模式，用RMAN做冷备，注意非归档模式不能做热备
--删除原来的备份
RMAN> delete noprompt backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak
13 13 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak
14 14 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak
15 15 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak
16 16 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak
17 17 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak
18 18 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak
19 19 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak
20 20 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak
21 21 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak
22 22 1 1 AVAILABLE DISK /u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_13_1.bak RECID=12 STAMP=951493023
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493023_12_1.bak RECID=13 STAMP=951493023
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493059_15_1.bak RECID=14 STAMP=951493060
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493058_14_1.bak RECID=15 STAMP=951493058
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_16_1.bak RECID=16 STAMP=951493066
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_18_1.bak RECID=17 STAMP=951493066
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493067_19_1.bak RECID=18 STAMP=951493067
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/full_ORADB11G_20170808_951493066_17_1.bak RECID=19 STAMP=951493067
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/spfile_ORADB11G_0ksbd8ed_1_1_20170808.bak RECID=20 STAMP=951493069
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_21_1.bak RECID=21 STAMP=951493071
deleted backup piece
backup piece handle=/u01/app/oracle/RMAN_Backup/hot/arch_ORADB11G_20170808_22_1.bak RECID=22 STAMP=951493071
Deleted 11 objects
--切换到非归档模式
SYS@seiang11g>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@seiang11g>startup mount
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
SYS@seiang11g>alter database noarchivelog;
Database altered.
SYS@seiang11g>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 3
Current log sequence 5
SYS@seiang11g>alter database open;
Database altered.
--用RMAN对数据库做一个冷备份
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:13:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB11G (DBID=3377212249)
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> allocate channel c1 type disk;
5> allocate channel c2 type disk;
6> backup database filesperset 2 format '/u01/app/oracle/RMAN_Backup/cold/full_%n_%T_%t_%s_%p.bak';
7> backup spfile format='/u01/app/oracle/RMAN_Backup/cold/spfile_%n_%U_%T.bak';
8> backup archivelog all format '/u01/app/oracle/RMAN_Backup/cold/arch_%d_%T_%s_%p.bak';
9> backup current controlfile format '/u01/app/oracle/RMAN_Backup/cold/ctl_%d_%T_%s_%p.bak';
10> alter database open;
11> release channel c1;
12> release channel c2;
13> }
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
allocated channel: c1
channel c1: SID=18 device type=DISK
allocated channel: c2
channel c2: SID=19 device type=DISK
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/OraDB11g/system01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/OraDB11g/users01.dbf
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/OraDB11g/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/OraDB11g/seiang01.dbf
channel c1: starting piece 1 at 08-AUG-17
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:45
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/OraDB11g/good01.dbf
channel c2: starting piece 1 at 08-AUG-17
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:03
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:11
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 08-AUG-17
including current control file in backup set
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498882_30_1.bak tag=TAG20170808T171345 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498874_29_1.bak tag=TAG20170808T171345 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/spfile_ORADB11G_0vsbde43_1_1_20170808.bak tag=TAG20170808T171443 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
Starting backup at 08-AUG-17
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=14 STAMP=951494133
channel c1: starting piece 1 at 08-AUG-17
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=16 STAMP=951494153
input archived log thread=1 sequence=15 RECID=17 STAMP=951494153
input archived log thread=1 sequence=16 RECID=15 STAMP=951494153
channel c2: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_32_1.bak tag=TAG20170808T171444 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=18 STAMP=951495099
input archived log thread=1 sequence=2 RECID=19 STAMP=951495624
input archived log thread=1 sequence=3 RECID=20 STAMP=951496104
input archived log thread=1 sequence=4 RECID=21 STAMP=951496520
channel c1: starting piece 1 at 08-AUG-17
channel c2: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_33_1.bak tag=TAG20170808T171444 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/arch_ORADB11G_20170808_34_1.bak tag=TAG20170808T171444 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
Starting backup at 08-AUG-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 08-AUG-17
channel c1: finished piece 1 at 08-AUG-17
piece handle=/u01/app/oracle/RMAN_Backup/cold/ctl_ORADB11G_20170808_35_1.bak tag=TAG20170808T171447 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-AUG-17
database opened
released channel: c1
released channel: c2
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
23 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
24 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
25 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
26 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
27 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
28 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171345
29 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171443
30 B A A DISK 08-AUG-17 1 1 NO TAG20170808T171444
31 B A A DISK 08-AUG-17 1 1 NO TAG20170808T171444
32 B A A DISK 08-AUG-17 1 1 NO TAG20170808T171444
33 B F A DISK 08-AUG-17 1 1 NO TAG20170808T171447
************************************************************************************************************************************
示例四：某个数据文件丢失，日志未覆盖情况下做RMAN完全恢复
--查看当前日志
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 INACTIVE
2 5 CURRENT
3 3 INACTIVE
SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables
2 where owner='SEIANG';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SEIANG WJQ1 SEIANG
SEIANG WJQ2 GOOD
SEIANG WJQ3 GOOD
--创建表test1,隶属于good表空间
SYS@seiang11g>create table seiang.test1(id number,grade number) tablespace good;
Table created.
--第一条数据，提交，归档
SYS@seiang11g>insert into seiang.test1 values(1,90);
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter database archive log current;
alter database archive log current
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
（非归档模式下，不能使用该命令切换日志）
SYS@seiang11g>alter system switch logfile;
System altered.
--第二条数据，提交，不归档
SYS@seiang11g>insert into seiang.test1 values(2,85);
1 row created.
SYS@seiang11g>commit;
Commit complete.
--第三条数据，不提交，不归档
SYS@seiang11g>insert into seiang.test1 values(3,99);
1 row created.
SYS@seiang11g>select * from seiang.test1;
ID GRADE
---------- ----------
1 90
2 85
3 99
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 INACTIVE
2 5 ACTIVE
3 6 CURRENT
--突然断电，手工删除good表空间对应的数据文件
SYS@seiang11g>shutdown abort
ORACLE instance shut down.
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf
--尝试启动数据库
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'
--登陆到RMAN，执行恢复操作
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:24:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB11G (DBID=3377212249, not open)
RMAN> run{
2> restore database;
3> recover database;
4> alter database open;
5> }
Starting restore at 08-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
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 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00001 to /u01/app/oracle/oradata/OraDB11g/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/OraDB11g/good01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 /u01/app/oracle/oradata/OraDB11g/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 08-AUG-17
Starting recover at 08-AUG-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log
archived log file name=/u01/app/oracle/oradata/OraDB11g/redo02.log thread=1 sequence=5
archived log file name=/u01/app/oracle/oradata/OraDB11g/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-AUG-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/08/2017 17:25:05
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
SYS@seiang11g>select * from seiang.test1;
ID GRADE
---------- ----------
1 90
2 85
由恢复的结果可以看出，恢复到最后一次提交。
总结：
非归档模式下，当某个数据文件丢失时且日志没有被覆盖的情况下，采用RMAN完全恢复的步骤为：
1、restore datafile 数据文件号（通过select file#,error from v$recover_file查询获取）
2、recover datafile 数据文件号
3、alter database open；
恢复方式，与归档模式下的一般恢复模式一样。
************************************************************************************************************************************
示例五：某个数据文件丢失，日志被覆盖情况下做RMAN不完全恢复
--创建托test2，隶属于good表空间
SYS@seiang11g>create table seiang.test2(name varchar2(20),grade number) tablespace good;
Table created.
--第一条数据，提交，归档
SYS@seiang11g>insert into seiang.test2 values('wjq',89);
1 row created.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--第二条数据，提交，不归档
SYS@seiang11g>insert into seiang.test2 values('seiang',90);
1 row created.
SYS@seiang11g>commit;
Commit complete.
--第三条数据，不提交，不归档
SYS@seiang11g>insert into seiang.test2 values('wjqgood',100);
1 row created.
SYS@seiang11g>select * from seiang.test2;
NAME GRADE
-------------------- ----------
wjq 89
seiang 90
wjqgood 100
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 2 CURRENT
3 0 UNUSED
--三个日志文件，执行四次切换，使得之前的日志被覆盖掉
SYS@seiang11g>alter system switch logfile;
System altered.
SYS@seiang11g>/
System altered.
SYS@seiang11g>/
System altered.
SYS@seiang11g>/
System altered.
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 INACTIVE
2 5 INACTIVE
3 6 CURRENT
--突然断电，good表空间对应的数据文件丢失
SYS@seiang11g>shutdown abort
ORACLE instance shut down.
SYS@seiang11g>
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/good01.dbf
--尝试启动数据库
SYS@seiang11g>startup
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/good01.dbf'
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE FILE NOT FOUND 0
--登陆RMAN，执行恢复操作
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:36:02 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB11G (DBID=3377212249, not open)
RMAN> restore datafile 7;
Starting restore at 08-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
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 00007 to /u01/app/oracle/oradata/OraDB11g/good01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-AUG-17
RMAN> recover datafile 7;
Starting recover at 08-AUG-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo01.log
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo02.log
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/OraDB11g/redo03.log
RMAN-08187: WARNING: media recovery until SCN 1138946 complete
Finished recover at 08-AUG-17
以上红色字体标识内容，可知recover失败了，提示信息不够明确，原因就是指日志被覆盖，不连续了，在此我们只能做整个数据库的恢复，具体操作如下：
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 8 17:38:35 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
RMAN> restore controlfile from '/u01/app/oracle/RMAN_Backup/cold/ctl_ORADB11G_20170808_35_1.bak';
Starting restore at 08-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/OraDB11g/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
Finished restore at 08-AUG-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 08-AUG-17
Starting implicit crosscheck backup at 08-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 08-AUG-17
Starting implicit crosscheck copy at 08-AUG-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-AUG-17
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
skipping datafile 7; already restored to file /u01/app/oracle/oradata/OraDB11g/good01.dbf
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 00002 to /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OraDB11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498825_26_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00001 to /u01/app/oracle/oradata/OraDB11g/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498826_25_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498871_28_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 /u01/app/oracle/oradata/OraDB11g/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/RMAN_Backup/cold/full_ORADB11G_20170808_951498870_27_1.bak tag=TAG20170808T171345
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 08-AUG-17
RMAN> recover database;
Starting recover at 08-AUG-17
using channel ORA_DISK_1
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 1138946 complete
Finished recover at 08-AUG-17
--查看当前数据库的SCN，发现已经恢复成功
SYS@seiang11g>select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1138946
SYS@seiang11g> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1138946
2 1138946
3 1138946
4 1138946
5 1138946
6 1138946
7 1138946
SYS@seiang11g> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1138946
2 1138946
3 1138946
4 1138946
5 1138946
6 1138946
7 1138946
SYS@seiang11g>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--由于在线日志文件的SCN和控制文件，数据文件不一致，只能使用resetlogs打开数据库
SYS@seiang11g>alter database open resetlogs;
Database altered.
SYS@seiang11g>select * from seiang.test2;
select * from seiang.test2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables
2 where owner='SEIANG';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SEIANG WJQ1 SEIANG
SEIANG WJQ2 GOOD
SEIANG WJQ3 GOOD
由此可见，这里只能恢复至备份状态，因为日志被丢失不能做完全恢复，只能恢复到开始备份时的状态。
总结：
非归档模式下，当某个数据文件丢失时且日志被覆盖的情况下，采用RMAN完全恢复的步骤为：：
1、通过restore controlfile from方式转储最新的控制文件备份
2、alter database mount
3、restore database
4、recover databse
5、alter database open resetlogs；
只是因为日志被丢失，只能做不完全恢复。
作者：SEian.G（苦练七十二变，笑对八十一难）
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31015730/viewspace-2143276/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31015730/viewspace-2143276/
6118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
