Neste artigo, vamos reproduzir um Flashback Database de um PDB específico, configurado com Local UNDO, utilizando um restore point.
Validando que nosso ambiente está com a Local Undo configurada:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [oracle@quiasma dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Sat May 29 05:40:37 2021 Version 18.13.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.13.0.0.0 SQL> col PROPERTY_NAME format a25 SQL> col PROPERTY_VALUE format a10 SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_V ------------------------- ---------- LOCAL_UNDO_ENABLED TRUE |
Garantindo um backup do nosso ambiente (tanto CDB quanto PDBs):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | [oracle@quiasma dbs]$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Sat May 29 05:43:26 2021 Version 18.13.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ASWAN (DBID=1341162082) RMAN> BACKUP DATABASE PLUS ARCHIVELOG; Starting backup at 29-MAY-21 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=77 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=37 RECID=32 STAMP=1073452633 input archived log thread=1 sequence=38 RECID=33 STAMP=1073452646 input archived log thread=1 sequence=39 RECID=34 STAMP=1073452661 input archived log thread=1 sequence=40 RECID=35 STAMP=1073452739 input archived log thread=1 sequence=41 RECID=36 STAMP=1073452740 input archived log thread=1 sequence=42 RECID=37 STAMP=1073452787 input archived log thread=1 sequence=43 RECID=38 STAMP=1073795863 input archived log thread=1 sequence=44 RECID=39 STAMP=1073795882 input archived log thread=1 sequence=45 RECID=40 STAMP=1073796975 channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/backupset/2021_05_29/o1_mf_annnn_TAG20210529T054334_jc3zopml_.bkp tag=TAG20210529T054334 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=43 STAMP=1073799814 channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/backupset/2021_05_29/o1_mf_annnn_TAG20210529T054334_jc3zoqoq_.bkp tag=TAG20210529T054334 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=46 RECID=41 STAMP=1073796976 input archived log thread=1 sequence=47 RECID=42 STAMP=1073796977 channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/backupset/2021_05_29/o1_mf_annnn_TAG20210529T054334_jc3zorr0_.bkp tag=TAG20210529T054334 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 29-MAY-21 Starting backup at 29-MAY-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_system_jbm31j11_.dbf input datafile file number=00003 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_sysaux_jbm32m4k_.dbf input datafile file number=00004 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_undotbs1_jbm33d7s_.dbf input datafile file number=00007 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_users_jbm33f9w_.dbf channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/backupset/2021_05_29/o1_mf_nnndf_TAG20210529T054337_jc3zosyk_.bkp tag=TAG20210529T054337 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00013 name=/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_system_jbs98k3r_.dbf input datafile file number=00014 name=/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_sysaux_jbs98k3v_.dbf input datafile file number=00015 name=/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_undotbs1_jbs98k3w_.dbf input datafile file number=00016 name=/oracle/dados/ASWAN/ASWAN/C32359B357110BDFE0536A00A8C0F81A/datafile/o1_mf_users_jbsg5qtv_.dbf channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/C32359B357110BDFE0536A00A8C0F81A/backupset/2021_05_29/o1_mf_nnndf_TAG20210529T054337_jc3zp12p_.bkp tag=TAG20210529T054337 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_system_jbm3c7f1_.dbf input datafile file number=00006 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_sysaux_jbm3c7dv_.dbf input datafile file number=00008 name=/oracle/dados/ASWAN/ASWAN/datafile/o1_mf_undotbs1_jbm3c7f2_.dbf channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/C2F42AF0A5944E90E0536A00A8C038BD/backupset/2021_05_29/o1_mf_nnndf_TAG20210529T054337_jc3zp45g_.bkp tag=TAG20210529T054337 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 29-MAY-21 Starting backup at 29-MAY-21 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=2 RECID=44 STAMP=1073799835 channel ORA_DISK_1: starting piece 1 at 29-MAY-21 channel ORA_DISK_1: finished piece 1 at 29-MAY-21 piece handle=/oracle/fra/ASWAN/backupset/2021_05_29/o1_mf_annnn_TAG20210529T054355_jc3zpcd3_.bkp tag=TAG20210529T054355 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 29-MAY-21 Starting Control File and SPFILE Autobackup at 29-MAY-21 piece handle=/oracle/fra/ASWAN/autobackup/2021_05_29/o1_mf_s_1073799836_jc3zpdlt_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 29-MAY-21 |
Agora vamos criar um restore point apenas para nosso PDB chamado HIPOFISE1:
1 2 3 | SQL> CREATE RESTORE POINT RP_DO_BRUNO FOR PLUGGABLE DATABASE HIPOFISE1 GUARANTEE FLASHBACK DATABASE; Restore point created. |
Com a query abaixo, conseguimos consultar o restore point existente:
1 2 3 4 5 6 | SQL> col name format a20 SQL> SELECT SCN, NAME, CON_ID, PDB_RESTORE_POINT, GUARANTEE_FLASHBACK_DATABASE, CLEAN_PDB_RESTORE_POINT FROM V$RESTORE_POINT; SCN NAME CON_ID PDB GUA CLE ---------- -------------------- ---------- --- --- --- 1813571 RP_DO_BRUNO 4 YES YES NO |
A tabela que usaremos como referência para o nosso teste possui 1 linha de conteúdo, conforme abaixo:
1 2 3 4 5 6 7 8 9 | SQL> ALTER SESSION SET CONTAINER=HIPOFISE1; Session altered. SQL> SELECT * FROM BSS1.TESTE1; DESCRICAO -------------------- FLASHBACK DO BRUNO |
Para simular uma situação que justifique o flashback, vamos deletar o conteúdo da tabela:
1 2 3 4 5 6 7 8 9 10 11 | SQL> DELETE FROM BSS1.TESTE1; 1 row deleted. SQL> COMMIT; Commit complete. SQL> SELECT * FROM BSS1.TESTE1; no rows selected |
Baixando o nosso PDB e realizando o seu respectivo Flashback:
1 2 3 4 5 6 7 8 9 | SQL> conn / as sysdba Connected. SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 CLOSE IMMEDIATE; Pluggable database altered. SQL> FLASHBACK PLUGGABLE DATABASE HIPOFISE1 TO RESTORE POINT RP_DO_BRUNO; Flashback complete. |
Abrindo apenas o PDB em resetlogs:
1 2 3 | SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN RESETLOGS; Pluggable database altered. |
Validando se o conteúdo da tabela está disponível:
1 2 3 4 5 6 7 8 9 | SQL> ALTER SESSION SET CONTAINER=HIPOFISE1; Session altered. SQL> SELECT * FROM BSS1.TESTE1; DESCRICAO -------------------- FLASHBACK DO BRUNO |
Caso o restore point não seja mais necessário, podemos realizar a sua exclusão:
1 2 3 | SQL> DROP RESTORE POINT RP_DO_BRUNO; Restore point dropped. |
É sempre indicado que após este tipo de procedimento, seja realizado um backup full em todo o ambiente.
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.