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;
'Backup > Archive Log Mode' 카테고리의 다른 글
data file, redo log file 손상되지 않고 control file 손상되었을 경우 (0) | 2024.01.18 |
---|---|
control file 손상 되었을 경우.(control file 의 백업 file 있을 때) (0) | 2024.01.18 |
archive file 이 다른 위치에 있을 경우 (0) | 2024.01.15 |
모든 데이터 파일 손상되었을 경우 (0) | 2024.01.15 |
system data file 손상되었을 경우 (1) | 2024.01.15 |