Enabling Oracle Block Change Tracking

Backup & Recovery / 14/07/2022

Quando fazemos um backup incremental no banco de dados, o Oracle precisa acessar todos os datafiles existentes para levantar quais foram aqueles que tiveram mudanças (no caso do diferencial, desde o último backup level 0 ou level 1 mais recente; e no caso do cumulativo, desde o último backup level 0). Esta operação, dependendo da volumetria e intensidade do uso do ambiente, pode gerar muito esforço ao Oracle. Desse modo, para evitar essa situação, a Oracle disponibilizou o Block Change Tracking, que é um arquivo binário que vai realizando o controle de todas as mudanças contempladas no banco (com retenção máxima dos últimos 8 backups). Com isso, o Oracle realiza a consulta neste arquivo, em vez de ir varrendo e comparando cada datafile, evitando assim esforço desnecessário.

Para checar se o BCT está habilitado, podemos executar o comando abaixo:

[oracle@oel8 OPatch]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 24 05:28: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> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
using target database control file instead of recovery catalog
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
DISABLED

Habilitando o BCT e realizando novamente a consulta. Podemos perceber que o arquivo é criado, por padrão, no valor do parâmetro DB_CREATE_FILE_DEST:

RMAN> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
 
Statement processed
 
RMAN> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
ENABLED
/oracle/dados/RMANDB/changetracking/o1_mf_j5p1tvdz_.chg

Caso precisemos alterar este arquivo, primeiro baixamos o banco de dados, movemos o arquivo na camada de sistema operacional, montamos o banco e realizamos o ALTER DATABASE RENAME, conforme exemplos abaixo:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 24 06:34:36 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@oel8 ~]$ mv /oracle/dados/RMANDB/changetracking/o1_mf_j5p1tvdz_.chg /home/oracle/BSS.chg
[oracle@oel8 ~]$

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 24 06:35:58 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area 1610612016 bytes
Fixed Size                  8658224 bytes
Variable Size             520093696 bytes
Database Buffers         1073741824 bytes
Redo Buffers                8118272 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE '/oracle/dados/RMANDB/changetracking/o1_mf_j5p1tvdz_.chg' TO '/home/oracle/BSS.chg';
 
Database altered.
 
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
ENABLED
/home/oracle/BSS.chg
 
 
SQL> ALTER DATABASE OPEN;
 
Database altered.

Caso não possamos baixar o banco de dados por falta de janela, podemos realizar a alteração de forma online, porém, os dados armazenados no arquivo original serão perdidos. Com isso, será necessário um backup level 0 do banco para que o novo arquivo consiga iniciar o seu trabalho:

SQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
 
INSTANCE_NAME    STATUS
---------------- ------------
RMANDB           OPEN
 
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
 
Database altered.
 
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/oracle/dados/RMANDB/changetracking/BSS.chg';
 
Database altered.
 
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
ENABLED
/oracle/dados/RMANDB/changetracking/BSS.chg
 
 
SQL> !ls -lthr /home/oracle/BSS.chg ; ls -lthr /oracle/dados/RMANDB/changetracking/BSS.chg
ls: cannot access '/home/oracle/BSS.chg': No such file or directory
-rw-r-----. 1 oracle oinstall 12M Mar 24 06:39 /oracle/dados/RMANDB/changetracking/BSS.chg

Realizando um backup incremental level 1. Como não existe um backup level 0, o mesmo será feito:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
 
Starting backup at 2021-03-24:06:42:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=110 device type=DISK
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 7 found
channel ORA_DISK_1: starting incremental level 0 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=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-24:06:42:43
channel ORA_DISK_1: finished piece 1 at 2021-03-24:06:43:48
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-24:06:43:48
channel ORA_DISK_1: finished piece 1 at 2021-03-24:06:43:49
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-24:06:43:49
 
Starting Control File and SPFILE Autobackup at 2021-03-24:06:43:49
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014630_j5p2gqgx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:06:43:53

A consulta abaixo nos reporta se o BCT foi usado e o seu nível de utilização. Percebemos que não foi utilizado pois ainda não existia um backup nível 0:

RMAN> SELECT USED_CHANGE_TRACKING, FILE#, AVG(DATAFILE_BLOCKS), AVG(BLOCKS_READ) FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 GROUP BY USED_CHANGE_TRACKING, FILE# ORDER BY 1;
 
USE      FILE# AVG(DATAFILE_BLOCKS) AVG(BLOCKS_READ)
--- ---------- -------------------- ----------------
NO           5                25600             1024

Realizando backup incremental novamente:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
 
Starting backup at 2021-03-24:06:46:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 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-03-24:06:46:55
channel ORA_DISK_1: finished piece 1 at 2021-03-24:06:46:56
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-24:06:46:56
 
Starting Control File and SPFILE Autobackup at 2021-03-24:06:46:57
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014817_j5p2nl1p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:06:46:58

Agora o bct foi utilizado:

RMAN> SELECT USED_CHANGE_TRACKING, FILE#, AVG(DATAFILE_BLOCKS), AVG(BLOCKS_READ) FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 GROUP BY USED_CHANGE_TRACKING, FILE# ORDER BY 1;
 
USE      FILE# AVG(DATAFILE_BLOCKS) AVG(BLOCKS_READ)
--- ---------- -------------------- ----------------
NO           5                25600             1024
YES          1               110080              107
YES          3                80640             1376
YES          4                39040              277
YES          5                25600                1
YES          7                  640                1

Limpando os backupsets gerados neste artigo:

RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
110     110     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068011384_j5oz98p4_.bkp
111     111     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp
112     112     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp
113     113     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014630_j5p2gqgx_.bkp
114     114     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp
115     115     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014817_j5p2nl1p_.bkp
116     116     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2ow5y_.bkp
117     117     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014861_j5p2oycy_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068011384_j5oz98p4_.bkp RECID=110 STAMP=1068011384
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp RECID=111 STAMP=1068014563
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp RECID=112 STAMP=1068014628
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014630_j5p2gqgx_.bkp RECID=113 STAMP=1068014631
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp RECID=114 STAMP=1068014816
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014817_j5p2nl1p_.bkp RECID=115 STAMP=1068014818
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5p2ow5y_.bkp RECID=116 STAMP=1068014860
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068014861_j5p2oycy_.bkp RECID=117 STAMP=1068014862
Deleted 8 objects

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.

2 thoughts on “Enabling Oracle Block Change Tracking”

  1. Pingback: Using Block Change Tracking (BCT) in CDB$ROOT – Bruno Santos da Silva

  2. Pingback: Using Block Change Tracking (BCT) in CDB$ROOT – SWIV

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish