In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.
Overview:
— Simulate loss of a datafile on standby database by renaming it.
— Restart standby database – Stops at mount stage as datafile is inaccessible.
— Check that redo apply to standby is stopped.
— Connect to primary database as target and standby as auxiliary.
— Take backup of the affected datafile from primary so that backup file is created on standby.
— Recover standby database.
— Open standby database.
— check that redo apply has been restrated on standby and configuration is successful again.
– Implementation –
– Check that dataguard configuration is running properly
DGMGRL> show configuration;
Configuration - dgconfig1
Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
– Find out names of datafiles on standby
SBY>select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/dg02/system01.dbf /u01/app/oracle/oradata/dg02/sysaux01.dbf /u01/app/oracle/oradata/dg02/undotbs01.dbf /u01/app/oracle/oradata/dg02/users01.dbf /u01/app/oracle/oradata/dg02/example01.dbf
– To simulate loss of the datafile of example tablespace , rename it
SBY>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf
– Restart standby database
— Stops at mount stage due to missing datafile
SBY>startup force; ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes Database mounted. ORA-10458: standby database requires recovery ORA-01196: file 1 is inconsistent due to a failed media recovery session ORA-01110: data file 1: '/u01/app/oracle/oradata/dg02/system01.dbf'
-- Switch logs on primary and verify that redo apply has stopped on standby
PRI>alter system switch logfile; DGMGRL> show configuration; Configuration - dgconfig1 Protection Mode: MaxPerformance Databases: dg01 - Primary database dg02 - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configuration Status: ERROR
– Using RMAN, connect to primary as target and standby as auxiliary
[oracle@node1 ~]$ . oraenv ORACLE_SID = [dg01] ? The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle [oracle@node1 ~]$ rman target / auxiliary sys/oracle@dg02 connected to target database: DG01 (DBID=434142737) connected to auxiliary database: DG01 (DBID=434142737, not open) RMAN>
– Try to take backupset type of backup of example tablespace on primary so that backup file is created on standby
— Fails as only image copies can be transported over network using RMAN
RMAN> backup tablespace example auxiliary format 2> '/u01/app/oracle/oradata/dg02/example01.dbf'; Starting backup at 26-DEC-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=48 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 12/26/2013 14:37:04 RMAN-06955: Network copies are only supported for image copies.
– Take image copy backup of example tablespace on primary so that backup file is created on standby
RMAN> backup as copy tablespace example auxiliary format 2> '/u01/app/oracle/oradata/dg02/example01.dbf'; Starting backup at 26-DEC-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/dg01/example01.dbf output file name=/u01/app/oracle/oradata/dg02/example01.dbf tag=TAG20131226T143727 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 26-DEC-13
– check that image copy copy has been created on standby
SBY>ho ls /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example01.dbf
– Recover standby database and open it
SBY>recover managed standby database disconnect; alter database open;
– Check that redo apply is resumed again and configuration is successful
DGMGRL> show configuration; Configuration - dgconfig1 Protection Mode: MaxPerformance Databases: dg01 - Primary database dg02 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Note: In 12c datafiles can be restored over network using backup sets/ compressed backupsets as shown in this post.
—————————————————————————————————–
Related links:
11g DataGuard: Flashback Standby After Resetlogs On Primary11g DataGuard Setup Using Active DataGuard on RHEL/OEL- 5
Flashback Through Role Transition For Physical Standby
How to reinstate the old primary as a standby after failover
12c Dataguard: Restore Datafile From Service
—————
After you backup the image copy in the standby database, don’t you need to apply the comman ” Sitch datafile file_name to copy” ?
Hi Sharif,
Switch datafile to copy is needed if you want to switch to image copy backup as datafile. Since location of image copy is different from current datafile location , the controlfile needs to be updated with new datafile location and for that switch … command is required. In this scenario, since we have restored backup to current datafile location, switch .. is not required.
Hope it helps
Anju Garg
Anju,
Very good post ..last time when i created datafile in standby manually and recovered with lot of efforts …If i face the same issue again surely it will use this best approach..
One doubt for me why you opened the database in open mode
SBY>recover managed standby database disconnect;
alter database open;
Is it snapshot standby ?
Thanks.
Chakradhar
Chakradhar,
Thanks for your time and feedback.
We can open even a physical standby. In case of active dataguard, recovery is applied even in open mode.
Regards
Anju Garg
Anju,
Actually i learnt it can be opened in read only for active dataguard? can it also opened in read & write also with active dataguard?
Chakradhar,
You are correct, Physical standby can be opened in read only mode. When you issue the command to open it as Alter Database open, the physical standby is automatically opened in read only mode only.
regards
Anju
Thanks once again….got it now .. I was confused
