《oracle 10g 单实例 升级方案文档.docx》由会员分享，可在线阅读，更多相关《oracle 10g 单实例 升级方案文档.docx（16页珍藏版）》请在三一办公上搜索。
1、WINXP 32bit从10.2.0.1升级到10.2.0.4（单实例） 1、安装前的工作1.1、下载升级包地址：ftp:/ 利用迅雷下载并作一个HASH认证，确保其正确下载文件: O:ROOTROOTORACLEp6810189_10204_Win32.zip大小: 1034621834 字节修改时间: 2011年11月10日, 11:56:50MD5: AF8818947E1903D008973B9F7CF3DF5BSHA1: 97EDE71B760438411022B57A60A897E5911EC8BACRC32: BD7783A91.2 检查系统状态检查dba_registry 查看
2、oracle组件状态 Column comp_name format a40Column version format a12Column status format a6Select comp_name, version, status from sys.dba_registry; COMP_NAME-VERSION STATUS- -Oracle Database Catalog Views10.2.0.1.0 VALIDOracle Database Packages and Types10.2.0.1.0 VALIDOracle Workspace Manager10.2.0.1.0
3、VALIDJServer JAVA Virtual Machine10.2.0.1.0 VALIDOracle XDK10.2.0.1.0 VALIDOracle Database Java Packages10.2.0.1.0 VALIDOracle Expression Filter10.2.0.1.0 VALIDOracle Data Mining10.2.0.1.0 VALIDOracle Text10.2.0.1.0 VALIDOracle XML Database10.2.0.1.0 VALIDOracle Rules Manager10.2.0.1.0 VALIDOracle i
4、nterMedia10.2.0.1.0 VALIDOLAP Analytic Workspace10.2.0.1.0 VALIDOracle OLAP API10.2.0.1.0 VALIDOLAP Catalog10.2.0.1.0 VALIDSpatial10.2.0.1.0 VALIDOracle Enterprise Manager10.2.0.1.0 VALID已选择17行。检查v$version 查看系统版本 SQL select * from v$version; BANNER-Oracle Database 10g Enterprise Edition Release 10.2
5、.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production如果存在invalid的组件执行如下脚本：SQL exec utl_recomp.recomp_serial (); 1.3停止oracle的所有服务(可以在控制面板-性能与维护-管理工具-服务中停止所有和 oracle相关的服务） 如：命令方式：C: emc
6、tl stop dbconsoleOracle Enterprise Manager 10g Database Control Release 10.2.0.1.0Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.http:/localhost:1158/em/console/aboutApplicationOracleDBConsoleorcl 服务正在停止.OracleDBConsoleorcl 服务已成功停止C: isqlplusctl stopiSQL*Plus 10.2.0.1.0Copyright (c
7、) 2003, 2005, Oracle. All rights reserved.Stopping iSQL*Plus .iSQL*Plus stopped.C: lsnrctl stopLSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-11月-2011 116:46Copyright (c) 1991, 2005, Oracle. All rights reserved.正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cxj)(PORT=1521)命令执行成功C
8、: sqlplus / as sysdbaSQL shutdown immediateDatabase closedDatabase dismounted.ORACLE instance shut down.1.4冷备份数据库系统利用系统操作系统命令对oracle database 10g和$ORACLE_HOME 整体备份1.5升级Oracle Time Zone Definitions（比较繁琐，需要注意）1.5.1 查看Oracle 当前时区定义版本：SQL conn / as sysdbaConnected.SQL SELECT version FROM v$timezone_file
9、;VERSION- 2 1) If this query reports version 4, no action is required; 2) If this reports a version lower, action is followed : 1.5.2 查看是否(user)TZ data可能被影响1.5.2.1 TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) This needs to be checked manually. There is no automated way to discover affected data. select c.
10、owner | . | c.table_name | ( | c.column_name | ) - | c.data_type | col from dba_tab_cols c, dba_objects owhere c.data_type like %LOCAL TIME ZONE and c.owner=o.owner and c.table_name = o.object_name and o.object_type = TABLEorder by col/ COL-OE.ORDERS(ORDER_DATE) -TIMESTAMP(6) WITH LOCAL TIME ZONE进行备
11、份1.5.2.2 TIMESTAMP WITH TIME ZONE (TSTZ) 1) 查看是否有用户自己定义的jobs and schedules,并进行备份These are the standard Oracle provided jobs and schedules for a 10g database:SQL SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_window);OBJECT_NAME-WEEKNIGHT_WINDOWWEEKEND_WINDOWSQL S
12、ELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_job);OBJECT_NAME-PURGE_LOGFGR$AUTOPURGE_JOBGATHER_STATS_JOBAUTO_SPACE_ADVISOR_JOBRLM$EVTCLEANUPRLM$SCHDNEGACTION6 rows selected.If you have ORACLE_OCM installed in 10g you will also see MGMT_CONFIG_JOB and MGMT_STATS_
13、CONFIG_JOB jobs, bringing the total to 8. (1) If only the Oracle provided jobs(as above SQL show) in DBMS_SCHEDULER jobs defined then there is no action (2) If you have your own DBMS_SCHEDULER jobs defined, then drop your jobs if they are time critical before upgrading to 10.2.0.4 and resubmit them
14、after the update/upgrade is finished. The Oracle provided jobs can be left alone, no need to recreate them 2) 查看是否有除与jobs、schedules以外的会受影响的(TSTZ)数据 Download utltzpv4.sql from and run it.SQL utltzpv4.sqlDROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTSERROR at line 1:ORA-00942: table or view does
15、not existTable created.DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTSERROR at line 1:ORA-00942: table or view does not existTable created.Your current timezone version is 2!Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONEdata is affected by version 4 transition rules.A
16、ny table with YES in the nested_tab column (last column) needsa manual check as these are nested tables.PL/SQL procedure successfully completed.Commit complete.(1) gives no rows then there is no action (2) give(s) affected columns then you need to back up the reported columns expect SYS.scheduler$%
17、columns 如下为jobs、schedules相关数据，不必进行考虑备份，之前备份相应jobs、schedulesSQL column table_owner format a4SQL column column_name format a18SQL select * from sys_tzuv2_temptab;TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES- - - - -SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3SYS SCHEDULER$_JOB NEXT_RUN_DATE 1SYS SCHEDULER$_JOB
18、START_DATE 1SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1SYS SCHEDULER$_WINDOW LAST_START_DATE 1if this colum contains a YES then it means that you have nested tables in your database who use a TIMESTAMP WITH TIME ZONE datatype.Utltzpv4.sql cannot scan th
19、ese for affected timezones, it will report any nested table using the TSTZ datatype. You will need to check the nested table definition and see on what this is based. 参考文献： Doc ID: 553812.1 Actions for the DSTv4 update in the 10.2.0.4 patchset 2、安装中的工作3.1界面上安装数据库patch下一步下一步就行步骤一：点击下一步步骤二：提供 $ORACLE_
20、HOME(10.2.0.1) 信息 ，注意安装目录改为已安装（旧版本）的Oracle的目录步骤三：确保条件通过，点击下一步步骤四：不启用Oracle Configuration Manager, 单击“下一步”。步骤五：点击安装步骤六：在安装结束画面, 单击“退出”, 然后确认退出 Oracle Universal Installer，至此，ORACLE升级包软件安装结束3、安装后的工作3.1 运行数据库预升级检查( Pre-Upgrade Information Tool)：服务里启动相对应的数据库和监听 C:sqlplus / as sysdbaSQLshutdown immediateS
21、QLstartup upgradeSQLSPOOL update_info.log SQL?rdbmsadminutlu102i.sqlSQLSPOOL OFF 在E:Documents and SettingsAdministratorupdate_info.log 文件Oracle Database 10.2 Upgrade Information Utility 11-12-2011 00:17:51 . * Database: * - name: ORCL - version: 10.2.0.1.0 - compatible:10.2.0.1.0 - blocksize: 8192 .
22、 * Tablespaces: make adjustments in the current environment * - SYSTEM tablespace is adequate for the upgrade. . minimum required size: 493 MB . AUTOEXTEND additional space required: 13 MB - UNDOTBS1 tablespace is adequate for the upgrade. . minimum required size: 403 MB . AUTOEXTEND additional spac
23、e required: 368 MB - SYSAUX tablespace is adequate for the upgrade. . minimum required size: 257 MB . AUTOEXTEND additional space required: 7 MB - TEMP tablespace is adequate for the upgrade. . minimum required size: 58 MB . AUTOEXTEND additional space required: 38 MB - EXAMPLE tablespace is adequat
24、e for the upgrade. . minimum required size: 78 MB . * Update Parameters: Update Oracle Database 10.2 init.ora or spfile * - No update parameter changes are required. . * Renamed Parameters: Update Oracle Database 10.2 init.ora or spfile * - No renamed parameters found. No changes are required. . * O
25、bsolete/Deprecated Parameters: Update Oracle Database 10.2 init.ora or spfile* - No obsolete parameters found. No changes are required . * Components: The following database components will be upgraded or installed * - Oracle Catalog Views upgrade VALID - Oracle Packages and Types upgrade VALID - JS
26、erver JAVA Virtual Machine upgrade VALID - Oracle XDK for Java upgrade VALID - Oracle Java Packages upgrade VALID - Oracle Text upgrade VALID - Oracle XML Database upgrade VALID - Oracle Workspace Manager upgrade VALID - Oracle Data Mining upgrade VALID - OLAP Analytic Workspace upgrade VALID - OLAP
27、 Catalog upgrade VALID - Oracle OLAP API upgrade VALID - Oracle interMedia upgrade VALID - Spatial upgrade VALID - Expression Filter upgrade VALID - EM Repository upgrade VALID - Rule Manager upgrade VALID PL/SQL 过程已成功完成。确保所有内容满足升级要求，并对不满足要求的按提示进行修改。3.2 进行数据库字典信息升级（时间挺长，近1个小时） （Upgrading a Release 1
28、0.2 Database）：C: lsnrctl startC: sqlplus / as sysdbaSQL startup upgradeSQL SPOOL patch.logSQL ?rdbmsadmincatupgrd.sqlSQL SPOOL OFF检查 patch.log 文件，如果有问题，修正后重新运行catupgrd.sql。Oracle Database 10.2 Upgrade Status Utility 11-12-2011 01:23:38.Component Status Version HH:MM:SSOracle Database Server VALID10.
29、2.0.4.0 00:09:15JServer JAVA Virtual MachineVALID10.2.0.4.000:15:31Oracle XDKVALID10.2.0.4.0 00:00:35Oracle Database Java PackagesVALID 10.2.0.4.0 00:00:30Oracle TextVALID10.2.0.4.0 00:00:48Oracle XML DatabaseVALID10.2.0.4.0 00:01:25Oracle Workspace ManagerVALID10.2.0.4.3 00:00:41Oracle Data MiningV
30、ALID10.2.0.4.0 00:00:23OLAP Analytic WorkspaceVALID10.2.0.4.0 00:00:22OLAP CatalogVALID10.2.0.4.0 00:00:56Oracle OLAP APIVALID10.2.0.4.0 00:00:42Oracle interMediaVALID10.2.0.4.0 00:03:37SpatialVALID10.2.0.4.0 00:01:43Oracle Expression FilterVALID 10.2.0.4.0 00:00:09Oracle Enterprise Manager VALID 10
31、.2.0.4.0 00:01:52Oracle Rule Manager VALID 10.2.0.4.0 00:00:06.Total Upgrade Time: 00:38:44Note:If you do not run the catupgrd.sql script as described in this section and you start up a database for normal operation, then ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log. 3.3 重启动数据库：SQLshutdown immediateSQL startup3.4 运行utlrp.sql脚本重新编译无效PL/SQL包SQL ?rdbmsadminutlrp.sqlTIMESTAMP-COMP_TIMESTAMP UTLRP_BGN 2011-11-12 09:28:17DOC The following PL/SQ
