Hello Friend's.
In this post we will see how to resolve the error
Some of the reasons for a file being created as UNNAMED or MISSING in the standby database,
Such as insufficient disk space on standby site (or) Improper parameter settings related to file management.
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. automatic standby file management is enabled,
file additions and deletions on the primary database are replicated on the standby database.
Take an example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL ,
While recovery MRP is trying to apply archives
it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.
Errors in file /oracle/FIN/saptrace/diag/rdbms/FINdg/FIN/trace/FIN_pr00_10633.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/FIN/19.0.0/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/FIN/19.0.0/dbs/UNNAMED00006'
PR00 (PID:10633): Managed Standby Recovery not using Real Time Apply
Stopping change tracking
2022-03-21T01:13:19.575342+02:00
Steps to be performed on Primary :
SQL> col NAME for a50
SQL> /
NAME
--------------------------------------------------
+DATA/FIN/DATAFILE/system.433.1089751117
+DATA/FIN/DATAFILE/sysaux.434.1089751125
+DATA/FIN/DATAFILE/psapundo.435.1089751129
+DATA/FIN/DATAFILE/psapsr3db.439.1089753261
+DATA/FIN/DATAFILE/psapsr3db.440.1089753279
+DATA/FIN/DATAFILE/perfstat.dbf
6 rows selected.
SQL> select file#,name from v$datafile where file#=6;
FILE# NAME
---------- --------------------------------------------------
6 +DATA/FIN/DATAFILE/perfstat.dbf
SQL> select file#,name,(bytes/1024/1024/1024) GB from v$datafile where file#=6;
FILE# NAME GB
---------- -------------------------------------------------- ----------
6 +DATA/FIN/DATAFILE/perfstat.dbf 1
Standby Alert File Snap :
2022-03-21T01:13:19.480050+02:00
Errors in file /oracle/FIN/saptrace/diag/rdbms/FINdg/FIN/trace/FIN_pr00_10633.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/FIN/19.0.0/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/FIN/19.0.0/dbs/UNNAMED00006'
PR00 (PID:10633): Managed Standby Recovery not using Real Time Apply
Stopping change tracking
2022-03-21T01:13:19.575342+02:00
Steps to be perfoemed on Standby :
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/FINDG/DATAFILE/system.432.1089825721
+DATA/FINDG/DATAFILE/sysaux.433.1089825721
+DATA/FINDG/DATAFILE/psapundo.434.1089825721
+DATA/FINDG/DATAFILE/psapsr3db.435.1089825721
+DATA/FINDG/DATAFILE/psapsr3db.436.1089825723
/oracle/FIN/19.0.0/dbs/UNNAMED00006
6 rows selected.
SQL> select * from v$recover_file where error like '%FILE%';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
6 ONLINE ONLINE FILE MISSING 0 0
SQL>
SQL> select file#,name from v$datafile where file#=6;
FILE# NAME
---------- -------------------------------------------------------
6 /oracle/FIN/19.0.0/dbs/UNNAMED00006
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
SQL>
SQL> alter system set standby_file_management=MANUAL scope=both;
System altered.
SQL> alter database create datafile '/oracle/FIN/19.0.0/dbs/UNNAMED00006' as '+DATA' size 1G;
Database altered.
SQL> alter system set standby_file_management=MANUAL scope=both;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> select * from v$recover_file where error like '%FILE%';
no rows selected
SQL>
SQL> select name from v$datafile;
NAME
-------------------------------------------------------
+DATA/FINDG/DATAFILE/system.432.1089825721
+DATA/FINDG/DATAFILE/sysaux.433.1089825721
+DATA/FINDG/DATAFILE/psapundo.434.1089825721
+DATA/FINDG/DATAFILE/psapsr3db.435.1089825721
+DATA/FINDG/DATAFILE/psapsr3db.436.1089825723
+DATA/FINDG/DATAFILE/perfstat.364.1099899133
6 rows selected.
ConversionConversion EmoticonEmoticon