Performing recovery of database loss by switching to image copies

Backup & Recovery / 15/07/2022

No último artigo (link AQUI), simulamos um caso onde usamos o switch de um datafile específico para agilizar o processo de recuperação de um ambiente (ou seja, nos bastidores o restore não era necessário, uma vez que o backup do tipo image copy era utilizado e era apenas necessário a recuperação do datafile com o comando de recover). Hoje, vamos realizar o mesmo processo, mas agora do banco de dados como um todo. Esse cenário economiza o tempo de restore, e pode ser uma boa estratégia para o negócio.

Reconhecendo o nosso banco de dados:

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 2 20:36:58 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> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
 
using target database control file instead of recovery catalog
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
RMANDB    READ WRITE           ARCHIVELOG
 
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/RESTORE/system.dbf
2    120      TS_HIPO_CATALOG      NO      /oracle/RESTORE/ts_hipo_catalog.dbf
3    730      SYSAUX               NO      /oracle/RESTORE/sysaux.dbf
4    305      UNDOTBS1             YES     /oracle/RESTORE/undo.tbs
5    200      TS_CORTEX_CATALOG    NO      /oracle/RESTORE/ts_cortex_catalog.dbf
7    16       USERS                NO      /oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.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

Realizando um backup as copy do banco de dados inteiro:

RMAN> BACKUP AS COPY DATABASE;
 
Starting backup at 2021-08-02:20:41:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/RESTORE/system.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_system_jjk0o792_.dbf tag=TAG20210802T204127 RECID=66 STAMP=1079556103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_sysaux_jjk0p0g4_.dbf tag=TAG20210802T204127 RECID=67 STAMP=1079556130
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
output file name=/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_jjk0pskk_.dbf tag=TAG20210802T204127 RECID=68 STAMP=1079556143
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_jjk0q0oo_.dbf tag=TAG20210802T204127 RECID=69 STAMP=1079556155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_ts_hipo__jjk0qhw7_.dbf tag=TAG20210802T204127 RECID=70 STAMP=1079556167
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_users_jjk0qz0o_.dbf tag=TAG20210802T204127 RECID=71 STAMP=1079556175
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 2021-08-02:20:42:56
 
Starting Control File and SPFILE Autobackup at 2021-08-02:20:42:56
piece handle=/oracle/fra/RMANDB/autobackup/2021_08_02/o1_mf_s_1079556176_jjk0r11y_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-08-02:20:42:59

Para reproduzirmos um problema que exige recuperação, vamos deletar os datafiles do nosso banco direto no S.O:

RMAN> host 'rm /oracle/RESTORE/system.dbf /oracle/RESTORE/ts_hipo_catalog.dbf /oracle/RESTORE/sysaux.dbf /oracle/RESTORE/undo.tbs /oracle/RESTORE/ts_cortex_catalog.dbf /oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.dbf';
 
host command complete

Com o comando VALIDATE DATABASE, já podemos notar que o problema foi reconhecido:

RMAN> VALIDATE DATABASE;
 
Starting validate at 2021-08-02:20:45:14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 08/02/2021 20:45:14
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oracle/RESTORE/system.dbf'
ORA-01565: error in identifying file '/oracle/RESTORE/system.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Vamos baixar a instância e montar o banco de dados:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Aug 2 20:46:17 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> SHU IMMEDIATE;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/RESTORE/system.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.
SQL>

Com um único comando RMAN, conseguimos rotear os datafiles que antes eram os nossos backups as image copy para que se tornem os datafiles oficiais do ambiente. Ou seja, poupamos aqui a etapa de restore dos mesmos, uma vez que o RMAN alterará no controlfile o novo nome e diretório dos datafiles:

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 2 20:49:12 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, not open)
 
RMAN> SWITCH DATABASE TO COPY;
 
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_system_jjk0o792_.dbf"
datafile 2 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_ts_hipo__jjk0qhw7_.dbf"
datafile 3 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_sysaux_jjk0p0g4_.dbf"
datafile 4 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_jjk0pskk_.dbf"
datafile 5 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_jjk0q0oo_.dbf"
datafile 7 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_users_jjk0qz0o_.dbf"

A partir daqui, é o caminho padrão: fazemos o recover dos datafiles com os vetores de alteração de eventuais archived redo logs e Online Redo Logs:

RMAN> RECOVER DATABASE;
 
Starting recover at 2021-08-02:20:50:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
 
starting media recovery
media recovery complete, elapsed time: 00:00:01
 
Finished recover at 2021-08-02:20:50:22

Abertura do banco e validação do mesmo:

RMAN> ALTER DATABASE OPEN;
 
Statement processed
 
RMAN> VALIDATE DATABASE;
 
Starting validate at 2021-08-02:20:50:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/fra/RMANDB/datafile/o1_mf_system_jjk0o792_.dbf
input datafile file number=00003 name=/oracle/fra/RMANDB/datafile/o1_mf_sysaux_jjk0p0g4_.dbf
input datafile file number=00004 name=/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_jjk0pskk_.dbf
input datafile file number=00005 name=/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_jjk0q0oo_.dbf
input datafile file number=00002 name=/oracle/fra/RMANDB/datafile/o1_mf_ts_hipo__jjk0qhw7_.dbf
input datafile file number=00007 name=/oracle/fra/RMANDB/datafile/o1_mf_users_jjk0qz0o_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              16514        112644          4489478
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_system_jjk0o792_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              74956
  Index      0              13665
  Other      0              7505
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              401          15361           2733896
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_ts_hipo__jjk0qhw7_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              150
  Index      0              98
  Other      0              14711
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              16502        93504           4489519
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_sysaux_jjk0p0g4_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              11620
  Index      0              10560
  Other      0              54758
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              1            39047           4489478
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_undotbs1_jjk0pskk_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              39039
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              505          25601           2790924
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_ts_corte_jjk0q0oo_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              175
  Index      0              135
  Other      0              24785
 
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              120          2101            3924688
  File Name: /oracle/fra/RMANDB/datafile/o1_mf_users_jjk0qz0o_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1440
  Index      0              19
  Other      0              501
 
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              722
Finished validate at 2021-08-02:20:51:11
 
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/fra/RMANDB/datafile/o1_mf_system_jjk0o792_.dbf
2    120      TS_HIPO_CATALOG      NO      /oracle/fra/RMANDB/datafile/o1_mf_ts_hipo__jjk0qhw7_.dbf
3    730      SYSAUX               NO      /oracle/fra/RMANDB/datafile/o1_mf_sysaux_jjk0p0g4_.dbf
4    305      UNDOTBS1             YES     /oracle/fra/RMANDB/datafile/o1_mf_undotbs1_jjk0pskk_.dbf
5    200      TS_CORTEX_CATALOG    NO      /oracle/fra/RMANDB/datafile/o1_mf_ts_corte_jjk0q0oo_.dbf
7    16       USERS                NO      /oracle/fra/RMANDB/datafile/o1_mf_users_jjk0qz0o_.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

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