如何用Dummy实例执行数据库的还原和恢复
今天实验了一下,如何在所有文件,包括数据文件,在线日志文件,控制文件都丢失的情况下,利用RMAN备份恢复和还原数据库。该实验的重点是用到了Dummy实例。
具体步骤如下:
备份数据库
[oracle@node2 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 25 23:25:51 2015 Copyright (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-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/test/users02.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/test/undotbs02.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf channel ORA_DISK_1: starting piece 1 at 25-MAY-15 channel ORA_DISK_1: finished piece 1 at 25-MAY-15 piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:01 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 25-MAY-15 channel ORA_DISK_1: finished piece 1 at 25-MAY-15 piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp tag=TAG20150525T232624 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08 Finished backup at 25-MAY-15 RMAN> quit
模拟数据丢失
[oracle@node2 ~]$ cd /u01/app/oracle/oradata/test [oracle@node2 test]$ ls control01.ctl redo01.log redo03.log sysaux01.dbf temp01.dbf undotbs02.dbf users02.dbf control01.dbf redo02.log redo04.log system01.dbf undotbs01.dbf users01.dbf [oracle@node2 test]$ rm ./*
试着登录数据库查询数据,会报以下错误:
[oracle@node2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon May 25 23:35:49 2015 Copyright (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 on Process ID: 0 Session ID: 0 Serial number: 0
看看ORACLE相关进程,会发现实例并没有奔溃,进程还在
[oracle@node2 ~]$ ps -ef |grep ora root 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/7 oracle 2812 2811 0 22:22 pts/7 00:00:00 -bash root 3006 1384 0 22:31 ? 00:00:00 sshd: oracle [priv] oracle 3008 3006 0 22:31 ? 00:00:00 sshd: oracle@pts/1 oracle 3009 3008 0 22:31 pts/1 00:00:00 -bash root 3120 1384 0 22:34 ? 00:00:00 sshd: oracle [priv] oracle 3122 3120 0 22:34 ? 00:00:00 sshd: oracle@pts/8 oracle 3123 3122 0 22:34 pts/8 00:00:00 -bash root 3482 1384 0 22:57 ? 00:00:00 sshd: oracle [priv] oracle 3484 3482 0 22:57 ? 00:00:01 sshd: oracle@pts/3 oracle 3485 3484 0 22:57 pts/3 00:00:00 -bash root 3566 1384 0 23:01 ? 00:00:00 sshd: oracle [priv] oracle 3568 3566 0 23:01 ? 00:00:00 sshd: oracle@pts/9 oracle 3569 3568 0 23:01 pts/9 00:00:00 -bash oracle 4035 1 0 23:23 ? 00:00:00 ora_pmon_test oracle 4037 1 0 23:23 ? 00:00:00 ora_psp0_test oracle 4039 1 4 23:23 ? 00:00:32 ora_vktm_test oracle 4043 1 0 23:23 ? 00:00:00 ora_gen0_test oracle 4045 1 0 23:23 ? 00:00:00 ora_diag_test oracle 4047 1 0 23:23 ? 00:00:00 ora_dbrm_test oracle 4049 1 0 23:23 ? 00:00:00 ora_dia0_test oracle 4051 1 0 23:23 ? 00:00:00 ora_mman_test oracle 4053 1 0 23:23 ? 00:00:00 ora_dbw0_test oracle 4055 1 0 23:23 ? 00:00:00 ora_lgwr_test oracle 4057 1 0 23:23 ? 00:00:00 ora_ckpt_test oracle 4059 1 0 23:23 ? 00:00:00 ora_smon_test oracle 4061 1 0 23:23 ? 00:00:00 ora_reco_test oracle 4063 1 0 23:23 ? 00:00:02 ora_mmon_test oracle 4065 1 0 23:23 ? 00:00:00 ora_mmnl_test oracle 4067 1 0 23:23 ? 00:00:00 ora_d000_test oracle 4069 1 0 23:23 ? 00:00:00 ora_s000_test oracle 4081 1 0 23:23 ? 00:00:00 ora_arc0_test oracle 4083 1 0 23:23 ? 00:00:00 ora_arc1_test oracle 4085 1 0 23:23 ? 00:00:00 ora_arc2_test oracle 4087 1 0 23:23 ? 00:00:00 ora_arc3_test oracle 4089 1 0 23:23 ? 00:00:00 ora_qmnc_test oracle 4103 1 0 23:24 ? 00:00:01 ora_cjq0_test oracle 4105 1 0 23:24 ? 00:00:00 ora_q000_test oracle 4107 1 0 23:24 ? 00:00:00 ora_q001_test oracle 4109 1 0 23:24 ? 00:00:05 ora_vkrm_test oracle 4129 1 0 23:24 ? 00:00:00 ora_smco_test oracle 4131 1 0 23:24 ? 00:00:00 ora_w000_test oracle 4149 1 0 23:29 ? 00:00:00 ora_w001_test oracle 4151 1 0 23:29 ? 00:00:00 ora_w002_test oracle 4153 1 0 23:30 ? 00:00:00 ora_w003_test oracle 4178 3123 0 23:35 pts/8 00:00:00 sqlplus as sysdba oracle 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 -ef oracle 4188 3485 0 23:36 pts/3 00:00:00 grep ora
关闭数据库
SQL> shutdown abort ORACLE 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 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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 '/u01/app/oracle/product/11.2.0.1/db_1/dbs/initdummy.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1071333376 bytes Fixed Size 1369420 bytes Variable Size 281021108 bytes Database Buffers 784334848 bytes Redo Buffers 4608000 bytes RMAN> 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-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=171 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 25-MAY-15 RMAN> shutdown immediate Oracle 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 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount Oracle instance started Total System Global Area 313860096 bytes Fixed Size 1364340 bytes Variable Size 268439180 bytes Database Buffers 37748736 bytes Redo Buffers 6307840 bytes RMAN> 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-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=136 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/test/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/test/control02.ctl Finished restore at 26-MAY-15
注意,要重新设置ORACLE_SID的值,不然启动的依旧是dummy实例。
将数据库启动到mount阶段
RMAN> alter database mount; database mounted released channel: ORA_DISK_1
还原和恢复数据库
RMAN> restore database; Starting restore at 26-MAY-15 Starting implicit crosscheck backup at 26-MAY-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=136 device type=DISK Crosschecked 4 objects Finished implicit crosscheck backup at 26-MAY-15 Starting implicit crosscheck copy at 26-MAY-15 using channel ORA_DISK_1 Finished implicit crosscheck copy at 26-MAY-15 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_ncsnf_TAG20150525T232624_bp6hx32w_.bkp using channel ORA_DISK_1 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 00001 to /u01/app/oracle/oradata/test/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/users02.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/test/undotbs02.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2015_05_25/o1_mf_nnndf_TAG20150525T232624_bp6hs0oo_.bkp tag=TAG20150525T232624 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:06 Finished restore at 26-MAY-15 RMAN> recover database; Starting recover at 26-MAY-15 using channel ORA_DISK_1 starting media recovery unable to find archived log archived log thread=1 sequence=1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/26/2015 00:19:20 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 956515 RMAN> 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。
相关文章
- 多云架构下,JAVA微服务技术选型实例解析
- 专家解读:利用Angular项目与数据库融合实例
- [ Openstack ] Openstack-Mitaka 高可用之 启动一个实例
- 达梦删除数据库实例(非软件)
- TableView_编辑 实例代码
- Oracle 数据库实例简介
- Oracle11g 启动数据库实例、关闭数据库实例
- 查询oracle数据库的数据库名、实例名、ORACLE_SID
- Python使用MySQL数据库的方法以及一个实例
- DBLink实现备份文件不落盘的导入其他Oracle数据库实例的方法
- Oracle 查看一个数据库实例下面所有的表大小
- Vue 简单实例 购物车3 - 删除商品
- jQuery对html元素的取值与赋值实例详解
- URLClassLoader使用方法和实例
- qt连接mysql数据库实例
- qt5连接sqlite数据库实例
- 【RAC】将RAC备份集恢复为单实例数据库
- 应用DriverManager类创建sqlserver数据库连接实例 JSP中使用数据库
- 本地登录多实例mysql ,默认登录数据库问题
- Cocos2d-x游戏开发实例详解3:无限滚动地图
- 查看Oracle数据库名和实例名的命令
- 014_swift_实例的引用特征和内存管理
- 大数据报异常---查日志实例
- 通过实例模拟ASP.NET MVC的Model绑定机制:数组
- 如何解决分布式系统中的跨时区问题[实例篇]
- 阿里云发布ECS企业级产品家族 19款实例族涵盖173个应用场景
- 【ORA-16196】一个实例在其生命周期里最多只能装载和打开一个数据库
- javascript入门系列演示·三种弹出对话框的用法实例
- Qt应用程序单实例化
- android之Fragment静态实现实例
- 大数据ClickHouse(五):数据库引擎介绍与实例演示
- [ Shell入门教程 ] Shell编程中数值计算方法实例