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

Note:  In 12c datafiles can be restored over network using backup sets/ compressed backupsets as shown in this post.

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

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
How to reinstate the old primary as a standby after failover
12c Dataguard: Restore Datafile From Service

—————

7 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

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

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

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

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

Leave a Reply to Anju Garg Cancel reply