Here's a trick I like to reduce the downtime associated with moving
large datafiles:
- Place the tablespace that contains the file(s) you want to move
into backup mode:
- alter tablespace <name> begin backup;
- Copy the file(s):
- cp <oldfilespec> <newfilespec>
- If you are moving multiple files, you can do this in parallel
- Take the tablespace out of backup mode:
- alter tablespace <name> end backup;
- (Now the very brief downtile begins)
Take the tablespace offline immediate
- alter tablespace <name> offline immediate;
- Issue the rename:
- alter database rename file <oldfilespec> to <newfilespec>;
- Recover the new file(s) or the whole tablespace (should be quite
brief)
- recover [datafile|tablespace] <name>;
- (Now the very brief downtime ends)
Bring the tablespace online again:
- alter tablespace <name> online;
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Thu, 25 Oct 2001, Rachel Carmichael wrote:
> depends on what you mean by downtime.
>
> yes you can do it without taking down the database and the instance,
> no, you can't do it without at least taking the tablespace offline.
>
> alter tablespace x offline;
> in os, make a copy of all datafiles in the new location
> alter database move file 'y' to 'z';
> <repeat for all datafiles>
> alter tablespace online;
>
>
> --- Greg Moore <sqlgreg_at_pacbell.net> wrote:
> > Can I move a tablespace and all it's associated datafiles to a new
> > disk
> > device with no downtime?
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Greg Moore
> > INET: sqlgreg_at_pacbell.net
> >
> > 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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Make a great connection at Yahoo! Personals.
> http://personals.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_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
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
INET: jwilton_at_speakeasy.net
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).
Received on Thu Oct 25 2001 - 09:32:20 CDT
