Oracle基於時間點的恢複

Oracle基於時間點的恢複2010-10-19 18:43

Oracle基於時間點的恢複能夠精確到什麼樣的精度? 這是一個需要關心的問題。 以下測試用於進行一點說明。 1.首先做好冷備份 2.建立測試資料 D:\>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:56:43 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. 11:56:44 SQL> startup ORACLE instance started. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. 11:57:01 SQL> create table test (name varchar2(20)); Table created. Elapsed: 00:00:00.04 11:57:23 SQL> insert into test values('aaaaaaaaaaaaaaaaaaaa'); 1 row created. Elapsed: 00:00:00.00 11:57:23 SQL> insert into test values('bbbbbbbbbbbbbbbbbbbb'); 1 row created. Elapsed: 00:00:00.00 11:57:23 SQL> insert into test values('cccccccccccccccccccc'); 1 row created. Elapsed: 00:00:00.00 11:57:24 SQL> commit; Commit complete. Elapsed: 00:00:00.00 11:57:28 SQL> --注意這個時間，是Commit完成時間 11:57:29 SQL> drop table test; Table dropped. Elapsed: 00:00:00.07 11:57:34 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 11:57:45 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production 3.恢複備份資料 保留當前日誌 D:\>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:58:04 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. 11:58:04 SQL> startup mount; ORACLE instance started. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 11:58:15 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. Elapsed: 00:00:00.00 11:58:17 SQL> recover database until time '2005-01-17 11:57:28'; Media recovery complete. recover database until time '2010-10-19 18:25:03'; --恢複到提交完成時刻 11:58:33 SQL> alter database open resetlogs; Database altered. Elapsed: 00:00:05.08 11:58:46 SQL> select * from test; no rows selected Elapsed: 00:00:00.00 --注意此時資料沒有被恢複。 --也就是說，落在了提交之前 4.第二個測試 D:\>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:48:50 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. 11:48:50 SQL> startup ORACLE instance started. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. 11:49:03 SQL> create table test (name varchar2(20)); Table created. Elapsed: 00:00:00.04 11:49:32 SQL> insert into test values('aaaaaaaaaaaaaaaaaaaa'); 1 row created. Elapsed: 00:00:00.00 11:49:32 SQL> insert into test values('bbbbbbbbbbbbbbbbbbbb'); 1 row created. Elapsed: 00:00:00.00 11:49:32 SQL> insert into test values('cccccccccccccccccccc'); 1 row created. Elapsed: 00:00:00.00 11:49:32 SQL> commit; Commit complete. Elapsed: 00:00:00.00 11:49:34 SQL> --注意這裡是提交時間 11:49:34 SQL> 11:49:35 SQL> --等待時間流逝一秒 11:49:36 SQL> 11:49:37 SQL> drop table test; Table dropped. Elapsed: 00:00:00.06 11:49:44 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 11:49:54 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production D:\>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:50:42 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. 11:50:42 SQL> startup mount; ORACLE instance started. Total System Global Area 101785428 bytes Fixed Size 454484 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. 11:50:59 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. Elapsed: 00:00:00.00 11:51:20 SQL> recover database until time '2005-01-17 11:49:35'; Media recovery complete. --此時恢複到提交一秒之後 11:51:22 SQL> alter database open resetlogs; Database altered. Elapsed: 00:00:03.09 11:51:32 SQL> select * from test; NAME -------------------- aaaaaaaaaaaaaaaaaaaa bbbbbbbbbbbbbbbbbbbb cccccccccccccccccccc Elapsed: 00:00:00.00 --資料得以恢複 結論: Oracle能夠恢複的時間精度為1秒，但是在Oracle資料庫內部，用以產生SCN的時間點有更為精確的精度。 所以，如果你指定秒級恢複，如11:57:28，那麼秒後的精度被置00，反而就落在了提交之前。（猜測） 而等待下一秒來到時，這種情況就不會出現了。

轉載自：http://hi.baidu.com/%C9%E1%B5%C30710/blog/item/874337532d2e2c010cf3e3b6.html