1、安装前的工作
1.1、下载升级包
地址:ftp://updates.oracle.com/6810189/p6810189_10204_Win32.zip 利用迅雷下载
并作一个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组件状态
Column comp_name format a40
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;
COMP_NAME
----------------------------------------------------
VERSION STATUS
------------------------------ ---------------------
Oracle Database Catalog Views
10.2.0.1.0 VALID
Oracle Database Packages and Types
10.2.0.1.0 VALID
Oracle Workspace Manager
10.2.0.1.0 VALID
JServer JAVA Virtual Machine
10.2.0.1.0 VALID
Oracle XDK
10.2.0.1.0 VALID
Oracle Database Java Packages
10.2.0.1.0 VALID
Oracle Expression Filter
10.2.0.1.0 VALID
Oracle Data Mining
10.2.0.1.0 VALID
Oracle Text
10.2.0.1.0 VALID
Oracle XML Database
10.2.0.1.0 VALID
Oracle Rules Manager
10.2.0.1.0 VALID
Oracle interMedia
10.2.0.1.0 VALID
OLAP Analytic Workspace
10.2.0.1.0 VALID
Oracle OLAP API
10.2.0.1.0 VALID
OLAP Catalog
10.2.0.1.0 VALID
Spatial
10.2.0.1.0 VALID
Oracle Enterprise Manager
10.2.0.1.0 VALID
已选择17行。
检查v$version 查看系统版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
如果存在invalid的组件执行如下脚本:
SQL> exec utl_recomp.recomp_serial ();
1.3停止oracle的所有服务
(可以在控制面板->性能与维护->管理工具->服务中停止所有和 oracle相关的服务)
如:
命令方式:
C:\> emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost:1158/em/console/aboutApplication
OracleDBConsoleorcl 服务正在停止........
OracleDBConsoleorcl 服务已成功停止
C:\> isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.
C:\> lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-11月-2011 11
6:46
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cxj)(PORT=1521)))
命令执行成功
C:\> sqlplus / as sysdba
SQL> shutdown immediate
Database closed
Database 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 sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;
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.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%LOCAL TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by col
/
COL
--------------------------------------------------------------
OE.ORDERS(ORDER_DATE) -TIMESTAMP(6) WITH LOCAL TIME ZONE
进行备份
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_WINDOW
WEEKEND_WINDOW
SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_job);
OBJECT_NAME
--------------------------------------------------------------------------------
PURGE_LOG
FGR$AUTOPURGE_JOB
GATHER_STATS_JOB
AUTO_SPACE_ADVISOR_JOB
RLM$EVTCLEANUP
RLM$SCHDNEGACTION
6 rows selected.
If you have ORACLE_OCM installed in 10g you will also see
MGMT_CONFIG_JOB and MGMT_STATS_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 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 www.metalink.oracle.com and run it.
SQL> @utltzpv4.sql
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
Your current timezone version is 2!
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
data is affected by version 4 transition rules.
Any table with YES in the nested_tab column (last column) needs
a 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$% columns
如下为jobs、schedules相关数据,不必进行考虑备份,之前备份相应jobs、schedules
SQL> column table_owner format a4
SQL> column column_name format a18
SQL> select * from sys_tzuv2_temptab;
TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES
---- ---------------------------------------------------- --------------------------------- ---------------- -----
SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3
SYS SCHEDULER$_JOB NEXT_RUN_DATE 1
SYS SCHEDULER$_JOB START_DATE 1
SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1
SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1
SYS SCHEDULER$_WINDOW LAST_START_DATE 1
if 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 these 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_HOME(10.2.0.1) 信息 ,注意安装目录改为已安装(旧版本)的Oracle的目录
步骤三:确保条件通过,点击下一步
步骤四:不启用Oracle Configuration Manager, 单击“下一步”。
步骤五:点击安装
步骤六:在安装结束画面, 单击“退出”, 然后确认退出 Oracle Universal Installer,至此,ORACLE升级包软件安装结束
3、安装后的工作
3.1 运行数据库预升级检查
( Pre-Upgrade Information Tool):
服务里启动相对应的数据库和监听
C:>sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup upgrade
SQL>SPOOL update_info.log
SQL>@?\rdbms\admin\utlu102i.sql
SQL>SPOOL OFF
在E:\Documents and Settings\Administrator\update_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
.
**********************************************************************
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 space 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 adequate 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.
.
**********************************************************************
Obsolete/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
--> JServer 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 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 10.2 Database):
C:> lsnrctl start
C:> sqlplus / as sysdba
SQL> startup upgrade
SQL> SPOOL patch.log
SQL> @?\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
检查 patch.log 文件,如果有问题,修正后重新运行catupgrd.sql。
Oracle Database 10.2 Upgrade Status Utility 11-12-2011 01:23:38
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:09:15
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:31
Oracle XDK VALID 10.2.0.4.0 00:00:35
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:30
Oracle Text VALID 10.2.0.4.0 00:00:48
Oracle XML Database VALID 10.2.0.4.0 00:01:25
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:41
Oracle Data Mining VALID 10.2.0.4.0 00:00:23
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:22
OLAP Catalog VALID 10.2.0.4.0 00:00:56
Oracle OLAP API VALID 10.2.0.4.0 00:00:42
Oracle interMedia VALID 10.2.0.4.0 00:03:37
Spatial VALID 10.2.0.4.0 00:01:43
Oracle Expression Filter VALID 10.2.0.4.0 00:00:09
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:52
Oracle Rule Manager VALID 10.2.0.4.0 00:00:06
.
Total Upgrade Time: 00:38:44
Note:
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 重启动数据库:
SQL>shutdown immediate
SQL> startup
3.4 运行utlrp.sql脚本重新编译无效PL/SQL包
SQL> @?\rdbms\admin\utlrp.sql
TIMESTAMP
------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-11-12 09:28:17
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2011-11-12 09:29:09
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
4.5 运行命令检查所有组件是否升级成功:
set lines 10000
set pages 1000
column comp_name format a40
column version format a12
column status format a6
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
所有输出为 VALID表示升级成功.
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
查看版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
查看是否有无效对象:
select object_name,status from dba_objects where status='INVALID';
No rows selected
如果使用Oracle Recovery Manager catalog, 需要对catalog进行升级,如下:
· C:> rman catalog username/password@alias
· RMAN> UPGRADE CATALOG;
发现em还是版本,
emca -displayconfig
继续升级emconsole
C:> emca -upgrade db
填入相应配置 ok
注:升级后访问使用的是https
4、问题处理
1)MSVCR71.DLL 更新时在使用
找到用该DLL的进程,具体方法如下:
MSVCR71.DLL is in use during Oracle patch installation. If you are trying to upgrade the Oracle Client and an error message is displayed telling you that msvcr71.dll is in use, try the following:
1) Verify that all Oracle services are stopped.
2) Stop the Distributed Transaction Coordinator – In most cases this will solve the iss.
3) Run Process Explorer
a) In the Find menu, click on “Find Handle or DLL” (CTRL F)
b) Type msvcr71.dll and click on Search
c) Go through the list and close the listed applications
这样找到使用的进程(msdtc.exe)。
对于msdtc.exe就是Distributed Transaction Coordinator的进程,在service中把该进程关闭。
安装正常进行~
2)数据字典问题:
刚开始没有升级数据字典。
发生如下问题
SQL> conn /as sysdba
SQL> startup
ORA-01092: ORACLE instance terminated. Disconnection forced
查看alertlog,如下问题:
SMON: enabling cache recovery
Fri Jul 2 15:30:15 2010
Errors in file F:\oracle\product\10.2.0\db_1\admin\orcl\udump\orcl_ora_12856.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Fri Jul 2 15:30:15 2010
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12856
ORA-1092 signalled during: ALTER DATABASE OPEN…
解决方法:正常升级数据字典。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24104518/viewspace-710949/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24104518/viewspace-710949/