数据库文件移位(一)

前言：需要把本地磁盘的所有数据文件，都移动到存储上去。
步骤概要：
1.shut immediate 停库
2.host cp dbfile   to new path
3.修改参数中controlfile的位置，把数据库启动到mount
4.rename相关文件
5.alter database open;
本次具体实施步骤：
一、查好需要移动的文件，以防漏掉。
SQL>
select name from v$datafile
union all
select name from v$controlfile
union all
select name from v$tempfile
union all
select member from v$logfile;

NAME
--------------------------------------------------
/oracle/oradata/test/system01.dbf
/oracle/oradata/test/undotbs01.dbf
/oracle/oradata/test/sysaux01.dbf
/oracle/oradata/test/users01.dbf
/oradata1/test01.dbf             <--------------注意该文件不在默认目录下。
/oracle/oradata/test/control01.ctl
/oracle/oradata/test/control02.ctl
/oracle/oradata/test/control03.ctl
/oracle/oradata/test/temp01.dbf
/oracle/oradata/test/redo03.log
/oracle/oradata/test/redo02.log
/oracle/oradata/test/redo01.log

12 rows selected.

SQL>
二、用语句生成rename语句。
datafile：
SQL> select ‘alter database rename file ‘||chr(10)||chr(39)||name ||chr(39)||chr(10)||‘ to ‘‘‘||replace(name,‘/oracle/oradata/test‘,‘/oracle/oradata/u1/test‘) ||‘‘‘;‘ aaa from v$datafile;

AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
‘/oracle/oradata/test/system01.dbf‘
 to ‘/oracle/oradata/u1/test/system01.dbf‘;

alter database rename file
‘/oracle/oradata/test/undotbs01.dbf‘
 to ‘/oracle/oradata/u1/test/undotbs01.dbf‘;

alter database rename file
‘/oracle/oradata/test/sysaux01.dbf‘
 to ‘/oracle/oradata/u1/test/sysaux01.dbf‘;

alter database rename file
‘/oracle/oradata/test/users01.dbf‘
 to ‘/oracle/oradata/u1/test/users01.dbf‘;

alter database rename file
‘/oradata1/test01.dbf‘
 to ‘/oracle/oradata/u1/test/test01.dbf‘;                <------------------------手动修改一下。


CONTROLFILE:
控制文件位置修改不需要通过rename，直接修改参数文件就行。



tempfile:
SQL> select ‘alter database rename file ‘||chr(10)||chr(39)||name ||chr(39)||chr(10)||‘ to ‘‘‘||replace(name,‘/oracle/oradata/test‘,‘/oracle/oradata/u1/test‘) ||‘‘‘;‘ aaa from v$tempfile;

AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
‘/oracle/oradata/test/temp01.dbf‘
 to ‘/oracle/oradata/u1/test/temp01.dbf‘;


redolog
SQL> select ‘alter database rename file ‘||chr(10)||chr(39)||member ||chr(39)||chr(10)||‘ to ‘‘‘||replace(member,‘/oracle/oradata/test‘,‘/oracle/oradata/u1/test‘) ||‘‘‘;‘ aaa from v$logfile
  2  ;

AAA
--------------------------------------------------------------------------------------------------------------
alter database rename file
‘/oracle/oradata/test/redo03.log‘
 to ‘/oracle/oradata/u1/test/redo03.log‘;

alter database rename file
‘/oracle/oradata/test/redo02.log‘
 to ‘/oracle/oradata/u1/test/redo02.log‘;

alter database rename file
‘/oracle/oradata/test/redo01.log‘
 to ‘/oracle/oradata/u1/test/redo01.log‘;

三、停库，拷贝文件
SQL>shut immediate;
SQL>!
cp /oracle/oradata/test/*    /oracle/oradata/u1/test/
cp  /oradata1/test01.dbf   /oracle/oradata/u1/test/

四、修改一下参数文件对controlfile的描述。

五、SQL>startup mount
         SQL>执行第二步生成的rename语句。
         SQL>alter database open;