log stand ppt
DESCRIPTION
PPT for Logical Standby databaseTRANSCRIPT
“This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be
incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in
making purchasing decision. The development, release, and timing of any features or functionality described for Oracle’s products
remains at the sole discretion of Oracle.
Logical Standby UnleashedSession 938
Introduction
• Who’s Who?• Requirements of a Typical SQL Apply User• Thomson Legal & Regulatory and SQL Apply
• Who are they?• Database Technology Challenges• Using SQL Apply
• How Data Guard met Thomson’s Challenges• Looking forward – Now and in the Future
Who’s Who?
• Larry M. Carpenter• Oracle• Principal Product Manager
• Dan Dressel• Thomson Legal & Regulatory• Database Architect
• Joydip Kundu• Oracle• Senior Manager, Software Development
“This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”
Requirements of a TypicalSQL Apply User
Larry M. Carpenter
Requirements of a Typical SQL Apply Customer
• Recovery Point Objective (RPO) is Zero• No Data Loss
• Recovery Time Objective (RTO) is Zero• Failure of Server, Disk, Instance or Site
• High Availability Objective (HAO)• Up-to-date standby database accessible for
reads at all times• Zero Downtime Standby creation• Minimally affected by Oracle Upgrades
Thomson Legal & Regulatory(TLR)
Dan Dressel
Who are We?
• Customers• More than one million customers
worldwide• All of the top 100 global law firms• All Big 4 accounting firms
• More than 120,000 daily online users• More than 53 million web site page
views per month
Our Markets
• Primary Markets Served• Legal• Tax and Accounting• Corporate• Consulting• Specialized libraries• Government• Law Schools
Our Brands
TLR is about Information Solutions
• Headquartered in Eagan, MN.• Two world class data centers
• 200,000 square feet.• More than 700 TB of enterprise class disk.• Eight diesel generators with 16 MW of power
backup.• Technology is critical to our business.
• We make heavy use of relational database technology.
• We’ve had them all – Oracle, DB2, SQL Server, Redbrick, Informix, MySQL, Sybase, ADABAS.
Database Technology Challenges
• Drive the level of availability up• Make solutions incrementally scalable• Ensure scaling strategy is transparent to
applications and developers• Don’t sacrifice performance• Improve workload management capabilities• Make the environment easy to setup and
administer• Make it cheaper
Primary DatabaseRead Write
LGWR SYNCData Guard
Standby DatabaseRead
Online Users
Site APrimary Location
Site BStandby Location
Our Solution
System & Network Configuration
• 4-node RAC Primary• IBM E325 Servers
• Each with 2 CPUs and 12GB RAM
• 4-node RAC Standby• IBM E325 Servers
• Each with 2 CPUs and 12GB RAM
• SUSE9 64bit (United Linux 1.0)• NetApp Storage • Gigabit network
Data Guard Configuration
• Oracle Database 10.1.0.3 SQL Apply• Maximum Availability
• (LGWR SYNC AFFIRM)• Real Time Apply• 500GB database growing to 3TB• 2.8MB/sec peak redo generation• LOB intensive workload.
• Average LOB size 4-8k, but 5% of workload is comprised of Large Objects greater than 1MB
Lessons Learned
• Make sure software/hardware choices are all certified to work with one-another.
• Get vendor contacts for break/fix early.• Leverage clusters and Data Guard to their fullest.
• Don’t build new configurations for each application.• Establish simple, cookie-cutter models.• Make reusability a priority. Stay away from one-offs.• Compensate for performance issues with additional
hardware rather than complex, highly-specialized tuning when possible to preserve standard models.
In Conclusion
• Hire good Network Engineers. • Hire good DBAs.• Hire good System Administrators.
Meeting Thomson’s Challengeswith Data Guard
Joydip Kundu
Thomson’s Requirements
• Recovery Point Objective (RPO) • No Data Loss
• Recovery Time Objective (RTO)• Failure of Server, Disk, Instance or Site
• High Availability Objective (HAO)• Up-to-date standby database accessible for
reads at all times• Zero Downtime Standby creation• Minimally affected by Oracle Upgrades
Meeting the RPO
Archived Redo Logs
PrimaryDatabase
Transactions
LGWR
Archived Redo Logs
StandbyRedo Logs
LNS RFS
ARCHARCH
Online Redo Logs
LGWR SYNC
Configuring for the RPO
• Use Log Writer Synchronous Redo Transport• LOG_ARCHIVE_DEST_2=‘SERVICE=wlp01a
LGWR SYNC NET_TIMEOUT=30 REOPEN=15VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=wlp01a’
• Add Standby Redo Logs• Set the Protection mode
• SQL> ALTER SYSTEM SET STANDBY 2 TO MAXIMIZE AVAILABILITY;
Meeting the Server Level RTO
• RAC
Primary DatabaseRead Write
Site APrimary Location
Meeting the Site RTO
An up-to-date Logical Standby
Database
Real Time Apply
LSP
Archived Redo Logs
StandbyRedo Logs
RFS
ARCH
LGWR SYNC
Site BStandby Location
Configuring for the RTO
• Turn on Real time apply• SQL> ALTER DATABASE START LOGICAL
2 STANDBY APPLY IMMEDIATE;
• That’s it!
Behind the Scenes
StandbyRedoLog
Threads
Reader Preparers
Redo Records
LCRLCR
:Shared Pool
Builder
Analyzer
Transaction groups
Coordinator
Transactions sorted in dependency order
Applier
Standby Database
Log Mining
Apply Processing
Logical Change Records not grouped into transactions
Transactions to be applied
T1
T2
T3
T4
LOB Performance Increase
• Large Object Datatypes (LOB) supported since first release of SQL Apply in Oracle9iRelease 2.
• Ordering of LOB information in the Redo stream changed for better Performance in Oracle Database 10g Release 1.
• Requires compatibility 10.1.0.0.0
LOB Handling Oracle9i
• LOB Blocks appear in Redo before necessary information to identify the table where they belong.
• All LOB modifications must be staged in memory until necessary information identifying the base table row being modified arrives in the Redo.
• With larger out-of-line LOB data the amount of memory in the LCR can be high.
LOB’s in Oracle Database10g
• Table identifying information now logged into the redo stream before the LOB blocks.
• Redo Mining can now send identifying information to the Apply components earlier.
• Further LOB blocks for the same transaction are then passed directly to the Apply processes without staging in the LCR cache.
More Performance Enhancements
• SQL Apply in Oracle Database10g Release 1 has been enhanced to provide the following performance improvements
• Redo records associated with tables that are not maintained by SQL Apply are filtered out early, and thus no longer occupy space in LCR cache.
• The internal cache where tables maintained by SQL Apply has been improved to occupy less space.
• SQL Apply will batch inserts together whenever possible to improve performance
Meeting the RTOOracle Database 10g Release 2
• New Metrics to Monitor your RPO and RTO• V$DATAGUARD_STATS
• Estimated Failover Time (seconds)• Apply Lag (seconds)• Transport Lag (seconds)
• V$STANDBY_APPLY_SNAPSHOT• Redo Apply Rate (KB/second)
• Additional metric in Grid Control• Redo Generation Rate (KB/second)
Meeting the RTOOracle Database 10g Release 2
• Fast-Start Failover• Data Guard automatically fails over to the target
standby without requiring any manual steps.• Requires
• A Broker configuration with a new Broker capability• The Observer, which monitors the environment and
triggers a failover if necessary• Maximum Availability protection mode• Flashback Database
• Broker automatically reinstates the old primary database as a new standby database.
• Specialized events generated to handle Client Failover.
Fast-Start Failover
Standby SitePrimary Site
Observer
1. Data Guard in steady state – transmitting redo2. Observer monitoring state of the configuration
Fast-Start Failover
Standby SitePrimary Site
Observer
3. Disaster strikes the primary – connections lost
Fast-Start Failover
Standby SitePrimary Site
Observer
4. Observer times out5. Observer validates connection with target standby6. Observer begins Fast-Start Failover
Fast-Start Failover
Observer
Primary Site
7. Target standby automatically becomes new primary
Fast-Start Failover
Observer
Standby Site Primary Site
8. After old primary is repaired, Observer re-establishes connection9. Observer automatically reinstates old primary to be a new standby10. Redo transmission starts from new primary to new standby
Thomson – Test Results for Fast Start Failover
• Data Guard 10g Release 2 test results:• Fast-start failover can execute in under 10
seconds • Reinstatement of old primary in under 5 minutes
ZeroUp to 30 minutesTime for DBA to respond
Less than 40 secondsUp to 40 minutesTotal elapsed time to failover
Less than 10 seconds10 minutesTime for database failover
30 secondsNot possibleAutomatic delay set on failover(user configurable)
After Fast-Start FailoverBefore Fast-Start FailoverFailover Characteristics
Thomson – User Experiences with Fast Start Failover
“Fast-start failover testing has shown great potential. Our testing has shown failover to a standby database completes automatically in less than 40 seconds of elapsed time for most failures. The original primary database can be reinstated as a new standby in less than 5 minutes once the initial failure has been corrected. Fast-start failover is changing the way we think about database high availability.”
Thomson Legal & Regulatory
Meeting the HAOUp-to-date Readable Standby
• Logical Standby Databases are always open.• Read access to Production system data
• No change of database role required• Real-Time Apply keeps the data current
• Read Write Access to additional data• User added tables• Global Temporary Tables
Meeting the HAOStandby Creation
• Improving Logical Standby Database Creation • Oracle9i
• Cold backup (downtime) or• Quiesce users (R/W downtime)
• Oracle Database 10g Release 1• Zero Down Time Creation!
• Meeting the Production HA Requirement• Oracle Database 10g Release 2
• Still Zero Down Time Creation but even easier!
Zero Down Time InstantiationOracle Database 10g Release 1
On-LineBackup
Primary Database
Physical Standby Database
Create and Copy Logical Standby
Control File
Transport Service
Restore
1
2
3
4
Recovery5
Zero Down Time InstantiationOracle Database 10g Release 1
Activation
Change DBNAME and DBIDLogical Standby
Database!
Start SQL Apply Services
6
7
8
Physical Standby Database
Recover to Logical Standby
Physical Standby Database!
Start SQL Apply Services
3
4
Perform Dictionary Build
on Primary database
2
Primary Database
Create a Physical Standby1
Simple InstantiationOracle Database 10g Release 2
Logical Standby Database!
Meeting the HAOUpgrading Oracle
• Starting with Oracle Database 10g Release 1 SQL Apply databases can be used to perform Rolling Upgrades to Oracle patch sets or major releases.
• Read access during the process is not hindered at all.
• Read Write access suffers only minutes of interruption.
SQL Apply – Rolling Upgrades
Major ReleaseUpgrades
Patch SetUpgrades
Cluster Software & Hardware Upgrades
Initial SQL Apply Config
Clients Redo
Version X Version X
1
BA
Switchover to B, upgrade A
Redo
4
Upgrade
X+1X+1
BA
Run in mixed mode to test
Redo
3X+1X
A B
Upgrade node B to X+1
Upgrade
LogsQueue
X2
X+1
A B
Airbus – User Experiences with Rolling Database Upgrades
“Data Guard's rolling upgrade capability is amazing. Airbus tested upgrading from Oracle Database 10g Release 1 to Oracle Database 10g Release 2. The elapsed time for the database upgrade was about 3 hours. But applications were available for all but less than 2 minutes while a Data Guard switchover was executed, quickly transitioning production from Release 1 over to Release 2. Oracle Data Guard has made great strides in helping us achieve our 100% availability goal.”
Werner Kawollek Application Management Operations Airbus Deutschland GmbH
In Conclusion
Larry M. Carpenter
Requirements Met!
• Recovery Point Objective (RPO) is Zero• No Data Loss occurs at Failover
• Recovery Time Objective (RTO) is Zero• Handles Failure of Server, Disk, Instance or Site
• High Availability Objective (HAO)• Standby database is accessible for reads at all
times and is up-to-date.• Standby creation requires no downtime• Oracle Upgrades have minimal effect
MAA Best Practices Home Page
http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm• HA Best Practices for Oracle Database
• Oracle Database High Availability Overview 10g Release 2 - Documentation • Oracle Database High Availability Architecture and Best Practices 10g Release 1• Oracle Database 10g Best Practices: Data Guard Redo Apply and Media Recovery • Oracle Database 10g Best Practices: Data Guard SQL Apply • Oracle Database 10g Best Practices: Data Guard Role Transitions and Streams • Using Recovery Manager with Oracle Data Guard in Oracle Database 10g• Oracle Database 10g Best Practices: Migration to Automatic Storage Management• Best Practices for Creating a Low-Cost Storage Grid for Oracle Databases • Oracle9i Data Guard: Primary Site and Network Configuration Best Practices • Oracle9i Fast-Start Checkpointing Best Practices
High Availability Demos/Sessions From Oracle Development
Sessions - Monday, Sep 191:30-2:30 pm, Room 303 - Optimizing Linux I/O3:00-4:00 pm, Room 104 - The Future of Database Information Technology4:30-5:30 pm, Room 103 - What They Didn't Print in the DOC - HA Best Practices by Gurus from Oracle's Maximum Availability Architecture Team
3:00-4:00 pm, Room 104 - Logical Standby Unleashed4:30-5:30 pm, Room 104 - Best Practices for Oracle Database 10g Backup and Recovery
Demogrounds - Monday, Sep 19 – Thursday, Sep 22Oracle Data GuardILM and Storage
Oracle Secure BackupRMAN, Flashback, and Online Redefinition
Sessions - Tuesday, Sep 20
High Availability Sessions From Oracle Development
Sessions - Thursday, Sep 221:00-2:00 pm, Room 104 - Minimizing Application Development Time Using Flashback: A Customer Case Study2:30-3:30 pm, Room 104 - Best Practices To Achieve Business Continuity Using Oracle Applications and Oracle Database Technology4:00-5:00 pm, Room 104 - Best Practices for Automatic Failover Using Oracle Data Guard 10g Release 2
Sessions - Wednesday, Sep 2111:00 am-12:00 pm, Room 104 - Improve Your Tape Backup Results with Oracle Secure Backup3:00-4:00 pm, Room 304 - Implementing Information Lifecycle Management (ILM) using the Oracle Database
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S