Bad filename convert on a physical standby

If a data file is added on a primary with a physical standby and there is no appropriate file name convert, the file gets created in the Oracle home. On a RAC this may also report as a certain node unable to lock a file as the file got created in the Oracle home (non ASM) of a different node.

Log on to the standby

Stop mrp

Set standby_file_management = ‘MANUAL’

Find the name of the “bad file”. In my case it was /oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034

You can find the bad file with:

select     name 
from       v$datafile 
where not name like '+%'
;

Find the correct name of the file from the primary.

The file will not actually exist in /opt/oracle. Then

alter database create datafile '/oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034' as '+ASM mount point/usually DB name/datafile/correct name of file from primary';

The goal is to create the file in the correct location as it would have been created if the correct file name convert was in place.

Check v$datafile again

Set standby_file_management = ‘AUTO’

Start MRP