Welcome 微信登录

首页 / 数据库 / MySQL / 探索Oracle不完全恢复之--基于时间恢复

基于时间（time）恢复基于时间的恢复将数据库恢复到备份点与失败点之间的某个时间点。基于时间的恢复不仅在介质失败的时候使用，也可以在数据库正常运行的时候使用。例如：某个用户误删除了某个表的数据，这个时候我们可以通过基于时间的恢复来将删除的数据恢复出来，示例如下： 1、查看当前用户下的表，只有一张WWL00121:07:31 SQL> select * from tab; TNAME                          TABTYPE  CLUSTERID------------------------------ -----------------WWL001                         TABLEElapsed: 00:00:00.06 我们通过WWL001来创建WWL002-WWL005 共四张表用来测试不完全恢复21:08:28 SQL> create table wwl002 asselect * from wwl001;Table created.Elapsed: 00:00:00.17 21:08:55 SQL> create table wwl003 asselect * from wwl001;Table created.Elapsed: 00:00:00.04 21:09:00 SQL> create table wwl004 asselect * from wwl001;Table created.Elapsed: 00:00:00.03 21:09:06 SQL> create table wwl005 asselect * from wwl001;Table created.Elapsed: 00:00:00.05如上表已经创建完成 2、我们在21:13:13开始删除表（请注意时间）21:13:13 SQL> drop table wwl002 purge;Table dropped.Elapsed: 00:00:00.16 21:13:28 SQL> drop table wwl003 purge;Table dropped.Elapsed: 00:00:00.11 21:13:34 SQL> truncate table wwl004;Table truncated.Elapsed: 00:00:00.32 21:13:44 SQL> truncate table wwl005;Table truncated.Elapsed: 00:00:00.0721:13:47 SQL>  因为我们是要做基于时间的恢复，那么我们只有将数据库恢复到21:13:13之前的这个时间段，才能把我们刚才创建的表找回来。 3、开始执行恢复，先将数据库启动到mount状态21:14:40 SQL> conn / as sysdbaConnected.21:14:44 SQL> shutdown immediateDatabase closed.Database dismounted.Oracle instance shut down.21:15:20 SQL> startup  mount;ORACLE instance started. Total System Global Area  100663296 bytesFixed Size                  1217884 bytesVariable Size              88083108 bytesDatabase Buffers            8388608 bytesRedo Buffers                2973696 bytesDatabase mounted.21:15:46 SQL> e 4、开始执行restore到备份数据库的当前状态：RMAN> restore database; Starting restore at 12-JUL-12using target database control file insteadof recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=47 devtype=DISK channel ORA_DISK_1: starting datafilebackupset restorechannel ORA_DISK_1: specifying datafile（s）to restore from backup setrestoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbfrestoring datafile 00002 to/DBBak2/oradata/WWL/undotbs01.dbfrestoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbfrestoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbfrestoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbfrestoring datafile 00006 to/DBBak2/oradata/WWL/wwl02.dbfrestoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbfchannel ORA_DISK_1: reading from backuppiece /DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1channel ORA_DISK_1: restored backup piece1piecehandle=/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1 tag=TAG20120712T095437channel ORA_DISK_1: restore complete,elapsed time: 00:01:35Finished restore at 12-JUL-12 RMAN> exit 5、执行基于时间点的恢复：21:27:54 SQL> recover database untiltime ‘YYYY-mm-dd hh24:mi:ss’21:27:54 SQL> recover database untiltime "2012-07-12 21:10:00";ORA-00279: change 1436429 generated at07/12/2012 09:54:38 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbfORA-00280: change 1436429 for thread 1 isin sequence #3  21:30:09 Specify log:{<RET>=suggested | filename | AUTO | CANCEL}autoORA-00279: change 1440657 generated at 07/12/201214:00:52 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788450452.dbfORA-00280: change 1440657 for thread 1 isin sequence #1  ORA-00279: change 1440855 generated at07/12/2012 15:08:58 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788454538.dbfORA-00280: change 1440855 for thread 1 isin sequence #1  ORA-00279: change 1441316 generated at07/12/2012 15:19:50 needed for thread 1ORA-00289: suggestion : /DBSoft/product/10.2.0/db_1/dbs/arch1_1_788455190.dbfORA-00280: change 1441316 for thread 1 isin sequence #1  ORA-00279: change 1442275 generated at07/12/2012 15:52:01 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788457121.dbfORA-00280: change 1442275 for thread 1 isin sequence #1  ORA-00279: change 1442953 generated at07/12/2012 16:25:06 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788459106.dbfORA-00280: change 1442953 for thread 1 is insequence #1  ORA-00279: change 1462958 generated at07/12/2012 16:28:16 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788459106.dbfORA-00280: change 1462958 for thread 1 isin sequence #2ORA-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 at07/12/2012 17:17:59 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_1_788462279.dbfORA-00280: change 1462963 for thread 1 isin sequence #1  ORA-00279: change 1483784 generated at07/12/2012 17:54:25 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_788462279.dbfORA-00280: change 1483784 for thread 1 isin sequence #2ORA-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 database open restlogs;alter database open restlogs 7、数据已经恢复完成，数据都回来了。21:34:04 SQL> select * from wwl002;        ID NAME-------------------------------------------------------        1 wwl        2 prodence        3 woo        4 xgx        5 cms Elapsed: 00:00:00.0121:34:16 SQL> select * from wwl003;        ID NAME-------------------------------------------------------        1 wwl        2 prodence        3 woo        4 xgx        5 cms Elapsed: 00:00:00.0021:34:18 SQL> select * from wwl004;        ID NAME-------------------------------------------------------        1 wwl        2 prodence        3 woo        4 xgx        5 cms Elapsed: 00:00:00.0121:34:20 SQL> select * from wwl005;        ID NAME---------- ---------------------------------------------        1 wwl        2 prodence        3 woo        4 xgx        5 cms Elapsed: 00:00:00.0121:34:22 SQL>相关阅读：探索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.htm探索Oracle之RMAN_07非一致性恢复（集合）探索Oracle不完全恢复之--基于SCN恢复 第一篇相关资讯      Oracle高级培训  Oracle不完全恢复 
  • Oracle数据库完全恢复和不完全恢复  （09/29/2014 20:13:21）
  • Oracle Apps Patching:adpatch（  （08/16/2012 15:41:37）
  • 话说V$SQL_MONITOR  （08/14/2012 07:19:54）
  • delete表的数据后恢复  （08/30/2012 08:59:58）
  • 使用ASH信息，发现高CPUsession  （08/14/2012 07:21:32）
  • 如何阅读Oracle Errorstack Output  （08/14/2012 07:15:47）
本文评论 查看全部评论 （0）
表情： 姓名： 字数