Neste artigo, vamos simular a perda de datafiles do nosso banco non-cdb (system e non-system datafiles) e a recuperação completa do banco de dados (que está rodando em archivelog mode). Vamos tomar conhecimento do nosso laboratório:
[oracle@oel8 datafile]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jul 24 00:07:03 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,LOG_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE LOG_MODE CDB
--------- -------------------- ------------ ---
RMANDB READ WRITE ARCHIVELOG NO
Realizando um backup Full do nosso banco de dados:
[oracle@oel8 datafile]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Sat Jul 24 00:07:39 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: RMANDB (DBID=3825250984)
RMAN> BACKUP DATABASE;
Starting backup at 2021-07-24:00:08:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 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/RMANDB/datafile/o1_mf_system_jhq037qr_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq037wr_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq037yg_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_jhq0380t_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-07-24:00:08:12
channel ORA_DISK_1: finished piece 1 at 2021-07-24:00:08:47
piece handle=/oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2021-07-24:00:08:47
Starting Control File and SPFILE Autobackup at 2021-07-24:00:08:47
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_24/o1_mf_s_1078704527_jhq11zgw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-24:00:08:48
Criando uma tabela simples após este backup, e gerando alguns archives:
RMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));
Statement processed
RMAN> INSERT INTO BSS.ASWAN (DESCRICAO) VALUES ('TESTE DO BRUNO');
Statement processed
RMAN> COMMIT;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
Realizando um backup dos archived redo logs (que serão armazenados em um backupset diferente do backup full). Após o backup desses arquivos, os mesmos serão removidos:
RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;
Starting backup at 2021-07-24:00:11: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=13 RECID=168 STAMP=1078703629
input archived log thread=1 sequence=14 RECID=172 STAMP=1078703629
input archived log thread=1 sequence=15 RECID=169 STAMP=1078703629
input archived log thread=1 sequence=16 RECID=170 STAMP=1078703629
input archived log thread=1 sequence=17 RECID=171 STAMP=1078703629
input archived log thread=1 sequence=18 RECID=173 STAMP=1078703724
input archived log thread=1 sequence=19 RECID=174 STAMP=1078704602
input archived log thread=1 sequence=20 RECID=175 STAMP=1078704605
input archived log thread=1 sequence=21 RECID=176 STAMP=1078704609
input archived log thread=1 sequence=22 RECID=177 STAMP=1078704682
channel ORA_DISK_1: starting piece 1 at 2021-07-24:00:11:22
channel ORA_DISK_1: finished piece 1 at 2021-07-24:00:11:23
piece handle=/oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp tag=TAG20210724T001122 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oracle/archives/1_13_1078693497.dbf RECID=168 STAMP=1078703629
archived log file name=/oracle/archives/1_14_1078693497.dbf RECID=172 STAMP=1078703629
archived log file name=/oracle/archives/1_15_1078693497.dbf RECID=169 STAMP=1078703629
archived log file name=/oracle/archives/1_16_1078693497.dbf RECID=170 STAMP=1078703629
archived log file name=/oracle/archives/1_17_1078693497.dbf RECID=171 STAMP=1078703629
archived log file name=/oracle/archives/1_18_1078693497.dbf RECID=173 STAMP=1078703724
archived log file name=/oracle/archives/1_19_1078693497.dbf RECID=174 STAMP=1078704602
archived log file name=/oracle/archives/1_20_1078693497.dbf RECID=175 STAMP=1078704605
archived log file name=/oracle/archives/1_21_1078693497.dbf RECID=176 STAMP=1078704609
archived log file name=/oracle/archives/1_22_1078693497.dbf RECID=177 STAMP=1078704682
Finished backup at 2021-07-24:00:11:23
Starting Control File and SPFILE Autobackup at 2021-07-24:00:11:23
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_24/o1_mf_s_1078704683_jhq16w12_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-24:00:11:24
Vamos simular a remoção dos datafiles do banco:
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RMANDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES /oracle/dados/RMANDB/datafile/o1_mf_system_jhq037qr_.dbf
2 120 TS_HIPO_CATALOG NO /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
3 730 SYSAUX NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq037wr_.dbf
4 305 UNDOTBS1 YES /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq037yg_.dbf
5 200 TS_CORTEX_CATALOG NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 16 USERS NO /oracle/dados/RMANDB/datafile/o1_mf_users_jhq0380t_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 69 TEMP 32767 /oracle/dados/RMANDB/datafile/o1_mf_temp_h8nyvt1f_.tmp
RMAN> host 'rm /oracle/dados/RMANDB/datafile/o1_mf_system_jhq037qr_.dbf /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf /oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq037wr_.dbf /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq037yg_.dbf /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf /oracle/dados/RMANDB/datafile/o1_mf_users_jhq0380t_.dbf';
host command complete
Baixando a instância e montando o banco:
SQL> SHU IMMEDIATE;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/dados/RMANDB/datafile/o1_mf_system_jhq037qr_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> SHU ABORT;
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1610612016 bytes
Fixed Size 8658224 bytes
Variable Size 520093696 bytes
Database Buffers 1073741824 bytes
Redo Buffers 8118272 bytes
Database mounted.
Antes de disparar o restore do banco, podemos validar a consistêcia do backup que será usado na operação através do comando abaixo. Obviamente, quanto maior o banco e seu backup, mais tempo esta validação durará.
RMAN> RESTORE DATABASE VALIDATE;
Starting restore at 2021-07-24:00:22:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2021-07-24:00:22:06
Disparando restore do banco:
RMAN> RESTORE DATABASE;
Starting restore at 2021-07-24:00:23:54
using channel ORA_DISK_1
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/RMANDB/datafile/o1_mf_system_jhq037qr_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/dados/RMANDB/datafile/ts_hipo_catalog.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/dados/RMANDB/datafile/o1_mf_sysaux_jhq037wr_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_jhq037yg_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oracle/dados/RMANDB/datafile/o1_mf_users_jhq0380t_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2021-07-24:00:24:29
Disparando comando de recover do banco. O interessante aqui é que, como deletamos os nossos archived redo logs, no processo de recover será realizado o restore desses arquivos para permitir a recuperação dos datafiles:
RMAN> RECOVER DATABASE;
Starting recover at 2021-07-24:00:28: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=19
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_07_24/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp tag=TAG20210724T001122
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_19_1078693497.dbf thread=1 sequence=19
archived log file name=/oracle/archives/1_20_1078693497.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 2021-07-24:00:28:23
Abrindo o banco e validando a nossa tabela de teste:
RMAN> ALTER DATABASE OPEN;
Statement processed
RMAN> SELECT * FROM BSS.ASWAN;
DESCRICAO
--------------------------------------------------
TESTE DO BRUNO
Como não fizemos a abertura do banco em resetlogs, o mesmo permanece com o seu ID incarnation:
RMAN> SELECT INCARNATION#, RESETLOGS_CHANGE#, PRIOR_RESETLOGS_CHANGE#, STATUS FROM V$DATABASE_INCARNATION ORDER BY 1;
INCARNATION# RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# STATUS
------------ ----------------- ----------------------- -------
1 1 0 PARENT
2 1477662 1 PARENT
3 3489797 1477662 CURRENT
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