Oracle基於時間點的恢復

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.
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.

--恢復到提交完成時刻

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

--資料得以恢復

11:51:48 SQL> drop table test;

Table dropped.

Elapsed: 00:00:00.09
11:54:40 SQL> shutdow immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
11:54:58 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

結論:
Oracle能夠恢復的時間精度為1秒，但是在Oracle資料庫內部，用以產生SCN的時間點有更為精確的精度。
所以，如果你指定秒級恢復，如11:57:28，那麼秒後的精度被置00，反而就落在了提交之前。（猜測）
而等待下一秒來到時，這種情況就不會出現了。