I've found this problem in a RAC environment.
Case the environment is a RAC
- real application cluster
, check if the datafile is in a shared storage and visible in all nodes.
Here is a example, my environment uses ASM
- Automatic Storage Management
to storage the datafiles, but my tablespace users is in filesystem non shared:
One way is:
- Backup the datafile as copy to ASM;
- Switch datafile to copy;
- Recover datafile.
NOTE: make it in the node where the file is visible.
Example using tablespace users:
SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name = 'USERS';
FILE_ID FILE_NAME TABLESPACE
---------- ---------------------------------------------------------------------------------------------------- ----------
29 /home/oracle/tbsusersdata_D-MOOTDB_I-1967025112_TS-USERS_FNO-29_6qv2k42g USERS
In RMAN:
backup as copy datafile 29 format '+DATA/MOOTDB/DATAFILE/users01.dbf';
switch datafile 29 to copy;
recover datafile 29;