DB Architecture

Memory structures

– great LINK
PGA (Program global area)
SGA (System global area)
sizing those two
AMM (Automatic memory management) – is a system in Oracle that allows you to give only one value memory_target and the Oracle instance will tune the PGA and SGA memories for you. If AMM is not turned on we need to specify PGA and SGA manually.

To check the size of memories type in SQL*PLUS:

SHOW PARAMETER TARGET

To change the size of memories:

ALTER SYSTEM SET MEMORY_MAX_TARGET = integer [K | M | G] SCOPE={BOTH|SPFILE};
ALTER SYSTEM SET MEMORY_TARGET = integer [K | M | G] SCOPE={BOTH|SPFILE};
ALTER SYSTEM SET SGA_TARGET = integer [K | M | G];
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = integer [K | M | G];

Process structures

There is a great LINK from the Oracle documentation.
SMON – System Monitor Process: Mounts and opens the DB. Collates free space in datafiles.
PMON – Process Monitor: Monitors user processes and detects problems
DBWn – Database Writer: Writes modified blocks from the database buffer cache to the data files
LGWR – Log Writer: writes log buffer (redo entries) to redo log files on disk
CKPT – Checkpoint Process: Signals DBWn at checkpoints and updates all the data files and control files of the database to indicate the most recent checkpoint

and the other main processes that are not mandatory like previous 5.

To check which processes are running you can type into SQL*PLUS

select program from v$process order by program;

Storage structures

Logical (invisible to OS) and Physical (visible to OS)
Tablespaces, data files, control files and online redo log files.

Tablespace is a logical container for datafiles.

Datafiles: consists of data blocks

SPFILE: contains parameters for starting a DB. There is also a PFILE. We should try to use SPFILE for more reasons (biggest I can think of is ability to be backedup with RMAN).

Checking whether we are using PFILE or SPFILE

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Creating SPFILE from PFILE

SQL> CREATE SPFILE FROM PFILE;
or if you do not want to use default location
SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Changing SPFILE parameters example

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

Here is a list of basic DB parameters LINK

Control File: Contains pointers to parts of DB – datafiles, redo logs, meta data

Online Redo Log Files: Stores every SQL change applied to DB. There are two types: online and archive