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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | [oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX as sysdbaDGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 30 05:00:32 2021Version 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [oracle@fornix1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:01:38 2021Version 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 - ProductionVersion 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:
1 2 3 4 5 | 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:
1 2 3 | SQL> ALTER DATABASE DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562109' OFFLINE; Database altered. |
Deletando o datafile no primary:
1 2 3 4 5 6 7 8 9 | [oracle@fornix1 ~]$ su - gridPassword:Last login: Wed Jun 30 04:47:52 -03 2021[grid@fornix1 ~]$ asmcmdASMCMD> cd +DG_DATA/CORTEX/DATAFILE/ASMCMD> cp bss.269.1076562109 bss.269.1076562109.BACKUPcopying +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109 -> +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109.BACKUPASMCMD> rm bss.269.1076562109ASMCMD> |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [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 2021Version 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-21starting full resync of recovery catalogfull resync completeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=71 device type=DISK channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: using network backup set from service CORTEXDRchannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to +DG_DATA/CORTEX/DATAFILE/bss.269.1076562109channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 30-JUN-21starting full resync of recovery catalogfull 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:
1 2 3 4 5 6 7 8 9 | RMAN> RECOVER DATAFILE '+DG_DATA/CORTEX/DATAFILE/bss.269.1076562799'; Starting recover at 30-JUN-21using channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:01 Finished recover at 30-JUN-21 |
Deixando o datafile ONLINE novamente:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@fornix1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:23:57 2021Version 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 - ProductionVersion 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.
Portuguese
English
Spanish