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 doesn‟t 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 May also be used for reporting To the point of last synchronization Can catch up as long as redo logs available
Primarily dedicated to being ready for failover
–
Guaranteed to be an exact copy
–
–
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
Is standby providing accurate data for reports? No changes to data/objects in standby
Standby for reporting
–
No one can change 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 can‟t be used for anything else
–
Constantly recovering Open standby db with resetlogs Can‟t be standby again without rebuild Can‟t fail back to primary without rebuild
Page 7
Failover
– – –
www.brianhitchcock.net
Brian Hitchcock October 23, 2007
Standby
Can’t change standby db objects
Primary Database
Standby Database
Standby mounted, recovering Can only be opened resetlogs Once opened, can’t 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) Read-only when not applying redo logs Read-write with limitations (Logical Standby)
Standby db can be used for other things
– –
Failover
– –
Primary fails, standby becomes primary Can‟t switch back without rebuild
Page 9
www.brianhitchcock.net
Brian Hitchcock October 23, 2007
DataGuard Classic*
*Before choice of physical or logical standby
Primary Database
Standby Database
Standby mounted, recovering or Standby read-only, no apply
Online Redo Logs
Can switch back and forth -Primary becomes standby -Standby becomes primary
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
Redo sent to standby as it is written on primary
Real-time apply
Standby Redo Logs (Optional)
– – –
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 doesn‟t 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 Doesn‟t wait for them to commit Primary stops if standby doesn‟t commit Requires standby redo logs Max protection but primary doesn‟t stop Switch to max perf until standby catches up
Switch back to max availability
Maximum Protection
–
–
Maximum Availability
– –
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 15
DataGuard Physical Standby
Can’t change standby db objects
Primary Database
Physical Standby Database
Standby mounted, recovering Or Standby read-only, no apply Can switch back and forth
Log Apply Services
Online Redo Logs
Archived Redo Logs Log Transport Services
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, can‟t 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
–
Don‟t need to do any dev, testing
Don‟t need backups
–
Recover from backups of primary database
One database No backups
What is added to your infrastructure?
– –
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 18
Physical vs Logical Standby
Physical Standby
– – –
„classic‟ standby Can‟t 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)
Primary Database
Logical Standby Database
SQL Apply Services
Standby open while redo applied
Online Redo Logs
Archived Redo Logs Log Transport Services
Online Redo Logs
Archived Redo Logs
Two sets of archived redo logs
DataGuard
Archived Redo Logs
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 20
DataGuard Logical Standby
Primary Database Logical Standby 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 doesn‟t maintain things added to standby This can be bypassed
Alter session or database Needed to import db objects into standby
Guard status restricts who can update
–
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 22
Logical Standby Guard Status
All
–
SYS can modify anything in standby database SYS can modify anything Other users can modify objects not maintained by DataGuard
Subject to normal user privs
Standby
– –
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 Refresh wipes out unique db objects Must extract them first Refresh from primary Load unique db objects
Logical Standby
– – – –
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 27
Standby Fails
Physical
– –
No problem, just a copy of primary Refresh from primary
What about unique db objects Recover standby from standby backups Extract unique db objects Refresh standby Insert unique db objects
Logical
–
–
– – –
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 doesn’t 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 doesn’t 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 Don‟t need to change for failover Don‟t need to change for fail-back On primary
Pointing to standby
Create tnsnames.ora entry
– –
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 Inactive
Active
Inactive
Active for Logical Standby
Db_unique_name=NY_DB
Db_unique_name=LA_DB
Primary Database
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)
Standby Database
Online Redo Logs
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 NY=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=)(PORT=1521)) (CONNECT_DATA= (SID=NY_DB) ) )
Archived Redo Logs
Tnsnames.ora LA=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=)(PORT=1521)) (CONNECT_DATA= (SID=LA_DB) ) )
Archived Redo Logs
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 33
DataGuard init.ora/spfile
Active Inactive Active for Logical Standby
Active
Inactive
Db_unique_name=NY
Db_unique_name=LA
Standby Database
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)
Primary Database
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)
Online Redo Logs
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 NY=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=)(PORT=1521)) (CONNECT_DATA= (SID=NY_DB) ) )
Archived Redo Logs
Tnsnames.ora LA=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=)(PORT=1521)) (CONNECT_DATA= (SID=LA_DB) ) )
Archived Redo Logs
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 34
DataGuard Reporting
Primary Database Logical Standby Database
Database objects Copied from primary Maintained by DataGuard
Online Redo Logs
Database objects added to Logical standby database -- indexes for reporting
Archived Redo Logs
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
Don‟t 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 Stop redo apply Convert database to logical standby
Change db_name
On Standby database
–
–
– – –
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 B Physical standby C
Physical standby A sends redo to
– –
Logical standby B sends redo to Don‟t you have enough to worry about?
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 41
Real World Example
What I‟m supporting now
– – –
Logical standby 2 added schemas for custom app Primary db supports Oracle Applications 11i Provide copy of primary 11i db for reporting
Oracle Discoverer
Requirements
–
– –
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
Processed Data Stored
Archived Redo Logs
DataGuard
Archived Redo Logs
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 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
If Logical standby fails
–
– – – –
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 No notification of skipped objects Examine primary for unsupported things
If unsupported, automatically skipped
–
–
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 Objects may not be well designed
Tables with poor (or no) indexes
Primary db
–
–
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 doesn‟t 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
Can‟t 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 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 4 standby databases Backups for 4 standby databases
Does this sound like a standby?
If Logical Standby is an „application‟
– – –
–
What is added to your infrastructure?
– –
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 Can‟t 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 aren‟t, why use logical standby?
Refresh or rebuild
–
Have to recover db objects unique to standby
Previously skipped tables
Do we skip them again? Do we wait for them to need to be skipped?
After refresh
–
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 52
Real World Redesign
Separate database dedicated to custom application
Primary Database Physical Standby Database Online Redo Logs
-- 2 schemas for custom app -- store data extracted from tables in physical standby database
Procs extract data
Processed Data Stored
Archived Redo Logs
DataGuard
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 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
Physical Standby database
– – –
Support an extra database
–
Don‟t have to support Logical Standby
Page 54
www.brianhitchcock.net
Brian Hitchcock October 23, 2007
Recommendation
Logical for reporting
– – –
Copy of primary Add indexes to speed reporting Add tables for aggregates Easily recreated from a SQL script Contain data that can always be regenerated from copy of primary
Objects added to standby
– –
www.brianhitchcock.net
Brian Hitchcock October 23, 2007 Page 55
Recommendation
Physical standby
– –
Is solid, dependable No issues 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?
Logical standby
–
–
– – –
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