OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c rman 일부 테이블스페이스 백업 후 복구 시나리오
본문에서는 rman으로 일부 테이블스페이스만 백업한 뒤 복구서버에서 복구하는 방법을 설명함
테스트
백업
복구 시도1
복구 시도2
테스트
백업
기존 백업 및 아카이브 삭제
|
1
2
3
|
$ rman target /
RMAN> delete noprompt backup;
RMAN> delete noprompt archivelog all;
|
rman 설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> show all;
show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORA19DBFS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oraimsi/rman/db_ctl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oraimsi/rman/%d_%U_%T.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/19c/dbs/snapcf_ORA19DBFS.f'; # default
|
현재 테이블스페이스 목록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, online_status from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible, status, null from dba_temp_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT STATUS ONLINE_
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- --- --------- -------
SYSTEM 1 /app/oracle/oradata/ORA19DBFS/system01.dbf 5.65 32 YES AVAILABLE SYSTEM
TEMP 1 /app/oracle/oradata/ORA19DBFS/temp01.dbf 1 32 YES ONLINE
SYSAUX 2 /app/oracle/oradata/ORA19DBFS/sysaux01.dbf 2.75 32 YES AVAILABLE ONLINE
UNDOTBS1 3 /app/oracle/oradata/ORA19DBFS/undotbs01.dbf 4.3 32 YES AVAILABLE ONLINE
USERS 4 /app/oracle/oradata/ORA19DBFS/users01.dbf 8 32 YES AVAILABLE ONLINE
SYSAUX 5 /oraimsi/oradata/ORA19DBFS/sysaux02.dbf 1 0 NO AVAILABLE ONLINE
SYSTEM 6 /oraimsi/oradata/ORA19DBFS/system02.dbf 7 0 NO AVAILABLE SYSTEM
IMSITS 7 /oraimsi/oradata/ORA19DBFS/imsits01.dbf .1 0 NO AVAILABLE ONLINE
8 rows selected.
|
rman으로 imsits 제외하고 나머지 테이블스페이스만 백업
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
RMAN> backup tablespace system, sysaux, users, undotbs1;
backup tablespace system, sysaux, users, undotbs1;
Starting backup at 2025-11-23 09:34:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/app/oracle/oradata/ORA19DBFS/users01.dbf
input datafile file number=00006 name=/oraimsi/oradata/ORA19DBFS/system02.dbf
input datafile file number=00005 name=/oraimsi/oradata/ORA19DBFS/sysaux02.dbf
channel ORA_DISK_1: starting piece 1 at 2025-11-23 09:34:26
channel ORA_DISK_1: finished piece 1 at 2025-11-23 09:34:41
piece handle=/oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk tag=TAG20251123T093426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/ORA19DBFS/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2025-11-23 09:34:41
channel ORA_DISK_1: finished piece 1 at 2025-11-23 09:34:56
piece handle=/oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk tag=TAG20251123T093426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/app/oracle/oradata/ORA19DBFS/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2025-11-23 09:34:56
channel ORA_DISK_1: finished piece 1 at 2025-11-23 09:34:59
piece handle=/oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk tag=TAG20251123T093426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/app/oracle/oradata/ORA19DBFS/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 2025-11-23 09:34:59
channel ORA_DISK_1: finished piece 1 at 2025-11-23 09:35:14
piece handle=/oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk tag=TAG20251123T093426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2025-11-23 09:35:14
Starting Control File and SPFILE Autobackup at 2025-11-23 09:35:14
piece handle=/oraimsi/rman/db_ctl_c-1166340695-20251123-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2025-11-23 09:35:16
|
imsits를 제외한 모든 테이블스페이스가 백업됨
로그스위치 수행
|
1
2
3
4
|
SQL>
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
|
컨트롤파일 수동 백업
|
1
2
3
|
SQL> alter database backup controlfile to '/home/oracle/ctl.ctl';
Database altered.
|
컨트롤파일 복구서버로 전송
|
1
2
|
$ scp /home/oracle/ctl.ctl 10.70.101.164:/home/oracle/
ctl.ctl 100% 10MB 130.3MB/s 00:00
|
rman 백업 파일 복구서버로 전송
|
1
2
3
4
5
6
|
$ cd /oraimsi/rman
$ scp ./*.bk 10.70.101.164:/oradata1/rman
ORA19DBF_2049is12_64_1_1_20251123.bk 100% 4746MB 272.4MB/s 00:17
ORA19DBF_2149is1h_65_1_1_20251123.bk 100% 4456MB 281.5MB/s 00:15
ORA19DBF_2249is20_66_1_1_20251123.bk 100% 5256KB 45.3MB/s 00:00
ORA19DBF_2349is23_67_1_1_20251123.bk 100% 1997MB 300.3MB/s 00:06
|
아카이브로그 파일 복구서버로 전송
|
1
2
3
4
5
|
$ cd /oraimsi
$ scp ./*.arc 10.70.101.164:/oradata1/arch
ORA19DBFS_1_222_1202553559.arc 100% 169MB 148.9MB/s 00:01
ORA19DBFS_1_223_1202553559.arc 100% 1024 60.9KB/s 00:00
ORA19DBFS_1_224_1202553559.arc 100% 1536 1.0MB/s 00:00
|
파라미터파일 복구서버로 전송
|
1
2
|
$ scp spfileORA19DBFS.ora 10.70.101.164:/app/oracle/product/19c/dbs/
spfileORA19DBFS.ora 100% 3584 533.3KB/s 00:00
|
복구서버에서 nomount 기동
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2499802208 bytes
Fixed Size 8942688 bytes
Variable Size 973078528 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7831552 bytes
|
rman으로 컨트롤파일 restore 및 mount
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
RMAN> restore controlfile from '/home/oracle/ctl.ctl';
restore controlfile from '/home/oracle/ctl.ctl';
Starting restore at 2025-11-23 09:44:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/app/oracle/oradata/ORA19DBFS/control01.ctl
output file name=/app/oracle/oradata/ORA19DBFS/control02.ctl
Finished restore at 2025-11-23 09:44:39
RMAN> alter database mount;
alter database mount;
released channel: ORA_DISK_1
Statement processed
|
백업파일, 아카이브로그 경로 카탈로깅
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
RMAN> catalog start with '/oradata1/rman' noprompt;
catalog start with '/oradata1/rman' noprompt;
searching for all files that match the pattern /oradata1/rman
List of Files Unknown to the Database
=====================================
File Name: /oradata1/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata1/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
File Name: /oradata1/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
RMAN> catalog start with '/oradata1/arch' noprompt;
catalog start with '/oradata1/arch' noprompt;
searching for all files that match the pattern /oradata1/arch
List of Files Unknown to the Database
=====================================
File Name: /oradata1/arch/ORA19DBFS_1_222_1202553559.arc
File Name: /oradata1/arch/ORA19DBFS_1_223_1202553559.arc
File Name: /oradata1/arch/ORA19DBFS_1_224_1202553559.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata1/arch/ORA19DBFS_1_222_1202553559.arc
File Name: /oradata1/arch/ORA19DBFS_1_223_1202553559.arc
File Name: /oradata1/arch/ORA19DBFS_1_224_1202553559.arc
|
백업 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
RMAN> list backup tag=TAG20251123T093426;
list backup tag=TAG20251123T093426;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
63 Full 4.63G
List of Datafiles in backup set 63
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
4 Full 21710174 2025-11-23 09:34:26 NO /app/oracle/oradata/ORA19DBFS/users01.dbf
5 Full 21710174 2025-11-23 09:34:26 NO /oraimsi/oradata/ORA19DBFS/sysaux02.dbf
6 Full 21710174 2025-11-23 09:34:26 NO /oraimsi/oradata/ORA19DBFS/system02.dbf
Backup Set Copy #1 of backup set 63
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:14 2025-11-23 09:34:40 NO TAG20251123T093426
List of Backup Pieces for backup set 63 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
63 1 AVAILABLE /oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
Backup Set Copy #2 of backup set 63
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:14 2025-11-23 09:34:40 NO TAG20251123T093426
List of Backup Pieces for backup set 63 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
68 1 AVAILABLE /oradata1/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
BS Key Type LV Size
------- ---- -- ----------
64 Full 4.35G
List of Datafiles in backup set 64
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 21710181 2025-11-23 09:34:41 NO /app/oracle/oradata/ORA19DBFS/system01.dbf
Backup Set Copy #1 of backup set 64
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:12 2025-11-23 09:34:54 NO TAG20251123T093426
List of Backup Pieces for backup set 64 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
64 1 AVAILABLE /oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
Backup Set Copy #2 of backup set 64
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:12 2025-11-23 09:34:53 NO TAG20251123T093426
List of Backup Pieces for backup set 64 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
69 1 AVAILABLE /oradata1/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
BS Key Type LV Size
------- ---- -- ----------
65 Full 5.13M
List of Datafiles in backup set 65
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
3 Full 21710188 2025-11-23 09:34:56 NO /app/oracle/oradata/ORA19DBFS/undotbs01.dbf
Backup Set Copy #1 of backup set 65
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:03 2025-11-23 09:34:59 NO TAG20251123T093426
List of Backup Pieces for backup set 65 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
65 1 AVAILABLE /oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
Backup Set Copy #2 of backup set 65
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:03 2025-11-23 09:34:59 NO TAG20251123T093426
List of Backup Pieces for backup set 65 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
70 1 AVAILABLE /oradata1/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
BS Key Type LV Size
------- ---- -- ----------
66 Full 1.95G
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
2 Full 21710191 2025-11-23 09:34:59 NO /app/oracle/oradata/ORA19DBFS/sysaux01.dbf
Backup Set Copy #1 of backup set 66
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:07 2025-11-23 09:35:07 NO TAG20251123T093426
List of Backup Pieces for backup set 66 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
66 1 AVAILABLE /oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
Backup Set Copy #2 of backup set 66
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
DISK 00:00:07 2025-11-23 09:35:06 NO TAG20251123T093426
List of Backup Pieces for backup set 66 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
71 1 AVAILABLE /oradata1/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
|
imsits를 제외한 모든 테이블스페이스가 백업되어 있음
복구 시도1
restore database
imsits 테이블스페이스(7번 데이터파일)은 복구하지 않음
운영서버에 있던 /oraimsi 디렉토리는 복구서버에는 존재하지 않기 때문에 5, 6번 데이터 파일 위치를 set newname으로 변경해줌
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
RMAN>
run {
set newname for datafile 5 to '/oradata1/ORA19DBFS/sysaux02.dbf';
set newname for datafile 6 to '/oradata1/ORA19DBFS/system02.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-11-23 09:45:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
creating datafile file number=7 name=/oraimsi/oradata/ORA19DBFS/imsits01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/23/2025 09:45:36
ORA-01119: error in creating database file '/oraimsi/oradata/ORA19DBFS/imsits01.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
RMAN-06956: create datafile failed; retry after removing /oraimsi/oradata/ORA19DBFS/imsits01.dbf from OS
RMAN Client Diagnostic Trace file : /app/oracle/diag/clients/user_oracle/RMAN_987707491_110/trace/ora_rman_71932_0.trc
|
imsits 때문에 에러가 발생함
imsits도 set newname에 넣어서 다시 restore
참고로 imsits는 백업되지 않았음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
RMAN>
run {
set newname for datafile 5 to '/oradata1/ORA19DBFS/sysaux02.dbf';
set newname for datafile 6 to '/oradata1/ORA19DBFS/system02.dbf';
set newname for datafile 7 to '/oradata1/ORA19DBFS/imsits01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-11-23 09:45:51
using channel ORA_DISK_1
#실제로는 백업하지 않았지만 깡통으로 imsits 데이터파일을 생성함
creating datafile file number=7 name=/oradata1/ORA19DBFS/imsits01.dbf
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 /app/oracle/oradata/ORA19DBFS/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata1/ORA19DBFS/sysaux02.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata1/ORA19DBFS/system02.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
#/oraimsi/rman(운영서버 rman 백업경로) 경로를 보고 rman 백업본을 찾다가 실패함
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
#이후 새로 카탈로깅한 /oradata1/rman 경로의 백업본으로 restore를 수행함
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2049is12_64_1_1_20251123.bk tag=TAG20251123T093426
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 00001 to /app/oracle/oradata/ORA19DBFS/system01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 /app/oracle/oradata/ORA19DBFS/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2249is20_66_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00002 to /app/oracle/oradata/ORA19DBFS/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2349is23_67_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2025-11-23 09:46:45
#데이터파일 5,6,7이 새로운 경로로 switch됨
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=1218016006 file name=/oradata1/ORA19DBFS/sysaux02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=1218016006 file name=/oradata1/ORA19DBFS/system02.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=1218016006 file name=/oradata1/ORA19DBFS/imsits01.dbf
|
복구가 완료됨
마지막 아카이브로그 sequence 확인
|
1
2
3
4
5
6
7
|
SQL>
select max(sequence#) from v$archived_log
where archived = 'YES';
MAX(SEQUENCE#)
--------------
224
|
마지막 아카이브로그까지 recover 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
RMAN>
run {
set until sequence 224;
recover database;
}
run {
2> set until sequence 224;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 2025-11-23 10:00:04
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 222 is already on disk as file /oradata1/arch/ORA19DBFS_1_222_1202553559.arc
archived log for thread 1 with sequence 223 is already on disk as file /oradata1/arch/ORA19DBFS_1_223_1202553559.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/ORA19DBFS/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2025 10:00:04
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 221 and starting SCN of 21408878 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 220 and starting SCN of 21408871 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 219 and starting SCN of 21408868 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 218 and starting SCN of 21407880 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 217 and starting SCN of 21407877 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 216 and starting SCN of 21407874 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 215 and starting SCN of 21406769 found to restore
|
warning 메세지에 recover가 성공했고 open resetlogs로 기동하라고 나옴
하지만 recover 자체는 실패함, 시퀀스 221과 SCN 21408878을 사용하여 복원할 수 있는 스레드 1에 대한 아카이브 로그의 백업이 없다고 나옴
datafile header 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>> select file#, recover, fuzzy, checkpoint_change# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
1 NO 21710181
2 NO 21710191
3 NO 21710188
4 NO 21710174
5 NO 21710174
6 NO 21710174
7 NO 21407564
7 rows selected
|
CHECKPOINT_CHANGE#이 맞지 않음
일단 기동 시도
|
1
2
3
4
5
6
|
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/app/oracle/oradata/ORA19DBFS/system01.dbf'
|
당연하게도 기동이 되지 않음
이유 : 백업은 imsits 테이블스페이스를 제외하고 백업했지만, 복구는 imsits까지 모두 restore를 하였기때문에
imsits의 recover에 필요한 아카이브를 더 찾고 있어 실패함, 참고로 여기서 imsits 데이터파일을 offline 해도 결과는 동일함
다음 테스트를 위해 restore된 데이터파일, 컨트롤파일 삭제
|
1
2
|
$ rm -rf /oradata1/ORA19DBFS/*
$ rm -rf /app/oracle/oradata/ORA19DBFS/*.dbf
|
db 종료 및 mount 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> startup force mount
ORACLE instance started.
Total System Global Area 2499802208 bytes
Fixed Size 8942688 bytes
Variable Size 973078528 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7831552 bytes
Database mounted.
|
복구 시도2
restore 시 skip tablespace 옵션 사용
여기서는 imsits 데이터파일 7번을 set newname 해주지 않아도 됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
RMAN>
run {
set newname for datafile 5 to '/oradata1/ORA19DBFS/sysaux02.dbf';
set newname for datafile 6 to '/oradata1/ORA19DBFS/system02.dbf';
restore database skip tablespace imsits;
switch datafile all;
switch tempfile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2025-11-23 10:54:33
using channel ORA_DISK_1
#복구 시도1과는 다르게 imsits의 데이터파일을 깡통으로 생성하지 않음
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 /app/oracle/oradata/ORA19DBFS/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata1/ORA19DBFS/sysaux02.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata1/ORA19DBFS/system02.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
#/oraimsi/rman(운영서버 rman 백업경로) 경로를 보고 rman 백업본을 찾다가 실패함
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2049is12_64_1_1_20251123.bk
#이후 새로 카탈로깅한 /oradata1/rman 경로의 백업본으로 restore를 수행함
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2049is12_64_1_1_20251123.bk tag=TAG20251123T093426
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 00001 to /app/oracle/oradata/ORA19DBFS/system01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2149is1h_65_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 /app/oracle/oradata/ORA19DBFS/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2249is20_66_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2249is20_66_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00002 to /app/oracle/oradata/ORA19DBFS/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
channel ORA_DISK_1: errors found reading piece handle=/oraimsi/rman/ORA19DBF_2349is23_67_1_1_20251123.bk
channel ORA_DISK_1: failover to piece handle=/oradata1/rman/ORA19DBF_2349is23_67_1_1_20251123.bk tag=TAG20251123T093426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2025-11-23 10:55:27
#데이터파일 5,6이 새로운 경로로 switch됨
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=1218020127 file name=/oradata1/ORA19DBFS/sysaux02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=1218020127 file name=/oradata1/ORA19DBFS/system02.dbf
|
복구가 완료됨
참고로 restore skip tablespace 구문에 forever라는 구문을 사용할수 있는데
이는 이전버전과 호환용으로만 놔둔 옵션이라고 함
원문 : Specifying the FOREVER keyword does not change the behavior of SKIP.
The FOREVER keyword exists solely to maintain compatible syntax between RESTORE SKIP FOREVER and RECOVER SKIP FOREVER.
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/RESTORE.html
마지막 아카이브로그 sequence 확인
|
1
2
3
4
5
6
7
|
SQL>
select max(sequence#) from v$archived_log
where archived = 'YES';
MAX(SEQUENCE#)
--------------
224
|
마지막 아카이브로그까지 recover 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
RMAN>
run {
set until sequence 224;
recover database;
}
executing command: SET until clause
Starting recover at 2025-11-23 11:00:15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2025 11:00:15
RMAN-06094: datafile 7 must be restored
|
datafile 7이 없어서 에러가 남
recover도 마찬가지로 skip tablespace 옵션을 사용해야함
recover 재시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN>
run {
set until sequence 224;
recover database skip tablespace imsits;
}
executing command: SET until clause
Starting recover at 2025-11-23 11:01:07
using channel ORA_DISK_1
Executing: alter database datafile 7 offline
starting media recovery
archived log for thread 1 with sequence 222 is already on disk as file /oradata1/arch/ORA19DBFS_1_222_1202553559.arc
archived log for thread 1 with sequence 223 is already on disk as file /oradata1/arch/ORA19DBFS_1_223_1202553559.arc
archived log file name=/oradata1/arch/ORA19DBFS_1_222_1202553559.arc thread=1 sequence=222
archived log file name=/oradata1/arch/ORA19DBFS_1_223_1202553559.arc thread=1 sequence=223
media recovery complete, elapsed time: 00:00:00
Finished recover at 2025-11-23 11:01:08
|
정상적으로 recover가 완료됨
참고로 recover skip tablespace 구문에 forever라는 구문을 사용할수 있는데
불완전 복구를 수행하거나 resetlogs 옵션으로 db를 오픈한 후 지정된 테이블스페이스를 삭제할 계획이라면 skip forever tablespace를 사용해야한다고함
하지만 본문내용처럼 forever를 사용하지 않아도 큰문제는 생기지 않는듯함
원문 : Takes the data files offline with the DROP option (see Example 3-3).
Use SKIP FOREVER TABLESPACE when you intend to drop the specified tablespaces after opening the database with the RESETLOGS option.
Note: If you perform incomplete recovery, then SKIP requires the FOREVER option.
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/RECOVER.html
redo 경로 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB SEQUENCE#
---------- ---------- ------------------------------------------------------------ --- ---------------- ---------- ----------
1 1 /app/oracle/oradata/ORA19DBFS/redo01.log YES INACTIVE 1024 224
1 2 /app/oracle/oradata/ORA19DBFS/redo02.log NO CURRENT 1024 225
1 3 /app/oracle/oradata/ORA19DBFS/redo03.log YES INACTIVE 1024 223
|
필요시 redo 경로를 변경
(만약 동일 서버에서 이 작업을 진행하는 경우 기존 redo를 덮어쓸수 있기때문에 resetlogs 기동 전 mount 상태에서 redo 경로를 변경해줘야함)
본문에서는 서버가 달라졌지만 해당 파일시스템은 존재하기 때문에 변경하지 않음
|
1
2
3
|
SQL> alter database rename file '이전경로' to '신규경로';
SQL> alter database rename file '이전경로' to '신규경로';
SQL> alter database rename file '이전경로' to '신규경로';
|
datafile header 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>> select file#, recover, fuzzy, checkpoint_change# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
1 NO 21710429
2 NO 21710429
3 NO 21710429
4 NO 21710429
5 NO 21710429
6 NO 21710429
7 0
7 rows selected.
|
모든 데이터파일의 checkpoint_change#이 맞음(datafile 7(imsits)는 0으로 표시됨)
db 기동
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
정상적으로 기동됨
참고1. alert log에 imsits에 대한 에러로그가 남음
alert log 확인
|
1
2
3
4
5
6
7
8
|
$ tail -300f /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/alert_ORA19DBFS.log
2025-11-23T15:04:49.034903+09:00
Errors in file /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_mz04_76877.trc:
ORA-01110: data file 7: '/oraimsi/oradata/ORA19DBFS/imsits01.dbf'
ORA-01565: error in identifying file '/oraimsi/oradata/ORA19DBFS/imsits01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
|
조치 : 해당 ts 삭제
|
1
2
3
|
SQL> drop tablespace IMSITS including contents and datafiles
Tablespace dropped.
|
삭제시 alert log
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ tail -300f /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/alert_ORA19DBFS.log
2025-11-23T15:21:02.990872+09:00
drop tablespace IMSITS including contents and datafiles
2025-11-23T15:21:06.880270+09:00
Errors in file /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_77032.trc:
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/oraimsi/oradata/ORA19DBFS/imsits01.dbf'
ORA-01565: error in identifying file '/oraimsi/oradata/ORA19DBFS/imsits01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2025-11-23T15:21:06.880551+09:00
Errors in file /app/oracle/diag/rdbms/ora19dbfs/ORA19DBFS/trace/ORA19DBFS_ora_77032.trc:
ORA-01259: unable to delete datafile /oraimsi/oradata/ORA19DBFS/imsits01.dbf
2025-11-23T15:21:06.887737+09:00
Tablespace dropped: IMSITS ts# 5
Completed: drop tablespace IMSITS including contents and datafiles
|
결론 :
테이블스페이스 단위로 백업을 받더라고 정상적으로 복구가 가능함
하지만 restore, recover 시 skip tablespace 옵션을 사용해줘야 정상적으로 기동이됨
만약 redo 경로가 변경이 필요한 경우 alter database rename 명령으로 redo 경로를 변경해줘야함
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/RECOVER.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/RESTORE.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/RECOVER.html
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c rman 증분 백업 및 복구 시나리오 (2) | 2025.11.27 |
|---|---|
| 오라클 11g to 19c 업그레이드 시 restore point 이용 복원 테스트(compatible 변경) (0) | 2025.10.10 |
| 오라클 11g to 19c 업그레이드 시 restore point 이용 복원 테스트 (0) | 2025.10.08 |
| 오라클 19c 핫백업시 redo 발생량 차이 확인 (0) | 2025.10.03 |
| 오라클 19c rman 커맨드 빠른 참조 (0) | 2025.07.21 |