ORACLE數(shù)據(jù)庫遷移方案

1、數(shù)據(jù)庫整理方案萬佳系統(tǒng)數(shù)據(jù)庫遷移方案信息系統(tǒng)課王川2012-7-271 事前事項(1)實施日期：高溫假期間,(2)預(yù)計用時：3個工作日(3)參與人員：(4)影響范圍：1、萬佳系統(tǒng)2、庫場系統(tǒng)7月29日到7月31日。2 事前準(zhǔn)備1、 安裝兩臺 IBM 750服務(wù)器操作系統(tǒng)：CentOS 5.6 X86_64bit。2、共享磁盤準(zhǔn)備：2個RAW OCR磁盤：每個 1G3個RAW VOTING磁盤：每個 1G1 個 ASM DATA+ 磁盤：150G1 個 ASM RECOVER磁盤：150G3、安裝介質(zhì)：（1）oracle 10gr2 clusterware（2）database（3）10.2.0

2、.4 升級包遷移流程：1、安裝系統(tǒng)及支持程序包。2、設(shè)置兩個節(jié)點的 HOSTS3、設(shè)置時間同步。4、測試時間同步。5、新增ORACLE用戶、用戶組。6、設(shè)置ORACLE用戶環(huán)境變量。7、設(shè)置用戶等價。&測試用戶等價。9、磁盤分區(qū)。10、創(chuàng)建RAW設(shè)備（5個）。11、檢查RAW設(shè)備創(chuàng)建是否成功。12、創(chuàng)建ASM磁盤（2個）。13、檢查ASM磁盤權(quán)限是否屬于用戶 ORACLE權(quán)限是否屬于用戶組 DBA。14、在1號節(jié)點上安裝 clusterware。15、測試雙節(jié)點clusterware進程及狀態(tài)是否正常。16、在1號節(jié)點上安裝 ASM。17、在1號節(jié)點上安裝 DADABASE SOFT1

3、8、在2號節(jié)點上VIPCA創(chuàng)建VIP。19、檢查雙節(jié)點的 VIP是否正常。20、在1號節(jié)點上DBCA創(chuàng)建數(shù)據(jù)庫，期間分配 ASM磁盤。21、10.2.0.2 升級到 10.2.0.4。22、檢測EM是否正常。23、在EM上新建用戶表空間： CASKWMES24、新增數(shù)據(jù)庫用戶： CASKWMES權(quán)限："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、查詢目前萬佳系統(tǒng)數(shù)據(jù)庫的信息：(1) 無效對象：SELECT * FROM DBA_OBJECTS WHERE STATUS='INVALID'(2) 相關(guān)用戶對象總數(shù)：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) 表 空間表 數(shù)量和 行數(shù)：SELECT TABLE_NAME,TABLESPACE_NAME,NUM_ROWS FROM USER_TABLES WHERE TABLESPACE_NAME='CASKWMES'

6、;26、停止萬佳系統(tǒng)應(yīng)用及服務(wù)。27、使用EXP/EXPDP命令將目前萬佳數(shù)據(jù)庫導(dǎo)出并SCP方式傳到需要遷移的服務(wù)器上，具體的命令如下(1) exp CASKWMES/密碼indexes=y rows=y file= /wmesbak/caskwmes_exp_201201xx.dmplog= /wmesbak/caskwmes_exp_201207xx .log(2) 第(1)種導(dǎo)出有問題時，采用該方法導(dǎo)出的文件進行導(dǎo)入：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 監(jiān)控：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、將導(dǎo)出的數(shù)據(jù)文件導(dǎo)入到需要遷移的數(shù)據(jù)庫中。imp CASKWMES/ 密 碼 file= caskwmes_exp_201201xx.dmp in dexes=y log= caskwmes_imp_201207xx .log29、查詢第25步導(dǎo)出的數(shù)據(jù)信息，遷移服務(wù)器進行對比。30、更改萬佳正式庫服務(wù)器的連接進程，重啟進程，重啟4.100的打印服務(wù)，數(shù)據(jù)同步服務(wù)，安燈31、通知趙夢男對萬佳狀態(tài)進行驗證。3 實施步驟3.1 安裝RAC1、根據(jù)生產(chǎn)系統(tǒng)情況修改hosts文件# vi /etc/hosts2、創(chuàng)建組 oinstall,dba,用戶 oracle# groupadd

9、 oin stall# groupadd dba# useradd -g oin stall -G dba oracle# passwd oracle3、 創(chuàng)建安裝目錄并修改權(quán)限，修改oracle用戶的初始化參數(shù)文件# mkdir /opt/ora10g-此處路徑僅為舉例# chow n oracle, oin stall /opt/ora10g# su - oracle$ vi /home/oracle/.bash_profile增加如下內(nèi)容：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安裝路徑介質(zhì)為 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頁數(shù)據(jù)庫整理方案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、配置內(nèi)核參數(shù)# vi /etc/sysctl.c onf增加或修改下列內(nèi)容kernel.shmall = 2097152 kernel.shmmax = 68719476736 - 內(nèi)存 128G 服務(wù)器的配置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設(shè)置oracle使用的文件數(shù)權(quán)限# vi /etc/security/limits.c onf增加下列內(nèi)容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、綁定裸設(shè)備給共享磁盤分區(qū)# fdisk /dev/sdb輸入的依次是"n/p/1/ 回車/回車/w"# fdisk /dev/sdc輸入的依次是”n/p/1/ 回車/回車/w”# vi /etc/udev/rules.d/60-raw.rules增加如下內(nèi)容：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”重啟服務(wù)# start_udev7、設(shè)置ssh測試兩個節(jié)點間互ping,確保都是通的首先在node1執(zhí)行：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執(zhí)行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，接著執(zhí)行：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兩機相互執(zhí)行，看看是否還需要輸入密碼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執(zhí)行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出現(xiàn)圖像安裝界面根據(jù)界面提示操作。需要注意的是在節(jié)點選擇界面添加2號節(jié)點，在設(shè)置網(wǎng)卡是指定好public和private。最后會提示以root用戶執(zhí)行腳本：在 node1上執(zhí)行：orain stRoot.sh;在 node2上執(zhí)行：orain stRoot.sh;在 node1上執(zhí)行：root.sh;# vi crs_1/bi n/vipca找到如下內(nèi)容:Remove this workaround when the bug 3937317 is fixed arch='un ame -m'if &quo