control file 손상 되었을 경우.(control file 의 백업 file 있을 때)

select * from v$datafile;

select * from v$log;
=> 백업 이후의 아카이브 확인

select * from v$logfile;

select a.file#, a.name, a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file# = b.file#;
select name from v$controlfile;

S! ls /home/oracle1/arch1

SQL> create table hr.emp_20 as select * from hr.employees where department_id = 20;

Table created.

SQL> select count(*) from hr.emp_20;

  COUNT(*)
----------
         2

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ! ls /u01/app/oracle/oradata/ora11g/control01.ctl
/u01/app/oracle/oradata/ora11g/control01.ctl

#) cold 백업 본 확인
SQL> ! ls /home/oracle1/backup/arch/cold_20240117
backup.log     example01.dbf            redo08.log    system01.dbf  users01.dbf
backup.sh      initora11g_20240117.ora  redo09.log    temp01.dbf
control01.ctl  redo07.log               sysaux01.dbf  undo01.dbf

# 장애 발생 #
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl

SQL> ! ls /u01/app/oracle/oradata/ora11g/control01.ctl
ls: cannot access /u01/app/oracle/oradata/ora11g/control01.ctl: No such file or directory

SQL> startup
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1367004 bytes
Variable Size             440402980 bytes
Database Buffers          264241152 bytes
Redo Buffers                5419008 bytes
ORA-00205: error in identifying control file, check alert log for more info

=> trace 에서 확인

Wed Jan 17 19:56:45 2024
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...

SQL> select status from v$instance;

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

SQL> shutdown abort
ORACLE instance shut down.

# 백업 컨트롤 파일을 원래 위치로 복사 
SQL> ! cp -av /home/oracle1/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle1/backup/arch/cold_20240117/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

# 오류발생 
=> 불안전한 복구 방식
SQL> startup
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1367004 bytes
Variable Size             440402980 bytes
Database Buffers          264241152 bytes
Redo Buffers                5419008 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file
=> 체크포인트 번호가 맞지 않아서 오류발생
=> recover 필요, redo 정보 필요

# recover DB 시도
SQL> recover database using backup controlfile
ORA-00279: change 832091 generated at 01/17/2024 19:29:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_17/o1_mf_1_13_%u_.arc
ORA-00280: change 832091 for thread 1 is in sequence #13


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
=> enter 

# 확인 
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

# 다시 한번더 recover 하기
SQL> recover database using backup controlfile
ORA-00279: change 832091 generated at 01/17/2024 19:29:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_17/o1_mf_1_13_%u_.arc
ORA-00280: change 832091 for thread 1 is in sequence #13


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo09.log => filename 적용
Log applied.
Media recovery complete.
=> 복구 완료!

# resetlog DB 올리기
SQL> alter database open resetlogs;

Database altered.

# redo log 정보 확인 & DB 올라왔는지 확인
SQL> select * from v$log;

SQL> select count(*) from hr.emp_20;

  COUNT(*)
----------
         2