By Brian Fitzgerald

Environment

2-node RAC on ASM on Linux. New tablespace MYAPP had just been created.

The error

A user reported:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file 
ORA-01110: data file 7: '/u01/app/oracle/product/1930/db_1/dbs/myapp.dbf'
Failed SQL stmt:  INSERT INTO PS_...

We have an oracle file in dbs, which is local, and therefore not shared across RAC nodes. ORA-01157 appears when inserts run on the second instance.

The cause

A review of the alert log shows that the DBA had run:

CREATE TABLESPACE MYAPP DATAFILE 'myapp.dbf'

“DATAFILE ‘myapp.dbf'” should not be specified in ASM.

The fix

By the time the user had reported the error, new tables had already been setup in MYAPP. There was interest in keeping the tablespace, if possible.

The following remedial statements were run:

sqlplus

alter database datafile 7 offline;

rman:

recover datafile 7;
backup as copy datafile 7 format '+DATA1';
switch datafile 7 to copy;

sqlplus:

alter database datafile 7 online;

The fix can be run in as few as three minutes.

rman output (edited)

starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished recover at 2024-03-05 16:17:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/product/1930/db_1/dbs/myapp.dbf
output file name=+DATA1/ORCL/DATAFILE/myapp.474.1162832655 tag=TAG20240305T170413 RECID=378134 STAMP=1162832655
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2024-03-05 17:04:15
using target database control file instead of recovery catalog
datafile 7 switched to datafile copy "+DATA1/ORCL/DATAFILE/myapp.474.1162832655"

Cleanup

delete datafilecopy '/u01/app/oracle/product/1930/db_1/dbs/myapp.dbf';

commands that were not used

Some commands mentioned in documents such as “How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command. (Doc ID 1610615.1)” were not used.

alter system switch logfile
asmcmd cp
alter database rename file
set newname

Also, the five commands mentioned in “fix” above refer to the data file by number, not name. referring to the data files by name adds complexity and error-proneness. For example:

RMAN> BACKUP AS COPY DATAFILE "+DATA/orcl/datafile/users.261.689589837" FORMAT "+USERDATA";

Conclusion

If you accidentally create a tablespace in dbs, don’t panic. Take inputs from available sources, but filter out extraneous, inapplicable, or overly complex advice. Think through the simplest, safest recovery strategy for your situation.