Oracle - 컨트롤파일 유실 장애 복구 시나리오

Control File 유실 장애 복구

장애 상황1 - Control file 유실

복구 방안 - 유실된 control file을 복사하여 추가 혹은 prameter file 에 유실된 control file 삭제

 

1. 현재 control file 확인

show parameter control_files 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /data01/ORCL/control01.ctl, /d
                                                 ata01/ORCL/control02.ctl, /dat
                                                 a01/ORCL/control03.ctl

select name from v$controlfile;
NAME
----------------------------------------
/data01/ORCL/control01.ctl
/data01/ORCL/control02.ctl
/data01/ORCL/control03.ctl

 

2. 데이터베이스 종료 후 컨트롤 파일 삭제

SQL> shutdown immediate

rm -rf /data01/ORCL/control03.ctl

 

3. 데이터베이스 start 및 장애 발생

SQL> startup 
ORACLE instance started.

Total System Global Area 1728050048 bytes
Fixed Size                  8897408 bytes
Variable Size             436207616 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7876608 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance;

STATUS
------------------------
STARTED

 

4. 장애 복구 - Control file 복사 및 데이터베이스 오픈

SQL> !cp /data01/ORCL/control01.ctl /data01/ORCL/control03.ctl

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN

 

4.1 장애복구 - parameter 파일 수정 

SQL> alter system set control_files='/data01/ORCL/control01.ctl','/data01/ORCL/control02.ctl' scope=spfile;

system altered.

SQL> shutdown immeidate;

SQL> startup
ORACLE instance started.

Total System Global Area 1728050048 bytes
Fixed Size                  8897408 bytes
Variable Size             436207616 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

 

5. alert log 파일 확인

cd /oracle/diag/rdbms/orcl/ORCL/trace

tail -f 20 alert_ORCL.log

 

장애상황2 - 모든 Control file 유실 

복구방안 - control file 재생성 스크립트를 이용해 복구 

 

1. control file 재생성 스크립트 - 최소 mount 단계 이상에서 실행 가능합니다.

SQL> alter database backup controlfile to trace as '/home/oracle/control.sql'

Database altered

SQL> shutdown immediate

 

2. 모든 컨트롤파일 삭제 및 데이터베이스 실행 

!rm -rf /data01/ORCL/*.ctl

SQL> startup
ORACLE instance started.

Total System Global Area 1728050048 bytes
Fixed Size                  8897408 bytes
Variable Size             436207616 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7876608 bytes
ORA-00205: error in identifying control file, check alert log for more info

 

3. 장애 복구 - 재생성 스크립트 실행

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data01/ORCL/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/data01/ORCL/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/data01/ORCL/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data01/ORCL/system01.dbf',
  '/data01/ORCL/sysaux01.dbf',
  '/data01/ORCL/undotbs01.dbf',
  '/data01/ORCL/users01.dbf',
  '/data01/ORCL/test.dbf',
  '/data01/ORCL/hr.dbf'
CHARACTER SET KO16MSWIN949
;


SQL> @/home/oracle/control

Control file created

SQL> alter database open;

Database altered

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL             OPEN