Move Oracle Database Datafiles and Log Files to New Disk

calendar Apr 6, 2024 / · 6 min read · dba v$datafile v$logfile Datafiles Management Oracle Database Administration  ·
Share on: linkedin copy

Moving Oracle Database Datafiles and Log Files to Another Disk

Where does ALTER DATABASE RENAME FILE get its long list of file names when a database moves from one mount point to another? From v$datafile and v$logfile. This script writes the whole list for you, ready to review and run.

Purpose

When a database outgrows its disk, or files must move from /u01 to new storage on /u02, every datafile and every online redo log member has to be renamed in the control file after it is copied. Typing those statements by hand for dozens of files invites mistakes. This script builds them: it reads each file name, swaps the old mount point for the new one, and spools the result to rename.sql.

The script only writes text. Nothing is renamed until you run rename.sql, and nothing is moved on disk by the script at all. Oracle's Administrator's Guide is explicit on that point: renaming a file this way changes only the pointers to the files, as recorded in the control file, and the files themselves must first be copied or moved with operating system commands.

Code

 1set trimspool on wrap off
 2set heading off
 3set verify off
 4set pages 1000 lines 100
 5
 6spool rename.sql
 7
 8select 	'alter database rename file ''' || 
 9	name || ''' to ''' || 
10	replace(name || ''';', '/u01', '/u02')
11from 	v$datafile
12/
13
14select 	'alter database rename file ''' || 
15	member || ''' to ''' || 
16	replace(member || ''';', '/u01', '/u02')
17from 	v$logfile
18/
19
20spool off

Code Breakdown

SQL*Plus settings

  • set trimspool on wrap off removes trailing spaces from spooled lines and stops long lines wrapping, so each generated statement stays on one line.
  • set heading off leaves column headings out of the file, so rename.sql holds only statements.
  • set verify off stops SQL*Plus echoing substitution variable changes.
  • set pages 1000 lines 100 sets a long page and a 100-character line.

The spool

spool rename.sql sends everything that follows to a file in the current directory, and spool off closes it. The file is the deliverable: open it, check every line, then run it with @rename.sql.

The datafile query

The first select reads v$datafile, where Oracle documents NAME as the name of the data file. For each row it builds alter database rename file '<old name>' to '<new name>';. The doubled quotes (''') produce a single quote character in the output, so each file name lands inside quotes in the generated statement.

The redo log query

The second select does the same for v$logfile. The column there is MEMBER, not NAME, because a redo log group can have several member files and each member is renamed separately.

The path swap

replace(name || ''';', '/u01', '/u02') appends the closing quote and semicolon to the file name, then replaces /u01 with /u02. Change those two strings to your own source and target paths before running it. replace swaps every occurrence of the string, so a path such as /u01/oradata/u01data/ would change in two places; check paths like that in the output.

Key Points

  • The control file is what changes. ALTER DATABASE RENAME FILE updates the control file's record of where each file lives. Copy the files with operating system commands before running the statements.
  • Redo log members need the database mounted, not open. Oracle's procedure for redo logs is: shut down, move the files with operating system commands, start the database with STARTUP MOUNT, run the rename statements, then open the database.
  • Datafiles can move offline or online. An offline datafile, or the files of an offline tablespace, can be renamed this way while the database is open. Oracle also provides ALTER DATABASE MOVE DATAFILE to rename or relocate a datafile while the database is open and users are accessing it.
  • Tempfiles are not in this list. v$datafile does not include tempfiles; handle them separately, usually by adding a new tempfile on the new disk and dropping the old one.

Insights and Best Practices

Choose the method by outage window

For a short planned outage, the classic method works for everything at once: shut down, copy all files, STARTUP MOUNT, run rename.sql, ALTER DATABASE OPEN. When there is no outage window, move datafiles one at a time with ALTER DATABASE MOVE DATAFILE. Oracle documents a KEEP option that keeps the old copy and uses the new one only after the move completes, and a REUSE option to overwrite an existing file at the destination; without REUSE, an existing file of the same name causes the statement to return an error rather than be overwritten.

Split the generated file when the methods differ

rename.sql holds datafile and redo log statements together. If you rename datafiles with the database open and their tablespace offline, cut the file in two: the datafile lines for the open database, and the redo log lines for the mounted database.

Check the copy before you rename

Before running the statements, list the new directory and confirm each file is there with the expected size. A missed copy is much simpler to catch before the database tries to open the file at its new path.

Take a backup first

Take a backup of the database, or at least of the control file, before renaming. A wrong path in rename.sql is easy to fix while you still have the old control file.

When to Run This

  • Moving a database to new storage or a new mount point.
  • Splitting datafiles and redo logs onto separate disks.
  • Renaming files to a new naming standard during a maintenance window.
  • Preparing a restored database whose file paths differ from the source.

Troubleshooting Common Issues

If rename.sql contains blank lines or SQL> prompts, the SQL*Plus settings did not take effect; run the script as a file with @ rather than pasting it. If a rename fails because the file cannot be found, the copy did not reach the new path or the new path in the statement is wrong; compare the statement with the directory listing. If the database will not open after a redo log rename, check v$logfile for the member it reports and confirm that file exists at the new location. For datafiles moved with ALTER DATABASE MOVE DATAFILE, an error about an existing file means the destination already has a file of that name; remove it, or add REUSE once you are sure it is not needed.

References

Posts in this series