- Query the Data Dictionary to determine the current locations of your control files:
SELECT name FROM v$controlfile;
- Query the Data Dictionary to determine the current locations of your data files:
SELECT name FROM v$datafile;
- Query the Data Dictionary to determine the current locations of your online redo logs:
SELECT member FROM v$logfile;
- Keep the information from above as it will be important in later steps.
Move the files:
- Modify the parameter file with the new location of your control files.
- If you are using the old style PFILE, simply modify the CONTROL_FILES parameter with a text editor.
- If you are using the new sytle SPFILE, modify the CONTROL_FILES parameter with this command:
ALTER SYSTEM SET control_files= ('/new_dir/control01.ctl','/new_dir/control02.ctl',....) SCOPE=spfile;
- Perform a clean shutdown of the database (SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE).
- Copy the control files, online redo logs and data files with OS commands. In Unix/Linux, we typically use the 'cp' command. In Windows, you can use the 'copy' command or Windows Explorer.
- Start up the database in MOUNT mode:
- Tell the control files of the new online redo log file locations:
ALTER DATABASE RENAME FILE '/old_dir/redo01a.log' TO '/new_dir/redo01a.log';
- Repeat the previous step for each and every online redo log that is moved.
- Tell the control files of the new data file locations:
ALTER DATABASE RENAME FILE '/old_dir/file01.dbf' TO '/new_dir/file01.dbf';
- Repeat the previous step for each and every data file that is moved.
- Open the database.
ALTER DATABASE OPEN;
- Shut down the database and take a backup after all of your hard work.
Step 1 under "Move the files" above changes the pointer of the control file locations so that the control files can be found in step 3. The remaining steps change the pointers in the control files to the new online redo log and datafile locations. Once all of the pointers have been changed, the database can be opened. I cannot stress strongly enough that a backup should be taken at this point. It will be very, very difficult to recover through this operation.
One other note -- sometimes, a DBA wants to change the file's name but not necessarily the location. In Oracle's world, the file's name is the combination of the directory path and the actual file name. So '/dir1/file1.dbf' and '/dir2/file1.dbf' are two distinctly different files. So the process to change the file's name is similar to the above. The ALTER DATABASE RENAME file is used to change the location and the name.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading