1、登陆+ASM实例查看ASM磁盘是否正
[oracle@rhel5 ~]$ export ORACLE_SID=+ASM
[oracle@rhel5 ~]$ sqlplus / as sysdba
SQL> select name,state from v$asm_diskgroup;
NAME STATE
--------------- -----------
DG1 MOUNTED
2、登陆数据库prod
[oracle@rhel5 ~]$ export ORACLE_SID=prod
[oracle@rhel5 ~]$ sqlplus / as sysdba
a、查看控制文件
SQL> show parameter control_files;
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_files string
/u01/app/oracle/oradata/prod/c
ontrol01.ctl, /u01/app/oracle/
oradata/prod/control02.ctl, /u
01/app/oracle/oradata/prod/con
trol03.ctl
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl
b、查看db_create_file_dest参数
SQL> show parameter db_create_file_dest;
db_create_file_dest string
c、修改控制文件的位置
SQL> alter system set control_files='+DG1' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DG1' scope=spfile;
d、关闭数据库
SQL> shutdown immediate;
3、登陆rman
[oracle@rhel5 ~]$ rman target /
a、利用rman迁移目标数据库控制文件和数据文件
RMAN> startup nomount;
b、利用rman将文件系统上的控制文件重建控制文件到ASM磁盘的DG1上
RMAN> restore controlfile from '/u01/app/oracle/oradata/prod/control01.ctl';
c、利用rman复制数据库文件到ASM磁盘组DG1上
RMAN> alter database mount;
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as copy database format '+DG1';
}
d、利用RMAN的SWITCH 命令修改控制文件内数据文件的指针，使其指向新位置
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
4、登陆数据库prod
+DG1/prod/controlfile/backup.271.842150617
SQL> select name from v$datafile;
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/users.276.842151211
+DG1/prod/datafile/example.274.842151187
a、迁移temp文件
SQL> select name,status,enabled from v$tempfile;
STATUS ENABLED
--------------------- ------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
ONLINE READ WRITE
由于temp文件没有可用的数据只是缓存数据，temp可以直接添加一个新的temp文件，然后将老的temp文件删除
SQL> alter tablespace temp add tempfile '+DG1';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/prod/temp01.dbf';
SQL> select name from v$tempfile;
+DG1/prod/tempfile/temp.279.842151759
b、迁移日志文件，在DG1创建新的日志文件，然后将老的文件删除
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
3
/u01/app/oracle/oradata/prod/redo03.log
2
/u01/app/oracle/oradata/prod/redo02.log
1
/u01/app/oracle/oradata/prod/redo01.log
SQL> alter database add logfile '+DG1' size 51m;
Database altered.
删除的时候需要日志文件组状态为inactive状态，不过删除不了，说明日志文件组不是inactive状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo01.log';
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo02.log';
alter database drop logfile '/u01/app/oracle/oradata/prod/redo02.log'
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance prod (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/prod/redo02.log'
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo03.log';
遇到删除不了时使用如下命令更改日志文件状态，知道可以删除
SQL> alter system switch logfile;
查看日志文件
SQL> select member from v$logfile;
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
查看迁移后的文件
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union
select member from v$logfile;
最后迁移初始化参数文件
SQL> create pfile from spfile;
File created.
SQL> create spfile='+DG1' from pfile;
重启数据库查看是否能够正常启动
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 71305220 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.