Courses/아이티윌 오라클 DBA 과정
251217 TIL
DevJoy
2025. 12. 18. 18:27
시나리오 8 : undo datafile 손상(백업 이후 리두 정보 존재, 완전 복구)
현재 체크포인트 확인
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2848149 16-DEC-25 04.45.54.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 2848149 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2848149 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2848149 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2848149 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 ACTIVE 2822872 2850368
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2850368 1.8447E+19undo 세그먼트 조회
# 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 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- s.username : 트랜잭션을 수행 중인 사용자
- s.sid : 세션 식별자
- s.serial# : 세션 일련 번호
- r.name : rollback segment name, 현재 트랜잭션이 사용중인 undo segment 이름
- t.ubafil : undo block address file number, undo 블록이 저장된 데이터파일 번호
- t.xidusn : transaction undo segment number, 트랜잭션이 사용하는 undo segment 번호
- t.ubablk : undo block address block number, undo segment에서 첫 번째 undo 블록의 블록 번호
- t.used_ublk : 현재 트랜잭션이 사용 중인 undo 블록 수
현재 트랜잭션이 진행 중인 세션 KILL
---------- <<HR>> ----------
HR@ora19c> select salary from hr.employees where employee_id = 200;
SALARY
----------
4400
-- 트랜잭션 발생
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
HR@ora19c> select salary from hr.employees where employee_id = 200;
SALARY
----------
4840
---------- <<SYS>> ----------
-- 트랜잭션 수행 중인 세션 조회
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 19 18134 _SYSSMU7_2329891355$ 4 7 1407 1
-- 해당 세션 kill
SYS@ora19c> alter system kill session '19,18134' 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
-- hr 세션 끊김
---------- <<HR>> ----------
HR@ora19c> select salary from hr.employees where employee_id = 200;
select salary from hr.employees where employee_id = 200
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 4696
Session ID: 19 Serial number: 18134트랜잭션 발생
---------- <<HR1>> ----------
HR@ora19c> conn hr/hr
Connected.
HR@ora19c> select salary from hr.employees where employee_id = 200;
SALARY
----------
4400
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
HR@ora19c> select salary from hr.employees where employee_id = 200;
SALARY
----------
4840
---------- <<HR1>> ----------
HR@ora19c> conn hr/hr
Connected.
HR@ora19c> select salary from hr.employees where employee_id = 100;
SALARY
----------
24000
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 100;
1 row updated.
HR@ora19c> select salary from hr.employees where employee_id = 100;
SALARY
----------
26400
---------- <<SYS>> ----------
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 154 31592 _SYSSMU6_813816332$ 4 6 210 1
HR 166 51191 _SYSSMU7_2329891355$ 4 7 225 1장애 유발
-- 장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/undotbs01.dbf: No such file or directory
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 154 31592 _SYSSMU6_813816332$ 4 6 210 1
HR 166 51191 _SYSSMU7_2329891355$ 4 7 225 1
-- 체크 포인트 발생
SYS@ora19c> alter system checkpoint;
System altered.
-- 오류 발생할 수도 있고 안할 수도 있음
-- 오류가 발생하지 않으면 데이터베이스 재시작
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 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
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED undo datafile 복구
1. 백업 찾아서 restore
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls undotbs01.dbf
undotbs01.dbf
[oracle@ora19c noarch]$ cp -v undotbs01.dbf /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
[oracle@ora19c noarch]$ exit
exit2. 백업 이후 리두 정보 적용
SYS@ora19c> recover datafile 4;
Media recovery complete.recover datafile 4;recover datafile '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf'recover tablespace undotbs1;
3. 데이터베이스 open
SYS@ora19c> alter database open;
Database altered.시나리오 9 : undo datafile 손상(백업 이후 리두 정보 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 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 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
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> select salary from hr.employees where employee_id = 200;
SALARY
----------
4400
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
HR@ora19c> select salary from hr.employees where employee_id = 200;
SALARY
----------
4840
---------- << SYS >> ----------
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 28 7845 _SYSSMU3_2421748942$ 4 3 1274 1
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846로그 스위치 발생
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 31 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2846661 1.8447E+19
2 30 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2846658 2846661
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2846655 2846658장애 유발
-- 장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/undotbs01.dbf: No such file or directory
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 28 7845 _SYSSMU1_1261223759$ 4 1 2251 1
---------- << HR >> ----------
-- undo datafile이 손상됐지만 트랜잭션 수행 가능
HR@ora19c> rollback;
Rollback complete.
HR@ora19c> select salary from hr.employees where employee_id = 150;
SALARY
----------
10000
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 150;
1 row updated.
HR@ora19c> select salary from hr.employees where employee_id = 150;
SALARY
----------
11000
---------- << SYS >> ----------
-- 데이터베이스 재시작
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 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완전 복구 시도 → 실패
SYS@ora19c> !
# 백업본 restore
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls undotbs01.dbf
undotbs01.dbf
[oracle@ora19c noarch]$ cp -v undotbs01.dbf /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
[oracle@ora19c noarch]$ exit
exit
# 백업 이후 리두 적용 실패
SYS@ora19c> recover datafile 4
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_17/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_17/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_17/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불완전 복구
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.시나리오 10 - 1 : undo datafile 손상(백업 이후 리두 정보 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 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 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
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@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
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 151 60585 _SYSSMU7_2329891355$ 4 7 1348 1로그 스위치 발생
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 31 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2846873 1.8447E+19
2 30 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2846870 2846873
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2846867 2846870장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/undotbs01.dbf: No such file or directory
SYS@ora19c> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 26794
Session ID: 125 Serial number: 53822
SYS@ora19c> startup
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
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 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새로운 undo tablespace를 생성해서 해결
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
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2846873 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2846873 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2846873 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2846873 ONLINE1. 기존 undo datafile을 offline drop
SYS@ora19c> alter database datafile 4 offline drop;
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
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2846873 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2846873 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2846873 RECOVER
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2846873 ONLINE2. 데이터베이스 open
SYS@ora19c> alter database open;
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 2947782 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2947782 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2846873 RECOVER
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2947782 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 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 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 selected3. 새로운 undo tablespace 생성
SYS@ora19c> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/ORA19C/undo1.dbf' size 10m autoextend on;
Tablespace created.
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
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_2416213215$ PUBLIC UNDO1 OFFLINE
12 _SYSSMU12_323492533$ PUBLIC UNDO1 OFFLINE
13 _SYSSMU13_1669884409$ PUBLIC UNDO1 OFFLINE
14 _SYSSMU14_1914075282$ PUBLIC UNDO1 OFFLINE
15 _SYSSMU15_3469478410$ PUBLIC UNDO1 OFFLINE
16 _SYSSMU16_4284437115$ PUBLIC UNDO1 OFFLINE
17 _SYSSMU17_790064437$ PUBLIC UNDO1 OFFLINE
18 _SYSSMU18_1092926197$ PUBLIC UNDO1 OFFLINE
19 _SYSSMU19_3145037474$ PUBLIC UNDO1 OFFLINE
20 _SYSSMU20_1140459433$ PUBLIC UNDO1 OFFLINE
21 rows selected.4. 새로운 undo tablespace 지정
SYS@ora19c> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SYS@ora19c> alter system set undo_tablespace = undo1;
System altered.
SYS@ora19c> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO1
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
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_2416213215$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_323492533$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1669884409$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_1914075282$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_3469478410$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_4284437115$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_790064437$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_1092926197$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_3145037474$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_1140459433$ PUBLIC UNDO1 ONLINE
21 rows selected.5. 기존 undo 테이블스페이스 삭제
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
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_2416213215$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_323492533$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1669884409$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_1914075282$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_3469478410$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_4284437115$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_790064437$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_1092926197$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_3145037474$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_1140459433$ PUBLIC UNDO1 ONLINE
11 rows selected.
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 2947782 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2947782 SYSTEM
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2947782 ONLINE
5 UNDO1 /u01/app/oracle/oradata/ORA19C/undo1.dbf 2948456 ONLINE원상복구
SYS@ora19c> alter system set undo_tablespace = undotbs1 scope = spfile;
System altered.
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c dbs]$ cd ~/backup/noarch
[oracle@ora19c noarch]$ cp -v * $ORACLE_BASE/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]$ ls /u01/app/oracle/oradata/ORA19C/
control01.ctl redo01.log redo03.log system01.dbf undo1.dbf users01.dbf
recover redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
[oracle@ora19c noarch]$ rm /u01/app/oracle/oradata/ORA19C/undo1.dbf
[oracle@ora19c noarch]$ exit
exit
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.
Database opened.시나리오 10-2
현재 상태 확인
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 a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
2 from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, 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 STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
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> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
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@ora19c> conn hr/hr
Connected.
HR@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
1 row updated.
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 37 48338 _SYSSMU2_27624015$ 4 2 303 1장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/undotbs01.dbf: No such file or directory
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21892
Session ID: 125 Serial number: 52494
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 * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
4 ONLINE ONLINE FILE NOT FOUND 0 0새로운 undo tablespace를 생성해서 해결
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED
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
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE1. 기존 undo datafile을 offline drop
SYS@ora19c> alter database datafile 4 offline drop;
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
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 RECOVER
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE2. 데이터베이스 open
- undo segment의 status가 NEEDS RECOVERY로 나옴
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 NEEDS RECOVERY
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 NEEDS RECOVERY
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 NEEDS RECOVERY
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 NEEDS RECOVERY
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 NEEDS RECOVERY
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 NEEDS RECOVERY
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 NEEDS RECOVERY
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 NEEDS RECOVERY
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 NEEDS RECOVERY
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 NEEDS RECOVERY
11 rows selected.3. 새로운 undo tablespace 생성
SYS@ora19c> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/ORA19C/undo1.dbf' size 10m autoextend on;
Tablespace created.
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 2946678 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2946678 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 RECOVER
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2946678 ONLINE
5 UNDO1 /u01/app/oracle/oradata/ORA19C/undo1.dbf 2947461 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 NEEDS RECOVERY
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 NEEDS RECOVERY
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 NEEDS RECOVERY
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 NEEDS RECOVERY
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 NEEDS RECOVERY
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 NEEDS RECOVERY
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 NEEDS RECOVERY
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 NEEDS RECOVERY
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 NEEDS RECOVERY
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 NEEDS RECOVERY
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_235323571$ PUBLIC UNDO1 OFFLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 OFFLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 OFFLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 OFFLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 OFFLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 OFFLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 OFFLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 OFFLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 OFFLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 OFFLINE
21 rows selected.4. 새로운 undo tablespace 지정
SYS@ora19c> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SYS@ora19c> alter system set undo_tablespace = undo1;
System altered.
SYS@ora19c> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO1
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 NEEDS RECOVERY
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 NEEDS RECOVERY
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 NEEDS RECOVERY
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 NEEDS RECOVERY
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 NEEDS RECOVERY
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 NEEDS RECOVERY
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 NEEDS RECOVERY
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 NEEDS RECOVERY
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 NEEDS RECOVERY
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 NEEDS RECOVERY
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_235323571$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 ONLINE
21 rows selected.5. 기존 undo tablespace 삭제 오류 발생
- 기존 undo segment에 트랜잭션이 있어서 삭제 오류 발생
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1261223759$' found, terminate dropping tablespace- 데이터베이스 재시작 후 다시 시도 → 실패
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
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 NEEDS RECOVERY
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 NEEDS RECOVERY
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 NEEDS RECOVERY
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 NEEDS RECOVERY
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 NEEDS RECOVERY
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 NEEDS RECOVERY
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 NEEDS RECOVERY
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 NEEDS RECOVERY
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 NEEDS RECOVERY
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 NEEDS RECOVERY
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_235323571$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 ONLINE
21 rows selected.
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1261223759$' found, terminate dropping tablespace6. 수동으로 undo segment를 offline으로 변경
# recovery가 필요한 undo segment 조회
SYS@ora19c> select segment_name || ',' from dba_rollback_segs where status = 'NEEDS RECOVERY';
SEGMENT_NAME||','
-------------------------------
_SYSSMU1_1261223759$,
_SYSSMU2_27624015$,
_SYSSMU3_2421748942$,
_SYSSMU4_625702278$,
_SYSSMU5_2101348960$,
_SYSSMU6_813816332$,
_SYSSMU7_2329891355$,
_SYSSMU8_399776867$,
_SYSSMU9_1692468413$,
_SYSSMU10_930580995$,
10 rows selected.
# pfile 생성
SYS@ora19c> create pfile from spfile;
File created.
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.
# 수동으로 undo segment를 offline으로 변경
[oracle@ora19c dbs]$ tail -1 initora19c.ora
*.undo_tablespace='UNDO1'
[oracle@ora19c dbs]$ vi initora19c.ora
[oracle@ora19c dbs]$ tail -12 initora19c.ora
*.undo_tablespace='UNDO1'
_offline_rollback_segments=(
_SYSSMU1_1261223759$,
_SYSSMU2_27624015$,
_SYSSMU3_2421748942$,
_SYSSMU4_625702278$,
_SYSSMU5_2101348960$,
_SYSSMU6_813816332$,
_SYSSMU7_2329891355$,
_SYSSMU8_399776867$,
_SYSSMU9_1692468413$,
_SYSSMU10_930580995$)7. pfile로 데이터베이스 재시작 후 기존 undo tablespace 삭제
# pfile로 데이터베이스 시작
SYS@ora19c> startup pfile=$ORACLE_HOME/dbs/initora19c.ora
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.
Database opened.
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 NEEDS RECOVERY
2 _SYSSMU2_27624015$ PUBLIC UNDOTBS1 NEEDS RECOVERY
3 _SYSSMU3_2421748942$ PUBLIC UNDOTBS1 NEEDS RECOVERY
4 _SYSSMU4_625702278$ PUBLIC UNDOTBS1 NEEDS RECOVERY
5 _SYSSMU5_2101348960$ PUBLIC UNDOTBS1 NEEDS RECOVERY
6 _SYSSMU6_813816332$ PUBLIC UNDOTBS1 NEEDS RECOVERY
7 _SYSSMU7_2329891355$ PUBLIC UNDOTBS1 NEEDS RECOVERY
8 _SYSSMU8_399776867$ PUBLIC UNDOTBS1 NEEDS RECOVERY
9 _SYSSMU9_1692468413$ PUBLIC UNDOTBS1 NEEDS RECOVERY
10 _SYSSMU10_930580995$ PUBLIC UNDOTBS1 NEEDS RECOVERY
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------------------------------ ------------------------------ ----------------
11 _SYSSMU11_235323571$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 ONLINE
21 rows selected.
# 기존 undo tablespace 삭제
SYS@ora19c> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
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_235323571$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 ONLINE
11 rows selected.
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 2950021 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2950021 SYSTEM
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2950021 ONLINE
5 UNDO1 /u01/app/oracle/oradata/ORA19C/undo1.dbf 2950021 ONLINE원상복구
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
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_235323571$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_943486663$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_1401231401$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2287875962$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_1437082692$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_3405821153$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_536003123$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_3128172769$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_689157689$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_2296113507$ PUBLIC UNDO1 ONLINE
11 rows selected.
SYS@ora19c> alter system set undo_tablespace = undotbs1 scope=spfile;
System altered.
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c dbs]$ cd ~/backup/noarch
[oracle@ora19c noarch]$ cp -v * $ORACLE_BASE/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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
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 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/undo1.dbf
/u01/app/oracle/oradata/ORA19C/undo1.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/undo1.dbf
SYS@ora19c> create pfile from spfile;
File created.시나리오 11 : temp file 손상 복구
현재 상태 조회
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT# sort area 크기 조회
SYS@ora19c> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/temp01.dbf: No such file or directory# 문제 없음 -> sort 공간이 많이 필요하지 않아 temp file을 쓰지 않음
HR@ora19c> select * from hr.employees order by salary desc;
# 대용량 sort 발생 -> temp file이 없어 오류 발생
HR@ora19c> select s.*, b.*
from all_objects s, all_objects b
order by 1, 2, 3, 4; 2 3
from all_objects s, all_objects b
*
ERROR at line 2:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/ORA19C/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3temp file 추가
SYS@ora19c> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ORA19C/temp02.dbf' size 10m autoextend on;
Tablespace altered.
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/temp02.dbf손상된 temp file 삭제
# 손상된 temp file 삭제
SYS@ora19c> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORA19C/temp01.dbf';
Tablespace altered.
SYS@ora19c> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS SHARED
------------------------------ ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------------
INST_ID
----------
/u01/app/oracle/oradata/ORA19C 2 TEMP 10485760 1280 ONLINE 2 YES 3.4360E+10 4194302 1 9437184 1152 SHARED
/temp02.dbf
temp file 추가
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ORA19C/temp01.dbf' size 5m;
SYS@ora19c> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS SHARED
------------------------------ ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------------
INST_ID
----------
/u01/app/oracle/oradata/ORA19C 1 TEMP 5242880 640 ONLINE 1 NO 0 0 0 4194304 512 SHARED
/temp01.dbf
/u01/app/oracle/oradata/ORA19C 2 TEMP 10485760 1280 ONLINE 2 YES 3.4360E+10 4194302 1 9437184 1152 SHARED
/temp02.dbftemp file 크기 수정
alter database tempfile '/u01/app/oracle/oradata/ORA19C/temp01.dbf' resize 10m;
SYS@ora19c> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- -----------
SHARED INST_ID
------------- ----------
/u01/app/oracle/oradata/ORA19C/temp01.dbf 1 TEMP 10485760 1280 ONLINE 1 NO 0 0 0 9437184 1152
SHARED
/u01/app/oracle/oradata/ORA19C/temp02.dbf 2 TEMP 10485760 1280 ONLINE 2 YES 3.4360E+10 4194302 1 9437184 1152
SHAREDtemp file 자동 확장 기능 활성화
SYS@ora19c> alter database tempfile '/u01/app/oracle/oradata/ORA19C/temp01.dbf' autoextend on;
Database altered.
SYS@ora19c> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- -----------
SHARED INST_ID
------------- ----------
/u01/app/oracle/oradata/ORA19C/temp01.dbf 1 TEMP 10485760 1280 ONLINE 1 YES 3.4360E+10 4194302 1 9437184 1152
SHARED
/u01/app/oracle/oradata/ORA19C/temp02.dbf 2 TEMP 10485760 1280 ONLINE 2 YES 3.4360E+10 4194302 1 9437184 1152
SHAREDtemp file 삭제
SYS@ora19c> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORA19C/temp02.dbf';
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORA19C/temp02.dbf'
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORA19C/temp02.dbf';
Tablespace altered.시나리오 12 : temp file 손상 복구
현재 상태 조회
# tempfile 조회
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
# 테이블스페이스 종류 조회
SYS@ora19c> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT# sort area 크기 조회
SYS@ora19c> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536# 기본 temp 테이블스페이스 조회
SYS@ora19c> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP
SYS@ora19c> select username, temporary_tablespace from dba_users where username = 'HR';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
HR TEMP장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/temp01.dbf: No such file or directory새로운 temp tablespace 생성한 후 default temporary tablespace로 지정
SYS@ora19c> create temporary tablespace temp_new tempfile '/u01/app/oracle/oradata/ORA19C/temp_new01.dbf' size 10m autoextend on;
Tablespace created.
SYS@ora19c> alter database default temporary tablespace temp_new;
Database altered.
SYS@ora19c> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP_NEW
SYS@ora19c> select username, temporary_tablespace from dba_users where username = 'HR';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
HR TEMP_NEW기존 temp tablespace 삭제
SYS@ora19c> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SYS@ora19c> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
USERS PERMANENT
TEMP_NEW TEMPORARY원래 temp로 변경
SYS@ora19c> create temporary tablespace temp tempfile '/u01/app/oracle/oradata/ORA19C/temp01.dbf' size 10m autoextend on;
Tablespace created.
SYS@ora19c> alter database default temporary tablespace temp;
Database altered.
SYS@ora19c> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP
SYS@ora19c> select username, temporary_tablespace from dba_users where username = 'HR';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
HR TEMP
SYS@ora19c> drop tablespace temp_new including contents and datafiles;
Tablespace dropped.
SYS@ora19c> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT시나리오 13 : temp file 손상 복구
현재 상태 조회
# tempfile 조회
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
# 테이블스페이스 종류 조회
SYS@ora19c> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT# sort area 크기 조회
SYS@ora19c> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536# 기본 temp 테이블스페이스 조회
SYS@ora19c> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------
TEMP
SYS@ora19c> select username, temporary_tablespace from dba_users where username = 'HR';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
HR TEMP장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/temp01.dbf: No such file or directory데이터베이스 시작 시 temp file이 손상된 경우 자동으로 생성
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 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/temp01.dbf