收到客戶要求要apply 最新的12.2 DB Critical Patch Update 需求(2018.4)

*建議在Base Score 7.5以上就要安裝最新的修補更新(Critical Patch Update)

http://www.oracle.com/technetwork/security-advisory/cpuapr2018-3678067.html

CVE#	Component	Package and/or Privilege Required	Protocol	Remote Exploit without Auth.?	CVSS VERSION 3.0 RISK (see Risk Matrix Definitions)									Supported Versions Affected	Notes
					Base Score	Attack Vector	Attack Complex	Privs Req'd	User Interact	Scope	Confid- entiality	Inte- grity	Avail- ability
CVE-2018-2841	Java VM	Create Session, Create Procedure	Multiple	No	8.5	Network	High	Low	None	Changed	High	High	High	11.2.0.4, 12.1.0.2, 12.2.0.1, 18.1.0.0

  

ref: http://oraniltips.blogspot.tw/2016/05/patching-using-datapatch-from-12c.html

先下載兩個patch

1.Patch 6880880: OPatch patch of version 12.2.0.1.13 for Oracle software releases DB 12.2.0.x and DB 18.x (APR 2018)

2.27726453: COMBO OF OJVM RU COMPONENT 12.2.0.1.180417 + DBRU 12.2.0.1.180417

現在上patch看起來簡便多了 , 
以前在11g上完opatch 還要再跑 @postinstall.sql 、@?/rdbms/admin/utlrp.sql 兩支 scripts
27726453解開後, 會看到兩個資料夾27245613, 27674384 , 特別要注意的地方是OVJM patch 要進入到startup upgrade模式進行

 --For OVJM, need to start the database with STARTUP UPGRADE.

--27245613

unzip p27245613_<version>_<platform>.zip

cd 27245613

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

27245613]$ $ORACLE_HOME/OPatch/opatch apply

SQL>startup upgrade

再cd $ORACLE_HOME/OPatch ,

 執行./datapatch -verbose

--27674384

--If the OVJM patch is not applied, you can do normal STARTUP

unzip p27674384_<version>_<platform>.zip

cd 27674384

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

27674384]$ $ORACLE_HOME/OPatch/opatch apply

SQL>startup

再cd $ORACLE_HOME/OPatch ,

 執行./datapatch -verbose

*由於是RAC環境對instance上patch的時候可以只啟動一個instance, 
但記得兩個DB_HOME都要apply patch,
服務恢復的時候要把cluster_database參數設回來成為true, 才能同時啟動RAC的兩個instance.

*針對12c CDB/PDB環境該如何上patch請詳閱readme.html ,

Starting the Database for Post Installation Steps in an Oracle RAC Environment

On only one node, perform the following steps to start an Oracle RAC database in startup upgrade mode.

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> alter system set cluster_database=false scope=spfile;

$ORACLE_HOME/bin/srvctl stop database -d <dbname>

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP UPGRADE

Restarting the Database after Running the Post Installation Steps in an Oracle RAC Environment

To start the database back in normal mode

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> alter system set cluster_database=true scope=spfile;

SQL> SHUTDOWN

$ORACLE_HOME/bin/srvctl start database -d <dbname>

最後檢查上完patch之後的結果

Check for the list of patches applied to the database.

SQL> select action_time, patch_id, patch_uid, version, status, bundle_series, description from dba_registry_sqlpatch;

or

SQL> !$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'DATABASE PSU|DATABASE PATCH SET UPDATE'

ref:

https://blog.pythian.com/step-by-step-jan-2016-psu-patch/

https://blog.pythian.com/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/


查有沒安裝Oracle JAVA Virtual Machine  http://www.oratable.com/how-to-check-if-jvm-is-installed-in-oracle/

--沒安裝OJVM 的output結果
SQL>  select * from all_registry_banners;

BANNER
--------------------------------------------------------------------------------
Oracle Database Catalog Views Release 12.2.0.1.0 - 64bit Production
Oracle Database Packages and Types Release 12.2.0.1.0 - Production
Oracle XML Database Version 12.2.0.1.0 - Production
Oracle Workspace Manager Release 12.2.0.1.0 - Production
JServer JAVA Virtual Machine Release 12.2.0.1.0 - Production
Oracle XDK Release 12.2.0.1.0 - Production
Oracle Database Java Packages Release 12.2.0.1.0 - Production
Oracle Text Release 12.2.0.1.0 - Production
Oracle Multimedia Release 12.2.0.1.0 - Production
OLAP Analytic Workspace Release 12.2.0.1.0 - Production
Oracle OLAP API Release 12.2.0.1.0 - Production

BANNER
--------------------------------------------------------------------------------
Spatial Release 12.2.0.1.0 - Production
Oracle Label Security Release 12.2.0.1.0 - Production
Oracle Application Express Release 4.2.5.00.08 - Production
Oracle Database Vault Release 12.2.0.1.0 - Production

15 rows selected.

--有安裝OJVM 的output結果
BANNER
-------------------------------------------------------------------
JServer JAVA Virtual Machine Release 12.2.0.1.0 - Production
Oracle Database Packages and Types Release 12.2.0.1.0 - Production

SQL> select * from v$option where parameter = 'Java';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
Java
TRUE                                                                   0

最後發現客戶端沒有安裝JVM, 所以不須上OJVM的patch (patch number 27245613). 可是還需要上DBRU 12.2.0.1.180417  (patch number 27674384)

心得：
以前上patch 超麻煩 現在簡單多了
opatch apply 沒加-local , 還會自動幫你把remote 節點也上patch
甚至還會上失敗

-
P.S.
如果沒安裝OJVM要如何安裝相關JVM套件, 可以參考以下兩篇文章：

How to Reload the JVM in 12.1.0.x (Doc ID 1612279.1)
How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)

如果有使用Spatial, Multimedia and OLAP and Oracle Warehouse Builder (OWBSYS)
則代表有使用OJVM, 如果Reload完JVM, 記得reinstall 這些套件.

ref:

Java in the database – OJVM non-rolling patches – OJVM Removal

https://mikedietrichde.com/2015/06/22/java-in-the-database-ojvm-non-rolling-patches/

How to Check if the Oracle JVM is Installed Correctly in the Database
http://serdarturgut.blogspot.tw/2010/06/how-to-check-if-oracle-jvm-is-installed.html

How to roll back (Revert) Oracle 12c database patch (PSU) – (de-install PSU)https://geodatamaster.com/2015/10/04/how-to-roll-back-revert-oracle-12c-database-patch-psu-de-install-psu/

lsnrctl stop LISTENER_TESTDB

sqlplus '/as sysdba'

SQL> shutdown immediate

**** Then run the Opatch utility to roll back the applied patch:

cd /app/oracle/downloaded_patch/july2015/20831110

opatch rollback -id 20831110

You should receive message similar “OPatch completed successfully”

If you have warning then you can check the logs in the directory specified in the output.

Now the Post De-instillation will take place:
sqlplus '/as sysdba’

startup

cd $ORACLE_HOME/OPatch

./datapatch –verbose

*** To verify that the patch is rolled back successfully:

select * from dba_registry_sqlpatch where PATCH_ID=20831110;

My recommendation is to use utilrip after that  to re-compile all database objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql