Control file 장애 종합 연습 문제

장애상황

Data file들만 남아있고 다른 모든 File(Redo log file, Control file)이 삭제된 상황.

Control file은 오래전에 Backup 받은 파일만 있다.

예전에 Backup 해놓은 Control file을 이용해서 Control file 재 생성 Script를 만들고 이 장애를 복구한다.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> select name from v$datafile; 

NAME

--------------------------------------------------

/home/oracle/oradata/testdb/system01.dbf

/home/oracle/oradata/testdb/undotbs01.dbf

/home/oracle/oradata/testdb/sysaux01.dbf

/home/oracle/oradata/testdb/users01.dbf

/home/oracle/oradata/testdb/example01.dbf

/home/oracle/oradata/testdb/ts_a01.dbf

/home/oracle/oradata/testdb/ts_b01.dbf

/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------

/home/oracle/disk4/redo01_a.log

/home/oracle/disk5/redo01_b.log

/home/oracle/disk4/redo02_a.log

/home/oracle/disk5/redo02_b.log

/home/oracle/disk4/redo03_a.log

/home/oracle/disk5/redo03_b.log

6 rows selected.

SQL> select name from v$controlfile;

NAME

--------------------------------------------------

/home/oracle/oradata/testdb/control01.ctl

/home/oracle/oradata/testdb/control02.ctl

/home/oracle/oradata/testdb/control03.ctl

#연습문제와 같이 장애를 발생시키기 위해 Redo log files와 Control file을 삭제하고 삭제 여부를 확인한다.

SQL> !rm -rf /home/oracle/disk4/*.log                                                  

SQL> !ls /home/oracle/disk4/*.log

ls: /home/oracle/disk4/*.log: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /home/oracle/disk5/*.log

SQL> !ls /home/oracle/disk5/*.log

ls: /home/oracle/disk5/*.log: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /home/oracle/oradata/testdb/*.ctl

SQL> !ls /home/oracle/oradata/testdb/*.ctl

ls: /home/oracle/oradata/testdb/*.ctl: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

#Control file과 Redo log file이 삭제된 상태에서 DB를 시작할 경우 아래와 같은 에러가 발생한다.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

#예전에 Backup 받아놓은 Control file을 Server Parameter에 설정된 경로로 복사한다.

SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control01.ctl

SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control02.ctl

SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control03.ctl

#현재 Control file은 예전에 Backup 받았던  Control file이기 때문에 아래와 같이 old control file 에러가 발생한다.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

ORA-01207: file is more recent than control file - old control file

#에러가 발생하면 Control file을 재 생성하는 Script를 생성한다.

SQL> alter database backup controlfile to trace as '/home/oracle/oradata/testdb/controlfile.sql';

Database altered.

#Control file 재 생성을 위해 Script 내용을 아래와 같이 수정한다.

SQL> !vi /home/oracle/oradata/testdb/controlfile.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/home/oracle/disk4/redo01_a.log',

    '/home/oracle/disk5/redo01_b.log'

  ) SIZE 5M,

  GROUP 2 (

    '/home/oracle/disk4/redo02_a.log',

    '/home/oracle/disk5/redo02_b.log'

  ) SIZE 5M,

  GROUP 3 (

    '/home/oracle/disk4/redo03_a.log',

    '/home/oracle/disk5/redo03_b.log'

  ) SIZE 5M

DATAFILE

  '/home/oracle/oradata/testdb/system01.dbf',

  '/home/oracle/oradata/testdb/undotbs01.dbf',

  '/home/oracle/oradata/testdb/sysaux01.dbf',

  '/home/oracle/oradata/testdb/users01.dbf',

  '/home/oracle/oradata/testdb/example01.dbf',

  '/home/oracle/oradata/testdb/ts_a01.dbf',

  '/home/oracle/oradata/testdb/ts_b01.dbf',

  '/home/oracle/oradata/testdb/ts_c01.dbf'

CHARACTER SET KO16KSC5601

;

"~/oradata/testdb/controlfile.sql" 31L, 894C written

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

#Control file 재 생성을 위해 DB를 mount 단계로 시작하기 위해 사용했던 이전 Control file은 삭제한다.

SQL> !ls /home/oracle/oradata/testdb/*.ctl    

/home/oracle/oradata/testdb/control01.ctl  /home/oracle/oradata/testdb/control03.ctl

/home/oracle/oradata/testdb/control02.ctl

SQL> !rm -rf /home/oracle/oradata/testdb/*.ctl   

SQL> !ls /home/oracle/oradata/testdb/*.ctl

ls: /home/oracle/oradata/testdb/*.ctl: 그런 파일이나 디렉토리가 없음

#Control file 재 생성을 위해 수정해놓은 /home/oracle/oradata/testdb/controlfile.sql Script 파일을 실행한다.

SQL> @/home/oracle/oradata/testdb/controlfile.sql

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Control file created.

SQL> !ls /home/oracle/oradata/testdb/*.ctl

/home/oracle/oradata/testdb/control01.ctl  /home/oracle/oradata/testdb/control03.ctl

/home/oracle/oradata/testdb/control02.ctl

#DB를 open 상태로 전환하고 Data file, Redo log file, Control file을 확인한다.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------

/home/oracle/oradata/testdb/system01.dbf

/home/oracle/oradata/testdb/undotbs01.dbf

/home/oracle/oradata/testdb/sysaux01.dbf

/home/oracle/oradata/testdb/users01.dbf

/home/oracle/oradata/testdb/example01.dbf

/home/oracle/oradata/testdb/ts_a01.dbf

/home/oracle/oradata/testdb/ts_b01.dbf

/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------

/home/oracle/disk4/redo03_a.log

/home/oracle/disk5/redo03_b.log

/home/oracle/disk4/redo02_a.log

/home/oracle/disk5/redo02_b.log

/home/oracle/disk4/redo01_a.log

/home/oracle/disk5/redo01_b.log

6 rows selected.

SQL> select name from v$controlfile;

NAME

--------------------------------------------------

/home/oracle/oradata/testdb/control01.ctl

/home/oracle/oradata/testdb/control02.ctl

/home/oracle/oradata/testdb/control03.ctl

SQL>