Comments for Renaming or Moving Oracle Files
koko said...A bit incomplete.
What if I have a DB with 200 datafiles and I want to rename half of them?
Issuing the command "ALTER..." 100 is not very practical
Tim... said...I don't see why this is a problem. You have the v$ views, which can be queried to build a list of ALTER statements.
Apart from that the only alternative as far as I can see is to backup the controlfile to trace and recreate the controlfile, which still involves amending 100 file references. I don't see how this differs drastically from the approaches presented here.
I might add this method also when I get time.
Raghu said...This was very helpful for me.
Gaby said...Just to say hellow!
Michael said...The instructions worked like a treat first time.
* * * * *
Ingo said...It is very well explained how to do this.
Of course, with a bit of scripting (I just would like to mention PERL) you souldn''t have any problems to rename 100, 1000 or 100,000 datafiles.
Freddy said...Very usefull. Thanks.
Just a remark, nothing about the temporary tablespace datafiles, when you want to move all datafiles on a another drive.
After completing these steps, i've created a permanent temporary "by default" tablespace with well-placed datafile(s) and after i've dropped the old one (tablespace).
ivan said...This is a very helpful article.
martin said...great help for me
murtuja said...In System tablespace,There is only one .DBF file .I want relocate this file to another location.How to do this?
Tim... said...Follow the instructions in the datafiles section. Looks pretty clear to me!
PS. Questions on the forum!
Didik Bhudi Gunawan said...nice tutorial
Thirsty Rock said...After unsuccessfully scouring the net for a solution to the infamous ORA-00205 "error in identifying ..." this article saved my day. Renaming data/log files as described was the only way out of the trouble I got into from changing hard disks and re-installing Oracle9i.
Alex said...No need to shutdown the DB...
- Get the tablespace containing the datafile offline
- mv the file, on the OS side
- alter... rename
- Get the tbs online.
Caroline said...Clear and useful - many thanks
Daniel said...excellent! thank you!
PaulSmyth said...Just a comment about the redo log relocation.
Its best to create additional redo logs in the new location and then offline/drop the originals.
Ulan said...It's great howto! I love it/
ionos77 said...i used this commands to transfer the database on a different machine with different drive structure and worked fine!
The only thing that must done (and its not writed here) is with the temp datafile. You cannot as i understand to transfer-rename tempfile because its the only tablespace used as temp.So i create a new temp with different name as default temporary and then rename the old one to the different drive letter.then i switched again as default tablespace the old one.
MT said...Thank you very much! it was very helpful
greeting from italy
Anmatr said...I'm using XE database on win.
Initially the move datafile part did not work, I got errors
ORA-12514: TNS: Listener kann in Connect-Deskriptor angegebenen Service aktuell nicht auflösen
ORA-24324: Dienst-Handle nicht initialisiert
ORA-01041: Interner Fehler: hostdef-Erweiterung ist nicht vorhanden
But found the problem:
After the host move command a reconnect is important:
connect / as sysdba;
With that it worked perfect!
Thank you for this instructions.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Danny said...Very nice!!!
Yechiel said...Thank you.
I had to move several data files and googeled to refresh my memory. I found this and it was a great help.
Mayank said...Amazing.. works like a charm...
Greetings from India...
Bob said...A great "how to" guide. Worked first time!!!
Anjy said...A simple and clear set of instructions.. Many thanks.
MikeO said...In a DataGuard setup, this method will work for Primary, but will fail on standby if STANDBY_FILE_MANAGEMENT=auto (ORA-01275). Simply change it to "manual", ALTER DATABASE RENAME FILE..., and then set it back to "auto".
Yes. When I wrote the article I was focusing very much on an individual installation. I've added a quick note to the bottom of the article about this.
Thanks for the feedback.
Frank said...That was beautifully clear. Thanks.
Noons said...Just a quick add-on to this.
In 11gr2 (and I believe also in 10g?) it's possible to move a datafile without having to bounce the instance.
Just alter tablespace offline, move the file, alter tablespace rename datafile and then alter tablespace online.
Bingo, all done and no need wait for upgrade to 12c.
See here: http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
Tim... said...Noons: I've added it and given you a shout out. :)
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!