Incomplete Recovery - Time Base 연습문제 2

drop user howkey cascade; 로 사용자를 삭제하고 select로 조회하면 jmember, jumsu Table이 삭제된 것을 확인할수 있다. 사용자를 삭제하기 전 상태로 복구한다.

SQL> create user howkey

  2  identified by howkey

  3  default tablespace ts_a

  4  temporary tablespace temp

  5  quota unlimited on ts_a;

User created.

SQL> grant connect, resource to howkey;

Grant succeeded.

SQL> conn howkey/howkey;

Connected.

SQL> create table jmember

  2  (  id      varchar2(10),

  3     name    varchar2(10),

  4     tel     varchar2(10));

Table created.

SQL> create table jumsu

  2  (  sub_name        varchar2(10),

  3     sub_prof        varchar2(10),

  4     sub_jumsu       number);

Table created.

SQL> insert into jmember values('kara1', '박규리', '1111-1111');

1 row created.

SQL> insert into jmember values('kara2', '한승연', '2222-2222');

1 row created.

SQL> insert into jmember values('kara3', '정니콜', '3333-3333');

1 row created.

SQL> insert into jmember values('kara4', '구하라', '4444-4444');

1 row created.

SQL> insert into jmember values('kara5', '강지영', '5555-5555');

1 row created.

SQL> insert into jumsu values('사회', '강석호', 100);

1 row created.

SQL> insert into jumsu values('수학', '차기봉', 99);

1 row created.

SQL> insert into jumsu values('영어', '앤써니양', 96);

1 row created.

SQL> insert into jumsu values('국어', '이은유', 93);

1 row created.

SQL> insert into jumsu values('과학', '장영식', 90);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from jmember;

ID         NAME       TEL

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

kara1      박규리     1111-1111

kara2      한승연     2222-2222

kara3      정니콜     3333-3333

kara4      구하라     4444-4444

kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU

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

사회       강석호            100

수학       차기봉             99

영어       앤써니양           96

국어       이은유             93

과학       장영식             90

#recover database until time에 적용할 시간을 확인한다.

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

SYSDATE

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

2010-03-11:17:40:19

SQL> conn / as sysdba

Connected.

#연습문제 내용과 같이 cascade 옵션을 사용해 howkey 계정을 삭제한다.

SQL> drop user howkey cascade;

User dropped.

#위에서 howkey 계정을 삭제했기 때문에 접속할 수 없다.

SQL> conn howkey/howkey

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

#복구를 위해 shutdown 한다.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

#복구를 위해 Backup 받아놓은 Data file만 복사한다.

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

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

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

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

`/data/backup/cr_close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'

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

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

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

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

`/data/backup/cr_close/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             100664912 bytes

Database Buffers          180355072 bytes

Redo Buffers                2973696 bytes

Database mounted.

#위에서 확인해 두었던 시간을 적용해 recover database until time 명령어를 실행하고 DB를 open한다.

SQL> recover database until time '2010-03-11:17:40:19';

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

#복구 여부를 확인하기 위해 삭제했던 howkey 계정으로 접속하고 select 문을 이용해 Data를 확인한다.

SQL> conn howkey/howkey

Connected.

SQL> select * from jmember;

ID         NAME       TEL

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

kara1      박규리     1111-1111

kara2      한승연     2222-2222

kara3      정니콜     3333-3333

kara4      구하라     4444-4444

kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU

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

사회       강석호            100

수학       차기봉             99

영어       앤써니양           96

국어       이은유             93

과학       장영식             90

SQL>