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

Fore more info on dataguard please visit.

https://docs.oracle.com/cd/B19306_01/server.102/b14239/scenarios.htm#i1008082