你的位置:首页 > 数据库

[数据库]如何用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

至此,数据库恢复完毕,从上面的最后一个命令alter database open resetlogs可以看出来,该恢复属于不完全恢复,毕竟,在线日志文件在“模拟数据丢失”那一步已被删除,完全恢复已不可能,同时,上述倒数第二步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。