모든 data file, redo log file, control file 손상되었을 경우

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                 834109 18-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

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

1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM SYSTEM 836949
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX SYSAUX ONLINE 836949
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS USERS ONLINE 836949
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE EXAMPLE ONLINE 836949
9 /u01/app/oracle/oradata/ora11g/undo01.dbf UNDO1 UNDO1 ONLINE 836949

#) 테이블 생성 및 확인
SQL> create table hr.new_20240119 as select * from hr.employees;

Table created.

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

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

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                 834109 18-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

#) log switch 강제로 발생
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

=> alert_ora11g.log 에서 확인 
Thu Jan 18 21:55:54 2024
Beginning log switch checkpoint up to RBA [0x2.2.10], SCN: 837429
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 8 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo08.log
Thu Jan 18 21:55:54 2024
Archived Log entry 5 added for thread 1 sequence 1 ID 0xf7c0724 dest 1:
Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 837432
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 9 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo09.log
Thu Jan 18 21:55:56 2024
Archived Log entry 6 added for thread 1 sequence 2 ID 0xf7c0724 dest 1:
Thread 1 cannot allocate new log, sequence 4
Checkpoint not complete
  Current log# 9 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo09.log
Thu Jan 18 21:55:59 2024
Completed checkpoint up to RBA [0x3.2.10], SCN: 837432
Beginning log switch checkpoint up to RBA [0x4.2.10], SCN: 837441
Completed checkpoint up to RBA [0x2.2.10], SCN: 837429
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 7 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo07.log
Thu Jan 18 21:55:59 2024
Archived Log entry 7 added for thread 1 sequence 3 ID 0xf7c0724 dest 1:
Completed checkpoint up to RBA [0x4.2.10], SCN: 837441

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;

7 4 /u01/app/oracle/oradata/ora11g/redo07.log 100 NO CURRENT
8 2 /u01/app/oracle/oradata/ora11g/redo08.log 100 YES INACTIVE
9 3 /u01/app/oracle/oradata/ora11g/redo09.log 100 YES INACTIVE

#) 아카이브 확인 
SQL> ! ls /home/oracle1/arch1/
arch_1_1_1158611832.arc  arch_1_2_1158611832.arc  arch_1_3_1158611832.arc

# 장애 유발 #
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log
=> alert_ora11g.log 에서 확인 
Thu Jan 18 21:57:52 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_21080.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
Thu Jan 18 21:57:53 2024
Starting background process SMCO
Thu Jan 18 21:57:53 2024
SMCO started with pid=27, OS id=21084

#) 새로운 session 에서 오류 발생
-> 로그인이 되지 않음
[oracle1@oracle ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 21:57:51 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Enter user-name:

 

# data file, log file, control file 확인

SQL> ! ls /u01/app/oracle/oradata/ora11g/
-> 확인되지 않는다.

#) 접속 가능 할 수도 있음 (or 접속 불가능)
-> 문제 있는 상황.
SQL> conn / as sysdba
Connected.
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 21:59:08 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

=> 비정상적인 로그인 

#) 아카이브 파일 확인
SQL> !
[oracle1@oracle ~]$ cd arch1
[oracle1@oracle arch1]$ ls
arch_1_1_1158611832.arc  arch_1_2_1158611832.arc  arch_1_3_1158611832.arc

# restore 하기 
(-> 최근 백업 받은 걸로 copy 해주기)
[oracle1@oracle ~]$ cp -av /home/oracle1/backup/arch/cold_20240117/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle1/backup/arch/cold_20240117/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle1/backup/arch/cold_20240117/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle1/backup/arch/cold_20240117/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle1/backup/arch/cold_20240117/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle1/backup/arch/cold_20240117/undo01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undo01.dbf’
‘/home/oracle1/backup/arch/cold_20240117/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle1@oracle ~]$ cp -av /home/oracle1/backup/arch/cold_20240117/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle1/backup/arch/cold_20240117/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

# DB가 스스로 open되어 있다..?

[oracle1@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 22:03:53 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN
=> DB를 startup 하지 않아도 알아서 올라가 있다.

 

# 장애 나기 전에 생성된 테이블 확인 

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

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

# data file, control file, redo file 확인 
SQL> ! ls /u01/app/oracle/oradata/ora11g/
control01.ctl  example01.dbf  sysaux01.dbf  system01.dbf  temp01.dbf  undo01.dbf  users01.dbf

# DB 비정상적인 종료 

SQL> shutdown abort
ORACLE instance shut down.

# DB 올리기

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-03113: end-of-file on communication channel
Process ID: 21398
Session ID: 125 Serial number: 5

=> 오류발생 

 

# 재접속 후 비정상적으로 DB 종료. 

SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.

 

# OS 에서 restore 하기 

SQL> exit
Disconnected
[oracle1@oracle ~]$ cd backup/arch/cold_20240117
[oracle1@oracle cold_20240117]$ ls
backup.log  control01.ctl  initora11g_20240117.ora  redo08.log  sysaux01.dbf  temp01.dbf  users01.dbf
backup.sh   example01.dbf  redo07.log               redo09.log  system01.dbf  undo01.dbf
[oracle1@oracle cold_20240117]$ cp -av *.ctl /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
[oracle1@oracle cold_20240117]$ cp -av *.dbf /u01/app/oracle/oradata/ora11g/
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘undo01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undo01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

# oracle로 접속해서 mount 단계까지 올리기

[oracle1@oracle cold_20240117]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 22:08:19 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
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.

# recover 하기
SQL> recover database until cancel using backup controlfile
ORA-00279: change 836946 generated at 01/18/2024 21:18:10 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_1_1158611832.arc
ORA-00280: change 836946 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto (입력)
ORA-00279: change 837429 generated at 01/18/2024 21:55:54 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_2_1158611832.arc
ORA-00280: change 837429 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle1/arch1/arch_1_1_1158611832.arc' no longer
needed for this recovery


ORA-00279: change 837432 generated at 01/18/2024 21:55:56 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_3_1158611832.arc
ORA-00280: change 837432 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle1/arch1/arch_1_2_1158611832.arc' no longer
needed for this recovery


ORA-00279: change 837441 generated at 01/18/2024 21:55:59 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_4_1158611832.arc
ORA-00280: change 837441 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle1/arch1/arch_1_3_1158611832.arc' no longer
needed for this recovery

=> 적용이 되지 않는 파일 체크

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

# 다시 recover 해보기, cancel 로 입력

SQL> recover database until cancel using backup controlfile
ORA-00279: change 837441 generated at 01/18/2024 21:55:59 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_4_1158611832.arc
ORA-00280: change 837441 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel(입력)
Media recovery cancelled.
=> alert_ora11g.log 에서 확인 
Thu Jan 18 22:10:46 2024
ALTER DATABASE RECOVER    CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER    CANCEL

# DB 를 resetlogs 로 올리기

SQL> alter database open resetlogs;

Database altered.
=> alert_ora11g.log 에서 확인  
Additional information: 3
Clearing online redo logfile 9 complete
Resetting resetlogs activation ID 259786532 (0xf7c0724)
Online log /u01/app/oracle/oradata/ora11g/redo07.log: Thread 1 Group 7 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo08.log: Thread 1 Group 8 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo09.log: Thread 1 Group 9 was previously cleared

# 테이블 확인 
SQL> select count(*) from hr.new_20240119;

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