Learn the steps of starting up the database to three different levels of availability depending on the task at hand. Learn several ways to shut down the database, some of them graceful, some of them not so much. You can start up and shut down the ASM instance in a similar fashion.
- [Narrator] Let's talk about database start up and shut down. Inevitably, you'll have to manually start up and shut down the database. Although, startup should be very smooth and you should only shut down on a rare occasion, for example, for patching the database or for a server downtime. Things along those lines. If you have to start up manually there are quite a few different options. Normally, you'll just type startup or startup open which is the default. It'll bring the database from being down, it will allocate the memory structures, access and open the data files and make the database available for other users.
For other users to access the database it has to be open. If you want to open the database but only restrict it to certain users that have the restricted session privilege you can open it with startup open restricted. Shutting down the database has almost too many options. Normally, you won't have to shut down the database, but if you do shut down manually you have several options. Normal, which is the default. Immediate, transactional, and abort.
Those are listed in order of slowest to fastest. As you can see, doing a shut down normal means that you're going to wait for everything to be done in the database and for all users to disconnect. So generally, I'll use shutdown immediate so as long as anybody is not in the middle of any important work it will go ahead and close all the connections and shut down the database. If you shut down with transactional it's going to wait for all pending user transactions to finish before closing connections and shutting down the database.
Abort is the fastest but least desirable method of shutting down the database. Usually it's some kind of emergency situation with the database server or it needs something to be shut down now. It does leave the database in somewhat of an unknown state and a backup made after a database has been shut down with the shutdown abort will not be consistent. So, generally avoid doing a shutdown abort unless absolutely necessary. When you do start up the database after that, with just a start up command it will do an instance recovery and it should be fine.
But abort is kind of a last ditch effort to get the database down especially if something is just not working, if all the other shutdown methods don't work. There is one shortcut that I think too many DBA's use and I think that's startup force. It's a combination of shutdown, abort, and startup. It's probably not going to cause any issues for you, but I would try to avoid it as a shorthand because you are technically forcing the database to crash and starting it up again and requesting that the database perform instance recovery.
Let's look at some of those commands in action. Here we're at the OS prompt. We're going to check to see what databases are available on this server. We use the oraenv command to set the database SID which is also the instance name in this case, the dbt121. We're going to connect to the database as sysdba and we'll try some of our shutdown options. If I just do a plain old shut down, that's shutdown normal and what that's going to do, it's going to wait for all user connections to finish what they're doing even if they just have a session connected and you can see, it's just waiting, waiting, waiting.
Well, I happen to have a SQL developer session still running and it probably has a connection to this database open. So shutdown is going to wait for that and I don't want to go back to that. So, I don't care. I don't have any transactions open, so I'm going to do a control c. I'll do shutdown immediate. Therefore even if I have an open connection as long as I don't have any transactions it's not going to cause any issues. Then I use the startup command and the default is startup normal. That didn't take too long, but let's say you really had to do a shutdown and a startup really fast or the database was hung.
You could use startup force but like I mentioned earlier only as a last resort but it is really fast, isn't it? You didn't even see it shut down. It really didn't show any shutdown messages but it did a shutdown abort and then it did a startup and there we go, the database is open. You're ready to go again.
- Design methodology and tools
- SQL query basics
- Advanced data manipulation
- Modifying and creating a database object
- Database architecture
- DBA configuration tools
- Managing tablespaces and disk space
- Table compression
- Manage system-related tablespaces
- Database monitoring