Tip:
Highlight text to annotate it
X
[music]
>> John: Moving on, we now look at some of the facilities.
Opening the standby database read-only. This has in fact been possible since the automation
of redo transport and redo apply when it was first introduced in release 8i.
[pause]
Redo is received on the standby database but is not applied. So the redo is written to
the RFS, slave to standby logs, archive to standby logs into archive logs but there's
no apply done. So recoverability is not compromised. There's still no danger being lots of data.
You're still receiving.
[pause]
The thing is open read-only. What does that mean? It means you can run queries against
it. You can offload all your heavyweight queries from the primary to the standby.
[pause]
When I say all of them, even queries that require vast massive temporary space, those
horrendous queries, those doing hash joins between multi gigabyte tables, they'll be
generating many gigabytes of temporary data. You can write temporary data.
[pause]
Provided that you're using proper temporary tablespaces which you're certain you should
be, you can run any query no matter how big, no matter how much temporary data it needs.
You can also write audit data and this is important because with version 11, there's
quite a lot of auditing enabled by default. By default, that auditing goes to the database,
writing database tables. And you can't write to a database table in a read-only database.
That we can configure for automatic switching and direct the audit records to the operating
system or the trail instead.
[pause]
The queries you run, why is this such a good facility? First, you're taking stress off
the primary. I'm sure that we're all DBAs here and we all know how difficult it is to
tune your database or a concurrent workload that includes both high volume DML and includes
heavyweight queries. Basically, you can't tune that way at all.
[pause]
Offloading the queries to the physical standby will reduce a lot of stress from the primary.
So your DML should run significantly better and you can even do a certain amount of tuning
on the standby to tune for optimal query performance. The most obvious tuning one would do, would
be the memory parameters. No one could for example give the primary a massive PGA aggregate
target and the much smaller SGA target or use indeed automatic memory management, perhaps
cell tuning.
[pause]
One can adjust many parameters to do with tuning the optimizer to drive the optimizer
to a certain execution plan, to exploit the environments. All that can be done on an open
read-only database.
How does one do it? It really couldn't be simpler.
[pause]
If we look at my physical standby here, this thing database role, physical standby - and
if we select the open mode from v$ database, we find this in mount mode.
How do we actually open at read-only? First off, we have to stop the recovery process
because remember redo is received but no applied. You have to cancel the recovery. So "alter
database recover managed standby database cancel."
[pause]
That will give us a read consistent version of the database.
The command is intelligent enough to know that there might be incomplete transactions.
Long-run transactions could've been partially received. We will end up with a read consistent
version.
To cancel it, all we do is alter database open read-only.
[pause]
It is that simple.
[pause]
And now on my primary, select * from scott.dept and I can now run all my heavyweight queries
over here. But of course, it is read-only if I insert into scott.dept.
[pause]
Values 99, a new department, and it can be for UK. Then I commit it.
[pause]
We already got 99. That's probably because I have an uncommitted transaction kicking
around.
[pause]
That's better.
[pause]
Commit. That is not going to appear over here of course because we are not applying the
redo at all.
[pause]
We already 99. There's no committed insert.
[pause]
We can see what's going on by the way. If we look at the query
[pause]
We can see we are still receiving redo. The redo is coming across in pretty much real
time.
[pause]
From my primary I am transmitting block 55303.
[pause]
I've already applied it. Sorry. I've already received it.
We're receiving all the redo in very nearly real time, but we're not applying it at all.
So what I'll need to do eventually is alter database close and then re-enable the manage
recovery.
[pause]
But there's no reason with a standard command. Right now I'd be launching my recovery process
and it will be applying all the redo that is being received.
[pause]
It's already caught up.
[pause]
We can set this up on many sites so that maybe you would open your database read-only first
thing in the morning and of all day redirect your queries to this thing. At night, close
it, apply the redo and it can catch up.
[pause]
But it gets better.
[pause]
Because we have the ability to open the database read-only.
Read-only with apply.
What I've just demonstrated is a wonderful technique for improving performance. You offload
all your heavyweight queries to standby without compromising recoverability and that you repeat
that without compromising recoverability. The ability to tune the standby run increase
optimally helps a lot. That's of course your queries are only as up to date as of the time
the database was opened.
If you want a real time data warehouse, then you need this. The real time data warehouse
facility open read-only with apply really does now mean that we can start saving money
because previously you would need to license two machines.
[pause]
You need to have one machine, perhaps open one machine which you would use as a physical
standby applying redo in mount mode. That would give you your fault tolerance. The second
machine, you'd have to license and uses a logical standby which you could open as a
real time data warehouse.
By using the open read-only with apply option, the one license machine can fulfill both functions.
They can run real time queries and give you a zero data loss.
[pause]
It can function full zero data loss without requiring a separate machine. It's a real
time queries and zero data loss into one box.
[pause]
One thing I should emphasize at this point by the way, in these webinars, we try not
to cover features that cost money other than of course the cost of paying us to assist
you in implementing them. But this feature is an exception.
[pause]
This is Active Data Guard. Active Data Guard is licensed on top of the Enterprise Edition.
I justify it by saying that it really is an option that saves you money because of the
ability to reduce your license footprints. You will find with Active Data Guard, you
don't need to license as many machines, because the one machine is both your data warehouse
site and your disaster recovery site.
[pause]
Because the standby database is a real time DSS system.
[pause]
The way it works
[pause]
My physical standby is now closed.
[pause]
Let's look at it in terms of the view. This is now a mounted database.
So we can't run any queries at all at this point. If for some reason I tried to, of course
database not open.
What do we do? We begin by counseling the manage recovery once again.
[pause]
We open the database. Alter database open.
[pause]
Run my queries. Fine. You can see the changes we've come across already. But now we start
recovery.
[pause]
Alter database recover managed standby database using current logfile disconnect from session.
[pause]
This would have the recovery process and I'm now legally required to have licensed of course
the Active Data Guard option.
[pause]
We can now see I'm applying logfiles at this precise moment. If we do some DML or open
a query, I'm running queries then against the database. This is indeed applying redo
at this precise moment.
If I were to delete from scott.dept where depts. No=98, of course I cannot run that
query without statement here.
[pause]
But I can run to my primary, no problem. And commit.
[pause]
In very nearly real time, the row's already gone. Your data is coming across.
So I'm getting real time information for my queries. And it gets better. If you look at
the last couple of points I have here, there's a configurable what time.
[pause]
If for some reason, the apply redo just slipped behind, which can happen at Data Guard environment.
You have a huge burst of activity on the primary. It may well be the apply service. Just get
a few seconds behind. You can configure lag times.
[pause]
You can configure lag times quite straightforwardly.
[pause]
With the command such as this.
[pause]
I can't do it for sys. But if I connect to scott, we can configure a lag time here of
10 seconds. What this means is if I run the queries, before running the query, my server
process will check, is the standby really up to date? If the standby is more than 10
seconds behind, I guess an appropriate error message, there's also another command we can
run to force a synchronization with the primary.
Normally we would setup this sort of thing for you as logon triggers, so that we can
guarantee that certain tools you might be running will always give you data that's accurate
within say three seconds when default to real time synchronization so the query will actually
hang until the data is up to date.
[pause]
>> Dave: John, hold on a moment if you would.
[pause]
I would like to do two things and pass you a few questions.
[pause]
What's the impact on database performance?
>> John: On the primary, there should be no database lost, no performance impact whatsoever.
Because all the primary is doing is generating redo. There is of course an extra process
or two to Data Guard which is the log writer network slave process. But that's just one
process. There should be no issue with that at all.
[pause]
>> Dave: Great. I'm sorry, were you finished with that one, John?
>> John: Yes.
>> Dave: Good. I just want to interrupt just for a moment. This is a little bit about John.
If you go to SkillBuilders.com and click on John's picture, you'll get to this page. I
just want to show you a little bit more about John's experience and credentials. Actually
you authored several books. You'll see thumbnails of those books here which will take you to
Amazon or something, if you want to buy those books if you don't already have them. But
you can see that John just got an incredible breath of certifications including Certified
Specialist in Implementation of Exadata.
[pause]
And a Certified Master Oracle Database 11g Administrator.
[pause]
Hey John, I took a stab. There was a question that came and I took a stab at the answer.
[pause]
What is the release of the database that you are operating on?
>> John: I am operating right now on release 11.2.0.3. Most of the facilities - open read-only
dates back to 8i. Open read-only with apply is most definitely 11g, but is any release
of 11g. So from 11.1 onwards, we can do everything we've got so far.
[pause]
>> Dave: You might have mentioned logical standby somewhere along the way and there
was just a quick question on that. Can we export data from a logical standby database?
>> John: A logical standby - now you certainly can. A logical standby - which I hadn't intended
to talk about today - but a logical standby is an absolutely normal database. It just
happens to be receiving a stream of changes through the SQL apply mechanism. So no problem
with that at all. No issue with that.
[pause]
>> Dave: Great.
[pause]
I think you might attack this a little later. But I'll put it in the queue now in your thought
train. Can the standby database be used as a load balancer? If so, what are the factors
that we need to consider before setup?
[pause]
>> John: What you need to consider there is what work can effectively be executed against
the standby? Because the standby database is as we've seen at the moment is of course
read-only.
[pause]
Let me check where I am on my slides. Yes, in about three slides time, we'll see how
you can intelligently direct some traffic that has DML to a standby. That would allow
you in effect use as low balancing mechanism. Direct some applications based on services,
balance some applications, the primary, and direct some other applications to the standby.
We will return to that later on. Thank you for raising that question. I'll make sure
I'll talk about it in two slides time.
[music]