File #... added to control file as UNNAMED000 and ORA-15041

On your standby database if you see a datafile with the name similar to ‘UNNAMED000’, it means that you have STANDBY_FILE_MANAGEMENT parameter set to auto and managed recovery process tried to create a new datafile (after you already added this file on primary database), but it could not create file because of some reason. I faced this error because my ASM diskgroup was full. Following were the entries in my alert log file of my standby database after adding a datafile in my primary database using command ‘ALTER TABLESPACE my_tablespace ADD DATAFILE ‘+DATA/datafile06.dbf’ SIZE 20g’

WARNING: File being created with same name as in Primary Existing file may be overwritten Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_mrp0_12030.trc: ORA-01119: error in creating database file '+DATA/datafile06.dbf' ORA-17502: ksfdcre:4 Failed to create file +DATA/datafile06.dbf' ORA-15041: diskgroup "DATA" space exhausted File #19 added to control file as 'UNNAMED00019'. Originally created as: '+DATA/datafile06.dbf' Recovery was unable to create the file as: '+DATA/datafile06.dbf' Errors with log /arch/mydb/archive/MYDB0001_0000004740_0874013687.ARC MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_mrp0_12030.trc: ORA-01274: cannot add datafile '+DATA/datafile01.dbf' - file could not be created

MRP (media recovery process) also stopped after this error as recovery could not continue after this error. Trying to start media recovery would return following error

Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_dbw0_11195.trc: ORA-01186: file 19 failed verification tests ORA-01157: cannot identify/lock data file 19 - see DBWR trace file ORA-01111: name for data file 19 is unknown - rename to correct file ORA-01110: data file 19: '/u01/app/oracle/product/11203/db1/dbs/UNNAMED00019' File 19 not verified due to error ORA-01157 Media Recovery failed with error 1111

V$datafile view in standby database showed file name as follows

SQL> select name from v$datafile'; NAME -------------------------------------------------------------------------------- … … … /u01/app/oracle/product/11203/db1/dbs/UNNAMED00019 19 rows selected.

There may be different reasons for file not being created in standby database and to solve this issue, you would need to find out what caused this datafile creation operation failure and resolve the issue first. As explained above that in my scenario my ASM diskgroup was left with less than 20G space that caused this failure. To resolve this issue, I added space in my ASM diskgroup so that I have enough space for adding the new datafile, and then executed following steps.

--Set standby_file_management to manual SQL> alter system set standby_file_management=MANUAL scope=memory; System altered -- Re-Create affected datafile as follows SQL> alter database create datafile '/u01/app/oracle/product/11203/db1/dbs/UNNAMED00019' as '+DATA/datafile06.dbf'; Database altered. --Set standby_file_management back to autoSQL> alter system set standby_file_management=auto scope=memory; System altered -- Now we can see our datafile listed under v$datafile SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- … … … +DATA/datafile06.dbf 19 rows selected. SQL> -- Start Managed Recovery SQL> alter database recover managed standby database using current logfile disconnect;