What happens when you start up a oracle database internally

When you start a database, basically three operations are performed :

1.       First you create an instance, which involves the allocation of some of your computer’s physical memory to Oracle (this memory is known as the SGA, System Global Area), plus a number of processes (or threads on systems such as Windows NT) which are going either to deal with I/Os (dbwr, lgwr, arch), monitoring (smon, pmon, some other optional processes) or in some cases pre-created processes to execute user requests.

To create the instance, Oracle needs one object, the parameter file, which indirectly specifies the amount of memory to allocate and which processes to run. You can specify the name of this parameter file or you can let Oracle use the default. Note that there MUST be a parameter file, even if most parameters have a default value, and that, under Unix for instance, Oracle expects to find a file (or link) named $ORACLE_HOME/dbs/init${ORACLE_SID}.ora. Under Unix again, a file named sgadef${ORACLE_SID}.dbf is created under$ORACLE_HOME/dbs and contains, among other things, the shared memory identifier returned by the shmget() system-call. This file will be read later by processes which want to attach to the SGA. You can check the presence of the shared memory by using theipcs -m command and of the processes by ps -ef | grep ora. Under the server manager utility, you can at this stage access some of the pseudo-views known as dynamic views, such as V$PARAMETER which lists the parameters read in the parameter file, or V$SGA which lists the sizes of a number of SGA sub-components.

2.       Then Oracle mounts the database. ‘Mounting’ is Oracle-speak for opening files (only one is mandatory but you are advised always to have two, on separate drives for security reasons) known as the control files; their names are held in the parameter file (control_file parameter). Control files are essential to the good working of the database as they contain all the information on the various other files (data files and log files) which make up the database, and to their status – some kind of timestamp is associated with each file, which for instance allows Oracle, when opening a file, to determine whether this file is the ‘good’ file or a restored backup and if some kind of recovery is or is not necessary.

At this stage, you can list the database files by querying V$DATAFILE and V$LOGFILE.

3.       The final step is the opening of the database, which is in fact no more than opening all the files named in the control file one after another. A special chunk in the very first file, called the bootstrap segment, is loaded into the SGA: it contains information on where to find the various data dictionary tables which describe all the user tables, physical location and so on. Note that the files may already have been opened by another instance running on a different machine in the same cluster, for instance (this is the case with the “Parallel Server” – this is why there is a distinction between an instance (one area of memory and a set of processes) and the database, in most cases one instance plus the various database files but which can be a number of instances concurrently accessing the same database files).

You can have fine control over where to stop (if needed) by adding special specifications to the startup command. By default,startup will execute the three steps, display the amount of shared memory attached, then an instance started message, then a database mounted message, then a database opened message.
You can stop at instance creation with the startup nomount command. This is used only when you create the database (CREATE DATABASE command) or when you want to recreate the control files (usually after having seriously messed up your database or to increase the maximum number of files you can add to your database) (CREATE CONTROLFILE command).

You can stop after the opening of the control files with the startup mount command. This is what you do when you want to rename some of your files : you rename your files at the operating system level, then you must let Oracle know the new names – and the only way you can do this is by an ALTER DATABASE command which is no more than a suitably disguised update of the control files. So you use startup mount, execute the ALTER DATABASE command to rename your files, and then execute ALTER DATABASE OPEN to have Oracle open the files – with their new names.

For my remaining posts on Oracle DBA please click here