What is the function
of Redo Log ?
The Primary function of the redo log is to record all changes made to data.
What is the use of
Redo Log Information ?
The Information in a redo log file is used only to recover the database from a
system or media failure prevents database data from being written to a
database’s data files.
What does a Control
file Contain ?
A Control file records the physical structure of the database. It contains the
following information.
Database Name
Names and locations of a database’s files and redolog files.
Time stamp of database creation.
What is the use of
Control File ?
When an instance of an ORACLE database is started, its control file is used to
identify the database and redo log files that must be opened for database
operation to proceed. It is also used in database recovery.
What is a Data
Dictionary ?
The data dictionary of an ORACLE database is a set of tables and views that are
used as a read-only reference about the database. It stores information about
both the logical and physical structure of the database, the valid users of an
ORACLE database, integrity constraints defined for tables in the database and
space allocated for a schema object and how much of it is being used.
What is an Integrity
Constrains ?
An integrity constraint is a declarative way to define a business rule for a
column of a table.
Can an Integrity
Constraint be enforced on a table if some existing table data does not satisfy
the constraint ?
No.
Describe the different
type of Integrity Constraints supported by ORACLE ?
NOT NULL Constraint - Disallows NULLs in a table’s column.
UNIQUE Constraint – Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint
- Disallows duplicate values and NULLs in a
column or set of columns.
FOREIGN KEY Constrain
- Require each value in a column or set of
columns match a value in a related table’s UNIQUE or PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical
expression of the constraint.
What is difference
between UNIQUE constraint and PRIMARY KEY constraint ?
A column defined as UNIQUE can contain NULLs while a column defined as PRIMARY
KEY can’t contain Nulls.
Describe Referential
Integrity ?
A rule defined on a
column (or set of columns) in one table that allows the insert or update of a
row only if the value for the column or set of columns (the dependent value)
matches a value in a column of a related table (the referenced value). It also
specifies the type of data manipulation allowed on referenced data and the
action to be performed on dependent data as a result of any action on
referenced data.
What are the
Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict – A referential integrity rule that disallows the
update or deletion of referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent
rows are deleted.
What is
self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called
self-referential integrity constraint.
What are the
Limitations of a CHECK Constraint ?
The condition must be a Boolean expression evaluated using the values in the
row being inserted or updated and can’t contain subqueries, sequence, the
SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or
ROWNUM.
What is the maximum
number of CHECK constraints that can be defined on a column ?
No Limit.
What constitute an ORACLE Instance ?
SGA and ORACLE background processes constitute an ORACLE instance. (or)
Combination of memory
structure and
background process.
What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that
contains data and
control information
for one ORACLE instance.
What are the components of SGA ?
Database buffers, Redo Log Buffer the
Shared Pool and Cursors.
What do Database Buffers contain ?
Database buffers store the most recently used blocks of database data. It can
also contain modified
data that has not yet
been permanently written to disk.
What do Redo Log Buffers contain ?
Redo Log Buffer stores redo entries a log of changes made to the database.
What is Shared Pool ?
Shared Pool is a portion of the SGA that contains shared memory constructs such
as shared SQL areas.
What is Shared SQL Area ?
A Shared SQL area is required to process every unique SQL statement submitted
to a database and
contains information
such as the parse tree and execution plan for the corresponding statement.
What is Cursor ?
A Cursor is a handle ( a name or pointer) for the memory associated with a
specific statement.
What is PGA ?
Program Global Area (PGA) is a memory buffer that contains data and control
information for a server
process.
What is User Process ?
A user process is created and maintained to execute the software code of an
application program. It
is a shadow process
created automatically to facilitate communication between the user and the
server process.
What is Server Process ?
Server Process handle requests from connected user process. A server process is
in charge of
communicating with the
user process and interacting with ORACLE carry out requests of the associated
user process.
What are the two types of Server Configurations ?
Dedicated Server Configuration and Multi-threaded Server Configuration.
What is Dedicated Server Configuration ?
In a Dedicated Server Configuration a Server Process handles requests for a
Single User Process.
What is a Multi-threaded Server Configuration ?
In a Multi-threaded Server Configuration many user processes share a group of
server process.
What is a Parallel Server option in ORACLE ?
A configuration for loosely coupled systems
where multiple instance share a single physical database
is called Parallel
Server.
Name the ORACLE Background Process ?
DBWR – Database Writer.
LGWR – Log Writer
CKPT – Check Point
SMON – System Monitor
PMON – Process Monitor
ARCH – Archiver
RECO – Recover
Dnnn – Dispatcher, and
LCKn – Lock
Snnn – Server.
What Does DBWR do ?
Database writer writes modified blocks from
the database buffer cache to the data files.
When Does DBWR write to the database ?
DBWR writes when more data needs to be read
into the SGA and too few database buffers are free. The
least recently used
data is written to the data files first. DBWR also writes when CheckPoint
occurs.
What does LGWR do ?
Log Writer (LGWR) writes redo log entries generated in the redo log buffer of
the SGA to on-line
Redo Log File.
When does LGWR write to the database?
LGWR writes redo log entries into an
on-line redo log file when transactions commit and the log
buffer files are full.
What is the function of checkpoint CKPT)?
The Checkpoint (CKPT) process is
responsible for signaling DBWR at checkpoints and updating all the
data files and control
files of the database.
What are the functions of SMON ?
System Monitor (SMON) performs instance
recovery at instance start-up. In a multiple instance system
(one that uses the
Parallel Server), SMON of one instance can also perform instance recovery for
other instance that
have failed SMON also cleans up temporary segments that are no longer in use
and
recovers dead
transactions skipped during crash and instance recovery because of file-read or
off-line errors. These
transactions are eventually recovered by SMON when the tablespace or file is
brought back on-line
SMON also coalesces free extents within the database to make free space
contiguous and easier
to allocate.
What are functions of PMON ?
Process Monitor (PMON) performs process recovery when a user process fails PMON
is responsible for
cleaning up the cache
and Freeing resources that the process was using PMON also checks on
dispatcher and server
processes and restarts them if they have failed.
What is the function of ARCH ?
Archiver (ARCH) copies the on-line redo log
files to archival storage when they are full. ARCH is
active only when a
database’s redo log is used in ARCHIVELOG mode.
What is function of RECO ?
RECOver (RECO) is used to resolve
distributed transactions that are pending due to a network or
system failure in a
distributed database. At timed intervals,the local RECO attempts to connect to
remote databases and
automatically complete the commit or rollback of the local portion of any
pending distributed
transactions.
What is the function of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is responsible
for routing requests from connected user processes to
available shared
server processes and returning the responses back to the appropriate user
processes.
How many Dispatcher Processes are created ?
Atleast one Dispatcher process is created for every communication protocol in
use.
What is the function of Lock (LCKn) Process ?
Lock (LCKn) are used for inter-instance
locking when the ORACLE Parallel Server option is used.
What is the maximum number of Lock Processes used ?
Though a single LCK process is sufficient
for most Parallel Server systems
upto Ten Locks (LCK0,….LCK9) are used for inter-instance locking.