Moving Datafiles from drive to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Moving Datafiles from drive to another

  1. #1
    Join Date
    Jan 2004
    Location
    Nottinghamshire, UK
    Posts
    14

    Question Moving Datafiles from drive to another

    Is it possible to move datafiles from one drive to the other? I have got to do a year end on our db server but their is not enough space on one drive to do this. So i need to know if it poss to do this and then Enterprise manager console tell the system where they are.

    HELP!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes you can

    take the tablespace offline (or startup mount)

    alter database datafile 'xxxx' rename to xxx' then copy the file to wherever you want it to be then online your tablespace or open it, make sure you can use the same tablespace and remove old file.

    The sql syntax may be wrong - it is from memory

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ALTER DATABASE RENAME FILE '/u01/foo.bar' TO '/u02/foo.bar'
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    Originally posted by davey23uk
    yes you can

    take the tablespace offline (or startup mount)

    alter database datafile 'xxxx' rename to xxx' then copy the file to wherever you want it to be then online your tablespace or open it, make sure you can use the same tablespace and remove old file.

    The sql syntax may be wrong - it is from memory

    I think the order is very important.


    1. take the tablespace offline.

    2. copy the file to new location

    3. alter database rename file oldname to newname

    4. take the tablespace online.

    because, when you rename the file it looks for file in the
    new location.

    -Raja

  5. #5
    Join Date
    Jan 2004
    Location
    Nottinghamshire, UK
    Posts
    14

    Question Move DB files

    Take it nice and slowy, I am a newbie to Oracle!! Not actually done the course yet but the manager has left and I have got the job of looking after the DB. Could you explain to me a bit more clearly?

    I assume when you take the DB offline you use Enterprise manager console yeah?

    i understand that you have got to take it offline before you move it! But how do you tell it where the "new" files are?

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    launch sqlplus

    connect as sys

    the following are the commands to enter.


    1. alter tablespace users offline;

    2. from your operating system prompt copy files from
    current location to new location.
    (eg. in windows we copy from d drive to e drive
    copy d:\oracle\oradata\test\users01.dbf e:\oracle\oradata\test
    )

    3. alter database rename file 'd:\oracle\oradata\test\users01.dbf' to 'e:\oracle\oradata\test\users01.dbf' ;

    4. alter tablespace users online;


    and very important step is read oracle documentation,
    if you want to continue as oracle DBA.

    -Raja

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Take a backup first

    savourys, my advice in your situation would be to phone Oracle for a half-day intervention. It's not difficult and it would only take half an hour - but it's your management should be carrying the can, not you.

  8. #8
    Join Date
    Jan 2004
    Location
    Nottinghamshire, UK
    Posts
    14

    Talking Your Help

    Cheers muchly for everybody's help! I have managed to move the files
    now using enterprise manager console cos I couldnt get the sql to work but once I used the console I selected show sql I now know what I did wrong.

    It got abit nerve racking at one point because oracel said that the file was knacked! But I had made a copy of it!

    but all in all it is still working. Not bad for a beginner if I do say so myself!


    Cheers again
    Sean

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