Oracle基于时间点的恢复能够精确到什么样的精度?
这是一个需要关心的问题。以下测试用于进行一点说明。
1.首先做好冷备份
2.创建测试数据D:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:56:43 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.11:56:44 SQL> startupORACLE instance started.Total System Global Area 101785428 bytesFixed Size 454484 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.11:57:01 SQL> create table test (name varchar2(20));Table created.Elapsed: 00:00:00.0411:57:23 SQL> insert into test values('aaaaaaaaaaaaaaaaaaaa');1 row created.Elapsed: 00:00:00.0011:57:23 SQL> insert into test values('bbbbbbbbbbbbbbbbbbbb');1 row created.Elapsed: 00:00:00.0011:57:23 SQL> insert into test values('cccccccccccccccccccc');1 row created.Elapsed: 00:00:00.0011:57:24 SQL> commit;Commit complete.Elapsed: 00:00:00.0011:57:28 SQL>--注意这个时间，是Commit完成时间11:57:29 SQL> drop table test;Table dropped.Elapsed: 00:00:00.0711:57:34 SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.11:57:45 SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer 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 2005Copyright (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 bytesFixed Size 454484 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.11:58:15 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.Elapsed: 00:00:00.0011: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.0811:58:46 SQL> select * from test;no rows selectedElapsed: 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 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.11:48:50 SQL> startupORACLE instance started.Total System Global Area 101785428 bytesFixed Size 454484 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.11:49:03 SQL> create table test (name varchar2(20));Table created.Elapsed: 00:00:00.0411:49:32 SQL> insert into test values('aaaaaaaaaaaaaaaaaaaa');1 row created.Elapsed: 00:00:00.0011:49:32 SQL> insert into test values('bbbbbbbbbbbbbbbbbbbb');1 row created.Elapsed: 00:00:00.0011:49:32 SQL> insert into test values('cccccccccccccccccccc');1 row created.Elapsed: 00:00:00.0011:49:32 SQL> commit;Commit complete.Elapsed: 00:00:00.0011: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.0611:49:44 SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.11:49:54 SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionD:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jan 17 11:50:42 2005Copyright (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 bytesFixed Size 454484 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.11:50:59 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.Elapsed: 00:00:00.0011: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.0911:51:32 SQL> select * from test;NAME--------------------aaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccElapsed: 00:00:00.00--数据得以恢复11:51:48 SQL> drop table test;Table dropped.Elapsed: 00:00:00.0911:54:40 SQL> shutdow immediate;Database closed.Database dismounted.ORACLE instance shut down.11:54:58 SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production 结论:
Oracle能够恢复的时间精度为1秒，但是在Oracle数据库内部，用以产生SCN的时间点有更为精确的精度。 所以，如果你指定秒级恢复，如11:57:28，那么秒后的精度被置00，反而就落在了提交之前。（猜测）
而等待下一秒来到时，这种情况就不会出现了。