Database and instance
1. Oracle Database and Instance
As Oracle defines it, Instance comprises of the shared Memory
structures and Background process which taps into a database i.e
manipulates the database.
Database is collection of operating system files, that is used to store
the data, which is the users data and the meta-data
show parameter instance_name // To get the instance name
show parameter db_name // To get the database name
NOTE : Show parameter command shows us the value of the initialization parameters
2. Memory structures
Oracle Instance is Memory Structure + the Background Processes
Buffer Cache, Log Buffer and Shared Pool are essential components other
are optional. Now we would have a look at each component
Buffer Cache : When data is read from the database it gets cached
in this area. db_cache_size is the parameter for
Log Buffer : The changes made to the database are stored in this
area of This area buffers modifications that are made
to the database before they are physically written to the
redo log files. The log_buffer parameter controls this
Shared Pool : This area stores the parsed SQL statements it also stores
data dictionary information shared_pool_size is used to
control the shard pool size
Java Pool : This is used by java programs i.e methods, objects etc.
Streams Pool :
Large Pool : This is used to store things such as shared server operations,
backup and restore tasks, and other miscellaneous things.
large_pool_size is used to control the size.
Keep Pool : Here you could dictate oracle to keep certain objects which
are frequently required like certain tables into memory, so that
faster information is server thereby improving performance.
Recycle Pool : Opposite of Keep Pool, for example if you scan a large table the
data gets pegged into buffer cache, but the data is not requred
again and should be cleared from the Memory immediately
thereby preventing memory hogging. The data in Recycle Pool is
immediately discarded once the operation is completed.
db_recycle_cache_size is the parameter used to control the size.
2K/ 4K/ 8K/ 16K/ 32K Buffer : Oracle does everything reads and writes in block, by default
we have 8K buffer, oracle 10g onwards we could have different
Buffer cache for different block sizes
3. Background Process
Checkpoint (ckpt) : Occurs at regular basis this process updates the datafile headers
and the control file
Database Writer (dbw1) : We could have more than one database writer process, its only
job is to write data from buffer cache to database files and it does so in blocks.
The writer flushes the data at certain intervals, or when buffer gets certain percentage full
Log Writer (lgwr) : Writes log buffer data to redo logs
ARCH (arc1) : We could have more than one archiver process. Their job is to archive/save the
redo logs to a folder
Process Monitor (pmon) : Process monitor as the name suggests this monitors the processes
and manages resources, for example if users session gets killed it does the clean up job
to clean up resources utilized by the session and also does restart of server process if
any is failing
NOTE : pmon process is basicaly used by the dba's to ascertain the running database
on a unix / linux system for example when you run the following command
ps -ef | grep pmon
we get the pmon process information of all the running databases, from that we could
infer the running databases on the system.
Service Monitor (smon) : System monitor process is normally related to recovery when there is
instance failure, also it monitors temporary segments and extents
4. Server Process
Server process are respoinsible for actually taking information (data)
from datafiles and caching it in buffer cache
Server procees have their own memory area called pga (program global area)
and it contains information about sessions, variables, cursors etc
. Foreground Process
Processes which are initiated by clients
Backkground Process are created by oracle and work for oracle database whereas
Foreground Process are the process which work for the clients of the database
example, execute the sql statements of the clients.
Query the v$session with the type='USER' to get the list of clients forground processes.
4. Physical Structure
By Physical structure we mean the files could be seen on the OS level
These are files found at the OS level, and these file make up the data-
base. The various components are datafile, redo log, archive log, control
file, pfile or spfile. Lets look at each of them in detail
Datafile : Database Files, these are binary files, and in these files actual
data is stored
To see datafiles location
select file_name,bytes from dba_data_files
To know more about structure of data storage click here
Redo Log File : These are redo logs, any action performed on the data
contained in the database are logged on to redo log files.
These logged records could then be used to perform
recovery in case of database crash, so that the work
done on the database is not lost.
To see the logfile location
select member from v$logfile
Archive Log File : When database archivelog mode, the redo logs are
copied/archived to provided location.
Control File : These are binary files containing information about the
physical structure of the database like the location of datafiles,
redo log files etc. Normally we have more than one control file,
which are exact copy of each other on different disk just for
avoiding failure in case one of them goes corrupt.
Pfile or SPfile : These are the files that contain startup parameter for the database instance
pfile : its old style file, text file and could be modified using text file
spfile : this is binary file, and could be modified using alter system commands
Other Files consists of trace files, alert log, Networking files,
Trace Files : This files contains detailed information on error generated
Alert Log Files : Events occured on our database are logged in this file,
events like startup ,shutdown, log switches, commands
To get the location of alert log file
show parameter background_dump_dest
Networking Files :
tnsnames.ora, sqlnet.ora,listener.ora are the main files involved in networking.
Listener.ora : This file contains the configuration parameter of the listener on the
tnsnames.ora : To connect to any database server from remote client we require
IP Address, Port Number, SID of the database, and proper user-id/password.
tnsnames.ora file contains the alias (tns name) of the parameter for connecting to remote server
viz , IP Address, Port Number, SID.
(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX )(PORT = 1521))
(SID = <SID_OF_THE_REMOTE_DATABASE)
Above tns-name MYDB is responsible for connecting to the db
Through sqlplus on client you could connect to remote server as follows
4. Logical Structures
Database is logically grouped in what are called as tablespaces,
select name from v$tablespaces;
Tablespace is grouping of datafiles, word logical is used because you
cannot find in on the OS level