Two SQL*Plus commands, STARTUP and SHUTDOWN, allow you to start and stop an Oracle instance. STARTUP, of course, is the command used to start an instance and open a database. In order to use it, you need to understand the various transitional states a database goes through on the way from being closed to open. These are described next, in Section 10.2.1.
The SHUTDOWN command is used to close a database and stop an instance. There are four ways to stop an instance. First, there is the normal shutdown, which closes a database cleanly and has the least disturbing effect on any current users. Three other options allow you to terminate user connections and shut down an instance more quickly.
There are four states an Oracle database may be in on the continuum between being fully shut down and fully operational. Usually, you want the database to be at one extreme or the other, either fully closed or fully open. However, certain administrative tasks, renaming a datafile, for example, require that the database be in one of the intermediate states.
To understand the four states, you need to know the difference between an Oracle instance and an Oracle database. These terms are often used interchangeably, but each has a precise definition.
Oracle uses the term instance to refer to a set of background processes and their shared memory structures. These background processes are the ones that write data to the database, maintain the redo logs, archive the redo logs, and so on. These processes all share an area of memory referred to as the System Global Area, or SGA for short. The instance only exists when these processes are in memory, are running, and the System Global Area has been allocated. Think of a program like Microsoft Word. When you first open it, you see the default document. Close that default document, and you pretty much have a blank window. Word is still running. You still have a menu bar and a window, but no files are open and you can’t do any word processing. Word in this state is analogous to an Oracle instance.
Oracle uses the term database to refer to the actual data being acted on by the instance. A database is stored in a collection of operating-system files on a host computer. To open a database, you first have to have an instance running. This makes sense if you think about it. Refer back to the Microsoft Word analogy. To open a document, you must start Word first.
Now, just as Windows lets you double-click a document’s icon to both start Word and open the document in what looks like one step, so Oracle has a way to start an instance and open a database with one command, the STARTUP command.
When you issue the STARTUP command, Oracle will start an instance, associate it with a database, open the database, and allow users to connect. This process is illustrated in Figure 10.1.
As you can see from Figure 10.1, there are four possible states to be aware of. The first state is when the instance is stopped and the database is closed. None of the background processes are running, no memory is allocated for a System Global Area, and all the database files are closed. The database is fully shut down.
The next state may be referred to as the NOMOUNT state. When you issue the STARTUP command, Oracle first starts all the background processes and allocates memory for the SGA. It’s possible to stop the process at this point. Why would you do this? One reason would be to create a new database. In the NOMOUNT state, the processes are all running, but no database files have been associated with the instance. This is like Word when all document files have been closed. It’s the perfect time to create something new.
After the NOMOUNT state has been passed, the next point you reach is the MOUNT state. This is the point where you have told the instance what database you want to work with. The control files for the instance, and only the control files, will be open. The database datafiles remain closed. Renaming a datafile is an example of a task you could perform now. If you’ve moved one of the tablespace files or log files, you can use the ALTER DATABASE RENAME DATAFILE command to have Oracle write the file’s new location to the database control files.
The final state to be reached is the OPEN state. All the datafiles associated with the database will now be open, and users will be allowed to connect. It’s important to understand this process. Next you will see how to control it using the STARTUP command.
The STARTUP command is used to start an Oracle instance and open a database. Before issuing STARTUP, you must first decide whether you want the database to be fully open or not. If you don’t want the database to be fully open, you must decide whether you want it to be mounted or not. Table 10.1 will help you decide, and shows you the basic command to reach each state.
Table 10-1. STARTUP Commands for Each State
|
Command |
Result |
Why? |
|---|---|---|
|
STARTUP |
Instance started, database files open, users can connect |
To make the database fully operational |
|
Instance started, database control files open, database data files closed, users cannot connect |
To perform maintenance tasks such as renaming a data file | |
|
Instance started, no database files open, users cannot connect |
To create a new database |
Depending on your environment, there may be other options you need to use with this command in order for your database to start successfully. One commonly used parameter is PFILE, which points to the initialization file for the database being started. The following example shows the STARTUP command being used with PFILE to do a normal startup of a database:
SQL>CONNECT INTERNAL/plumtree@plumConnected to an idle instance. SQL>STARTUP PFILE=k:instancespluminitplum.oraORACLE instance started. Total System Global Area 63475916 bytes Fixed Size 35760 bytes Variable Size 37192988 bytes Database Buffers 26214400 bytes Redo Buffers 32768 bytes Database mounted. Database opened.
This instance is started, the database is opened, and users may connect. In order to perform certain administrative tasks, you may need to only start the instance, or only mount the database. You do that with the NOMOUNT and MOUNT keywords, respectively. The following example starts an instance and mounts a database, but does not open it:
SQL> STARTUP MOUNT PFILE=k:instancespluminitplum.ora
ORACLE instance started.
Total System Global Area 63475916 bytes
Fixed Size 35760 bytes
Variable Size 37192988 bytes
Database Buffers 26214400 bytes
Redo Buffers 32768 bytes
Database mounted.By using the NOMOUNT keyword instead of the MOUNT keyword, you could start the instance without associating a database. This is what you would need to do if you were planning to issue the CREATE DATABASE statement.
Sometimes you want to restrict access to a database after it is opened, so that only the DBA can connect. You can accomplish this with the RESTRICT keyword, as shown in the following example. In restricted mode, only users with the RESTRICTED SESSION system privilege (usually just the database administrators) are allowed to connect.
SQL> STARTUP RESTRICT PFILE=k:instancespluminitplum.ora OPEN
ORACLE instance started.
Total System Global Area 5355764 bytes
Fixed Size 63732 bytes
Variable Size 5013504 bytes
Database Buffers 204800 bytes
Redo Buffers 73728 bytes
Database mounted.
Database opened.Tip
When opening a database in restricted mode, you need to use the OPEN keyword. Otherwise, the database is just mounted, but not opened.
There are other options that may be used with the STARTUP command. These are described fully in Appendix A.
The SHUTDOWN command is used to stop an Oracle instance. If you are not running with the parallel server option, shutting down an instance also closes the database. If you are using multiple instances to access a database, the database files will be closed only after all instances have been shut down.
There are four ways to stop an instance. One way is to do a normal shutdown, which waits for all active users to disconnect before stopping the instance. Alternatively, you can use the TRANSACTIONAL, IMMEDIATE, or ABORT keywords to stop things more abruptly. Which keyword you should use depends on how quickly you need to stop the instance, and what effect you want that to have on the current users.
A normal shutdown is generally considered the most prudent approach to stopping an instance and closing a database. It certainly is the least disruptive to any currently connected users. The command to issue is simply:
SHUTDOWN
When you issue this command, Oracle performs the following steps:
New user connections are disallowed.
Oracle waits for all users to disconnect.
The database files are closed.
The instance is stopped.
Step 2, waiting for the users to disconnect, may take a long time. In a normal shutdown, Oracle will not forcefully disconnect any active users, so the amount of time it takes to shut down the database depends largely on how quickly your users exit their applications.
Tip
If all you want to do is quickly shut down and restart the database, the wait for active users to disconnect can be frustrating. On the one hand, you will have users who can’t connect because the database is being shut down, yet on the other hand, you won’t be able to finish the shutdown because there are active users still connected. In such a situation, you may have to take more drastic steps, such as doing a SHUTDOWN IMMEDIATE.
Here is an example showing a normal SHUTDOWN:
SQL>CONNECT INTERNAL/plumtree@plumConnected. SQL>SHUTDOWNDatabase closed. Database dismounted. ORACLE instance shut down.
A normal shutdown results in the database activity being brought to an orderly halt. Recent changes contained in memory are flushed to files, the files are closed, memory structures are deallocated, and the instances’s processes are stopped. At this point, the database files are all consistent with each other, and a cold backup could be taken. Other shutdown options, such as TRANSACTIONAL and IMMEDIATE, allow you perform a shutdown more quickly and still leave the database files in a consistent state.
A new feature of Oracle8i is the ability to perform a transactional shutdown. Unlike a normal shutdown, a transactional shutdown results in all users being forcibly disconnected. However, before being disconnected, users are allowed to complete their current transaction. Here is the syntax to use:
SHUTDOWN TRANSACTIONAL
When you issue the SHUTDOWN TRANSACTIONAL command, Oracle stops all new connections and begins waiting for each user to commit. As each user commits his current transaction, that user is disconnected. When no more users remain, the database is closed and the instance is stopped.
An immediate shutdown is very similar to a transactional shutdown, except that each user is disconnected when her current SQL statement completes, regardless of whether her transaction is complete or not. The command to use for an immediate shutdown is:
SHUTDOWN IMMEDIATE
In spite of its name, an immediate shutdown may not be very immediate. As users are kicked off, any open transactions must be rolled back. For short transactions, the rollback process is fairly quick, but in the case of a long transaction that affects a lot of a data, the rollback may take a noticeably long time. During this time, your session will appear “stuck,” because SHUTDOWN IMMEDIATE does not provide any feedback until the rollback process is complete.
When you absolutely have to have the database down now, use the ABORT option. Aborting an instance is pretty much like kicking out the plug on your computer. All background processes are abruptly stopped. Files are not closed, at least not by Oracle, and open transactions are not rolled back. Instead, the rollback occurs when you restart the instance and Oracle performs crash recovery. The command to abort an instance is:
SHUTDOWN ABORT
Usually, you should only abort an instance when some critical event is imminent. Maybe an earthquake is occurring, and you expect to lose power any moment. Another reason to abort an instance is to remove any leftover background processes remaining after the instance has crashed. It’s possible to get into a situation where STARTUP commands fail with errors saying that the instance is running, and where SHUTDOWN commands fail with errors saying that the instance is not running. In such a case, SHUTDOWN ABORT will usually set things right again.
Since processes are stopped immediately when you abort an instance, rollback of open transactions cannot occur in response to a SHUTDOWN ABORT. Instead, this work is deferred until the next time you start the instance. At that time, Oracle will detect the previous crash, and will initiate a process called crash recovery . During the crash recovery process, Oracle will roll forward through the redo logs to catch up on any updates that were lost, and will then roll back any transactions that weren’t committed. The end result is the same as if you had done a SHUTDOWN IMMEDIATE.