Performing a Flashback Database Operation for PDBs (with Local UNDO)

Backup & Recovery, Multitenant / 13/07/2022

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:

[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):

[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:

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:

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:

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:

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:

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:

SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN RESETLOGS;
 
Pluggable database altered.

Validando se o conteúdo da tabela está disponível:

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:

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish