在 Azure 中設計和實作 Oracle 資料庫Design and implement an Oracle database in Azure

假設Assumptions

  • 您打算將 Oracle 資料庫從內部部署環境移轉至 Azure。You're planning to migrate an Oracle database from on-premises to Azure.
  • 您想要遷移的 Oracle Database 有診斷套件You have the Diagnostics Pack for the Oracle Database you're looking to migrate
  • 您已了解 Oracle AWR 報表中的各種計量。You have an understanding of the various metrics in Oracle AWR reports.
  • 您已基本了解應用程式效能和平台使用量。You have a baseline understanding of application performance and platform utilization.

目標Goals

  • 了解如何將 Azure 中的 Oracle 部署最佳化。Understand how to optimize your Oracle deployment in Azure.
  • 探索 Azure 環境中 Oracle 資料庫的效能調整選項。Explore performance tuning options for an Oracle database in an Azure environment.

內部部署和 Azure 實作之間的差異The differences between an on-premises and Azure implementation

以下是一些您在將內部部署應用程式移轉至 Azure 時應該謹記的重要事項。Following are some important things to keep in mind when you're migrating on-premises applications to Azure.

其中的一項重要差異是,Azure 實作中的資源 (例如 VM、磁碟和虛擬網路) 會與其他用戶端共用。One important difference is that in an Azure implementation, resources such as VMs, disks, and virtual networks are shared among other clients. 此外,您可以根據需求來節流資源。In addition, resources can be throttled based on the requirements. Azure 更聚焦在讓失敗存活 (MTTR),而不是聚焦於避免失敗 (MTBF)。Instead of focusing on avoiding failing (MTBF), Azure is more focused on surviving the failure (MTTR).

下表列出 Oracle 資料庫在內部部署實作和 Azure 實作之間的一些差異。The following table lists some of the differences between an on-premises implementation and an Azure implementation of an Oracle database.

內部部署實作On-premises implementation Azure 實作Azure implementation
網路功能Networking LAN/WANLAN/WAN SDN (軟體定義網路)SDN (software-defined networking)
安全性群組Security group IP/連接埠限制工具IP/port restriction tools 網路安全性群組 (NSG)Network Security Group (NSG)
恢復功能Resilience MTBF (平均失敗時間)MTBF (mean time between failures) MTTR (平均復原時間)MTTR (mean time to recovery)
預定的維修Planned maintenance 修補/升級Patching/upgrades 可用性設定組 (Azure 所管理的修補/升級)Availability sets (patching/upgrades managed by Azure)
ResourceResource 專用Dedicated 與其他用戶端共用Shared with other clients
區域Regions 資料中心Datacenters 區域配對Region pairs
儲存體Storage SAN/實體磁碟SAN/physical disks Azure 受控儲存體Azure-managed storage
縮放Scale 垂直調整Vertical scale 水平調整Horizontal scale

需求Requirements

  • 決定資料庫大小和成長率。Determine the database size and growth rate.
  • 決定 IOPS 需求,您可以根據 Oracle AWR 報表或其他網路監視工具進行評估。Determine the IOPS requirements, which you can estimate based on Oracle AWR reports or other network monitoring tools.

設定選項Configuration options

有四個可能的區域可供您進行調整,以改善 Azure 環境中的效能:There are four potential areas that you can tune to improve performance in an Azure environment:

  • 虛擬機器大小Virtual machine size
  • 網路輸送量Network throughput
  • 磁碟類型和設定Disk types and configurations
  • 磁碟快取設定Disk cache settings

產生 AWR 報表Generate an AWR report

如果您目前已有 Oracle 資料庫,且打算移轉至 Azure,您會有數個選項。If you have an existing an Oracle database and are planning to migrate to Azure, you have several options. 如果您有 Oracle 實例的診斷套件, 您可以執行 oracle AWR 報表來取得計量 (IOPS、Mbps、gib 等等)。If you have the Diagnostics Pack for your Oracle instances, you can run the Oracle AWR report to get the metrics (IOPS, Mbps, GiBs, and so on). 然後根據收集到的計量選擇 VM。Then choose the VM based on the metrics that you collected. 或者,連絡基礎結構小組,取得類似的資訊。Or you can contact your infrastructure team to get similar information.

您可以考慮在一般和尖峰工作負載期間執行 AWR 報表,以進行比較。You might consider running your AWR report during both regular and peak workloads, so you can compare. 根據這些報表,您可以根據平均工作負載或最大工作負載來調整 VM 大小。Based on these reports, you can size the VMs based on either the average workload or the maximum workload.

以下是如何產生 AWR 報表的範例 (如果您目前的安裝有一個, 請使用您的 Oracle Enterprise Manager 產生 AWR 報表):Following is an example of how to generate an AWR report (Generate your AWR reports using your Oracle Enterprise Manager, if your current install has one):

$ sqlplus / as sysdba
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL> @?/rdbms/admin/awrrpt.sql

重要計量Key metrics

以下是您可以從 AWR 報表取得的計量:Following are the metrics that you can obtain from the AWR report:

  • 核心總數Total number of cores
  • CPU 時脈速度CPU clock speed
  • 記憶體總計 (GB)Total memory in GB
  • CPU 使用率CPU utilization
  • 尖峰資料傳送速率Peak data transfer rate
  • I/O 變更率 (讀/寫)Rate of I/O changes (read/write)
  • 重做記錄速率 (MBPs)Redo log rate (MBPs)
  • 網路輸送量Network throughput
  • 網路延遲速率 (低/高)Network latency rate (low/high)
  • 資料庫大小 (GB)Database size in GB
  • 透過 SQL*Net 從/至用戶端收到的位元組Bytes received via SQL*Net from/to client

虛擬機器大小Virtual machine size

1.根據 AWR 報表中的 CPU、記憶體和 I/O 使用量來預估 VM 大小1. Estimate VM size based on CPU, memory, and I/O usage from the AWR report

您可以查看的一個事項是前五個定時前景事件,其指出系統瓶頸位置。One thing you might look at is the top five timed foreground events that indicate where the system bottlenecks are.

例如在下圖中,記錄檔案同步在頂端。For example, in the following diagram, the log file sync is at the top. 這代表等候 LGWR 將記錄緩衝區寫入重做記錄檔的次數。It indicates the number of waits that are required before the LGWR writes the log buffer to the redo log file. 這些結果代表您需要效能更好的儲存體或磁碟。These results indicate that better performing storage or disks are required. 此外,此圖也會顯示 CPU (核心) 數目和記憶體數量。In addition, the diagram also shows the number of CPU (cores) and the amount of memory.

AWR 報表頁面的螢幕擷取畫面

下圖顯示讀取和寫入的 I/O 總計。The following diagram shows the total I/O of read and write. 報表統計期間共有 59 GB 的讀取和 247.3 GB 的寫入。There were 59 GB read and 247.3 GB written during the time of the report.

AWR 報表頁面的螢幕擷取畫面

2.選擇 VM2. Choose a VM

根據您從 AWR 報表收集到的資訊,下一個步驟是選擇符合您需求且大小類似的 VM。Based on the information that you collected from the AWR report, the next step is to choose a VM of a similar size that meets your requirements. 您可以在記憶體最佳化一文中找到可用 VM 的清單。You can find a list of available VMs in the article Memory optimized.

3.根據 ACU 微調類似 VM 系列的 VM 大小3. Fine-tune the VM sizing with a similar VM series based on the ACU

在您選擇 VM 之後,請注意 VM 的 ACU。After you've chosen the VM, pay attention to the ACU for the VM. 您可以根據較適合您需求的 ACU 值,選擇不同的 VM。You might choose a different VM based on the ACU value that better suits your requirements. 如需詳細資訊,請參閱 Azure 計算單位For more information, see Azure compute unit.

ACU 單位頁面的螢幕擷取畫面

網路輸送量Network throughput

下圖顯示輸送量與 IOPS 之間的關聯性:The following diagram shows the relation between throughput and IOPS:

輸送量的螢幕擷取畫面

總網路輸送量是根據下列資訊進行預估:The total network throughput is estimated based on the following information:

  • SQL*Net 流量SQL*Net traffic
  • MBps x 伺服器數目 (輸出串流,例如 Oracle Data Guard)MBps x number of servers (outbound stream such as Oracle Data Guard)
  • 其他因素,例如應用程式複寫Other factors, such as application replication

SQL*Net 輸送量的螢幕擷取畫面

根據您的網路頻寬需求,有各種閘道類型可供您選擇。Based on your network bandwidth requirements, there are various gateway types for you to choose from. 這些類型包括基本、VpnGw 和 Azure ExpressRoute。These include basic, VpnGw, and Azure ExpressRoute. 如需詳細資訊,請參閱 VPN 閘道定價頁面For more information, see the VPN gateway pricing page.

建議Recommendations

  • 與內部部署相較之下,網路延遲較高。Network latency is higher compared to an on-premises deployment. 減少網路來回行程可以大幅改善效能。Reducing network round trips can greatly improve performance.
  • 若要減少來回行程,請合併相同虛擬機器上具有高交易或 “Chatty” 應用程式的應用程式。To reduce round-trips, consolidate applications that have high transactions or “chatty” apps on the same virtual machine.
  • 使用具有加速網路的虛擬機器, 以獲得更好的網路效能。Use Virtual Machines with Accelerated Networking for better network performance.
  • 針對特定 Linux distrubutions, 請考慮啟用修剪/取消對應支援。For certain Linux distrubutions, consider enabling TRIM/UNMAP support.
  • 在個別的虛擬機器上安裝Oracle Enterprise ManagerInstall Oracle Enterprise Manager on a separate Virtual Machine.
  • 在 linux 上, 預設不會啟用大量頁面。Huge pages are not enabled on linux by default. 請考慮啟用龐大的頁面use_large_pages = ONLY , 並在 Oracle DB 上設定。Consider enabling huge pages and set use_large_pages = ONLY on the Oracle DB. 這可能有助於提升效能。This may help increase performance. 如需詳細資訊,請參閱 這裡More information can be found here.

磁碟類型和設定Disk types and configurations

  • 預設 OS 磁碟:這些磁碟類型可提供持續性資料和快取。Default OS disks: These disk types offer persistent data and caching. 它們最適合用在啟動時的 OS 存取,但其設計目的並非用於交易式或資料倉儲 (分析) 工作負載。They are optimized for OS access at startup, and aren't designed for either transactional or data warehouse (analytical) workloads.

  • 非受控磁碟︰您可以使用這些磁碟類型來管理用來儲存虛擬硬碟 (VHD) 檔案 (對應至您的 VM 磁碟) 的儲存體帳戶。Unmanaged disks: With these disk types, you manage the storage accounts that store the virtual hard disk (VHD) files that correspond to your VM disks. VHD 檔案會以分頁 Blob 的形式儲存在 Azure 儲存體帳戶中。VHD files are stored as page blobs in Azure storage accounts.

  • 受控磁碟:Azure 會管理您用於 VM 磁碟的儲存體帳戶。Managed disks: Azure manages the storage accounts that you use for your VM disks. 您可以指定需要的磁碟類型 (進階或標準) 和磁碟大小。You specify the disk type (premium or standard) and the size of the disk that you need. Azure 會為您建立並管理該磁碟。Azure creates and manages the disk for you.

  • 進階儲存體磁碟:這些磁碟類型最適合生產工作負載使用。Premium storage disks: These disk types are best suited for production workloads. 進階儲存體支援可連結至特定大小系列 VM (例如 DS、DSv2、GS 和 F 系列 VM) 的 VM 磁碟。Premium storage supports VM disks that can be attached to specific size-series VMs, such as DS, DSv2, GS, and F series VMs. 進階磁碟會隨附不同的大小,而且您可以選擇範圍從 32 GB 到 4096 GB 的磁碟。The premium disk comes with different sizes, and you can choose between disks ranging from 32 GB to 4,096 GB. 每個磁碟大小都有自己的效能規格。Each disk size has its own performance specifications. 端視您的應用程式需求而定,您可以將一或多個磁碟連結至您的 VM。Depending on your application requirements, you can attach one or more disks to your VM.

當您從入口網站建立新的受控磁碟時,可以選擇您想要使用之磁碟類型的 [帳戶類型]。When you create a new managed disk from the portal, you can choose the Account type for the type of disk you want to use. 請記住,並非所有可用的磁碟都會顯示在下拉式功能表中。Keep in mind that not all available disks are shown in the drop-down menu. 在選擇特定的 VM 大小之後,功能表只會根據該 VM 大小顯示可用的進階儲存體 SKU。After you choose a particular VM size, the menu shows only the available premium storage SKUs that are based on that VM size.

受控磁碟頁面的螢幕擷取畫面

在 VM 上設定儲存體之後,您可能會想要在建立資料庫之前對磁碟進行負載測試。After you configure your storage on a VM, you might want to load test the disks before creating a database. 知道延遲和輸送量方面的 I/O 速率可以協助您判斷 VM 是否支援具有延遲目標的預期輸送量。Knowing the I/O rate in terms of both latency and throughput can help you determine if the VMs support the expected throughput with latency targets.

有數種工具可以進行應用程式負載測試,例如 Oracle Orion、Sysbench 和 Fio。There are a number of tools for application load testing, such as Oracle Orion, Sysbench, and Fio.

在部署好 Oracle 資料庫之後,請再次執行負載測試。Run the load test again after you've deployed an Oracle database. 啟動您的一般和尖峰工作負載,其結果會顯示您環境的基準數據。Start your regular and peak workloads, and the results show you the baseline of your environment.

根據 IOPS 速率而非儲存體大小來調整儲存體大小可能更為重要。It might be more important to size the storage based on the IOPS rate rather than the storage size. 例如,如果所需的 IOPS 是 5000,但您只需要 200 GB,則您可能仍會得到 P30 等級的進階磁碟,即使它隨附 200 GB 以上的儲存體。For example, if the required IOPS is 5,000, but you only need 200 GB, you might still get the P30 class premium disk even though it comes with more than 200 GB of storage.

IOPS 速率可以從 AWR 報表取得。The IOPS rate can be obtained from the AWR report. 此速率是由重做記錄、實體讀取和寫入速率所決定。It's determined by the redo log, physical reads, and writes rate.

AWR 報表頁面的螢幕擷取畫面

例如:重做大小是每秒 12,200,000 個位元組,相當於 11.63 MBPs。For example, the redo size is 12,200,000 bytes per second, which is equal to 11.63 MBPs. IOPS 是 12,200,000 / 2,358 = 5,174。The IOPS is 12,200,000 / 2,358 = 5,174.

在清楚了解 I/O 需求之後,即可選擇最符合這些需求的磁碟機組合。After you have a clear picture of the I/O requirements, you can choose a combination of drives that are best suited to meet those requirements.

建議Recommendations

  • 針對資料的資料表空間,使用受控儲存體或 Oracle ASM,將 I/O 工作負載分散到一些磁碟。For data tablespace, spread the I/O workload across a number of disks by using managed storage or Oracle ASM.
  • 隨著 I/O 區塊大小的增加,針對讀取密集和寫入密集作業,新增更多資料磁碟。As the I/O block size increases for read-intensive and write-intensive operations, add more data disks.
  • 增加大型循序處理序的區塊大小。Increase the block size for large sequential processes.
  • 使用資料壓縮來減少 I/O (適用於資料和索引)。Use data compression to reduce I/O (for both data and indexes).
  • 區隔不同資料磁碟上的重做記錄、系統、暫時和重做 TS。Separate redo logs, system, and temps, and undo TS on separate data disks.
  • 不要將任何應用程式檔案放在預設 OS 磁碟 (/dev/sda)。Don't put any application files on default OS disks (/dev/sda). 這些磁碟不適合用於快速 VM 啟動階段,因此可能不會為您的應用程式提供良好的效能。These disks aren't optimized for fast VM boot times, and they might not provide good performance for your application.
  • 使用 Premium 儲存體上的 M 系列 Vm 時, 請在重做記錄磁片上啟用寫入加速器When using M-Series VMs on Premium storage, enable Write Accelerator on redo logs disk.

磁碟快取設定Disk cache settings

有三個主機快取選項:There are three options for host caching:

  • ReadOnly:快取所有要求,以供未來讀取。ReadOnly: All requests are cached for future reads. 所有寫入會直接保存到 Azure Blob 儲存體。All writes are persisted directly to Azure Blob storage.

  • ReadWrite:這是「預先讀取」演算法。ReadWrite: This is a “read-ahead” algorithm. 快取讀取和寫入,以供未來讀取。The reads and writes are cached for future reads. 非直接寫入式寫入會先保存到本機快取。Non-write-through writes are persisted to the local cache first. 它也會為輕量工作負載提供最低的磁碟延遲。It also provides the lowest disk latency for light workloads. 對於不負責保存必要資料的應用程式,如果使用「讀寫」快取,一旦 VM 損毀,可能會導致資料遺失。Using ReadWrite cache with an application that does not handle persisting the required data can lead to data loss, if the VM crashes.

  • 無 (已停用):使用此選項即可略過快取。None (disabled): By using this option, you can bypass the cache. 所有資料都會傳輸至磁碟,並保存到 Azure 儲存體。All the data is transferred to disk and persisted to Azure Storage. 這種方法可提供您最高 I/O 速率來進行 I/O 密集式工作負載。This method gives you the highest I/O rate for I/O intensive workloads. 您也需要考量「交易成本」。You also need to take “transaction cost” into consideration.

建議Recommendations

若要將輸送量最大化, 建議您從 [] 開始進行主機快取。To maximize the throughput, we recommend that you start with None for host caching. 針對進階儲存體,請記住您必須在使用 [唯讀] 或 [無] 選項掛接檔案系統時停用「屏障」。For Premium Storage, keep in mind that you must disable the "barriers" when you mount the file system with the ReadOnly or None options. 將具有 UUID 的 /etc/fstab 檔案更新到磁碟。Update the /etc/fstab file with the UUID to the disks.

受控磁碟頁面的螢幕擷取畫面

  • 針對 OS 磁碟,使用預設的 [讀取/寫入] 快取。For OS disks, use default Read/Write caching.
  • 針對 SYSTEM、TEMP 和 UNDO,對快取功能使用 [無]。For SYSTEM, TEMP, and UNDO use None for caching.
  • 針對 DATA,對快取功能使用 [無]。For DATA, use None for caching. 但是,如果您的資料庫是唯讀或讀取密集,請使用 [唯讀] 快取。But if your database is read-only or read-intensive, use Read-only caching.

除非您卸載 OS 層級的磁碟機,然後在進行變更後重新予以掛接,否則在儲存資料磁碟設定之後,就無法變更主機快取設定。After your data disk setting is saved, you can't change the host cache setting unless you unmount the drive at the OS level and then remount it after you've made the change.

安全性Security

在安裝並設定 Azure 環境之後,下一個步驟是保護您的網路。After you have set up and configured your Azure environment, the next step is to secure your network. 以下是一些建議:Here are some recommendations:

  • NSG 原則:可依子網路或 NIC 定義 NSG。NSG policy: NSG can be defined by a subnet or NIC. 在子網層級上控制存取比較簡單, 這兩者都是為了安全性和強制路由傳送應用程式防火牆之類的專案。It's simpler to control access at the subnet level, both for security and force routing for things like application firewalls.

  • Jumpbox:基於更安全的存取,系統管理員不應該直接連接至應用程式服務或資料庫。Jumpbox: For more secure access, administrators should not directly connect to the application service or database. Jumpbox 作為系統管理員機器與 Azure 資源之間的媒體。A jumpbox is used as a media between the administrator machine and Azure resources. Jumpbox 拓撲頁面的螢幕擷取畫面Screenshot of the Jumpbox topology page

    系統管理員機器只應該對 Jumpbox 提供 IP 受限的存取權。The administrator machine should offer IP-restricted access to the jumpbox only. Jumpbox 應該要能夠存取應用程式和資料庫。The jumpbox should have access to the application and database.

  • 私人網路 (子網路):我們建議您將應用程式服務和資料庫放在不同的子網路上,讓 NSG 原則可以設定更好的控制。Private network (subnets): We recommend that you have the application service and database on separate subnets, so better control can be set by NSG policy.

其他閱讀資料Additional reading

後續步驟Next steps