Buffer cache

Is an area of memory that stores data blocks. It improves performance when you run SQL statements. SQL statements are cached as much as possible. No constant IO activities going on.

Note: Everything is stored in Oracle

Log Buffer

Is an area of memory that saves changes to database.

Shared Pool

Is a place parts of SQL*Plus get's stored. It also stored data dictionary info - metadata infn about d database is stored. It is some place that tells us what tablespaces we have, what views we have, what indexes, what tables we have etc.

SGA

• Java Pool - Stored Java commands
• Streams - You get's this when you have multiple servers hosting a db
• Large Pool - stores info abt backup activities and restore activities and shared server information.
• Keep Pool - Stores Things you want to keep in the memory e.g look up tables
• Recycle Pool - This is the opposite of the Keep Pool. E.g If we need to get all the rows in a table and we dont want to clog up our main memory or main buffer, we can store it in the Recycle pool. Storing things here saves us a lot of space in the buffer cache which is the main memory area.

About Blocks

When Oracle is passing information around, it is always in block sizes. To find out information about your database block size;

• SQL> show parameter db_block_size

When you set a default block size in your db, you really can't change it.

Note: the 2k, 4k, 16k, 32k buffers are already accounted for in the Buffer cache. So when you are assiging a memory size to a db object, you are doing this in blocks. In addition to our defaut block size, we can have upto 4 additional block sizes (i.e the k buffers - 2k, 4k, 16k, 32k). So u can just easily assign a 2k buffer or 4k buffer to a db object without necessarily directly allocating the memory space on the Buffer cache.

• DB Writer (DBW) - DB Writer Process writes from the buffer cache to the db file. It does all these in blocks not records. It doesn't continually writes this data. It does it at intervals.
• CheckPoint - occurs whenever the db writer flushes the buffer cache in to disk. Occurs whenever redo logs becomes full. It updates the data file headers.
• Log Writer - writes log buffer data to redo logs. We need to always have at least 2 redo logs. If u have archiving turned on, before one of the redo logs is overwritten, it writes the data into an archive. If you are in a testing environment you dont need archiving. The major purpose of redo logs is to perform recovery.
• Archiving - You can have more than one. This is not a required process

PMon

Process monitor. This is used by any user process.

SMON - System Monitor

Perform instance recovery especially when you don't shutdown the db properly

Program Global Area (PGA)

Check ur process with ps -ef | grep oracle

A user process is a process that connects to the server process e.g SQLPLUS is a user process. All server processes contains a memory area called the PGA. It contains info about sessions, variable etc.

• Physical Structures - real files you can see on the OS level e.g db files, redo logs, log files. ends with .dbf

To see the system db files

• SQL> select file_name, bytes/1024/1024 "MB" from dba_data_files order by 1

To see when a file is created

• SQL>select name, creation_time from v$datafile

To query ur redo log files

• SQL> select member, status, group# from v$logfile

To query infn about our log group

• SQL> select group#, sequence#, bytes, archived, status from v$log;

To add more log files to ur existing group log files

SQL> alter database

to group 1

To clear the log file after creation

SQL> alter system switch logfile; // this will create an archive log file

To add a completely new redo log group

spfile - System parameter file

When you start up a db, you will always have an spfile or init file

To know where ur spfile is

• SQL> show parameter spfile

If oracle cannot file any of the files in the category of spfile, then your instance won't startup.

What Oracle does on startup is that it first looks up for SPFILE<ORACLESID>.ORA, if doesn't exist, it looks for spfile.ora, if that doesn't exist, it looks for init.ora and if any of these doesn't exist, then your Oracle won't startup. To locate these files, looks in the dbs directory.

Alert log / Trace Files

Tells about major event in the db. Trace Files handle session logs. You can find info about alert log files in $ORACLE_HOME/admin/<ORACLE_SID/bdump. You might want to backup this file on a 24 hour basis.

To know where this file is

SQL> show parameter background

Control Files

Are files that are read by the instance when the db is getting into the mount mode. Contains information about physical structure of the db. ie. where the redo log files are, db file files are;

To know where your control files are;

SQL> select name from v$controlfile

Control files are most of the time multiplexed

Backup Files

If you have lots of dbfiles, you might want to back them up here.

Logical Storage Structures

Talks about how is our data logically organized in the tablespace. i.e structures that can only be queried in the database. The largest logical storage structure is called the TABLESPACE

System Tablespace

Used for metadata information. Stores information about the structure of your whole database.

Sysaux Tablespace

Is used for auxillary purposes. it supports system tablespace

Note: make sure users don't store data in system tablespace. Know that when working with tablespaces, what you are doing is that you are logically arranging your storage structures. You can have as many Tablespaces as you want.

Undo Table

Stores the before image information about your data. e.g performing a rollback

Temp Tablespace

Used for sorting data should in case you dont have enough memory to do the sort.

Note: every single object (table, user, views, index, segment etc) will be contained inside a tablespace (only inside a single tablespace)

Segment

Segment is the next smallest logical unit. E.g you can have Person table segment, Emp Table segment or Personnel Index Segment etc.

Types of Segments

• Table segment
• Index segment
• Temporary Segment

Note: You can scatter you segments among different tablespaces depending on how you want to organize your storage structure.

So when you create a table, it will be automatically given a chunk of space called a Segment. Once that space is filled up in that segment, Oracle is going to allocate another space in that area called an EXTENT. Once the extent is filled up, Oracle will create more EXTENT to cater for your storage space.

An extent is therefore defined as several contiguous database blocks.

Note: You can specify a max number of extents if you want to.

Database Blocks

Is the smallest logical unit. it doesn't map exactly into an OS block. It is a multiple of the OS block size.