Incomplete Recovery - using backup controlfile 연습문제 #2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 12. 02:25
#시나리오
A. Data file, Control file을 open Backup 한다.
B. 새로운 ts_d Tablespace를 생성한다.
C. Tablespace ts_d에 scott.test Table을 생성하고 Data를 입력한다.
D. ts_d Tablespace를 삭제한다. rm 또는 drop tablespace 동일하다.
　 ※ ts_d Tablespace는 Backup 받은 Data file이 없다.

#연습문제
D에서 삭제된 ts_d Talespace를 복구한다.


#현재 Data file을 확인한다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#모든 Data file을 Open Backup 한다.
SQL> alter tablespace system begin backup;

Tablespace altered.

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

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

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

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

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

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

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

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

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

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace ts_a begin backup;

Tablespace altered.

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

SQL> alter tablespace ts_a end backup;

Tablespace altered.

SQL> alter tablespace ts_b begin backup;

Tablespace altered.

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

SQL> alter tablespace ts_b end backup;

Tablespace altered.

SQL> alter tablespace ts_c begin backup;

Tablespace altered.

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

SQL> alter tablespace ts_c end backup;

Tablespace altered.

#Data file을 Open Backup 했다면, 중요한 Control file 도 Backup 한다.
SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#시나리오와 같이 ts_d Tablespace를 생성한다.
SQL> create tablespace ts_d datafile '/home/oracle/oradata/testdb/ts_d01.dbf' size 5m;

Tablespace created.

#ts_d Tablespace에 test Table을 생성하고 Data를 입력한다.
SQL> create table scott.test(no number) tablespace ts_d;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2
         3

#Data까지 입력하고 ts_d Tablespace를 삭제하기 전에 복구를 위해 현재 시간을 확인한다.
#rm 이 아닌 drop tablespace ts_d including contents and datafiles; 를 사용할 경우 alter_testdb.log 파일에서 drop tablespace ts_d including contents and datafiles 했던 시간을 확인할 수 있다.
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:02:02:12

#ts_d Tablespace를 삭제하고 삭제 되었는지 확인한다.
SQL> !rm /home/oracle/oradata/testdb/ts_d01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*.dbf
/home/oracle/oradata/testdb/ts_a01.dbf  /home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf

#Archive log를 만들기 위해 로그 스위치를 몇 차례 발생시킨다.
#주의사항 : 실습 또는 연습문제에서 로그 스위치를 발생시키지 않을 경우 Archive log가 생성되지 않아 복구할 수 없다.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

SYSDATE
-------------------
2010-03-12:02:04:43

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir /home/oracle/oradata/testdb/temp

#복구시 실수로 인해 기존 DB까지 사용하지 못할 수 있다. 따라서 현재의 원본 Data file, Redo log file, Control file을 /data/backup/temp 디렉토리를 생성하고 Backup 받는다.
SQL> !mkdir /data/backup/temp

SQL> !cp -av /home/oracle/oradata/testdb/* /data/backup/temp/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/temp/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/temp/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/temp/control03.ctl'
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/temp/example01.dbf'
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/temp/redo03.log'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/temp/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/temp/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/temp/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/temp/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/temp/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/temp/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/temp/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/temp/users01.dbf'

#원본 Data file, Redo log file, Control file을 Backup 받은 후 이전에 Backup 받아놓은 Data file을 복원한다.
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'

#이전에 Backup 받아놓은 Control file을 복원한다.
SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control01.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control02.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control03.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

#복구를 위해 mount 단계로 시작한다.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> 
#ts_d Tablespace에 문제(삭제)가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;
ORA-00279: change 505394 generated at 03/12/2010 01:55:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 505394 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'


ORA-01112: media recovery not started

#ts_d01.dbf가 존재하지 않아 오류가 발생한다. 따라서 ts_d01.dbf를 복원해야 한다. 아래와 같이 현재 Data file을 재 확인하면 새로운 Data file("/home/oracle/product/10g/dbs/UNNAMED00009) 목록이 확인된다.
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
/home/oracle/product/10g/dbs/UNNAMED00009

9 rows selected.

#기존 Data file인 ts_d01.dbf를 복원하기 위해 아래와 같이 실행한다.
SQL> alter database create datafile 'UNNAMED00009' as '/home/oracle/oradata/testdb/ts_d01.dbf';

Database altered.

#위 과정을 통해 현재 Data file을 재 확인해 보면 ts_d01.dbf 파일로 변경된 것을 확인할 수 있다.
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
/home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

#ts_d01.dbf가 복원 되었으니 다시한번 ts_d Tablespace에 문제가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;               
ORA-00279: change 508678 generated at 03/12/2010 02:00:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 508678 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
#복구가 완료된 후 resetlogs 옵션을 이용해 open 단계로 변경한다.
SQL> alter database open resetlogs;

Database altered.

#select 문을 이용해 복구 여부를 확인한다.
SQL> select * from scott.test;

        NO
----------
         1
         2
         3

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #1  (0) 2010.03.15
Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10

Incomplete Recovery - using backup controlfile

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 11. 12:36
실습 순서
1. Tablespace 생성 후 DB 전체를 Backup 한다.
2. test 테이블을 1번에서 생성한 tablespace에 생성한다.
3. drop tablespace로 1번에서 생성한 tablespace를 삭제한다.
4. 로그스위치를 수차례 발생시킨다.
5. 1번 과정에서 Backup 받았던 Data files과 Control files를 복원한다.
6. alert_testdb.log 파일에서 3번 작업을 수행했던 시간을 확인한다.
7. DB를 mount 단계로 시작한다.
8. recover database until time 'YYYY-MM-DD:HH24:MI:SS' using backup controlfile; 실행한다.
9. 8번 실행 후 alter database open resetlogs; 실행한다.
10. 복구가 완료 되었는지 확인한다.

SQL> create table scott.test(no number) tablespace ts_c;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2

SQL> drop tablespace ts_c including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

#복구 중 실수로 문제가 발생한 DB 이외에 기존 DB 까지 사용하지 못하는 경우가 발생될 수 있다.
#따라서 문제가 발생한 Data file까지 포함해 모든 Data file, Redo log file, Control file까지 Backup을 받아놓는다.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/temp

#위와 같이 Data file, Redo log file, Control file까지 Backup을 받아놓은 후 아래와 같이 복구를 시작한다.
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> !cp -av /data/backup/cr_close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/cr_close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/cr_close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/cr_close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.

#alert_testdb.log 에서 drop tablespace ts_c including contents and datafiles 작업 수행 시간을 확인한다.
#/home/oracle/admin/testdb/bdump/alert_testdb.log 내용 중 일부
Thu Mar 11 12:27:55 2010
drop tablespace ts_c including contents and datafiles
Thu Mar 11 12:27:58 2010
Deleted file /home/oracle/oradata/testdb/ts_c01.dbf
Completed: drop tablespace ts_c including contents and datafiles

SQL> recover database until time '2010-03-11:12:27:50' using backup controlfile;
ORA-00279: change 502969 generated at 03/11/2010 11:14:17 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 502969 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test;

        NO
----------
         1
         2

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Control file 장애  (0) 2010.03.15
Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08

Incomplete Recovery - Time Base 연습문제 2

Oracle 10g/10g - 연습문제
posted by I유령I 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

티스토리툴바