448
【雲端起舞】Oracle雲上一鍵安裝數據庫補丁集
編輯手記：為數據庫升級打補丁是一項常規的任務，在通常情況下 ，打補丁是一件繁瑣的事情，需要考慮的細節比較多。但在雲上，可以通過按鈕一鍵式應用 相關補丁集，高效便捷。我們今天一起來學習 Oracle Cloud Database Patching Then patch like a king with single click Database As A Service (DbaaS)
係列文章回顧：
1、Configure and Practice Backup and Recovery in Cloud 在Oracle公有雲上備份與恢複的配置和實踐
2、Create a Primary and Standby Database in the Cloud 在Oracle公有雲上創建standby數據庫
3、 Create a database clone in the cloud 在Oracle公有雲上創建克隆數據庫
作者簡介
Joel Perez
Oracle ACE總監， Maximum Availability OCM，OTN 專家，全球第一批ACE稱號獲得者，致力於數據庫高可用、災難恢複、升級遷移和數據複製等方向設計和實現解決方案。
Nassyam Basha
Oracle ACE總監，專注於Oracle高可用技術，印度甲骨文社區成員，定期在Oracle技術活動中進行演講。並在實踐中為客戶提供最佳解決方案。
Introduction簡介
Patching your databases is regular taskwhenever Oracle releases new security patches on quarterly basis and at the endit is customer take depending on the downtime, when you planned for patching ofOn-Premises database then you have to take care of many things manually such asconflicts, OPatch utility version, Patching and post tasks but all these taskswe handle manually and carefully each step. This is numerously challenges aredecreased if your database on cloud. This article explains how easy to performpatching on your database (DBaaS) using single click.
當Oracle每季度發布新的安全修補程序時，及時升級打補丁是常規任務，客戶一般會根據停機時間決定是否應用補丁。當有計劃性地為數據庫安裝補丁集時，必須手動處理許多事情，例如版本衝突，OPatch實用程序版本，修補和一些安裝後的任務，所有這些任務，我們都要手動並且仔細處理。 如果你的數據庫在雲上，安裝的過程會簡化許多。 本文介紹如何輕鬆地通過單擊執行安裝補丁集到數據庫（DBaaS）的過程。
Challengesof patching over On-Premises
計劃性打補丁集麵臨的挑戰
This is the job we are doing so far and ofcourse still, this requires lot of plan and below is the few important ones
目前對於打補丁這項工作，仍然並將一直需要同時處理以下事務：
What patches have been applied?已經打過哪些補丁
What patches we can apply onour database?目標庫能打哪些補丁
Downloading patches 下載補丁集
OPatch utility version 確定Opatch應用程序的版本
One-off patch conflictdetection and resolution 一次性補丁衝突檢測和解析
Installation of patch at OracleHome level 安裝補丁到Oracle目錄
Performing post tasks (loadingmodified SQL) 執行安裝後的程序
Invalid objects compilation 失效對象編譯
These are the above steps if we haveplanned very well and lot of work involved to download the patch and update theOPatch utility version to meet the requirement and many steps. If the oneoracle home shared by the more than one databases then we have to take care ofall the databases to perform post tasks.
對於以上涉及到的步驟我們都要做完整規劃部署並按照要求執行，尤其是Opatch應用程序的版本一定要滿足。如果一個oracle home目錄被多個數據庫共享，那麼必須針對所有數據庫執行post程序。
DBaaS– Cloud patching
DbaaS-雲上打補丁的實現
Now consider customer databases moved fromon-premises to the Oracle cloud then….
假設用戶的數據庫現在遷移到了雲上
Question: How easy to apply patch when database is on cloud?
問：在雲上為數據庫打補丁實現起來真的很簡單嗎？
Guess: Probably we host database in cloud but again at the end itsdatabase, hence have to apply patch like we do on-premises.?
猜測：是不是隻是在雲端托管數據庫，但最終還是在數據庫終端打補丁，因此做法跟普通數據庫應用補丁集並沒有太大區別？
Answer: Single click apply patch will do everything for you within 30minutes of span time for the security patches again it depends.
答：隻需要通過單擊按鈕一鍵式安裝補丁集，根據補丁集的不同，時間會不等，但一般在半個下傲視之內都可以完成。
Howit is possible?
這可能嗎？
Oracle cloud made things easy tointerrelate for all the oracle sources such as My Oracle Support, Special DBaaSMonitoring, and Easy access to the secured Oracle sources and hence OracleCloud have flexibility to perform all the tasks at one stop, so that manualintervention is not required any more to download the patches or to analyze orto apply.
Oracle雲為所有Oracle數據源（例如My Oracle Support，特殊DBaaS監控和輕鬆訪問受保護的Oracle源）提供了易於互連的功能，因此Oracle Cloud可以靈活地一站式執行所有任務，以便手動幹預，並且不需要任何更多的下載補丁或分析應用。
Environment Information
Service/Hostname : CKPT-DBaaS
Domain : nassyambasha
Oracle RDBMS : 12.1.0.2
Database : On Cloud
SID : ORC1
OPatch version : 12.1.0.1.10
Patch to apply : April 2016 PSU
Patch 22291127: DATABASE PATCH SET UPDATE 12.1.0.2.160419
Patch 22291127 - Database Patch Set Update 12.1.0.2.160419(Includes CPUApr2016)
Applying patchon Database of cloud
在雲上應用數據庫補丁集
In order to access our database service from the cloudwe can navigate as Dashboard à Select Oracle Database Cloud Serviceà from My services “click on URL” à from new page under database click on “ x instance(s) and then youcan see below screen with the list of databases services.
我們通過在導航欄輸入Dashboard訪問到Oracle雲上的數據庫服務頁麵，然後選擇“Oracle Database Cloud Service” à在我的服務中選擇“click onURL”à然後在新的頁麵選擇數據庫實例名稱就可以打開以下頁麵，頁麵上我們會看到很多的數據庫服務。
From the home page of database service, wecan see the hardware configuration, connectivity information of database. Nowwe can jump to the administration section of the left side panel to check thelist of the available patches and to know the patch history.
在數據庫服務的主頁麵，有硬件配置、數據庫的連接信息，選擇控製麵板，查看可用的補丁集和 已經應用的補丁集。
So this section shows us that the databaseis eligible to apply the PSU update 12.1.0.2.160419 which is April 2016 PSU andwe can see other information such as when this patch is released and quickaccess to readme file.
在這部分我們可以看到在2016年4月份該數據庫成功應用過12.1.0.2.160419的補丁集，同樣，我們也可以查看該補丁集什麼時候被釋放，或者直接點擊閱讀readme文件。
If once we decided to go to apply the patchthen the first step is to perform the “pre-check” of database over thedatabase. Manually we have to run the command using OPatch but here singleclick can perform and provides the output whether the patch have any conflictsor not.
如果決定應用補丁，第一步就是在數據庫上執行數據庫的“預檢查”。非雲上數據塊我們必須手動執行Opatch程序，但這裏直接可以單擊執行，並提供輸出顯示補丁是否有衝突。
After initiating pre-check it will startchecking with database homes.
預檢查完成之後接下來檢查Oracle home目錄。
It takes very little time to perform thepre-check and after that you will see below status message of the conflicts.This step can be performed as much as many times if in case.
執行預檢查的過程很快就會完成，然後提示一下狀態信息（如圖），圖中顯示有衝突，因此該步驟會根據衝突發生的不同情況而反複執行。
Now we are ready to go for patching, priorto that we will gather the OPatch lsinventory information so that we cancrosscheck after applying the patch.
現在準備就緒，可以應用補丁集了。應用之前，先收集OPatch lsinventory信息，以便在應用補丁後進行交叉檢查。
[oracle@CKPT-DBaaS~]$ hostname
CKPT-DBaaS
[oracle@CKPT-DBaaS~]$ id
uid=54321(oracle)gid=54321(oinstall) groups=54321(oinstall),54322(dba)
[oracle@CKPT-DBaaS~]$
[oracle@CKPT-DBaaS~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@CKPT-DBaaS~]$ OPatch version
OPatch Version:12.1.0.1.10
OPatch succeeded.
[oracle@CKPT-DBaaS~]$ OPatch lsinventory
Oracle Interim PatchInstaller version 12.1.0.1.10
Copyright (c) 2016,Oracle Corporation. All rights reserved.
Oracle Home :/u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory :/u01/app/oraInventory
from :/u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version : 12.1.0.1.10
OUI version : 12.1.0.2.0
Log file location :/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/OPatch2016-07-16_08-23-00AM_1.log
Lsinventory Outputfile location :
/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/lsinv/lsinventory2016-07-16_08-23-00AM.txt
----------------------------------------------------------------------
LocalMachine Information::
Hostname:CKPT-DBaaS.compute-nassyambasha.oraclecloud.internal
ARU platform id: 226
ARU platformdescription:: Linux x86-64
Installed Top-levelProducts (1):
Oracle Database12c 12.1.0.2.0
There are 1 productsinstalled in this Oracle Home.
Interim patches (3):
Patch 22139226 : applied on Thu Jan 21 12:30:53 IST 2016
Unique PatchID: 19729684
...
----------------------------------------------------------------------
OPatch succeeded.
[oracle@CKPT-DBaaS~]$
We have gathered required information andnow we will proceed to apply patch directly from the cloud dashboard for thedatabase service.
收集了所需的信息，接下來將直接從雲儀表板應用數據庫服務的修補程序。
Before proceeding with patching cloudprompts for your approval once again to ensure, because it requires downtimeand hence this step should consider when the downtime is accepted. You canleave a comment so that the job id tagged with comment to view for otherusers.
在修補雲提示以獲得批準之前，請再次確認，因為需要停機時間，此步驟應考慮何時接受停機時間。 操作用戶可以在此處留下評論，以便將作業標簽標記為評論，以供其他用戶查看。
Once the comment entered and acknowledged thatmeans we are ensuring to apply patch forcibly then again we can click on thepatch button and then the job id will be created and oracle cloud handles thecomplete patching.
當輸入評論以後，就意味著用戶將會強製應用補丁集，此時點擊Opatch程序將會創建job id, oracle cloud handles將會輔助完成應用補丁集的過程。
Now the patching is in progress and we cancheck the status on clicking the “Last Patch applied” section and it shows whenthe patching is started with the job id and the current status.
如上圖，正在應用補丁集。可以通過選擇“LastPatch applied”查看應用狀態。狀態信息包括開始應用的時間，job ID，當前完成狀態等。
We can review the database and listenerstatus while patching in progress from the console using putty ssh terminal. Ifwe see below the listener and also the database are already down and that meansof course patching is in progress.
我們可以在使用putty ssh終端從控製台進行修補過程中查看數據庫和監聽器狀態。我們看到下麵的監聽器和數據庫已經關閉，安裝補丁集的過程還在進行。
[oracle@CKPT-DBaaS~]$ ps -ef|grep pmon
oracle 6848 1369 0 08:36 pts/1 00:00:00 grep pmon
[oracle@CKPT-DBaaS~]$ ps -ef|grep tns
root 20 2 0 Jun26 ? 00:00:00 [netns]
oracle 6850 1369 0 08:36 pts/1 00:00:00 grep tns
[oracle@CKPT-DBaaS~]$
Whole patching took for 25 minutes ofoverall process; the duration depends on each patch and the number ofdatabases.
整個安裝補丁集的過程25分鍾就完成了，一般完成時間取決於不同的補丁集和數據庫的數量。（不大於30分鍾）。
In span of 25 minutes all the tasks areperformed
在25分鍾完成了以下全部過程：
Download required patches 下載相應的補丁集
Apply the patch over OracleHome 在Oracle home目錄應用補丁集
Loading modified SQL 上傳修飾SQL
Recompiling invalid objects 重新編譯失效對象。
Further more information can be grabbedfrom the Patch history by clicking like below.
如下圖，可以查詢曆史補丁集的更多信息。
After patching completed we can verify thestatus from database level using the below query .
完成補丁應用以後，我們通過以下語句確認數據庫層麵的各類狀態。
The same information we can also gatherfrom the database alert log and the contents are below.
以上信息我們也可以通過數據庫的告警日誌獲得。
Sat Jul 16 08:44:452016
AQPC started withpid=35, OS id=15902
DatabaseCharacterset for PDB$SEED is AL32UTF8
Opening pdb PDB$SEED(2) with no Resource Manager plan active
ALTER PLUGGABLEDATABASE ALL OPEN
DatabaseCharacterset for PSPDB is AL32UTF8
Opening pdb PSPDB(3) with no Resource Manager plan active
Pluggable databasePSPDB opened read write
Completed: ALTERPLUGGABLE DATABASE ALL OPEN
Starting backgroundprocess CJQ0
Completed: ALTERDATABASE OPEN
Sat Jul 16 08:44:482016
CJQ0 started withpid=36, OS id=16023
Sat Jul 16 08:44:492016
===========================================================
Dumping currentpatch information
===========================================================
Patch Id: 19769480
...
Patch Id: 23192060
...
===========================================================
Sat Jul 16 08:44:492016
db_recovery_file_dest_sizeof 6144 MB is 48.22% used. This is a
user-specified limiton the amount of space that will be used by this
database forrecovery-related files, and does not reflect the amount of
space available inthe underlying filesystem or ASM diskgroup.
Sat Jul 16 08:45:382016
SERVER COMPONENTid=UTLRP_BGN: timestamp=2016-07-16 08:45:38 Container=CDB$ROOT Id=1
diag_adl:SERVERCOMPONENT id=UTLRP_END: timestamp=2016-07-16 08:45:41 Container=CDB$ROOT Id=1
diag_adl:
diag_adl:XDBinstalled.
diag_adl:
diag_adl:XDBinitialized.
Sat Jul 16 08:54:282016
Resize operationcompleted for file# 1, old size 839680K, new size 849920K
We have crosschecked the patching and theloading modified sql from alert log and we finally crosscheck the OPatchlsinventory to know the list of patches are applied.
我們已經對補丁集做了交叉檢查，並從告警日誌加載了修飾sql，通過交叉檢查OPatch lsinventory可以獲取應用補丁列表。
[oracle@CKPT-DBaaS ~]$export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@CKPT-DBaaS~]$ OPatch lsinventory
Oracle Interim PatchInstaller version 12.1.0.1.10
Copyright (c) 2016,Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory :/u01/app/oraInventory
from :/u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version : 12.1.0.1.10
OUI version : 12.1.0.2.0
Log file location :/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/OPatch/OPatch2016-07-16_15-37-52PM_1.log
Lsinventory Outputfile location :
...
OPatch succeeded.
[oracle@CKPT-DBaaS~]$
Conclusion總結
We’ve seen the step by step procedure toapply the patch for database service on cloud and the major challenges ofapplying patches from on-premises and the advantages of patching of clouddatabase with just single click.
通過以上一步一步的過程，將雲計算數據庫服務的補丁應用到雲上。文章同時對比了傳統應用補丁的主要挑戰以及雲數據庫應用補丁集的優點。
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:52