Transcript OracleWorld 2003

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 10g
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
Backup
Production
Database
Redo Apply
Network
DIGITAL DAT A ST ORAGE
DIGITAL DAT A ST ORAGE
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
Network
Redo Shipment




Backup
DIGITAL DAT A ST ORAGE
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

Logical Standby Database is an open, independent, active database





Standby
Redo Logs
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 10g
Customer Success Story – Sun Microsystems
Summary & Q/A
Oracle Data Guard 10g 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 10g 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 Oracle9i 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
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:00',
'YYYY-MM-DD HH24:MI:SS');
Enhanced DR with Flashback Database
Primary Database
Redo
Shipment
Real Time
Apply
Standby Database
Real Time
Reporting
No Delay!
Flashback
Log
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
Redo
Clients
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
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
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 10g
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
 7x24 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 SharePlex
Oracle Advanced Replication (OAR)
Oracle Real Application Clusters (RAC)
Oracle Data Guard (Standby)
We Chose Data Guard




7x24 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 Oracle9i)
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)
Copied
Copied
Archive
Archive
Redo
Redo
Oracle
Net
Primary
Database
LGWR
Managed
Recovery
Process (MRP)
On-line
Online
Redo
Redo
ARCn
Local
Local
Archive
Archive
Redo
Redo
Standby
Database
Database Architecture




50M archive redo logs
1 Gig of redo per day
Primary in Colorado
Standbys in North Carolina, Holland and
Singapore
 Database size currently 60 Gig
 Hardware Sun 6500, 280R, 4500
 Storage T3 partner pair fiber channel
Implementation of Physical Standby
1. Ensure primary database is in archive log mode
Note: In Data Guard 10g, 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 10g
SQL> startup mount;
 In Data Guard 10g, 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
 7x24 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
l3srv1
Standby 1
brdstn
Reports
Primary
Database
ARCn
l3srv2
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=l3srv1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=l3srv2)(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 9i, we keep 9i Primary init.ora on
Standby
 In Data Guard 10g, VALID_FOR eliminates this need
Archive Gap Management
 This is one of our favorite Data Guard 9i features
 Addresses critical issues such as:
–
–
What if network or server is down?
After failure resolution, how is the standby caught up?
 In Oracle8i Standby Database, we would manually fix
 In Oracle9i:
–
–
–
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 9i
 In Oracle8i Standby Database, add/drop
tablespace/datafile commands not automatically
propagated
–
DBA had to intervene
 In Oracle9i 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 10g 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 10g
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 papers1:






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 10g
Revolutionizes Availability and
Enables the Grid
Oracle Database 10g - RMAN and ATA
Storage in Action
11:00 AM
3:30 PM
Oracle Recovery Manager (RMAN)
10g: Reloaded
Oracle Data Guard: Maximum Data
Protection at Minimum Cost
1:00 PM
5:00 PM
Proven Techniques for Maximizing
Availability
Oracle Database 10g 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 10g Data
Warehouse Backup and Recovery:
Automatic, Simple, Reliable
8:30 AM in Moscone Room 104
Building RAC Clusters over
InfiniBand
Real Application Clusters
Data Guard
Database Backup & Recovery
Flashback Recovery
LogMiner, 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
OracleWorld online session
survey
Thank you.
QUESTIONS
ANSWERS