Welcome 微信登录

首页 / 数据库 / MySQL / 探索Oracle之RMAN_07单个数据文件丢失恢复

备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了RMAN的备份，实际上也穿插的谈了些复杂的完整恢复。当然在这节当中我们将会由浅入深的详细谈谈在几种不同情况下的数据库恢复。 相关阅读：探索Oracle之RMAN_01概念 http://www.linuxidc.com/Linux/2012-05/60530.htm探索Oracle之RMAN_02基本使用 http://www.linuxidc.com/Linux/2012-05/60578.htm探索Oracle之RMAN_03非一致性备份 http://www.linuxidc.com/Linux/2012-05/61025.htm探索Oracle之RMAN_04非一致性备份 http://www.linuxidc.com/Linux/2012-05/61180.htm探索Oracle之RMAN_05增量备份 http://www.linuxidc.com/Linux/2012-05/61181.htm探索Oracle之RMAN_06备份策略 http://www.linuxidc.com/Linux/2012-05/61450.htm探索Oracle之RMAN_07单个数据文件丢失恢复 http://www.linuxidc.com/Linux/2012-06/63524.htm探索Oracle之RMAN_07整个业务表空间丢失恢复 http://www.linuxidc.com/Linux/2012-07/64582.htm探索Oracle之RMAN_07 磁盘损坏数据丢失恢复 http://www.linuxidc.com/Linux/2012-07/64588.htm探索Oracle之RMAN_07 数据库所有文件全部丢失恢复 http://www.linuxidc.com/Linux/2012-07/64587.htm探索Oracle之RMAN_07 重做日志redu文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64586.htm探索Oracle之RMAN_07 参数文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64585.htm探索Oracle之RMAN_07控制文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64584.htm探索Oracle之RMAN_07 system表空间丢失恢复 http://www.linuxidc.com/Linux/2012-07/64583.htm1、     数据文件的丢失恢复1.1    在wwl表空间上创建5张表，并添加数据。SQL> create table wwl01 （id number（3）,namevarchar2（10））;Table created. SQL> insert into wwl01 values（1,"wwl"）;1 row created. SQL> insert into wwl01 values（2,"wm"）;1 row created. SQL> insert into wwl01 values（3,"zq"）;1 row created. SQL> insert into wwl01 values（4,"wbq"）;1 row created. SQL> insert into wwl01 values（5,"wq"）;1 row created. SQL> create table wwl02 as select * from wwl01;Table created. SQL> create table wwl03 as select * from wwl01;Table created. SQL> create table wwl04 as select * from wwl01;Table created. SQL> create table wwl05 as select * from wwl01;Table created. 查看表中的数据：SQL> select * from tab; TNAME     TABTYPE  CLUSTERID---------- ------- ----------WWL01      TABLEWWL02      TABLEWWL03      TABLEWWL04      TABLEWWL05      TABLE1.2    执行全库备份[oracle@wwldb ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 2200:59:59 2012Copyright （c） 1982, 2005, Oracle. All rights reserved. connected to target database: WWL （DBID=5520179） RMAN> backup database; 1.3    模拟数据丢失，手动删除数据文件wwl001.dbf[oracle@wwldb WWL]$ rm -rfwwl001.dbf 1.4    再次启动数据库，无法启动并报错不能锁定数据文件5，查看dbwr的跟踪文件。SQL>startup force;ORACLEinstance started. TotalSystem Global Area  285212672 bytesFixedSize                  1218968 bytesVariableSize              92276328 bytesDatabaseBuffers          184549376 bytesRedoBuffers                7168000 bytesDatabasemounted.ORA-01157:cannot identify/lock data file 5 - see DBWR trace fileORA-01110: data file 5:"/DBData/WWL/wwl001.dbf" 1.5    检查跟踪文件，报如下错误，非常的清楚的告诉了找不到的文件：Errors in file /DBSoft/admin/WWL/bdump/wwl_dbw0_29185.trc:ORA-01157: Message 1157 not found; No messagefile for product=RDBMS, facility=ORA; arguments: [5]ORA-01110: Message 1110 not found; No messagefile for product=RDBMS, facility=ORA; arguments: [5] [/DBData/WWL/wwl001.dbf]ORA-27037: Message 27037 not found; No messagefile for product=RDBMS, facility=ORALinux Error: 2: No such file or directoryAdditional information: 3ORA-1157 signalled during: ALTER DATABASEOPEN...由以上信息可以得出数据库故障是由于数据文件wwl001.dbf数据文件异常丢失或者损坏导致数据库的故障，那么数据文件丢失就必然存在数据库数据的丢失，但是万幸的是，在丢失之前我们已经做过备份了，现在我们就来通过之前的备份将wwl001.dbf文件恢复回来，数据文件的恢复分为两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下： 1.6    恢复方法一，零停机，在线恢复开始执行恢复操作，分为如下七个步骤：1、强制将数据库启动到mount状态RMAN>startup force mount;Oracle instancestarteddatabasemountedTotal System GlobalArea     285212672 bytesFixed Size                     1218968 bytesVariableSize                 92276328 bytesDatabaseBuffers             184549376 bytesRedoBuffers                   7168000 bytes 2、将对应的数据文件offlineSQL> alter database datafile "/DBData/WWL/wwl001.dbf" offline;Database altered. 3、开启数据库SQL> alter database open;Database altered. 4、将数据文件从备份中restore出来RMAN> restore datafile"/DBData/WWL/wwl001.dbf";Starting restore at 22-JUN-12using target database control fileinstead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=141devtype=DISKchannel ORA_DISK_1: startingdatafile backupset restorechannel ORA_DISK_1: specifyingdatafile（s） to restore from backup setrestoring datafile 00005 to /DBData/WWL/wwl001.dbfchannel ORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1channel ORA_DISK_1: restored backuppiece 1piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021channel ORA_DISK_1: restorecomplete, elapsed time: 00:00:07Finished restore at 22-JUN-12 查看到数据文件已经restore出来了。[oracle@wwldb WWL]$ ll wwl*-rw-r----- 1 oracle oinstall52436992 Jun 22 01:21 wwl001.dbf-rw-r----- 1 oracle oinstall  5251072 Jun 22 01:20 wwl002.dbf-rw-r----- 1 oracle oinstall  5251072 Jun 22 01:20 wwl003.dbf 5、执行数据恢复，保证scn一致RMAN>recover datafile "/DBData/WWL/wwl001.dbf";Startingrecover at 22-JUN-12using targetdatabase control file instead of recovery catalogallocatedchannel: ORA_DISK_1channelORA_DISK_1: sid=144 devtype=DISKstarting mediarecoverymedia recoverycomplete, elapsed time: 00:00:01Finishedrecover at 22-JUN-12 RMAN> 6、将数据文件在线SQL> conn / as sysdbaConnected.SQL> alter database datafile"/DBData/WWL/wwl001.dbf" online; Database altered. 7、验证数据是否恢复，可以看到数据全部恢复回来了。SQL>conn wwl/wwlConnected.SQL>select * from tab; TNAME                          TABTYPE  CLUSTERID------------------------------------- ----------WWL01                          TABLEWWL02                          TABLEWWL03                          TABLEWWL04                          TABLEWWL05                          TABLE SQL>select count（*） from tab;   COUNT（*）----------         5 SQL> 1.7    恢复方法二，离线恢复开始执行恢复操作，分为五个步骤：1、强制将数据库启动到mount状态SQL> startupforce mount;ORACLE instancestarted.Total SystemGlobal Area  285212672 bytesFixed Size                  1218968 bytesVariableSize              96470632 bytesDatabaseBuffers          180355072 bytesRedoBuffers                7168000 bytesDatabasemounted. 2、将数据文件从备份中restore出来RMAN> restoredatafile "/DBData/WWL/wwl002.dbf"2> ;Starting restoreat 22-JUN-12using targetdatabase control file instead of recovery catalogallocatedchannel: ORA_DISK_1channelORA_DISK_1: sid=156 devtype=DISK channelORA_DISK_1: starting datafile backupset restorechannelORA_DISK_1: specifying datafile（s） to restore from backup setrestoringdatafile 00006 to /DBData/WWL/wwl002.dbfchannelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1channelORA_DISK_1: restored backup piece 1piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021channelORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restoreat 22-JUN-12 3、执行数据恢复，保证scn一致RMAN> recover datafile "/DBData/WWL/wwl002.dbf";Starting recover at 22-JUN-12using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:02Finished recover at 22-JUN-12RMAN> 4、开启数据库SQL> conn /as sysdbaConnected.SQL> alterdatabase open;Databasealtered. 5、验证数据是否恢复SQL> select *from tab;TNAME                          TABTYPE  CLUSTERID------------------------------------- ----------WWL01                          TABLEWWL02                          TABLEWWL03                          TABLEWWL04                          TABLEWWL05                          TABLE SQL> selectcount（*） from wwl01;  COUNT（*）----------         5SQL>
  • 1
  • 2
  • 3
  • 4
  • 下一页
MySQL数据库安装统一数据库集群相关资讯      Oracle高级培训 
  • delete表的数据后恢复  （08/30/2012 08:59:58）
  • 使用ASH信息，发现高CPUsession  （08/14/2012 07:21:32）
  • 如何阅读Oracle Errorstack Output  （08/14/2012 07:15:47）
  • Oracle Apps Patching:adpatch（  （08/16/2012 15:41:37）
  • 话说V$SQL_MONITOR  （08/14/2012 07:19:54）
  • Oracle Apps DBA工具:ADADMIN使用  （08/14/2012 07:00:09）
本文评论 查看全部评论 （0）
表情： 姓名： 字数