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)

Comments