Na arquitetura Multitenant também temos a possibilidade de realizar um PITR (Point-in-Time Recovery), que nos permite recuperar um ambiente em um ponto específico do passado, utilizando SCN, tempo (data/hora), Log Sequence Number ou um próprio restore point. Neste artigo vamos explorar na prática uma recuperação do CDB todo, considerando que o mesmo esteja no modo Archivelog e tenha os backups disponíveis para uso.
Realizando backup full do ambiente:
[oracle@quiasma ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Thu May 20 03:37:02 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=1340416544)
RMAN> BACKUP DATABASE;
Starting backup at 20-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
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/datafile/o1_mf_system_j9yzl519_.dbf
input datafile file number=00003 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wdsb2f_.dbf
input datafile file number=00004 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc1bk9_.dbf
input datafile file number=00007 name=/oracle/dados/ASWAN/datafile/o1_mf_users_j9wc1clt_.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-21
channel ORA_DISK_1: finished piece 1 at 20-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp tag=TAG20210520T033709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb463o46_.dbf
input datafile file number=00010 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_jb487ow0_.dbf
input datafile file number=00011 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
input datafile file number=00012 name=/oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-21
channel ORA_DISK_1: finished piece 1 at 20-MAY-21
piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp tag=TAG20210520T033709 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=00005 name=/oracle/dados/ASWAN/datafile/o1_mf_system_j9wc9px6_.dbf
input datafile file number=00006 name=/oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wc9px1_.dbf
input datafile file number=00008 name=/oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc9px7_.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-21
channel ORA_DISK_1: finished piece 1 at 20-MAY-21
piece handle=/oracle/fra/ASWAN/C2469670772D160AE0536A00A8C00FFA/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0xcsc_.bkp tag=TAG20210520T033709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-MAY-21
Starting Control File and SPFILE Autobackup at 20-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_20/o1_mf_s_1073014654_jbd0xh3m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-MAY-21
Criando uma tabela dentro do CDB$ROOT para usarmos como referência na recuperação:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu May 20 03:42:13 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> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> CREATE TABLE C##BSS.TESTE (DESCRICAO VARCHAR2(20));
Table created.
SQL> INSERT INTO C##BSS.TESTE (DESCRICAO) VALUES ('RPIT DO CDB');
1 row created.
SQL> COMMIT;
Commit complete.
Coletando o atual SCN, que será o parâmetro de recuperação que vamos utilizar neste artigo:
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2169033
Gerando alguns archives:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.
Executando backup dos archivelogs:
[oracle@quiasma ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Thu May 20 03:46:20 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=1340416544)
RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;
Starting backup at 20-MAY-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 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=6 RECID=1 STAMP=1072501770
input archived log thread=1 sequence=7 RECID=2 STAMP=1072501809
input archived log thread=1 sequence=8 RECID=3 STAMP=1072503370
input archived log thread=1 sequence=9 RECID=4 STAMP=1072503403
input archived log thread=1 sequence=10 RECID=5 STAMP=1072586228
input archived log thread=1 sequence=11 RECID=6 STAMP=1072586257
input archived log thread=1 sequence=12 RECID=7 STAMP=1072586635
input archived log thread=1 sequence=13 RECID=8 STAMP=1072586846
input archived log thread=1 sequence=14 RECID=9 STAMP=1072586861
input archived log thread=1 sequence=15 RECID=10 STAMP=1072587344
input archived log thread=1 sequence=16 RECID=11 STAMP=1072756615
input archived log thread=1 sequence=17 RECID=12 STAMP=1072756632
input archived log thread=1 sequence=18 RECID=13 STAMP=1072757472
input archived log thread=1 sequence=19 RECID=14 STAMP=1072757489
input archived log thread=1 sequence=20 RECID=15 STAMP=1072758669
input archived log thread=1 sequence=21 RECID=16 STAMP=1072758684
input archived log thread=1 sequence=22 RECID=17 STAMP=1072759485
input archived log thread=1 sequence=23 RECID=18 STAMP=1072759502
input archived log thread=1 sequence=24 RECID=19 STAMP=1073015120
input archived log thread=1 sequence=25 RECID=20 STAMP=1073015121
input archived log thread=1 sequence=26 RECID=21 STAMP=1073015122
input archived log thread=1 sequence=27 RECID=22 STAMP=1073015192
channel ORA_DISK_1: starting piece 1 at 20-MAY-21
channel ORA_DISK_1: finished piece 1 at 20-MAY-21
piece handle=/oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp tag=TAG20210520T034632 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oracle/archives/1_6_1072500770.dbf RECID=1 STAMP=1072501770
archived log file name=/oracle/archives/1_7_1072500770.dbf RECID=2 STAMP=1072501809
archived log file name=/oracle/archives/1_8_1072500770.dbf RECID=3 STAMP=1072503370
archived log file name=/oracle/archives/1_9_1072500770.dbf RECID=4 STAMP=1072503403
archived log file name=/oracle/archives/1_10_1072500770.dbf RECID=5 STAMP=1072586228
archived log file name=/oracle/archives/1_11_1072500770.dbf RECID=6 STAMP=1072586257
archived log file name=/oracle/archives/1_12_1072500770.dbf RECID=7 STAMP=1072586635
archived log file name=/oracle/archives/1_13_1072500770.dbf RECID=8 STAMP=1072586846
archived log file name=/oracle/archives/1_14_1072500770.dbf RECID=9 STAMP=1072586861
archived log file name=/oracle/archives/1_15_1072500770.dbf RECID=10 STAMP=1072587344
archived log file name=/oracle/archives/1_16_1072500770.dbf RECID=11 STAMP=1072756615
archived log file name=/oracle/archives/1_17_1072500770.dbf RECID=12 STAMP=1072756632
archived log file name=/oracle/archives/1_18_1072500770.dbf RECID=13 STAMP=1072757472
archived log file name=/oracle/archives/1_19_1072500770.dbf RECID=14 STAMP=1072757489
archived log file name=/oracle/archives/1_20_1072500770.dbf RECID=15 STAMP=1072758669
archived log file name=/oracle/archives/1_21_1072500770.dbf RECID=16 STAMP=1072758684
archived log file name=/oracle/archives/1_22_1072500770.dbf RECID=17 STAMP=1072759485
archived log file name=/oracle/archives/1_23_1072500770.dbf RECID=18 STAMP=1072759502
archived log file name=/oracle/archives/1_24_1072500770.dbf RECID=19 STAMP=1073015120
archived log file name=/oracle/archives/1_25_1072500770.dbf RECID=20 STAMP=1073015121
archived log file name=/oracle/archives/1_26_1072500770.dbf RECID=21 STAMP=1073015122
archived log file name=/oracle/archives/1_27_1072500770.dbf RECID=22 STAMP=1073015192
Finished backup at 20-MAY-21
Starting Control File and SPFILE Autobackup at 20-MAY-21
piece handle=/oracle/fra/ASWAN/autobackup/2021_05_20/o1_mf_s_1073015196_jbd1gdh6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-MAY-21
Baixando o CDB e montando-o:
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.
Definindo o valor do SCN que deve ser respeitado no restore e recover do ambiente:
RMAN> RUN {
SET UNTIL SCN 2169033;
RESTORE DATABASE;
RECOVER DATABASE;
}2> 3> 4> 5>
executing command: SET until clause
Starting restore at 20-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
skipping datafile 5; already restored to file /oracle/dados/ASWAN/datafile/o1_mf_system_j9wc9px6_.dbf
skipping datafile 6; already restored to file /oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wc9px1_.dbf
skipping datafile 8; already restored to file /oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc9px7_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/dados/ASWAN/datafile/o1_mf_system_j9yzl519_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/dados/ASWAN/datafile/o1_mf_sysaux_j9wdsb2f_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/dados/ASWAN/datafile/o1_mf_undotbs1_j9wc1bk9_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oracle/dados/ASWAN/datafile/o1_mf_users_j9wc1clt_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp tag=TAG20210520T033709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_system_jb463o46_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_sysaux_jb487ow0_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_undotbs1_j9wcyoh8_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oracle/dados/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/datafile/o1_mf_users_j9yz06wp_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/C246BD61C42A1E80E0536A00A8C076F1/backupset/2021_05_20/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp tag=TAG20210520T033709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-MAY-21
Starting recover at 20-MAY-21
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=24
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp tag=TAG20210520T034632
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/archives/1_24_1072500770.dbf thread=1 sequence=24
archived log file name=/oracle/archives/1_25_1072500770.dbf thread=1 sequence=25
archived log file name=/oracle/archives/1_26_1072500770.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=28
channel ORA_DISK_1: reading from backup piece /oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_annnn_TAG20210520T035017_jbd1o9lf_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/ASWAN/backupset/2021_05_20/o1_mf_annnn_TAG20210520T035017_jbd1o9lf_.bkp tag=TAG20210520T035017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 20-MAY-21
Abrindo o ambiente com a opção de Resetlogs, uma vez que utilizamos o RPIT:
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
Validando se o CDB e seus PDBs estão abertos:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu May 20 03:59:47 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> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
HIPOFISE1
READ WRITE
Validando se nossa tabela que foi recentemente criada aparece no banco:
SQL> SELECT * FROM C##BSS.TESTE;
DESCRICAO
--------------------
RPIT DO CDB
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.
Portuguese
English
Spanish