LinuxRHEL5下ORACLE10g102040升級102050操.doc

北京科邁科技有限公司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操作文檔 系統(tǒng)環(huán)境：RHEL5文檔版本：V1.0.1整理：Tony-Liang更新時(shí)間：2013-09-02備注：DBA專用目 錄目 錄21.文檔更新記錄42. 文檔說明53. 升級前準(zhǔn)備工作63.1.1. 臨時(shí)增加shared pool和java pool 的大小63.1.2. 冷備份oracle軟件，備份數(shù)據(jù)庫73.1.3. 停掉監(jiān)聽，OEM,ISQLPLUS 等73.1.3.1停監(jiān)聽73.1.3.2停EM73.1.3.3停isqlplusctl73.1.4. 上傳補(bǔ)丁包84. 數(shù)據(jù)庫軟件產(chǎn)品的升級94.1.1.登錄圖形界面執(zhí)行runInstaller，開啟安裝OUI界面94.1.2出現(xiàn)OUI圖形界面如下：104.1.3點(diǎn)擊Next,進(jìn)入下一界面114.1.4點(diǎn)擊Next,進(jìn)入下一界面134.1.5點(diǎn)擊Next，進(jìn)入下一界面144.1.6點(diǎn)擊Next，進(jìn)入下一界面154.1.7點(diǎn)擊Next，進(jìn)入下一界面154.1.8等待安裝完成出現(xiàn)以下界面174.1.9執(zhí)行腳本174.1.9點(diǎn)擊”O(jiān)K”按鈕，彈出如下界面：185. 進(jìn)行數(shù)據(jù)庫的升級操作195.1.1. 開始升級及更新數(shù)據(jù)字典195.1.2. 重編譯失效對象205.1.3. 重新創(chuàng)建數(shù)據(jù)字典CATALOG.SQL215.1.4 重新創(chuàng)建運(yùn)行腳本CATPROC.SQL215.1.5. 察看數(shù)據(jù)庫安裝的組件、版本、狀態(tài)226. 升級總結(jié)286.1.1. 全新安裝的情況286.1.2. 非全新安裝的情況286.1.3. 升級失敗，嘗試回退機(jī)制291.文檔更新記錄 時(shí)間修改人版本號修改說明2013-09-02Tony-Liang1.0.0建立文檔 2. 文檔說明 本文檔規(guī)范oracle10.2.0.4.0升級到10.2.0.5.0版本的升級流程及操作，供DBA人員在進(jìn)行數(shù)據(jù)庫版本升級的時(shí)參考使用。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. 臨時(shí)增加shared pool和java pool 的大小 確保參數(shù)shared_pool_size和java_pool_size 至少為150M大小，保證catupgrd.sql（10g）升級腳本運(yùn)行正常。 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軟件，備份數(shù)據(jù)庫 oracleTRH10g u01$ tar -cvf /u01/arch.tar.gz /u01/app/ 3.1.3. 停掉監(jiān)聽，OEM,ISQLPLUS 等 lsnrctl stopemctl stop dbconsoleisqlplusctl stop3.1.3.1停監(jiān)聽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. 上傳補(bǔ)丁包 將p8202632_10205_LINUX.zip上傳到虛擬機(jī)上，并解壓oracleTRH10g backup$ unzip p8202632_10205_LINUX.zip4. 數(shù)據(jù)庫軟件產(chǎn)品的升級 4.1.1.登錄圖形界面執(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, 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出現(xiàn)OUI圖形界面如下：4.1.3點(diǎn)擊Next,進(jìn)入下一界面4.1.4點(diǎn)擊Next,進(jìn)入下一界面對編譯不通過的選項(xiàng)需要進(jìn)行處理。內(nèi)核參數(shù)不通過需要修改內(nèi)核參數(shù)，需要修改的文件為/etc/sysctl.conf 。缺少rmp找到相關(guān)的rpm，用命令rpm ivh rmp名稱 安裝4.1.5點(diǎn)擊Next，進(jìn)入下一界面4.1.6點(diǎn)擊Next，進(jìn)入下一界面4.1.7點(diǎn)擊Next，進(jìn)入下一界面4.1.8等待安裝完成出現(xiàn)以下界面4.1.9執(zhí)行腳本以root 身份登錄，執(zhí)行腳本/u01/oracle/product/10.2.0/db_1/root.sh，該腳本會提示是否覆蓋已存在的目錄,默認(rèn)為否或?yàn)槭恰racleTRH10g 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點(diǎn)擊”O(jiān)K”按鈕，彈出如下界面：此時(shí)提示版本升級已經(jīng)成功，點(diǎn)擊 ”Exit”按鈕，退出即可。 至此，數(shù)據(jù)庫軟件產(chǎn)品升級已經(jīng)完成。5. 進(jìn)行數(shù)據(jù)庫的升級操作startup upgrade;?/rdbms/admin/catupgrd.sqlshutdown immediate;startup;?/rdbms/admin/utlrp.sql?/rdbms/admin/catalog.sql?/rdbms/admin/catproc.sql5.1.1. 開始升級及更新數(shù)據(jù)字典此腳本執(zhí)行時(shí)間看機(jī)器性能及JAVA_POOL_SIZE大小而定設(shè)置JAVA_POOL_SIZE 為150M時(shí)的升級所用時(shí)間Total Upgrade Time: 00:19:35 不設(shè)置JAVA_POOL_SIZE 為150M時(shí)的升級所用時(shí)間Total Upgrade Time: 01:10:03所以，一定要記得設(shè)置這個(gè)參數(shù)。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. 重新創(chuàng)建數(shù)據(jù)字典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 重新創(chuàng)建運(yùn)行腳本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. 察看數(shù)據(jù)庫安裝的組件、版本、狀態(tài)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有兩個(gè)無效對象，需要重新編譯，處理過程如下：-修改密碼并賦予相關(guān)權(quán)限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用的，編譯是提示權(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_HISTORY6 rows selected.-驗(yàn)證，提示不存在表和視圖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.重新編譯后，順利解決問題。查看實(shí)例狀態(tài)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