Recovering a (primary) missing datafile from the standby database

Neste artigo, vamos reproduzir um recurso interessante, de podermos recuperar um datafile perdido no banco Primary a partir do banco Standby, economizando assim o tempo para normalizar o ambiente.

Observando o nosso Data Guard de laboratório:

[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 30 05:00:32 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxPerformance
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 50 seconds ago)
 
DGMGRL> SHOW DATABASE CORTEX;
 
Database - cortex
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    cortex
 
Database Status:
SUCCESS
 
DGMGRL> SHOW DATABASE CORTEXDR;
 
Database - cortexdr
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 64.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    CORTEXDR
 
Database Status:
SUCCESS
 
DGMGRL>

No banco de dados Primary, vou criar uma nova tablespace chamada BSS, e gerar alguns archivelogs:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:01:38 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> CREATE TABLESPACE BSS;
 
Tablespace created.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> /
 
System altered.
 
SQL>

Verificando o nome do datafile criado na tablespace em questão:

SQL> select name from v$datafile where upper(name) like upper('%BSS%');
 
NAME
--------------------------------------------------------------------------------
+DG_DATA/CORTEX/DATAFILE/bss.269.1076562109

Colocando o datafile como offline no Primary Database:

SQL> ALTER DATABASE DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562109' OFFLINE;
 
Database altered.

Deletando o datafile no primary:

[oracle@fornix1 ~]$ su - grid
Password:
Last login: Wed Jun 30 04:47:52 -03 2021
[grid@fornix1 ~]$ asmcmd
ASMCMD> cd +DG_DATA/CORTEX/DATAFILE/
ASMCMD> cp bss.269.1076562109 bss.269.1076562109.BACKUP
copying +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109 -> +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109.BACKUP
ASMCMD> rm bss.269.1076562109
ASMCMD>

A parte legal é que, caso tenhamos nosso ambiente Data Guard (Physical Standby) sincronizado, podemos utilizá-lo para recuperar este nosso datafile perdido no primary. Basta conectarmos no primary e emitir o comando de exemplo abaixo:

[oracle@fornix1 ~]$ rman target sys/oracle@CORTEX catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 30 05:12:38 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> RESTORE DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562109' FROM SERVICE CORTEXDR;
 
Starting restore at 30-JUN-21
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CORTEXDR
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-JUN-21
starting full resync of recovery catalog
full resync complete
 
RMAN>

Devido o OMF, o nome do datafile novo é diferente, e desse modo podemos pegar este nome conforme abaixo, para procedermos com o seu recover:

RMAN> RECOVER DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562799';
 
Starting recover at 30-JUN-21
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:01
 
Finished recover at 30-JUN-21

Deixando o datafile ONLINE novamente:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:23:57 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER DATABASE DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562799' ONLINE;
 
Database altered.

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 Cancel Reply