251224 TIL
2025. 12. 24. 19:30ㆍCourses/아이티윌 오라클 DBA 과정
시나리오 6 : 백업 받지 않은 테이블스페이스에 데이터파일 손상되었을 경우 기존 위치가 아닌 새로운 위치로 복구
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2874811 23-DEC-25 04.40.37.000000000 PM
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2874811 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2874811 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2874811 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2874811 ONLINE
5 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2875465 ONLINE
SYS@ora19c> select a.file#, a.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#; 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2874811 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2874811 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2874811 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2875465 NOT ACTIVE 0
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2874811 NOT ACTIVE 2864455 2025-12-23 14:25:45
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ------------
3 34 /u01/app/oracle/oradata/ORA19C/redo03.log 50 YES INACTIVE 2874808 2874811
2 33 /u01/app/oracle/oradata/ORA19C/redo02.log 50 YES INACTIVE 2874805 2874808
1 35 /u01/app/oracle/oradata/ORA19C/redo01.log 50 NO CURRENT 2874811 1.8447E+19
SYS@ora19c> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
29 /home/oracle/arch1/arch_1_29_1218118200.arc 2850110 2864976
29 /home/oracle/arch2/arch_1_29_1218118200.arc 2850110 2864976
30 /home/oracle/arch1/arch_1_30_1218118200.arc 2864976 2866763
30 /home/oracle/arch2/arch_1_30_1218118200.arc 2864976 2866763
31 /home/oracle/arch1/arch_1_31_1218118200.arc 2866763 2866808
31 /home/oracle/arch2/arch_1_31_1218118200.arc 2866763 2866808
32 /home/oracle/arch1/arch_1_32_1218118200.arc 2866808 2874805
32 /home/oracle/arch2/arch_1_32_1218118200.arc 2866808 2874805
33 /home/oracle/arch1/arch_1_33_1218118200.arc 2874805 2874808
33 /home/oracle/arch2/arch_1_33_1218118200.arc 2874805 2874808
34 /home/oracle/arch1/arch_1_34_1218118200.arc 2874808 2874811
34 /home/oracle/arch2/arch_1_34_1218118200.arc 2874808 2874811
12 rows selected.
SYS@ora19c> ! ls arch*
arch1:
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc
arch2:
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf
/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf: No such file or directory
SYS@ora19c> shutdown immediate
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@ora19c> conn / as sysdba
Connected.
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf'
SYS@ora19c> seelct * from v$recover_file;
SP2-0734: unknown command beginning "seelct * f..." - rest of line ignored.
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
5 ONLINE ONLINE FILE NOT FOUND 0 0
offline
SYS@ora19c> alter database datafile 5 offline;
Database altered.
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2875465백업 받지 않은 테이블스페이스에 속한 파일을 새로운 위치에 재생성
SYS@ora19c> alter database create datafile '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf' as '/home/oracle/data_tbs01.dbf';
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 ONLINE
5 DATA_TBS /home/oracle/data_tbs01.dbf 2875465 RECOVER
SYS@ora19c> ! ls data_tbs01.dbf
data_tbs01.dbf recover
SYS@ora19c> recover datafile 5
ORA-00279: change 2873627 generated at 12/23/2025 16:36:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_32_1218118200.arc
ORA-00280: change 2873627 for thread 1 is in sequence #32
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2874805 generated at 12/23/2025 16:40:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_33_1218118200.arc
ORA-00280: change 2874805 for thread 1 is in sequence #33
Log applied.
Media recovery complete.alert log
2025-12-24T10:26:01.891187+09:00
ALTER DATABASE RECOVER datafile 5
2025-12-24T10:26:01.893960+09:00
Media Recovery Start
2025-12-24T10:26:01.899822+09:00
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 5 ...
2025-12-24T10:26:09.176560+09:00
ALTER DATABASE RECOVER CONTINUE DEFAULT
2025-12-24T10:26:09.179352+09:00
Media Recovery Log /home/oracle/arch2/arch_1_32_1218118200.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
2025-12-24T10:26:09.228183+09:00
Media Recovery Log /home/oracle/arch2/arch_1_33_1218118200.arc
2025-12-24T10:26:09.237858+09:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 34 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo03.log
2025-12-24T10:26:09.239607+09:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 35 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo01.log
2025-12-24T10:26:09.273205+09:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 36 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo02.log
2025-12-24T10:26:09.279092+09:00
Media Recovery Complete (ora19c)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULTonline
SYS@ora19c> alter database datafile 5 online;
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 ONLINE
5 DATA_TBS /home/oracle/data_tbs01.dbf 2980726 ONLINESYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /home/oracle/data_tbs01.dbf 2980726 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, a.next_chan ge#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE# NEXT_ CHANGE#
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ----- -------
3 34 /u01/app/oracle/oradata/ORA19C/redo03.log 50 YES INACTIVE 2874808 2874811
2 36 /u01/app/oracle/oradata/ORA19C/redo02.log 50 NO CURRENT 2979716 1.8 447E+19
1 35 /u01/app/oracle/oradata/ORA19C/redo01.log 50 YES INACTIVE 2874811 2979716
SYS@ora19c> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
29 /home/oracle/arch1/arch_1_29_1218118200.arc 2850110 2864976
29 /home/oracle/arch2/arch_1_29_1218118200.arc 2850110 2864976
30 /home/oracle/arch1/arch_1_30_1218118200.arc 2864976 2866763
30 /home/oracle/arch2/arch_1_30_1218118200.arc 2864976 2866763
31 /home/oracle/arch1/arch_1_31_1218118200.arc 2866763 2866808
31 /home/oracle/arch2/arch_1_31_1218118200.arc 2866763 2866808
32 /home/oracle/arch1/arch_1_32_1218118200.arc 2866808 2874805 <<- 2873627 2025-12-23 16:36:47
32 /home/oracle/arch2/arch_1_32_1218118200.arc 2866808 2874805
33 /home/oracle/arch1/arch_1_33_1218118200.arc 2874805 2874808
33 /home/oracle/arch2/arch_1_33_1218118200.arc 2874805 2874808
34 /home/oracle/arch1/arch_1_34_1218118200.arc 2874808 2874811
34 /home/oracle/arch2/arch_1_34_1218118200.arc 2874808 2874811
35 /home/oracle/arch1/arch_1_35_1218118200.arc 2874811 2979716
35 /home/oracle/arch2/arch_1_35_1218118200.arc 2874811 2979716
14 rows selected.
특정 테이블스페이스에 대해서 백업
SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /home/oracle/data_tbs01.dbf 2980726 NOT ACTIVE 0
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
SYS@ora19c> alter tablespace data_tbs begin backup;
Tablespace altered.
SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /home/oracle/data_tbs01.dbf 2982943 ACTIVE 2982943 2025-12-24 11:15:42
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
SYS@ora19c> ! ls /home/oracle/backup/arch/open
control01.ctl create_control.txt sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SYS@ora19c> ! cp -v /home/oracle/data_tbs01.dbf /home/oracle/backup/arch/open
‘/home/oracle/data_tbs01.dbf’ -> ‘/home/oracle/backup/arch/open/data_tbs01.dbf’
SYS@ora19c> alter tablespace data_tbs end backup;
Tablespace altered.
SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /home/oracle/data_tbs01.dbf 2982943 NOT ACTIVE 2982943 2025-12-24 11:15:42
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2979717 NOT ACTIVE 2864455 2025-12-23 14:25:45현재 control file 백업
SYS@ora19c> alter database backup controlfile to '/home/oracle/backup/arch/open/control_20251224.ctl';
Database altered.
SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/backup/arch/open/create_control_20251224.txt';
Database altered.
SYS@ora19c> ! ls /home/oracle/backup/arch/open/
control01.ctl create_control_20251224.txt data_tbs01.dbf system01.dbf undotbs01.dbf
control_20251224.ctl create_control.txt sysaux01.dbf temp01.dbf users01.dbfSYS@ora19c> alter system archive log current;
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ------------
3 37 /u01/app/oracle/oradata/ORA19C/redo03.log 50 NO CURRENT 2983450 1.8447E+19
2 36 /u01/app/oracle/oradata/ORA19C/redo02.log 50 YES ACTIVE 2979716 2983450
1 35 /u01/app/oracle/oradata/ORA19C/redo01.log 50 YES INACTIVE 2874811 2979716
SYS@ora19c> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
29 /home/oracle/arch1/arch_1_29_1218118200.arc 2850110 2864976
29 /home/oracle/arch2/arch_1_29_1218118200.arc 2850110 2864976
30 /home/oracle/arch1/arch_1_30_1218118200.arc 2864976 2866763
30 /home/oracle/arch2/arch_1_30_1218118200.arc 2864976 2866763
31 /home/oracle/arch1/arch_1_31_1218118200.arc 2866763 2866808
31 /home/oracle/arch2/arch_1_31_1218118200.arc 2866763 2866808
32 /home/oracle/arch1/arch_1_32_1218118200.arc 2866808 2874805
32 /home/oracle/arch2/arch_1_32_1218118200.arc 2866808 2874805
33 /home/oracle/arch1/arch_1_33_1218118200.arc 2874805 2874808
33 /home/oracle/arch2/arch_1_33_1218118200.arc 2874805 2874808
34 /home/oracle/arch1/arch_1_34_1218118200.arc 2874808 2874811
34 /home/oracle/arch2/arch_1_34_1218118200.arc 2874808 2874811
35 /home/oracle/arch1/arch_1_35_1218118200.arc 2874811 2979716
35 /home/oracle/arch2/arch_1_35_1218118200.arc 2874811 2979716
36 /home/oracle/arch1/arch_1_36_1218118200.arc 2979716 2983450
36 /home/oracle/arch2/arch_1_36_1218118200.arc 2979716 2983450
16 rows selected.
SYS@ora19c> ! ls /home/oracle/arch*
/home/oracle/arch1:
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc arch_1_35_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc arch_1_36_1218118200.arc
/home/oracle/arch2:
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc arch_1_35_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc arch_1_36_1218118200.arc
SYS@ora19c> ! df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 971M 0 971M 0% /dev
tmpfs 990M 444M 546M 45% /dev/shm
tmpfs 990M 18M 973M 2% /run
tmpfs 990M 0 990M 0% /sys/fs/cgroup
/dev/mapper/ol-root 47G 25G 23G 53% /
/dev/sda1 1014M 218M 797M 22% /boot
tmpfs 198M 12K 198M 1% /run/user/42
tmpfs 198M 0 198M 0% /run/user/54321시나리오 7 : non-system datafile이 손상된 경우 기존 위치가 아닌 새로운 위치로 복구
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /home/oracle/data_tbs01.dbf 2983450 ONLINE장애 유발
SYS@ora19c> ! rm /home/oracle/data_tbs01.dbf
SYS@ora19c> ! ls /home/oracle/data_tbs01.dbf
ls: cannot access /home/oracle/data_tbs01.dbf: No such file or directory복구
1. 복구 수행해야 할 대상 데이터파일 offline
SYS@ora19c> alter database datafile 5 offline;
Database altered.2. 백업을 새로운 위치로 restore
SYS@ora19c> ! cp -v backup/arch/open/data_tbs01.dbf /u01/app/oracle/oradata/ORA19C/
‘backup/arch/open/data_tbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf’3. 컨트롤 파일에 기존 위치가 아닌 새로운 위치로 변경
SYS@ora19c> alter database rename file '/home/oracle/data_tbs01.dbf' to '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf';
Database altered.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2983450 RECOVER4. 백업 이후 변경정보(리두) 적용
SYS@ora19c> recover datafile 5
Media recovery complete.
-- alert log
2025-12-24T11:39:03.774453+09:00
ALTER DATABASE RECOVER datafile 5
2025-12-24T11:39:03.774765+09:00
Media Recovery Start
2025-12-24T11:39:03.775637+09:00
Serial Media Recovery started
2025-12-24T11:39:03.849399+09:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 36 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo02.log
2025-12-24T11:39:03.891670+09:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 37 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo03.log
2025-12-24T11:39:03.909135+09:00
Media Recovery Complete (ora19c)
Completed: ALTER DATABASE RECOVER datafile 55. 복구 완료된 대상 데이터파일 online으로 변경
SYS@ora19c> alter database datafile 5 online;
Database altered.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2983896 ONLINE시나리오 8 : 테이블스페이스에 속한 여러 데이터 파일 중에 특정한 파일이 손상되었을 경우 복구
테이블스페이스 데이터 파일 추가
SYS@ora19c> alter tablespace data_tbs add datafile '/u01/app/oracle/oradata/ORA19C/data_tbs02.dbf' size 5m;
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
2 2985332 2025-12-24 12:02:58 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf 2985333 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2983896 ONLINE
6 rows selected.SYS@ora19c> create table hr.emp_2025
tablespace data_tbs
as select * from hr.employees; 2 3
Table created.
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_2025'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf
SYS@ora19c> insert into hr.emp_2025 select * from hr.emp_2025;
107 rows created.
...
SYS@ora19c> /
27392 rows created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.emp_2025;
COUNT(*)
----------
54784
SYS@ora19c> select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2025'
and owner = 'HR'
group by f.tablespace_name, f.file_name; 2 3 4 5 6
TABLESPACE_NAME FILE_NAME COUNT(*)
------------------------------ -------------------------------------------------- ----------
DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 17
DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf 3SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
2 /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf 2985333 NOT ACTIVE 0 <<- 백업 X
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
5 /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2983896 NOT ACTIVE 2982943 2025-12-24 11:15:42
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
6 rows selected.특정한 테이블스페이스에 속한 데이터 파일 백업
SYS@ora19c> alter tablespace data_tbs begin backup;
Tablespace altered.
SYS@ora19c> ! cp -v /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf /home/oracle/backup/arch/open/data_tbs02.dbf
‘/u01/app/oracle/oradata/ORA19C/data_tbs02.dbf’ -> ‘/home/oracle/backup/arch/open/data_tbs02.dbf’
SYS@ora19c> alter tablespace data_tbs end backup;
Tablespace altered.장애 유발
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf: No such file or directory복구
1. 테이블스페이스 offline temporary
SYS@ora19c> alter tablespace data_tbs offline temporary;
Tablespace altered.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
2 2985332 2025-12-24 12:02:58 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf 2987195 OFFLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2987195 OFFLINE
6 rows selected.2. 손상된 데이터파일 백업본으로 restore
SYS@ora19c> ! cp -v /home/oracle/backup/arch/open/data_tbs02.dbf /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf
‘/home/oracle/backup/arch/open/data_tbs02.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/data_tbs02.dbf’3. 백업 이후 변경정보(리두) 적용
SYS@ora19c> recover datafile 2;
Media recovery complete.
4. 복구 완료된 테이블스페이스 online으로 변경
SYS@ora19c> alter tablespace data_tbs online;
Tablespace altered.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE
2 2985332 2025-12-24 12:02:58 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs02.dbf 2987287 ONLINE
5 2873627 2025-12-23 16:36:47 DATA_TBS /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf 2987287 ONLINE
6 rows selected.원상복구
새로 생성한 테이블스페이스 삭제
SYS@ora19c> drop tablespace data_tbs including contents and datafiles;
Tablespace dropped.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 ONLINE새로 생성한 테이블스페이스 데이터파일 백업본 삭제
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/arch/open
[oracle@ora19c open]$ ls
control01.ctl create_control_20251224.txt data_tbs01.dbf sysaux01.dbf temp01.dbf users01.dbf
control_20251224.ctl create_control.txt data_tbs02.dbf system01.dbf undotbs01.dbf
[oracle@ora19c open]$ rm data_tbs*
[oracle@ora19c open]$ ls
control01.ctl create_control_20251224.txt sysaux01.dbf temp01.dbf users01.dbf
control_20251224.ctl create_control.txt system01.dbf undotbs01.dbf
[oracle@ora19c open]$ exit
exitSYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2983450 NOT ACTIVE 2864455 2025-12-23 14:25:45
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ------------
3 37 /u01/app/oracle/oradata/ORA19C/redo03.log 50 NO CURRENT 2983450 1.8447E+19
2 36 /u01/app/oracle/oradata/ORA19C/redo02.log 50 YES INACTIVE 2979716 2983450
1 35 /u01/app/oracle/oradata/ORA19C/redo01.log 50 YES INACTIVE 2874811 2979716
SYS@ora19c> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
29 /home/oracle/arch1/arch_1_29_1218118200.arc 2850110 2864976
29 /home/oracle/arch2/arch_1_29_1218118200.arc 2850110 2864976
30 /home/oracle/arch1/arch_1_30_1218118200.arc 2864976 2866763
30 /home/oracle/arch2/arch_1_30_1218118200.arc 2864976 2866763
31 /home/oracle/arch1/arch_1_31_1218118200.arc 2866763 2866808
31 /home/oracle/arch2/arch_1_31_1218118200.arc 2866763 2866808
32 /home/oracle/arch1/arch_1_32_1218118200.arc 2866808 2874805
32 /home/oracle/arch2/arch_1_32_1218118200.arc 2866808 2874805
33 /home/oracle/arch1/arch_1_33_1218118200.arc 2874805 2874808
33 /home/oracle/arch2/arch_1_33_1218118200.arc 2874805 2874808
34 /home/oracle/arch1/arch_1_34_1218118200.arc 2874808 2874811
34 /home/oracle/arch2/arch_1_34_1218118200.arc 2874808 2874811
35 /home/oracle/arch1/arch_1_35_1218118200.arc 2874811 2979716
35 /home/oracle/arch2/arch_1_35_1218118200.arc 2874811 2979716
36 /home/oracle/arch1/arch_1_36_1218118200.arc 2979716 2983450
36 /home/oracle/arch2/arch_1_36_1218118200.arc 2979716 2983450
16 rows selected.
이전 백업본, 아카이브 파일 삭제
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/arch/open
[oracle@ora19c open]$ ls
control01.ctl create_control_20251224.txt sysaux01.dbf temp01.dbf users01.dbf
control_20251224.ctl create_control.txt system01.dbf undotbs01.dbf
[oracle@ora19c open]$ rm *
[oracle@ora19c open]$ cd
[oracle@ora19c ~]$ cd arch1
[oracle@ora19c arch1]$ ls
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc arch_1_35_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc arch_1_36_1218118200.arc
[oracle@ora19c arch1]$ rm *
[oracle@ora19c arch1]$ cd ..
[oracle@ora19c ~]$ cd arch2
[oracle@ora19c arch2]$ ls
arch_1_29_1218118200.arc arch_1_31_1218118200.arc arch_1_33_1218118200.arc arch_1_35_1218118200.arc
arch_1_30_1218118200.arc arch_1_32_1218118200.arc arch_1_34_1218118200.arc arch_1_36_1218118200.arc
[oracle@ora19c arch2]$ rm *
[oracle@ora19c arch2]$ cd
[oracle@ora19c ~]$ ls backup/arch/open
[oracle@ora19c ~]$ ls arch*
arch1:
arch2:
[oracle@ora19c ~]$ exit
exit새롭게 백업
SYS@ora19c> alter database begin backup;
Database altered.
SYS@ora19c> ! cp -v /u01/app/oracle/oradata/ORA19C/*.dbf /home/oracle/backup/arch/open
‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’ -> ‘/home/oracle/backup/arch/open/sysaux01.dbf’
‘/u01/app/oracle/oradata/ORA19C/system01.dbf’ -> ‘/home/oracle/backup/arch/open/system01.dbf’
‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’ -> ‘/home/oracle/backup/arch/open/temp01.dbf’
‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’ -> ‘/home/oracle/backup/arch/open/undotbs01.dbf’
‘/u01/app/oracle/oradata/ORA19C/users01.dbf’ -> ‘/home/oracle/backup/arch/open/users01.dbf’
SYS@ora19c> alter database end backup;
Database altered.
SYS@ora19c> select a.file#, a.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#(+); 2 3
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TO_CHAR(B.TIME,'YYY
---------- -------------------------------------------------- ------------------ ------------------ ---------- -------------------
1 /u01/app/oracle/oradata/ORA19C/system01.dbf 2990993 NOT ACTIVE 2990993 2025-12-24 13:57:19
3 /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2990993 NOT ACTIVE 2990993 2025-12-24 13:57:19
4 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2990993 NOT ACTIVE 2990993 2025-12-24 13:57:19
7 /u01/app/oracle/oradata/ORA19C/users01.dbf 2990993 NOT ACTIVE 2990993 2025-12-24 13:57:19
SYS@ora19c> alter database backup controlfile to '/home/oracle/backup/arch/open/control_20251224.ctl';
Database altered.
SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/backup/arch/open/create_control_20251224.txt';
Database altered.
SYS@ora19c> ! ls /home/oracle/backup/arch/open/
control_20251224.ctl sysaux01.dbf temp01.dbf users01.dbf
create_control_20251224.txt system01.dbf undotbs01.dbf
SYS@ora19c> alter system archive log current;
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ------------
3 37 /u01/app/oracle/oradata/ORA19C/redo03.log 50 YES ACTIVE 2983450 2992105
2 36 /u01/app/oracle/oradata/ORA19C/redo02.log 50 YES INACTIVE 2979716 2983450
1 38 /u01/app/oracle/oradata/ORA19C/redo01.log 50 NO CURRENT 2992105 1.8447E+19
SYS@ora19c> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
29 /home/oracle/arch1/arch_1_29_1218118200.arc 2850110 2864976
29 /home/oracle/arch2/arch_1_29_1218118200.arc 2850110 2864976
30 /home/oracle/arch1/arch_1_30_1218118200.arc 2864976 2866763
30 /home/oracle/arch2/arch_1_30_1218118200.arc 2864976 2866763
31 /home/oracle/arch1/arch_1_31_1218118200.arc 2866763 2866808
31 /home/oracle/arch2/arch_1_31_1218118200.arc 2866763 2866808
32 /home/oracle/arch1/arch_1_32_1218118200.arc 2866808 2874805
32 /home/oracle/arch2/arch_1_32_1218118200.arc 2866808 2874805
33 /home/oracle/arch1/arch_1_33_1218118200.arc 2874805 2874808
33 /home/oracle/arch2/arch_1_33_1218118200.arc 2874805 2874808
34 /home/oracle/arch1/arch_1_34_1218118200.arc 2874808 2874811
34 /home/oracle/arch2/arch_1_34_1218118200.arc 2874808 2874811
35 /home/oracle/arch1/arch_1_35_1218118200.arc 2874811 2979716
35 /home/oracle/arch2/arch_1_35_1218118200.arc 2874811 2979716
36 /home/oracle/arch1/arch_1_36_1218118200.arc 2979716 2983450
36 /home/oracle/arch2/arch_1_36_1218118200.arc 2979716 2983450
37 /home/oracle/arch1/arch_1_37_1218118200.arc 2983450 2992105 <<- 2983450
37 /home/oracle/arch2/arch_1_37_1218118200.arc 2983450 2992105
18 rows selected. - 아카이브 파일은 앞에서 삭제했기때문에 물리적으로 존재하지 않음, but 컨트롤 파일에는 아직 정보가 있음 → 지우고 싶다면 컨트롤 파일을 재생성
시나리오 9 : 모든 데이터파일 손상되었을 경우 복구
장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/system01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.dbf: No such file or directory[oracle@ora19c ~]$ sqlplus hr/hr
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 24 14:23:41 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
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: 3SYS@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 595591168 bytes
Database Buffers 218103808 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
3 ONLINE ONLINE FILE NOT FOUND 0 0
4 ONLINE ONLINE FILE NOT FOUND 0 0
7 ONLINE ONLINE FILE NOT FOUND 0 0
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED 복구
1. 모든 백업 데이터파일을 restore
SYS@ora19c> ! cp -v backup/arch/open/*.dbf /u01/app/oracle/oradata/ORA19C/
‘backup/arch/open/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘backup/arch/open/system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘backup/arch/open/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘backup/arch/open/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘backup/arch/open/users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’2. 백업 이후 변경정보(리두) 적용
SYS@ora19c> recover database
Media recovery complete.
-- alert log
2025-12-24T14:28:12.988432+09:00
ALTER DATABASE RECOVER database
2025-12-24T14:28:12.996832+09:00
Media Recovery Start
Started logmerger process
2025-12-24T14:28:13.173574+09:00
Parallel Media Recovery started with 2 slaves
2025-12-24T14:28:13.213117+09:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 37 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo03.log
2025-12-24T14:28:14.061196+09:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 38 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo01.log
2025-12-24T14:28:14.345586+09:00
Media Recovery Complete (ora19c)
Completed: ALTER DATABASE RECOVER database3. 데이터베이스 open
SYS@ora19c> alter database open;
Database altered.시나리오 10 : undo data file 손상 복구
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 ONLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 ONLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 ONLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 ONLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 ONLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 ONLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 ONLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 ONLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 ONLINE
11 rows selected.SYS@ora19c> select s.username, s.sid, s.serial#, r.name, t.ubafil, t.xidusn, 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; 2 3 4
no rows selected
-- << HR SESSION >>
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
-- << SYS SESSION >>
SYS@ora19c> /
USERNAME SID SERIAL# NAME UBAFIL XIDUSN UBABLK USED_UBLK
------------------------------ ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ----------
HR 39 53037 _SYSSMU10_930580995$ 4 10 438 1
SYS@ora19c> alter system kill session '39,53037' immediate;
System altered.SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3192850 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3192850 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 3192850 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3192850 ONLINE
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.장애 유발
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
4 ONLINE ONLINE FILE NOT FOUND 0 0백업
1. 백업 파일 찾아서 restore
SYS@ora19c> ! cp -v backup/arch/open/undotbs01.dbf /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
‘backup/arch/open/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’2. 백업 이후 변경 정보 (리두) 적용
SYS@ora19c> recover datafile 4
Media recovery complete.
-- alert log
2025-12-24T15:07:13.640427+09:00
ALTER DATABASE RECOVER datafile 4
2025-12-24T15:07:13.641496+09:00
Media Recovery Start
2025-12-24T15:07:13.647227+09:00
Serial Media Recovery started
2025-12-24T15:07:13.699326+09:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 37 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo03.log
2025-12-24T15:07:13.782979+09:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 38 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo01.log
2025-12-24T15:07:13.787617+09:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA19C/redo02.log
2025-12-24T15:07:13.874292+09:00
Media Recovery Complete (ora19c)
Completed: ALTER DATABASE RECOVER datafile 43. 데이터베이스 open
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> select s.username, s.sid, s.serial#, r.name, t.ubafil, t.xidusn, 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;
2 3 4
USERNAME SID SERIAL# NAME UBAFIL XIDUSN UBABLK USED_UBLK
------------------------------ ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ----------
HR 163 60350 _SYSSMU1_1261223759$ 4 1 2216 1
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 ONLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 ONLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 ONLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 ONLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 ONLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 ONLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 ONLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 ONLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 ONLINE
11 rows selected.
시나리오 11 : 운영 중에 undo data file이 손상 복구
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3195615 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3195615 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 3195615 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3195615 ONLINE
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 ONLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 ONLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 ONLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 ONLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 ONLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 ONLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 ONLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 ONLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 ONLINE
11 rows selected.
SYS@ora19c> select s.username, s.sid, s.serial#, r.name, t.ubafil, t.xidusn, 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; 2 3 4
no rows selected
-- << HR >>
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
-- << SYS >>
SYS@ora19c> /
USERNAME SID SERIAL# NAME UBAFIL XIDUSN UBABLK USED_UBLK
------------------------------ ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ----------
HR 163 60350 _SYSSMU2_27624015$ 4 2 661 1장애 유발
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
-- << HR >>
[oracle@ora19c ~]$ sqlplus hr/hr
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 24 15:17:10 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3복구
undo tablespace 생성
SYS@ora19c> create undo tablespace undo datafile '/u01/app/oracle/oradata/ORA19C/undo01.dbf' size 10m autoextend on;
Tablespace created.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3195615 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3195615 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 3195615 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3195615 ONLINE
5 3196735 2025-12-24 15:21:15 UNDO /u01/app/oracle/oradata/ORA19C/undo01.dbf 3196736 ONLINEdefault undo tablepsace 수정
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 ONLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 ONLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 ONLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 ONLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 ONLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 ONLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 ONLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 ONLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 ONLINE
11 _SYSSMU11_2227802525$ PUBLIC UNDO OFFLINE
12 _SYSSMU12_4052055371$ PUBLIC UNDO OFFLINE
13 _SYSSMU13_2264748279$ PUBLIC UNDO OFFLINE
14 _SYSSMU14_1082969648$ PUBLIC UNDO OFFLINE
15 _SYSSMU15_238415375$ PUBLIC UNDO OFFLINE
16 _SYSSMU16_4042886290$ PUBLIC UNDO OFFLINE
17 _SYSSMU17_1650302642$ PUBLIC UNDO OFFLINE
18 _SYSSMU18_1412930375$ PUBLIC UNDO OFFLINE
19 _SYSSMU19_998689733$ PUBLIC UNDO OFFLINE
20 _SYSSMU20_1166304545$ PUBLIC UNDO OFFLINE
21 rows selected.
SYS@ora19c> alter system set undo_tablespace = undo;
System altered.
SYS@ora19c> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 OFFLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 OFFLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 OFFLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 OFFLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 OFFLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 OFFLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 OFFLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 OFFLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 OFFLINE
11 _SYSSMU11_2227802525$ PUBLIC UNDO ONLINE
12 _SYSSMU12_4052055371$ PUBLIC UNDO ONLINE
13 _SYSSMU13_2264748279$ PUBLIC UNDO ONLINE
14 _SYSSMU14_1082969648$ PUBLIC UNDO ONLINE
15 _SYSSMU15_238415375$ PUBLIC UNDO ONLINE
16 _SYSSMU16_4042886290$ PUBLIC UNDO ONLINE
17 _SYSSMU17_1650302642$ PUBLIC UNDO ONLINE
18 _SYSSMU18_1412930375$ PUBLIC UNDO ONLINE
19 _SYSSMU19_998689733$ PUBLIC UNDO ONLINE
20 _SYSSMU20_1166304545$ PUBLIC UNDO ONLINE
21 rows selected.
진행 중인 트랜잭션 kill
SYS@ora19c> select s.username, s.sid, s.serial#, r.name, t.ubafil, t.xidusn, 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; 2 3 4
USERNAME SID SERIAL# NAME UBAFIL XIDUSN UBABLK USED_UBLK
------------------------------ ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ----------
HR 163 60350 _SYSSMU2_27624015$ 4 2 661 1
SYS@ora19c> alter system kill session '163,60350' immediate;
System altered.
SYS@ora19c> select s.username, s.sid, s.serial#, r.name, t.ubafil, t.xidusn, 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; 2 3 4
no rows selected
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 OFFLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 ONLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 OFFLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 OFFLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 OFFLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 OFFLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 OFFLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 OFFLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 OFFLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 OFFLINE
11 _SYSSMU11_2227802525$ PUBLIC UNDO ONLINE
12 _SYSSMU12_4052055371$ PUBLIC UNDO ONLINE
13 _SYSSMU13_2264748279$ PUBLIC UNDO ONLINE
14 _SYSSMU14_1082969648$ PUBLIC UNDO ONLINE
15 _SYSSMU15_238415375$ PUBLIC UNDO ONLINE
16 _SYSSMU16_4042886290$ PUBLIC UNDO ONLINE
17 _SYSSMU17_1650302642$ PUBLIC UNDO ONLINE
18 _SYSSMU18_1412930375$ PUBLIC UNDO ONLINE
19 _SYSSMU19_998689733$ PUBLIC UNDO ONLINE
20 _SYSSMU20_1166304545$ PUBLIC UNDO ONLINE
21 rows selected.
기존 undo tablespce 삭제
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SYS@ora19c> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 3085
Session ID: 125 Serial number: 53138
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf'SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3195615 SYSTEM
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3195615 ONLINE
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 3195615 ONLINE
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3195615 ONLINE
5 3196735 2025-12-24 15:21:15 UNDO /u01/app/oracle/oradata/ORA19C/undo01.dbf 3196736 ONLINE
SYS@ora19c> alter database datafile 4 offline;
Database altered.
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_1261223759$ PUBLIC UNDOTBS1 OFFLINE
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 OFFLINE
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 OFFLINE
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 OFFLINE
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 OFFLINE
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 OFFLINE
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 OFFLINE
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 OFFLINE
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 OFFLINE
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 OFFLINE
11 _SYSSMU11_2227802525$ PUBLIC UNDO ONLINE
12 _SYSSMU12_4052055371$ PUBLIC UNDO ONLINE
13 _SYSSMU13_2264748279$ PUBLIC UNDO ONLINE
14 _SYSSMU14_1082969648$ PUBLIC UNDO ONLINE
15 _SYSSMU15_238415375$ PUBLIC UNDO ONLINE
16 _SYSSMU16_4042886290$ PUBLIC UNDO ONLINE
17 _SYSSMU17_1650302642$ PUBLIC UNDO ONLINE
18 _SYSSMU18_1412930375$ PUBLIC UNDO ONLINE
19 _SYSSMU19_998689733$ PUBLIC UNDO ONLINE
20 _SYSSMU20_1166304545$ PUBLIC UNDO ONLINE
21 rows selected.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3297437 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3297437 SYSTEM
4 1920446 2019-04-17 02:11:02 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 3195615 RECOVER
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3297437 ONLINE
5 3196735 2025-12-24 15:21:15 UNDO /u01/app/oracle/oradata/ORA19C/undo01.dbf 3297437 ONLINE
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# CREATION_CHANGE# CREATION_TIME TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
3 5480 2019-04-17 00:56:21 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 3297437 ONLINE
1 9 2019-04-17 00:56:09 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 3297437 SYSTEM
7 32876 2019-04-17 00:57:08 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 3297437 ONLINE
5 3196735 2025-12-24 15:21:15 UNDO /u01/app/oracle/oradata/ORA19C/undo01.dbf 3297437 ONLINE
SYS@ora19c> select segment_id, segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
11 _SYSSMU11_2227802525$ PUBLIC UNDO ONLINE
12 _SYSSMU12_4052055371$ PUBLIC UNDO ONLINE
13 _SYSSMU13_2264748279$ PUBLIC UNDO ONLINE
14 _SYSSMU14_1082969648$ PUBLIC UNDO ONLINE
15 _SYSSMU15_238415375$ PUBLIC UNDO ONLINE
16 _SYSSMU16_4042886290$ PUBLIC UNDO ONLINE
17 _SYSSMU17_1650302642$ PUBLIC UNDO ONLINE
18 _SYSSMU18_1412930375$ PUBLIC UNDO ONLINE
19 _SYSSMU19_998689733$ PUBLIC UNDO ONLINE
20 _SYSSMU20_1166304545$ PUBLIC UNDO ONLINE
11 rows selected.
- 이것도 안되면 pfile에 히든 파라미터
_offline_rollback_segments를 사용해서 undo segment를 수동으로 offline으로 내린 후 삭제
SYS@ora19c> create pfile from spfile;
File created.
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd $ORACLE_HOME/dbs
[oracle@ora19c dbs]$ vi initora19c.ora
...
_offline_rollback_segments=(_SYSSMU17_1554399778$)
[oracle@ora19c dbs]$ exit
exit
SYS@ora19c> startup pfile=$ORACLE_HOME/dbs/initora19c.ora
SYS@ora19c> drop tablespace undo including contents and datafiles;
Tablespace dropped.'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| 251223 TIL (0) | 2025.12.24 |
|---|---|
| 251222 TIL (0) | 2025.12.22 |
| 251219 TIL (0) | 2025.12.22 |
| 251218 TIL (1) | 2025.12.18 |
| 251217 TIL (0) | 2025.12.18 |