[2025-09-14] Oracle RAC 2 Node 환경에서 풀 백업 후 특정 Datafile을 추가 후 DB 유실 후 전체 DB 복구 과정
[테스트
개요]
RAC 2노드 환경에서 풀백업 후 데이터파일 추가 → DB 유실 → 전체 복구
[테스트 환경]
OS : Oracle
Linux Server 7.9 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d
'"')
OS Kernal : 5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version
: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from
v\$version where banner like 'Oracle Database%';" | sqlplus -s / as
sysdba)
Oracle Configuration
DB명 : ORA12R1 (echo -e
'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name
from v$database;' | sqlplus -s / as sysdba) '
RAC Node
1
Hostname : ol7ora12r11
(hostname)
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R11 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol7ora12r12
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R12 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE)
(33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913)
(26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720
(33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
Oracle (opatch lspatches) (oracle os
user)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
1. 데이터 파일 현황 및 컨트롤 파일 내용 확인
--본 테스트를 위한 디렉토리
생성
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ mkdir -pv
/home/oracle/TEST_BACKUP/autobackup
mkdir: created directory
‘/home/oracle/TEST_BACKUP’
mkdir: created directory
‘/home/oracle/TEST_BACKUP/autobackup’
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 12:36:04 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$
COL
TABLESPACE_NAME FOR A20
COL
FILE_ID FOR 99990
COL
FILE_NAME FOR A60
COL
SIZE_MB FOR
999,999,990.00
COL
BLOCKS FOR
999,999,999
COL
STATUS FOR
A10
COL AUTOEXTENSIBLE FOR A3
SELECT
A.TABLESPACE_NAME
,
A.FILE_ID
, A.FILE_NAME
, ROUND(A.BYTES/1024/1024, 2) AS SIZE_MB
,
A.BLOCKS
, A.STATUS
,
A.AUTOEXTENSIBLE
FROM DBA_DATA_FILES A
ORDER BY
A.FILE_ID;
TABLESPACE_NAME
FILE_ID
FILE_NAME
SIZE_MB BLOCKS
STATUS AUT
-------------------- -------
------------------------------------------------------------ ---------------
------------ ----------
---
SYSTEM
1
+DATA1/ORA12R1/DATAFILE/system.273.1211816079
830.00 106,240 AVAILABLE
YES
TUNER_DATA1
2
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
1,536.00 196,608 AVAILABLE
YES
SYSAUX
3
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
1,614.00 206,592 AVAILABLE
YES
UNDOTBS1
4
+DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
955.00 122,240 AVAILABLE
YES
UNDOTBS2
5
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
100.00 12,800 AVAILABLE
YES
USERS
7
+DATA1/ORA12R1/DATAFILE/users.263.1211816233
5.00 640 AVAILABLE
YES
TUNER_IDX1
8
+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
1,024.00 131,072 AVAILABLE
YES
--> 현재 총 7개의 datafile 이
존재함
7 rows
selected.
Elapsed: 00:00:00.00
[ol7ora12r11]<SYS@ORA12R11>$ alter database backup controlfile to trace as '/home/oracle/TEST_BACKUP/controlfile_bak.sql';
Database altered.
Elapsed: 00:00:00.03
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ vi /home/oracle/TEST_BACKUP/controlfile_bak.sql
...생략
CREATE CONTROLFILE REUSE DATABASE "ORA12R1" RESETLOGS
ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+FRA1/ORA12R1/ONLINELOG/group_1.259.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_1.265.1211817503'
) SIZE 200M
BLOCKSIZE 512,
GROUP 2 (
'+FRA1/ORA12R1/ONLINELOG/group_2.258.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_2.266.1211817503'
) SIZE 200M
BLOCKSIZE 512,
GROUP 3 (
'+FRA1/ORA12R1/ONLINELOG/group_3.299.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_3.277.1211817503'
) SIZE 200M
BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA1/ORA12R1/DATAFILE/system.273.1211816079',
'+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067',
'+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093',
'+DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079',
'+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093',
'+DATA1/ORA12R1/DATAFILE/users.263.1211816233',
'+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069'
CHARACTER SET
KO16MSWIN949
;
...생략
--> 현재 총 7개의 datafile 이 존재함
2. 풀 백업 진행
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
rt
alias rt='rman target /'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ rt
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Sep 14 12:47:19 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12R1 (DBID=270370777)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
using
target database control file instead of recovery catalog
old RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN
configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/TEST_BACKUP/autobackup/%F';
old RMAN
configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/home/oracle/TEST_BACKUP/autobackup/%F';
new RMAN configuration
parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/home/oracle/TEST_BACKUP/autobackup/%F';
new RMAN configuration parameters
are successfully stored
--CONFIGURE
DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED
BACKUPSET;
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE
CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE
DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
sql 'alter system
checkpoint';
sql 'alter system archive log
current';
crosscheck backupset;
crosscheck
backup;
crosscheck copy;
crosscheck archivelog
all;
BACKUP INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET FORMAT
'/home/oracle/TEST_BACKUP/%d_L0_%T_%U.bkp' DATABASE;
delete noprompt
obsolete;
delete noprompt expired backup;
sql 'alter system
archive log current';
BACKUP AS COMPRESSED BACKUPSET FORMAT
'/home/oracle/TEST_BACKUP/arch_%d_%T_%U.bkp' ARCHIVELOG ALL delete
input;
delete backup of archivelog all completed before
'SYSDATE-14';
RELEASE CHANNEL c1;
RELEASE CHANNEL
c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL
c4;
}
allocated
channel: c1
channel c1: SID=17 instance=ORA12R11 device
type=DISK
allocated
channel: c2
channel c2: SID=572 instance=ORA12R11 device
type=DISK
allocated
channel: c3
channel c3: SID=1116 instance=ORA12R11 device
type=DISK
allocated
channel: c4
channel c4: SID=1646 instance=ORA12R11 device
type=DISK
sql statement: alter system checkpoint
sql statement: alter system archive log current
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3r43m8hp_1_1.bkp RECID=110
STAMP=1211834937
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3s43m8hp_1_1.bkp
RECID=111 STAMP=1211834937
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3p43m8hp_1_1.bkp RECID=112
STAMP=1211834937
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3q43m8hp_1_1.bkp
RECID=113 STAMP=1211834937
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-11 RECID=114
STAMP=1211834962
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3u43m8ik_1_1.bkp RECID=115
STAMP=1211834964
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3v43m8ik_1_1.bkp RECID=116
STAMP=1211834964
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-12 RECID=117
STAMP=1211834965
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3r43m8hp_1_1.bkp RECID=110
STAMP=1211834937
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3s43m8hp_1_1.bkp
RECID=111 STAMP=1211834937
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3p43m8hp_1_1.bkp RECID=112
STAMP=1211834937
crosschecked backup piece: found to be 'AVAILABLE'
backup
piece handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3q43m8hp_1_1.bkp
RECID=113 STAMP=1211834937
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-11 RECID=114
STAMP=1211834962
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3u43m8ik_1_1.bkp RECID=115
STAMP=1211834964
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3v43m8ik_1_1.bkp RECID=116
STAMP=1211834964
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-12 RECID=117
STAMP=1211834965
Crosschecked 2 objects
specification does not match any datafile copy in the
repository
specification does not match any control file copy in the
repository
validation succeeded for archived log
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_11.298.1211835007
RECID=155 STAMP=1211835006
Crosschecked 1 objects
validation succeeded for archived log
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_11.298.1211835007
RECID=155 STAMP=1211835006
Crosschecked 1 objects
Starting backup at 2025-09-14 20:50:07
channel c1: starting compressed
incremental level 0 datafile backup set
channel c1: specifying datafile(s) in
backup set
input datafile file number=00002
name=+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
input datafile file
number=00001 name=+DATA1/ORA12R1/DATAFILE/system.273.1211816079
channel c1:
starting piece 1 at 2025-09-14 20:50:07
channel c2: starting compressed
incremental level 0 datafile backup set
channel c2: specifying datafile(s) in
backup set
input datafile file number=00003
name=+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
channel c2: starting piece
1 at 2025-09-14 20:50:07
channel c3: starting compressed incremental level 0
datafile backup set
channel c3: specifying datafile(s) in backup set
input
datafile file number=00004
name=+DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
input datafile file
number=00005 name=+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
channel c3:
starting piece 1 at 2025-09-14 20:50:07
channel c4: starting compressed
incremental level 0 datafile backup set
channel c4: specifying datafile(s) in
backup set
input datafile file number=00008
name=+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
input datafile file
number=00007 name=+DATA1/ORA12R1/DATAFILE/users.263.1211816233
channel c4:
starting piece 1 at 2025-09-14 20:50:07
channel c3: finished piece 1 at
2025-09-14 20:50:08
piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
tag=TAG20250914T205007 comment=NONE
channel c3: backup set complete, elapsed
time: 00:00:01
channel c4: finished piece 1 at 2025-09-14 20:50:10
piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
tag=TAG20250914T205007 comment=NONE
channel c4: backup set complete, elapsed
time: 00:00:03
channel c1: finished piece 1 at 2025-09-14 20:50:22
piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
tag=TAG20250914T205007 comment=NONE
channel c1: backup set complete, elapsed
time: 00:00:15
channel c2: finished piece 1 at 2025-09-14 20:50:32
piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
tag=TAG20250914T205007 comment=NONE
channel c2: backup set complete, elapsed
time: 00:00:25
Finished backup at 2025-09-14 20:50:32
Starting
Control File and SPFILE Autobackup at 2025-09-14 20:50:32
piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-13
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-09-14
20:50:33
RMAN
retention policy will be applied to the command
RMAN retention policy is set
to redundancy 1
Deleting the following obsolete backups and
copies:
Type
Key Completion Time
Filename/Handle
-------------------- ------ ------------------
--------------------
Backup
Set
112 2025-09-14 20:49:11
Backup
Piece 112 2025-09-14
20:49:11 /home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3p43m8hp_1_1.bkp
Backup
Set
113 2025-09-14 20:49:17
Backup
Piece 113 2025-09-14
20:49:17 /home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3q43m8hp_1_1.bkp
Backup
Set
110 2025-09-14 20:48:57
Backup
Piece 110 2025-09-14
20:48:57 /home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3r43m8hp_1_1.bkp
Backup
Set
111 2025-09-14 20:48:58
Backup
Piece 111 2025-09-14
20:48:58 /home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3s43m8hp_1_1.bkp
Backup
Set
114 2025-09-14 20:49:22
Backup
Piece 114 2025-09-14
20:49:22 /home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-11
Backup
Set
115 2025-09-14 20:49:24
Backup
Piece 115 2025-09-14
20:49:24
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3u43m8ik_1_1.bkp
Backup
Set
116 2025-09-14 20:49:24
Backup
Piece 116 2025-09-14
20:49:24
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3v43m8ik_1_1.bkp
Archive
Log 155
2025-09-14 20:50:06
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_11.298.1211835007
Backup
Set
117 2025-09-14 20:49:25
Backup
Piece 117 2025-09-14
20:49:25 /home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-12
deleted
backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3s43m8hp_1_1.bkp RECID=111
STAMP=1211834937
deleted backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3p43m8hp_1_1.bkp RECID=112
STAMP=1211834937
deleted backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3r43m8hp_1_1.bkp RECID=110
STAMP=1211834937
deleted backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_3q43m8hp_1_1.bkp RECID=113
STAMP=1211834937
deleted backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-11 RECID=114
STAMP=1211834962
deleted backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3u43m8ik_1_1.bkp RECID=115
STAMP=1211834964
Deleted 2 objects
deleted
backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_3v43m8ik_1_1.bkp RECID=116
STAMP=1211834964
Deleted 2 objects
deleted
archived log
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_11.298.1211835007
RECID=155 STAMP=1211835006
Deleted 2 objects
deleted
backup piece
backup piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-12 RECID=117
STAMP=1211834965
Deleted 3 objects
specification does not match any backup in the
repository
sql statement: alter system archive log current
Starting
backup at 2025-09-14 20:50:34
current log archived
channel c1: starting
compressed archived log backup set
channel c1: specifying archived log(s) in
backup set
input archived log thread=1 sequence=12 RECID=156
STAMP=1211835034
channel c1: starting piece 1 at 2025-09-14
20:50:34
channel c2: starting compressed archived log backup set
channel
c2: specifying archived log(s) in backup set
input archived log thread=1
sequence=13 RECID=157 STAMP=1211835034
channel c2: starting piece 1 at
2025-09-14 20:50:34
channel c1: finished piece 1 at 2025-09-14
20:50:35
piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
tag=TAG20250914T205034 comment=NONE
channel c1: backup set complete, elapsed
time: 00:00:01
channel c1: deleting archived log(s)
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_12.298.1211835035
RECID=156 STAMP=1211835034
channel c2: finished piece 1 at 2025-09-14
20:50:35
piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
tag=TAG20250914T205034 comment=NONE
channel c2: backup set complete, elapsed
time: 00:00:01
channel c1: deleting archived log(s)
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_13.287.1211835035
RECID=157 STAMP=1211835034
Finished backup at 2025-09-14
20:50:35
Starting
Control File and SPFILE Autobackup at 2025-09-14 20:50:35
piece
handle=/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-14
comment=NONE
Finished Control File and SPFILE Autobackup at 2025-09-14
20:50:36
specification does not match any backup in the repository
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN> list backup;
List of
Backup Sets
===================
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
118 Incr 0
6.55M
DISK 00:00:01
2025-09-14 20:50:08
BP Key:
118 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
List of
Datafiles in backup set 118
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
4 0 Incr
3821578 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
5 0 Incr 3821578 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
119 Incr 0
14.15M DISK
00:00:02 2025-09-14
20:50:09
BP Key: 119
Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
List of
Datafiles in backup set 119
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
7 0 Incr
3821579 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/users.263.1211816233
8 0 Incr 3821579 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
120 Incr 0
243.66M DISK
00:00:14 2025-09-14
20:50:21
BP Key: 120
Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
List of
Datafiles in backup set 120
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
1 0 Incr
3821576 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/system.273.1211816079
2 0 Incr 3821576 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
121 Incr 0
321.74M DISK
00:00:21 2025-09-14
20:50:28
BP Key: 121
Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
List of
Datafiles in backup set 121
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
3 0 Incr
3821577 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
122 Full
19.09M DISK
00:00:00 2025-09-14
20:50:32
BP Key: 122
Status: AVAILABLE Compressed: NO Tag:
TAG20250914T205032
Piece Name:
/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-13
SPFILE
Included: Modification time: 2025-09-14 20:50:28
SPFILE
db_unique_name: ORA12R1
Control File Included: Ckp SCN:
3821837 Ckp time: 2025-09-14
20:50:32
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
123
4.50K
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
123 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
List
of Archived Logs in backup set 123
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
13 3821870 2025-09-14 20:50:34
3821881 2025-09-14 20:50:34
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
124
1.53M
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
124 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
List
of Archived Logs in backup set 124
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
12 3821531 2025-09-14 20:50:06
3821870 2025-09-14 20:50:34
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
125 Full
19.09M DISK
00:00:00 2025-09-14
20:50:35
BP Key: 125
Status: AVAILABLE Compressed: NO Tag:
TAG20250914T205035
Piece Name:
/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-14
SPFILE Included: Modification time: 2025-09-14 20:50:28
SPFILE
db_unique_name: ORA12R1
Control File Included: Ckp SCN:
3821901 Ckp time: 2025-09-14
20:50:35
--> 현재 시점에 백업된 컨트롤 파일
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias alog
alias
alog='tail -f
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
alog
Current log# 2 seq# 14 mem# 1:
+DATA1/ORA12R1/ONLINELOG/group_2.266.1211817503
Sun Sep 14 20:50:34
2025
Archived Log entry 157 added for T-1.S-13 ID 0x10244064 LAD:1
Sun Sep
14 20:50:35 2025
Starting control autobackup
Control autobackup written to DISK device
handle
'/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-14'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ls -lRh
/home/oracle/TEST_BACKUP
/home/oracle/TEST_BACKUP:
total
588M
-rw-r-----. 1 oracle asmadmin 1.6M Sep 14 20:50
arch_ORA12R1_20250914_4643m8kq_1_1.bkp
-rw-r-----. 1 oracle asmadmin 5.0K Sep
14 20:50 arch_ORA12R1_20250914_4743m8kq_1_1.bkp
drwxr-xr-x. 2 oracle
oinstall 68 Sep 14 20:50 autobackup
-rw-r--r--. 1 oracle
asmadmin 13K Sep 14 20:47 controlfile_bak.sql
-rw-r-----. 1 oracle
asmadmin 244M Sep 14 20:50 ORA12R1_L0_20250914_4143m8jv_1_1.bkp
-rw-r-----. 1
oracle asmadmin 322M Sep 14 20:50
ORA12R1_L0_20250914_4243m8jv_1_1.bkp
-rw-r-----. 1 oracle asmadmin 6.6M Sep
14 20:50 ORA12R1_L0_20250914_4343m8jv_1_1.bkp
-rw-r-----. 1 oracle
asmadmin 15M Sep 14 20:50
ORA12R1_L0_20250914_4443m8jv_1_1.bkp
/home/oracle/TEST_BACKUP/autobackup:
total 39M
-rw-r-----. 1 oracle
asmadmin 20M Sep 14 20:50 c-270370777-20250914-13
-rw-r-----. 1 oracle
asmadmin 20M Sep 14 20:50 c-270370777-20250914-14
3. 데이터
파일 추가
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 13:43:57 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ ALTER TABLESPACE TUNER_DATA1 ADD DATAFILE '+DATA1' SIZE 1024M;
Tablespace altered.
Elapsed: 00:00:00.74
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 13:43:57 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$
COL
TABLESPACE_NAME FOR
A20
COL
FILE_ID FOR
99990
COL FILE_NAME FOR
A60
COL
SIZE_MB FOR 999,999,990.00
COL
BLOCKS FOR
999,999,999
COL
STATUS FOR
A10
COL AUTOEXTENSIBLE FOR
A3
SELECT A.TABLESPACE_NAME
,
A.FILE_ID
,
A.FILE_NAME
, ROUND(A.BYTES/1024/1024, 2) AS
SIZE_MB
, A.BLOCKS
, A.STATUS
, A.AUTOEXTENSIBLE
FROM DBA_DATA_FILES A
ORDER BY
A.FILE_ID;
TABLESPACE_NAME FILE_ID
FILE_NAME
SIZE_MB BLOCKS
STATUS AUT
--------------------
------- ------------------------------------------------------------
--------------- ------------ ---------- ---
SYSTEM
1
+DATA1/ORA12R1/DATAFILE/system.273.1211816079
830.00 106,240 AVAILABLE YES
TUNER_DATA1
2
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
1,536.00 196,608 AVAILABLE YES
SYSAUX
3
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
1,624.00 207,872 AVAILABLE YES
UNDOTBS1
4
+DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
955.00 122,240 AVAILABLE YES
UNDOTBS2
5
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
100.00 12,800 AVAILABLE YES
USERS
7
+DATA1/ORA12R1/DATAFILE/users.263.1211816233
5.00 640 AVAILABLE
YES
TUNER_IDX1
8
+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
1,024.00 131,072 AVAILABLE YES
TUNER_DATA1
9
+DATA1/ORA12R1/DATAFILE/tuner_data1.264.1211835267
1,024.00 131,072 AVAILABLE
NO
--> FILE_ID=9 인 datafile 이
추가됨
8 rows selected.
Elapsed: 00:00:00.02
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 13:46:18 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
-- datafile 추가 후 컨트롤 파일
재확인
[ol7ora12r11]<SYS@ORA12R11>$ alter database backup controlfile to trace as
'/home/oracle/TEST_BACKUP/controlfile_bak2.sql';
Database altered.
Elapsed: 00:00:00.01
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ vi
/home/oracle/TEST_BACKUP/controlfile_bak2.sql
...생략
CREATE CONTROLFILE REUSE
DATABASE "ORA12R1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+FRA1/ORA12R1/ONLINELOG/group_1.259.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_1.265.1211817503'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'+FRA1/ORA12R1/ONLINELOG/group_2.258.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_2.266.1211817503'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'+FRA1/ORA12R1/ONLINELOG/group_3.299.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_3.277.1211817503'
) SIZE 200M BLOCKSIZE 512,
GROUP 4 (
'+FRA1/ORA12R1/ONLINELOG/group_4.291.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_4.272.1211817503'
) SIZE 200M BLOCKSIZE 512,
GROUP 5 (
'+FRA1/ORA12R1/ONLINELOG/group_5.297.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_5.267.1211817503'
) SIZE 200M BLOCKSIZE 512,
GROUP 6 (
'+FRA1/ORA12R1/ONLINELOG/group_6.289.1211817501',
'+DATA1/ORA12R1/ONLINELOG/group_6.278.1211817503'
) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA1/ORA12R1/DATAFILE/system.273.1211816079',
'+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067',
'+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093',
'+DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079',
'+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093',
'+DATA1/ORA12R1/DATAFILE/users.263.1211816233',
'+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069',
'+DATA1/ORA12R1/DATAFILE/tuner_data1.264.1211835267'
CHARACTER SET KO16MSWIN949
;
--> 컨트롤파일에 새로 추가한 데이터
파일('+DATA1/ORA12R1/DATAFILE/tuner_data1.264.1211835267')이 보임
...생략
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 13:50:38 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CREATE TABLE
TUNER.TB_DATAFILE_BACKUP_TEST
(
TEST_NO NUMBER(15)
) TABLESPACE TUNER_DATA1
;
INSERT INTO
TUNER.TB_DATAFILE_BACKUP_TEST
SELECT LVL
FROM TUNER.TB_CUST A, (SELECT LEVEL LVL FROM DUAL
CONNECT BY LEVEL <= 10)
;
commit;
Commit complete.
[ol7ora12r11]<SYS@ORA12R11>$ select count(*) as cnt from TUNER.TB_DATAFILE_BACKUP_TEST;
CNT
----------
100000
1 row selected.
Elapsed: 00:00:00.01
[ol7ora12r11]<SYS@ORA12R11>$ select current_scn from v$database;
CURRENT_SCN
-----------
3823198
1 row selected.
[ol7ora12r11]<SYS@ORA12R11>$ select sysdate from dual;
SYSDATE
-------------------
2025-09-14:20:57:30
1 row selected.
Elapsed: 00:00:00.00
col tablespace_name format a20
col
owner format a20
col segment_name format a30
col
blocks format
999G999G999G990
col
size_mb format
999G999G990D0000
SELECT A.TABLESPACE_NAME
, A.OWNER
, A.SEGMENT_NAME
, A.BLOCKS
, ROUND(A.BYTES/1024/1204, 4) AS
SIZE_MB
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_NAME =
UPPER('TB_DATAFILE_BACKUP_TEST')
AND
A.TABLESPACE_NAME = UPPER('TUNER_DATA1')
;
TABLESPACE_NAME
OWNER
SEGMENT_NAME
BLOCKS SIZE_MB
-------------------- --------------------
------------------------------ ---------------- -----------------
TUNER_DATA1
TUNER
TB_DATAFILE_BACKUP_TEST
256
1.7010
1 row selected.
Elapsed: 00:00:00.01
COL
OWNER FOR
A20
COL
FILE_ID FOR 99990
COL FILE_NAME FOR
A60
COL TABLESPACE_NAME FOR A20
COL
SIZE_MB FOR
999,999,990.0000
SELECT E.OWNER
, A.FILE_ID
, A.FILE_NAME
, A.TABLESPACE_NAME
, ROUND(SUM(E.BYTES)/1024/1204, 4)
AS SIZE_MB
FROM DBA_EXTENTS E
, DBA_DATA_FILES A
WHERE E.FILE_ID = A.FILE_ID
AND A.TABLESPACE_NAME = 'TUNER_DATA1'
GROUP BY E.OWNER, A.FILE_ID, A.FILE_NAME,
A.TABLESPACE_NAME
ORDER BY A.FILE_ID
;
OWNER
FILE_ID
FILE_NAME
TABLESPACE_NAME
SIZE_MB
-------------------- -------
------------------------------------------------------------
-------------------- -----------------
TUNER
2
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
TUNER_DATA1
1,163.4817
TUNER
9
+DATA1/ORA12R1/DATAFILE/tuner_data1.264.1211835267
TUNER_DATA1
0.2126
--> FILE_ID=9 인 파일에 데이터가 쌓인 것을
확인
--현재까지의 변경 사항을 아카이브 로그에 저장
set heading ON
col thread#
format 999
col
group# format 999
col sequence# format
999999
col
size_mb format 999G999G990D00
col archived format
a3
col
status format a12
col first_change# format 999999999999999
col next_change# format 999999999999999
col member
format a50
SELECT A.GROUP#
, A.THREAD#
, A.SEQUENCE#
, A.BYTES/1024/1024 AS SIZE_MB
, A.ARCHIVED
, A.STATUS
, A.FIRST_CHANGE#
, A.NEXT_CHANGE#
, B.MEMBER
FROM V$LOG A
, V$LOGFILE B
WHERE A.GROUP# = B.GROUP#
ORDER BY A.THREAD#, A.SEQUENCE#;
GROUP# THREAD#
SEQUENCE# SIZE_MB ARC
STATUS
FIRST_CHANGE# NEXT_CHANGE# MEMBER
------ ------- --------- --------------- --- ------------
---------------- ----------------
--------------------------------------------------
3
1
12 200.00 YES
INACTIVE
3821531 3821870
+FRA1/ORA12R1/ONLINELOG/group_3.299.1211817501
3
1
12 200.00 YES
INACTIVE
3821531 3821870
+DATA1/ORA12R1/ONLINELOG/group_3.277.1211817503
1
1
13 200.00 YES
INACTIVE
3821870 3821881
+DATA1/ORA12R1/ONLINELOG/group_1.265.1211817503
1
1
13 200.00 YES
INACTIVE
3821870 3821881
+FRA1/ORA12R1/ONLINELOG/group_1.259.1211817501
2
1
14 200.00 NO
CURRENT
3821881 ################ +DATA1/ORA12R1/ONLINELOG/group_2.266.1211817503
2
1
14 200.00 NO
CURRENT
3821881 ################ +FRA1/ORA12R1/ONLINELOG/group_2.258.1211817501
6
2
0 200.00 YES
UNUSED
0
0 +FRA1/ORA12R1/ONLINELOG/group_6.289.1211817501
6
2
0 200.00 YES
UNUSED
0
0 +DATA1/ORA12R1/ONLINELOG/group_6.278.1211817503
4
2
1 200.00 YES
INACTIVE
3729849 3730120
+FRA1/ORA12R1/ONLINELOG/group_4.291.1211817501
4
2
1 200.00 YES
INACTIVE
3729849 3730120
+DATA1/ORA12R1/ONLINELOG/group_4.272.1211817503
5
2
2 200.00 NO
CURRENT
3828803 ################ +FRA1/ORA12R1/ONLINELOG/group_5.297.1211817501
5
2
2 200.00 NO
CURRENT
3828803 ################
+DATA1/ORA12R1/ONLINELOG/group_5.267.1211817503
--> 현재 thread 1의 sequence =
14번 및 thread 2의 sequence = 2번이 current인 것을 알 수 있음
--> datafile 9번을 추가 후 테이블 생성 하고 데이터를 입력한 모든 과정까지가 해당 리두
로그 범위 내에 저장되어 있다고 할 수 있음
--2번
실행해줌
[ol7ora12r11]<SYS@ORA12R11>$ alter system archive log current;
[ol7ora12r11]<SYS@ORA12R11>$ alter system archive log current;
--다시 조회
GROUP# THREAD#
SEQUENCE# SIZE_MB ARC
STATUS
FIRST_CHANGE# NEXT_CHANGE# MEMBER
------ ------- --------- --------------- --- ------------
---------------- ----------------
--------------------------------------------------
2
1
14 200.00 YES
ACTIVE
3821881 3831902
+FRA1/ORA12R1/ONLINELOG/group_2.258.1211817501
2
1
14 200.00 YES
ACTIVE
3821881 3831902
+DATA1/ORA12R1/ONLINELOG/group_2.266.1211817503
3
1
15 200.00 YES
ACTIVE
3831902 3831913
+FRA1/ORA12R1/ONLINELOG/group_3.299.1211817501
3
1
15 200.00 YES
ACTIVE
3831902 3831913
+DATA1/ORA12R1/ONLINELOG/group_3.277.1211817503
1
1
16 200.00 NO
CURRENT
3831913 ################ +FRA1/ORA12R1/ONLINELOG/group_1.259.1211817501
1
1
16 200.00 NO
CURRENT
3831913 ################ +DATA1/ORA12R1/ONLINELOG/group_1.265.1211817503
5
2
2 200.00 YES
ACTIVE
3828803 3831906
+FRA1/ORA12R1/ONLINELOG/group_5.297.1211817501
5
2
2 200.00 YES
ACTIVE
3828803 3831906
+DATA1/ORA12R1/ONLINELOG/group_5.267.1211817503
6
2
3 200.00 YES
ACTIVE
3831906 3831916
+FRA1/ORA12R1/ONLINELOG/group_6.289.1211817501
6
2
3 200.00 YES
ACTIVE
3831906 3831916
+DATA1/ORA12R1/ONLINELOG/group_6.278.1211817503
4
2
4 200.00 NO
CURRENT
3831916 ################ +FRA1/ORA12R1/ONLINELOG/group_4.291.1211817501
4
2
4 200.00 NO
CURRENT
3831916 ################
+DATA1/ORA12R1/ONLINELOG/group_4.272.1211817503
--> 현재 thread 1의 sequence =
14번 및 15번 과 thread 2의 sequence = 2번 및 3번이 Archiving된 것을 알 수 있음
--> 현재 thread 1의
sequence = 16번 및 thread 2의 sequence = 4번이 current인 것을 알 수 있음
--> 만약 redo
log를 유실한 후 복구한다면 thread 1의 16번이 없다고 하면서 에러 날 것임 (FIRST_CHANGE#이 3831913 이므로
thread 2의 4번 보다 작음)
[ol7ora12r11]<SYS@ORA12R11>$ select current_scn from v$database;
CURRENT_SCN
-----------
3833554
-->
TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및 commit한 직후 scn
3823198 이었음
--> scn은 지속적으로 증가하므로 현재 3833554 까지 간
상태임 (3823198 -> 3833554)
[ol7ora12r11]<SYS@ORA12R11>$ SELECT RESETLOGS_CHANGE#, ARCHIVE_CHANGE#, CHECKPOINT_CHANGE#, CONTROLFILE_CHANGE#, CURRENT_SCN FROM V$DATABASE;
RESETLOGS_CHANGE#
ARCHIVE_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CURRENT_SCN
----------------- --------------- ------------------
------------------- -----------
3729849 3831909
3831913
3836145 3836147
-->
TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및 commit한 직후 scn 3823198
이었음
--> 현재 ARCHIVE_CHANGE#가 3831909 까지 갔으므로 아카이브 로그 만으로도
-->
TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및 commit한 직후 까지 복구 가능한 상황인
것임
1 row selected.
Elapsed: 00:00:00.01
4. 장애 발생
--우선 db를 abort로 내림 (2 Node RAC이므로 두개의 노드를 모두 내림)
--1번 노드
[ol7ora12r11]<SYS@ORA12R11>$ shutdown abort;
ORACLE instance
shut down.
--2번 노드
[ol7ora12r12]<SYS@ORA12R12>$ shutdown abort;
ORACLE instance
shut down.
--테스트를 위해 asmcmd로 직접 데이터 파일을
제거해야하므로 asm 인스턴스 강제로 재기동
--1번
노드
[+ASM1:grid@ol7ora12r11][/home/grid]$ srvctl stop asm -n
ol7ora12r11 -force
[+ASM1:grid@ol7ora12r11][/home/grid]$
srvctl start asm -n ol7ora12r11
--2번 노드
[+ASM2:grid@ol7ora12r12][/home/grid]$ srvctl stop asm -n
ol7ora12r12 -force
[+ASM2:grid@ol7ora12r12][/home/grid]$
srvctl start asm -n ol7ora12r12
[+ASM1:grid@ol7ora12r11][/home/grid]$ alias
asmcmd
alias asmcmd='rlwrap asmcmd -p'
[+ASM1:grid@ol7ora12r11][/home/grid]$
asmcmd
ASMCMD
[+DATA1/ORA12R1/CONTROLFILE] > rm -f *
ASMCMD
[+FRA1/ORA12R1/CONTROLFILE] > rm -f *
ASMCMD
[+DATA1/ORA12R1/DATAFILE] > rm -f
ASMCMD
[+DATA1/ORA12R1/TEMPFILE] > rm -f *
ASMCMD
[+DATA1/ORA12R1/ONLINELOG] > rm -f *
ASMCMD
[+FRA1/ORA12R1/ONLINELOG] > rm -f *
--> 컨트롤 파일,
데이터 파일, 템프 파일, 리두로그 삭제
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 14:26:35 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[]<SYS@ORA12R11>$ startup
ORACLE instance started.
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
ORA-00205: error in identifying control
file, check alert log for more info
--> 컨트롤 파일이 없어서 mount부터 실패하는 상황임 장애
발생!
[]<SYS@ORA12R11>$ shutdown abort
ORACLE instance
shut down.
5. 복구 작업
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[]<SYS@ORA12R11>$ startup nomount
ORACLE instance
started.
Total System
Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
[]<SYS@ORA12R11>$ show parameter spfile;
NAME
TYPE
VALUE
------------------------------------
---------------------------------
--------------------------------------------------
spfile
string
+DATA1/ORA12R1/PARAMETERFILE/spfile.276.1211378811
--백업 작업 당시 백업 받았었던 컨트롤
파일 (이 컨트롤 파일로 복구할 것임 datafile 9번을 add후 자동으로 생성된 컨트롤 파일은 사용하지 않을
것임)
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
125
Full 19.09M
DISK 00:00:00
2025-09-14 20:50:35
BP Key:
125 Status: AVAILABLE Compressed: NO Tag:
TAG20250914T205035
Piece Name:
/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-14
SPFILE Included: Modification time: 2025-09-14
20:50:28
SPFILE db_unique_name: ORA12R1
Control File Included: Ckp SCN:
3821901 Ckp time: 2025-09-14 20:50:35
--> 백업
작업 시점에 백업된 컨트롤 파일
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
rt
alias rt='rman
target /'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
rt
RMAN> restore controlfile from '/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-14';
Starting restore
at 2025-09-14 21:53:11
using target database control
file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel ORA_DISK_1: SID=1102
instance=ORA12R11 device type=DISK
channel
ORA_DISK_1: restoring control file
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:01
output file
name=+FRA1/ORA12R1/CONTROLFILE/current.289.1211838791
output file
name=+DATA1/ORA12R1/CONTROLFILE/current.279.1211838791
Finished restore at 2025-09-14 21:53:12
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+FRA1/ORA12R1/CONTROLFILE
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT FINE
SEP 14 21:00:00 Y
16384 1217 19939328 33554432
current.289.1211838791
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1/CONTROLFILE
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT FINE
SEP 14 21:00:00 Y
16384 1217 19939328 33554432
current.279.1211838791
--> 컨트롤 파일이 복구된 것을 확인 완료함
-- 컨트롤 파일이 복구 됐으니 마운트
가능
RMAN> alter
database mount;
Statement
processed
released channel:
ORA_DISK_1
RMAN> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA12R11
MOUNTED
RMAN> list backup;
List of Backup
Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
118 Incr
0 6.55M
DISK 00:00:01
2025-09-14 20:50:08
BP Key:
118 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
List of Datafiles in backup set 118
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
4 0 Incr 3821578 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
5 0 Incr
3821578 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
119 Incr
0 14.15M
DISK 00:00:02
2025-09-14 20:50:09
BP Key:
119 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
List of Datafiles in backup set 119
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
7 0 Incr 3821579 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/users.263.1211816233
8 0 Incr
3821579 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
120 Incr
0 243.66M DISK
00:00:14 2025-09-14 20:50:21
BP Key:
120 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
List of Datafiles in backup set 120
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
1 0 Incr 3821576 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/system.273.1211816079
2 0 Incr
3821576 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
121 Incr
0 321.74M DISK
00:00:21 2025-09-14 20:50:28
BP Key:
121 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
List of Datafiles in backup set 121
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
3 0 Incr 3821577 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
122
Full 19.09M
DISK 00:00:00
2025-09-14 20:50:32
BP Key:
122 Status: AVAILABLE Compressed: NO Tag:
TAG20250914T205032
Piece Name:
/home/oracle/TEST_BACKUP/autobackup/c-270370777-20250914-13
SPFILE Included: Modification time: 2025-09-14
20:50:28
SPFILE db_unique_name: ORA12R1
Control File Included: Ckp SCN:
3821837 Ckp time: 2025-09-14
20:50:32
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
123
4.50K
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
123 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
List of Archived Logs in backup set 123
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 13 3821870
2025-09-14 20:50:34 3821881 2025-09-14
20:50:34
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
124
1.53M
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
124 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
List of Archived Logs in backup set 124
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 12 3821531
2025-09-14 20:50:06 3821870 2025-09-14 20:50:34
-->
TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및 commit한 직후 scn
3823198
--> 현재 아카이브 백업본은 3823198 보다 작은 3821881 까지 백업되어
있음 (최종 백업 받은 시점이후로 생성된 아카이브 파일은 백업되지 않은 상태임)
--> 현재 백업본 만으로는 3823198 까지 갈수 없는 상황임
--> 당연히 해당 백업을 받은 이후 datafile 추가, 테이블 생성, 데이터 입력을 한 것이므로
없는 것임
-- 아카이브는 현재 정상적으로 asmdisk에 존재함
-- (여기서 주목! thread 1의 13과 thread 2의 12는 백업받은 직후 delete input되었으므로 삭제된 상태임!)
-- rman에서 recover한다면 백업된 아카이브를 자기가 알아서 restore한 후
recover하지만 sqlplus에서 recover한다면 해당 아카이브 백업본을 restore시킨 후 sqlplus
recover해야함)
ASMCMD
[+FRA1/ORA12R1/ARCHIVELOG/2025_09_14] > ls
thread_1_seq_14.287.1211835989
thread_1_seq_15.302.1211835991
thread_1_seq_16.300.1211837103
thread_1_seq_17.301.1211837105
thread_2_seq_2.298.1211835989
thread_2_seq_3.303.1211835991
ASMCMD
[+FRA1/ORA12R1/ARCHIVELOG/2025_09_14] >
--> 아까전에 정리했던 내용임
--> 현재 thread 1의 sequence = 14번 및 15번 과 thread 2의
sequence = 2번 및 3번이 Archiving된 것을 알 수 있음
--> 현재
thread 1의 sequence = 16번 및 thread 2의 sequence = 4번이 current인 것을 알 수 있음
--> 만약 redo log를 유실 후 복구한다면 thread 1의 16번이 없다고 하면서 에러 날
것임 (FIRST_CHANGE#이 3831913 이므로 thread 2의 4번 보다 작음)
--> 그 사이에 thread 1의 sequence = 16번이 Archiving된 상태임
--> 이제 복구를 시도하면 thread 2의 sequence = 4번이 없다고 나올것으로 예측
가능함
--mount된 db에서 아래의 sql문을
치면
SELECT *
FROM v$archived_log
WHERE 1=1
AND
resetlogs_change# = 3729849
AND 3823198
BETWEEN FIRST_CHANGE# AND NEXT_CHANGE#
ORDER BY
thread#, sequence#;
--> 당연히 백업된 컨트롤 파일을
읽어서 mount시킨 상태기 때문에 결과가 나오지 않음
--> (백업 시점에는 해당 scn까지 못갔으므로, 백업 시점! 컨트롤 파일이 백업 컨트롤 파일임!)
-- 복구 시 사용될 백업본을
preview
RMAN> RESTORE DATABASE PREVIEW;
Starting restore
at 2025-09-14 22:03:56
using target database control
file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel ORA_DISK_1: SID=1648
instance=ORA12R11 device type=DISK
List of Backup Sets
===================
BS Key Type LV
Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
120 Incr
0 243.66M DISK
00:00:14 2025-09-14 20:50:20
BP Key:
120 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
List of Datafiles in backup set 120
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
1 0 Incr 3821576 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/system.273.1211816079
2 0 Incr
3821576 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
121 Incr
0 321.74M DISK
00:00:21 2025-09-14 20:50:28
BP Key:
121 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
List of Datafiles in backup set 121
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
3 0 Incr 3821577 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
118 Incr
0 6.55M
DISK 00:00:01
2025-09-14 20:50:08
BP Key:
118 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
List of Datafiles in backup set 118
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
4 0 Incr 3821578 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
5 0 Incr
3821578 2025-09-14
20:50:07
NO
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
BS Key Type
LV Size Device Type Elapsed Time Completion
Time
------- ---- -- ---------- ----------- ------------
-------------------
119 Incr
0 14.15M
DISK 00:00:02
2025-09-14 20:50:07
BP Key:
119 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205007
Piece Name:
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
List of Datafiles in backup set 119
File LV Type Ckp SCN Ckp
Time Abs Fuz
SCN Sparse Name
---- -- ---- ----------
------------------- ----------- ------ ----
7 0 Incr 3821579 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/users.263.1211816233
8 0 Incr
3821579 2025-09-14
20:50:07
NO +DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ -------------------
124
1.53M
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
124 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
List of
Archived Logs in backup set 124
Thrd
Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 12 3821531
2025-09-14 20:50:06 3821870 2025-09-14
20:50:34
BS Key
Size Device Type Elapsed Time Completion
Time
------- ---------- ----------- ------------
-------------------
123
4.50K
DISK 00:00:00
2025-09-14 20:50:34
BP Key:
123 Status: AVAILABLE Compressed: YES Tag:
TAG20250914T205034
Piece Name:
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
List of Archived Logs in backup set 123
Thrd Seq Low
SCN Low
Time Next
SCN Next Time
---- ------- ----------
------------------- ---------- ---------
1 13 3821870
2025-09-14 20:50:34 3821881 2025-09-14 20:50:34
recovery will be done up to SCN 3821576
Media recovery start SCN is 3821576
Recovery must be done beyond SCN 3821579 to clear datafile
fuzziness
Finished restore at 2025-09-14 22:03:56
--> 여기서
3821576 까지 복구될 것이라고 나옴
RMAN> restore database;
Starting restore
at 2025-09-14 22:05:51
using channel
ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from
backup set
channel ORA_DISK_1: restoring datafile 00004
to +DATA1/ORA12R1/DATAFILE/undotbs1.274.1211816079
channel ORA_DISK_1: restoring datafile 00005 to
+DATA1/ORA12R1/DATAFILE/undotbs2.269.1211816093
channel
ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4343m8jv_1_1.bkp
tag=TAG20250914T205007
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to
+DATA1/ORA12R1/DATAFILE/users.263.1211816233
channel
ORA_DISK_1: restoring datafile 00008 to
+DATA1/ORA12R1/DATAFILE/tuner_idx1.268.1211834069
channel ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4443m8jv_1_1.bkp
tag=TAG20250914T205007
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:03
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to
+DATA1/ORA12R1/DATAFILE/system.273.1211816079
channel
ORA_DISK_1: restoring datafile 00002 to
+DATA1/ORA12R1/DATAFILE/tuner_data1.260.1211834067
channel ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4143m8jv_1_1.bkp
tag=TAG20250914T205007
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
channel ORA_DISK_1: starting
datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211816093
channel
ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/ORA12R1_L0_20250914_4243m8jv_1_1.bkp
tag=TAG20250914T205007
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:25
Finished restore at 2025-09-14
22:06:47
--> restore 는 일단 성공
ASMCMD [+] > ls
-l +DATA1/ORA12R1/DATAFILE
DATAFILE
UNPROT COARSE SEP 14 22:00:00 Y
SYSAUX.270.1211839583
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
SYSTEM.269.1211839557
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
TUNER_DATA1.268.1211839557
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
TUNER_IDX1.263.1211839555
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
UNDOTBS1.271.1211839551
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
UNDOTBS2.264.1211839551
DATAFILE UNPROT
COARSE SEP 14 22:00:00 Y
USERS.260.1211839555
--> datafile 9번은 아직 없음
SET LINESIZE 220 PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT 999,999,999,990
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
BLOCK1_OFFSET FOR 999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
, A.BLOCK1_OFFSET
FROM V$DATAFILE A
ORDER BY A.FILE#;
FILE# TS#
NAME
SIZE_MB STATUS
CHECKPOINT_CHANGE# BLOCK1_OFFSET
------ ------
------------------------------------------------------------ ---------------
---------- -------------------- ----------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
SYSTEM
3,821,576 4,294,967,295
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,821,576 4,294,967,295
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,821,577 4,294,967,295
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,821,578 4,294,967,295
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,821,578 4,294,967,295
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,821,579 4,294,967,295
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,821,579 4,294,967,295
--> TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터
insert 및 commit한 직후 scn 3823198 이었음, 무조건 아카이브 로그로 복구해야하는
상황임!
SET LINESIZE 220
PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT
999,999,999,990
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
FROM V$DATAFILE_HEADER A;
FILE# TS#
NAME
SIZE_MB STATUS CHECKPOINT_CHANGE#
------ ------
------------------------------------------------------------ ---------------
---------- --------------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
ONLINE
3,821,576
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,821,576
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,821,577
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,821,578
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,821,578
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,821,579
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,821,579
--> TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및
commit한 직후 scn 3823198 이었음
SET LINESIZE 220
PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT
999,999,999,999,999
COL
NAME
FOR A20
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
ARCHIVE_CHANGE# FOR 999,999,999,999,999
COL CONTROLFILE_CHANGE# FOR
999,999,999,999,999
SELECT NAME, CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#, CONTROLFILE_CHANGE#, current_scn FROM
V$DATABASE;
NAME
CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
CONTROLFILE_CHANGE#
CURRENT_SCN
-------------------- --------------------
-------------------- -------------------- --------------------
ORA12R1
3,821,531
3,821,877
3,821,901
0
-->
TUNER.TB_DATAFILE_BACKUP_TEST 테이블에 데이터 insert 및 commit한 직후 scn 3823198
이었음
--> 결국 recover가 필요한 상황
(당연히 recover가 필요한 상황임! 백업
받은 후에 datafile 추가 후 테이블 만들고 데이터 입력한 상태에서 데이터가 유실됐으니 당연히 recover필요함! 이해하자!
이해하자!)
RMAN> recover
database; --> rman에서 이렇게 해주면 자동으로
추가했었던 datafile을 복구하고 마지막까지 복구해주지면 실습(학습)을 위해서 사용하지 않음
--> sqlplus에서 recover 진행함
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 22:12:34 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ recover database;
ORA-00283:
recovery session canceled due to errors
ORA-01610:
recovery using the BACKUP CONTROLFILE option must be done
--> 현재 백업된 컨트롤 파일로 mount했으므로 실행 불가
[ol7ora12r11]<SYS@ORA12R11>$ recover database using backup
controlfile;
ORA-00279: change 3821576 generated at 09/14/2025 20:50:07
needed for thread 1
ORA-00289: suggestion : +FRA1
ORA-00280: change 3821576 for thread 1 is in sequence
#12
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
AUTO
ORA-00308: cannot
open archived log '+FRA1'
ORA-17503: ksfdopn:2 Failed to
open file +FRA1
ORA-15045: ASM file name '+FRA1' is not
in reference form
ORA-00308: cannot open
archived log '+FRA1'
ORA-17503: ksfdopn:2 Failed to open
file +FRA1
ORA-15045: ASM file name '+FRA1' is not in
reference form
--> recover 실패함 thread 1의 12번이 없다고함!
--> 해당 아카이브 로그는 백업 시 백업 한 후 delete input된 상홤! 지워진 상황!
--> 그래서 못찾는 것임!
-- 아카이브 restore 해줌!
RMAN> RESTORE ARCHIVELOG SEQUENCE 12 THREAD 1;
Starting restore
at 2025-09-14 23:10:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1104 instance=ORA12R11 device
type=DISK
channel
ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4643m8kq_1_1.bkp
tag=TAG20250914T205034
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
Finished restore at 2025-09-14
23:10:38
RMAN> RESTORE ARCHIVELOG SEQUENCE 13 THREAD 1;
Starting restore
at 2025-09-14 23:10:50
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_DISK_1: reading from backup piece
/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/TEST_BACKUP/arch_ORA12R1_20250914_4743m8kq_1_1.bkp
tag=TAG20250914T205034
channel ORA_DISK_1: restored
backup piece 1
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:01
Finished restore at 2025-09-14
23:10:51
--> 아카이브 로그 백업본을 복구함!!
-- 다시 시도
[ol7ora12r11]<SYS@ORA12R11>$ recover database using backup
controlfile;
ORA-00279: change 3821576 generated at 09/14/2025 20:50:07
needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_12.297.1211843437
ORA-00280: change 3821576 for thread 1 is in sequence
#12
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3821870 generated at 09/14/2025 20:50:34
needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_13.291.1211843451
ORA-00280: change 3821870 for thread 1 is in sequence
#13
ORA-00278: log file
'+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_12.297.1211843437' no longer
needed for this recovery
ORA-00279: change
3821881 generated at 09/14/2025 20:50:34 needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_14.287.1211835989
ORA-00280: change 3821881 for thread 1 is in sequence
#14
ORA-00278: log file
'+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_13.291.1211843451' no longer
needed for this recovery
ORA-00283:
recovery session canceled due to errors
ORA-01244:
unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9:
'+DATA1/ORA12R1/DATAFILE/tuner_data1.264.1211835267'
ORA-01112: media recovery not started
--> data file 9가 없다고 복구 불가
--> 아카이브에서는 백업 작업 후 추가했었던 datafile 9번으로 접근하려했지만
--> 백업 시점의 컨트롤 파일로 mount 시킨 상태이므로 해당 datafile 9는 컨트롤 파일에 없으니 recover가 진행이안되는 상황인 것임!
SET LINESIZE 220 PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT 999,999,999,990
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
BLOCK1_OFFSET FOR 999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
, A.BLOCK1_OFFSET
FROM V$DATAFILE A
ORDER BY A.FILE#;
FILE# TS#
NAME
SIZE_MB STATUS
CHECKPOINT_CHANGE# BLOCK1_OFFSET
------ ------
------------------------------------------------------------ ---------------
---------- -------------------- ----------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
SYSTEM
3,821,881 4,294,967,295
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,821,881 4,294,967,295
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,821,881 4,294,967,295
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,821,881 4,294,967,295
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,821,881 4,294,967,295
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,821,881 4,294,967,295
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,821,881 4,294,967,295
9 6
/u01/app/oracle/product/12c/db_1/dbs/UNNAMED00009
0.00
RECOVER
3,823,000 4,294,967,295
--recover batabase using backup controlfile 명령 이후 9번 데이터파일이
UNNAMED00009으로 임시로 작성되어 있음
[ol7ora12r11]<SYS@ORA12R11>$ alter database create datafile '/u01/app/oracle/product/12c/db_1/dbs/UNNAMED00009' as '+DATA1/ORA12R1/DATAFILE/tuner_data1.2';
--> 데이터베이스 recover 전 기존에 추가한 데이터파일을 복원시키기 위한 빈 데이터파일 수동 추가
Database altered.
Elapsed: 00:00:00.70
[ol7ora12r11]<SYS@ORA12R11>$ recover database using backup
controlfile;
ORA-00279: change 3823000 generated at 09/14/2025 20:54:27
needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_14.287.1211835989
ORA-00280: change 3823000 for thread 1 is in sequence
#14
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
AUTO
ORA-00279: change
3828805 generated at 09/14/2025 21:04:07 needed for thread 2
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_2_seq_2.298.1211835989
ORA-00280: change 3828805 for thread 2 is in sequence
#2
ORA-00279: change 3831902 generated at 09/14/2025 21:06:28
needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_15.302.1211835991
ORA-00280: change 3831902 for thread 1 is in sequence
#15
ORA-00278: log file
'+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_14.287.1211835989' no longer
needed for this recovery
ORA-00279: change 3831906 generated at 09/14/2025 21:06:28
needed for thread 2
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_2_seq_3.303.1211835991
ORA-00280: change 3831906 for thread 2 is in sequence #3
ORA-00278: log file
'+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_2_seq_2.298.1211835989' no longer
needed for this recovery
ORA-00279: change 3831913 generated at 09/14/2025 21:06:31
needed for thread 1
ORA-00289: suggestion :
+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_16.300.1211837103
ORA-00280: change 3831913 for thread 1 is in sequence
#16
ORA-00278: log file
'+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_1_seq_15.302.1211835991' no longer
needed for this recovery
ORA-00279: change 3831916
generated at 09/14/2025 21:06:31 needed for thread 2
ORA-00289: suggestion : +FRA1
ORA-00280: change 3831916 for thread 2 is in sequence
#4
ORA-00278: log
file '+FRA1/ORA12R1/ARCHIVELOG/2025_09_14/thread_2_seq_3.303.1211835991' no
longer needed for this recovery
ORA-00308: cannot open archived log '+FRA1'
ORA-17503: ksfdopn:2 Failed to open file +FRA1
ORA-15045: ASM file name '+FRA1' is not in reference
form
--> 아까 예측 했었던
--> 이제 복구를 시도하면 thread 2의 sequence = 4번이 없다고 나올것으로 예측
가능함
--> thread 2의 sequence = 4번이 없다고 나오고
있음
[ol7ora12r11]<SYS@ORA12R11>$ recover database until cancel using backup
controlfile;
ORA-00279: change 3831916
generated at 09/14/2025 21:06:31 needed for thread 2
ORA-00289: suggestion : +FRA1
ORA-00280: change 3831916 for thread 2 is in sequence
#4
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
CANCEL
Media recovery cancelled.
[ol7ora12r11]<SYS@ORA12R11>$ alter database open;
alter database
open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for
database open
--> 백업 컨트롤 파일로 복구한 것이므로, 또한
리두로그 유실이므로 resetlogs로 오픈해야함
Elapsed: 00:00:00.02
[ol7ora12r11]<SYS@ORA12R11>$ alter database open resetlogs;
Database altered.
Elapsed: 00:00:04.80
[ol7ora12r11]<SYS@ORA12R11>$ select instance_name, status from v$instance;
INSTANCE_NAME
STATUS
------------------------------------------------
----------
ORA12R11
OPEN
1 row selected.
SET LINESIZE 220
PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT
999,999,999,990
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
BLOCK1_OFFSET FOR 999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
, A.BLOCK1_OFFSET
FROM V$DATAFILE A
ORDER BY A.FILE#;
FILE# TS#
NAME
SIZE_MB STATUS
CHECKPOINT_CHANGE# BLOCK1_OFFSET
------ ------
------------------------------------------------------------ ---------------
---------- -------------------- ----------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
SYSTEM
3,831,920 4,294,967,295
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,831,920 4,294,967,295
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,831,920 4,294,967,295
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,831,920 4,294,967,295
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,831,920 4,294,967,295
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,831,920 4,294,967,295
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,831,920 4,294,967,295
9 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.2
1,024.00
ONLINE
3,831,920 4,294,967,295
--> 모든 데이터파일의 scn이 3,831,920까지 가게됨, 당연히 지금 이순간도 current_scn은 증가하고 있음
SET LINESIZE 220
PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT
999,999,999,990
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
FROM V$DATAFILE_HEADER A;
FILE# TS#
NAME
SIZE_MB STATUS CHECKPOINT_CHANGE#
------ ------
------------------------------------------------------------ ---------------
---------- --------------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
ONLINE
3,831,920
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,831,920
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,831,920
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,831,920
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,831,920
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,831,920
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,831,920
9 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.2
1,024.00
ONLINE
3,831,920
SET LINESIZE 220
PAGESIZE 1000 TRIMSPOOL ON
SET NUMFORMAT
999,999,999,999,999
COL
NAME
FOR A20
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
ARCHIVE_CHANGE# FOR 999,999,999,999,999
COL CONTROLFILE_CHANGE# FOR
999,999,999,999,999
SELECT NAME, CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#, CONTROLFILE_CHANGE# FROM V$DATABASE;
NAME
CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
CONTROLFILE_CHANGE#
--------------------
-------------------- -------------------- --------------------
ORA12R1
3,831,920
0
3,833,165
[ol7ora12r11]<SYS@ORA12R11>$ select count(*) as cnt from TUNER.TB_DATAFILE_BACKUP_TEST;
CNT
--------------------
100,000
--> 백업 한 이후로 만들었었던 테이블에 대한 복구가 성공함
1 row selected.
[ol7ora12r11]<SYS@ORA12R11>$ select name from v$tempfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA1/ORA12R1/TEMPFILE/temp.265.1211843921
+DATA1/ORA12R1/TEMPFILE/tuner_temp.280.1211843921
--> 템프
파일도 open 시 재생성됨
[]<SYS@ORA12R12>$ startup
--> 2번
노드도 startup
6. 테스트 종료 후 후속 처리
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ rm -rf
/home/oracle/TEST_BACKUP
RMAN> delete backup;
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 14 20:14:56 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>
COL
OWNER FOR A15
COL SEGMENT_NAME FOR A30
COL PARTITION_NAME FOR A20
COL
SEGMENT_TYPE FOR A20
COL TABLESPACE_NAME FOR
A20
COL
SIZE_MB FOR
999,999,990.00
--추가한 데이터 파일에 존재하는 세그먼트
조회
SELECT A.OWNER
, A.SEGMENT_NAME
, A.PARTITION_NAME
, A.SEGMENT_TYPE
, A.TABLESPACE_NAME
, SUM(A.BYTES)/1024/1024 AS
SIZE_MB
FROM DBA_EXTENTS A
WHERE A.FILE_ID = 9 --> 신규로
추가했었던 datafile 9번에 속해있는 모든 세그먼트를 출력
GROUP
BY A.OWNER
,
A.SEGMENT_NAME
,
A.PARTITION_NAME
,
A.SEGMENT_TYPE
,
A.TABLESPACE_NAME
ORDER BY SIZE_MB
DESC;
OWNER
SEGMENT_NAME
PARTITION_NAME
SEGMENT_TYPE
TABLESPACE_NAME
SIZE_MB
--------------- ------------------------------
-------------------- -------------------- --------------------
---------------
TUNER
TB_DATAFILE_BACKUP_TEST
(NULL)
TABLE
TUNER_DATA1
0.25
COL
FILE#
FOR 99990
COL
TS#
FOR 99990
COL
NAME
FOR A60
COL
SIZE_MB
FOR 999,999,990.00
COL
STATUS
FOR A10
COL CHECKPOINT_CHANGE# FOR
999,999,999,999,999
COL
BLOCK1_OFFSET FOR 999,999,999,999
SELECT A.FILE#
, A.TS#
, A.NAME
, A.BYTES/1024/1024 AS SIZE_MB
, A.STATUS
, A.CHECKPOINT_CHANGE#
, A.BLOCK1_OFFSET
FROM V$DATAFILE A
ORDER BY
A.FILE#;
FILE# TS#
NAME
SIZE_MB STATUS
CHECKPOINT_CHANGE# BLOCK1_OFFSET
------ ------
------------------------------------------------------------ ---------------
---------- -------------------- ----------------
1 0
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00
SYSTEM
3,831,920 4,294,967,295
2 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00
ONLINE
3,831,920 4,294,967,295
3 1
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00
ONLINE
3,831,920 4,294,967,295
4 2
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00
ONLINE
3,831,920 4,294,967,295
5 5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00
ONLINE
3,831,920 4,294,967,295
7 4
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00
ONLINE
3,831,920 4,294,967,295
8 7
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00
ONLINE
3,831,920 4,294,967,295
9 6
+DATA1/ORA12R1/DATAFILE/tuner_data1.2
1,024.00
ONLINE
3,831,920 4,294,967,295
COL TABLESPACE_NAME FOR A20
COL
FILE_ID FOR 99990
COL FILE_NAME FOR
A60
COL
SIZE_MB FOR
999,999,990.00
COL
BLOCKS FOR
999,999,999
COL
STATUS FOR A10
COL AUTOEXTENSIBLE FOR A3
SELECT A.TABLESPACE_NAME
, A.FILE_ID
, A.FILE_NAME
, ROUND(A.BYTES/1024/1024, 2) AS
SIZE_MB
, A.BLOCKS
, A.STATUS
, A.AUTOEXTENSIBLE
FROM DBA_DATA_FILES A
ORDER BY
A.FILE_ID;
TABLESPACE_NAME FILE_ID
FILE_NAME
SIZE_MB BLOCKS
STATUS AUT
--------------------
------- ------------------------------------------------------------
--------------- ------------ ---------- ---
SYSTEM
1
+DATA1/ORA12R1/DATAFILE/system.269.1211839557
830.00 106,240 AVAILABLE YES
TUNER_DATA1
2
+DATA1/ORA12R1/DATAFILE/tuner_data1.268.1211839557
1,536.00 196,608 AVAILABLE YES
SYSAUX
3
+DATA1/ORA12R1/DATAFILE/sysaux.270.1211839583
1,624.00 207,872 AVAILABLE YES
UNDOTBS1
4
+DATA1/ORA12R1/DATAFILE/undotbs1.271.1211839551
955.00 122,240 AVAILABLE YES
UNDOTBS2
5
+DATA1/ORA12R1/DATAFILE/undotbs2.264.1211839551
100.00 12,800 AVAILABLE YES
USERS
7
+DATA1/ORA12R1/DATAFILE/users.260.1211839555
5.00 640 AVAILABLE
YES
TUNER_IDX1
8
+DATA1/ORA12R1/DATAFILE/tuner_idx1.263.1211839555
1,024.00 131,072 AVAILABLE YES
TUNER_DATA1
9
+DATA1/ORA12R1/DATAFILE/tuner_data1.2
1,024.00 131,072 AVAILABLE
NO
-- 해당 테이블을 일단 TUNER_IDX1 테이블 스페이스로 ONLINE MOVE함
[ol7ora12r11]<SYS@ORA12R11>$ ALTER TABLE tuner.tb_datafile_backup_test MOVE TABLESPACE TUNER_IDX1 ONLINE;
Table altered.
Elapsed: 00:00:05.41
-- 그 후 해당 데이터 파일을 날림 (ONLINE MOVE해서 해당
DATAFILE을 비웠기 때문에 날릴수 있는 것임!)
[ol7ora12r11]<SYS@ORA12R11>$ ALTER TABLESPACE TUNER_DATA1 DROP DATAFILE
'+DATA1/ORA12R1/DATAFILE/tuner_data1.2';
Tablespace altered.
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1/DATAFILE
Type Redund
Striped
Time
Sys Block_Size Blocks
Bytes Space Name
DATAFILE UNPROT COARSE SEP 14
23:00:00 Y
8192 207873 1702895616 1711276032
SYSAUX.270.1211839583
DATAFILE UNPROT
COARSE SEP 14 23:00:00
Y 8192
106241 870326272 876609536
SYSTEM.269.1211839557
DATAFILE UNPROT
COARSE SEP 14 23:00:00
Y 8192 196609
1610620928 1619001344 TUNER_DATA1.268.1211839557
DATAFILE UNPROT COARSE SEP 14
23:00:00 Y
8192 131073 1073750016 1082130432
TUNER_IDX1.263.1211839555
DATAFILE UNPROT
COARSE SEP 14 23:00:00
Y 8192 122241
1001398272 1006632960 UNDOTBS1.271.1211839551
DATAFILE UNPROT COARSE SEP 14
23:00:00 Y
8192 12801 104865792 109051904
UNDOTBS2.264.1211839551
DATAFILE UNPROT
COARSE SEP 14 23:00:00
Y
8192 641
5251072 8388608
USERS.260.1211839555
--> 날라감 DATAFILE 9번은 이제
존재하지 않음
-- TUNER_IDX1 테이블 스페이스에 속해있는
해당 테스트용 테이블을 DROP PURGE 시킴
[ol7ora12r11]<SYS@ORA12R11>$ drop table tuner.tb_datafile_backup_test purge;
Table dropped.
Elapsed:
00:00:00.04