Quick move tablespace
in [Oracle]
|
From: bob123 on 6 Jan 2010 15:18 Hi, I have to move a big tablespace with a minimum of offline time. So is it possible to do: - alter tablespace begin backup - cp datafiles - alter tablespace end backup - alter tablespace offline - recover tablespace - alter tablespace online Thanks for your help
From: Mark D Powell on 6 Jan 2010 16:00 On Jan 6, 3:18 pm, "bob123" <bob...(a)gmail.com> wrote: > Hi, > > I have to move a big tablespace > with a minimum of offline time. > So is it possible to do: > - alter tablespace begin backup > - cp datafiles > - alter tablespace end backup > - alter tablespace offline > - recover tablespace > - alter tablespace online > > Thanks for your help To move a tablespace you would actually move the datafiles under the tablespace. To do that you take the tablespace offline, Copy or move the files at the OS level, rename the files in Oracle to reflect the new OS names, and bring the tablespace back online. Now in the case of a copy at the OS level remove the old datafiles. See the DBA Administration manual for your version of Oracle. I expect it has an example. HTH -- Mark D Powell --
From: Hemant K Chitale on 7 Jan 2010 02:26 You have to take the Tablespace Offline *before* you move the datafiles and then use an ALTER DATABASE RENAME FILE 'oldlocation' TO 'newlocation' , then bring the Tablespace Online If you have multiple datafiles you could copy the datafiles in parallel OR you could do the TABLESPACE OFFLINE ; copy/move single datafile ; RENAME FILE ; TABLESPACE ONLINE cyclically -- e.g. one datafile every 6 hours or every day so that you reduce the OFFLINE time to smaller windows. Hemant K Chitale On Jan 7, 4:18 am, "bob123" <bob...(a)gmail.com> wrote: > Hi, > > I have to move a big tablespace > with a minimum of offline time. > So is it possible to do: > - alter tablespace begin backup > - cp datafiles > - alter tablespace end backup > - alter tablespace offline > - recover tablespace > - alter tablespace online > > Thanks for your help
From: Eugene Pokopac on 7 Jan 2010 09:29 1 - ALTER TABLESPACE <ts_name> OFFLINE ; 2 - At the server - Issue appropriate commands to copy ALL files in the tablespace from the OLD location to the NEW location: Unix example: mv /u015/ORACLE/ofdvdata/<filename> /u004/ORACLE/ ofdv/<filename> 3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE /u015/ORACLE/ofdvdata/<filename> TO /u004/ORACLE/ofdv/<filename> ; 4 - ALTER TABLESPACE <ts_name> ONLINE ;
From: Mladen Gogala on 7 Jan 2010 15:06
On Thu, 07 Jan 2010 06:29:02 -0800, Eugene Pokopac wrote: > 1 - ALTER TABLESPACE <ts_name> OFFLINE ; > > 2 - At the server - Issue appropriate commands to copy ALL files in the > tablespace from the OLD location to the NEW location: > > Unix example: mv /u015/ORACLE/ofdvdata/<filename> /u004/ORACLE/ > ofdv/<filename> > > 3 - ALTER TABLESPACE <ts_name> RENAME DATAFILE > /u015/ORACLE/ofdvdata/<filename> TO > /u004/ORACLE/ofdv/<filename> ; > > 4 - ALTER TABLESPACE <ts_name> ONLINE ; Eugene, how about doing a little testing? -- http://mgogala.byethost5.com |