Oracle Data Guard

1
Oracle Data Guard
2
Topics to be covered

• What is Oracle Data Guard?
• Oracle 9i Data Guard Features
• New features in 10g
• New features in 11g
• Management Tools
• What are we doing with Data Guard?

3
What is Oracle Data Guard?

• Oracle Data Guard is the management, monitoring,
  and automation software infrastructure that
  creates, maintains, and monitors one or more
  standby databases to protect data from failures,
  disasters, errors, and corruption

4
Oracle 9i Data Guard

• Types of Standby Databases
• Creating Standby Databases
• Log Transport Services
• Log Apply Services
• Switchover/Failover

5
Types of Standby Databases

• Physical Standby Database
• Logical Standby Database
• Snapshot Standby Database (11g)

6
Types of Standby Databases
7
Physical Standby Database

• A physical standby database is physically
  identical to the primary database, with on-disk
  database structures that are identical to the
  primary database on a block-for-block basis. The
  database schema, including indexes, must be the
  same.

8
Physical Standby Database

• Managed Recovery- Physical standby is maintained
  by applying archived redo logs on the standby
  using Oracle recovery operations. Recovery
  applies changes on a block for block basis.
• Open Read-Only- Physical standby databases can
  only be queried in Read-Only mode.

9
Physical Standby benefits

• Disaster Recovery/High Availability-
  Switchover/failover allow for role reversal of
  primary and standby, minimizing downtime of
  primary database.
• Data Protection- Data Guard can ensure no data
  loss with physical standby.
• Reduction in primary database workload- Can take
  backups from physical standby.

10
Physical Standby benefits (cont.)

• Performance- Physical standby is more efficient
  than Logical, in that it uses redo apply
  technology rather than SQL apply. It bypasses
  SQL level mechanisms, and applies redo directly
  at block level.

11
Logical Standby Database

• Initially created as a copy of primary database.
  Can later be changed.
• Automatically applies archived redo log info by
  transforming data in redo logs into SQL
  statements and executing them on the logical
  standby (must remain open).
• Target tables on standby (ones being updated from
  primary) are read only.

12
Logical Standby Benefits

• Efficient use of standby hardware- Logical
  standby can contain other schemas than those
  being updated by Data Guard. those other schemas
  are read/write capable, while those being
  maintained by Data Guard are read only.
• Reduction of Primary Database Workload- Since
  standby is open, its available for query (or
  reporting), offloading work from primary.

13
Snapshot Standby Database (11g)

• Physical standby database that can be opened for
  read/write use. Redo logs are still received
  from primary, just not applied until snapshot
  standby is converted back into a physical
  standby.
• Can have similar functionality with 10g Data
  Guard, however must be done manually with
  flashback database.

14
Creating a Physical Standby Database

• Make copy of primary database (data files)
• Create standby control file on primary database

15
Creating a Physical Standby Database (cont.)

• Prepare pfile for standby database

16
Standby Parameters Defined

• REMOTE_ARCHIVE_ENABLE- Permission to write remote
  archive logs.
• Values True,False,Send
• DB_FILE_NAME_CONVERT- Converts the filename of a
  datafile on the primary to a filename onthe
  standby database. (also paths)

17
Standby Parameters Defined

• LOG_FILE_NAME_CONVERT- Converts the filename of a
  log on the primary database to the filename of a
  log on the standby database. (Must use if paths
  are different between primary and standby)
• FAL_SERVER- Assigns the Oracle Net service name
  that the standby will use to connect to the fetch
  archive log server.

18
Standby Parameters Defined

• FAL_CLIENT- Assigns fetch archive log client name
  to be used by the FAL server. This is the Oracle
  Net service name that the FAL server should use
  to refer to the standby database.
• STANDBY_ARCHIVE_DEST- Location on standby system
  where archived redo logs received from the
  primary are stored.

19
Standby Parameters Defined

• STANDBY_FILE_MANAGEMENT- Automates the creation
  and deletion of datafile filenames on the standby
  site using the same filenames as the primary
  site. Valuesauto,manual

20
Creating a Physical Standby Database (cont.)

• Copy files from primary to standby system
• On primary system set pfile parameters

21
Primary Parameters Defined

• LOG_ARCHIVE_DEST_2- Specify SERVICEXXXX, XXXX
  being the service name of the standby database
  listed in the tnsnames.ora file on the primary
  system. This is the location for the primary to
  write its redo.
• LOG_ARCHIVE_DEST_STATE_2- Enables or disables
  writing of logs to log_archive_dest_2. Values
  enable, defer, Alternate

22
Primary Parameters Defined

• REMOTE_ARCHIVE_ENABLE- Enables or disables the
  sending of redo logs to remote destinations and
  the receipt of remote redo logs. Values
  true,false,send,receive (truesend receive)

23
Creating a Physical Standby Database (cont.)

• Configure listeners for primary and standby
  databases
• Set SQLNET.EXPIRE_TIME2 in sqlnet.ora
• Modify tnsnames.ora on both primary and standby
• Create spfile on standby (optional)

24
Creating a Physical Standby Database (cont.)

• Start physical standby database
• STARTUP NOMOUNT
• ALTER DATABASE MOUNT STANDBY DATABASE
• Initiate log apply services
• ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  DISCONNECT FROM SESSION

25
Creating a Physical Standby Database (cont.)

• Start remote archiving (from primary)
• ALTER SYSTEM ARCHIVE LOG CURRENT
• Physical standby database is now up and running!

26
Log Transport Services

• What are Log Transport services?
• Log Transport Services control the automated
  transfer of redo data within a Data Guard
  configuration. They also control the level of
  data protection for your database.

27
Data Protection Modes

• Maximum Protection- Highest level of protection.
  A primary database transaction will not commit
  until all redo data needed to recover the
  transaction have been written to at least one
  standby database, if it cant write to a standby,
  the primary will shutdown. Guarantees no data
  loss, but has highest impact on performance of
  the primary database.

28
Data Protection Modes (cont.)

• Maximum Performance- This is the default
  protection mode. A primary database transaction
  will not wait to commit until redo data needed to
  recover the transaction is written to a standby
  database. This mode provides the highest level of
  protection available without affecting
  performance or availability of the primary
  database. Does not guarantee no data loss.

29
Data Protection Modes (cont.)

• Maximum Availability- Offers 2nd highest level
  of protection. Same as Maximum Protection, except
  if no standby database is available the primary
  does not shutdown, it temporarily goes into
  Maximum Performance mode until a standby is
  available. This mode guarantees no data loss
  except for when in Maximum Performance mode. It
  doesnt effect the availability of the primary
  like Maximum Protection Mode does.

30
Data Protection Modes
Maximum Protection Maximum Availability Maximum Performance
No Data Loss No Data Loss (except when in Max Perform) Does Not Effect Availability Does Not Effect Performance Does Not Effect Availability
Effects Performance Effects Availability Effects Performance Possible Data Loss
31
Transporting Redo Data

• Maximum Protection and Maximum Availability modes
  both require standby redo log files on the
  standby system. Both use RFS (Remote File Server)
  process to write to standby redo logs on the
  standby database system. Maximum Performance mode
  does not use standby redo logs when using arch
  process to write to standby.

32
Maximum Protection
33
Maximum Performance
34
Transmission and Reception of Redo Data

• Specify the process to transmit redo, ARCH or
  LGWR (ARCH is the default)
• LOG_ARCHIVE_DEST_2SERVICEstdby LGWR
• Choose SYNC or ASYNC network transmission mode.
  Must use SYNC for zero data loss.
• LOG_ARCHIVE_DEST_2SERVICEstdby ASYNC

35
Data Protection Modes
Max Protection Max Availability Max Performance
Redo Archival Process LGWR LGWR LGWR OR ARCH
Network Transmission Mode SYNC SYNC ASYNC
Disk Write Option AFFIRM AFFIRM NOAFFIRM
Standby Redo Logs Required? Yes Required for Physical Only Only if using LGWR
Database Types Physical Physical/Logical Physical/Logical
36
Log Apply Services

• Processes involved in Log apply services (for
  physical standby)
• Remote File Server (RFS)- Receives redo data from
  primary.
• Archiver (ARCn)- Archives standby redo logs that
  are to be applied to standby.
• Managed Recovery Process (MRP)- Applies archived
  redo logs to standby.

37
Configuring Log Apply Services

• Start the Physical Standby
• To Stop Log Apply Services

38
Log Apply Services

• To Verify Managed Recovery
• To Determine if there is an Archive Gap
  

39
Monitoring Log Apply Services

• The following views can be used to monitor Log
  Apply Services
• VMANAGED_STANDBY
• VARCHIVED_LOG
• VLOG_HISTORY
• VDATAGUARD_STATUS

40
Switchover/Failover

• Switchover- Role reversal between primary and
  standby databases. Old primary is now a standby.
  Used for hardware upgrades, OS upgrades, etc.
• Failover- Primary is down with little hope of
  quick recovery (ie. hardware failure on primary
  machine). Once failover has been initiated, the
  old primary database is no longer useful.

41
How to Perform a Switchover

• On primary database
• Initiate switchover on primary database
• After the above statement completes the old
  primary is now a standby database

42
How to Perform a Switchover (cont.)

• While still on old primary shutdown database and
  restart as standby
• On the old standby system (new primary)

43
How to Perform a Switchover (cont.)

• On new primary complete switchover then restart
  database
• On new standby start managed recovery
• On new primary begin redo transport
• Switchover Complete!

44
How to Perform a Failover

• Manually register any redo logs with the standby
  that are available and that have not been applied
• If using standby redo logs, initiate failover
  with
• If not using standby redo logs

45
How to Perform a Failover (cont.)

• Convert the Physical Standby to Primary (modify
  pfile before restart)
• Failover complete!

46
10g New Features

• Fast Start Failover- Provides the ability to
  automatically fail over to a designated standby
  database when the primary goes down.
  Automatically reconfigures old primary as a
  standby when it re-enters the configuration.

47
10g New Features (cont.)

• Asynchronous Redo Transmission- (LGWR ASYNC) has
  been improved to reduce the impact on the primary
  database (LSNn process added to do redo log
  transmission).
• Flashback Database across Data Guard Switchovers-
  Now possible to flash back primary and standby to
  an SCN or point in time prior to switchover
  operation.

48
11g New Features

• Snapshot Standby- Physical standby that can be
  opened read-write, then revert back to managed
  recovery.
• Active Data Guard- Physical standby that can be
  opened read-only while redo is still applied to
  the standby. (SUNY is not currently licensed for
  this feature)

49
11g New Features (cont.)

• New advanced compression for redo transport.
• Transient Logical Standby allows a physical
  standby to be changed to a logical standby and
  then back to a physical. Used for rolling
  database upgrades.

50
Management Tools

• SQL - Data Guard can be managed and monitored
  with SQLPLUS. Mostly Manual.
• Data Guard Broker- Special interface for Data
  Guard. Has command line interface and Java GUI.
  Less manual than SQLPLUS.
• Grid Control- Grid control has a web based GUI
  for Data Guard Broker. Enables you to use GC to
  schedule and monitor Data Guard operations

51
What is ITEC doing with Data Guard?

• Currently ITEC is using Data Guard in its Data
  Vaulting service. ITEC currently hosts physical
  standby databases for the following campuses
• Hudson Valley Community College
• Niagara County Community College
• SUNY Brockport
• SUNY Fredonia
• SUNY Oneonta
• SUNY Oswego

52
What is ITEC doing with Data Guard?

• Currently we are using all Physical standby
  databases. When we started the Data Vaulting
  service, Banner had data types that were
  incompatible with logical standby databases.
  This may be re-evaluated in the future (newer
  releases of Oracle are compatible with more data
  types).

53
What is ITEC doing with Data Guard?

• ITEC currently manages all of its physical
  standby databases using the SQLPlus interface.
  Our future intent is to use Grid Control to
  manage and monitor all standby databases. ITEC
  is currently in the process of rolling out Grid
  Control to all systems ITEC manages.

54
What is ITEC doing with Data Guard?

• In the Future, when the Dual Data Center project
  is complete, ITEC will replicate the production
  databases hosted at ITEC to the Albany (Sys
  Admin) data center using either storage level
  replication or Data Guard.

55
Questions???Erik Snydererik.snyder_at_itec.suny.edu