场景说明：模拟数据库所在磁盘发生故障导致所有文件丢失，包括参数文件，控制文件，数据文件，但是有完整数据库全库备份（含参数文件及控制文件的备份），以及完整的归档日志，online redo备份。

RMAN异地恢复（同平台同操作系统）
场景说明：模拟数据库所在磁盘发生故障导致所有文件丢失，包括参数文件，控制文件，所有redo文件，数据文件，但是有完整数据库全库备份（含参数文件及控制文件的备份），以及完整的归档日志备份。

首先设置rman控制文件自动备份

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

查看数据库是否归档模式

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Next log sequence to archive   29
Current log sequence           29

注意：此处数据库是出于归档模式的，至于如何设定归档模式，此处不再赘述。

查看测试数据

SQL> select * from test.test;

        ID NAME
---------- --------------------
         1 yallonking

使用RMAN备份数据库

[oracle@ora10gr2 ~]$ rman target / log='/u01/app/oracle/backups/full_db_rman_backups.log'
RMAN> run {
2> allocate channel dev1 type disk;
3> allocate channel dev2 type disk;
4> backup full database tag 'full_db_backup' format '/u01/app/oracle/backups/%d_%T_%U_full.dbf';
5> sql 'alter system archive log current';
6> backup filesperset 5 format '/u01/app/oracle/backups/arch_%u_%s_%p' archivelog all delete input;
7> backup current controlfile  format '/u01/app/oracle/backups/db_ctl_%T_%t_%s_%p.bak';
8> release channel dev1;
9> release channel dev2;
10> }
RMAN> 

下边模拟删除所有数据库文件
注意：如果删除所有redo日志，那么如果关闭时没有干净关闭库的话，可能会在下次恢复时进行不完全恢复。

[oracle@ora10gr2 ora10gr2]$ ls -l
total 1006384
-rw-r----- 1 oracle oinstall   7356416 Jan 21 20:12 control01.ctl
-rw-r----- 1 oracle oinstall   7356416 Jan 21 20:12 control02.ctl
-rw-r----- 1 oracle oinstall   7356416 Jan 21 20:12 control03.ctl
-rw-r----- 1 oracle oinstall  52436992 Jan 21 19:31 ogg01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jan 21 19:25 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jan 21 20:12 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jan 21 19:25 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Jan 21 20:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Jan 21 20:11 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jan  1 23:51 temp01.dbf
-rw-r----- 1 oracle oinstall  10493952 Jan 21 19:31 test01.dbf
-rw-r----- 1 oracle oinstall  26222592 Jan 21 20:11 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jan 21 19:31 users01.dbf
[oracle@ora10gr2 ora10gr2]$ mv * ../
[oracle@ora10gr2 ora10gr2]$ ls -l
total 0

删除参数文件

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileora10gr2.ora
[oracle@ora10gr2 dbs]$ ls
alert_ora10gr2.log  hc_ora10gr2.dat  initdw.ora  init.ora  lkORA10GR2  orapwora10gr2  snapcf_ora10gr2.f  spfileora10gr2.ora
[oracle@ora10gr2 dbs]$ mv spfileora10gr2.ora ../

模拟主机断电（此处不是一致性关闭库）

SQL> shutdown abort
ORACLE instance shut down.

RMAN开始恢复
从RMAN日志中找到数据库的DBID

[oracle@ora10gr2 ~]$ cat /u01/app/oracle/backups/full_db_rman_backups.log | grep DBID
connected to target database: ORA10GR2 (DBID=4093928674)
[oracle@ora10gr2 ~]$ rman target / 

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 21 20:21:05 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> set dbid=4093928674

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area     608174080 bytes

Fixed Size                     1220844 bytes
Variable Size                201330452 bytes
Database Buffers             402653184 bytes
Redo Buffers                   2969600 bytes

RMAN> 

查看参数文件备份片，并从备份恢复参数文件到其他目录

[oracle@ora10gr2 ~]$ cat /u01/app/oracle/backups/full_db_rman_backups.log| grep -A 2 SPFILE
Starting Control File and SPFILE Autobackup at 21-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333346_8hwjq5pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-13

sql statement: alter system archive log current
--
Starting Control File and SPFILE Autobackup at 21-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333417_8hwjsbhc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-13

released channel: dev1
[oracle@ora10gr2 ~]$
RMAN> restore spfile to '/u01/app/spfile_temp' from '/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333417_8hwjsbhc_.bkp';

Starting restore at 21-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333417_8hwjsbhc_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 21-JAN-13

[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 20:45:27 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create pfile='/u01/app/pfile.txt' from spfile='/u01/app/spfile_temp';

File created.

查看控制文件所在备份片，并从备份恢复控制文件

[oracle@ora10gr2 ~]$ cat /u01/app/oracle/backups/full_db_rman_backups.log| grep -A 2 Control
Starting Control File and SPFILE Autobackup at 21-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333346_8hwjq5pd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-13

sql statement: alter system archive log current
--
Starting Control File and SPFILE Autobackup at 21-JAN-13
piece handle=/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333417_8hwjsbhc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-13

released channel: dev1

RMAN> restore controlfile to '/u01/app/controlfile01.ctl' from '/u01/app/oracle/flash_recovery_area/ORA10GR2/autobackup/2013_01_21/o1_mf_s_805333417_8hwjsbhc_.bkp';

Starting restore at 21-JAN-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 21-JAN-13

在新地址复用控制文件

[oracle@ora10gr2 app]$ pwd
/u01/app
[oracle@ora10gr2 app]$ ls -l
total 7220
-rw-r----- 1 oracle oinstall 7356416 Jan 21 20:51 controlfile01.ctl
drwxrwxr-x 4 grid   oinstall    4096 Aug 19 04:06 grid
drwxr-xr-x 3 oracle oinstall    4096 Jan  1 21:53 ogg
drwxrwxr-x 7 oracle oinstall    4096 Jan 21 20:40 oracle
drwxrwx--- 6 grid   oinstall    4096 Aug 19 05:03 oraInventory
-rw-r--r-- 1 oracle oinstall    1119 Jan 21 20:46 pfile.txt
-rw-r----- 1 oracle oinstall    3584 Jan 21 20:42 spfile_temp
[oracle@ora10gr2 app]$ cp controlfile01.ctl controlfile02.ctl
[oracle@ora10gr2 app]$ cp controlfile01.ctl controlfile03.ctl

修改参数文件中control_files参数的值指向新的地址

[oracle@ora10gr2 app]$ cat pfile.txt | grep control_files
*.control_files='/u01/app/controlfile01.ctl','/u01/app/controlfile02.ctl','/u01/app/controlfile03.ctl'

使用新的pfile以及新的控制文件在mount下恢复数据库到新的地址

RMAN> shutdown abort

Oracle instance shut down
[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 21:00:52 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile='/u01/app/pfile.txt';
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             201330452 bytes
Database Buffers          402653184 bytes
Redo Buffers                2969600 bytes
Database mounted.

在rman中找到以前数据库数据文件的路径

[oracle@ora10gr2 ~]$ rman target / 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 22 00:02:41 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORA10GR2 (DBID=4093928674, not open)

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    359.09M    DISK        00:02:06     21-JAN-13      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: FULL_DB_BACKUP
        Piece Name: /u01/app/oracle/backups/ORA10GR2_20130121_01o00q6k_1_1_full.dbf
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 501257     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/system01.dbf
  4       Full 501257     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/users01.dbf
  5       Full 501257     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/test01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    172.80M    DISK        00:02:18     21-JAN-13      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: FULL_DB_BACKUP
        Piece Name: /u01/app/oracle/backups/ORA10GR2_20130121_02o00q6k_1_1_full.dbf
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 501258     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
  3       Full 501258     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
  6       Full 501258     21-JAN-13 /u01/app/oracle/oradata/ora10gr2/ogg01.dbf
此处省略其他输出...

根据以上输出在rman的run块中将数据库文件重新指向新的地址，并作restore和recover

RMAN> run {
2> allocate channel dev1 type disk;
3> allocate channel dev2 type disk;
4> set newname for datafile 1  to '/u01/app/system01.dbf';
5> set newname for datafile 4  to '/u01/app/users01.dbf';
6> set newname for datafile 5  to '/u01/app/test01.dbf';
7> set newname for datafile 2  to '/u01/app/undotbs01.dbf';
8> set newname for datafile 3  to '/u01/app/sysaux01.dbf';
9> set newname for datafile 6  to '/u01/app/ogg01.dbf';
10> restore database;
11> switch datafile all;
12> recover database;
13> release channel dev1;
14> release channel dev2;
15> }

released channel: ORA_DISK_1
allocated channel: dev1
channel dev1: sid=155 devtype=DISK

allocated channel: dev2
channel dev2: sid=154 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-JAN-13

channel dev1: starting datafile backupset restore
channel dev1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/system01.dbf
restoring datafile 00004 to /u01/app/users01.dbf
restoring datafile 00005 to /u01/app/test01.dbf
channel dev1: reading from backup piece /u01/app/oracle/backups/ORA10GR2_20130121_01o00q6k_1_1_full.dbf
channel dev2: starting datafile backupset restore
channel dev2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/undotbs01.dbf
restoring datafile 00003 to /u01/app/sysaux01.dbf
restoring datafile 00006 to /u01/app/ogg01.dbf
channel dev2: reading from backup piece /u01/app/oracle/backups/ORA10GR2_20130121_02o00q6k_1_1_full.dbf
channel dev1: restored backup piece 1
piece handle=/u01/app/oracle/backups/ORA10GR2_20130121_01o00q6k_1_1_full.dbf tag=FULL_DB_BACKUP
channel dev1: restore complete, elapsed time: 00:01:07
channel dev2: restored backup piece 1
piece handle=/u01/app/oracle/backups/ORA10GR2_20130121_02o00q6k_1_1_full.dbf tag=FULL_DB_BACKUP
channel dev2: restore complete, elapsed time: 00:01:07
Finished restore at 22-JAN-13

datafile 1 switched to datafile copy
input datafile copy recid=13 stamp=805339465 filename=/u01/app/system01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=14 stamp=805339465 filename=/u01/app/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15 stamp=805339465 filename=/u01/app/test01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=16 stamp=805339465 filename=/u01/app/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=17 stamp=805339465 filename=/u01/app/sysaux01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=18 stamp=805339465 filename=/u01/app/ogg01.dbf

Starting recover at 22-JAN-13

starting media recovery

archive log thread 1 sequence 29 is already on disk as file /u01/app/oracle/oradata/arch/1_29_791701862.dbf
archive log thread 1 sequence 30 is already on disk as file /u01/app/oracle/oradata/arch/1_30_791701862.dbf
archive log filename=/u01/app/oracle/oradata/arch/1_29_791701862.dbf thread=1 sequence=29
archive log filename=/u01/app/oracle/oradata/arch/1_30_791701862.dbf thread=1 sequence=30
unable to find archive log
archive log thread=1 sequence=31
released channel: dev1
released channel: dev2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/22/2013 01:24:33
RMAN-06054: media recovery requesting unknown log: thread 1 seq 31 lowscn 501317

此处发现在recover时缺少日志，这个也和之前将所有redo移走有关（并且在shutdown abort之前没有进行切换日志将日志信息及时写进归档日志），如果redo真的丢失，那么就只能进行不完全恢复，此处我们将之前的redo（假定是online redo的一个镜像备份还存在）移动到新的位置（新位置模拟异地恢复），并再次recover，这里可以从seq号在alert.log中查到具体的redo组，这里将所有redo移过去，挨个尝试。

[oracle@ora10gr2 oradata]$ ls
arch           control02.ctl  ogg01.dbf  redo01.log  redo03.log    system01.dbf  test01.dbf     users01.dbf
control01.ctl  control03.ctl  ora10gr2   redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf
[oracle@ora10gr2 oradata]$ mv *.log /u01/app
RMAN> recover database from "/u01/app/redo01.log";

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "double-quoted-string": expecting one of: "tag"
RMAN-01007: at line 1 column 23 file: standard input

由于recover database时无法指定from子句，此处通过建立软连接进行恢复

[oracle@ora10gr2 ~]$ ln -s /u01/app/redo01.log /u01/app/oracle/oradata/ora10gr2/redo01.log
[oracle@ora10gr2 ~]$ ln -s /u01/app/redo02.log /u01/app/oracle/oradata/ora10gr2/redo02.log
[oracle@ora10gr2 ~]$ ln -s /u01/app/redo03.log /u01/app/oracle/oradata/ora10gr2/redo03.log
[oracle@ora10gr2 ~]$ cd /u01/app/oracle/oradata/ora10gr2/;ls -l
total 0
lrwxrwxrwx 1 oracle oinstall 19 Jan 22 01:41 redo01.log -> /u01/app/redo01.log
lrwxrwxrwx 1 oracle oinstall 19 Jan 22 01:42 redo02.log -> /u01/app/redo02.log
lrwxrwxrwx 1 oracle oinstall 19 Jan 22 01:43 redo03.log -> /u01/app/redo03.log
RMAN> recover database;

Starting recover at 22-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u01/app/oracle/oradata/ora10gr2/redo03.log
archive log filename=/u01/app/oracle/oradata/ora10gr2/redo03.log thread=1 sequence=31
media recovery complete, elapsed time: 00:00:16
Finished recover at 22-JAN-13

最后以resetlogs打开库。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

注意：此时数据库是以RESETLOGS打开的，oracle建议立即对该库进行全完备份。（但是本人在10g的库里边在以resetlogs打开库之后，之前的备份有时候还是可以用的。）

剩下的就是用pfile创建spfile，并以spfile打开库即可。

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile='/u01/app/spfileora10gr2.ora' from pfile='/u01/app/pfile.txt';

File created.

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initora10gr2.ora'

此处是因为oracle通过环境变量ORACLE_HOME和SID找以前路径的下的参数文件，我们可以修改环境变量或者通过软连接的方式解决。此处通过软连接。

[oracle@ora10gr2 dbs]$ ln -s /u01/app/spfileora10gr2.ora /u01/app/oracle/product/10.2.0/db_1/dbs/spfileora10gr2.ora
[oracle@ora10gr2 dbs]$ ls -l /u01/app/oracle/product/10.2.0/db_1/dbs/spfileora10gr2.ora
lrwxrwxrwx 1 oracle oinstall 27 Jan 22 02:07 /u01/app/oracle/product/10.2.0/db_1/dbs/spfileora10gr2.ora -> /u01/app/spfileora10gr2.ora
[oracle@ora10gr2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 22 02:08:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             201330452 bytes
Database Buffers          402653184 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL> select * from test.test;

        ID NAME
---------- --------------------
         1 yallonking
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileora10gr2.ora

至此，全部OK。