Rename/move all files/datafiles in a database to another location
Rename/move all files/datafiles in a database to another location
old location: /oracle/mnt02/oradata/orcl1
new location: /oracle/mnt03/oradata/orcl1
If you are not sure of the location, execute the below queries to find out the location of all files related to the database
select name from v$controlfile;
select member from v$logfile;
select name from v$datafile;
select name from v$tempfile;
select filename from v$block_change_tracking;
save this data onto a notepad.
check if you use spfile or pfile
show parameters spfile
If there is something specified under value, then it means you are using spfile otherwise you are using pfile.
If you are using spfile follow step A or else for pfile use step B.
A)
with database in open mode
alter system set control_files=’/oracle/mnt03/oradata/orcl1/control01.ctl’,'/oracle/mnt03/oradata/orcl1/control02.ctl’,'/oracle/mnt03/oradata/orcl1/control03.ctl’ scope=spfile;
shutdown immediate;
skip step B, C and D. Start copying the files.
B)
create pfile=’/oracle/mnt03/oradata/orcl1/initorcl1.ora’ from spfile;
shutdown immediate;
Now edit the pfile to change the new control file location. If the search and replace string(location) does not include a / use step C otherwise use step D. With step D, you escape the / with a \ and specify the complete location. The result of sed is captured into a new file initorcl1.ora_new Make sure the location is correct. Then backup the old initorcl1.ora as initorcl1.ora.orig and then rename initorcl1.ora_new as initorcl1.ora with move command.
c)
vi initorcl1.ora
press “esc” key then %s/mnt02/mnt03/g
press “esc” key then : then wq
skip step D. Start copying the files.
D)
sed ‘s/\/oracle\/mnt02\/oradata\/orcl1/\/oracle\/mnt03\/oradata\/orcl1/g’ initorcl1.ora > initorcl1.ora_new
mv initorcl1.ora initorcl1.ora.orig
mv initorcl1.ora_new initorcl1.ora
Start copying the files.
Copying the files:
cp /oracle/mnt02/oradata/orcl1/* /oracle/mnt03/oradata/orcl1/*
Once the files are copied, startup the database in mount mode.
If you had created pfile specified in step B and edited the control file manually as specified in step C or D (or in any other way), use the command below:
startup pfile=’/oracle/mnt03/oradata/orcl1/initorcl1.ora’ mount;
If you use spfile and used step A to change the location of the control file, use the command below:
startup mount;
It is a tedious job to write the rename file command when you have hundreds of files. The easiest way to do this is as follows:
save the data from the following queries onto a notepad
select name from v$controlfile;
select member from v$logfile;
select name from v$datafile;
So the notepad should look like the following:
/oracle/mnt02/oradata/orcl1/control01.ctl
/oracle/mnt02/oradata/orcl1/control02.ctl
/oracle/mnt02/oradata/orcl1/redo1.log
/oracle/mnt02/oradata/orcl1/redo2.log
/oracle/mnt02/oradata/orcl1/system01.dbf
/oracle/mnt02/oradata/orcl1/undotbs01.dbf
/oracle/mnt02/oradata/orcl1/sysaux01.dbf
/oracle/mnt02/oradata/orcl1/users01.dbf
/oracle/mnt02/oradata/orcl1/tools01.dbf
Now open an excel and copy this to column A
Now go the notepad and use control + h
This opens find and replace dialog box:
enter /oracle/mnt02/oradata/orcl1 in find what
enter /oracle/mnt02/oradata/orcl1 in replace with
and choose replace all
Now copy the replaced location onto the excel column B
Now in column C1, paste the following command
=C1=”ALTER DATABASE RENAME FILE ‘”&A1&”‘ TO ‘”&B1&”‘;”
The above command replaces the values from A1 and B1 and generated the entire line for you.
When you place the mouse pointer onto the bottom right corner, the pointer changes to a plus(+) sign, and then click and drag it till you address all you files.
Column C will have the entire alter database statements ready to be executed.
Now issue the alter database rename file command:
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/control01.ctl’ TO ‘/oracle/mnt03/oradata/orcl1/control01.ctl’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/control02.ctl’ TO ‘/oracle/mnt03/oradata/orcl1/control02.ctl’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/redo1.log’ TO ‘/oracle/mnt03/oradata/orcl1/redo1.log’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/redo2.log’ TO ‘/oracle/mnt03/oradata/orcl1/redo2.log’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/system01.dbf’ TO ‘/oracle/mnt03/oradata/orcl1/system01.dbf’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/undotbs01.dbf’ TO ‘/oracle/mnt03/oradata/orcl1/undotbs01.dbf’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/sysaux01.dbf’ TO ‘/oracle/mnt03/oradata/orcl1/sysaux01.dbf’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/users01.dbf’ TO ‘/oracle/mnt03/oradata/orcl1/users01.dbf’;
ALTER DATABASE RENAME FILE ‘/oracle/mnt02/oradata/orcl1/tools01.dbf’ TO ‘/oracle/mnt03/oradata/orcl1/tools01.dbf’;
Now open the database with
Alter database open;
