本文以
Upgrade Guide
和Readme
为主轴
整理而成，因为笔者水平有限，文中如有不准确之处请包涵，如转载请注明原文出处:
http://blog.itpub.net/25583515/
環境：
| 項目 | OS | DB版本 | Oracle Base&Oracle Home |
| 升級前 |
Redhat Linux 6.6
x86 |
Oracle 11.2.0.1 |
/u01/product
/u01/product/oracle |
| 升級后 | Oracle 11.2.0.4.171017 |
/u02/product
/u02/product/oracle |
PS:为什么会有11.2.0.4.17017这样的PSU呢？请看 http://blog.itpub.net/25583515/viewspace-2147615/
步驟：
1.升級Oracle 11.2.0.1軟件到11.2.0.4
(升級後：ORACLE_BASE= /u02/product ORACLE_HOME=/u02/product/oracle)
2.DBUA升級數據庫(runInstaller自動引導出的界面)
3.升級Oracle 11.2.0.4軟件到PSU 11.2.0.4.171017
4.升級數據庫到PSU 11.2.0.4.171017
1 ．升級 Oracle 11.2.0.1 軟件到 11.2.0.4
1.1 升級前準備工作：
1>確認沒有物化視圖正在刷新(也建議升級期間停止刷新工作):
SQL> SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;
2>確認沒有datafile需要Media Recovery
SQL> SELECT * FROM v$recover_file;
3>確認沒有datafile在begin backup模式
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
4>確認沒有分佈式事務，如果有執行下面清理purge_lost_db_entry(因為這裡是先執行關庫再使用runinstall自動帶出的DBUA升級所以此步也可以不要)
SQL> SELECT * FROM dba_2pc_pending;
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('local_tran_id');
SQL> COMMIT;
5>回收站沒有對象，如果回收站有對象，請先清理
SQL> PURGE dba_recyclebin
6>如果有設置event, _trace_event參數建議檢查下，升級過程中參數會保留，建议
7>爲了升級速度更快，升級前收集數據字典統計信息
SQL> exec dbms_stats.gather_dictionary_stats;
8>查詢并記錄升級前失效對象和job，升級后可對比
SQL> select * from dba_jobs
where BROKEN<>'N';
SQL> select * from dba_objects
where status='INVALID' and OBJECT_TYPE<>'SYNONYM';
9>運行升級檢查脚本(Pre-Upgrade Information Tool)
SQL> SPOOL upgrade_info.log
SQL> @utlu112i.sql
--可从其它装有11.2.0.4DB目录
ORACLE_HOME/rdbms/admin中copy一个到本地DB中运行
SQL> SPOOL OFF
10>升級過程中，停掉所有job及OS cron之類
alter system set job_queue_processes=0 scope=both
11>停庫，停監聽
SQL> shutdown immediate
$ lsnrctl stop
12>修改環境變量：
testdb<*orcl*/data/packages/11.2.0.4>$ vi ~/.bash_profile
#ORACLE_BASE=/u01/product;export ORACLE_BASE
#ORACLE_HOME=/u01/product/oracle;export ORACLE_HOME
ORACLE_BASE=/u02/product;export ORACLE_BASE
ORACLE_HOME=/u02/product/oracle;export ORACLE_HOME
testdb<*orcl*/data/packages/11.2.0.4>$ source ~/.bash_profile
1.2 解壓 11.2.0.4 安裝包后 , 修改 cvu_config, 執行 runInstaller:
testdb<*orcl*/data/packages/11.2.0.4>$ unzip p13390677_112040_Linux-x86-64_1of7.zip
testdb<*orcl*/data/packages/11.2.0.4>$ unzip p13390677_112040_Linux-x86-64_2of7.zip
testdb<*orcl*/data/packages/11.2.0.4>$ vi database/stage/cvu/cv/admin/cvu_config
CV_ASSUME_DISTID=OEL6 ---這行由OEL4改為OEL6
testdb<*orcl*/data/packages/11.2.0.4/database>$ ./runInstaller
選擇Upgrade an existing database，
也可以選擇install database software，後面手動執行DBCA升級
選擇所有語言
注意選擇新的Oracle Base, Software Location(Oracle Home)位置
使用root 登錄執行
[root@testdb ~]#
/u02/product/oracle/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/product/oracle
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed 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.
執行完成，圖形頁面點擊OK后會自動引導出DBUA
2 ． DBUA 升級數據庫
勾選Perform typical configure
選擇
Recompile invaild objects at the end of upgrade
Degree of parallelism:適當增加并行度可以減少重新編譯時間
不選擇更新 Timezone version and TIMESTAMP WITH TIME ZONE Data
不選擇 backup datbase，如果選擇話DBUA會以shutdown冷備份不壓縮datafile copy文件到指定目錄
選擇 Do Not Move Database Files as Part of Upgrade
不選擇配置
EM
升級完成后工作：
確認Oracle軟件版本：
testdb<*orcl*/home/oracle>$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u02/product/oracle
Central Inventory : /u01/oraInventory --注意:因為最初 Oracle 11.2.0.1安裝時Inventory目錄設 為了/u01/oraInventory，它下面記錄了Oracle 軟件 所有安裝的 組件升級相關的信息，固/u01/oraInventory 不 能刪除，否則 DB升級相關、opatch工具都不能使用
from : /u02/product/oracle/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u02/product/oracle/cfgtoollogs/opatch/opatch2018-01-18_13-34-43PM_1.log
Lsinventory Output file location : /u02/product/oracle/cfgtoollogs/opatch/lsinv/lsinventory2018-01-18_13-34-43PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
testdb<*orcl*/home/oracle>$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 13:41:55 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
確認數據庫升級信息：
set pages 2000 lines 500
col action format a17
col namespace format a10
col version format a10
col comments format a30
col action_time format a30
col bundle_series format a15
SQL> select * from DBA_REGISTRY_HISTORY;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
------------------------------ ----------------- ---------- ---------- ---------- --------------- ------------------------------
15-JAN-18 05.33.17.373012 PM VIEW INVALIDATE 8289601 view invalidation
15-JAN-18 05.33.17.631418 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.1.0
15-JAN-18 05.34.08.090497 PM APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
確認數據庫版本信息：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
確認數據庫各組件信息：
SQL> SELECT COMP_NAME, VERSION, STATUS,MODIFIED FROM SYS.DBA_REGISTRY;
OWB 11.2.0.1.0 VALID 15-JAN-2018 17:37:36
Oracle Application Express 3.2.1.00.10 VALID 15-JAN-2018 17:37:36
Oracle Enterprise Manager 11.2.0.4.0 VALID 15-JAN-2018 17:12:55
OLAP Catalog 11.2.0.4.0 VALID 15-JAN-2018 17:37:35
Spatial 11.2.0.4.0 VALID 15-JAN-2018 17:37:35
Oracle Multimedia 11.2.0.4.0 VALID 15-JAN-2018 17:37:31
Oracle XML Database 11.2.0.4.0 VALID 15-JAN-2018 17:37:30
Oracle Text 11.2.0.4.0 VALID 15-JAN-2018 17:37:27
Oracle Expression Filter 11.2.0.4.0 VALID 15-JAN-2018 17:37:27
Oracle Rules Manager 11.2.0.4.0 VALID 15-JAN-2018 17:37:31
Oracle Workspace Manager 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
Oracle Database Catalog Views 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
Oracle Database Packages and Types 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
JServer JAVA Virtual Machine 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
Oracle XDK 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
Oracle Database Java Packages 11.2.0.4.0 VALID 15-JAN-2018 17:37:26
OLAP Analytic Workspace 11.2.0.4.0 VALID 15-JAN-2018 17:37:32
Oracle OLAP API 11.2.0.4.0 VALID 15-JAN-2018 17:37:33
18 rows selected.
修改compatible參數至11.2.0.4.0
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1085640704 bytes
Fixed Size 2252424 bytes
Variable Size 788529528 bytes
Database Buffers 285212672 bytes
Redo Buffers 9646080 bytes
Database mounted.
Database opened.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
3. 升級 Oracle 11.2.0.4 軟件到 PSU 11.2.0.4.171017
停庫，停監聽：
SQL> shutdown immediate
$ lsnrctl stop
解壓PSU 11.2.0.4.171017
testdb<*orcl*/data/packages/11.2.0.4>$ unzip p26392168_112040_Linux-x86-64.zip
安裝較新版本 Opatch 工具
testdb<*orcl*/data/packages/11.2.0.4>$ unzip p6880880_112000_Linux-x86-64.zip
testdb<*orcl*/data/packages/11.2.0.4>$ ll
total 2649692
drwxr-xr-x 18 oracle dba 4096 Oct 6 14:55 26392168
drwxr-xr-x 7 oracle dba 4096 Aug 27 2013 database
drwxr-xr-x 8 oracle dba 4096 Dec 14 2013 OPatch
-rwxr-xr-x 1 oracle dba 1395582860 Jan 15 14:25 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-xr-x 1 oracle dba 1151304589 Jan 15 14:27 p13390677_112040_Linux-x86-64_2of7.zip
-rw-r--r-- 1 oracle dba 133259658 Jan 15 14:28 p26392168_112040_Linux-x86-64.zip
-rwxr-xr-x 1 oracle dba 33020933 Jan 15 14:28 p6880880_112000_Linux-x86-64.zip
-rw-rw-r-- 1 oracle dba 84972 Oct 17 19:54 PatchSearch.xml
testdb<*orcl*/data/packages/11.2.0.4>$ cd $ORACLE_HOME
testdb<*orcl*/u02/product/oracle>$ mv OPatch OPatch_bak
testdb<*orcl*/u02/product/oracle>$ mv /data/packages/11.2.0.4/OPatch .
testdb<*orcl*/u02/product/oracle>$ opatch version
OPatch Version: 11.2.0.3.6
OPatch succeeded.
testdb<*orcl*/data/packages/11.2.0.4>$ cd 26392168/
執行 opatch apply 升級
testdb<*orcl*/data/packages/11.2.0.4/26392168>$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u02/product/oracle
Central Inventory : /u01/oraInventory
from : /u02/product/oracle/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /u02/product/oracle/cfgtoollogs/opatch/opatch2018-01-19_11-20-37AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075 25869727 26609445 26392168
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u02/product/oracle')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/u02/product/oracle'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
………….
Log file location: /u02/product/oracle/cfgtoollogs/opatch/opatch2018-01-19_11-20-37AM_1.log
OPatch succeeded.
4. 升級數據庫到 PSU 11.2.0.4.171017
testdb<*orcl*/data/packages/11.2.0.4/26392168>$ cd /u02/product/oracle/rdbms/admin/
testdb<*orcl*/u02/product/oracle/rdbms/admin>$ sqlplus '/as sysdba'
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
裝載被修改的 sql 文件到數據庫
SQL> @catbundle.sql psu apply
Check the following log file for errors:
/u02/product/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2018Jan19_15_56_56.log
編譯失效對象
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-01-19 16:09:41
1 row selected.
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 2018-01-19 16:09:46
1 row selected.
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
1 row selected.
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
1 row selected.
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DBA_REGISTRY_HISTORY
2018/1/15 17:33:17.373012 VIEW INVALIDATE 8289601 view invalidation
2018/1/15 17:33:17.631418 UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.1.0
2018/1/15 17:34:08.090497 APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
2018/1/19 15:59:21.283170 APPLY SERVER 11.2.0.4 171017 PSU PSU 11.2.0.4.171017
這時，可開啓升級前禁用的job_queue_processes等參數設定
注：Readme文檔中還有一步針對Oracle JVM升級的緩解補丁步驟
This patch now includes the OJVM Mitigation patch (Patch:19721304). If an OJVM PSU is installed or planned to be installed, no further actions are necessary. Otherwise, the workaround of using the OJVM Mitigation patch can be activated. As SYSDBA do the following from the admin directory:
SQL > @dbmsjdev.sql
SQL > exec dbms_java_dev.disable
這裡我沒有執行，具體OJVM Mitigation patch說明可見：
http://blog.itpub.net/25583515/viewspace-2150335
至此，Oracle 11.2.0.1 升級到11.2.0.4.171017完成
最後開啟監聽：
testdb<*orcl*/home/oracle>$ lsnrctl start