[DB] oracle disk 스토리지 장애로 controlfile 관련 장애 처리 가이드

1. DISK 스토리지 장애로 controlfile 관련 장애 발생

**** ATTENTION: ****
The controlfile header block returned by the OS has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
Please do the following:
(1) Save all copies of the controlfile for later analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE'
(3) Unmount the instance.
(4) Use the script in the trace file to RE-CREATE THE CONTROLFILE and open the database.
**** -- ****

2. pfile로 DB를 기동하기 위해 pfile 생성

SQL> create pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora' from spfile;

3. Controlfile 을 trace 파일로 백업하기 위해 pfile로 DB mount 상태로 재기동

SQL> shut immediate;
SQL> startup mount pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora' 

4. Controlfile 재성성을 위해 trace 파일로 백업

SQL> alter database backup controlfile to trace;

5. Controlfile 백업한 trace 파일에서 controlfile 재생성 부분 추출

SQL> 
create controlfile reuse database "DBDB" noresetlogs archivelog
    maxlogfiles 192
    maxlogmembers 3
    maxdatafiles 1024
    maxInstances 32
    maxLoghistory 292
LOGFILE
GROUP 1 (
    '+DATA/DBDB/ONLINELOG/group_1.290.102934',
    '+DATA/DBDB/ONLINELOG/group_1.257.'656753'
) SIZE 2000M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/DBDB/ONLINELOG/group_2.291.1029453',
    '+DATA/DBDB/ONLINELOG/group_2.258.'656723'
) SIZE 2000M BLOCKSIZE 512
-- standby logfile
DATAFILE
    '+DATA/DBDB/DATAFILE/system.298.24891235',
    '+DATA/DBDB/DATAFILE/sysaux.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs1.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs2.298.24891235',
    '+DATA/DBDB/DATAFILE/users.298.24891235',
CHARACTER SET AL32UTF8
;

6. Trace 파일로 백업한 controlfile 을 적용하기 위해 nomount 상태로 재기동

SQL> shut immediate
SQL> startup nomount pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora';

7. Controlfile 추출한 내용 적용 및 DB open

-- create database 문 실행
SQL> 
create controlfile reuse database "DBDB" noresetlogs archivelog
    maxlogfiles 192
    maxlogmembers 3
    maxdatafiles 1024
    maxInstances 32
    maxLoghistory 292
LOGFILE
GROUP 1 (
    '+DATA/DBDB/ONLINELOG/group_1.290.102934',
    '+DATA/DBDB/ONLINELOG/group_1.257.'656753'
) SIZE 2000M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/DBDB/ONLINELOG/group_2.291.1029453',
    '+DATA/DBDB/ONLINELOG/group_2.258.'656723'
) SIZE 2000M BLOCKSIZE 512
-- standby logfile
DATAFILE
    '+DATA/DBDB/DATAFILE/system.298.24891235',
    '+DATA/DBDB/DATAFILE/sysaux.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs1.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs2.298.24891235',
    '+DATA/DBDB/DATAFILE/users.298.24891235',
CHARACTER SET AL32UTF8
;

SQL> recover database;
SQL> alter database open;

8. Temp tablespace add

SQL> alter tablespace temp add tempfile '+DATA/DBDB/TEMPFILE/temp.291.12930124' REUSE;

끝.