Hi,
I have faced a interesting issue which occurs very commonly for everyone and just want to share my experience on this for reference
Please find the steps to be followed if we have an error below on standby due to MRP:
Errors in file /local/oracle/diag/rdbms/test/test_DR_mrp0_11295.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/local/oracle/product/11.1.0/db_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: '/local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008'
1) Identify which is the file you are missing on standby database from the alert log as above seen.
Primary : select FILE#,NAME,bytes/1024/1024 from v$datafile where file#=8;
- Review the tablespace and how much size it has been allocated with the above Query.
Standby: select FILE#,NAME,bytes/1024/1024 from v$datafile where file#=8;
- Review the tablespace and the location and name . In this example the file got created at the home location as below.
2) check the standby_file_management parameter on standby and make it to MANUAL from AUTO;
sho parameter standby_file_management
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
standby_file_management |string |MANUAL
alter system set standby_file_management=MANUAL;
3) create a datafile with the following syntax.
alter database create datafile 'local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008' as '< ASM Diskgroup> ' size <xxxm>;
or
alter database create datafile 'local/oracle/product/11.1.0/db_1/dbs/UNNAMED00008' as < specified path> size < xxxm > ;
4) Change the standby_file_management parameter to AUTO .
5) start the mrp as below..
- alter database recover managed standby database disconnect;
Hope this help for all ...
Thank you !!
SG
