12 Replies Latest reply: Sep 16, 2013 9:04 AM by EdStevens RSS

    How to move Oracle datafiles to other location

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

      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

          Moved to the correct forum.

           

           

          Thanks,

          Sergiusz

          • 2. Re: How to move Oracle datafiles to other location
            TSharma-Oracle

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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.