RE: RE: Moving datafiles

Doug,

An aclaration: you can only put a tablespace or a datafile offline if the database is in ARCHIVELOG mode. You can issue an offline drop when the database is in NOARCHIVELOG mode. However I do not recomend it because of the risk of losing the information from the redo.

After you put a tablespace/datafile offline, Oracle ALWAYS requires that you do media recovery on that datafile: recover datafile 'xxx'; ( you can issue a recover tablespace when the database is open ). Then you can put it online.

Another thing that comes to my mind. If you want to rename a datafile in a ARCHIVELOG mode database and you choose to put the datafile offline, you have to do it with the NORMAL and not IMMEDIATE. Because with an OFFLINE NORMAL Oracle issues a checkpoint.

And as Gaja said you can not put the tablespace SYSTEM offline.

Regards.

> -----Mensaje original-----
> De: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> Enviado el: Jueves 6 de Julio de 2000 14:28
> Para: Multiple recipients of list ORACLE-L
> Asunto: Re:RE: Moving datafiles
>
> Doug,
>
> Your comment about offlining the tablespace and the effect of
> that in running into the "datafile needs recovery" message is a
> valid one. But if the database is in ARCHIVELOG mode, it is
> easy enough to issue a "recover tablespace xxx" before the
> tablespace is brought online.
>
> The message about one or more datafiles needing recovery in an
> "offlined" tablespace is indicative of probably a checkpoint
> and/or a log switch that has occured in the interval between the
> tablespace being "offlined", datafile(s) being relocated and the
> tablespace being "onlined". There are absolutely no issues with
> issuing a "recover tablespace xxx" at that stage and it just
> ends up synching up the header(s) of the datafile(s).
>
> I do agree with your methodology in you are running in
> NOARCHIVELOG mode, when one runs the potential risk of n log
> switches occuring during the operation, where n is the number of
> redo log groups in the database. Of course the SYSTEM and
> ROLLBACK tablespaces can be relocated only by the method that
> you outlined.
>
> If we are talking about a normal production database, it may not
> be too ambitious to assume that a "shutdown" is not feasible.
> In that case "offlining", copying the datafile(s) and "onlining"
> the tablepace will be the only option. Just wanted to share
> some thoughts....
>
> Best Regards,
>
> Gaja.
>
> --- dgoulet_at_vicr.com wrote:
> > Call me paranoid if you like, but my desired course of action
> > on this has always
> > been:
> >
> > 1) shutdown the database (either normal or immediate, but NOT
> > abort)
> > 2) cp /old_dir/old_file_name to /new_dir/new_file_name
> > 3) startup mount the database
> > 4) alter system rename file '/old_dir/old_file_name' to
> > '/new_dir/new_file_name';
> > 5) alter database open If the db opens at this point matters
> > should be well.
> >
> > 6) check date/time stamps on both the old file and the new
> > file. The new file
> > date/time stamp should be just a minute or so ago while the
> > old file date/time
> > stamp should be earlier. If so, delete the old file.
> >
> > Why one would ask?? I've trashed a couple of db's by not
> > following these rules
> > in the past. If you try to do this by offlining the
> > tablespace you can run into
> > the "datafile needs recovery" message. Also a mv is a one way
> > thing. If you
> > happen to mv from a good disk drive to a bad one there is NO
> > fall back. (Been
> > there, done that, and it still smarts!!!)
> >
> > Dick Goulet
> >
> > ____________________Reply Separator____________________
> > Author: Trassens Christian <trassenc_at_TELEFONICA.COM.AR>
> > Date: 7/6/00 5:27 AM
> >
> > Yes. However or the database is down or you put the tablespace
> > offline
> > first.
> >
> > Regards.
> >
> > > -----Mensaje original-----
> > > De: Solis Llera, Eduardo [SMTP:esolis_at_mapfre.com]
> > > Enviado el: Jueves 6 de Julio de 2000 07:54
> > > Para: Multiple recipients of list ORACLE-L
> > > Asunto: RE: Moving datafiles
> > >
> > > yes that is correct.
> > >
> > > > -----Mensaje original-----
> > > > De: Weerd de E.C. Kirsten
> > [SMTP:Kirsten.deWeerd_at_oranjewoud.nl]
> > > > Enviado el: jueves 6 de julio de 2000 11:44
> > > > Para: Multiple recipients of list ORACLE-L
> > > > Asunto: Moving datafiles
> > > >
> > > > Hi all,
> > > >
> > > > The simplest things sometimes keep confusing me, so
> > I'll just ask
> > > > and see if someone agress or not :-)
> > > >
> > > > I need to move some datafiles around on Hp-UX 10.20
> > > >
> > > > I figure I can do this in two steps :
> > > >
> > > > 1. on the OS : mv original_dir\datafile.dbf
> > new_dir\datafile.dbf
> > > >
> > > > 2. in the DB : alter database rename file
> > original_dir\datafile.dbf to
> > > > new_dir\datafile.dbf
> > > >
> > > > Correct ???
> > > >
> > > > Greets,
> > > >
> > > > Kirsten
> > > > Living near Antwerp, Belgium
> > > > Working near Breda, Netherlands
> > > >
> > > > --
> > > > Author: Weerd de E.C. Kirsten
> > > > INET: Kirsten.deWeerd_at_Oranjewoud.nl
> > > >
> > > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > > > San Diego, California -- Public Internet access /
> > Mailing Lists
> > > >
> >
> --------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > > > (or the name of mailing list you want to be removed from).
> > You may
> > > > also send the HELP command for other information (like
> > subscribing).
> > > --
> > > Author: Solis Llera, Eduardo
> > > INET: esolis_at_mapfre.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > > San Diego, California -- Public Internet access /
> > Mailing Lists
> > >
> >
> --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from).
> > You may
> > > also send the HELP command for other information (like
> > subscribing).
> > --
> > Author: Trassens Christian
> > INET: trassenc_at_TELEFONICA.COM.AR
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > San Diego, California -- Public Internet access /
> > Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> > You may
> > also send the HELP command for other information (like
> > subscribing).
> > --
> > Author:
> > INET: dgoulet_at_vicr.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > San Diego, California -- Public Internet access /
> > Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> > You may
> > also send the HELP command for other information (like
> subscribing).
>
>
> =====
> Gaja Krishna Vaidyanatha
> Director, I-O Management Products
> Quest Software Inc.
> (972)-304-1170
> gajav_at_yahoo.com
>
> "Opinions and views expressed are my own and not of Quest"
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo! Messenger.
> http://im.yahoo.com/
> --
> Author: Gaja Krishna Vaidyanatha
> INET: gajav_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Thu Jul 06 2000 - 17:43:32 CDT