We have following two reasons for getting UNNAMED files created on standby database:
1. STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. Setting this parameter to MANUAL causes MRP process to termainate creating UNNAMED datafile under $ORACLE_HOME/dbs location.
OR
2. We have insufficient space available at mount point /diskgroup of standby database server.
Alert log will show following errors:
MRP0: Background Media Recovery terminated with error 1111 Mon Jun 14 07:52:10 2014 Errors in file /u04/app/oracle/admin/testdr/TEST_DR_dr_srv1/bdump/testdr1_mrp0_28595.trc: ORA-01111: name for data file 18 is unknown - rename to correct file ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' ORA-01157: cannot identify/lock data file 18 - see DBWR trace file ORA-01111: name for data file 18 is unknown - rename to correct file ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' Mon Jun 14 07:52:10 2014 Errors in file /u04/app/oracle/admin/fodsdr/FODS_DR_rdr-db1/bdump/testdr1_mrp0_28595.trc: ORA-01111: name for data file 18 is unknown - rename to correct file ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' ORA-01157: cannot identify/lock data file 18 - see DBWR trace file ORA-01111: name for data file 18 is unknown - rename to correct file ORA-01110: data file 18: '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' Mon Jun 14 07:52:10 2014 MRP0: Background Media Recovery process shutdown (TESTDR1)
How to resolve:
On standby database run following command to find missing files:
SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME from v$datafile_header where ERROR='FILE MISSING'; FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME -------------------------------------------------------------------------------- 18,FILE MISSING,ONLINE,,, SQL> select file#,name from v$datafile where file_id=18; FILE# NAME ----- --------------------------------------------------------- 18 /u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018
On primary database check for datafile details:
SQL> select FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||bytes from dba_data_files where file_id = 18; FILE_NAME||','||TABLESPACE_NAME||','||FILE_ID||','||STATUS||','||BYTES -------------------------------------------------------------------------------- +DATA1/test/datafile/test.639.756552633,TEST,18,AVAILABLE,32212254720
Change STANDBY_FILE_MANAGEMENT to MANUAL on standby database:
SQL> alter system set standby_file_management=MANUAL sid='*'; System altered.
Now manually create missing datafile on standby database:
SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/test/dbs/UNNAMED0018' as '+DATA1' size 30G;
Now once again change STANDBY_FILE_MANAGEMENT to AUTO on standby database:
SQL> alter system set standby_file_management=AUTO sid='*'; System altered.
Now start managed recovery on standby database:
SQL> recover managed standby database disconnect from session;
After this u will see MRP process starts applying archivelog on standby database.
Hope so u will find this post very useful 🙂
Cheers
Regards,
Adityanath
Adityanath's Database & Cloud Blog 
Leave a reply to Anonymous Cancel reply