Database and instance Oracle database server are the concepts that constitute it. (There may be more than one instance on the same server, each of which takes the memory allocated to it.)

Instance = Memory (SGA) + background process. When the instance is started (sql>startup), it takes the allocated memory from the system, starts the background processes, and begins serving users.

Oracle has two structures: physical and logical. The physical structure can be changed without affecting the logical one. (Such as changing the name or location of a datafile)

Database = the location where datafiles, control files, and online redologs are stored.

Oracle tries to perform operations in memory as much as possible. There is a significant cost difference between operations performed on disk and those done in memory. When Oracle starts, it divides the memory it receives into parts for different purposes. Some hold execution plans, others hold data, and share this with users.

Areas that can be changed while DB is open

• Database Buffer Cache
• shared pool
• large pool
• java pool
• streams pool

memory_target and memory_max_target parameters can be configured to enable Oracle to manage memory automatically. Additionally, by setting the above areas manually, we can prevent their size from falling below a certain value.
System Global Area (SGA) / Shared Global Area;

It consists of memory groups that contain data (shared memory structure). Both read and write operations are performed on data in the SGA. This data is shared among users.

– Each instance has its own SGA.

When the instance is started, it allocates RAM from the system, and when shut down, it releases it.

Select * from V$SGA_DYNAMIC_COMPONENTS ;
Show parameter memory ; (11G)
Show parameter sga ; (9i, 10G, 11G)

Program Global Area (PGA);
Unlike the SGA, it is not shared. It stores data and control information for server and background processes. It is created when a server or background process starts. Each process has its own PGA.
(The goal is to keep cache_hit_percent above 90%.)

Show parameter pga ;(9i, 10G, 11G)
alter system set pga_aggregate_target=1G scope=both;

Operations like Order by, group by, and rollup are attempted in the PGA first; if it’s not enough, they are done in the temp tablespace. Each user gets a server process and a dedicated PGA area.

Bind Variable

select * from employees where dept_id=:b1;

dept_id=b1 Different users can assign different values to b1, such as 5 or 7. These values are called bind variables and are stored in the PGA.

In dedicated mode, SGA and PGA are separate; in shared mode, PGA is included in the SGA. Large pool is an optional area and can be configured.

Queries that pass through the PGA in one go are called “optimal executions.” If passed in two steps, it’s “one pass executions.” More than two is called “multipass executions.” The goal is to reduce multipasses (the red ones).

select name from v$fixed_table
where name like '%PGA%' ;

select name from v$fixed_table
where upper(name) like '%ADVICE%' ;

All tables that start with v$ are listed in v$fixed_table.

select * from V$DB_CACHE_ADVICE ;

select * from v$shared_pool_advice ;

select * from v$pga_target_advice ;

Within the SGA, there is a shared area for users. It stores copies of data blocks read from datafiles.
When a user runs a select query and wants to read data, the DBC (Database Buffer Cache) is checked first. If present (cache hit), data is served from DBC.

If not (cache miss), data is read from the datafile and one copy goes to the DBC and the other to the user. To write data to the DBC, there must be available space. If not, Oracle frees up space using a mixed algorithm based on LRU (Least Recently Used). Simply put, frequently used data stays in memory (not FIFO).

select * from employees where employee_id = 1000 ;

In a SQL statement like the one above, not the entire “employees” table is copied into DBC—only the portion required by the “where” clause.
spfile/pfile

The buffer cache appears as db_cache_size.
Note: If the buffer cache wait event is high, the buffer cache size should be increased.

The ratio of logical reads to physical reads should be above 90%. Physical reads should be minimized.

select id, name, physical_reads, physical_writes
from v$buffer_pool_statistics;

Tables with heavy read/write operations can be kept in the buffer cache persistently.

alter table hr.employees storage(buffer_pool keep);

To revert:

alter table hr.employees storage(buffer_pool default);

Changes made to the database (insert, update, delete, create, alter, drop) are logged. These logs are essential for recovery when needed. It uses a circular operation logic. The LGWR background process writes information to redolog files. (In 9i, the default size was 512K, which caused errors in the alert log file; increasing it to 5–10MB depending on the system is advisable.)
log_buffer = redo log buffer

• data dictionary cache
• library cache

select component, current_size/1048576 , min_size/1048576 , max_size/1048576 ,
last_oper_time from v$sga_dynamic_components ;

select * from v$sgainfo ;

ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

ALTER SYSTEM SET sga_max_size=5G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=5G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Oracle processes can be divided into two main groups:

1– Processes that run when an application is executed or when code is run through a tool (Java app / sqlplus or Toad etc.)

2– Processes that Oracle Database runs on its own (server and background processes)

When a user runs an application like sqlplus or Toad, Oracle creates a user process and then a server process to run the code from the user process. In addition, there are background processes belonging to the instance. Background processes handle memory management, asynchronous disk read/write, and other essential tasks.

The structure of processes varies depending on database configuration, operating system, and Oracle database options. For example, in non-archive log mode, the archiver process does not run. In dedicated server mode, one server process is created per user process.

In shared server mode, a server process is not created for each user process. The dispatcher holds session requests in a pool and executes multiple user requests using fewer server processes. Sometimes, the application and the Oracle database reside on the same machine; in this case, Oracle can use the same process to minimize system load.

The server process corresponding to the user process performs one or more of the following:
–parse and execute the SQL statement
– Read data blocks from datafiles and copy them into SGA
– Return the result to the user

• Database writer process (DBWn)
• Log writer process (LGWR)
• Checkpoint process (CKPt)
• System Monitor (SMON)
• Process Monitor (PMON)
• Recover process (RECO)
• Job Queue process
• Archiver Process (ARCn)
• Queue Monitor Process (QMNn)

select name, description from v$bgprocess;
select paddr, name, description
from v$BGprocess
where paddr != '00';

The Database Writer (DBW) process is responsible for writing dirty data from the Database Buffer Cache to disk. (Dirty data refers to data that has been modified and committed.) For most systems, a single DBW process is sufficient, but more can be added if necessary. If your database performs a lot of write operations, adding DBW processes can improve write performance. However, on single-CPU systems, increasing the number of DBW processes is usually not helpful.

When a change is made to a buffer area in the DBC and a commit is issued, it is marked as dirty and added to the LRUW (LRU write) list. This list is maintained in SCN order. This order is aligned with the redo sequence to be written into redologs. When the DBC runs out of space (hits a threshold), the DBW process writes dirty buffers to datafiles based on the LRUW list order.

The LRU algorithm keeps frequently accessed blocks in the buffer cache. Blocks written to disk are likely to be those accessed less frequently.

To see the System Change Number (SCN):

select current_scn from v$database; -- In 10G
select dbms_flashback.get_system_change_number from dual; -- In 9i

LGWR is responsible for writing data from the redo log buffer to the redo log files. It performs write operations in the following cases:

• When a transaction is committed (sometimes before the commit if more redo buffer is needed)
• When 1/3 of the redo log buffer is full
• Before DBW writes to disk
• Every 3 seconds

When DBW writes dirty buffers from the DBC to disk, the checkpoint process writes information about the modified blocks to control files and the headers of datafiles. The purpose of a checkpoint is to determine the point in the redolog files where instance recovery should begin.

This is called the Checkpoint Position.
It ensures that modified data blocks are regularly written from memory to disk, reducing the risk of data loss if an instance or database failure occurs.
It also minimizes the time needed for instance recovery.
During shutdown, it ensures that all committed data is written to disk.

In summary, checkpoint information is written into control files by the checkpoint process. Oracle reads from control files at startup. Important checkpoint contents include: SCN (System Change Number), the position in the redo log file where recovery starts, and log metadata.
During instance startup, if needed, it performs recovery by aligning the SCN in datafiles and control files, then opens the database. It rolls forward committed changes and rolls back uncommitted ones.

It cleans up unused temporary segments. Other processes can trigger SMON if necessary.

If a user process terminates unexpectedly, PMON handles recovery for that process. It releases the buffer cache and other resources assigned to the user. For example, if there are active transactions and the session is aborted, locks are released and the process ID is removed.

It releases locks on users’ uncommitted rows (such as when the DB shuts down unexpectedly).
PMON periodically checks dispatcher and server processes and restarts them if they’ve crashed (but not if they were shut down intentionally).
It registers instance and dispatcher info with the listener.
Like SMON, PMON can also be triggered by other processes when needed.

In multi-instance environments, RECO handles failed distributed transactions by connecting to the other database and resolving the issue automatically.

If the DB is in archive log mode, ARCn runs and archives redo logs to a specified path after each log switch. If standby servers exist, it also transfers logs to them. (More in Data Guard topic)

Other Processes
MMON (Manageability Monitor Process): Handles alerts, statistics gathering, and management tasks.
MMNL (Lightweight Manageability Monitor Process): Performs lightweight manageability tasks like session history tracking.
MMAN (Memory Manager Process): Automatically handles memory distribution between components based on need.
RBAL (Rebalance Process): Balances disks in ASM environments.
CJQ0 (Job Queue Coordinator): Executes scheduled jobs (like PL/SQL procedures).
QMNC (AQ Coordinator Process): Monitors advanced queues and supports Oracle Streams.

As mentioned before, when a query is received, Oracle checks if the block is in the buffer cache. If yes, it returns it to the user; if not, it reads from the datafile, loads it into the buffer cache, and then returns it.

Buffer states can be:
pinned: Multiple users want to write to the same block; users wait for the lock to release.
clean: Not in use and subject to replacement if not accessed again.
free or unused: Initial state when the instance has just started.
dirty: Block has been modified and committed, waiting to be written to disk.

A phone line between two people means a connection exists; when one calls and the other picks up, a session begins. (A server process starts for each client process.)

1– Instance is started, memory is allocated from the OS, background processes are launched, and the system becomes ready for user access.
2– The user starts an application or attempts to connect via an Oracle tool.
3– The server uses the listener service to make the instance accessible over the network, receives the connection request, and creates a dedicated server process.
4– The user executes a DML or SQL command, e.g., updates an employee record.
5– The server process checks the shared pool to see if the same SQL was previously executed. If yes, it verifies user permissions and reuses the plan; if not, it parses the SQL.
6– If the data block exists in buffer cache, it is read from there; otherwise, it is retrieved from datafiles.
7– The server process modifies data in the SGA.
LGWR writes the changes to redologs.
Later, DBW writes changes permanently to datafiles.
8– If the transaction succeeds, the server process returns success via the network; otherwise, it returns an error.
9– Background processes monitor and assist throughout the entire flow (e.g., ARCn archives logs after a log switch, manages user transactions, prevents conflicts, etc.)

Control Files: Store identity information about the database. Each instance has at least one control file. It is recommended to have at least two, placed on different physical disks.
Control files contain datafile and redolog names/paths, current log sequence numbers, backup set details, and the “system change number” (SCN). These are binary files and cannot be modified manually. See “Control File Management” for more details.

DataFiles: Store user objects. A datafile is the physical equivalent of a tablespace. One datafile belongs to one tablespace. One tablespace can have many datafiles (except Big File Tablespace). See Tablespace Management for more.

Online Redo Log Files: Store transaction data. Default count is 3, minimum is 2. Can be increased as needed. It is highly recommended to place members on different disks. They operate in a circular manner.

Parameter File: Also called startup file. It is the first file Oracle reads on startup to determine memory allocation and control file location. It is binary but can be copied to a text file using “create spfile from pfile”.

BackUp Files: Export, DBF, and RMAN backup files. There are three backup types in Oracle:
1– Export (logical backup)
2– Cold Backup (user-managed; database is shut down and files are copied)
3– RMAN backup (primary tool)

Archive Redo Log Files: In archive log mode, Oracle copies online redo logs to archive logs. These allow point-in-time recovery.

Password File: Allows access to the DB even when it is shut down, using an external file for authentication. Can be recreated if needed.

Alert Log: A file that logs everything happening in the database, including errors. DBAs should monitor this regularly.

Trace Files: Contain more detailed error information. If SQL tracing is enabled, trace files include this data too.

Schema = User (object catalog) (equivalent of database in MSSQL and MySQL)

select Owner, table_name, tablespace_name from all_tables where owner = 'SCOTT'

select Owner, index_name, tablespace_name from all_indexes where owner = 'SCOTT'

Database = Consists of tablespaces
Tablespace = A logical structure that contains segments
Segment = Made of extents
Data segments store user tables/indexes and form tablespaces. (e.g., index segment)
Undo segments are used for read consistency and form the undo tablespace.
Extent = Made of blocks
Datablock = The smallest unit in Oracle where data is physically stored
[bash]$dumpe2fs /dev/sda3 | grep -i ‘Block size’
Block size: 4096[/bash]

Oracle databases are logically divided into tablespaces:

• system
• sysaux
• user tbs (big/small)
• undo tbs
• temporary

System Tablespace: Automatically created with the DB. Mandatory for all DBs. Contains data dictionary and core system structures. Cannot be taken offline.

SysAux Tablespace: Introduced in 10G to support the system tablespace. Stores AWR statistics.