Caso precisemos aplicar algum tipo de compressão nos backups realizados no Oracle, nos é dada a opção de utilizar os próprios algoritmos de compressão binária do RMAN. Este tipo de operação reduz o tamanho dos backups, e por padrão, opera com a configuração BASIC (que não exige nenhum licenciamento extra). Caso seja relevante utilizar os outros tipos, como LOW,MEDIUM e HIGH, a licença “Oracle Advanced Compression” será necessária. Obviamente este recurso traz um economia em relação ao espaço em disco/fita necessários, mas exige mais processamento (CPU). Por outro lado, nenhuma etapa adicional é necessária para realizar o restore a partir deste backups. Neste artigo exploraremos sobre estas diferenças técnicas.
Analisando o algoritmo de compressão vigente:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Tue Apr 27 06:39:34 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: RMANDB (DBID=3825250984)
RMAN> SHOW COMPRESSION ALGORITHM;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
RMAN>
Realizando um backup do banco de dados com esta opção habilitada:
RMAN> run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
BACKUP DATABASE TAG 'SEM_COMPRESSAO';
}2> 3> 4>
allocated channel: c1
channel c1: SID=1 device type=DISK
Starting backup at 2021-04-27:06:41:59
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel c1: starting piece 1 at 2021-04-27:06:41:59
channel c1: finished piece 1 at 2021-04-27:06:43:54
piece handle=/oracle/fra/RMANDB/backupset/2021_04_27/o1_mf_nnndf_SEM_COMPRESSAO_j8hq3l7z_.bkp tag=SEM_COMPRESSAO comment=NONE
channel c1: backup set complete, elapsed time: 00:01:55
Finished backup at 2021-04-27:06:43:54
Starting Control File and SPFILE Autobackup at 2021-04-27:06:43:54
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_27/o1_mf_s_1070952234_j8hq6vpz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-27:06:43:57
released channel: c1
RMAN>
Coletando as informações deste backup para comparação posterior:
RMAN> LIST BACKUPSET TAG 'SEM_COMPRESSAO';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
202 Full 1.22G DISK 00:01:27 2021-04-27:06:43:26
BP Key: 202 Status: AVAILABLE Compressed: NO Tag: SEM_COMPRESSAO
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_27/o1_mf_nnndf_SEM_COMPRESSAO_j8hq3l7z_.bkp
List of Datafiles in backup set 202
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2330319 2021-04-27:06:41:59 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2330319 2021-04-27:06:41:59 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2330319 2021-04-27:06:41:59 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2330319 2021-04-27:06:41:59 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2330319 2021-04-27:06:41:59 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Deletando backupset:
RMAN> DELETE BACKUPSET TAG 'SEM_COMPRESSAO';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
202 202 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_04_27/o1_mf_nnndf_SEM_COMPRESSAO_j8hq3l7z_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_27/o1_mf_nnndf_SEM_COMPRESSAO_j8hq3l7z_.bkp RECID=202 STAMP=1070952130
Deleted 1 objects
Alterando valor do algoritmo de compressão do RMAN para LOW:
RMAN> SET COMPRESSION ALGORITHM 'LOW';
executing command: SET compression
Executando backup com esta nova opção:
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE TAG 'COMPRESSAO_LOW';
Starting backup at 2021-04-28:04:09:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-28:04:09:21
channel ORA_DISK_1: finished piece 1 at 2021-04-28:04:10:16
piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_LOW_j8l2k27b_.bkp tag=COMPRESSAO_LOW comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 2021-04-28:04:10:16
Starting Control File and SPFILE Autobackup at 2021-04-28:04:10:16
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_28/o1_mf_s_1071029416_j8l2lsbn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-28:04:10:19
Coletando dados:
RMAN> LIST BACKUPSET TAG 'COMPRESSAO_LOW';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
206 Full 423.23M DISK 00:00:43 2021-04-28:04:10:04
BP Key: 206 Status: AVAILABLE Compressed: YES Tag: COMPRESSAO_LOW
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_LOW_j8l2k27b_.bkp
List of Datafiles in backup set 206
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2363901 2021-04-28:04:09:21 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2363901 2021-04-28:04:09:21 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2363901 2021-04-28:04:09:21 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2363901 2021-04-28:04:09:21 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2363901 2021-04-28:04:09:21 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Deletando arquivos de backup:
RMAN> DELETE BACKUPSET TAG 'COMPRESSAO_LOW';
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
206 206 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_LOW_j8l2k27b_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_LOW_j8l2k27b_.bkp RECID=206 STAMP=1071029362
Deleted 1 objects
Alterando algoritmo para MEDIUM:
RMAN> SET COMPRESSION ALGORITHM 'MEDIUM';
executing command: SET compression
Backup:
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE TAG 'COMPRESSAO_MEDIUM';
Starting backup at 2021-04-28:04:26:18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-28:04:26:18
channel ORA_DISK_1: finished piece 1 at 2021-04-28:04:26:54
piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_MEDIUM_j8l3jtz0_.bkp tag=COMPRESSAO_MEDIUM comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 2021-04-28:04:26:54
Starting Control File and SPFILE Autobackup at 2021-04-28:04:26:54
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_28/o1_mf_s_1071030414_j8l3kyw4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-28:04:26:57
Coletando dados:
RMAN> LIST BACKUPSET TAG 'COMPRESSAO_MEDIUM';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
212 Full 361.59M DISK 00:00:30 2021-04-28:04:26:48
BP Key: 212 Status: AVAILABLE Compressed: YES Tag: COMPRESSAO_MEDIUM
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_MEDIUM_j8l3jtz0_.bkp
List of Datafiles in backup set 212
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2365541 2021-04-28:04:26:18 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2365541 2021-04-28:04:26:18 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2365541 2021-04-28:04:26:18 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2365541 2021-04-28:04:26:18 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2365541 2021-04-28:04:26:18 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Deletando backup:
RMAN> DELETE BACKUPSET TAG 'COMPRESSAO_MEDIUM';
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
208 208 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_MEDIUM_j8l2tnw0_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_MEDIUM_j8l2tnw0_.bkp RECID=208 STAMP=1071029668
Deleted 1 objects
Alterando configuração para HIGH:
RMAN> SET COMPRESSION ALGORITHM 'HIGH';
executing command: SET compression
using target database control file instead of recovery catalog
Backup:
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE TAG 'COMPRESSAO_HIGH';
Starting backup at 2021-04-28:04:18:03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=119 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-28:04:18:04
channel ORA_DISK_1: finished piece 1 at 2021-04-28:04:19:59
piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_HIGH_j8l31d5s_.bkp tag=COMPRESSAO_HIGH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2021-04-28:04:19:59
Starting Control File and SPFILE Autobackup at 2021-04-28:04:19:59
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_28/o1_mf_s_1071029999_j8l34zsx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-28:04:20:00
Coleta:
RMAN> LIST BACKUPSET TAG 'COMPRESSAO_HIGH';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
210 Full 293.19M DISK 00:01:53 2021-04-28:04:19:57
BP Key: 210 Status: AVAILABLE Compressed: YES Tag: COMPRESSAO_HIGH
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_HIGH_j8l31d5s_.bkp
List of Datafiles in backup set 210
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2364312 2021-04-28:04:18:04 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2364312 2021-04-28:04:18:04 2364479 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2364312 2021-04-28:04:18:04 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2364312 2021-04-28:04:18:04 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2364312 2021-04-28:04:18:04 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Removendo arquivos:
RMAN> DELETE BACKUPSET TAG 'COMPRESSAO_HIGH';
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
210 210 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_HIGH_j8l31d5s_.bkp
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_28/o1_mf_nnndf_COMPRESSAO_HIGH_j8l31d5s_.bkp RECID=210 STAMP=1071029884
Deleted 1 objects
Tabela comparativa entre todas as opções usadas neste artigo. Obviamente ela não representa de forma estática os pontos positivos e negativos para todos os ambientes, então a melhor opção é testar em seu ambiente a execução e realizar também a comparação.
| Algoritmo | Tamanho do Backup | Tempo do Backup |
| BASIC | 1.22G | 00:01:27 |
| LOW | 423.23M | 00:00:43 |
| MEDIUM | 361.59M | 00:00:30 |
| HIGH | 293.19M | 00:01:53 |
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish
Pingback: Enabling RMAN Backup Compression – Bruno Santos da Silva
Pingback: Enabling RMAN Backup Compression – SWIV