Database: It is a system to organize, store and retrieve data.
Relational database: It is that which allows linking tables in a database. This can be manipulate and accessed by SQL (Structured query language), It is to stop data duplication and enforcing data integrity by relating or sharing columns between two tables in a database.
Program Languages: SQL, PL/ SQL, JAVA
The following figure depicts the Logical and physical aspects of Oracle database architecture:
Oracle Instance: Oracle Instance is a memory structure + the processes that support one database. One instance is focused on to one database.
SGA (System Global Area): It is a memory structure. It can be called as partitioned collection or compartments of memory, it consists of for following major area of memory:
- DB Buffer: This is used as a scratch space for SQL, it stores recent user queries, All the changes made to the data files are stored here in DB buffer which are then written to data file by DBWn. At every commit.
- Log Buffer: This is a staging area of Redo logs. This stores all the sql statements fired by a user so that SQL statements could be replayed in case recovery is required. The background process LGWr flushes the Log buffer to write the the SQL statements to the online redo log files so that the Log buffer is not full.
- Shared Pool: If a user fires a SQL query it’s execution plan is stored in shared pool so that the next time if the same query is run by any user oracle does not have to work hard and it just has to execute the same execution plan as stored in the shared pool.
The query to check the processes:
SELECT program FROM v$process ORDER BY program;
The major processes are:
- SMON: Mounts database, Opends DB, collates free spaces in datafiles. Works in DB like house keeper.
- PMON: Monitors user processes and detects problems. It detects idle user sessions and clears them.
- DBWn: Writes changed blocks to the disk.
- LGWn: Writes log buffer to the online redo log files on the disk. It is written before the DBWn writes for that data.
- CKPT: Incrementally instructs DBWn to write dirty buffers to datafiles. It can be considered as periodic points with information, updating the headers of data file and control files letting the database know which batches of transactions have been committed and are complete 100% so that in so that in the event of failure we can redo the complete data.
- ARCn: It archives the online redo log files.
When does DBWn write:
- When a server process of a user has waited or searched too long for a free buffer while reading a buffer into the database buffer cache
- When a segment is dropped
- When checkpoint occurs
- When number of buffers which are modified and committed but unwritten is too large.
- When a tablespace is taken into backup mode
- When a tablespace is placed in offline mode to make it unavailable,
- When a tablespace is put to readonly mode.
When does LGWn write:
- It writes every 3 seconds.
- It writes when the redo log buffer is 1/3 rd full
- It writes when a user commits a transaction
- It write when redo log buffer contains 1MB worth of redo information.
- It writes everytime before DBWr writes on checkpoint events.
Program Global Area: If oracle is in dedicated server mode it has every user has one memory space in the program global area. It has one server process per user. Each user here has his own server process on the oracle server.
It requires a lot of memory if a lot of users connect to the database. So we can configure the server in shared mode which has a connection pools instead of separate PGAs for separate users.
Sizing Memory Structures in Oracle:
Automatic Memory Management: It is used to allocate memory automatically as required to SGA (at it’s component levels) and PGA. Oracle manages memory allocation you just hav to specify memory_target init parameter. (It can also be done manually these have to be defined in server parameter file).
To set memory_target or any initialization parameter to something :
To check a parameter:
Show parameter <parameter_name>
To set it to any other value:
Alter system set memory_target = 820M
Alter system set memory_target = 820M SCOPE= BOTH
The question is what is scope = BOTH.
If you do not specify SCOPE keyword you change will affect the currently running instance and the spfile permanently.
Oracle Storage Structures:
Datafiles: It consists of data blocks. (Tip: Match OS blocks to Oracle Blocks)
Control File: Control file contains the information (pointers) about the data files, redo logs, and matadata. This should be multiplexed.
Online Redolog files: It contains all the changes made to the database in form of SQL statement. These should be multiplexed. It has two types:
Server Parameter file: It contains all your initialization parameter.
Tempfile: Temporary files
Password file: It contain User Passwords.
View is stored SQL code.
USER, ALL, DBA