oracle升級操作流程

1、 數(shù)據(jù)庫版本升級操作手冊 目錄備份數(shù)據(jù)庫2升級Oracle軟件3修改ORACLE_HOME12升級實例13手工使用腳本升級實例13升級遇到問題的解決辦法14檢查升級后的版本信息和無效對象15附錄16本文用于指導(dǎo)oracle數(shù)據(jù)庫由版本.0升級到版本.0升級步驟如下：Ø 備份數(shù)據(jù)庫Ø 升級oracle 軟件Ø 修改ORACLE_HOMEØ 升級實例Ø 檢查升級后的版本信息和無效對象備份數(shù)據(jù)庫由于oracle升級存在一定風(fēng)險，所以在升級前一定要對已有數(shù)據(jù)進(jìn)行備份。1、 停掉所有使用數(shù)據(jù)庫的相關(guān)程序，以及數(shù)據(jù)庫。2、

2、對數(shù)據(jù)庫文件進(jìn)行冷備份。備份后的文件可轉(zhuǎn)存到異機(jī)或在本地存放。升級Oracle軟件1、獲取oracle升級軟件p10404530_112030_Linux-x86-64_1of7.zip 及p10404530_112030_Linux-x86-64_2of7.zip。2、將其上傳到數(shù)據(jù)庫服務(wù)器的/tmp/目錄下并解壓。unzip p10404530_112030_Linux-x86-64_1of7.zipunzip p10404530_112030_Linux-x86-64_1of7.zip3、 解壓后，會在當(dāng)前目錄下生成一個database目錄。4、 切換到oracle用戶，執(zhí)行oracle

3、升級腳本。5、 su - oracle/tmp/database/runInstaller6、不需要進(jìn)行任何更改，直接點擊下一步。7、選擇跳過軟件更新，點擊下一步。8、選擇升級現(xiàn)有數(shù)據(jù)庫，點擊下一步。9、語言選擇簡體中文和英語，點擊下一步。10、選擇企業(yè)版，并點擊下一步。11、Oracle基目錄無需更改，軟件位置需要更改為新的位置，這樣可以減少宕機(jī)時間，是oracle所推薦的方法。如下圖我們將軟件安裝位置選擇為/home/oracle/oracle11g3，修改后，點擊下一步。12、數(shù)據(jù)庫管理員選擇dba，數(shù)據(jù)庫操作者選擇oinstall，點擊下一步。13、檢查先決條件，通過后點擊“安裝”按鈕

4、14、用root用戶執(zhí)行root.sh腳本，執(zhí)行后，點擊“確定”按鈕。15、配置監(jiān)控，因為我們之前已經(jīng)配置過，直接選擇取消即可。16、由于選擇跳過配置監(jiān)聽，所以此處會報錯，不過沒有關(guān)系。點擊下一步。17、此處會提示一些配置失敗，是否繼續(xù)。18、選擇“是”， 結(jié)束Oracle database 軟件的安裝。修改ORACLE_HOME注：此文中，數(shù)據(jù)庫升級前軟件的安裝目錄為/home/oracle/oracle11g2，升級后的安裝目錄為/home/oracle/oracle11g3，若升級前的安裝目錄與文中提到的不符，請按實際內(nèi)容進(jìn)行修改。如下所有內(nèi)容均使用oracle用戶來進(jìn)行修改。1、 拷貝

5、dbs目錄內(nèi)容oracledave dbs$ cd /home/oracle/oracle11g2/dbs oracledave dbs$ cp * /home/oracle/oracle11g3/dbs2、拷貝admin目錄中的內(nèi)容oracledave admin$ cd / home/oracle/oracle11g2/network/admin oracledave admin$ cp -R * /home/oracle/oracle11g3/network/admin3、修改.bashrc文件中的ORACLE_HOME。oracledave admin$ vi /home/oracle

6、/.bashrcexport ORACLE_BASE=/home/oracleexport ORACLE_HOME=$ORACLE_BASE/oracle11g3/export ORACLE_SID=orclexport PATH=$ORACLE_HOME/bin:$PATH修改后保存退出。4、 修改/etc/oratab中的ORACLE_HOMEoracledave db_1$ vi /etc/orataborcl:/home/oracle/oracle11g3:N升級實例 升級Instance 有兩種方法，第一種是使用dbua工具，第二種是手工執(zhí)行腳本。 此文中，我們使用腳本來升級實例。升

7、級前驗證由于之前，我們修改了oracle用戶的.bashrc，因此一定要重新連接oracle用戶以獲得最新的ORACLE_HOME。查看ORACLE_HOME的方法：su - oracleexport 找到ORACLE_HOME那一條，查看是否為最新。確認(rèn)已獲取到最新的ORACLE_HOME后則可執(zhí)行后續(xù)操作；否則應(yīng)退出當(dāng)前用戶，重新連接。手工使用腳本升級實例1、 切換oracle用戶，以dba角色進(jìn)入數(shù)據(jù)庫。su - oraclesqlplus / as sysdba2、 以升級方式啟動數(shù)據(jù)庫。SQL> STARTUP UPGRADE3、設(shè)置log輸出。:SQL> SPOOL u

8、pgrade.log4、打印預(yù)升級信息。SQL> $ORACLE_HOME/rdbms/admin/utlu112i.sql5、執(zhí)行升級腳本。SQL> $ORACLE_HOME/rdbms/admin/catupgrd.sql6、由于上一步會關(guān)閉數(shù)據(jù)庫。所以此處要重啟數(shù)據(jù)庫。SQL> STARTUP7、運行狀態(tài)工具顯示升級結(jié)果。該腳本會顯示升級各個組件所花的時間。SQL> $ORACLE_HOME/rdbms/admin/utlu112s.sql8、運行升級后操作。SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql9、重編譯并重驗

9、證任何剩余的應(yīng)用程序?qū)ο?。SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql10、檢查包和類的正確性。SQL> SELECT count(*) FROM dba_invalid_objects;COUNT(*)- 0SQL> SELECT distinct object_name FROM dba_invalid_objects;no rows selected11、Exit SQL*Plus.至此，通過腳本升級操作結(jié)束。升級遇到問題的解決辦法如果在升級中遇到問題，可以重新進(jìn)行升級。具體的步驟如下：1、停止數(shù)據(jù)庫。SQL> SHUTDOWN

10、IMMEDIATE2、以升級模式啟動數(shù)據(jù)庫。SQL> STARTUP UPGRADE3、設(shè)置log輸出。SQL> SPOOL upgrade.log4、重新執(zhí)行catupgrd.sqlSQL>$ORACLE_HOME/rdbms/admin/catupgrd.sql注意：第一次執(zhí)行此腳本不會報錯，再次執(zhí)行時，會報ORA-00001的錯誤，可以直接忽略。5.執(zhí)行 utlu112s.sql:SQL>$ORACLE_HOME/rdbms/admin/utlu112s.sql檢查升級后的版本信息和無效對象SQL> SELECT COMP_NAME, VERSION,STA

11、TUS FROM SYS.DBA_REGISTRY;COMP_NAME VERSION STATUS- -OWB .0 VALIDOracle Application Express 0.10 VALIDOracle Enterprise Manager .0 VALIDOLAP Catalog .0 VALIDSpatial .0 VALIDOracle Multimedia .0 VALIDOracle XML Database .0 VALIDOracle Text 11.2.0

12、.3.0 VALIDOracle Expression Filter .0 VALIDOracle Rules Manager .0 VALIDOracle Workspace Manager .0 VALIDCOMP_NAME VERSION STATUS- -Oracle Database Catalog Views .0 VALIDOracle Database Packages and Types .0 VALIDJServer JAVA Virtual Machine .0 VALIDOr

13、acle XDK .0 VALIDOracle Database Java Packages .0 VALIDOLAP Analytic Workspace .0 VALIDOracle OLAP API .0 VALID18 rows selected.附錄各腳本執(zhí)行結(jié)果，供參考：utlu112i.sqlSQL> $ORACLE_HOME/rdbms/admin/utlu112i.sqlOracle Database 11.2 Pre-Upgrade Information Tool 06-20-2013 16:01:00

14、Script Version: .0 Build: 001.*Database:*-> name: SOC-> version: .0-> compatible: .0-> blocksize: 8192-> platform: Linux x86 64-bit-> timezone file: V11.*Tablespaces: make adjustments in the current environment*-> SYSTEM tablespace is adequate for the upg

15、rade. minimum required size: 695 MB-> SYSAUX tablespace is adequate for the upgrade. minimum required size: 483 MB-> UNDOTBS1 tablespace is adequate for the upgrade. minimum required size: 400 MB-> TEMP tablespace is adequate for the upgrade. minimum required size: 60 MB.*Flashback: OFF*Upd

16、ate Parameters: Update Oracle Database 11.2 init.ora or spfileNote: Pre-upgrade tool was run on a lower version 64-bit database.*-> If Target Oracle is 32-Bit, refer here for Update Parameters:- No update parameter changes are required.-> If Target Oracle is 64-Bit, refer here for Update Param

17、eters:- No update parameter changes are required.*Renamed Parameters: Update Oracle Database 11.2 init.ora or spfile*- No renamed parameters found. No changes are required.*Obsolete/Deprecated Parameters: Update Oracle Database 11.2 init.ora or spfile*- No obsolete parameters found. No changes are r

18、equired.*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 Workspace Manager upgrade VALI

19、D-> OLAP Analytic Workspace upgrade VALID-> OLAP Catalog upgrade VALID-> EM Repository upgrade VALID-> Oracle Text upgrade VALID-> Oracle XML Database upgrade VALID-> Oracle Java Packages upgrade VALID-> Oracle interMedia upgrade VALID-> Spatial upgrade VALID-> Expression

20、Filter upgrade VALID-> Rule Manager upgrade VALID-> Oracle Application Express upgrade VALID. APEX will only be upgraded if the version of APEX in. the target Oracle home is higher than the current one.-> Oracle OLAP API upgrade VALID.*Miscellaneous Warnings*WARNING: -> Database is using

21、 a timezone file older than version 14. After the release migration, it is recommended that DBMS_DST package. be used to upgrade the .0 database timezone version. to the latest version which comes with the new release.WARNING: -> Database contains INVALID objects prior to upgrade. The lis

22、t of invalid SYS/SYSTEM objects was written to. registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to. registry$nonsys_inv_objs. Use utluiobj.sql after the upgrade to identify any new invalid. objects due to the upgrade. USER PUBLIC has 27 INVALID objects.WARNING: -> Your recyc

23、le bin contains 12 object(s). It is REQUIRED that the recycle bin is empty prior to upgrading. your database. The command: PURGE DBA_RECYCLEBIN. must be executed immediately prior to executing your upgrade.WARNING: -> Database contains schemas with objects dependent on DBMS_LDAP package. Refer to

24、 the 11g Upgrade Guide for instructions to configure Network ACLs. USER APEX_030200 has dependent objects.*Recommendations*Oracle recommends gathering dictionary statistics prior toupgrading the database.To gather dictionary statistics execute the following commandwhile connected as SYSDBA: EXECUTE

25、dbms_stats.gather_dictionary_stats;*Oracle recommends reviewing any defined events prior to upgrading.To view existing non-default events execute the following commandswhile connected AS SYSDBA: Events: SELECT (translate(value,chr(13)|chr(10),' ') FROM sys.v$parameter2 WHERE UPPER(name) =

26、9;EVENT' AND isdefault='FALSE' Trace Events: SELECT (translate(value,chr(13)|chr(10),' ') from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'Changes will need to be made in the init.ora or spfile.*Utlu112s.sqlSQL> $ORACLE_HOME/rdbms/

27、admin/utlu112s.sql.Oracle Database 11.2 Post-Upgrade Status Tool 06-20-2013 16:54:31.Component Current Version Elapsed TimeName Status Number HH:MM:SS.Oracle Server. VALID .0 00:07:41JServer JAVA Virtual Machine. VALID .0 00:02:12Oracle Workspace Manager. VALID .0 00:00:22OLA

28、P Analytic Workspace. VALID .0 00:00:51OLAP Catalog. VALID .0 00:00:40Oracle OLAP API. VALID .0 00:00:16Oracle Enterprise Manager. VALID .0 00:02:53Oracle XDK. VALID .0 00:00:22Oracle Text. VALID .0 00:00:21Oracle XML Database. VALID .0 00:01:2

29、7Oracle Database Java Packages. VALID .0 00:00:09Oracle Multimedia. VALID .0 00:01:57Spatial. VALID .0 00:02:19Oracle Expression Filter. VALID .0 00:00:06Oracle Rules Manager. VALID .0 00:00:05Oracle Application Express. VALID 0.10Gathering Statistics.

30、00:01:59Total Upgrade Time: 00:23:47PL/SQL procedure successfully completed.catuppst.sqlSQL> $ORACLE_HOME/rdbms/admin/catuppst.sqlTIMESTAMP-COMP_TIMESTAMP POSTUP_BGN 2013-06-20 16:57:19PL/SQL procedure successfully completed.This script will migrate the Baseline data on a pre-11g databaseto the 1

31、1g database. . Completed Moving the Baseline Data . . If there are no Move BL Data messages . above, then there are no renamed . baseline tables in the system. . . . Completed the Dropping of the . Renamed Baseline Tables . . If there are no Drop Table messages . above, then there are no renamed . b

32、aseline tables in the system. . .PL/SQL procedure successfully completed.0 rows created.Commit complete.Table created.2 rows created.1 row updated.2 rows updated.0 rows updated.Table dropped.Commit complete.0 rows updated.Commit complete.0 rows updated.Commit complete.0 rows updated.Commit complete.

33、0 rows created.Commit complete.0 rows created.Commit complete.PL/SQL procedure successfully completed.TIMESTAMP-COMP_TIMESTAMP POSTUP_END 2013-06-20 16:57:20PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.Generating apply and rollback scripts.Check the following file for errors:/home/oracle/cfgtoollogs/catbundle/catbundle_PSU_SOC_GENERATE_2013Jun20_16_57_21.logApply script: /home/oracle/oracle11g3/rdbms/admin/catbundle_PSU_SOC_APPLY.sqlRollback script: /home/oracle/oracle11g3/rdbms/admin/catbundle_PSU_SOC_ROLLBACK.sqlPL