Datafiles Named "UNNAMED%"
The datafiles are incorrectly stored under $ORACLE_HOME/dbs location with name 'UNNAMED%' for different reasons. No space available to create the datafiles under the allotted datafile location or if the parameter "STANDBY_FILE_MANAGEMENT" is not set to AUTO
As per Oracle
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.
Precaution should be taken while using this parameter, Make sure the FILE NAME CONVERT parameter is set correctly.
1st Case : If the allotted datafiles location is running short on space and a new datafile is added on Primary Database, then the datafile is stored under $ORACLE_HOME/dbs location.
Fix : Get more space added to the mountpoint and perform the below steps to rename the datafile
2nd Case : If a new datafile is added on Primary database and the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL, then Oracle Recovery Process(MRP) will get terminated due to wrong parameter setting and create the datafile under $ORACLE_HOME/dbs location.
Fix : Rename the Datafile to correct name and change the parameter STANDBY_FILE_MANAGEMENT
I had the 2nd Case on my Database
Errors From Alert Log file:-
Mon Oct 26 14:02:03 2020
Managed Standby Recovery starting Real Time Apply
Mon Oct 26 14:02:03 2020
Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_dbw0_2960.trc:
ORA-01186: file 997 failed verification tests
ORA-01157: cannot identify/lock data file 997 - see DBWR trace file
ORA-01111: name for data file 997 is unknown - rename to correct file
ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'
File 997 not verified due to error ORA-01157
Mon Oct 26 14:02:04 2020
MRP0: Background Media Recovery terminated with error 1111
Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_pr00_342871.trc:
ORA-01111: name for data file 997 is unknown - rename to correct file
ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'
ORA-01157: cannot identify/lock data file 997 - see DBWR trace file
ORA-01111: name for data file 997 is unknown - rename to correct file
ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'
Managed Standby Recovery not using Real Time Apply
Fix:-
Check for the files needs to be recovered.
SQL> select file#,name,status from v$datafile where status not in ('ONLINE','SYSTEM');
FILE# NAME STATUS
---------- ------------------------------------------------------- -------
997 /usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997 RECOVER
Get the datafile from Primary Database
FILE# NAME STATUS
---------- --------------------------------------------------
997 +DATA/PDB/datafile/tbs1.4268.1054563669 ONLINE
- Stop Recovery on Standby Database ( Most Likely it wont be running as Oracle will be terminating it)
SQL > RECOVER MANAGED STANDBY DATABASE CANCEL;
- Make sure STANDBY_FILE_MANAGEMENT is set to MANUAL, if not then run below
SQL> alter system set standby_file_management=MANUAL scope=both sid='*';
System altered.
SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as '+DATA/SDB1/datafile/tbs1.4268.1054563669';
*
ERROR at line 1:
ORA-01276: Cannot add file
+DATA/SDB1/datafile/tbs1.4268.1054563669. File has an Oracle Managed Files file name.
This Failed for me as we were managing the ASM with OMF. With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).
With OMF in place, its easier to rename the dummy datafile
SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as new;
Database altered.
-- Set the Parameter back to AUTO
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
-- Start Recovery Process
SQL> alter database recover managed standby database disconnect from session;
Database altered.
-- This fixed my issue and got the MRP started back and all Datafiles ONLINE. Hope it resolves your Issue...
