Google it ....

Showing posts with label ORA-01157. Show all posts
Showing posts with label ORA-01157. Show all posts

Friday, July 27, 2018

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;