Environment related:
OS: CentOS release 6.6
RAC: 11.2.0.4.0 dual node ASM Library

1. Cause of failure

Today, four data files are added in batches. add datafile '+ DATADG' in the second to fourth data file add SQL misses' + '. After oracle successfully adds the first data file, it is found that the second data file is added as "$oracle ﹣ home / DBS / DATADG". Then the third and fourth data files fail to be added (because the DATADG file already exists).

2. Problem repair

Well, the remedial action is as follows:

sqlplus / as sysdba
col tablespace_name for a20
col file_name for a80
set linesize 150
set pagesize 1000
select f.tablespace_name,
       f.file_name,
       f.bytes / 1024 / 1024 / 1024 as cgb,
       f.maxbytes / 1024 / 1024 / 1024 as mgb
  from dba_data_files f
 order by 1,regexp_substr(file_name, '[^/]+', 1, length(regexp_replace(file_name, '[^/]+')));
-- View data file related information

Find the table space with data file problems, view and record the relevant file path and file name information

select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS,ONLINE_STATUS 
  from dba_data_files where TABLESPACE_NAME='TS_GATEWAY_DATA';
-- Found FILE_NAME It needs to be recorded. It needs to be used for comparison below

Offline the data file

alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/DATADG' offline;
exit

Using rman to copy local data files to ASM

rman target /
copy datafile  '/u01/app/oracle/product/11.2.0/db_1/dbs/DATADG' to '+DATADG';
exit;

Check the data file of this tablespace in ASM, compare the data file name just recorded, and find the data file name generated by rman's copy

su - grid
asmcmd
ls DATADG/newecpss/datafile/TS_GATEWAY_DATA*
exit
# According to the file name information found in the second SQL query, comparelsFind the new data file ASM Data file name

According to the data file name in ASM, rename

sqlplus / as sysdba
alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/DATADG' 
to '+DATADG/newecpss/datafile/TS_GATEWAY_DATA.364.964346781';

Try online data file

alter database datafile '+DATADG/newecpss/datafile/TS_GATEWAY_DATA.364.964346781' online;
-- report errors:
-- ORA-01113: file 82 needs media recovery
-- ORA-01110: data file 82: '+DATADG/newecpss/datafile/ts_gateway_data.364.964346781'
exit

media recovery with rman command

rman target /
recover datafile 82;
exit;

Go online again data file

sqlplus / as sysdba
alter database datafile '+DATADG/newecpss/datafile/TS_GATEWAY_DATA.364.964346781' online;
exit;

Clear the tail

mv -v /u01/app/oracle/product/11.2.0/db_1/dbs/DATADG /tmp/

[TOC]