ORA-01110 ORA-01157 cannot identify/lock data file 227 - see DBWR trace file

Problem:

Tue Dec 04 18:28:58 2018
Errors in file /d01/app/oracle/diag/rdbms/bacdbst/bacdb/trace/bacdb_pr00_18214.trc:
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
ORA-01157: cannot identify/lock data file 227 - see DBWR trace file
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
Managed Standby Recovery not using Real Time Apply
Tue Dec 04 18:28:58 2018
Recovery Slave PR00 previously exited with exception 1111
Tue Dec 04 18:28:58 2018
Errors in file /d01/app/oracle/diag/rdbms/bacdbst/bacdb/trace/bacdb_mrp0_18206.trc:
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
ORA-01157: cannot identify/lock data file 227 - see DBWR trace file
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
Tue Dec 04 18:28:58 2018
MRP0: Background Media Recovery process shutdown (bacdb)
Completed: alter database recover managed standby database disconnect
RFS[15]: Selected log 6 for thread 1 sequence 85731 dbid 3693154305 branch 923676036
Tue Dec 04 18:29:09 2018
Archived Log entry 72318 added for thread 1 sequence 85730 ID 0xffffffffdc211e01 dest 1:

Solution

in Primary:

backup as copy datafile 227 format='/export/home/oracle/%U.bak' tag='lost file backup';

copy the backup file to Primary server

scp data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak oracle@10.11.1.133:/export/home/oracle/

in Standby:

alter database recover managed standby database cancel;


RMAN> catalog datafilecopy '/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak';

RMAN> report schema;

227  0        STLBAS:ORBDT2        ***     /d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227



RMAN> switch datafile 227 to copy;

datafile 227 switched to datafile copy "/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak"

RMAN>

RMAN> report schema;

227  5120     STLBAS:ORBDT2        ***     /export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak


RMAN> copy datafile 227 to '+DATA/BACDB/STLBAS/orbdt206.dbf​';

RMAN> copy datafile 227 to '+DATA/BACDB/STLBAS/orbdt206.dbf';

Starting backup at 04-DEC-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile file number=00227 name=/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak
output file name=+DATA/BACDB/STLBAS/orbdt206.dbf tag=TAG20181204T193437 RECID=130 STAMP=994016080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-DEC-18

RMAN>
RMAN> report schema;

227  500      STLBAS:ORBDT2        ***     /export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    10240    TEMP                 32767       +DATA/bacdb/temp01.dbf
2    244      PDB$SEED:TEMP        32767       +DATA/bacdb/fdb039e555681306e04400163e5bea5f/datafile/pdbseed_temp012016-09-27_04-41-20-pm.dbf
3    20       PDBTEST:TEMP         32767       +DATA/pdbtest_temp012016-09-27_04-41-20-pm.dbf

RMAN> switch datafile 227 to copy;

datafile 227 switched to datafile copy "+DATA/BACDB/STLBAS/orbdt206.dbf"

RMAN> report schema;


227  500      STLBAS:ORBDT2        ***     +DATA/BACDB/STLBAS/orbdt206.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    10240    TEMP                 32767       +DATA/bacdb/temp01.dbf
2    244      PDB$SEED:TEMP        32767       +DATA/bacdb/fdb039e555681306e04400163e5bea5f/datafile/pdbseed_temp012016-09-27_04-41-20-pm.dbf
3    20       PDBTEST:TEMP         32767       +DATA/pdbtest_temp012016-09-27_04-41-20-pm.dbf
4    48       OFFSHR:TEMP          32767       +DATA/bacdb/offshr/temp012016-05-25_01-45-13-pm.dbf
5    8738     EMOB:TEMP            32767       +DATA/bacdb/emob/temp012016-05-25_01-45-13-pm.dbf
6    32767    STLBAS:TEMP          32767       +DATA/bacdb/stlbas/temp01.dbf
7    32767    STLBAS:TEMP          32767       +DATA/bacdb/stlbas/temp02.dbf
8    30720    STLBAS:TEMP          30720       +DATA/bacdb/stlbas/temp03.dbf

Now start redo apply on standby :-

Background Media Recovery terminated with ORA-1274 ORA-01186 ORA-01157 ORA-01111 after adding a Datafile in Primary

Error

Errors in file /u01/oracle/diag/rdbms/orcldg/ORCL/trace/ORCL_dbw0_8408.trc:
ORA-01186: file 8 failed verification tests
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008'
File 8 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/oracle/diag/rdbms/orcldg/T


Reason 

This Error occurs if we add a Datafile OR Tablespace in PRIMARY Database and that could not be translated to the Standby Database due to these Reasons:

    - Standby_file_management is set to MANUAL
    - Primary & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in Primary and Standby
    - Insufficient Space or wrong Permissions on the Standby Database to create the Datafile
    - If standby_file_management is set to Auto ,but directory path of Primary and standby are different , db_file_name_convert is not set ,but db_create_file_dest has been set to wrong value on standby.


The Redo Log generated from Primary will have Information about the Tablespace / Datafile added however it could not be created successfully in Physical Standby Database due to the standby_file_management = MANUAL

or is not able to find the specified Folder due to a missing / incorrect Filename Conversion.
The File Entry is added to Standby Controlfile as "UNNAMED0000n" in /dbs or /database
folder depends on the Operating System and eventually the MRP terminates.

Solution:

Step 1: Check that the standby_file_management = 'MANUAL.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>

Step 2: Identify the File which is "unnamedn"


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADATA/orcldg/datafile/system.310.932810169
+ORADATA/orcldg/datafile/sysaux.309.932810385
+ORADATA/orcldg/datafile/undotbs1.308.932810409
+ORADATA/orcldg/datafile/users.305.932810455
+ORADATA/orcldg/datafile/undotbs2.307.932810425
+ORADATA/orcldg/datafile/abc.311.932809925
+ORADATA/orcldg/datafile/abcd.306.932810439
/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

8 rows selected.


Step 3: Rename/create the Datafile to the correct Filename

SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'D:\oradata\mystd\sales01.dbf';


If the standby is in ASM + OMF then use the below command,

SQL> alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as <'+ASMDISKGROUPNAME'> size ;

alter database create datafile '/u01/oracle/product/11.2.0/db_1/dbs/UNNAMED00008' as '+ORADATA' size 25G;


Step 4: Verify the Filename is correct

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ORADATA/orcldg/datafile/system.310.932810169
+ORADATA/orcldg/datafile/sysaux.309.932810385
+ORADATA/orcldg/datafile/undotbs1.308.932810409
+ORADATA/orcldg/datafile/users.305.932810455
+ORADATA/orcldg/datafile/undotbs2.307.932810425
+ORADATA/orcldg/datafile/abc.311.932809925
+ORADATA/orcldg/datafile/abcd.306.932810439
+ORADATA/orcldg/datafile/audit_tblspc.329.948974823

8 rows selected.

SQL>

Step 5: Change the STANDBY_FILE_MANAGMENT  MANUAL to AUTO


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.

Step 6: Start the MRP (this is using Real Time Apply)


SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Step 7: Verify the MRP is running as expected


SQL> select process, status , sequence# from v$managed_standby;

PROCESS   STATUS SEQUENCE#
--------- ------------ ----------
ARCH  CONNECTED 0
ARCH  CONNECTED 0
ARCH  CONNECTED 0
ARCH  CONNECTED 0
RFS  IDLE 0
RFS  IDLE 0
RFS  IDLE    27033
RFS  IDLE    38836
RFS  IDLE 0
RFS  IDLE 0
MRP0  APPLYING_LOG    27026

11 rows selected.

SQL>