Oracle DataGuard

Concepts and Architecture

Brian Hitchcock
OCP 10g DBA
Sun Microsystems
brian.hitchcock@sun.com
brhora@aol.com
www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 1

Oracle DataGuard
Maintains a standby database

Archived redo logs on primary

Sent to standby and applied

Simple idea
Many configuration options

No attempt to cover them all here

Discuss several specific sets of options

Can become very complicated

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 2

Oracle DataGuard
Comes in Two Flavors

Physical Standby
When I was young this was all we had
Read-only when not applying redo logs

Logical Standby
Can be read-write while applying redo logs
Can add db objects to standby
Indexes for reporting
Many options

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 3

Themes
What is a standby?

standby implies specific capabilities

Ready for failover
Complete copy of primary
No need to verify standby before failover

When is a standby not a standby?

When it doesnt provide what name implies

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 4

What is a standby database?

Database that we can fail over to
Kept closely synchronized with primary db

Up to the minute
Once a day

Primarily dedicated to being ready for failover

May also be used for reporting

Guaranteed to be an exact copy

To the point of last synchronization

Can catch up as long as redo logs available

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 5

What is a standby database?

No question about standby

Is it a complete copy?
Is it ready for failover

Standby for reporting

Is standby providing accurate data for reports?

No one can change standby

No changes to data/objects in standby

If changes made to standby

Should be very obvious

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 6

Before DataGuard?
Scripts, cron jobs

Copy archived redo logs from primary to standby

Apply redo logs on standby periodically
When script(s) executes

Standby db cant be used for anything else

Constantly recovering

Failover

Open standby db with resetlogs

Cant be standby again without rebuild
Cant fail back to primary without rebuild

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 7

Standby
Cant change standby db objects

Primary
Database

Standby
Database

Standby mounted, recovering

Can only be opened resetlogs
Once opened, cant switch back

Online Redo
Logs

Archived
Redo Logs

Scripts, Cron Jobs

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 8

Why DataGuard?
Part of Oracle RDBMS

No scripts or cronjobs to maintain

Supported by Oracle
Can switch between primary/standby repeatedly
Redo sent and applied continuously (options)

Standby db can be used for other things

Read-only when not applying redo logs

Read-write with limitations (Logical Standby)

Failover

Primary fails, standby becomes primary

Cant switch back without rebuild

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 9

DataGuard Classic*
*Before choice of physical or logical standby

Primary
Database

Standby
Database

Standby mounted, recovering

or
Standby read-only, no apply

Can switch back and forth

-Primary becomes standby
-Standby becomes primary

Online Redo
Logs

Archived
Redo Logs

DataGuard

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 10

Applying Redo Logs

Default

Archived redo log complete on primary

Sent and applied to standby

Standby Redo Logs (Optional)

Redo sent to standby as it is written on primary

Real-time apply

No waiting for primary archive redo log complete

No waiting for primary log switch

Not to be confused with init.ora parameter

VALID_FOR=(STANDBY_LOGFILES,)

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 11

Standby Redo Logs

Primary
Database

Primary redo written

continuously to standby
No waiting for primary log
switch

Standby
Database

Online Redo
Logs

Archived
Redo Logs

Archived
Redo Logs

DataGuard

Standby
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 12

Typical Configurations
Standby dedicated for failover

Primary db
configured for log switch every 15 minutes

Standby db
always applying redo logs
Behind primary by 15 minutes at most
Less if primary writes redo logs more often
Use standby redo logs
Very close to primary at all times

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 13

Typical Configurations
Standby for failover and reporting

Primary db
configured for log switch every 15 minutes

Standby db
apply redo logs 8pm to 5am
Long enough to apply 24 hours of redo logs
Stop applying redo logs 5am to 8pm
Standby up to 15 hours behind primary
Open for reporting 15 hours a day
Reports use old data
Standby data doesnt change from 5am to 8pm

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 14

Protection Modes
Maximum Performance (default)

Primary sends transactions to standby

Doesnt wait for them to commit

Maximum Protection

Primary stops if standby doesnt commit

Requires standby redo logs

Maximum Availability

Max protection but primary doesnt stop

Switch to max perf until standby catches up
Switch back to max availability

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 15

DataGuard Physical Standby

Cant change standby db objects

Online Redo
Logs

Log Transport
Services

Log Apply
Services

Physical
Standby
Database

Primary
Database

Standby mounted, recovering

Or
Standby read-only, no apply
Can switch back and forth

Archived
Redo Logs

DataGuard

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 16

Physical Standby a Standby?

Failover

Ready to failover?
Block by block copy of primary
If any changes made, cant failover
Standby had to be opened resetlogs to change

Refresh

No questions
Complete rebuild from primary
Only need backup of primary, nothing else

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 17

Physical Standby
Just a copy of production
Only needed in production

Dont need to do any dev, testing

Dont need backups

Recover from backups of primary database

What is added to your infrastructure?

One database
No backups

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 18

Physical vs Logical Standby

Physical Standby

classic standby
Cant connect to db while applying redo logs
Can be read only when not applying redo logs

Logical Standby

Applies redo logs from primary all the time

As long as SQL apply process is running

Open for users for read and write

Various restrictions
Many configuration options

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 19

DataGuard Logical Standby

Can change db objects (restrictions)

SQL Apply
Services

Logical
Standby
Database

Primary
Database

Standby open while redo applied

Online Redo
Logs

Log Transport
Services

Online Redo
Logs
Archived
Redo Logs

DataGuard

Archived
Redo Logs

Two sets of archived

redo logs

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 20

DataGuard Logical Standby

Logical
Standby
Database

Primary
Database

SQL applied to Logical Standby

Just like any other database user

Online Redo
Logs

Archived
Redo Logs

DataGuard

Archived
Redo Logs

LogMiner
Extract SQL

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 21

Logical Standby
Standby is open, read-write
Anything copied from primary

Maintained by DataGuard
Depending on Guard status

DataGuard doesnt maintain things added to

standby

Guard status restricts who can update

This can be bypassed

Alter session or database
Needed to import db objects into standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 22

Logical Standby Guard Status

All

SYS can modify anything in standby database

Standby

SYS can modify anything

Other users can modify objects not maintained
by DataGuard
Subject to normal user privs

None

Standby not protected by DataGuard

Any user can alter db objects
Subject to normal user privs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 23

Skipping
Schemas, tables, transactions

May be skipped automatically

Can be skipped manually

Why anything skipped?

Performance
Unsupported db objects
More later

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 24

Logical Standby a Standby?

Failover

Ready to failover?
Not sure if standby is a complete copy
Tables, schemas, transactions skipped?
Standby can be changed
Failover not prevented
Changes can be made
No warning that changes made

Who knows what you are failing over to?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 25

Logical Standby a Standby?

Refresh

Additional db objects in standby need backup

Refresh from primary wipes out these objects

Complete rebuild from primary

Insert additional db objects

If standby fails
Need to recover standby db from backups
Extract additional db objects from standby
Refresh logical standby
Insert additional db objects

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 26

Refresh Standby
Physical Standby

Just refresh
Standard process
No debate

Logical Standby

Refresh wipes out unique db objects

Must extract them first
Refresh from primary
Load unique db objects

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 27

Standby Fails
Physical

No problem, just a copy of primary

Refresh from primary

Logical

What about unique db objects

Recover standby from standby backups
Extract unique db objects
Refresh standby
Insert unique db objects

Logical standby db must be backed up

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 28

Recovering Logical Standby

You wanted Logical

To add things
Indexes for reporting

How to recover Logical?

Rebuild physical from primary

Convert to logical
How to recreate the additional db objects/data?
If indexes, recreate them
If data extracted from copy of primary?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 29

How It Works

Log_archive_dest_n
Where n is 1 to 10
Specific value doesnt matter

Basic DataGuard setup

Where to send archived redo logs?
Primary

Log_archive_dest_1
Location for local archived redo logs
Location=/arch01/NY Valid_for=(All_Logfiles, All_Roles)
Log_archive_dest_2
Sends archived redo logs to service name
Service name points to standby
Service=LA Valid_for=(Online_logfiles, Primary_Role)
Tnsnames.ora
Contains entry for service name for standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 30

How it works
Standby

Log_archive_dest_n
Where n is 1 to 10
Specific value doesnt matter

Log_archive_dest_1
Location for local archived redo logs
Not used while db is physical standby
Location=/arch01/LA Valid_for=(All_Logfiles, All_Roles)

Log_archive_dest_2
Location receives archived redo logs from primary
Location=/arch02/LA Valid_for=(Standby_logfiles,
Standby_Role)

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 31

Symmetrical init.ora/spfile
Use three log_archive_dest_n parameters

Setup on primary and standby

Dont need to change for failover
Dont need to change for fail-back

Create tnsnames.ora entry

On primary
Pointing to standby

On standby
Pointing to primary

Less maintenance for frequent failover/back

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 32

DataGuard init.ora/spfile
Active

Active

Inactive

Db_unique_name=NY_DB

Primary
Database

Online Redo
Logs

Archived
Redo Logs

Inactive

Db_unique_name=LA_DB

Log_archive_dest_1
LOCATION=/arch01/NY
VALID_FOR=(ALL_LOGFILES,
ALL_ROLES)

Log_archive_dest_1
LOCATION=/arch01/LA
VALID_FOR=(ALL_LOGFILES,
ALL_ROLES)

Log_archive_dest_2
LOCATION=/arch02/NY
VALID_FOR=(STANDBY_LOGFILES,
STANDBY_ROLE)

Log_archive_dest_2
LOCATION=/arch02/LA
VALID_FOR=(STANDBY_LOGFILES,
STANDBY_ROLE)

Log_archive_dest_3
SERVICE=LA
VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE)

Log_archive_dest_3
SERVICE=NY
VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE)

Tnsnames.ora
LA=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<hostnameLA>)(PORT=1521))
(CONNECT_DATA= (SID=LA_DB) ) )

Active for Logical Standby

Tnsnames.ora
NY=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<hostnameNY>)(PORT=1521))
(CONNECT_DATA= (SID=NY_DB) ) )

Standby
Database

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 33

DataGuard init.ora/spfile
Active

Inactive

Active for Logical Standby

Db_unique_name=NY

Standby
Database

Archived
Redo Logs

Active

Inactive

Db_unique_name=LA

Log_archive_dest_1
LOCATION=/arch01/NY
VALID_FOR=(ALL_LOGFILES,
ALL_ROLES)

Log_archive_dest_1
LOCATION=/arch01/LA
VALID_FOR=(ALL_LOGFILES,
ALL_ROLES)

Log_archive_dest_2
LOCATION=/arch02/NY
VALID_FOR=(STANDBY_LOGFILES,
STANDBY_ROLE)

Log_archive_dest_2
LOCATION=/arch02/LA
VALID_FOR=(STANDBY_LOGFILES,
STANDBY_ROLE)

Log_archive_dest_3
SERVICE=LA
VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE)

Log_archive_dest_3
SERVICE=NY
VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE)

Tnsnames.ora
LA=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<hostnameLA>)(PORT=1521))
(CONNECT_DATA= (SID=LA_DB) ) )

Tnsnames.ora
NY=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<hostnameNY>)(PORT=1521))
(CONNECT_DATA= (SID=NY_DB) ) )

Primary
Database

Online Redo
Logs

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 34

DataGuard Reporting
Logical
Standby
Database

Primary
Database

Database objects added to

Logical standby database
-- indexes for reporting

Online Redo
Logs

Archived
Redo Logs

Database objects
Copied from primary
Maintained by DataGuard

DataGuard

Archived
Redo Logs

LogMiner
Extract SQL

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 35

Rebuild Reporting Standby

If additional standby db objects have no data

Indexes for reporting

Refresh from primary wipes out indexes

Refresh standby

Execute script to recreate indexes

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 36

Create DataGuard Standby

Physical Standby
Logical Standby

Create physical standby

Convert to logical standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 37

Create Physical Standby

On Primary database

Enable Forced Logging

Create password file
Setup init.ora/spfile parameters
Verify archiving enabled
Backup db (hot or cold)
Create standby control file
Dont use backup control file

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 38

Create Physical Standby

On Standby database

Copy db backup files from primary

Copy standby control file from primary
Setup init.ora/spfile parameters
Db_name same as primary db_name

Start physical standby db

Verify physical standby working

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 39

Convert to Logical Standby

On Primary database

Build LogMiner dictionary

On Standby database

Stop redo apply

Convert database to logical standby
Change db_name

Restart db
Open resetlogs
Verify logical standby working

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 40

Cascaded Standbys
DataGuard supports cascading standbys
Primary sends redo to

Physical standby A
Logical standby B

Physical standby A sends redo to

Physical standby B
Physical standby C

Logical standby B sends redo to

Dont you have enough to worry about?
www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 41

Real World Example

What Im supporting now

Logical standby
2 added schemas for custom app
Primary db supports Oracle Applications 11i

Requirements

Provide copy of primary 11i db for reporting

Oracle Discoverer

Provide copy of 20-30 tables for custom app

Additional schemas store custom app data
Extracted from standby copies of primary tables

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 42

DataGuard Logical Real World

Primary
Database

Database objects added to

Logical standby database
-- 2 schemas for custom app
-- store data extracted from
standby copies of primary
tables

Logical
Standby
Database

Database objects
Copied from primary
Maintained by DataGuard

Procs extract data

Online Redo
Logs

Archived
Redo Logs

DataGuard

Archived
Redo Logs

Processed Data
Stored

LogMiner
Extract SQL

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 43

Real World Refresh/Recovery

Refresh

Backup standby db objects not in primary

Refresh standby from primary
Recreate additional db objects in standby

If Logical standby fails

Db objects not in primary are lost

Need to recover standby db
Extract db objects
Refresh standby from primary
Recreate additional db objects in standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 44

Logical Standby Issues

Does this sound like a standby?

Unsupported

Data types
BFILE, user-defined types

PL/SQL supplied packages

That modify metadata, DBMA_JAVA etc.

Other things, see manual

If unsupported, automatically skipped

No notification of skipped objects

Examine primary for unsupported things

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 45

Logical Standby Issues

Does this sound like a standby?

Processing

Everything done on primary has to be extracted

from redo logs and applied to standby db
Apply process is just another db user session

Primary db

Objects may not be well designed

Tables with poor (or no) indexes

Updates on primary can be very slow when

applied as SQL to standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 46

Logical Standby Issues

Does this sound like a standby?

If applying to standby too slow

May have to skip for performance

To keep standby in synch per business reqmts
Ready for reporting once per day

Primary SQL depends on files on primary

Create java class

Class files not on standby
DataGuard doesnt maintain filesystems

No notification of such problems

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 47

Logical Standby Issues

Performance impact

At any time, slow SQL may take days to complete

If you need standby in synch once per day
Must skip table

If you must have this table in standby

Must do full refresh from primary

If you can and do skip the table

Cant support requirement for reporting on standby

You never know when this will happen

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 48

Logical Standby Issues

Logical Standby is like an application

Needs control, review, careful release process

If Logical Standby is an application

Need dev, alpha, beta, prod

Logical standby database for dev, alpha, beta
Backups for additional dbs
Add space to primary production database?
Need to add space to 4 primary, 4 logical dbs

What is added to your infrastructure?

4 standby databases
Backups for 4 standby databases
Does this sound like a standby?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 49

Logical Standby Issues

How can standby get out of synch?

Someone bypassed guard and left it off

Someone left guard altered to NONE
SYS altered db objects in standby
Schema, table, transaction skipped
No record of transactions skipped

No utilities
Compare logical standby to primary
Compare tables standby/primary

Does this sound like a standby?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 50

Logical Standby Issues

Logical apply process examines standby

When applying update from primary to standby

Compares
Previous values on primary
Current values on standby

Does this sound like a standby?

If different, refuses to apply update from primary

Apply process fails
Cant apply anything more
must cure issue or skip table/transaction

Differences can go undetected indefinitely

Until next time primary updates object

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 51

Logical Standby Issues

Does this sound like a standby?

Need backups of logical standby database

If there are any unique database objects

If there arent, why use logical standby?

Refresh or rebuild

Have to recover db objects unique to standby

After refresh

Previously skipped tables

Do we skip them again?
Do we wait for them to need to be skipped?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 52

Real World Redesign

Separate database dedicated to custom application

Primary
Database
Physical
Standby
Database

-- 2 schemas for custom app

-- store data extracted from
tables in physical standby
database

Online Redo
Logs
Procs extract data

Archived
Redo Logs

DataGuard

Processed Data
Stored

Archived
Redo Logs

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 53

Real World Redesign

Custom Application database

Dedicated for app schemas

Db link into physical standby
Backup provides recovery of app schemas

Physical Standby database

Let DataGuard do what it does well

None of the Logical Standby issues
Can be used as read-only for reporting
When not applying redo logs

Support an extra database

Dont have to support Logical Standby

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 54

Recommendation
Logical for reporting

Copy of primary
Add indexes to speed reporting
Add tables for aggregates

Objects added to standby

Easily recreated from a SQL script

Contain data that can always be regenerated
from copy of primary

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 55

Recommendation
Physical standby

Is solid, dependable
No issues

Logical standby

Is it really a standby?
Is it ready for failover?
Is it providing complete data for reports?
Lots of issues
Is it worth the effort/risk?

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 56

DataGuard Support Issues

Covered in 2nd presentation
Oracle DataGuard Logical Standby Support
Issues

www.brianhitchcock.net
Brian Hitchcock October 23, 2007

Page 57