Incomplete Recovery (Time Based)

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL> create table time_back(name varchar(100)) tablespace users;

Table created.

SQL> insert into time_back values('******* 1st *******');

1 row created.

SQL> insert into time_back values('******* 2nd *******');

1 row created.

SQL> commit;

Commit complete.

SQL> alter database begin backup;

Database altered.

C:\>copy c:\oracle\product\10.2.0\oradata\dba20\* "c:\Hot Backup"

c:\oracle\product\10.2.0\oradata\dba20\CONTROL01.CTL

c:\oracle\product\10.2.0\oradata\dba20\CONTROL02.CTL

c:\oracle\product\10.2.0\oradata\dba20\CONTROL03.CTL

c:\oracle\product\10.2.0\oradata\dba20\REDO01.LOG

c:\oracle\product\10.2.0\oradata\dba20\REDO02.LOG

c:\oracle\product\10.2.0\oradata\dba20\REDO03.LOG

c:\oracle\product\10.2.0\oradata\dba20\SYSAUX01.DBF

c:\oracle\product\10.2.0\oradata\dba20\SYSTEM01.DBF

c:\oracle\product\10.2.0\oradata\dba20\TEMP01.DBF

c:\oracle\product\10.2.0\oradata\dba20\TEST01.DBF

c:\oracle\product\10.2.0\oradata\dba20\UNDOTBS01.DBF

c:\oracle\product\10.2.0\oradata\dba20\USERS01.DBF

12 file(s) copied.

SQL> alter database end backup;

Database altered.

SQL> create table time_back2(name varchar(100)) tablespace users;

Table created.

SQL> desc time_back2;

Name Null? Type

----------------------------------------- -------- --------------------

NAME VARCHAR2(100)

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

TO_CHAR(SYSDATE,'YY

-------------------

2009-02-12 14:54:42

SQL> drop table time_back;

Table dropped.

SQL> drop table time_back2;

Table dropped.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Delete Data Files.

SQL> startup;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247876 bytes

Variable Size 75498876 bytes

Database Buffers 83886080 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF'

C:\>copy /Y "c:\Hot Backup\*.DBF" c:\oracle\product\10.2.0\oradata\dba20\

c:\Hot Backup\SYSAUX01.DBF

c:\Hot Backup\SYSTEM01.DBF

c:\Hot Backup\TEMP01.DBF

c:\Hot Backup\TEST01.DBF

c:\Hot Backup\UNDOTBS01.DBF

c:\Hot Backup\USERS01.DBF

6 file(s) copied.

SQL> recover database until time '2009-02-12 14:54:42’;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from time_back;

NAME

---------------------------------------------------------------------

******* 1st *******

******* 2nd *******

SQL> desc time_back2;

Name Null? Type

----------------------------------------- -------- -----------------

NAME VARCHAR2(100)