Segundo a documentação da Oracle sobre este assunto em específico (link AQUI), é possível atingir uma melhor taxa de compressão nos backups executados via RMAN caso o recurso de Precompression Block seja utilizado. Ele realiza uma manobra interna de consolidar os espaços livres de cada bloco de dados, marcando-os com binários em 0. Essa característica é relevante nos ambientes mais voláteis, que executam inserts e deletes constantes.
A opção “OPTIMIZE FOR LOAD” que é responsável por controlar este recurso. Seu valor padrão está configurado como TRUE, o que significa que o Precompression não está sendo utilizado. Para os casos de habilitá-lo, basta mudar o valor para FALSE, obviamente sabendo que o processamento no servidor será um pouco mais exigido.
Para simularmos sua execução, configurando o algoritmo de compressão do RMAN em BASIC, porém sem habilitar o Precompression:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Thu Apr 29 05:47:14 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
Backup do banco e seus dados:
RMAN> BACKUP DATABASE;
Starting backup at 2021-04-29:05:16:40
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=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-29:05:16:40
channel ORA_DISK_1: finished piece 1 at 2021-04-29:05:16:56
piece handle=/oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T051640_j8ntv90s_.bkp tag=TAG20210429T051640 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 2021-04-29:05:16:56
Starting Control File and SPFILE Autobackup at 2021-04-29:05:16:56
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_29/o1_mf_s_1071119816_j8ntvrkl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-29:05:16:57
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
246 Full 1.22G DISK 00:00:09 2021-04-29:05:16:49
BP Key: 246 Status: AVAILABLE Compressed: NO Tag: TAG20210429T051640
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T051640_j8ntv90s_.bkp
List of Datafiles in backup set 246
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2505025 2021-04-29:05:16:40 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2505025 2021-04-29:05:16:40 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2505025 2021-04-29:05:16:40 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2505025 2021-04-29:05:16:40 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2505025 2021-04-29:05:16:40 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Alterando algoritmo de compressão do RMAN para HIGH, sem habilitar o precompression:
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 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
Backup e informações:
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at 2021-04-29:05:36:40
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=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-29:05:36:40
channel ORA_DISK_1: finished piece 1 at 2021-04-29:05:38:35
piece handle=/oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T053640_j8nw0r7r_.bkp tag=TAG20210429T053640 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2021-04-29:05:38:35
Starting Control File and SPFILE Autobackup at 2021-04-29:05:38:35
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_29/o1_mf_s_1071121115_j8nw4cn8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-29:05:38:36
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
256 Full 294.33M DISK 00:01:45 2021-04-29:05:38:25
BP Key: 256 Status: AVAILABLE Compressed: YES Tag: TAG20210429T053640
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T053640_j8nw0r7r_.bkp
List of Datafiles in backup set 256
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2506811 2021-04-29:05:36:40 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2506811 2021-04-29:05:36:40 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2506811 2021-04-29:05:36:40 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2506811 2021-04-29:05:36:40 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2506811 2021-04-29:05:36:40 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Habilitando o recurso de Precompression:
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE;
old RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD FALSE;
new RMAN configuration parameters are successfully stored
Realizando backup e coletando informações:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Thu Apr 29 05:40:11 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> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at 2021-04-29:05:40:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 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-29:05:40:16
channel ORA_DISK_1: finished piece 1 at 2021-04-29:05:42:11
piece handle=/oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T054015_j8nw7j61_.bkp tag=TAG20210429T054015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2021-04-29:05:42:11
Starting Control File and SPFILE Autobackup at 2021-04-29:05:42:11
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_29/o1_mf_s_1071121331_j8nwc3qd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-29:05:42:12
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
258 Full 238.70M DISK 00:01:47 2021-04-29:05:42:03
BP Key: 258 Status: AVAILABLE Compressed: YES Tag: TAG20210429T054015
Piece Name: /oracle/fra/RMANDB/backupset/2021_04_29/o1_mf_nnndf_TAG20210429T054015_j8nw7j61_.bkp
List of Datafiles in backup set 258
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 2507015 2021-04-29:05:40:16 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 Full 2507015 2021-04-29:05:40:16 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 Full 2507015 2021-04-29:05:40:16 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 Full 2507015 2021-04-29:05:40:16 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 Full 2507015 2021-04-29:05:40:16 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
A taxa de compressão foi um pouco maior no último exemplo, mesmo considerando que meu banco de laboratório não tem característica de sofrer muitos deletes e inserts, e possuir um tamanho muito menor que os bancos corporativos. Mas já podemos ter uma noção da diferença que este recurso pode prover, logicamente sendo aplicado com critério e cuidado em nossos ambientes.
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