Data Guard Technology Overview

1
Oracle Maximum Availability Architecture
(MAA) Workshop Asaf Lev Ofir
Manor asaf.lev_at_oracle.com ofir.manor_at_oracle.com
http//tech.theoracles.co.il
2
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

3
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

4
Oracles Integrated HA Solutions
System Failures
Real Application Clusters
UnplannedDowntime
Automatic Storage ManagementFlashbackRMAN
Flash Recovery AreaData Guard
Data Failures
System Changes
Online ReconfigurationRolling Upgrades
PlannedDowntime
Data Changes
Online Redefinition
5
Oracles Integrated HA Solutions
System Failures
Real Application Clusters
UnplannedDowntime
Automatic Storage ManagementFlashbackRMAN
Flash Recovery AreaData Guard
Data Failures
System Changes
Online ReconfigurationRolling Upgrades
PlannedDowntime
Data Changes
Online Redefinition
6
Technology Workshops

• Workshop Environment
• Windows
• Vmware
• Virtual Machines running Linux RedHat 4 U4
• Oracle ASM
• Oracle Databases 10.2.0.1
• Oracle Grid Control
• Firefox, Swingbench

7
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

8
Automatic Storage Management

• Eliminates need for conventional file system and
  volume manager
• Automatic I/O load balancing
• Stripes data across disks to balance load
• Best I/O throughput
• Automatic mirroring
• Capacity on demand
• Add/drop disks online
• ASMCMD

9
Dynamic Rebalancing

• Automatic online rebalance whenever storage
  configuration changes

Disk Group
10
Dynamic Rebalancing

• Automatic online rebalance whenever storage
  configuration changes
• Only move data proportional to storage added

Disk Group
11
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

12
Flashback for Rapid Recovery from Human Error
FlashbackData Archiveand Transaction
13
Oracle Database 10g - Flashback technology

• Flashback query
• Flashback version query
• Flashback transaction query
• Flashback table
• Flashback database
• Flashback drop
• Flashback Data Archive (Total Recall 11G)

14
Flashback Query

• SELECT FROM EMP AS OF
• TIMESTAMP
• (SYSTIMESTAMP INTERVAL '1' HOUR')
• SELECT FROM EMP AS OF
• SCN 487320

15
Flashback Time Navigation

• Flashback Query
• Query all data at point in time
• Flashback Versions Query
• See all versions of a row between two times
• See transactions that changed the row
• Flashback Transaction Query
• See all changes made by a transaction

16
Flashback Database

• A new strategy for point in time recovery
• Flashback Log captures old versions of changed
  blocks
• Think of it as a continuous backup
• Replay log to restore DB to time
• Restores just changed blocks
• Flashback Database to 205 PM

17
Flashback Error Correction

• Recovery at all levels
• Database Level
• Flashback Database restores the whole database to
  time
• Uses Flashback Logs
• Table Level
• Flashback Table restores rows in a set of tables
  to time
• Uses UNDO in database
• Flashback Drop restores a dropped table or a
  index
• Recycle bin for DROPs
• (bin tables)
• Row Level
• Restore individual rows
• Uses Flashback Query

Database
Customer
Order
18
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

19
Data Guard Best Failure Protection at Lowest Cost
Automatic Failover
Physical or Logical Standby DB
Production Database
Synchronous Redo Shipping
Data Guard

• Comprehensive data protection e.g. data
  corruptions dont propagate
• Failover in seconds standby is already
  synchronized
• Zero data loss over large distances advantage
  over array-based mirroring technologies

20
Data Guard Redo Apply
Physical Standby Database
Primary Database
Data Guard Broker
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

21
Data Guard SQL Apply
Additional Indexes Materialized Views
Logical Standby Database
Primary Database
Data Guard Broker
Transform Redo to SQL and Apply
ContinuouslyOpen 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

22
Flexible Data Protection Modes
Protection Mode Risk of Data Loss Redo Shipment
Maximum Protection Zero Data Loss Double Failure Protection Synchronous redo shipping to 2 sites
Maximum Availability Zero Data Loss Single Failure Protection Synchronous redo shipping
Maximum Performance Minimal data loss usually 0 to few seconds Asynchronous redo shipping
Balance cost, availability, performance, and
transaction protection
23
Enhanced DR with Flashback Database
Redo Shipment
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

24
Active Data Guard 11g Real-time Query Improve
Quality of Service
Real-time Query
Real-time Queries
Continuous Redo Shipment and Apply

• Offload read-only queries to physical standby
• Offload fast incremental backups to physical
  standby

Physical Standby Database
Production Database
25
The Workshop http//tech.theoracles.co
.il/
26
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

27
Switchover and Failover

• Primary and Standby role transitions
• Switchover
• Planned role reversal
• No database reinstantiation required
• Used for maintenance of OS or hardware
• Failover
• Unplanned failure (e.g. disasters) of primary
• Primary database must be reinstantiated / flashed
  back 10g
• Initiated using simple SQL / GUI interface
• Data Guard automates the processes involved

28
SQL Apply Rolling Database Upgrades
Upgrade
Redo
Clients
Logs Queue
Version X
Version X
X
X1
1
2
Initial SQL Apply Config
Upgrade node B to X1
Redo
Redo
Upgrade
X1
X1
X1
X
Switchover to B, upgrade A
4
Run in mixed mode to test
3
29
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

30
Online Redefinition

• All indexing operations can be done online
• Create new index, move index, defragment index
• Tables can be Reorganized Redefined online
  (DBMS_REDEFINITION)
• Table contents are copied to a new table
• Defragments and allows changing location, table
  type, partitioning
• Contents can be transformed as they are copied
• Can change columns, types, sizes - specified
  using SQL Select
• Updates and Queries can continue uninterrupted

Transform
Copy Table
Source Table
Result Table
GUI interface to make it simple
Store Updates
Update Tracking
Continuous Queries Updates
Transform Updates
31
Agenda

• HA Overview
• ASM Management
• Flashback
• Creating Data Guard
• DataGuard Switchover
• Online Redefinition
• TSPITR

32
Tablespace Point in Time Recovery Steps

• RMAN backup for recover tablespace cmd
• RMAN TSPITR
• Star second instance ( auxiliary instance)
• Takes target tablespaces offline in the target DB
• Restores a backup control file from a point in
  time before the target time to the auxiliary
  instance
• Restores the datafiles from the recovery to the
  auxiliary instance.
• Recovers the restored datafiles in the auxiliary
  instance to the specified time
• Opens auxiliary database (RESETLOGS)

33
Tablespace Point in Time Recovery Steps

• Exports the dictionary metadata about objects to
  the target database
• Shuts down the auxiliary instance
• SWITCH commands on the target instance, so target
  database control file now points to the datafiles
  in the recovery set recovered at the auxiliary
  instance
• Imports the dictionary metadata from the
  auxiliary instance to the target instance,
  allowing the recovered objects to be accessed
• Deletes all auxiliary set files

34
The Workshop http//tech.theoracles.co.il/
35
Summary http//tech.theoracles.co.il/
36
Oracle Maximum Availability Architecture
Integrated Deployment of Oracle HA Technologies
Active Standby Site
Production Site
Real Application Clusters Oracle
Clusterware Fault Tolerant Server Scale-Out
Online Table Redefinition
Online H/W S/W Upgrade
Data Guard / Streams Active-active Sites, Data
Protection, Rolling Upgrades
Primary Database
Automatic Storage Management Fault Tolerant
Storage Scale-Out
Storage
Flashback Technologies Move Back in Time to
Correct Errors
Recovery Manager Oracle Secure Backup Data
Protection Archival
37
Oracle Maximum Availability Architecture
Integrated set of Oracle HA Best Practices

• Technology alone is not enough
• MAA is also a blueprint for achieving HA
• Brings together all that has been discussed
• Operational best practices
• Prevent, tolerate, and recover
• Tested, validated, and documented
• Database, Middleware, Apps
• Storage, Cluster, Network

MAA
Prevent,
Tolerate, and
Recover
From Outages
otn.oracle.com/deploy/availability
Maximum Availability Unbreakable Architecture
Best Practices