Oracle 数据文件移动步骤

ORACLE数据文件移动步骤

一、现创建好目标路径
二、关闭数据库和监听(该库里面只有一个用户表空间为sms所以使用tablespace offline和关闭数据库的方法所受影响的时间是一样的)
三、cp源文件到目标路径
四、startup mount pfile=$ORACLE_HOME/dbs/initsid.ora 然后使用下面语句更新控制文件的信息(如果控制的文件也要移动的话那么更改spfile文件在mount之前)

SQL> select 'alter database rename file '''||file_name||''' to '''||file_name||''' ;' from dba_data_files;
alter database rename file '/data/oradata/caitong/users01.dbf' to '/data1/oradata/caitong/users01.dbf';
alter database rename file '/data/oradata/caitong/sysaux01.dbf' to '/data1/oradata/caitong/sysaux01.dbf';
alter database rename file '/data/oradata/caitong/undotbs01.dbf' to '/data1/oradata/caitong/undotbs01.dbf';
alter database rename file '/data/oradata/caitong/system01.dbf' to '/data1/oradata/caitong/system01.dbf';
alter database rename file '/data/oradata/caitong/sms.dbf' to '/data1/oradata/caitong/sms.dbf';
alter database rename file '/data/oradata/caitong/sms2.dbf' to '/data1/oradata/caitong/sms2.dbf';
alter database rename file '/data/oradata/caitong/sms3.dbf' to '/data1/oradata/caitong/sms3.dbf';
alter database rename file '/data/oradata/caitong/sms4.dbf' to '/data1/oradata/caitong/sms4.dbf';

五、create spfile from pfile
六、shutdown immediate
七、startup
八、select group#,status from v$log
九、alter database drop logfile group 1;
十、alter database add logfiel group 1('$PATH/redo01.log') size 100m
十、select * from database_properties; --查看临时表空间
十一、create temporary tablespace new_temp tempfile '$PATH/temp02.dbf' size 2g autoextend off;
十二、alter database default temporary tablespace new_temp;
十三、drop tablespace old_temp including contents and datafiles;

备注：更改用户默认表空间
alter user username temporary tablespace new_temp

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13879334/viewspace-1034748/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13879334/viewspace-1034748/