Tip:
Highlight text to annotate it
X
[music]
>> 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.
[pause]
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.
[pause]
How can I do that?
[pause]
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.
[pause]
Conn/as sysdba
On my primary, I will create a user.
[pause]
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.
[pause]
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
[pause]
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,
[pause]
conn readmost/readmost
[pause]
It doesn't have create session. You need to give them a couple of privileges.
[pause]
So what I'll do is, "grant dba to readmost" on the primary and then connects over here.
[pause]
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.
[pause]
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.
[pause]
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,
[pause]
scott.dept, is still there. Transactional consistency is maintained. Commit my delete.
[pause]
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.
[pause]
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.
[pause]
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.
[music]