1z0-062 Oracle Database 12c: Installation and Administration – Module 7: Internal Structures Part 3

9. Background Processes Part1B

Sometimes you can even decide if multiple instances of these background processes exist. Here on the slide I’ve described, one called DVW. Remember, they almost all have four letter abbreviations. I know this one doesn’t have four letters. It has four letters, three letters, and a number. It really does have four letters. Without the numbers there, it’s dbwr. I’ll show you that abbreviation later. Dbwr. That stands for Database Writer. There can be more than one of them, always should be more than one of them, can be one, but there always should be more than one.

And if there is more than one, we say DBW zero for the first one, DBW one, and so on. So here’s multiple instances of the Database Writer DBW zero, DBW one. And very often these background processes can be altered in your parameter file. We haven’t seen a parameter file yet, but a parameter file is something that launched. That when the instance launches. In other words, when Oracle starts up from scratch, the parameter file is something that Oracle reads to see how it should run itself. And one of the things, as an example, is Database Writer.

How many writers should I have? Should I have one? No. In this case, it looks like we’ve got at least four DBW zero up to DBW three. Okay, let’s go through some of the most important background processes and the ones you’re likely to see on the exam. Here’s one process monitor. It’s got those four letter initials. PMO. N pmon. Pmon monitors the background processes. That’s why it’s called the process monitor. It monitors the other processes and makes sure that they are working properly. Or more specifically, it generally monitors to see if they are working at all.

If one of these important background processes fails, it’s the first to know, and generally it will attempt to get them started again. If that’s the appropriate thing to do, it may not be appropriate to start them again. So if they failed, something else may need to happen, like something may need to stop immediately. Mostly though, if a process, if a background process terminates abnormally, and who knows why that might happen, but for some reason, for instance, database Writer is no longer working.

Well, we got to have database writer. Database Writer does what it sounds like. It writes to the database. If Database Writer isn’t working, there is going to be no inserts, there’s going to be no updates, because there’s no way to write to the database. So process monitor. Pmon will attempt to restart the database writer or database writers. That is a critical system function. So Pmon will monitor those kinds of functions, and if they fail, if they go down for some reason, it’ll restart them. It also cleans up the buffer cache. The database buffer cache, by the way, is the Ram area where data waits to be written to the database. And once Database has been written to the database, there’s no reason to keep anything in Ram in the Buffer cache.

So it cleans it out, it empties it. You’ve been written to the database. I can take you out of the Buffer cache. Once it’s written, Pmon can take it out. And then lastly, it cleans up any background processes if they’ve terminated abnormally. So in other words, you run in the background just like I do, but I see that you’ve failed. And because you’ve failed, you’ve left the database in some problematic state, an unstable state or something like that. So we need to fix that. We need to clean up the mess that you’ve made. That’s the job of pima. So Pima is one of our most important background processes because it actually monitors some of the other background processes. And if they fail, it tries to fix it. Okay, important processes.

10. Background Processes Part2A

Database Writer, we know a lot about this one. Database Writer’s job is simply to write to the database. Don’t think that it’s writing to the hard drive. Database Writer does not write to the hard drive, necessarily. Database writer may be writing to Ram. Remember, we want to keep the least recently used data back on the hard drive, but the data that’s getting used a lot is going to be stored in Ram. Now, there is a certain time at which that data is going to be written to the hard drive from Ram. We’ll get there, we’ll talk about it. But Database Writer, its job is to make sure that the data which is being written to the Ram gets written to the Ram. Now, it also has the important job of making sure that data gets written to the hard drive. We’ll talk about when that occurs in just a second. Let me talk about one other thing that I’ve mentioned so that I don’t forget it.

As I go through the slide, there must be a Database Writer process. You can see at the top of the slide, I’ve said DBW, and then an italicized n n for number, because there must be at least one that would be dbwr or DBW zero would be the first one, be the first instance of dbwr, and then there can be more. The first writer is DBW zero, and there must be one. If you don’t have a DBW, then there’s going to be no writing to the database. Now, when does the writing happen? There aren’t a whole lot of things that you need to memorize if you’re taking the exam, or memorize if you’re the DBA working on the database. But this list is one of them, and I don’t know of any secret to help you to memorize this. It’s just one of those things you have to seal into your brain, and it’s not so bad once you start using it, using the Oracle database, because it does kind of sink into your brain. But it’s a little bit intimidating when you look at it at first, because even the list relies on a couple of other lists. But let me talk about it. It’s not complicated at all. It is not the least bit complicated at all. The answer to the question, how often does Database Writer write to the database? The answer is all the time. That thing is just writing like crazy, so you don’t really ever have to worry about somebody tripping over the cord because Database Writer is at it all the time. And that’s a security function, because you may be thinking, well, doesn’t that take time to write to the database all the time? Sure it does.

And if it’s too much for one little database writer, you should never have one. By the way, we know that. If it’s too much for two little database writers, DBW is zero and DBW one, then add some more. We’ll see how to do that in another session. So here’s when the Database Writer goes off, that is it’s. When the database writer gets up off its rear end and does its job, which is to write data to the database. This is when it happens at these times and some of them overlap sometimes it’s writing. It writes and then based on something it did, it writes again. So it’s constantly writing. It’s almost a loop of writing. Here’s the list on the slide. It writes when there are no free buffers, and when I say no free buffers, I mean no free buffers in the block. Then it knows it’s got to get things written to that hard drive. That’s kind of an emergency when you don’t have any free space in the blocks anymore. Got to get that data written to the hardware. This is writing to the hard drive, by the way. Got to get that data written to the hard drive. Got to get that taken care of.

Second, every 3 seconds, data will not sit in Ram for more than 3 seconds, which, by the way, in terms of a database, that’s a lifetime. That’s a long time to sit around. But you know, in the real world, that’s database, that’s, that’s data getting written to the hard drive a lot. That’s a lot of times to hit the database and that’s good. Data should not sit in Ram any more than is absolutely necessary. Does that mean the hard drive is spinning an awful lot in that server? Yes, it does, and it should. We want data to be accessible in Ram because we need that speed. But we need that database or that data to be on the hard drive quickly because that’s security. So it’s going to happen at least every 3 seconds. And as we’re going to see here, it’s going to happen a lot more than every 3 seconds. Database Writer is going to be writing constantly. Okay? So first, one, there’s no free buffers in the blocks. Two, every 3 seconds. Next, at every checkpoint, you control the timing of checkpoints. Checkpoint is just something that goes off. It’s just like a timer. It’s a built in timer within the Oracle system. And you’re going to say when it goes off. You might right now be saying, well, good grapes going to go off.

I mean, Database Writer goes off every 3 seconds. Isn’t that good enough? Okay, it’s your system. If you don’t have I mean, if people only write to the database every 15 minutes, then clearly you don’t need to worry about checkpoint because that data is getting written every 3 seconds. There is no stopping it. Database Writer is going to write every 3 seconds. If data is only being updated, inserted, deleted every 15 minutes, no problem. But if this is a constant thing and data is being changed by the millisecond, you may need to change things a little bit. To make it a little faster. And you may set your checkpoints in such a way that data gets written a lot more often than every 3 seconds. What happens if you set your checkpoint just saying, every 1 second, still, database writer is going to go off every 3 seconds.

There’s no stopping it. You may have it going off every 1 second, it’s still going off every 3 seconds. Believe me, the database wants its data saved a lot. A lot. Next, when the redo log buffers maximum is reached, if you saw the section on redo logs, remember you set the maximum if you want to. The basic maximum is 33% of the redo log buffer. When you get that maximum and you hit 33 or higher, 33. 1%, database writer goes off. Okay, we just filled one of the redo logs, let’s write to the database. Well, wait a minute, didn’t you just write 2. 5 seconds ago? Yes, we did. Let’s write again. The philosophy here is you just can’t write to the database or excuse me, to the hard drive often enough. We are darn well going to make sure that this data is saved. We don’t want somebody tripping over that power cord. And when we go back and try and recover this transaction data, nothing gets lost. Things are going to commit and commit and commit. The database gets written to and written to and written to the next time. And this one’s kind of an obvious one, whenever you shut down, whenever you shut down normally this is like whenever you shut down Word and it’s got that little dialog box that pops up and says, do you want to save? Well, you don’t even get the do you wanna from Oracle. You get a save, you get a commit, you get database Writer.

Whenever you do a shutdown, a normal shutdown, there are four different kinds of shutdown, and we’ll learn about those. Whenever you do a shutdown, it writes, it saves. Basically you’re shutting me down, I’m going to save everything that you’ve got. It might wait for all the transactions to complete before it shuts down, but it is going to shut down if it at all can, it’s going to save when it shuts down. And then lastly, when you do something with the table space, remember, the Oracle system keeps things in table spaces, and you may have ten tables in this table space and 25 in this other table space. What happens if you take a table space down? You can do that within Oracle, you can shut a table space down. You can change it from writable to read only status. If you do that, Oracle wants that database. Once that data saved, you’re going to take down a table space. You can’t write data suddenly to the tables that are in that table space. That’s panic time. Whenever you can’t save things, Oracle panics.

So what does it do before everything shuts down? It hurries up if it can, and it saves the data, database writer is the process that does that. So those are the times at which database writer will go off. In other words, when it’ll save data or chunks of data, when it runs out of free buffers every 3 seconds, every time there’s a checkpoint, when you hit that 33% or whatever you set it to and you redo. Log buffers whenever Oracle is shut down, cleanly shut down. And whenever a table space goes offline or goes into read only or some status which would stop a normal save to a table or some structure in that table space, So those are the times. Those are the times that database writer will go off. You can see there are a lot of them, but that’s just because Oracle wants to save data, and obviously you want it to save data. You want integrity to be strong in your data. And like I said, probably a good idea, especially for those of you who are taking the exam, to save or excuse me, to memorize to save in your memory, run database writer on your memory so that you put that list up there.

Memorize that list. Okay. The checkpoint process. We talked about checkpoint just a little bit ago in this last slide. The checkpoint process goes off essentially when you tell it to or every 3 seconds. What does it do? Checkpoint writes causes database writer to go off. When a checkpoint happens, database writer will save the data. Just yet another in that long list of times the database writer goes off. When a checkpoint goes off, you might set it at every 1 second, every 10th of a second, whatever. When checkpoint happens, database writer happens. And then the other thing that checkpoint does is it writes a system change number an SCN. Remember, everything’s got to be abbreviated in Oracle. An SCN, a system change number to a data block header. All right, let me take you back to when we talked about data blocks. Remember at the top of a data block, there was that header, that little bit of space there that could be written to because normally, if you’ll recall, data in a block gets written from the bottom up, it starts at the bottom and it writes up. Usually it gets to 33%. Then it’s done with that block and moves on to the next block because there’s plenty of room for updates and so forth. You got 67% worth of updates that can go on for the rest of that block. And then, so data is in the bottom, from the bottom up, it adds data. Then the top there was the header.

One of the things I said, that the header, what was recorded in the header was a number. I don’t know if I said back then that it was the system change number, but that’s the number that I was talking about. It’s a specific number, which is unique to that data block. And the system change number gets written there by the checkpoint. Checkpoint keeps track of the status of every block, and it does that by writing a system change number there. The system change number tells us that at that point, the block was changed. And when does the checkpoint go off and write all those numbers on the block? Well, essentially whenever you tell it to. The system change number keeps track of when was the last time that the block was written to. It does that through the checkpoint process, and checkpoint goes off when you tell it to. It has a default. So you don’t have to make a change to checkpoint. You don’t have to do anything in the parameter files to make that change. But you can if you want to. The recovery Process Rico RECO the recovery process is a fairly simple process. You may not use it. It’s not used in the default configuration of the Oracle database.

But if you run a distributed system, and a distributed system is typically a database which is distributed across multiple servers, the purpose of Rico is to make sure that the data is consistent across servers, across instances of the Oracle database. Is the data on server one? Is it consistent? Does it look like the data in server two? So unless you’re running in a multi server configuration, you won’t even worry about Rico. But that’s its purpose. That’s the reason why it exists. That’s its purpose in life.

That’s all it wants to do, is make sure that the data is consistently distributed and that it’s consistent between multiple instances of Oracle. If the data is this on server one, it needs to be that in server two. That’s its life. That’s what it does. Okay, so those are the background processes that we care about for purposes of the exam. Those are the ones that you want to pay special attention to, make sure that you know them all and that you know what they all do. There are some tuning methods that are used for some of these. That’s more the purview of a tuning class than it is for this particular class. For this exam, you do need to know all the ones that I’ve listed here, and you do need, need to know what they all do. So if you’re good with all that, then you should be okay on the exam. Thanks.

11. Background Processes Part2B

Next one is the listener registration. We will talk about the listener soon. The listener is the database’s ears. It listens to see if anybody is trying to communicate with the database. Maybe for SQL statements, maybe a select statement is coming in, some DML is coming in, DDL is coming in. Whatever. The listener listener is the program which listens. It’s the ears of the database. It listens for things coming in. It needs to run. Obviously it’s important, just like all these processes are important. It’s a very, very important process. And to make sure the database has got its ears on, there is listener registration. The listener registration process, or L reg. L reg makes sure that incoming requests can connect to the database. That’s its job and its only job. It just says, hey database, can you hear what that phone call that just came in? Did you hear that ringing? And that’s its only function. Its function is to make sure that the database is hearing the phone calls that are being made.

That’s LRAD Simson the system monitor process. And it does a couple of things really related to recovery. Okay? So first, if there’s some problem at startup of the database, it will attempt recovery. So database starts up. It sees, for instance, that there are uncommitted transactions. SMA will do what it can to see that the transactions get committed. Why didn’t these transactions commit? Who knows? Let’s roll back the old stuff, start it again and do what you can in order to make sure that this particular transaction gets committed. It’ll also try and commit those things which couldn’t commit for whatever reason when there was some recovery of the instance.

And by recovery, I mean like from a backup. So maybe the database went down in the middle of some kind of transaction. And now obviously that’s a problem because while recovery may have been happening, maybe some other transactions are coming in. So it’s going to now try to make sure that those transactions make it to the database. And then the last thing that it does is it cleans up any unused temporary segments. Temporary segments can occur when, for instance, data is being moved from one table space to another. Maybe when data is being updated for some reason, or just anytime that the database needs to place some data into a temporary area, it’s working on the data for some reason and needs to move that data from its temporary table space or tables or whatever into the temporary area, into some area where it can work on it.

So if that data is no longer needed to be in a temporary area, maybe because the transactions have finished or for whatever reason, Esmon will clean that up for us, make sure that the data no longer exists in a temporary state and that it’s no longer taking up space in that temporary table space or whatever temporary area it sits in. So that’s the system monitor its most important function. If you remember no other function, remember this it does recovery. Its job is to make sure that the recovery happens at startup if the recovery needs to happen.

So when the database gets started, it jumps in there and says are we all good? Is there any problem? Was the shutdown good last time or did somebody trip over the power cord? If somebody did trip over the power cord, is the data in the proper state? And if it’s not, if some recovery needs to happen, let’s go through the recovery. That may involve reading the redo logs or something like that. But if some recovery needs to happen will detect it and then it’ll make the recovery happen. That’s CIS Mon’s main job recovery.