Oracle Architecture :-
Oracle Architectural Components:
Objectives:
This is introduces the Oracle server
architecture by examining the physical, memory,process, and logical structures
involved in establishing a database connection, creating a session, and
executing SQL commands.
Oracle is a Relational Database Management System (RDBMS), which uses
Relational Data Model to store its database and SQL (commonly abbreviated as
Structured Query Language) to process the stored data. The architecture of
Oracle system can be best explained in terms of client/server paradigm. Thus,
we will explain the architecture of Oracle server using the structure called
instance..
An oracle instance is a complex set of memory structures and operating system
processes. It is the Oracle instance, which manages all database activities,
such as transaction processing, database recovery, form generation, and so on.
The instance structure is loosely styled after UNIX's implementation of
multitasking operating system. Discrete processes perform specialized tasks
within the RDBMS that work together to accomplish the goals of the instance.
Each process has a separate memory block that it uses to store local and
private variables, address stacks and other runtime information. The processes
use a common shared memory area for processing data concurrently. This memory
block is called the System Global Area (SGA). Figure 1 illustrates the
architecture of an instance.
System Global Area (SGA)
The SGA is the primary memory
component of the instance. It provides memory
structure necessary for data manipulation, SQL statement parsing, and redo
caching. The SGA is shared, which means that the multiple processes can access
and modify the data contained in it in a synchronized manner. The SGA consists
of the following components:
- Shared Pool
- Database buffer cache
- Redo Log Buffer
Multithread server(MTS) structures.
The SGA is
a read/write memory area that stores information shared by all database
processes and by all users of the database (sometimes it is called the Shared Global Area).
o This information
includes both organizational data and control information used by the Oracle
Server.
o The SGA is
allocated in memory and virtual memory.
o The size of the
SGA can be established by a DBA by assigning a value to the parameter SGA_MAX_SIZE in the parameter file—this is an
optional parameter.
The SGA is allocated when an Oracle
instance (database) is started up based on values specified in the
initialization parameter file (either PFILE or SPFILE).
The SGA has the following mandatory
memory structures:
· Database
Buffer Cache
· Redo
Log Buffer
· Java
Pool
· Streams
Pool
· Shared
Pool – includes two components:
o Library Cache
o Data Dictionary
Cache
· Other
structures (for example, lock and latch management, statistical data)
Additional optional memory
structures in the SGA include:
· Large
Pool
The SHOW
SGA SQL command will show you the SGA memory allocations.
· This
is a recent clip of the SGA for the DBORCL database at SIUE.
· In
order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user
accounts that are members of the DBA Linux group).
SQL> connect / as
sysdba
Connected.
SQL> show sga
Total System Global Area
1610612736 bytes
Fixed
Size 2084296
bytes
Variable
Size 1006633528
bytes
Database
Buffers 587202560
bytes
Redo
Buffers 14692352
bytes
Early versions of Oracle
used a Static SGA. This meant that if modifications to
memory management were required, the database had to be shutdown, modifications
were made to the init.ora parameter file, and then the database had
to be restarted.
Oracle 11g uses a Dynamic SGA. Memory configurations for the system global
area can be made without shutting down the database instance. The
DBA can resize the Database Buffer Cache and Shared Pool dynamically.
Several initialization
parameters are set that affect the amount of random access memory dedicated to
the SGA of an Oracle Instance. These are:
· SGA_MAX_SIZE: This optional parameter is used to set a limit on the
amount of virtual memory allocated to the SGA – a typical setting
might be 1 GB; however, if the value for SGA_MAX_SIZE in
the initialization parameter file or server parameter file is less than the sum
the memory allocated for all components, either explicitly in the parameter
file or by default, at the time the instance is initialized, then the database
ignores the setting for SGA_MAX_SIZE. For optimal performance, the
entire SGA should fit in real memory to eliminate paging to/from disk by the
operating system.
· DB_CACHE_SIZE: This optional parameter is used to tune the amount
memory allocated to the Database Buffer Cache in standard database
blocks. Block sizes vary among operating systems. The
DBORCL database uses 8
KB blocks. The
total blocks in the cache defaults to 48 MB on LINUX/UNIX and 52 MB on Windows operating systems.
· LOG_BUFFER: This optional parameter specifies the number of
bytes allocated for the Redo Log Buffer.
· SHARED_POOL_SIZE: This optional parameter specifies the number of bytes
of memory allocated to shared SQL and PL/SQL. The default is 16 MB. If the operating system is based on a 64 bit configuration, then the default size is 64 MB.
· LARGE_POOL_SIZE: This is an optional memory object – the size of the
Large Pool defaults to zero. If the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default size is automatically calculated.
· JAVA_POOL_SIZE: This is another optional memory object. The
default is 24 MB of memory.
The size of the SGA
cannot exceed the parameter SGA_MAX_SIZE minus
the combination of the size of the additional parameters, DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE.
Memory is allocated to
the SGA as contiguous virtual memory in units termed granules. Granule
size depends on the estimated total size of the SGA, which as was noted above,
depends on the SGA_MAX_SIZE parameter. Granules are sized as
follows:
· If
the SGA is less than 1
GB in total, each
granule is 4 MB.
· If
the SGA is greater than 1 GB in total, each
granule is 16 MB.
Granules are assigned to
the Database Buffer Cache, Shared Pool, Java Pool, and other memory structures,
and these memory components can dynamically grow and shrink. Using
contiguous memory improves system performance. The actual number of
granules assigned to one of these memory components can be determined by
querying the database view named V$BUFFER_POOL.
Granules are allocated
when the Oracle server starts a database instance in order to provide memory
addressing space to meet the SGA_MAX_SIZE parameter. The minimum is
3 granules: one each for the fixed SGA, Database Buffer Cache, and
Shared Pool. In practice, you'll find the SGA is allocated much more
memory than this. The SELECT statement shown below shows a
current_size of 1,152 granules.
SELECT name,
block_size,current_size,prev_size,prev_buffers
FROM v$buffer_pool;
NAME BLOCK_SIZE
CURRENT_SIZE PREV_SIZE PREV_BUFFERS
-------- ----------
------------ ---------- ------------
DEFAULT 8192 560
576 71244
Shared pool
The Oracle shared pool
contains Oracle's library cache, which is responsible for collecting, parsing,
interpreting, and executing all of the SQL statements that go against the
Oracle database. Hence, the shared pool is a key component, so it's necessary
for the Oracle database administrator to check for shared pool contention.
The shared pool is like
a buffer for SQL statements. Oracle's parsing algorithm ensures that
identical SQL statements do not have to be parsed each time they're
executed. The shared pool is used to store SQL statements, and it
includes the following components:
Since it is not possible
to dedicate separate regions of memory for the shared pool components, the
shared pool is usually the second-largest SGA memory area (depending on the
size of the db_cache_size parameter). The shared pool contains
RAM memory regions that serve the following purposes:
• Library cache – The library
cache contains the current SQL execution plan information. It also holds stored
procedures and trigger code.
• Dictionary cache - The dictionary cache stores environmental
information, which includes referential integrity, table definitions, indexing
information, and other metadata stored within Oracle's internal tables.
• Session information – Systems that use SQL*Net version 2
with a multi-threaded server need this area to store session information.
Beginning with Oracle, the v$session view contains information related to
Oracle*Net users.
The following table
lists the different areas stored in the shared pool and their purpose:
* Shared SQL Area - The shared
SQL area stores each SQL statement executed in the database. This area allows
SQL execution plans to be reused by many users.
* Private SQL Area - Private
SQL areas are non-shared memory areas assigned to unique user sessions.
* PL/SQL Area - Used to hold
parsed and compiled PL/SQL program units, allowing the execution plans to be
shared by many users.
* Control Structures - Common
control structure information, for example, lock information.
Database Buffer Cache
The database buffer
cache holds copies of data blocks read from the data files. The term data block
is used to describe a block containing table data, index data, clustered data,
and so on. Basically, it is a block that contains data. All user processes
concurrently connected to the instance share access to the database buffer
cache. The database buffer cache is logically segmented into multiple sets.
This reduces contention on multiprocessor systems.
This area of the SGA
contains only the buffers themselves and not their control structures. For each
buffer, there is a corresponding buffer header in the variable area of the SGA.
Program Global Area
(PGA)
A Program Global Area
(PGA) is a memory region that contains data and control information for a
server process. It is a non-shared memory region created by Oracle when a
server process is started. Access to the PGA is exclusive to that server
process and it is read and written only by Oracle code acting on its behalf. It
contains a private SQL area and a session memory area.
A private SQL area
contains data such as bind information and runtime memory structures. Each
session that issues a SQL statement has a private SQL area. Session memory is
the memory allocated to hold a session’s variables (logon information) and
other information related to the session. For a shared server, the session
memory is shared and not private.
Buffer Cache Management
The database buffer
cache is organized in two lists: the write list and the least-recently-used
(LRU) list. The write list holds dirty buffers, which contain data that has
been modified but has not yet been written to disk. The LRU list holds free
buffers, pinned buffers, and dirty buffers that have not yet been moved to the
write list. Free buffers do not contain any useful data and are available for
use. Pinned buffers are buffers that are currently being accessed.
When an Oracle process
requires data, it searches the buffer cache, finds the data blocks, and then
uses the data. This is known as a cache hit. If it cannot find the data, then
it must be obtained from the data file. In this case, it finds a free buffer to
accommodate the data block by scanning the LRU list, starting at the
least-recently-used from the end of the list. The process searches either until
it finds a free buffer or until it has searched the threshold limit of buffers.
When the user process is
performing a full table scan, it reads the data blocks into buffers and places
them on the LRU end instead of the MRU end of the LRU list. This is because a
fully scanned table is usually needed only briefly and the blocks should be
moved out quickly.
What Is a Dirty Block?
Whenever a server process
changes or modifies a data block, it becomes a dirty block. Once a server
process makes changes to the data block, the user may commit transactions, or
transactions may not be committed for quite some time. In either case, the
dirty block is not immediately written back to disk.
Writing dirty blocks to
disk takes place under the following two conditions:
* When a server process
cannot find a clean, reusable buffer after scanning a threshold number of
buffers, then the database writer process writes the dirty blocks to disk.
* When the checkpoint
takes place, the database writer process writes the dirty blocks to disk.
Large
Pool
Oracle Large Pool is an optional memory component of the
oracle database SGA. This area is used for providing large memory allocations
in many situations that arise during the operations of an oracle database
instance.
1. Session memory for the a shared server and
the Oracle XA Interface when distributed transactions are involved
2. I/O Server Processes
3. Parallel Query Buffers
4. Oracle Backup and Restore Operations using
RMAN
Large Pool plays an important role in Oracle Database
Tuning since the allocation of the memory for the above
components otherwise is done from the shared pool. Also due to the large memory
requirements for I/O and Rman operations, the large pool is better able to
satisfy the requirements instead of depending on the Shared Pool Area.
Usage of a Large Pool Area allows the shared
pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL
Cache Area
JAVA
Pool
The JAVA Pool holds the
JAVA execution code in a similar manner to the PL/SQL cache in the shared pool.
The JAVA pool is used by many internal routines, such as import and export, and
should be sized at approximately 60 megabytes if no other JAVA will be utilized
in the user applications
The Redo Log Buffer
The redo log buffer is a
RAM area (defined by the initialization parameter log_buffer) that works to
save changes to data, in case something fails and Oracle has to put it back
into its original state (a “rollback”). When Oracle SQL updates a table
(a process called Data Manipulation Language, or DML), redo images are created
and stored in the redo log buffer. Since RAM is faster than disk, this makes
the storage of redo very fast.
The Oracle redo log
buffer provides the following functions within the Oracle SGA:
- Serves for assistance with database recovery tasks
- Records all changes made to database blocks
- Places changes recorded to redo entries for redo logs
The database initialization
parameter log_buffer defines the default size of the redo log
buffer within Oracle 11g.
Redo log files:
Redo logs are
transaction journals. Each transaction is recorded in the redo logs. Redo logs
are used in a serial fashion with each transaction queuing up in the redo log
buffers and being written one at a time into the redo logs. Redo logs as a
general rule should switch about every thirty minutes. However, you may need to
adjust the time up or down depending on the importance of your data. The rule
of thumb is to size the redo logs such that you only loose the amount of data
you can stand to loose should for some reason the online redo log become
corrupt. With modern Oracle redo log mirroring and with disk array mirroring
and various forms of online disk repair and replacement the occurrence of redo
log corruptions has dropped to practically zero, so size based on the number of
archive logs you want to apply should the database fail just before your next
backup.
The LOG_BUFFER_SIZE and
LOG_BUFFERS parameters control the redo log buffers. The LOG_BUFFER_SIZE should
be set to reduce the number of writes required per redo log but not be so large
that it results in an excessive IO wait time. Some studies have shown that
sizing bigger than one megabyte rarely results in performance gains. Generally
I size the LOG_BUFFER_SIZE such that it is equal to or results in an even
divisor of the redo log size.
Monitor redo logs using
the alert log, V$LOGHIST, V$LOGFILE, V$RECOVERY_LOG and V$LOG DPTs.
Control files
Control files are
created when the database is created. Control file size is determined by a
number of initialization parameters and really shouldn’t concern you. However,
if you are using raw devices for control files make sure the raw partition is
sized to allow the control file to grow in Oracle8 and Oracle8i databases as
backup information is added. Each database instance must have at least one
control file of it will not start. Loss of all control files will cause the
database to crash. Have at least two copies on physically separate disk arrays
or devices.
I suggest maintaining a
script to rebuild your control files. The control file rebuild script should be
recreated after each physical change to the database. The command CREATE
CONTROLFILE is used to manually create a controlfile. However, the ALTER
DATABASE BACKUP CONTROL FILE TO TRACE command is the preferred method to
generate a script that can be used to rebuild the control file if it becomes
damaged.
Initialization File
Although not normally
discussed as a database file a database will not startup without a valid
initialization parameter file. The file is only read at startup and contains
the information required to set up the SGA, control file locations, trace and
dump file locations and setup parameters for multi-threaded server, parallel
query and Oracle Parallel Server. I suggest keeping a copy in a secure place
incase yours ever becomes corrupted, deleted or damaged. The Oracle Reference,
which is included in the Oracle8 and Oracle8i documentation set, provides
descriptions of all initialization parameters. There are also undocumented
initialization parameters that allow specialized behaviors to be configured.
Both the documented and undocumented initialization parameters are covered in
my book “ORACLE8i Administration and Management” from Wiley and Sons.
It should be obvious
that the most important file regarding database setup and operation is probably
the INIT<sid>.ORA, or initialization file. This file contains the
assignments for the database initialization parameters.
NAME
|
VALUE
|
DESCRIPTION
|
O7_DICTIONARY_ACCESSIBILITY
|
TRUE
|
V7 Dictionary accessibility Support (Migration only)
|
ALWAYS_ANTI_JOIN
|
NESTED_LOOPS
|
Always use this anti-join when possible
|
ALWAYS_SEMI_JOIN
|
standard
|
Always use this semi-join when possible
|
AQ_TM_PROCESSES
|
0
|
Number of AQ Time Managers to start
|
AUDIT_FILE_DEST
|
(PD)
|
Destination for audit files
|
AUDIT_TRAIL
|
NONE
|
Enable system auditing
|
BACKGROUND_CORE_DUMP
|
PARTIAL
|
Sets whether SGA is dumped with core file dump, PARTIAL means don't dump SGA.
|
BACKGROUND_DUMP_DEST
|
(PD)
|
Detached process dump directory
|
BACKUP_TAPE_IO_SLAVES
|
FALSE
|
BACKUP Tape I/O slaves
|
BITMAP_MERGE_AREA_SIZE
|
1048576
|
Maximum memory allow for BITMAP MERGE
|
BLANK_TRIMMING
|
FALSE
|
Blank trimming semantics parameter
|
BUFFER_POOL_KEEP
|
0
|
Number of database blocks/latches in KEEP buffer pool
|
BUFFER_POOL_RECYCLE
|
0
|
Number of database blocks/latches in recycle buffer pool
|
COMMIT_POINT_STRENGTH
|
1
|
Bias this node has toward not preparing in a two-phase commit
|
COMPATIBLE
|
8.1.0
|
Database will be compatible with this software version
|
CONTROL_FILE_RECORD_KEEP_TIME
|
7
|
Control file record keep time in days
|
CONTROL_FILES
|
(PD)
|
Control file names list
|
CORE_DUMP_DEST
|
(PD)
|
Destination for core dump files.
|
CPU_COUNT
|
(PD)
|
Number of cpu's for this instance
|
CREATE_BITMAP_AREA_SIZE
|
8388608
|
Size of create bitmap buffer for bitmap index
|
CURSOR_SPACE_FOR_TIME
|
FALSE
|
Use more memory in order to get faster execution
|
DB_BLOCK_BUFFERS
|
8000
|
Number of database blocks cached in memory
|
DB_BLOCK_CHECKING
|
TRUE
|
Data and index block checking overrides events 10210 and 10211
|
DB_BLOCK_CHECKSUM
|
FALSE
|
Store checksum in db blocks and check during reads
|
DB_BLOCK_LRU_LATCHES
|
1|CPU_COUNT/2
|
Number of lru latches
|
DB_BLOCK_MAX_DIRTY_TARGET
|
DB_BLOCK_BUFFERS
|
Upper bound on modified buffers/recovery reads
|
DB_BLOCK_SIZE
|
(PD)
|
Size of database block in bytes
|
DB_DOMAIN
|
WORLD
|
Directory part of global database name stored with CREATE DATABASE
|
DB_FILE_DIRECT_IO_COUNT
|
64
|
Sequential I/O block count
|
DB_FILE_MULTIBLOCK_READ_COUNT
|
8
|
Db blocks read for each IO
|
DB_FILE_NAME_CONVERT
|
NULL
|
Datafile name convert pattern and string for standby/clone database
|
DB_FILES
|
MAXDATAFILES
|
Max allowable # db files
|
DB_NAME
|
(PD)
|
Database name specified in CREATE DATABASE
|
DB_WRITER_PROCESSES
|
1
|
Number of background database writer processes to start
|
DBLINK_ENCRYPT_LOGIN
|
FALSE
|
Enforce password for distributed login always be encrypted
|
DBWR_IO_SLAVES
|
0
|
Number of DBWR I/O slaves
|
DELAYED_LOGGING_BLOCK_CLEANOUTS
|
TRUE
|
Turns delayed block cleanout on or off
|
DISK_ASYNCH_IO
|
TRUE
|
Use asynch I/O for random access devices
|
DISTRIBUTED_TRANSACTIONS
|
(PD)
|
Max. number of concurrent distributed transactions
|
DML_LOCKS
|
4*Trans.
|
Dml locks - one for each table modified in a transaction
|
ENQUEUE_RESOURCES
|
Derived
|
Resources for enqueues
|
EVENT
|
NULL
|
Debug event control
|
FIXED_DATE
|
NULL
|
Fixed SYSDATE value
|
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY
|
FALSE
|
Freeze database during instance recovery (OPS)
|
GC_DEFER_TIME
|
10
|
How long to defer down converts for hot buffers (DFS)(OPS)
|
GC_FILES_TO_LOCKS
|
NULL
|
Mapping between file numbers and lock buckets (DFS)(OPS)
|
GC_RELEASABLE_LOCKS
|
0
|
Number of releasable locks (DFS)(OPS)
|
GC_ROLLBACK_LOCKS
|
20
|
Locks for the rollback segments (DFS)(OPS)
|
GLOBAL_NAMES
|
TRUE
|
Enforce that database links have same name as remote database
|
HASH_AREA_SIZE
|
2*SORT
|
Size of in-memory hash work area
|
HASH_JOIN_ENABLED
|
TRUE
|
Enable/disable hash join
|
HASH_MULTIBLOCK_IO_COUNT
|
1
|
Number of blocks hash join will read/write at once
|
HI_SHARED_MEMORY_ADDRESS
|
0
|
SGA starting address (high order 32-bits on 64-bit platforms)
|
HS_AUTOREGISTER
|
TRUE
|
Enable automatic server DD updates in HS agent self-registration
|
IFILE
|
NULL
|
Include file in init.ora
|
INSTANCE_GROUPS
|
NULL
|
List of instance group names
|
INSTANCE_NAME
|
NULL
|
Instance name supported by the instance
|
|
|
|
INSTANCE_NUMBER
|
0
|
Instance number
|
JAVA_POOL_SIZE
|
10000K
|
Size in bytes of the Java pool
|
JOB_QUEUE_INTERVAL
|
60
|
Wakeup interval in seconds for job queue processes
|
JOB_QUEUE_KEEP_CONNECTIONS
|
FALSE
|
Keep network connections between execution of jobs
|
JOB_QUEUE_PROCESSES
|
0
|
Number of job queue processes to start
|
LARGE_POOL_SIZE
|
0
|
Size in bytes of the large allocation pool (auto set at 600k)
|
LICENSE_MAX_SESSIONS
|
0
|
Maximum number of non-system user sessions allowed
|
LICENSE_MAX_USERS
|
0
|
Maximum number of named users that can be created in the database
|
LICENSE_SESSIONS_WARNING
|
0
|
Warning level for number of non-system user sessions
|
LM_LOCKS
|
12000
|
Number of locks configured for the lock manager (OPS)
|
LM_PROCS
|
64
|
Number of client processes configured for the lock manager (OPS)
|
LM_RESS
|
6000
|
Number of resources configured for the lock manager (OPS)
|
LOCAL_LISTENER
|
NULL
|
Local listener
|
LOCK_NAME_SPACE
|
NULL
|
Lock name space used for generating lock names for standby/clone database
|
LOCK_SGA
|
FALSE
|
Lock entire SGA in physical memory
|
Oracle Database
Structure:
• A database is
collection of data files plus programs that manipulate those data files.
• Two types of
information are stored in an Oracle database
• User data, relevant to
a particular application and
• System data, that the
database needs to manage itself
Components:
• Database files –
contain all database data
• Control files –
contain info to access and manipulate the data
• Redo Logs – record all
the transactions on the database
• Tablespaces – logical
divisions of a database
• Segments – set of
database blocks
• Extents – allocation
of contiguous space on disk exp in bytes.
Database files:
• An Oracle database
consists of one or more database files.
• These files contain
data that can be accessed with the help of SQL.
• A file can be
associated with one and only one tablespace.
• The database when
created initially, has only one physical file.
• The maximum number of
files that can be opened at a time are 32 by default.
• One or more physical
files form a logical unit called Tablespace.
Control files:
• A database’s overall physical architecture is maintained by its control
files.
• These record control
information about all the files within the database.
• A control file is
associated with a single database
• Control files are
automatically modified by Oracle; a user cannot edit them.
• They are used to
maintain internal consistency and guide recovery operations.
Redo Log files:
• A Redo Log contains all the transactions that have occurred against
the database.
• No activity in the
database can take place without being recorded in Redo Logs.
• The Redo Logs are
necessary to protect against data loss.
• Redo Logs are also
called transaction logs.
• Every database
requires a minimum of two Redo Logs.
• These are used in
recovery operations to restore lost or damaged files.
Oracle Processes:
The front-line support
for Oracle backup and recovery is the Oracle architecture. One part
of this architecture is
the processes related to the Oracle database. Although the Oracle
database has a number of
processes, only a few really matter with respect to backup and
recovery and will be
mentioned in this text. These processes are as follows:
SMON
PMON
CKPT
LGWR
DBWR
ARCH
User processes
Let’s discuss each of
these processes next so you can better understand how they impact
database recovery.
SMON (System Monitor):
SMON (System MONitor)
is an Oracle background process created when you start a database instance.
The SMON process performs instance recovery, cleans up after dirty shutdowns
and coalesces adjacent free extents into larger free extents.
When you startup your
Oracle database, Oracle will perform several checks to see if the database is
healthy. SMON manages your database for you!
If Oracle finds a
problem at startup time, the System Monitor process (SMON) will perform
recovery activities. SMON also performs certain types of database maintenance.
These activities occur in the background and have no real impact on normal
database operations.
Performs multiple maintenance tasks, including the following:
• Creates and manages the temporary tablespace metadata
• Reclaims space used by orphaned temp segments
• Maintains the undo tablespace by on-lining, off-lining, and
shrinking undo segments based on undo space usage statistics
• Cleans up the data dictionary when in a transient and
inconsistent state
• Maintains the SCN to time mapping table used to support
Flashback
PMON (Process Monitor):
Process Monitor process recovers failed process resources. If MTS
(also called Shared Server Architecture) is being utilized, PMON monitors and
restarts any failed dispatcher or server processes. In RAC, PMON’s role as
service registration agent is particularly important.
• The Process Monitor is the janitor of the database,
cleaning-up trash left-over from aborted user sessions (for instance, if a
client abnormally disconnects from the database). If this should happen, it is
the job of the Process Monitor (PMON) process to cleanup after that failure.
PMON will cleanup memory areas and other database resources that were in use by
that user process. PMON constantly checks the status of user and database
processes. In some cases, failed database processes can be restarted by PMON.
LGWR Process
The log writer process
(LGWR) is responsible for keeping the online redo logs up-to-date. The
job of the LGWR process
is to move redo from the volatile (nonpersistent) redo log buffer in
the System (sometimes
called Shared) Global Area (SGA) to the persistence of the online redo
logs. A number of
different things cause LGWR to wake up and write the redo data, among
them commits and when
the redo log buffer fills to a certain point.
DBWR Process
The database writer process
DBWn is responsible for writing to the database datafiles.
This writing occurs
during events called checkpoints. A database checkpoint may, in reality,
happen at just about any
time while the database is running. DBWR has very little
to do with recovery of
the database (other than to support it by writing to the datafiles)
because database
datafile writes are often delayed and the blocks within the datafiles
themselves
are not consistent with
the current state of the data inside of the SGA.
ARCH Process
The archiver process
ARCn is responsible for the creation of archived redo logs. In a later
section in this chapter
on redo logs, we will discuss how redo logs are filled with redo. Once
the redo log file fills,
a log switch occurs and Oracle will begin to write to the next online
redo log. If the
database is in ARCHIVELOG mode (see the section “NOARCHIVELOG
and ARCHIVELOG Modes”),
the ARCH process will be responsible for taking that filled
archived redo log and
copying it to one or more backup locations.
In Oracle Database 11g,
the ARCH process starts automatically. Oracle can also start
more than one ARCH
process if multiple redo logs need to be archived. For ARCH to work
properly, you will need
to configure the appropriate archiving locations (see “Configuring the
Database for Backup and
Recovery” later in this chapter for more). The ARCH process is so
vital to backup and
recovery of the database that if it cannot copy the archived redo logs to the
mandatory archived log
destinations, the database will eventually stall until the problem
is corrected.
User Processes
At first glance, it
might seem that the user processes are not all that important to backup and
recovery. As you will
see, user processes are actually an integral part of backup and recovery
since you have to be
able to connect to the database instance to actually do a database backup
or recovery.
Tablespaces:
• A database is divided into logical divisions called tablespaces
• A database may have
one or more tablespaces
• Each logical
tablespace corresponds to one or more physical database files.
•The typical tablespace present in an Oracle database are (11g) :
SYSTEM Tablespace:
• Every Oracle database contains a tablespace
named SYSTEM, which Oracle creates automatically when the database is
created. The SYSTEM tablespace is always online when the database is
open.
To take advantage of the
benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace,
or you can migrate an existing dictionary managed SYSTEM tablespace
to a locally managed format.
In a database with a
locally managed SYSTEM tablespace, dictionary tablespaces cannot be
created. It is possible to plug in a dictionary managed tablespace using the
transportable feature, but it cannot be made writable.
• The SYSTEM tablespace always contains
the data dictionary tables for the entire database. The data dictionary tables
are stored in datafile 1.
Note: Once a tablespace is
locally managed, it cannot be reverted back to being dictionary managed.
SYSAUX Tablespace:
• The SYSAUX tablespace is always
created at database creation. The SYSAUX tablespace serves as an
auxiliary tablespace to the SYSTEM tablespace. Because it is the
default tablespace for many Oracle Database features and products that
previously required their own tablespaces, it reduces the number of tablespaces
required by the database. It also reduces the load on the SYSTEM tablespace.
You can specify only
datafile attributes for the SYSAUX tablespace, using the SYSAUX
DATAFILE clause in the CREATE DATABASE statement. Mandatory
attributes of the SYSAUX tablespace are set by Oracle Database and
include:
- PERMANENT
- READ WRITE
- EXTENT MANAGMENT LOCAL
- SEGMENT SPACE MANAGMENT AUTO
You cannot alter these
attributes with an ALTER TABLESPACE statement, and any attempt
to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.
Undo Tablespaces:
• Undo tablespaces are special tablespaces
used solely for storing undo information. You cannot create any other segment
types (for example, tables or indexes) in undo tablespaces. Each database
contains zero or more undo tablespaces. In automatic undo management mode, each
Oracle instance is assigned one (and only one) undo tablespace. Undo data is
managed within an undo tablespace using undo segments that are automatically
created and maintained by Oracle.
When the first DML
operation is run within a transaction, the transaction is bound (assigned) to
an undo segment (and therefore to a transaction table) in the current undo
tablespace. In rare circumstances, if the instance does not have a designated undo
tablespace, the transaction binds to the system undo segment.
• Each undo tablespace is composed of a set of
undo files and is locally managed. Like other types of tablespaces, undo blocks
are grouped in extents and the status of each extent is represented in the
bitmap. At any point in time, an extent is either allocated to (and used by) a
transaction table, or it is free.
Temporary Tablespace:
• When the SYSTEM tablespace is locally
managed, you must define a default temporary tablespace when creating a
database. A locally managed SYSTEM tablespace cannot be used for
default temporary storage.
• If SYSTEM is dictionary managed and if
you do not define a default temporary tablespace when creating the database,
then SYSTEM is still used for default temporary storage. However, you
will receive a warning inALERT.LOG saying that a default temporary
tablespace is recommended and will be necessary in future releases.
USER Tablespace:
• information about users is stored
