{"id":4753,"date":"2021-05-19T08:06:10","date_gmt":"2021-05-19T08:06:10","guid":{"rendered":"https:\/\/swiv.com.br\/using-block-change-tracking-bct-in-cdbroot\/"},"modified":"2026-05-27T20:02:32","modified_gmt":"2026-05-27T19:02:32","slug":"using-block-change-tracking-bct-in-cdbroot","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/05\/19\/using-block-change-tracking-bct-in-cdbroot\/","title":{"rendered":"Using Block Change Tracking (BCT) in CDB$ROOT"},"content":{"rendered":"

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

Para checar se o BCT est\u00e1 habilitado, podemos executar o comando abaixo:<\/p>\n\n\n

\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed May 19 04:51:11 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            CDB\n--------- -------------------- ---\nASWAN     READ WRITE           YES\n \nSQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nDISABLED\n<\/pre><\/div>\n\n\n
Habilitando o BCT e realizando novamente a consulta. Podemos perceber que o arquivo \u00e9 criado, por padr\u00e3o, no valor do par\u00e2metro DB_CREATE_FILE_DEST:<\/p>\n\n\n
\nSQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;\n \nDatabase altered.\n \nSQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nENABLED\n\/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg\n<\/pre><\/div>\n\n\n
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:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n<\/pre><\/div>\n\n
\n[oracle@quiasma ~]$ mv \/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg \/home\/oracle\/BSS.chg\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed May 19 04:55:54 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \nConnected to an idle instance.\n \nSQL> STARTUP MOUNT;\nORACLE instance started.\n \nTotal System Global Area 2466249080 bytes\nFixed Size                  8898936 bytes\nVariable Size             671088640 bytes\nDatabase Buffers         1778384896 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\nSQL> ALTER DATABASE RENAME FILE '\/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg' TO '\/home\/oracle\/BSS.chg';\n \nDatabase altered.\n \nSQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nENABLED\n\/home\/oracle\/BSS.chg\n \n \nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Caso n\u00e3o possamos baixar o banco de dados por falta de janela, podemos realizar a altera\u00e7\u00e3o de forma online, por\u00e9m, os dados armazenados no arquivo original ser\u00e3o perdidos. Com isso, ser\u00e1 necess\u00e1rio um backup level 0 do banco para que o novo arquivo consiga iniciar o seu trabalho:<\/p>\n\n\n
\nSQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;\n \nDatabase altered.\n \nSQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '\/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg';\n \nDatabase altered.\n \nSQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nENABLED\n\/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg\n \n \nSQL> !ls -lthr \/home\/oracle\/BSS.chg ; ls -lthr \/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg\nls: cannot access '\/home\/oracle\/BSS.chg': No such file or directory\n-rw-r-----. 1 oracle oinstall 12M May 19 05:00 \/oracle\/dados\/ASWAN\/changetracking\/o1_mf_jb9jxx56_.chg\n<\/pre><\/div>\n\n\n
Realizando um backup incremental level 1. Como n\u00e3o existe um backup level 0, o mesmo ser\u00e1 feito:<\/p>\n\n\n
\n[oracle@quiasma ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Wed May 19 05:02:23 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: ASWAN (DBID=1340416544)\n \nRMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';\n \nStarting backup at 19-MAY-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=1 device type=DISK\nno parent backup or copy of datafile 1 found\nno parent backup or copy of datafile 3 found\nno parent backup or copy of datafile 4 found\nno parent backup or copy of datafile 7 found\nno parent backup or copy of datafile 5 found\nno parent backup or copy of datafile 6 found\nno parent backup or copy of datafile 8 found\nno parent backup or copy of datafile 9 found\nno parent backup or copy of datafile 10 found\nno parent backup or copy of datafile 11 found\nchannel ORA_DISK_1: starting incremental level 0 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9yzl519_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wdsb2f_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc1bk9_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_users_j9wc1clt_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_19\/o1_mf_nnnd0_BSS_jb9kjs6s_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15\nchannel ORA_DISK_1: starting incremental level 0 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00009 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_system_jb463o46_.dbf\ninput datafile file number=00010 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_sysaux_jb487ow0_.dbf\ninput datafile file number=00011 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_undotbs1_j9wcyoh8_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_19\/o1_mf_nnnd0_BSS_jb9kk89w_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nchannel ORA_DISK_1: starting incremental level 0 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00005 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9wc9px6_.dbf\ninput datafile file number=00006 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wc9px1_.dbf\ninput datafile file number=00008 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc9px7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C2469670772D160AE0536A00A8C00FFA\/backupset\/2021_05_19\/o1_mf_nnnd0_BSS_jb9kkcd8_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00012 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_users_j9yz06wp_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_19\/o1_mf_nnnd1_BSS_jb9kkgjt_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 19-MAY-21\n \nStarting Control File and SPFILE Autobackup at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_19\/o1_mf_s_1072933375_jb9kkhs5_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 19-MAY-21\n<\/pre><\/div>\n\n\n
A consulta abaixo nos reporta se o BCT foi usado e o seu n\u00edvel de utiliza\u00e7\u00e3o. Percebemos que n\u00e3o foi utilizado pois ainda n\u00e3o existia um backup n\u00edvel 0:<\/p>\n\n\n
\nRMAN> 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;\n \nUSE      FILE# AVG(DATAFILE_BLOCKS) AVG(BLOCKS_READ)\n--- ---------- -------------------- ----------------\nNO          12                12800              128\n<\/pre><\/div>\n\n\n
Realizando backup incremental novamente:<\/p>\n\n\n
\nRMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';\n \nStarting backup at 19-MAY-21\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9yzl519_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wdsb2f_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc1bk9_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_users_j9wc1clt_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_19\/o1_mf_nnnd1_BSS_jb9kn4dn_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00009 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_system_jb463o46_.dbf\ninput datafile file number=00010 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_sysaux_jb487ow0_.dbf\ninput datafile file number=00011 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_undotbs1_j9wcyoh8_.dbf\ninput datafile file number=00012 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_users_j9yz06wp_.dbf\nchannel ORA_DISK_1: starting piece 1 at 19-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_19\/o1_mf_nnnd1_BSS_jb9kn5hy_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nchannel ORA_DISK_1: starting incremental level 1 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00005 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9wc9px6_.dbf\nskipping datafile 00005 because it has not changed\ninput datafile file number=00006 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wc9px1_.dbf\nskipping datafile 00006 because it has not changed\ninput datafile file number=00008 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc9px7_.dbf\nskipping datafile 00008 because it has not changed\nchannel ORA_DISK_1: backup cancelled because all files were skipped\nFinished backup at 19-MAY-21\n \nStarting Control File and SPFILE Autobackup at 19-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_19\/o1_mf_s_1072933462_jb9kn6q8_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 19-MAY-21\n<\/pre><\/div>\n\n\n
Agora o bct foi utilizado:<\/p>\n\n\n
\nRMAN> 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;\n \nUSE      FILE# AVG(DATAFILE_BLOCKS) AVG(BLOCKS_READ)\n--- ---------- -------------------- ----------------\nNO          12                12800              128\nYES          1               124160                1\nYES          3                74240                1\nYES          4                51840                1\nYES          7                  640                1\nYES          9                51200                1\nYES         10                48640                1\nYES         11                12800                1\nYES         12                12800                1\n \n9 rows selected\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
J\u00e1 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\u00e3o exatamente as mesmas. Para checar se o BCT est\u00e1 habilitado, podemos executar o comando abaixo: Habilitando o BCT e realizando novamente a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,8],"tags":[],"class_list":["post-4753","post","type-post","status-publish","format-standard","hentry","category-backup-recovery","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=4753"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4753\/revisions"}],"predecessor-version":[{"id":9131,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4753\/revisions\/9131"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}