RECOVER STANDBY DATAFILE FROM PRIMARY

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

—————————————————————————————————–

Related links:

Home
11g Dataguard Index

11g  DataGuard: Flashback Standby After Resetlogs On Primary11g  DataGuard Setup Using Active DataGuard on RHEL/OEL- 5 
Flashback Through Role Transition For Physical Standby

 

—————

2 thoughts on “RECOVER STANDBY DATAFILE FROM PRIMARY

  1. After you backup the image copy in the standby database, don’t you need to apply the comman ” Sitch datafile file_name to copy” ?

    1. 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

Your comments and suggestions are welcome!