探索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: 下載需要有Oracle Support才可以。

Patchset 包含有7個檔案，關於這七個檔案的作用，詳見如下連結：

我們升級Database，只需要其中的第一個和第二檔案即可。將2個檔案解壓縮後就可以執行升級操作了。

升級前準備：

1、
1、檢視資料庫和作業系統相關資訊:

[root@db01 ~]# uname -a
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
[root@db01 ~]# lsb_release -a
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
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Release: 5.8
Codename: Tikanga
[root@db01 ~]# su - oracle -c \"sqlplus / as sysdba\";
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 3 21:32:02 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
WOO
SQL>

2、備份資料庫

[oracle@db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WOO (DBID=4199461782)
RMAN> backup database plus archivelog delete input format \'/DBBackup/Phycal/full_%U.bak\';
Starting backup at 03-OCT-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=860017183
input archived log thread=1 sequence=16 RECID=2 STAMP=860017184
input archived log thread=1 sequence=17 RECID=3 STAMP=860017186
input archived log thread=1 sequence=18 RECID=4 STAMP=860017186
input archived log thread=1 sequence=19 RECID=5 STAMP=860017188
input archived log thread=1 sequence=20 RECID=6 STAMP=860017387
channel ORA_DISK_1: starting piece 1 at 03-OCT-14
channel ORA_DISK_1: finished piece 1 at 03-OCT-14
piece handle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG20141003T214307 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_15_b2x9rz0z_.arc RECID=1 STAMP=860017183
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_16_b2x9s05l_.arc RECID=2 STAMP=860017184
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_17_b2x9s2nx_.arc RECID=3 STAMP=860017186
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_18_b2x9s2od_.arc RECID=4 STAMP=860017186
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_19_b2x9s4dr_.arc RECID=5 STAMP=860017188
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_20_b2x9zbz2_.arc RECID=6 STAMP=860017387
Finished backup at 03-OCT-14
Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/DBData/woo/system01.dbf
input datafile file number=00002 name=/DBData/woo/sysaux01.dbf
input datafile file number=00005 name=/DBData/woo/example01.dbf
input datafile file number=00003 name=/DBData/woo/undotbs01.dbf
input datafile file number=00004 name=/DBData/woo/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-OCT-14
channel ORA_DISK_1: finished piece 1 at 03-OCT-14
piece handle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_nnndf_TAG20141003T214309_b2x9zfm5_.bkp tag=TAG20141003T214309 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-OCT-14
channel ORA_DISK_1: finished piece 1 at 03-OCT-14
piece handle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_ncsnf_TAG20141003T214309_b2xb2qlm_.bkp tag=TAG20141003T214309 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-OCT-14
Starting backup at 03-OCT-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=7 STAMP=860017496
channel ORA_DISK_1: starting piece 1 at 03-OCT-14
channel ORA_DISK_1: finished piece 1 at 03-OCT-14
piece handle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG20141003T214456 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_21_b2xb2rsf_.arc RECID=7 STAMP=860017496
Finished backup at 03-OCT-14
RMAN>

3、停止資料庫

###停止資料庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
###停止監聽
SQL> host lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2014 01:39:26
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
###停止EM
SQL> host emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://db01:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
###檢視oracle程式，檢查是否已經停止完畢
[root@db01 ~]# ps -ef|grep ora
root 4971 4944 0 01:46 pts/0 00:00:00 grep ora

3、 4、備份老的ORACLE_HOME和oraInventory

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

4、 5、上傳並解壓縮Oracle Database 11.2.0.4安裝介質

[oracle@db01 ~]$ ll
total 2489644
drwxr-xr-x 2 oracle oinstall 4096 Oct 3 04:06 Desktop
-rw-r--r-- 1 oracle oinstall 1395582860 Oct 3 06:55 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Oct 3 06:54 p13390677_112040_Linux-x86-64_2of7.zip
[oracle@db01 ~]$ unzip p13390677_112040_Linux-x86-64_1of7.zip
[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指令碼

[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環境變數

[root@db01 ~]# su - oracle
[oracle@db01 ~]$ vi .bash_profile ---修改如下行,將11.2.3改成11.2.4即可
export ORACLE_HOME=$ORACLE_BASE/Product/11.2.4/db_1
[oracle@db01 ~]$ vi /etc/oratab ------修改如下行11.2.3為11.2.4
woo:/DBSoft/Product/11.2.4/db_1:N
[oracle@db01 admin]$ cp /DBSoft/Product/11.2.3/db_1/dbs/* /DBSoft/Product/11.2.4/db_1/dbs/

9、複製監聽配置檔案

[oracle@db01 admin]$ source ~/.bash_profile
[oracle@db01 admin]$ pwd
/DBSoft/Product/11.2.3/db_1/network/admin
[oracle@db01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@db01 admin]$ ls
samples shrept.lst
[oracle@db01 admin]$ cp /DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin
cp: omitting directory `/DBSoft/Product/11.2.3/db_1/network/admin/samples\

10、執行預升級指令碼檢查

[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:
**********************************************************************

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

--&gt SYSTEM tablespace isadequate for the upgrade.
.... minimum required size:917 MB
--&gt SYSAUX tablespace isadequate for the upgrade.
.... minimum required size:646 MB
--&gt UNDOTBS1 tablespaceis adequate for the upgrade.
.... minimum required size:400 MB
--&gt 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.
**********************************************************************

--&gt If Target Oracle is32-Bit, refer here for Update Parameters:
WARNING: --&gt"shared_pool_size" needs to be increased to at least 236 MB
.
--&gt If Target Oracle is64-Bit, refer here for Update Parameters:
WARNING: --&gt"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]
**********************************************************************

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

WARNING: --&gt 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: --&gt 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、修改不滿足項

SQL> show parametershared
NAME TYPE VALUE
----------------------------------------------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 160M
shared_server_sessions integer
shared_servers integer 1
SQL> alter system setshared_pool_size=800m scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mountl
SP2-0714: invalidcombination of STARTUP options
SQL> startup mount;
ORACLE instance started.
Total System Global Area2872786944 bytes
Fixed Size 2256712 bytes
Variable Size 2634023096 bytes
Database Buffers 218103808 bytes
Redo Buffers 18403328 bytes
Database mounted.
SQL> alter databaseflashback on;
###建立一個回滾點
SQL> create restore pointmaclean_rollback guarantee flashback database;
Restore point created.
SQL> select * fromv$restore_point;
SCN DATABASE_INCARNATION# GUASTORAGE_SIZE
------------------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME PRE
------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
1187867 2 YES 52428800
04-OCT-14 02.41.43.000000000AM
YES
MACLEAN_ROLLBACK
###再次執行檢查：
SQL>@?/rdbms/admin/utlu112i.sql
Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 03:26:20
Script Version: 11.2.0.4.0Build: 001
.
**********************************************************************
Database:
**********************************************************************
--&gt name: WOO
--&gt version: 11.2.0.3.0
--&gt compatible: 11.2.0.0.0
--&gt blocksize: 8192
--&gt platform: Linux x86 64-bit
--&gt timezone file: V14
.
**********************************************************************
Tablespaces: [makeadjustments in the current environment]
**********************************************************************
--&gt SYSTEM tablespace isadequate for the upgrade.
.... minimum required size:917 MB
--&gt SYSAUX tablespace isadequate for the upgrade.
.... minimum required size:646 MB
--&gt UNDOTBS1 tablespaceis adequate for the upgrade.
.... minimum required size:400 MB
--&gt TEMP tablespace isadequate for the upgrade.
.... minimum required size:60 MB
.
**********************************************************************
Flashback: ON
**********************************************************************
FlashbackInfo:
--&gt name: /DBSoft/fast_recovery_area
--&gt limit: 4122 MB
--&gt used: 1490 MB
--&gt size: 4122 MB
--&gt reclaim: 1097.734375 MB
--&gt files: 11
WARNING: --&gt FlashbackRecovery Area Set. Please ensureadequate disk space inrecover
y areas before performing anupgrade.
.
**********************************************************************
Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool wasrun on a lower version 64-bit database.
**********************************************************************
--&gt If Target Oracle is32-Bit, refer here for Update Parameters:
-- No update parameterchanges are required.
.
--&gt If Target Oracle is64-Bit, refer here for Update Parameters:
-- No update parameterchanges are required.
.
**********************************************************************
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]
**********************************************************************
--&gt Oracle CatalogViews [upgrade] VALID
--&gt Oracle Packages andTypes [upgrade] VALID
--&gt JServer JAVA VirtualMachine [upgrade] VALID
--&gt Oracle XDK forJava [upgrade] VALID
--&gt Oracle WorkspaceManager [upgrade] VALID
--&gt OLAP AnalyticWorkspace [upgrade] VALID
--&gt OLAP Catalog [upgrade] VALID
--&gt EM Repository [upgrade] VALID
--&gt Oracle Text [upgrade] VALID
--&gt Oracle XMLDatabase [upgrade] VALID
--&gt Oracle JavaPackages [upgrade] VALID
--&gt OracleinterMedia [upgrade] VALID
--&gt Spatial [upgrade] VALID
--&gt ExpressionFilter [upgrade] VALID
--&gt Rule Manager [upgrade] VALID
--&gt Oracle ApplicationExpress [upgrade] VALID
... APEX will only beupgraded if the version of APEX in
... the target Oracle homeis higher than the current one.
--&gt Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --&gt 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: --&gt 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.

12、執行升級操作

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area2872786944 bytes
Fixed Size 2256712 bytes
Variable Size 2634023096 bytes
Database Buffers 218103808 bytes
Redo Buffers 18403328 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> spool/home/oracle/upgrade.log
SQL> set time on;
03:28:37 SQL>@?/rdbms/admin/catupgrd.sql ---該指令碼會執行十分鐘左右
……..
Oracle Database 11.2Post-Upgrade Status Tool 10-04-2014 04:01:36
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:08:38
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:04:26
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:25
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:01:27
OLAP Catalog
. VALID 11.2.0.4.0 00:00:32
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:19
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:06:25
Oracle XDK
. VALID 11.2.0.4.0 00:00:26
Oracle Text
. VALID 11.2.0.4.0 00:00:24
Oracle XML Database
. VALID 11.2.0.4.0 00:01:46
Oracle Database JavaPackages
. VALID 11.2.0.4.0 00:00:08
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:43
Spatial
. VALID 11.2.0.4.0 00:05:34
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:06
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:07
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:32:35
PL/SQL proceduresuccessfully completed.
04:01:36 SQL>
04:01:36 SQL> SETSERVEROUTPUT OFF
04:01:36 SQL> SET VERIFYON
04:01:36 SQL> commit;
Commit complete.
04:01:36 SQL>
04:01:36 SQL> shutdownimmediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
04:01:54 SQL>
04:01:54 SQL>
04:01:54 SQL> DOC
04:01:54 DOC>#######################################################################
04:01:54DOC>#######################################################################
04:01:54 DOC>
04:01:54 DOC> The above sql script is the final step ofthe upgrade. Please
04:01:54 DOC> review any errors in the spool log file. Ifthere are any errors in
04:01:54 DOC> the spool file, consult the Oracle DatabaseUpgrade Guide for
04:01:54 DOC> troubleshooting recommendations.
04:01:54 DOC>
04:01:54 DOC> Next restart for normal operation, and thenrun utlrp.sql to
04:01:54 DOC> recompile any invalid application objects.
04:01:54 DOC>
04:01:54 DOC> If the source database had an older timezone version prior to
04:01:54 DOC> upgrade, then please run the DBMS_DSTpackage. DBMS_DST will upgrade
04:01:54 DOC> TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped
04:01:54 DOC> with Oracle.
04:01:54 DOC>
04:01:54DOC>#######################################################################
04:01:54 DOC>#######################################################################
04:01:54 DOC>#
04:01:54 SQL>
04:01:54 SQL> Rem Seterrorlogging off
04:01:54 SQL> SETERRORLOGGING OFF;
04:01:54 SQL>
04:01:54 SQL> REM END OFCATUPGRD.SQL
04:01:54 SQL>
04:01:54 SQL> REM bug12337546 - Exit current sqlplus session at end of catupgrd.sql.
04:01:54 SQL> REM This forces user to start a newsqlplus session in order
04:01:54 SQL> REM to connect to the upgraded db.
04:01:54 SQL> exit
Disconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP,Data Mining and Real Application Testing options

以上catupgrd.sql指令碼執行了50分鐘左右，執行完之後會shutdown immediate資料庫，這個時候我們將要重啟資料庫執行utlrp.sql指令碼編譯失效物件：

13、執行utlrp.sql編譯失效物件

[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、至此資料庫已經升級完成，檢視各元件版本號：

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、檢查無效物件：

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

16、升級成功後刪除原來的目錄，透過EMCA重建EM

[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
手工建立EM資料庫:
####emca -repos drop
[oracle@db01 /]$ emca -reposdrop
STARTED EMCA at Oct 4, 20146:11:41 AM
EM Configuration Assistant,Version 11.2.0.3.0 Production
Enter the followinginformation:
Database SID: woo
Listener port number: 1521
Password for SYS user:
Password for SYSMANuser:
----------------------------------------------------------------------
WARNING : While repositoryis dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue?[yes(Y)/no(N)]: y
Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMConfig perform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_11_41.log.
Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EMrepository (this may take a while) ...
Oct 4, 2014 6:13:37 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Repositorysuccessfully dropped
Enterprise Managerconfiguration completed successfully
FINISHED EMCA at Oct 4, 20146:13:37 AM
####emca -repos create
[oracle@db01 /]$ emca -reposcreate
STARTED EMCA at Oct 4, 20146:14:07 AM
EM Configuration Assistant,Version 11.2.0.3.0 Production
Enter the followinginformation:
Database SID: woo
Listener port number: 1521
Password for SYS user:
Password for SYSMANuser:
Do you wish to continue?[yes(Y)/no(N)]: y
Oct 4, 2014 6:14:20 AM oracle.sysman.emcp.EMConfigperform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_14_07.log.
Oct 4, 2014 6:14:21 AMoracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EMrepository (this may take a while) ...
Oct 4, 2014 6:17:57 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Repositorysuccessfully created
Enterprise Managerconfiguration completed successfully
FINISHED EMCA at Oct 4, 20146:17:57 AM
###emca -config dbcontrol db
[oracle@db01 /]$ emca-config dbcontrol db
STARTED EMCA at Oct 4, 20146:24:04 AM
EM Configuration Assistant,Version 11.2.0.3.0 Production
Enter the followinginformation:
Database SID: woo
Database Control is alreadyconfigured for the database woo
You have chosen to configureDatabase Control for managing the database woo
This will remove theexisting configuration and the default settings and perform a freshconfiguration
Do you wish to continue?[yes(Y)/no(N)]: y
Listener ORACLE_HOME [/DBSoft/Product/11.2.4/db_1 ]:
Password for SYS user:
Password for DBSNMPuser:
Password for SYSMANuser:
Email address fornotifications (optional):
Outgoing Mail (SMTP) serverfor notifications (optional):
-----------------------------------------------------------------
You have specified thefollowing settings
Database ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
Local hostname................ db01
Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
Listener port number................ 1521
Database SID................ woo
Email address fornotifications ...............
Outgoing Mail (SMTP) serverfor notifications ...............
-----------------------------------------------------------------
Do you wish to continue?[yes(Y)/no(N)]: y
Oct 4, 2014 6:24:49 AMoracle.sysman.emcp.EMConfig perform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_24_04.log.
Oct 4, 2014 6:24:50 AMoracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping DatabaseControl (this may take a while) ...
Oct 4, 2014 6:24:54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploadingconfiguration data to EM repository (this may take a while) ...
Oct 4, 2014 6:25:53 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configurationdata successfully
Oct 4, 2014 6:25:57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing DatabaseControl (this may take a while) ...
Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Controlsecured successfully.
Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting DatabaseControl (this may take a while) ...
Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Controlstarted successfully
Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO:>>>>>>>>>>> The Database Control URL ishttps://db01:5500/em <<<<<<<<<<<
Oct 4, 2014 6:26:25 AMoracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
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.
***********************************************************
Enterprise Managerconfiguration completed successfully
FINISHED EMCA at Oct 4, 20146:26:25 AM
[oracle@db01 /]$

17、至此，資料庫已經升級完成。

探索Oracle之資料庫升級二 11.2.0.3升級到11.2.0.4完整步驟

相關文章