Oracle Data Guard Tutorial: Data Guard for Performance and Reduced Licensing Costs

We all know that Data Guard is essential for protecting data. And we know that it is expensive and relies on largely idle systems, sitting there waiting for a problem that may never happen.  This tutorial provides a quick review of what Data Guard is, and then goes on to show how you can extract value from Data Guard in every-day usage: how you can use it to enhance performance, and to reduce (yes, reduce!) your need for Oracle licences.

Audience:  This will be of interest to anyone already running Data Guard (or the old Standard Edition “standby database” facility) who needs to become aware of the newer capabilities, and to anyone trying to motivate the investment needed for a new Data Guard implementation.

Recommended Prerequisites: As is common with SkillBuilders webinars, the content is technical, including live demonstrations of the techniques described. Some knowledge of Data Guard is recommended.

Presenter: John Watson, Oracle Certified Master.

This free training is segmented into several separate lessons:

  1. Introduction and Data Guard Quick Review (Revision) (16:53)
  2. Demonstration: Open the Standby Read Only (16:49)
  3. Demonstration: Open the Standby Read Write (14:09)
  4. Demonstration: The Read Mostly Database: Query the Standby, DML to the Primary, Transparently (07:19) (click on video below)

Date: Nov 7, 2012

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


Demonstration: The Read Mostly Database: Query the Standby, DML to the Primary, Transparently

4. Demonstration: The Read Mostly Database: Query the Standby, DML to the Primary, Transparently




>> John:  Now, the last topic. I’ve just got time to dealing with this. The last topic I want to look at is – I’d like to call it the read mostly database. 


This is a very nice technique. We’re getting even more value out of your physical standby because remember what I’ve talked about before? When you open your database read-only you can write a very limited amount of data, basically only temporary data. 




Opening it read-write to the snapshot standby, yes, it’s read-write but then you’re not applying the redo so it’s no longer valid as a real time data warehouse. The read mostly database is a compromise between the two. So it adds even more value ideally to Active Data Guard. 


It would function without Active Data Guard license but Active Data Guard is where you’re going to get most benefits of this. This is where we’ve come to that low balancing question we had earlier. 


If you have say two applications, perhaps one application is your standard OLTP application. The other application is your query application. Maybe you’ve got business objects or discoverer. Business objects applications, discover applications, they’re read intensive. They run massive queries. But they also do an occasional bit of DML. Not much, but they do enough to mean you cannot run them against read-only database in many circumstances. 


So what we need to do is redirect the small amount of DML to the primary while running all heavy weight queries against the standby. In a way, that means your application doesn’t even know it’s happening. 




How can I do that? 




I can do it like this. On my primary, as we see on the slide, send them some database links. So, on my primary which is Madrid, I’ll begin by creating a user. I do need to start manage recovery here. So I cancelled recovery, I opened a database, let apply going. I’m about to connect to a user I haven’t even created. 




Conn/as sysdba 


On my primary, I will create a user. 




I’m trying to connect to the user again. This is what you get if you copy and paste when you try to create to save time. I’ll create my user. This is called readmost and that’s the schema we’re going to connect to through, for example, discoverer. 




Then I shall create a database link. And before any of you start screaming and shouting at me, I do know that a public database link with connection credentials is a huge security hole but it will do purely for demonstration purposes at the moment. 


Then synonyms, a very simple environment here. I shall create a synonym in the readmost schema for the emp table and I’ll also create a synonym 




for depts. That goes show the database link. 


Now I’m working on the primary, so my users should be propagated to the standby, the synonym should be propagated, the other synonym should be propagated. So if I go to let’s say I’ll work on Madrid at this point, 




conn readmost/readmost 




It doesn’t have create session. You need to give them a couple of privileges. 




So what I’ll do is, “grant dba to readmost” on the primary and then connects over here. 




And note, everything comes across in real time because I do happen to have got this thing setup. I’ve done so much work on Berlin. 




I just want to make sure my spare one is a physical standby and clearly it’s open read-only. I won’t be able to do anything so if I select – what now happens? This is my readmost user. From my read-only application, if he tries for example to delete from emp, it’s a read only database. 




But what happens now? He can in fact do this. He can insert into dept values or just do a select first, select * from dept. 


Nothing special about that. It’s a read-only database. What if I were to delete from dept where deptno=99, one row deleted even though it’s a read-only database. 


Transactional consistency is gone. Whereas of course on the other side in the actual primary,




scott.dept, is still there. Transactional consistency is maintained. Commit my delete. 




And it’s gone. It’s what I call a read mostly database. This is really, really powerful and my application is very simple. It’s based on two tables. I’ve got one table dept so I can do DML, another table emp where I run the heavy weight queries, and it’s completely transparent modes. There’s no configuration of the application at all. 


I, as DBA, in conjunction with new application people, have to identify what tables are likely to be written and what ones aren’t. And that gives us the read mostly database. 




We can redirect work transparent to the application and it would all be based on services, which brings us to the very last point which I will not have time to talk about in great detail. My apologies for that. 


In this environment I’ve been laying out, we need to be absolutely clear that you connect to the correct database. There’s no good having users connecting to the primary and they think they’re logged on to a snapshot standby. 


We can control all that and automate the connectivity – the use of services, startup triggers, TNS connect strings, its appropriate use of services, and also a mechanism that dynamically adapts role changes. Because remember fault tolerance – at any stage, my primary might fail and I need to automatically activate the standby. At that point, roles change and we don’t need to redirect sessions appropriately. We can automate that for you as well. 




And that concludes what I wanted to talk about, techniques for gaining more value your disaster recovery system, and perhaps help you motivate installation and move towards a more advanced Data Guard configuration.




Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!