Thursday, March 20, 2014

ORA-01111: name for data file 129 is unknown - rename to correct file

Recently i was working on moving some data file on the primary server and i have set the standby_file_management='MANUAL'. and  then I saw the following error on my standby server.



ALTER DATABASE RECOVER managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (ORCL)
Thu Mar 20 16:07:33 2014
MRP0 started with pid=35, OS id=22622
MRP0: Background Managed Standby Recovery process started (ORCL)
started logmerger process
Thu Mar 20 16:07:38 2014
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_pr00_22624.trc:
ORA-01111: name for data file 214 is unknown - rename to correct file
ORA-01110: data file 214: '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214'
ORA-01157: cannot identify/lock data file 214 - see DBWR trace file
ORA-01111: name for data file 214 is unknown - rename to correct file
ORA-01110: data file 214: '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (ORCL)
Completed: ALTER DATABASE RECOVER managed standby database disconnect from session

Here  is what I did to resolve it:

 STEP1:- Check the name of the datafile on standby database

select name from v$datafile; ---- This command should show the datafile name as UNNAMEDxxxxx

STEP2:- ON STANDBY DATABASE
alter system set standby_file_management='manual';

STEP3:- Rename the datafile
alter database create datafile 'C:\ORACLE\ORA10G\DATABASE\UNNAMED00129' as 'C:\ORACLE\ORA10G\DATABASE\actual_dbfile_name.dbf'


STEP4:- On the standby database
alter system set standby_file_management='auto';

STEP5:- On the standby database
recover managed standby database disconnect;

shutdown and then reopen the standby database and continue applying the redo logs


sample :
SQL> alter system set standby_file_management='manual';

System altered.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214' as '/u03/oracle/oradata/ORCL/datafiles/auditdata03.dbf';

and now start archive log application.
SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

 /u03/oracle/oradata/ORCL/datafiles/ORCL_data118.dbf
/u03/oracle/oradata/ORCL/datafiles/ORCL_data119.dbf
/u03/oracle/oradata/ORCL/datafiles/auditdata03.dbf





1 comment: