Neste artigo, vamos reproduzir um Flashback Database de um PDB espec\u00edfico, configurado com Local UNDO, utilizando um restore point.<\/p>\n\n\n\n
Validando que nosso ambiente est\u00e1 com a Local Undo configurada:<\/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 05:40:37 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> col PROPERTY_NAME format a25\nSQL> col PROPERTY_VALUE format a10\nSQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';\n \nPROPERTY_NAME PROPERTY_V\n------------------------- ----------\nLOCAL_UNDO_ENABLED TRUE\n<\/pre><\/div>\n\n\nGarantindo um backup do nosso ambiente (tanto CDB quanto 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 05:43:26 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=77 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\ninput archived log thread=1 sequence=44 RECID=39 STAMP=1073795882\ninput archived log thread=1 sequence=45 RECID=40 STAMP=1073796975\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_TAG20210529T054334_jc3zopml_.bkp tag=TAG20210529T054334 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\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=1 RECID=43 STAMP=1073799814\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_TAG20210529T054334_jc3zoqoq_.bkp tag=TAG20210529T054334 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\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=46 RECID=41 STAMP=1073796976\ninput archived log thread=1 sequence=47 RECID=42 STAMP=1073796977\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_TAG20210529T054334_jc3zorr0_.bkp tag=TAG20210529T054334 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_TAG20210529T054337_jc3zosyk_.bkp tag=TAG20210529T054337 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_TAG20210529T054337_jc3zp12p_.bkp tag=TAG20210529T054337 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_TAG20210529T054337_jc3zp45g_.bkp tag=TAG20210529T054337 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\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=2 RECID=44 STAMP=1073799835\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_TAG20210529T054355_jc3zpcd3_.bkp tag=TAG20210529T054355 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 29-MAY-21\n \nStarting Control File and SPFILE Autobackup at 29-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_29\/o1_mf_s_1073799836_jc3zpdlt_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 29-MAY-21\n<\/pre><\/div>\n\n\nAgora vamos criar um restore point apenas para nosso PDB chamado HIPOFISE1:<\/p>\n\n\n
\nSQL> CREATE RESTORE POINT RP_DO_BRUNO FOR PLUGGABLE DATABASE HIPOFISE1 GUARANTEE FLASHBACK DATABASE;\n \nRestore point created.\n<\/pre><\/div>\n\n\nCom a query abaixo, conseguimos consultar o restore point existente:<\/p>\n\n\n
\nSQL> col name format a20\nSQL> SELECT SCN, NAME, CON_ID, PDB_RESTORE_POINT, GUARANTEE_FLASHBACK_DATABASE, CLEAN_PDB_RESTORE_POINT FROM V$RESTORE_POINT;\n \n SCN NAME CON_ID PDB GUA CLE\n---------- -------------------- ---------- --- --- ---\n 1813571 RP_DO_BRUNO 4 YES YES NO\n<\/pre><\/div>\n\n\nA tabela que usaremos como refer\u00eancia para o nosso teste possui 1 linha de conte\u00fado, conforme abaixo:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT * FROM BSS1.TESTE1;\n \nDESCRICAO\n--------------------\nFLASHBACK DO BRUNO\n<\/pre><\/div>\n\n\nPara simular uma situa\u00e7\u00e3o que justifique o flashback, vamos deletar o conte\u00fado da tabela:<\/p>\n\n\n
\nSQL> DELETE FROM BSS1.TESTE1;\n \n1 row deleted.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> SELECT * FROM BSS1.TESTE1;\n \nno rows selected\n<\/pre><\/div>\n\n\nBaixando o nosso PDB e realizando o seu respectivo Flashback:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> ALTER PLUGGABLE DATABASE HIPOFISE1 CLOSE IMMEDIATE;\n \nPluggable database altered.\n \nSQL> FLASHBACK PLUGGABLE DATABASE HIPOFISE1 TO RESTORE POINT RP_DO_BRUNO;\n \nFlashback complete.\n<\/pre><\/div>\n\n\nAbrindo apenas o PDB em resetlogs:<\/p>\n\n\n
\nSQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN RESETLOGS;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nValidando se o conte\u00fado da tabela est\u00e1 dispon\u00edvel:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT * FROM BSS1.TESTE1;\n \nDESCRICAO\n--------------------\nFLASHBACK DO BRUNO\n<\/pre><\/div>\n\n\nCaso o restore point n\u00e3o seja mais necess\u00e1rio, podemos realizar a sua exclus\u00e3o:<\/p>\n\n\n
\nSQL> DROP RESTORE POINT RP_DO_BRUNO;\n \nRestore point dropped.\n<\/pre><\/div>\n\n\n\u00c9 sempre indicado que ap\u00f3s este tipo de procedimento, seja realizado um backup full em todo o 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":"
Neste artigo, vamos reproduzir um Flashback Database de um PDB espec\u00edfico, configurado com Local UNDO, utilizando um restore point. Validando que nosso ambiente est\u00e1 com a Local Undo configurada: Garantindo um backup do nosso ambiente (tanto CDB quanto PDBs): Agora vamos criar um restore point apenas para nosso PDB chamado HIPOFISE1: Com a query abaixo, […]<\/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-4949","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\/4949","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=4949"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4949\/revisions"}],"predecessor-version":[{"id":9123,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4949\/revisions\/9123"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}