database error recovering system datafile from local storage to shared storage

Error:

ORA­01157: cannot identify/lock data file 64 ­ see DBWR trace fileORA­01110: data file 64: '/app/oracle/product/11.2.0.4/dbhome_1/dbs/path_to_your_datafiles_foldername_of_df_you_want.dbf'ORA02002: error while writing to audit trailORA­00604: error occurred at recursive SQL level 1 ORA­01157: cannot identify/lock data file 64 ­ see DBWR trace fileORA­01110: data file 64: '/app/oracle/product/11.2.0.4/dbhome_1/dbs/path_to_your_datafiles_foldername_of_df_you_want.dbf'


Solution:

We followed the following set of steps to resolve the issue
RMAN> copy datafile '/app/oracle/product/11.2.0.4/dbhome_1/dbs/path_to_your_datafiles_foldername_of_df_you_want.dbf' to '+DATA';

using SQL PLUS rename the Datafile to the New location

SQL>alter database rename file '/app/oracle/product/11.2.0.4/dbhome_1/dbs/path_to_your_datafiles_foldername_of_df_you_want.dbf'' to '+DATA/SYSTEM.xxx.zzzzzz';

­ open the DB
SQL> alter Database open;
­ remove the old file $ rm /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/path_to_your_datafiles_foldername_of_df_you_want.dbf


This is expected as it is RAC DB and it suppose to create the new Datafile on the shared disk no on the local node
when user add data file using
SQL> alter tablespace system add datafile 'FRA' SIZE 250M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
its add the datafile in the default OMG location db_create_file_dest


Extra commands used during troubleshooting.
use below commands to check the backup of currept file.


set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set nls_date_format = 'DD­MON­RRRR HH24:MI:SS';
Spool recover.lst select name,platform_name,open_mode,controlfile_type,log_mode,flashback_on,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$database; select substr(name, 1, 50), status from v$datafile; select substr(name,1,50), recover, fuzzy, checkpoint_change#,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$datafile_header; select * from v$backup; select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database; select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log; select GROUP#,substr(member,1,60) from v$logfile; select * from v$log_history; select * from v$recover_file; select * from v$recovery_log; select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH; spool off