Moving Oracle Database files Automatically

Often you will want to move database files to a new location.

 

This means locating the files, scripting the mv commands, scripting the alter database commands while remembering where everything is.

 

The following scripts can be used to perform this function and they cover control files, data files, temp files and redo log files. No mess, no worry.

 

If the files exist in multiple locations, the script must be run for each. This allows you move the files as groups either to the same or alternative locations. This is particularly important when it comes to redo logs and control files which should be in different places.

 

The first script is list_file.sql. This simple display the locations of your files. It is able to take a parameter which is inserted at the start of each line output from note purposes.

 

For example…

 

SQL> @list_files 'Now'

 

Now:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

Now:ORCLSA:CTRL:/u03/oradata/other/control02.ctl

Now:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

Now:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

Now:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

Now:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

Now:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

Now:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

Now:ORCLSA:DATA:/u02/oradata/jh/system.dbf

Now:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

Now:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

Now:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

Now:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

Now:ORCLSA:TEMP:/u03/oradata/other/temp.tmp

 

As you can see, two of the database files are located in a different directory from the others. These are highlighted above in red.

 

The list_files.sql script is also used within the move_files.sql script to provide before and after details.

 

Move_Files.sql also has default locations for the files built in but this can easily be changed, and these can also be overridden during each run.

 

So now to demonstrate the process, the first example will move the two files in /u03/oradata/other to /u02/oradata/orclsa.

 

Example 1 (comments in red, actions in green)

 

SQL> @move_files

 

:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

:ORCLSA:CTRL:/u03/oradata/other/control02.ctl

:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

:ORCLSA:DATA:/u02/oradata/jh/system.dbf

:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

:ORCLSA:TEMP:/u03/oradata/other/temp.tmp                          

 

the display of list_files.sql occurshere to provide the necessary imformation to assist your selections.

You can now override the default directories as necessary.

 

Press enter the FROM directory (/u02/oradata/jh)? /u03/oradata/other <ENTER>

Press enter the TO directory (/u03/oradata/orclsa)? <ENTER>

 

The following output is also spooled to script goforit.sql. The display here is for you so you can be sure it’ll do what it’s expected to do.

 

@list_files 'Before'

 

set head off

 

alter system set control_files =

'/u02/oradata/jh/control01.ctl',

'/u03/oradata/orclsa/control02.ctl'

scope = spfile;

 

shutdown immediate

 

!mv /u03/oradata/other/control02.ctl /u03/oradata/orclsa/control02.ctl

 

startup mount

 

!mv /u03/oradata/other/temp.tmp /u03/oradata/orclsa/temp.tmp

alter database rename file '/u03/oradata/other/temp.tmp' to '/u03/oradata/orclsa/temp.tmp';

 

 

alter database open;

 

select 'Finished on ORCLSA' from dual;

 

@list_files 'After'

 

The next few lines give you a chance to review what is going to take place and abort if necessary.

 

******************************************************************

Please check the details above.

CTRL+C to abort, Press <ENTER> to continue with changes  <ENTER>

 

Having pressed <ENTER>, the goforit.sql script is actioned to move the files.

Again the list_files.sql script is run before and after the work for documentary purposes.

 

Before:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

Before:ORCLSA:CTRL:/u03/oradata/other/control02.ctl

Before:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

Before:ORCLSA:DATA:/u02/oradata/jh/system.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

Before:ORCLSA:TEMP:/u03/oradata/other/temp.tmp

Database closed.

Database dismounted.

ORACLE instance shut down.

 

ORACLE instance started.

 

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             281021112 bytes

Database Buffers          167772160 bytes

Redo Buffers                6008832 bytes

Database mounted.

 

 

Finished on ORCLSA

 

Finally we see the results

 

After:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

After:ORCLSA:CTRL:/u03/oradata/orclsa/control02.ctl

After:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

After:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

After:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

After:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

After:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

After:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

After:ORCLSA:DATA:/u02/oradata/jh/system.dbf

After:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

After:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

After:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

After:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

After:ORCLSA:TEMP:/u03/oradata/orclsa/temp.tmp

 

Example 2

 

Now we run the move_files.sql script to move the remaining files to their new directory.

 

SQL> @move_files

 

:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

:ORCLSA:CTRL:/u03/oradata/orclsa/control02.ctl

:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

:ORCLSA:DATA:/u02/oradata/jh/system.dbf

:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

:ORCLSA:TEMP:/u03/oradata/orclsa/temp.tmp

 

Press enter the FROM directory (/u02/oradata/jh)? <ENTER>

Press enter the TO directory (/u03/oradata/orclsa)? <ENTER>

 

@list_files 'Before'

 

set head off

 

alter system set control_files =

'/u03/oradata/orclsa/control01.ctl',

'/u03/oradata/orclsa/control02.ctl'

scope = spfile;

 

shutdown immediate

 

!mv /u02/oradata/jh/control01.ctl /u03/oradata/orclsa/control01.ctl

 

startup mount

 

!mv /u02/oradata/jh/system.dbf /u03/oradata/orclsa/system.dbf

alter database rename file '/u02/oradata/jh/system.dbf' to '/u03/oradata/orclsa/system.dbf';

 

!mv /u02/oradata/jh/sysaux.dbf /u03/oradata/orclsa/sysaux.dbf

alter database rename file '/u02/oradata/jh/sysaux.dbf' to '/u03/oradata/orclsa/sysaux.dbf';

 

!mv /u02/oradata/jh/undo.dbf /u03/oradata/orclsa/undo.dbf

alter database rename file '/u02/oradata/jh/undo.dbf' to '/u03/oradata/orclsa/undo.dbf';

 

!mv /u02/oradata/jh/users01.dbf /u03/oradata/orclsa/users01.dbf

alter database rename file '/u02/oradata/jh/users01.dbf' to '/u03/oradata/orclsa/users01.dbf';

 

!mv /u02/oradata/jh/examples01.dbf /u03/oradata/orclsa/examples01.dbf

alter database rename file '/u02/oradata/jh/examples01.dbf' to '/u03/oradata/orclsa/examples01.dbf';

 

 

!mv /u02/oradata/jh/redo_1a.log /u03/oradata/orclsa/redo_1a.log

alter database rename file '/u02/oradata/jh/redo_1a.log' to '/u03/oradata/orclsa/redo_1a.log';

 

!mv /u02/oradata/jh/redo_1b.log /u03/oradata/orclsa/redo_1b.log

alter database rename file '/u02/oradata/jh/redo_1b.log' to '/u03/oradata/orclsa/redo_1b.log';

 

!mv /u02/oradata/jh/redo_2a.log /u03/oradata/orclsa/redo_2a.log

alter database rename file '/u02/oradata/jh/redo_2a.log' to '/u03/oradata/orclsa/redo_2a.log';

 

!mv /u02/oradata/jh/redo_2b.log /u03/oradata/orclsa/redo_2b.log

alter database rename file '/u02/oradata/jh/redo_2b.log' to '/u03/oradata/orclsa/redo_2b.log';

 

!mv /u02/oradata/jh/redo_3a.log /u03/oradata/orclsa/redo_3a.log

alter database rename file '/u02/oradata/jh/redo_3a.log' to '/u03/oradata/orclsa/redo_3a.log';

 

!mv /u02/oradata/jh/redo_3b.log /u03/oradata/orclsa/redo_3b.log

alter database rename file '/u02/oradata/jh/redo_3b.log' to '/u03/oradata/orclsa/redo_3b.log';

 

 

alter database open;

 

select 'Finished on ORCLSA' from dual;

 

@list_files 'After'

******************************************************************

Please check the details above.

CTRL+C to abort, Press <ENTER> to continue with changes  <ENTER>

 

Before:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl

Before:ORCLSA:CTRL:/u03/oradata/orclsa/control02.ctl

Before:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log

Before:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log

Before:ORCLSA:DATA:/u02/oradata/jh/system.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/undo.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/users01.dbf

Before:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf

Before:ORCLSA:TEMP:/u03/oradata/orclsa/temp.tmp

Database closed.

Database dismounted.

ORACLE instance shut down.

 

ORACLE instance started.

 

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             281021112 bytes

Database Buffers          167772160 bytes

Redo Buffers                6008832 bytes

Database mounted.

 

Finished on ORCLSA

 

After:ORCLSA:CTRL:/u03/oradata/orclsa/control01.ctl

After:ORCLSA:CTRL:/u03/oradata/orclsa/control02.ctl

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_1a.log

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_1b.log

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_2a.log

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_2b.log

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_3a.log

After:ORCLSA:LOG:/u03/oradata/orclsa/redo_3b.log

After:ORCLSA:DATA:/u03/oradata/orclsa/system.dbf

After:ORCLSA:DATA:/u03/oradata/orclsa/sysaux.dbf

After:ORCLSA:DATA:/u03/oradata/orclsa/undo.dbf

After:ORCLSA:DATA:/u03/oradata/orclsa/users01.dbf

After:ORCLSA:DATA:/u03/oradata/orclsa/examples01.dbf

After:ORCLSA:TEMP:/u03/oradata/orclsa/temp.tmp

 

 

Scripts

 

[oracle@localhost ~]$ cat list_files.sql

set head off

set feedback off

set pages 100

set verify off

select '&1:'||b.name||':CTRL:'||a.name from v$controlfile a, v$database b

union all

select '&1:'||b.name||':LOG:'||a.member from v$logfile a, v$database b

union all

select '&1:'||b.name||':DATA:'||a.name from v$datafile a, v$database b

union all

select '&1:'||b.name||':TEMP:'||a.name from v$tempfile a, v$database b;

 

[oracle@localhost ~]$

[oracle@localhost ~]$ cat move_files.sql

define fromdir = '/u02/oradata/jh'

define todir = '/u03/oradata/orclsa'

 

set head off

set lines 200

set verify off

set feedback off

 

@list_files ' '

 

prompt

 

accept fromdir default '&fromdir' prompt 'Press enter the FROM directory (&fromdir)? '

 

accept todir default '&todir' prompt 'Press enter the TO directory (&todir)? '

 

spool goforit.sql

 

-- List all files before the change occurs

select '@list_files ''Before''' from dual;

select 'set head off' from dual;

 

-- Control files parameter must be set before closing the database (spfile only)

select aaa from

(

select 0 anum,'alter system set control_files = ' aaa from dual

union

select rownum, ''''||replace(name,'&fromdir','&todir')||''''||

        case rownum when (select count(0) from v$controlfile) then null else ',' end

   from v$controlfile

union

select 99999999, 'scope = spfile;' from dual

) order by anum;

 

-- shutdown the database completely

select 'shutdown immediate' from dual;

 

-- move the control files to their new location while the database is completely closed

select '!mv '||name||' '||replace(name,'&fromdir','&todir')

   from v$controlfile

where name like '&fromdir%';

 

-- startup mount for the movement of all other files

select 'startup mount' from dual;

 

-- move the files themselves (datafiles)

select '!mv '||name||' '||replace(name,'&fromdir','&todir'),

'alter database rename file '''||name||''' to '''||replace(name,'&fromdir','&todir')||''';'

from v$datafile

where name like '&fromdir%'

/

 

-- move the files themselves (tempfiles)

select '!mv '||name||' '||replace(name,'&fromdir','&todir'),

'alter database rename file '''||name||''' to '''||replace(name,'&fromdir','&todir')||''';'

from v$tempfile

where name like '&fromdir%'

/

 

-- move the files themselves (logfiles)

select '!mv '||member||' '||replace(member,'&fromdir','&todir'),

'alter database rename file '''||member||''' to '''||replace(member,'&fromdir','&todir')||''';'

from v$logfile

where member like '&fromdir%'

/

 

-- Open the database

select 'alter database open;' from dual;

 

select 'select ''Finished on '||name||''' from dual;' from v$database;

 

--set feedback on

--set verify on

--set head on

 

-- List all files after the change occurs

select '@list_files ''After''' from dual;

 

spool off

 

prompt ******************************************************************

prompt Please check the details above.

accept aaa prompt 'CTRL+C to abort, Press <ENTER> to continue with changes '

@goforit

 

[oracle@localhost ~]$

 

 

 

Happyjohn.