Time based recovery

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 7 21:56:53 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE




SQL> select to_char(sysdate,'hh24:mi:ss') from dual;

TO_CHAR(
--------
21:58:49

SQL> drop table emp;

Table dropped.


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
SALGRADE                       TABLE
DEPT                           TABLE



SQL>

SQL> conn /as sysdba;
Connected.




SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL>

SQL>



SQL> recover database until time '2010-01-07 02:10:00';
ORA-00279: change 217495 generated at 01/04/2010 17:52:22 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 217495 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancle
ORA-00308: cannot open archived log 'cancle'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancle;
ORA-00308: cannot open archived log 'cancle;'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 237399 generated at 01/04/2010 19:49:54 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00002.001
ORA-00280: change 237399 for thread 1 is in sequence #2
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00001.001' no longer needed for
this recovery


ORA-00279: change 290539 generated at 01/05/2010 15:50:12 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00003.001
ORA-00280: change 290539 for thread 1 is in sequence #3
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00002.001' no longer needed for
this recovery


ORA-00279: change 314785 generated at 01/05/2010 16:11:01 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00004.001
ORA-00280: change 314785 for thread 1 is in sequence #4
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00003.001' no longer needed for
this recovery


ORA-00279: change 341547 generated at 01/05/2010 16:48:51 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00005.001
ORA-00280: change 341547 for thread 1 is in sequence #5
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00004.001' no longer needed for
this recovery


ORA-00279: change 418411 generated at 01/06/2010 12:39:02 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00006.001
ORA-00280: change 418411 for thread 1 is in sequence #6
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00005.001' no longer needed for
this recovery


ORA-00279: change 418869 generated at 01/06/2010 12:43:32 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00007.001
ORA-00280: change 418869 for thread 1 is in sequence #7
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00006.001' no longer needed for
this recovery


ORA-00279: change 418871 generated at 01/06/2010 12:43:36 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00008.001
ORA-00280: change 418871 for thread 1 is in sequence #8
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00007.001' no longer needed for
this recovery


ORA-00279: change 418875 generated at 01/06/2010 12:43:43 needed for thread 1
ORA-00289: suggestion : H:\ORACLE\ORA92\RDBMS\ARC00009.001
ORA-00280: change 418875 for thread 1 is in sequence #9
ORA-00278: log file 'H:\ORACLE\ORA92\RDBMS\ARC00008.001' no longer needed for
this recovery


ORA-00309: log belongs to wrong database
ORA-00334: archived log: 'H:\ORACLE\ORA92\RDBMS\ARC00009.001'


SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            h:\oracle\ora92\RDBMS
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1




SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL>

SQL>