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> 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 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 bytesFixed Size 454484 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase 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.0011: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.0811: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> 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 - 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 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--------------------aaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
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转载于:https://www.cnblogs.com/wuhenke/archive/2010/10/28/1863580.html
相关资源：数据结构—成绩单生成器