내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c rman 백업 압축 알고리즘별 압축률 비교
오라클 19c 환경에서 rman 을 이용한 백업시 압축 옵션을 사용할 수 있음
압축 옵션은 총 4가지가 존재함
BASIC은 기본 압축이라 무료이고 나머지 3가지 압축은 고급 압축으로 들어가서 별도의 라이센스가 필요함
BASIC : 기본 압축 옵션
LOW : 백업 처리량에 미치는 영향이 가장 적음
MEDIUM : 압축률과 속도의 좋은 조합이라 대부분의 환경에 권장됨
HIGH : 제한 요소가 네트워크 속도인 느린 네트워크를 통한 백업에 적합함
rman 백업 알고리즘 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col algorithm_name for a10
col algorithm_description a 50
select ALGORITHM_ID, algorithm_name, algorithm_description, ALGORITHM_COMPATIBILITY
from v$rman_compression_algorithm;
ALGORITHM_ID ALGORITHM_ ALGORITHM_DESCRIPTION ALGORITHM_COMPATIB
------------ ---------- ---------------------------------------------------------------- ------------------
0 BZIP2 good compression ratio 9.2.0.0.0
1 BASIC good compression ratio 9.2.0.0.0
2 LOW maximum possible compression speed 11.2.0.0.0
3 ZLIB balance between speed and compression ratio 11.0.0.0.0
4 MEDIUM balance between speed and compression ratio 11.0.0.0.0
5 HIGH maximum possible compression ratio 11.2.0.0.0
6 ZSTD zstd - balance between speed and compression ratio 19.0.0.0.0
7 MEDIUM zstd - balance between speed and compression ratio 19.0.0.0.0
8 rows selected.
|
8개가 존재함
공식 문서에는 BASIC, LOW, MEDIUM, HIGH만 존재하는데 이 뷰에는 ZSTD라는 옵션도 존재함
검색해보니 ZSTD도 알고리즘의 한 종류라고 하는데
테스트 시 같이 진행해볼 예정임
테스트
샘플 ts 생성
|
1
2
3
|
SQL>
drop tablespace rmancomp including contents and datafiles;
create tablespace rmancomp datafile '/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf' size 6g;
|
샘플 유저 생성 및 권한 부여
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop user rmanuser cascade;
create user rmanuser identified by rmanuser account unlock default tablespace rmancomp quota unlimited on rmancomp;
User created.
SQL> grant resource, connect, dba to rmanuser;
Grant succeeded.
|
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
conn rmanuser/rmanuser
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
Table created.
|
샘플 데이터 삽입(여러번 수행)
|
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
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..100000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
데이터 추가 생성 필요시 itas 실행(반복)
|
1
2
3
4
5
6
7
|
SQL> insert /*+ append parallel */ into sample_t select /*+ parallel */ * from sample_t;
1000000 rows created.
SQL> commit;
Commit complete.
|
테이블 용량 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col segment_name for a10
select segment_name, round(bytes/1024/1024, 2) mb from dba_segments
where segment_name = 'SAMPLE_T'
and tablespace_name = 'RMANCOMP';
SEGMENT_NA MB
---------- ----------
SAMPLE_T 2944
|
db 전체 용량 확인(segment 기준)
|
1
2
3
4
5
6
|
SQL> select round(sum(bytes/1024/1024), 2) segmb, round(sum(bytes/1024/1024/1024), 2) seggb
from dba_segments;
SEGMB SEGGB
---------- ----------
3816.63 3.73
|
rman 백업 전 테스트를 정확하게 보기위해 기존 백업 및 아카이브 로그 삭제
|
1
2
3
4
|
$ rman target /
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정
|
1
2
3
4
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_nocomp.bk';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F';
--CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
|
BASIC 압축 옵션이 기본값이라 변경하지 않음
rman 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 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 '/ORA19/app/oracle/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 '/ORA19/app/oracle/rman/%d_%U_%T_bck_nocomp.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 '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
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
43
44
45
46
47
|
$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
RMAN> backup as backupset database plus archivelog;
Starting backup at 2024-01-22 14:23:20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=5 STAMP=1158934988
input archived log thread=1 sequence=12 RECID=6 STAMP=1158935000
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:23:21
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:23:22
piece handle=/ORA19/app/oracle/rman/ORACLE19_022h7sep_1_1_20240122_bck_nocomp.bk tag=TAG20240122T142321 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:23:22
Starting backup at 2024-01-22 14:23:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:23:22
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:23:37
piece handle=/ORA19/app/oracle/rman/ORACLE19_032h7seq_1_1_20240122_bck_nocomp.bk tag=TAG20240122T142322 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2024-01-22 14:23:37
Starting backup at 2024-01-22 14:23:37
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=7 STAMP=1158935017
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:23:38
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:23:39
piece handle=/ORA19/app/oracle/rman/ORACLE19_042h7sfa_1_1_20240122_bck_nocomp.bk tag=TAG20240122T142338 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:23:39
Starting Control File and SPFILE Autobackup at 2024-01-22 14:23:39
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:23:40
|
압축을 하지 않았을때는 백업이 20초 소요됨
용량 확인
|
1
2
3
4
5
6
7
|
$ cd /ORA19/app/oracle/rman
$ ls -lh
합계 3.4G
-rw-r----- 1 oracle oinstall 6.0K 1월 22 14:23 ORACLE19_022h7sep_1_1_20240122_bck_nocomp.bk
-rw-r----- 1 oracle oinstall 3.4G 1월 22 14:23 ORACLE19_032h7seq_1_1_20240122_bck_nocomp.bk
-rw-r----- 1 oracle oinstall 4.0K 1월 22 14:23 ORACLE19_042h7sfa_1_1_20240122_bck_nocomp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:23 db_ctl_c-3209222764-20240122-00
|
3.4G임
기존 백업 삭제 및 기존 아카이브 로그 삭제
|
1
2
3
|
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정
|
1
|
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_basic_comp.bk';
|
rman 백업(BASIC 압축)
|
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> backup as compressed backupset database plus archivelog;
Starting backup at 2024-01-22 14:24:41
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=8 STAMP=1158935081
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:24:42
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:24:43
piece handle=/ORA19/app/oracle/rman/ORACLE19_062h7sha_1_1_20240122_bck_basic_comp.bk tag=TAG20240122T142442 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:24:43
Starting backup at 2024-01-22 14:24:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:24:43
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:26:08
piece handle=/ORA19/app/oracle/rman/ORACLE19_072h7shb_1_1_20240122_bck_basic_comp.bk tag=TAG20240122T142443 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2024-01-22 14:26:08
Starting backup at 2024-01-22 14:26:08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=9 STAMP=1158935168
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:26:09
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:26:10
piece handle=/ORA19/app/oracle/rman/ORACLE19_082h7sk1_1_1_20240122_bck_basic_comp.bk tag=TAG20240122T142609 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:26:10
Starting Control File and SPFILE Autobackup at 2024-01-22 14:26:10
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:26:11
|
basic 압축을 했을때는 백업이 1분 30초 소요됨
용량 확인
|
1
2
3
4
5
6
|
$ ls -lh
합계 1.1G
-rw-r----- 1 oracle oinstall 235K 1월 22 14:24 ORACLE19_062h7sha_1_1_20240122_bck_basic_comp.bk
-rw-r----- 1 oracle oinstall 1.1G 1월 22 14:26 ORACLE19_072h7shb_1_1_20240122_bck_basic_comp.bk
-rw-r----- 1 oracle oinstall 6.5K 1월 22 14:26 ORACLE19_082h7sk1_1_1_20240122_bck_basic_comp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:26 db_ctl_c-3209222764-20240122-01
|
1.1G임
기존 백업 삭제 및 기존 아카이브 로그 삭제
|
1
2
3
|
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정 및 압축 옵션 변경(LOW)
|
1
2
3
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_low_comp.bk';
CONFIGURE COMPRESSION ALGORITHM 'LOW';
|
rman 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 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 '/ORA19/app/oracle/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 '/ORA19/app/oracle/rman/%d_%U_%T_bck_low_comp.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
정상적으로 LOW로 변경됨
rman 백업(LOW 압축)
|
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> backup as compressed backupset database plus archivelog;
Starting backup at 2024-01-22 14:31:49
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=10 STAMP=1158935509
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:31:49
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:31:50
piece handle=/ORA19/app/oracle/rman/ORACLE19_0a2h7sul_1_1_20240122_bck_low_comp.bk tag=TAG20240122T143149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:31:50
Starting backup at 2024-01-22 14:31:50
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:31:51
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:32:16
piece handle=/ORA19/app/oracle/rman/ORACLE19_0b2h7sun_1_1_20240122_bck_low_comp.bk tag=TAG20240122T143151 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2024-01-22 14:32:16
Starting backup at 2024-01-22 14:32:16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=11 STAMP=1158935536
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:32:16
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:32:17
piece handle=/ORA19/app/oracle/rman/ORACLE19_0c2h7svg_1_1_20240122_bck_low_comp.bk tag=TAG20240122T143216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:32:17
Starting Control File and SPFILE Autobackup at 2024-01-22 14:32:18
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:32:19
|
LOW 압축을 했을때는 백업이 30초 소요됨
용량 확인
|
1
2
3
4
5
6
|
$ ls -lh
합계 1.7G
-rw-r----- 1 oracle oinstall 6.5K 1월 22 14:31 ORACLE19_0a2h7sul_1_1_20240122_bck_low_comp.bk
-rw-r----- 1 oracle oinstall 1.7G 1월 22 14:32 ORACLE19_0b2h7sun_1_1_20240122_bck_low_comp.bk
-rw-r----- 1 oracle oinstall 1.5M 1월 22 14:32 ORACLE19_0c2h7svg_1_1_20240122_bck_low_comp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:32 db_ctl_c-3209222764-20240122-02
|
1.7G임
기존 백업 삭제 및 기존 아카이브 로그 삭제
|
1
2
3
|
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정 및 압축 옵션 변경(MEDIUM)
|
1
2
3
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_medium_comp.bk';
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
|
rman 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 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 '/ORA19/app/oracle/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 '/ORA19/app/oracle/rman/%d_%U_%T_bck_medium_comp.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
정상적으로 MEDIUM로 변경됨
rman 백업(MEDIUM 압축)
|
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> backup as compressed backupset database plus archivelog;
Starting backup at 2024-01-22 14:35:13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=12 STAMP=1158935713
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:35:14
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:35:15
piece handle=/ORA19/app/oracle/rman/ORACLE19_0e2h7t52_1_1_20240122_bck_medium_comp.bk tag=TAG20240122T143514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:35:15
Starting backup at 2024-01-22 14:35:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:35:15
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:36:00
piece handle=/ORA19/app/oracle/rman/ORACLE19_0f2h7t53_1_1_20240122_bck_medium_comp.bk tag=TAG20240122T143515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 2024-01-22 14:36:00
Starting backup at 2024-01-22 14:36:00
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=13 STAMP=1158935760
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:36:01
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:36:02
piece handle=/ORA19/app/oracle/rman/ORACLE19_0g2h7t6h_1_1_20240122_bck_medium_comp.bk tag=TAG20240122T143601 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:36:02
Starting Control File and SPFILE Autobackup at 2024-01-22 14:36:02
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:36:04
|
MEDIUM 압축을 했을때는 백업이 30초 소요됨
용량 확인
|
1
2
3
4
5
6
|
$ ls -lh
합계 1.3G
-rw-r----- 1 oracle oinstall 5.5K 1월 22 14:35 ORACLE19_0e2h7t52_1_1_20240122_bck_medium_comp.bk
-rw-r----- 1 oracle oinstall 1.3G 1월 22 14:35 ORACLE19_0f2h7t53_1_1_20240122_bck_medium_comp.bk
-rw-r----- 1 oracle oinstall 17K 1월 22 14:36 ORACLE19_0g2h7t6h_1_1_20240122_bck_medium_comp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:36 db_ctl_c-3209222764-20240122-03
|
1.3G임
기존 백업 삭제 및 기존 아카이브 로그 삭제
|
1
2
3
|
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정 및 압축 옵션 변경(HIGH)
|
1
2
3
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_high_comp.bk';
CONFIGURE COMPRESSION ALGORITHM 'HIGH';
|
rman 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 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 '/ORA19/app/oracle/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 '/ORA19/app/oracle/rman/%d_%U_%T_bck_high_comp.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
정상적으로 HIGH로 변경됨
rman 백업(HIGH 압축)
|
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> backup as compressed backupset database plus archivelog;
Starting backup at 2024-01-22 14:39:18
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=14 STAMP=1158935958
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:39:19
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:39:20
piece handle=/ORA19/app/oracle/rman/ORACLE19_0i2h7tcn_1_1_20240122_bck_high_comp.bk tag=TAG20240122T143919 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:39:20
Starting backup at 2024-01-22 14:39:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:39:20
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:43:35
piece handle=/ORA19/app/oracle/rman/ORACLE19_0j2h7tco_1_1_20240122_bck_high_comp.bk tag=TAG20240122T143920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:15
Finished backup at 2024-01-22 14:43:35
Starting backup at 2024-01-22 14:43:35
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=15 STAMP=1158936215
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:43:36
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:43:37
piece handle=/ORA19/app/oracle/rman/ORACLE19_0k2h7tko_1_1_20240122_bck_high_comp.bk tag=TAG20240122T144336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:43:37
Starting Control File and SPFILE Autobackup at 2024-01-22 14:43:37
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-04 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:43:38
|
HIGH 압축을 했을때는 백업이 4분 20초 소요됨
용량 확인
|
1
2
3
4
5
6
|
$ ls -lh
합계 931M
-rw-r----- 1 oracle oinstall 39K 1월 22 14:39 ORACLE19_0i2h7tcn_1_1_20240122_bck_high_comp.bk
-rw-r----- 1 oracle oinstall 920M 1월 22 14:43 ORACLE19_0j2h7tco_1_1_20240122_bck_high_comp.bk
-rw-r----- 1 oracle oinstall 878K 1월 22 14:43 ORACLE19_0k2h7tko_1_1_20240122_bck_high_comp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:43 db_ctl_c-3209222764-20240122-04
|
931M임
기존 백업 삭제 및 기존 아카이브 로그 삭제
|
1
2
3
|
RMAN>
delete noprompt backup;
delete noprompt archivelog all;
|
rman 백업용 파라미터 설정 및 압축 옵션 변경(ZSTD)
|
1
2
3
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bck_zstd_comp.bk';
CONFIGURE COMPRESSION ALGORITHM 'ZSTD';
|
rman 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 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 '/ORA19/app/oracle/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 '/ORA19/app/oracle/rman/%d_%U_%T_bck_zstd_comp.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'ZSTD' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
정상적으로 ZSTD로 변경됨
rman 백업(ZSTD 압축)
|
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> backup as compressed backupset database plus archivelog;
Starting backup at 2024-01-22 14:51:17
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=17 STAMP=1158936677
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:51:17
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:51:18
piece handle=/ORA19/app/oracle/rman/ORACLE19_0o2h7u35_1_1_20240122_bck_zstd_comp.bk tag=TAG20240122T145117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:51:18
Starting backup at 2024-01-22 14:51:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmancomp01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:51:18
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:52:03
piece handle=/ORA19/app/oracle/rman/ORACLE19_0p2h7u36_1_1_20240122_bck_zstd_comp.bk tag=TAG20240122T145118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 2024-01-22 14:52:03
Starting backup at 2024-01-22 14:52:03
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=18 STAMP=1158936723
channel ORA_DISK_1: starting piece 1 at 2024-01-22 14:52:04
channel ORA_DISK_1: finished piece 1 at 2024-01-22 14:52:05
piece handle=/ORA19/app/oracle/rman/ORACLE19_0q2h7u4k_1_1_20240122_bck_zstd_comp.bk tag=TAG20240122T145204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-22 14:52:05
Starting Control File and SPFILE Autobackup at 2024-01-22 14:52:05
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20240122-05 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-22 14:52:07
|
ZSTD 압축을 했을때는 백업이 50초 소요됨
용량 확인
|
1
2
3
4
5
6
|
$ ls -lh
합계 1.3G
-rw-r----- 1 oracle oinstall 5.5K 1월 22 14:51 ORACLE19_0o2h7u35_1_1_20240122_bck_zstd_comp.bk
-rw-r----- 1 oracle oinstall 1.3G 1월 22 14:51 ORACLE19_0p2h7u36_1_1_20240122_bck_zstd_comp.bk
-rw-r----- 1 oracle oinstall 4.0K 1월 22 14:52 ORACLE19_0q2h7u4k_1_1_20240122_bck_zstd_comp.bk
-rw-r----- 1 oracle oinstall 11M 1월 22 14:52 db_ctl_c-3209222764-20240122-05
|
1.3G임
미압축, BASIC, MEDIUM, HIGH, ZSTD 테스트 결과
미압축 / 20초 / 3.9GB
BASIC 압축 / 1분 30초 / 1.1GB
LOW 압축 / 30초 / 1.7G
MEDIUM 압축 / 30초 / 1.3G
HIGH 압축 / 4분 20초 / 0.9G(931M)
ZSTD 압축 / 50초 / 1.3G
5가지 압축 방식 중 HIGH가 가장 느렸지만 압축률이 가장 좋음
LOW와 MEDIUM이 시간은 동일했지만 압축률은 MEDIUM이 더 좋았음
BASIC은 LOW와 MEDIUM보다 시간은 더 소요됬지만 용량은 그 둘의 중간 수준임
ZSTD는 MEDIUM과 같은 수준으로 압축되지만 시간은 더 소요됨
그리고 추가로 v$rman_compression_algorithm에 있던 다른 압축 알고리즘들도 테스트 해보았지만
알고리즘 자체가 적용되지 않아 테스트 불가했음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
rman 백업용 파라미터 설정 및 압축 옵션 변경(ZLIB)
RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 01/22/2024 14:54:51
RMAN-06806: compression algorithm 'ZLIB' of release DEFAULT not found
rman 백업용 파라미터 설정 및 압축 옵션 변경(BZIP2)
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 01/22/2024 14:55:48
RMAN-06806: compression algorithm 'BZIP2' of release DEFAULT not found
|
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/backing-up-database.html#GUID-22AFE123-0D60-4194-9DE0-C4C0884BAEF9
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/configuring-rman-client-advanced.html#GUID-3117DA93-EC34-488D-A4FB-29E6CD4D168A
https://positivemh.tistory.com/567
rman 백업 정리 및 스크립트
OS환경 : Oracle Linux 7.5 (64bit) DB 환경 : Oracle Database 19.7.0.0 방법 : rman 백업 스크립트 및 정리rman 이란?recovery manager (RMAN)은 오라클에서 제공하는 백업 및 복구 작업을 수행하고 백업 전략 관리를 자동
positivemh.tistory.com
https://positivemh.tistory.com/980
오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교
OS환경 : Oracle Linux 7.4 (64bit) DB 환경 : Oracle Database 19.3.0.0 방법 : 오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 오라클 19c에서 rman으로 백업할때는 2가지 방식을 사용해서 백업할 수 있
positivemh.tistory.com
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0) | 2024.04.01 |
|---|---|
| 오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0) | 2024.03.28 |
| 오라클 19c rman 백업 이후 datafile이 추가된 경우 복구 상황 시나리오 (0) | 2023.12.21 |
| 오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 (0) | 2023.12.21 |
| 오라클 19c rman 접속하는 몇가지 방법 (0) | 2023.01.25 |