本文以和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
升級後	Redhat Linux 6.6 x86	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

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

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

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 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

Oracle 11.2.0.1升級到11.2.0.4.171017

相關文章