Re: Alter database rename datafile question
Date: 1996/10/01
Message-ID: <32514702.82609940_at_newshost.us.oracle.com>#1/1
janet <janet_at_telesph.com> wrote:
>Hi,
> I'd like to move an entire instance from one logical disk to
>another. It's not a big one, only 5 tablespaces, and 4 of them
>are SYSTEM, RBS, TEMP, and TOOLS.
> Is the move as simple as:
>alter tablespace TBSP_NAME rename datafile '/disk1/system01.dbf' to
>'/disk2/system01.dbf'
The move is not quite as simple, Oracle does not move the datafiles for you, and simply copying the datafiles then issuing the above SQL may not work as Oracle could be in the process of writing to a file as you copy it.
As you wish to move files across file systems (one disk to another) you will need to take each tablespace offline copy the corresponding datafiles and then issue the alter tablespace command and then bring the tablespace back online.
However this approach will not work for the SYSTEM tablespace as you cannot take it offline. To move this tablespace you must issue a 'alter database backup controlfile to trace', this will create a SQL script in the 'user_dump_dest' directory specified in 'init<SID>.ora' (or check v$parameter). After some editing you will be able to run this in SQL*DBA or SVRMGRL having first backed-up everything and moved the SYSTEM tablespace datafiles.
If you were moving files around on the same file system under UNIX you could create links (the 'ln' command) and then issue the 'alter tablespace' command as a link does not create a copy of the file just another access path to the same one!
Hope this helps.
Robert Kent (rkent_at_uk.oracle.com)
The opinions expressed here are my own, and are not necessarily those of Oracle Corporation.
Received on Tue Oct 01 1996 - 00:00:00 CEST