251216 TIL
2025. 12. 17. 09:39ㆍCourses/아이티윌 오라클 DBA 과정
시나리오2 : 특정 데이터 파일 손상(백업 이후 리두 정보 존재, 완전 복구 가능)
현재 체크 포인트 조회
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO CURRENT 2856520 16-DEC-25 1.8447E+19 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO INACTIVE 2822872 12-DEC-25 2856520 16-DEC-25 0
SYS@ora19c> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf 2859507
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2859507
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2859507
/u01/app/oracle/oradata/ORA19C/users01.dbf 2859507
SYS@ora19c> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2859507백업 이후 작업 수행
SYS@ora19c> create table hr.emp(id number) tablespace users;
Table created.
SYS@ora19c> insert into hr.emp(id) values(1);
1 row created.
SYS@ora19c> commit;
Commit complete.SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf장애 유발
# 데이터베이스 종료
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
# 장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> !ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
# 데이터베이스 시작
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
# 복구 수행해야 할 데이터 파일 정보(복구 완료되기 전까지 조회 가능)
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- --------- ----------
7 ONLINE ONLINE FILE NOT FOUND 0 0현재 데이터베이스 상태 조회
# 현재 데이터베이스 상태
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED
SYS@ora19c> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO CURRENT 2856520 16-DEC-25 1.8447E+19 0
3 1 28 52428800 512 1 NO INACTIVE 2822872 12-DEC-25 2856520 16-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
문제 있는 데이터 파일을 offline drop
# noarchivelog mode에서는 데이터 파일을 offline 모드로 수행하는 경우 꼭 offline drop해야 함
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.
SYS@ora19c> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE
7 /u01/app/oracle/oradata/ORA19C/users01.dbf OFFLINE데이터베이스 open
# 데이터베이스 open
SYS@ora19c> alter database open;
Database altered.
# 문제가 없는 데이터 파일의 데이터는 조회 가능
SYS@ora19c> select count(*) from hr.employees;
COUNT(*)
----------
107
# 문제가 있는 데이터 파일의 데이터는 조회 불가
SYS@ora19c> select * from hr.emp;
select * from hr.emp
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'복구 진행
1. 백업본 찾아서 restore
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’2. 백업 이후 변경 정보 적용
SYS@ora19c> recover datafile 7
Media recovery complete.3. 복구 완료된 데이터 파일을 online으로 변경
SYS@ora19c> alter database datafile 7 online;
Database altered.
SYS@ora19c> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE
SYS@ora19c> select * from hr.emp;
ID
----------
1 원상복구
# 데이터베이스 강제 종료
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
# 백업본으로 덮어쓰기
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ ls /u01/app/oracle/oradata/ORA19C/
control01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@ora19c noarch]$ exit
exit
# 데이터베이스 시작
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.시나리오3 : 특정 데이터 파일 손상(백업 이후 리두 정보 X, 불완전 복구 수행)
현재 체크포인트 확인
# 현재 체크포인트 확인
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0
SYS@ora19c> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf 2845590
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590
/u01/app/oracle/oradata/ORA19C/users01.dbf 2845590
SYS@ora19c> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2845590
SYS@ora19c> select name, checkpoint_change# from v$database;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
ORA19C 2845590백업 이후 작업 수행
# 백업 이후 작업 수행
SYS@ora19c> create table hr.emp(id number) tablespace users;
Table created.
SYS@ora19c> insert into hr.emp(id) values(1);
1 row created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0
SYS@ora19c> col file_name format a50
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and e.owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf로그 스위치 발생
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO INACTIVE 2849858 16-DEC-25 2849865 16-DEC-25 0
2 1 30 52428800 512 1 NO INACTIVE 2849865 16-DEC-25 2849868 16-DEC-25 0
3 1 31 52428800 512 1 NO CURRENT 2849868 16-DEC-25 1.8447E+19 0장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> !ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- -------------------------------------------------- ---------- --------- ----------
7 ONLINE ONLINE FILE NOT FOUND
SYS@ora19c> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 문제 있는 데이터파일을 offline drop
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.데이터베이스 open
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> select count(*) from hr.employees;
COUNT(*)
----------
107
SYS@ora19c> select * from hr.emp;
select * from hr.emp
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'완전 복구 수행(실패)
1. 백업본 찾아서 resotre
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’2. 백업 이후 리두 정보가 없어서 완전 복구 실패
SYS@ora19c> recover datafile 7
ORA-00279: change 2845587 generated at 12/15/2025 16:00:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7불완전 복구
- noarchivelog mode에서 완전 복구 실패하게 되면 모든 데이터 파일, 컨트롤 파일, 리두 로그 파일을 restore하면 됨
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘recover’ -> ‘/u01/app/oracle/oradata/ORA19C/recover’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.시나리오4 : 백업 받지 않은 테이블스페이스 데이터 파일 손상(리두 정보 존재)
테이블스페이스 생성
SYS@ora19c> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> create tablespace insa_tbs datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf' size 10m;
Tablespace created.
SYS@ora19c> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf
INSA_TBS /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf현재 체크포인트 확인
SYS@ora19c> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2850481
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2850481
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2850481
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2850481
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 2851255
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2850481
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0백업 이후 작업 수행
SYS@ora19c> create table hr.insa(id number) tablespace insa_tbs;
Table created.
SYS@ora19c> insert into hr.insa(id) values(1);
1 row created.
SYS@ora19c> commit;
Commit complete.SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'INSA'
and e.owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf: No such file or directory백업 받지 않은 데이터 파일 손상 시 리두가 있을 때 복구 수행
1. 손상된 데이터 파일 offline drop
# 파일 번호로 offline drop
SYS@ora19c> alter database datafile 5 offline drop;
# 또는 파일명으로 offline drop
SYS@ora19c> alter database datafile /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf offline drop;
Database altered.
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2850481
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2850481
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2850481
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf RECOVER 2851255
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 28504812. 백업본이 없는 손상된 데이터 파일에 대해서 물리적인 파일을 새롭게 생성
SYS@ora19c> alter database create datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf';
Database altered.
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
3. 새롭게 생성한 데이터 파일에 리두 정보를 적용
SYS@ora19c> alter database recover datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf';
Database altered.4. 복구 수행된 데이터 파일 online 변경
SYS@ora19c> alter database datafile 5 online;
Database altered.
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2850481
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2850481
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2850481
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 2854557
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2850481
SYS@ora19c> select * from hr.insa;
ID
----------
1 SYS@ora19c> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.시나리오5 : 백업 받지 않은 테이블스페이스 데이터 파일 손상(리두 정보 X)
테이블스페이스 생성
SYS@ora19c> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> create tablespace insa_tbs datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf' size 10m;
Tablespace created.
SYS@ora19c> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf
INSA_TBS /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf현재 체크포인트 확인
SYS@ora19c> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2854958
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2856855
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2856855
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2856855
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf ONLINE 2856855
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2856855
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0
백업 이후 작업 수행
SYS@ora19c> create table hr.insa(id number) tablespace insa_tbs;
Table created.
SYS@ora19c> insert into hr.insa(id) values(1);
1 row created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'INSA'
and e.owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0로그 스위치 발생
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO INACTIVE 2856849 16-DEC-25 2856852 16-DEC-25 0
2 1 30 52428800 512 1 NO INACTIVE 2856852 16-DEC-25 2856855 16-DEC-25 0
3 1 31 52428800 512 1 NO CURRENT 2856855 16-DEC-25 1.8447E+19 0장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf: No such file or directory복구(실패)
1. 손상된 데이터 파일 offline drop
# 파일 번호로 offline drop
SYS@ora19c> alter database datafile 5 offline drop;
# 또는 파일명으로 offline drop
SYS@ora19c> alter database datafile /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf offline drop;
Database altered.
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2850481
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2850481
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2850481
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf RECOVER 2851255
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 28504812. 백업본이 없는 손상된 데이터 파일에 대해서 물리적인 파일을 새롭게 생성
SYS@ora19c> alter database create datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf';
Database altered.
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf
3. 새롭게 생성한 데이터 파일에 리두 정보를 적용 수행하는 순간 오류 발생
- 손상된 데이터 파일의 처음 생성 시부터 해서 현재까지 리두가 필요한데 생성 시점 이후에 리두가 없어서 오류 발생
SYS@ora19c> alter database recover datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf';
alter database recover datafile '/u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf'
*
ERROR at line 1:
ORA-00279: change 2855746 generated at 12/16/2025 14:50:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc
ORA-00280: change 2855746 for thread 1 is in sequence #28
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO INACTIVE 2856849 16-DEC-25 2856852 16-DEC-25 0
2 1 30 52428800 512 1 NO INACTIVE 2856852 16-DEC-25 2856855 16-DEC-25 0
3 1 31 52428800 512 1 NO CURRENT 2856855 16-DEC-25 1.8447E+19 0
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2856855
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2856855
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2856855
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf RECOVER 2856855 <<- 복구 실패한 데이터 파일
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2856855손상된 테이블스페이스 drop
# drop 불가능 -> 작업 중이던 프로세스들이 있어서
SYS@ora19c> drop tablespace insa_tbs including contents and datafiles;
drop tablespace insa_tbs including contents and datafiles
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
# 데이터베이스 재시작
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2857475
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2857475
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2857475
5 /u01/app/oracle/oradata/ORA19C/insa_tbs01.dbf RECOVER 2856855
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2857475
# drop 가능
SYS@ora19c> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.원상복구
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘recover’ -> ‘/u01/app/oracle/oradata/ORA19C/recover’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.시나리오 6 : 시스템 데이터 파일 손상(리두 로그 존재)
현재 체크포인트 확인
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2845590 15-DEC-25 04.00.14.000000000 PM
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2845590
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2845590
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2845590
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2845590
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0
SYS@ora19c> select count(*) from user$;
COUNT(*)
----------
136
장애 유발
# 장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/system01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/system01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/system01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/system01.dbf: No such file or directory
# 딕셔너리 캐시를 먼저 조회하기 때문에 오류 X
SYS@ora19c> select count(*) from tab$;
COUNT(*)
----------
2227
# 체크 포인트 발생 -> 세션 끊김
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25963
Session ID: 125 Serial number: 39739
SYS@ora19c> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0 0system datafile 손상 복구 작업
- system, temp, active redo log file은 offline 불가
1. 백업본 찾아서 restore
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2845590
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2845590
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2845590
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2845590
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls system01.dbf
system01.dbf
[oracle@ora19c noarch]$ cp -v system01.dbf /u01/app/oracle/oradata/ORA19C/system01.dbf
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
[oracle@ora19c noarch]$ exit
exit2. 백업 이후 리두 적용
SYS@ora19c> recover datafile 1
Media recovery complete.3. 데이터베이스 open
SYS@ora19c> alter database open;
Database altered.시나리오 7 : 시스템 데이터 파일 손상(리두 로그 X)
현재 체크포인트 확인
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2845590 15-DEC-25 04.00.14.000000000 PM
SYS@ora19c> select file#, name, status, checkpoint_change# from v$datafile;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------- ------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf SYSTEM 2845590
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 2845590
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 2845590
7 /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 2845590
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 26 52428800 512 1 NO INACTIVE 2767903 11-DEC-25 2797846 12-DEC-25 0
2 1 27 52428800 512 1 NO INACTIVE 2797846 12-DEC-25 2822872 12-DEC-25 0
3 1 28 52428800 512 1 NO CURRENT 2822872 12-DEC-25 1.8447E+19 0
SYS@ora19c> select count(*) from user$;
COUNT(*)
----------
136로그 스위치 발생
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 29 52428800 512 1 NO INACTIVE 2846660 16-DEC-25 2846663 16-DEC-25 0
2 1 30 52428800 512 1 NO INACTIVE 2846663 16-DEC-25 2846667 16-DEC-25 0
3 1 31 52428800 512 1 NO CURRENT 2846667 16-DEC-25 1.8447E+19 0
장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/system01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/system01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/system01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/system01.dbf: No such file or directory
# 체크 포인트 발생
SYS@ora19c> alter system checkpoint;
System altered.
# 새 접속 시 오류 발생
SYS@ora19c> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@> exit# sysdba로 재접속 및 상태 확인
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 16 16:13:51 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/system01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/system01.dbf: No such file or directory
SYS@ora19c> select status from v$instance;
STATUS
------------
OPEN1. 백업본 찾아서 recover
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0 0
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls system01.dbf
system01.dbf
[oracle@ora19c noarch]$ cp -v system01.dbf /u01/app/oracle/oradata/ORA19C/system01.dbf
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
[oracle@ora19c noarch]$ exit
exit
2. 백업 이후 리두 정보 적용을 수행했지만 리두가 없어서 오류 발생
SYS@ora19c> recover datafile 1
ORA-00279: change 2845587 generated at 12/15/2025 16:00:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_16/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7 - 완전 복구 실패
불완전 복구 방식 진행
- 모든 데이터 파일, 컨트롤 파일, 리두 로그 파일 restore
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@ora19c noarch]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘recover’ -> ‘/u01/app/oracle/oradata/ORA19C/recover’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| 251215 TIL (0) | 2025.12.15 |
|---|---|
| 251212 TIL (0) | 2025.12.12 |
| 251211 TIL (0) | 2025.12.11 |
| 251210 TIL (1) | 2025.12.11 |
| 251209 TIL (0) | 2025.12.09 |