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

1、北京科迈科技Linux(RHEL5)下.0升级到.0操作文档Tony-Liang2021/9/2Linux(RHEL5)下.0升级到.0操作文档 系统环境：RHEL5整理：Tony-Liang更新时间：2021-09-02备注：DBA专用目 录 TOC o 1-3 h z u HYPERLINK l _Toc365930758 目 录 PAGEREF _Toc365930758 h 2 HYPERLINK l _Toc365930759 1.文档更新记录 PAGEREF _Toc365930759 h 4 HYPERLINK l _Toc365930760 2. 文档说明 PAGEREF _T

2、oc365930760 h 5 HYPERLINK l _Toc365930761 3. 升级前准备工作 PAGEREF _Toc365930761 h 6 HYPERLINK l _Toc365930762 3.1.1. 临时增加shared pool和java pool 的大小 PAGEREF _Toc365930762 h 6 HYPERLINK l _Toc365930763 3.1.2. 冷备份oracle软件，备份数据库 PAGEREF _Toc365930763 h 7 HYPERLINK l _Toc365930764 3.1.3. 停掉监听，OEM,ISQLPLUS 等 PA

3、GEREF _Toc365930764 h 7 HYPERLINK l _Toc365930765 停监听 PAGEREF _Toc365930765 h 7 HYPERLINK l _Toc365930766 停EM PAGEREF _Toc365930766 h 7 HYPERLINK l _Toc365930767 停isqlplusctl PAGEREF _Toc365930767 h 7 HYPERLINK l _Toc365930768 3.1.4. 上传补丁包 PAGEREF _Toc365930768 h 8 HYPERLINK l _Toc365930769 4. 数据库软件

4、产品的升级 PAGEREF _Toc365930769 h 9 HYPERLINK l _Toc365930770 4.1.1.登录图形界面执行runInstaller，开启安装OUI界面 PAGEREF _Toc365930770 h 9 HYPERLINK l _Toc365930771 出现OUI图形界面如下： PAGEREF _Toc365930771 h 10 HYPERLINK l _Toc365930772 点击?Next,进入下一界面 PAGEREF _Toc365930772 h 11 HYPERLINK l _Toc365930773 点击?Next,进入下一界面 PAGE

5、REF _Toc365930773 h 13 HYPERLINK l _Toc365930774 点击?Next，进入下一界面 PAGEREF _Toc365930774 h 14 HYPERLINK l _Toc365930775 点击?Next，进入下一界面 PAGEREF _Toc365930775 h 15 HYPERLINK l _Toc365930776 点击?Next，进入下一界面 PAGEREF _Toc365930776 h 15 HYPERLINK l _Toc365930777 等待安装完成出现以下界面 PAGEREF _Toc365930777 h 17 HYPERLI

6、NK l _Toc365930778 执行脚本 PAGEREF _Toc365930778 h 17 HYPERLINK l _Toc365930779 点击OK按钮，弹出如下界面： PAGEREF _Toc365930779 h 18 HYPERLINK l _Toc365930780 5. 进行数据库的升级操作 PAGEREF _Toc365930780 h 19 HYPERLINK l _Toc365930781 5.1.1. 开始升级及更新数据字典 PAGEREF _Toc365930781 h 19 HYPERLINK l _Toc365930782 5.1.2. 重编译失效对象 P

7、AGEREF _Toc365930782 h 20 HYPERLINK l _Toc365930783 5.1.3. 重新创立数据字典 PAGEREF _Toc365930783 h 21 HYPERLINK l _Toc365930784 5.1.4 重新创立运行脚本 PAGEREF _Toc365930784 h 21 HYPERLINK l _Toc365930785 5.1.5. 观察数据库安装的组件、版本、状态 PAGEREF _Toc365930785 h 22 HYPERLINK l _Toc365930786 6. 升级总结 PAGEREF _Toc365930786 h 28

8、 HYPERLINK l _Toc365930787 6.1.1. 全新安装的情况 PAGEREF _Toc365930787 h 28 HYPERLINK l _Toc365930788 6.1.2. 非全新安装的情况 PAGEREF _Toc365930788 h 28 HYPERLINK l _Toc365930789 6.1.3. 升级失败，尝试回退机制 PAGEREF _Toc365930789 h 291.文档更新记录 时间修改人版本号修改说明2021-09-02Tony-Liang建立文档 2. 文档说明 本文档标准.0升级到.0版本的升级流程及操作，供DBA人员在进行数据库版本

9、升级的时参考使用。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.sql10g升级脚本运行正常。 oracleTRH10g backup$ sqlplus / as

10、 sysdbaSQL*Plus: Release .0 - Production on Wed Aug 28 12:16:33 2021Copyright (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 bytesData

11、base 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软件，备份数据库 orac

12、leTRH10g u01$ tar -cvf /u01/arch.tar.gz /u01/app/ 3.1.3. 停掉监听，OEM,ISQLPLUS 等 lsnrctl stopemctl stop dbconsoleisqlplusctl stoporacleTRH10g dbs$ lsnrctl stopLSNRCTL for Linux: Version .0 - Production on 01-SEP-2021 00:08:37Copyright (c) 1991, 2007, Oracle. All rights reserved.Connecting to (DESCRIPTIO

13、N=(ADDRESS=(PROTOCOL=TCP)(HOST=TRH10g)(PORT=1521)The command completed successfullyoracleTRH10g app$ emctl stop dbconsoleTZ set to PRCisqlplusctloracleTRH10g app$ isqlplusctl stopiSQL*Plus .0Copyright (c) 2003, 2005, Oracle. All rights reserved.getnameinfo failediSQL*Plus instance on port 5560 is no

14、t running . 3.1.4. 上传补丁包 将上传到虚拟机上，并解压oracleTRH10g backup$ unzip 4. 数据库软件产品的升级 .登录图形界面执行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, redh

15、at-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/OraInstall2021-08-26_10-34-03AM. Please wait .oracleTRH10g backup$ Oracle Universal Installer, Versi

16、on .0 Production Copyright (C) 1999, 2021, Oracle. All rights reserved. 出现OUI图形界面如下：点击?Next,进入下一界面点击?Next,进入下一界面对编译不通过的选项需要进行处理。内核参数不通过需要修改内核参数，需要修改的文件为 。缺少rmp找到相关的rpm，用命令rpm ivh rmp名称 安装点击?Next，进入下一界面点击?Next，进入下一界面?Next，进入下一界面等待安装完成出现以下界面执行脚本以root 身份登录，执行脚本/u01/oracle/product/10.2.0/db_1/root.sh，该脚

17、本会提示是否覆盖已存在的目录,默认为否或为是。oracleTRH10g Disk1$ su - rootPassword: Running Oracle10 root.sh script.The following environment variables are set as: ORACLE_OWNER= oracleEnter the full pathname of the local bin directory: /usr/local/bin: The file dbhome already exists in /usr/local/bin. Overwrite it? (y/n)

18、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

19、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.点击OK按钮，弹出如下界面：此时提示版本升级已经成功，点击 Exit按钮，退出即可。 至此，数据库软件产品升级已经完成。5. 进行数据库的升级操作startup upgrade;shutdown immediate;startup;5.1.1. 开

20、始升级及更新数据字典此脚本执行时间看机器性能及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 .0 - Production on Wed Aug 28 13:08:46 2021Co

21、pyright (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.Databas

22、e opened.SQL DOC#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

23、, 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 t

24、roubleshootingDOC 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;. 重编译失效对象oracleTRH10g $ sqlplus / as sysdba;SQL*Plus: Release .0 - Production on Wed Aug 28 14:22:1

25、6 2021Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to an idle instance.SQL .PL/SQL procedure successfully completed. 重新创立数据字典L Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. GrantsPUBLICaccess to the synon

26、yms.SQL DOC#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. 重新创立运行脚本 Runs all script

27、s required for or used with PL/SQL.SQL DOC#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.s

28、chema_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 OFF. 观察数据库安装的组件、版本、状态SQL shutdown immediate;Database closed.Database dismounted

29、.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 5

30、0;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 identifi

31、ed 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

32、TABLEPRODUCT_INFORMATION TABLEPRODUCT_DESCRIPTIONS TABLEPROMOTIONS TABLECOUNTRIES SYNONYMLOCATIONS SYNONYMDEPARTMENTS SYNONYMJOBS SYNONYMEMPLOYEES SYNONYMJOB_HISTORY SYNONYMPRODUCTS VIEWSYDNEY_INVENTORY VIEWBOMBAY_INVENTORY VIEWTORONTO_INVENTORY VIEWPRODUCT_PRICES VIEWACCOUNT_MANAGERS VIEWCUSTOMERS_

33、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_CUSTOM

34、ERS 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_HISTORY

35、6 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; STATUSOP

36、EN1 row selected.查看版本信息SQL select * from v$version; BANNEROracle Database 10g Enterprise Edition Release .0 - ProdPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for Linux: Version .0 - ProductionNLSRTL Version .0 - Production5 rows selected.SQL col comp_name format a30 SQL col version format a3

37、0 SQL col status format a10SQL SELECT comp_name, version, status FROM dba_registry;COMP_NAME VERSION STATUS Oracle Enterprise Manager .0 VALIDSpatial .0 VALIDOracle interMedia .0 VALIDOLAP Catalog .0 VALIDOracle XML Database .0 VALIDOracle Text .0 VALIDOracle Expression Filter .0 VALIDOracle Rule Manager .0 VALIDOracle Workspace Man