“I can’t connect to the database. Can you please check if it’s shutdown and start it up please, Thanks” I was asked by a savvy developer once upon a time, and when I did ” Start the database” I asked them to try connecting again and they could. I then replied the database was never down 😊.
While I was just knitpicking and quite frankly joking, The fact is we actually never shut down a database because the database is just a collection of files That is all it is those files are always there on your filesystem or in an ASM diskgroup. What you actually start up is your instance which is a collection of your memory structures and background processes. This is bread and butter for most DBAs but other ITfolks might find this a very silly straw splitting distinction. So what is really the database and what is an instance? and what all happens when we “start our database”. That is our bone of contemption for today’s post.
A database is just storage, that’s all it really is a group of operating system files which are binary in nature. there include the datafiles, controlfiles and your online redo logs. These files are always there and never get start up or shut down but rather are opened and read by the background processes when the oracle instance is started. When data is queried this binary data is interpreted and returned to your session in a format that is useful to you.
On the other hand an Instance, is a collection of background processes; things like the smon, pmon, lgwr, Ckpt, Arcn, mmon, dw00 and so on and so forth. There are about 60 or so background processes required for an oracle instance and these can be viewed on linux sytems using the ps commands. It also includes all of the memory structures ; System Global Area SGA and Program Global Area PGA. The SGA is made up of different compnnents such as the Shared pool, Streams pool, Large pool, Database buffer Cache and Redo log buffer to name a few. These structures in a nutshell serve to reduce the amount of I/O s to disk that a server process will do by doing things like caching frequently accessed data and keeping changed or “dirty blocks” in memory until they are “commited” to disk at the end of a transaction. There is a lot of cool stuff going on within the oracle SGA and It’s all very sophisticated. I cannot hope to cover it all in depth here on this blog post but you can certainly read all about it here on the oracle website. Next up is the PGA or Program Global Area which is a private memory area that can be assigned to a single server process. A server process particulary in the dedicated server architecture world would represent one session connection to an oracle instance. This pga enables user to be able to peform activities such as sorts, hash join, bitmap merges and bitmap creates. The sizes of these memory parameters if you are using ASMM (Which I think we all really should be) can be set by using the initialization parameters
sga_target
& pga_aggregate_target
Now that we know a bit about what an instance is and what a database is, let’s delve into what happens when we “Start our database” so to speak.
There are 3 stages involved in a database startup, the nomount, mount and open stages. When you issue the startup command the oracle server goes through all this stages before you have a fully running oracle instance(open mode).
At the nomount stage, the initialization parameter file is read whether you’re using the older pfile or the more modern binary spfile for database parameter settings, this file is read and the memory for the instance is allocated and background processes are running for that particular instance based on what it needs. No user can connect to the database at this stage except for the sys user and if we had lost our controlfile it is at this stage we would issue a “recover controlfile command”.
Next up the database goes into mount mode and in this mode the controlfiles are read and the database knows the location of the online redo logs and datafiles. and finally in the open mode the data files are read and opened and the database is available for useers to connect to at this time. If the database was last closed abnormally, either because the database administrator terminated its instance or because of a power failure, then Oracle automatically performs recovery when the database is reopened.
So there you have it, My first blog post on the oracle start up process. I hope you enjoyed reading please leave any comments and suggestions below about oracle startup process and stay tuned as I will be posting more on the fundamentals of the database here.