Oracle dataguard concepts and architecture l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 57

Oracle DataGuard Concepts and Architecture PowerPoint PPT Presentation


  • 666 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Oracle DataGuard Concepts and Architecture

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle dataguard concepts and architecture l.jpg

Oracle DataGuardConcepts and Architecture

Brian HitchcockOCP 10g DBA

Sun Microsystems

[email protected]

[email protected]

www.brianhitchcock.net

Brian Hitchcock October 23, 2007

Page 1


Oracle dataguard l.jpg

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


Oracle dataguard3 l.jpg

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


Themes l.jpg

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


What is a standby database l.jpg

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


What is a standby database6 l.jpg

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


Before dataguard l.jpg

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


Standby l.jpg

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

Scripts, Cron Jobs

Archived

Redo Logs

Archived

Redo Logs


Why dataguard l.jpg

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


Dataguard classic l.jpg

DataGuard Classic*

*Before choice of physical or logical standby

Standby mounted, recovering

or

Standby read-only, no apply

Primary

Database

Standby

Database

Can switch back and forth

-Primary becomes standby

-Standby becomes primary

Online Redo

Logs

DataGuard

Archived

Redo Logs

Archived

Redo Logs


Applying redo logs l.jpg

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,…)


Standby redo logs l.jpg

Standby Redo Logs

Primary redo written continuously to standby

No waiting for primary log switch

Primary

Database

Standby

Database

Online Redo

Logs

Archived

Redo Logs

DataGuard

Archived

Redo Logs

Standby

Redo Logs


Typical configurations l.jpg

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


Typical configurations14 l.jpg

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


Protection modes l.jpg

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


Dataguard physical standby l.jpg

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

DataGuard

Log Transport

Services

Archived

Redo Logs


Physical standby a standby l.jpg

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


Physical standby l.jpg

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


Physical vs logical standby l.jpg

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


Dataguard logical standby l.jpg

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

Archived

Redo Logs

Two sets of archived

redo logs

DataGuard

Log Transport

Services

Archived

Redo Logs


Dataguard logical standby21 l.jpg

DataGuard Logical Standby

Logical

Standby

Database

Primary

Database

SQL applied to Logical Standby

Just like any other database user

Online Redo

Logs

DataGuard

LogMiner

Extract SQL

Archived

Redo Logs

Archived

Redo Logs


Logical standby l.jpg

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


Logical standby guard status l.jpg

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


Skipping l.jpg

Skipping

  • Schemas, tables, transactions

    • May be skipped automatically

    • Can be skipped manually

  • Why anything skipped?

    • Performance

    • Unsupported db objects

    • More later


Logical standby a standby l.jpg

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?


Logical standby a standby26 l.jpg

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


Refresh standby l.jpg

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


Standby fails l.jpg

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


Recovering logical standby l.jpg

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?


How it works l.jpg

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


How it works31 l.jpg

How it works

Log_archive_dest_n

Where n is 1 to 10

Specific value doesn’t matter

  • Standby

    • 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)


Symmetrical init ora spfile l.jpg

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


Dataguard init ora spfile l.jpg

DataGuard init.ora/spfile

Active

Active

Inactive

Active for Logical Standby

Inactive

Db_unique_name=NY_DB

Db_unique_name=LA_DB

Primary

Database

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)

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)

Archived

Redo Logs

Tnsnames.ora

LA=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)

(HOST=<hostnameLA>)(PORT=1521)) (CONNECT_DATA= (SID=LA_DB) ) )

Archived

Redo Logs

Tnsnames.ora

NY=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)

(HOST=<hostnameNY>)(PORT=1521)) (CONNECT_DATA= (SID=NY_DB) ) )


Dataguard init ora spfile34 l.jpg

DataGuard init.ora/spfile

Active

Active

Active for Logical Standby

Inactive

Inactive

Db_unique_name=NY

Db_unique_name=LA

Standby

Database

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)

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)

Archived

Redo Logs

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) ) )

Archived

Redo Logs


Dataguard reporting l.jpg

DataGuard Reporting

Logical

Standby

Database

Primary

Database

Database objects

Copied from primary

Maintained by DataGuard

Database objects added to

Logical standby database

-- indexes for reporting

Online Redo

Logs

DataGuard

LogMiner

Extract SQL

Archived

Redo Logs

Archived

Redo Logs


Rebuild reporting standby l.jpg

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


Create dataguard standby l.jpg

Create DataGuard Standby

  • Physical Standby

  • Logical Standby

    • Create physical standby

    • Convert to logical standby


Create physical standby l.jpg

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


Create physical standby39 l.jpg

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


Convert to logical standby l.jpg

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


Cascaded standbys l.jpg

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?


Real world example l.jpg

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


Dataguard logical real world l.jpg

DataGuard Logical Real World

Logical

Standby

Database

Primary

Database

Database objects

Copied from primary

Maintained by DataGuard

Database objects added to

Logical standby database

-- 2 schemas for custom app

-- store data extracted from standby copies of primary tables

Processed Data

Stored

Procs extract data

Online Redo

Logs

DataGuard

LogMiner

Extract SQL

Archived

Redo Logs

Archived

Redo Logs


Real world refresh recovery l.jpg

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


Logical standby issues l.jpg

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


Logical standby issues46 l.jpg

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


Logical standby issues47 l.jpg

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


Logical standby issues48 l.jpg

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


Logical standby issues49 l.jpg

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?


Logical standby issues50 l.jpg

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?


Logical standby issues51 l.jpg

Logical Standby Issues

  • Logical apply process examines standby

    • When applying update from primary to standby

    • Compares

      • Previous values on primary

      • Current values on 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

Does this sound like a standby?


Logical standby issues52 l.jpg

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?


Real world redesign l.jpg

Real World Redesign

Separate database dedicated to custom application

Primary

Database

-- 2 schemas for custom app

-- store data extracted from tables in physical standby database

Physical

Standby

Database

Online Redo

Logs

Processed Data

Stored

Procs extract data

DataGuard

Archived

Redo Logs

Archived

Redo Logs


Real world redesign54 l.jpg

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


Recommendation l.jpg

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


Recommendation56 l.jpg

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?


Dataguard support issues l.jpg

DataGuard Support Issues

  • Covered in 2nd presentation

  • “Oracle DataGuard Logical Standby Support Issues”


  • Login