« Windows Xp中如何设置自动登录 | Blog首页 | 使用dbms_rectifier_diff解决高级复制中的数据冲突问题 »
Oracle基于时间点的恢复
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2005/01/oracleuoueaeaae.html
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> 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
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 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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
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 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
Oracle能够恢复的时间精度为1秒，但是在Oracle数据库内部，用以产生SCN的时间点有更为精确的精度。
所以，如果你指定秒级恢复，如11:57:28，那么秒后的精度被置00，反而就落在了提交之前。（猜测）
而等待下一秒来到时，这种情况就不会出现了。
这篇 【Oracle基于时间点的恢复】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2005-01-17 15:38 | Comments (13) | Posted to Backup&Recovery | Edit |Pageviews:
留言 (13)
我试验了怎么不能恢复呀。上面提到的冷备份该什么时候作？
Posted by: ggg at March 7, 2005 4:47 PM
在进行实验的DML操作之前进行冷备份。
注意你的步骤，肯定是没有问题的。
Posted by: eygle at March 7, 2005 4:56 PM
试验了怎么不能恢复呀。(我的 环境在 vwindows9.2.0.2.0)
还有 sql的 前面的 时间时 如何出现的
11:51:32 SQL> select * from test;
Posted by: liyihongcug at July 29, 2005 8:39 AM
set time on
Posted by: eygle at July 29, 2005 9:00 AM
试过了，是这样的。
Posted by: ignu at July 29, 2005 2:51 PM
试过了，是这样的。
Posted by: ignu at July 29, 2005 2:51 PM
为什么还是不能还原？总是提示表或视图不存在。
（windows2000+9.2.0.1.0,运行在归档方式，自动存档启动）
Posted by: idiomson at October 18, 2006 10:35 PM
表或视图不存在,说明你的恢复有问题。
你可能没有从备份中恢复，或者恢复的redo应用的过多或不够。
Posted by: eygle at October 19, 2006 8:46 AM
ryryrryr
Posted by: fdgdgf at February 1, 2007 11:18 AM
问题同ignu提出！
Version 9.2.0.1.0
是不是该版本不支持基于时间点的恢复！
Posted by: george_ding at February 6, 2007 2:07 PM
我试了一下也不行 ，因为我省略了
1.首先做好冷备份和3.恢复备份数据。
冷备份是在create table test (name varchar2(20));就已经做了的，问题是test表所在数据文件是没问题的， 我只是想恢复到历史的某一时刻，为什么非得要冷备份的数据文件，还请解释一下。
Posted by: zcy258 at September 13, 2007 3:22 PM
顶，数据文件非要？OK??
Posted by: jak_lee at October 29, 2007 6:32 PM
本人也按照作者的步骤试过，但是没成功，最后发现少一步，具体见星号里的内容：
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> 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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
***************************************************
此处将冷备份的数据拷贝回原先的目录下。
记住每次再在这个测试时都要做冷备份，不要用原先做好的冷备份，如果使用，下面数据库将无法启动，本人就遇到这样的情况。
*******************************************************
11:50:42 SSQL> 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
Posted by: Anonymous at June 3, 2008 8:25 PM
