1z0-062 Oracle Database 12c: Installation and Administration – Module 8: Starting Up and Shutting Down Database

1. Starting And Stopping DataBase Part1A

So in this section we want to talk about something that you would think would be pretty simple, which is running the database and stopping the database. And it is pretty simple, it’s just not that simple. It’s not as easy as say, double clicking on an icon on your desktop or clicking on File exit to shut the program down. Or shut the program down. It’s a little bit more complicated than that, and it can be considerably more complicated than that, but it’s not so bad.

There’s some concepts that we need to go through here, but nothing too terrible. Remember, Oracle is a big complex system and that’s why it’s a really good idea to do a class like this, actually, in most cases, several classes like this, in order to understand all the intricacies of it. And a lot of people like myself make a career off of software like Oracle. And therefore it’s important to understand the ins and outs of it. And especially if you’re going to be certified in Oracle, there’s a lot of stuff you’ve got to know. It’s not that it’s terribly complicated, it’s not, it’s just having it explained to you. But there is a lot to it.

So even starting up a database and turning off, if you will, shutting down a database, even that has its nuances. And so we need to talk about a few of those nuances. It’s not like you walk into work in the morning and flip a switch and then when you leave, you turn off that switch at night. Actually it’s really easier than that with the Oracle database system because when you walk into work in the morning, the database is probably already up and running and it probably has been for weeks. And when you leave at night, chances are you won’t flip a switch to turn off the database. Actually less than that, because you probably never will turn off the database. I think at one point we had a record of a running Oracle database on a Linux system. I think we ran it for two years and three months without ever shutting it down. And it ran like a dream for all that time. Two and a quarter years we kept that database up and running. And I say we kept it up and running. We did. But it’s not through anything necessarily that we did. It just ran itself for two and a quarter years.

So we just had to do, yes, we tuned it on a daily basis and we did the different things, the different things that we had to do to keep it running and in good order. But we did not do a whole lot of work to that database to keep it running for a long time. And I hope that you have that same experience. One day it crashed, and we never figured out why it crashed and we shed our tears over it, but then we brought it back up again within an hour or so. And that’s just the miracle that is Oracle. These things are going to happen. There is a course out there in recovering your database. There’s a course out there in keeping your database running. Hopefully this is a great background for this, especially in keeping your database running. Hopefully you will never have to recover your database. Hopefully that son of a gun is just going to run and run and run, and you’ll never have to worry about it.

The only time you’ll ever have to shut it down is when you’re moving to a bigger office and a bigger building somewhere. But let’s talk about, in case you ever need to know, how you’re going to shut down that database and how you’re going to start up that database. Both concepts are very, very easy. They’re very easy to do. Oracle does it for you. If there are no problems with the database, if it’s been backing itself up normally, then you probably will be able to do this with no problem whatsoever. It’s as simple as it may be. Just as simple as double clicking on an icon on your desktop and Oracle will run itself. And if you’ve properly tuned it. And tuning is very fun, by the way, in Oracle, it’s something that I have always really enjoyed. Two things that I really enjoy with Oracle. One is programming against the Oracle database, because I started out my career as a software developer back in the days of, I don’t know, Visual Basic version three, or something like that. And so I’ve always enjoyed programming. The other thing that I really enjoy doing is performance tuning. Especially on an Oracle database, most especially on an Oracle database, because I don’t know why, I just think there’s a challenge in getting your database. Maybe race car driver or race car mechanics have the same thrill of just getting that engine to work as perfectly as possible. That’s the thrill I get from tuning Oracle. Maybe I’m just nerdy that way. If it is, then it is what it is. So that’s just something that I enjoy. So let’s talk about starting up and shutting down the database. It’s just if you’re doing this just normally, there are several tools out there, several graphical tools that you can use, and non graphical tools that you can use.

The way that I normally run a database, the way that I normally do most things, and maybe it’s just because I’ve been doing it for a while, is to use SQL Plus. And I do almost all of those basic things from SQL Plus. My life as an Oracle DBA is run from SQL Plus. It is a fairly simple tool to use as you know you’ve seen it. It’s a command line tool. If you haven’t seen it, by the way, go ahead and run it. Matter of fact, pause a video and go run it right now. Just so you can take a look at what it looks like. And I’m not at all a command line person, not at all. I am purely a graphical user interface person. That’s one of the reasons why I kind of like Windows. I shouldn’t say that I necessarily like Windows, I’m kind of drawn to Windows because I like the graphical user interface. Same way, even when I run Linux and Unix, I tend to stay in a graphical user interface. That’s where I want to be. I like graphical systems. But when it comes to the basic tasks in Oracle, I run from SQL Plus most of the time. And if I’m going to and by the way, the exception to that is when I write somewhat complex code. And for that, it’s just so much easier to use a graphical interface system.

And I would suggest that you do the same thing. Once it gets to more than three lines of code in a single statement, then it’s probably time to break out Toad or one of those tools. But anyway, for the most part, I’m in SQL Plus all the time, so no problem to run the database from SQL Plus. I’ll even show you how that works from you. In fact, I’ll show you how that works in this section. And I won’t show you how it works from any other section, because it’s pretty much the same thing even in a graphical user interface. You’re just typing startup and shutdown for the most part to run the database system. It may be even clicking on file startup, but that’s how it’s done.

So anyway, typically, I think as a DBA, even as a developer, by the way, you will very typically be using SQL Plus. And I think SQL Plus is a great tool for starting up and shutting down the database. And if I had problems with the database and I needed to recover my data in the database, and you may find this surprising absolutely, SQL plus would be the way that I would do it. I’ll show you why here in a few minutes. The other thing that you might use graphical tools, graphical not very complex at all, is Armand, the recovery manager. That’s what it’s for, is recovering the database. So it has things built into it for that specific purpose. It will do a lot more, but its whole reason for existing is to recover the database. And so if you’re going to use a graphical user interface, and it’s not even a very graphical graphical user interface, our man is a good one to use.

2. Starting And Stopping DataBase Part1B

If you want to get up to the big fat tools that can do everything, oracle Enterprise Manager very easy to start. In fact, that’s one of those click on the menu startup, click on the menu shutdown type of tools. It’s a little bit harder once you go into true recovery where some problem has occurred with your database. And so you may not want to use these graphical tools for that necessarily. Although those features, the features that we’re going to talk about, that is ways to start up and shut down the database in an unusual manner, where you’re not just doing a start up or a shutdown. Those are built into the graphical tools, all the ones that I’m aware of, I think, unless my mind is slipping with age, as I recall, that’s built into all of them. And there are some third party tools out there. There are lots of great third party Oracle tools out there. And I’ve mentioned my favorite in the past, or one of my favorites, which is Toad, which is the tool for Oracle. Application developers don’t think of it necessarily as a developer tool, although it is a great developer tool. There is a version of it, and any version is good for DBAs, but there is a version of it specifically for DBAs which will help automate your tasks. It’s a complex piece of software. It certainly merits its own course, or at least its own book on how to do things. But Toad is a good tool and there are lots of good tools out there, third party tools that are available for Oracle that just do all kinds of things within the Oracle database system.

They’re great to use, really great to use. If you’re starting out and you’re not so happy about using SQL Plus or one of the command line tools you’re still a little squeamish about, oh, my gosh, this is a big database system, and it’s got lots and lots of options, which it is. But get into SQL Plus and do things by hand, and I think you’ll be happy with it. And in this section, I think everything I did, as I recall as I was creating these slides, I think I did everything from SQL Plus. Not a whole lot of coding here because there’s not a whole lot of coding to starting up or shutting down a database. It’s all pretty simple and there’s just a few options available to you to do it. It’s more a question of did you do the startup or the shutdown correctly, especially the shutdown, rather than did I use the right tool or is it that complex to make it shut down the way I wanted it to? Okay, so let’s get into it now. So any tool that you use, I think they all have some well, not all, almost all of them have some method of doing a startup and a shutdown. But I think good old SQL plus. I like SQL Plus. It’s a great command line tool and that’s what I use for most of my DBA tasks. Easiest way to run SQL Plus, or excuse me, to run the Oracle system startup. That’s all that there is to it.

As long as you’re connected to the database, as long as you have logged in and you are connected to the database startup, by the way, this doesn’t start the program. Starting the program is even easier still. Starting the program is just running the computer, just running the server. Once the server starts running, Oracle will start running. Oracle is basically a background process, or at least the thing which gets it all running is a background process. So once you turn on your Linux or your Windows or whatever server Oracle goes, oracle is ready to go. In fact, in most OS’s, it’s not something that you stop the Oracle system on, say, a Windows boot, Oracle will run. And there was a time when our computers were a lot less powerful, a few years ago, not that long ago, when that was a bad thing, when you did not want Oracle running because it was going to really dog out your server, it was really going to use up a lot of system resources. But now servers have come so far that if you’ve got a relatively recent server, you’ve got something fairly new.

And servers are cheap. They don’t cost a whole lot more generally than, say, your home computer. Once you turn that computer on, you can just let Oracle run, even though you may be running lots of other kinds of software. If you are. For instance, I have on one of my computers at home, I have Oracle Enterprise running because, as you know, for learning purposes, you are more than welcome to download Oracle Enterprise or Oracle Standard, either one. You can download them onto any machine and use them. As long as you’re doing it for learning purposes, it’s free. Oracle is free for learning purposes. And there was a time many, many years ago when I started as an Oracle trainer, that that was a problem, that you had to start up your computer without Oracle running because Oracle was just too much of a drag on your computer. Now I don’t even worry about it. Oracle runs with everything else. It’s no problem. You can start up or shut down Oracle as you want to, and you can certainly set your computer so that Oracle does not automatically start up when you start up the computer. But I do not think you’re going to notice any difference. Oracle now as big a program as it is, as many processes as it runs at a time, if you take a look at your process monitor in your system, you will see that it runs very, very lightly. Now I just let Oracle run. I don’t even think twice about it, and I’ve never shut it down. Because of computer resources, because of a computer resources problem. And I run it at home on one of my computers just so that I can run exercises and things of that nature. And I believe my computer at home that I run that on is a standard Windows eight computer. So that’s what I use. And so if you’ve got something like that, you are welcome to oracle gives away the software so that for learning purposes only, oracle gives away the software.

You can download it from the Oracle website and put it on your machine and you can feel free to run whatever exercises you want to. Okay? But if your Oracle system is not running, the first thing you do is connect to Oracle from SQL Plus or whatever system. So you’re going to connect as, say, Sysdba. And if your Oracle instance is not running, easy to do, well, there are a couple of ways to do it in Windows, a couple of graphical ways to do it in Windows, but let’s forget that for a second. Let’s forget the graphical methods and you are not going to see those on the exam, by the way.

The way to run Oracle is once you’ve logged in startup, that’s it, that’s all that there is to it, startup. And I know you’re thinking, wait a second, what do you mean once you’ve logged into the Oracle system, remember, the Oracle background process will be running and that’s going to happen at startup no matter what. If that’s not running, then go into your system, however that’s done and go ahead and start the Oracle process. It’s going to be called Oracle Exe or whatever it’s or just Oracle on a Linux system and get it running. Now you’re not running the database, you’re not running any of the, you’re not running database writer or anything like that.

None of that is writing is running. You’re not accessing any data, you’re just running the one background process, which is listening to see if anybody logs in. Then go ahead and log in to say, SQL Plus. You may want to log in as Sysdba or as system, because remember, they have special powers when it comes to starting up and shutting down the database. But if you just want to start up the database, logging in as any DBA will work for you. If you’re just doing a regular startup, logging in as a regular DBA is fine. And how do we do it? We type the word startup. That’s all that we do. Startup is the default method for starting up the Oracle database. Once you do that, the entire system should come online.

If it doesn’t, we’ll talk in this chapter about some things that we might want to do to see what’s going on with the Oracle database or to get it running maybe so that we can find out what’s going on with it. This isn’t the course to try and fix the oracle database, that’s a different course. This is just the Oracle class for running an Oracle database. But if you just want to run an Oracle database startup, technically the command is startup open. Because we want the database to start in an open way. And when we say Open, we mean the database is open, accessible. You can retrieve data to it, you can perform DML on the tables, that’s the Open status. But Open, of course, is an optional keyword. As long as you type in startup, then the database system will run. Actually, it does a couple of things. It starts the database, it mounts the database and it opens the instance of the database.

And what do we mean by that? Start runs the Oracle software. That is, it’s got everything running so that it’s ready to move on to the next step. In other words, the basic software processes are going to run, but there’s no instance running. What I mean by instance that is there’s no ability to access tables. Those processes are not going to start. The only thing that’s going to run is the Oracle program. Well, some of the background processes run as well, but basically the Oracle process is going to run and it’s going to get into a state where it can access data so long as the next couple of steps are taken. And what are the next couple of steps? Next is the Mount State. When Mount happens, that means it’s opened itself up to database access. All the background processes are going to run in the mount state. The last thing that needs to happen is it actually needs to make its connections to the database tables and what other structures are in the database. And that’s going to happen on Open. Once the database is open, then that means it’s open and accessible. It’s in the regular state of running the Oracle database system. So those are the three stages.

Start, which is just when Oracle itself is open and running and the background processes have begun to run. It’s not just a listener waiting for somebody to log into it and start it up. Start means the Oracle software is actually running and it’s ready to connect to the database. When mount happens, there’s a full blown instance of Oracle and the control file runs. So all the parameters from the control file have run. Now Oracle knows where its tables are, where its table spaces are, all of that is ready to go. And once it’s opened, then the database is in a full blown running process. That’s when Oracle is actually fully running. When it’s in an Open state. That’s the basic way, that’s the way that you’re almost always, if you have to run Oracle, that’s the way that you’re going to do it. You’re going to do it with the startup keyword. Or you could say startup open, but that’s not, you don’t need to say Open, it’s just startup now let’s talk about what we should say is an emergency situation. Because like I said, every day that you need to start up Oracle, normally it’ll just run forever, but if you ever need to start it up for whatever reason, you’re just going to say startup. These other conditions are all problem conditions for the startup. Not necessarily problems, but something other than the database going into a normal condition is going to start.

So what do we do if we need to say, work on the database or it’s an emergency and the database won’t start up or we can’t see the data or something like that’s going to happen? So these are what I call problems, may not be a problem because you may just need to do some maintenance on the database. You may just need to change some parameters or something and you can’t do that while the database is running. Chances are this is going to be an emergency. Database data has been lost, the database won’t start up. For some reason you’re getting error messages. So this is the other statuses that you can start up other than startup open. Remember, startup open is condition one. We’re going to talk about three others. The second is startup no mount. So this runs the Oracle software, but it has no background instances. It’s got no background software running in it. It’s got Oracle exe in Windows running or the Oracle program running in whatever system.

So we have Oracle running but nothing else. The database has not mounted. The mount status is when the database is getting ready, if you want to think of it that way, to open and run as a database system. So this is not mounting any of the database. When would we do this? This is really rare, but the time that we would probably do this is a database creation. That’s about the only time you would start up no mount. And when we say database creation, we mean we’re going to lay down the data dictionary and all of those things on the hard drive. We can’t mount in that instance because the background processes haven’t even been created. All that we have is the basic Oracle program running. So typically startup no mount. Our next step is going to be to create a database next sort of emergency condition, startup mount. In startup mount, we’re actually going to mount the instance. So remember, startup, the lowest thing that we can do. Startup no mount, that’s the lowest thing we can do. Starts oracle exe starts the Oracle program, whatever it’s called on your operating system. So it starts the Oracle program and that’s basically it. Startup mount is much more serious because it’s going to run the Oracle software, but it’s also going to start the Oracle instance. And it’s got one file that it’s going to look through and that’s the control file can’t get to the data in a mount condition because we haven’t opened the database yet. We’ve just mounted it. It’s like in an in between state. We got a control file, we’re ready to open the database, hopefully, but the database has not yet opened, and this is not at all an uncommon condition. Startup. No mount is uncommon because, like I said, it’s usually only used for creating the database startup mount is something went really wrong.

Typically you’ve lost some tables, been some corruption, maybe oracle runs on server number one. Your main table space, the one that you have all your tables in, that you care about, is on server two, and something’s going wrong on server two. Somebody started to erase stuff. Somebody started to format that hard drive on server two. You got a serious problem now, and you’re going to have to run some backups or something on server two to reclaim that hard drive. Typically, you’ll start up mount with startup mount, you may be able to see what happened, maybe because you do have Oracle running a little bit. You have some processes running, oracle exe is running.

You got a control file, so the thing will start up and you can tell. A control file, by the way, will tell you where the rest of the database is. It’ll tell you what hard drive things are on. You may get an error message, something like tablespace, my table space does not exist. You may get an error message. You may get an or a negative whatever, error. Not sure which one that would be off the top of my head, but at least you may be able to look at some things and say, oh my gosh, that server in the next room across the hall is not working for some reason. We better find out why. And then you find out. Half the data has been erased and you need to recover it. So that’s what you can do in the mount condition.