oracle 10g 单实例 升级方案文档.docx
- 文档编号:654043
- 上传时间:2023-04-29
- 格式:DOCX
- 页数:22
- 大小:347.29KB
oracle 10g 单实例 升级方案文档.docx
《oracle 10g 单实例 升级方案文档.docx》由会员分享,可在线阅读,更多相关《oracle 10g 单实例 升级方案文档.docx(22页珍藏版)》请在冰点文库上搜索。
oracle10g单实例升级方案文档
WINXP32bit从10.2.0.1升级到10.2.0.4(单实例)
1、安装前的工作
1.1、下载升级包
地址:
ftp:
//利用迅雷下载
并作一个HASH认证,确保其正确下载
文件:
O:
\ROOT\ROOT\ORACLE\p6810189_10204_Win32.zip
大小:
1034621834字节
修改时间:
2011年11月10日,11:
56:
50
MD5:
AF8818947E1903D008973B9F7CF3DF5B
SHA1:
97EDE71B760438411022B57A60A897E5911EC8BA
CRC32:
BD7783A9
1.2检查系统状态
检查dba_registry查看oracle组件状态
Columncomp_nameformata40
Columnversionformata12
Columnstatusformata6
Selectcomp_name,version,statusfromsys.dba_registry;
COMP_NAME
----------------------------------------------------
VERSIONSTATUS
---------------------------------------------------
OracleDatabaseCatalogViews
10.2.0.1.0VALID
OracleDatabasePackagesandTypes
10.2.0.1.0VALID
OracleWorkspaceManager
10.2.0.1.0VALID
JServerJAVAVirtualMachine
10.2.0.1.0VALID
OracleXDK
10.2.0.1.0VALID
OracleDatabaseJavaPackages
10.2.0.1.0VALID
OracleExpressionFilter
10.2.0.1.0VALID
OracleDataMining
10.2.0.1.0VALID
OracleText
10.2.0.1.0VALID
OracleXMLDatabase
10.2.0.1.0VALID
OracleRulesManager
10.2.0.1.0VALID
OracleinterMedia
10.2.0.1.0VALID
OLAPAnalyticWorkspace
10.2.0.1.0VALID
OracleOLAPAPI
10.2.0.1.0VALID
OLAPCatalog
10.2.0.1.0VALID
Spatial
10.2.0.1.0VALID
OracleEnterpriseManager
10.2.0.1.0VALID
已选择17行。
检查v$version查看系统版本
SQL>select*fromv$version;
BANNER
----------------------------------------------------------------
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Prod
PL/SQLRelease10.2.0.1.0-Production
CORE10.2.0.1.0Production
TNSfor32-bitWindows:
Version10.2.0.1.0-Production
NLSRTLVersion10.2.0.1.0-Production
如果存在invalid的组件执行如下脚本:
SQL>executl_recomp.recomp_serial();
1.3停止oracle的所有服务
(可以在控制面板->性能与维护->管理工具->服务中停止所有和oracle相关的服务)
如:
命令方式:
C:
\>emctlstopdbconsole
OracleEnterpriseManager10gDatabaseControlRelease10.2.0.1.0
Copyright(c)1996,2005OracleCorporation.Allrightsreserved.
http:
//localhost:
1158/em/console/aboutApplication
OracleDBConsoleorcl服务正在停止........
OracleDBConsoleorcl服务已成功停止
C:
\>isqlplusctlstop
iSQL*Plus10.2.0.1.0
Copyright(c)2003,2005,Oracle.Allrightsreserved.
StoppingiSQL*Plus...
iSQL*Plusstopped.
C:
\>lsnrctlstop
LSNRCTLfor32-bitWindows:
Version10.2.0.1.0-Productionon11-11月-201111
6:
46
Copyright(c)1991,2005,Oracle.Allrightsreserved.
正在连接到
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cxj)(PORT=1521)))
命令执行成功
C:
\>sqlplus/assysdba
SQL>shutdownimmediate
Databaseclosed
Databasedismounted.
ORACLEinstanceshutdown.
1.4冷备份数据库系统
利用系统操作系统命令对oracledatabase10g和$ORACLE_HOME整体备份
1.5升级OracleTimeZoneDefinitions(比较繁琐,需要注意)
1.5.1查看Oracle当前时区定义版本:
SQL>conn/assysdba
Connected.
SQL>SELECTversionFROMv$timezone_file;
VERSION
----------
2
1)Ifthisqueryreportsversion4,noactionisrequired;
2)Ifthisreportsaversionlower,actionisfollowed:
1.5.2查看是否(user)TZdata可能被影响
1.5.2.1TIMESTAMPWITHLOCALTIMEZONE(TSLTZ)
Thisneedstobecheckedmanually.Thereisnoautomatedwaytodiscoveraffecteddata.
selectc.owner||'.'||c.table_name||'('||c.column_name||')-'||c.data_type||''col
fromdba_tab_colsc,dba_objectso
wherec.data_typelike'%LOCALTIMEZONE'
andc.owner=o.owner
andc.table_name=o.object_name
ando.object_type='TABLE'
orderbycol
/
COL
--------------------------------------------------------------
OE.ORDERS(ORDER_DATE)-TIMESTAMP(6)WITHLOCALTIMEZONE
进行备份
1.5.2.2TIMESTAMPWITHTIMEZONE(TSTZ)
1)查看是否有用户自己定义的jobsandschedules,并进行备份
ThesearethestandardOracleprovidedjobsandschedulesfora10gdatabase:
SQL>SELECTobject_nameFROMdba_objectsWHEREobject_idIN(SELECTobj#FROMscheduler$_window);
OBJECT_NAME
--------------------------------------------------------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
SQL>SELECTobject_nameFROMdba_objectsWHEREobject_idIN(SELECTobj#FROMscheduler$_job);
OBJECT_NAME
--------------------------------------------------------------------------------
PURGE_LOG
FGR$AUTOPURGE_JOB
GATHER_STATS_JOB
AUTO_SPACE_ADVISOR_JOB
RLM$EVTCLEANUP
RLM$SCHDNEGACTION
6rowsselected.
IfyouhaveORACLE_OCMinstalledin10gyouwillalsosee
MGMT_CONFIG_JOBandMGMT_STATS_CONFIG_JOBjobs,bringingthetotalto8.
(1)IfonlytheOracleprovidedjobs(asaboveSQLshow)inDBMS_SCHEDULERjobsdefinedthenthereisnoaction
(2)IfyouhaveyourownDBMS_SCHEDULERjobsdefined,thendropyourjobsiftheyaretimecriticalbeforeupgradingto10.2.0.4andresubmitthemaftertheupdate/upgradeisfinished.TheOracleprovidedjobscanbeleftalone,noneedtorecreatethem
2)查看是否有除与jobs、schedules以外的会受影响的(TSTZ)数据
Downloadutltzpv4.sqlfromandrunit.
SQL>@utltzpv4.sql
DROPTABLEsys.sys_tzuv2_temptabCASCADECONSTRAINTS
ERRORatline1:
ORA-00942:
tableorviewdoesnotexist
Tablecreated.
DROPTABLEsys.sys_tzuv2_affected_regionsCASCADECONSTRAINTS
ERRORatline1:
ORA-00942:
tableorviewdoesnotexist
Tablecreated.
Yourcurrenttimezoneversionis2!
Doaselect*fromsys.sys_tzuv2_temptab;toseeifanyTIMEZONE
dataisaffectedbyversion4transitionrules.
AnytablewithYESinthenested_tabcolumn(lastcolumn)needs
amanualcheckasthesearenestedtables.
PL/SQLproceduresuccessfullycompleted.
Commitcomplete.
(1)givesnorowsthenthereisnoaction
(2)give(s)affectedcolumnsthenyouneedtobackupthereportedcolumnsexpectSYS.scheduler$%columns
如下为jobs、schedules相关数据,不必进行考虑备份,之前备份相应jobs、schedules
SQL>columntable_ownerformata4
SQL>columncolumn_nameformata18
SQL>select*fromsys_tzuv2_temptab;
TABLTABLE_NAMECOLUMN_NAMEROWCOUNTNES
--------------------------------------------------------------------------------------------------------------
SYSSCHEDULER$_JOBLAST_ENABLED_TIME3
SYSSCHEDULER$_JOBNEXT_RUN_DATE1
SYSSCHEDULER$_JOBSTART_DATE1
SYSSCHEDULER$_JOB_RUN_DETAILSREQ_START_DATE1
SYSSCHEDULER$_JOB_RUN_DETAILSSTART_DATE1
SYSSCHEDULER$_WINDOWLAST_START_DATE1
ifthiscolumcontainsa"YES"thenitmeansthatyouhavenestedtablesinyourdatabasewhouseaTIMESTAMPWITHTIMEZONEdatatype.
Utltzpv4.sqlcannotscantheseforaffectedtimezones,itwillreportanynestedtableusingtheTSTZdatatype.Youwillneedtocheckthenestedtabledefinitionandseeonwhatthisisbased.
参考文献:
DocID:
553812.1ActionsfortheDSTv4updateinthe10.2.0.4patchset
2、安装中的工作
3.1界面上安装数据库patch
下一步下一步就行
步骤一:
点击下一步
步骤二:
提供$ORACLE_HOME(10.2.0.1)信息,注意安装目录改为已安装(旧版本)的Oracle的目录
步骤三:
确保条件通过,点击下一步
步骤四:
不启用OracleConfigurationManager,单击“下一步”。
步骤五:
点击安装
步骤六:
在安装结束画面,单击“退出”,然后确认退出OracleUniversalInstaller,至此,ORACLE升级包软件安装结束
3、安装后的工作
3.1运行数据库预升级检查
(Pre-UpgradeInformationTool):
服务里启动相对应的数据库和监听
C:
>sqlplus/assysdba
SQL>shutdownimmediate
SQL>startupupgrade
SQL>SPOOLupdate_info.log
SQL>@?
\rdbms\admin\utlu102i.sql
SQL>SPOOLOFF
在E:
\DocumentsandSettings\Administrator\update_info.log文件
OracleDatabase10.2UpgradeInformationUtility11-12-201100:
17:
51
.
**********************************************************************
Database:
**********************************************************************
-->name:
ORCL
-->version:
10.2.0.1.0
-->compatible:
10.2.0.1.0
-->blocksize:
8192
.
**********************************************************************
Tablespaces:
[makeadjustmentsinthecurrentenvironment]
**********************************************************************
-->SYSTEMtablespaceisadequatefortheupgrade.
....minimumrequiredsize:
493MB
....AUTOEXTENDadditionalspacerequired:
13MB
-->UNDOTBS1tablespaceisadequatefortheupgrade.
....minimumrequiredsize:
403MB
....AUTOEXTENDadditionalspacerequired:
368MB
-->SYSAUXtablespaceisadequatefortheupgrade.
....minimumrequiredsize:
257MB
....AUTOEXTENDadditionalspacerequired:
7MB
-->TEMPtablespaceisadequatefortheupgrade.
....minimumrequiredsize:
58MB
....AUTOEXTENDadditionalspacerequired:
38MB
-->EXAMPLEtablespaceisadequatefortheupgrade.
....minimumrequiredsize:
78MB
.
**********************************************************************
UpdateParameters:
[UpdateOracleDatabase10.2init.oraorspfile]
**********************************************************************
--Noupdateparameterchangesarerequired.
.
**********************************************************************
RenamedParameters:
[UpdateOracleDatabase10.2init.oraorspfile]
**********************************************************************
--Norenamedparametersfound.Nochangesarerequired.
.
**********************************************************************
Obsolete/DeprecatedParameters:
[UpdateOracleDatabase10.2init.oraorspfile]
**********************************************************************
--Noobsoleteparametersfound.Nochangesarerequired
.
**********************************************************************
Components:
[Thefollowingdatabasecomponentswillbeupgradedorinstalled]
**********************************************************************
-->OracleCatalogViews[upgrade]VALID
-->OraclePackagesandTypes[upgrade]VALID
-->JServerJAVAVirtualMachine[upgrade]VALID
-->OracleXDKforJava[upgrade]VALID
-->OracleJavaPackages[upgrade]VALID
-->OracleText[upgrade]VALID
-->OracleXMLDatabase[upgrade]VALID
-->OracleWorkspaceManager[upgrade]VALID
-->OracleDataMining[upgrade]VALID
-->OLAPAnalyticWorkspace[upgrade]VALID
-->OLAPCatalog[upgrade]VALID
-->OracleOLAPAPI
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 10g 单实例 升级方案文档 10 实例 升级 方案 文档