:

How to Move Oracle Datafiles

You can move your oracle data files, temp files and redo log files very easily using commands which dynamically create sql from sql within a pl/sql block. This approach has many advantages, such as very little manual work or risk of the scripts being out of date if the rename commands were created in advance.

Steps to Move Datafiles, TEMP files and REDO Logs

  • Check current locations for datafiles – select name from v$datafile;
  • Check current locations for log files – select member from v$logfile;
  • Check current TEMP file locations – select name from v$tempfile;

The statements below are the pl/sql blocks which will run to rename the datafiles, REDO log files and TEMP files. You just need to replace the file paths with the old and new values.

Note: It is best to make sure the file paths are all upper case, or at least for the old file locations because that is how they are stored in the v$ views.

The PL/SQL Code

begin
for i in
(select 'alter database rename file
    '''||name||''' to '''||replace(name,'<old_path>','<new_path>')||''''
 as cmd
from v$datafile
where name like 'O:\ORACLE\ORADATA\%')
loop
execute immediate i.cmd;
end loop;
end;
/

begin
for i in
(select 'alter database rename file
    '''||member||''' to '''||replace(member,'<old_path>','<new_path>')||''''
 as cmd
from v$logfile
where member like 'O:\ORACLE\ORADATA\%')
loop
execute immediate i.cmd;
end loop;
end;
/

begin
for i in
(select 'alter database rename file
    '''||name||''' to '''||replace(name,'<old_path>','<new_path>')||''''
 as cmd
from v$tempfile
where name like 'O:\ORACLE\ORADATA\%')
loop
execute immediate i.cmd;
end loop;
end;
/

Now that you have prepared the pl/sql blocks which can be run after the files have been physically moved, you can prepare a new init.ora file.

INIT.ora File Changes & Datafile Copy

create pfile='C:\TEMP\initTEST.ora' from SPFILE='C:\TEMP\initSPFILETEST.ora';

Note: If you are already using a Pfile then you can modify it directly, just remember to take a backup of it first.

-- Shut the database down
SHUTDOWN IMMEDIATE

Once the database has been shutdown cleanly you should physically move all of the files to their new location. This may take a while so leave that running and at the same time you should copy over all of the configuration files, such as anything in the diagnostic_dest folder (if using 11g), or the admin folders (prior to 11g), which include:

  • control files
  • log_archive_dest_n
  • adump, udump, bdump, cdump

The easiest thing to do is look in your init.ora file and search for anything with the old path and move it. At the same time you are checking this, edit the init.ora file so that the old locations are changed to the new ones.

Note: If you have really large TEMP files it’s probably easier to drop them and ***re-create the TEMP tablespace in the new location.

Rename Original Directory & STARTUP

When the copy of the files has finished, make sure that you rename the old folders so that you can’t mistakenly start the database using any of the old files. This also gives you a fail back approach.

Now you’ll want to start up the database in steps, checking everything is working as you go. If you want to know more about the different startup oracle database options, have a read of my other article first.

-- Use modified version of PFile to start the database
STARTUP NOMOUNT PFILE='C:\TEMP\initTEST.ora';

-- Mount the database
ALTER DATABASE MOUNT;

Now run in the pl/sql commands which are above, to rename the REDO, TEMP and Data files.

-- Open the database
ALTER DATABASE OPEN;

As you go through the steps above you should be checking the alert log for errors, although most of them will also be shown in the screen as they are likely to be fatal at this stage of proceedings.

If you decided to drop and re-create your temporary tablespace and are experiencing issues, have a read of my other article on how to drop Oracle tempfiles. As a final check, look in the alert log and make sure that there are no unexpected errors.

Good luck with the move and if something goes wrong, don’t panic!

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *