Recovering Data File in Primary Database from the standby database.

You have two approaches for data file restoration when your data file gets corrupted or deleted in data guard environment.

  1. Restore from RMAN backup
  2. Recover it from Standby Database.

In our routine, we use the first approach where we simply restore it using RMAN restore command and recover it through incremental backup or archive logs.

In the second approach, we will restore data file with help of Physical Standby database.

In this article, we will see steps for recovering data file in Primary Database using Standby database approach.

Approach 1: Restore From RMAN backup

We can simply restore it using RMAN restore and recover command.



Approach 2: Recover it from Standby Database : 

Let’s simulate this case :

Step 1: Create the tablespace

SQL> create tablespace test datafile '/u01/oracle/oradata/dbupgrade/test01.dbf' size 5m;

Tablespace created.

Step 2 : Create the table in test tablespace

SQL> create table a (no number) tablespace test;

Table created.

add some records to a table.

Step 3: Manually delete the physical file of this tablespace :

[oracle@localhost dbupgrade]$ rm -rfv test01.dbf 
removed `test01.dbf'

Step 4: Connect with RMAN using the target as Standby and Auxiliary as Primary :

[oracle@localhost dbupgrade]$ rman target sys/oracle@std_dbupgrade auxiliary sys/oracle@dbupgrade

Recovery Manager: Release - Production on Thu Jul 12 09:36:43 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBUPGRAD (DBID=1987568604)
connected to auxiliary database: DBUPGRAD (DBID=1987568604)

Step 5: Backup that datafile using the following command.


Starting backup at 12-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oracle/oradata/dbupgrade/test01.dbf
output file name=/u01/test01.dbf tag=TAG20180712T093756
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-JUL-18

Step 6: Now connect primary as target and catalog as a catalog database.

Note: You need catalog database for this approach.

[oracle@localhost admin]$ rman catalog rcatown/rcatown@std_upgdb

Recovery Manager: Release - Production on Thu Jul 12 10:35:21 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> connect target sys/oracle@std_dbupgrade;

connected to target database: DBUPGRAD (DBID=1987568604, not open)

Step 7: Catalog datafile backup

RMAN> catalog datafilecopy '/u01/test01.dbf';

Step 8: Run the following script to set newname for datafile and then switch it to the new name and then recover datafile.

RMAN> run{
set newname for datafile 5 to '/u01/test01.dbf';
switch datafile 5;
}2> 3> 4> 

executing command: SET NEWNAME

datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=981282541 file name=/u01/test01.dbf
starting full resync of recovery catalog
full resync complete

RMAN> recover datafile 5;

Starting recover at 12-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 12-JUL-18


Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel:

Skant Gupta’s LinkedIn:

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp


About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.