在所有文件丢失的情况下使用RMAN恢复数据库
1、实验场景
在数据库的spfile文件、控制文件、所有数据文件、重做日志文件全部丢失的情况下，使用RMAN对数据库进行不完全恢复（由于在线日志也丢失，所以只能进行不完全恢复）
2、备份数据库
[oracle@test backup]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 20 21:10:59 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: XU (DBID=3272830258)
RMAN> backup format='/usr/local/oracle/backup/%d_%s.bak'
2> database include current controlfile
3> spfile;
Starting backup at 20-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/usr/local/oracle/oradata/xu/system01.dbf
input datafile fno=00003 name=/usr/local/oracle/oradata/xu/sysaux01.dbf
input datafile fno=00002 name=/usr/local/oracle/oradata/xu/undotbs01.dbf
input datafile fno=00004 name=/usr/local/oracle/oradata/xu/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_14.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_15.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_16.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-12
Starting Control File and SPFILE Autobackup at 20-JAN-12
piece handle=/usr/local/oracle/10g/dbs/c-3272830258-20120120-04 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-12
3、确认控制文件及spfile文件所在的备份集
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:00 20-JAN-12
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120120T184420
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-01
SPFILE Included: Modification time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 6.80M DISK 00:00:01 20-JAN-12
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20120120T185350
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-03
SPFILE Included: Modification time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 80.00K DISK 00:00:00 20-JAN-12
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20120120T211123
Piece Name: /usr/local/oracle/backup/XU_16.bak
SPFILE Included: Modification time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 6.80M DISK 00:00:00 20-JAN-12
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20120120T211141
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-04
SPFILE Included: Modification time: 20-JAN-12
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:00 20-JAN-12
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120120T184420
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-01
Control File Included: Ckp SCN: 454954 Ckp time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 6.80M DISK 00:00:01 20-JAN-12
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20120120T185350
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-03
Control File Included: Ckp SCN: 455272 Ckp time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 6.77M DISK 00:00:00 20-JAN-12
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20120120T211123
Piece Name: /usr/local/oracle/backup/XU_15.bak
Control File Included: Ckp SCN: 459426 Ckp time: 20-JAN-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 6.80M DISK 00:00:00 20-JAN-12
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20120120T211141
Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-04
Control File Included: Ckp SCN: 459431 Ckp time: 20-JAN-12
4、创建测试表
[oracle@test backup]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:15:59 2012
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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /usr/local/oracle/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> create table t_xu ( a number);
Table created.
SQL> insert into t_xu values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t_xu values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t_xu values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from t_xu;
A
----------
1
2
3
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /usr/local/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>
5、删除spfile文件及数据库文件
[oracle@test backup]$ rm -rf /usr/local/oracle/10g/dbs/spfilexu.ora
[oracle@test backup]$ rm -rf /usr/local/oracle/oradata/xu/*
[oracle@test backup]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:18:19 2012
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> shutdown abort
ORACLE instance shut down.
SQL> exit
6、恢复spfile文件
由于spfile文件丢失，所以使用sqlplus无法启动实例，这时需要使用rman来启动实例，或者手工创建一个只包含db_name的init文件
在正式恢复时，如果使用restore spfile from autobackup命令则需要首先set dbid,否则RMAN无法确定自动备份文件，如果使用restore spfile from ....则不需要set dbid
[oracle@test dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:19:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/10g/dbs/initxu.ora'
SQL> exit
Disconnected
[oracle@test dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 20 21:20:48 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/10g/dbs/initxu.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
RMAN> restore spfile from autobackup;
Starting restore at 20-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/20/2012 21:21:10
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> set dbid=3272830258
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 20-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20120120
channel ORA_DISK_1: autobackup found: c-3272830258-20120120-04
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-JAN-12
RMAN> restore spfile from '/usr/local/oracle/backup/XU_16.bak';
Starting restore at 20-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /usr/local/oracle/backup/XU_16.bak
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-JAN-12
RMAN>
恢复完成后可重启实例，使用恢复后的spfile文件
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 1090519040 bytes
Fixed Size 1218920 bytes
Variable Size 301991576 bytes
Database Buffers 771751936 bytes
Redo Buffers 15556608 bytes
7、恢复控制文件
和恢复spfile文件一样,如果从自动备份中恢复则需要先set dbid，否则不需要
RMAN> set dbid=3272830258
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 20-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20120120
channel ORA_DISK_1: autobackup found: c-3272830258-20120120-04
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/usr/local/oracle/oradata/xu/control01.ctl
output filename=/usr/local/oracle/oradata/xu/control02.ctl
output filename=/usr/local/oracle/oradata/xu/control03.ctl
Finished restore at 20-JAN-12
RMAN>
RMAN> restore controlfile from '/usr/local/oracle/backup/XU_15.bak';
Starting restore at 20-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/usr/local/oracle/oradata/xu/control01.ctl
output filename=/usr/local/oracle/oradata/xu/control02.ctl
output filename=/usr/local/oracle/oradata/xu/control03.ctl
Finished restore at 20-JAN-12
RMAN>
8、mount数据库，并把归档日志信息追加到控制文件
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> catalog start with '/usr/local/oracle/arch';
searching for all files that match the pattern /usr/local/oracle/arch
List of Files Unknown to the Database
=====================================
File Name: /usr/local/oracle/arch/1_2_773088821.dbf
File Name: /usr/local/oracle/arch/1_1_773088821.dbf
File Name: /usr/local/oracle/arch/1_3_773088821.dbf
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /usr/local/oracle/arch/1_2_773088821.dbf
File Name: /usr/local/oracle/arch/1_1_773088821.dbf
File Name: /usr/local/oracle/arch/1_3_773088821.dbf
RMAN>
9、对数据库进行不完全恢复，并以resetlogs方式打开数据库
RMAN> restore database;
Starting restore at 20-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /usr/local/oracle/oradata/xu/system01.dbf
restoring datafile 00002 to /usr/local/oracle/oradata/xu/undotbs01.dbf
restoring datafile 00003 to /usr/local/oracle/oradata/xu/sysaux01.dbf
restoring datafile 00004 to /usr/local/oracle/oradata/xu/users01.dbf
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/backup/XU_14.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/usr/local/oracle/backup/XU_14.bak tag=TAG20120120T211123
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 20-JAN-12
RMAN> recover database until sequence=4;
Starting recover at 20-JAN-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1 is already on disk as file /usr/local/oracle/arch/1_1_773088821.dbf
archive log thread 1 sequence 2 is already on disk as file /usr/local/oracle/arch/1_2_773088821.dbf
archive log thread 1 sequence 3 is already on disk as file /usr/local/oracle/arch/1_3_773088821.dbf
archive log filename=/usr/local/oracle/arch/1_1_773088821.dbf thread=1 sequence=1
archive log filename=/usr/local/oracle/arch/1_2_773088821.dbf thread=1 sequence=2
archive log filename=/usr/local/oracle/arch/1_3_773088821.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-JAN-12
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
RMAN>
10、检查数据恢复情况
在这种情况下，已经归档的数据是可以得到恢复的，对于已经提交的事务，但由于在线日志没有归档，且日志也丢失，所以这部分数据公丢失
[oracle@test dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:29:00 2012
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>
SQL>
SQL> select * from t_xu;
A
----------
1
2
3
SQL>
11、以上步骤只是一个实验，用以记录此场景的大致恢复步骤，在实际操作中会有所不同，难度也会比这大
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/593077/viewspace-716054/ ，如需转载，请注明出处，否则将追究法律责任。
