Em algumas circunst\u00e2ncias, principalmente por conta de performance, podemos nos deparar com processos no banco de dados usando a op\u00e7\u00e3o de NOLOGGING, o que significa que os vetores de altera\u00e7\u00e3o n\u00e3o ser\u00e3o gravados nos Online Redo Logs. Mas isso implica na seguinte quest\u00e3o: caso tenhamos um objeto dessa natureza criado DEPOIS do \u00faltimo backup, n\u00e3o conseguiremos recuper\u00e1-lo enquanto n\u00e3o fa\u00e7amos um novo backup. Nas piores situa\u00e7\u00f5es, caso n\u00e3o tenhamos esses objetos contidos no \u00faltimo backup, o que resta \u00e9 recri\u00e1-los (e isso pode implicar em perda de dados). Neste artigo vamos explorar este processo em mais detalhes.<\/p>\n\n\n\n
Reconhecendo o ambiente de laborat\u00f3rio:<\/p>\n\n\n
\n[oracle@oel7 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 19:21:04 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nNAME OPEN_MODE LOG_MODE\n--------- -------------------- ------------\nCORTEX READ WRITE ARCHIVELOG\n<\/pre><\/div>\n\n\nRealizando um backup FULL do banco de dados:<\/p>\n\n\n
\n[oracle@oel7 ~]$ rman target \/\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 19:41:22 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nconnected to target database: CORTEX (DBID=548968087)\n \nRMAN> BACKUP DATABASE;\n \nStarting backup at 08-SEP-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=249 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=+DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445\ninput datafile file number=00005 name=+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205\ninput datafile file number=00003 name=+DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519\ninput datafile file number=00004 name=+DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555\ninput datafile file number=00007 name=+DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555\nchannel ORA_DISK_1: starting piece 1 at 08-SEP-21\nchannel ORA_DISK_1: finished piece 1 at 08-SEP-21\npiece handle=+DG_FRA\/CORTEX\/BACKUPSET\/2021_09_08\/nnndf0_tag20210908t194129_0.268.1082749291 tag=TAG20210908T194129 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:45\nFinished backup at 08-SEP-21\n \nStarting Control File and SPFILE Autobackup at 08-SEP-21\npiece handle=+DG_FRA\/CORTEX\/AUTOBACKUP\/2021_09_08\/s_1082749395.267.1082749397 comment=NONE\nFinished Control File and SPFILE Autobackup at 08-SEP-21\n<\/pre><\/div>\n\n\nAp\u00f3s a conclus\u00e3o do backup, vamos criar uma tabela de exemplo usando a op\u00e7\u00e3o de NOLOGGING:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> CREATE TABLE SOE.BSS_NOLOG NOLOGGING AS SELECT * FROM DBA_USERS;\n \nTable created.\n<\/pre><\/div>\n\n\nAgora assumimos que o nosso banco de dados sofreu um evento que exige a sua recupera\u00e7\u00e3o, executada abaixo:<\/p>\n\n\n
\nRMAN> SHUTDOWN IMMEDIATE;\n \nusing target database control file instead of recovery catalog\ndatabase closed\ndatabase dismounted\nOracle instance shut down\n \nRMAN> STARTUP MOUNT;\n \nconnected to target database (not started)\nOracle instance started\ndatabase mounted\n \nTotal System Global Area 2583690520 bytes\n \nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\n \nRMAN> RESTORE DATABASE;\n \nStarting restore at 08-SEP-21\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=367 device type=DISK\n \nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00001 to +DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445\nchannel ORA_DISK_1: restoring datafile 00003 to +DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519\nchannel ORA_DISK_1: restoring datafile 00004 to +DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555\nchannel ORA_DISK_1: restoring datafile 00005 to +DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205\nchannel ORA_DISK_1: restoring datafile 00007 to +DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555\nchannel ORA_DISK_1: reading from backup piece +DG_FRA\/CORTEX\/BACKUPSET\/2021_09_08\/nnndf0_tag20210908t194129_0.268.1082749291\nchannel ORA_DISK_1: piece handle=+DG_FRA\/CORTEX\/BACKUPSET\/2021_09_08\/nnndf0_tag20210908t194129_0.268.1082749291 tag=TAG20210908T194129\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:01:55\nFinished restore at 08-SEP-21\n \nRMAN> RECOVER DATABASE;\n \nStarting recover at 08-SEP-21\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:03\n \nFinished recover at 08-SEP-21\n \nRMAN> ALTER DATABASE OPEN;\n \nStatement processed\n \nRMAN> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nNAME OPEN_MODE LOG_MODE\n--------- -------------------- ------------\nCORTEX READ WRITE ARCHIVELOG\n \nRMAN>\n<\/pre><\/div>\n\n\nAo tentar consultar nossa tabela, a mensagem de erro abaixo \u00e9 apresentada:<\/p>\n\n\n
\nRMAN> SELECT * FROM SOE.BSS_NOLOG;\n \nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of sql statement command at 09\/08\/2021 19:49:13\nORA-01578: ORACLE data block corrupted (file # 5, block # 104452)\nORA-01110: data file 5: '+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205'\nORA-26040: Data block was loaded using the NOLOGGING option\n<\/pre><\/div>\n\n\nNeste caso extremo, nossa \u00fanica op\u00e7\u00e3o \u00e9 remover o objeto e recri\u00e1-lo. Desse modo, \u00e9 importante, se poss\u00edvel, ter ci\u00eancia ou at\u00e9 monitorar este tipo de situa\u00e7\u00e3o no banco de dados.<\/p>\n\n\n
\nRMAN> DROP TABLE SOE.BSS_NOLOG;\n \nStatement processed\n \nRMAN> CREATE TABLE SOE.BSS_NOLOG NOLOGGING AS SELECT * FROM DBA_USERS;\n \nStatement processed\n \nRMAN> SELECT COUNT(*) FROM SOE.BSS_NOLOG;\n \n COUNT(*)\n----------\n 38\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":"
Em algumas circunst\u00e2ncias, principalmente por conta de performance, podemos nos deparar com processos no banco de dados usando a op\u00e7\u00e3o de NOLOGGING, o que significa que os vetores de altera\u00e7\u00e3o n\u00e3o ser\u00e3o gravados nos Online Redo Logs. Mas isso implica na seguinte quest\u00e3o: caso tenhamos um objeto dessa natureza criado DEPOIS do \u00faltimo backup, n\u00e3o […]<\/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-6352","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\/6352","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=6352"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6352\/revisions"}],"predecessor-version":[{"id":9050,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6352\/revisions\/9050"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}