log stand ppt
DESCRIPTION
PPT for Logical Standby databaseTRANSCRIPT
![Page 1: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/1.jpg)
“This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”
![Page 2: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/2.jpg)
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.
![Page 3: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/3.jpg)
![Page 4: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/4.jpg)
Logical Standby UnleashedSession 938
![Page 5: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/5.jpg)
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
![Page 6: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/6.jpg)
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.”
![Page 7: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/7.jpg)
Requirements of a TypicalSQL Apply User
Larry M. Carpenter
![Page 8: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/8.jpg)
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
![Page 9: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/9.jpg)
Thomson Legal & Regulatory(TLR)
Dan Dressel
![Page 10: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/10.jpg)
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
![Page 11: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/11.jpg)
Our Markets
• Primary Markets Served• Legal• Tax and Accounting• Corporate• Consulting• Specialized libraries• Government• Law Schools
![Page 12: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/12.jpg)
Our Brands
![Page 13: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/13.jpg)
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.
![Page 14: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/14.jpg)
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
![Page 15: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/15.jpg)
Primary DatabaseRead Write
LGWR SYNCData Guard
Standby DatabaseRead
Online Users
Site APrimary Location
Site BStandby Location
Our Solution
![Page 16: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/16.jpg)
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
![Page 17: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/17.jpg)
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
![Page 18: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/18.jpg)
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.
![Page 19: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/19.jpg)
In Conclusion
• Hire good Network Engineers. • Hire good DBAs.• Hire good System Administrators.
![Page 20: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/20.jpg)
Meeting Thomson’s Challengeswith Data Guard
Joydip Kundu
![Page 21: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/21.jpg)
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
![Page 22: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/22.jpg)
Meeting the RPO
Archived Redo Logs
PrimaryDatabase
Transactions
LGWR
Archived Redo Logs
StandbyRedo Logs
LNS RFS
ARCHARCH
Online Redo Logs
LGWR SYNC
![Page 23: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/23.jpg)
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;
![Page 24: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/24.jpg)
Meeting the Server Level RTO
• RAC
Primary DatabaseRead Write
Site APrimary Location
![Page 25: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/25.jpg)
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
![Page 26: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/26.jpg)
Configuring for the RTO
• Turn on Real time apply• SQL> ALTER DATABASE START LOGICAL
2 STANDBY APPLY IMMEDIATE;
• That’s it!
![Page 27: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/27.jpg)
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
![Page 28: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/28.jpg)
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
![Page 29: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/29.jpg)
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.
![Page 30: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/30.jpg)
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.
![Page 31: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/31.jpg)
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
![Page 32: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/32.jpg)
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)
![Page 33: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/33.jpg)
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.
![Page 34: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/34.jpg)
Fast-Start Failover
Standby SitePrimary Site
Observer
1. Data Guard in steady state – transmitting redo2. Observer monitoring state of the configuration
![Page 35: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/35.jpg)
Fast-Start Failover
Standby SitePrimary Site
Observer
3. Disaster strikes the primary – connections lost
![Page 36: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/36.jpg)
Fast-Start Failover
Standby SitePrimary Site
Observer
4. Observer times out5. Observer validates connection with target standby6. Observer begins Fast-Start Failover
![Page 37: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/37.jpg)
Fast-Start Failover
Observer
Primary Site
7. Target standby automatically becomes new primary
![Page 38: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/38.jpg)
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
![Page 39: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/39.jpg)
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
![Page 40: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/40.jpg)
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
![Page 41: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/41.jpg)
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
![Page 42: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/42.jpg)
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!
![Page 43: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/43.jpg)
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
![Page 44: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/44.jpg)
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
![Page 45: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/45.jpg)
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!
![Page 46: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/46.jpg)
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.
![Page 47: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/47.jpg)
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
![Page 48: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/48.jpg)
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
![Page 49: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/49.jpg)
In Conclusion
Larry M. Carpenter
![Page 50: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/50.jpg)
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
![Page 51: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/51.jpg)
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
![Page 52: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/52.jpg)
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
![Page 53: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/53.jpg)
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
![Page 54: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/54.jpg)
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
![Page 55: Log Stand Ppt](https://reader031.vdocuments.us/reader031/viewer/2022020207/552902c2550346432e8b45cb/html5/thumbnails/55.jpg)