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

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

1. .
7. 2. Memory structures

4. Oracle Instance is Memory Structure + the Background Processes
6. Buffer Cache, Log Buffer and Shared Pool are essential components other
7. are optional. Now we would have a look at each component
11. Buffer Cache : When data is read from the database it gets cached
12. in this area. db_cache_size is the parameter for
13. controling size.
15. Log Buffer : The changes made to the database are stored in this
16. area of This area buffers modifications that are made
17. to the database before they are physically written to the
18. redo log files. The log_buffer parameter controls this
19. memory area
21. Shared Pool : This area stores the parsed SQL statements it also stores
22. data dictionary information shared_pool_size is used to
23. control the shard pool size
25. Java Pool : This is used by java programs i.e methods, objects etc.
27. Streams Pool :
29. Large Pool : This is used to store things such as shared server operations,
30. backup and restore tasks, and other miscellaneous things.
31. large_pool_size is used to control the size.
33. Keep Pool : Here you could dictate oracle to keep certain objects which
34. are frequently required like certain tables into memory, so that
35. faster information is server thereby improving performance.
37. Recycle Pool : Opposite of Keep Pool, for example if you scan a large table the
38. data gets pegged into buffer cache, but the data is not requred
39. again and should be cleared from the Memory immediately
40. thereby preventing memory hogging. The data in Recycle Pool is
41. immediately discarded once the operation is completed.
42. db_recycle_cache_size is the parameter used to control the size.
44. 2K/ 4K/ 8K/ 16K/ 32K Buffer : Oracle does everything reads and writes in block, by default
45. we have 8K buffer, oracle 10g onwards we could have different
46. Buffer cache for different block sizes

8. .
13. 3. Background Process
17. 1. Checkpoint (ckpt) : Occurs at regular basis this process updates the datafile headers
  2. and the control file
  5. Database Writer (dbw1) : We could have more than one database writer process, its only
  6. job is to write data from buffer cache to database files and it does so in blocks.
  7. The writer flushes the data at certain intervals, or when buffer gets certain percentage full
  10. Log Writer (lgwr) : Writes log buffer data to redo logs
  13. ARCH (arc1) : We could have more than one archiver process. Their job is to archive/save the
  14. redo logs to a folder
  17. Process Monitor (pmon) : Process monitor as the name suggests this monitors the processes
  18. and manages resources, for example if users session gets killed it does the clean up job
  19. to clean up resources utilized by the session and also does restart of server process if
  20. any is failing
  22. NOTE : pmon process is basicaly used by the dba's to ascertain the running database
  23. on a unix / linux system for example when you run the following command
  24. ps -ef | grep pmon
  26. we get the pmon process information of all the running databases, from that we could
  27. infer the running databases on the system.
  30. Service Monitor (smon) : System monitor process is normally related to recovery when there is
  31. instance failure, also it monitors temporary segments and extents

8. 4. Server Process
10. Server process are respoinsible for actually taking information (data)
11. from datafiles and caching it in buffer cache
12. Server procees have their own memory area called pga (program global area)
13. and it contains information about sessions, variables, cursors etc
14. 2. . Foreground Process
  4. Processes which are initiated by clients
  5. Foreground process,
  7. Backkground Process are created by oracle and work for oracle database whereas
  8. Foreground Process are the process which work for the clients of the database
  9. example, execute the sql statements of the clients.
  11. Query the v$session with the type='USER' to get the list of clients forground processes.

3. .
10. 4. Physical Structure
13. By Physical structure we mean the files could be seen on the OS level
14. These are files found at the OS level, and these file make up the data-
15. base. The various components are datafile, redo log, archive log, control
16. file, pfile or spfile. Lets look at each of them in detail
18. Datafile : Database Files, these are binary files, and in these files actual
19. data is stored
21. To see datafiles location
22. select file_name,bytes from dba_data_files
24. To know more about structure of data storage click here
26. Redo Log File : These are redo logs, any action performed on the data
27. contained in the database are logged on to redo log files.
28. These logged records could then be used to perform
29. recovery in case of database crash, so that the work
30. done on the database is not lost.
32. To see the logfile location
33. select member from v$logfile
35. Archive Log File : When database archivelog mode, the redo logs are
36. copied/archived to provided location.
39. Control File : These are binary files containing information about the
40. physical structure of the database like the location of datafiles,
41. redo log files etc. Normally we have more than one control file,
42. which are exact copy of each other on different disk just for
43. avoiding failure in case one of them goes corrupt.
46. Pfile or SPfile : These are the files that contain startup parameter for the database instance
48. pfile : its old style file, text file and could be modified using text file
49. spfile : this is binary file, and could be modified using alter system commands
52. Other Files consists of trace files, alert log, Networking files,
54. Trace Files : This files contains detailed information on error generated
56. Alert Log Files : Events occured on our database are logged in this file,
57. events like startup ,shutdown, log switches, commands
58. executed etc,
60. To get the location of alert log file
61. show parameter background_dump_dest
63. Networking Files :
66. tnsnames.ora, sqlnet.ora,listener.ora are the main files involved in networking.
68. Listener.ora : This file contains the configuration parameter of the listener on the
69. database server
71. tnsnames.ora : To connect to any database server from remote client we require
72. IP Address, Port Number, SID of the database, and proper user-id/password.
74. tnsnames.ora file contains the alias (tns name) of the parameter for connecting to remote server
75. viz , IP Address, Port Number, SID.
77. Example
79. MYDB =
80. (DESCRIPTION =
81. (ADDRESS_LIST =
82. (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX )(PORT = 1521))
83. )
84. (CONNECT_DATA =
85. (SID = <SID_OF_THE_REMOTE_DATABASE)
86. )
87. )
89. Above tns-name MYDB is responsible for connecting to the db
91. Through sqlplus on client you could connect to remote server as follows
93. sqlplus <userid>/<password>@MYDB

7. 4. Logical Structures
11. Database is logically grouped in what are called as tablespaces,
13. select name from v$tablespaces;
15. Tablespace is grouping of datafiles, word logical is used because you
16. cannot find in on the OS level