Utilizar o recurso de Flashback Database nos traz uma oportunidade interessante de recuperar nosso banco de dados de alguns eventos de falhas ou erros humanos, que por vezes pode ser mais r\u00e1pido que aplicar um PITR. Na arquitetura Multitenant, temos a op\u00e7\u00e3o de aplicar este processo a n\u00edvel de CDB ou em algum PDB espec\u00edfico. Neste artigo vou explorar o primeiro caso, utilizando o target point como SCN (j\u00e1 que temos as op\u00e7\u00f5es de usar tamb\u00e9m um PDB restore point, CDB restore point, um por express\u00e3o de data\/hora).<\/p>\n\n\n\n
Realizando um backup do CDB e seus PDBs:<\/p>\n\n\n
\n[oracle@quiasma dbs]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Sat May 29 04:37: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: ASWAN (DBID=1341162082)\n \nRMAN> BACKUP DATABASE PLUS ARCHIVELOG;\n \n \nStarting backup at 29-MAY-21\ncurrent log archived\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=65 device type=DISK\nchannel ORA_DISK_1: starting archived log backup set\nchannel ORA_DISK_1: specifying archived log(s) in backup set\ninput archived log thread=1 sequence=37 RECID=32 STAMP=1073452633\ninput archived log thread=1 sequence=38 RECID=33 STAMP=1073452646\ninput archived log thread=1 sequence=39 RECID=34 STAMP=1073452661\ninput archived log thread=1 sequence=40 RECID=35 STAMP=1073452739\ninput archived log thread=1 sequence=41 RECID=36 STAMP=1073452740\ninput archived log thread=1 sequence=42 RECID=37 STAMP=1073452787\ninput archived log thread=1 sequence=43 RECID=38 STAMP=1073795863\nchannel ORA_DISK_1: starting piece 1 at 29-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_29\/o1_mf_annnn_TAG20210529T043744_jc3vt88r_.bkp tag=TAG20210529T043744 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 29-MAY-21\n \nStarting backup at 29-MAY-21\nusing channel ORA_DISK_1\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=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_system_jbm31j11_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_sysaux_jbm32m4k_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_undotbs1_jbm33d7s_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_users_jbm33f9w_.dbf\nchannel ORA_DISK_1: starting piece 1 at 29-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_29\/o1_mf_nnndf_TAG20210529T043745_jc3vt9nn_.bkp tag=TAG20210529T043745 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00013 name=\/oracle\/dados\/ASWAN\/ASWAN\/C32359B357110BDFE0536A00A8C0F81A\/datafile\/o1_mf_system_jbs98k3r_.dbf\ninput datafile file number=00014 name=\/oracle\/dados\/ASWAN\/ASWAN\/C32359B357110BDFE0536A00A8C0F81A\/datafile\/o1_mf_sysaux_jbs98k3v_.dbf\ninput datafile file number=00015 name=\/oracle\/dados\/ASWAN\/ASWAN\/C32359B357110BDFE0536A00A8C0F81A\/datafile\/o1_mf_undotbs1_jbs98k3w_.dbf\ninput datafile file number=00016 name=\/oracle\/dados\/ASWAN\/ASWAN\/C32359B357110BDFE0536A00A8C0F81A\/datafile\/o1_mf_users_jbsg5qtv_.dbf\nchannel ORA_DISK_1: starting piece 1 at 29-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C32359B357110BDFE0536A00A8C0F81A\/backupset\/2021_05_29\/o1_mf_nnndf_TAG20210529T043745_jc3vtjmf_.bkp tag=TAG20210529T043745 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00005 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_system_jbm3c7f1_.dbf\ninput datafile file number=00006 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_sysaux_jbm3c7dv_.dbf\ninput datafile file number=00008 name=\/oracle\/dados\/ASWAN\/ASWAN\/datafile\/o1_mf_undotbs1_jbm3c7f2_.dbf\nchannel ORA_DISK_1: starting piece 1 at 29-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C2F42AF0A5944E90E0536A00A8C038BD\/backupset\/2021_05_29\/o1_mf_nnndf_TAG20210529T043745_jc3vtmpg_.bkp tag=TAG20210529T043745 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07\nFinished backup at 29-MAY-21\n \nStarting backup at 29-MAY-21\ncurrent log archived\nusing channel ORA_DISK_1\nspecification does not match any archived log in the repository\nbackup cancelled because there are no files to backup\nFinished backup at 29-MAY-21\n \nStarting Control File Autobackup at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_29\/o1_mf_n_1073795882_jc3vtv2g_.bkp comment=NONE\nFinished Control File Autobackup at 29-MAY-21\n<\/pre><\/div>\n\n\nPara a valida\u00e7\u00e3o do Flashback, vamos utilizar um common user como refer\u00eancia. Vou cri\u00e1-lo nesse momento em todo o ambiente:<\/p>\n\n\n
\n[oracle@quiasma dbs]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Sat May 29 04:39:10 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> CREATE USER C##LUXOR IDENTIFIED BY oracle CONTAINER=ALL;\n \nUser created.\n \nSQL> GRANT CREATE SESSION TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n<\/pre><\/div>\n\n\nValidando que a FRA (Fast Recovery Area) est\u00e1 habilitada:<\/p>\n\n\n
\nSQL> SHO PARAMETER DB_RECOVERY_FILE_DEST;\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ndb_recovery_file_dest string \/oracle\/fra\ndb_recovery_file_dest_size big integer 10512M\n<\/pre><\/div>\n\n\nValidando se o Flashback est\u00e1 habilitado:<\/p>\n\n\n
\nSQL> SELECT FLASHBACK_ON FROM V$DATABASE;\n \nFLASHBACK_ON\n------------------\nNO\n<\/pre><\/div>\n\n\nComo precisaremos habilitar o Flashback, o processo exige o restart do CDB, conforme demonstrado abaixo:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\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 SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;\n \nSystem altered.\n \nSQL> ALTER DATABASE FLASHBACK ON;\n \nDatabase altered.\n \nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nColetando o SCN do banco:<\/p>\n\n\n
\nSQL> SELECT CURRENT_SCN FROM V$DATABASE;\n \nCURRENT_SCN\n-----------\n 1810936\n<\/pre><\/div>\n\n\nAgora vamos dropar o nosso common user para simular a situa\u00e7\u00e3o que justifique o flashback:<\/p>\n\n\n
\nSQL> DROP USER C##LUXOR CASCADE;\n \nUser dropped.\n<\/pre><\/div>\n\n\nGerando alguns archives:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n<\/pre><\/div>\n\n\nAgora podemos baixar o ambiente e montar o banco, para proceder com o Flashback, utilizando o SCN que coletamos:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\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> FLASHBACK DATABASE TO SCN 1810936;\n \nFlashback complete.\n<\/pre><\/div>\n\n\nAbrindo o banco em read only para validar se o nosso common user est\u00e1 dispon\u00edvel:<\/p>\n\n\n
\nSQL> ALTER DATABASE OPEN READ ONLY;\n \nDatabase altered.\n \nSQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;\n \nPluggable database altered.\n \nSQL> SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##LUXOR';\n \nUSERNAME\n--------------------------------------------------------------------------------\nCOM CON_ID\n--- ----------\nC##LUXOR\nYES 1\n \nC##LUXOR\nYES 4\n<\/pre><\/div>\n\n\nUma vez validado, podemos abrir o banco em resetlogs:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\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 OPEN RESETLOGS;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nSempre \u00e9 recomendado que, ap\u00f3s este tipo de procedimento, realizarmos um backup full do ambiente.<\/p>\n\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":"
Utilizar o recurso de Flashback Database nos traz uma oportunidade interessante de recuperar nosso banco de dados de alguns eventos de falhas ou erros humanos, que por vezes pode ser mais r\u00e1pido que aplicar um PITR. Na arquitetura Multitenant, temos a op\u00e7\u00e3o de aplicar este processo a n\u00edvel de CDB ou em algum PDB espec\u00edfico. […]<\/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-4922","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\/4922","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=4922"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4922\/revisions"}],"predecessor-version":[{"id":9124,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4922\/revisions\/9124"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}