This page describes how a database can be moved. Includes tablespace, table and objects movement. All explanations assume one datafile per tablespace and all tablespaces have logging turned on.


The system tablespace
Can be moved to another disk (or ASM group) by moving all individual datafiles. Recipe:
  • Make an image copy with rman:
RMAN> COPY DATAFILE '[full name]' TO '[some disk group, no path for automatic file management]';
  • Start the database in mount mode. This can be done in a RAC environment using:
srvctl start  database -d oradb01 -o mount 
  • Modify the control file to point to the new location:
alter database rename file '[old file, full path]' to '[new file, full path]';
  • Recover the datafile (also from mount mode).
  • For a RAC installation to open all instances this can be done in the end:
srvctl stop  database -d oradb01
srvctl start  database -d oradb01
  • Delete old files using rm or asmcmd
There is NO reason why the control file has to be dumped as a script, modified and recreated!

Undo tablespaces
There must be at least one undo tablespaces opened. Moving the undo tablespaces as for the system tablespace is possible but very slow. A better solution would be to create a new tablespace and drop the old one.

Regular tablespaces
The same strategy as with the system tablespace can be applied. This implies that the to be shutdown which might not be desired. Another solution that reduces the time the database is offline:
  • Make datafile offline:
alter database datafile '[full name]' offline;  
  • Make an image copy with rman. See above for example.
  • Using sqlplus rename the old file to the new copy
  • This step is optional: use rman to
  • Recover datafile in the same way as for the system tablespace
  • Make the datafile online:
alter database datafile '[full name]' online;  

-- RaduStoica - 22 Oct 2007

Edit | Attach | Watch | Print version | History: r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r1 - 2007-10-22 - RaduStoica
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LHCb All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback