Error in naming a datafile

At times life as a DBA gets so tiring that you goof up even a simplest task of adding a datafile to a tablespace, I did too (chuckles), I entered a comma(,) instead of a period(.) for the datafile extentsion.

SQL> alter tablespace BLOB_TS add datafile
  2  '/prd/u01/oradata/prod/lob08,dbf' size 5000M;

Only after hitting return did I realize that I made a blunder in the datafile name, I had to immediately take the datafile offline to prevent any users from filling it up with data.

SQL> alter database datafile '/prd/u01/oradata/prod/lob08,dbf' offline;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
RECOVER /prd/u01/oradata/prod/lob08,dbf

The status above shows that the datafile needs recovery, I ran a 'RECOVER DATAFILE' command to recover the datafile and have its status changed from 'RECOVER' to 'OFFLINE'. In order for this to be successful, the same log sequence should be running in the database if the database is in no-archive mode or it could be a different log sequence if the database is in archive mode but the archive logs be present on disk to be applied if needed.

In my case, it was in archive mode and luckily the same log sequence was running when I ran the 'RECOVER DATAFILE' command

SQL> recover datafile '/prd/u01/oradata/prod/lob08.dbf';
Media recovery complete.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
OFFLINE /prd/u01/oradata/prod/lob08,dbf

Now the file has to be copied with the corrected name at the OS level.

SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf

SQL> !cp "lob08,dbf" "lob08.dbf"
SQL>

SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf

The file is then renamed at the database level and should be brought online.

SQL> alter database rename file '/prd/u01/oradata/prod/lob08,dbf' to
  2  '/prd/u01/oradata/prod/lob08.dbf';

SQL> alter database datafile '/prd/u01/oradata/prod/lob08.dbf' online;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
ONLINE  /prd/u01/oradata/prod/lob08.dbf

It is now safe to remove the file with comma(,) in its name at the OS level

SQL> !rm "lob08,dbf"
SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf