上篇中我們完成了Oracle軟體的升級，下面我們進行補丁包安裝和資料庫升級。

4、使用OPatch打補丁

補丁11724962是需要使用OPatch進行打補丁動作。先將壓縮包解壓。

[oracle@SimpleLinux upload]$ ls -l

total 4348

drwxr-xr-x 5 oracle oinstall 4096 Mar 14 2011 11724962

-rw-r--r-- 1 oracle oinstall 4278863 May 13 11:27 p11724962_10205_LINUX.zip

-rwxrwxrwx 1 oracle oinstall 165290 Jul 20 2010 README.html

直接使用當前的OPatch進行apply動作。

[oracle@SimpleLinux 11724962]$ /u01/app/oracle/product/10.2.0/db_1/OPatch/opatch apply

Invoking OPatch 10.2.0.4.9

（篇幅原因，有省略……）

ApplySession applying interim patch '11724962' to OH '/u01/app/oracle/product/10.2.0/db_1'

ApplySession failed: ApplySession failed to prepare the system.

Patch 11724962 requires OPatch version 10.2.0.5.0.

The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.

System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

補丁要求OPatch版本為10.2.0.5，但是當前OPatch只有10.2.0.4.9。報錯不匹配。注意：我們使用OPatch打補丁的時候，經常會遇到版本不匹配的情況。所以，經常需要我們到MOS上下載對應Oracle軟體版本的最新版OPatch。

我們首先備份當前的OPatch包。

[oracle@SimpleLinux bin]$ cd /u01/app/oracle/product/10.2.0/db_1/OPatch/

[oracle@SimpleLinux OPatch]$ ./opatch version

Invoking OPatch 10.2.0.4.9

OPatch Version: 10.2.0.4.9

OPatch succeeded.

[oracle@SimpleLinux db_1]$ tar zcvf opatch_bk.tar OPatch

OPatch/

OPatch/opatchprereqs/

OPatch/opatchprereqs/prerequisite.properties

（篇幅原因，有省略……）

[oracle@SimpleLinux db_1]$ ls -l | grep opatch

-rw-r--r-- 1 oracle oinstall 1467463 May 13 12:33 opatch_bk.tar

從MOS下載針對10g的最新版本OPatch，上傳到目錄中。

[oracle@SimpleLinux upload]$ ls -l

total 31120

drwxr-xr-x 5 oracle oinstall 4096 Mar 14 2011 11724962

-rw-r--r-- 1 oracle oinstall 4278863 May 13 11:27 p11724962_10205_LINUX.zip

-rw-r--r-- 1 root root 27412455 May 13 12:49 p6880880_102000_LINUX.zip

-rwxrwxrwx 1 oracle oinstall 165290 Jul 20 2010 README.html

[oracle@SimpleLinux upload]$ unzip p6880880_102000_LINUX.zip -d $ORACLE_HOME

Archive: p6880880_102000_LINUX.zip

extracting: /u01/app/oracle/product/10.2.0/db_1/OPatch/ocm/ocm.zip

inflating: /u01/app/oracle/product/10.2.0/db_1/OPatch/ocm/lib/osdt_jce.jar

inflating: /u01/app/oracle/product/10.2.0/db_1/OPatch/ocm/lib/osdt_core3.jar

inflating: /u01/app/oracle/product/10.2.0/db_1/OPatch/ocm/lib/emocmclnt-14.jar

(......)

重新進行補丁操作。

[oracle@SimpleLinux 11724962]$ /u01/app/oracle/product/10.2.0/db_1/OPatch/opatch apply

Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1

Oracle Home : /u01/app/oracle/product/10.2.0/db_1

Central Inventory : /u01/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 10.2.0.5.1

OUI version : 10.2.0.5.0

OUI location : /u01/app/oracle/product/10.2.0/db_1/oui

Verifying the update...

Inventory check OK: Patch ID 11724962 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 11724962 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.

驗證補丁正確性。

[oracle@SimpleLinux OPatch]$ ./opatch lsinventory

Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1

Oracle Home : /u01/app/oracle/product/10.2.0/db_1

Central Inventory : /u01/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 10.2.0.5.1

OUI version : 10.2.0.5.0

OUI location : /u01/app/oracle/product/10.2.0/db_1/oui

Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-05-13_12-55-24PM.log

Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-05-13_12-55-24PM.txt

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

Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0a

There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch 11724962 : applied on Tue May 13 12:54:00 CST 2014

Unique Patch ID: 13426771

Created on 13 Mar 2011, 21:24:09 hrs PST8PDT

Bugs fixed:

（篇幅原因，有省略……）

OPatch succeeded.

5、資料庫升級

最後就是對現有資料庫ora11g的升級動作。進行資料庫升級有兩個方法，dbua圖形化方法和手工指令碼執行方法。dbua方法比較簡單，但是在一些早期版本中問題比較多。手工指令碼雖然麻煩，但是診斷錯誤更加方便。筆者選擇手工指令碼方法。

首先需要將資料庫以upgrade模式進行啟動。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 1273080 bytes

Variable Size 83886856 bytes

Database Buffers 163577856 bytes

Redo Buffers 2920448 bytes

Database mounted.

Database opened.

執行指令碼（在伺服器端！！）catupgrd.sql，升級資料字典物件。

SQL> @?/rdbms/admin/catupgrd.sql

（長時間等待）

DOC>#######################################################################

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

DOC> any errors in the spool log file. If there are errors in the spool

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#

在執行過程中，很多資料庫字典物件都是被重建，所以容易出現物件失效的情況。所以Oracle建議在之後重啟資料庫，進行失效物件重新編譯動作。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 1273080 bytes

Variable Size 121635592 bytes

Database Buffers 125829120 bytes

Redo Buffers 2920448 bytes

Database mounted.

Database opened.

執行指令碼：

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2014-05-13 13:27:54

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

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

在提示資訊中，也介紹了一些檢視過程進展的方法。最後，我們驗證資料庫升級情況：

[oracle@SimpleLinux OPatch]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 13 13:29:30 2014

SQL> conn / as sysdba

Connected.

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 – Production

各個資料庫元件升級成功，並且狀態均為valid。

SQL> select comp_name, version, status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 10.2.0.5.0 VALID

Oracle Database Packages and Types 10.2.0.5.0 VALID

Oracle Workspace Manager 10.2.0.5.0 VALID

JServer JAVA Virtual Machine 10.2.0.5.0 VALID

（篇幅原因，有省略……）

Oracle Enterprise Manager 10.2.0.5.0 VALID

17 rows selected

db time zone資訊也能顯示正常。

SQL> select START_DATE from dba_scheduler_jobs;

START_DATE

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

01-7月 -05 03.00.00.800000 上午 AMERICA/LOS_ANGELES

13-5月 -14 02.24.49.000000 下午 +08:00

13-5月 -14 01.24.31.629677 下午 +08:00

13-5月 -14 01.09.56.713160 下午 +08:00

8 rows selected

升級成功！

6、結論

Oracle升級，特別是10g版本升級，是我們進行部署時候經常遇到的問題。隨著版本升級過程，一些不相容問題可能都會出現。充分的測試實驗加合理穩妥的方案規劃，是保證我們在生產系統中不出問題、少出問題的法寶。

從10.2.0.1升級到10.2.0.5操作實驗（下）

相關文章