dml문으로 수정을 하면 redo가 먼저 생기고 결과가 수정이 됨.
이전값이 어떤 언두 블록에 있는지 찾아감. 그 언두 정보는 트랜잭션 레이어의 트랜잭션 슬랏에 있음
_________________________________________________________________________________________
■ RAC redo log file 관리
SYS@racdb1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------
1 1 15 209715200 512 2 NO INACTIVE 3426885 24-JUL-26 3587392 24-JUL-26 0
2 1 16 209715200 512 2 NO CURRENT 3587392 24-JUL-26 1.8447E+19 0
3 2 3 209715200 512 2 NO INACTIVE 2858292 22-JUL-26 3700906 27-JUL-26 0
4 2 4 209715200 512 2 NO CURRENT 3700906 27-JUL-26 1.8447E+19 0
rac에서 언두 테이블스페이스와 리두 로그 그룹은 분리되어있다.
rac 노드가 2개밖에 없으면 첫 번째 노드로 오는 사이클이 너무 빠를 수 있다. 그럼 문제가 생김.
alert log 메시지도 보이고 대기 이벤트가 발생할 수도 있다.
만약 리두로그파일 크기가 너무 큰데 트랜잭션 양이 너무 적으면 스토리지 공간 낭비.
해결방법은 새로운 그룹을 추가할 때 적절한 사이즈로 추가
SYS@racdb1> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ---------- ------- --------------------------------------------- --- ------
2 ONLINE +DATA/RACDB/ONLINELOG/group_2.265.1239202961 NO 0
2 ONLINE +FRA/RACDB/ONLINELOG/group_2.258.1239202963 YES 0
1 ONLINE +DATA/RACDB/ONLINELOG/group_1.264.1239202961 NO 0
1 ONLINE +FRA/RACDB/ONLINELOG/group_1.257.1239202963 YES 0
3 ONLINE +DATA/RACDB/ONLINELOG/group_3.268.1239203385 NO 0
3 ONLINE +FRA/RACDB/ONLINELOG/group_3.259.1239203387 YES 0
4 ONLINE +DATA/RACDB/ONLINELOG/group_4.269.1239203387 NO 0
4 ONLINE +FRA/RACDB/ONLINELOG/group_4.260.1239203389 YES 0
# log buffer space
- 트랜잭션에 의해 생성되는 리두의 양에 비해 리두 로그 버퍼의 크기가 작은 경우 발생하는 대기 이벤트
# log file switch completion
- lgwr이 리두 로그 버퍼에 리두 엔트리를 리두 로그 파일에 write하는 시점에 리두 로그 파일이 꽉 차서 더 이상 쓰기를 할 수 없을 경우 다음 그룹으로 log switch가 발생할 때 대기하는 이벤트
# log file switch(checkpoint incomplete)
- 새로 사용할 리두 로그 파일에 대한 체크포인트 작업이 아직 끝나지 않았다면 기다려야 할 때 발생하는 대기 이벤트
- checkpoint not complete
# log file switch(archiving needed)
- 새로 사용할 리두 로그 파일에 대해 archive 작업이 아직 끝나지 않았다면 기다려야 할 때 발생하는 대기 이벤트
2~4번째 대기이벤트의 공통점은 잦은 로그 스위치, 이거 때문에 첫 사이클로 돌아오는 게 너무 빠르다는 점.
리두 로그 그룹을 적절한 사이즈로 추가하기.
SYS@racdb1> select b.name,a.group_number, a.disk_number, a.name, a.mount_status, a.path, a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
NAME GROUP_NUMBER DISK_NUMBER NAME MOUNT_S PATH TOTAL_MB FREE_MB
---------- ------------ ----------- ---------- ------- -------------------------------------------------- ---------- ----------
ASM_DG 3 0 ASMDISK05 CACHED /dev/oracleasm/disks/ASMDISK05 5119 5049
FRA 2 1 FRA_0001 CACHED /dev/oracleasm/disks/ASMDISK04 5116 4604
DATA 1 0 DATA_0000 CACHED /dev/oracleasm/disks/ASMDISK01 5116 3440
DATA 1 1 DATA_0001 CACHED /dev/oracleasm/disks/ASMDISK02 5116 3480
FRA 2 0 FRA_0000 CACHED /dev/oracleasm/disks/ASMDISK03 5116 4592
SYS@racdb1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------
1 1 15 209715200 512 2 NO INACTIVE 3426885 24-JUL-26 3587392 24-JUL-26 0
2 1 16 209715200 512 2 NO CURRENT 3587392 24-JUL-26 1.8447E+19 0
3 2 3 209715200 512 2 NO INACTIVE 2858292 22-JUL-26 3700906 27-JUL-26 0
4 2 4 209715200 512 2 NO CURRENT 3700906 27-JUL-26 1.8447E+19 0
# 1번 인스턴스에 redo log group 5번 추가
SYS@racdb1> alter database add logfile thread 1 group 5 '+DATA' size 50m;
Database altered.
# alter database add logfile thread 1 group 5 '+DATA' size 50m; ('+DATA','+FRA') size 50m;
# 2번 인스턴스에 redo log group 6번 추가
SYS@racdb2> alter database add logfile thread 2 group 6 '+DATA' size 50m;
Database altered.
SYS@racdb1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------
1 1 15 209715200 512 2 NO INACTIVE 3426885 24-JUL-26 3587392 24-JUL-26 0
2 1 16 209715200 512 2 NO CURRENT 3587392 24-JUL-26 1.8447E+19 0
3 2 3 209715200 512 2 NO INACTIVE 2858292 22-JUL-26 3700906 27-JUL-26 0
4 2 4 209715200 512 2 NO CURRENT 3700906 27-JUL-26 1.8447E+19 0
5 1 0 52428800 512 1 YES UNUSED 0 0 0
6 2 0 52428800 512 1 YES UNUSED 0 0 0
SYS@racdb1> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ---------- ------- --------------------------------------------- --- ------
2 ONLINE +DATA/RACDB/ONLINELOG/group_2.265.1239202961 NO 0
2 ONLINE +FRA/RACDB/ONLINELOG/group_2.258.1239202963 YES 0
1 ONLINE +DATA/RACDB/ONLINELOG/group_1.264.1239202961 NO 0
1 ONLINE +FRA/RACDB/ONLINELOG/group_1.257.1239202963 YES 0
3 ONLINE +DATA/RACDB/ONLINELOG/group_3.268.1239203385 NO 0
3 ONLINE +FRA/RACDB/ONLINELOG/group_3.259.1239203387 YES 0
4 ONLINE +DATA/RACDB/ONLINELOG/group_4.269.1239203387 NO 0
4 ONLINE +FRA/RACDB/ONLINELOG/group_4.260.1239203389 YES 0
5 ONLINE +DATA/RACDB/ONLINELOG/group_5.275.1239737917 NO 0
6 ONLINE +DATA/RACDB/ONLINELOG/group_6.278.1239455001 NO 0
# 그룹의 멤버 추가
SYS@racdb1> alter database add logfile member '+FRA' to group 5, '+FRA' to group 6;
Database altered.
SYS@racdb1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------
1 1 15 209715200 512 2 NO INACTIVE 3426885 24-JUL-26 3587392 24-JUL-26 0
2 1 16 209715200 512 2 NO CURRENT 3587392 24-JUL-26 1.8447E+19 0
3 2 3 209715200 512 2 NO INACTIVE 2858292 22-JUL-26 3700906 27-JUL-26 0
4 2 4 209715200 512 2 NO CURRENT 3700906 27-JUL-26 1.8447E+19 0
5 1 0 52428800 512 2 YES UNUSED 0 0 0
6 2 0 52428800 512 2 YES UNUSED 0 0 0
SYS@racdb1> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ---------- ------- --------------------------------------------- --- ------
2 ONLINE +DATA/RACDB/ONLINELOG/group_2.265.1239202961 NO 0
2 ONLINE +FRA/RACDB/ONLINELOG/group_2.258.1239202963 YES 0
1 ONLINE +DATA/RACDB/ONLINELOG/group_1.264.1239202961 NO 0
1 ONLINE +FRA/RACDB/ONLINELOG/group_1.257.1239202963 YES 0
3 ONLINE +DATA/RACDB/ONLINELOG/group_3.268.1239203385 NO 0
3 ONLINE +FRA/RACDB/ONLINELOG/group_3.259.1239203387 YES 0
4 ONLINE +DATA/RACDB/ONLINELOG/group_4.269.1239203387 NO 0
4 ONLINE +FRA/RACDB/ONLINELOG/group_4.260.1239203389 YES 0
5 ONLINE +DATA/RACDB/ONLINELOG/group_5.275.1239737917 NO 0
6 ONLINE +DATA/RACDB/ONLINELOG/group_6.278.1239455001 NO 0
5 INVALID ONLINE +FRA/RACDB/ONLINELOG/group_5.263.1239738205 NO 0
6 INVALID ONLINE +FRA/RACDB/ONLINELOG/group_6.264.1239738205 NO 0
# 멤버 삭제
inactive, unused 상태의 멤버 삭제 가능.
SYS@racdb1> alter database drop logfile member '+FRA/RACDB/ONLINELOG/group_5.263.1239738205';
Database altered.
SYS@racdb1> alter database drop logfile member '+FRA/RACDB/ONLINELOG/group_6.264.1239738205';
Database altered.
# 그룹 삭제
inactive, unused 상태의 그룹 삭제 가능.
(지금 실습에서는 삭제하지 않기.)
alter database drop logfile group 5;
alter database drop logfile group 6;
# 수동으로 로그 스위치
- 한 쪽 인스턴스(한 쪽 노드)만 영향
SYS@racdb1> alter system switch logfile;
SYS@racdb2> alter system switch logfile;
- 모든 인스턴스에 영향(archivelog mode)
SYS@racdb1> alter system archive log current;
# 모든 인스턴스에 대해 체크포인트를 수행(기본값)
SYS@racdb1> alter system checkpoint;
또는
SYS@racdb1> alter system checkpoint global;
# 한 쪽 인스턴스에서만 checkpoint
SYS@racdb1> alter system checkpoint local;
■ RAC Archivelog mode 설정
SYS@racdb1> select name, log_mode from v$database;
NAME LOG_MODE
---------- ------------
RACDB NOARCHIVELOG
SYS@racdb1> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19
SYS@racdb1> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 8256M
SYS@racdb1> select destination, binding, status from v$archive_dest where destination is not null;
DESTINATION BINDING STATUS
------------------------------ --------- ----------
USE_DB_RECOVERY_FILE_DEST MANDATORY(의무적인) VALID(유효한)
# noarchivelog mode -> archivelog mode 변경(싱글 인스턴스)
1) shutdown immediate
2) startup mount
3) alter database archivelog;
4) alter database open;
# RAC noarchivelog mode -> archivelog mode 변경
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac2
rac에서는 모든 노드를 shutdown immediate 해야 함.
1) 모든 인스턴스 정상적인 종료
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
2) 한 쪽 노드만 mount로 시작
SYS@racdb1> startup mount
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 486539264 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
Database mounted.
3) archivelog mode 변경
SYS@racdb1> alter database archivelog;
Database altered.
4) 데이터베이스 open
SYS@racdb1> alter database open;
Database altered.
SYS@racdb1> select name, log_mode from v$database;
NAME LOG_MODE
--------- ------------
RACDB ARCHIVELOG
5) 다른 인스턴스 시작
SYS@racdb2> select name, log_mode from v$database;
NAME LOG_MODE
--------- ------------
RACDB ARCHIVELOGSYS@racdb1> alter system archive log current;
System altered.
SYS@racdb1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 24 209715200 512 2 NO CURRENT 3726727 27-JUL-26 1.8447E+19 0
2 1 22 209715200 512 2 YES INACTIVE 3721378 24-JUL-26 3721384 24-JUL-26 0
3 2 12 209715200 512 2 NO CURRENT 3726731 24-JUL-26 1.8447E+19 0
4 2 10 209715200 512 2 YES INACTIVE 3721374 24-JUL-26 3721388 24-JUL-26 0
5 1 23 52428800 512 1 YES ACTIVE 3722334 27-JUL-26 3726727 27-JUL-26 0
6 2 11 52428800 512 1 YES ACTIVE 3721388 24-JUL-26 3726731 24-JUL-26 0
SYS@racdb1> select sequence#, name, first_change#, next_change# from v$archived_log; -- 시간 rdate 그거 안 해서 27일로 되어있다.
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- --------------------------------------------- ------------- ------------
19 +FRA/RACDB/ARCHIVELOG/2026_07_27/thread_1_seq 3719176 3720126
_19.264.1239742011
9 +FRA/RACDB/ARCHIVELOG/2026_07_27/thread_2_seq 3719263 3721064
_9.263.1239742043
20 +FRA/RACDB/ARCHIVELOG/2026_07_24/thread_1_seq 3720126 3721376
_20.265.1239459217
10 +FRA/RACDB/ARCHIVELOG/2026_07_24/thread_2_seq 3721374 3721388
_10.266.1239459217
21 +FRA/RACDB/ARCHIVELOG/2026_07_24/thread_1_seq 3721376 3721378
_21.267.1239459219
22 +FRA/RACDB/ARCHIVELOG/2026_07_24/thread_1_seq 3721378 3721384
_22.268.1239459219
23 +FRA/RACDB/ARCHIVELOG/2026_07_27/thread_1_seq 3722334 3726727
_23.269.1239742319
11 +FRA/RACDB/ARCHIVELOG/2026_07_24/thread_2_seq 3721388 3726731
_11.270.1239459321
SYS@racdb1> select b.name,a.group_number, a.disk_number, a.name, a.mount_status, a.path, a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
NAME GROUP_NUMBER DISK_NUMBER NAME MOUNT_S PATH TOTAL_MB FREE_MB
-------------------- ------------ ----------- -------------------- ------- -------------------------------------------------- ---------- ----------
ASM_DG 3 0 ASMDISK05 CACHED /dev/oracleasm/disks/ASMDISK05 5119 5006
FRA 2 1 FRA_0001 CACHED /dev/oracleasm/disks/ASMDISK04 5116 4584
DATA 1 0 DATA_0000 CACHED /dev/oracleasm/disks/ASMDISK01 5116 3380
DATA 1 1 DATA_0001 CACHED /dev/oracleasm/disks/ASMDISK02 5116 3416
FRA 2 0 FRA_0000 CACHED /dev/oracleasm/disks/ASMDISK03 5116 4580
■ RAC controlfile 관리
SYS@racdb1> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/RACDB/CONTROLFILE/curren
t.263.1239202955, +FRA/RACDB/C
ONTROLFILE/current.256.1239202
957
SYS@racdb1> select name from v$controlfile;
NAME
--------------------------------------------------
+DATA/RACDB/CONTROLFILE/current.263.1239202955
+FRA/RACDB/CONTROLFILE/current.256.1239202957
# RAC 환경에서 SPFILE의 control_files 파라미터를 변경하고 RMAN을 이용해 ASM 디스크 그룹에 컨트롤 파일을 추가하여 3중화하는 과정
1) 초기 파라미터 파일 뭐로 되어있는지 확인
SYS@racdb1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/RACDB/PARAMETERFILE/spfi
le.270.1239203391
2) SPFILE로부터 PFILE 생성
SYS@racdb1> create pfile='/home/oracle/init.ora' from spfile;
File created.
SYS@racdb1> ! vi init.ora
...
racdb1.__streams_pool_size=0
racdb2.__unified_pga_pool_size=0
racdb1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
...
sid 정보가 앞에 접두어로 붙는다.
공통적으로 사용하는 거는 앞에 *
특정한 거는 racdb1, racdb2 이렇게 이름이 붙음.
3) control_files 파라미터에 세 번째 ASM 위치 추가
SYS@racdb1> alter system set control_files='+DATA/RACDB/CONTROLFILE/current.263.1239202955','+FRA/RACDB/CONTROLFILE/current.256.1239202957','+ASM_DG' scope=spfile sid='*';
System altered.
-- scope 무조건 spfile로 하기.
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac2
4) RAC DB 정상 종료
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
5) rman에서 nomount까지 올리기
[oracle@rac1 ~]$ rman target/
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 28 14:10:49 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 486539264 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
6) 컨트롤파일 restore
RMAN> restore controlfile from '+DATA/RACDB/CONTROLFILE/current.263.1239202955';
Starting restore at 28-JUL-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=racdb1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/RACDB/CONTROLFILE/current.263.1239202955
output file name=+FRA/RACDB/CONTROLFILE/current.256.1239202957
output file name=+ASM_DG/RACDB/CONTROLFILE/current.258.1239804729
Finished restore at 28-JUL-26
RMAN> exit
Recovery Manager complete.
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 28 14:13:40 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@racdb1> select instance_name, status, host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ -------------------------
racdb1 STARTED rac1
7) mount, open 순으로 차근차근 올리기
SYS@racdb1> alter database mount;
Database altered.
SYS@racdb1> alter database open;
Database altered.
8) 다른 노드는 startup하면 됨.
SYS@racdb2> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 469762048 bytes
Database Buffers 754974720 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SYS@racdb1> select instance_name, status, host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
racdb1 OPEN rac1
racdb2 OPEN rac2
3중화 완료
SYS@racdb1> select name from v$controlfile; -- 삼중화 완료
NAME
--------------------------------------------------
+DATA/RACDB/CONTROLFILE/current.263.1239202955
+FRA/RACDB/CONTROLFILE/current.256.1239202957
+ASM_DG/RACDB/CONTROLFILE/current.258.1239804729
# control file parallel write 대기 이벤트 발생할 수 있다.
- 로그 스위치가 자주 발생하는 경우
- 체크포인트가 자주 발생하는 경우
- I/O 시스템의 성능이 느린 경우
컨트롤파일을 독립적인 디스크 공간에 위치시키고 raw device, asm 사용하는 것이 좋다.
컨트롤파일의 개수가 지나치게 많은 경우에는 복구에 필요한 최소한의 컨트롤 파일만 사용하는 것이 좋다.
# enq : CF - contention: 여러 세션이 동시에 컨트롤 파일을 갱신하기 위해 CF락을 획득하는 과정에서 경합이 발생할 수 있다.
# 3중화 -> 2중화 변경
1) 초기 파라미터 파일 수정
SYS@racdb1> alter system set control_files='+DATA/RACDB/CONTROLFILE/current.263.1239202955','+FRA/RACDB/CONTROLFILE/current.256.1239202957' scope=spfile sid='*';
System altered.
2) DB 정상 종료
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
3) DB 시작
[oracle@rac1 ~]$ srvctl start database -d racdb
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac22중화 완료
[oracle@rac1 ~]$ exit
exit
SYS@racdb1> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@rac1 ~]$ sqlplus / as sysdba -- 세션 끊어졌으니 나갔다가 재접속
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 28 14:33:31 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@racdb1> select instance_name, status, host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
racdb1 OPEN rac1
racdb2 OPEN rac2
SYS@racdb1> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/RACDB/CONTROLFILE/curren
t.263.1239202955, +FRA/RACDB/C
ONTROLFILE/current.256.1239202
957
SYS@racdb1> select name from v$controlfile;
NAME
--------------------------------------------------
+DATA/RACDB/CONTROLFILE/current.263.1239202955
+FRA/RACDB/CONTROLFILE/current.256.1239202957
[oracle@rac1 ~]$ ls +ASM_DG/RACDB/CONTROLFILE/current.258.1239804729
ls: cannot access +ASM_DG/RACDB/CONTROLFILE/current.258.1239804729: No such file or directory
-- +ASM파일은 이렇게 삭제하면 안 된다.
# asmcmd 유틸리티: asm 디스크 그룹을 관리하는 유틸리티, 리눅스 파일 시스템 명령어를 사용하여 asm 관리를 한다.
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [racdb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ asmcmd
ASMCMD> ls
ASM_DG/
DATA/
FRA/
ASMCMD> ls +ASM_DG/RACDB/CONTROLFILE/current.258.1239804729
current.258.1239804729
ASMCMD> ls -l +ASM_DG/RACDB/CONTROLFILE/current.258.1239804729
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JUL 28 14:00:00 Y current.258.1239804729
-- ASM 구조 탐색 및 워밍업(asmcmd 익히기)
ASMCMD> cd asm_dg
ASMCMD> ls
RACDB/
ASMCMD> cd racdb
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.258.1239804729
ASMCMD> pwd
+asm_dg/racdb/controlfile
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd datafile
ASMCMD> ls
ASM_TBS.256.1239728975
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE JUL 24 16:00:00 Y ASM_TBS.256.1239728975
ASMCMD> rm current.258.1239804729 -- 의미 없는 파일 삭제
ASMCMD> ls
ASMCMD-8002: entry 'CONTROLFILE' does not exist in directory '+ASM_DG/RACDB/'
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? racdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ rman target/ -- ASM에서는 rman 접속 안 됨.
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 28 15:04:24 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1236819723)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES +DATA/RACDB/DATAFILE/system.259.1239202827
2 10 ASM_TBS NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
3 740 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 +DATA/RACDB/DATAFILE/system.259.1239202827
3 0 +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 0 +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 0 +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 0 +DATA/RACDB/DATAFILE/users.262.1239202887
-- 백업 생성
RMAN> backup as compressed backupset database;
Starting backup at 28-JUL-26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/RACDB/DATAFILE/system.259.1239202827
input datafile file number=00003 name=+DATA/RACDB/DATAFILE/sysaux.260.1239202861
input datafile file number=00004 name=+DATA/RACDB/DATAFILE/undotbs1.261.1239202887
input datafile file number=00005 name=+DATA/RACDB/DATAFILE/undotbs2.267.1239203295
input datafile file number=00002 name=+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
input datafile file number=00007 name=+DATA/RACDB/DATAFILE/users.262.1239202887
channel ORA_DISK_1: starting piece 1 at 28-JUL-26
channel ORA_DISK_1: finished piece 1 at 28-JUL-26
piece handle=+FRA/RACDB/BACKUPSET/2026_07_28/nnndf0_tag20260728t150640_0.277.1239808001 tag=TAG20260728T150640 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 28-JUL-26
Starting Control File and SPFILE Autobackup at 28-JUL-26
piece handle=+FRA/RACDB/AUTOBACKUP/2026_07_28/s_1239808036.278.1239808037 comment=NONE
Finished Control File and SPFILE Autobackup at 28-JUL-26
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 18.89M DISK 00:00:00 24-JUL-26
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20260724T102456
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_24/s_1239445496.261.1239445497
SPFILE Included: Modification time: 24-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3487304 Ckp time: 24-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 18.89M DISK 00:00:00 27-JUL-26
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20260727T170936
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_27/s_1239728976.262.1239728977
SPFILE Included: Modification time: 27-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3591269 Ckp time: 27-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 319.27M DISK 00:00:31 28-JUL-26
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20260728T150640
Piece Name: +FRA/RACDB/BACKUPSET/2026_07_28/nnndf0_tag20260728t150640_0.277.1239808001
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/system.259.1239202827
2 Full 3759213 28-JUL-26 NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975 -- 장애 유발(삭제)
3 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/users.262.1239202887
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 18.89M DISK 00:00:00 28-JUL-26
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20260728T150716
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_28/s_1239808036.278.1239808037
SPFILE Included: Modification time: 24-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3759235 Ckp time: 28-JUL-26
Compressed: YES -- 압축된 것
SYS@racdb1> select tablespace_name, file_name, bytes/1024/1024 mb, autoextensible from dba_data_files;
TABLESPACE_NAME FILE_NAME MB AUT
------------------------------ ------------------------------------------------------- ---------- ---
SYSTEM +DATA/RACDB/DATAFILE/system.259.1239202827 910 YES
SYSAUX +DATA/RACDB/DATAFILE/sysaux.260.1239202861 740 YES
UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.267.1239203295 25 YES
USERS +DATA/RACDB/DATAFILE/users.262.1239202887 5 YES
ASM_TBS +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975 10 YES
UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.261.1239202887 355 YES
SYS@racdb1> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'EMP_ASM';
FILE_NAME
-------------------------------------------------------
+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
-- 데이터 확인(복구 검증용 기준점)
SYS@racdb1> select count(*) from hr.emp_asm;
COUNT(*)
----------
107
장애 상황1: NON-SYSTEM 데이터파일(ASM_TBS) 손상
1. 삭제 시도 -> 실패(보호 확인)
[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [ASM2] ? +ASM2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ asmcmd
ASMCMD> ls +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
asm_tbs.256.1239728975
ASMCMD> rm +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
ORA-15032: not all alterations performed
ORA-15028: ASM file '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
-- 운영 중에 삭제 안 되게 해놓음.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ASM_TBS ONLINE
2. 일부러 안전장치 해제(테이블스페이스 오프라인)
SYS@racdb1> alter tablespace asm_tbs offline;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
ASM_TBS OFFLINE
SYS@racdb1> select tablespace_name, file_name, bytes/1024/1024 mb, autoextensible, status from dba_data_files;
TABLESPACE_NAME FILE_NAME MB AUT STATUS
------------------------------ ------------------------------------------------------- ---------- --- ---------
SYSTEM +DATA/RACDB/DATAFILE/system.259.1239202827 910 YES AVAILABLE
SYSAUX +DATA/RACDB/DATAFILE/sysaux.260.1239202861 740 YES AVAILABLE
UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.267.1239203295 25 YES AVAILABLE
USERS +DATA/RACDB/DATAFILE/users.262.1239202887 5 YES AVAILABLE
ASM_TBS +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975 AVAILABLE
UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.261.1239202887 355 YES AVAILABLE
3. 장애 유발
ASMCMD> rm +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
ASMCMD> ls +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975
ASMCMD-8002: entry 'RACDB' does not exist in directory '+ASM_DG/'
SYS@racdb1> alter tablespace asm_tbs online; -- 장애 나서 온라인으로 못 바꾼다.
alter tablespace asm_tbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975'
4. RMAN 복구
SYS@racdb1> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@rac1 ~]$ rman target/
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 28 15:20:37 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1236819723)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES +DATA/RACDB/DATAFILE/system.259.1239202827
2 0 ASM_TBS NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239728975 -- 장애 난 파일
3 740 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
* 복구과정
RMAN> restore datafile 2;
RMAN> recover datafile 2;
RMAN> alter tablespace asm_tbs online;
RAC에서는 list failure도 안 되고 advice도 안 됨.
장애 상황 2: SYSTEM 데이터파일 손상
1. DB 정상 종료
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac2
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
2. SYSTEM 데이터파일 삭제 (장애 유발)
ASMCMD> find +data system*
+data/RACDB/DATAFILE/SYSTEM.259.1239202827
ASMCMD> rm +data/RACDB/DATAFILE/SYSTEM.259.1239202827
ASMCMD> find +data system*
ASMCMD> <- 아무 것도 안 나옴.(삭제 확인)
3. DB startup 시도 → SYSTEM 파일 없음 확인
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 28 15:48:38 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SYS@racdb1> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 486539264 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/RACDB/DATAFILE/system.259.1239202827'
4. 손상 파일 확인
SYS@racdb1> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- -------------------------------- ---------- --------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0 0
5. RMAN 복구
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;
6. 복구 결과 확인 -> 위치가 바뀐 것을 발견
RMAN> report schema;
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES +ASM_DG/RACDB/DATAFILE/system.258.1239810859 -- asm_dg에 복구를 했다 거참. 원래는 +DATA/RACDB/DATAFILE/system.259.1239202827 이 위치여야 하는데 말이다.
2 10 ASM_TBS NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919
3 740 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
ASMCMD> find +asm_dg system*
+asm_dg/RACDB/DATAFILE/SYSTEM.258.1239810859
# system datafile 이관
+asm_dg/RACDB/DATAFILE/SYSTEM.258.1239810859 -> +DATA 그룹
DB 정상 종료 -> RMAN 접속 -> startup mount -> copy -> rename -> alter database open;
1. DB 정상 종료
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is not running on node rac2
[oracle@rac1 ~]$ srvctl stop instance -d racdb -i racdb1 -- rac1만 내리기
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
2. RMAN 접속하여 mount까지 올리기
[oracle@rac1 ~]$ rman target/
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 28 16:05:20 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 486539264 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
RMAN> select instance_name, status from v$instance;
using target database control file instead of recovery catalog
INSTANCE_NAME STATUS
---------------- ------------
racdb1 MOUNTED
RMAN> select instance_name, status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 MOUNTED
3. 파일 복사
RMAN> report schema;
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM *** +ASM_DG/RACDB/DATAFILE/system.258.1239810859 -- 이 위치를 +DATA로 copy해야 함.(image copy처럼)
2 10 ASM_TBS *** +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919
3 740 SYSAUX *** +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 *** +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 *** +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS *** +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
RMAN> copy datafile '+ASM_DG/RACDB/DATAFILE/system.258.1239810859' to '+DATA';
Starting backup at 28-JUL-26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+ASM_DG/RACDB/DATAFILE/system.258.1239810859
output file name=+DATA/RACDB/DATAFILE/system.259.1239811659 tag=TAG20260728T160738 RECID=3 STAMP=1239811661
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-JUL-26
Starting Control File and SPFILE Autobackup at 28-JUL-26
piece handle=+FRA/RACDB/AUTOBACKUP/2026_07_28/s_1239811458.281.1239811665 comment=NONE
Finished Control File and SPFILE Autobackup at 28-JUL-26
RMAN> list copy;
specification does not match any control file copy in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
3 1 A 28-JUL-26 3763461 28-JUL-26 NO
Name: +DATA/RACDB/DATAFILE/system.259.1239811659 -- 가장 최근 게 제일 위에 있음.
Tag: TAG20260728T160738
...
4. 컨트롤파일의 위치 정보 갱신(rename)
RMAN> alter database rename file '+ASM_DG/RACDB/DATAFILE/system.258.1239810859' to '+DATA/RACDB/DATAFILE/system.259.1239811659';
Statement processed
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM *** +DATA/RACDB/DATAFILE/system.259.1239811659
2 10 ASM_TBS *** +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919
3 740 SYSAUX *** +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 *** +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 *** +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS *** +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
5. DB 열기
RMAN> alter database open;
Statement processed
나머지 인스턴스 재기동 및 최종 확인
RMAN> exit
Recovery Manager complete.
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is not running on node rac2
[oracle@rac1 ~]$ srvctl start instance -d racdb -i racdb2
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac2
ASMCMD> find +asm_dg system* <- 결과 없음
ASMCMD> find +data system*
+data/RACDB/DATAFILE/SYSTEM.259.1239811659+ASM_DG에는 더 이상 SYSTEM 파일이 없고, +DATA에 새 이름(SYSTEM.259.1239811659)으로 정상적으로 자리 잡은 것을 최종 확인
ASM_TBS 파일 재손상 → "복구 + 이관"을 한 번에 처리
1. DB 전체 종료 후 파일 삭제(장애 유발)
[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is not running on node rac1
Instance racdb2 is not running on node rac2
[oracle@rac1 ~]$
ASMCMD> find +asm_dg asm*
+asm_dg/RACDB/DATAFILE/ASM_TBS.256.1239808919
ASMCMD> rm +asm_dg/RACDB/DATAFILE/ASM_TBS.256.1239808919 -- 장애 유발
ASMCMD> find +asm_dg asm*
2. startup → OPEN 실패 → 부분적 OPEN으로 우회(손상된 파일 OFFLINE 후 DB OPEN)
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 28 16:21:28 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SYS@racdb1> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 486539264 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919'
SYS@racdb1> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
2 ONLINE ONLINE FILE NOT FOUND 0 0
SYS@racdb1> alter database datafile 2 offline;
Database altered.
SYS@racdb1> alter database open;
Database altered.
SYS@racdb1> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@rac1 ~]$ rman target/ -- rman에서 복구 수행
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 28 16:23:06 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1236819723)
3. RMAN에서 "복구 + 이관"을 한 번에 (run 블록)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES +DATA/RACDB/DATAFILE/system.259.1239811659
2 0 ASM_TBS NO +ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919 -- offline모드로 해놔서 사이즈가 0으로 보임.
3 740 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
작업형 복구 모드
run {
set newname for datafile '+ASM_DG/RACDB/DATAFILE/asm_tbs.256.1239808919' to '+DATA';
restore datafile 2;
switch datafile 2;
recover datafile 2;
alter database datafile 2 online;
}4. 결과 확인
RMAN> report schema;
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES +DATA/RACDB/DATAFILE/system.259.1239811659
2 10 ASM_TBS NO +DATA/RACDB/DATAFILE/asm_tbs.276.1239812769 -- +DATA 그룹에 복구 완료.
3 740 SYSAUX NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 355 UNDOTBS1 YES +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 25 UNDOTBS2 YES +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 5 USERS NO +DATA/RACDB/DATAFILE/users.262.1239202887
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 39 TEMP 32767 +DATA/RACDB/TEMPFILE/temp.266.1239202971
RMAN> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'EMP_ASM';
FILE_NAME
--------------------------------------------------------------------------------
+DATA/RACDB/DATAFILE/asm_tbs.276.1239812769
5. 백업 상태 재확인 및 백업 정리
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 18.89M DISK 00:00:00 24-JUL-26
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20260724T102456
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_24/s_1239445496.261.1239445497
SPFILE Included: Modification time: 24-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3487304 Ckp time: 24-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 18.89M DISK 00:00:00 27-JUL-26
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20260727T170936
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_27/s_1239728976.262.1239728977
SPFILE Included: Modification time: 27-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3591269 Ckp time: 27-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 319.27M DISK 00:00:31 28-JUL-26
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20260728T150640
Piece Name: +FRA/RACDB/BACKUPSET/2026_07_28/nnndf0_tag20260728t150640_0.277.1239808001
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/system.259.1239811659
2 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/asm_tbs.276.1239812769 -- 백업 또 안 해도 되게 이렇게 자동으로 바꿔줌. 원래 ASM_DG에 있었는데 말이야. 참 친절하다.
3 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/sysaux.260.1239202861
4 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/undotbs1.261.1239202887
5 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/undotbs2.267.1239203295
7 Full 3759213 28-JUL-26 NO +DATA/RACDB/DATAFILE/users.262.1239202887
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 18.89M DISK 00:00:00 28-JUL-26
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20260728T150716
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_28/s_1239808036.278.1239808037
SPFILE Included: Modification time: 24-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3759235 Ckp time: 28-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 18.89M DISK 00:00:00 24-JUL-26
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20260724T173904
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_24/s_1239471544.279.1239471545
SPFILE Included: Modification time: 24-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3761111 Ckp time: 24-JUL-26
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 18.89M DISK 00:00:00 28-JUL-26
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20260728T160745
Piece Name: +FRA/RACDB/AUTOBACKUP/2026_07_28/s_1239811458.281.1239811665
SPFILE Included: Modification time: 28-JUL-26
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 3763461 Ckp time: 28-JUL-26
RMAN> report obsolete; -- 정책상 더 이상 필요 없는 백업 조회
RMAN> delete obsolete; -- 정책상 더 이상 필요 없는 백업파일 삭제
RMAN> crosscheck copy; -- 카탈로그(컨트롤파일)와 실제 디스크의 파일 존재 여부를 대조
RMAN> list expired copy; -- 대조 결과 "카탈로그엔 있는데 실제로 없는" 파일 목록 확인
RMAN> delete expired copy; -- 그 유령 항목들을 카탈로그에서 정리
RMAN> exit
Recovery Manager complete.
6. 나머지 인스턴스 기동
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is not running on node rac2
[oracle@rac1 ~]$ srvctl start instance -d racdb -i racdb2
[oracle@rac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node rac1
Instance racdb2 is running on node rac2'강남 아이티윌 오라클DBA과정 94기 > RAC' 카테고리의 다른 글
| [오라클 DBA 과정] 92일차 RAC(9) (2026-07-30) (0) | 2026.07.31 |
|---|---|
| [오라클 DBA 과정] 91일차 RAC(8) (2026-07-29) (0) | 2026.07.29 |
| [오라클 DBA 과정] 89일차 RAC(6) (2026-07-27) (0) | 2026.07.27 |
| [오라클 DBA 과정] 88일차 RAC(5) (2026-07-24) (0) | 2026.07.25 |
| [오라클 DBA 과정] 87일차 RAC(4) (2026-07-23) (0) | 2026.07.23 |
