探索Oracle之数据库升级二 11.2.0.3升级到11.2.0.4完整步骤--测试成功版本

原文地址：http://blog.itpub.net/20674423/viewspace-1352257/

这篇是我测试成功的版本，感谢原文作者，我只是转发，上一篇转发的是不成功的，或者说步骤不明确，给大家带来的麻烦，深感抱歉。

探索Oracle之数据库升级二

 11.2.0.3 升级到11.2.0.4 完整步骤

说明：

        这篇文章主要是记录下单实例环境下Oracle 11.2.0.1 升级到11.2.0.3的过程，当然RAC的升级是会有所不同。但是他们每个版本之间升级步骤都是差不多的，先升级Database Software，再升级Oracle Instance。

Oracle 11.2.0.4 的Patchset No: <span font-size:14pt;"="" style="word-wrap: break-word; color: rgb(51, 127, 229);">19852360下载需要有Oracle Support才可以。

 Patchset 包含有7个文件，关于这七个文件的作用，详见如下链接：  

我们升级Database，只需要其中的第一个和第二文件即可。将2个文件解压缩后就可以执行升级操作了。  

升级前准备：

1、   
1、查看数据库和操作系统相关信息:

1. [root@db01 ~]# uname -a
   
2. 
   
3. Linux db01 2.6.18-308.el5 #1 SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
   
4. [root@db01 ~]# lsb_release -a
   
5. LSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
   
6. Distributor ID: RedHatEnterpriseServer
   
7. Description: Red Hat Enterprise Linux Server release 5.8 (Tikanga)
   
8. Release: 5.8
   
9. Codename: Tikanga
   
10. 
    
11. [root@db01 ~]# su - oracle -c \"sqlplus / as sysdba\";
    
12. 
    
13. SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 3 21:32:02 2014
    
14. Copyright (c) 1982, 2011, Oracle. All rights reserved.
    
15. 
    
16. Connected to:
    
17. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
18. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
19. 
    
20. SQL> select name from v$database;
    
21. 
    
22. NAME
    
23. ---------
    
24. WOO
    
25. 
    
26. SQL>

 
2、备份数据库   

1. [oracle@db01 ~]$ rman target / 
   
2.  
   
3. Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014 
   
4. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 
   
5. connected to target database: WOO (DBID=4199461782) 
   
6.  
   
7. RMAN> backup database plus archivelog delete input format \'/DBBackup/Phycal/full_%U.bak\'; 
   
8.  
   
9. Starting backup at 03-OCT-14 
   
10. current log archived 
    
11. using target database control file instead of recovery catalog 
    
12. allocated channel: ORA_DISK_1 
    
13. channel ORA_DISK_1: SID=149 device type=DISK 
    
14. channel ORA_DISK_1: starting archived log backup set 
    
15. channel ORA_DISK_1: specifying archived log(s) in backup set 
    
16. input archived log thread=1 sequence=15 RECID=1 STAMP=860017183 
    
17. input archived log thread=1 sequence=16 RECID=2 STAMP=860017184 
    
18. input archived log thread=1 sequence=17 RECID=3 STAMP=860017186 
    
19. input archived log thread=1 sequence=18 RECID=4 STAMP=860017186 
    
20. input archived log thread=1 sequence=19 RECID=5 STAMP=860017188 
    
21. input archived log thread=1 sequence=20 RECID=6 STAMP=860017387 
    
22. channel ORA_DISK_1: starting piece 1 at 03-OCT-14 
    
23. channel ORA_DISK_1: finished piece 1 at 03-OCT-14 
    
24. piece handle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG20141003T214307 comment=NONE 
    
25. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 
    
26. channel ORA_DISK_1: deleting archived log(s) 
    
27. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_15_b2x9rz0z_.arc RECID=1 STAMP=860017183 
    
28. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_16_b2x9s05l_.arc RECID=2 STAMP=860017184 
    
29. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_17_b2x9s2nx_.arc RECID=3 STAMP=860017186 
    
30. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_18_b2x9s2od_.arc RECID=4 STAMP=860017186 
    
31. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_19_b2x9s4dr_.arc RECID=5 STAMP=860017188 
    
32. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_20_b2x9zbz2_.arc RECID=6 STAMP=860017387 
    
33. Finished backup at 03-OCT-14 
    
34.  
    
35. Starting backup at 03-OCT-14 
    
36. using channel ORA_DISK_1 
    
37. channel ORA_DISK_1: starting full datafile backup set 
    
38. channel ORA_DISK_1: specifying datafile(s) in backup set 
    
39. input datafile file number=00001 name=/DBData/woo/system01.dbf 
    
40. input datafile file number=00002 name=/DBData/woo/sysaux01.dbf 
    
41. input datafile file number=00005 name=/DBData/woo/example01.dbf 
    
42. input datafile file number=00003 name=/DBData/woo/undotbs01.dbf 
    
43. input datafile file number=00004 name=/DBData/woo/users01.dbf 
    
44. channel ORA_DISK_1: starting piece 1 at 03-OCT-14 
    
45. channel ORA_DISK_1: finished piece 1 at 03-OCT-14 
    
46. piece handle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_nnndf_TAG20141003T214309_b2x9zfm5_.bkp tag=TAG20141003T214309 comment=NONE
    
47. channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45 
    
48. channel ORA_DISK_1: starting full datafile backup set 
    
49. channel ORA_DISK_1: specifying datafile(s) in backup set 
    
50. including current control file in backup set 
    
51. including current SPFILE in backup set 
    
52. channel ORA_DISK_1: starting piece 1 at 03-OCT-14 
    
53. channel ORA_DISK_1: finished piece 1 at 03-OCT-14 
    
54. piece handle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_ncsnf_TAG20141003T214309_b2xb2qlm_.bkp tag=TAG20141003T214309 comment=NONE
    
55. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 
    
56. Finished backup at 03-OCT-14 
    
57.  
    
58. Starting backup at 03-OCT-14 
    
59. current log archived 
    
60. using channel ORA_DISK_1 
    
61. channel ORA_DISK_1: starting archived log backup set 
    
62. channel ORA_DISK_1: specifying archived log(s) in backup set 
    
63. input archived log thread=1 sequence=21 RECID=7 STAMP=860017496 
    
64. channel ORA_DISK_1: starting piece 1 at 03-OCT-14 
    
65. channel ORA_DISK_1: finished piece 1 at 03-OCT-14 
    
66. piece handle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG20141003T214456 comment=NONE 
    
67. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 
    
68. channel ORA_DISK_1: deleting archived log(s) 
    
69. archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_21_b2xb2rsf_.arc RECID=7 STAMP=860017496 
    
70. Finished backup at 03-OCT-14 
    
71.  
    
72. RMAN>

 
3、停止数据库  

1. ###停止数据库 
   
2. SQL> shutdown immediate 
   
3. Database closed. 
   
4. Database dismounted. 
   
5. ORACLE instance shut down. 
   
6.  
   
7. ###停止监听 
   
8. SQL> host lsnrctl stop 
   
9.  
   
10. LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2014 01:39:26 
    
11. Copyright (c) 1991, 2011, Oracle. All rights reserved. 
    
12. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) 
    
13. The command completed successfully 
    
14.  
    
15. ###停止EM 
    
16. SQL> host emctl stop dbconsole 
    
17. Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0 
    
18. Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved. 
    
19. https://db01:1158/em/console/aboutApplication 
    
20. Stopping Oracle Enterprise Manager 11g Database Control ... 
    
21.  
    
22. ###查看oracle进程，检查是否已经停止完毕 
    
23. [root@db01 ~]# ps -ef|grep ora 
    
24. root 4971 4944 0 01:46 pts/0 00:00:00 grep ora

3、   4、备份老的ORACLE_HOME和oraInventory

1. [root@db01 ~]#tar –cvf product.zip /DBSoft/product/ 
   
2. [root@db01 ~]#tar –cvf oraInventory.zip /DBSoft/product/oraInventory/

 

4、   5、上传并解压缩Oracle Database 11.2.0.4安装介质

1. [oracle@db01 ~]$ ll 
   
2.  
   
3.  total 2489644 
   
4.  drwxr-xr-x 2 oracle oinstall 4096 Oct 3 04:06 Desktop 
   
5.  -rw-r--r-- 1 oracle oinstall 1395582860 Oct 3 06:55 p13390677_112040_Linux-x86-64_1of7.zip 
   
6.  -rw-r--r-- 1 oracle oinstall 1151304589 Oct 3 06:54 p13390677_112040_Linux-x86-64_2of7.zip 
   
7.  
   
8. [oracle@db01 ~]$ unzip p13390677_112040_Linux-x86-64_1of7.zip 
   
9. [oracle@db01 ~]$ unzip p13390677_112040_Linux-x86-64_2of7.zip

6、开始安装
  6.1 将文件解压缩后进入安装目录执行./runInstall

  
  6.2 取消Oracle支持选项，点击Next

  
  6.3 选择最后一个选项"skip software update" 点击Next


   6.4 选择最后一个选项"Upgrade an existing database" 后执行Next


   6.5 选择所有语言，后点击Next


  6.6 选择要升级的数据库版本，后点击Next


   6.7 选择新版本的数据库软件安装目录，后点击Next


   6.8 选择数据库所属用户组，后点击Next


  6.9 察看数据库配置信息后，点击Install开始进行新版本软件安装


   6.10 安装进度，这个过程将会持续15分钟左右 


  6.11 弹出对话框要求执行/DBSoft/Product/11.2.4/db_1/root.sh 脚本

 

7、执行root脚本

1. [root@db01 ~]# /DBSoft/Product/11.2.4/db_1/root.sh  
    
   Performing root user operation for Oracle 11g  
   The following environment variables are set as:  
       ORACLE_OWNER= oracle 
       ORACLE_HOME=  /DBSoft/Product/11.2.4/db_1 
    
   Enter the full pathname of the local bin directory: [/usr/local/bin]:  
   The contents of "dbhome" have not changed. No need to overwrite. 
   The contents of "oraenv" have not changed. No need to overwrite. 
   The contents of "coraenv" have not changed. No need to overwrite. 
     
   Entries will be added to the /etc/oratab file as needed by 
   Database Configuration Assistant when a database is created 
   Finished running generic part of root script. 
   Now product-specific root actions will be performed. 
   Finished product-specific root actions. 
   [root@db01 ~]#

    6.12 执行完脚本之后继续运行，提示配置监听，选择Cancel，稍后复制即可


  6.13 提示监听配置失败，点击ok即可


  6.14 有报错不用管，我们直接Next即可 


   6.15 至此软件安装完成，点击Close关闭安装界面


至此软件安装完成，但是并不代表数据库就已经升级完成了。

8、至此11.2.0.4的软件就已经装完了，修改Oracle环境变量  

1. [root@db01 ~]# su - oracle 
   
2.  
   
3. [oracle@db01 ~]$ vi .bash_profile ---修改如下行,将11.2.3改成11.2.4即可 
   
4.  
   
5. export ORACLE_HOME=$ORACLE_BASE/Product/11.2.4/db_1 
   
6.  
   
7. [oracle@db01 ~]$ vi /etc/oratab ------修改如下行11.2.3为11.2.4
   
8.  woo:/DBSoft/Product/11.2.4/db_1:N 
   
9.  
   
10. [oracle@db01 admin]$ cp /DBSoft/Product/11.2.3/db_1/dbs/* /DBSoft/Product/11.2.4/db_1/dbs/

9、拷贝监听配置文件

1. [oracle@db01 admin]$ source ~/.bash_profile 
   
2. [oracle@db01 admin]$ pwd 
   
3. /DBSoft/Product/11.2.3/db_1/network/admin 
   
4.  
   
5. [oracle@db01 admin]$ cd $ORACLE_HOME/network/admin 
   
6. [oracle@db01 admin]$ ls 
   
7. samples shrept.lst 
   
8.  
   
9. [oracle@db01 admin]$ cp /DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin 
   
10. cp: omitting directory `/DBSoft/Product/11.2.3/db_1/network/admin/samples\

10、执行预升级脚本检查   

1. [oracle@db01 dbs]$ sqlplus /as sysdba
    
   SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 02:26:01 2014
   Copyright (c) 1982, 2013,Oracle.  All rights reserved.
   Connected to an idleinstance.
    
   SQL> startup upgrade;

   ORACLE instance started.
   Total System Global Area2037673984 bytes
   Fixed Size                  2254704 bytes
   Variable Size            1811941520 bytes
   Database Buffers          218103808 bytes
   Redo Buffers                5373952 bytes
   Database mounted.
   Database opened.

   SQL>@?/rdbms/admin/utlu112i.sql    ---执行升级前检查

   Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 02:27:38
   Script Version: 11.2.0.4.0Build: 001
   .
   **********************************************************************
   Database:
   **********************************************************************

   --> name:          WOO
   --> version:       11.2.0.3.0
   --> compatible:    11.2.0.0.0
   --> blocksize:     8192
   --> platform:      Linux x86 64-bit
   --> timezone file: V14
   .
   **********************************************************************
   Tablespaces: [makeadjustments in the current environment]
   **********************************************************************

   --> SYSTEM tablespace isadequate for the upgrade.
   .... minimum required size:917 MB
   --> SYSAUX tablespace isadequate for the upgrade.
   .... minimum required size:646 MB
   --> UNDOTBS1 tablespaceis adequate for the upgrade.
   .... minimum required size:400 MB
   --> TEMP tablespace isadequate for the upgrade.
   .... minimum required size:60 MB
   .
   **********************************************************************
   Flashback: OFF
   **********************************************************************

   **********************************************************************
   Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
   Note: Pre-upgrade tool wasrun on a lower version 64-bit database.
   **********************************************************************

   --> If Target Oracle is32-Bit, refer here for Update Parameters:
   WARNING: -->"shared_pool_size" needs to be increased to at least 236 MB
   .
   --> If Target Oracle is64-Bit, refer here for Update Parameters:
   WARNING: -->"shared_pool_size" needs to be increased to at least 472 MB
   .
   **********************************************************************
   Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
   **********************************************************************
   -- No renamed parametersfound. No changes are required.
   .
   **********************************************************************
   Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]
   **********************************************************************
   -- No obsolete parametersfound. No changes are required

   .
   **********************************************************************
   Components: [The followingdatabase components will be upgraded or installed]
   **********************************************************************

   --> Oracle CatalogViews         [upgrade]  VALID
   --> Oracle Packages andTypes    [upgrade]  VALID
   --> JServer JAVA VirtualMachine [upgrade]  VALID
   --> Oracle XDK forJava          [upgrade]  VALID
   --> Oracle WorkspaceManager     [upgrade]  VALID
   --> OLAP Analytic Workspace      [upgrade] VALID
   --> OLAP Catalog                 [upgrade]  VALID
   --> EM Repository                [upgrade]  VALID
   --> Oracle Text                  [upgrade]  VALID
   --> Oracle XMLDatabase          [upgrade]  VALID
   --> Oracle Java Packages         [upgrade]  VALID
   --> OracleinterMedia            [upgrade]  VALID
   --> Spatial                      [upgrade]  VALID
   --> ExpressionFilter            [upgrade]  VALID
   --> Rule Manager                 [upgrade]  VALID
   --> Oracle ApplicationExpress   [upgrade]  VALID
   ... APEX will only beupgraded if the version of APEX in
   ... the target Oracle homeis higher than the current one.
   --> Oracle OLAP API              [upgrade]  VALID
   .
   **********************************************************************
   Miscellaneous Warnings
   **********************************************************************

   WARNING: --> Your recyclebin is turned on and currently contains no objects.
   .... Because it is REQUIREDthat the recycle bin be empty prior to upgrading
   .... and your recycle bin isturned on, you may need to execute the command:
           PURGE DBA_RECYCLEBIN
   .... prior to executing yourupgrade to confirm the recycle bin is empty.
   WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package.
   .... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs.
   .... USER APEX_030200 hasdependent objects.
   .

   **********************************************************************
   Recommendations
   **********************************************************************

   Oracle recommends gatheringdictionary statistics prior to
   upgrading the database.
   To gather dictionarystatistics execute the following command
   while connected as SYSDBA:

       EXECUTE dbms_stats.gather_dictionary_stats;

   **********************************************************************
   Oracle recommends removingall hidden parameters prior to upgrading.
   To view existing hiddenparameters execute the following command
   while connected AS SYSDBA:
    
       SELECT name,description fromSYS.V$PARAMETER WHERE name
           LIKE '\_%' ESCAPE '\'

   Changes will need to be madein the init.ora or spfile.
   **********************************************************************

   Oracle recommends reviewingany defined events prior to upgrading.
   To view existing non-defaultevents execute the following commands
   while connected AS SYSDBA:

     Events:
       SELECT (translate(value,chr(13)||chr(10),'')) FROM sys.v$parameter2
         WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

    
     Trace Events:
       SELECT (translate(value,chr(13)||chr(10),'')) from sys.v$parameter2
         WHERE UPPER(name) = '_TRACE_EVENTS' ANDisdefault='FALSE'
    
   Changes will need to be madein the init.ora or spfile.

11、修改不满足项

1. SQL> show parametershared
   
2.  
   
3. NAME TYPE VALUE
   
4. ----------------------------------------------- ------------------------------
   
5. hi_shared_memory_address integer 0
   
6. max_shared_servers integer
   
7. shared_memory_address integer 0
   
8. shared_pool_reserved_size big integer 8M
   
9. shared_pool_size big integer 160M
   
10. shared_server_sessions integer
    
11. shared_servers integer 1
    
12. 
    
13. SQL> alter system setshared_pool_size=800m scope=spfile;
    
14.  
    
15. System altered.
    
16. 
    
17. SQL> shutdown immediate
    
18. 
    
19. Database closed.
    
20. Database dismounted.
    
21. ORACLE instance shut down.
    
22. 
    
23. SQL> startup mountl
    
24. SP2-0714: invalidcombination of STARTUP options
    
25. 
    
26. SQL> startup mount;
    
27. 
    
28. ORACLE instance started.
    
29. Total System Global Area2872786944 bytes
    
30. Fixed Size 2256712 bytes
    
31. Variable Size 2634023096 bytes
    
32. Database Buffers 218103808 bytes
    
33. Redo Buffers 18403328 bytes
    
34. 
    
35. Database mounted.
    
36. 
    
37. SQL> alter databaseflashback on;
    
38. 
    
39.  
    
40. ###创建一个回滚点
    
41. SQL> create restore pointmaclean_rollback guarantee flashback database;
    
42. Restore point created.
    
43. 
    
44. SQL> select * fromv$restore_point;
    
45.  
    
46.        SCN DATABASE_INCARNATION# GUASTORAGE_SIZE
    
47. ------------------------------- --- ------------
    
48. 
    
49. TIME
    
50. ---------------------------------------------------------------------------
    
51. RESTORE_POINT_TIME PRE
    
52. ------------------------------------------------------------------------------
    
53. NAME
    
54. --------------------------------------------------------------------------------
    
55.    1187867 2 YES 52428800
    
56. 
    
57. 04-OCT-14 02.41.43.000000000AM
    
58.                                                                            YES
    
59. MACLEAN_ROLLBACK
    
60. 
    
61.  
    
62. 
    
63. ###再次运行检查：
    
64. 
    
65. SQL>@?/rdbms/admin/utlu112i.sql
    
66. 
    
67. Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 03:26:20
    
68. Script Version: 11.2.0.4.0Build: 001
    
69. 
    
70. .
    
71. **********************************************************************
    
72. Database:
    
73. **********************************************************************
    
74. 
    
75. --> name: WOO
    
76. --> version: 11.2.0.3.0
    
77. --> compatible: 11.2.0.0.0
    
78. --> blocksize: 8192
    
79. --> platform: Linux x86 64-bit
    
80. --> timezone file: V14
    
81. 
    
82. .
    
83. **********************************************************************
    
84. Tablespaces: [makeadjustments in the current environment]
    
85. **********************************************************************
    
86. 
    
87. --> SYSTEM tablespace isadequate for the upgrade.
    
88. .... minimum required size:917 MB
    
89. --> SYSAUX tablespace isadequate for the upgrade.
    
90. .... minimum required size:646 MB
    
91. --> UNDOTBS1 tablespaceis adequate for the upgrade.
    
92. .... minimum required size:400 MB
    
93. --> TEMP tablespace isadequate for the upgrade.
    
94. .... minimum required size:60 MB
    
95. 
    
96. .
    
97. **********************************************************************
    
98. Flashback: ON
    
99. **********************************************************************
    
100. 
     
101. FlashbackInfo:
     
102. --> name: /DBSoft/fast_recovery_area
     
103. --> limit: 4122 MB
     
104. --> used: 1490 MB
     
105. --> size: 4122 MB
     
106. --> reclaim: 1097.734375 MB
     
107. --> files: 11
     
108. 
     
109. WARNING: --> FlashbackRecovery Area Set. Please ensureadequate disk space inrecover
     
110. y areas before performing anupgrade.
     
111. .
     
112. **********************************************************************
     
113. Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
     
114. Note: Pre-upgrade tool wasrun on a lower version 64-bit database.
     
115. **********************************************************************
     
116. 
     
117. --> If Target Oracle is32-Bit, refer here for Update Parameters:
     
118. -- No update parameterchanges are required.
     
119. .
     
120. 
     
121.  
     
122. --> If Target Oracle is64-Bit, refer here for Update Parameters:
     
123. -- No update parameterchanges are required.
     
124. .
     
125. 
     
126. **********************************************************************
     
127. Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
     
128. **********************************************************************
     
129. -- No renamed parametersfound. No changes are required.
     
130. .
     
131. 
     
132. **********************************************************************
     
133. Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]
     
134. **********************************************************************
     
135. -- No obsolete parametersfound. No changes are required
     
136. .
     
137.  
     
138. 
     
139. **********************************************************************
     
140. Components: [The followingdatabase components will be upgraded or installed]
     
141. **********************************************************************
     
142. 
     
143. --> Oracle CatalogViews [upgrade] VALID
     
144. --> Oracle Packages andTypes [upgrade] VALID
     
145. --> JServer JAVA VirtualMachine [upgrade] VALID
     
146. --> Oracle XDK forJava [upgrade] VALID
     
147. --> Oracle WorkspaceManager [upgrade] VALID
     
148. --> OLAP AnalyticWorkspace [upgrade] VALID
     
149. --> OLAP Catalog [upgrade] VALID
     
150. --> EM Repository [upgrade] VALID
     
151. --> Oracle Text [upgrade] VALID
     
152. --> Oracle XMLDatabase [upgrade] VALID
     
153. --> Oracle JavaPackages [upgrade] VALID
     
154. --> OracleinterMedia [upgrade] VALID
     
155. --> Spatial [upgrade] VALID
     
156. --> ExpressionFilter [upgrade] VALID
     
157. --> Rule Manager [upgrade] VALID
     
158. --> Oracle ApplicationExpress [upgrade] VALID
     
159. ... APEX will only beupgraded if the version of APEX in
     
160. ... the target Oracle homeis higher than the current one.
     
161. --> Oracle OLAP API [upgrade] VALID
     
162. .
     
163. 
     
164. **********************************************************************
     
165. Miscellaneous Warnings
     
166. **********************************************************************
     
167. 
     
168. WARNING: --> Your recyclebin is turned on and currently contains no objects.
     
169. .... Because it is REQUIREDthat the recycle bin be empty prior to upgrading
     
170. .... and your recycle bin isturned on, you may need to execute the command:
     
171.         PURGE DBA_RECYCLEBIN
     
172. .... prior to executing yourupgrade to confirm the recycle bin is empty.
     
173. WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package.
     
174. .... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs.
     
175. .... USER APEX_030200 hasdependent objects.
     
176. .
     
177. 
     
178. **********************************************************************
     
179. Recommendations
     
180. **********************************************************************
     
181. Oracle recommends gatheringdictionary statistics prior to
     
182. upgrading the database.
     
183. To gather dictionarystatistics execute the following command
     
184. while connected as SYSDBA:
     
185. 
     
186.  
     
187.     EXECUTE dbms_stats.gather_dictionary_stats;
     
188. 
     
189. **********************************************************************
     
190. Oracle recommends removingall hidden parameters prior to upgrading.
     
191. To view existing hiddenparameters execute the following command
     
192. while connected AS SYSDBA:
     
193.  
     
194. 
     
195.     SELECT name,description fromSYS.V$PARAMETER WHERE name
     
196.         LIKE \'\\_%\' ESCAPE \'\\\'
     
197. 
     
198. Changes will need to be madein the init.ora or spfile.
     
199. 
     
200. **********************************************************************
     
201. Oracle recommends reviewingany defined events prior to upgrading.
     
202. To view existing non-defaultevents execute the following commands
     
203. while connected AS SYSDBA:
     
204.   Events:
     
205.     SELECT (translate(value,chr(13)||chr(10),\'\')) FROM sys.v$parameter2
     
206.       WHERE UPPER(name) =\'EVENT\' AND isdefault=\'FALSE\'
     
207. 
     
208.   Trace Events:
     
209.     SELECT (translate(value,chr(13)||chr(10),\'\')) from sys.v$parameter2
     
210.       WHERE UPPER(name) = \'_TRACE_EVENTS\' ANDisdefault=\'FALSE\'
     
211. 
     
212.  
     
213. Changes will need to be madein the init.ora or spfile.

12、执行升级操作   

1. SQL> shutdown immediate
   
2. 
   
3. Database closed.
   
4. Database dismounted.
   
5. ORACLE instance shut down.
   
6. 
   
7. SQL> startup upgrade;
   
8. 
   
9. ORACLE instance started.
   
10. Total System Global Area2872786944 bytes
    
11. Fixed Size 2256712 bytes
    
12. Variable Size 2634023096 bytes
    
13. Database Buffers 218103808 bytes
    
14. Redo Buffers 18403328 bytes
    
15. Database mounted.
    
16. 
    
17. Database opened.
    
18. 
    
19. SQL> set echo on
    
20. SQL> spool/home/oracle/upgrade.log
    
21. SQL> set time on;
    
22. 
    
23. 03:28:37 SQL>@?/rdbms/admin/catupgrd.sql ---该脚本会运行十分钟左右
    
24. ……..
    
25. 
    
26. Oracle Database 11.2Post-Upgrade Status Tool 10-04-2014 04:01:36
    
27. .
    
28. Component Current Version Elapsed Time
    
29. Name Status Number HH:MM:SS
    
30. .
    
31. Oracle Server
    
32. . VALID 11.2.0.4.0 00:08:38
    
33. JServer JAVA Virtual Machine
    
34. . VALID 11.2.0.4.0 00:04:26
    
35. Oracle Workspace Manager
    
36. . VALID 11.2.0.4.0 00:00:25
    
37. OLAP Analytic Workspace
    
38. . VALID 11.2.0.4.0 00:01:27
    
39. OLAP Catalog
    
40. . VALID 11.2.0.4.0 00:00:32
    
41. Oracle OLAP API
    
42. . VALID 11.2.0.4.0 00:00:19
    
43. Oracle Enterprise Manager
    
44. . VALID 11.2.0.4.0 00:06:25
    
45. Oracle XDK
    
46. . VALID 11.2.0.4.0 00:00:26
    
47. Oracle Text
    
48. . VALID 11.2.0.4.0 00:00:24
    
49. Oracle XML Database
    
50. . VALID 11.2.0.4.0 00:01:46
    
51. Oracle Database JavaPackages
    
52. . VALID 11.2.0.4.0 00:00:08
    
53. Oracle Multimedia
    
54. . VALID 11.2.0.4.0 00:01:43
    
55. Spatial
    
56. . VALID 11.2.0.4.0 00:05:34
    
57. Oracle Expression Filter
    
58. . VALID 11.2.0.4.0 00:00:06
    
59. Oracle Rules Manager
    
60. . VALID 11.2.0.4.0 00:00:07
    
61. Oracle Application Express
    
62. . VALID 3.2.1.00.12
    
63. Final Actions
    
64. . 00:00:00
    
65. Total Upgrade Time: 00:32:35
    
66. 
    
67.  
    
68. PL/SQL proceduresuccessfully completed.
    
69. 
    
70.  
    
71. 04:01:36 SQL> 
    
72. 04:01:36 SQL> SETSERVEROUTPUT OFF
    
73. 04:01:36 SQL> SET VERIFYON
    
74. 04:01:36 SQL> commit;
    
75. 
    
76.  
    
77. Commit complete.
    
78. 
    
79.  
    
80. 
    
81. 04:01:36 SQL> 
    
82. 04:01:36 SQL> shutdownimmediate;
    
83. 
    
84. Database closed.
    
85. Database dismounted.
    
86. ORACLE instance shut down.
    
87. 
    
88. 04:01:54 SQL> 
    
89. 04:01:54 SQL> 
    
90. 04:01:54 SQL> DOC
    
91. 04:01:54 DOC>#######################################################################
    
92. 04:01:54DOC>#######################################################################
    
93. 04:01:54 DOC>
    
94. 04:01:54 DOC> The above sql script is the final step ofthe upgrade. Please
    
95. 04:01:54 DOC> review any errors in the spool log file. Ifthere are any errors in
    
96. 04:01:54 DOC> the spool file, consult the Oracle DatabaseUpgrade Guide for
    
97. 04:01:54 DOC> troubleshooting recommendations.
    
98. 04:01:54 DOC>
    
99. 04:01:54 DOC> Next restart for normal operation, and thenrun utlrp.sql to
    
100. 04:01:54 DOC> recompile any invalid application objects.
     
101. 04:01:54 DOC>
     
102. 04:01:54 DOC> If the source database had an older timezone version prior to
     
103. 04:01:54 DOC> upgrade, then please run the DBMS_DSTpackage. DBMS_DST will upgrade
     
104. 04:01:54 DOC> TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped
     
105. 04:01:54 DOC> with Oracle.
     
106. 04:01:54 DOC>
     
107. 04:01:54DOC>#######################################################################
     
108. 04:01:54 DOC>#######################################################################
     
109. 04:01:54 DOC>#
     
110. 04:01:54 SQL> 
     
111. 04:01:54 SQL> Rem Seterrorlogging off
     
112. 04:01:54 SQL> SETERRORLOGGING OFF;
     
113. 04:01:54 SQL> 
     
114. 04:01:54 SQL> REM END OFCATUPGRD.SQL
     
115. 04:01:54 SQL> 
     
116. 04:01:54 SQL> REM bug12337546 - Exit current sqlplus session at end of catupgrd.sql.
     
117. 04:01:54 SQL> REM This forces user to start a newsqlplus session in order
     
118. 04:01:54 SQL> REM to connect to the upgraded db.
     
119. 04:01:54 SQL> exit
     
120. 
     
121. Disconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
     
122. With the Partitioning, OLAP,Data Mining and Real Application Testing options

以上catupgrd.sql脚本运行了50分钟左右，执行完之后会shutdown immediate数据库，这个时候我们将要重启数据库运行utlrp.sql脚本编译失效对象：  

13、运行utlrp.sql编译失效对象

1. [oracle@db01 dbs]$ sqlplus /as sysdba

   SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 04:11:22 2014
   Copyright (c) 1982, 2013,Oracle.  All rights reserved.
    
   Connected to an idleinstance.
    
   SQL> startup

   ORACLE instance started.
    
   Total System Global Area 2872786944bytes
   Fixed Size                  2256712 bytes
   Variable Size            2634023096 bytes
   Database Buffers          218103808 bytes
   Redo Buffers               18403328 bytes
   Database mounted.

   Database opened.

   SQL> @?/rdbms/admin/utlrp
    
   TIMESTAMP
   --------------------------------------------------------------------------------

   COMP_TIMESTAMPUTLRP_BGN  2014-10-04 04:14:57
   DOC>   The following PL/SQL block invokesUTL_RECOMP to recompile invalid
   DOC>   objects in the database. Recompilation timeis proportional to the
   DOC>   number of invalid objects in the database,so this command may take
   DOC>   a long time to execute on a database with alarge number of invalid
   DOC>   objects.
   DOC>
   DOC>   Use the following queries to trackrecompilation progress:
   DOC>
   DOC>   1. Query returning the number of invalidobjects remaining. This
   DOC>      number should decrease with time.
   DOC>         SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
   DOC>
   DOC>   2. Query returning the number of objectscompiled so far. This number
   DOC>      should increase with time.
   DOC>         SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
   DOC>
   DOC>   This script automatically chooses serial orparallel recompilation
   DOC>   based on the number of CPUs available(parameter cpu_count) multiplied
   DOC>   by the number of threads per CPU (parameterparallel_threads_per_cpu).
   DOC>   On RAC, this number is added across all RACnodes.
   DOC>
   DOC>   UTL_RECOMP uses DBMS_SCHEDULER to createjobs for parallel
   DOC>   recompilation. Jobs are created withoutinstance affinity so that they
   DOC>   can migrate across RAC nodes. Use thefollowing queries to verify
   DOC>   whether UTL_RECOMP jobs are being createdand run correctly:
   DOC>
   DOC>   1. Query showing jobs created by UTL_RECOMP
   DOC>         SELECT job_name FROM dba_scheduler_jobs
   DOC>            WHERE job_name like'UTL_RECOMP_SLAVE_%';
   DOC>
   DOC>   2. Query showing UTL_RECOMP jobs that arerunning
   DOC>         SELECT job_name FROMdba_scheduler_running_jobs
   DOC>            WHERE job_name like'UTL_RECOMP_SLAVE_%';
   DOC># 
   PL/SQL proceduresuccessfully completed.
    
   TIMESTAMP
   --------------------------------------------------------------------------------
   COMP_TIMESTAMPUTLRP_END  2014-10-04 04:15:34
   DOC> The following queryreports the number of objects that have compiled
   DOC> with errors.
   DOC>
   DOC> If the number ishigher than expected, please examine the error
   DOC> messages reportedwith each object (using SHOW ERRORS) to see if they
   DOC> point to systemmisconfiguration or resource constraints that must be
   DOC> fixed beforeattempting to recompile these objects.
   DOC>#

   OBJECTS WITH ERRORS
   -------------------
                     0

   DOC> The following queryreports the number of errors caught during
   DOC> recompilation. Ifthis number is non-zero, please query the error
   DOC> messages in thetable UTL_RECOMP_ERRORS to see if any of these errors
   DOC> are due tomisconfiguration or resource constraints that must be
   DOC> fixed before objectscan compile successfully.
   DOC>#
    
   ERRORS DURING RECOMPILATION
   ---------------------------
                             0
    
   Function created.
    
   PL/SQL proceduresuccessfully completed.
    
   Function dropped.
    
   PL/SQL proceduresuccessfully completed.

   SQL>

该脚本耗时约为3分钟左右。   

14、至此数据库已经升级完成，查看各组件版本号：

 

1. SQL> select comp_name,status,version fromdba_server_registry
    
   COMP_NAME                                STATUS         VERSION
   ------------------------------------------------------ ------------------------------

   OWB                                      VALID          11.2.0.3.0
   Oracle ApplicationExpress                VALID          3.2.1.00.12
   Oracle EnterpriseManager                 VALID          11.2.0.4.0
   OLAP Catalog                             VALID          11.2.0.4.0
   Spatial                                  VALID          11.2.0.4.0
   Oracle Multimedia                        VALID          11.2.0.4.0
   Oracle XML Database                      VALID          11.2.0.4.0
   Oracle Text                              VALID          11.2.0.4.0
   Oracle ExpressionFilter                  VALID          11.2.0.4.0
   Oracle Rules Manager                     VALID          11.2.0.4.0
   Oracle WorkspaceManager                  VALID          11.2.0.4.0
   Oracle Database CatalogViews             VALID          11.2.0.4.0
   Oracle Database Packages andTypes        VALID          11.2.0.4.0
   JServer JAVA VirtualMachine              VALID          11.2.0.4.0
   Oracle XDK                               VALID          11.2.0.4.0
   Oracle Database JavaPackages             VALID          11.2.0.4.0
   OLAP Analytic Workspace                  VALID          11.2.0.4.0
   Oracle OLAP API                          VALID          11.2.0.4.0
    
   18 rows selected.

15、检查无效对象：  

1. SQL> select * from dba_objects where status !=\'VALID\'; 
   
2.  
   
3. no rows selected

16、升级成功后删除原来的目录，通过EMCA重建EM  

1. [oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
   
2. 
   
3. 手工创建EM资料库:
   
4. ####emca -repos drop
   
5.  
   
6. [oracle@db01 /]$ emca -reposdrop
   
7. 
   
8. STARTED EMCA at Oct 4, 20146:11:41 AM
   
9. EM Configuration Assistant,Version 11.2.0.3.0 Production
   
10. 
    
11. Copyright (c) 2003, 2011,Oracle. All rights reserved.
    
12.  
    
13. Enter the followinginformation:
    
14. Database SID: woo
    
15. Listener port number: 1521
    
16. Password for SYS user: 
    
17. Password for SYSMANuser: 
    
18.  
    
19. ----------------------------------------------------------------------
    
20. WARNING : While repositoryis dropped the database will be put in quiesce mode.
    
21. ----------------------------------------------------------------------
    
22. 
    
23. Do you wish to continue?[yes(Y)/no(N)]: y
    
24. Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMConfig perform
    
25. INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_11_41.log.
    
26. Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMReposConfig invoke
    
27. INFO: Dropping the EMrepository (this may take a while) ...
    
28. Oct 4, 2014 6:13:37 AMoracle.sysman.emcp.EMReposConfig invoke
    
29. INFO: Repositorysuccessfully dropped
    
30. Enterprise Managerconfiguration completed successfully
    
31. FINISHED EMCA at Oct 4, 20146:13:37 AM
    
32. 
    
33. ####emca -repos create
    
34.  
    
35. [oracle@db01 /]$ emca -reposcreate
    
36.  
    
37. STARTED EMCA at Oct 4, 20146:14:07 AM
    
38. EM Configuration Assistant,Version 11.2.0.3.0 Production
    
39. Copyright (c) 2003, 2011,Oracle. All rights reserved.
    
40.  
    
41. Enter the followinginformation:
    
42. Database SID: woo
    
43. Listener port number: 1521
    
44. Password for SYS user: 
    
45. Password for SYSMANuser: 
    
46. 
    
47. Do you wish to continue?[yes(Y)/no(N)]: y
    
48. Oct 4, 2014 6:14:20 AM oracle.sysman.emcp.EMConfigperform
    
49. INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_14_07.log.
    
50. Oct 4, 2014 6:14:21 AMoracle.sysman.emcp.EMReposConfig createRepository
    
51. INFO: Creating the EMrepository (this may take a while) ...
    
52. Oct 4, 2014 6:17:57 AMoracle.sysman.emcp.EMReposConfig invoke
    
53. INFO: Repositorysuccessfully created
    
54. Enterprise Managerconfiguration completed successfully
    
55. FINISHED EMCA at Oct 4, 20146:17:57 AM
    
56.  
    
57. ###emca -config dbcontrol db
    
58. 
    
59. [oracle@db01 /]$ emca-config dbcontrol db
    
60.  
    
61. STARTED EMCA at Oct 4, 20146:24:04 AM
    
62. EM Configuration Assistant,Version 11.2.0.3.0 Production
    
63. Copyright (c) 2003, 2011,Oracle. All rights reserved.
    
64.  
    
65. Enter the followinginformation:
    
66. Database SID: woo
    
67. Database Control is alreadyconfigured for the database woo
    
68. You have chosen to configureDatabase Control for managing the database woo
    
69. This will remove theexisting configuration and the default settings and perform a freshconfiguration
    
70. Do you wish to continue?[yes(Y)/no(N)]: y
    
71. Listener ORACLE_HOME [/DBSoft/Product/11.2.4/db_1 ]:
    
72. Password for SYS user: 
    
73. Password for DBSNMPuser: 
    
74. Password for SYSMANuser: 
    
75. Email address fornotifications (optional):
    
76. Outgoing Mail (SMTP) serverfor notifications (optional):
    
77. ----------------------------------------------------------------- 
    
78. You have specified thefollowing settings
    
79. 
    
80. Database ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
    
81. Local hostname................ db01
    
82. Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
    
83. Listener port number................ 1521
    
84. Database SID................ woo
    
85. Email address fornotifications ...............
    
86. Outgoing Mail (SMTP) serverfor notifications ...............
    
87. -----------------------------------------------------------------
    
88. 
    
89. Do you wish to continue?[yes(Y)/no(N)]: y
    
90. Oct 4, 2014 6:24:49 AMoracle.sysman.emcp.EMConfig perform
    
91. INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_24_04.log.
    
92. Oct 4, 2014 6:24:50 AMoracle.sysman.emcp.util.DBControlUtil stopOMS
    
93. INFO: Stopping DatabaseControl (this may take a while) ...
    
94. Oct 4, 2014 6:24:54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
    
95. INFO: Uploadingconfiguration data to EM repository (this may take a while) ...
    
96. Oct 4, 2014 6:25:53 AMoracle.sysman.emcp.EMReposConfig invoke
    
97. INFO: Uploaded configurationdata successfully
    
98. Oct 4, 2014 6:25:57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
    
99. INFO: Securing DatabaseControl (this may take a while) ...
    
100. Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
     
101. INFO: Database Controlsecured successfully.
     
102. Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil startOMS
     
103. INFO: Starting DatabaseControl (this may take a while) ...
     
104. Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
     
105. INFO: Database Controlstarted successfully
     
106. Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
     
107. INFO:>>>>>>>>>>> The Database Control URL ishttps://db01:5500/em <<<<<<<<<<<
     
108. Oct 4, 2014 6:26:25 AMoracle.sysman.emcp.EMDBPostConfig invoke
     
109. WARNING: 
     
110. 
     
111. ************************ WARNING ************************
     
112. Management Repository hasbeen placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in thefile: /DBSoft/Product/11.2.4/db_1/db01_woo/sysman/config/emkey.ora. Ensure thisfile is backed up as the encrypted data will become unusable if this file islost.
     
113. ***********************************************************
     
114. 
     
115. Enterprise Managerconfiguration completed successfully
     
116. FINISHED EMCA at Oct 4, 20146:26:25 AM
     
117. 
     
118. [oracle@db01 /]$

17、至此，数据库已经升级完成。