data file, redo log file 손상되지 않고 control file 손상되었을 경우

SQL> select * from v$log;

select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# 테이블 생성 및 확인
SQL> create table hr.emp_30 as select * from hr.employees where department_id = 30;
SQL> select count(*) from hr.emp_30;

# 장애 발생 #
=> control01.ctl 지우기, log switch 발생
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> alter system switch logfile;

System altered.

# redo 정보 확인
SQL> select * from v$log;

# arch1 정보 확인
SQL> ! ls /home/oracle1/arch1/

SQL> 
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# 새로운 session 에서 오류 발생
SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
#################################################
Wed Jan 17 21:26:47 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_9490.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
##################################################

# hr 계정에서의 확인
SQL> conn hr/hr
Connected.
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

# 기존 session 에서 정상적인 DB 내리기

=> 정상적으로 DB 를 내리기.

SQL> shutdown immediate

# 비정상적인 DB 내리기
SQL> shutdown abort

# 최근에 백업 받은 걸로 restore 하기
SQL> ! cp -av /home/oracle1/backup/arch/cold_20240117/control01.ctl /u01/app/oracle/oradata/ora11g/

# start mount단계 까지 올리기
SQL> startup mount

SQL> select status from v$instance;

# recover 시도 
=> database 레벨로 시도 
SQL> recover database;

=> recover 실패
=> 복구 작업 실패

SQL> select status from v$instance;

# 컨트롤 파일을 재생성한 후 데이터베이스를 시작
=> pfile에도 수정된다.
SQL> alter database backup controlfile to trace as '/home/oracle1/new_control.sql';

# DB 내리기
SQL> shutdown abort

# OS 나와서 파일확인
SQL> !
[oracle1@oracle ~]$ ls
arch1   database   Downloads        p13390677_112040_LINUX_1of7.zip  Public     Videos
arch2   Desktop    Music            p13390677_112040_LINUX_2of7.zip  Templates
backup  Documents  new_control.sql  Pictures                         userdata
[oracle1@oracle ~]$ vi new_control.sql

=> new_control.sql -> create_control.sql 'NORESETLOGS' copy 하기
[oracle1@oracle ~]$ vi create_control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G"(DB 이름 변경) NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16 => 리두로그그룹갯수(기본값)
    MAXLOGMEMBERS 3 => 한 그룹당 멤버 최대 갯수
    MAXDATAFILES 100 => 현재 데이터파일의 갯수
    MAXINSTANCES 8 => 8코어, instance 여러대
    MAXLOGHISTORY 292 => log_history 갯수
LOGFILE => rename 변경 갯수
  GROUP 7 '/u01/app/oracle/oradata/ora11g/redo07.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 8 '/u01/app/oracle/oradata/ora11g/redo08.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 9 '/u01/app/oracle/oradata/ora11g/redo09.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ora11g/system01.dbf',
  '/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/app/oracle/oradata/ora11g/users01.dbf',
  '/u01/app/oracle/oradata/ora11g/example01.dbf',
  '/u01/app/oracle/oradata/ora11g/undo01.dbf'
CHARACTER SET AL32UTF8
;

[oracle1@oracle ~]$ ls
arch1               database   Music                            Pictures   Videos
arch2               Desktop    new_control.sql                  Public
backup              Documents  p13390677_112040_LINUX_1of7.zip  Templates
create_control.sql  Downloads  p13390677_112040_LINUX_2of7.zip  userdata
[oracle1@oracle ~]$ cat create_control.sql

# 확인 및 수행
[oracle1@oracle ~]$ sqlplus / as sysdba
SQL> ! ls

SQL> @create_control.sql
=> 새롭게 control 파일을 생성

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
해석) 복구할 필요가 없다.
=> 그냥 DB open 올리기

SQL> alter database open;

Database altered.
또는 
#오류발생
SQL> alter database open;
=> recovery 필요.

SQL> recover database;

###############################################

SQL> 
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;

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         7          1          1  104857600        512          1 NO
CURRENT                 837671 17-JAN-24   2.8147E+14

         8          1          0  104857600        512          1 YES
UNUSED                       0                      0

         9          1          0  104857600        512          1 YES
UNUSED                       0                      0

# ★temp file 확인 및 추가
=> temp file 인식을 못함
SQL> select * from v$tempfile;
SQL> select * from dba_temp_files;

# 위치 확인

SQL> ! ls /u01/app/oracle/oradata/ora11g/temp01.dbf

# temp 파일 추가

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' reuse;

select * from v$tempfile;

SQL> select name from v$tempfile;

select * from dba_temp_files;

SQL> select file_name from dba_temp_files;

=====================================================================================

(할 필요 없는 명령문장들)
SQL> recover database using backup controlfile
ORA-00279: change 837669 generated at 01/17/2024 21:29:24 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch2/arch_1_8_1158527069.arc
ORA-00280: change 837669 for thread 1 is in sequence #8


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

ORA-00308: cannot open archived log
'/home/oracle1/arch2/arch_1_8_1158527069.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> recover database using backup controlfile
ORA-00279: change 837669 generated at 01/17/2024 21:29:24 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch2/arch_1_8_1158527069.arc
ORA-00280: change 837669 for thread 1 is in sequence #8


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo08.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.
=========================================================================================