Using Block Change Tracking (BCT) in CDB$ROOT

Backup & Recovery, Multitenant / 13/07/2022

Já tive a oportunidade de demonstrar o uso do BCT em arquitetura non-CDB (NESTE artigo), e agora vou tentar implementar este recurso na arquitetura Multitenant, especificamente no CDB$ROOT. Veremos que as etapas são exatamente as mesmas.

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

[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 19 04:51:11 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> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
 
NAME      OPEN_MODE            CDB
--------- -------------------- ---
ASWAN     READ WRITE           YES
 
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
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:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
 
Database altered.
 
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
ENABLED
/oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.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:

SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@quiasma ~]$ mv /oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.chg /home/oracle/BSS.chg
[oracle@quiasma ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 19 04:55:54 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 2466249080 bytes
Fixed Size                  8898936 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE '/oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.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> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
 
Database altered.
 
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.chg';
 
Database altered.
 
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
 
STATUS
----------
FILENAME
--------------------------------------------------------------------------------
ENABLED
/oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.chg
 
 
SQL> !ls -lthr /home/oracle/BSS.chg ; ls -lthr /oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.chg
ls: cannot access '/home/oracle/BSS.chg': No such file or directory
-rw-r-----. 1 oracle oinstall 12M May 19 05:00 /oracle/dados/ASWAN/changetracking/o1_mf_jb9jxx56_.chg

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

[oracle@quiasma ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 19 05:02:23 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ASWAN (DBID=1340416544)
 
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
 
Starting backup at 19-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 8 found
no parent backup or copy of datafile 9 found
no parent backup or copy of datafile 10 found
no parent backup or copy of datafile 11 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/ASWAN/datafile/o1_mf_system_j9yzl519_.dbf
input datafile file number=00003 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wdsb2f_.dbf
input datafile file number=00004 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc1bk9_.dbf
input datafile file number=00007 name=/oracle/dados/ASWAN/datafile/o1_mf_users_j9wc1clt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_19/o1_mf_nnnd0_BSS_jb9kjs6s_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
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=00009 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb463o46_.dbf
input datafile file number=00010 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_jb487ow0_.dbf
input datafile file number=00011 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_19/o1_mf_nnnd0_BSS_jb9kk89w_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
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=00005 name=/oracle/dados/ASWAN/datafile/o1_mf_system_j9wc9px6_.dbf
input datafile file number=00006 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wc9px1_.dbf
input datafile file number=00008 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc9px7_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/C2469670772D160AE0536A00A8C00FFA/backupset/2021_05_19/o1_mf_nnnd0_BSS_jb9kkcd8_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
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=00012 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_19/o1_mf_nnnd1_BSS_jb9kkgjt_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-MAY-21
 
Starting Control File and SPFILE Autobackup at 19-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_19/o1_mf_s_1072933375_jb9kkhs5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAY-21

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          12                12800              128

Realizando backup incremental novamente:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
 
Starting backup at 19-MAY-21
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/ASWAN/datafile/o1_mf_system_j9yzl519_.dbf
input datafile file number=00003 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wdsb2f_.dbf
input datafile file number=00004 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc1bk9_.dbf
input datafile file number=00007 name=/oracle/dados/ASWAN/datafile/o1_mf_users_j9wc1clt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_19/o1_mf_nnnd1_BSS_jb9kn4dn_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=00009 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb463o46_.dbf
input datafile file number=00010 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_jb487ow0_.dbf
input datafile file number=00011 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
input datafile file number=00012 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-21
channel ORA_DISK_1: finished piece 1 at 19-MAY-21
piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_19/o1_mf_nnnd1_BSS_jb9kn5hy_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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/ASWAN/datafile/o1_mf_system_j9wc9px6_.dbf
skipping datafile 00005 because it has not changed
input datafile file number=00006 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wc9px1_.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00008 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc9px7_.dbf
skipping datafile 00008 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 19-MAY-21
 
Starting Control File and SPFILE Autobackup at 19-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_19/o1_mf_s_1072933462_jb9kn6q8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAY-21

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          12                12800              128
YES          1               124160                1
YES          3                74240                1
YES          4                51840                1
YES          7                  640                1
YES          9                51200                1
YES         10                48640                1
YES         11                12800                1
YES         12                12800                1
 
9 rows selected

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish