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

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

基于SCN和时间点的不完全恢复

原创 Oracle 作者：呱呱牛 时间：2016-08-09 08:38:06 0 删除 编辑

完全恢复与不完全恢复是数据库的两种恢复方式，顾名思义，他们的区别就是恢复后数据没有没丢失，不完全恢复是指恢复后有部分数据丢失，而完全恢复没有。
完整恢复是由于Oracle应用了归档日志和联机重做日志中所有的修改，将数据块恢复到最接近当前时间的时间点。
不完全恢复是利用备份产生一个非当前版本的数据库。换句话说，恢复过程中不会应用备份产生后生成的所有的重做日志。

下面我们基于时间点和SCN号的不完全恢复测试，truncate表后进行不完全恢复并验证恢复情况。
一、基于时间点的不完全恢复

点击(此处)折叠或打开


  2. SQL> select count(*) from test;

  4.   COUNT(*)
  5. ----------
  6.      86954

  8. SQL> select sysdate from dual;

  10. SYSDATE
  11. -------------------
  12. 2016-08-09 00:49:02

  14. SQL> truncate table test;

  16. Table truncated.

  18. SQL> exit
  19. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  20. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  21. [oracle@mhxy164 mhxy]$ rman target /

  23. Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 9 00:49:20 2016

  25. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  27. connected to target database: MHXY (DBID=4239099671)

  29. RMAN> run{
  30. 2> shutdown immediate;
  31. 3> startup mount;
  32. 4> set until time '2016-08-09 00:49:02';
  33. 5> restore database;
  34. 6> recover database;
  35. 7> alter database open resetlogs;
  36. 8> }

  38. using target database control file instead of recovery catalog
  39. database closed
  40. database dismounted
  41. Oracle instance shut down

  43. connected to target database (not started)
  44. Oracle instance started
  45. database mounted

  47. Total System Global Area 776646656 bytes

  49. Fixed Size 2257272 bytes
  50. Variable Size 507514504 bytes
  51. Database Buffers 260046848 bytes
  52. Redo Buffers 6828032 bytes

  54. executing command: SET until clause

  56. Starting restore at 2016-08-09 00:50:57
  57. allocated channel: ORA_DISK_1
  58. channel ORA_DISK_1: SID=133 device type=DISK

  60. creating datafile file number=6 name=/oracle/database/oradata/mhxy/tg.dbf
  61. channel ORA_DISK_1: starting datafile backup set restore
  62. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  63. channel ORA_DISK_1: restoring datafile 00001 to /oracle/database/oradata/mhxy/system01.dbf
  64. channel ORA_DISK_1: restoring datafile 00002 to /oracle/database/oradata/mhxy/sysaux01.dbf
  65. channel ORA_DISK_1: restoring datafile 00003 to /oracle/database/oradata/mhxy/undotbs01.dbf
  66. channel ORA_DISK_1: restoring datafile 00004 to /oracle/database/oradata/mhxy/users01.dbf
  67. channel ORA_DISK_1: restoring datafile 00005 to /oracle/database/oradata/mhxy/example01.dbf
  68. channel ORA_DISK_1: reading from backup piece /oracle/database/fast_recovery_area/MHXY/backupset/2016_08_08/o1_mf_nnndf_TAG20160808T224812_ctk6xf58_.bkp
  69. channel ORA_DISK_1: piece handle=/oracle/database/fast_recovery_area/MHXY/backupset/2016_08_08/o1_mf_nnndf_TAG20160808T224812_ctk6xf58_.bkp tag=TAG20160808T224812
  70. channel ORA_DISK_1: restored backup piece 1
  71. channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
  72. Finished restore at 2016-08-09 00:52:25

  74. Starting recover at 2016-08-09 00:52:25
  75. using channel ORA_DISK_1

  77. starting media recovery

  79. archived log for thread 1 with sequence 13 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_13_ctk7hzhd_.arc
  80. archived log for thread 1 with sequence 14 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_14_ctk7j32v_.arc
  81. archived log for thread 1 with sequence 15 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_15_ctk81l1y_.arc
  82. archived log file name=/oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_13_ctk7hzhd_.arc thread=1 sequence=13
  83. media recovery complete, elapsed time: 00:00:04
  84. Finished recover at 2016-08-09 00:52:30

  86. database opened

  88. RMAN> exit

  90. Recovery Manager complete.
  91. [oracle@mhxy164 mhxy]$ sqlplus / as sysdba

  93. SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 9 00:52:57 2016

  95. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  97. Connected to:
  98. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  99. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  101. SQL> select count(*) from test;

  103.   COUNT(*)
  104. ----------
  105.      86954
二、基于SCN号的不完全恢复

点击(此处)折叠或打开

  1. [oracle@mhxy164 mhxy]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 9 00:58:55 2016

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  12. SQL> select count(*) from test1;

  14.   COUNT(*)
  15. ----------
  16.      86956

  18. SQL> select dbms_flashback.get_system_change_number from dual;

  20. GET_SYSTEM_CHANGE_NUMBER
  21. ------------------------
  22.                  1180680

  24. SQL> truncate table test1;

  26. Table truncated.

  28. SQL> select count(*) from test1;

  30.   COUNT(*)
  31. ----------
  32.          0

  34. SQL> exit
  35. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  36. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  37. [oracle@mhxy164 mhxy]$ rman target /

  39. Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 9 01:00:05 2016

  41. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

  43. connected to target database: MHXY (DBID=4239099671)

  45. RMAN> run{
  46. 2> shutdown immediate;
  47. 3> startup mount
  48. 4> set until scn 1180680;
  49. 5> restore database;
  50. 6> recover database;
  51. 7> alter database open resetlogs;
  52. 8> }

  54. using target database control file instead of recovery catalog
  55. database closed
  56. database dismounted
  57. Oracle instance shut down

  59. connected to target database (not started)
  60. Oracle instance started
  61. database mounted

  63. Total System Global Area 776646656 bytes

  65. Fixed Size 2257272 bytes
  66. Variable Size 507514504 bytes
  67. Database Buffers 260046848 bytes
  68. Redo Buffers 6828032 bytes

  70. executing command: SET until clause

  72. Starting restore at 2016-08-09 01:01:01
  73. allocated channel: ORA_DISK_1
  74. channel ORA_DISK_1: SID=133 device type=DISK

  76. creating datafile file number=6 name=/oracle/database/oradata/mhxy/tg.dbf
  77. channel ORA_DISK_1: starting datafile backup set restore
  78. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  79. channel ORA_DISK_1: restoring datafile 00001 to /oracle/database/oradata/mhxy/system01.dbf
  80. channel ORA_DISK_1: restoring datafile 00002 to /oracle/database/oradata/mhxy/sysaux01.dbf
  81. channel ORA_DISK_1: restoring datafile 00003 to /oracle/database/oradata/mhxy/undotbs01.dbf
  82. channel ORA_DISK_1: restoring datafile 00004 to /oracle/database/oradata/mhxy/users01.dbf
  83. channel ORA_DISK_1: restoring datafile 00005 to /oracle/database/oradata/mhxy/example01.dbf
  84. channel ORA_DISK_1: reading from backup piece /oracle/database/fast_recovery_area/MHXY/backupset/2016_08_08/o1_mf_nnndf_TAG20160808T224812_ctk6xf58_.bkp
  85. channel ORA_DISK_1: piece handle=/oracle/database/fast_recovery_area/MHXY/backupset/2016_08_08/o1_mf_nnndf_TAG20160808T224812_ctk6xf58_.bkp tag=TAG20160808T224812
  86. channel ORA_DISK_1: restored backup piece 1
  87. channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
  88. Finished restore at 2016-08-09 01:01:58

  90. Starting recover at 2016-08-09 01:01:58
  91. using channel ORA_DISK_1

  93. starting media recovery

  95. archived log for thread 1 with sequence 13 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_13_ctk7hzhd_.arc
  96. archived log for thread 1 with sequence 14 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_14_ctk7j32v_.arc
  97. archived log for thread 1 with sequence 15 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_15_ctk81l1y_.arc
  98. archived log for thread 1 with sequence 16 is already on disk as file /oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_09/o1_mf_1_16_ctkg6gcb_.arc
  99. archived log file name=/oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_13_ctk7hzhd_.arc thread=1 sequence=13
  100. archived log file name=/oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_14_ctk7j32v_.arc thread=1 sequence=14
  101. archived log file name=/oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_08/o1_mf_1_15_ctk81l1y_.arc thread=1 sequence=15
  102. archived log file name=/oracle/database/fast_recovery_area/MHXY/archivelog/2016_08_09/o1_mf_1_16_ctkg6gcb_.arc thread=1 sequence=16
  103. media recovery complete, elapsed time: 00:00:05
  104. Finished recover at 2016-08-09 01:02:05

  106. database opened

  108. RMAN> exit

    Recovery Manager complete.
    [oracle@mhxy164 mhxy]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 9 01:45:21 2016

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> select count(*) from test1;

      COUNT(*)
    ----------
        86956
    SQL> 

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

0
0
上一篇： RAC环境下一次更新PSU（p18139609）的经历
下一篇： linux 配置SAN存储-IPSAN
请登录后发表评论 登录
全部评论
呱呱牛

注册时间：2014-05-29

  • 博文量
    96
  • 访问量
    286799

最新文章

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

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

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

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