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

3. Demonstration: Open the Standby Read Write

 

[music]

 

>> John:  The next option

 

[pause]

 

Open the standby read-write.

 

[pause]

 

And that sounds impossible, doesn’t it? But it is what we call a snapshot standby. The snapshot standby capability, it’s even better as a cost saver, even better. And as a database administrator as well, I love it. It’s wonderful for DBAs.

 

The idea is you take your physical standby database and you open it read-write. While it’s opened read-write, to end users it appears to be an absolutely normal open database. What do you use it for? You could run queries against it, but perhaps more commonly you’d use it as your test system, your development system, your UAT (user acceptance testing) system, anything you want.

 

[pause]

 

For example, you could open standby as a snapshot standby and deploy a whole new application release to it. Do all the testing you want. Including using things like your AWR reports, database replay, do anything you want in it. But the absolute certainty, the database is a real recent clone reproduction. As up to date, as the point of which you opened it read-write.

 

While you’re using it with development and so on, redo is still being received. Redo is still being written from the primary by LNS to RFS and written standby logs. Redo’s being archived, so recoverability is not compromised.

 

[pause]

 

And you can open it in this fashion for a week maybe. You probably wouldn’t want to do it for a month, but you can open it for a long period. So you open it read-write, do whatever you want to it and then when you’ve done your testing, tested your application release, done whatever it may be, done your development work, then we re-synchronize it to the primary.

 

Now the enabling technology by the way is one has to setup flashback database and the flashback is the point of which was opened and then all the backups redo, all the redo that’s been received and not applied gets applied. So there’s absolutely no compromise to recoverability.

[pause]

 

How do you do it?

 

[pause]

 

Here’s my database. This thing right now, this is open. Isn’t it? So I’ll need to close it at this point. So I get to say, “Shut down immediate connect/as sysdba.”

 

[pause]

 

Because it was opened read-only. I will have to shut down.

 

[pause]

 

But note throughout this – I’m working on the standby at this point. There’s no downtime on the primary at all at this stage.

 

[pause]

 

And then I’ll start if up in mount mode.

 

[pause]

 

Once it comes up in mount mode,

 

[pause]

 

I want to make sure it is in fact synchronized with the primary.

 

[pause]

 

So my primary database…

 

[pause]

 

I’ll start the manage recovery process.

 

[pause]

 

I’m just looking in these queries of the [04:16 inaudible] sequence number of the block. I could equally well look at the system change number. That would be another way to proving they’re we’re synchronized.

 

We’re now applying 29 block 5611. We’re very nearly up to date and right now I’m pretty sure will occur.

 

[pause]

 

Let me just force save log switch to make sure we really are synchronized, and we are. We’re now receiving the log number 30.

 

How do I open it read-write?

 

[pause]

 

What we do is we begin by cancelling the manage recovery.

 

[pause]

 

Let’s cancel the recovery. Then just one command and that command is “alter database convert to snapshot standby.”

 

[pause]

 

This process we would normally automate by the way. And if we now look at what’s happening within this database,

 

[pause]

 

and look at the views, you’ll see that. The mount mode then we alter database open.

 

[pause]

 

Previously while I open the database, of course it was opening read-write. Sorry, previously it was opening read-only I should say.

 

[pause]

 

This time it’s going to open read-write.

 

[pause]

 

It’s opened read-write. Oracle is aware of what’s going on, however. If I look at the database role, Oracle knows that it’s theoretically still a standby database. So the redo is still being received. It’s simply isn’t being replied and this is an absolutely normal read-write database. I can select * from scott.dept. Of course, I can. I can delete from scott.emp. I can commit.

 

[pause]

 

I can do anything I want on those things. And absolutely as far users are concerned, this is an absolutely read-write database. It will be generating AWR snapshots and generate ADDM reports, I can tune mySQL, I can run work low test, I can deploy whole new applications, create new users, drop schemas. I can do anything I at all as I like as I want to this database. And having done all my work

 

[pause]

 

I can even drop the table, drop table scott.emp. And that table is gone as far as this database is concerned.

 

A week later maybe, I’ve completed my test run. So what do I do next? Terminate the instance.

 

[pause]

 

Just abort it.

 

[pause]

 

There’s no particular need to be nice by using a shutdown immediate because I’m going to throw away all the work I’ve done, then mount it. Startup mount.

 

[pause]

 

Note that throughout this whole process of course, the primary is running exactly as normal. There’s no downtime at all on the primary.

 

Yes, Dave?

 

[pause]

 

>> Dave:  Where are the redo logs created or written when the database is a snapshot standby?

 

>> John:  In this particular case, I’ve configured my databases in the simplest way possible. And if you look at this, sho parameter – I’m going to Madrid, my spare standby at this point. Sho parameter log_archive_dest.

 

[pause]

 

I set this up in the easiest way possible and that I setup – in fact, I haven’t setup. I’m relying completely on defaults. Yes, if we look at this, there’s too much to show you.

 

Archive log list, connect/as sysdba. By default,

 

[pause]

 

they go to the flash recovery area or fast recovery area I should call it. What will be happening in the fast recovery area is there will have been all the time that it was open as a snapshot standby to that fast recovery area there will have been two strings of redo written. There will have been a redo generated from the primary by standby logs and the redo generated over the database Berlin because it was an open database.

 

To lead on from that question, when you open your snapshot standby, you remember it was open as a normal read-write database. If it was generating its own redo, so it was in fact archiving two strings of redo. The redo received from the London primary and the redo generated by the Berlin snapshot standby. And I didn’t configure it other than just default, it goes to the fast recovery area.

 

[pause]

 

On a production system, we’d be a bit cleverer than that and we would direct the redo from the primary and the redo from the snapshots in a more intelligent fashion than that.

 

[pause]

 

But it was a good question, I should emphasize that. There were two strings of redo being generated while the thing was open read-write.

 

[pause]

 

>> Dave:  Great. One more question, John. If we open a physical standby in write mode then can we bring it back to mount mode and receive changes from the primary and apply them?

 

>> John:  That’s what I’m at right now. I’ll shut it down, I’ll start in mount mode, one more command.

 

[pause]

 

The next command, convert to physical standby. This is the conversion back. Quite a lot is going on at this point. At this point, we are using the flashback database facility to flash back the database to the exact system change number have which I opened it.

 

So in effect, backing out all the changes that I did. But the back out of course is done through the flashback database mechanism which is extremely fast and efficient. So we’re reversing the database back to when it was a true physical standby. The proof of that…

 

[pause]

 

After we started it’s got a dramatic change because this still convert the control file, as well as backing up the database.

 

[pause]

 

So I’ll terminate the instance and mount it again.

 

[pause]

 

We would of course automate all this in any sort of production configuration.

 

[pause]

 

I must emphasize that throughout this whole process, my primary stays running and there is no break or minimal break in the transmission and reception of redo.

 

[pause]

 

I guess there will be a few seconds during this process when redo is not in fact being received. Having remounted, it’s now back to normal physical standby. So what do I do next?

 

I start the recovery. And at this point, you’ll be applying all the redo that was received and archived from the primary during the period it was opened as a snapshot standby.

 

[pause]

 

If it had been opened as a snapshot standby for a week, it’s going to take a while to catch up. But there is no compromise to recoverability. All the data was there. We can see – note I dropped the table at that point. I haven’t opened it read-only.

 

[pause]

 

But if I recover my standby database, cancel.

 

[pause]

 

If I now open the database read-only – alter database open – I no longer have to specify read-only. I did back with 8i. But currently Oracle is of course intelligent not to know that. And we see everything is there. So I have now, in effect, thrown away the week’s worth of work and applied any changes that’s being done on the primary in the meantime.

 

This is a truly wonderful capability. What does it do? It saves you any licensed footprint clearly because I’ve now got one system is being used for all my test or development work and at the same time is giving me 100% recoverability in the case of disaster.

 

In the DBA’s point of view, it’s really productive. It saves me as a DBA the hassle of continually having to clone testing development systems from production. It’s a really powerful capability.

 

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

 

×