* 목표:
- CONTROL FILE 복원후 DB 정상 가동
- 사용자 데이터 파일 복원
- 로그맴버 재생성
* 시스템 상황
- 2개의 디스크를 사용중인데 1개의 디스크에서 I/O 에러가 발생
- 문제는 에러가 발생한 디스크에 user datafile 과 control file, 그리고 redo logfile 이 존재
- 다행이 현재 oracle 은 archive mode 이며 하루에 한번씩 hot backup 을 돌림
- control file 과 redo logfile 은 각각 4개, 2개(log member) 로 다중화 되어 운영중임
* 장애 복구 과정
1. control file 이 있는 디스크에 장애가 발생했기 때문에 checkpoint 가 일어날 경우 db 가 down 되기 때문에 먼저 control file 복구를 우선시함
2. 다행이 control file 이 다중화 되어 있기때문에 db 를 shutdown 후 정상적인 control file 을 복사해서 복원
SQL> shutdown immediate;
ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/backup/ORCL/control04.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@dbtest1 backup]$ cp /db/oradata/ORCL/control01.ctl /backup/ORCL/control04.ctl
SQL> startup
ORACLE instance started.
Total System Global Area 236000476 bytes
Fixed Size 451804 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
3. db 가 정상적으로 실행되었으나 사용자 tablespace 가 디스크 장애로 datafile 이 존재하지 않으므로 offline 됨.
아래와 같이 상태를 확인후 hot backup 또는 닫힌 백업 받은 데이터 파일과 백업 된 이후의 archive logfile 을 모두 복원후 recover tablespace 명령으로 복원.
모두 복원이 완료 되었으면 online 으로 전환
단 위의 과정은 모두 online 상태에서 가능
SQL>select h.tablespace_name, d.name, h.error, d.status, h.status from v$datafile d, v$datafile_header h where d.file#=h.file#
TABLESPACE_NAME NAME ERROR STATUS STATUS
-------------------------------------------------------- -------------- ------- -------
SYSTEM /db/oradata/ORCL/system01.dbf SYSTEM ONLINE
UNDOTBS1 /db/oradata/ORCL/undotbs01.dbf ONLINE ONLINE
CWMLITE /db/oradata/ORCL/cwmlite01.dbf ONLINE ONLINE
DRSYS /db/oradata/ORCL/drsys01.dbf ONLINE ONLINE
EXAMPLE /db/oradata/ORCL/example01.dbf ONLINE ONLINE
INDX /db/oradata/ORCL/indx01.dbf ONLINE ONLINE
ODM /db/oradata/ORCL/odm01.dbf ONLINE ONLINE
TOOLS /db/oradata/ORCL/tools01.dbf ONLINE ONLINE
USERS /db/oradata/ORCL/users01.dbf ONLINE ONLINE
XDB /db/oradata/ORCL/xdb01.dbf ONLINE ONLINE
EXAMPLE /db/oradata/ORCL/example02.dbf ONLINE ONLINE
TABLESPACE_NAME NAME ERROR STATUS STATUS
------------------ -------------------------------------- ------- ---------------------------------------------
SALE_DATA_TS /backup/df1.dbf FILE NOT FOUND RECOVER OFFLINE
SALE_DATA_TS /backup/df2.dbf FILE NOT FOUND RECOVER OFFLINE
SALE_DATA_TS /backup/df3.dbf FILE NOT FOUND RECOVER OFFLINE
SQL> recover tablespace SALE_DATA_TS;
Media recovery complete.
SQL> alter tablespace SALE_DATA_TS online;
Tablespace altered.
SQL>select h.tablespace_name, d.name, h.error, d.status, h.status from v$datafile d, v$datafile_header h where d.file#=h.file#
TABLESPACE_NAME NAME ERROR STATUS STATUS
-------------------------------------------------------- -------------- ------- -------
SYSTEM /db/oradata/ORCL/system01.dbf SYSTEM ONLINE
UNDOTBS1 /db/oradata/ORCL/undotbs01.dbf ONLINE ONLINE
CWMLITE /db/oradata/ORCL/cwmlite01.dbf ONLINE ONLINE
DRSYS /db/oradata/ORCL/drsys01.dbf ONLINE ONLINE
EXAMPLE /db/oradata/ORCL/example01.dbf ONLINE ONLINE
INDX /db/oradata/ORCL/indx01.dbf ONLINE ONLINE
ODM /db/oradata/ORCL/odm01.dbf ONLINE ONLINE
TOOLS /db/oradata/ORCL/tools01.dbf ONLINE ONLINE
USERS /db/oradata/ORCL/users01.dbf ONLINE ONLINE
XDB /db/oradata/ORCL/xdb01.dbf ONLINE ONLINE
EXAMPLE /db/oradata/ORCL/example02.dbf ONLINE ONLINE
TABLESPACE_NAME NAME ERROR STATUS STATUS
------------------ -------------------------------------- ------- -------
/backup/df1.dbf ONLINE ONLINE
/backup/df2.dbf ONLINE ONLINE
/backup/df3.dbf ONLINE ONLINE
4. 장애가 발생한 디스크에 로그그룹의 멤버들이 위치하였기에 로그파일 확인후 로그파일을 재생성해야 함
단 주의 사항은 CURRENT 되어있는 log member 는 drop 이 되지 않기때문에 log switch 를 일으킨 후에 drop 시켜야 함
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 STALE ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
1 INVALID ONLINE /backup/ORCL/redo01_02.log
2 INVALID ONLINE /backup/ORCL/redo02_02.log
3 INVALID ONLINE /backup/ORCL/redo03_02.log
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$logfile; // 로그파일이 존재하지 않기에 INVALID 상태가 됨
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
1 INVALID ONLINE /backup/ORCL/redo01_02.log
2 INVALID ONLINE /backup/ORCL/redo02_02.log
3 INVALID ONLINE /backup/ORCL/redo03_02.log
6 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
1 INVALID ONLINE /backup/ORCL/redo01_02.log
2 INVALID ONLINE /backup/ORCL/redo02_02.log
3 INVALID ONLINE /backup/ORCL/redo03_02.log
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 35 104857600 2 YES INACTIVE 1004167 03-OCT-06
2 1 36 104857600 2 NO CURRENT 1004169 03-OCT-06
3 1 34 104857600 2 YES INACTIVE 1004164 03-OCT-06
SQL> alter database drop logfile member '/backup/ORCL/redo01_02.log';
Database altered.
SQL> alter database drop logfile member '/backup/ORCL/redo03_02.log';
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
2 INVALID ONLINE /backup/ORCL/redo02_02.log
SQL> alter database drop logfile member '/backup/ORCL/redo02_02.log';
alter database drop logfile member '/backup/ORCL/redo02_02.log'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/db/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/backup/ORCL/redo02_02.log'
// 현재 current logfile 이기때문에 drop 이 안됨
아래와 같이 log switch 를 일으킨후에 drop 하면 정상적으로 drop 됨
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 35 104857600 1 YES INACTIVE 1004167 03-OCT-06
2 1 36 104857600 2 YES ACTIVE 1004169 03-OCT-06
3 1 37 104857600 1 NO CURRENT 1004460 03-OCT-06
SQL> alter database drop logfile member '/backup/ORCL/redo02_02.log';
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 35 104857600 1 YES INACTIVE 1004167 03-OCT-06
2 1 36 104857600 1 YES ACTIVE 1004169 03-OCT-06
3 1 37 104857600 1 NO CURRENT 1004460 03-OCT-06
SQL> alter system switch logfile;
System altered.
SQL> alter database add logfile member '/backup/ORCL/redo01_02.log' to group 1;
Database altered.
SQL> alter database add logfile member '/backup/ORCL/redo02_02.log' to group 2;
Database altered.
SQL> alter database add logfile member '/backup/ORCL/redo03_02.log' to group 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
1 INVALID ONLINE /backup/ORCL/redo01_02.log
2 INVALID ONLINE /backup/ORCL/redo02_02.log
3 INVALID ONLINE /backup/ORCL/redo03_02.log
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
select * from v$logfile
System altered.
SQL>
System altered.
SQL> ;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /db/oradata/ORCL/redo03.log
2 ONLINE /db/oradata/ORCL/redo02.log
1 ONLINE /db/oradata/ORCL/redo01.log
1 ONLINE /backup/ORCL/redo01_02.log
2 ONLINE /backup/ORCL/redo02_02.log
3 ONLINE /backup/ORCL/redo03_02.log
6 rows selected.
|
