2010. 3. 10. 12:11
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>
'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글
| Incomplete Recovery - using backup controlfile 연습문제 #2 (0) | 2010.03.12 |
|---|---|
| Incomplete Recovery - using backup controlfile 연습문제 #1 (0) | 2010.03.11 |
| Incomplete Recovery - Time Base 연습문제 1 (0) | 2010.03.10 |
| Complete Recovery (다른 경로에 응급 복구) (0) | 2010.03.10 |
| Complete Recovery 연습 문제 (0) | 2010.03.09 |