1、北京科邁科技Linux(RHEL5)下.0升級到.0操作文檔Tony-Liang2021/9/2Linux(RHEL5)下.0升級到.0操作文檔 系統(tǒng)環(huán)境：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. 升級前準(zhǔn)備工作 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軟件，備份數(shù)據(jù)庫 PAGEREF _Toc365930763 h 7 HYPERLINK l _Toc365930764 3.1.3. 停掉監(jiān)聽，OEM,ISQLPLUS 等 PA

3、GEREF _Toc365930764 h 7 HYPERLINK l _Toc365930765 停監(jiān)聽 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. 上傳補(bǔ)丁包 PAGEREF _Toc365930768 h 8 HYPERLINK l _Toc365930769 4. 數(shù)據(jù)庫軟件

4、產(chǎn)品的升級 PAGEREF _Toc365930769 h 9 HYPERLINK l _Toc365930770 4.1.1.登錄圖形界面執(zhí)行runInstaller，開啟安裝OUI界面 PAGEREF _Toc365930770 h 9 HYPERLINK l _Toc365930771 出現(xiàn)OUI圖形界面如下： PAGEREF _Toc365930771 h 10 HYPERLINK l _Toc365930772 點擊?Next,進(jìn)入下一界面 PAGEREF _Toc365930772 h 11 HYPERLINK l _Toc365930773 點擊?Next,進(jìn)入下一界面 PAGE

5、REF _Toc365930773 h 13 HYPERLINK l _Toc365930774 點擊?Next，進(jìn)入下一界面 PAGEREF _Toc365930774 h 14 HYPERLINK l _Toc365930775 點擊?Next，進(jìn)入下一界面 PAGEREF _Toc365930775 h 15 HYPERLINK l _Toc365930776 點擊?Next，進(jìn)入下一界面 PAGEREF _Toc365930776 h 15 HYPERLINK l _Toc365930777 等待安裝完成出現(xiàn)以下界面 PAGEREF _Toc365930777 h 17 HYPERLI

6、NK l _Toc365930778 執(zhí)行腳本 PAGEREF _Toc365930778 h 17 HYPERLINK l _Toc365930779 點擊OK按鈕，彈出如下界面： PAGEREF _Toc365930779 h 18 HYPERLINK l _Toc365930780 5. 進(jìn)行數(shù)據(jù)庫的升級操作 PAGEREF _Toc365930780 h 19 HYPERLINK l _Toc365930781 5.1.1. 開始升級及更新數(shù)據(jù)字典 PAGEREF _Toc365930781 h 19 HYPERLINK l _Toc365930782 5.1.2. 重編譯失效對象 P

7、AGEREF _Toc365930782 h 20 HYPERLINK l _Toc365930783 5.1.3. 重新創(chuàng)立數(shù)據(jù)字典 PAGEREF _Toc365930783 h 21 HYPERLINK l _Toc365930784 5.1.4 重新創(chuàng)立運(yùn)行腳本 PAGEREF _Toc365930784 h 21 HYPERLINK l _Toc365930785 5.1.5. 觀察數(shù)據(jù)庫安裝的組件、版本、狀態(tài) PAGEREF _Toc365930785 h 22 HYPERLINK l _Toc365930786 6. 升級總結(jié) 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. 升級失敗，嘗試回退機(jī)制 PAGEREF _Toc365930789 h 291.文檔更新記錄 時間修改人版本號修改說明2021-09-02Tony-Liang建立文檔 2. 文檔說明 本文檔標(biāo)準(zhǔn).0升級到.0版本的升級流程及操作，供DBA人員在進(jìn)行數(shù)據(jù)庫版本

9、升級的時參考使用。3. 升級前準(zhǔn)備工作 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 的大小 確保參數(shù)shared_pool_size和java_pool_size 至少為150M大小，保證catupgrd.sql10g升級腳本運(yùn)行正常。 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軟件，備份數(shù)據(jù)庫 orac

12、leTRH10g u01$ tar -cvf /u01/arch.tar.gz /u01/app/ 3.1.3. 停掉監(jiān)聽，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. 上傳補(bǔ)丁包 將上傳到虛擬機(jī)上，并解壓oracleTRH10g backup$ unzip 4. 數(shù)據(jù)庫軟件產(chǎn)品的升級 .登錄圖形界面執(zhí)行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. 出現(xiàn)OUI圖形界面如下：點擊?Next,進(jìn)入下一界面點擊?Next,進(jìn)入下一界面對編譯不通過的選項需要進(jìn)行處理。內(nèi)核參數(shù)不通過需要修改內(nèi)核參數(shù)，需要修改的文件為 。缺少rmp找到相關(guān)的rpm，用命令rpm ivh rmp名稱 安裝點擊?Next，進(jìn)入下一界面點擊?Next，進(jìn)入下一界面?Next，進(jìn)入下一界面等待安裝完成出現(xiàn)以下界面執(zhí)行腳本以root 身份登錄，執(zhí)行腳本/u01/oracle/product/10.2.0/db_1/root.sh，該腳

17、本會提示是否覆蓋已存在的目錄,默認(rèn)為否或為是。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按鈕，彈出如下界面：此時提示版本升級已經(jīng)成功，點擊 Exit按鈕，退出即可。 至此，數(shù)據(jù)庫軟件產(chǎn)品升級已經(jīng)完成。5. 進(jìn)行數(shù)據(jù)庫的升級操作startup upgrade;shutdown immediate;startup;5.1.1. 開

20、始升級及更新數(shù)據(jù)字典此腳本執(zhí)行時間看機(jī)器性能及JAVA_POOL_SIZE大小而定設(shè)置JAVA_POOL_SIZE 為150M時的升級所用時間Total Upgrade Time: 00:19:35 不設(shè)置JAVA_POOL_SIZE 為150M時的升級所用時間Total Upgrade Time: 01:10:03所以，一定要記得設(shè)置這個參數(shù)。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. 重新創(chuàng)立數(shù)據(jù)字典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. 重新創(chuàng)立運(yùn)行腳本 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. 觀察數(shù)據(jù)庫安裝的組件、版本、狀態(tài)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有兩個無效對象，需要重新編譯，處理過程如下：-修改密碼并賦予相關(guān)權(quán)限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用的，編譯是提示權(quán)限缺乏，應(yīng)該是沒有select權(quán)限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權(quán)限SQL conn / as sysdba;Connected.SQL grant select on HR.COUNTRIES to OE;Grant succeeded.重新編譯后，順利解決問題。查看實例狀態(tài)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