undo Data file 장애 발생했을 경우

1. 테이블 생성

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

2) 조회

SELECT f.file_name
FROM dba_extents e, dba_data_files f
WHERE f.file_id = e.file_id
AND e.segment_name = 'NEW_202401'
AND e.owner='HR';

=> users01 TBS에 저장됨

2. 장애발생 
SQL> ! rm /home/oracle/arch1/*.*
SQL> ! rm /home/oracle/arch2/*.*
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
--> user01 TBS, 아카이브정보 삭제

3. 가정) 업무때문에 DB를 정상적으로 내린 후 작업하고 올리기
1) shutdown immediate
2) startup

4. 문제인식
1) 리커버 파일 조회

select * from v$recover_file;

5. 해결방법

alter database datafile 4 offline;

2) 조회

select name, status from v$datafile;

=> offline 모드 확인

3) DB OPEN;

alter database open;

4) 가장 최근에 받은 백업본 restore
(1) 조회
! ls /home/oracle/backup/arch/cold_20240118/users01.dbf

(2) restore
! cp -av /home/oracle/backup/arch/cold_20240118/users01.dbf /u01/app/oracle/oradata/ora11g/

5) 복구하기 (recover)
recover tablespace users;

--> auto
--> 실패 그러면 cancle base recovery 해야함

recover tablespace users;
--> cancle
--> Media recovery cancelled.


6) users TBS 켜기
alter tablespace users online;
--> 실패 why? 아카이브가 유실됐기 때문에 복구못해

7) 데이터파일 헤더정보 확인해야함
select file#, name, checkpoint_change#, status from v$datafile;
======================================================
1 /u01/app/oracle/oradata/ora11g/system01.dbf 1184363 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1184363 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1184360 OFFLINE ★
5 /u01/app/oracle/oradata/ora11g/example01.dbf 1184363 ONLINE
7 /u01/app/oracle/oradata/ora11g/undotbs.dbf 1184363 ONLINE
======================================================
--> 현재상태에서 users는 offline으로 떨어져 있기 때문에 수위를 맞춰 복구할 수 없음 ★★★ 
--> (어제 놓친 부분)
--> 즉, online으로 바꾸고 cold backup을 진행해야함.

8) online으로 바꾸기 위해서 DB를 내렸다 올림 (why? mount 단계에서 online하기위해서)
shutdown immediate
startup mount



9) users online으로 바꾸고 조회
- 컨트롤파일에 적용하는 작업
alter database datafile 4 online;
--> Database이(가) 변경되었습니다

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

10) 다시 scn싱크를 맞추기 위해서 DB 내리기
shutdown immediate

11) cold backup 본으로 datafile만 restore하기
[oracle@oracle cold_20240118]$ 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’
‘temp_new01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp_new01.dbf’
‘undotbs.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’


12) startup mount

select file#, name, checkpoint_change#, status from v$datafile;
======================================================
1 /u01/app/oracle/oradata/ora11g/system01.dbf 1185765 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1185765 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1184360 ONLINE
5 /u01/app/oracle/oradata/ora11g/example01.dbf 1185765 ONLINE
7 /u01/app/oracle/oradata/ora11g/undotbs.dbf 1185765 ONLINE
======================================================
--> SCN번호가 다를 수 있다


13) recover하기
SQL> recover database until cancel
ORA-00279: change 1182902 generated at 01/18/2024 10:16:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_23_1157971633.arc
ORA-00280: change 1182902 for thread 1 is in sequence #23


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

--> 캔슬베이스리커버리 why? 아카이브가 손상됐기 때문에


14) DB 오픈
alter database open resetlogs;
--> Database altered.
--> redo랑 dbf랑 수위가 안맞아서 resetlogs해야함

15)  조회
(1) dbf 조회
select file#, name, checkpoint_change#, status from v$datafile;
======================================================
1 /u01/app/oracle/oradata/ora11g/system01.dbf 1182906 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1182906 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1182906 ONLINE
5 /u01/app/oracle/oradata/ora11g/example01.dbf 1182906 ONLINE
7 /u01/app/oracle/oradata/ora11g/undotbs.dbf 1182906 ONLINE
======================================================
--> 일정한 SCN번호로 오픈됨


(2) select * from v$log;
===========================================================
1 1 1 52428800 512 1 NO CURRENT 1182903 24/01/18 281474976710655
2 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
===========================================================
--> seq#1부터 시작함
--> 복구완료 : 불완전한 복구시 cold백업 및 hot백업을 다시 받아야한다.

(3)
SELECT f.file_name
FROM dba_extents e, dba_data_files f
WHERE f.file_id = e.file_id
AND e.segment_name = 'NEW_202401'
AND e.owner='HR';
=====
값 없음
=====
-->데이터유실을 조심해야한다. 그래서 클론db 생성하고 유실된 지점과 현재의 갭을 EXPORT하고, 운영DB에 IMPORT해야한다.