undo.dbf 장애 발생했을 경우

1. data file, tablespace 상태 및 체크포인트 번호 확인 

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

 

2. 백업 정보 확인

select a.file#, a.name file_name, a.checkpoint_change#, b.status, b.change#, to_char(b.time, 'yyyy-mm-dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file# = b.file#;

 

3. redo log 정보 확인

=> SCN , 상태 확인

select * from v$log;

 

4. archive log 정보 확인

=> archive file이 있는지 확인

select sequence#, name, first_change#, first_time, next_change#, next_time 
from v$archived_log;

 

5. arch1, arch2 디렉터리에 있는지 확인

SQL> ! ls /home/oracle1/arch1/ /home/oracle1/arch2/

 

6. redo의 history 정보 

select * from v$log_history;

 

7. rollback segment 정보

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

 

8. test 용도, 트랜잭션 시도

<HR SESSION>

update hr.employees
set salary = 2000
where employee_id = 100;

8. 트랜잭션 정보 

<SYS SESSION>

select s.username, s.sid, s.serial#, r.name, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

 

9. KILL 하는 방법 

ALTER SYSTEM KILL SESSION '19,15' IMMEDIATE;

 

10. <HR SESSION> 확인 

(=> PMON 에서 확인)

SELECT * FROM hr.employees;

10-1) 재접속 후 update문 실행 

SQL> conn hr/hr

SQL> update hr.employees set salary = 2000 where employee_id = 100; 

11. session 확인 

<SYS SESSION>

select s.username, s.sid, s.serial#, r.name, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

 

12. undo 확인

SQL> show parameter undo

undo_management	AUTO	undo 자동관리, 기본 10개
undo_retention	900	commit 을 하더라도 900초(스토리지 보장)동안 이전값을 보장하겠다. 보장하는 이유? 읽기일관성을 보장하기 위해서, DML을 하기 위해
undo_tablespace	UNDO1	parameter file가 undo 갖고 있음

 

(상황) unod01.dbf 지운 걸 몰랐다!

#) DB 내리기 

SQL> shutdown immediate

 

#) undo data file 장애 유발하기

SQL> ! rm /u01/app/oracle/oradata/ora11g/undo01.dbf

 

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

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

 

#) 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-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: '/u01/app/oracle/oradata/ora11g/undo01.dbf'

 

1. recover 대상 file 확인 

select * from v$recover_file;

 

 

#) backup 본으로 restore 하기

1) 위치 확인

SQL> !

[oracle1@oracle arch]$ cd hot_20240115/

[oracle1@oracle hot_20240115]$ ls

backup.log  control01.ctl  sysaux01.dbf  temp_new01.dbf  users01.dbf

backup.sh   example01.dbf  system01.dbf  undo01.dbf

[oracle1@oracle hot_20240115]$ pwd

/home/oracle1/backup/arch/hot_20240115/

2) copy 하기

SQL> ! cp -av /home/oracle1/backup/arch/hot_20240115/undo01.dbf /u01/app/oracle/oradata/ora11g/

‘/home/oracle1/backup/arch/hot_20240115/undo01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undo01.dbf’

 

3) recover 하기

SQL> recover tablespace undo1

ORA-00279: change 1041732 generated at 01/15/2024 02:33:32 needed for thread 1

ORA-00289: suggestion : /home/oracle1/arch2/arch_1_19_1157923223.arc

ORA-00280: change 1041732 for thread 1 is in sequence #19

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

auto(입력)

ORA-00279: change 1041933 generated at 01/15/2024 02:38:12 needed for thread 1

ORA-00289: suggestion : /home/oracle1/arch2/arch_1_20_1157923223.arc

ORA-00280: change 1041933 for thread 1 is in sequence #20

ORA-00279: change 1042044 generated at 01/15/2024 02:42:08 needed for thread 1

ORA-00289: suggestion : /home/oracle1/arch2/arch_1_21_1157923223.arc

ORA-00280: change 1042044 for thread 1 is in sequence #21

 

Log applied.

Media recovery complete.

 

#) DB 올리기

SQL> alter database open;

 

#) DB 제대로 올라왔는지 확인 

SQL> select salary from hr.employees where employee_id = 100;

 

#) <HR SESSION >

SQL> conn hr/hr

Connected.

SQL> UPDATE hr.employees SET salary = 2000 WHERE employee_id = 100;

 

#) <SYS SESSION> 

=> 트랜잭션 조회

select s.username, s.sid, s.serial#, r.name, t.xidusn, t.ubafil, t.ubablk, t.used_ublk

from v$session s, v$transaction t, v$rollname r

where s.taddr = t.addr

and t.xidusn = r.usn;