ORA-Error – 01111/ORA-01110/ORA-01157 name the datafile is unKnown

ORA-Error – 01111/ORA-01110/ORA-01157 name the datafile is unKnown

Cause:

This error will occur on standby database after adding the new datafile into primary database

Solution:

View the alert log

[oracle@db-std ~]$ cd /u01/app/oracle/diag/rdbms/teststdby/teststdby/trace/
[oracle@db-std trace]$ tail -100 alert_teststdby.log
ORA-01157: cannot identify/lock data file 36 - see DBWR trace file
ORA-01111: name for data file 36 is unknown - rename to correct file
ORA-01110: data file 36: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00036'

Verify the rename datafile from the primary database

SQL> select file#,name from v$datafile;
 36  +DATA/testdb/datafile/test_tajmi.295.953808069

This shows that the datafile name is mismatch, we need to correct it by using the below procedure

SQL> alter system set standby_file_management=manual;

Syntax:
SQL> ALTER DATABASE CREATE DATAFILE '< ....UNNAMED00167>' as '< datafile name with the correct path>';

Example:

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00036' as '/oradata/teststdby/datafile/test_tajmi.295.953808069';

Sql> alter system set standby_file_management=auto;

SQL> shutown abort;

Sql> startup mount;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Therefore the standby database is sync & applies the log gap successfully.