Re: rename datafile without shutting down

In article <86nqd5$1t$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <86nnop$u2p$1_at_nnrp1.deja.com>,
> Christ Follower <christ_follower_at_my-deja.com> wrote:
> > I have my database running in archive mode. Is it possible to
rename a
> > datafile without even bring the tablespace offline?
> >
> > Here is what I did:
> > 1. ALTER TABLESPACE my_table_space BEGIN BACKUP;
> > 2. Use O.S. to copy the data file to 'new.dbf'.
> > 3. ALTER DATABASE RENAME FILE 'old.dbf' TO 'new.dbf';
> > ==> Oracle complains that the file is in use.
> > 4. ALTER DATABASE DATAFILE 'old.dbf' OFFLINE;
> > 5. Repeat step 3.
> > 6. ALTER DATABASE DATAFILE 'new.dbf' ONLINE;
> > ==> Oracle complains that media recovery is required.
> >
> > I was hoping to use 'begin backup' feature so Oracle wouldn't write
> > info to the datafile. But, at this point, I had no choice but run
> > recovery from server manager. Also the tablespace/datafile was
offline
> > and the application cannot continue.
> >
> > I wonder if there is a way to make such change "transparently" to
the
> > users.
> >
> > Thanks.
> >
> The begin backup is for on-line hot backups. You need to use the
alter
> tablespace rename option. Check the SQL manual for exact syntax.
>
> For all tablespaces except system the procedure is
> alter tablespace offline
> copy file at OS level
> alter tablespace rename
> alter tablespace on-line
>
> The alter database rename option is normally only used during recovery
> operations.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

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?

Thanks.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 26 2000 - 16:44:49 CST