-
To change the location of all the datafiles
I am imitating a database in one server to another server.My previous data files are in E Drive.Since new server doesn't have a E drive,I need to change the location of the database files in the control file.Please give me an advice..
-
re-create the control file with the correct location
-
Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Start the instance to mount state and rename all files you moved
including datafiles, online log files and tempfiles.
The old names you get from controfile with these queries:
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;
You also need to adjust pfile/spfile if the controlfiles moved.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Originally Posted by ales
Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Start the instance to mount state and rename all files you moved
including datafiles, online log files and tempfiles.
The old names you get from controfile with these queries:
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;
You also need to adjust pfile/spfile if the controlfiles moved.
the problem with that is havign run a command many times or doing some code to loop round all the files
doing a global replace in a control file is much easier
-
Originally Posted by davey23uk
the problem with that is havign run a command many times or doing some code to loop round all the files
doing a global replace in a control file is much easier
Yes, I admit I proposed an old-school approach :-)
With a bit of scripting it's not much work.
Ales The whole difference between a little boy and an adult man is the price of toys
-
Something like this might work. I guess you run the query on the source database, and then startup nomount on the target database and apply the output. You can also recreate the control file by 'alter database backup control file to trace;' and then edit that file so that you can create new control files on the target system.
Code:
select 'ALTER DATABASE RENAME FILE ' || name
|| ' TO ' ||
REPLACE(name, 'E:\', 'D:\') ||';'
from ( select name
from v$datafile
union all
select member name
from v$logfile
union all
select name
from v$tempfile )
order by name;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|