探索ORACLE之RMAN07单个数据文件丢失恢复 电脑资料

探索ORACLE之RMAN07单个数据文件丢失恢复 电脑资料 相关链接： 探索ORACLE之RMAN_01概念 /database/xx05/132243.html； 探索ORACLE之RMAN_02基本使用 /database/xx05/132329.html； 探索ORACLE之RMAN_02一致性备份 /database/xx05/132664.html； 探索ORACLE之RMAN_03非一致性备份 /database/xx05/133139.html； 探索ORACLE之RMAN_04非一致性备份 /database/xx05/133374.html； 探索ORACLE之RMAN_05增量备份 /database/xx05/133559.html； 探索ORACLE之RMAN_06备份策略 /database/xx06/136832.html 备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了RMAN的备份，实际上也穿插的谈了些复杂的完整恢复， 1、 数据文件的丢失恢复 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 TABLE WWL02 TABLE WWL03 TABLE WWL04 TABLE WWL05 TABLE 1.2 执行全库备份 oraclewwldb $ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 2200:59:59 xx Copyright (c) 1982, xx, Oracle. All rights reserved. connected to target database: WWL (DBID=55xx9) RMAN backup database; 1.3 模拟数据丢失，手动删除数据文件wwl001.dbf oraclewwldb WWL$ rm -rfwwl001.dbf 1.4 再次启动数据库，无法启动并报错不能锁定数据文件5，查看dbwr的跟踪文件。 SQLstartup force; ORACLEinstance started. TotalSystem Global Area 285212672 bytes FixedSize 1218968 bytes VariableSize 92276328 bytes DatabaseBuffers 184549376 bytes RedoBuffers 7168000 bytes Databasemounted. ORA-01157:cannot identify/lock data file 5 - see DBWR trace file ORA-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=ORA Linux Error: 2: No such file or directory Additional information: 3 ORA-1157 signalled during: ALTER DATABASEOPEN. 由以上信息可以得出数据库故障是由于数据文件wwl001.dbf数据文件异常丢失或者损坏导致数据库的故障，那么数据文件丢失就必然存在数据库数据的丢失，但是万幸的是，在丢失之前我们已经做过备份了，现在我们就来通过之前的备份将wwl001.dbf文件恢复回来，数据文件的恢复分为两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下： 1.6 恢复方法一，零停机，在线恢复 开始执行恢复操作，分为如下七个步骤： 1、强制将数据库启动到mount状态 RMANstartup force mount; Oracle instancestarted databasemounted Total System GlobalArea 285212672 bytes Fixed Size 1218968 bytes VariableSize 92276328 bytes DatabaseBuffers 184549376 bytes RedoBuffers 7168000 bytes 2、将对应的数据文件offline SQL 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-12 using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=141devtype=DISK channel ORA_DISK_1: startingdatafile backupset restore channel ORA_DISK_1: specifyingdatafile(s) to restore from backup set restoring datafile 00005 to /DBData/WWL/wwl001.dbf channel ORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1 channel ORA_DISK_1: restored backuppiece 1 piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAGxx0622T010021 channel ORA_DISK_1: restoreplete, elapsed time: 00:00:07 Finished restore at 22-JUN-12 查看到数据文件已经restore出来了， oraclewwldb 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、执行数据恢复，保证s一致 RMANrecover datafile /DBData/WWL/wwl001.dbf; Startingrecover at 22-JUN-12 using targetdatabase control file instead of recovery catalog allocatedchannel: ORA_DISK_1 channelORA_DISK_1: sid=144 devtype=DISK starting mediarecovery media recoveryplete, elapsed time: 00:00:01 Finishedrecover at 22-JUN-12 RMAN 6、将数据文件在线 SQL conn / as sysdba Connected. SQL alter database datafile/DBData/WWL/wwl001.dbf online; Database altered. 7、验证数据是否恢复，可以看到数据全部恢复回来了。 SQLconn wwl/wwl Connected. SQLselect * from tab; TNAME TABTYPE CLUSTERID - - WWL01 TABLE WWL02 TABLE WWL03 TABLE WWL04 TABLE WWL05 TABLE SQLselect count(*) from tab; COUNT(*) - 5 SQL 1.7 恢复方法二，离线恢复 开始执行恢复操作，分为五个步骤： 1、强制将数据库启动到mount状态 SQL startupforce mount; ORACLE instancestarted. Total SystemGlobal Area 285212672 bytes Fixed Size 1218968 bytes VariableSize 96470632 bytes DatabaseBuffers 180355072 bytes RedoBuffers 7168000 bytes Databasemounted. 2、将数据文件从备份中restore出来 RMAN restoredatafile /DBData/WWL/wwl002.dbf 2 ; Starting restoreat 22-JUN-12 using targetdatabase control file instead of recovery catalog allocatedchannel: ORA_DISK_1 channelORA_DISK_1: sid=156 devtype=DISK channelORA_DISK_1: starting datafile backupset restore channelORA_DISK_1: specifying datafile(s) to restore from backup set restoringdatafile 00006 to /DBData/WWL/wwl002.dbf channelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1 channelORA_DISK_1: restored backup piece 1 piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAGxx0622T010021 channelORA_DISK_1: restore plete, elapsed time: 00:00:01 Finished restoreat 22-JUN-12 3、执行数据恢复，保证s一致 RMAN recover datafile /DBData/WWL/wwl002.dbf; Starting recover at 22-JUN-12 using channel ORA_DISK_1 starting media recovery media recovery plete, elapsed time: 00:00:02 F