Start up and Shutdown
The start up and shut down sequences are
different between Microsoft SQL Server and Oracle. With SQL Server, registry
entries, trace flags and system configuration values play a key role in the
start up behaviour. Among a few other things, DLLs are loaded, memory
structures are created, listener ports are opened and databases are started up
and recovered during the start up phase. The process is not distinctive unless
you look at the error log. Normally the DBA would start the SQL service either from
the Control Panel > Services applet (for SQL Server 2000 and before), or
from the Configuration Manager (for SQL Server 2005 and latter). You can have
some control over how the SQL service would start by running sqlservr.exe
from command prompt in single user mode using the –m switch. You can
also choose to start the server process with minimal configuration by using the
–f switch.
With Oracle, the start up happens in three
very distinct phases and you can either run the three phases altogether or have
them run as you issue the commands one after another.
To start Oracle, the DBA just needs to issue
one single command from SQL*Plus or other query tool: STARTUP. The start
up process will first read the Oracle parameter file and create the memory
structures and background processes. This phase results in the database instance
being created but it does not open the database. In the next phase, the
database is mounted. When mounted, Oracle opens the database control
file and reads its contents to determine the locations of the database files.
In the third and final phase, the database is opened. If the last Oracle
shutdown was not orderly and the files were not synchronised before Oracle stopped,
recovery operation is performed in this phase with incomplete transactions
rolled back and completed transactions rolled forward. At the end of this
phase, database files are accessible by client connections.
You can start each of these phases separately
for troubleshooting. Issuing the following command creates the instance only:
STARTUP
NOMOUNT;
If this succeeds, you can instruct Oracle to
load the control file by issuing the next command:
ALTER
DATABASE MOUNT;
Once the database is mounted, you can open it
using the final command:
ALTER
DATABASE OPEN;
As you can imagine, if there is a problem
during Oracle’s start up, you can issue these commands manually to go from one
phase to another so that the problem’s source can be identified. This is
similar to starting SQL Server from the command prompt with trace flags and
different switches.
When you want to stop SQL Server normally,
you can do so from the Windows Control Panel’s Services applet, although using
the Configuration Manager is the preferred method. Stopping the service will
result in the data files and their headers being updated and log entries being
hardened to log files before SQL stops all connections and shuts down. You can
also issue the SHUTDOWN command from the Query Analyzer or Management
Studio. One option that you can use with the SHUTDOWN command is the WITH
NOWAIT clause. When you specify WITH NOWAIT, SQL Server stops immediately
without performing any checkpoints on the database files. This results in the rollback
of incomplete transactions when SQL Server starts again.
Not surprisingly, Oracle can be shutdown
using one single command and if you guessed that command to be SHUTDOWN, you
are right. However, Oracle’s SHUTDOWN comes with some four options.
If you execute SHUTDOWN ABORT, this is
similar to SQL’s SHUTDOWN WITH NOWAIT. This is equivalent to the server being
switched off or the plug being pulled. Oracle is not given a chance to perform
any checkpoints. Files are not updated; logs are not flushed to disk.
Executing SHUTDOWN IMMEDAITE will
result in Oracle closing any existing connections that are not part of any
transaction and refusing any new connection requests. Existing connections with
active transactions will also be closed with the transactions rolled back. The
database will then be shut down.
If you want your existing connections to
finish their active transactions before Oracle shuts down, you can issue the SHUTDOWN
TRANSACTIONAL command. This is similar to the SHUTDOWN IMMEDIATE, but
sessions are allowed to complete their transactions before being terminated.
One shutdown command DBAs typically do not
use is SHUTDOWN NORMAL. This also results in new session requests being
refused. However Oracle shuts down only when all user connections are closed
and users have logged off. Unlike other shutdown methods, Oracle does not
forcibly terminate sessions here.
Apart from SHUTDOWN ABORT, the other three
methods of shutdown ensure Oracle stops in a consistent manner. When Oracle shuts
down cleanly, any incomplete user transactions are rolled back, the checkpoint
process updates the data files and log records are flushed from memory on to
the disk files. Finally, the file headers are all updated and the files are
closed.
]]>