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> With the Partitioning, OLAP and Oracle Data Mining options
JServer> 3.恢复备份数据
保留当前日志
D:\>sqlplus “/ as sysdba”
SQL*Plus:> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an> 11:58:04 SQL> startup mount;
ORACLE instance started.
Total System Global Area 101785428 bytes
Fixed>
Variable> Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
11:58:15 SQL>>
Session> 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>>
Database> 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:> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an> 11:48:50 SQL> startup
ORACLE instance started.
Total System Global Area 101785428 bytes
Fixed>
Variable> 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> With the Partitioning, OLAP and Oracle Data Mining options
JServer> D:\>sqlplus “/ as sysdba”
SQL*Plus:> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an> 11:50:42 SQL> startup mount;
ORACLE instance started.
Total System Global Area 101785428 bytes
Fixed>
Variable> Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
11:50:59 SQL>>
Session> 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>>
Database> 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> With the Partitioning, OLAP and Oracle Data Mining options
JServer> 结论:
Oracle能够恢复的时间精度为1秒，但是在Oracle数据库内部，用以产生SCN的时间点有更为精确的精度。
所以，如果你指定秒级恢复，如11:57:28，那么秒后的精度被置00，反而就落在了提交之前。（猜测）
而等待下一秒来到时，这种情况就不会出现了。
页:
[1]