Oracle Database might be sometimes very surprising by produced error messages. Here is one of them which has occurred during adding of datafile to existing tablespace.
Examine following command and it's output:
Connected to: Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@EDMREP AS SYSDBA> alter tablespace DATA add datafile '/storage/edm/oradata318/EDMREP/data36.dbf' size 30G; alter tablespace DATA add datafile '/storage/edm/oradata318/EDMREP/data36.dbf' size 30G * ERROR at line 1: ORA-01119: error in creating database file '/storage/edm/oradata318/EDMREP/data36.dbf' ORA-17502: ksfdcre:4 Failed to create file /storage/arclog/EDMREP/archivelog/2016_02_18/o1_mf_1_350404_1p9RIDy_X_.arc
OK, we've got "ORA-01119: error in creating database file" followed by "ORA-17502: ksfdcre:4 Failed to create file". Let's check description for ORA-17502 error which says: "File creation failed due to either insufficient OS permission or the file already exists." Well... of course the file exists as it's archive redo log file (as DB is in archivelog mode) and also it's couldn't be insufficient OS permission problem as archive redo log file had been created successfully.
Well, at this point I already knew the reason (or I was almost sure what the real reason might be) of command failure but let's continue in (guess what) game. So let's take a look at permissions first and other information at OS level:
oracle@[charon ~]$ cd /storage/arclog/EDMREP/archivelog/2016_02_18/ oracle@[charon /storage/arclog/EDMREP/archivelog/2016_02_18]$ touch test oracle@[charon /storage/arclog/EDMREP/archivelog/2016_02_18]$ ls -la test -rw-r--r-- 1 oracle dba 0 Feb 18 10:06 test oracle@[charon /storage/arclog/EDMREP/archivelog/2016_02_18]$ rm test oracle@[charon ~]$ ls -la /storage/arclog/EDMREP/archivelog/2016_02_18/o1_mf_1_350404_1p9RIDy_X_.arc -r--r----- 1 oracle dba 91896320 Feb 18 10:05 /storage/arclog/EDMREP/archivelog/2016_02_18/o1_mf_1_350404_1p9RIDy_X_.arc oracle@[charon ~]$ df -h | grep arclog /dev/vx/dsk/edmdata_datadg/ecm_arclog_vol 150G 40G 104G 28% /storage/arclog
As shown above permissions are correct, destination directory is accessible, writeable and has sufficient of space available. Archive redo log file is present but this is expected behaviour of course. Logically and according to above checks obviously this error is completely misleading and took us away from real cause. Now back to real reason of the error.
oracle@[charon ~]$ df -h | grep oradata318 /dev/vx/dsk/edmdata_datadg/edm_oradata318_vol 900G 871G 29G 97% /storage/edm/oradata318 oracle@[charon ~]$ df -h | grep oradata319 /dev/vx/dsk/edmdata_datadg/edm_oradata319_vol 900G 661G 238G 74% /storage/edm/oradata319
Yes, this is the real cause of error message. There is no free space for 30 GB datafile in /storage/edm/oradata318 mount point, so we use next available.
SYS@EDMREP AS SYSDBA> alter tablespace DATA add datafile '/storage/edm/oradata319/EDMREP/data36.dbf' size 30G; Tablespace altered.
As you can see it's obvious that text of ORA-17502 message was irrelevant and moreover completely misleading...