oracle迁移至ASM磁盘

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;
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
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;
System altered.

d、关闭数据库
SQL> shutdown immediate;
3、登陆rman
[oracle@rhel5 ~]$ export ORACLE_SID=prod
[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
[oracle@rhel5 ~]$ export ORACLE_SID=prod
[oracle@rhel5 ~]$ sqlplus / as sysdba
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+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;
NAME
--------------------------------------------------------------------------------
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‘;
Tablespace altered.

SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+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.
SQL> alter database add logfile ‘+DG1‘ size 51m;
Database altered.
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‘;
Database altered.
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‘;
Database altered.

遇到删除不了时使用如下命令更改日志文件状态，知道可以删除
SQL> alter system switch logfile;
SQL> alter database drop logfile ‘/u01/app/oracle/oradata/prod/redo02.log‘;
Database altered.
查看日志文件
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+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;
NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp.279.842151759

最后迁移初始化参数文件
SQL> create pfile from spfile;
File created.
SQL> create spfile=‘+DG1‘ from pfile;
File created.

重启数据库查看是否能够正常启动
SQL> shutdown immediate;
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.
查看迁移后的文件
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union select member from v$logfile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp.279.842151759

原文：http://www.cnblogs.com/houzhiqing/p/4799153.html