Recover Datafile in Standby Database in Data Guard Environment

In the previous article, we have to seen Recovering Primary Database Datafile using Standby Database if it’s not deleted from Standby Database.

Here we will see what if datafile gets corrupted or deleted from Standby Database.

Step 1: In the below code, we can see When I’m trying to query the job table it gives me the error, file# 5 is not accessible.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select * from job;
select * from job
              *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/oracle/oradata/testdb/STD_TESTDB/datafile/o1_mf_test_fnnx98k2_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

In this case shut down database and start it in a mount stage.

Step 2: start the database in mount stage

SQL> startup force mount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size		    2256192 bytes
Variable Size		  578814656 bytes
Database Buffers	   79691776 bytes
Redo Buffers		    3145728 bytes
Database mounted.
SQL>

Step 3: Restore datafile from backup

RMAN> restore datafile 5;

Starting restore at 15-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/oradata/testdb/STD_TESTDB/datafile/o1_mf_test_fnnx98k2_.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/STD_TESTDB/backupset/2018_07_15/o1_mf_nnndf_TAG20180715T034417_fnnxfsov_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/STD_TESTDB/backupset/2018_07_15/o1_mf_nnndf_TAG20180715T034417_fnnxfsov_.bkp tag=TAG20180715T034417
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-18

RMAN>

Step 4: If all archive logs after the restoration of a data file are present in default location then start MRP process.

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
TESTDB	  READ ONLY	       PHYSICAL STANDBY

SQL>  recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select * from  job;

	NO
----------
	 1
	 1
	 1
	 1
	 1
	 1
	 1
	 2
	 2
	 2
	 2
	 3
	 3
	 3
	 3

15 rows selected.

SQL>

We can see here after starting recovery we can access the job table.

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: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp