数据库文件由本机迁移到存储上（文件系统到文件系统）_数据库文件备份到文件存储-优快云博客

本文详细介绍了一种Oracle数据库的迁移流程，包括在线迁移、离线迁移和停库迁移三种方式。在线迁移涉及临时表空间、撤销表空间及重做日志文件的调整；离线迁移涵盖数据表空间的移动；停库迁移则包括控制文件复制和系统表空间的移动。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--测试
将test——10.0.22.69的数据文件由/oradata/test 迁移到/home/oracle/oradata/test/

1.在线迁移
--a.temp tablespace
create temporary tablespace temp2 tempfile '/home/oracle/oradata/test/temp01.dbf' size 10m;
alter database default temporary tablespace temp2;
drop tablespace temp;
create temporary tablespace temp tempfile '/home/oracle/oradata/test/temp1.dbf' size 10m;
alter database default temporary tablespace temp;
drop tablespace temp2;

--b.undo tablespace
create undo tablespace undotbs2 datafile '/home/oracle/oradata/test/undotbs02.dbf' size 10m;
ALTER SYSTEM SET undo_tablespace = undotbs2 ;
drop tablespace undotbs1;
create undo tablespace undotbs1 datafile '/home/oracle/oradata/test/undotbs1.dbf' size 10m;
ALTER SYSTEM SET undo_tablespace = undotbs1 ;
drop tablespace undotbs2 including contents and datafiles;

--c.redo files
alter system switch logfile;
alter system checkpoint;
alter database add logfile group 1 '/home/oracle/oradata/test/redo01.ctl' size 10m;
alter database add logfile group 2 '/home/oracle/oradata/test/redo02.ctl' size 10m;
alter database drop logfile group 3 '/home/oracle/oradata/test/redo02.ctl' size 10m;

2.offline或readonly迁移
--d.data tablespace
alter tablespace i_msts offline;
mv /oradata/test/i_msts01.dbf /home/oracle/oradata/test/
mv /oradata/test/i_msts02.dbf /home/oracle/oradata/test/
alter database rename file '/oradata/test/i_msts01.dbf' to '/home/oracle/oradata/test/i_msts01.dbf';
alter database rename file '/oradata/test/i_msts02.dbf' to '/home/oracle/oradata/test/i_msts02.dbf';
alter tablespace i_msts online;

alter tablespace users offline;
mv /oradata/test/users01.dbf /home/oracle/oradata/test/
alter database rename file '/oradata/test/users01.dbf' to '/home/oracle/oradata/test/users01.dbf';
alter tablespace users online;

--e.sysaux
alter tablespace sysaux offline;
mv /oradata/test/sysaux01.dbf /home/oracle/oradata/test/
alter database rename file '/oradata/test/sysaux01.dbf' to '/home/oracle/oradata/test/sysaux01.dbf';
alter tablespace sysaux online;

3.停库迁移
--f.control file
create pfile from spfile;
方法一：
shutdown immediate
startup mount
rman target/
copy current controlfile to '/home/oracle/oradata/test/control01.ctl';
shutdown immediate
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl
startup mount pfile='/oracle/product/10.2.0/db_1/dbs/inittest.ora'
alter database open resetlogs;

方法二：
cp /oradata/test/control01.ctl /home/oracle/oradata/test/control01.ctl
cp /oradata/test/control02.ctl /home/oracle/oradata/test/control02.ctl
cp /oradata/test/control03.ctl /home/oracle/oradata/test/control03.ctl
startup mount pfile='/oracle/product/10.2.0/db_1/dbs/inittest.ora'
alter database open
create spfile from pfile;
restart database

--g.system tablespace
shutdown immediate
mv /oradata/test/system01.dbf /home/oracle/oradata/test/
startup mount
alter database rename file '/oradata/test/system01.dbf' to '/home/oracle/oradata/test/system01.dbf';
alter database open