探索ORACLE之RMAN_07非一致性恢复(集合)
探索ORACLE之RMAN_07非一致性恢复(集合)
作者：吴伟龙 Name：Prodence Woo
QQ：286507175 msn:hapy-wuweilong@hotmail.com
在前面的议题中实际上已经对rman的备份恢复做了个比较完整的介绍，谈到了数据库，表空间，参数文件，控制文件的备份，同时也谈到了它们的恢复，当然还包括了灾难性的恢复。对于那些恢复实际上已经用到了我们今天所要说的非一致性恢复，只不过没有说明罢了。什么叫非一致性恢复，非一致性恢复就是在恢复的过程中会有部分数据丢失。
那么非一致性恢复主要应用于在控制文件，重做日志文件，或者归档日志文件失败，数据库的完全介质恢复将无法进行的时候，为使损失达到最小，这个时候才可执行数据库的不一致性恢复。
我们可以通过如下图来理解什么是非一致性恢复：
如图上中，数据库在100的时候已经做了全备，重做日志已经捕获了100-500之间的SCN，并同归redo应用归档，当在500的时候，数据库发生了介质失败；那么这个时候要做数据库的恢复就要用到在scn 100时做的备份以及100-500这期间所产生的归档和重做日志信息。如果说归档在scn 300的时候损坏了，那么这个时候就只能恢复到scn为200的这个点，这也就是我们说的数据库的不完全恢复.
非一致性恢复的类型主要分为以下四种：
基于时间(time)恢复
基于取消(cancel)恢复
基于SCN(change)恢复
基于备份控制文件(unsing backup controlfile)的恢复
基于时间(time)恢复
基于时间的恢复将数据库恢复到备份点与失败点之间的某个时间点。基于时间的恢复不仅在介质失败的时候使用，也可以在数据库正常运行的时候使用。例如：某个用户误删除了某个表的数据，这个时候我们可以通过基于时间的恢复来将删除的数据恢复出来，示例如下：
1、查看当前用户下的表，只有一张WWL001
21:07:31 SQL> select * fromtab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
WWL001 TABLE
Elapsed: 00:00:00.06
我们通过WWL001来创建WWL002-WWL005 共四张表用来测试不完全恢复
21:08:28 SQL> create tablewwl002 as select * from wwl001;
Table created.
Elapsed: 00:00:00.17
21:08:55 SQL> create tablewwl003 as select * from wwl001;
Table created.
Elapsed: 00:00:00.04
21:09:00 SQL> create tablewwl004 as select * from wwl001;
Table created.
Elapsed: 00:00:00.03
21:09:06 SQL> create tablewwl005 as select * from wwl001;
Table created.
Elapsed: 00:00:00.05
如上表已经创建完成
2、我们在21:13:13开始删除表(请注意时间)
21:13:13 SQL> drop tablewwl002 purge;
Table dropped.
Elapsed: 00:00:00.16
21:13:28 SQL> drop tablewwl003 purge;
Table dropped.
Elapsed: 00:00:00.11
21:13:34 SQL> truncate tablewwl004;
Table truncated.
Elapsed: 00:00:00.32
21:13:44 SQL> truncate tablewwl005;
Table truncated.
Elapsed: 00:00:00.07
21:13:47 SQL>
因为我们是要做基于时间的恢复，那么我们只有将数据库恢复到21:13:13之前的这个时间段，才能把我们刚才创建的表找回来。
3、开始执行恢复，先将数据库启动到mount状态
21:14:40 SQL> conn / assysdba
Connected.
21:14:44 SQL> shutdownimmediate
Database closed.
Database dismounted.
ORACLE instance shut down.
21:15:20 SQL> startup mount;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
21:15:46 SQL> e
4、开始执行restore到备份数据库的当前状态：
RMAN> restore database;
Starting restore at 12-JUL-12
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47devtype=DISK
channel ORA_DISK_1: startingdatafile backupset restore
channel ORA_DISK_1: specifyingdatafile(s) to restore from backup set
restoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbf
restoring datafile 00002 to/DBBak2/oradata/WWL/undotbs01.dbf
restoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbf
restoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbf
restoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbf
restoring datafile 00006 to/DBBak2/oradata/WWL/wwl02.dbf
restoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbf
channel ORA_DISK_1: readingfrom backup piece /DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1
channel ORA_DISK_1: restoredbackup piece 1
piecehandle=/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1 tag=TAG20120712T095437
channel ORA_DISK_1: restorecomplete, elapsed time: 00:01:35
Finished restore at 12-JUL-12
RMAN> exit
5、执行基于时间点的恢复：
21:27:54 SQL> recover database until time ‘YYYY-mm-ddhh24:mi:ss’
21:27:54 SQL> recoverdatabase until time '2012-07-12 21:10:00';
ORA-00279: change 1436429generated at 07/12/2012 09:54:38 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf
ORA-00280: change 1436429 forthread 1 is in sequence #3
21:30:09 Specify log:{=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1440657generated at 07/12/2012 14:00:52 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788450452.dbf
ORA-00280: change 1440657 forthread 1 is in sequence #1
ORA-00279: change 1440855generated at 07/12/2012 15:08:58 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.dbf
ORA-00280: change 1440855 forthread 1 is in sequence #1
ORA-00279: change 1441316generated at 07/12/2012 15:19:50 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788455190.dbf
ORA-00280: change 1441316 forthread 1 is in sequence #1
ORA-00279: change 1442275generated at 07/12/2012 15:52:01 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788457121.dbf
ORA-00280: change 1442275 forthread 1 is in sequence #1
ORA-00279: change 1442953generated at 07/12/2012 16:25:06 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf
ORA-00280: change 1442953 forthread 1 is in sequence #1
ORA-00279: change 1462958generated at 07/12/2012 16:28:16 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbf
ORA-00280: change 1462958 forthread 1 is in sequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf' no longer needed forthis recovery
ORA-00279: change 1462963generated at 07/12/2012 17:17:59 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf
ORA-00280: change 1462963 forthread 1 is in sequence #1
ORA-00279: change 1483784generated at 07/12/2012 17:54:25 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbf
ORA-00280: change 1483784 forthread 1 is in sequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf' no longer needed forthis recovery
Log applied.
Media recovery complete.
21:30:29 SQL>
至此已经恢复完成。
6、因为做了不完全恢复，必须要做restlogs打开数据库。
21:30:29 SQL> alter databaseopen restlogs;
alter database open restlogs
7、数据已经恢复完成，数据都回来了。
21:34:04 SQL> select * fromwwl002;
ID NAME
-------------------------------------------------------
1 wwl
2 prodence
3 woo
4 xgx
5 cms
Elapsed: 00:00:00.01
21:34:16 SQL> select * fromwwl003;
ID NAME
-------------------------------------------------------
1 wwl
2 prodence
3 woo
4 xgx
5 cms
Elapsed: 00:00:00.00
21:34:18 SQL> select * fromwwl004;
ID NAME
---------- ---------------------------------------------
1 wwl
2 prodence
3 woo
4 xgx
5 cms
Elapsed: 00:00:00.01
21:34:20 SQL> select * fromwwl005;
ID NAME
-------------------------------------------------------
1 wwl
2 prodence
3 woo
4 xgx
5 cms
Elapsed: 00:00:00.01
21:34:22 SQL>
至此恢复已经完成。
基于SCN的恢复 第一篇
1、在删除数据之前，察看下SCN号是多少：
SQL> col name format a45
SQL> set line 300
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
---------------------------
/DBBak2/oradata/WWL/system01.dbf 1487389
/DBBak2/oradata/WWL/undotbs01.dbf 1487389
/DBBak2/oradata/WWL/sysaux01.dbf 1487389
/DBBak2/oradata/WWL/users01.dbf 1487389
/DBBak2/oradata/WWL/wwl01.dbf 1487389
/DBBak2/oradata/WWL/wwl02.dbf 1487389
/DBBak2/oradata/WWL/wwl03.dbf 1487389
7 rows selected.
SQL> select file#,checkpoint_change# fromv$datafile;
FILE#CHECKPOINT_CHANGE#
---------- ------------------
1 1487389
2 1487389
3 1487389
4 1487389
5 1487389
6 1487389
7 1487389
7 rows selected.
SQL>
2、删除测试用表：
SQL> drop table wwl002 purge;
Table dropped.
SQL> drop table wwl003 purge;
Table dropped.
SQL> drop table wwl004 purge;
Table dropped.
SQL> drop table wwl005 purge;
Table dropped.
3、开始做基于SCN的恢复：
SQL> recover database until change 1487389;
ORA-00279: change 1436429 generated at 07/12/201209:54:38 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf
ORA-00280: change 1436429 for thread 1 is insequence #3
Specify log: {=suggested | filename |AUTO | CANCEL}
auto
ORA-00279: change 1440657 generated at 07/12/201214:00:52 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788450452.dbf
ORA-00280: change 1440657 for thread 1 is insequence #1
ORA-00279: change 1440855 generated at 07/12/201215:08:58 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.dbf
ORA-00280: change 1440855 for thread 1 is insequence #1
ORA-00279: change 1441316 generated at 07/12/201215:19:50 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788455190.dbf
ORA-00280: change 1441316 for thread 1 is insequence #1
ORA-00279: change 1442275 generated at 07/12/201215:52:01 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788457121.dbf
ORA-00280: change 1442275 for thread 1 is insequence #1
ORA-00279: change 1442953 generated at 07/12/201216:25:06 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf
ORA-00280: change 1442953 for thread 1 is insequence #1
ORA-00279: change 1462958 generated at 07/12/201216:28:16 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbf
ORA-00280: change 1462958 for thread 1 is insequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf' no longer needed forthis recovery
ORA-00279: change 1462963 generated at 07/12/201217:17:59 needed for thread 1
ORA-00289: suggestion : /DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf
ORA-00280: change 1462963 for thread 1 is insequence #1
ORA-00279: change 1483784 generated at 07/12/201217:54:25 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbf
ORA-00280: change 1483784 for thread 1 is insequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf' no longer needed forthis recovery
ORA-00279: change 1486119 generated at 07/12/201220:35:27 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788474127.dbf
ORA-00280: change 1486119 for thread 1 is insequence #1
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS optionfor database open
SQL> alter database open resetlogs;
Database altered.
SQL>
4、至此，数据已经恢复完成：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
WWL001 TABLE
WWL002 TABLE
WWL003 TABLE
WWL004 TABLE
WWL005 TABLE
SQL> select * from wwl005;
IDNAME
---------- ---------------------------------------------
1wwl
2prodence
3woo
4xgx
5cms
SQL>
基于SCN的恢复，第二篇
1、察看当前的SCN，以便于后期数据丢失用来恢复：
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1511297
SQL> select file#,checkpoint_change# fromv$datafile;
FILE#CHECKPOINT_CHANGE#
---------- ------------------
1 1510535
2 1510535
3 1510535
4 1510535
5 1510535
6 1510535
7 1510535
7 rows selected.
SQL> col name format a45
SQL> setline 300
SQL> select name,checkpoint_change# fromv$datafile_header;
NAME CHECKPOINT_CHANGE#
---------------------------
/DBBak2/oradata/WWL/system01.dbf 1510535
/DBBak2/oradata/WWL/undotbs01.dbf 1510535
/DBBak2/oradata/WWL/sysaux01.dbf 1510535
/DBBak2/oradata/WWL/users01.dbf 1510535
/DBBak2/oradata/WWL/wwl01.dbf 1510535
/DBBak2/oradata/WWL/wwl02.dbf 1510535
/DBBak2/oradata/WWL/wwl03.dbf 1510535
7 rows selected.
SQL>
2、删除测试表
SQL> drop table wwl002 purge;
Table dropped.
SQL> drop table wwl003 purge;
Table dropped.
SQL> drop table wwl004 purge;
Table dropped.
SQL> drop table wwl005 purge;
Table dropped.
SQL> conn / as sysdba
Connected.
3、对数据库执行了DML操作后，数据库的SCN号改变了。
SQL> selectcurrent_scn from v$database;
CURRENT_SCN
-----------
1511437
4、开始执行restore，到备份时候的状态：
RMAN> restore database;
Starting restore at 13-JUL-12
using target database control file instead ofrecovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
channel ORA_DISK_1: starting datafile backupsetrestore
channel ORA_DISK_1: specifying datafile(s) torestore from backup set
restoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbf
restoring datafile 00002 to /DBBak2/oradata/WWL/undotbs01.dbf
restoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbf
restoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbf
restoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbf
restoring datafile 00006 to /DBBak2/oradata/WWL/wwl02.dbf
restoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbf
channel ORA_DISK_1: reading from backup piece/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1tag=TAG20120712T095437
channel ORA_DISK_1: restore complete, elapsed time:00:01:06
Finished restore at 13-JUL-12
5、开始执行恢复，到我们删除表之前的状态：
SQL> recover database until change 1511297;
ORA-00279: change 1436429 generated at 07/12/201209:54:38 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf
ORA-00280: change 1436429 for thread 1 is insequence #3
Specify log: {=suggested | filename |AUTO | CANCEL}
auto
ORA-00279: change 1440657 generated at 07/12/201214:00:52 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788450452.dbf
ORA-00280: change 1440657 for thread 1 is insequence #1
ORA-00279: change 1440855 generated at 07/12/201215:08:58 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.dbf
ORA-00280: change 1440855 for thread 1 is in sequence#1
ORA-00279: change 1441316 generated at 07/12/201215:19:50 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788455190.dbf
ORA-00280: change 1441316 for thread 1 is insequence #1
ORA-00279: change 1442275 generated at 07/12/201215:52:01 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788457121.dbf
ORA-00280: change 1442275 for thread 1 is insequence #1
ORA-00279: change 1442953 generated at 07/12/201216:25:06 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf
ORA-00280: change 1442953 for thread 1 is insequence #1
ORA-00279: change 1462958 generated at 07/12/201216:28:16 needed for thread 1
ORA-00289: suggestion : /DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbf
ORA-00280: change 1462958 for thread 1 is insequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbf' no
longer needed for this recovery
ORA-00279: change 1462963 generated at 07/12/201217:17:59 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf
ORA-00280: change 1462963 for thread 1 is insequence #1
ORA-00279: change 1483784 generated at 07/12/201217:54:25 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbf
ORA-00280: change 1483784 for thread 1 is insequence #2
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbf' no
longer needed for this recovery
ORA-00279: change 1486119 generated at 07/12/201220:35:27 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788474127.dbf
ORA-00280: change 1486119 for thread 1 is insequence #1
ORA-00279: change 1487388 generated at 07/12/201221:31:17 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788477477.dbf
ORA-00280: change 1487388 for thread 1 is insequence #1
Log applied.
Media recovery complete.
SQL>
6、执行完恢复之后，SCN被清空
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
7、这个时候我们需要对redo也进行清空
SQL> alter database open restlogs;
8、由此可以见得，数据已经恢复回来了：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
WWL001 TABLE
WWL002 TABLE
WWL003 TABLE
WWL004 TABLE
WWL005 TABLE
SQL> select * from wwl005;
IDNAME
---------- ------------
1wwl
2prodence
3woo
4xgx
5cms
SQL>
至此恢复已经完成。
基于cancel的不一致性恢复(归档存在) 第一篇
基于取消的恢复只适用于以下情况： 归档日志丢失导致完全恢复失败； 丢失了数据文件和未归档的重做日志(联机重做日志)；
1、先关闭数据库，执行一次全库冷备份。
SQL> select file_name fromdba_data_files;
FILE_NAME
--------------------------------------------
/DBBak2/oradata/WWL/users01.dbf
/DBBak2/oradata/WWL/sysaux01.dbf
/DBBak2/oradata/WWL/undotbs01.dbf
/DBBak2/oradata/WWL/system01.dbf
/DBBak2/oradata/WWL/WWL001.dbf
/DBBak2/oradata/WWL/WWL002.dbf
/DBBak2/oradata/WWL/WWL003.dbf
7 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir/DBBak2/oradata/WWL2
SQL> !cp/DBBak2/oradata/WWL/* /DBBak2/oradata/WWL2/
2、然后打开数据库继续使用，我们可以看到备份的时候数据库wwl001表中只有4条记录。
SQL> select * from wwl001;
IDNAME
---------- ------------------
1jetsen
2woo
3prudence
4beijin
4 rows selected.
3、继续向wwl001表中插入数据，并切换日志让redo log归档。
SQL> insert into wwl001values(5,'china');
1 row created.
SQL> insert into wwl001values(6,'america');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from wwl001;
IDNAME
---------- ------------------
1jetsen
2woo
3prudence
4beijin
5china
6america
6 rows selected.
SQL>
SQL> alter system switchlogfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
4、关闭数据库，模拟数据文件丢失，归档日志也丢失了，所以数据库只能做不一致性恢复。
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !rm -rf/DBBak2/oradata/WWL/*.dbf
SQL> !cp/DBBak2/oradata/WWL2/*.dbf /DBBak2/oradata/WWL/
SQL> !cp /DBBak2/oradata/WWL2/*.dbf/DBBak2/oradata/WWL/
5、执行基于取消的不完全恢复
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
SQL>select * fromv$recover_file;
FILE#ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------
1ONLINE ONLINE 1783695 31-JUL-12
2ONLINE ONLINE 1783695 31-JUL-12
3ONLINE ONLINE 1783695 31-JUL-12
4ONLINE ONLINE 1783695 31-JUL-12
5ONLINE ONLINE 1783695 31-JUL-12
6ONLINE ONLINE 1783695 31-JUL-12
7ONLINE ONLINE 1783695 31-JUL-12
7 rows selected.
SQL> recover databaseuntil cancel;
ORA-00279: change 1783695 generated at 07/31/201215:32:04 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch/1_2_790095025.dbf
ORA-00280: change 1783695 for thread 1 is insequence #2
Specify log: {=suggested | filename |AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL> select * fromv$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------------ ---------
1ONLINE ONLINE 1783695 31-JUL-12
2ONLINE ONLINE 1783695 31-JUL-12
3ONLINE ONLINE 1783695 31-JUL-12
4ONLINE ONLINE 1783695 31-JUL-12
5ONLINE ONLINE 1783695 31-JUL-12
6ONLINE ONLINE 1783695 31-JUL-12
7ONLINE ONLINE 1783695 31-JUL-12
7 rows selected.
SQL> alter database openresetlogs;
Database altered.
SQL> conn wwl/wwl
Connected.
SQL> select * from wwl001;
IDNAME
---------- ------------------
1jetsen
2woo
3prudence
4beijin
SQL>
至此恢复已经完成。
基于cancel 的不一致性恢复(归档丢失) 第二篇
主要适用于：基于Cancel的不完全恢复适用场景：Recover时，所需的某个归档日志损坏，或主机断电，current状态的联机日志损坏。
创建测试表
创建wwl002表，切换日志，再创建新的wwl003表，主机断电，删除当前日志，模拟文件损坏。
SQL> conn wwl/wwl
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
WWL001 TABLE
SQL> create table WWL002 as select * fromwwl001;
Table created.
SQL> conn / as sysdba
Connected.
切换日志
SQL> alter system switch logfile;
System altered.
后再创建第二张表
SQL> conn wwl/wwl
Connected.
SQL> create table wwl003 as select * fromwwl001;
Table created.
查看当前日志组，确定当前活动的日志组，是组4
SQL> conn / as sysdba
Connected.
SQL> set line 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------
4 1 2 134217728 2 YES ACTIVE 1716929 30-JUL-12
5 1 1 134217728 2 YESINACTIVE 1692728 27-JUL-12
6 1 3 134217728 2 NO CURRENT 1720396 30-JUL-12
7 1 0 134217728 2 YES UNUSED 0
定位当前日志组的日志文件，有两个。
SQL> colmember format a30
SQL> select * from v$logfile;
GROUP#STATUS TYPE MEMBER IS_
---------- ------- ------------------------------------- ---
7 ONLINE /DBBak2/oradata/WWL/redo7a.log NO
7 ONLINE /DBBak2/oradata/WWL/redo7b.log NO
6 ONLINE /DBBak2/oradata/WWL/redo6a.log NO
6 ONLINE /DBBak2/oradata/WWL/redo6b.log NO
5STALE ONLINE /DBBak2/oradata/WWL/redo5a.log NO
5STALE ONLINE /DBBak2/oradata/WWL/redo5b.log NO
4 ONLINE /DBBak2/oradata/WWL/redo4a.log NO
4 ONLINE /DBBak2/oradata/WWL/redo4b.log NO
8 rows selected.
删除当前日志组文件，模拟在线事务丢失：
SQL> !rm -f /DBBak2/oradata/WWL/redo4a.log
SQL> !rm -f /DBBak2/oradata/WWL/redo4b.log
模拟服务器断电
SQL> shutdown abort;
ORACLE instance shut down.
恢复步骤:
1、尝试启动数据库的时候报当前日志丢失。
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed formembers of log group 4 of thread 1
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4b.log'
ORA-27037: unable to obtain filestatus
Linux Error: 2: No such file ordirectory
Additional information: 3
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4a.log'
ORA-27037: unable to obtain filestatus
Linux Error: 2: No such file ordirectory
Additional information: 3
2、尝试Clear redo4
SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery ofinstance WWL (thread 1)
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4a.log'
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4b.log'
3、在当前库做基于Cancel的不完全恢复
SQL> recover database until cancel;
ORA-00279: change 1716930 generated at 07/30/201211:03:51 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf
ORA-00280: change 1716930 for thread 1 is insequence #2
Specify log: {=suggested | filename |AUTO | CANCEL}
auto
ORA-00279: change 1720396 generated at 07/30/201213:37:21 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf
ORA-00280: change 1720396 for thread 1 is insequence #3
ORA-00278: log file '/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf'no longer needed for this recovery
ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPENRESETLOGS would get error below
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
做完恢复之后必须使用resetlogs选项打开数据库：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
恢复之后，使用Resetlogs选项，仍无法打开数据库，提示数据文件不一致，System表空间需要进一步的恢复。当前日志损坏时，不能基于当前的数据库做不完全恢复。只能用以前的备份，做一个基于Cancel的不完全恢复。
重建下控制文件：
SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
'/DBBak2/oradata/WWL/redo4a.log',
MAXDATAFILES100
MAXINSTANCES 8
GROUP 5 (
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/DBBak2/oradata/WWL/redo4a.log',
'/DBBak2/oradata/WWL/redo4b.log'
GROUP 6 (
) SIZE128M,
GROUP 5 (
'/DBBak2/oradata/WWL/redo5a.log',
'/DBBak2/oradata/WWL/redo5b.log'
'/DBBak2/oradata/WWL/redo7b.log'
) SIZE128M,
GROUP 6 (
'/DBBak2/oradata/WWL/redo6a.log',
'/DBBak2/oradata/WWL/redo6b.log'
) SIZE128M,
GROUP 7 (
'/DBBak2/oradata/WWL/redo7a.log',
'/DBBak2/oradata/WWL/redo7b.log'
) SIZE 128M
-- STANDBY LOGFILE
DATAFILE
'/DBBak2/oradata/WWL/system01.dbf',
'/DBBak2/oradata/WWL/undotbs01.dbf',
'/DBBak2/oradata/WWL/sysaux01.dbf',
'/DBBak2/oradata/WWL/users01.dbf',
'/DBBak2/oradata/WWL/wwl001',
'/DBBak2/oradata/WWL/wwl002',
'/DBBak2/oradata/WWL/wwl003'
CHARACTER SET ZHS16CGB231280
34 ;
Control file created.
再次打开，结果还是不行
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
可以尝试使用_allow_resetlogs_corruption隐含参数来打开数据库
SQL> alter system set "_allow_resetlogs_corruption"=truescope=spfile;
System altered.
修改完参数之后重启数据库到mount状态
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS optionfor database open
同样以resetlogs模式启动数据库
SQL> alter database open resetlogs;
Database altered.
一定记得关闭该参数
SQL> alter system set"_allow_resetlogs_corruption"=FALSE scope=spfile;
System altered.
让参数关闭生效，再次启动数据库
SQL> startup force;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
检查_allow_resetlogs_corruption隐含参数是否关闭，确定已经关闭
SQL> show parameter _allow_resetlogs_corruption
NAME TYPE VALUE
------------------------------------ -----------------------------------------
_allow_resetlogs_corruption boolean FALSE
SQL>
因为重建了控制文件，默认是没有制定temp表空间，这里制定下：
SQL> ALTER TABLESPACE TEMP01 ADD TEMPFILE'/DBBak2/oradata/WWL/temp01.dbf' REUSE;
Tablespace altered.
检查数据
SQL> conn wwl/wwl
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
WWL001 TABLE
WWL002 TABLE
可以看到表WWL002存在，表WWL003不存在。因为WWL003的创建，是保存在当前REDO日志中的，而当前REDO日志损坏，所有当前日志中保存的操作全部丢失了。归档日志、或者REDO日志损失，数据库就只能恢复到丢失的日志之前了。
至此恢复已经完成。
基于备份控制文件(unsing backup controlfile)的恢复
主要适用于：基于备份控制文件的恢复只要适用于以下情况：表空间被意外删除；所有控制文件全部损坏。
1、关闭数据库执行一次全库冷备份：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------
/DBBak2/oradata/WWL/users01.dbf
/DBBak2/oradata/WWL/sysaux01.dbf
/DBBak2/oradata/WWL/undotbs01.dbf
/DBBak2/oradata/WWL/system01.dbf
/DBBak2/oradata/WWL/WWL001.dbf
/DBBak2/oradata/WWL/WWL002.dbf
/DBBak2/oradata/WWL/WWL003.dbf
7 rowsselected.
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL> !mkdir /DBBak2/oradata/WWL2
SQL> !cp /DBBak2/oradata/WWL/* /DBBak2/oradata/WWL2/
通过如上的操作数据库现在已经有了一个冷备份了，里面包含另外wwl表空间，现在模拟用户误删除了wwl表空间。
SQL> startup
ORACLEinstance started.
TotalSystem Global Area 100663296 bytes
FixedSize 1217884 bytes
VariableSize 88083108 bytes
DatabaseBuffers 8388608 bytes
RedoBuffers 2973696 bytes
Databasemounted.
Databaseopened.
SQL>
模拟删除表空间
SQL> drop tablespace wwl including contents;
Tablespacedropped.
当前的控制文件中已经不包含wwl表空间了，但是我们之前冷备份的控制文件中还是包含的，所以必须使用以前的控制文件来进行wwl表空间的恢复。
还原之前备份的数据文件：
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL> !rm -rf /DBBak2/oradata/WWL/*.dbf
SQL> !rm -rf /DBBak2/oradata/WWL/*.ctl
SQL> !cp /DBBak2/oradata/WWL2/*.dbf /DBBak2/oradata/WWL/
SQL> !cp /DBBak2/oradata/WWL2/*.ctl /DBBak2/oradata/WWL/
SQL>
使用控制文件进行恢复：
SQL> startup
ORACLEinstance started.
TotalSystem Global Area 100663296 bytes
FixedSize 1217884 bytes
VariableSize 88083108 bytes
DatabaseBuffers 8388608 bytes
RedoBuffers 2973696 bytes
Databasemounted.
ORA-00338:log 5 of thread 1 is more recent than control file
ORA-00312:online log 5 thread 1: '/DBBak2/oradata/WWL/redo5a.log'
ORA-00312:online log 5 thread 1: '/DBBak2/oradata/WWL/redo5b.log'
SQL> recover database using backup controlfile until cancel;
ORA-00279:change 1782560 generated at 07/31/2012 14:37:24 needed for thread 1
ORA-00289:suggestion : /DBSoft/product/10.2.0/db_1/dbs/arch/1_3_790085314.dbf
ORA-00280:change 1782560 for thread 1 is in sequence #3
Specifylog: {=suggested | filename | AUTO | CANCEL}
cancel
Mediarecovery cancelled.
SQL> alter database open resetlogs;
Databasealtered.
SQL>
至此已经恢复成功。
403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
