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\u00e7as (no caso do diferencial, desde o \u00faltimo backup level 0 ou level 1 mais recente; e no caso do cumulativo, desde o \u00faltimo backup level 0). Esta opera\u00e7\u00e3o, dependendo da volumetria e intensidade do uso do ambiente, pode gerar muito esfor\u00e7o ao Oracle. Desse modo, para evitar essa situa\u00e7\u00e3o, a Oracle disponibilizou o Block Change Tracking, que \u00e9 um arquivo bin\u00e1rio que vai realizando o controle de todas as mudan\u00e7as contempladas no banco (com reten\u00e7\u00e3o m\u00e1xima dos \u00faltimos 8 backups). Com isso, o Oracle realiza a consulta neste arquivo, em vez de ir varrendo e comparando cada datafile, evitando assim esfor\u00e7o desnecess\u00e1rio.<\/p>\n\n\n\n
Para checar se o BCT est\u00e1 habilitado, podemos executar o comando abaixo:<\/p>\n\n\n
\n[oracle@oel8 OPatch]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 24 05:28:34 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: RMANDB (DBID=3825250984)\n \nRMAN> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nusing target database control file instead of recovery catalog\nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nDISABLED\n<\/pre><\/div>\n\n\nHabilitando 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
\nRMAN> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;\n \nStatement processed\n \nRMAN> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nENABLED\n\/oracle\/dados\/RMANDB\/changetracking\/o1_mf_j5p1tvdz_.chg\n<\/pre><\/div>\n\n\nCaso 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
\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 24 06:34:36 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> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n<\/pre><\/div>\n\n\n[oracle@oel8 ~]$ mv \/oracle\/dados\/RMANDB\/changetracking\/o1_mf_j5p1tvdz_.chg \/home\/oracle\/BSS.chg\n[oracle@oel8 ~]$\n<\/pre><\/div>\n\n\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 24 06:35:58 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 1610612016 bytes\nFixed Size 8658224 bytes\nVariable Size 520093696 bytes\nDatabase Buffers 1073741824 bytes\nRedo Buffers 8118272 bytes\nDatabase mounted.\nSQL> ALTER DATABASE RENAME FILE '\/oracle\/dados\/RMANDB\/changetracking\/o1_mf_j5p1tvdz_.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\nCaso 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> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nRMANDB OPEN\n \nSQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;\n \nDatabase altered.\n \nSQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '\/oracle\/dados\/RMANDB\/changetracking\/BSS.chg';\n \nDatabase altered.\n \nSQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;\n \nSTATUS\n----------\nFILENAME\n--------------------------------------------------------------------------------\nENABLED\n\/oracle\/dados\/RMANDB\/changetracking\/BSS.chg\n \n \nSQL> !ls -lthr \/home\/oracle\/BSS.chg ; ls -lthr \/oracle\/dados\/RMANDB\/changetracking\/BSS.chg\nls: cannot access '\/home\/oracle\/BSS.chg': No such file or directory\n-rw-r-----. 1 oracle oinstall 12M Mar 24 06:39 \/oracle\/dados\/RMANDB\/changetracking\/BSS.chg\n<\/pre><\/div>\n\n\nRealizando um backup incremental level 1. Como n\u00e3o existe um backup level 0, o mesmo ser\u00e1 feito:<\/p>\n\n\n
\nRMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';\n \nStarting backup at 2021-03-24:06:42:40\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=110 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\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\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-03-24:06:42:43\nchannel ORA_DISK_1: finished piece 1 at 2021-03-24:06:43:48\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05\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\/RMANDB\/datafile\/ts_cortex_catalog.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-03-24:06:43:48\nchannel ORA_DISK_1: finished piece 1 at 2021-03-24:06:43:49\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-03-24:06:43:49\n \nStarting Control File and SPFILE Autobackup at 2021-03-24:06:43:49\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014630_j5p2gqgx_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-03-24:06:43:53\n<\/pre><\/div>\n\n\nA 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 5 25600 1024\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 2021-03-24:06:46:54\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\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-03-24:06:46:55\nchannel ORA_DISK_1: finished piece 1 at 2021-03-24:06:46:56\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-03-24:06:46:56\n \nStarting Control File and SPFILE Autobackup at 2021-03-24:06:46:57\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014817_j5p2nl1p_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-03-24:06:46:58\n<\/pre><\/div>\n\n\nAgora 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 5 25600 1024\nYES 1 110080 107\nYES 3 80640 1376\nYES 4 39040 277\nYES 5 25600 1\nYES 7 640 1\n<\/pre><\/div>\n\n\nLimpando os backupsets gerados neste artigo:<\/p>\n\n\n
\nRMAN> DELETE BACKUPSET;\n \nusing channel ORA_DISK_1\n \nList of Backup Pieces\nBP Key BS Key Pc# Cp# Status Device Type Piece Name\n------- ------- --- --- ----------- ----------- ----------\n110 110 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068011384_j5oz98p4_.bkp\n111 111 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp\n112 112 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp\n113 113 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014630_j5p2gqgx_.bkp\n114 114 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp\n115 115 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014817_j5p2nl1p_.bkp\n116 116 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2ow5y_.bkp\n117 117 1 1 AVAILABLE DISK \/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014861_j5p2oycy_.bkp\n \nDo you really want to delete the above objects (enter YES or NO)? YES\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068011384_j5oz98p4_.bkp RECID=110 STAMP=1068011384\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd0_BSS_j5p2dmmm_.bkp RECID=111 STAMP=1068014563\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2gnpc_.bkp RECID=112 STAMP=1068014628\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014630_j5p2gqgx_.bkp RECID=113 STAMP=1068014631\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2nj7q_.bkp RECID=114 STAMP=1068014816\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014817_j5p2nl1p_.bkp RECID=115 STAMP=1068014818\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_03_24\/o1_mf_nnnd1_BSS_j5p2ow5y_.bkp RECID=116 STAMP=1068014860\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_03_24\/o1_mf_s_1068014861_j5p2oycy_.bkp RECID=117 STAMP=1068014862\nDeleted 8 objects\n<\/pre><\/div>\n\n\nObs: 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":"
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\u00e7as (no caso do diferencial, desde o \u00faltimo backup level 0 ou level 1 mais recente; e no caso do cumulativo, desde o \u00faltimo backup level 0). Esta opera\u00e7\u00e3o, dependendo da […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3113","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3113","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=3113"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3113\/revisions"}],"predecessor-version":[{"id":9205,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3113\/revisions\/9205"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}