Monday, July 15, 2013

Datafile Creation Error On Standby When Primary Is On ASM

The issues happen when you add datafiles on primary which is on ASM (OMF).
MRP aborted on standby due to below error while your  standby db is on filesystem (not ASM)
The standby complains it can't create datafile even though  standby_file_management='auto' is in place

 Error like in alert logs are :
ORA-01119: error in creating database file '+data'
ORA-17502: ksfdcre:4 Failed to create file +data

ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
File #56 added to control file as 'UNNAMED00056'.

First we need to make MRP work and narrow the gap,so we need to manually create datafile 'UNNAMED00056' on standby
  • use sql select file#,name from v$datafile where name like '%UNNAMED%';  to check how many we have
  • use sql alter database to create a new datafile to replace old one.  ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/' as '/u111/oradata/test/test_05.dbf' size 10g
  •  Then you can restart MRP to continue recovery process
Second we need to prevent this error in the future
  • Make sure  standby_file_management='auto' is in place
  • In some notes, you may see db_file_create_dest, however it's obsolete after 11gR2
  • The good parameter we need to set is DB_FILE_NAME_CONVERT
  • Example is alter system set DB_FILE_NAME_CONVERT = '+DATA/test/datafile','/u111/oradata/test' scope=spfile sid='*'; 
  • This parameter can't set dynamic, must be set on spfile