Re: rename datafile without shutting down

From: <markp7832_at_my-deja.com>
Date: Thu, 27 Jan 2000 19:22:48 GMT
Message-ID: <86q5u1$mbs$1@nnrp1.deja.com>

In article <86ntd0$2cb$1_at_nnrp1.deja.com>,   Christ Follower <christ_follower_at_my-deja.com> wrote:
> 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.
> >
> > --

>

> 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.

>
I think Brian's post answered the rest of your questions. You can not rename a file while it is in-use; you must take it away from the on- line system.

--
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. Received on Thu Jan 27 2000 - 13:22:48 CST