Data Guard Physical Standby Missing File tips

Introduction – Physical and Logical Standby

A Physical standby database is an exact copy of the primary database. It is always kept in a managed recovery mode and is unusable as long as primary is up and functional. 

The prominent difference with a logical standby database is that the latter is not an exact replica of the primary database. A logical standby can be a subset or a superset of the primary and is a fully operational database used for reporting etc. Unlike a physical standby, tables in logical standby can be queries also. 

In this particular scenario we are using a physical standby database where some datafiles are missing causing the managed recovery process to stop and hence forcing it to get out of sync with primary database.

Environment

Let us review the db environment which we are going to use for demonstrating this scenario.

1. Primary has 200 datafiles and standby has only 166 datafiles

2. Primary is a 3 node cluster and Standby is a 2 node cluster.

3. The DB name is DCPROC

4. MRP on standby is not running

Problem and Symptoms

Here is a detailed description of the actual problem and symptoms/indications which helped us choose the appropriate corrective measures.

1. On discovering that physical standby is out of sync, when we tried to start the MRP on standby, it reported the following error in alert log:

Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_21189.trc

ORA-01111: name for data file 167 is unknown - rename to correct file

"ORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'

ORA-01157: cannot identify/lock data file 167 - see DBWR trace file

ORA-01111: name for data file 167 is unknown - rename to correct file

ORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'

2. On further investigation, standby’s alert log also shows following errors:

Tue Sep 9 04:05:03 2008

Media Recovery Log /u03/oradata/mydb/arc_backup/mydb_2_2173.arc

Media Recovery Log /u03/oradata/mydb/arc_backup/mydb_1_1896.arc

WARNING: File being created with same name as in Primary

Existing file may be overwritten

File #167 added to control file as 'UNNAMED00167'. Originally created as:

'/u07/oradata/mydb/myfile_1.dbf'

Recovery was unable to create the file as: '/u07/oradata/mydb/myfile_1.dbf'

MRP0: Background Media Recovery terminated with error 1274

Tue Sep 9 04:05:06 2008

Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_7175.trc:

ORA-01274: cannot add datafile '/u07/oradata/mydb/myfile_1.dbf' - file could not be created

ORA-01119: error in creating database file '/u07/oradata/mydb/myfile_1.dbf'

ORA-27054: Message 27054 not found; product=RDBMS; facility=ORA

Linux-x86_64 Error: 13: Permission denied

3. On checking the view v$archived_log, there were lot of log sequence# which were APPLIED=NO

4. Also note that there is no gap in the sequence#

What caused the missing datafile(s) condition on Standby?

Parameter db_file_name_convert was not set at standby database. So as long as the files were created on /u02 and /u03 on primary, there was no problem on the standby because standby had /u02 and /u03. But when file#167 was added at /u07 on primary (on Sep 9 04:05:03 2008), it could not map to a /u07 mount point on standby because /u07 does not exists on standby and db_file_name_convert was also not set. As indicated by the alert log, the file#167 was registered in the standby’s control file as “UNANMED00167” at the default location of $ORACLE_HOME/dbs but the file was not created physically on standby database.

Action Plan: How to resolve this

1. At the standby:

Please set the db_file_name_convert parameter at the Standby for the /u07 folder at the Primary to the corresponding folder at the Standby.

Since this parameter is a Static parameter, you need to bounce the Standby DB.

As step#1, you can do following instead of the above step:

At the standby:

Create /u07 soft link for /u02, to eliminate the bounce of standby db due to the addition of db_file_name_convert init.ora parameter

2. At the standby :

SQL> alter system set standby_file_management=manual;

3. At the Primary for the datafile 167 :

SQL> alter tablespace <tablespace name> begin backup;

Copy the Datafile from the Primary to Standby to the correct location.

SQL> Alter tablespace <tablespace name> end backup;

4. At the Standby:

SQL> alter database rename file '.......UNNAMED00167' to '< actual location of the datafile >';     

You can skip steps#3 and #4 and instead do following step after #2:

At the Standby:

SQL> ALTER DATABASE CREATE DATAFILE '< ....UNNAMED00167>' as '<datafile name with the correct path>';

To create the remaining datafiles at the Standby automatically:

SQL> alter system set standby_file_management=auto;

Start the MRP at the Standby

SQL> alter database recover managed standby database;

At standby database ensure the MRP is running as expected

SQL> select process, status, sequence# from v$managed_standby;

Word of Caution: Prevent this from happening again

Before adding datafiles on the primary, make sure:

1. The corresponding mount point exists on the standby

2. Or there should be an appropriate mapping between the primary’s and standby’s mount points using the parameter db_file_name_convert     

3. Or create a soft link on standby server with the same name as that of primary’s mount point if it does not exist on the standby.

Reference:

http://www.dba-oracle.com/t_data_guard_physical_standby_missing_file_tips.html