How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database

The physical standby recovery process was terminated with the error below:

Standby Crash Recovery aborted due to error 1111.
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
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/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
Completed Standby Crash Recovery.
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-10458: standby database requires recovery
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/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'

How to resolve the errors so that the recovery process could proceed so that the physical standby could be in sync with the primary database?
Solution:
perform the following steps on the physical standby database to resolve the issue
On standby database run following command to find missing files

SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME 
from v$datafile_header where ERROR='FILE MISSING';

cancel the managed recovery

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Set standby_file_management=manual

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

If the physical standby database is RAC, then please make the change to all standby instances

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';

Rename the unknown datafile 8

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'absolute path/real datafile name' ;

For ASM

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'+DG01' size 'actual size of datafile on Primary';

Set standby_file_management=auto

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

Start managed recovery

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

ORA-16047: DGID mismatch between destination setting and target database

The error shows a mismatch of DG_CONFIG parameter.

ORA-16047: DGID mismatch between destination setting and target database

Check the below parameters on both Primary and Standby databases.
On Primary:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(testdb,teststby)

SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      TESTDB
fal_server                           string      teststby


SQL> show parameter log_archive_dest_2                  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=teststby NOAFFIRM ASYN
                                                 C VALID_FOR=(ONLINE_LOGFILES,P
                                                 RIMARY_ROLE) DB_UNIQUE_NAME=te
                                                 ststby
             
SQL> show parameter log_archive_dest_state_2            
log_archive_dest_state_2             string      ENABLE

On Standby:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(testdb,teststby)

SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      teststby
fal_server                           string      testdb

SQL> show parameter log_archive_dest_2
                 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=testdb ASYNC VALID_FOR
                                                 =(ONLINE_LOGFILES,PRIMARY_ROLE
                                                 ) DB_UNIQUE_NAME=testdb
             
SQL> show parameter log_archive_dest_state_2
           
log_archive_dest_state_2             string      DEFER

If any of the above parameters are not set correctly the below error will appear

select DEST_NAME,STATUS,ERROR from v$ARCHIVE_DEST where status!='INACTIVE';

DEST_NAME          STATUS     ERROR
------------------ --------   -------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID     
LOG_ARCHIVE_DEST_2 DISABLED   ORA-16047: DGID mismatch between destination setting and target database

If the parameters are set correct

select DEST_NAME,STATUS,ERROR from v$ARCHIVE_DEST where status!='INACTIVE';

DEST_NAME          STATUS     ERROR
------------------ --------   -------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID     
LOG_ARCHIVE_DEST_2 VALID     

ORA-00600: ORA-10567: ORA-10560: Physical standby redo applying error

It is not the first time we face the problem with applying redo on physical standby. Very old environment - Oracle 10gr2 with raw devices on old SUN storage believed to be suffering from physical lost writes. Particularly at the heavy load on primary where loads of redo log files are being generated.
So, the errors in alert.log are as follow:

ORA-00600: internal error code, arguments: [3020], [87], [154002], [365058450], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 87, block# 154002)
ORA-10564: tablespace CB_IND
ORA-01110: data file 87: '/dev/vx/rdsk/ora02dg/cb_ind05'
ORA-10560: block type '0'

Famous ORA-00600 error when metalink gets handful. It says:

“This error can be reported if any of these updates are lost for some reason.
Therefore, thoroughly check your operating system and disk hardware.
In the case of a lost update, restore an old copy of the datafile and attempt to recover and roll forward again. (Doc ID 30866.1)”

Our only option was a restore by oracle as os investigation would take significant time. Even more, you still need to restore damaged file from oracle at the end. So, as a recommendation we suggest not thinking much and start immediate restore.

Steps are as follow:

1. primary database -

 backup as copy datafile 87 format '/share/sbtarch/ind5'; 

(raw devices add more trick to backup and restore operations. In case of normal fs it would be possible to put corresponding TS in backup mode and just a copy the datafile )

2. primary database -

 copy current controlfile for standby TO '/share/sbtarch/control03.dbf'  

3. standby database – mount instance with newly copied control file

4. standby database –

  run { 
             ALLOCATE CHANNEL ch02 TYPE DISK;
             restore datafile 87; 
      }

5. on standby (in case of rman catalog you might avoid this step)

 catalog start with '/oracle/arch';  after control file copied 

6. on standby –

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session; 

At this point, recovery started and all archived log files in queue applied successfully.

Thanks,