Time Based Recovery

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

 

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> alter database archivelog;

 Database altered.

 

SQL> alter system set log_archive_start=true scope=spfile;

 System altered.

 

SQL> alter database open;

 Database altered.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

 

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> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

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

        1 file(s) copied.

 

SQL> startup;

ORACLE instance started.

 Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              79693180 bytes

Database Buffers           79691776 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

 

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

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

2010-06-17 14:43:23

 

SQL> drop table time_back;

 Table dropped.

 

SQL> drop table time_back2;

 Table dropped.

 

SQL> select * from time_back;

select * from time_back

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

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

        1 file(s) copied.

 

SQL> startup mount;

ORACLE instance started.

 Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              79693180 bytes

Database Buffers        79691776 bytes

Redo Buffers                7139328 bytes

Database mounted.

 

SQL> recover database until time '2010-06-17 15:13:53';

Media recovery complete.

 

SQL> alter database open resetlogs;

 Database altered.

 

SQL> select * from time_back;

 NAME

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

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

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

 

SQL> desc time_back2;

 Name                                      Null?    Type

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

  NAME                                               VARCHAR2(100)

Comments