Oracle Restoration Based on Time points

Oracle Restoration Based on Time points

What precision can Oracle restore Based on Time points be accurate? This is a concern. The following tests are used to illustrate the problem. 1. First, make a cold backup 2. Create Test Data D: \> sqlplus "/As sysdba" SQL * Plus: Release 9.2.0.6.0-production on Mon Jan 17 11:56:43 2005 Copyright (c) 1982,200 2, 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 ('aaaaaaaaaaaaaaaaaaaaa '); 1 row created. Elapsed: 00:00:00. 00 11:57:23 SQL> insert into test values ('bbbbbbbbbbbbbbbbbbbbbbbbbb '); 1 row created. Elapsed: 00:00:00. 00 11:57:23 SQL> insert into test values ('cccccccccccccccccccccccccccccccc '); 1 row created. Elapsed: 00:00:00. 00 11:57:24 SQL> commit; Commit complete. Elapsed: 00:00:00. 00 11:57:28 SQL> -- Note that this time is the commit completion time. 11:57:29 SQL & gt; 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. Restore backup data Retain current log D: \> sqlplus "/As sysdba" SQL * Plus: Release 9.2.0.6.0-production on Mon Jan 17 11:58:04 2005 Copyright (c) 1982,200 2, 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. SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual; At 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 '2017-01-17 11:57:28 '; Media recovery complete. Recover database until time '2017-10-19 18:25:03 '; -- Restore to the submission completion time At 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 -- Note that the data is not recovered at this time. -- That is, before submission 4. Second test D: \> sqlplus "/As sysdba" SQL * Plus: Release 9.2.0.6.0-production on Mon Jan 17 11:48:50 2005 Copyright (c) 1982,200 2, 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 ('aaaaaaaaaaaaaaaaaaaaa '); 1 row created. Elapsed: 00:00:00. 00 11:49:32 SQL> insert into test values ('bbbbbbbbbbbbbbbbbbbbbbbbbb '); 1 row created. Elapsed: 00:00:00. 00 11:49:32 SQL> insert into test values ('cccccccccccccccccccccccccccccccc '); 1 row created. Elapsed: 00:00:00. 00 11:49:32 SQL> commit; Commit complete. Elapsed: 00:00:00. 00 11:49:34 SQL> -- Note that the submission time is 11:49:34 SQL> 11:49:35 SQL> -- Wait for one second 11:49:36 SQL> 11:49:37 SQL & gt; 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,200 2, 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. At 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 '2017-01-17 11:49:35 '; Media recovery complete. -- Restore to one second after submission At 11:51:22 SQL> alter database open resetlogs; Database altered. Elapsed: 00:00:03. 09 11:51:32 SQL> select * from test; Name -------------------- Aaaaaaaaaaaaaaaaaaaa Bbbbbbbbbbbbbbbbbbbbbb Cccccccccccccccccc Elapsed: 00:00:00. 00 -- Data Recovery Conclusion: The Time Precision of Oracle recovery is 1 second, but the time points used to generate SCN in the Oracle database are more accurate. Therefore, if you specify second-level recovery, for example, 11:57:28, the precision after seconds is set to 00, instead of before submission. (Guess) This will not happen when the next second comes.

Reprinted from: http://hi.baidu.com/%C9%E1%B5%C30710/blog/item/874337532d2e2c010cf3e3b6.html