Understanding Orcle Instance and Instance Management

Understanding Oracle database:
All of us are familiar with creating a new folder under one directory and placing our files under each folder in an organized manner. We have an option to search for the file name or directory. But we dont have a proper mechanism to search the data and store the data in relation to other. For this purpose we have database. More specifically we say Relational Database Management System.

Hence Oracle database is combination of operating system files having data entered by the user and the structural information about the database, which is called metadata (data about data).

If a person needs to see these stored data or need to update the existing one, there should be some processes running by Oracle and allocate some memory to be used during these operations. This background process and memory allocation together is called an instance. So whenever we need to read/write data from database we should start a new session/instance.

What is Initialization parameter?
When a person starts a new session/instance of an Oracle database to read/write, the session that opens up with some basic charecteristics which are configured in the initialization parameter file. Properties of an instance depends up on the parameter values in the initialization parameter files.

When an instance is started, Oracle database server reads these parameters and monitor them throughout the session and is stored in memory, of which some changes are dynamically. Availability of this dyanmic changes during database startup and shutdown depends on the type of parameter files.

What are different type of parameter files?
Server Parameter File:
A binary file which can be read and write by database. Can't be edited manually. This file resides on the machine where oracle is running on and changes are persistenct across database shutdown and startup.

Text Initialization Parameter File:
File which is configured by user and read by database. File is persistent across database shut down and start up.

Structure of Memory allocation while initiating a session:
The performance of Oracle database is affected by the size of instance memory structures which are configured in the initialization parameter file.
When a database is created, the memory parameters are set automatically based on database load, however it can set manually based on our usage.

Oracle provides alerts and advicor to determine the optimal values to set to handle memory isssues.

The two different memory structures in oracle are:
System Global Area (SGA):
This is a shared memory area where data and instance controlling information resides. Multiple users can use data in this area, hence called shared area.

Program Global Area (PGA):
Area used by a single Oracle server process. A server process is a process that service client's request. Each server process has its own non shared PGA when the process is started.

Why PGA is used for? What are all the kind of services processed in PGA?
To process SQL statements and to hold logon and other session information.

Oracle Background Process: Why Background Processess?
* To Manage memory structure
* Asynchronously perform I/O to write data to disk
* General Maintenance

What are different type of processes available?
* Database Writer (DBWn): Writes modified blocks from buffer cache to disk.
* Log Writer (LGWR): Write redo log entries to disk.
* Checkpoint: At a specific period of time data from buffer in SGA is written to disk. This point is called checkpoint. Checkpoint process signals DBWn, updates all files and logs the time of update.
* System Monitor (SMON): Perfroms crash recovery when a failed instance starts again
* Process Monitor (PMON): Performs recovery when a user process fails. Cleans up cache and free memory used by failed process.
* Archiver (ARCn): Copy redo log files into archival storage when log files are full.