backup file에 redo log file(옵션 파일)이 없을 경우

( 상황 ) 
1. noarchive log mode
2. Whole database backup (일관성 있는 백업, cold backup)을 /home/oracle/backup/noarch/20240111 디렉터리에 백업하세요.
3. 단 data file, control file 만 백업해주세요.
[ => data file, control file : 과거시점으로 돌아가야 하는 checkpoint scn 번호가 동일한 file이 있어야 함 ]

 

[oracle1@oracle noarch]$ mkdir 20240111/
cp -av system01.dbf /home/oracle1/backup/noarch/20240111
cp -av sysaux01.dbf /home/oracle1/backup/noarch/20240111
cp -av users01.dbf /home/oracle1/backup/noarch/20240111 
cp -av example01.dbf /home/oracle1/backup/noarch/20240111
cp -av undo01.dbf /home/oracle1/backup/noarch/20240111

#1) data file backup 
[oracle1@oracle 20240111]$ cd /u01/app/oracle/oradata/ora11g/
[oracle1@oracle ora11g]$ cp -av users01.dbf /home/oracle1/backup/noarch/20240111
‘users01.dbf’ -> ‘/home/oracle1/backup/noarch/20240111/users01.dbf’
[oracle1@oracle ora11g]$ cp -av example01.dbf /home/oracle1/backup/noarch/20240111
‘example01.dbf’ -> ‘/home/oracle1/backup/noarch/20240111/example01.dbf’
[oracle1@oracle ora11g]$ cp -av undo01.dbf /home/oracle1/backup/noarch/20240111
‘undo01.dbf’ -> ‘/home/oracle1/backup/noarch/20240111/
01.dbf’
[oracle1@oracle ora11g]$ cp -av system01.dbf /home/oracle1/backup/noarch/20240111
‘system01.dbf’ -> ‘/home/oracle1/backup/noarch/20240111/system01.dbf’
[oracle1@oracle ora11g]$ cp -av sysaux01.dbf /home/oracle1/backup/noarch/20240111
‘sysaux01.dbf’ -> ‘/home/oracle1/backup/noarch/20240111/sysaux01.dbf’

#2) control file backup 
[oracle1@oracle ora11g]$ cp -av control01.ctl /home/oracle1/backup/noarch/20240111
‘control01.ctl’ -> ‘/home/oracle1/backup/noarch/20240111/control01.ctl’

#3) backup 확인
[oracle1@oracle ora11g]$ cd /home/oracle1/backup/noarch/20240111
[oracle1@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  undo01.dbf
example01.dbf  system01.dbf  users01.dbf
[oracle1@oracle 20240111]$ ll
total 1738264
-rw-r-----. 1 oracle1 oinstall   9748480 Jan 10 20:53 control01.ctl
-rw-r-----. 1 oracle1 oinstall 363077632 Jan 10 20:48 example01.dbf
-rw-r-----. 1 oracle1 oinstall 597696512 Jan 10 20:54 sysaux01.dbf
-rw-r-----. 1 oracle1 oinstall 786440192 Jan 10 20:54 system01.dbf
-rw-r-----. 1 oracle1 oinstall  17768448 Jan 10 20:54 undo01.dbf
-rw-r-----. 1 oracle1 oinstall   5251072 Jan 10 20:48 users01.dbf

#) 오라클 접속
[oracle1@oracle 20240111]$ sqlplus / as sysdba

#) redo 정보 확인 

select * from v$log;

#) data 확인 

select name, checkpoint_change#, status from v$datafile;

#) temp 확인

select name from v$tempfile;

#) control 확인

select name from v$controlfile;

#) pfile backup
tip) 초기 파라미터 파일도 백업하자 

create pfile ='/백업할 경로/initora_날짜.ora' from spfile;

#) 정상적인 종료 후 OS로 나오기 

shutdown immediate
!

#) copy 하기

=> 절대경로를 작성해줘야 하나, 같은 경로에 cp할거라면 .만 해도 무방
[oracle1@oracle 20240111]$ cp -av /home/oracle1/ora_data/*.dbf . 

‘/home/oracle1/ora_data/example01.dbf’ -> ‘./example01.dbf’
‘/home/oracle1/ora_data/sysaux01.dbf’ -> ‘./sysaux01.dbf’
‘/home/oracle1/ora_data/system01.dbf’ -> ‘./system01.dbf’
‘/home/oracle1/ora_data/temp01.dbf’ -> ‘./temp01.dbf’
‘/home/oracle1/ora_data/undo01.dbf’ -> ‘./undo01.dbf’
‘/home/oracle1/ora_data/users01.dbf’ -> ‘./users01.dbf’
[oracle1@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf  users01.dbf
example01.dbf  system01.dbf  undo01.dbf

#) DB 올리기

startup

#) (가정 상황) table 생성

create table hr.emp_new as select * from hr.employees;

 

select * from v$log;

#) table 확인

select count(*) from hr.emp_new;

#) table 위치 확인

select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_NEW'
and e.owner = 'HR';

#) redo log 정보 확인

select * from v$log;

#) log switch 강제로 발생*3번

alter system switch logfile;

#) seq 번호 확인
=> 변경 

select * from v$log;

#) scn 번호를 맞추기 위함

shutdown immediate
!

# 장애 유발 #
[oracle1@oracle 20240111]$ rm /u01/app/oracle/oradata/ora11g/users01.dbf
[oracle1@oracle 20240111]$ ls /u01/app/oracle/oradata/ora11g/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/users01.dbf: No such file or directory

# 오류발생!

startup

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' => #장애발생#

#) users01.dbf 있는지 확인!

! ls /u01/app/oracle/oradata/ora11g/users01.dbf

 

ls: cannot access /u01/app/oracle/oradata/ora11g/users01.dbf: No such file or directory

#) redo log 파일 복구 정보 있다고 생각 하고 완전복구 시도!

alter database datafile 4 offline drop;

#) users01.dbf => OFFLINE (상태정보)으로 변경되었는지 확인

select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf         OFFLINE
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE
/u01/app/oracle/oradata/ora11g/undo01.dbf          ONLINE

#) DB open 까지 문제없음!

alter database open;

#) 조회 가능

select count(*) from hr.employees;

=> 방법1) 완전복구를 위한 restore 작업
#) OS 나오기,(장애 공지) 
SQL> !
[oracle1@oracle 20240111]$ cp -av /home/oracle1/backup/noarch/20240111/users01.dbf /u01/app/oracle/oradata/ora11g/ 
=> 복원(원위치 시도)
‘/home/oracle1/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle1@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf  users01.dbf
example01.dbf  system01.dbf  undo01.dbf

#) cp 되었는지 확인
[oracle1@oracle 20240111]$ ls /u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf

#) 삭제된 users01.dbf 를 recovery! (=> scn 번호를 통해서 recovery 시도)

recover tablespace users;

ORA-00279: change 828316 generated at 01/08/2024 20:19:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_12_%u_.arc
ORA-00280: change 828316 for thread 1 is in sequence #12

(=> scn 번호를 통해서 recovery 시도 scn번호 찾을 수가 없다!, 완전복구 실패!)
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto(입력)
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_12_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_12_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
AUTO (입력 : 실패됨)

=> ★ 방법2) 불안전한 복구로 진행해야 함!(data, control file 밖에 없어서 불안전한 복구밖에 방법이 없음)

shutdown abort

#) data file cp 하기

! cp -av /home/oracle1/backup/noarch/20240111/*.dbf /u01/app/oracle/oradata/ora11g

‘/home/oracle1/backup/noarch/20240111/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle1/backup/noarch/20240111/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle1/backup/noarch/20240111/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle1/backup/noarch/20240111/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle1/backup/noarch/20240111/undo01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undo01.dbf’
‘/home/oracle1/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

#) cotrol file cp 하기

! cp -av /home/oracle1/backup/noarch/2024011/*.ctl /u01/app/oracle/oradata/ora11g

‘/home/oracle1/backup/noarch/20240111/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

#) DB mount 단계까지 올리기

=> redo 는 맞지 않기 때문에, redo는 backup 하지 않음

startup mount

 

#) recovery, redo scn 번호는 맞지 않음.
- 옵션 작성 : until cancel using backup controlfile (redo 맞지 않을 경우)

recover database until cancel using backup controlfile;

ORA-00279: change 828316 generated at 01/08/2024 20:19:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_10/o1_mf_1_12_%u_.arc
ORA-00280: change 828316 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL (입력)
Media recovery cancelled.
☆ 주의) redo 정보를 앞당겨져 있으니, open 단계에서 다른 file과 맞춰야 함.

#) DB open 하게 되면 오류발생 -> seq 번호 다시 시작하게 startup 
- resetlogs: 재설정

alter database open resetlogs;

#) seq 번호 초기화처럼 1번부터 시작된다!
- 이후에 DB 정상적인 종료했다가 올려줘야 한다.
- redo scn 번호 초기화, data-control-redo file 백업 받아주는 것이 좋다!

select * from v$log;

#) data file 확인

select name, checkpoint_change#, status from v$datafile;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf                    828320 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf                    828320 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf                     828320 ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf                   828320 ONLINE
/u01/app/oracle/oradata/ora11g/undo01.dbf                      828320 ONLINE

#) temp file 확인

select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

#) control file 확인

select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl

 

#) 복구 완료!