1z0-062 Oracle Database 12c: Installation and Administration – Module 9: Critical Storage Files Part 2

3. Critical Storage Files Part2A

Here we talk about now. I want to tell you why there is normally not going to be just one table space. Not that you can’t do this, but you really shouldn’t, and Oracle is going to try hard not to let you. You’re almost always going to have at least three table spaces, and I have them listed here. They are the system, system table space, the Sysox table space, and the Temp table space. You could get rid of the temp table space. In fact, that’s not at all an unusual procedure.

You may not get rid of the system table space, and you really should never get rid of the Sys ox table space, although you could move all of its functions into system. Very, very unwise. In fact, it’s unwise to just even try to mess with it. Always have at least four table spaces. Let me go through this slide with you and I’ll tell you why. Okay? First of all, the system table space, as I’ve said here on the slide, stores the data dictionary. If that sounds important to you because you know the data dictionary is important to you, then it is.

It is awfully darn important to you. In fact, the data dictionary is so important, we don’t even access it directly. We access it as we know through the Vdol views. We only view the data dictionary. We don’t ever view the tables in the data dictionary. That’s how important it is. We never, never perform DML on the data dictionary. It just isn’t done. Only Oracle itself does DML on the data dictionary and it’s doing it all the time. It’s doing it constantly.

That’s a necessity throughout the entire life of the database system. But it’s got to happen and it happens all the time, but you don’t do it. That’s something you will never do. You will only read the views in the data dictionary. So if it sounds like the data dictionary is very important, yeah, absolutely it is. You know that if the data dictionary were to go offline or were somehow to become corrupt, if that sounds like a serious problem. Yeah, it is. So if you’re thinking I don’t even want any DML to go on in that system table space, you would be very correct.

So if no DML is going to happen in that system table space, except that which has to on the data dictionary, you probably don’t want to put any tables or any other structures in the system table space. Right. Good thought. Don’t do it. However, if you use the standard installation in Oracle and you just click on next as you do the install, where’s it going to put those tables, system tablespace, don’t let them go there. Make sure that this doesn’t happen.

It’s true that you do want the system table space. Why it’s got to be there because it’s got the data dictionary in it. We want the dictionary to live alone. We want it to live a lonely life in its own table space. Let’s talk about the other two table spaces. Sysox. Sysox stores what’s called the automatic workload repository. The AWR is something we don’t even get into in this class. The AWR is a bunch of database objects which help you with performance tuning. I love the performance tuning class. I think I’ve said before, it’s my favorite class to teach.

And performance tuning is one of my favorite things to do with the Oracle database system. So when you take that class, and I hope you do, you can learn all about the automatic workload repository. But for now, just realize those are some objects that help you tune the database. The Oracle OLAP, that’s also a very cool thing that we also don’t get into in this class.

The OLAP is a series of objects that you can use and they’re all graphical. And if you like graphical user interfaces, you’ll like these tools which you use for performance tuning. So you can see for instance, on a graph. And they’re not all for graphical performance tuning, but that’s the slick thing about them. You can use those tools to see with a graph what happens when you do different things with the database to tune it.

You can watch whether the database, whether you can reduce, for instance, the number of times the hard drive gets hit, or if you just change sometimes tweak small things, whether the database itself becomes faster. Again, not part of this class, but still something you probably need to know at some point. And the Spatial and Graph tools, which is a separate set of tools, it doesn’t come included with the Oracle database system.

The Spatial and Graph tools are some data analysis tools, so they’re not so much about tuning, although I guess you could use them a little bit for tuning. But the Spatial and Graph tools are a set of analysis tools that you use for your data. They’re just data tools, so you use them to analyze the data itself. And you may never see those tools as a DBL, but they’re interesting.

Some time to learn about it could be very important to your business. Separate set of tools sold by Oracle. So that’s the system table space which has the data dictionary that’s Sysox and you can see it contains a series of specialized tools. So that’s two. If it sounds like Syslox is a bad place to put your data because it’s got these specialized tools in it, you’d be right.

Then lastly, the third special kind of table space that I want to talk about is the Temp table space. The temp table space really has a couple of functions, but the one that we’re really concerned about in this course is that it performs large sorts, that is the sorting of data. So say for instance, you’re just sorting say, 150 rows, which typically is not a very large sort that will occur in whatever table space your tables are in. The tables that you’re sorting on the temp table space, though, will usually be used for, say, I don’t know, thousands of rows, millions of rows, something like that.

So the temp table space is normally mostly empty, except that it’s got a lot of empty Ram space in it, so that it’s free to sort these large pieces of data. That’s normally what you want to do with the temp table space. If your database is very small and you don’t do those very large sorts, and I say large, and I say a million rows is large, I don’t know. I mean, ten rows may be very, very large in your database system, but I don’t know how large that would be.

Typically ten rows is nothing. Typically 1000 rows is nothing. So a sort of, say 1000 rows would occur within any table space that you would put data into. That’s a pretty typical kind of sort. But very, very large tables with very, very large amounts of sorts would sort themselves in the temp table space. So you might want to have a temp table space for that purpose. You may never use the temp table space, you may never need it because your database may never do large sorts. And what is a large sort? This is another topic that’s more for the performance tuning class than it is for this class. And I can’t answer the question what is large? It’s one of those how high is the sky questions because what is large to your database may not be large to my database, or vice versa. It just depends on the database system and what your hardware configuration is, how large your disk drive is, how much Ram you have, those sorts of things. But the bottom line is the temp database is normally used, or the temp tablespace, I’m sorry, is normally used for large sorts and that’s typically all that it’s used for.

You may never use it. So let’s talk again about these three typically, just very quickly, let’s talk again about these three tablespace types system, which in our world we now know should only hold the data dictionary for a number of reasons. We only want to put the data dictionary into it. Sysaxx, which is really only going to hold these Oracle tools in it, the data needed for these specific Oracle tools, and Temp, which we are typically going to use just for large sorts. We don’t have to, but it’s best set aside for large sorts. There are a few other things, but I won’t get into those now. Now, what we’re missing, of course, is a place for our data, which is the whole reason we have an Oracle database system. We’ve got no place to put our data and where are we going to put our data? Well, if you use all the Oracle defaults, it’s all going to go into system we don’t want that.

So what we want to do is create at least one other table space. Maybe a dozen other tables, maybe 100 other tablespaces. I don’t know, depends on how you want to configure your database system. I’ll talk about that just for a second. I won’t linger on it here, but I’ll talk about it for a second. But why do we want to create a different table space for our data? Well, because these other three all have specific reasons for being alive. They have their reasons for existence, and storing our data isn’t one of them. And I know you’re thinking, what the heck? Okay, well, now let me get to the what the heck. Why you want one or more of your own table spaces into which to put your own data. Let’s say you’re doing backups. Backups you may do by the table space. That’s an option within Oracle is to do backups by the table space, which means you may want to take those table spaces down for a short time while you do the backups. And maybe the database is going to continue to run.

Well, the database won’t run if you take down the data dictionary, right? So you don’t want to take down the data dictionary in the system table space. And if you’ve got tables that you use for your own data, your company’s data in the system table space, to back up that system table space, you have to take down the data dictionary. Actually, you don’t. There’s a way around it, but let’s just skip it so that I sound like I really know what I’m talking about here, okay? Because you can take things down by the table, but anyway, you want to do it by the table space. If you take down the system table space, you’ve taken down the data dictionary. You can’t do that. That’s impossible. The database itself won’t run without a data dictionary. So that’s a reason not to put tables into the data dictionary table space, the system table space because you can’t do a backup.

You can’t take it down for a backup. Well, how about if you corrupt a table? Not you, someone else, right? How about if some table within the system table space gets corrupted and you need to take it down for repairs, for backups, for restoration, something like that? You just need to work on it. Well, if you’re going to take it down, you’re going to take down the data dictionary, and you take down the data dictionary, you take down everything. So that obviously will not work. That’s a serious problem for us. So you can see the only real answer is right from the offset. Put your own data into its own table space or table spaces. Just an important thing to remember from the very beginning when you’re creating your Oracle database. All right, let’s talk about another very important set of files that exist within the Oracle database system, and that’s the redo log files. When you do data DML on the Oracle database system, and in particular, in particular, sorry, when you do an update or a delete, that part of DML, that bit of DML is always saved into what’s called a redo log.

And the redo logs. And the reason why they exist is because they redo DML if such a need arises. The reason for that is, what if somehow you lose some of your DML? You’re going through a transaction, you’re inserting data. Inserting? Well, not inserting, but let’s say you’re updating data. Updating data. And as you’re doing these updates, the system crashes. Now, you’ve lost that transaction, maybe right in the middle. That’s a problem. Now, there are other ways you can back up your loss, and we will talk about backups and restores in another section. I already said that. But wouldn’t it be nice if Oracle could just pick up right where you left off and fix what the problem was? And one of the ways that it does that is through the redo logs. Because the redo logs are capturing your DML as it happens, so that if the system goes down, it can just pick it right up again and go back and restart that transaction. Maybe more than one transaction. Depends on how much data you lost.

4. Critical Storage Files Part2B

So the purpose of the redo logs is to recover data when a commit, for some reason, doesn’t happen. So once the commit happens, then we’ve moved the data into the tables where they should be. Theoretically, the data is on the hard drive, and it’s saved. Our problem is, what if we’re in the middle of a commitment or in the middle, excuse me, of a transaction, and we somehow lose the data in the middle of a transaction? That’s what the redo logs are for. The redo logs. This may not be important now, but it’s going to be very important later. The redo logs are multiplexed, always from the outset, from the point at which it is installed. Oracle will make sure that the redo logs are multiplexed and they are circular. In other words, an entire redo log will fill up, and then it will move on to the next log. So log one fills up completely. Once it’s full, it moves on to log two.

Once that log is full, then it moves back onto log one and refills it again from top to bottom. Actually, it refills it like in a clock fashion. It refills it around a circle. Once it refills redo log number one in a circle, it jumps again to relog two. If there are three redo logs in a sequence, it does one, two, three, back to one, then two, then three, et cetera. Even if a commit happens, let’s say, in the middle of redo log number two, so data is on the hard drive, the same process just keeps on going. It does not jump back to redo log number one. It’ll do. Let’s say, for example, fill up redo log number one, jump to redo log number two. Commit, keep going with whatever new data is coming in. Commit. Jump to redo log number three. Fill it with data. Fill it with data. Jump to redo log number one. Fill it with data. Commit.

So at whatever point the commit happens, the redo log will just roll right along, 123123, however many logs you have. But there must be at least two, obviously, because they work by jumping from one to another, and they commit at whatever point is appropriate. And remember, there are a series of times at which commit occurs. So you’ve always got to have at least two groups, and you’ll always have more than one in a sequence. Within those groups, they are multiplexed, and there’s always more than one in a sequence of those multiplexed groups. How do we find the redo logs on our hard drive? And the rules for the redo logs, I say just me, I say should be the same as the control file. Put them in some safe place somewhere. Now, be careful, by the way, because the control file is very, very small. It’s probably the smallest. Because I know what you’re thinking. You’re thinking, I’m just going to set up one machine, and that machine is going to have my control file and my redo logs. That’s all it’s going to have on it. Okay? Which is, by the way, I think that’s probably fine. But be a little bit careful. Control file is just a little tiny binary thing. It’s not even a text file, it’s just a little binary thing.

It might be K. Not even Megs. It might be the size of K. That wouldn’t surprise me a bit. Redo logs, however, can grow very, very large because they’re all the DML data, they are completing all the DML data, storing all that DML data, at least in terms of inserts and excuse me, at least in terms of updates and deletes. Inserts aren’t stored that way. There’s a reason for it, but I won’t go into it. Right now. Let’s take a look at where we find our redo logs, which are not going to be on the data hard drive, hopefully. Select group number member from Vdol log file. There’s our data dictionary view. And what do we have? Group number. And there’s the group number. It looks like there’s only one. In this particular series we have, on a different hard drive, group number one.

First we have group number two listed, then we have group number one. A lot can go on with these redo logs, though, because remember, we’re moving from log to log to log. How do we know which one is active? In other words, which is the one which is currently being written to? Because they go in a circular fashion. You know, one, two, three, back to one or however many you have in the series. Select group number members. Don’t get confused. It’s not member, it’s members in this view. I don’t know why. So that group number members status from VDA log. Different view from the last slide. This is Vdol log, group number members. And then the status we can see in this particular case, in group number two, the status is currently inactive. In group number one, the status is currently active. So the redo log, a redo log in group number one is currently being written to. When that one is full, what’s going to happen? We’ll jump over to group number two and we’ll start writing on group number two. What happens to the data in group number one? Nothing.

Redo logs do not flush out, they just get overwritten in a circular fashion. The data is always there. If the redo logs been filled in the past, it stays filled. Data gets overwritten, it never gets flushed. What are the statuses we’re going to get that we could have from that last? The Vdol log data dictionary view. Here they are. Status you’ll probably never see unused unless you’ve just added some new redo logs, which you can, you can certainly do that. You can do that right in the middle of database performance. And you can actually watch this status as they get written to especially if you have a very active database. When we teach this class as a lab, we actually show the students as data gets written to these logs through these data dictionary views.

So what’s one possible status? Unused, maybe you’ve just added some redo logs to your database system and they’ve never been written to in the past. That’s when they show up as the status of unused. That status obviously is not going to last very long, especially if you have a very active database system, because they switch one to the next to the next status of current, that’s the current group of logs being written to. Don’t take them down, take down the redo logs. And it is possible, by the way, to turn off redo logging so that you could take down all the redo logs, but typically you will never do that. And if redo logging is active, you don’t want to take down the current.

Although Oracle is not stupid, it knows if something happens to one set of the redo logs, it knows to switch to the next set of redo logs. So the current status, if that status is returned to you, is that’s the current active logs being written to. If you get active as opposed to current, that tells you that there’s a problem, which is kind of a weird status that gets returned. If a status is current, it’s currently being written to, which is fine, there’s no problem with that. These things get written to. That’s their purpose in life. If you get active, it means they’re not being written to. And why not? Active means there’s a problem. They are turned on, but not being written to, not current active is an issue. Active means they need to be recovered somehow. Something has happened there. It’s not difficult, by the way. And if you ever take the backup and recovery course, you’ll see that basically what we do is we take them out and put them back in. When they’re back in the sequence, they just start rewriting again, chances are, unless something’s really wrong with them. So if you see the active status, that means there’s a problem. They got stuck in the middle of a right or something like that, but they’ll never become current unless somehow the database recovers them. But chances are they’ll never become current. They’ll just sit in that active status just for a second.

Should you look at your redo logs every once in a while to make sure that none of them are active? That weird. Status of active? Yes, because remember, if they become active, they’re out of service, they need fixing. Active is bad. Active means chances are they won’t become current. So you got to take them down, erase them, whatever you need to do. Be careful with that. I know that thought scares you, and it should. So there are some procedures that you might want to use before you take them out completely you want to make sure you’re not going to lose data, probably not a problem. The redo system is a backup system, so it won’t hurt your Active data. It shouldn’t hurt your data tables for any reason. But if you see Active, it’s bad, it’s broken. Clearing this is a way of fixing an active redo log. If it’s clearing, it’s in the process of being erased.

So we can erase it, we can start making it current again. We can put it back into the system so that data can be written to it. And one of the ways that we might clear it is, say, alter database clear log file. That just basically erases that particular file so that you can take it out of Active status and hopefully put it back into Current status. Or when its turn comes up again, it’ll go into Current status. Clearing current is where it’s basically being turned off completely by the system so that it can go back into current status again. And then inactive means it’s not being used.

You may have turned it off because it is possible to set a redo login to inactive status, and maybe there’s some problem with it, or maybe you’re just not using it anymore. Various reasons why you may not take it inactive, essentially. Okay, so those are some critical files within the database system. We’ve talked about the redo logs here. Very important because that’s a major, not the only backup, but a major backup system within the Oracle database system, the control file, which is essentially the bridge of the Enterprise. You don’t want your control file going down. And then, of course, we talked about the data files, and we know the data files are important because that’s where our data file or that’s where our data is stored. Thank you.