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 |