Thursday, September 11, 2008

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

# Physical Standby. Datafile added to primary causes recovery to fail on Standby

SQL> recover standby database;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 22 is unknown - rename to correct file
ORA-01110: data file 22: '/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00022'
ORA-01157: cannot identify/lock data file 22 - see DBWR trace file
ORA-01111: name for data file 22 is unknown - rename to correct file
ORA-01110: data file 22: '/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00022'

# Fix
alter database create datafile '/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00022' as '+ASM_ORADATA52/pl01dis1/datafile/disdwh_dat_nassm.dbf';

# To avoid a repeat
alter system set db_create_file_dest='+ASM_ORADATA52';
alter system set standby_file_management=auto;