ORA-01111 and ORA-01110 on the STANDBY DATABASE

Hi,

Recently we built a STANDBY DATABASE for one of our production databases.

The STANDBY DATABASE was 10g with raw volumes

Issue Description

The recovery was stuck with the below error on the STANDBY DATABASE.

Alert Log

Errors in file /oracle/admin/CPD/bdump/CPD_mrp0_19746.trc:

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

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

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

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

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

Trace File

MRP0: Background Media Recovery terminated with error 1111

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

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

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

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

ORA-01110: data file 178: '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'

Troubleshooting

The error codes pointed out that it is a issue with missing file which is present in the control file but not present physically on the STANDBY host.

As this issue occurred later after we had successfully configured the STANDBY DATABASE, we were able to make out that this could have occured due to new raw volume addition on primary database which has not been provisioned on the STANDBY DATABASE side.

Hence at first we needed to find the number of files added on primary after the STANDBY DATABASE build. The below query listed the time the raw volumes were added to the database and hence we could figure out the raw volumes added to the database

set lines 150

set pages 0

col name for a75

select name,to_char(CREATION_TIME,'HH24:MI:SS MON-DD-YYYY') from v$datafile order
by creation_time;

We found that there were 2 files added to the database after we had created the STANDBY DATABASE.

Nextly we needed to find the exact raw volume which was needed by the STANDBY DATABASE to continue recovery.

From the error we were able to know that the STANDBY DATABASE needed the datafile with file number 178

We ran the below query on the primary database, which yielded us the exact name of the missing raw volume and the same query on the STANDBY side gave the psuedo name the STANDBY DATABASE had given to the missing file on the STANDBY DATABASE side. Hence confirming the exact raw volume name.

Primary Side

------------

SQL> select name from v$datafile where file#=178;

NAME

----------------------------------------

/dev/mapper/oraCPD_data_017_4600Mp9

STANDBY Side

-------

SQL> select name from v$datafile where file#=178;

NAME

----------------------------------------

/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178

Note: Though there were 2 files added to the production that was missing on STANDBY DATABASE side, the recovery was stuck at the very first missing file.

Hence we needed to correct only that file but for the issue not to occur again due to the other missing raw volume you need to make sure even that

raw volume is made available on the STANDBY DATABASE side.

(We got the all the missing raw volumes present on the primary side added to the STANDBY to avoid such issues in the future when there is new raw volume addition)

Solution

1. Make sure that all the missing raw volumes are added to the STANDBY side at host level.

2. Stop Recovery on the Standby Server

recover managed standby database cancel;

3. Change the standby_file_management parameter to MANUAL on STANDBY DATABASE

alter system set standby_file_management=manual;

4. Rename the datafile on Standby side

alter database create datafile '/oracle/product/10.2.0.4/CPD/dbs/UNNAMED00178'
as '/dev/mapper/oraCPD_data_017_4600Mp9';

5. Change the standby_file_management parameter to AUTO on STANDBY DATABASE

alter system set standby_file_management=auto;

6. Restart the Recovery

alter database recover managed standby database disconnect;

Regards,

Tanveer