oracle11.0.4,Oracle 11.2.0.1升级到11.2.0.4.171017-CSDN博客

本文以

和Readme

为主轴

整理而成，因为笔者水平有限，文中如有不准确之处请包涵，如转载请注明原文出处:

http://blog.itpub.net/25583515/

環境：

項目

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

．升級

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$

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$

source ~/.bash_profile

1.2

解壓

11.2.0.4

安裝包后

修改

cvu_config,

執行

runInstaller:

testdb$

unzip p13390677_112040_Linux-x86-64_1of7.zip

testdb$

unzip p13390677_112040_Linux-x86-64_2of7.zip

testdb$

vi database/stage/cvu/cv/admin/cvu_config

CV_ASSUME_DISTID=OEL6 ---這行由OEL4改為OEL6

testdb$

./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]:

Copying oraenv to /usr/local/bin ...

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

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

．

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

不選擇配置

升級完成后工作：

確認Oracle軟件版本：

testdb$

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$

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

升級

Oracle 11.2.0.4

軟件到

PSU 11.2.0.4.171017

停庫，停監聽：

SQL>

shutdown immediate

lsnrctl stop

解壓PSU 11.2.0.4.171017

testdb$

unzip p26392168_112040_Linux-x86-64.zip

安裝較新版本

Opatch

工具

testdb$

unzip p6880880_112000_Linux-x86-64.zip

testdb$

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$

cd $ORACLE_HOME

testdb$

mv OPatch OPatch_bak

testdb$

mv /data/packages/11.2.0.4/OPatch .

testdb$

opatch version

OPatch Version: 11.2.0.3.6

OPatch succeeded.

testdb$

cd 26392168/

執行

opatch apply

升級

testdb$

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]

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

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]

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.

升級數據庫到

PSU 11.2.0.4.171017

testdb$

cd /u02/product/oracle/rdbms/admin/

testdb$

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

-------------------

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

---------------------------

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$

lsnrctl start