This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 14, 2013 5:43 AM by Nicolas.Gasparotto RSS

Rename the datafile which has special characters.

Robin Newbie
Currently Being Moderated
Hi,

In AIX server, I have added a datafile and unknownly special characters are added due to given backspace.Now i want to rename the datafile which has special character to correct file name.

The file name is :

ls -ltr
-rw-rw---- 1 oracle dba 52436992 Jan 27 03:44 test_0001.dbf

ls -b (special character details)

test_0001\010\010.dbf

mv test_0001* test_correct.dbf

But in db ,if fire the rename command,I am getting the below error.

1.

alter database rename file '/oracle/u01/test_0001.dbf' to '/oracle/u01/test_correct.dbf';

ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile
"/oracle/u01/test_0001.dbf"


2.alter database rename file '/oracle/u01/test_0001\010\010.dbf' to '/oracle/u01/test_0001.db.dbf';


ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile
"/oracle/u01/test_0001\010\010.dbf"

Please help me to solve this issue.Your help is really appricated.

Thanks
Sai
  • 1. Re: Rename the datafile which has special characters.
    user176253 Journeyer
    Currently Being Moderated
    Before renaming see what is name at oracle leve. Fire a query SELECT FILE_NAME FROM DBA_DATA_FILES. You can see the file name for specified file and then you can rename it easily.

    Regards.
  • 2. Re: Rename the datafile which has special characters.
    Anurag Tibrewal Guru
    Currently Being Moderated
    Hi,

    How is the file named in OS currently is it with special characters or is it free from special characters.
    Also post the output of
    select file_name, tablespace_name from dba_Data_files where upper(file_name) like '%test%'
    Regards
    Anurag
  • 3. Re: Rename the datafile which has special characters.
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I'm sure in the past I have run

    alter database rename file 7 to '/dsfd/dsf/df.dbf'

    So you do it by file number rather than name.

    Sorry got nowhere to try it at the moment to confirm. The syntax might be slightly different possible alter database datafile 7 rename to 'vvc'.

    Regards,
    Harry
  • 4. Re: Rename the datafile which has special characters.
    Robin Newbie
    Currently Being Moderated
    Thanks Everyone for your reply. Even file_id also is not working.

    SQL> select file_name from dba_data_files where file_name like '%test_000%';

    FILE_NAME
    --------------------------------------------------------------------------------
    /oracle/u01/test_0001.dbf


    In OS, It is displaying like this

    !ls -lr test_000*
    -rw-rw---- 1 oracle dba 52436992 Jan 27 03:44 test_0001.dbf
    =============================================
    for finding special character in OS

    !ls -b test_000*
    test_0001.dbf.\010\010.dbf
    =========================================


    SQL> select file_name, tablespace_name from dba_Data_files where file_name like '%test_00%';

    FILE_NAME
    --------------------------------------------------------------------------------
    TABLESPACE_NAME
    ------------------------------
    /oracle/u01/test_0001.dbf
    TEST_01

    SQL> select file_id from dba_data_files where file_name like '%test_000%';

    FILE_ID
    ----------
    135

    SQL> alter database rename file 135 to '/oracle/u01/test_0001_correct.dbf' ;
    alter database rename file 135 to '/oracle/u01/test_0001_correct.dbf'
    *
    ERROR at line 1:
    ORA-02236: invalid file name

    Awaiting for your reply.
  • 5. Re: Rename the datafile which has special characters.
    user176253 Journeyer
    Currently Being Moderated
    Use the name listed in oracle to rename file:
    Step1- Rename it to some xyz.dbf and fire database query- alter database rename file '/oracle/u01/test_0001.dbf' to '/oracle/u01/xyz.dbf';
    Step2- Rename it back to your original name.

    Regards.
  • 6. Re: Rename the datafile which has special characters.
    Robin Newbie
    Currently Being Moderated
    Agian same issues.

    SQL> alter database rename file '/oracle/u01/test_0001.dbf' to '/oracle/u01/test_new.dbf';
    alter database rename file '/oracle/u01/test_0001.dbf' to '/oracle/u01/test_new.dbf';
    *
    ERROR at line 1:
    ORA-01511: error in renaming log/data files
    ORA-01516: nonexistent log file, datafile, or tempfile
    "/oracle/u01/test_0001.dbf"
  • 7. Re: Rename the datafile which has special characters.
    Anurag Tibrewal Guru
    Currently Being Moderated
    Hi,

    I am not sure if the file name is not correct then how it got added to the database?
    Is it that file was renamed on OS after adding to the database.?

    Even if it is renamed after adding to the database?
    How come database did not complained?
    Can you post below output
    select status, tablespace_name, file_name from dba_data_files where file_id = 135;
    select status, name from v$datafile where file#=135;
    select status, name from v$datafile_header where file#=135
    Regards
    Anurag
  • 8. Re: Rename the datafile which has special characters.
    742587 Newbie
    Currently Being Moderated
    Hi,

    I'm sure you are doing it on some UNIX server.

    Please do following steps: Assume your filename is like '/oracle/u01/test_000<junk_characters>1.dbf'

    1. ls -ltr /oracle/u01/test_000*.dbf* > rename.sql (This will create a file naming rename.sql with list all the files starting with test_000 and ending with dbf)
    2. vi rename.sql (Now eliminate all unwanted files which you don't want to rename but don't remove junk characters)
    a. add "alter database datafile " string before datafile which has junk characters
    b. add " to /oracle/u01/test_0001*.dbf" at the end of datafile which has junk characters.
    c. don't forget to add single quotes before and after both datafile names ( one with junk character and one which is new or original name)
    3. run rename.sql in mount stage of database.

    Regards,
    Avi
  • 9. Re: Rename the datafile which has special characters.
    680268 Newbie
    Currently Being Moderated
    You are not able to rename because file name itself have the special character. You need to recreate the controlfile to rename the file.

    You need to run following command

    Alter database backup controfile to trace;

    it will generate trace file at user_dump_dest. COpy it to some other location with .sql and delete unwanted lines.
    shutdown the database. rename the existing controfile. Rename your datafile to correct name. Correct the name in .sql file you have generated then run the .sql file to generate new controlfile.
  • 10. Re: Rename the datafile which has special characters.
    Robin Newbie
    Currently Being Moderated
    Thanks Everyone.As you said we can only do the below steps.

    1. trace the controlfile

    2.shutdown the db

    3.mv the file name to correct file name in OS

    4.edit the control file with renamed file

    5.startup nomount

    6.create a controlfile

    7.open the database.

    Thanks again ..
  • 11. Re: Rename the datafile which has special characters.
    749706 Newbie
    Currently Being Moderated
    hi,,

    instead of mv command use cp command in OS prompt to copy the file. you have used mv command so it cut and pasted to new file. while renaming it will raise the error file not exist.

    bye
  • 12. Re: Rename the datafile which has special characters.
    Robin Newbie
    Currently Being Moderated
    Thanks Avi and all.

    The below steps are working..

    Please do following steps: Assume your filename is like '/oracle/u01/test_000<junk_characters>1.dbf'

    1. ls -ltr /oracle/u01/test_000*.dbf* > rename.sql (This will create a file naming rename.sql with list all the files starting with test_000 and ending with dbf)
    2. vi rename.sql (Now eliminate all unwanted files which you don't want to rename but don't remove junk characters)
    a. add "alter database datafile " string before datafile which has junk characters
    b. add " to /oracle/u01/test_0001*.dbf" at the end of datafile which has junk characters.
    c. don't forget to add single quotes before and after both datafile names ( one with junk character and one which is new or original name)
    3. run rename.sql in mount stage of database.

    Thanks
    Sai
  • 13. Re: Rename the datafile which has special characters.
    user7181669 Newbie
    Currently Being Moderated
    thank you sooooo much. the tip of the ls > rename.sql did the trick for me at least.
    I was able to generate a file that I then edited in vi and it had the special characters at the end of the filenam.

    Edited by: user7181669 on Feb 10, 2012 1:20 PM
  • 14. Re: Rename the datafile which has special characters.
    Gerrit Haase Newbie
    Currently Being Moderated
    Oops

    Edited by: Gerrit Haase on 14.02.2013 14:08
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points