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

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

8. 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.

9. 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.

10. 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.

11. 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, it’s available for query (or reporting), offloading work from primary.

12. 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.

13. Creating a Physical Standby Database • Make copy of primary database (data files) • Create standby control file on primary database

14. Creating a Physical Standby Database (cont.) • Prepare pfile for standby database

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

16. 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.

17. 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.

18. 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. Values:auto,manual

19. Creating a Physical Standby Database (cont.) • Copy files from primary to standby system • On primary system set pfile parameters

20. Primary Parameters Defined • LOG_ARCHIVE_DEST_2- Specify ‘SERVICE=XXXX’, 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 it’s redo. • LOG_ARCHIVE_DEST_STATE_2- Enables or disables writing of logs to log_archive_dest_2. Values: enable, defer, Alternate

21. 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 (true=send + receive)

22. Creating a Physical Standby Database (cont.) • Configure listeners for primary and standby databases • Set SQLNET.EXPIRE_TIME=2 in sqlnet.ora • Modify tnsnames.ora on both primary and standby • Create spfile on standby (optional)

23. 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;

24. Creating a Physical Standby Database (cont.) • Start remote archiving (from primary) • ALTER SYSTEM ARCHIVE LOG CURRENT; • Physical standby database is now up and running!

25. 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.

26. 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 can’t write to a standby, the primary will shutdown. Guarantees no data loss, but has highest impact on performance of the primary database.

27. 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.

28. 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 doesn’t effect the availability of the primary like Maximum Protection Mode does.

29. Data Protection Modes

30. 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.

31. Maximum Protection

32. Maximum Performance

33. Transmission and Reception of Redo Data • Specify the process to transmit redo, ARCH or LGWR (ARCH is the default) • LOG_ARCHIVE_DEST_2=’SERVICE=stdby LGWR’ • Choose SYNC or ASYNC network transmission mode. Must use SYNC for zero data loss. • LOG_ARCHIVE_DEST_2=’SERVICE=stdby ASYNC’

34. Data Protection Modes

35. 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.

36. Configuring Log Apply Services • Start the Physical Standby: • To Stop Log Apply Services:

37. Log Apply Services • To Verify Managed Recovery: • To Determine if there is an Archive Gap:

38. Monitoring Log Apply Services • The following views can be used to monitor Log Apply Services: • V$MANAGED_STANDBY • V$ARCHIVED_LOG • V$LOG_HISTORY • V$DATAGUARD_STATUS

39. 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.

40. 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

41. How to Perform a Switchover (cont.) • While still on old primary shutdown database and restart as standby: • On the old standby system (new primary):

42. 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!

43. 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:

44. How to Perform a Failover (cont.) • Convert the Physical Standby to Primary (modify pfile before restart): • Failover complete!

45. 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.

46. 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.

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

48. 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.

49. Management Tools • SQL - Data Guard can be managed and monitored with SQL*PLUS. Mostly Manual. • Data Guard Broker- Special interface for Data Guard. Has command line interface and Java GUI. Less manual than SQL*PLUS. • 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

50. What is ITEC doing with Data Guard? • Currently ITEC is using Data Guard in it’s 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