To change the location of all the datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: To change the location of all the datafiles

  1. #1
    Join Date
    Jun 2007
    Posts
    42

    To change the location of all the datafiles

    I am imitating a database in one server to another server.My previous data files are in E Drive.Since new server doesn't have a E drive,I need to change the location of the database files in the control file.Please give me an advice..

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    re-create the control file with the correct location

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

    Start the instance to mount state and rename all files you moved
    including datafiles, online log files and tempfiles.
    The old names you get from controfile with these queries:

    select name from v$datafile;
    select member from v$logfile;
    select name from v$tempfile;

    You also need to adjust pfile/spfile if the controlfiles moved.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by ales View Post
    Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

    Start the instance to mount state and rename all files you moved
    including datafiles, online log files and tempfiles.
    The old names you get from controfile with these queries:

    select name from v$datafile;
    select member from v$logfile;
    select name from v$tempfile;

    You also need to adjust pfile/spfile if the controlfiles moved.
    the problem with that is havign run a command many times or doing some code to loop round all the files

    doing a global replace in a control file is much easier

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by davey23uk View Post
    the problem with that is havign run a command many times or doing some code to loop round all the files

    doing a global replace in a control file is much easier
    Yes, I admit I proposed an old-school approach :-)
    With a bit of scripting it's not much work.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Something like this might work. I guess you run the query on the source database, and then startup nomount on the target database and apply the output. You can also recreate the control file by 'alter database backup control file to trace;' and then edit that file so that you can create new control files on the target system.

    Code:
    select 'ALTER DATABASE RENAME FILE ' || name 
           || ' TO ' || 
           REPLACE(name, 'E:\', 'D:\') ||';' 
      from ( select name 
               from v$datafile
              union all
             select member name 
               from v$logfile
              union all
             select name 
               from v$tempfile )
     order by name;
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width