oracle 时间点,Oracle基于时间点的恢复技巧-CSDN博客

Oracle基于时间点的恢复能够精确到什么样的精度?

这是一个需要关心的问题。

以下测试用于进行一点说明。

1.首先做好冷备份

2.创建测试数据

D:>sqlplus “/ as sysdba”

SQL*Plus: Release 9.2.0.6.0 – Production on Mon Jan 17 11:56:43 2005

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

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

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

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，反而就落在了提交之前。(猜测)

而等待下一秒来到时，这种情况就不会出现了。

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget

官方微博

TechTarget中国