转载

RMAN完全恢复案例

一、归档模式

示例一：所有数据文件丢失，控制文件也丢失

示例二：新建表空间对应的数据文件丢失

示例三：某数据文件丢失，且所在的磁盘损坏

二、非归档模式

示例四：某数据文件丢失，日志未覆盖情况下做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

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

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

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

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

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

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；
只是因为日志被丢失，只能做不完全恢复。