Dell EqualLogic Best Practices Series Best Practices for Oracle 11g Backup and Recovery using Oracle Recovery Manager (RMAN) and Dell EqualLogic Snapshots A Dell Technical Whitepaper This document has been archived and will no longer be maintained or updated. For more information go to the Storage Solutions Technical Documents page on Dell TechCenter or contact support.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND. © 2011 Dell Inc. All rights reserved. Reproduction of this material in any manner whatsoever without the express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.
Table of Contents 1 Introduction – key database administration challenges ............................................................................ 5 1.1 How this paper can help you meet these challenges ........................................................................ 5 1.2 Audience ..................................................................................................................................................... 6 1.3 The rest of this paper ................................
7.3 8 Comparison of backup and recovery methods ................................................................................. 30 Best practices for backup and recovery ...................................................................................................... 32 8.1 Use a Virtualized Database Backup Server ......................................................................................... 32 8.2 RMAN backup ...........................................................................
Acknowledgements This whitepaper was produced by the PG Storage Infrastructure and Solutions team between January 2011 and April 2011 at the Dell Labs facility in Round Rock, Texas.
1 Introduction – key database administration challenges Managing Oracle® database backup and recovery is a critical operational requirement for database and SAN administrators. Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are the key requirements that influence design of a data protection solution.
mounted on a different server (the backup server show in Figure 5 on page 19). The snapshots are mounted as an Oracle instance by the backup server and the database is backed from those volume snapshots using RMAN. As a result, all benefits of using RMAN backup are realized while also taking advantage EqualLogic PS Series storage features to minimize impact on production quality of service. This method provided several benefits, which we present in Section 8, Best practices for backup and recovery 1.
2 Oracle backup and recovery strategies overview IT administrators can back up and recover an Oracle database using multiple methods, with each method having its own advantages and disadvantages. The choice of method to use depends on various factors, such as backup window requirements, recovery point, and recovery time objectives. The EqualLogic volume snapshots feature used in conjunction with RMAN can help improve the overall efficiency of backup and recovery operations.
Table 2 summarizes the methods used in this paper to test and compare Oracle database backup and recovery solutions. Table 2 – Oracle Backup and Recovery Solutions Methodology Description Oracle RMAN Full Backup Oracle Recovery Manager (RMAN) is a commonly used utility supplied by Oracle for performing backup and recovery of Oracle databases. Administrators can use this utility to perform disk-to-tape or disk-to-disk backups.
3 Backup solution architecture For the tests detailed in this paper, we created a reference design architecture that closely simulates a production server, network, and storage environment. The Quest Benchmark Factory tool was used to simulate TPC-C workload on a two-node Oracle 11g R2 RAC production database. Oracle database full backup and recovery operations were performed using the Oracle RMAN utility. Figure 1 shows the test system configuration.
Some key design details in the test system configuration: • • • • • We hosted the Oracle 11g R2 RAC database on the two Dell PowerEdge R710 Database servers as shown in Figure 1. VMware ESX 4.1 was hosted on another, separate Dell PowerEdge R710 server. We configured a Linux VM (RHEL 5.5) on the ESX server to run Oracle. We stored the production database volumes on the EqualLogic PS6010XV array. The backup target volumes were stored on the EqualLogic PS6010E array.
The Oracle ASM disks were configured using the following layout: ORADB Database files; temporary table space; online redo logs; system related table spaces such as SYSTEM and UNDO ORALOG REDO logs ORAARCH Archive logs ORAFLASH Flash Recovery Area ORACRS ASM Cluster file system (ACFS) for storing Clusterware related information such as the OCR and voting disks 4 Table 3 shows the ASM Disk configuration used in each ASM Group for all tests.
Figure 2 - Oracle disk groups and disks distribution on EqualLogic volumes Best Practices for Oracle 11g Backup and Recovery using RMAN and Dell EqualLogic Snapshots 12
4 Test design and results: RMAN First, we evaluated Oracle backup and recovery solutions using the native RMAN utility. Then we used EqualLogic snapshots along with RMAN to offload the backup processing to a backup server. We also compared point-in-time recovery methods using Oracle’s FLASHBACK DATABASE utility vs. using EqualLogic snapshots. Test plan summary: • • • 4.
Figure 3 –System components: Test phase 1 typical RMAN based backup We used Quest Benchmark Factory to simulate a TPC-C workload of 4000 concurrent users on the production database (Size = 200GB). The production database volumes resided on the EqualLogic PS6010XV array (15K SAS drives) and the backup target volumes were stored on the EqualLogic PS6010E array (SATA drives). The disk-to-disk backup and recovery operations from SAS to SATA drives were performed using the same database.
4.1.1 Database backup time: RMAN full backup The time necessary to create the full backup of our test database (while under TPC-C transactional load) is shown below. RMAN Full Backup Time to create full backup 4.1.2 Duration (Minutes) 54 Size (GB) 150 Performance impact: RMAN full backup Figure 4 shows a composite chart illustrating the incremental impact on CPU utilization, database response time and transactions per second (TPS) caused by RMAN backup processing.
You can see in Figure 4 that a significant incremental increase in CPU utilization occurred at the start of RMAN backup processing. We also measured a significant impact on the performance of the production database (increase in response time and decrease in TPS shown in the bottom part of Figure 4). The impact on the production database is due to how RMAN quiesces database I/O activity at the beginning of the process.
During a level 1 incremental backup and onwards, RMAN verifies every block in the data files to detect if any modifications have occurred since the previous incremental backup. If there are modifications, then only the blocks containing modifications will be copied. As a result, size of incremental backups (level 1 and onwards) is typically much less than level 0 or full backups.
5 Test design and results: RMAN offload using EqualLogic snapshots In this phase of our testing we implemented a user-managed backup process, combining RMAN with EqualLogic snapshots. The following changes were made to the system design and backup process: • • RMAN backup and recovery operations were run on an additional virtualized server (instead of the same servers used for hosting the database workload).
Figure 5 - System components: Test phase 2 user managed backup Best Practices for Oracle 11g Backup and Recovery using RMAN and Dell EqualLogic Snapshots 19
5.1 EqualLogic volume and snapshot collections EqualLogic PS Series arrays provide the ability to create Volume Collections. Volume collections are useful when you need to perform an operation simultaneously on multiple volumes. Once you have created a volume collection you can create a snapshot of the entire collection in one operation. This feature can be very useful in Oracle environments because Oracle database files are typically spread across multiple volumes.
Figure 8 illustrates this process, showing the new snapshot collection of data volumes that will be mounted by the backup server.
5.2 Virtualized backup server A backup server is an auxiliary host that runs Oracle RMAN and mounts the collection of volume snapshots described in the previous section. Why run a dedicated backup server? The backup server completely offloads the backup processing to a different host. This results in no impact due to RMAN processing on the production database servers. This server can also be used to install external backup agents.
3. 4. 5. 6. 7. 8. 9. instance database on backup server, you need to remove all RAC related settings from the database parameter file. Discover the iSCSI targets on the backup server after mounting the snapshots. Login to the iSCSI targets. Discover the Oracle ASM disks using the “oracleasm scandisks” utility. Since the mounted snapshots are copies of the original database volumes the original ASM labels are available. Perform the steps detailed in section 8.
6 Test design and results: point-in-time recovery using Oracle FLASHBACK DATABASE vs. EqualLogic snapshots There are many scenarios where database administrators chose to recover databases to specific points in time. For example, if an error occurred (such as inadvertent table deletion or database corruption), the first thing a DBA does is recover the database to a time prior to when the database corruption occurred.
5. Start up the database in mount state. 7 6. Restore the database to a specific previous time using the System Change Number (SCN). 7. Once the flashback operation is complete, open the database using resetlogs. Configuration Notes: • • The FLASHBACK DATABASE feature uses flashback logs to track database changes. If the flashback feature is enabled, then flashback logs are generated and are written sequentially to flash recovery area during normal database operation.
6.3 Point-in-time recovery using FLASHBACK DATABASE and EqualLogic snapshots – test results In this section we compare recovery times using Oracle FLASHBACK DATABASE utility and EqualLogic snapshot based approaches. The amount of time taken to recover a database using the FLASHBACK DATABASE feature is proportional to how far back the database needs to be rolled back. Usually this is less than the time necessary to restore and recover an entire database using RMAN.
7 Conclusions from test results This section describes key observations captured during each test. The database backup and recovery operations were performed using RMAN full backup, RMAN incremental backup and EqualLogic snapshots. We also studied the point-in-time recovery operations of the database using Oracle’s FLASHBACK DATABASE feature and EqualLogic snapshots. 7.
Figure 10 – Backup time comparison 7.2 Database recovery – key observations Complete Recovery Time to complete database recovery for the three different methods is shown in Figure 11. • • As expected, the time to complete recovery from an RMAN full backup when comparing regular RMAN to EqualLogic snapshot based RMAN was similar. Also as expected, the time to complete recovery from incremental RMAN backup was significantly lower than from full backup.
Figure 11 – Recovery time comparison Point-in-time recovery Refer back to Figure 9 for a comparison of point-in-time recovery results. • • The database needs to be in FLASHBACK mode for enabling the FLASHBACK DATABASE feature. This introduces additional processing overhead on the database servers as well as storage space requirements for the flashback recovery area.
• • EqualLogic snapshots efficiently met the point-in-time recovery objectives. The Snapshot restore operation was almost instantaneous and the entire recovery process was able to be manually completed within six minutes. Restore/recovery time when using EqualLogic snapshots did not change based on the amount of database transactions that need to be replayed, as it would if using Oracle FLASHBACK DATABASE. 7.
Table 5 compares point-in-time recovery methods when using EqualLogic snapshots vs. Oracle FLASHBACK DATABASE. Table 5 – Comparison of point-in-time methods Method RTO Impact (Refer to Figure 9 in Section 6.3) FLASHBACK DATABASE Recovery time depends on transaction history. Can take significantly longer to complete than recovery from EqualLogic snapshots. EQL Snapshots Time to recover is very short, and does not depend on transaction history.
8 Best practices for backup and recovery 8.1 Use a Virtualized Database Backup Server We recommend that you use Dell EqualLogic Snapshots along with Oracle RMAN to reduce performance impact on production database servers (CPU Utilization) and improve the efficiency of backup and recovery operations.
Another key point to consider is that the EqualLogic array automatically creates a snapshot during the restore operation. The array captures the snapshot and copies the original source volume content into this snapshot before the actual restore operation. IT storage administrators may find the automatic creation feature useful since they can analyze the snapshot content to determine the possible root cause of data corruption failure.
Firmware : 6.0.1 / Driver : 1.60.50.v41.2 The URL for downloading the Broadcom drivers from VMware is: http://downloads.vmware.com/d/details/esx41_broadcom_netextremeii_dt/ZHcqYnRlaHRiZHdlZQ 8.8 RMAN configuration parameters By default, the BACKUP OPTIMIZATION parameter for RMAN is OFF. We enabled backup optimization by executing the following command from the RMAN prompt.
Appendix A Test system component details This section contains an overview of both the hardware and software configurations used throughout the testing described in this document. Table 6 – Test Configuration Hardware Components Test Configuration – Hardware Components Oracle Database Servers 2 x Dell PowerEdge R710 Servers: • RHEL 5.5 OS • BIOS Version: 2.1.15 • 2 x Quad Core Intel® Xeon® X5570 Processors • 64 GB RAM, 8M Cache, 2.
Table 7 – Test Configuration Software Components Test Configuration – Software Components Database Servers Red Hat Enterprise Linux 5.5 • iSCSI software initiator: 6.2.0.871-0.16.el5 • MPIO Enabled: v0.4.7 Oracle Database 11g R2 Enterprise Edition • Two-Node Oracle RAC database. • ASM for Clusterware and Database Virtualized Backup Server Red Hat Enterprise Linux 5.5 • iSCSI software initiator: 6.2.0.871-0.16.el5 MPIO Enabled: v0.4.
Appendix B Automation of snapshot-based backup In Example 2 we provide the source for the expect script used to automate parts of the user managed backup method presented in Section 5. This expect script performs three tasks: 1. Place the database in hot backup mode 2. Create the snapshot collection on the EqualLogic storage array 3.
} } } expect "*$ " send "\r" # Take the database out of hot backup mode" spawn sqlplus / as sysdba; expect "SQL>" send "alter database end backup;\r" expect "SQL>" send "alter system archive log current;\r" expect "SQL>" send "exit\r" exit Best Practices for Oracle 11g Backup and Recovery using RMAN and Dell EqualLogic Snapshots 38
Related publications The following Dell publications are referenced in this document or are recommended sources for additional information. Dell EqualLogic PS Series Network Performance Guidelines http://www.equallogic.com/resourcecenter/assetview.aspx?id=5229 Sizing and Best Practices for Deploying Oracle 11g Transaction Processing Databases on Dell EqualLogic Storage http://www.delltechcenter.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND.
