一．数据迁移说明
在DBA的工作中会遇到数据迁移的情况，比如将本地磁盘迁移到ASM，亦或者需要更换存储设备，那么我就需要迁移整个数据库的存储位置。
如果只是移动表空间或者数据文件，我们可以将表空间或者数据文件offline 之后，移动位置，在用alter database rename 和alter tablespace rename 来将位置的变化写入控制文件即可。
（1）按数据文件来：
1.先将相应的数据文件 offline
ALTER DATABASE DATAFILE 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' OFFLINE;
2.把数据文件 copy 到新位置
3. alter database rename file 'D:/ORACLE/ORADATA/DBA/TEST01.DBF' to 'D:/TEST01.DBF';
4. 介质恢复(offline 数据文件必须要介质恢复)
recover datafile 'D:/TEST01.DBF'
5. 将相应的数据文件 online
SQL>ALTER DATABASE DATAFILE 'D:/TEST01.DBF' ONLINE;
（2）按表空间来：
1.先将相应的表空间 offline
SQL>alter tablespace test offline;
2.把数据文件 copy 到新位置
3. alter tablespace TEST rename datafile 'D:/TEST01.DBF' to 'D:/ORACLE/ORADATA/DBA/TEST01.DBF'
4. 将表空间 online
SQL>alter tablespace test online;
ALTERDATABASE 与 ALTERTABLESPACE OFFLINE的区别
http://blog.csdn.net/tianlesoftware/article/details/4898800
在这里我们演示一下使用RMAN copy迁移整个数据库的操作。 这个操作数据库必须在mount 状态下进行。
二．在Mount 状态下转移整个数据库
如果只是移动数据文件，操作还是比较简单的，步骤和第一节里讲的类似，仅仅是数据库变成了mount状态，我们不需要去offline表空间或者数据文件而已。
如果是整库的转移，我们还需要考虑如下文件位置转移： 数据文件，undo ，Temp，Redo 和控制文件。
1. 移动数据文件位置
1.查看datafile 位置：
SQL>col file_name for a70
SQL>select file_name from dba_data_files
2 union all
3 select file_name from dba_temp_files;
FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/dave/users01.dbf
/u01/app/oracle/oradata/dave/undotbs01.dbf
/u01/app/oracle/oradata/dave/sysaux01.dbf
/u01/app/oracle/oradata/dave/system01.dbf
/u01/app/oracle/oradata/dave/example01.dbf
/u01/app/oracle/oradata/dave/temp01.dbf
6 rows selected.
2.创建一个新目录，用来存放数据文件：
[oracle@dave oradata]$ pwd
/u01/app/oracle/oradata
[oracle@dave oradata]$ mkdir -p/u01/app/oracle/oradata/anqing
[oracle@dave oradata]$ ls
anqing dave
3.编写RMAN 脚本：Rcopy.sh：
这里的copy可以直接敲命令，如果数据文件很多的话，还是建议用脚本，后台来跑。因为这样不会因为连接中断而出现问题，操作也会更安全一些。
export LANG=en_US
RMAN_LOG_FILE=${0}.out
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
RMAN=$ORACLE_HOME/bin/rman
export RMAN
ORACLE_SID=dave
export ORACLE_SID
ORACLE_USER=oracle
export ORACLE_USER
echo "ORACLE_SID:$ORACLE_SID">>$RMAN_LOG_FILE
echo"ORACLE_HOME:$ORACLE_HOME">>$RMAN_LOG_FILE
echo"ORACLE_USER:$ORACLE_USER">>$RMAN_LOG_FILE
echo"==========================">>$RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
$RMAN nocatalog TARGET / msglog$RMAN_LOG_FILE append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
copy datafile'/u01/app/oracle/oradata/dave/users01.dbf' to '/u01/app/oracle/oradata/anqing/users01.dbf';
copy datafile'/u01/app/oracle/oradata/dave/undotbs01.dbf' to'/u01/app/oracle/oradata/anqing/undotbs01.dbf';
copy datafile'/u01/app/oracle/oradata/dave/sysaux01.dbf' to'/u01/app/oracle/oradata/anqing/sysaux01.dbf';
copy datafile '/u01/app/oracle/oradata/dave/system01.dbf'to '/u01/app/oracle/oradata/anqing/system01.dbf';
copy datafile'/u01/app/oracle/oradata/dave/example01.dbf' to'/u01/app/oracle/oradata/anqing/example01.dbf';
copy datafile '/u01/app/oracle/oradata/dave/temp01.dbf' to '/u01/app/oracle/oradata/anqing/temp01.dbf';
/u01/app/oracle/oradata/dave/users01.dbf
release channel c2;
release channel c1;
}
EOF
echo >> $RMAN_LOG_FILE
exit
--赋执行权限：
[oracle@dave u01]$ chmod 755 rcopy.sh
4.将DB 启动到mount 状态：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2232800 bytes
Variable Size 490737184 bytes
Database Buffers 322961408 bytes
Redo Buffers 2469888 bytes
Database mounted.
5.执行rman copy 脚本：
[oracle@dave u01]$ nohup sh /u01/rcopy.sh>rcopy.out 2>&1 &
[1] 5249
[oracle@dave u01]$ jobs
[1]+ Running nohup sh/u01/rcopy.sh > rcopy.out 2>&1 &
[oracle@dave u01]$ jobs
[1]+ Done nohup sh/u01/rcopy.sh > rcopy.out 2>&1
--确认拷贝：
[root@dave anqing]# ls -lh
total 1.7G
-rw-r----- 1 oracle oinstall 347M Sep 1201:27 example01.dbf
-rw-r----- 1 oracle oinstall 571M Sep 12 01:25sysaux01.dbf
-rw-r----- 1 oracle oinstall 721M Sep 1201:26 system01.dbf
-rw-r----- 1 oracle oinstall 96M Sep 12 01:24 undotbs01.dbf
--注意，这里的temp数据文件并没有copy成功，这个后面在说明.
6. rename 数据文件
还是使用后台操作，脚本rename.sh如下：
#!/bin/ksh
sqlplus / as sysdba<< EOF
alter database rename file'/u01/app/oracle/oradata/dave/users01.dbf' to '/u01/app/oracle/oradata/anqing/users01.dbf';
alter database rename file'/u01/app/oracle/oradata/dave/undotbs01.dbf' to'/u01/app/oracle/oradata/anqing/undotbs01.dbf';
alter database rename file'/u01/app/oracle/oradata/dave/sysaux01.dbf' to'/u01/app/oracle/oradata/anqing/sysaux01.dbf';
alter database rename file'/u01/app/oracle/oradata/dave/system01.dbf' to'/u01/app/oracle/oradata/anqing/system01.dbf';
alter database rename file'/u01/app/oracle/oradata/dave/example01.dbf' to'/u01/app/oracle/oradata/anqing/example01.dbf';
exit
EOF
--给执行权限：
[oracle@dave u01]$ chmod 755 rename.sh
[oracle@dave u01]$ nohup sh rename.sh >rename.out 2>&1 &
7. open 数据库验证
SQL> set lin 120
SQL> col file_name for a70
SQL> select file_name fromdba_data_files
2 union all
3 select file_name fromdba_temp_files;
FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/anqing/users01.dbf
/u01/app/oracle/oradata/anqing/undotbs01.dbf
/u01/app/oracle/oradata/anqing/sysaux01.dbf
/u01/app/oracle/oradata/anqing/system01.dbf
/u01/app/oracle/oradata/anqing/example01.dbf
/u01/app/oracle/oradata/dave/temp01.dbf
6 rows selected.
注意这里的temp 表空间，还在原来位置。下面我们来处理temp datafile。
8. 对Temp datafile 处理
我们在第五步copy了temp file，但是实际上并没有copy成功。 RMAN copy 的错误如下：
Starting backup at 12-SEP-12
released channel: c1
released channel: c2
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of backup command at09/12/2012 01:27:20
RMAN-20201: datafile not found in therecovery catalog
RMAN-06010: error while looking updatafile: /u01/app/oracle/oradata/dave/temp01.dbf
RMAN 在备份的时候也不会备份备份locally managed 的tempfiles。原因如下：
1. Locally managed tempfiles are always setto NOLOGGING mode. So thus will have no undo.
2. Extents are managed by bitmap in each datafile to keep track of free or usedstatus of blocks in that datafile.
3. The data dictionary does not manage the tablespace.
4. Rollback information is not generated because there is no update on the datadictionary.
5. Media recovery does not recognize tempfiles.
所以我们这里在copy 数据文件时，不需要copy 临时表空间。只需要在copy 结束后给临时表空间添加一个数据文件，然后把原来目录下的临时文件drop 掉即可。这个是必须的操作，步骤如下：
--这个操作必须在dbopen 状态下执行：
SQL> alter tablespace temp add tempfile'/u01/app/oracle/oradata/anqing/temp01.dbf' size 500M autoextend off;
Tablespace altered.
SQL> alter tablespace temp drop tempfile'/u01/app/oracle/oradata/dave/temp01.dbf';
Tablespace altered.
SQL>
在次验证：
SQL> set lin 120
SQL> col file_name for a70
SQL> select file_name fromdba_data_files
2 union all
3 select file_name fromdba_temp_files;
FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/anqing/users01.dbf
/u01/app/oracle/oradata/anqing/undotbs01.dbf
/u01/app/oracle/oradata/anqing/sysaux01.dbf
/u01/app/oracle/oradata/anqing/system01.dbf
/u01/app/oracle/oradata/anqing/example01.dbf
/u01/app/oracle/oradata/anqing/temp01.dbf
6 rows selected.
这次数据文件全部转移成功，只要不进错目录，我们就可以大胆的rm掉之前的数据文件了。
http://blog.csdn.net/tianlesoftware/article/details/4974440
http://blog.csdn.net/tianlesoftware/article/details/4697417
9. 处理Redo log file：
1. 查看Redo 信息：
SQL> set lin 120
SQL> col member for a60
SQL> select group#,type, member fromv$logfile;
GROUP# TYPE MEMBER
---------- -------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/dave/redo03.log
2 ONLINE /u01/app/oracle/oradata/dave/redo02.log
1 ONLINE /u01/app/oracle/oradata/dave/redo01.log
SQL> selectgroup#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- -------------------------------
1 1 NO CURRENT 50
2 1 NO INACTIVE 50
3 1 NO INACTIVE 50
SQL> select b.group# , b.status ,a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
---------- ----------------------------------------------------------------------------
1 CURRENT /u01/app/oracle/oradata/dave/redo01.log
2 INACTIVE /u01/app/oracle/oradata/dave/redo02.log
3 INACTIVE /u01/app/oracle/oradata/dave/redo03.log
我们这里的处理方法很简单，给每个group 添加一个redo logfile，然后把旧目录下的logfile drop掉即可。 当然也可以添加几个新组，在把旧组drop掉。
注意的是，我们只能drop inactive 和unused 状态的log file，其他状态不能drop。
操作如下：
--先给每组加个成员：
SQL> alter database add logfile member'/u01/app/oracle/oradata/anqing/redo01.log' to group 1;
Database altered.
SQL> alter database add logfile member'/u01/app/oracle/oradata/anqing/redo02.log' to group 2;
Database altered.
SQL> alter database add logfile member'/u01/app/oracle/oradata/anqing/redo03.log' to group 3;
Database altered.
--验证：
SQL> select b.group# , b.status ,a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP#STATUS MEMBER
---------- ----------------------------------------------------------------------------
1 CURRENT /u01/app/oracle/oradata/dave/redo01.log
1 CURRENT /u01/app/oracle/oradata/anqing/redo01.log
2 INACTIVE /u01/app/oracle/oradata/anqing/redo02.log
2 INACTIVE /u01/app/oracle/oradata/dave/redo02.log
3 INACTIVE /u01/app/oracle/oradata/anqing/redo03.log
3 INACTIVE /u01/app/oracle/oradata/dave/redo03.log
6 rows selected.
2. Drop 旧目录的log file：
这里group 1是ACTIVE，我们不能drop，所以我们先drop 2和3，然后switch logfile，在drop group 1。
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/dave/redo03.log';
Database altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER'/u01/app/oracle/oradata/dave/redo02.log';
Database altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER'/u01/app/oracle/oradata/dave/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER'/u01/app/oracle/oradata/dave/redo01.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log forthread 1 - cannot drop members
ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/dave/redo01.log'
ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/anqing/redo01.log'
SQL> alter system switch logfile;
System altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER'/u01/app/oracle/oradata/dave/redo01.log';
Database altered.
--验证：
SQL> select b.group# , b.status , a.memberfrom v$logfile a , v$log b where a.group# = b.group# order by 1;
GROUP# STATUS MEMBER
---------- ----------------------------------------------------------------------------
1 ACTIVE /u01/app/oracle/oradata/anqing/redo01.log
2 CURRENT /u01/app/oracle/oradata/anqing/redo02.log
3 INACTIVE /u01/app/oracle/oradata/anqing/redo03.log
到这里，redo log 也ok了。还差最后一个，控制文件。
10. 处理控制文件
控制文件的处理很简单，将库shutdown，然后把控制文件copy 到新位置，修改一下pfile参数就ok了。
这里要注意控制文件存放位置问题：
*.control_files='/u01/app/oracle/oradata/dave/control01.ctl','/u01/app/oracle/fast_recovery_area/dave/control02.ctl'
这里是Oracle 11g的配置，这里的控制文件只有2个，其中一个在FRA目录下。 而在Oracle 10g中，会有三个控制文件，他们都在一个目录下面。
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@dave dave]$ ls
control01.ctl redo01.log redo03.log system01.dbf users01.dbf
example01.dbf redo02.log sysaux01.dbf undotbs01.dbf
[oracle@dave dave]$ cp control01.ctl../anqing
[oracle@dave dave]$ cd ..
[oracle@dave oradata]$ cd anqing
[oracle@dave anqing]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
SQL> create spfile frompfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdave.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2232800 bytes
Variable Size 490737184 bytes
Database Buffers 322961408 bytes
Redo Buffers 2469888 bytes
Database mounted.
Database opened.
SQL> show parameter control_files
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_files string /u01/app/oracle/oradata/anqing
/control01.ctl, /u01/app/oracl
e/fast_recovery_area/dave/cont
rol02.ctl
至此，整个使用RMAN copy 转移整个数据库操作结束。 现在我们可以大胆的rm 掉之前的目录了。
[oracle@dave oradata]$ pwd
/u01/app/oracle/oradata
[oracle@dave oradata]$ ls
anqing dave
[oracle@dave oradata]$ rm -rf dave
[oracle@dave oradata]$ ls
anqing
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Skype: tianlesoftware
Email: tianlesoftware@gmail.com
Blog: http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940