Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>Oracle基于时间点的恢复

Oracle基于时间点的恢复

时间：2010-10-24 来源：gjbxx110

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

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

--数据得以恢复

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