Oracle Data Guard Broker Session-3

1. Oracle Data Guard Broker Session-3 Hariprasath.R

2. Training Objective • Introduction to Oracle Data Guard • Oracle Data Guard Broker: Overview • Creating a Data Guard Broker Configuration • Configuring Data Protection Modes • Performing Role Transitions • Using Flashback Database in a Data Guard Configuration • Enabling Fast-Start Failover • Managing Client Connectivity • Monitoring a Data Guard Configuration • Optimizing a Data Guard Configuration

3. Outcome expected • Purely understanding real time environment by having training in realistic way of dealing issues. • Use Data Guard standby databases to support production functions such as reporting, querying, testing, and performing backups • Create and manage physical and logical standby databases • Use Enterprise Manager Grid Control and the Data Guard command-line interface (DGMGRL) to maintain a Data Guard configuration • Use Data Guard to achieve a highly available Oracle database

4. OVERVIEW • Oracle Data Guard to help protect their Oracle database against planned and unplanned downtimes. They also learn how Data Guard standby databases can be used to support production functions such as reporting, querying, and testing, while in a standby role. • The course includes Data Guard architecture, the configuration of physical and logical standby databases, and role transitions. Oracle Data Guard 11g features, including Oracle Active Data Guard and snapshot standby databases are outlined. In addition, management of a Data Guard configuration and troubleshooting are discussed.

5. Data Guard Standby Database

6. Data Guard Role Transitions • There are two types of role transition • Switchover • Planned failover to standby database • Original primary becomes new standby • Original standby becomes new primary • No data loss • Can switchback at any time • Failover • Unplanned failover to standby database • Original standby becomes new primary • Original primary may need to be rebuilt • Possible data loss

7. Chennai-dg01 Chennai-dg02 Chennai-dg01 Chennai-dg02 PhysicalStandby Primary Primary PhysicalStandby Instance Redo Instance Instance Instance Redo Database Database Database Database Primary Database Standby Database Standby Database PrimaryDatabase Data Guard Switchover After Switchover Before Switchover

8. Site1 Site2 Site2 Site1 Site2 PhysicalStandby PhysicalStandby Primary Primary Primary Instance Instance Instance Instance Redo Redo Instance Database Database Database Database Database Primary Database Standby Database Standby Databas PrimaryDatabase Unavailable Data Guard Failover Before Failover After Failover

9. Data Guard Broker • Introduced in Oracle 9.2 • Stable in Oracle 10.2 and above • Managed using DGMGRL utility • Contains Data Guard configuration • Additional layer of complexity • Used by Enterprise Manager to manage standby • Mandatory for some new functionality e.g. • Fast Start Failover

10. Data Guard Broker • Any Data Guard configuration consists of one primary database and up to nine standby databases. • The Data Guard broker logically groups these primary and standby databases into a broker configuration so as to manage and monitor them together as an integrated unit. • Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration. • Accessed either locally or remotely using either of the two clients: CLI interface(DGMGRL) or the Data Guard page from GUI( OEM Grid Control )

11. Data Guard Broker • DGMGRL does not have the ability to create standby (GUI can do it). CLI is used mostly for configuration and management. • Easy switchover/failover with one command thereby minimizing overall downtime associated with the planned/unplanned outage • Integrated with CRS so that database role changes occur smoothly and seamlessly. • Instead of managing primary and standby databases with various SQL*Plus statements, broker provides a single unified configuration

12. Physical Standby Database Related Processes On the Primary Database: LGWR: The log writer process flushes log buffers from the SGA to Online Redo Log files. LNS : The LogWriter Network Service (LNS) reads the redo being flushed from the redo buffers by the LGWR and sends the redo over network to the standby database. Themain purpose of the LNS process is to free up the LGWR process from performing the redo transport role. ARCH  : The archiver processes archives the ORL files to archive log files. Up to 30 ARCH processes can exist, and these ARCH processes are also used to fulfill gap resolution requests. Note that one ARCH process has a special role in that it is dedicated to local redo log archiving only and never communicates with a standby database. :

13. Physical Standby Database Related Processes RFS : The main objective of the Remote File Server process is to perform a network receive of redo transmitted from the primary site and then writes the network buffer (redo data) to the standby redo log (SRL) files. ARCH : The archive processes on the standby site perform the same functions performed on the primary site, except that on the standby site, an ARCH process generates archived log files from the SRLs. MRP : The managed recovery process coordinates media recovery management. Remember that a physical standby is in perpetual recovery mode.

14. Data Guard Broker Related Processes • BACKGROUND PROCESSES ON EACH NODE • All of the processes run on every node of Data Guard setup. • Data Guard Monitor (DMON) • Coordinates all Broker actions as well as maintains the Broker configuration files • Enabled or disabled with the DG_BROKER_START parameter. • Broker Resource Manager (RSM) • RSM process comes in play whenever broker need to run any SQL command in the database. • SQL may be required to be run during Data Guard setup or because of result of a change to the configuration made through DGMGRL

15. Data Guard Broker Related Processes • Data Guard Net Server (NSVn) • NSV processes are responsible for making connection with the remote database and to send across any work items to the remote database. • So, DMON or RSM process may ask NSV process to connect to other node for any work request. DMON may need to send some communication to the other node and RSM may have to get some data through SQL from other node. • DRCn • NSV processes contact DRC process running on other node to establish the connection, so DRC process acts like a receiver on other node. • Each NSV process will have a partner DRC process on the target database, which will perform the actual work on behalf of the source database NSV process and return the results or status.

16. Data Guard Broker Related Processes • Internode servers (INSVs) • If Data Guard setup involves Real Application Clusters (RAC), INSV process come into play. It maintain a connection between the RAC database nodes in the cluster to ensure that the Broker on each node knows the state of the cluster. • What Happens At Database Startup • Step 1) Primary database startup. • Step 2) DMON process at primary ask local NSV process to connect to each standby database. • Step 3) NSV process in turn will contact DRC process on standby and get confirmation. • Step 4) DMON process at primary will then ask local RSM to send the setup commands to the standby database. • Step 5) RSM will send the setup commands to NSV. • Step 6) NSV process in turn will contact DRC process on standby to run the setup commands.

17. Broker Configuration Files Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration.Additionally two copies of the configuration files are always stored on each database for redundancy. Below parameters control where the configuration files will be stored. DG_BROKER_CONFIG_FILE1DG_BROKER_CONFIG_FILE2 For RAC instances, you need to keep the files in shared location where all database instances can access it. Broker keeps in sync all of these configuration files but DMON process running on the primary database is the owner of the master copy of the files.This means that if you started the standby database while Data guard process are down at primary node, then no data guard related activities will be done until the standby database can connect to the primary database

18. Observer Primary Standby Node 1 Node 2 Site3 Database Database Site2 Site1 Data Guard Fast Start Failover

19. Data Guard Fast Start Failover • Detects failure of primary database • Automatically fails over to nominated standby database • Requirements include • Flashback logging must be configured • DGMGRL must be used • Observer process running in third independent site • Highly available in Oracle 11.1 and above • MAXIMUM AVAILABILITY protection mode • Standby database archive log destination must be configured as LGWR SYNC • MAXIMUM PERFORMANCEprotection mode • Oracle 11.1 and above • Primary database can potentially be reinstated automatically • Using flashback logs

20. Data Guard Advantages & Disadvantages • Advantages • No interconnect network required between sites • No storage network required between sites • RAC licences not required if each site is a single-instance • Disadvantages • Active / Passive • Requires Enterprise Edition licence • Remaining infrastructure must also failover • Network • Application tier • Clients

21. Implementation notes

22. Switchoverto Standby

23. Switchover to Standby Step:6 Start the apply process alter database recover managed standby database cancel; ALTER DATABASE OPEN READ ONLY; alter database recover managed standby database using current logfile disconnect; Step:7 Verify Current Status DGMGRL> show configuration

24. Failover to Standby Step:1 The databases must be in flashback mode for the reinstate to work. Step:2 select message from v$dataguard_status; select switchover_status from v$database; Step:3 show configuration; Step:4 failover to stand;

25. Failover to Standby At this point, you should have your former physical standby database orcl2 running fully as your primary database server. After a failover, the physical standby database will not be present in your current setup. In the case that the failed database could be brought back, issue the following command in Data Guard Command-Line Interface to reinstate the failed database as a physical standby database. Step:5 DGMGRL> reinstate database prime; Step:6 show configuration;

26. Physical Standby Data Guard useful SQL scripts Basic information of database (primary or standby) Select Database_role, Db_unique_name Instance, Open_mode, Protection_mode, Protection_level, Switchover_status From V$database; Check for messages/errors Select Message From V$dataguard_status; Current status of physical standby database background processes. Select Process, Status, Thread#, Sequence#, Block#, Blocks From V$managed_standby; Check the log status Select 'Last Log Applied : ' Logs, To_char(next_time,'dd-mon-yy:hh24:mi:ss') Time From V$archived_log Where Sequence# = (Select Max(sequence#) From V$archived_log Where Applied='yes') Union Select 'Last Log Received : ' Logs, To_char(next_time,'dd-mon-yy:hh24:mi:ss') Time From V$archived_log Where Sequence# = (Select Max(sequence#) From V$archived_log); To find last applied log Select To_char(max(first_time),'hh24:mi:ss Dd/Mm/Yyyy') FROM V$ARCHIVED_LOG Where Applied='yes';

27. Monitoring the Data Guard Broker If we receive any error or warnings we cab obtain more information about the same ny running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning. DGMGRL> show database prime statusreport STATUS REPORT DGMGRL> show database stand logxptstatus LOG TRANSPORT STATUS DGMGRL> show database prime InconsistentProperties INCONSISTENT PROPERTIES

28. Q&A Please fill your FEEDBACK without fail. The URL is http://54.243.211.119:7777/pls/PROD/f?p=102