Incomplete Recovery - Time Base

SQL> create table test7 (no number, hiredate date) tablespace ts_c;

Table created.

SQL> insert into test7 values (1, sysdate);

1 row created.

SQL> insert into test7 values (2, sysdate);

1 row created.

SQL> insert into test7 values (3, sysdate);

1 row created.

SQL> insert into test7 values (4, sysdate);

1 row created.

SQL> insert into test7 values (5, sysdate);

1 row created.

#commit 하지 않은 insert data는 복구가 불가능하다.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE

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

2010-03-10:10:58:07

SQL> drop table test7;

Table dropped.

SQL> select * from test7;

select * from test7

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !mkdir /data/temp_time

SQL> !cp -av /data/backup/open/*.dbf /data/temp_time/

`/data/backup/open/example01.dbf' -> `/data/temp_time/example01.dbf'

`/data/backup/open/sysaux01.dbf' -> `/data/temp_time/sysaux01.dbf'

`/data/backup/open/system01.dbf' -> `/data/temp_time/system01.dbf'

`/data/backup/open/ts_a01.dbf' -> `/data/temp_time/ts_a01.dbf'

`/data/backup/open/ts_b01.dbf' -> `/data/temp_time/ts_b01.dbf'

`/data/backup/open/ts_c01.dbf' -> `/data/temp_time/ts_c01.dbf'

`/data/backup/open/undotbs01.dbf' -> `/data/temp_time/undotbs01.dbf'

`/data/backup/open/users01.dbf' -> `/data/temp_time/users01.dbf'

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             113247824 bytes

Database Buffers          167772160 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> select name from v$datafile;

NAME

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

/home/oracle/oradata/testdb/system01.dbf

/home/oracle/oradata/testdb/undotbs01.dbf

/home/oracle/oradata/testdb/sysaux01.dbf

/home/oracle/oradata/testdb/users01.dbf

/home/oracle/oradata/testdb/example01.dbf

/home/oracle/oradata/testdb/ts_a01.dbf

/home/oracle/oradata/testdb/ts_b01.dbf

/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/system01.dbf'

  3  to '/data/temp_time/system01.dbf';

Database altered.

SQL> 

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'

  3  to '/data/temp_time/sysaux01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'

  3  to '/data/temp_time/undotbs01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/users01.dbf'

  3  to '/data/temp_time/users01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/example01.dbf'

  3  to '/data/temp_time/example01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/ts_a01.dbf'

  3  to '/data/temp_time/ts_a01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/ts_b01.dbf'

  3  to '/data/temp_time/ts_b01.dbf';

Database altered.

SQL> alter database rename

  2  file '/home/oracle/oradata/testdb/ts_c01.dbf'

  3  to '/data/temp_time/ts_c01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME

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

/data/temp_time/system01.dbf

/data/temp_time/undotbs01.dbf

/data/temp_time/sysaux01.dbf

/data/temp_time/users01.dbf

/data/temp_time/example01.dbf

/data/temp_time/ts_a01.dbf

/data/temp_time/ts_b01.dbf

/data/temp_time/ts_c01.dbf

8 rows selected.

SQL> recover database until time '2010-03-10:10:58:07';

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select no, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from test7;

        NO HIREDATE

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

         1 2010-03-10:10:56:18

         2 2010-03-10:10:56:20

         3 2010-03-10:10:56:24

         4 2010-03-10:10:56:26

         5 2010-03-10:10:56:28

SQL> 

#실습문제 설명

10:01:00 insert into test7 values (1, sysdate);

10:02:00 insert into test7 values (2, sysdate);

10:03:00 insert into test7 values (3, sysdate);

10:03:30 commit;

10:04:00 insert into test7 values (4, sysdate);

10:05:00 insert into test7 values (5, sysdate);

recover database until time '2010-03-10:10:03:15';

-> commit; 이전으로 복구하는 부분이기 때문에 모든 데이터는 복구되지 않는다.

recover database until time '2010-03-10:10:03:45';

-> commot; 이전 1, 2, 3 데이터만 복구가 되며, commit; 이후 데이터는 복구되지 않는다.