XX局资源库系统数据库迁移项目实施报告

XXXX局资源库系统数据库迁移项目实施报告2011-11-27- 42 -目 录1.概述31.1.项目背景31.2.面临的问题31.3.解决方案32.实施计划32.1.数据库迁移的流程32.2.时间进度完成情况42.3.实施结果53.实施过程53.1.服务器62和63的安装与配置53.1.1.操作系统环境准备53.1.1.1.修改内核参数53.1.1.2.创建Oracle软件安装所需文件系统63.1.1.3.创建数据库迁移用文件系统63.1.2.安装Oracle 10g for Itanuim 软件63.1.2.1.安装准备63.2.数据库迁移过程123.2.1.备份生产数据库(10.88.82.131)123.2.2.复制131备份数据至62服务器143.2.3.恢复62上的9.2.0.1数据库143.2.4.升级数据库至9.2.0.8213.2.5.升级数据库至10.2.0.5233.2.6.将数据库的文件系统转换为ASM313.2.7.将单实例转换为RAC343.2.8.迁移完的其它设置383.2.9.测试391. 概述1.1. 项目背景XXXX局资源库数据库目前版本为9.2.0.1，运行于两台HP主机上，服务器配置ServiceGuard 集群软件，对数据库提供双机热备保护。数据规模约120GB。数据库运行于非归档模式，使用rman备份数据库。1.2. 面临的问题由于应用系统的特点，对数据库的可用性要求很高，要求数据库724可用。业务的发展，对数据库系统提出了更多的容量和处理能力要求，数据库系统压力越来越大，系统的处理能力接近极限。随着系统使用年限的增加，系统的维护成本也逐年提高，系统已渐渐不能适应业务的需求，需要对系统进行扩容改装。1.3. 解决方案目前已采购新的HP服务器和存储设备。cup：6颗双核，每核1.6G。内存48G，操作系统：hp unix11i v3，磁盘阵列为28个146G 。计划在新系统上安装Oracle 10g RAC数据库软件。2. 实施计划2.1. 数据库迁移的流程本次数据库迁移需要从oracle9i（9.2.0.1）迁移到oracle10g（10.2.0.5）。迁移流程如下：1， 将10.88.82.131数据库的数据通过RMAN迁移到10.88.82.62服务器上的临时数据库中。2， 将10.88.82.62服务器上的临时数据库从9.2.0.1升级到9.2.0.8。3， 将10.88.82.62服务器上的临时数据库从9.2.0.8的数据库升级到10.2.0.54， 将10.88.82.62服务器上的临时数据库从文件系统方式转变为ASM工作方式。5， 将10.88.82.62服务器上的临时数据库转换为RAC工作模式，随即转变为正式环境。2.2. 时间进度完成情况2010-11-25l 项目协调会，确认实施计划及实施内容l 在新的机器上安装10g RAC(10.2.0.5)2010-11-26l 准备9i测试环境l 准备迁移方案l 备份目前资源库的数据，并ftp到新的环境中。2010-11-27l 测试整个迁移方案。l 预估迁移时间l 编写实施报告2010-12-03l 应用测试2010-12-03正式迁移下表为关键步骤实测耗时经修正后的列表XXXX局资源数据库迁移关键步骤耗时一览（不包含软件安装时间）步骤耗时131数据库备份3小时131备份数据ftp到622.5小时在服务器62上恢复9.2.0.1数据库25分钟9.2.0.1数据库升级到9.2.0.830分钟备份9.2.0.8数据库30分钟9.2.0.8数据库升级至10.2.0.51小时10.2.0.5数据库备份30分钟数据文件迁移至ASM35分钟单实例迁移至RAC40分钟测试30分钟其它1小时总共：大约11小时2.3. 实施结果62上测试用9.2.0.1数据库已升级到10.2.0.5，运行状态正常数据库已迁移到Itanuim服务器上，运行于RAC模式，运行状态正常3. 实施过程3.1. 服务器62和63的安装与配置3.1.1. 操作系统环境准备3.1.1.1. 修改内核参数KSI_ALLOC_MAX (NPROC*8) EXECUTABLE_STACK=0 MAX_THREAD_PROC 1024 MAXDSIZ 1073741824 bytes MAXDSIZ_64BIT 2147483648 bytes MAXSSIZ 134217728 bytes MAXSSIZ_64BIT 1073741824 MAXSWAPCHUNKS 16384* MAXUPRC (NPROC*9)/10)+1 MSGMAP (MSGTQL+2) MSGMNI (NPROC) MSGSEG 32767 MSGTQL (NPROC) (B25293-01) NCSIZE (NINODE+1024)* NFILE (15*NPROC+2048) NFLOCKS (NPROC) (B25293-01) NINODE (8*NPROC+2048) NKTHREAD (NPROC*7)/4)+16) NPROC 4096 SEMMAP (SEMMNI+2)* SEMMNI (NPROC) (B25293-01) SEMMNS (SEMMNI*2) SEMMNU (NPROC - 4) SEMVMX 32767 SHMMAX AvailMem SHMMNI 512 SHMSEG 120 VPS_CEILING 643.1.1.2. 创建Oracle软件安装所需文件系统文件系统lv名称/dev/vg00/lvora文件系统lv size30GB文件系统mount点/oracle3.1.1.3. 创建数据库迁移用文件系统在62主机上创建vgbak卷组，并创建lvora-bak，并在lvora-bak上创建文件系统mount到/database2路径。3.1.2. 安装Oracle 10g for Itanuim 软件3.1.2.1. 安装准备3.1.2.1.1. 修改核心参数已由HP完成3.1.2.1.2. 存储准备数据库安装可用磁盘列表如下：disk-namedisk-sizevdisk-namehostname-1hostname-2mount-pointdisk421GOCR-1Ggabzyk01gabzyk02disk431GVote-1Ggabzyk01gabzyk02disk441GASMspfile-1Ggabzyk01gabzyk02disk451GASM-OTHER-1-1Ggabzyk01gabzyk02disk54100GASM-OTHER-2-1Ggabzyk01gabzyk02disk63100GASM-DATA-100G-1gabzyk01gabzyk02disk72100GASM-DATA-100G-2gabzyk01gabzyk02disk81100GASM-DATA-100G-3gabzyk01gabzyk02disk90100GASM-DATA-100G-4gabzyk01gabzyk02disk99100GASM-DATA-100G-5gabzyk01gabzyk02disk108100GASM-DATA-100G-6gabzyk01gabzyk02disk117100GASM-DATA-100G-7gabzyk01gabzyk02disk126100GASM-DATA-100G-8gabzyk01gabzyk02disk135100GASM-DATA-100G-9gabzyk01gabzyk02disk144100GASM-DATA-100G-10gabzyk01gabzyk02disk1531000Goracle-bakgabzyk01/backup3.1.2.1.3. 网络准备host-info节点1名称GABZYK01节点2名称GABZYK02节点1 LAN信息lan名称IP地址子网掩码网关lan0(心跳)192.168.1.1255.255.255.0lan110.88.82.62255.255.255.010.88.82.250lan2lan3节点2 LAN信息lan名称IP地址子网掩码网关lan0(心跳)192.168.1.2255.255.255.0无lan110.88.82.63255.255.255.010.88.82.250lan2lan33.1.2.1.4. 创建oracle用户1. root用户分别登录alice01、alice02主机2. 执行sam命令创建oracle用户和组，信息如下：用户名oracle口令oracleShellksh用户id500组信息组名：oinstall：500id命令输出uid=500(oracle) gid=500(oinstall)3. 修改oracle用户.profile，在.profile文件中增加如下内容，并登录oracle用户export ORACLE_BASE=/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crsexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/local/libexport ORACLE_SID=orcl12export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATHumask 022unset USERNAME4. 创建oracle安装目录mkdir p ORACLE_BASEmkdir p ORACLE_HOMEmkdir p ORA_CRS_HOME5. 配置alice01、alice02两台主机的互信：root用户登录主机，编辑.rhosts文件，在文件中输入+，保存退出3.1.2.1.5. 安装clusterware软件由于Itanuim服务器的操作系统版本高于Oracle软件需求版本，启动安装程序是会校验失败，导致不能启动安装程序，需要早启动安装程序时添加-ignoreSysPrereqs参数调高操作系统版本检查。具体命令是：runInstaller -ignoreSysPrereqs1. 将oracle 10.2.0.1 clusterware软件复制到alice01服务上，路径为：/oracle/product/10.2.0/crs2. oracle用户登录alice01主机3. export TMP=/u01/tmp4. export DISPLAY=客户端ip:0.05. 启动xmanager passive6. xclock测试图形应用能正常启动7. /runInstaller -ignoreSysPrereqs启动oracle Clusterware安装程序8. 根据提示安装软件9. 根据安装程序提示完成安装后步骤10. 执行crs_stat -t检查clusterware运行状态，输出应为：crs_stat -t名称 类型 目标 状态 主机 -ora.k01.gsd application ONLINE ONLINE gabzyk01 ora.k01.ons application ONLINE ONLINE gabzyk01 ora.k01.vip application ONLINE ONLINE gabzyk01 ora.k02.gsd application ONLINE ONLINE gabzyk02 ora.k02.ons application ONLINE ONLINE gabzyk02 ora.k02.vip application ONLINE ONLINE gabzyk02 3.1.2.1.6. 安装database软件1. 将oracle 10.2.0.1 database软件复制到62服务上，路径为：/oracle/product/10.2.0/db_12. oracle用户登录alice01主机3. export TMP=/u01/tmp4. export DISPLAY=客户端ip:0.05. 启动xmanager passive6. xclock测试图形应用能正常启动7. /runInstaller -ignoreSysPrereqs启动oracle database安装程序8. 根据提示安装软件，安装过程中不创建数据库9. 根据安装程序提示完成安装后步骤3.1.2.1.7. 安装 clusterware 软件10.2.0.5补丁1. 将oracle 10.2.0.3 补丁软件复制到62服务上.2. oracle用户登录62主机3. export TMP=/u01/tmp4. export DISPLAY=客户端ip:0.05. 启动xmanager passive6. xclock测试图形应用能正常启动7. /runInstaller -ignoreSysPrereqs启动oracle安装程序8. 根据提示安装软件，应选择CRS_HOME作为安装路径9. 根据安装程序提示完成安装后步骤3.1.2.1.8. 安装 database软件10.2.0.5 补丁1. 将oracle 10.2.0.3 补丁软件复制到62服务上.2. oracle用户登录alice01主机3. export TMP=/u01/tmp4. export DISPLAY=客户端ip:0.05. 启动xmanager passive6. xclock测试图形应用能正常启动7. /runInstaller -ignoreSysPrereqs启动oracle安装程序8. 根据提示安装软件，应选择ORACLE_HOME作为安装路径9. 根据安装程序提示完成安装后步骤3.1.2.1.9. 配置Listener1. oracle用户登录alice01服务器2. export DISPLAY=客户端ip:0.03. 启动xmanager passive4. xclock测试图形应用能正常启动5. netca 启动oracle网络配置助手6. 选择cluster配置7. 根据提示完成listener配置8. 检查集群状态应为:2008-09-19 14:43:39alice01 /u01/10203patch $ crs_stat -t名称 类型 目标 状态 主机 -ora.01.lsnr application ONLINE ONLINE gabzyk01 ora.k01.gsd application ONLINE ONLINE gabzyk01 ora.k01.ons application ONLINE ONLINE gabzyk01 ora.k01.vip application ONLINE ONLINE gabzyk01 ora.02.lsnr application ONLINE ONLINE gabzyk02 ora.k02.gsd application ONLINE ONLINE gabzyk02 ora.k02.ons application ONLINE ONLINE gabzyk02 ora.k02.vip application ONLINE ONLINE gabzyk02 输出显示两台服务器上的listener已自动添加到集群中，并且已经online3.1.2.1.10. 配置ASM磁盘组1. oracle用户登录alice01服务器2. export DISPLAY=客户端ip:0.03. 启动xmanager passive4. xclock测试图形应用能正常启动5. dbca 启动数据库配置助手6. 选择集群配置7. 选择配置ASM，ASM实例sys用户口令为oracle8. 创建ASM磁盘组，采用外部冗余方式，创建一个磁盘组：ORADATA3.2. 数据库迁移过程3.2.1. 备份生产数据库(10.88.82.131)由于目前的生产数据库是非归档方式，所以只能进行冷备。备份脚本如下：lsnrct stopshutdown immediate;startup mount;rman target /run allocate channel c1 type disk maxpiecesize 1800m;allocate channel c2 type disk maxpiecesize 1800m;allocate channel c3 type disk maxpiecesize 1800m;allocate channel c4 type disk maxpiecesize 1800m;allocate channel c5 type disk maxpiecesize 1800m;allocate channel c6 type disk maxpiecesize 1800m;allocate channel c7 type disk maxpiecesize 1800m;allocate channel c8 type disk maxpiecesize 1800m;backup incremental level=0 format=/database2/backup/%U.bak tag=cold_full_bakdatabaseinclude current controlfile;release channel c1;release channel c2;release channel c3;release channel c4;release channel c5;release channel c6;release channel c7;release channel c8;run allocate channel c1 type disk;backup format=/database2/backup/%U.ctl CURRENT CONTROLFILE;release channel c1;shutdown immediate备份控制文件、参数文件、密码文件：cp /database1/redo* /database2/backupcp /database1/control* /database2/backupcp initorcl.ora /database2/backupcp 密码文件 /database2/backup备份过程中可通过如下的语句查询备份进度SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) % COMPLETE FROM V$SESSION_LONGOPS WHERE OPNAME LIKE RMAN% AND OPNAME NOT LIKE %aggregate% AND TOTALWORK != 0 AND SOFAR TOTALWORK;3.2.2. 复制131备份数据至62服务器1. 登录131服务器2. 将/database2/backup目录的内容ftp到62服务器的/database2/backup下。ftp 10.88.82.62binprompt offmput *3. ftp完成后，比对两边服务器文件大小。3.2.3. 恢复62上的9.2.0.1数据库1. 修改参数文件su - oraclecd /database2/backup/cp contro* /database1/cp redo* /database1/cp initorcl.ora orapworcl1 /database2/9i/db_1/dbs/mv initorcl.ora initorcl1.oramkdir -p /database2/9i/admin/orcl1/bdump mkdir -p /database2/9i/admin/orcl1/cdumpmkdir -p /database2/9i/admin/orcl1/udumpvi /database2/9i/db_1/dbs/ initorcl1.ora注释如下参数：sort_area_size=524288hash_join_enabled=TRUE修改如下参数：background_dump_dest=/database2/9i/admin/orcl1/bdumpcore_dump_dest=/database2/9i/admin/orcl1/cdumpuser_dump_dest=/database2/9i/admin/orcl1/udumpinstance_name=orcl12. 启动数据库export ORACLE_BASE=/database2/9iexport ORACLE_HOME=$ORACLE_BASE/db_1export PATH=$ORACLE_HOME/bin:$PATHexport ORACLE_SID=orcl1startup nomount pfile=?/dbs/initorcl1.ora; create spfile from pfile=?/dbs/initorcl1.ora; shutdown abortstartup nomountalter system set sga_max_size=4g scope=spfile;alter system set pga_aggregate_target=2g scope=spfile;ALTER SYSTEM SET SHARED_POOL_SIZE=1024m SCOPE=spfile; ALTER SYSTEM SET JAVA_POOL_SIZE=1024m SCOPE=spfile;shutdown abort;startup mount;3. 恢复数据库runallocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;allocate channel c4 type disk;allocate channel c5 type disk;allocate channel c6 type disk;allocate channel c7 type disk;allocate channel c8 type disk;set newname for datafile 1 to /database2/9i/oradata/orcl1/system01.dbf; set newname for datafile 2 to /database2/9i/oradata/orcl1/undotbs01.dbf; set newname for datafile 3 to /database2/9i/oradata/orcl1/example01.dbf; set newname for datafile 4 to /database2/9i/oradata/orcl1/indx01.dbf; set newname for datafile 5 to /database2/9i/oradata/orcl1/tools01.dbf; set newname for datafile 6 to /database2/9i/oradata/orcl1/users01.dbf; set newname for datafile 7 to /database2/9i/oradata/orcl1/ACCIDENT.dbf; set newname for datafile 8 to /database2/9i/oradata/orcl1/ACCi_DATA.dbf; set newname for datafile 9 to /database2/9i/oradata/orcl1/IMPORTVEH1.dbf; set newname for datafile 10 to /database2/9i/oradata/orcl1/ntiis_26.dbf; set newname for datafile 11 to /database2/9i/oradata/orcl1/undotbs03.dbf; set newname for datafile 12 to /database2/9i/oradata/orcl1/MANAGE1.dbf; set newname for datafile 13 to /database2/9i/oradata/orcl1/undotbs04.dbf; set newname for datafile 14 to /database2/9i/oradata/orcl1/WZZD_ZD1.dbf; set newname for datafile 15 to /database2/9i/oradata/orcl1/UNDOTBS11.dbf; set newname for datafile 16 to /database2/9i/oradata/orcl1/undotbs05.dbf; set newname for datafile 17 to /database2/9i/oradata/orcl1/undotbs02.dbf; set newname for datafile 18 to /database2/9i/oradata/orcl1/database2dacr.ora; set newname for datafile 19 to /database2/9i/oradata/orcl1/P_NEW_DATA1.ora; set newname for datafile 20 to /database2/9i/oradata/orcl1/P_INDEX.ora; set newname for datafile 21 to /database2/9i/oradata/orcl1/P_TEMP.ora; set newname for datafile 22 to /database2/9i/oradata/orcl1/ntiis.ora; set newname for datafile 23 to /database2/9i/oradata/orcl1/ntiis_2.ora; set newname for datafile 24 to /database2/9i/oradata/orcl1/ntiis_index.ora; set newname for datafile 25 to /database2/9i/oradata/orcl1/ntiis_12_1.ora; set newname for datafile 26 to /database2/9i/oradata/orcl1/ntiis_12_2.ora; set newname for datafile 27 to /database2/9i/oradata/orcl1/ntiis_index_12_1.ora; set newname for datafile 28 to /database2/9i/oradata/orcl1/ntiis_index_12_2.ora; set newname for datafile 29 to /database2/9i/oradata/orcl1/ntiis_3.dbf; set newname for datafile 30 to /database2/9i/oradata/orcl1/ntiis_4.dbf; set newname for datafile 31 to /database2/9i/oradata/orcl1/ntiis_5.dbf; set newname for datafile 32 to /database2/9i/oradata/orcl1/ntiis_6.dbf; set newname for datafile 33 to /database2/9i/oradata/orcl1/ntiis_7.dbf; set newname for datafile 34 to /database2/9i/oradata/orcl1/ntiis_8.dbf; set newname for datafile 35 to /database2/9i/oradata/orcl1/ntiis_9.dbf; set newname for datafile 36 to /database2/9i/oradata/orcl1/ntiis_10.dbf; set newname for datafile 37 to /database2/9i/oradata/orcl1/ntiis_11.dbf; set newname for datafile 38 to /database2/9i/oradata/orcl1/ntiis_12.dbf; set newname for datafile 39 to /database2/9i/oradata/orcl1/ntiis_13.dbf; set newname for datafile 40 to /database2/9i/oradata/orcl1/ntiis_14.dbf; set newname for datafile 41 to /database2/9i/oradata/orcl1/ntiis_index_1.ora; set newname for datafile 42 to /database2/9i/oradata/orcl1/ntiis_index_2.ora; set newname for datafile 43 to /database2/9i/oradata/orcl1/ntiis_15.dbf; set newname for datafile 44 to /database2/9i/oradata/orcl1/P_NEW_DATA2.ora; set newname for datafile 45 to /database2/9i/oradata/orcl1/ntiis_index_3.ora; set newname for datafile 46 to /database2/9i/oradata/orcl1/ntiis_12_3.ora; set newname for datafile 47 to /database2/9i/oradata/orcl1/IV_DATA1.ora; set newname for datafile 48 to /database2/9i/oradata/orcl1/IV_DATA2.ora; set newname for datafile 49 to /database2/9i/oradata/orcl1/IV_INDEX1.ora; set newname for datafile 50 to /database2/9i/oradata/orcl1/IV_INDEX2.ora; set newname for datafile 51 to /database2/9i/oradata/orcl1/IV_USER1.ora; set newname for datafile 52 to /database2/9i/oradata/orcl1/ntiis_index_4.ora; set newname for datafile 53 to /database2/9i/oradata/orcl1/ntiis_index_5.ora; set newname for datafile 54 to /database2/9i/oradata/orcl1/ntiis_16.dbf; set newname for datafile 55 to /database2/9i/oradata/orcl1/ntiis_17.dbf; set newname for datafile 56 to /database2/9i/oradata/orcl1/ntiis_18.dbf; set newname for datafile 57 to /database2/9i/oradata/orcl1/ntiis_19.dbf; set newname for datafile 58 to /database2/9i/oradata/orcl1/ntiis_20.dbf; set newname for datafile 59 to /database2/9i/oradata/orcl1/ntiis_12_4.ora; set newname for datafile 60 to /database2/9i/oradata/orcl1/ntiis_12_5.ora; set newname for datafile 61 to /database2/9i/oradata/orcl1/ntiis_12_6.ora; set newname for datafile 62 to /database2/9i/oradata/orcl1/ntiis_21.dbf; set newname for datafile 63 to /database2/9i/oradata/orcl1/ntiis_22.dbf; set newname for datafile 64 to /database2/9i/oradata/orcl1/ntiis_23.dbf; set newname for datafile 65 to /database2/9i/oradata/orcl1/ntiis_24.dbf; set newname for datafile 66 to /database2/9i/oradata/orcl1/ntiis_25.dbf; set newname for datafile 67 to /database2/9i/oradata/orcl1/ntiis_index_6.ora; set newname for datafile 68 to /database2/9i/oradata/orcl1/ntiis_index_7.ora; set newname for datafile 69 to /database2/9i/oradata/orcl1/undotbs06.dbf; set newname for datafile 70 to /database2/9i/oradata/orcl1/undotbs07.dbf; set newname for datafile 71 to /database2/9i/oradata/orcl1/undotbs08.dbf; set newname for datafile 72 to /database2/9i/oradata/orcl1/undotbs09.dbf; set newname for datafile 73 to /database2/9i/oradata/orcl1/ACCi_DATA_1.dbf; set newname for datafile 74 to /database2/9i/oradata/orcl1/ACCi_DATA_2.dbf; set newname for datafile 75 to /database2/9i/oradata/orcl1/ACCi_DATA_3.dbf; set newname for datafile 76 to /database2/9i/oradata/orcl1/ACCi_DATA_4.dbf; set newname for datafile 77 to /database2/9i/oradata/orcl1/ACCi_DATA_5.dbf; set newname for datafile 78 to /database2/9i/oradata/orcl1/ntiis_index_8.ora; set newname for datafile 79 to /database2/9i/oradata/orcl1/ntiis_index_9.ora; set newname for datafile 80 to /database2/9i/oradata/orcl1/P_NEW_DATA3.ora; set newname for datafile 81 to /database2/9i/oradata/orcl1/ntiis_27.dbf; set newname for datafile 82 to /database2/9i/oradata/orcl1/ntiis_28.dbf; set newname for datafile 83 to /database2/9i/oradata/orcl1/ntiis_12_7.ora; set newname for datafile 84 to /database2/9i/oradata/orcl1/ntiis_index_12_3.ora; set newname for datafile 85 to /database2/9i/oradata/orcl1/P_NEW_DATA4.ora; set newname for datafile 86 to /database2/9i/oradata/orcl1/ntiis_12_8.ora; restore database;switch datafile all;release channel c1;release channel c2;release channel c3;release channel c4;release channel c5;release channel c6;release channel c7;release channel c8; alter database open;服务器应该能够正常启动。4. 创建临时表空间alter database tempfile /database1/temp01.dbf drop; alter database tempfile /database1/temp02.dbf drop; alter database tempfi