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.
