ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 基于时间的不完全恢复

基于时间的不完全恢复

原创 Oracle 作者：刘锡亚 时间：2017-04-06 09:48:41 0 删除 编辑
  1. 首先要在开启归档的前提下
  2. 做全库备份

    点击(此处)折叠或打开

    1. RMAN> backup as compressed backupset full database;

    3. Starting backup at 06-APR-17
    4. using channel ORA_DISK_1
    5. channel ORA_DISK_1: starting compressed full datafile backup set
    6. channel ORA_DISK_1: specifying datafile(s) in backup set
    7. input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
    8. input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
    9. input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
    10. input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
    11. input datafile file number=00007 name=/u01/app/oracle/oradata/PROD1/test02.dbf
    12. input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/test01.dbf
    13. input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
    14. channel ORA_DISK_1: starting piece 1 at 06-APR-17
    15. channel ORA_DISK_1: finished piece 1 at 06-APR-17
    16. piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1 tag=TAG20170406T091318 comment=NONE
    17. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
    18. channel ORA_DISK_1: starting compressed full datafile backup set
    19. channel ORA_DISK_1: specifying datafile(s) in backup set
    20. including current control file in backup set
    21. including current SPFILE in backup set
    22. channel ORA_DISK_1: starting piece 1 at 06-APR-17
    23. channel ORA_DISK_1: finished piece 1 at 06-APR-17
    24. piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/03s10amr_1_1 tag=TAG20170406T091318 comment=NONE
    25. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    26. Finished backup at 06-APR-17

  3. 创建表t1 ，并将表t1 删除

    点击(此处)折叠或打开

    1. SQL> create table t1(x int);

    3. Table created.


    6. SQL> insert into t1 select rownum from dual connect by rownum<=10;

    8. 10 rows created.

    10. SQL> commit;

    12. Commit complete.

    14. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

    16. TO_CHAR(SYSDATE,'YY
    17. -------------------
    18. 2017-04-06 09:16:22

    20. SQL> drop table t1;

    22. Table dropped.

    24. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

    26. TO_CHAR(SYSDATE,'YY
    27. -------------------
    28. 2017-04-06 09:17:01

    30. SQL> shutdown immediate;
    31. Database closed.
    32. Database dismounted.
    33. ORACLE instance shut down.

  4. 在mount状态下 利用rman 做基于时间的恢复

    点击(此处)折叠或打开

    1. RMAN> startup mount;

    3. Oracle instance started
    4. database mounted

    6. Total System Global Area 217157632 bytes

    8. Fixed Size 2251816 bytes
    9. Variable Size 159384536 bytes
    10. Database Buffers 50331648 bytes
    11. Redo Buffers 5189632 bytes

    13. RMAN> run{
    14. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
    15. 3> set until time='2017-04-06 09:16:22';
    16. 4> restore database;
    17. 5> recover database;
    18. 6> alter database open resetlogs;
    19. 7> }

    21. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

    23. executing command: SET until clause

    25. Starting restore at 06-APR-17
    26. allocated channel: ORA_DISK_1
    27. channel ORA_DISK_1: SID=18 device type=DISK

    29. channel ORA_DISK_1: starting datafile backup set restore
    30. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    31. channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/system01.dbf
    32. channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/sysaux01.dbf
    33. channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/undotbs01.dbf
    34. channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/users01.dbf
    35. channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/example01.dbf
    36. channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test01.dbf
    37. channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/test02.dbf
    38. channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1
    39. channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1 tag=TAG20170406T091318
    40. channel ORA_DISK_1: restored backup piece 1
    41. channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
    42. Finished restore at 06-APR-17

    44. Starting recover at 06-APR-17
    45. using channel ORA_DISK_1

    47. starting media recovery
    48. media recovery complete, elapsed time: 00:00:00

    50. Finished recover at 06-APR-17

    52. database opened

  5. 验证

    点击(此处)折叠或打开

    1. SQL> select * from t1;

    3.          X
    4. ----------
    5.          1
    6.          2
    7.          3
    8.          4
    9.          5
    10.          6
    11.          7
    12.          8
    13.          9
    14.         10

    16. 10 rows selected.


  6. 此时如果再想恢复到别的时间，会报错

    点击(此处)折叠或打开

    1. RMAN> run{
    2. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
    3. 3> set until time='2017-04-06 09:16:21';
    4. 4> restore database;
    5. 5> recover database;
    6. 6> alter database open resetlogs;
    7. 7> }

    9. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

    11. executing command: SET until clause

    13. Starting restore at 06-APR-17
    14. RMAN-00571: ===========================================================
    15. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    16. RMAN-00571: ===========================================================
    17. RMAN-03002: failure of restore command at 04/06/2017 09:41:55
    18. RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

    点击(此处)折叠或打开

    1. RMAN> run{
    2. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
    3. 3> set until time='2017-04-06 09:16:22';
    4. 4> restore database;
    5. 5> recover database;
    6. 6> alter database open resetlogs;
    7. 7> }

    9. using target database control file instead of recovery catalog
    10. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

    12. executing command: SET until clause

    14. Starting restore at 06-APR-17
    15. RMAN-00571: ===========================================================
    16. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    17. RMAN-00571: ===========================================================
    18. RMAN-03002: failure of restore command at 04/06/2017 09:30:29
    19. RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31405405/viewspace-2136688/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 利用句柄找回误删除的数据文件
下一篇： 更换数据文件的位置/名字（system表空间数据文件除外）
请登录后发表评论 登录
全部评论
刘锡亚

注册时间：2016-10-25

  • 博文量
    13
  • 访问量
    18653

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593