本文主要记述10个完全恢复的演示案例，每个案例均可与之前手工完全恢复演练相对应，操作时可复习下之前手工备份的内容。在案例演示之前，先要做好冷热备份，下面开始做rman冷热备。

--执行冷备

RMAN> run{

2> shutdown immediate;

3> startup mount;

allocate channel c1 type disk;

allocate channel c2 type disk;

4> 5> 6> backup database format'/home/oracle/rman_bak/cold_bak/%d_%s_%p.bak';

7> alter database open;

8> }

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

allocated channel: c1

channel c1: SID=19 device type=DISK

allocated channel: c2

channel c2: SID=21 device type=DISK

Starting backup at 18-APR-14

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/disk3/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/PROD/disk3/fba01.dbf

channel c1: starting piece 1 at 18-APR-14

channel c2: starting compressed full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/disk3/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

channel c2: starting piece 1 at 18-APR-14

channel c1: finished piece 1 at 18-APR-14

piece handle=/home/oracle/rman_bak/cold_bak/PROD_55_1.bak tag=TAG20140418T174338 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:17

channel c2: finished piece 1 at 18-APR-14

piece handle=/home/oracle/rman_bak/cold_bak/PROD_56_1.bak tag=TAG20140418T174338 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:39

Finished backup at 18-APR-14

Starting Control File Autobackup at 18-APR-14

piece handle=/home/oracle/backup/control/c-239015079-20140418-00 comment=NONE

Finished Control File Autobackup at 18-APR-14

database opened

released channel: c1

released channel: c2

--执行热备

RMAN> run{

2> sql 'alter system switch logfile';

3> allocate channel c1 type disk;

4> allocate channel c2 type disk;

5> backup database format'/home/oracle/rman_bak/hot_bak/%d_%s_%u.bak' filesperset 3;

sql 'alter system switch logfile';

}

6> 7>

sql statement: alter system switch logfile

allocated channel: c1

channel c1: SID=19 device type=DISK

allocated channel: c2

channel c2: SID=21 device type=DISK

Starting backup at 18-APR-14

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/disk3/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/PROD/disk3/fba01.dbf

channel c1: starting piece 1 at 18-APR-14

channel c2: starting compressed full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/disk3/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

channel c2: starting piece 1 at 18-APR-14

channel c1: finished piece 1 at 18-APR-14

piece handle=/home/oracle/rman_bak/hot_bak/PROD_58_1qp62ant.bak tag=TAG20140418T174636 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:08

channel c2: finished piece 1 at 18-APR-14

piece handle=/home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak tag=TAG20140418T174636 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:26

Finished backup at 18-APR-14

Starting Control File Autobackup at 18-APR-14

piece handle=/home/oracle/backup/control/c-239015079-20140418-01 comment=NONE

Finished Control File Autobackup at 18-APR-14

sql statement: alter system switch logfile

released channel: c1

released channel: c2

一、归档模式，执行rman的恢复

案例一：所有数据都丢失（控制文件丢失）

场景：对scott.lxtb1表分三次插入数据（如之前手工完全恢复演练一样，第一次提交归档、第二次只提交不归档，第三次不提交不归档），之后突然断电，然后删除所有的控制文件及数据文件，在此情况，对数据库做rman完全恢复；

--控制文件破坏之前，先对其做转储

SQL> show parameter dump;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /u01/app/oracle/oradata/PROD/d

ump/diag/rdbms/prod/PROD/trace

core_dump_dest string /u01/app/oracle/oradata/PROD/d

ump/diag/rdbms/prod/PROD/cdump

max_dump_file_size string unlimited

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/oradata/PROD/d

ump/diag/rdbms/prod/PROD/trace

SQL> alter database backup controlfile to trace;

Database altered

注意：转储控制文件时，还可指定文件名，方便查找，如：

alter database backup controlfile to trace as '/u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/prod_ora_20140526.tra';

[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace

[oracle@gc1 trace]$ ls -lt | grep PROD_ora

-rw-r----- 1 oracle oinstall 10177 Apr 18 18:02 PROD_ora_2612.trc

-rw-r----- 1 oracle oinstall 110 Apr 18 18:02 PROD_ora_2612.trm

-rw-r----- 1 oracle oinstall 2106 Apr 18 17:47 PROD_ora_2528.trc

-rw-r----- 1 oracle oinstall 488 Apr 18 17:47 PROD_ora_2528.trm

-rw-r----- 1 oracle oinstall 1004 Apr 18 17:44 PROD_ora_2526.trc

-rw-r----- 1 oracle oinstall 78 Apr 18 17:44 PROD_ora_2526.trm

-rw-r----- 1 oracle oinstall 1384 Apr 18 17:43 PROD_ora_2529.trc

-rw-r----- 1 oracle oinstall 82 Apr 18 17:43 PROD_ora_2529.trm

-rw-r----- 1 oracle oinstall 14428 Apr 18 17:43 PROD_ora_2330.trc

-rw-r----- 1 oracle oinstall 258 Apr 18 17:43 PROD_ora_2330.trm

-rw-r----- 1 oracle oinstall 1553 Apr 18 17:42 PROD_ora_2165.trc

……

--查看转储下来的PROD_ora_2612.trc控制文件信息

[oracle@gc1 trace]$ more PROD_ora_2612.trc

找到noresetlogs方式的重建控制文件语句，如下：

--开始做破坏性实验操作，实验表为scott.lxtb1

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select count(1) from scott.lxtb1;

COUNT(1)

----------

14

SQL> insert into scott.lxtb1 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(1) from scott.lxtb1;

COUNT(1)

----------

28

SQL> insert into scott.lxtb1 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from scott.lxtb1;

COUNT(1)

----------

42

SQL> insert into scott.lxtb1 select * from scott.emp;

14 rows created.

SQL> select count(1) from scott.lxtb1;

COUNT(1)

----------

56

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

6 rows selected.

--模拟断电

SQL> shutdown abort;

ORACLE instance shut down.

--手工破坏所有数据文件及控制文件

[oracle@gc1 rman_bak]$ cd /u01/app/oracle/oradata/PROD/

[oracle@gc1 PROD]$ ls

disk1 disk2 disk3 disk4 disk5 dump

[oracle@gc1 PROD]$ cd disk1;

[oracle@gc1 disk1]$ ls

control01.ctl redo01.log redo02.log redo03.log

[oracle@gc1 disk1]$ rm *.ctl

[oracle@gc1 disk1]$ cd ../disk2

[oracle@gc1 disk2]$ ls

control01.ctl redo01.log redo02.log redo03.log

[oracle@gc1 disk2]$ rm *.ctl

[oracle@gc1 disk2]$ ls

redo01.log redo02.log redo03.log

[oracle@gc1 disk2]$ cd ../disk3

[oracle@gc1 disk3]$ ls

control01.ctl fba01.dbf lxtbs01.dbf system01.dbf users01.dbf

[oracle@gc1 disk3]$ rm *

[oracle@gc1 disk3]$ cd ../disk4

[oracle@gc1 disk4]$ ls

sysaux01.dbf temp01.dbf

[oracle@gc1 disk4]$ rm *

[oracle@gc1 disk4]$ cd ../disk5

[oracle@gc1 disk5]$ ls

undotbs01.dbf

[oracle@gc1 disk5]$ rm *

[oracle@gc1 disk5]$

--此时启库报错

SQL> startup;

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

ORA-00205: error in identifying control file, check alert log for more info

--开始恢复

--查看控制文件备份信息，并转储最新的控制文件

[oracle@gc1 disk5]$ cd /home/oracle/backup/control

[oracle@gc1 control]$ ls -lt

total 15448

-rw-r----- 1 oracle oinstall 7897088 Apr 18 17:47 c-239015079-20140418-01

-rw-r----- 1 oracle oinstall 7897088 Apr 18 17:44 c-239015079-20140418-00

[oracle@gc1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 18 19:18:51 2014

connected to target database: PROD (not mounted)

RMAN> restore controlfile from '/home/oracle/backup/control/c-239015079-20140418-01';

Starting restore at 18-APR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/u01/app/oracle/oradata/PROD/disk1/control01.ctl

output file name=/u01/app/oracle/oradata/PROD/disk2/control01.ctl

output file name=/u01/app/oracle/oradata/PROD/disk3/control01.ctl

Finished restore at 18-APR-14

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=19 device type=DISK

Starting restore at 18-APR-14

Starting implicit crosscheck backup at 18-APR-14

Crosschecked 5 objects

Finished implicit crosscheck backup at 18-APR-14

Starting implicit crosscheck copy at 18-APR-14

Finished implicit crosscheck copy at 18-APR-14

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 00003 to /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk3/users01.dbf

channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

channel c1: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_58_1qp62ant.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/PROD/disk3/system01.dbf

channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

channel c2: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

channel c2: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak

channel c1: piece handle=/home/oracle/rman_bak/hot_bak/PROD_58_1qp62ant.bak tag=TAG20140418T174636

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:36

channel c2: piece handle=/home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak tag=TAG20140418T174636

channel c2: restored backup piece 1

channel c2: restore complete, elapsed time: 00:00:57

Finished restore at 18-APR-14

Starting recover at 18-APR-14

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/oradata/PROD/disk1/redo02.log

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/oradata/PROD/disk1/redo03.log

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/oradata/PROD/disk1/redo01.log

archived log file name=/u01/app/oracle/oradata/PROD/disk1/redo02.log thread=1 sequence=14

archived log file name=/u01/app/oracle/oradata/PROD/disk1/redo03.log thread=1 sequence=15

archived log file name=/u01/app/oracle/oradata/PROD/disk1/redo01.log thread=1 sequence=16

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

Finished recover at 18-APR-14

released channel: c1

released channel: c2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 04/18/2014 19:29:03

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

到此，数据库恢复成功。

--查看恢复后,scott.lxtb1表的记录数。

SQL> select count(1) from scott.lxtb1;

COUNT(1)

----------

42

由此可见，恢复到最后一次提交。

总结：当所有数据都丢失（控制文件、数据文件）时，采用rman完全恢复的步骤为：

1、通过restore controlfile from方式转储最新的控制文件备份

2、alter database mount

3、restore database

4、recover databse

5、alter database open resetlogs；

其中3、4、5步骤直接写在一个run语句中，一步执行即可。而转储的文件信息由rman自己决定最优选择，不需要我们人为指定（这里有冷热备的情况,rman优先选择了热备，若想人为干预，你也可以指定用哪个备份）。

而采用手工完全恢复（详见“手工完全恢复演练”章节案例一）步骤为：

1、os级cp转储所有的数据文件

2、采用noresetlogs方式重建控制文件（重建语句从控制文件的trace文件获取）

3、recover databse

4、alter database open

二者对比可知，rman方式更便捷。

案例二、所有数据文件都丢失（控制文件还在）

场景：对scott.lxtb2表分三次插入数据（操作流程同案例一），之后突然断电，然后删除所有的数据文件，在此情况，对数据库做rman完全恢复；

--对scott.lxtb2实验表分三次插入数据

SQL> select count(1) from scott.lxtb2;

COUNT(1)

----------

14

SQL> insert into scott.lxtb2 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> insert into scott.lxtb2 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from scott.lxtb2;

COUNT(1)

----------

42

SQL> insert into scott.lxtb2 select * from scott.emp;

14 rows created.

SQL> select count(1) from scott.lxtb2;

COUNT(1)

----------

56

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

6 rows selected.

--模拟断电即非法关库

SQL> shutdown abort;

ORACLE instance shut down.

--手工破坏所有数据文件

[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/disk3

[oracle@gc1 disk3]$ ls

control01.ctl fba01.dbf lxtbs01.dbf system01.dbf users01.dbf

[oracle@gc1 disk3]$ rm *.dbf

[oracle@gc1 disk3]$ cd ../disk4;

[oracle@gc1 disk4]$ ls

sysaux01.dbf temp01.dbf

[oracle@gc1 disk4]$ rm *

[oracle@gc1 disk4]$ cd ../disk5

[oracle@gc1 disk5]$ ls

undotbs01.dbf

[oracle@gc1 disk5]$ rm *

[oracle@gc1 disk5]$

--启库报错，查看具体的文件错误信息

SQL> startup;

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 FILE NOT FOUND

2 FILE NOT FOUND

3 FILE NOT FOUND

4 FILE NOT FOUND

5 FILE NOT FOUND

6 FILE NOT FOUND

6 rows selected.

--执行rman恢复数据库

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> }

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=18 device type=DISK

allocated channel: c2

channel c2: SID=21 device type=DISK

Starting restore at 19-APR-14

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/PROD/disk5/undotbs01.dbf

channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/disk3/users01.dbf

channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

channel c1: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_58_1qp62ant.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/PROD/disk3/system01.dbf

channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

channel c2: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

channel c2: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak

channel c1: piece handle=/home/oracle/rman_bak/hot_bak/PROD_58_1qp62ant.bak tag=TAG20140418T174636

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:52

channel c2: piece handle=/home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak tag=TAG20140418T174636

channel c2: restored backup piece 1

channel c2: restore complete, elapsed time: 00:01:01

Finished restore at 19-APR-14

Starting recover at 19-APR-14

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/PROD/1_14_844605031.dbf

archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/PROD/1_15_844605031.dbf

archived log for thread 1 with sequence 16 is already on disk as file /home/oracle/PROD/1_16_844605031.dbf

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/PROD/1_1_845235054.dbf

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/PROD/1_2_845235054.dbf

archived log file name=/home/oracle/PROD/1_14_844605031.dbf thread=1 sequence=14

archived log file name=/home/oracle/PROD/1_15_844605031.dbf thread=1 sequence=15

archived log file name=/home/oracle/PROD/1_16_844605031.dbf thread=1 sequence=16

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

Finished recover at 19-APR-14

database opened

released channel: c1

released channel: c2

RMAN>

--查看恢复后scott.lxtb2表的记录数

SQL> select count(1) from scott.lxtb2;

COUNT(1)

----------

42

由此可见，也是恢复至数据库的最后一次提交。

总结：当所有数据文件都丢失时，采用rman完全恢复的步骤为：

1、restore database

2、recover databse

3、alter database open；

而且这三步放在一个run语句中，直接执行即可。同上例一样，这里不需人为指定要转储的数据文件信息（当然，若想人为干预，可指定转储信息），rman会自己选择最优的转储方式。

而采用手工完全恢复（详见“手工完全恢复演练”章节案例二）步骤为：

1、os级cp转储所有的数据文件

2、recover databse

3、alter database open

二者对比可知，rman方式更便捷。

案例三、部分数据文件丢失，rman完全恢复演练

场景：对scott.lxtb3表分三次插入数据（操作流程同案例一），之后突然断电，然后删除某个数据文件（lxtb01.dbf），在此情况，对数据库做rman完全恢复；

--对scott.lxtb3表分三次插入数据

SQL> select count(1) from scott.lxtb3;

COUNT(1)

----------

14

SQL> insert into scott.lxtb3 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(1) from scott.lxtb3;

COUNT(1)

----------

28

SQL> insert into scott.lxtb3 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from scott.lxtb3;

COUNT(1)

----------

42

SQL> insert into scott.lxtb3 select * from scott.emp;

14 rows created.

SQL> select count(1) from scott.lxtb3;

COUNT(1)

----------

56

--查看scott.lxtb3表所在表空间

SQL> select table_name,owner,tablespace_name from dba_tables where table_name like 'LXTB%';

TABLE_NAME OWNER TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

LXTB7 SCOTT LXTBS

LXTB6 SCOTT LXTBS

LXTB5 SCOTT LXTBS

LXTB4 SCOTT LXTBS

LXTB3 SCOTT LXTBS

LXTB1 SCOTT LXTBS

LXTB2 SCOTT LXTBS

LXTB9 SCOTT LXTBS

LXTB8 SCOTT LXTBS

9 rows selected.

--查看数据文件信息

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

6 rows selected.

--模拟断电，非法关库

SQL> shutdown abort;

ORACLE instance shut down.

--删除lxtbs01.dbf

[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

--启库报错，查看具体的文件错误信息

SQL> startup;

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 UNKNOWN ERROR

2 UNKNOWN ERROR

3 UNKNOWN ERROR

4 UNKNOWN ERROR

5 FILE NOT FOUND

--执行rman恢复数据库（这次采用指定转储方式恢复被破坏的数据文件）

RMAN> run{

2> restore datafile 5 from tag 'TAG20140418T174338';

3> recover datafile 5;

4> alter database open;

5> }

Starting restore at 19-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

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/PROD/disk3/lxtbs01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bak/cold_bak/PROD_56_1.bak

channel ORA_DISK_1: piece handle=/home/oracle/rman_bak/cold_bak/PROD_56_1.bak tag=TAG20140418T174338

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 19-APR-14

Starting recover at 19-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/PROD/1_13_844605031.dbf

archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/PROD/1_14_844605031.dbf

archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/PROD/1_15_844605031.dbf

archived log for thread 1 with sequence 16 is already on disk as file /home/oracle/PROD/1_16_844605031.dbf

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/PROD/1_1_845235054.dbf

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/PROD/1_2_845235054.dbf

archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/PROD/1_3_845235054.dbf

archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/PROD/1_4_845235054.dbf

archived log file name=/home/oracle/PROD/1_13_844605031.dbf thread=1 sequence=13

archived log file name=/home/oracle/PROD/1_14_844605031.dbf thread=1 sequence=14

archived log file name=/home/oracle/PROD/1_15_844605031.dbf thread=1 sequence=15

archived log file name=/home/oracle/PROD/1_16_844605031.dbf thread=1 sequence=16

archived log file name=/home/oracle/PROD/1_1_845235054.dbf thread=1 sequence=1

archived log file name=/home/oracle/PROD/1_2_845235054.dbf thread=1 sequence=2

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

Finished recover at 19-APR-14

database opened

注意：这次转储数据文件时，指定了转储冷备的信息，从打印的恢复日志可知，用的是冷备（前面rman自选的是热备）。当然，如果不指定的话，直接用restore datafile 5即可。

--查看恢复后scott.lxtb3的记录数

SQL> select count(1) from scott.lxtb3;

COUNT(1)

----------

42

由此可见，也是恢复到最后一次提交。

总结：当某个数据文件丢失时，采用rman完全恢复的步骤为：

1、restore datafile 数据文件号（通过select file#,error from v$recover_file查询获取）

2、recover datafile 数据文件号

3、alter database open；

而且这三步放在一个run语句中，直接执行即可。还可使用restore/recover tablespace方式进行恢复，上面案例恢复演示过程中，指定了转储的备份信息，通常我们可以不指定，rman会自己选择最优方式转储，在这只是为了演示可以采取指定的方式。

而采用手工完全恢复（详见“手工完全恢复演练”章节案例三）步骤为：

1、os级cp转储被破坏的数据文件

2、recover tablespace 表空间名/recover datafile 数据文件号（注意：若此表空间对应多个数据文件则采用recover tablespace方式，否则选recover datafile方式）

3、alter database open

二者对比可知，优选rman方式。

案例四、部分数据文件丢失，rman完全恢复演练（高可用模式）

场景：此场景同案例三（对scott.lxtb4表分三次插入数据（操作流程同案例一），之后突然断电，然后删除数据文件lxtb01.dbf），只是恢复时采用高可用模式（即先开库，再恢复）；

--对scott.lxtb4表分三次插入数据

SQL> select count(1) from scott.lxtb4;

COUNT(1)

----------

14

SQL> insert into scott.lxtb4 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(1) from scott.lxtb4;

COUNT(1)

----------

28

SQL> insert into scott.lxtb4 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from scott.lxtb4;

COUNT(1)

----------

42

SQL> insert into scott.lxtb4 select * from scott.emp;

14 rows created.

SQL> select count(1) from scott.lxtb4;

COUNT(1)

----------

56

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

6 rows selected.

--非法关库

SQL> shutdown abort;

ORACLE instance shut down.

--手工删除lxtb01.dbf数据文件

[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

--启库失败，查看文件错误信息

SQL> startup

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 UNKNOWN ERROR

2 UNKNOWN ERROR

3 UNKNOWN ERROR

4 UNKNOWN ERROR

5 FILE NOT FOUND

--执行rman恢复

RMAN> run{

2> sql 'alter database datafile 5 offline';

3> alter database open;

4> restore datafile 5;

5> recover datafile 5;

6> sql 'alter database datafile 5 online';

7> }

using target database control file instead of recovery catalog

sql statement: alter database datafile 5 offline

database opened

Starting restore at 19-APR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=35 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 00005 to /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak

channel ORA_DISK_1: piece handle=/home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak tag=TAG20140418T174636

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

Finished restore at 19-APR-14

Starting recover at 19-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/PROD/1_14_844605031.dbf

archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/PROD/1_15_844605031.dbf

archived log for thread 1 with sequence 16 is already on disk as file /home/oracle/PROD/1_16_844605031.dbf

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/PROD/1_1_845235054.dbf

archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/PROD/1_2_845235054.dbf

archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/PROD/1_3_845235054.dbf

archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/PROD/1_4_845235054.dbf

archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/PROD/1_5_845235054.dbf

archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/PROD/1_6_845235054.dbf

archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/PROD/1_7_845235054.dbf

archived log file name=/home/oracle/PROD/1_14_844605031.dbf thread=1 sequence=14

archived log file name=/home/oracle/PROD/1_15_844605031.dbf thread=1 sequence=15

archived log file name=/home/oracle/PROD/1_16_844605031.dbf thread=1 sequence=16

archived log file name=/home/oracle/PROD/1_1_845235054.dbf thread=1 sequence=1

archived log file name=/home/oracle/PROD/1_2_845235054.dbf thread=1 sequence=2

archived log file name=/home/oracle/PROD/1_3_845235054.dbf thread=1 sequence=3

archived log file name=/home/oracle/PROD/1_4_845235054.dbf thread=1 sequence=4

archived log file name=/home/oracle/PROD/1_5_845235054.dbf thread=1 sequence=5

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

Finished recover at 19-APR-14

sql statement: alter database datafile 5 online

RMAN>

--查看恢复后，scott.lxtb4表的记录数

SQL> select count(1) from scott.lxtb4;

COUNT(1)

----------

42

注意：本案例的恢复方式同上一案例差不多，只是先将破坏的数据文件offline，开库后再做恢复。但要注意若system与undo表空间对应的数据文件破坏了，不能采用此方式进行恢复，因为他们不能offline。

另外，此案例可以在若在不关库的情况下，做数据文件的删除破坏，则要体现报错，需要执行alter system flush buffer_cache清理缓存。恢复时，只要去掉开库命令即可，在此不演示了。

总结：当某个数据文件丢失时，采用rman高可用模式进行完全恢复的步骤为：

1、sql 'alter database datafile 数据文件号 offline'；

2、alter database open；

3、restore datafile 数据文件号（通过select file#,error from v$recover_file查询获取）

4、recover datafile 数据文件号

5、sql 'alter database datafile 数据文件号 online'；

所有步骤放一个run语句中，直接执行即可。system、undo表空间数据文件损坏不能采用此方式进行恢复，只能用案例三的一般模式进行恢复。

而采用手工完全恢复（详见“手工完全恢复演练”章节案例四）步骤为：

1、alter database datafile 数据文件号 offline；

2、alter database open；

3、os级cp转储被破坏的数据文件;

4、recover datafile 数据文件号;

5、alter database datafile 数据文件号 online;

二者对比可知，优选rman方式。

案例五、新建表空间对应的数据文件丢失，执行rman完全恢复

场景：新建表空间zhentbs，然后在其上新建表zhentb，并对zhentb表分三次插入数据，然后模拟断电，非法关库，删除zhentbs对应的数据文件，在此情况下，做rman的完全恢复（因是新建的表空间，故没有相关的数据文件备份）。

--查看当前表空间及数据文件信息

SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS

------------------------------ --------- ---------

SYSTEM PERMANENT ONLINE

SYSAUX PERMANENT ONLINE

UNDOTBS UNDO ONLINE

TEMPTS TEMPORARY ONLINE

USERS PERMANENT ONLINE

LXTBS PERMANENT ONLINE

FBA PERMANENT ONLINE

7 rows selected.

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

6 rows selected.

--创建表空间zhentbs，并在其上面新建表zhentb

SQL> create tablespace zhentbs datafile '/u01/app/oracle/oradata/PROD/disk4/zhen01.dbf' size 50m;

Tablespace created.

SQL> create table zhentb tablespace zhentbs as select * from scott.emp;

Table created.

SQL> select count(1) from zhentb;

COUNT(1)

----------

14

--对zhentbs表分三次插入数据

SQL> insert into zhentb select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(1) from zhentb;

COUNT(1)

----------

28

SQL> insert into zhentb select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from zhentb;

COUNT(1)

----------

42

SQL> insert into zhentb select * from scott.emp;

14 rows created.

SQL> select count(1) from zhentb;

COUNT(1)

----------

56

--非法关库，并删除刚新建表空间对应的数据文件zhen01.dbf

SQL> shutdown abort;

ORACLE instance shut down.

[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk4/zhen01.dbf

--启库失败，查看具体数据文件错误信息

SQL> startup

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD/disk4/zhen01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 UNKNOWN ERROR

2 UNKNOWN ERROR

3 UNKNOWN ERROR

4 UNKNOWN ERROR

5 UNKNOWN ERROR

6 UNKNOWN ERROR

7 FILE NOT FOUND

7 rows selected.

--采用高可用模式执行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 19-APR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=33 device type=DISK

creating datafile file number=7 name=/u01/app/oracle/oradata/PROD/disk4/zhen01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 19-APR-14

Starting recover at 19-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

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

Finished recover at 19-APR-14

sql statement: alter database datafile 7 online

RMAN>

从恢复日志可知，虽然没有新建的zhen01.dbf的备份信息，但使用restore datafile语句时，rman实际上是做了create datafile 操作，与之前的手工恢复类似。

--查看恢复后zhentb表的记录数

SQL> select count(1) from zhentb;

COUNT(1)

----------

42

总结：当新建表空间对应数据文件丢失时，采用rman恢复步骤：

1、sql 'alter database datafile 数据文件号 offline'；

2、alter database open；

3、restore datafile 数据文件号；

4、recover datafile 数据文件号；

5、sql 'alter database datafile 数据文件号 online'；

注意：此案例恢复方式实际与上一案例的高可用模式进行完全恢复的步骤一样，只是restore datafile时并非真正转储备份的数据文件，而是通过create datafile方式新建数据文件。

而采用手工完全恢复（详见“手工完全恢复演练”章节案例五）步骤为：

1、alter database datafile 数据文件号 offline；

2、alter database open；

3、alter database create datafile 数据文件存放路径（与之前指定的路径一样）;

4、recover datafile 数据文件号;

5、alter database datafile 数据文件号 online;

二者对比可知，rman方式实际也是转储时做了create datafile操作，只不过交由rman来做，不需要人工创建，更省事些。

案例六：某个数据文件丢失且所在磁盘坏掉时，执行rman的完全恢复

场景：对scott.lxtb5表分三次插入数据（操作流程同案例一），之后突然断电，然后删除对应数据文件（lxtb01.dbf）且其所在碰盘坏掉了，此情况下，做rman的完全恢复。

--对scott.lxtb5表分三次插入数据

SQL> select count(1) from scott.lxtb5;

COUNT(1)

----------

14

SQL> insert into scott.lxtb5 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select count(1) from scott.lxtb5;

COUNT(1)

----------

28

SQL> insert into scott.lxtb5 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from scott.lxtb5;

COUNT(1)

----------

42

SQL> insert into scott.lxtb5 select * from scott.emp;

14 rows created.

SQL> select count(1) from scott.lxtb5;

COUNT(1)

----------

56

SQL> col name for a50;

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

6 /u01/app/oracle/oradata/PROD/disk3/fba01.dbf

7 /u01/app/oracle/oradata/PROD/disk4/zhen01.dbf

7 rows selected.

--非法关库

SQL> shutdown abort;

ORACLE instance shut down.

--删除此表所在的数据文件

[oracle@gc1 disk5]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf

--启库失败，查看具体错误信息

SQL> startup

ORACLE instance started.

Total System Global Area 836976640 bytes

Fixed Size 1339740 bytes

Variable Size 490737316 bytes

Database Buffers 339738624 bytes

Redo Buffers 5160960 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 UNKNOWN ERROR

2 UNKNOWN ERROR

3 UNKNOWN ERROR

4 UNKNOWN ERROR

5 FILE NOT FOUND

--执行rman恢复

RMAN> run{

2> sql 'alter database datafile 5 offline';

3> alter database open;

4> set newname for datafile 5 to '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf';

5> restore datafile 5;

6> switch datafile 5;

7> recover datafile 5;

8> sql 'alter database datafile 5 online';

9> }

using target database control file instead of recovery catalog

sql statement: alter database datafile 5 offline

database opened

executing command: SET NEWNAME

Starting restore at 19-APR-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=30 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 00005 to /u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak

channel ORA_DISK_1: piece handle=/home/oracle/rman_bak/hot_bak/PROD_59_1rp62ant.bak tag=TAG20140418T174636

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:09

Finished restore at 19-APR-14

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=845286991 file name=/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf