Startup and Shutdown

Databases don't just appear by magic. You can go through all the steps of installing an Oracle server and building a database, but the database must be running or your customers will not be able to get to it.

Normally your database starts up automatically whenever you boot the server. In Chapter 7 we discussed how to find out which Oracle services, including databases, are running ( Start Control Panel Services , or Control Panel Admin Tools Services for W2K). The following screen shot shows services on a Windows 2000 Oracle database server with four databases running. (The Services screen on an NT server is almost exactly the same.)

Now let's discuss how we go about starting up and shutting down the database (actually, starting up and shutting down the services) when we have to, and then how to bring it back up.

Why would we want to shut a database down? Here are the usual reasons:

• Upgrades or maintenance

• An emergency

• Backups

• Loading data, such as with SQL*Loader

• Import or export

Usually you will not want to shut the database down during normal business hours, but if you must, get the word to your customers as quickly as possible.

In the NT/W2K world, there are several ways to start and stop a database. I'll talk about two in detail, and then briefly mention a few others:

1. If you have the Enterprise version, you also have the Enterprise manager utilities, which include the instance manager. I refer you to the documentation if you are running the Enterprise version.

2. With the standard version, many DBAs prefer to use an Oracle program called SVRMGRL. There are a few tricks to using SVRMGRL:

   • You must go to the directory of the database you want to shut down and start. Do not try this at the root directory.

   • Be patient. Sometimes the shutdown or startup can take a few minutes.

   • This is not supported in 9 i !

   • To get a good understanding of the process, take a look at this screen:

     

You must be at the directory of the database that is being shut down. So when you first go into DOS, use the CD command to change to that directory. Then just enter "SVRMGRL", use the ID "CONNECT", and then type "SHUTDOWN". This may take a couple of minutes, so just wait.

Note

If you go to Services , you will see it shows the database as active. This is confusing, but unfortunately for now, it's just the way it is, so accept it. The database is really shut down! Anyone trying to log on will get an error message.

To start the database, again make sure you are in the directory of the database you want to bring up, start SVRMGRL, and then just type "STARTUP". Oracle's SVRMGRL will generate several system messages that will keep you occupied while the database comes back online:

You're probably wondering about the IMMEDIATE parameter. There are actually three choices:

1. NORMAL means to wait until everyone is logged off. It also prevents anyone from starting a new session. This is the default.

2. IMMEDIATE is a full shutdown, with rollback of any active transactions.

3. ABORT is used only in emergencies. No rollback is done at this time; rather it is done the next time the database is started. Again, use this option only in emergencies. This kind of thing happens when your system is under attack, or there is a physical emergency such as a water main burst or something similar. (I have never yet run into this situation ”fortunately!)

Usually you will use IMMEDIATE . But be sure that your users are logged off first! It is best to use NORMAL , but sometimes after you've sent out a dozen e-mail reminders, called users who are still logged on, and it's getting late, you have no choice but to send out one last reminder that the database is coming down in five minutes regardless, and then do an immediate shutdown.

The second way to start and stop a database is to get to the services mentioned earlier (this is why you have to be on the database server). To start the database in the W2K world, select the Oracle Services ID that you want, and click on Action Start . And as you might have guessed, to shut the database down, click on Action Stop .

For NT, highlight the service, and then click on the Start or Stop button. On a W2K server, highlight the service with the database name , then hit Action Stop , and you will see the following status bar:

This shutdown goes very quickly. When it is done, the W2K Services screen looks like this:

To start up a database in the W2K world, again click on Action Start , and you'll see the following status bar:

Note that startup may take a couple of minutes.

Note

Services looks to the registry for shutdown information. At installation, your registry has a default of I (for IMMEDIATE ) for the shutdown; it will happen very, very quickly. You can change the setting to N (for NORMAL ), send an e-mail warning to your users ahead of time, then follow up with e- mails every five minutes. You can then check to see who has a session open . Use the data dictionary view, give them a call, and tell them to wrap it up. You can also set the wait time before the database is shut down.

To change the registry settings, go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE . Look for ORA_<SID-ID>_SHUTDOWN_TYPE and set it to N if you want. Then go to ORA_<SID-ID>SHUTDOWN_TIMEOUT and set the time to whatever you would like.

Get ready. We have three more ways to stop and start a database:

1. For NT, Oracle's Admin Assistant works very well: Go to START/PROGRAMS/ORACLEHOME/DATABASE ADMINISTRATION/ADMIN ASSISTANT . The first screen provides an overview of the Admin Assistant. Read it, then click on Oracle Managed Objects , and then keep clicking down the tree until you get to your database. You can now right-click on the database name and see selections for Connect , Stop , Startup/Shutdown , and so forth.

2. You can also use Oracle's NET STOP and NET START commands. These commands offer a quick and simple way to cycle the database, as the following screen shot shows:

   

   The syntax is just NET STOP (or START ) <service name> . What's interesting here is that if you go to Services , you will see "STARTING" under the Status column for the service.

3. Another method, and this is the last one I'll show you, is to go back to the DOS prompt and use the ORADIM commands. Run ORADIM (notice the spelling; everyone tries "oradMIN"!) from the DOS prompt, and use only the two commands with the database name as shown in this screen print:

   

You do not get any messages while the processes are running; it either works or doesn't work! This is why many DBAs prefer to use SVRMGRL.

The following screen shot shows an example of an error from Services that illustrates why some DBAs prefer to use the command line instead of Services. If you use Services and there is a problem, all you get is a generic error. In this case, we were trying to start the database CWE2P:

Unfortunately, this leaves the service between up and down, and it will show a status of "STARTING." At this point all you can do is (1) wait a couple of minutes; (2) stop and restart; or (3) as a last resort, reboot the server. Often either the service will eventually come up, or a stop and start will work.

Note

You will find your own preferred method for handling this task. Feel free to experiment, as there are other ways besides what I have shown you here. These seem to be the most popular and the easiest approaches, at least for now. Many DBAs like the messages that come with SVRMGRL; others like the speed of using Services. Try them both and then decide.