如何用Dummy实例执行数据库的还原和恢复

今天实验了一下，如何在所有文件，包括数据文件，在线日志文件，控制文件都丢失的情况下，利用RMAN备份恢复和还原数据库。该实验的重点是用到了Dummy实例。

具体步骤如下：

备份数据库

[oracle@node2 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:25:51 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: TEST (DBID=2176055307)RMAN> backup database;Starting backup at 25-MAY-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/test/users02.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/test/undotbs02.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbfchannel ORA_DISK_1: starting piece 1 at 25-MAY-15channel ORA_DISK_1: finished piece 1 at 25-MAY-15piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:01channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 25-MAY-15channel ORA_DISK_1: finished piece 1 at 25-MAY-15piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp tag=TAG20150525T232624 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:08Finished backup at 25-MAY-15RMAN> quit

模拟数据丢失

[oracle@node2 ~]$ cd /u01/app/oracle/oradata/test[oracle@node2 test]$ lscontrol01.ctl  redo01.log  redo03.log  sysaux01.dbf  temp01.dbf     undotbs02.dbf  users02.dbfcontrol01.dbf  redo02.log  redo04.log  system01.dbf  undotbs01.dbf  users01.dbf[oracle@node2 test]$ rm ./*

试着登录数据库查询数据，会报以下错误：

[oracle@node2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon May 25 23:35:49 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected.SQL> select * from scott.dept;select * from scott.dept*ERROR at line 1:ORA-01012: not logged onProcess ID: 0Session ID: 0 Serial number: 0

看看ORACLE相关进程，会发现实例并没有奔溃，进程还在

[oracle@node2 ~]$ ps -ef |grep oraroot      1362  1320  0 19:25 ?        00:00:08 hald-addon-storage: polling /dev/sr0 (every 2 sec)root      2809  1384  0 22:22 ?        00:00:00 sshd: oracle [priv]oracle    2811  2809  0 22:22 ?        00:00:01 sshd: oracle@pts/7oracle    2812  2811  0 22:22 pts/7    00:00:00 -bashroot      3006  1384  0 22:31 ?        00:00:00 sshd: oracle [priv]oracle    3008  3006  0 22:31 ?        00:00:00 sshd: oracle@pts/1oracle    3009  3008  0 22:31 pts/1    00:00:00 -bashroot      3120  1384  0 22:34 ?        00:00:00 sshd: oracle [priv]oracle    3122  3120  0 22:34 ?        00:00:00 sshd: oracle@pts/8oracle    3123  3122  0 22:34 pts/8    00:00:00 -bashroot      3482  1384  0 22:57 ?        00:00:00 sshd: oracle [priv]oracle    3484  3482  0 22:57 ?        00:00:01 sshd: oracle@pts/3oracle    3485  3484  0 22:57 pts/3    00:00:00 -bashroot      3566  1384  0 23:01 ?        00:00:00 sshd: oracle [priv]oracle    3568  3566  0 23:01 ?        00:00:00 sshd: oracle@pts/9oracle    3569  3568  0 23:01 pts/9    00:00:00 -bashoracle    4035     1  0 23:23 ?        00:00:00 ora_pmon_testoracle    4037     1  0 23:23 ?        00:00:00 ora_psp0_testoracle    4039     1  4 23:23 ?        00:00:32 ora_vktm_testoracle    4043     1  0 23:23 ?        00:00:00 ora_gen0_testoracle    4045     1  0 23:23 ?        00:00:00 ora_diag_testoracle    4047     1  0 23:23 ?        00:00:00 ora_dbrm_testoracle    4049     1  0 23:23 ?        00:00:00 ora_dia0_testoracle    4051     1  0 23:23 ?        00:00:00 ora_mman_testoracle    4053     1  0 23:23 ?        00:00:00 ora_dbw0_testoracle    4055     1  0 23:23 ?        00:00:00 ora_lgwr_testoracle    4057     1  0 23:23 ?        00:00:00 ora_ckpt_testoracle    4059     1  0 23:23 ?        00:00:00 ora_smon_testoracle    4061     1  0 23:23 ?        00:00:00 ora_reco_testoracle    4063     1  0 23:23 ?        00:00:02 ora_mmon_testoracle    4065     1  0 23:23 ?        00:00:00 ora_mmnl_testoracle    4067     1  0 23:23 ?        00:00:00 ora_d000_testoracle    4069     1  0 23:23 ?        00:00:00 ora_s000_testoracle    4081     1  0 23:23 ?        00:00:00 ora_arc0_testoracle    4083     1  0 23:23 ?        00:00:00 ora_arc1_testoracle    4085     1  0 23:23 ?        00:00:00 ora_arc2_testoracle    4087     1  0 23:23 ?        00:00:00 ora_arc3_testoracle    4089     1  0 23:23 ?        00:00:00 ora_qmnc_testoracle    4103     1  0 23:24 ?        00:00:01 ora_cjq0_testoracle    4105     1  0 23:24 ?        00:00:00 ora_q000_testoracle    4107     1  0 23:24 ?        00:00:00 ora_q001_testoracle    4109     1  0 23:24 ?        00:00:05 ora_vkrm_testoracle    4129     1  0 23:24 ?        00:00:00 ora_smco_testoracle    4131     1  0 23:24 ?        00:00:00 ora_w000_testoracle    4149     1  0 23:29 ?        00:00:00 ora_w001_testoracle    4151     1  0 23:29 ?        00:00:00 ora_w002_testoracle    4153     1  0 23:30 ?        00:00:00 ora_w003_testoracle    4178  3123  0 23:35 pts/8    00:00:00 sqlplus   as sysdbaoracle    4186  4178  0 23:36 ?        00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle    4187  3485  0 23:36 pts/3    00:00:00 ps -eforacle    4188  3485  0 23:36 pts/3    00:00:00 grep ora

关闭数据库

SQL> shutdown abortORACLE instance shut down.

现在开始恢复数据库，在这里会借助Oracle的dummy实例，即哑实例。

[oracle@node2 ~]$ export ORACLE_SID=dummy[oracle@node2 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:44:28 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database (not started)RMAN> startup nomountstartup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.1/db_1/dbs/initdummy.ora'starting Oracle instance without parameter file for retrieval of spfileOracle instance startedTotal System Global Area    1071333376 bytesFixed Size                     1369420 bytesVariable Size                281021108 bytesDatabase Buffers             784334848 bytesRedo Buffers                   4608000 bytesRMAN> restore spfile to '/u01/app/oracle/product/11.2.0.1/db_1/dbs/spfiletest.ora' from '/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp';Starting restore at 25-MAY-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=171 device type=DISKchannel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 25-MAY-15RMAN> shutdown immediateOracle instance shut down

在这里，我们将spfile恢复到实例默认的目录，即$ORACLE_HOME/dbs,采用的是默认值，即spfile实例名.ora。这样，在将数据库启动到nomount状态下，直接startup nomount即可。

恢复控制文件

[oracle@node2 ~]$ export ORACLE_SID=test[oracle@node2 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:54:50 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database (not started)RMAN> startup nomountOracle instance startedTotal System Global Area     313860096 bytesFixed Size                     1364340 bytesVariable Size                268439180 bytesDatabase Buffers              37748736 bytesRedo Buffers                   6307840 bytesRMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp';Starting restore at 26-MAY-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=136 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/oradata/test/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/test/control02.ctlFinished restore at 26-MAY-15

注意，要重新设置ORACLE_SID的值，不然启动的依旧是dummy实例。

将数据库启动到mount阶段

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

还原和恢复数据库

RMAN> restore database;Starting restore at 26-MAY-15Starting implicit crosscheck backup at 26-MAY-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=136 device type=DISKCrosschecked 4 objectsFinished implicit crosscheck backup at 26-MAY-15Starting implicit crosscheck copy at 26-MAY-15using channel ORA_DISK_1Finished implicit crosscheck copy at 26-MAY-15searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkpusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/users02.dbfchannel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/test/undotbs02.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:02:06Finished restore at 26-MAY-15RMAN> recover database;Starting recover at 26-MAY-15using channel ORA_DISK_1starting media recoveryunable to find archived logarchived log thread=1 sequence=1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/26/2015 00:19:20RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 956515RMAN> alter database open resetlogs;database opened

至此，数据库恢复完毕，该恢复属于不完全恢复，毕竟，在线日志文件在“模拟数据丢失”那一步已被删除，完全恢复已不可能，同时，上述倒数第二步recover database提示所需的归档文件不存在，指的也是已被删除的在线日志文件。在这里，直接resetlogs即可。

总结：

1> 如果启用了RMAN的自动备份功能configure autobackup on，在恢复spfile和controlfile那一步可直接执行restore spfile from autobackup和restore controlfile from autobackup，不用上述那么麻烦，需要指定备份集。当然，在这种情况下，需指定dbid。

2> OCP官方教材里面其实也提供了在一切都丢失的情况下，如何执行数据库的还原和恢复的脚本，具体如下：

RMAN> run{startup nomount pfile=dummy.pfile;2> set dbid=2176055307;3> restore spfile from autobackup;4> shutdown abort;5> startup nomount;6> restore controlfile from autobackup;7> alter database mount;8> restore database;9> recover database;10> alter database open resetlog;}

关于这个脚本，说明如下：

第1行，使用只有一个参数（DB_NAME）的哑参数文件启动实例

第2行，告诉RMAN所使用的数据库的DBID

第3行，根据默认目录和默认值，从最近的自动备份集中提取spfile

第4行，关闭哑实例

第5行，利用还原的spfile启动实例

第6行，从最近的备份集中提取控制文件

第7行，加载控制文件

第8行，还原所有数据文件

第9行，通过应用增量备份以及归档日志文件和联机日志文件执行完整恢复。

第10行，打开数据库并重新初始化在线重做日志文件，在还原控制文件后总是需要resetlogs。