Disaster Recovery Infrastructure - White Paper

1. Active Data Guard G GE ET TT TI IN NG G M MO OS ST T O OU UT T O OF F Y YO OU UR R D DI IS SA AS ST TE ER R R RE EC CO OV VE ER RY Y I IN NF FR RA AS ST TR RU UC CT TU UR RE E U US SI IN NG G A AC CT TI IV VE E D DA AT TA A G GU UA AR RD D Manoj Machiwal, Jade Global Inc. ABSTRACT Most Organizations setup their disaster recovery environment and hardly able to utilize their disaster recover infrastructure to full capacity. In this white paper, you will learn how to setup an active data guard environment and best practices to setup active data guard environment, which can be utilized as a reporting infrastructure which supports MIS reporting, also in case of disaster can be used as failback option. TRADITIONAL DISASTER RECOVERY/HIGH AVAILABILITYSOLUTION Adding basic fault tolerance to an IT infrastructure is not hard. You can add a few redundant components, and you can claim fault tolerance, or high availability. If you have some failure in your IT stack, there are redundant components available to which you can failover. Following this basic principle, some customers have built an HA framework consisting of:  An N+1 active-passive server clustering model (e.g., clustering integrated with the OS)  Mirroring of the bits in the storage array to some other remote storage array  A tape backup product which ensures that periodic backups are taken and stored offsite  A separate volume management product to ease the management of the underlying storage 1 Session #334

2. Active Data Guard This type of configuration works, but with important limitations, as follows:  Typically, the solutions mentioned above come from different vendors. Stitching together and managing these disparate solutions require a non-trivial effort.  Because the overall architecture is based on disparate point solutions, it is difficult to scale the configuration to increase throughput. Scaling effectively is critical from an HA standpoint.  While hardware-centric HA solutions (e.g., mirroring) offer simple data protection methods, their byte-level approach makes it very difficult to build application-optimized capabilities.  A related factor is return on investment (ROI) on the HA systems. If a server is configured in a cold-cluster N+1 environment as the failover target, it cannot support production workload, and computing resources are wasted. If a remote storage array is receiving bits through storage mirroring technology, no applications or databases can be mounted on that storage array – more waste. ORACLE DATABASE HIGH AVAILABILITY Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Oracle Database have many HA capabilities such as Real Application Clusters (RAC), Data Guard, Automatic Storage Management (ASM), Flashback, Recovery Manager (RMAN), Online Reorganization, Edition-based Redefinition, etc. are available as built-in features of the Oracle Database. Along with complementary products such as Oracle Secure Backup and Oracle Golden Gate, these capabilities work together in an integrated manner to offer the best solution for the Oracle Database to reduce or eliminate both planned and unplanned downtime. Oracle also offers the Oracle Maximum Availability Architecture (MAA), which is Oracle's HA best practices blueprint involving its entire technology stack from applications to storage. Following the MAA guidelines, customers can significantly reduce the cost and complexity of their HA deployments, while building an HA architecture that is optimized for their Oracle environment. Following are the key capabilities that comprise Oracle Database's HA solution set.  Real Application Clusters  Clusterware  Data Guard  GoldenGate  Streams  Secure Backup  Recovery Manager (RMAN)  Flashback Technologies  VM  Cloud Computing  Cloud Storage  Cross-Platform Transportable Tablespace  Edition-Based Redefinition 2 Session #334

3. Active Data Guard  Online Reorganization ACTIVE DATA GUARD OVERVIEW Oracle Data Guard is THE solution for Oracle data protection, data availability, and disaster recovery. Oracle Data Guard provides the management, monitoring, and automation software to create and maintain one or more standby databases to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications Data Guard's loosely coupled architecture provides optimal data protection and availability:  Database changes are transmitted directly from memory, isolating the standby from I/O corruptions that occur at the primary.  A standby database uses a different software code-path than the primary – isolating it from firmware and software errors that impact the primary database.  Oracle corruption detection insures that data is logically and physically consistent before it is applied to a standby database.  Data Guard detects silent corruptions caused by hardware errors (memory, cpu, disk, NIC) and data transfer faults, and prevents them from impacting the standby database.  A standby database is used to perform planned maintenance in a rolling fashion, minimizing downtime and eliminating the risks inherent with introducing change to production environments. 3 Session #334

4. Active Data Guard Traditional Data Guard Oracle Active Data Guard 11g extends basic Data Guard functionality by allowing read-only access to a physical standby database while continuously applying changes received from the primary database. This increases performance and return on investment by offloading ad-hoc queries, Web-based access, reporting, and backups from the primary database while also providing disaster protection. Active Data Guard ENABLING ACTIVE DATA GUARD The general process of enabling Active Data Guard is as simple as opening the standby database in read-only mode and starting Redo Apply. This section describes how to enable Active Data Guard on a standby database.  If the standby instance and redo apply have been cleanly shut down a.Using SQL*Plus Start the standby instance in read-only mode. SQL> startup Once the database is open, start Redo Apply: SQL> recover managed standby database disconnect using current logfile; b.If you are using the Data Guard broker to manage your Data Guard configuration Start the standby instance in read-only mode. Connect to the standby instance using DGMGRL and issue the following command: DGMGRL> startup 4 Session #334

5. Active Data Guard The default state for the standby database is APPLY ON and Redo Apply starts automatically. If the default has been changed, then start Redo Apply by issuing the following command: DGMGRL> EDIT DATABASE ‘RTQ' SET STATE=‘APPLY-ON’ If the standby database is mounted and redo apply is running using the following steps Using SQL*Plus Stop redo apply SQL> recover managed standby database cancel; Open the database read only SQL> alter database open read only; Once the database is open, start redo apply: SQL> recover managed standby database disconnect using current logfile; If you are using the Data Guard broker to manage your Data Guard configuration Stop redo apply using DGMGRL DGMGRL> EDIT DATABASE ‘RTQ' SET STATE=‘APPLY-OFF’ In SQL*Plus open the database read only alter database open read only; Restart redo apply by issuing the following command: DGMGRL> EDIT DATABASE ‘RTQ' SET STATE=‘APPLY-ON’ Note: In 11gR2, Alter database read only, automatically stops the redo apply before opening the database and starts redo apply after opening the database in read only mode. ACTIVE DATA GUARD COOL FEATURES • Write Operations to Primary • This feature helps redirecting updates from an Active Data Guard standby to a remote database, using database link. This feature is very useful for applications which are doing mostly read operations except a very few write operations. These write operations can be redirected to Primary database using database link. • Automatic Repair of Block Corruption • When Oracle detects corrupt blocks at the primary database, it will repair online by copying the good version from an active standby database (and vice versa)  MAINTENANCE/MENTORINGTIPS • Confirming Active Data Guard is Enabled 5 Session #334

6. Active Data Guard SQL> SELECT OPEN_MODE FROM V$DATABASE; OPEN_MODE --------------------- READ ONLY WITH APPLY Monitor the Apply Lag SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag'; SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0; • CUSTOMER CASE STUDY a.Customer Background • Industry –Solar Manufacturing • Technical foot Print –11gR2 –2 Node RAC –Exadata Machine • Application –Factory Operations –MES Reporting b.Customer Challenges • Manufacturing Operations and Reporting Operations running on Same database, which creates the resource hogging issues while running reports and manufacturing operations have significant impact. • No High Availability Architecture • Exadata Patching was challenge as system cannot be brought down and there is no HA environment c.Existing Infrastructure 6 Session #334

7. Active Data Guard d.Proposed Infrastructure e.Lessons Learned Failover site was not tuned for Factory operations so Factory operations were having performance issues while switching over to standby site for doing patching on Exadata Machine. 7 Session #334

8. Active Data Guard Lot of Rework need to be planned as reporting queries were originally optimized in terms of Exadata Machine and they have to be reworked and tuned to work on Active Standby Environment. Use of RAT Capture and RAT Replay should have been done effectively to measure performance of active standby during actual rollover and Switchover for exadata patching, this would have identified many issues in advance and corrective actions could have been applied in advance. About the Author Manoj Machiwal is a Consulting Director at Jade Global. He has over 12 years of experience in Oracle DBA and Oracle Apps DBA. Manoj is a specialist in Upgrades, Migrations, High Availability and Performance, Datawarehouse architecting and Proactive Monitoring. Manoj has worked for some major names in the industry including Solyndra, Orbotech, Gallo Wineries, Silicon Storage Technology (SST),Telegent Systems, Riverbed Technology, Cisco Systems and Merrill Lynch. He is also an Oracle Certified Professional DBA .Manoj can be reached at manoj.machiwal@jadeglobal.com and on +1-408- 899-7217. 8 Session #334