Imaginemos que você precise realizar a recuperação de um banco de dados, e pense em usar a metodologia tradicional: restaurar os datafiles a partir de um backup e recuperá-los a partir dos Archives Redologs + ORLs. O que devemos considerar neste cenário é que, caso tenhamos um backup do tipo “Image Copy”, já disponível em algum local acessível no ambiente, podemos apenas realizar o “Switch” desses datafiles (ou seja, uma alteração interna no controlfile com o nome e localização nova dos datafiles em questão), além do seu respectivo recover. Isso pode economizar muito tempo de execução, principalmente por conta do fato de não ser necessário um restore. Neste artigo vamos simular esta situação.
Validando nosso ambiente laboratório:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Jul 28 20:22: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)
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/dados/users.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
Vamos realizar um backup as image copy do banco de dados:
RMAN> BACKUP AS COPY DATABASE;
Starting backup at 2021-07-28:20:24:31
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_jj3sshns_.dbf tag=TAG20210728T202431 RECID=60 STAMP=1079123093
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_jj3st8ld_.dbf tag=TAG20210728T202431 RECID=61 STAMP=1079123121
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
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_jj3svcoz_.dbf tag=TAG20210728T202431 RECID=62 STAMP=1079123146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
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_jj3svts5_.dbf tag=TAG20210728T202431 RECID=63 STAMP=1079123152
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
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__jj3sw1x6_.dbf tag=TAG20210728T202431 RECID=64 STAMP=1079123159
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oracle/dados/users.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.dbf tag=TAG20210728T202431 RECID=65 STAMP=1079123161
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 2021-07-28:20:26:02
Starting Control File and SPFILE Autobackup at 2021-07-28:20:26:02
piece handle=/oracle/fra/RMANDB/autobackup/2021_07_28/o1_mf_s_1079123162_jj3swc14_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-07-28:20:26:05
Removendo o datafile 7:
RMAN> host 'rm /oracle/dados/users.dbf';
host command complete
Realizando o validate do banco de dados, para que seja identificado o problema:
RMAN> host 'rm /oracle/dados/users.dbf';
host command complete
RMAN> ALTER SYSTEM SWITCH LOGFILE;
Statement processed
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-28:20:29:23
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/RESTORE/system.dbf
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 07/28/2021 20:29:24
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/oracle/dados/users.dbf'
ORA-01565: error in identifying file '/oracle/dados/users.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
A partir daqui, podemos deixar o datafile em questão com status OFFLINE:
RMAN> ALTER DATABASE DATAFILE 7 OFFLINE;
Statement processed
Como o nosso backup do tipo image copy está disponível na FRA, conseguimos apenas realizar o SWITCH deste datafile, conforme exemplo abaixo. Nesse caso, a referência dentro do controlfile será atualizada para considerar a nova localização e nome do datafile 7:
RMAN> SWITCH DATAFILE 7 TO COPY;
datafile 7 switched to datafile copy "/oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.dbf"
Depois disso, nos basta apenas recuperar o datafile e trazê-lo para ONLINE:
RMAN> RECOVER DATAFILE 7;
Starting recover at 2021-07-28:20:33:22
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2021-07-28:20:33:23
RMAN> ALTER DATABASE DATAFILE 7 ONLINE;
Statement processed
Validando o banco de dados:
RMAN> VALIDATE DATABASE;
Starting validate at 2021-07-28:20:34:02
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/RESTORE/system.dbf
input datafile file number=00003 name=/oracle/RESTORE/sysaux.dbf
input datafile file number=00004 name=/oracle/RESTORE/undo.tbs
input datafile file number=00005 name=/oracle/RESTORE/ts_cortex_catalog.dbf
input datafile file number=00002 name=/oracle/RESTORE/ts_hipo_catalog.dbf
input datafile file number=00007 name=/oracle/fra/RMANDB/datafile/o1_mf_users_jj3sw90n_.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 16529 112644 4162617
File Name: /oracle/RESTORE/system.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 74921
Index 0 13655
Other 0 7535
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 401 15361 2733896
File Name: /oracle/RESTORE/ts_hipo_catalog.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 93496 4162728
File Name: /oracle/RESTORE/sysaux.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11489
Index 0 10275
Other 0 55174
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 39047 4162728
File Name: /oracle/RESTORE/undo.tbs
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/RESTORE/ts_cortex_catalog.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_jj3sw90n_.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-07-28:20:34:18
Como previsto, o banco está 100%, utilizando o nosso datafile na FRA:
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
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
Pingback: Performing recovery of database loss by switching to image copies – Bruno Santos da Silva
Pingback: Performing recovery of database loss by switching to image copies – SWIV