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) (click on video below)
  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)

Date: Nov 7, 2012

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

Transcript

Demonstration: Open the Standby Read Only

2. Demonstration: Open the Standby Read Only

 

[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]

 

Copyright SkillBuilders.com 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!

 

×