Recently,while creating a physical standby database after restoring the cold backup(188.8.131.52 version) and starting the MRP process i faced the below error-
Errors in file /oracle/app/admin/TEST/bdump/TESTdr_dbw0_1681.trc: ORA-01157: cannot identify/lock data file 85 - see DBWR trace file ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 MRP0: Background Media Recovery terminated with error 1110 Wed Oct 25 20:54:09 2010 Errors in file /oracle/app/admin/TEST/bdump/TESTdr_mrp0_1728.trc: ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf' ORA-01157: cannot identify/lock data file 85 - see DBWR trace file ORA-01110: data file 85: '/s02/oradata/TEST/TESTDATA36.dbf' MRP0: Background Media Recovery process shutdown Wed Oct 25 20:54:10 2010 Completed: alter database recover managed standby database disconnect from session
With the little experience that i have in oracle , i had faced some similar (error provided below) error where the standby_file_management was not set to AUTO(by default its MANUAL)
ORA-01111: name for data file 11 is unknown - rename to correct file ORA-01110: data file 11: '/oracle/app/oracle/product/9.2.0/db/dbs/UNNAMED00011'
This time it was different.The parameter standby_file_management was already set to AUTO and moreover i didn’t get something like “UNNAMED00011”.
Doing a little investigation, things got clear.A datafile was added to the tablespace in the database after the completion of the cold backup.Hence, the latest standby controlfile backup that i took from the primary had the new datafile name.The view v$datafile showed the full proper name “/s02/oradata/TEST/TESTDATA36.dbf” as it reads from the controlfile and the status showed “MISSING”.
To fix the problem :
SQL> alter system set standby_file_management=manual; System altered. SQL> alter database create datafile 85 as '/s02/oradata/TEST/TESTDATA36.dbf' size 2046M ; --> size same as primary db. Database altered. SQL> alter system set standby_file_management=auto; System altered.
Then re-start the MRP
sql>alter database recover managed standby database disconnect from session
Things went well 🙂