This discussion is archived
12 Replies Latest reply: Sep 16, 2013 7:04 AM by EdStevens RSS

How to move Oracle datafiles to other location

af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
Currently Being Moderated

I'm using Oracle 12 on Windows Server 2008 R2

 

and,

 

would like to move Oracle database files (*.dbf) , Log Files (*.log) and Control files (*.ctl) form G:\oradata\PLAZA\DNCPIDB to G:\oradata\PLAZA\ on the same Oracle server.

 

 

Can someone provide me the step-by-step process of completing this task ?

Thanks

 

Sohail

  • 1. Re: How to move Oracle datafiles to other location
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated

    Moved to the correct forum.

     

     

    Thanks,

    Sergiusz

  • 2. Re: How to move Oracle datafiles to other location
    TSharma-Oracle Guru
    Currently Being Moderated

    Not Step by Step but you can use this link and resolve your problem by your own. Sometimes its good to try.

     

    http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php

  • 3. Re: How to move Oracle datafiles to other location
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    Tsharma

     

    I have already read that article.This article belongs to renaming & moving of Oracle database, but I would like to simply move the database without renaming it.

     

    I do not mind to try myself, but wanted to make sure that it will not break the Oracle database after I moved the files incorrectly.So can you please help me to provide steps/SQL plus commands for moving the database alongwith log and control files ?

     

    Thanks

    Sohail

     


  • 4. Re: How to move Oracle datafiles to other location
    TSharma-Oracle Guru
    Currently Being Moderated

    If you are moving the files to different location , this means you are renaming it period. For example:

    if you are moving file from 'c:\datafile\abc.dbf' to 'd:\datafile\abc.dbf', here(According to you) we are not changing file name(abc.dbf) but we are changing a path, still we say this as a file renaming.

    Again, changing the path means renaming of the file. hope this clear. You can still use the above doc.

  • 5. Re: How to move Oracle datafiles to other location
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    TSharma

     

    Thanks for the clarification. I am not an Oracle dba and this is my first working experience with Oracle databases, thatswhy couldn't understand the terminology "renaming".

     

    Again which command to use for relcation of database file with renamimg the database file name ? confused !

    SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

     

    OR

     

    SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -

    >  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';

     

    Coudl you please advice ?

     

    Thanks

  • 6. Re: How to move Oracle datafiles to other location
    JohnWatson Guru
    Currently Being Moderated

    af5bbdf1-8a49-4494-865f-4950e3aac85e wrote:

     

    I'm using Oracle 12 on Windows Server 2008 R2

     

    and,

     

    would like to move Oracle database files (*.dbf) , Log Files (*.log) and Control files (*.ctl) form G:\oradata\PLAZA\DNCPIDB to G:\oradata\PLAZA\ on the same Oracle server.

     

     

    Can someone provide me the step-by-step process of completing this task ?

    Thanks

     

    Sohail

    Moving datafiles is easy, no need to rename anything or use any OS commands:

     

    orclz> select name from v$datafile;

     

    NAME

    ----------------------------------------------------------------------------------

    C:\APP\ORACLE\ORADATA\ORCLZ\SYSTEM01.DBF

    C:\APP\ORACLE\ORADATA\ORCLZ\SYSAUX01.DBF

    C:\APP\ORACLE\ORADATA\ORCLZ\UNDOTBS01.DBF

    C:\APP\ORACLE\ORADATA\ORCLZ\USERS01.DBF

    C:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\TESTEX.DBF

    C:\ORADATA\BFTS.DBF

     

    6 rows selected.

     

    orclz> alter database move datafile 'C:\ORADATA\BFTS.DBF'to 'c:\tmp\moved.dbf';

     

    Database altered.

     

    orclz>

  • 7. Re: How to move Oracle datafiles to other location
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Since the OP runs Oracle 12c,  he can move the datafile online as John Watson demonstrates.

     

    Those reading this forum thread in the near future : Take Note : If you are running 11g or below, you must

    a. Take the Tablespace Offline

    b.  Use an OS command to move / rename / relocate the datafile

    c.  Use the SQL command ALTER DATABASE RENAME FILE 'oldname' TO 'newname';

    d. Bring the Tablespace Online

    This requires downtime on the tablespace

     

    Hemant K Chitale


  • 8. Re: How to move Oracle datafiles to other location
    EdStevens Guru
    Currently Being Moderated

    af5bbdf1-8a49-4494-865f-4950e3aac85e wrote:

     

    TSharma

     

    Thanks for the clarification. I am not an Oracle dba and this is my first working experience with Oracle databases, thatswhy couldn't understand the terminology "renaming".

     

    Again which command to use for relcation of database file with renamimg the database file name ? confused !

    SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

     

    OR

     

    SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -

    >  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';

     

    Coudl you please advice ?

     

    Thanks

     

    Both.

     

    The first command (host move ....) is telling sqlplus to issue a command to the OS to actually move the file.  Actually, I don't think it is literally "host move"  but "host <whatever the host os command is for moving a file>).

    The second command (alter database rename) isn't doing anything to the file itself, but is altering its entry in the control file ... so it is telling the database where to locate the file now that you've moved it.

  • 9. Re: How to move Oracle datafiles to other location
    gmartinca Newbie
    Currently Being Moderated

    Move the control files:

     

    1. With your database in nomount, mount, started or open, SQL> create pfile from spfile
    2. SQL> show parameters control_files (this shows you the current directory of your control files)
    3. Shutdown your database
    4. Edit the ORACLE_HOME/database/init%ORACLE_SID%.ora
    5. Set the parameter Control_file='new_directory1', 'new_directory2'
    6. Copy and paste the files to the new directory
    7. Then with the database shutdown:

            SQL>startup pfile= ORACLE_HOME/database/init%ORACLE_SID%.ora

            SQL>create spfile from pfile

     

    Edit: a mistake

  • 10. Re: How to move Oracle datafiles to other location
    EdStevens Guru
    Currently Being Moderated

    gmartinca wrote:

     

    Move the control files:

     

    1. With your database in nomount, mount, started or open, SQL> create pfile from spfile
    2. SQL> show parameters control_files (this shows you the current directory of your control files)
    3. Shutdown your database
    4. Edit the ORACLE_HOME/database/init%ORACLE_SID%.ora
    5. Set the parameter Control_file='new_directory1', 'new_directory2'
    6. Copy and paste the files to the new directory
    7. Then with the database in nomount mode:

            SQL>startup pfile= ORACLE_HOME/database/init%ORACLE_SID%.ora

            SQL>create spfile from pfile

    How can you 'startup pfile' when you already "with the database in nomount mode"?

     

    Actually, no need to create the pfile, except perhaps to have a backup position.   Just

    shutdown,

    move the control file (at the os level),

    startup nomount,

    ALTER SYSTEM SET CONTROLFILES ..... SCOPE=SPFILE; 

  • 11. Re: How to move Oracle datafiles to other location
    gmartinca Newbie
    Currently Being Moderated

    I forgot the shutdown step before startup pfile

     

    The reason to startup with pfile is that you can try the configuration without overwrite the spfile(the last stable configuration), when you are sure that all it's ok, you can overwrite the spfile.

  • 12. Re: How to move Oracle datafiles to other location
    EdStevens Guru
    Currently Being Moderated

    gmartinca wrote:

     

    I forgot the shutdown step before startup pfile

     

    The reason to startup with pfile is that you can try the configuration without overwrite the spfile(the last stable configuration), when you are sure that all it's ok, you can overwrite the spfile.

    Fair enough.  Different strokes for different folks.  For myself, I'd make the pfile (or maybe just a copy of the spfile) as a fall-back, but not actually startup with it.

Legend

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