1、WINXPP 32bbit HYPERLINK /wzy0623/article/details/6245761 從升升級(jí)到100.2.00.4（單實(shí)例例） 1、安裝前前的工作1.1、下下載升級(jí)包包地址：fttp:/updaates.oraccle.ccom/668101189/pp68100189_102004_Wiin32.zip 利用迅雷雷下載并作一個(gè)HHASH認(rèn)認(rèn)證，確保保其正確下下載文件: OO:ROOOTRROOTORACCLEpp68100189_102004_Wiin32.zip大小: 1103466218334 字節(jié)節(jié)修改時(shí)間: 20111年111月10日日

2、, 111:56:50MD5: AF888189447E19903D00089773B9FF7CF33DF5BBSHA1: 97EEDE711B760043844110222B577A60AA897EE59111EC8BBACRC322: BDD77833A91.2 檢檢查系統(tǒng)狀狀態(tài)檢查dbaa_reggistrry 查看看oraccle組件件狀態(tài) Colummn coomp_nname formmat aa40Colummn veersioon foormatt a122Colummn sttatuss forrmat a6Selecct coomp_nname, verrsionn, st

3、tatuss froom syys.dbba_reegisttry; COMP_NAMEEVERSIION STTATUSS Oraclle Daatabaase CCatallog VViewss.0 VAALIDOraclle Daatabaase PPackaages and Typees.0 VAALIDOraclle Woorksppace Manaager.0 VAALIDJServver JJAVA Virttual Machhine.0 VAALIDOraclle XDDK.0 VAALIDOrac

4、lle Daatabaase JJava Packkagess.0 VAALIDOraclle Exxpresssionn Fillter.0 VAALIDOraclle Daata MMininng.0 VAALIDOraclle Teext.0 VAALIDOraclle XMML Daatabaase.0 VAALIDOraclle Ruules Manaager.0 VAALIDOraclle innterMMediaa.0 VAALIDOLAP Anallyticc Wor

5、rkspaace.0 VAALIDOraclle OLLAP AAPI.0 VAALIDOLAP Cataalog.0 VAALIDSpatiial.0 VAALIDOraclle Ennterpprisee Mannagerr.0 VAALID已選擇177行。檢查v$vversiion 查查看系統(tǒng)版版本 SQL seleect * froom v$verssion; BANNEEROraclle Daatabaase 110g EEnterrprisse Edditioon Reeleasse 100.2.00.

6、1.00 - PProdPL/SQQL Reeleasse 100.2.00.1.00 - PProduuctioonCORE .0 PProduuctioonTNS ffor 332-biit Wiindowws: VVersiion .0 - ProdductiionNLSRTTL Veersioon 100.2.00.1.00 - PProduuctioon如果存在iinvallid的組組件執(zhí)行如如下腳本：SQL execc utll_reccomp.recoomp_sseriaal (); 1.3停止止oraccle的所所有服務(wù)(可以在控控制面板-性

7、能與與維護(hù)-管理工具具-服務(wù)務(wù)中停止所所有和 ooraclle相關(guān)的的服務(wù)） 如：命令方式：C: emcttl sttop ddbconnsoleeOraclle Ennterpprisee Mannagerr 10gg Dattabasse Coontrool Reeleasse 100.2.00.1.00Copyrrightt (c) 19996, 22005 Oraccle CCorpooratiion. Alll rigghts reseervedd.http:/loocalhhost:11588/em/conssole/abouutAppplicaationnOraclleDBCCo

8、nsooleorrcl 服服務(wù)正在停停止OraclleDBCConsooleorrcl 服服務(wù)已成功功停止C: isqllplussctl stoppiSQL*Pluss .0Copyrrightt (c) 20003, 22005, Oraacle. Alll riightss resserveed.Stoppping iSQLL*Pluus iSQL*Pluss stooppedd.C: lsnrrctl stoppLSNRCCTL ffor 332-biit Wiindowws: VVersiion .0 - Prodductiion oon 111-1

9、1月月-20111 1116:46Copyrrightt (c) 19991, 22005, Oraacle. Alll riightss resserveed.正在連接到到 (DESCCRIPTTION=(ADDDRESSS=(PRROTOCCOL=TTCP)(HOSTT=cxjj)(POORT=11521)命令執(zhí)行成成功C: sqlpplus / ass syssdbaSQL shuttdownn immmediaateDatabbase clossedDatabbase dismmountted.ORACLLE innstannce sshut downn.1.4冷備備份數(shù)據(jù)庫庫系統(tǒng)利用

10、系統(tǒng)操操作系統(tǒng)命命令對(duì)orraclee dattabasse 100g和$OORACLLE_HOOME 整整體備份1.5升級(jí)級(jí)Oraccle TTime Zonee Deffinittionss（比較繁繁瑣，需要要注意）1.5.11 查看OOraclle 當(dāng)前前時(shí)區(qū)定義義版本：SQL connn / aas syysdbaaConnnecteed.SQQL SSELECCT veersioon FRROM vv$timmezonne_fiile;VERSIION 2 1) Iff thiis quuery repoorts verssion 4, nno acctionn is requuir

11、edd; If thhis rreporrts aa verrsionn lowwer, actiion iis foollowwed : 1.5.22 查看是是否(usser)TTZ daata可能能被影響 TTIMESSTAMPP WITTH LOOCAL TIMEE ZONNE (TTSLTZZ) This needds too be checcked manuuallyy. Thhere is nno auutomaated way to ddiscoover affeectedd datta. selecct c.owneer | . | c.taable_namee | ( | c

12、c.collumn_namee | ) - | c.ddata_typee | col froom dbba_taab_cools cc, dbba_obbjectts owwheree c.ddata_typee likke %LOCAAL TIIME ZZONE aand cc.ownner=oo.ownner andd c.ttablee_namme = o.obbjectt_namme and o.obbjectt_typpe = TABBLEoorderr by col/ COLOE.ORRDERSS(ORDDER_DDATE) -TIIMESTTAMP(6) WWITH LOCAA

13、L TIIME ZZONE進(jìn)行備份 TTIMESSTAMPP WITTH TIIME ZZONE (TSTTZ) 1) 查看看是否有用用戶自己定定義的joobs aand sschedduless,并進(jìn)行行備份Thesee aree thee staandarrd Orraclee proovideed joobs aand sschedduless forr a 110g ddatabbase:SQL SELLECT objeect_nname FROMM dbaa_objjectss WHEERE oobjecct_idd IN (SELLECT obj# FROOM sccheduule

14、r$_winndow);OBJJECT_NAMEEWEEKKNIGHHT_WIINDOWWWEEKKEND_WINDDOWSQQL SSELECCT obbjectt_namme FRROM ddba_oobjeccts WWHEREE objject_id IIN (SSELECCT obbj# FFROM scheeduleer$_jjob);OBJEECT_NNAMEPPURGEE_LOGGFGR$AUTOOPURGGE_JOOBGATTHER_STATTS_JOOBAUTTO_SPPACE_ADVIISOR_JOBRRLM$EEVTCLLEANUUPRLMM$SCHHDNEGGACTI

15、ION6 rowss sellecteed.Iff youu havve ORRACLEE_OCMM insstallled iin 100g yoou wiill aalso see MGMTT_CONNFIG_JOB and MGMTT_STAATS_CCONFIIG_JOOB joobs, brinngingg thee tottal tto 8. (1) IIf onnly tthe OOraclle prrovidded jjobs(as aabovee SQLL shoow) iin DBBMS_SSCHEDDULERR jobbs deefineed thhen ttheree

16、is no aactioon If yoou haave yyour own DBMSS_SCHHEDULLER jjobs defiined, thenn droop yoour jjobs if tthey are timee criiticaal beeforee upggradiing tto 100.2.00.4 aand rresubbmit themm aftter tthe uupdatte/uppgradde iss finnisheed. TThe OOraclle prrovidded jjobs can be lleft alonne, nno neeed tto re

17、ecreaate tthem 2) 查看看是否有除除與jobbs、sccheduules以以外的會(huì)受受影響的(TSTZZ)數(shù)據(jù) Downlload utlttzpv44.sqll froom wwww.meetaliink.ooraclle.coom annd ruun itt.SQL utlltzpvv4.sqqlDROP TABLLE syys.syys_tzzuv2_tempptab CASCCADE CONSSTRAIINTSERRORR at linee 1:ORA-0009422: taable or vview doess nott exiistTablee creeatedd.

18、DROP TABLLE syys.syys_tzzuv2_affeectedd_reggionss CASSCADEE CONNSTRAAINTSSERRORR at linee 1:ORA-0009422: taable or vview doess nott exiistTablee creeatedd.Your currrent timeezonee verrsionn is 2!Do a seleect * froom syys.syys_tzzuv2_tempptab; to see if aany TTIMEZZONEdata is aaffeccted by vversiion

19、44 traansittion rulees.Any ttablee witth YEES inn thee nessted_tab coluumn (lastt collumn) neeedsa mannual checck ass theese aare nnesteed taabless.PL/SQQL prroceddure succcessffullyy commpletted.Commiit coompleete.(1) ggivess no rowss theen thhere is nno acctionn (2) ggive(s) aaffeccted coluumns th

20、enn youu neeed too bacck upp thee repporteed coolumnns exxpectt SYSS.schheduller$% collumnss 如下為joobs、sschedduless相關(guān)數(shù)據(jù)據(jù)，不必進(jìn)進(jìn)行考慮備備份，之前前備份相應(yīng)應(yīng)jobss、schhedullesSQL coluumn ttablee_ownner fformaat a44SQL coluumn ccolummn_naame fformaat a118SQL seleect * froom syys_tzzuv2_tempptab;TABL TABLLE_NAAME COLUUM

21、N_NNAME ROWCCOUNTT NESS SYS SSCHEDDULERR$_JOOB LAAST_EENABLLED_TTIME 3SYS SSCHEDDULERR$_JOOB NEEXT_RRUN_DDATE 1SYS SSCHEDDULERR$_JOOB STTART_DATEE 1SYS SSCHEDDULERR$_JOOB_RUUN_DEETAILLS RREQ_SSTARTT_DATTE 1SYS SSCHEDDULERR$_JOOB_RUUN_DEETAILLS SSTARTT_DATTE 1SYS SSCHEDDULERR$_WIINDOWW LASTT_STAART_D

22、DATE 1if thhis ccolumm conntainns a YESS thhen iit meeans thatt youu havve neestedd tabbles in yyour dataabasee whoo usee a TTIMESSTAMPP WITTH TIIME ZZONE dataatypee.Utlltzpvv4.sqql caannott scaan thhese for affeectedd timmezonnes, it wwill repoort aany nnesteed taable usinng thhe TSSTZ ddatattype.

23、Youu willl neeed tto chheck the nestted ttablee deffinittion and see on wwhat thiss is baseed. 參考文獻(xiàn)： Docc ID: 5533812.1 AActioons ffor tthe DDSTv44 upddate in tthe patcchsett 2、安裝中中的工作3.1界面面上安裝數(shù)數(shù)據(jù)庫paatch下一步下一一步就行步驟一：點(diǎn)點(diǎn)擊下一步步步驟二：提提供 $OORACLLE_HOOME() 信息 ，注意安裝裝目錄改為為已安裝（舊舊版本）的的Oraccle

24、的目目錄步驟三：確確保條件通通過，點(diǎn)擊擊下一步步驟四：不不啟用Orraclee Connfiguuratiion MManagger, 單擊“下一步”。步驟五：點(diǎn)點(diǎn)擊安裝步驟六：在在安裝結(jié)束束畫面, 單擊“退出”, 然后后確認(rèn)退出出 Oraacle Univversaal Innstalller，至至此，ORRACLEE升級(jí)包軟軟件安裝結(jié)結(jié)束3、安裝后后的工作3.1 運(yùn)運(yùn)行數(shù)據(jù)庫庫預(yù)升級(jí)檢檢查( Pree-Upggradee Infformaationn Toool)：服務(wù)里啟啟動(dòng)相對(duì)應(yīng)應(yīng)的數(shù)據(jù)庫庫和監(jiān)聽 C:sqqlpluus / as ssysdbbaSQLsshutddown immee

25、diatteSQLsstarttup uupgraadeSQQLSPPOOL updaate_iinfo.log SQL?rdbmmsadmiinutlu1102i.sqlSSQLSSPOOLL OFFF 在E:DDocummentss andd SetttinggsAddminiistraatorupdaate_iinfo.log 文件Oraclle Daatabaase 110.2 Upgrrade Infoormattion Utillity 111-122-20111 000:17:51 . * Databbase: * - nname: OORCL - vversiion: 1.0

26、- ccompaatiblle:1.0 - bblockksizee: 81922 . * Tableespacces: makke addjusttmentts inn thee currrentt envvironnmentt * - SSYSTEEM taablesspacee is adeqquatee forr thee upggradee. miniimum requuiredd sizze: 4493 MMB AUTOOEXTEEND aaddittionaal sppace requuiredd: 133 MB - UUNDOTTBS1 tabllespaace iis add

27、equaate ffor tthe uupgraade. miniimum requuiredd sizze: 4403 MMB AUTOOEXTEEND aaddittionaal sppace requuiredd: 3668 MBB - SSYSAUUX taablesspacee is adeqquatee forr thee upggradee. miniimum requuiredd sizze: 2257 MMB AUTOOEXTEEND aaddittionaal sppace requuiredd: 7 MB - TTEMP tabllespaace iis addequaa

28、te ffor tthe uupgraade. miniimum requuiredd sizze: 558 MBB AUTOOEXTEEND aaddittionaal sppace requuiredd: 388 MB - EEXAMPPLE ttableespacce iss adeequatte foor thhe uppgradde. miniimum requuiredd sizze: 778 MBB . * Updatte Paarameeterss: UUpdatte Orraclee Dattabasse 100.2 iinit.ora or sspfille * - Noo

29、 upddate paraameteer chhangees arre reequirred. . * Renammed PParammeterrs: Updaate OOraclle Daatabaase 110.2 initt.oraa or spfiile * - Noo rennamedd parrametters founnd. NNo chhangees arre reequirred. . * Obsollete/Deprrecatted PParammeterrs: Updaate OOraclle Daatabaase 110.2 initt.oraa or spfiile*

30、 - Noo obssolette paarameeterss fouund. No cchangges aare rrequiired . * Compoonentts: The folllowinng daatabaase ccompoonentts wiill bbe uppgradded oor innstallled * - OOraclle Caataloog Viiews upggradee VVALIDD - OOraclle Paackagges aand TTypess upggradee VVALIDD - JJServver JJAVA Virttual Machhin

31、e upggradee VVALIDD - OOraclle XDDK foor Jaava upggradee VVALIDD - OOraclle Jaava PPackaages upggradee VVALIDD - OOraclle Teext upggradee VVALIDD - OOraclle XMML Daatabaase upggradee VVALIDD - OOraclle Woorksppace Manaager upggradee VVALIDD - OOraclle Daata MMininng upggradee VVALIDD - OOLAP Anallyt

32、icc Worrkspaace upggradee VVALIDD - OOLAP Cataalog upgrrade VAALID - OOraclle OLLAP AAPI upggradee VVALIDD - OOraclle innterMMediaa upggradee VVALIDD - SSpatiial upggradee VVALIDD - EExpreessioon Fiilterr upggradee VVALIDD - EEM Reeposiitoryy upggradee VVALIDD - RRule Manaager upggradee VVALIDD PL/S

33、QQL 過程程已成功完完成。確保所有內(nèi)內(nèi)容滿足升升級(jí)要求，并并對(duì)不滿足足要求的按按提示進(jìn)行行修改。3.2 進(jìn)進(jìn)行數(shù)據(jù)庫庫字典信息息升級(jí)（時(shí)間挺長(zhǎng)長(zhǎng)，近1個(gè)個(gè)小時(shí)） （Upgrradinng a Releease 10.22 Dattabasse）：C: lsnrrctl starrtC: ssqlpllus / as sysddbaSQQL sstarttup uupgraadeSQL SPOOOL paatch.logSSQL ?rrdbmssadmiincatuupgrdd.sqllSQL SPOOOL OOFF檢查查 pattch.llog 文文件，如果果有問題，修正后重新運(yùn)行catup

34、grd.sql。Oraclle Daatabaase 110.2 Upgrrade Stattus UUtiliity 11-12-22011 01:223:388.Compoonentt Sttatuss VVersiion HH:MMM:SSSOraclle Daatabaase SServeer VALIID4.0 00:009:155JServver JJAVA Virttual MachhineVALIID4.000:115:311Oraclle XDDKVALIID4.0 00:000:355Oraclle Daatabaase JJava PackkagessVALIID 4.0

35、 00:000:300Oraclle TeextVALIID4.0 00:000:488Oraclle XMML DaatabaaseVALIID4.0 00:001:255Oraclle Woorksppace ManaagerVALIID4.3 00:000:411Oraclle Daata MMininngVALIID4.0 00:000:233OLAP Anallyticc WorrkspaaceVALIID4.0 00:000:222OLAP CataalogVALIID4.0 00:000:566Oraclle OLLAP AAPIVALIID4.0 00:000:422Oracl

36、le innterMMediaaVALIID4.0 00:003:377SpatiialVALIID4.0 00:001:433Oraclle Exxpresssionn FillterVALIID .0 000:000:09Oraclle Ennterpprisee Mannagerr VALIID .0 000:011:52Oraclle Ruule MManagger VVALIDD 100.2.00.4.00 000:00:06.Totall Upggradee Timme: 000:388:44Note:If yoou doo nott runn

37、thee cattupgrrd.sqql sccriptt as desccribeed inn thiis seectioon annd yoou sttart up aa dattabasse foor noormall opeeratiion, thenn ORAA-010092: ORACCLE iinstaance termminatted. Discconneectioon foorcedd errrors willl occcur aand tthe eerrorr ORAA-397700: dataabasee musst bee opeened withh UPGGRADEE

38、 opttion willl be in tthe aalertt logg. 3.3 重重啟動(dòng)數(shù)據(jù)據(jù)庫：SQLshuttdownn immmediaateSQQL sstarttup3.4 運(yùn)運(yùn)行utllrp.ssql腳本本重新編譯譯無效PLL/SQLL包SQLL ?rdbbmsadmiinutlrrp.sqqlTIMESSTAMPPCOMP_TIMEESTAMMP UTTLRP_BGN 20111-111-12 09:228:177DOC Thhe foollowwing PL/SSQL bblockk invvokess UTLL_RECCOMP to rrecommpilee invv

39、aliddDOC obbjectts inn thee dattabasse. RRecommpilaationn timme iss prooporttionaal too theeDOC nuumberr of invaalid objeects in tthe ddatabbase, so thiss commmandd mayy takkeDOC a longg timme too exeecutee on a daatabaase wwith a laarge numbber oof innvaliidDOC obbjectts.DOCDOC Usse thhe foollowwin

40、g querries to ttrackk reccompiilatiion pprogrress:DOCDOC 1. Queery rreturrningg thee nummber of iinvallid oobjeccts rremaiiningg. ThhisDOC nummber shouuld ddecreease withh timme.DOC SELEECT CCOUNTT(*) FROMM objj$ WHHERE stattus IIN (44, 5, 6);DOCDOC 2. Queery rreturrningg thee nummber of oobjeccts c

41、compiiled so ffar. Thiss nummberDOC shoould incrreasee witth tiime.DOC SELEECT CCOUNTT(*) FROMM UTLL_RECCOMP_COMPPILEDD;DOCDOC Thhis sscrippt auutomaaticaally choooses seriial oor paaralllel rrecommpilaationnDOC baased on tthe nnumbeer off CPUUs avvailaable (parrametter ccpu_ccountt) muultipplieddDO

42、C byy thee nummber of tthreaads pper CCPU (paraameteer paaralllel_tthreaads_pper_ccpu).DOC Onn RACC, thhis nnumbeer iss addded aacrosss alll RAAC noodes.DOCDOC UTTL_REECOMPP usees DBBMS_SSCHEDDULERR to creaate jjobs for paraallellDOC reecomppilattion. Jobbs arre crreateed wiithouut innstannce aaffin

43、nity so tthat theyyDOC caan miigratte accrosss RACC noddes. Use the folllowinng quueriees too verrifyDOC whhetheer UTTL_REECOMPP jobbs arre beeing creaated and run corrrectlly:DOCDOC 1. Queery sshowiing jjobs creaated by UUTL_RRECOMMPDOC SELEECT jjob_nname FROMM dbaa_schheduller_jjobsDOC WWHEREE job

44、b_namme liike UTL_RECOOMP_SSLAVEE_%;DOCDOC 2. Queery sshowiing UUTL_RRECOMMP joobs tthat are runnningDOC SELEECT jjob_nname FROMM dbaa_schheduller_rrunniing_jjobsDOC WWHEREE jobb_namme liike UTL_RECOOMP_SSLAVEE_%;DOC#TIMESSTAMPPCOMP_TIMEESTAMMP UTTLRP_END 20111-111-12 09:229:099DOC The folllowinng q

45、uuery repoorts the numbber oof obbjectts thhat hhave comppileddDOC withh errrors (objjectss thaat coompille wiith eerrorrs haave sstatuus seet too 3 iinDOC obj$). IIf thhe nuumberr is highher tthan expeectedd, plleasee exaaminee thee errrorDOC messsagess repporteed wiith eeach objeect (usinng SHHOW

46、EERRORRS) tto seee iff theeyDOC poinnt too sysstem miscconfiiguraationn or resoourcee connstraaintss thaat muust bbeDOC fixeed beeforee atttemptting to rrecommpilee theese oobjeccts.DOC#OBJECCTS WWITH ERROORS 0DOC The folllowinng quuery repoorts the numbber oof errrorss cauught duriingDOC recoompill

47、atioon. IIf thhis nnumbeer iss nonn-zerro, ppleasse quuery the erroorDOC messsagess in the tablle UTTL_REECOMPP_ERRRORS to ssee iif anny off theese eerrorrsDOC are due to mmiscoonfiggurattion or rresouurce consstraiints thatt musst beeDOC fixeed beeforee objjectss cann commpilee succcesssfullly.DOC#

48、ERRORRS DUURINGG RECCOMPIILATIION 04.5 運(yùn)運(yùn)行命令檢檢查所有組組件是否升升級(jí)成功:set linees 100000set ppagess 10000colummn coomp_nname formmat aa40colummn veersioon foormatt a122colummn sttatuss forrmat a6SQL SELEECT CCOMP_NAMEE, VEERSIOON, SSTATUUS FRROM SSYS.DDBA_RREGISSTRY;所有輸出出為 VAALID表表示升級(jí)成成功. COMP_NAMEE VERSSION S

49、TAATUS Oraclle Daatabaase CCatallog VViewss4.0 VALIIDOraclle Daatabaase PPackaages and Typees4.0 VALIIDOraclle Woorksppace Manaager4.3 VALIIDJServver JJAVA Virttual Machhine 4.0 VALIIDOraclle XDDK 4.0 VALIIDOraclle Daatabaase JJava Packkagess 4.0 VALIIDOraclle Exxpresssionn Fillter 4.0 VALIIDOraclle

50、 Daata MMininng 4.0 VALIIDOraclle Teext 4.0 VALIIDOraclle XMML Daatabaase 4.0 VALIIDOraclle Ruule MManagger 4.0 VALIIDOraclle innterMMediaa 4.0 VALIIDOLAP Anallyticc Worrkspaace 4.0 VALIIDOraclle OLLAP AAPI 4.0 VALIIDOLAP Cataalog 4.0 VALIIDSpatiial 4.0 VALIIDOraclle Ennterpprisee Mannagerr 4.0 VALI

51、ID查看版本：SQL seleect * froom v$verssion;BANNEEROraclle Daatabaase 110g EEnterrprisse Edditioon Reeleasse 100.2.00.4.00 - PProdPL/SQQL Reeleasse 100.2.00.4.00 - PProduuctioonCORE .0 PProduuctioonTNS ffor 332-biit Wiindowws: VVersiion .0 - ProdductiionNLSRTTL Veersioon 100.2.00.4.00 - PProduuctioon查看是否有有無效對(duì)象象：selecct obbjectt_namme,sttatuss froom dbba_obbjectts whhere stattus=INVAALID;No roows sseleccted 如果使用用Oraccle