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
—————