Mir Sayeed Hassan – Oracle Blog

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: If you’re standby database is file system storage

View the error in 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_trd.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;
System altered.

Syntax:

SQL> alter database create datafile  '< ....UNNAMED00167> ' as ' ';

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;
System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

In case if it’s not resolved, then shutdown, startup database with mount & apply the recovery process

SQL> shutown immdiate
SQL> startup mount;

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

Solution: If standby database is ASM

Note:

If  your standby database is ASM with OMF, therefore you are not able create with rename command with same OMF Name, the reason is the file system is not supported with ASM, So you need to perform with just name as shown below.

Check the stanby_file_management status

SQL> show parameter standby

NAME                                 TYPE                      VALUE
---------------------------------------------------------------------
standby_archive_dest string standby_file_management string     AUTO

SQL> alter system set standby_file_management=manual;
System altered.

Verify

SQL> show parameter standby_

NAME                               TYPE                         VALUE
----------------------------------------------------------------------
standby_archive_dest string standby_file_management string       MANUAL

Try to rename the file with ASM Name

SQL> alter database create datafile '/u01/oraclebase/oracle/dbs/UNNAMED00048' as
'+ORADATA/racdbn1/datafile/trd_tbl.326.1043752881';

alter database create datafile '/u01/oraclebase/oracle/dbs/UNNAMED00048' as '+ORADATA/racdbn1/datafile/trd_tbl.326.1043752881' 
* ERROR at line 1: ORA-01276: Cannot add file +ORADATA/racdbn1/datafile/trd_tbl.326.1043752881. 
File has an Oracle Managed Files file name.

Rename with Normal datafile name for ASM

SQL> alter database create datafile '/u01/oraclebase/oracle/dbs/UNNAMED00048' as '+ORADATA/racdbn1/datafile/trd_tbl';
Database altered.

SQL> alter system set standby_file_management=auto;
System altered.

Start the recovery process

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

Check the MRP, RFS Process started

@/home/oracle/script/stbytest.sql;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

ARCH CLOSING 1 653006 32768 426
ARCH CLOSING 1 653185 18421760 104
MRP0 APPLYING_LOG 1 653061 1632233 18405052
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 2 329223 12109122 2
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 653186 14843798 16
RFS IDLE 0 0 0 0

11 rows selected.

THREAD# MAX(SEQUENCE#)
----------------------
1 653060
2 329130

====Hence tested & verified in our test env=====