Re: moving datafiles

From: diversif <xnospamx.diversif_at_ix.netcom.com>
Date: 1997/06/12
Message-ID: <01bc773e$0a342b00$8b98b8cd@newmicronpc>#1/1

Dick is correct, but if the database is on-line and you can't bring it down, and the datafile does not belong to the system tablespace or rollback tablespace, you can:

1. alter the tablespace that the file belongs to OFFLINE,
2. at the OS level COPY (not MOVE) the file to the new location.
3. alter the tablespace ONLINE
4. Assuming the tablespace came on-line ok, remove the old file at the OS level
5. alter database backup controlfile to trace.

Should always do step 5 if altering the physical structure of the database.

Dick Allie <dallie_at_ionet.net> wrote in article <339EA59E.7E12_at_ionet.net>...
> muller.brenda_at_primestar.tci.com wrote:
> >
> > Hi,
> >
> > Can anyone tell me if there is a way to update the Oracle fixed tables
> > to change the name of a datafile? I can't find the real underlying
 table
> > where these names are kept, and even if I knew where it was, I
> > wouldn't know how to update it directly.
> >
> > To clarify, let's say I want to move /oradata1/name01.dbf
> > to
 /oradata01/name01.dbf
> >
> > I know I need to recreate the control files, but how can I update the
> > system DDL?
> >
> > TIA,
> >
> > Brenda
> >
> > -------------------==== Posted via Deja News

 ====-----------------------


> >       http://www.dejanews.com/     Search, Read, Post to Usenet

>
> Hi Brenda,
>
> You can change the name of the file with the alter database or alter
> tablespace command. The newfile_name has to be present when the rename
> is done and the database has to be in mount status not open status.
> ex.
> start svrmgr or sqldba in line mode.
> connect internal
> startup mount
> alter database rename '/oradata1/name01.dbf' to '/oradata01/name01.dbf';
> alter database open
> alter database backup controlfile to trace;
>
> The last statement gives you an ascii text file you can edit that shows
> all the files in your database. You should have this as part of your
> backup script when you do a cold backup. It insures that you can
> rebuild the control files incase they are lost.
>
> The above example doesn't require you to rebuild the control files. The
> alter database rename statement does that for you before you open the
> database.
>
> You must be sure the new file name is available when this is done.
>
> Hope this helps.
>
> Dick Allie - dallie_at_ionet.net
>
Received on Thu Jun 12 1997 - 00:00:00 CDT