Recover Corrupted datafile on standby

In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.

Overview:

1.Simulate loss of a datafile on standby database by renaming it.
2.Restart standby database – Stops at mount stage as datafile is inaccessible.
3.Check that redo apply to standby is stopped.
4.Connect to primary database as target and standby as auxiliary.
5.Take backup of the affected datafile from primary so that backup file is created on standby.
6.Recover standby database and open it.
7.check that redo apply has been restarted on standby and configuration is successful again.

Implementation:

Step 1

Find out names of datafiles on standby

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

SYS>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf

Step 2

Restart standby database

Stops at mount stage due to missing datafile

SYS> 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'

Step 3:

Switch logs on primary and verify that redo apply has stopped on standby

SYS> alter system switch logfile;

SYS> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Step 4:

Using RMAN, connect to primary as target and standby as auxiliary

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [DCPROC] ?

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@DRPROC

connected to target database: DG01 (DBID=434142737)

connected to auxiliary database: DG01 (DBID=434142737, not open)

Step 5:

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 '/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 has been created on standby

SYS>ho ls /u01/app/oracle/oradata/dg02/example01.dbf

/u01/app/oracle/oradata/dg02/example01.dbf

Step 6:

Recover standby database and open it

SYS> recover managed standby database disconnect;

SYS> alter database open;

Check that redo apply is resumed again and configuration is successful

SYS> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;