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


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

    1. .

    2. 2. Memory structures

    1. Oracle Instance is Memory Structure + the Background Processes

    2. Buffer Cache, Log Buffer and Shared Pool are essential components other

    3. are optional. Now we would have a look at each component

    4. Buffer Cache : When data is read from the database it gets cached

    5. in this area. db_cache_size is the parameter for

    6. controling size.

    7. Log Buffer : The changes made to the database are stored in this

    8. area of This area buffers modifications that are made

    9. to the database before they are physically written to the

    10. redo log files. The log_buffer parameter controls this

    11. memory area

    12. Shared Pool : This area stores the parsed SQL statements it also stores

    13. data dictionary information shared_pool_size is used to

    14. control the shard pool size

    15. Java Pool : This is used by java programs i.e methods, objects etc.

    16. Streams Pool :

    17. Large Pool : This is used to store things such as shared server operations,

    18. backup and restore tasks, and other miscellaneous things.

    19. large_pool_size is used to control the size.

    20. Keep Pool : Here you could dictate oracle to keep certain objects which

    21. are frequently required like certain tables into memory, so that

    22. faster information is server thereby improving performance.

    23. Recycle Pool : Opposite of Keep Pool, for example if you scan a large table the

    24. data gets pegged into buffer cache, but the data is not requred

    25. again and should be cleared from the Memory immediately

    26. thereby preventing memory hogging. The data in Recycle Pool is

    27. immediately discarded once the operation is completed.

    28. db_recycle_cache_size is the parameter used to control the size.

    29. 2K/ 4K/ 8K/ 16K/ 32K Buffer : Oracle does everything reads and writes in block, by default

    30. we have 8K buffer, oracle 10g onwards we could have different

    31. Buffer cache for different block sizes

    1. .

    2. 3. Background Process

      1. Checkpoint (ckpt) : Occurs at regular basis this process updates the datafile headers

      2. and the control file

      3. Database Writer (dbw1) : We could have more than one database writer process, its only

      4. job is to write data from buffer cache to database files and it does so in blocks.

      5. The writer flushes the data at certain intervals, or when buffer gets certain percentage full

      6. Log Writer (lgwr) : Writes log buffer data to redo logs

      7. ARCH (arc1) : We could have more than one archiver process. Their job is to archive/save the

      8. redo logs to a folder

      9. Process Monitor (pmon) : Process monitor as the name suggests this monitors the processes

      10. and manages resources, for example if users session gets killed it does the clean up job

      11. to clean up resources utilized by the session and also does restart of server process if

      12. any is failing

      13. NOTE : pmon process is basicaly used by the dba's to ascertain the running database

      14. on a unix / linux system for example when you run the following command

      15. ps -ef | grep pmon

      16. we get the pmon process information of all the running databases, from that we could

      17. infer the running databases on the system.

      18. Service Monitor (smon) : System monitor process is normally related to recovery when there is

      19. instance failure, also it monitors temporary segments and extents

    1. 4. Server Process

    2. Server process are respoinsible for actually taking information (data)

    3. from datafiles and caching it in buffer cache

    4. Server procees have their own memory area called pga (program global area)

    5. and it contains information about sessions, variables, cursors etc

      1. . Foreground Process

      2. Processes which are initiated by clients

      3. Foreground process,

      4. Backkground Process are created by oracle and work for oracle database whereas

      5. Foreground Process are the process which work for the clients of the database

      6. example, execute the sql statements of the clients.

      7. Query the v$session with the type='USER' to get the list of clients forground processes.

    1. .

    2. 4. Physical Structure

    3. By Physical structure we mean the files could be seen on the OS level

    4. These are files found at the OS level, and these file make up the data-

    5. base. The various components are datafile, redo log, archive log, control

    6. file, pfile or spfile. Lets look at each of them in detail

    7. Datafile : Database Files, these are binary files, and in these files actual

    8. data is stored

    9. To see datafiles location

    10. select file_name,bytes from dba_data_files

    11. To know more about structure of data storage click here

    12. Redo Log File : These are redo logs, any action performed on the data

    13. contained in the database are logged on to redo log files.

    14. These logged records could then be used to perform

    15. recovery in case of database crash, so that the work

    16. done on the database is not lost.

    17. To see the logfile location

    18. select member from v$logfile

    19. Archive Log File : When database archivelog mode, the redo logs are

    20. copied/archived to provided location.

    21. Control File : These are binary files containing information about the

    22. physical structure of the database like the location of datafiles,

    23. redo log files etc. Normally we have more than one control file,

    24. which are exact copy of each other on different disk just for

    25. avoiding failure in case one of them goes corrupt.

    26. Pfile or SPfile : These are the files that contain startup parameter for the database instance

    27. pfile : its old style file, text file and could be modified using text file

    28. spfile : this is binary file, and could be modified using alter system commands

    29. Other Files consists of trace files, alert log, Networking files,

    30. Trace Files : This files contains detailed information on error generated

    31. Alert Log Files : Events occured on our database are logged in this file,

    32. events like startup ,shutdown, log switches, commands

    33. executed etc,

    34. To get the location of alert log file

    35. show parameter background_dump_dest

    36. Networking Files :

    37. tnsnames.ora, sqlnet.ora,listener.ora are the main files involved in networking.

    38. Listener.ora : This file contains the configuration parameter of the listener on the

    39. database server

    40. tnsnames.ora : To connect to any database server from remote client we require

    41. IP Address, Port Number, SID of the database, and proper user-id/password.

    42. tnsnames.ora file contains the alias (tns name) of the parameter for connecting to remote server

    43. viz , IP Address, Port Number, SID.

    44. Example

    45. MYDB =

    46. (DESCRIPTION =

    47. (ADDRESS_LIST =

    48. (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX )(PORT = 1521))

    49. )

    50. (CONNECT_DATA =


    52. )

    53. )

    54. Above tns-name MYDB is responsible for connecting to the db

    55. Through sqlplus on client you could connect to remote server as follows

    56. sqlplus <userid>/<password>@MYDB

    1. 4. Logical Structures

    2. Database is logically grouped in what are called as tablespaces,

    3. select name from v$tablespaces;

    4. Tablespace is grouping of datafiles, word logical is used because you

    5. cannot find in on the OS level