数据迁移服务V200R100C00----Oracle_RAC下使用ASM的数据迁移方案

ASM是10g版本后oracle大力推荐的一种数据文件存储方式,也是以后的一个重点方向.尽管现在asm在稳定性和一些操作上还存在不少问题，但已经有越来越多的企业把核心系统部署架构到上面.本文重点不在于探讨ASM的优劣问题，爱青菜与爱白菜的人总有自己的理由支持自己的喜好.朋友有套系统需要更换存储，数据库文件部署在asm上，需要尽量短的停机时间完成此次存储更换。

由于不涉及异构的迁移转换，迁移起来也不难，无需借助三方的工具来完成这次高可用切换.当然，ASM下的一些特性也为我们做迁移提供了更多的选择方案.针对该环境，列几种选择方案(以下操作都在新存储已挂载在主机上的情况下).1.利用ASM的热添加和删除磁盘的方式完成存储迁移.该方案充分发挥了asm管理磁盘数据的能力.简要步骤:(1).划分raw或者asm disk，并检查或更改asm参数,例如asm_disktring，使得新存储的asm disk对ASM实例可识别(2).将新存储disk添加到现有的asm diskgroup中.SQL>alter diskgroup < asm_group_name > add disk '< asm_disk_path >';(3).删除旧存储对应的asm diskSQL>alter diskgroup < asm_group_name > drop disk < asm_disk_name >;注意:以上2步，通过观察v$asm_operation视图来判断数据重组的进度，注意删除disk的时候，确保整个diskgroup有足够的空间。

如果asm disk比较多，可以一个一个的分步执行减缓系统压力.有点可惜的是，在10g版本中，oracle 不支持asm diskgroup冗余类型的转换, 也没有直接提供删除failgroup的方法，否则以添加镜像failgroup的方式来完成这次数据迁移，个人觉得有更强的可控性和更低的风险.优缺点:该方案可以实现迁移过程中系统的零停机，但整个操作进度不可控，数据重组过程中我们无法把握进度和风险，如果你对ASM产品足够信任，该方案不失一用2.利用Switch copy的方式完成存储迁移Switch copy并不是只能在ASM下才能用，但asm对数据文件的管理使得switch copy变得异常简单，免去了手工输入大量脚本的工作。

Oracle将数据从文件系统迁移至ASM一、要迁移的文件类型有三个，分别是：1、数据文件2、日志文件3、控制文件二、通过查询找到控制文件su – oracle. oraenvorclsqlplus / as sysdbaselect * from v$controlfile;select * from v$datafile;这里可以看到，controlfile以及表空间文件的路径均在文件系统上一般会先迁移日志文件，如果日志文件不存在了，那只能resetlogs了，换化身。

1、日志每个数据文件头上有一个时间戳，这个时间戳必须和控制文件的时间戳必须一致，否则控制文件将拒绝打开数据库日志文件是循环写入，日志组最少得有2个，这种叫非归档模式日志文件会写到硬盘上以后，数据块才会做日志组3种状态INACTIVE 所有的数据块描述已经写回硬盘CURRENT 正在写的状态ACTIVE 有一些还没有写回硬盘在内存种的数据库转移INACTIVE状态的日志组1、删除1号日志组文件alter database drop logfile group 1;2、指定文件的路径,创建日志组冗余，分别在+DATA和+FRA上创建日志组在sqlplus中操作show parameter db_create_file_dest;alter system set db_create_file_dest=’+data’;show parameter db_recovery_file;alter system set db_recovery_file=’+fra’;3、建立1号日志组alter database add logfile group 1 size 200M;4、手工进行切换CURRENT状态的日志组alter system switch logfile;这时日志组处于active状态select resetlogs_change#,checkpoint_change#,current_scn from v$database;select * from v$datafile;这里看到，检查点号目前是2197301，CURRENT_SCN是2220808，数据文件头都是2197301现在建立完全检查点，将内存中所有的脏数据块写回硬盘alter system checkpoint;再次查看数据文件头，这时发现数据文件头的检查点号都变成了2221070这时，group 1 已经变为INACTIVE重复1、3步骤这时日志文件迁移完成日志组不能太小、不能太少、不能不对称二、迁移数据文件sqlplus / as sysdbashutdown immediate;startup mount;exitrman target /backup as copy database format ‘+data’;list copy;switch database to copy;这时数据文件全部迁移到了ASM中了三、迁移控制文件，可以重建控制文件在mount状态下执行，指定一下控制文件的路径alter system set control_files='+data','+fra' scope=spfile;在原数据库上生成出控制文件,在mount状态下导出控制文件脚本在rman下执行alter database backup controlfile to trace as '/home/oracle/controlasm.sql';编辑控制文件脚本/home/oracle/controlasm.sql数据库在mount状态下查看当前控制文件路径show parameter control修改控制文件路径alter system set control_files=’+data’,’+fra’ scope=spfile;启动数据库到nomountsqlplus / as sysdbashutdown immediate;startup nomount;@/home/oracle/controlasm.sqlselect open_mode vrom v$database;这时看到数据库已经到mounted启动数据库到open状态alter database open；创建临时文件alter tablespace temp add tempfile size 30M autoextend on;四、检查1、检查控制文件2、检查日志文件3、检查临时文件。

oraclerac在asm下的备份与恢复oracle 10g rac+asm 的备份与恢复，因为asm 磁盘管理是两个实例都可以访问的，并且是共享的，归档日志文件也是放在asm磁盘上，所以备份与恢复的操作和单实例差不多，只需要在一个节点上做可以了，以下是测试过程：SQL> show parameter log_archive_dest_1NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string location=+DISKGR OUPlog_archive_dest_10 string1.备份[oracle@rac1 ~]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 3 20:30:40 2009Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: RAC (DBID=2257786532)RMAN> show all;RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUPTYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata/%U';CONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_rac1.f'; # defaultRMAN> backup database plus archivelog delete all input;Starting backup at 03-JAN-09current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=46 recid=70 stamp=674701009input archive log thread=1 sequence=47 recid=73 stamp=674701367input archive log thread=1 sequence=48 recid=74 stamp=674701585input archive log thread=1 sequence=49 recid=76 stamp=675201928input archive log thread=1 sequence=50 recid=79 stamp=675203712input archive log thread=2 sequence=34 recid=71 stamp=674701052input archive log thread=2 sequence=35 recid=72 stamp=674701368input archive log thread=2 sequence=36 recid=75 stamp=674701587input archive log thread=2 sequence=37 recid=77 stamp=675203284input archive log thread=2 sequence=38 recid=78 stamp=675203700channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0lk3tik2_1_1 tag=TAG20090103T203513 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:08channel ORA_DISK_1: deleting archive log(s)archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_1_se q_46.288.674701007 recid=70 stamp=674701009archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_1_se q_47.281.674701367 recid=73 stamp=674701367archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_1_se q_48.287.674701585 recid=74 stamp=674701585archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_1_seq_49.280.675201927 recid=76 stamp=675201928archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_1_se q_50.282.675203705 recid=79 stamp=675203712archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_2_se q_34.283.674701051 recid=71 stamp=674701052archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_2_se q_35.286.674701369 recid=72 stamp=674701368archive log filename=+DISKGROUP/rac/archivelog/2008_12_29/thread_2_se q_36.279.674701587 recid=75 stamp=674701587archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_2_se q_37.285.675203283 recid=77 stamp=675203284archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_2_se q_38.284.675203701 recid=78 stamp=675203700Finished backup at 03-JAN-09Starting backup at 03-JAN-09using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00007 name=/oradata/paul01.dbfinput datafile fno=00003 name=+DISKGROUP/rac/datafile/sysaux.270.669804585 input datafile fno=00005 name=+DISKGROUP/rac/datafile/users.278.672888597 input datafile fno=00002name=+DISKGROUP/rac/datafile/undotbs1.269.669804571 channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0mk3tikd_1_1 tag=TAG20090103T203524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=+DISKGROUP/rac/datafile/system.268.669804533 channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0nk3tilq_1_1 tag=TAG20090103T203524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00004 name=+DISKGROUP/rac/datafile/undotbs2.272.669804611 channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0ok3timu_1_1 tag=TAG20090103T203524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00006name=+DISKGROUP/rac/datafile/test.277.669863437 channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0pk3tin1_1_1 tag=TAG20090103T203524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0qk3tin2_1_1 tag=TAG20090103T203524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:08Finished backup at 03-JAN-09Starting backup at 03-JAN-09current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=51 recid=81 stamp=675203822input archive log thread=2 sequence=39 recid=80 stamp=675203817channel ORA_DISK_1: starting piece 1 at 03-JAN-09channel ORA_DISK_1: finished piece 1 at 03-JAN-09piece handle=/oradata/0rk3ting_1_1tag=TAG20090103T203703 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02channel ORA_DISK_1: deleting archive log(s)archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_1_se q_51.285.675203823 recid=81 stamp=675203822archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_2_se q_39.284.675203817 recid=80 stamp=675203817Finished backup at 03-JAN-09RMAN>2.加入测试数据rac1[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 3 20:51:30 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL>SQL> select count(*) from t;COUNT(*)----------160688SQL> insert into t select * from t;160688 rows created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.rac2[oracle@rac2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 3 20:53:00 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL> insert into t select * from t;321376 rows created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> select count(*) from t;COUNT(*)----------6427523.恢复：注意恢复时另外一个节点数据库一定要关闭，要不然在恢复时会报如下错误:Recovery Manager complete.[oracle@rac1 ~]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 3 21:02:48 2009Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database (not started)RMAN> startup mount;Oracle instance starteddatabase mountedTotal System Global Area 285212672 bytesFixed Size 1267068 bytesVariable Size 104860292 bytesDatabase Buffers 176160768 bytesRedo Buffers 2924544 bytesRMAN> restore database;Starting restore at 03-JAN-09using target database control file instead of recovery catalog allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=150 instance=rac1 devtype=DISK channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00002 to +DISKGROUP/rac/datafile/undotbs1.269.669804571restoring datafile 00003 to +DISKGROUP/rac/datafile/sysaux.270.669804585restoring datafile 00005 to +DISKGROUP/rac/datafile/users.278.672888597restoring datafile 00007 to /oradata/paul01.dbfchannel ORA_DISK_1: reading from backup piece /oradata/0mk3tikd_1_1RMAN-00571:========================================= ==================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571:========================================= ==================RMAN-03002: failure of restore command at 01/03/2009 21:06:08ORA-19870: error reading backup piece /oradata/0mk3tikd_1_1ORA-19573: cannot obtain exclusive enqueue for datafile 2以下是关闭另外一个实例后的恢复过程RMAN>[oracle@rac1 ~]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 3 21:02:48 2009Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database (not started)RMAN> startup mount;Oracle instance starteddatabase mountedTotal System Global Area 285212672 bytesFixed Size 1267068 bytesVariable Size 104860292 bytesDatabase Buffers 176160768 bytesRedo Buffers 2924544 bytesRMAN> restore database2> ;Starting restore at 03-JAN-09using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00002 to +DISKGROUP/rac/datafile/undotbs1.269.669804571restoring datafile 00003 to +DISKGROUP/rac/datafile/sysaux.270.669804585restoring datafile 00005 to +DISKGROUP/rac/datafile/users.278.672888597restoring datafile 00007 to /oradata/paul01.dbfchannel ORA_DISK_1: reading from backup piece /oradata/0mk3tikd_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oradata/0mk3tikd_1_1 tag=TAG20090103T203524channel ORA_DISK_1: restore complete, elapsed time: 00:01:09channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to +DISKGROUP/rac/datafile/system.268.669804533channel ORA_DISK_1: reading from backup piece /oradata/0nk3tilq_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oradata/0nk3tilq_1_1tag=TAG20090103T203524channel ORA_DISK_1: restore complete, elapsed time: 00:01:20channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to +DISKGROUP/rac/datafile/undotbs2.272.669804611channel ORA_DISK_1: reading from backup piece /oradata/0ok3timu_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oradata/0ok3timu_1_1 tag=TAG20090103T203524channel ORA_DISK_1: restore complete, elapsed time: 00:00:37channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00006 to +DISKGROUP/rac/datafile/test.277.669863437channel ORA_DISK_1: reading from backup piece /oradata/0pk3tin1_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oradata/0pk3tin1_1_1 tag=TAG20090103T203524channel ORA_DISK_1: restore complete, elapsed time: 00:00:18Finished restore at 03-JAN-09RMAN> recover database;Starting recover at 03-JAN-09using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 52 is already on disk as file +DISKGROUP/rac/archivelog/2009_01_03/thread_1_seq_52.284. 675204727archive log thread 2 sequence 40 is already on disk as file +DISKGROUP/rac/archivelog/2009_01_03/thread_2_seq_40.285. 675204811channel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=51channel ORA_DISK_1: restoring archive logarchive log thread=2 sequence=39channel ORA_DISK_1: reading from backup piece /oradata/0rk3ting_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oradata/0rk3ting_1_1 tag=TAG20090103T203703channel ORA_DISK_1: restore complete, elapsed time: 00:00:08archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_1_se q_51.279.675206019 thread=1 sequence=51archive log filename=+DISKGROUP/rac/archivelog/2009_01_03/thread_2_se q_39.286.675206019 thread=2 sequence=39 ---可以看到两个实例下的归档日志都可以恢复media recovery complete, elapsed time: 00:00:09Finished recover at 03-JAN-09RMAN> alter database open;database openedRMAN>启动另外个节点，验证数据SQL> startupORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1267068 bytesVariable Size 92277380 bytesDatabase Buffers 188743680 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL>SQL> select count(*) from t;COUNT(*)----------642752一致恢复完成总结:10g rac+asm的备份与恢复和单实例差不多，很方便管理,目前我们客户是9irac+裸设备的方式，由于归档日志文件不能放在裸设备上，只能放在文件系统上，所以备份与恢复相对就麻烦些，必须在各个节点上备份各自的归档日志文件，和相应恢复各自的归档日志文件下面一篇文章就介绍归档放在文件系统上的备份与恢复方法。

oracle数据库数据迁移解决⽅案⼤部分系统由于平台和版本的原因，做的是逻辑迁移，少部分做的是物理迁移，接下来把⼼得与⼤家分享⼀下 去年年底做了不少系统的数据迁移，⼤部分系统由于平台和版本的原因，做的是逻辑迁移，少部分做的是物理迁移，有⼀些⼼得体会，与⼤家分享。

⾸先说说迁移流程，在迁移之前，写好⽅案，特别是实施的⽅案步骤⼀定要写清楚，然后进⾏完整的测试。

我们在迁移时，有的系统测试了四五次，通过测试来完善⽅案和流程。

针对物理迁移，也即通过RMAN备份来进⾏还原并应⽤归档的⽅式（这⾥不讨论通过dd⽅式进⾏的冷迁移），虽然注意的是要将数据库设为force logging的⽅式，在⽤RMAN做全备之前，⼀定要执⾏： 否则可能会产⽣坏块。

对于逻辑迁移，在job_processes设置为>0的数值之前，注意job的下次执⾏时间和job所属⽤户。

⽐如job的定义在之前已经导⼊，但是在迁移之时，job已经运⾏过，那么迁移完成之后，job的下次时间还是原来的时间，这样可能会重复运⾏。

另外，job通过IMP导⼊后，job 所属⽤户会变成导⼊⽤户的名称，显然job原来的⽤户就不能对JOB进⾏管理了，可以通过下⾯的sql进⾏修改： 在迁移之前，应该禁⽌对系统进⾏结构上的修改和发布，⽐如表结构，索引，存储过程包等。

如果是⽤exp/imp导⼊的对象，包括存储过程等，应该检查对象是否与原⽣产库⼀致，⽐如由于dblink的原因，imp之后，存储过程不能创建，导致有部分存储过程丢失，尽管这些存储过程可能没有被使⽤。

下⾯是⼀些加快迁移速度的技巧： 通过dblink，使⽤append insert的⽅式，同时利⽤并⾏，这种⽅式⽐exp/imp更快 对于有LONG类型的列，insert..select的⽅式显然是不⾏的，可以通过exp/imp的⽅式，但是这种⽅式速度⾮常慢，其原因在于imp时⼀⾏⼀⾏地插⼊表。

有另外⼀种⽅式，即sqlplus的copy命令，下⾯是⼀个⽰例： 不过，sqlpus的copy命令不⽀持有timestamp和lob列类型的表。