Incomplete Recovery - Time Base 연습문제 1

update 실수! update 이전 시점으로 복구하기

SQL> create table member

  2  (  no              number,

  3     name            varchar2(10),

  4     address         varchar2(10),

  5     hiredate        date)

  6  tablespace ts_c;

Table created.

SQL> insert into member values(1, '박규리', '서울', sysdate);

1 row created.

SQL> insert into member values(2, '한승연', '서울', sysdate);

1 row created.

SQL> insert into member values(3, '정니콜', '미국', sysdate);

1 row created.

SQL> insert into member values(4, '구하라', '광주', sysdate);

1 row created.

SQL> insert into member values(5, '강지영', '경기도', sysdate);

1 row created.

SQL> commit;

Commit complete.

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

        NO NAME       ADDRESS    HIREDATE

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

         1 박규리     서울       2010-03-10:11:45:24

         2 한승연     서울       2010-03-10:11:45:28

         3 정니콜     미국       2010-03-10:11:45:31

         4 구하라     광주       2010-03-10:11:45:34

         5 강지영     경기도     2010-03-10:11:45:36

SQL> update member set address='대한민국';

5 rows updated.

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

        NO NAME       ADDRESS    HIREDATE

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

         1 박규리     대한민국   2010-03-10:11:45:24

         2 한승연     대한민국   2010-03-10:11:45:28

         3 정니콜     대한민국   2010-03-10:11:45:31

         4 구하라     대한민국   2010-03-10:11:45:34

         5 강지영     대한민국   2010-03-10:11:45:36

SQL> commit;

Commit complete.

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

SYSDATE

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

2010-03-10:11:57:33

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp -av /data/backup/open/*.dbf /home/oracle/oradata/testdb/

`/data/backup/open/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'

`/data/backup/open/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'

`/data/backup/open/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'

`/data/backup/open/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'

`/data/backup/open/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'

`/data/backup/open/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'

`/data/backup/open/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'

`/data/backup/open/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             121636432 bytes

Database Buffers          159383552 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> recover database until time '2010-03-10:11:50:00';

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

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

        NO NAME       ADDRESS    HIREDATE

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

         1 박규리     서울       2010-03-10:11:45:24

         2 한승연     서울       2010-03-10:11:45:28

         3 정니콜     미국       2010-03-10:11:45:31

         4 구하라     광주       2010-03-10:11:45:34

         5 강지영     경기도     2010-03-10:11:45:36

SQL>