How to rename a datafile which has blank characters or whitespace

Introduction

One of my earlier posts has the procedure that describes how to rename a datafile. A similar but slightly different procedure is when a datafile has newline or whitespace character in it. Obviously its not a great practice to create a datafile with newline or whitespace characters is in it. This post describes how to remove a newline or any special characters in datafiles.

Problem

In our production database there is a datafile that has 1 newline and 4 whitespace characters in it. It was created long ago by a DBA by mistake. Due to these special characters, every database clone procedure has a special task to rename this datafile before creating the target database's controlfile. This extra manual step can be avoided if the datafile is renamed in the production database.

Solution

1. select file_name
from dba_data_files
where file_name like '%appsprod_custcpsinx03%';

/dbf07/oracle/appsproddata/
custcpsprod_inx03.dbf

2. Note the above newline and the whitespace characters.

3. select dump(name)
from dba_data_files
where file_name like '%custcpsprod_inx03.dbf%'
DUMP(NAME)--------------------------------------------------------------------------------Typ=1 Len=57: 47,100,98,102,48,55,47,111,114,97,99,108,101,47,97,112,112,115,112,114,111,100,100,97,116,97,47,10,32,32,32,32,97,112,112,115,112,114,111,100,95,99,117,115,116,99,112,115,105,110,120,48,51,46,100,98,102

4. The above dumps all the ascii equavalent values of the characters in the datafile.
5. http://www.columbia.edu/kermit/ascii.html can be referred for ascii values and their equavalents.

6. 10 is newline and 32 is whitespace. Therefore the above datafile has 1 newline and 4 whitespace characters.
7. Shutdown the database
8. cd /dbf07/oracle/appsproddata/
9. ls –lrt *appsprod_custcpsinx03.dbf
10. -rw-r----- 1 oracle dba 3145736192 Nov 29 19:35
appsprod_custcpsinx03.dbf
11. mv *appsprod_custcpsinx03.dbf appsprod_custcpsinx03.dbf
12. Login as 'sqlplus '/ as sysdba' and issue 'startup mount'
13. Verify the existing datafile entry in the controlfile – select name from v$datafile where name like ‘%appsprod_custcpsinx%’; - It still has the old entry in the controlfile
14. alter database rename file '/dbf07/oracle/appsproddata/
appsprod_custcpsinx03.dbf '
to
'/dbf07/oracle/appsproddata/appsprod_custcpsinx03.dbf'

15. In the above alter database statement, exactly one newline and exactly 4 whitespace characters have to be entered. Otherwise controlfile will not recognize the datafile.

16. alter database open

Conclusion

Whereas Oracle allows a datafile to have any number of newline or whitespace characters, it is not a great practice to create datafiles with these special characters and as noticed above it is not a straightforward rename exercise. Renaming any datafile requires a database bounce or atleast a 'tablespace offline' and this means downtime for business.