Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: best way to move datafile?

Re: best way to move datafile?

From: Frank an der Heiden <fh_at_energotec.de>
Date: Wed, 28 Feb 2001 10:51:43 +0100
Message-ID: <97ihmu$9a9$1@oxygen.technet.net>

Hi,

As myron already said, it's important to shutdown the database (or set the tablespace offline) before copying the datafile on OS-level. Otherwise you'll get in trouble with the SCN (System change number). The SCN is an internal kind of timestamp which is used by the oracle server to provide the consistency of all files, belonging to the database. The oracle server increments this number after every transaction. If you copy the datafile BEFORE shutting down (or setting the tablespace offline),
the SCN of your datafile copy is older than in your data-dictionary, because the
oracle server increments this number after the shutdown. Therefore a recovery of the copied datafile would be necessarry which could only be successful, if you have the necessarry Archive-log. (=> DB must run in archivelog-mode).

If I remember well, you can see the SCN in the view V$DATAFILE_HEADER.

so long
frank

btw: For leaving the database online I would prefer to use the second method.
if the tablespace you want to move is not the SYSTEM tablespace

"Glen A. Sromquist" <stromqgl_at_alpac.ca> schrieb im Newsbeitrag news:%fRm6.365$oN4.56956_at_news0.telusplanet.net...
> A datafile can be moved by either of the following procedures correct?
>
> do an OS copy of the datafile to new location
> shutdown the database & startup mount but not open
> alter database rename file "oldname' 'newname'
> alter database open
> backup the controlfile
> OS delete the original file
>
>
> OS copy the file to new location
> alter tablespace "name" offline
> alter tablespace rename datafile 'oldname' 'newname'
> alter tablespace 'name' online
> backup the controlfile
> OS delete the original file
>
> (of course a full backup was done beforehand...)
>
> both proc's achieve the same result - which is preferable? With the alter
> tablespace proc the process happens without an outage.
>
> Like to hear some opinions on this....
>
>
Received on Wed Feb 28 2001 - 03:51:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US