What is oracle database?
Oracle database is a relational database Management system (RDBMS) that mange storage, organization and retrieval of data.
An Oracle Database consists of a database and at least one instance.
Single Instance Database & RAC database
- A oracle database have one single instance .
- RAC database have multiple instance
Database & Instance
Instance consists of memory structure and background process. Database consists of files (control files ,Redo files ,data files)
Instance include 2 types
1. System global area(SGA)
2. Process Global area(PGA)
SGA is main memory in oracle database each server process and background process have own PGA ,so PGA is private memory area.
Process
- A process is a mechanism in an operating system that can run a series of steps.
- process run code module.
- A process normally runs in its own private memory area.
There are two type of process
- Client process– run application or oracle tool code
- Oracle process – Run the oracle data base code
Client process are started when over application you start the application and execute the application code.
Oracle process
There are two types of oracle process
- Server process
- Background process
Server process
Perform work based on client request
Ex-
Parsing and running SQL statement ,retrieving and returning result to the client program.
Back ground processer
- Perform maintenance tasks required to operate the data base .
- Each background process has a separate task ,but works with other process.
- Oracle data base create background process automatically when database instance starts.
some question:-
When server process is start?
Server process will start when user connect to database.
When background process is started?
Background process are started when you start database
Oracle Database storage structure
There are two types
Physical storage structure and Logical storage structure.
SGA-(System Global Area)
The SGA is a group of share memory structure, known as SGA components. Memory area contain data and control information for one oracle database instance.
SGA servers various purpose including
- Cashing data blocks reads from database.
- Buffering redo data before writing it to the online redo log file.
- Storing SQSL execution plan.
- Maintaining internal data structural the many process and threads access concurrently.
- All server process and background process share the SGA.
Database buffer cache
- Stores copies of data blocks read from data files.
- All user concurrently connected to a database instance share access to the buffer cache.
Redo log buffer
- Stores redo entire.(description of change made to the database)
- Redo entire contains contain the information necessary to reconstruct(redo) change made to the database by DML or DDl operation.
- Database recovery applies redo entries to data file to reconstruct lost changes.
Shared pool
Cache various type of program data like parsed SQL,PLSQL code, system parameters end data dictionary information.
Fixed SGA
An internal housekeeping area that contains:
- General information about the state of the database and instance.
- Information communicate between processes such as information about locks.
PGA
The PGA is non-shared memory region that contains data and control information exclusively for use by server process and background processes.
PGA stores information about:
- Session
- Bind variable values
- Parsed SQL statement
- Query execution state
Memory Management
Automatic Memory Management
You specify the target size for instance memory. The instance automatically tunes to the target memory size, redistributing memory as needed between SGA and the instance PGA.
Automatic Shared Memory Management
This management mode is partially automated. You set a target size of the SGA and then have the option of setting an aggregate target for PGA or managing PGA work areas individually.
Manual Memory Management
Instead of setting of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.
Background Process
Background process perform maintenance tasks required to operate database. Each background process performs a unique task, but work with other process oracle database creates background process automatically when you start a database instance.
Database Writer (DBWn)
Writes modified buffers in database buffer cache to database.
Log Writer
Writes Redo log entries to redo log files
Checkpoint (ckpt)
Starts a check point request by messaging database writer process (DBWn) to begin writing dirty buffers.
On completion of individual checkpoint requests, ckpt updates data file headers and control files to record the most recent checkpoint.
SMON(System Monitor Process)
It perform system-wide monitoring it does many things that’s why it is called system monitor.
Process Monitor
Process monitor which monitors only the processes SMON goes beyond their process and the monitor overall system.