Tablespace 실습 : 백업파일이 없는 경우 복구하기

 

★ 장애상황 만들기

 

SQL> create tablespace haksa <- haksa 테이블스페이스 10m로 생성
  2  datafile '/home/oracle/oradata/testdb/haksa01.dbf' size 10m;

Tablespace created.

 

SQL> @dd

TABLESPACE         MB FILE_NAME
---------- ---------- --------------------------------------------------
UNDO               10 /home/oracle/oradata/testdb/undo01.dbf
HAKSA              10 /home/oracle/oradata/testdb/haksa01.dbf
TEST               10 /home/oracle/oradata/testdb/test01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf
USERS               5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            250 /home/oracle/oradata/testdb/sysaux01.dbf
SYSTEM            450 /home/oracle/oradata/testdb/system01.dbf

7 rows selected.

 

SQL> !rm -rf /home/oracle/oradata/testdb/haksa01.dbf <- 삭제

 

SQL> desc student;
ERROR:
ORA-04043: object student does not exist

SQL> create table student (no number,name varchar2(20)) tablespace haksa;

Table created.

 

SQL> insert into student values(1,'aaa');

1 row created.

 

SQL> insert into student values(2,'bbb');

1 row created.

 

SQL> commit;

Commit complete.

 

 

★ 장애 발생 확인

 

SQL> alter tablespace haksa offline;

Tablespace altered.

 

SQL> alter tablespace haksa online;
alter tablespace haksa online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/oradata/testdb/haksa01.dbf'

 

★ 기존 컨트롤 파일에 기록된 haksa tablespace 를 Data file 을 생성해서 복구함니다

SQL> alter database create datafile '/home/oracle/oradata/testdb/haksa01.dbf'

(윗쪽 경로가 control file 에 기록된 경로)

as '/home/oracle/oradata/testdb/haksa01.dbf';

as 다음이 이쪽위치에다가 Data file 을 생성하는 경로 지정

Database altered.

 

SQL> recover tablespace haksa; <- haksa 복구
Media recovery complete.

SQL> alter tablespace haksa online; <- haksa online으로 설정

Tablespace altered.