Transcript DataGuard_Concepts_Architecture_10072007

Oracle DataGuard
Concepts and Architecture
Brian Hitchcock
OCP 10g DBA
Sun Microsystems
[email protected]
[email protected]
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
 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 can’t be used for anything else
–
Constantly recovering
 Failover
–
–
–
Open standby db with resetlogs
Can’t be standby again without rebuild
Can’t fail back to primary without rebuild
www.brianhitchcock.net
Brian Hitchcock October 23, 2007
Page 7
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)
 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
Can’t 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 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
 Maximum Protection
–
–
Primary stops if standby doesn’t commit
Requires standby redo logs
 Maximum Availability
–
–
Max protection but primary doesn’t 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
Can’t change standby db objects
Log Apply
Services
Physical
Standby
Database
Primary
Database
Standby mounted, recovering
Or
Standby read-only, no apply
Can switch back and forth
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
 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
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)
SQL Apply
Services
Logical
Standby
Database
Primary
Database
Standby open while redo applied
Online Redo
Logs
Online Redo
Logs
Archived
Redo Logs
Log Transport
Services
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 doesn’t 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 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
 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
 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
 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
 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
 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 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
 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
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
 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
–
Don’t 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