Oracle Maximum Availability Architecture Best Practices for Oracle Exadata (CON8392)

(1)

Oracle Maximum Availability

Architecture Best Practices

for Oracle Exadata (CON8392)

Joseph Meeks, Director

High Availability Product Management, Oracle

Michael Smith, Consulting Member of Technical Staff MAA Development, Oracle

Rahul Pednekar, VP, Senior Oracle DBA

(2)

Rahul Pednekar

DBA- Bank Of America

Exadata and Maximum Availability Architecture

(3)

3

Cognos Reports

Equities Data _consumers.NET

ETL Real-time

Messages Batch Files

What is CRC?

• Centralized Data Warehouse for reference data, financial transactions, positions, and balances data for institutional investors

• Periodic Position calculation

• Millions of unique trades/non-trades are processed daily

• 6,000 reports generated daily, expected to grow by 10X in next few years

• Over 150 inbound feeds/message flows, over 300 workflows (Informatica)

• Database Size: Over 20 TB

Informatica

RDW IDS

Oracle 10g

Business & IT Challenges

• Complexity of the stack • Fight for System Resources • Regular miss of SLAs

• Unproductive use of technical resources

for job scheduling, database backup, resource management, etc.

• 20+ hours of backup /recovery of 2 large

10g DBs.

• DR site could not be used for backup due

SRDF method of replication

• Corruption could not be avoided due to

(4)

4

IDS

Cognos Reports

Equities Data _consumers.NET

Landing Staging ETL Real-time Messages Batch Files ETL Business Benefits

• NO SLA misses since going live in May 2011

• New applications that could not be deployed in pre-Exadata environment due to capacity and performance bottlenecks are deployed now

• Performance Improvement - ETL and Batch jobs are running up to 7X faster

• Generating over 10,000 reports daily

• Maximum Availability - No Single Point of Failure

• Disaster Recovery (DR) Database can be opened anytime if needed

Informatica

Exadata X2-2

(5)

5 Primary DC RDW IDS Pre-Exadata (10g DR) RDW IDS Pre-Exadata (10g Prod) 2. Break Mirror DR DC 1. Stop Databases 3. 11g DB pre-created. Data move using TTS EMC SRDF

4. Create Standby at primary DC using Compressed Backup from DR site

5. Reverse Roles

Two large 10g databases, total 20TB, were consolidated and migrated to Oracle 11gR2 in Exadata within 15 hours. DR solution was built by using Oracle Data Guard

(6)

6

• Broke storage mirror between Production and DR

• DR file systems were mounted on Oracle Exadata machine and multiple NIC

cards were used .

• Use of 4 NIC cards to pull data into Oracle Exadata significantly improved

data transfer rate during migration. Difference made by 4 NICs v/s 1 NIC in terms of throughput and elapsed time to migrate 20 Terabytes reduced from 33 hrs to 13 hrs.

• RMAN convert and TTS methodology used in migration. Multiple RMAN

convert scripts launched in parallel for faster data copy from 10g to 11g.

• Physical Standby with Maximum Performance Mode Created and roles were

(7)

7

• Minor changes to applications as it was already running on Oracle and Linux

• Database growing at 500GB per month vs. 250GB before oracle Exadata

• Full Backup takes <6 hours for 30 TB vs. 21 hours for 20TB in the old system

• Stats gathering now takes 6 hours vs. 48 hours in the old system

• Development team can concentrate on new development activities

• Unlike Storage replication (SRDF), Data Guard is protecting data from

corruptions

• Effective Use of Standby resources for backup and reporting (future)

(8)

DGMGRL> show configuration;

Configuration - gmfcdwp_conf

Protection Mode: MaxPerformance

Databases:

gmfcdwp_tel - Primary database gmfcdwp_lvt - Physical standby

database

Fast-Start Failover: DISABLED Configuration Status: SUCCESS 8 NY Data Center DW PA Data Center Data Guard Standby Dev/QA Primary X2-2 X2-2 X2-2

(9)

9  Daily ARCH generation at CRC ranges (8 instances) between 2 to 4 Terabytes/day

 Occasional spikes seen that goes beyond 10+ Terabytes for certain ad-hoc maintenances done in DB such as MERGE partitions, SPLIT partitions of big partition TABLES

(10)

10

DGMGRL> show database 'gmfcdwp_lvt'; Database - gmfcdwp_lvt

Role: PHYSICAL STANDBY Intended State: APPLY-ON

Transport Lag: 0 seconds Apply Lag: 1 second

Real Time Query: OFF Instance(s): gmfcdwp1 gmfcdwp2 gmfcdwp3 gmfcdwp4 gmfcdwp5 gmfcdwp6 gmfcdwp7 (apply instance) gmfcdwp8 Database Status: SUCCESS

(11)

11

 _{Benefits of Data Guard in Current Implementations.}

• Rapid provisioning of Standby with Compressed backup onto FRA and copying the same to Standby using ASMCP

• Use Data Guard Broker and Grid Control for easier mgmt, switchover, failover, etc. • Offload backup to DR Site and Backup Standby database using RMAN to FRA then

copy the backup files to tape using RMAN via backup recovery area

• Weekly FULL, incremental daily backup with compressed & block change tracking to improve the performance of backup

• RMAN compressed backup with 64 Channels on Full X2-2 gave us best performance – Under 6 hrs for 30TB

• Standby Database backups used for refreshing downstream application databases

 _{Next Steps to expand benefits of Data Guard at BAC.}

• Use of 10gE network between Standby and QA/Dev machines for faster refresh • Implement ACTIVE data guard for real-time reporting .

(12)

12

• Exadata is delivering both IT and Business Benefits

_{No SLA misses}

_{Excellent Performance}

_{Ability to support new business initiatives}

• Maximum Availability Architecture with Data Guard is

delivering:

_{Maximum Availability}

_{Effective Use of Standby resources for backup and reporting (future)}

_{Protection from data corruptions}

_{Faster refresh of downstream databases}