Re: rename datafile without shutting down

The original poster's idea is a good one, and not necessarily flawed. It is true that datafiles are written during backup mode, but the original poster's method can be used to dramatically reduce the amount of time that the tablespace is unavailable for a large datafile move. It is a method I have personally used repeatedly to maintain high availability.

Basically, the procedure goes like this:

• Put the tablespace in backup mode.
• Copy the datafile to the new location.
• take the tablespace out of backup mode.
• take the tablespace offline.
• alter database rename file...;
• recover the tablespace.
• put the tablespace back online.

Using this method, the tablespace is only unavailable for the time it takes to issue the rename, recover and online commands. Above a certain size of datafile, that time is much shorter than the time needed to offline the tablespace during the whole file copy.

So, hats off to Christ Follower for a great idea to maintain better availability for users.
--
Jeremiah

Brian Peasland wrote:
>
> > Thank you for the reply. I know I can bring the tablespace offline and
> > rename the file. However, this would make the tablespace unavailable
> > for the application during the operation. That was the motivation that
> > I tried to put the tablespace into backup mode (hoping Oracle would do
> > everything with redo logs until I am done with it). I just wonder if it
> > is ever possible to rename a file without interrupting the application.
> > I also wonder if the errors I got was a complication from the backup
> > mode. Comments?
>
> You can not rename a datafile while the tablespace is online. One, you
> have to move the datafile first and then rename it in the database. Just
> renaming the datafile does not move it. It has to be moved with OS
> commands. Second, with the tablespace being online, the application is
> trying to access the tablespace (and it's datafiles). If the datafile is
> "in transit" from one location to another, then how can the application
> access it? This much you must have known because you tried to put the
> tablespace in backup mode, "hoping Oracle would do everything with redo
> logs until I am done with it". But this brings up the final point. When
> a tablespace is in backup mode, the RDMBS still writes to the datafiles!
> Putting the tablespace in backup mode just stops the SCN from being
> updated in the file header. But data is still being applied to the
> datafiles. Therefore, you can't move the datafile while it's tablespace
> is in backup mode.
>
> HTH,
> Brian
Received on Thu Jan 27 2000 - 11:52:47 CST