当前位置:  数据库>oracle

探索Oracle不完全恢复之--基于时间恢复

    来源: 互联网  发布时间:2017-05-03

    本文导语: 基于时间(time)恢复 基于时间的恢复将数据库恢复到备份点与失败点之间的某个时间点。基于时间的恢复不仅在介质失败的时候使用,也可以在数据库正常运行的时候使用。例如:某个用户误删除了某个表的数据,这个时候我...

基于时间(time)恢复

基于时间的恢复将数据库恢复到备份点与失败点之间的某个时间点。基于时间的恢复不仅在介质失败的时候使用,也可以在数据库正常运行的时候使用。例如:某个用户误删除了某个表的数据,这个时候我们可以通过基于时间的恢复来将删除的数据恢复出来,示例如下:

 

1、查看当前用户下的表,只有一张WWL001

21:07:31 SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ -----------------

WWL001                         TABLE

Elapsed: 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.07

21:13:47 SQL>

 

因为我们是要做基于时间的恢复,那么我们只有将数据库恢复到21:13:13之前的这个时间段,才能把我们刚才创建的表找回来。

 

3、开始执行恢复,先将数据库启动到mount状态

21:14:40 SQL> conn / as sysdba

Connected.

21:14:44 SQL> shutdown immediate

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 control file insteadof recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=47 devtype=DISK

 

channel ORA_DISK_1: starting datafilebackupset restore

channel ORA_DISK_1: specifying datafile(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: reading from backuppiece /DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1

channel ORA_DISK_1: restored backup piece1

piecehandle=/DBSoft/product/10.2.0/db_1/dbs/01nft4mu_1_1 tag=TAG20120712T095437

channel ORA_DISK_1: restore complete,elapsed time: 00:01:35

Finished 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 1

ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_788372282.dbf

ORA-00280: change 1436429 for thread 1 isin sequence #3

 

 

21:30:09 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 isin sequence #1

 

 

ORA-00279: change 1440855 generated at07/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 for thread 1 isin sequence #1

 

 

ORA-00279: change 1441316 generated at07/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 for thread 1 isin sequence #1

 

 

ORA-00279: change 1442275 generated at07/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 for thread 1 isin sequence #1

 

 

ORA-00279: change 1442953 generated at07/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 for thread 1 is insequence #1

 

 

ORA-00279: change 1462958 generated at07/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 for thread 1 isin sequence #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 at07/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 for thread 1 isin sequence #1

 

 

ORA-00279: change 1483784 generated at07/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 for thread 1 isin 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 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.01

21:34:16 SQL> select * from wwl003;

 

       ID NAME

-------------------------------------------------------

        1 wwl

        2 prodence

        3 woo

        4 xgx

        5 cms

 

Elapsed: 00:00:00.00

21:34:18 SQL> select * from wwl004;

 

       ID NAME

-------------------------------------------------------

        1 wwl

        2 prodence

        3 woo

        4 xgx

        5 cms

 

Elapsed: 00:00:00.01

21:34:20 SQL> select * from wwl005;

 

       ID NAME

---------- ---------------------------------------------

        1 wwl

        2 prodence

        3 woo

        4 xgx

        5 cms

 

Elapsed: 00:00:00.01

21:34:22 SQL>

相关阅读:

探索Oracle之RMAN_01概念

探索Oracle之RMAN_02基本使用

探索Oracle之RMAN_03非一致性备份

探索Oracle之RMAN_04非一致性备份

探索Oracle之RMAN_05增量备份

探索Oracle之RMAN_06备份策略

探索Oracle之RMAN_07单个数据文件丢失恢复

探索Oracle之RMAN_07整个业��表空间丢失恢复

探索Oracle之RMAN_07 磁盘损坏数据丢失恢复

探索Oracle之RMAN_07 数据库所有文件全部丢失恢复

探索Oracle之RMAN_07 重做日志redu文件丢失恢复

探索Oracle之RMAN_07 参数文件丢失恢复

探索Oracle之RMAN_07控制文件丢失恢复

探索Oracle之RMAN_07 system表空间丢失恢复


    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 生命游戏探索 Golly
  • 每个人都能探索太阳 JHelioviewer
  • 关于<Java 2 完全探索>的三个问题
  • 各位大虾请问:有本<java完全探索>的书怎么样
  • 最近探索JBOSS+TOMCAT,但发现2.4.3版本的没有帮助@!!!
  • 能亲身投入最先进系统的开发和最先进技术的探索,将是您一生的骄傲,您愿加入我们吗?
  • 探索linux的道路上有点迷失了,希望大家给小弟提点建议,谢谢啦~
  • Linux 编译原来内核失败原因探索
  • 大家评论一下这两本书:《java入门经典》、《java完全探索》好吗?我正在看《核心技术卷I》,怎么觉得写的很零碎呀??请看过这些数的兄弟多多指点一下,小弟多谢了!!? //bow bow
  • PHP内核探索:变量概述
  • 正则表达式不区分大小写以及解决思路的探索 .
  • 菜鸟的提问:为什么很多人都推荐Debian发行版?大伙觉的那个发行版更适合深入探索Linux
  • MSSQL优化之探索MSSQL执行计划(转)
  • Eclipse中自动重构实现探索
  • Oracle9i 动态SGA,PGA特性探索
  • PHP内核探索:变量存储与类型使用说明
  • 正则应用之 逆序环视探索 .
  • 探索ORACLE之ASM概念(完整版)


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,