Session Id 40056 Oracle Data Guard Maximum Data

Session Id: 40056 Oracle Data Guard: Maximum Data Protection at Minimum Cost Ashish Ray Senior Product Manager Oracle Corporation Darl Kuhn Senior DBA, Staff Engineer Sun Microsystems

Agenda Ÿ Oracle Data Guard – a Quick Introduction Ÿ Data Guard Features in Oracle Database 10 g Ÿ Customer Success Story – Sun Microsystems Ÿ Summary & Q/A

What is Oracle Data Guard? Ÿ Oracle’s disaster recovery solution for Oracle data Ÿ Feature of Oracle Database Enterprise Edition Ÿ Automates the creation and maintenance of one or more transactionally consistent copies (standby) of the production (or primary) database Ÿ If the primary database becomes unavailable (disasters, maintenance), a standby database can be activated and assume the primary role

Oracle Data Guard Focus ŸData Failures & Site Disasters: – – – Data Protection Data Availability Data Recovery All 3 are important! Data is the core asset of the enterprise! • Also addresses human errors & planned maintenances

Oracle Data Guard Architecture Physical Standby Database Sync or Async Redo Shipping Production Database Backup Redo Apply Network Broker Transform Redo to SQL Logical Standby Database SQL Apply Open for Reports Additional Indexes & MVs

Data Guard Redo Apply Primary Database Data Guard Broker Physical Standby Database Redo Apply Backup Network Redo Shipment Ÿ Ÿ Standby Redo Logs Physical Standby Database is a block-for-block copy of the primary database Uses the database recovery functionality to apply changes Can be opened in read-only mode for reporting/queries Can also be used for backups, offloading production database

Data Guard SQL Apply Additional Indexes & Materialized Views Primary Database Data Guard Broker Logical Standby Database Transform Redo to SQL and Apply Continuously Open for Reports Network Redo Shipment Standby Redo Logs Ÿ Logical Standby Database is an open, independent, active database § § § Contains the same logical information (rows) as the production database Physical organization and structure can be very different Can host multiple schemas Ÿ Can be queried for reports while logs are being applied via SQL Ÿ Can create additional indexes and materialized views for better query performance

Agenda Ÿ Oracle Data Guard – a Quick Introduction Ÿ Data Guard Features in Oracle Database 10 g Ÿ Customer Success Story – Sun Microsystems Ÿ Summary & Q/A

Oracle Data Guard 10 g Objectives Ÿ Establish Data Guard as an extremely – – easy-to-use low-cost comprehensive reliable Ÿ Disaster Recovery solution for enterprise data

Overview of Objectives Ÿ Ease of use – simplified SQL, easy to create, manage and administer standby databases, simplified GUI focused on best practices Ÿ Low cost – businesses can leverage existing resources to implement Data Guard, zero integration costs Ÿ Comprehensive – feature-rich and flexible Ÿ Reliable – a rock-solid solution for protection of mission critical business data

Data Guard 10 g New Features ŸGeneral new features – – Real Time Apply Flashback Database Integration ŸSQL Apply new features – – – Zero Downtime Instantiation Rolling Upgrades Additional Datatypes ŸData Guard Broker & Enterprise Manager new features – – RAC integration Simplified browser-based interface focused on best practices

Real Time Apply Ÿ Redo data is applied to the standby database as soon as it is received from the primary database – In Oracle 9 i Data Guard this apply has to wait till an archivelog is created on the standby database Ÿ For Redo Apply: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE Ÿ For SQL Apply: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE Ÿ When real time apply is enabled, RECOVERY_MODE column in V$ARCHIVE_DEST_STATUS displays “MANAGED REAL TIME APPLY”

Real Time Apply Architecture An up-to-date Physical/Logical Standby Database Oracle Net Transactions LGWR Online Redo Logs Primary Database MRP/ LSP RFS Standby Redo Logs ARCH Real Time Apply ARCH Archived Redo Logs

Real Time Apply – Benefits Ÿ Standby databases now more closely synchronized with the primary – – More up-to-date, real-time reporting Faster switchover and failover times Ÿ Reduces planned and unplanned downtime Ÿ Better Recovery Time Objective (RTO) for DR

Existing Site Recovery Tradeoffs Primary Database Standby Database Redo Shipment Delayed Apply Ÿ Log apply may be delayed to protect from user errors but: – Switchover/Failover gets delayed – Reports run on old data Ÿ After failing over to standby, production DB must be rebuilt Reporting on delayed data

Flashback Database Ÿ A new strategy for point in time recovery Ÿ Eliminate the need to restore a whole database backup Ÿ Integrated seamlessly with RMAN – – Think of it as a continuous backup Restores just changed blocks Ÿ It’s fast - recover in minutes, not hours Ÿ It’s easy - single command restore RMAN> FLASHBACK DATABASE TIMESTAMP to_timestamp ('2003 -08 -15 16: 00', 'YYYY-MM-DD HH 24: MI: SS');

Enhanced DR with Flashback Database Primary Database Redo Shipment Real Time Apply Standby Database Real Time Reporting No Delay! Flashback Log Primary: No reinstantiation after failover! Ÿ Flashback DB removes the need to delay application of logs Ÿ Flashback DB removes the need to reinstantiate primary after failover Ÿ Real-time apply enables real-time reporting on standby

SQL Apply: Zero Downtime Instantiation ŸLogical standby database can now be created from an online backup of the primary database, without shutting down or quiescing the primary database – No shutdown implies no downtime of production system – No quiesce implies no wait on quiesce and no dependence on Resource Manager

Rolling Upgrades Upgrade Clients Redo A Version X 1 B Logs Queue Version X Initial SQL Apply Config A X 2 X+1 Upgrade node B to X+1 Redo Upgrade B Redo A B X+1 4 Switchover to B, upgrade A A X 3 B X+1 Run in mixed mode to test Patch Set Upgrades Major Release Upgrades Cluster Software & Hardware Upgrades

SQL Apply: Additional Data Types Ÿ SQL Apply now supports the following additional data types: – – – – Multi-byte CLOB NCLOB LONG RAW BINARY_FLOAT BINARY_DOUBLE IOT-s (without overflows and without LOB columns) Ÿ Allows logical standby databases to recover and protect a wider variety of data, thus increasing the overall database protection and recovery options for Data Guard

Enterprise Manager New Features Ÿ Streamlined browser-based interface that enables complete standby database lifecycle management Ÿ Focus on: – – – Ease of use Management based on best practices Pre-built integration with other HA features

RAC Support – Broker Ÿ Now possible to use the Broker to create and manage configurations that contain RAC primary and RAC standby databases Ÿ Data Guard Broker interfaces with Oracle Clusterware such that it has control over critical operations during specific Data Guard state transitions – Switchovers, failovers, protection mode changes, state changes

RAC Primary Two standby dbs

Instance specific

Example – Ease of Use Ÿ Switchover using Enterprise Manager is now literally two mouse clicks

Switched!

Agenda Ÿ Oracle Data Guard – a Quick Introduction Ÿ Data Guard & Features in Oracle Database 10 g Ÿ Customer Success Story – Sun Microsystems Ÿ Summary & Q/A

Case Study Ÿ Oracle Data Guard at Sun Microsystems Darl Kuhn Senior DBA, Staff Engineer Ÿ Ÿ Business decision considerations Architecture Implementation Features we use

Project Requirements Ÿ Patch and Knowledge databases for Sun Support Services Ÿ 7 x 24 High Availability – – Minimize scheduled downtime Minimize unscheduled downtime Ÿ Disaster Recovery (DR) protection Ÿ Do more with less resources Ÿ Minimize costs Ÿ Minimize complexity

Solutions We Investigated Ÿ Backup the database, restore from tape Ÿ Operating System failover Ÿ Remote Mirroring Ÿ Quest’s Share. Plex Ÿ Oracle Advanced Replication (OAR) Ÿ Oracle Real Application Clusters (RAC) Ÿ Oracle Data Guard (Standby)

We Chose Data Guard Ÿ 7 x 24 DR protection Ÿ Simple to implement Ÿ Requires DBA with B&R skills Ÿ Didn’t need special System Administration skills or consultants Ÿ Low maintenance (do more w/less DBAs) Ÿ No extra licensing (built into Oracle 9 i)

Implementation Decisions Ÿ Which data protection mode? – – – Maximum Protection Maximum Availability Maximum Performance Ÿ We chose Maximum Performance Ÿ Two identical servers Ÿ Directory structures the same Ÿ Database name the same Ÿ Introduce a delay in application of redo

Maximum Performance Primary Database Production Site Standby Database Server . Users Fetch Archive Log (FAL) Remote File Server (RFS) Oracle Net Primary Database LGWR On-line Online Redo Copied Archive Redo Managed Recovery Process (MRP) ARCn Local Archive Redo Standby Database

Database Architecture Ÿ 50 M archive redo logs Ÿ 1 Gig of redo per day Ÿ Primary in Colorado Ÿ Standbys in North Carolina, Holland Singapore Ÿ Database size currently 60 Gig Ÿ Hardware Sun 6500, 280 R, 4500 Ÿ Storage T 3 partner pair fiber channel

Implementation of Physical Standby 1. Ensure primary database is in archive log mode Note: In Data Guard 10 g, you also need to implement a password file for both Primary and Standby 2. Take backup of primary database datafiles – options: • • RMAN Hot Cold Do not backup controlfiles or online redo logs

Using RMAN to Build Standby On Primary: a) RMAN> backup database; b) Copy backup pieces to Standby c) Create a Standby controlfile and copy to Standby Then on Standby: a) b) c) d) SQL> startup nomount; SQL> alter database mount standby database; RMAN> restore database; SQL> alter database recover managed standby database disconnect;

Implementation of Physical Standby 3. Copy backup datafiles to standby server 4. Create a standby controlfile 5. Copy the standby controlfile to standby server 6. Configure primary init. ora or spfile 7. Copy primary database init. ora file to standby server and make modifications for standby database 8. Configure Oracle Net

Implementation of Physical Standby 9. Startup and mount standby database SQL> startup nomount; SQL> alter database mount standby database; Ÿ Startup syntax is simplified in Oracle Data Guard 10 g SQL> startup mount; Ÿ In Data Guard 10 g, the “startup” will put the Standby into read-only mode SQL> startup;

Implementation of Physical Standby 10. Enable managed recovery mode on Standby SQL> alter database recover managed standby database disconnect; Ÿ Troubleshooting $ tail –f alert_BRDSTN. log Ÿ Almost all problems encountered were: – – TNS set up incorrectly Initialization parameters set wrong

Preventing User Errors Ÿ Logs copied but not applied for 60 minutes Ÿ Used to have to manually script this SQL> alter database recover managed standby database delay 60 disconnect; Ÿ To disable delay: SQL> alter database recover managed standby database nodelay;

Use of Read-Only Standby Ÿ 7 x 24 business requirement for knowledge reporting Ÿ Primary database batch loaded once a day Ÿ How do we ensure that there will always be a database available? – – Create two (or more) Standby databases Shut down one at a time, apply redo

Use of Read-Only Standby Primary Database Production Site Two Separate Read-Only Standby Database Servers . Oracle Net Daily Batch Load l 3 srv 1 Standby 1 brdstn Reports Primary Database ARCn l 3 srv 2 Standby 2 brdstn

Use of Read-Only Standby Ÿ Let Oracle Net connection figure out which read-only physical Standby database available brdstn= (DESCRIPTION = (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=tcp)(HOST=l 3 srv 1)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=l 3 srv 2)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=brdstn)) )

Disaster Happens… Ÿ Haven’t had a “complete disaster”… yet Ÿ We have had bad hardware cause failovers Ÿ We were able to easily failover to Standby SQL> alter database activate standby database; Ÿ In Data Guard 9 i, we keep 9 i Primary init. ora on Standby Ÿ In Data Guard 10 g, VALID_FOR eliminates this need

Archive Gap Management Ÿ This is one of our favorite Data Guard 9 i features Ÿ Addresses critical issues such as: – – What if network or server is down? After failure resolution, how is the standby caught up? Ÿ In Oracle 8 i Standby Database, we would manually fix Ÿ In Oracle 9 i: – – – Data Guard has automatic methods for gap resolution Fetch Archive Log (FAL) processes In our experience, very reliable

Propagation of Datafile Operations Ÿ Another task automated in Data Guard 9 i Ÿ In Oracle 8 i Standby Database, add/drop tablespace/datafile commands not automatically propagated – DBA had to intervene Ÿ In Oracle 9 i Data Guard – – Fully automated In Standby initialization file: standby_file_management = auto

Propagation of Datafile Operations Example: SQL> drop tablespace HRM_SALA including contents and datafiles; Ÿ On standby the tablespace HRM_SALA will be automatically dropped and all datafiles will be deleted from disk Ÿ Note: If you rename a datafile, DBA must intervene

Oracle Data Guard 10 g Beta Feedback Ÿ Logical Standby easier to setup Ÿ Simplified SQL syntax Ÿ More helpful, feature-rich initialization parameters

Agenda Ÿ Oracle Data Guard – a Quick Introduction Ÿ Data Guard & Features in Oracle Database 10 g Ÿ Customer Success Story – Sun Microsystems Ÿ Summary & Q/A

Maximum Availability Architecture Ÿ Best Practices on: ü ü ü ü General Data Guard configuration Redo data transport mechanisms Protection modes Switchover/Failover Media recovery SQL Apply configuration Network configuration Integration with other HA technologies Ÿ White papers 1: ü MAA – detailed ü Media Recovery ü Site/Network configuration ü Fast-Start Checkpointing ü SQL Apply Best Practices ü Role Management 1. Ref. http: //otn. oracle. com/deploy/availability/htdocs/maa. htm for latest updates

Data Guard Customers Transportation Telecom Financial/ Insurance Utilities Government Manufacturing Health Care Other Industries e-Commerce

Customer Testimonials Ÿ “Data Guard automates disaster-recovery procedures and reduces Fidelity's exposure to data loss by an order of magnitude compared to previous approaches. ” Jonathan Schapiro Vice President Data Architecture & Services Global Equity Trading & Technology

Customer Testimonials Ÿ “We needed to consider the safe-keeping of our data, but we also needed to look at cost. Oracle Data Guard provides everything for a high availability solution at a lower cost than other alternatives” Ann Collins Technical Director

Customer Testimonials Ÿ “We don't have to baby-sit it – it just works!” Darl Kuhn Senior DBA & Staff Engineer Database Services Sun Services Global Engineering

Why Oracle Data Guard? 1. Disaster Recovery & High Availability – 2. Complete data protection – 3. Flexible data protection/synchronization modes Automatic resynchronization after restoration of network connectivity – 6. Standby databases can be used for reporting, backups, queries Balance data availability against performance – 5. Enables zero data loss, safeguard against data corruptions Efficient utilization of system resources – 4. Easy failover/switchover between primary and standby databases Automatic archive gap detection and resolution with no manual intervention Centralized and simple management – Push-button graphical interface for management and monitoring

Of Course … Complete, out-of-the-box integration with Oracle database, at no extra cost!

Resources Ÿ Maximum Availability Architecture white papers: http: //otn. oracle. com/deploy/availability/htdocs/maa. htm Ÿ HA Portal on OTN: http: //otn. oracle. com/deploy/availability Ÿ Data Guard home page on OTN: http: //otn. oracle. com/deploy/availability/htdocs/odg_overview. html Ÿ Oracle Consulting Services: http: //otn. oracle. com/consulting

Next Steps High Availability Sessions from Oracle Tuesday in Moscone Room 304 Wednesday in Moscone Room 304 11: 00 AM 8: 30 AM How Oracle Database 10 g Revolutionizes Availability and Enables the Grid Oracle Database 10 g - RMAN and ATA Storage in Action 3: 30 PM Oracle Data Guard: Maximum Data Protection at Minimum Cost Oracle Recovery Manager (RMAN) 10 g: Reloaded 5: 00 PM Proven Techniques for Maximizing Availability 11: 00 AM 1: 00 PM Oracle Database 10 g Time Navigation: Human-Error Correction 4: 30 PM Data Guard SQL Apply: Back to the Future For More Info On Oracle HA Go To http: //otn. oracle. com/deploy/availability/

Next Steps High Availability Sessions from Oracle Thursday Database HA Demos All Four Days In The Oracle Demo Campground 8: 30 AM in Moscone Room 304 Oracle Database 10 g Data Warehouse Backup and Recovery: Automatic, Simple, Reliable 8: 30 AM in Moscone Room 104 Building RAC Clusters over Infini. Band Real Application Clusters Data Guard Database Backup & Recovery Flashback Recovery Log. Miner, Online Redefinition, and Cross Platform Transportable Tablespaces For More Info On Oracle HA Go To http: //otn. oracle. com/deploy/availability/

Reminder – please complete the Oracle. World online session survey Thank you.

Q& A Q U E S T I O N S A N S W E R S