Database Technologies‎ > ‎Oracle‎ > ‎

Database and instance


  1. Oracle Database and Instance
  2. Memory structures
  3. Background Process
  4. Server Process
  5. Physical Structures
  6. Logical Structures
               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
                 SQLPLUS COMMANDS
                 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
                                  controling size.
    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
                                  memory area
    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
    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
    Processes which are initiated by clients

    Foreground process,
    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
                                          executed etc,
            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
    database server
     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.
    MYDB =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX )(PORT = 1521))
        (CONNECT_DATA =
    Above tns-name MYDB is responsible for connecting to the db
    Through sqlplus on client you could connect to remote server as follows
    sqlplus <userid>/<password>@MYDB
    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