1/11用友股份-LE支持服务业务本部技术方案--《NC57数据迁移案例分析》建立日期：2014-01-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2014-00122/11文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2014-01-20索昊技术支持部V1.0版本领域模块备注3/11目录NC57数据迁移案例分析..........................................................................................4一、系统环境............................................................................................................4二、问题描述............................................................................................................4三、检查分析............................................................................................................41、确定归档目录和归档日志序列.........................................................................42、确定数据文件位置：.........................................................................................53、确定onlineredolog文件位置...........................................................................64、temp文件位置....................................................................................................65、导出现有spfile....................................................................................................66、确定数据库dbid.................................................................................................7四、迁移操作步骤....................................................................................................71、编写备份脚本.....................................................................................................72、新服务器准备.....................................................................................................73、开始restore新服务器数据文件........................................................................84、追加以前的归档日志.......................................................................................105、申请停机时间，开始recoverdatabase..........................................................104/11NC57数据迁移案例分析注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！一、系统环境NC版本：NC57应用服务器AIX5.3数据库服务器AIX6.1二、问题描述客户新建机房,购买了新数据库服务器，需要将生产数据库迁移到新数据库服务器上，由于客户业务忙，几乎7X24小时，所以要求迁移尽量快的完成。跟客户商量后，决定采用rman迁移追归档方式。三、检查分析1、确定归档目录和归档日志序列检查数据库归档模式和归档目录确定现在的归档文件序列：Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL>archiveloglist;5/11DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/oracle/oralog/archive/Oldestonlinelogsequence25413Nextlogsequencetoarchive25417Currentlogsequence254172、确定数据文件位置：SQL>selectnamefromv$datafile;NAME--------------------------------------------------------------------------------/oracle/orasoft/ncv5/system01.dbf/oracle/orasoft/ncv5/undotbs01.dbf/oracle/orasoft/ncv5/sysaux01.dbf/oracle/orasoft/ncv5/users01.dbf/oracle/oradata/nnc_data01.dbf/oracle/oradata/nnc_data02.dbf/oracle/oraindex/nnc_data03.dbf/oracle/oraindex/nnc_index01.dbf/oracle/oraindex/nnc_index02.dbf/oracle/oradata/nnc_index03.dbf/oracle/oraindex/iufo.dbfNAME--------------------------------------------------------------------------------/oracle/oradata/nnc_data01_2.dbf/oracle/oradata/nnc_data01_3.dbf/oracle/oradata/nnc_index03_1.dbf/oracle/oraindex/nnc_index01_1.dbf/oracle/oradata/nnc_data01_4.dbf/oracle/oradata/nnc_data01_5.dbf/oracle/oraindex/nnc_data03_2.dbf/oracle/oraindex/nnc_index01_2.dbf/oracle/oradata/data01_6.dbf/oracle/oraindex/nnc_data03_3.dbf.dbf/oracle/oraindex/nnc_index01_3.dbfNAME--------------------------------------------------------------------------------/oracle/oraindex/nnc_index02_1.dbf/oracle/oradata/nnc_index03_2.dbf6/11/oracle/oradata/data01_7.dbf/oracle/oradata/data01_.dbf8.dbf/oracle/oraindex/nnc_data03_4.dbf/oracle/oradata/nnc_data01_9.dbf/oracle/oradata/nnc_index03_3.dbf/oracle/oraindex/nnc_index01_4.dbf3、确定onlineredolog文件位置SQL>selectmemberfromv$logfile;MEMBER--------------------------------------------------------------------------------/oracle/oralog/ncv5/redo01.log/oracle/oralog/ncv5/redo02.log/oracle/oralog/ncv5/redo03.log/oracle/oralog/ncv5/redo04.log/oracle/oralog/ncv5/redo05.log4、temp文件位置SQL>selectnamefromv$tempfile;NAME--------------------------------------------------------------------------------/oracle/orasoft/ncv5/temp01.dbf/oracle/orasoft/ncv5/temo02.dbf/oracle/orasoft/ncv5/temp03.dbf/oracle/orasoft/ncv5/temp04.dbf/oracle/orasoft/ncv5/temp05.dbf/oracle/orasoft/ncv5/temp06.dbf5、导出现有spfileCreatepfile=’/tmp/11.txt’fromspfile;7/116、确定数据库dbiddbid情况RecoveryManager:Release10.2.0.4.0-ProductiononMonOct2815:27:142013Copyright(c)1982,2007,Oracle.Allrightsreserved.connectedtotargetdatabase:NCV5(DBID=655251056)四、迁移操作步骤1、编写备份脚本rmantarget/log/tmp/rmanbak.logappend<setdbid=655251056executingcommand:SETDBIDRMAN>startupnomountpfile='/tmp/aa.txt'OracleinstancestartedTotalSystemGlobalArea8388608000bytesFixedSize2152264bytesVariableSize1839147192bytesDatabaseBuffers6408896512bytesRedoBuffers138412032bytesRMAN>restorecontrolfilefrom'/oradata/ncdata/ctl_NCV5_20131029_1861_1';Startingrestoreat29-OCT-13usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:sid=1097devtype=DISKchannelORA_DISK_1:restoringcontrolfile9/11channelORA_DISK_1:restorecomplete,elapsedtime:00:00:01outputfilename=/oracle/orasoft/control01.ctloutputfilename=/oradata/ncdata/control02.ctloutputfilename=/oradata/ncdata/control03.ctlFinishedrestoreat29-OCT-13（2）启动到mount模式，开始恢复oracle数据文件注意：由于旧服务器oracle数据文件和新服务器数据文件所在的目录结构不同，所以这里使用了setnewname命令，如果不使用，如果没有提前建立目录结构，则oracle会自己建立RMAN>RMAN>sql'alterdatabasemount';sqlstatement:alterdatabasemountreleasedchannel:ORA_DISK_1RMAN>------------------------------------RMAN>run{setnewnamefordatafile'/oracle/orasoft/ncv5/system01.dbf'to'/oradata/ncdata/system01.dbf';setnewnamefordatafile'/oracle/orasoft/ncv5/undotbs01.dbf'to'/oradata/ncdata/undotbs01.dbf';setnewnamefordatafile'/oracle/orasoft/ncv5/sysaux01.dbf'to'/oradata/ncdata/sysaux01.dbf';setnewnamefordatafile'/oracle/orasoft/ncv5/users01.dbf'to'/oradata/ncdata/users01.dbf';setnewnamefordatafile'/oracle/oradata/nnc_data01.dbf'to'/oradata/ncdata/nnc_data01.dbf';setnewnamefordatafile'/o2>racle/oradata/nnc_data02.dbf'to'/oradata/ncdata/nnc_data02.dbf';setnewnamefordatafile'/oracle/oraindex/nnc_data03.dbf'to'/oradata/ncdata/nnc_data03.dbf';setnewnamefordatafile'/oracle/oraindex/nnc_index01.dbf'to'/oradata/ncdata/nnc_index01.dbf';setnewnamefordatafile'/oracle/oraindex/nnc_index02.dbf'to'/oradata/ncdata/nnc_index02.dbf';setnewnamefordatafile'/oracle/oradata/nnc_index03.dbf'to'/oradata/ncdata/nnc_index03.dbf';10/11setnewnamefordatafile'/oracle/oraindex/iufo.dbf'to'/oradata/ncdata/iufo.dbf';setnewnamefordatafile'/oracle/oradata/nnc_data01_2.dbf'to'/oradata/ncdata/nnc_data01_2.dbf';setnewnamefordatafile'/oracle/oradata/nnc_data01_3.dbf'to'/oradata/ncdata/nnc_data01_3.dbf';setnewnamefordatafile'/oracle/oradata/nnc_index03_1.dbf'to'/oradata/ncdata/nnc_index03_1.dbf';setnewnamefordatafile'/oracle/oraindex/nnc_index01_1.dbf'to'/oradata/ncdata/nnc_index01_1.dbf';restoredatabaseswitchdatafileall3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>21>22>23>24>25>26>27>28>29>30>31>32>33>运行结果略注意：setnewname命令之后一定要加上restoredatabase注意：此时oracle处于mount状态，并且不能openresetlogs，一旦打开，需要重新恢复4、追加以前的归档日志Rmantarget/RMAN>recoverdatabase;注意：此命令会开始追加以前的归档日志，恢复到RMAN全备之前的oracleSCN，但是最后会报错，提示找不到xxxx日志，这个XXXX就是全备之后产生的日志，下面只需要将全备之后产生的日志再次recoverdatabase就可以完全恢复了5、申请停机时间，开始recoverdatabase准备工作：1）强制切换旧服务器oracleonlineredolog，目的是让所有在线日志都归档Altersystemarchivelogcurrent11/11多执行几次2）干净的关闭旧oracle数据库Shutdownimmediate3）将所有归档日志拷贝到新服务器为了节省时间，一般都先将rman备份之前的所有归档拷贝到新服务器，并恢复4）用rman在控制文件中更新备份集信息例如：RMAN>catalogbackuppiece'/oracle/full_controlfile_orcl_20091201_704449444';5）将旧数据库onlineredolog文件拷贝到新服务器相应位置，如果需要修改目录结构，可以使用alterdatabaserenamefile命令开始再次恢复oracle数据库Recoverdatabase这里不会像刚才一样报错，因为oracle所有数据文件中的SCN均一样了，数据库已经全部恢复完成打开oracle数据库Alterdatabaseopenresetlogs注意：当天或者立即做一次数据库全备注意：以前的所有归档日志均无效了，全部可以删除
