[RAC] Offline되는 tablespace 장애발생 (복구내용이 redo log file에 있는경우)

Offline되는 tablespace 장애발생 (복구내용이 redo log file에 있는경우)

① 테스트용 tablespace생성 후 백업

② 생성한 tablespace에 데이터입력

③ 장애발생 : 해당 data file 초기화

④ 백업해둔 data file 복원 후 복구

step 1. tablespace를 생성할 여분의 장치 확인

SQL> set sqlprompt "_USER>"

--테이블스페이스 확인
SYS>@dd

TABLESPACE_NAME                        MB FILE_NAME
------------------------------ ---------- ---------------------------------------------
SYSTEM                                599 /dev/raw/raw6
UNDOTBS1                              199 /dev/raw/raw8
SYSAUX                                299 /dev/raw/raw7
UNDOTBS2                              199 /dev/raw/raw9
USERS                                   5 /dev/raw/raw10

--root계정으로 raw device 확인
[oracle@rac1 ~]$ su -
Password:

[root@rac1 ~]# lvscan
  ACTIVE            '/dev/rac/ocr1' [300.00 MB] inherit
  ACTIVE            '/dev/rac/ocr2' [300.00 MB] inherit
  ACTIVE            '/dev/rac/vote1' [300.00 MB] inherit
  ACTIVE            '/dev/rac/vote2' [300.00 MB] inherit
  ACTIVE            '/dev/rac/vote3' [300.00 MB] inherit
  ACTIVE            '/dev/rac/system' [600.00 MB] inherit
  ACTIVE            '/dev/rac/sysaux' [300.00 MB] inherit
  ACTIVE            '/dev/rac/undotbs1' [200.00 MB] inherit
  ACTIVE            '/dev/rac/undotbs2' [200.00 MB] inherit
  ACTIVE            '/dev/rac/users' [12.00 MB] inherit
  ACTIVE            '/dev/rac/temp' [100.00 MB] inherit
  ACTIVE            '/dev/rac/control01' [52.00 MB] inherit
  ACTIVE            '/dev/rac/control02' [52.00 MB] inherit
  ACTIVE            '/dev/rac/control03' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo01_a' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo01_b' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo02_a' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo02_b' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo03_a' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo03_b' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo04_a' [52.00 MB] inherit
  ACTIVE            '/dev/rac/redo04_b' [52.00 MB] inherit
  ACTIVE            '/dev/rac/spfile' [52.00 MB] inherit
  ACTIVE            '/dev/rac/example' [100.00 MB] inherit
  ACTIVE            '/dev/rac/data1' [100.00 MB] inherit
  ACTIVE            '/dev/rac/data2' [100.00 MB] inherit
  ACTIVE            '/dev/rac/data3' [100.00 MB] inherit

--해당 device 확인
[root@rac1 ~]# cat /etc/sysconfig/rawdevices
# This file and interface are deprecated.
# Applications needing raw device access should open regular
# block devices with O_DIRECT.
# raw device bindings
# format:    
#           
# example: /dev/raw/raw1 /dev/sda1
#          /dev/raw/raw2 8 5

/dev/raw/raw1   /dev/rac/ocr1
/dev/raw/raw2   /dev/rac/ocr2
/dev/raw/raw3   /dev/rac/vote1
/dev/raw/raw4   /dev/rac/vote2
/dev/raw/raw5   /dev/rac/vote3
/dev/raw/raw6   /dev/rac/system
/dev/raw/raw7   /dev/rac/sysaux
/dev/raw/raw8   /dev/rac/undotbs1
/dev/raw/raw9   /dev/rac/undotbs2
/dev/raw/raw10  /dev/rac/users
/dev/raw/raw11  /dev/rac/temp
/dev/raw/raw12  /dev/rac/control01
/dev/raw/raw13  /dev/rac/control02
/dev/raw/raw14  /dev/rac/control03
/dev/raw/raw15  /dev/rac/redo01_a
/dev/raw/raw16  /dev/rac/redo01_b
/dev/raw/raw17  /dev/rac/redo02_a
/dev/raw/raw18  /dev/rac/redo02_b
/dev/raw/raw19  /dev/rac/redo03_a
/dev/raw/raw20  /dev/rac/redo03_b
/dev/raw/raw21  /dev/rac/redo04_a
/dev/raw/raw22  /dev/rac/redo04_b
/dev/raw/raw23  /dev/rac/spfile
/dev/raw/raw24  /dev/rac/example
/dev/raw/raw25  /dev/rac/data1
/dev/raw/raw26  /dev/rac/data2
/dev/raw/raw27  /dev/rac/data3

step 2. 여분으로 생성해 두었던 디바이스에 tablespace 생성 

--여분으로 생성했던 /dev/raw/raw25에 ts_new tablespace 생성
SYS>create tablespace ts_new datafile '/dev/raw/raw25';
Tablespace created.

SYS>@dd

TABLESPACE_NAME                        MB FILE_NAME
------------------------------ ---------- ---------------------------------------------
SYSTEM                                599 /dev/raw/raw6
UNDOTBS1                              199 /dev/raw/raw8
SYSAUX                                299 /dev/raw/raw7
UNDOTBS2                              199 /dev/raw/raw9
USERS                                   5 /dev/raw/raw10
TS_NEW                         99.9921875 /dev/raw/raw25

6 rows selected.

step 3. 생성한 tablespace 백업 수행 (begin backup)

--ts_new tablespace 백업수행 (open backup)
SYS>alter tablespace ts_new begin backup;
Tablespace altered.

SYS>!dd if=/dev/raw/raw25 of=/home/oracle/backup/open/raw25_ts_new bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다

SYS>alter tablespace ts_new end backup;
Tablespace altered.

step 4. 생성한 tablespace에 데이터 입력

--생성한 tablespace에 데이터 입력
SYS>create table scott.tt100 (no number) tablespace ts_new;

Table created.

SYS>insert into scott.tt100 values (1);

1 row created.

SYS>commit;

Commit complete.

SYS>select * from scott.tt100;

        NO
----------
         1

step 5. 해당 로우디바이스를 초기화 시켜 장애발생시킴

--dd명령으로 해당 로우디바이스 초기화 (장애발생)
SYS>!dd if=/dev/zero of=/dev/raw/raw25 bs=8k
dd: writing `/dev/raw/raw25': 장치에 남은 공간이 없음
12801+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다

SYS>alter tablespace ts_new offline immediate;

Tablespace altered.

SYS>alter tablespace ts_new online;
alter tablespace ts_new online
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/dev/raw/raw25'
ORA-01251: Unknown File Header Version read for file number 6

SYS>select * from scott.tt100;
select * from scott.tt100
                    *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/dev/raw/raw25'

step 6. 백업해둔 data file 복원 후 복구

--백업해둔 data file없이 복구 -> 파일이 없어서 에러
SYS>recover tablespace ts_new;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 6: '/dev/raw/raw25'
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/dev/raw/raw25'
ORA-01251: Unknown File Header Version read for file number 6

--백업해둔 data file 복원 후 복구
--redo log에 복구할 내역이 있었기 때문에 별도로 archive log file 사용하지 않아도됨
SYS>!dd if=/home/oracle/backup/open/raw25_ts_new of=/dev/raw/raw25 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다

SYS>recover tablespace ts_new;
Media recovery complete.

SYS>alter tablespace ts_new online;
Tablespace altered.

SYS>select * from scott.tt100;

        NO
----------
         1