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 

What happens when you shutdown a database internally

Whenever we have to shutdown a database, we would normally go by the golden rule – “The database shutdown has to consistent”. So we shutdown the database with either “shutdown” or “shutdown immediate” commands. Because we know if the database is shutdown normal or immediate, it won’t ask for recovery and would be in consistent state. So I just wondered what happens if I say “shutdown abort” at the SQL prompt. The answer would be – you will have database shut down in an inconsistent state.
So I just gave a try to identify what goes on in the background with the processes when we shutdown the database with different options.

Shutdown:

Whenever we say “shutdown” at the SQL prompt, the oracle process will wait for all the client connections to be closed before proceeding with the shutdown of the database. Once all the connections are closed, ckpt process will fire a complete checkpoint in which all the headers of the data files are freezed and updated with latest SCN. After the checkpoint is done, pmon process will come into action by cleaning the process variables and releasing the associated memory. Later smon will close the pmon process thereby bringing down the database. It also performs the cleaning up of the pmon process releasing memory associated with it.
So the database is shutdown normal without any inconsistencies.

Shutdown Immediate:

With shutdown immediate, in contrary to normal shutdown, the oracle process won’t wait for all the client connection to be closed; instead it will kill all the client connections. Once the client connections are closed, rest all processes continue to action as in with normal shutdown. Hence even when the database is shutdown using immediate option, the database is consistent.
Attached is the snapshot of alert log file:

Shutting down instance: further logons disabled
Shutting down instance (immediate)

– This is where all the connections are closed and PMON is invoked.

License high water mark = 10
Wed Aug 10 22:26:44 2005
ALTER DATABASE CLOSE NORMAL
Wed Aug 10 22:26:44 2005

– This is where PMON is closed and SMON is invoked.

SMON: disabling TX recovery
SMON: disabling cache recovery
Wed Aug 10 22:26:44 2005
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1913
Successful close of redo thread 1.
Wed Aug 10 22:26:44 2005
Completed: ALTER DATABASE CLOSE NORMAL
Wed Aug 10 22:26:44 2005

Shutdown Abort:

So my real concern is with this option. What happens when the database is shutdown abort?
Whenever the database is shutdown using abort option, the ckpt process is by passed, thereby closing the oracle bequeath process. This in turn invokes pmon process to clean up the process and releases any memory associated with it, thus bringing the database down. Eventually as the ckpt process is not fired against the database before shutdown, the database will be in inconsistent state. Oracle does not recommend using this option.
Lets take a case study. Consider we have a database by name MAHESH.

The processes pertaining to the database would be:

$ ps –ef|grep MAHESH 

oracle10 26996 1 0 14:25:55 ? 0:00 ora_p001_MAHESH 

oracle10 26998 1 0 14:25:56 ? 0:00 ora_p002_MAHESH 

oracle10 26992 26960 0 14:25:55 ? 0:02 oracleMAHESH (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 

oracle10 26974 1 0 14:25:50 ? 0:00 ora_psp0_MAHESH 

oracle10 26978 1 0 14:25:50 ? 0:00 ora_dbw0_MAHESH 

oracle10 26988 1 0 14:25:51 ? 0:01 ora_mmon_MAHESH 

oracle10 26990 1 0 14:25:51 ? 0:00 ora_mmnl_MAHESH 

oracle10 26994 1 0 14:25:55 ? 0:00 ora_p000_MAHESH 

oracle10 26980 1 0 14:25:51 ? 0:00 ora_lgwr_MAHESH 

oracle10 27018 27017 0 14:26:41 pts/9 0:00 grep MAHESH 

oracle10 26972 1 0 14:25:50 ? 0:00 ora_pmon_MAHESH 

oracle10 26986 1 0 14:25:51 ? 0:00 ora_reco_MAHESH 

oracle10 27000 1 0 14:25:57 ? 0:00 ora_qmnc_MAHESH 

oracle10 26976 1 0 14:25:50 ? 0:00 ora_mman_MAHESH 

oracle10 27014 1 0 14:26:12 ? 0:00 ora_q001_MAHESH 

oracle10 27007 1 0 14:26:07 ? 0:00 ora_q000_MAHESH 

oracle10 26982 1 0 14:25:51 ? 0:00 ora_ckpt_MAHESH 

oracle10 26984 1 0 14:25:51 ? 0:00 ora_smon_MAHESH

When the database is shutdown using abort option, the oracle bequeath process with pid 26992 is closed, thereby by passing the ckpt process and leaving the data file headers un-updated.

This could be clearly seen in the alert log of the MAHESH database:

Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 26992

Startup Force:

The startup force option internally shuts down the database and restarts it. So if you have a look at alert log of this, you will see that the database is shutdown using “shutdown abort” option and started normal. The database in this case would be in consistent state as it started up normal.

Tue Jul 31 14:54:50 2007
Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 27249
Tue Jul 31 14:54:52 2007
Starting ORACLE instance (normal)
Tue Jul 31 14:54:52 2007

Where pid 27249 is the oracle bequeath process id.

$ ps –ef|grep MAHESH 

oracle10 27249 26936 0 14:25:55 ? 0:02 oracleMAHESH (DESCRIPTION=(LOCAL=YES)(ADDRESS

For my remaining posts on Oracle DBA please click here 


What happens when you start a database internally