Oracle Dabase Basics

SCN(system chain number):

SCN (System Change Number) is a primary mechanism to maintain data consistency in Oracle database. SCN is used primarily in the following areas, of course, this is not a complete list:

1.  Every redo record has an SCN version of the redo record in the redo header (and redo records can have non-unique SCN). Given redo records from two threads (as in the case of RAC), Recovery will order them in SCN order, essentially maintaining a strict sequential order. Every redo record has multiple change vectors too.

2.  Every data block also has block SCN (aka block version). In addition to that, a change vector in a redo record also has expected block SCN. This means that a change vector can be applied to one and only version of the block. Code checks if the target SCN in a change vector is matching with the block SCN before applying the redo record. If there is a mismatch, corruption errors are thrown.

3. Read consistency also uses SCN. Every query has query environment which includes an SCN at the start of the query. A session can see the transactional changes only if that transaction commit SCN is lower then the query environment SCN.

4. Commit. Every commit will generate SCN, aka commit SCN, that marks a transaction boundary. Group commits are possible too.

Achive and Noarchive:

Placing the database in archivelog mode prevents redo logs from  being overwritten until they have been archived. In  archive the data in the redo logs are wrote to the archive log files by the archiver(ARCn) before they are being overwritten.

Shared Connection and Dedicated Connection: 
Shared Connection: 

In Shared Connecion there is many-to-one ratio between server processes and user processes. Each server process uses system resources, including CPU cycles and memory and it shared among the user processes.     

Dedicated Connection:

In a Dedicated Connection there is one-to-one ratio between server processes and user processes. Each server process uses system resources, including CPU cycles and memory.

Server Processes and Background Processes:

Server Process:

Server Process connects to the Oracle instance and is started when a user establishes a session.

Background Processes:

Background Processes are started when an Oracle instance is started.

Block:

Database objects, such as tables and indexes, are stored as segments in tablespaces. Each segments contains one or more extents. An extent consists of contiguous data blocks, which means that each extent can exitst only in one data file. Data blocks are the smallest unit of I/O in the database. The size of the data block  can be set at the time of the creation of the database. The default size is 8KB.

Locally Managed and Dictionary Managed Tablespaces:

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

Instance:

Each running Oracle database is associated with an Oracle instance. When a database is started on a database server, the Oracle software allocates a shared memory area called the System Global Area (SGA) and starts several Oracle background processes. This combination of the SGA and the Oracle processes is called an Oracle instance.

Parameter File and Password File:

Parameter File(p-file):

It is used to define how the istance is configured when it starts up.

Password File:

It allows the users to connect remotely to the database and perform administrative tasks.

Checkpoint:

Every three seconds (or more frequently), the CKPT  process stores data in the control file to document which modified data blocks DBWn has written from the SGA to disk. This is called a Checkpoint. The purpose of a checkpoint is to identify that place in the online redo log file where instance recovery is to begin(checkpoint position).

PMON and SMON:

PMON (Process Monitor):
Performs user and server processes cleanup when users process fails. Registers service with the listeners.
SMON ( System Monitor):

Performs crash recovery when the instance is started following a failure. It also clean Temp Segments and Collates Free Space.

Large Pool:

Large Pool is an optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes.

Phases of Statement Execution(Shortly):

1. Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct. 

2. Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.

3. Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases.

Deadlocks:

A deadlock is a special example of a lock conflict. Deadlocks arise when two or more sessions wait for data locked by each other. Because each is waiting for the other, neither can complete their transaction to resolve the conflict.   

         Notes :  LOCKS:  Before the database allows a session to modify data, the  session must first lock the data that is being modified. A lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released.

DDL and DML:

DDL (Data Definition Language): 
It is used to sets up, changes, and removes data structures from table. Collectively known as DDL (Data Definition Language). It consists of CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT statements.

DML (Data Manipulation Language):

It is used to retrieves data from the database, enters new rows, changes existing rows, and removes unwanted rows from tables in the database. Collectively known as DML (Data Manipulation Language). It consists of SELECT, INSERT, UPDATE, DELETE, MERGE statements.

      

Library Cache:

The Library Cache is a piece of memory within the SGA that Oracle uses in order to store SQL statement. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. If for example I do a insert into city (name, abbr) values ('Geneva', 'GE');. the Library Cache checks if there is a table named city having the columns name and abbr. As an additional task, the Library Cache also checks if the user's privileges are sufficient to execute the statement.

In a similar way, the Library Cache also caches PL/SQL Statements and Objects. The Library Cache was introduced because parsing (and validating and checking privileges) is expensive in terms of CPU cycles.

Data File and Control File:

Data Files:

Data files is a part of physical database Structure. It contains the user or application data of the database.

Controlfile Contents:

It contains data about the database itself ( that is, physical database structure information). These files are critical to the dataset. Without them, data files can’t be opened to access the data within the database. It is a binary file and it contains the name, startup, shutdown, and also many other vital information about the database.

Archiver(ARCn ):

Archiver copies redo log files to the archival storage when a log switch occurs.

User Process and Server Pocess:

User Process:

User Process is started at the time a database user requests a connection to the Oracle server.

Server Process: 

Server Process connects to the Oracle instance and is started when a user establishes a session.