oracle数据库文件系统迁移到ASM

查看ASM磁盘组

SQL> select name,state from  v$asm_diskgroup;

登陆RMAN执行镜像全备到ASM磁盘组

$rman target /

RMAN> backup as copy database format '+data';Starting backup at 01-JAN-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=13 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbfoutput file name=+DATA/prod1/datafile/system.260.900002069 tag=TAG20160101T163428 RECID=22 STAMP=900002071channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbfoutput file name=+DATA/prod1/datafile/sysaux.259.900002071 tag=TAG20160101T163428 RECID=23 STAMP=900002074channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbfoutput file name=+DATA/prod1/datafile/undotbs1.261.900002079 tag=TAG20160101T163428 RECID=24 STAMP=900002080channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copyinput datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/test01.dbfoutput file name=+DATA/prod1/datafile/test1.262.900002083 tag=TAG20160101T163428 RECID=25 STAMP=900002082channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01channel ORA_DISK_1: starting datafile copyinput datafile file number=00007 name=/u01/app/oracle/oradata/PROD1/test02.dbfoutput file name=+DATA/prod1/datafile/test2.258.900002083 tag=TAG20160101T163428 RECID=26 STAMP=900002083channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=/u01/app/oracle/oradata/t1.dbfoutput file name=+DATA/prod1/datafile/t1.257.900002085 tag=TAG20160101T163428 RECID=27 STAMP=900002084channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf<pre name="code" class="plain">RMAN> restore controlfile from '/u01/app/oracle/oradata/PROD1/control02.ctl';Starting restore at 01-JAN-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=14 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=+DATA/prod1/controlfile/current.263.900003463Finished restore at 01-JAN-16

修改文件创建默认位置和控制文件位置

SQL> alter system set control_files='+data' scope=spfile;System altered.SQL> alter system set db_create_file_dest='+data' scope=spfile;System altered.

重启

SQL> shutdown immediateSQL> startup

迁移控制文件

RMAN> startup nomount

RMAN> restore controlfile from '/u01/app/oracle/oradata/PROD1/control02.ctl';Starting restore at 01-JAN-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=14 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=+DATA/prod1/controlfile/current.263.900003463Finished restore at 01-JAN-16

迁移数据文件

RMAN> alter database mount;

RMAN> switch database to copy; RMAN> recover database;     #一致性备份不需要）RMAN> alter database open;

迁移日志文件

创建新文件，删除老文件。(日志组处于INACTIVE状态才可以删除）

SQL> select group#,status from v$log;

强制切换日志组改变日志组状态

SQL> alter system switch logfile;

删除老的日志组(删一个，加一个)

SQL> alter database drop logfile group 3;Database altered.SQL> alter database drop logfile group 1;Database altered.SQL> alter database drop logfile group 2;Database altered.

增加日志组到ASM

SQL> alter database add logfile group 3 '+data' size 50m;Database altered.SQL> alter database add logfile group 1 '+data' size 50m;Database altered.SQL> alter database add logfile group 2 '+data' size 50m;Database altered.

迁移初始化文件

SQL> create pfile from spfile;File created.SQL> create spfile='+data' from pfile;File created.

vi $ORACLE_HOME/dbs/initPROD1.oraspfile='+data/db_unknown/parameterfile/SPFILE.263.905991571'

mv spfilePROD1.ora spfilePROD1.ora.bak

迁移temp文件

SQL> select name from v$tempfile;SQL> alter tablespace temp add tempfile '+data'；SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/PROD1/temp01.dbf';SQL> select name from v$tempfile;

查看迁移的文件

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----------------------------------------------------------------------------------------------------+DATA/prod1/controlfile/current.263.900003463+DATA/prod1/datafile/sysaux.259.900002071+DATA/prod1/datafile/system.260.900002069+DATA/prod1/datafile/t1.257.900002085+DATA/prod1/datafile/test1.262.900002083+DATA/prod1/datafile/test2.258.900002083+DATA/prod1/datafile/undotbs1.261.900002079+DATA/prod1/datafile/users.256.900002085+DATA/prod1/onlinelog/group_1.265.900005697+DATA/prod1/onlinelog/group_2.267.900005919+DATA/prod1/onlinelog/group_3.266.900005729+DATA/prod1/tempfile/temp.268.900006743

SQL>show parameter pfile;NAME     TYPE VALUE------------------------------------ ----------- ------------------------------spfile     string +DATA/db_unknown/parameterfile /spfile.263.905991571