Performing Flashback Database for whole CDB

Backup & Recovery, Multitenant / 13/07/2022

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ápido que aplicar um PITR. Na arquitetura Multitenant, temos a opção de aplicar este processo a nível de CDB ou em algum PDB específico. Neste artigo vou explorar o primeiro caso, utilizando o target point como SCN (já que temos as opções de usar também um PDB restore point, CDB restore point, um por expressão de data/hora).

Realizando um backup do CDB e seus 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
[oracle@quiasma dbs]$ rman target /
  
Recovery Manager: Release 18.0.0.0.0 - Production on Sat May 29 04:37:34 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=65 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
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_TAG20210529T043744_jc3vt88r_.bkp tag=TAG20210529T043744 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_TAG20210529T043745_jc3vt9nn_.bkp tag=TAG20210529T043745 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_TAG20210529T043745_jc3vtjmf_.bkp tag=TAG20210529T043745 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_TAG20210529T043745_jc3vtmpg_.bkp tag=TAG20210529T043745 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
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 29-MAY-21
  
Starting Control File Autobackup at 29-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_29/o1_mf_n_1073795882_jc3vtv2g_.bkp comment=NONE
Finished Control File Autobackup at 29-MAY-21

Para a validação do Flashback, vamos utilizar um common user como referência. Vou criá-lo nesse momento em todo o ambiente:

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 04:39:10 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> CREATE USER C##LUXOR IDENTIFIED BY oracle CONTAINER=ALL;
  
User created.
  
SQL> GRANT CREATE SESSION TO C##LUXOR CONTAINER=ALL;
  
Grant succeeded.

Validando que a FRA (Fast Recovery Area) está habilitada:

1
2
3
4
5
6
SQL> SHO PARAMETER DB_RECOVERY_FILE_DEST;
  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/fra
db_recovery_file_dest_size           big integer 10512M

Validando se o Flashback está habilitado:

1
2
3
4
5
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
  
FLASHBACK_ON
------------------
NO

Como precisaremos habilitar o Flashback, o processo exige o restart do CDB, conforme demonstrado abaixo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
  
Total System Global Area 2466249080 bytes
Fixed Size                  8898936 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;
  
System altered.
  
SQL> ALTER DATABASE FLASHBACK ON;
  
Database altered.
  
SQL> ALTER DATABASE OPEN;
  
Database altered.

Coletando o SCN do banco:

1
2
3
4
5
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
  
CURRENT_SCN
-----------
    1810936

Agora vamos dropar o nosso common user para simular a situação que justifique o flashback:

1
2
3
SQL> DROP USER C##LUXOR CASCADE;
  
User dropped.

Gerando alguns archives:

1
2
3
4
5
6
7
8
9
10
11
SQL> ALTER SYSTEM SWITCH LOGFILE;
  
System altered.
  
SQL> /
  
System altered.
  
SQL> /
  
System altered.

Agora podemos baixar o ambiente e montar o banco, para proceder com o Flashback, utilizando o SCN que coletamos:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
  
Total System Global Area 2466249080 bytes
Fixed Size                  8898936 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 1810936;
  
Flashback complete.

Abrindo o banco em read only para validar se o nosso common user está disponível:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> ALTER DATABASE OPEN READ ONLY;
  
Database altered.
  
SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;
  
Pluggable database altered.
  
SQL> SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##LUXOR';
  
USERNAME
--------------------------------------------------------------------------------
COM     CON_ID
--- ----------
C##LUXOR
YES          1
  
C##LUXOR
YES          4

Uma vez validado, podemos abrir o banco em resetlogs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
  
Total System Global Area 2466249080 bytes
Fixed Size                  8898936 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;
  
Database altered.

Sempre é recomendado que, após este tipo de procedimento, realizarmos um backup full do 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