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

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

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

点击(此处)折叠或打开

1. 
   
2. SQL> select count(*) from test;
3. 
   
4.   COUNT(*)
   
5. ----------
   
6.      86954
   
7. 
   
8. SQL> select sysdate from dual;
   
9. 
   
10. SYSDATE
    
11. -------------------
    
12. 2016-08-09 00:49:02
    
13. 
    
14. SQL> truncate table test;
    
15. 
    
16. Table truncated.
    
17. 
    
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 /
    
22. 
    
23. Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 9 00:49:20 2016
    
24. 
    
25. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    
26. 
    
27. connected to target database: MHXY (DBID=4239099671)
    
28. 
    
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> }
    
37. 
    
38. using target database control file instead of recovery catalog
    
39. database closed
    
40. database dismounted
    
41. Oracle instance shut down
    
42. 
    
43. connected to target database (not started)
    
44. Oracle instance started
    
45. database mounted
    
46. 
    
47. Total System Global Area 776646656 bytes
    
48. 
    
49. Fixed Size 2257272 bytes
    
50. Variable Size 507514504 bytes
    
51. Database Buffers 260046848 bytes
    
52. Redo Buffers 6828032 bytes
    
53. 
    
54. executing command: SET until clause
    
55. 
    
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
    
59. 
    
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
    
73. 
    
74. Starting recover at 2016-08-09 00:52:25
    
75. using channel ORA_DISK_1
    
76. 
    
77. starting media recovery
    
78. 
    
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
    
85. 
    
86. database opened
    
87. 
    
88. RMAN> exit
    
89. 
    
90. Recovery Manager complete.
    
91. [oracle@mhxy164 mhxy]$ sqlplus / as sysdba
    
92. 
    
93. SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 9 00:52:57 2016
    
94. 
    
95. Copyright (c) 1982, 2013, Oracle. All rights reserved.
    
96. 
    
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
    
100. 
     
101. SQL> select count(*) from test;
     
102. 
     
103.   COUNT(*)
     
104. ----------
     
105.      86954

二、基于SCN号的不完全恢复

点击(此处)折叠或打开

1. [oracle@mhxy164 mhxy]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 9 00:58:55 2016
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. 
   
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
    
11. 
    
12. SQL> select count(*) from test1;
13. 
    
14.   COUNT(*)
    
15. ----------
    
16.      86956
    
17. 
    
18. SQL> select dbms_flashback.get_system_change_number from dual;
    
19. 
    
20. GET_SYSTEM_CHANGE_NUMBER
    
21. ------------------------
    
22.                  1180680
    
23. 
    
24. SQL> truncate table test1;
    
25. 
    
26. Table truncated.
    
27. 
    
28. SQL> select count(*) from test1;
    
29. 
    
30.   COUNT(*)
    
31. ----------
    
32.          0
    
33. 
    
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 /
    
38. 
    
39. Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 9 01:00:05 2016
    
40. 
    
41. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    
42. 
    
43. connected to target database: MHXY (DBID=4239099671)
    
44. 
    
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> }
    
53. 
    
54. using target database control file instead of recovery catalog
    
55. database closed
    
56. database dismounted
    
57. Oracle instance shut down
    
58. 
    
59. connected to target database (not started)
    
60. Oracle instance started
    
61. database mounted
    
62. 
    
63. Total System Global Area 776646656 bytes
    
64. 
    
65. Fixed Size 2257272 bytes
    
66. Variable Size 507514504 bytes
    
67. Database Buffers 260046848 bytes
    
68. Redo Buffers 6828032 bytes
    
69. 
    
70. executing command: SET until clause
    
71. 
    
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
    
75. 
    
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
    
89. 
    
90. Starting recover at 2016-08-09 01:01:58
    
91. using channel ORA_DISK_1
    
92. 
    
93. starting media recovery
    
94. 
    
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
     
105. 
     
106. database opened
     
107. 
     
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博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2123184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27039319/viewspace-2123184/