Linux RHEL5下ORACLE 10g 10.2.0.4.0升级10.2.0.5.0操作文档.doc

Linux(RHEL5)下ORACLE_10g_10.2.0.4.0升级到10.2.0.5.0操作文档2013-9-2北京科迈科技有限公司Linux(RHEL5)下ORACLE_10g_10.2.0.4.0升级到10.2.0.5.0操作文档Tony-Liang2013/9/2Linux(RHEL5)下ORACLE 10g 10.2.0.4.0升级到10.2.0.5.0操作文档 系统环境：RHEL5文档版本：V1.0.1整理：Tony-Liang更新时间：2013-09-02备注：DBA专用目 录目 录21.文档更新记录42. 文档说明53. 升级前准备工作63.1.1. 临时增加shared pool和java pool 的大小63.1.2. 冷备份oracle软件，备份数据库73.1.3. 停掉监听，OEM,ISQLPLUS 等73.1.3.1停监听73.1.3.2停EM73.1.3.3停isqlplusctl73.1.4. 上传补丁包84. 数据库软件产品的升级94.1.1.登录图形界面执行runInstaller，开启安装OUI界面94.1.2出现OUI图形界面如下：104.1.3点击Next,进入下一界面114.1.4点击Next,进入下一界面134.1.5点击Next，进入下一界面144.1.6点击Next，进入下一界面154.1.7点击Next，进入下一界面154.1.8等待安装完成出现以下界面174.1.9执行脚本174.1.9点击”OK”按钮，弹出如下界面：185. 进行数据库的升级操作195.1.1. 开始升级及更新数据字典195.1.2. 重编译失效对象205.1.3. 重新创建数据字典CATALOG.SQL215.1.4 重新创建运行脚本CATPROC.SQL215.1.5. 察看数据库安装的组件、版本、状态226. 升级总结286.1.1. 全新安装的情况286.1.2. 非全新安装的情况286.1.3. 升级失败，尝试回退机制291.文档更新记录 时间修改人版本号修改说明2013-09-02Tony-Liang1.0.0建立文档 2. 文档说明 本文档规范oracle10.2.0.4.0升级到10.2.0.5.0版本的升级流程及操作，供DBA人员在进行数据库版本升级的时参考使用。3. 升级前准备工作 alter system set shared_pool_size=150M scope=spfile;alter system set java_pool_size=150M scope=spfile;tar -cvf /u01/arch.tar.gz /u01/app/3.1.1. 临时增加shared pool和java pool 的大小 确保参数shared_pool_size和java_pool_size 至少为150M大小，保证catupgrd.sql（10g）升级脚本运行正常。 oracleTRH10g backup$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 28 12:16:33 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.00:06:50 SYS # Tony startupORACLE instance started.Total System Global Area 524288000 bytesFixed Size 1268460 bytesVariable Size 146801940 bytesDatabase Buffers 373293056 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened. 12:16:39 SYS # Tony alter system set shared_pool_size=150M scope=spfile; 12:16:41 SYS # Tony alter system set java_pool_size=150M scope=spfile; 12:16:42 SYS # Tony shutdown immediate 3.1.2. 冷备份oracle软件，备份数据库 oracleTRH10g u01$ tar -cvf /u01/arch.tar.gz /u01/app/ 3.1.3. 停掉监听，OEM,ISQLPLUS 等 lsnrctl stopemctl stop dbconsoleisqlplusctl stop3.1.3.1停监听oracleTRH10g dbs$ lsnrctl stopLSNRCTL for Linux: Version 10.2.0.4.0 - Production on 01-SEP-2013 00:08:37Copyright (c) 1991, 2007, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TRH10g)(PORT=1521)The command completed successfully3.1.3.2停EMoracleTRH10g app$ emctl stop dbconsoleTZ set to PRCOC4J Configuration issue. /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_TRH10g_Tony not found. 3.1.3.3停isqlplusctloracleTRH10g app$ isqlplusctl stopiSQL*Plus 10.2.0.4.0Copyright (c) 2003, 2005, Oracle. All rights reserved.getnameinfo failediSQL*Plus instance on port 5560 is not running . 3.1.4. 上传补丁包 将p8202632_10205_LINUX.zip上传到虚拟机上，并解压oracleTRH10g backup$ unzip p8202632_10205_LINUX.zip4. 数据库软件产品的升级 4.1.1.登录图形界面执行runInstaller，开启安装OUI界面 oracleTRH10g backup$ ./runInstaller Starting Oracle Universal Installer. Checking installer requirements. Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11 Passed All installer requirements met. Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-08-26_10-34-03AM. Please wait .oracleTRH10g backup$ Oracle Universal Installer, Version 10.2.0.5.0 Production Copyright (C) 1999, 2010, Oracle. All rights reserved. 4.1.2出现OUI图形界面如下：4.1.3点击Next,进入下一界面4.1.4点击Next,进入下一界面对编译不通过的选项需要进行处理。内核参数不通过需要修改内核参数，需要修改的文件为/etc/sysctl.conf 。缺少rmp找到相关的rpm，用命令rpm ivh rmp名称 安装4.1.5点击Next，进入下一界面4.1.6点击Next，进入下一界面4.1.7点击Next，进入下一界面4.1.8等待安装完成出现以下界面4.1.9执行脚本以root 身份登录，执行脚本/u01/oracle/product/10.2.0/db_1/root.sh，该脚本会提示是否覆盖已存在的目录,默认为否或为是。oracleTRH10g Disk1$ su - rootPassword: rootTRH10g # cd /u01/app/oracle/product/10.2.0/db_1/rootTRH10g db_1# ./root.shRunning Oracle10 root.sh script.The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1Enter the full pathname of the local bin directory: /usr/local/bin: The file dbhome already exists in /usr/local/bin. Overwrite it? (y/n) n: y Copying dbhome to /usr/local/bin .The file oraenv already exists in /usr/local/bin. Overwrite it? (y/n) n: y Copying oraenv to /usr/local/bin .The file coraenv already exists in /usr/local/bin. Overwrite it? (y/n) n: y Copying coraenv to /usr/local/bin .Entries will be added to the /etc/oratab file as needed byDatabase Configuration Assistant when a database is createdFinished running generic part of root.sh script.Now product-specific root actions will be performed.4.1.9点击”OK”按钮，弹出如下界面：此时提示版本升级已经成功，点击 ”Exit”按钮，退出即可。 至此，数据库软件产品升级已经完成。5. 进行数据库的升级操作startup upgrade;?/rdbms/admin/catupgrd.sqlshutdown immediate;startup;?/rdbms/admin/utlrp.sql?/rdbms/admin/catalog.sql?/rdbms/admin/catproc.sql5.1.1. 开始升级及更新数据字典此脚本执行时间看机器性能及JAVA_POOL_SIZE大小而定设置JAVA_POOL_SIZE 为150M时的升级所用时间Total Upgrade Time: 00:19:35 不设置JAVA_POOL_SIZE 为150M时的升级所用时间Total Upgrade Time: 01:10:03所以，一定要记得设置这个参数。rootTRH10g db_1# su - oracleoracleTRH10g $ sqlplus / as sysdba;SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 28 13:08:46 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to an idle instance.SQL startup upgrade;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1267068 bytesVariable Size 88083076 bytesDatabase Buffers 192937984 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL ?/rdbms/admin/catupgrd.sqlDOC#DOC#DOC The following statement will cause an ORA-01722: invalid numberDOC error if the user running this script is not SYS. DisconnectDOC and reconnect with AS SYSDBA.Total Upgrade Time: 01:10:03DOC#DOC#DOCDOC The above PL/SQL lists the SERVER components in the upgradedDOC database, along with their current version and status.DOCDOC Please review the status and version columns and look forDOC any errors in the spool log file. If there are errors in the spoolDOC file, or any components are not VALID or not the current version,DOC consult the Oracle Database Upgrade Guide for troubleshootingDOC recommendations.DOCDOC Next shutdown immediate, restart for normal operation, and thenDOC run utlrp.sql to recompile any invalid application objects.DOCDOC#DOC#SQL shutdown immediate;5.1.2. 重编译失效对象oracleTRH10g $ sqlplus / as sysdba;SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 28 14:22:16 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to an idle instance.SQL ?/rdbms/admin/utlrp.sql.PL/SQL procedure successfully completed.5.1.3. 重新创建数据字典CATALOG.SQL CATALOG.SQLCreates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. GrantsPUBLICaccess to the synonyms.SQL ?/rdbms/admin/catalog.sqlDOC#DOC#DOC The following statement will cause an ORA-01722: invalid numberDOC error and terminate the SQLPLUS session if the user is not SYS.DOC Disconnect and reconnect with AS SYSDBA.DOC#DOC#.Synonym created.Grant succeeded.PL/SQL procedure successfully completed.5.1.4 重新创建运行脚本CATPROC.SQL CATPROC.SQLRuns all scripts required for or used with PL/SQL.SQL ?/rdbms/admin/catproc.sqlDOC#DOC#DOC The following PL/SQL block will cause an ORA-20000 error andDOC terminate the current SQLPLUS session if the user is not SYS.DOC Disconnect and reconnect with AS SYSDBA.DOC#DOC#.SQL BEGIN 2 dbms_registry.update_schema_list(CATPROC, 3 dbms_registry.schema_list_t(SYSTEM, OUTLN, DBSNMP); 4 dbms_registry.loaded(CATPROC); 5 dbms_registry_sys.validate_catproc; 6 dbms_registry_sys.validate_catalog; 7 END; 8 /PL/SQL procedure successfully completed.SQL SQL SET SERVEROUTPUT OFF5.1.5. 察看数据库安装的组件、版本、状态SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startup;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1267068 bytesVariable Size 180357764 bytesDatabase Buffers 100663296 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.查看有无无效对象，有需要重新编译SQLset linesize 500;SQLset pagesize 50;SQLcol object_name for a30;SQLselect owner,object_name,subobject_name,object_type,status from dba_objects where statusVALID;OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE STATUS- - - - -OE ACCOUNT_MANAGERS VIEW INVALIDOE CUSTOMERS_VIEW VIEW INVALID有两个无效对象，需要重新编译，处理过程如下：-修改密码并赋予相关权限SQL alter user OE identified by oe;User altered.SQL grant resource to oe;Grant succeeded.SQL grant connect to oe;Grant succeeded.SQL alter user oe account unlock;User altered.SQL conn oe/oeConnected.-查OE有哪些对象SQL select * from tab;TNAME TABTYPE CLUSTERID- - -CUSTOMERS TABLEWAREHOUSES TABLEORDER_ITEMS TABLEORDERS TABLEINVENTORIES TABLEPRODUCT_INFORMATION TABLEPRODUCT_DESCRIPTIONS TABLEPROMOTIONS TABLECOUNTRIES SYNONYMLOCATIONS SYNONYMDEPARTMENTS SYNONYMJOBS SYNONYMEMPLOYEES SYNONYMJOB_HISTORY SYNONYMPRODUCTS VIEWSYDNEY_INVENTORY VIEWBOMBAY_INVENTORY VIEWTORONTO_INVENTORY VIEWPRODUCT_PRICES VIEWACCOUNT_MANAGERS VIEWCUSTOMERS_VIEW VIEWORDERS_VIEW VIEWPURCHASEORDER TABLESYS_IOT_OVER_52386 TABLESYS_IOT_OVER_52391 TABLELINEITEM_TABLE TABLEACTION_TABLE TABLECATEGORIES_TAB TABLEPRODUCT_REF_LIST_NESTEDTAB TABLESUBCATEGORY_REF_LIST_NESTEDTAB TABLEOC_INVENTORIES VIEWOC_PRODUCT_INFORMATION VIEWOC_CUSTOMERS VIEWOC_CORPORATE_CUSTOMERS VIEWOC_ORDERS VIEW35 rows selected.-查看无效对象引用哪些对象可以看到COUNTRIES表HR用的，编译是提示权限不足，应该是没有select权限SQL select * from user_synonyms;SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK- - - -COUNTRIES HR COUNTRIESLOCATIONS HR LOCATIONSDEPARTMENTS HR DEPARTMENTSJOBS HR JOBSEMPLOYEES HR EMPLOYEESJOB_HISTORY HR JOB_HISTORY6 rows selected.-验证，提示不存在表和视图SQL select * from COUNTRIES;select * from COUNTRIES *ERROR at line 1:ORA-00942: table or view does not exist-授予OE用户select权限SQL conn / as sysdba;Connected.SQL grant select on HR.COUNTRIES to OE;Grant succeeded.重新编译后，顺利解决问题。查看实例状态SQL select status from v$instance; STATUS-OPEN1 row selected.查看版本信息SQL select * from v$version; BANNER-Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProdPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production5 rows selected.SQL col comp_name format a30 SQL col version format a30 SQL col status format a10SQL SELECT comp_name, version, status FROM dba_registry;COMP_NAME VERSION STATUS- - -Oracle Enterprise Manager 10.2.0.5.0 VALIDSpatial 10.2.0.5.0 VALIDOracle interMedia 10.2.0.5.0 VALIDOLAP Catalog 10.2.0.5.0 VALIDOracle XML Database 10.2.0.5.0 VALIDOracle Text 10.2.0.5.0 VALIDOracle Expression Filter 10.2.0.5.0 VALIDOracle Rule Manager 10.2.0.5.0 VALIDOracle Workspace Manager 10.2.0.5.0 VALIDOracle Data Mining 10.2.0.5.0 VALIDOracle Database Catalog Views 10.2.0.5.0 VALIDOracle Database Packages and Types 10.2.0.5.0 VALIDJServer JAVA Virtua