system data file 손상되었을 경우

# 백업 확인(물리적위치)
SQL> ! ls /home/oracle1/backup/arch/hot_20240112/

 

# 백업 정보
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#;

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

SQL> ! ls /u01/app/oracle/oradata/ora11g/system01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/system01.dbf: No such file or directory

SQL> shutdown immediate

SQL> startup
- 오류발생

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

#) 상태 확인 

SQL> select status from v$instance;

#) recover file 확인 

SQL> select * from v$recover_file;

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

SQL> recover tablespace system;
ORA-00279: change 933110 generated at 01/12/2024 00:07:41 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch2/arch_1_4_1157923223.arc
ORA-00280: change 933110 for thread 1 is in sequence #4


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

 

Media recovery complete.

#) DB open 하기 

SQL> alter database open;

# recover 대상 file 확인
SQL> select * from v$recover_file;

# tablespace 상태 정보 확인
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#;

# 복구 완료