A High Level Comparison Between Oracle and SQL Server – Part III
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:
If this succeeds, you can instruct Oracle to load the control file by issuing the next command:
Once the database is mounted, you can open it using the final command:
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.