單例項環境下Oracle 11.2.0.3升級到11.2.0.4的過程
本文是單例項環境下Oracle 11.2.0.3升級到11.2.0.4的過程，先升級Database Software，再升級Oracle Instance。
Oracle 11.2.0.4的Patchset No:13390677，可以直接從Oracle Support下載到。
# 後跟命令表示以作業系統下root使用者操作;
$ 後跟命令表示以作業系統下oracle使用者操作;
升級前準備：
1、檢視資料庫相關資訊:
SQL> select name from v$database;
NAME
------------------
ORA11G
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2、備份資料庫
可以參考該博文進行資料庫備份：資料庫備份http://blog.itpub.net/29439655/viewspace-1988549/
###停止資料庫
SQL> shutdown immediate
###停止監聽
SQL> host lsnrctl stop
###停止EM
SQL> host emctl stopdbconsole
###檢視oracle程式，檢查是否已經停止完畢
# ps -ef|grepora
備份老的ORACLE_HOME和oraInventory
#tar –cvf product.zip /u01/app/oracle/product/
#tar -cvf oraInventory.zip /u01/app/oraInventory/
3、安裝資料庫軟體
目標版本是11.2.0.4，為目標版本建立相應的主目錄。
$ mkdir -p /u01/app/oracle/product/11.2.0.4/dbhome_1
將安裝介質上傳伺服器，並解壓。
通過命令列的方式安裝資料庫軟體。可以直接參考博文：http://blog.itpub.net/29439655/viewspace-1988530/
生成響應檔案模板，將database/response資料夾下的db_install.rsp檔案複製一份出來，重新命名為dbupg.rsp，對裡面的引數進行修改。
$ vi /home/oracle/dbupg.rsp
#--------------------------------------------------------------------
#以下引數根據實際情況更改,一般也無需更改
oracle.install.option=UPGRADE_DB
ORACLE_HOSTNAME=dbbackup
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
DECLINE_SECURITY_UPDATES=true
$ ./runInstaller -silent -noconfig -ignorePrereq -responseFile /home/oracle/dbupg.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 248510 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-14_05-20-01PM. Please wait ...[oracle@dbbackup database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2016-02-14_05-20-01PM.log
The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2016-02-14_05-20-01PM.log' for more details.
As a root user, execute the following script(s):
1. /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Successfully Setup Software.
##########################################################
Start Database Upgrade Assistant to upgrade the database.
##########################################################
# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_dbbackup_2016-02-14_17-24-19.log for the output of root script
$ cp -r /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/* /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
6、升級前環境檢查
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 14 17:39:58 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 603980840 bytes
Database Buffers 1879048192 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-15-2016 10:06:04
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORA11G
--> version: 11.2.0.3.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 619 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: ON
**********************************************************************
FlashbackInfo:
--> name: /u01/app/oracle/fast_recovery_area
--> limit: 4122 MB
--> used: 1166 MB
--> size: 4122 MB
--> reclaim: 0 MB
--> files: 4
WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recover
y areas before performing an upgrade.
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: 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 Parameters:
-- 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 required
.
**********************************************************************
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] VALID
--> 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 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: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events 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' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
7、執行升級操作
SQL> set echo on
SQL> spool /home/oracle/upgrade.log
SQL> set time on;
10:10:37 SQL> @?/rdbms/admin/catupgrd.sql
.....
Oracle Database 11.2 Post-Upgrade Status Tool 02-15-2016 10:43:10
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:10:01
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:03:10
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:29
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:55
OLAP Catalog
. VALID 11.2.0.4.0 00:00:42
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:17
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:05:42
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:47
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:09
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:36
Spatial
. VALID 11.2.0.4.0 00:04:08
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:06
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:06
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:30:05
PL/SQL procedure successfully completed.
10:43:10 SQL>
10:43:10 SQL> SET SERVEROUTPUT OFF
10:43:10 SQL> SET VERIFY ON
10:43:10 SQL> commit;
Commit complete.
10:43:10 SQL>
10:43:10 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
10:43:20 SQL>
10:43:20 SQL>
10:43:20 SQL> DOC
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>
10:43:20 DOC> The above sql script is the final step of the upgrade. Please
10:43:20 DOC> review any errors in the spool log file. If there are any errors in
10:43:20 DOC> the spool file, consult the Oracle Database Upgrade Guide for
10:43:20 DOC> troubleshooting recommendations.
10:43:20 DOC>
10:43:20 DOC> Next restart for normal operation, and then run utlrp.sql to
10:43:20 DOC> recompile any invalid application objects.
10:43:20 DOC>
10:43:20 DOC> If the source database had an older time zone version prior to
10:43:20 DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
10:43:20 DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
10:43:20 DOC> with Oracle.
10:43:20 DOC>
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>#
10:43:20 SQL>
10:43:20 SQL> Rem Set errorlogging off
10:43:20 SQL> SET ERRORLOGGING OFF;
10:43:20 SQL>
10:43:20 SQL> REM END OF CATUPGRD.SQL
10:43:20 SQL>
10:43:20 SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
10:43:20 SQL> REM This forces user to start a new sqlplus session in order
10:43:20 SQL> REM to connect to the upgraded db.
10:43:20 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
以上catupgrd.sql指令碼執行了40分鐘左右，執行完之後會shutdown immediate資料庫。
8、執行utlrp.sql指令碼編譯失效物件
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 10:46:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 687866920 bytes
Database Buffers 1795162112 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-02-15 10:46:51
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is 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 a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and 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 are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-02-15 10:47:28
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
9、至此資料庫已經升級完成，檢視各元件版本號：
SQL> col version format a20
SQL> col comp_name format a40
SQL> col status format a10
SQL> select comp_name,status,version from dba_server_registry;
COMP_NAME STATUS VERSION
---------------------------------------- ---------- --------------------
OWB VALID 11.2.0.3.0
Oracle Application Express VALID 3.2.1.00.12
Oracle Enterprise Manager 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 Expression Filter VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages 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.
10、檢查無效物件
SQL> select * from dba_objects where status !='VALID';
no rows selected
11、啟動監聽
$ lsnrctl start
$ lsnrctl status
12、升級成功後刪除原來的目錄，通過EMCA重建EM
[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
手工建立EM資料庫:
####emca -repos drop
[oracle@db01 /]$ emca -reposdrop
####emca -repos create
[oracle@db01 /]$ emca -reposcreate
###emca -config dbcontrol db
[oracle@db01 /]$ emca-config dbcontrol db
至此，升級已經全部完成。
參考博文：http://blog.csdn.net/wuweilong/article/details/41627189
Oracle 11.2.0.4的Patchset No:13390677，可以直接從Oracle Support下載到。
# 後跟命令表示以作業系統下root使用者操作;
$ 後跟命令表示以作業系統下oracle使用者操作;
升級前準備：
1、檢視資料庫相關資訊:
SQL> select name from v$database;
NAME
------------------
ORA11G
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2、備份資料庫
可以參考該博文進行資料庫備份：資料庫備份http://blog.itpub.net/29439655/viewspace-1988549/
###停止資料庫
SQL> shutdown immediate
###停止監聽
SQL> host lsnrctl stop
###停止EM
SQL> host emctl stopdbconsole
###檢視oracle程式，檢查是否已經停止完畢
# ps -ef|grepora
備份老的ORACLE_HOME和oraInventory
#tar –cvf product.zip /u01/app/oracle/product/
#tar -cvf oraInventory.zip /u01/app/oraInventory/
3、安裝資料庫軟體
目標版本是11.2.0.4，為目標版本建立相應的主目錄。
$ mkdir -p /u01/app/oracle/product/11.2.0.4/dbhome_1
將安裝介質上傳伺服器，並解壓。
通過命令列的方式安裝資料庫軟體。可以直接參考博文：http://blog.itpub.net/29439655/viewspace-1988530/
生成響應檔案模板，將database/response資料夾下的db_install.rsp檔案複製一份出來，重新命名為dbupg.rsp，對裡面的引數進行修改。
$ vi /home/oracle/dbupg.rsp
#--------------------------------------------------------------------
#以下引數根據實際情況更改,一般也無需更改
oracle.install.option=UPGRADE_DB
ORACLE_HOSTNAME=dbbackup
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
DECLINE_SECURITY_UPDATES=true
$ ./runInstaller -silent -noconfig -ignorePrereq -responseFile /home/oracle/dbupg.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 248510 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-14_05-20-01PM. Please wait ...[oracle@dbbackup database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2016-02-14_05-20-01PM.log
The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2016-02-14_05-20-01PM.log' for more details.
As a root user, execute the following script(s):
1. /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Successfully Setup Software.
##########################################################
Start Database Upgrade Assistant to upgrade the database.
##########################################################
# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_dbbackup_2016-02-14_17-24-19.log for the output of root script
至此11.2.0.4的軟體就已經裝完了，修改Oracle環境變數
4、修改環境變數及引數
$ vi .bash_profile
-------修改如下行,將11.2.0.3改成11.2.0.4即可
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
$ source .bash_profile
$ vi /etc/oratab
-------修改如下行11.2.3為11.2.4
ora11g:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
5、配置監聽
$ cp /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/* $ORACLE_HOME/dbs4、修改環境變數及引數
$ vi .bash_profile
-------修改如下行,將11.2.0.3改成11.2.0.4即可
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
$ source .bash_profile
$ vi /etc/oratab
-------修改如下行11.2.3為11.2.4
ora11g:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
5、配置監聽
$ cp -r /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/* /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
6、升級前環境檢查
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 14 17:39:58 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 603980840 bytes
Database Buffers 1879048192 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-15-2016 10:06:04
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORA11G
--> version: 11.2.0.3.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 619 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: ON
**********************************************************************
FlashbackInfo:
--> name: /u01/app/oracle/fast_recovery_area
--> limit: 4122 MB
--> used: 1166 MB
--> size: 4122 MB
--> reclaim: 0 MB
--> files: 4
WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recover
y areas before performing an upgrade.
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: 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 Parameters:
-- 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 required
.
**********************************************************************
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] VALID
--> 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 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: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events 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' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
7、執行升級操作
SQL> set echo on
SQL> spool /home/oracle/upgrade.log
SQL> set time on;
10:10:37 SQL> @?/rdbms/admin/catupgrd.sql
.....
Oracle Database 11.2 Post-Upgrade Status Tool 02-15-2016 10:43:10
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:10:01
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:03:10
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:29
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:55
OLAP Catalog
. VALID 11.2.0.4.0 00:00:42
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:17
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:05:42
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:47
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:09
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:36
Spatial
. VALID 11.2.0.4.0 00:04:08
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:06
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:06
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:30:05
PL/SQL procedure successfully completed.
10:43:10 SQL>
10:43:10 SQL> SET SERVEROUTPUT OFF
10:43:10 SQL> SET VERIFY ON
10:43:10 SQL> commit;
Commit complete.
10:43:10 SQL>
10:43:10 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
10:43:20 SQL>
10:43:20 SQL>
10:43:20 SQL> DOC
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>
10:43:20 DOC> The above sql script is the final step of the upgrade. Please
10:43:20 DOC> review any errors in the spool log file. If there are any errors in
10:43:20 DOC> the spool file, consult the Oracle Database Upgrade Guide for
10:43:20 DOC> troubleshooting recommendations.
10:43:20 DOC>
10:43:20 DOC> Next restart for normal operation, and then run utlrp.sql to
10:43:20 DOC> recompile any invalid application objects.
10:43:20 DOC>
10:43:20 DOC> If the source database had an older time zone version prior to
10:43:20 DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
10:43:20 DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
10:43:20 DOC> with Oracle.
10:43:20 DOC>
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>#
10:43:20 SQL>
10:43:20 SQL> Rem Set errorlogging off
10:43:20 SQL> SET ERRORLOGGING OFF;
10:43:20 SQL>
10:43:20 SQL> REM END OF CATUPGRD.SQL
10:43:20 SQL>
10:43:20 SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
10:43:20 SQL> REM This forces user to start a new sqlplus session in order
10:43:20 SQL> REM to connect to the upgraded db.
10:43:20 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
以上catupgrd.sql指令碼執行了40分鐘左右，執行完之後會shutdown immediate資料庫。
8、執行utlrp.sql指令碼編譯失效物件
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 10:46:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 687866920 bytes
Database Buffers 1795162112 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-02-15 10:46:51
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is 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 a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and 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 are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-02-15 10:47:28
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
9、至此資料庫已經升級完成，檢視各元件版本號：
SQL> col version format a20
SQL> col comp_name format a40
SQL> col status format a10
SQL> select comp_name,status,version from dba_server_registry;
COMP_NAME STATUS VERSION
---------------------------------------- ---------- --------------------
OWB VALID 11.2.0.3.0
Oracle Application Express VALID 3.2.1.00.12
Oracle Enterprise Manager 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 Expression Filter VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages 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.
SQL> select * from dba_objects where status !='VALID';
no rows selected
11、啟動監聽
$ lsnrctl start
$ lsnrctl status
12、升級成功後刪除原來的目錄，通過EMCA重建EM
[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
手工建立EM資料庫:
####emca -repos drop
[oracle@db01 /]$ emca -reposdrop
####emca -repos create
[oracle@db01 /]$ emca -reposcreate
###emca -config dbcontrol db
[oracle@db01 /]$ emca-config dbcontrol db
至此，升級已經全部完成。
參考博文：http://blog.csdn.net/wuweilong/article/details/41627189
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29439655/viewspace-1988896/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 記錄一次Oracle 11.2.0.4 RAC異地恢復到單例項Oracle單例
- ORACLE windows和linux環境下 10g升級到11.2.0.4 並安裝11.2.0.4.19補丁OracleWindowsLinux
- ORACLE-LINUX環境字元介面單例項安裝OracleLinux字元單例
- oracle rac 11.2.0.3 升級到11.2.0.4Oracle
- oracle資料庫11.2.0.3升級到11.2.0.4Oracle資料庫
- ORACLE事務和例項恢復過程梳理Oracle
- Spring 原始碼學習 - 單例bean的例項化過程Spring原始碼單例Bean
- ORACLE 資料庫11.2.0.4 單例項伺服器IO等待高問題分析Oracle資料庫單例伺服器
- Win環境至Linux環境Oracle資料庫遷移全過程LinuxOracle資料庫
- Oracle 11g RAC到單例項OGG同步Oracle單例
- JavaScript：從Callback到Async的發展過程——例項演示JavaScript
- Oracle 11.2.0.4 rac for aix acfs異常環境的克隆環境ASM磁碟組掛載緩慢OracleAIASM
- 使用DBUA升級 Oracle 11.2.0.4到Oracle 19C的問題記錄Oracle
- 靜默升級oracle 11g （從11.2.0.1升級到11.2.0.4）Oracle
- Oracle 10.2.0.5升級至11.2.0.4Oracle
- 11.2.0.4單例項ASM安裝報錯ohasd failed to ... line 73.單例ASMAI
- rac恢復到單例項單例
- Oracle：Oracle RAC 11.2.0.4 升級為 19cOracle
- 通過rman為客戶實現linux下oracle11.2.0.4到windows下oracle同版本資料庫的異機恢復。LinuxOracleWindows資料庫
- Oracle RAC 環境下的連線管理Oracle
- oracle 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- 需求過程化分析方法-例項分享
- Spring Bean 的例項化過程原始碼解析SpringBean原始碼
- oracle之 單例項監聽修改埠Oracle單例
- C# 併發控制框架：單執行緒環境下實現每秒百萬級排程C#框架執行緒
- 利用白名單繞過360例項
- Oracle ADG環境下的RMAN備份策略Oracle
- Linux下-LNMP環境搭建部落格網站（全過程）LinuxLNMP網站
- linux環境下安裝mysql8.0過程介紹GMFXLinuxMySql
- Solaris下Oracle RAC 11.2.0.4 安裝方法Oracle
- Spark程式設計環境搭建及WordCount例項Spark程式設計
- 【UP_ORACLE】使用AutoUpgrade工具升級Oracle 11.2.0.4至12.2.0.1Oracle
- 【XTTS】Oracle XTTS V4--Oracle11.2.0.4+ 遷移升級TTSOracle
- Python中類建立和例項化過程Python
- 從Oracle 11.2.0.4 BUG到Oracle子查詢展開分析Oracle
- Kubernetes 無法查詢到並且無法刪除pod例項的排查過程
- Linux環境下如何升級openssl?Linux
- oracle 11g 單例項資料庫的安裝Oracle單例資料庫
- 超級簡單入門vuex 小例項Vue