ORACLE数据库迁移方案

1、数据库整理方案万佳系统数据库迁移方案信息系统课王川2012-7-271 事前事项(1)实施日期：高温假期间,(2)预计用时：3个工作日(3)参与人员：(4)影响范围：1、万佳系统2、库场系统7月29日到7月31日。2 事前准备1、 安装两台 IBM 750服务器操作系统：CentOS 5.6 X86_64bit。2、共享磁盘准备：2个RAW OCR磁盘：每个 1G3个RAW VOTING磁盘：每个 1G1 个 ASM DATA+ 磁盘：150G1 个 ASM RECOVER磁盘：150G3、安装介质：（1）oracle 10gr2 clusterware（2）database（3）10.2.0

2、.4 升级包迁移流程：1、安装系统及支持程序包。2、设置两个节点的 HOSTS3、设置时间同步。4、测试时间同步。5、新增ORACLE用户、用户组。6、设置ORACLE用户环境变量。7、设置用户等价。&测试用户等价。9、磁盘分区。10、创建RAW设备（5个）。11、检查RAW设备创建是否成功。12、创建ASM磁盘（2个）。13、检查ASM磁盘权限是否属于用户 ORACLE权限是否属于用户组 DBA。14、在1号节点上安装 clusterware。15、测试双节点clusterware进程及状态是否正常。16、在1号节点上安装 ASM。17、在1号节点上安装 DADABASE SOFT1

3、8、在2号节点上VIPCA创建VIP。19、检查双节点的 VIP是否正常。20、在1号节点上DBCA创建数据库，期间分配 ASM磁盘。21、10.2.0.2 升级到 10.2.0.4。22、检测EM是否正常。23、在EM上新建用户表空间： CASKWMES24、新增数据库用户： CASKWMES权限："DBA","CONNECT","EXP_FULL_DATABASE","IMP_FULL_DATABASE"表空间：用户表空间 CASKWMES临时表空间：TEMPcreate tablespace caskwmes

4、 datafile +WORKAREA' size 30g;alter tablespace caskwmes add datafile +WORKAREA' size 30g;alter tablespace caskwmes add datafile +WORKAREA' size 30g;25、查询目前万佳系统数据库的信息：(1) 无效对象：SELECT * FROM DBA_OBJECTS WHERE STATUS='INVALID'(2) 相关用户对象总数：SELECT * FROM DBA_OBJECTS WHERE OWNER IN (&#

5、39;CASKWMES') ANDOBJECT_NAME NOT LIKE 'BIN$%' ORDER BY OWNER,OBJECT_TYPE,OBJECT_NAME(3) 序列的前后对比： select * from dba_seque nces where seque nce_ow ner in ('CASKWMES')(4) 表 空间表 数量和 行数：SELECT TABLE_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TABLES WHERE TABLESPACE_NAME='CASKWMES'

6、;26、停止万佳系统应用及服务。27、使用EXP/EXPDP命令将目前万佳数据库导出并SCP方式传到需要迁移的服务器上，具体的命令如下(1) exp CASKWMES/密码indexes=y rows=y file= /wmesbak/caskwmes_exp_201201xx.dmplog= /wmesbak/caskwmes_exp_201207xx .log(2) 第(1)种导出有问题时，采用该方法导出的文件进行导入：create directory dump_test as '/wmesbak/expdp/' ;grant read, write on director

7、y dump_test to caskwmes;expdp caskwmes / caskwmes directory=dump_test dumpfile= expdp_201207xx.dmp 监控：select * from DBA_DATAPUBMP_JOBS;select sid,serial# from v$session s,dba_datapump_sessions d where s.saddr=d.saddr ；(3 ) SCP - P 22 - r oracle192.168.1.167:/wmesbak/ caskwmes_exp_201201xx.dmp/u01/wm

8、esbak/28、将导出的数据文件导入到需要迁移的数据库中。imp CASKWMES/ 密 码 file= caskwmes_exp_201201xx.dmp in dexes=y log= caskwmes_imp_201207xx .log29、查询第25步导出的数据信息，迁移服务器进行对比。30、更改万佳正式库服务器的连接进程，重启进程，重启4.100的打印服务，数据同步服务，安灯31、通知赵梦男对万佳状态进行验证。3 实施步骤3.1 安装RAC1、根据生产系统情况修改hosts文件# vi /etc/hosts2、创建组 oinstall,dba,用户 oracle# groupadd

9、 oin stall# groupadd dba# useradd -g oin stall -G dba oracle# passwd oracle3、 创建安装目录并修改权限，修改oracle用户的初始化参数文件# mkdir /opt/ora10g-此处路径仅为举例# chow n oracle, oin stall /opt/ora10g# su - oracle$ vi /home/oracle/.bash_profile增加如下内容：export TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/opt/ora10gexport ORACL

10、E_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_SID=caskdb1 -二号机为 caskdb2export ORACLE_TERM=xtermexport PATH=/usr/sbi n: $PATHexport PATH=$ORACLE_HOME/bi n:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6 /bin/export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jl

11、ib:$ORACLE_HOME/rdbms/jlib ulimit -u 16384 -n 65536umask 0224、安装需要的包在Linux桌面的 Application菜单下启动 Package Manger,添加 CentOS-5.6-x86_64安装路径介质为 repository，然后安装一下所有的包及其依赖的包：bi nutils-2.17.50.0.6-2 (x86_64)compat-db-4.2.52-5.1 (x86_64)compat-libstdc+-296-2.96-138 (i386)compat-libstdc+-33-3.2.3-61(x86_64)com

12、pat-libstdc+-33-3.2.3-61 (i386)第3页共12页数据库整理方案con trol-ce nter-2.16.0-14 (x86_64) gcc-4.1.1-52 (x86_64) gcc-c+-4.1.1-52 (x86_64) glibc-2.5-12 (x86_64) glibc-2.5-12 (i686)glibc-commo n-2.5-12 (x86_64) glibc-devel-2.5-12 (x86_64) glibc-devel-2.5-12 (i386) glibc-headers-2.5-12 (x86_64) ksh-20060214-1.4

13、(x86_64) libaio-0.3.106-3.2 (x86_64) libgcc-4.1.1-52 (i386) libgcc-4.1.1-52 (x86_64) libg nome-2.16.0-6 (x86_64) libgnomeui-2.16.0-5 (x86_64) libgomp-4.1.1-52 (x86_64) libstdc+-4.1.1-52 (x86_64) libstdc+-devel-4.1.1-52 (x86_64) libXp-1.0.0-8 (i386) libXtst-1.0.1-3.1(i386) make-3.81-1.1 (x86_64) syss

14、tat-7.0.0-3 (x86_64) util-li nux-2.13 -0.44 (x86_64)5、配置内核参数# vi /etc/sysctl.c onf增加或修改下列内容kernel.shmall = 2097152 kernel.shmmax = 68719476736 - 内存 128G 服务器的配置kern el.shm mni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 n et.ipv4.ip_local_port_ra nge = 9000 65000 n et.core.rmem_default

15、= 262144 net.core.rmem max = 2097152 n et.core.wmem_default = 262144n et.core.wmem_max = 1048576# sysctl -p设置oracle使用的文件数权限# vi /etc/security/limits.c onf增加下列内容oracle soft n proc 2047oracle hard nproc 16384oracle soft nofile 1024 oracle hard nofile 65536修改安全限制# vi /etc/pam.d/login增加：sessi on require

16、d /lib/security/pam_limits.so配置Hangcheck计时器# vi /etc/rcocal增加：modprobe han gcheck-timer han gcheck-tick=30 han gcheck_marg in=1806、绑定裸设备给共享磁盘分区# fdisk /dev/sdb输入的依次是"n/p/1/ 回车/回车/w"# fdisk /dev/sdc输入的依次是”n/p/1/ 回车/回车/w”# vi /etc/udev/rules.d/60-raw.rules增加如下内容：ACTION=”add”，KERNEL=”/dev/sdb

17、1”,RUN+=”/bi n/raw /dev/raw/raw1 %N”ACTION=”add”，ENVMAJOR=”8”,ENVMINOR=”17”,RUN+=”/bi n/raw /dev/raw/raw1 %M %m"ACTION="add", KERNEL="/dev/sdc1",RUN+="/bi n/raw /dev/raw/raw2 %N"ACTION="add", ENVMAJOR=”8”,ENVMINOR=”33”,RUN+=”/bi n/raw /dev/raw/raw2 %M %m&

18、quot;KERNEL=”raw1-2”，OWNER=”oracle”，GROUP=”oi nstall”，MODE=”640”重启服务# start_udev7、设置ssh测试两个节点间互ping,确保都是通的首先在node1执行：root no de1 opt# su - oracleoracle n ode1 $ mkdir /.sshoracle no de1 $ chmod 700 /.sshoracle no de1 $ ssh-keyge n -t rsaGenerating public/private rsa key pair.En ter file in which to

19、save the key (/home/oracle/.ssh/id_rsa):En ter passphrase (empty for no passphrase):En ter same passphrase aga in:Your identification has been saved in /home/oracle/.ssh/id_rsa.Your public key has bee n saved in /home/oracle/.ssh/id_rsa.pub.The key fin gerpri nt is: d2:69:eb:ac:86:62:27:50:99:ff:e8:

20、1e:a2:e6:5d:7f oracle n odel oracle no de1 $ ssh-keyge n -t dsaGenerating public/private dsa key pair.En ter file in which to save the key (/home/oracle/.ssh/id_dsa):En ter passphrase (empty for no passphrase):En ter same passphrase aga in:Your ide ntificatio n has bee n saved in /home/oracle/.ssh/i

21、d_dsa.Your public key has bee n saved in /home/oracle/.ssh/id_dsa.pub.The key fin gerpri nt is:0a:9a:20:46:a2:28:ec:72:23:82:f2:9d:f8:62:9b:d1 oracle n ode1 然后换node2执行rootnode2 # ping 192.168.100.102rootnode2 # ping 10.10.17.222root no de2 opt# su - oracleoracle n ode2 $ mkdir /.sshoracle no de2 $ c

22、hmod 700 /.sshoracle no de2 $ ssh-keyge n -t rsaGenerating public/private rsa key pair.En ter file in which to save the key (/home/oracle/.ssh/id_rsa):En ter passphrase (empty for no passphrase):En ter same passphrase aga in:Your identification has been saved in /home/oracle/.ssh/id_rsa.Your public

23、key has bee n saved in /home/oracle/.ssh/id_rsa.pub.The key fin gerpri nt is:dd:be:7a:37:e4:b5:f0:b2:24:95:50:61:ea:a1:61:07 oraclenode2oracle no de2 $ ssh-keyge n -t dsaGenerating public/private dsa key pair.En ter file in which to save the key (/home/oracle/.ssh/id_dsa):En ter passphrase (empty fo

24、r no passphrase):En ter same passphrase aga in:Your ide ntificatio n has bee n saved in /home/oracle/.ssh/id_dsa.Your public key has bee n saved in /home/oracle/.ssh/id_dsa.pub.The key fin gerpri nt is:bc:b5:cb:43:c7:19:53:d6:f7:16:69:85:12:7f:aa:be oracle no de2切换回node1，接着执行：oracle no de1 $ cat /.s

25、sh/id_rsa.pub >> /.ssh/authorized_keysoracle no de1 $ cat /.ssh/id_dsa.pub >> /.ssh/authorized_keysoracle no de1 $ ssh no de2 cat /.ssh/id_rsa.pub >> /.ssh/authorized_keysThe authe nticity of host ' node2 (192.168.100.102)' can't be established.RSA key fingerprint is 92

26、:d1:ce:5b:c8:a1:52:d5:ac:00:5f:48:5d:12:06:e4.Are you sure you want to con ti nue connecting (yes/no)? yesWarning: Permanently added 'node2,192.168.100.102' (RSA) to the list of known hosts.oracle no de2's password:oracle no de1 $ ssh no de2 cat /.ssh/id_dsa.pub >> /.ssh/authorized

27、_keysoracle no de2's password:oracle no del $ scp /.ssh/authorized_keys no de2:/.ssh/authorized_keys oracle no de2's password:authorized_keys 100% 1992 2.0KB/S 00:00两机相互执行，看看是否还需要输入密码oracle no de1 $ ssh node1 dateoracle no de1 $ ssh node2 dateoracle no de1 $ ssh no de1-priv dateoracle no de1

28、 $ ssh no de2-priv date切换至node2执行oracle no de2 $ ssh node1 dateoracle no de2 $ ssh no de2 dateoracle no de2 $ ssh no de1-priv dateoracle no de2 $ ssh no de2-priv date8、用CVU检查crs$ ./ runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose9、安装crs在 vnc 或 xmanager中运行# xhost+# su - oracle$ ./runlnstaller- ignoreSysPrereqs出现图像安装界面根据界面提示操作。需要注意的是在节点选择界面添加2号节点，在设置网卡是指定好public和private。最后会提示以root用户执行脚本：在 node1上执行：orain stRoot.sh;在 node2上执行：orain stRoot.sh;在 node1上执行：root.sh;# vi crs_1/bi n/vipca找到如下内容:Remove this workaround when the bug 3937317 is fixed arch='un ame -m'if &quo