OS 환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : RMAN 백업 현황 확인 쿼리
백업현황 확인 쿼리
|
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
|
SQL>
set lines 200 pages 1000
col recid for 999999
col tag for a20
col handle for a50
col il for 999
col e_sec for 999
col e_min for 999
col st for a10
select vbs.recid, backup_type, incremental_level il, vbp.tag, handle, vrbjd.status st, vbp.status s
, to_char(vbs.start_time, 'yyyy/mm/dd hh24:mi:ss') start_time
, to_char(vbs.completion_time, 'yyyy/mm/dd hh24:mi:ss') completion_time
, vbs.elapsed_seconds e_sec, round(vbs.elapsed_seconds/60, 1) e_min
, round(bytes/1024/1024/1024, 2) gb, compressed
from v$backup_set vbs, v$backup_piece vbp, v$rman_backup_job_details vrbjd
where 1=1
and vbs.set_stamp = vbp.set_stamp
and vbs.set_count = vbp.set_count
and vbs.start_time = vrbjd.start_time(+)
order by vbs.recid;
RECID B IL TAG HANDLE ST S START_TIME COMPLETION_TIME E_SEC E_MIN GB COM
------- - ---- -------------------- -------------------------------------------------- ---------- - ------------------- ------------------- ----- ----- ---------- ---
9 D TAG20220626T104953 /app/oracle/rman/ORA11FS_0b3svj2h_1_1_20220626.bk A 2022/06/26 10:49:53 2022/06/26 10:49:54 1 0 .25 NO
10 D TAG20220626T104956 /app/oracle/rman/db_ctl_c-3794755058-20220626-00 A 2022/06/26 10:49:56 2022/06/26 10:49:56 0 0 .01 NO
11 L TAG20220626T105001 /app/oracle/rman/ORA11FS_0d3svj2p_1_1_20220626.bk A 2022/06/26 10:50:01 2022/06/26 10:50:01 0 0 .01 NO
12 D TAG20220626T105002 /app/oracle/rman/db_ctl_c-3794755058-20220626-01 A 2022/06/26 10:50:02 2022/06/26 10:50:02 0 0 .01 NO
13 L TAG20220626T105012 /app/oracle/rman/ORA11FS_0f3svj34_1_1_20220626.bk A 2022/06/26 10:50:12 2022/06/26 10:50:12 0 0 .01 NO
14 D TAG20220626T105013 /app/oracle/rman/ORA11FS_0g3svj35_1_1_20220626.bk A 2022/06/26 10:50:13 2022/06/26 10:50:14 1 0 .25 NO
15 L TAG20220626T105014 /app/oracle/rman/ORA11FS_0h3svj36_1_1_20220626.bk A 2022/06/26 10:50:14 2022/06/26 10:50:14 0 0 0 NO
16 D TAG20220626T105015 /app/oracle/rman/db_ctl_c-3794755058-20220626-02 A 2022/06/26 10:50:15 2022/06/26 10:50:15 0 0 .01 NO
17 D TAG20220626T105019 /app/oracle/rman/ORA11FS_0j3svj3b_1_1_20220626.bk A 2022/06/26 10:50:19 2022/06/26 10:50:20 1 0 .25 NO
18 D TAG20220626T105020 /app/oracle/rman/db_ctl_c-3794755058-20220626-03 A 2022/06/26 10:50:20 2022/06/26 10:50:20 0 0 .01 NO
19 D TAG20220626T105110 /app/oracle/rman/ORA11FS_0l3svj4u_1_1_20220626.bk FAILED A 2022/06/26 10:51:10 2022/06/26 10:51:11 1 0 .25 NO
20 D 0 TAG20220626T105217 /app/oracle/rman/ORA11FS_0m3svj72_1_1_20220626.bk A 2022/06/26 10:52:18 2022/06/26 10:52:18 0 0 .25 NO
21 D TAG20220626T105219 /app/oracle/rman/db_ctl_c-3794755058-20220626-04 A 2022/06/26 10:52:19 2022/06/26 10:52:19 0 0 .01 NO
22 I 1 TAG20220626T105240 /app/oracle/rman/ORA11FS_0o3svj7o_1_1_20220626.bk COMPLETED A 2022/06/26 10:52:40 2022/06/26 10:52:46 6 0 0 NO
23 D TAG20220626T105247 /app/oracle/rman/db_ctl_c-3794755058-20220626-05 A 2022/06/26 10:52:47 2022/06/26 10:52:47 0 0 .01 NO
24 I 1 TAG20220626T105519 /app/oracle/rman/ORA11FS_0q3svjcn_1_1_20220626.bk COMPLETED A 2022/06/26 10:55:19 2022/06/26 10:55:21 2 0 0 NO
25 D TAG20220626T105522 /app/oracle/rman/db_ctl_c-3794755058-20220626-06 A 2022/06/26 10:55:22 2022/06/26 10:55:22 0 0 .01 NO
17 rows selected.
|
주요 컬럼 설명 :
v$backup_set.backup_type : 백업에 포함된 파일 유형
- L : 백업에 보관된 리두 로그가 포함된 경우
- D : 데이터 파일 전체 백업인 경우
- I : 증분 백업인 경우
status (st) : v$rman_backup_job_details 뷰에서 가져온 백업 작업의 상태이며, COMPLETED, FAILED 등으로 나타남
status (s) : v$backup_piece 뷰에서 가져온 해당 백업 피스의 상태이며, A(사용 가능), D(삭제됨), X(만료됨)이 있음
incremental_level (il) : 인크리멘탈 백업의 레벨을 의미하며, 0 또는 1의 값을 가짐
tag : RMAN 백업 실행 시 명시적으로 지정하거나 자동 부여된 백업 태그임
handle : 실제 백업 파일의 경로 및 이름임
elapsed_seconds (e_sec) : 백업 작업에 소요된 전체 시간(초 단위)임
elapsed_minute (e_min) : 백업 작업 소요 시간을 분 단위로 환산한 값임
gb : 백업 파일의 크기를 GB 단위로 환산한 값임
compressed : 해당 백업이 압축되었는지를 나타내며, YES 또는 NO 값을 가짐
v$rman_backup_job_details에서 input, output bytes도 확인
|
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
|
SQL>
set lines 200 pages 1000
col KEY for 9,999
col INPUT_TYPE for a12
col STATUS for a10
col JOB_TIME for a8
col RATIO for 999
col INPUT_SEC for a10
col OUTPUT_SEC for a10
select session_key key,
input_type,
status,
to_char(start_time,'yyyy/mm/dd hh24:mi:ss') start_time,
to_char(end_time,'yyyy/mm/dd hh24:mi:ss') end_time,
time_taken_display job_time,
optimized,
round(compression_ratio, 0) ratio,
input_bytes_per_sec_display input_sec,
output_bytes_per_sec_display output_sec
from v$rman_backup_job_details
order by 1;
KEY INPUT_TYPE STATUS START_TIME END_TIME JOB_TIME OPT RATIO INPUT_SEC OUTPUT_SEC
------ ------------ ---------- ------------------- ------------------- -------- --- ----- ---------- ----------
13 DB FULL FAILED 2022/06/26 10:44:31 2022/06/26 10:45:09 00:00:38 NO 2 40.70M 20.95M
25 DB FULL FAILED 2022/06/26 10:49:41 2022/06/26 10:49:42 00:00:01 NO 1 0.00K 0.00K
27 DB FULL FAILED 2022/06/26 10:49:52 2022/06/26 10:50:21 00:00:29 NO 2 53.65M 27.78M
38 DB FULL FAILED 2022/06/26 10:51:10 2022/06/26 10:51:11 00:00:01 NO 2 505.00M 255.02M
40 DB INCR COMPLETED 2022/06/26 10:52:17 2022/06/26 10:52:20 00:00:03 NO 2 171.43M 88.13M
43 DB INCR COMPLETED 2022/06/26 10:52:40 2022/06/26 10:52:48 00:00:08 NO 411 513.13M 1.25M
46 DB INCR COMPLETED 2022/06/26 10:55:19 2022/06/26 10:55:23 00:00:04 NO 411 1.00G 2.49M
7 rows selected.
|
rman에서 summary의 tag를 이용해 확인
|
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
|
$ rman target /
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
9 B F A DISK 2022-06-26 10:49:54 1 1 NO TAG20220626T104953
10 B F A DISK 2022-06-26 10:49:56 1 1 NO TAG20220626T104956
11 B A A DISK 2022-06-26 10:50:01 1 1 NO TAG20220626T105001
12 B F A DISK 2022-06-26 10:50:02 1 1 NO TAG20220626T105002
13 B A A DISK 2022-06-26 10:50:12 1 1 NO TAG20220626T105012
14 B F A DISK 2022-06-26 10:50:14 1 1 NO TAG20220626T105013
15 B A A DISK 2022-06-26 10:50:14 1 1 NO TAG20220626T105014
16 B F A DISK 2022-06-26 10:50:15 1 1 NO TAG20220626T105015
17 B F A DISK 2022-06-26 10:50:20 1 1 NO TAG20220626T105019
18 B F A DISK 2022-06-26 10:50:20 1 1 NO TAG20220626T105020
19 B F A DISK 2022-06-26 10:51:11 1 1 NO TAG20220626T105110
20 B 0 A DISK 2022-06-26 10:52:18 1 1 NO TAG20220626T105217
21 B F A DISK 2022-06-26 10:52:19 1 1 NO TAG20220626T105219
22 B 1 A DISK 2022-06-26 10:52:46 1 1 NO TAG20220626T105240
23 B F A DISK 2022-06-26 10:52:47 1 1 NO TAG20220626T105247
24 B 1 A DISK 2022-06-26 10:55:21 1 1 NO TAG20220626T105519
25 B F A DISK 2022-06-26 10:55:22 1 1 NO TAG20220626T105522
RMAN> list backup tag=TAG20220626T105519;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24 Incr 1 632.00K DISK 00:00:02 2022-06-26 10:55:21
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20220626T105519
Piece Name: /app/oracle/rman/ORA11FS_0q3svjcn_1_1_20220626.bk
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 1 Incr 278910 2022-06-26 10:55:19 /app/oracle/oradata/ORA11FS/system01.dbf
2 1 Incr 278910 2022-06-26 10:55:19 /app/oracle/oradata/ORA11FS/sysaux01.dbf
3 1 Incr 278910 2022-06-26 10:55:19 /app/oracle/oradata/ORA11FS/undotbs01.dbf
4 1 Incr 278910 2022-06-26 10:55:19 /app/oracle/oradata/ORA11FS/users01.dbf
|
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| default temporary tablespace 지정(database, user) (0) | 2018.04.13 |
|---|---|
| HASH_VALUE로 쿼리 전체 뽑기 sql (0) | 2018.04.05 |
| DBA가 가지고 있어야 할 10가지 SQL (0) | 2018.02.22 |
| TABLE에 걸려 있는 INDEX 찾기 (0) | 2018.02.22 |
| 오라클 EMP, DEPT table 생성 스크립트 (0) | 2018.02.20 |