Administrator and Administration Tasks

1
Administrator and Administration Tasks

• The Administrator is responsible for the
  following tasks
• Managing the Availability of the database
• Planning and creating databases
• Managing the structure of the database
• Managing Storage
• Managing the security of the system and user
  access and activities
• Managing network connections ( Clients and
  Server connections)
• Perform backup tasks and insure valid recovery
  procedure is in place
• Perform tuning task for optimum server
  performance
• This presentation will introduce
• The basic components of Oracle Servers memory.
• User and Background Processes
• Database physical files

2
The Oracle Instance - Overall

• Every time a database is started, a system global
  area (SGA) is allocated and Oracle background
  processes are started. The system global area is
  an area of memory used for database information
  shared by the database users. The combination of
  the background processes and memory buffers is
  called an Oracle instance.
• The illustration below depicts the architecture
  of an instance in its simplest form.

3
The Oracle Instance - Overall

• An Oracle instance has two types of processes
• A user process executes the code of an
  application program (such as an Oracle Forms
  application) or an Oracle Tool (such as Oracle
  Enterprise Manager WorkSheet).
• Oracle processes are server processes that
  perform work for the user processes and
  background processes that perform maintenance
  work for the Oracle server. Not shown here ad
  will be discussed in a later class.

Oracle Database
Database process (DBWR)
Log Writer Process (LGWR)
User Process SQL Session Or Oracle App
4
The SGA
Parts of the SGA The SGA contains data and
control information for the servers
operations. Segments of the SGA Shared Pool
Contains most recently executed SQL by users as
well as recently data used from the
Data Dictionary. Database Buffer Cache Stores
most recently used data. Redo Log Buffer Used
to register changes made to the database. NOTE
An instance, defined by setting ORACLE_SID at the
operating system level, when open uses one
database at one given time. NOTE There are
other area of the SGA that are beyond the scope
of this course.
5
SGA - Shared Pool
The Shared Pool is segmented into 2 segments,
Library Cache and Data Dictionary Cache. 1-
Library Cache Contains statement text, Parsed
code or a parse tree which the compiled version
of the statement, and execution plan which
describes the steps to be followed when running
the statement determined by the optimizer. 2-
Data Dictionary Cache Contains most recently
used table and column definitions and user
access privileges. The size of the Shared Pool
is defined the parameter SHARED_POOL SIZE within
the parameter file Init.ora.
6
SGA - Program Global Area (also called Process
Global Area)
Contains information for a single server process
and a single server or background process.
Different from the SGA which is a shared and
written to by several processes, the PGA is
written to by a single background or server
process. The PGA contains 1- Sort Area Used
for sorting rows before processing and returning
to the user. 2- Session Information User access
privileges for the session. ( Not an MTS
configuration server). 3- Cursor State The
state of cursors used in the session. 4- Stack
Space Contains session variables. NOTE The
PGA is allocated when a user process is created
and de-allocated when the session is terminated.
7
SGA - Database Buffer Cache
Used to store most recently used data blocks. The
data in this buffer is stored in blocks each
block is equal the size of the database
block. The size of the database data block is
specified by the parameter DB_BLOCK_SIZE with in
the initialisation file. The number of buffers
is the to the value of the DB_BLOCK_BUFFERS.
DB_BLOCK_SIZE DB_BLOCK_BUFFERS the size of
the cache.
Database Buffer Cache
Process
Note DB_BLOCK_SIZE once set for the database can
not be changed.
8
SGA Redo Log Buffer
The server process records changes made to the
database in the redo log buffer.
Redo Log Buffer
Its size is set by the LOG_Buffer parameter (with
Oracle version, block size, and default value
considerations).
Process

• Its contents are records of changed blocks the
  changed block, the location of the change, and
  the new value. Redo records do not distinguish
  between the type of block that changed index,
  table, or rollback block.
• Changes are recorded on the log buffer
  sequentially with no wait.
• It is a circular buffer that is reused after it
  is filled, but no data is dropped before it is
  recorded onto the redo log.

9
The Oracle Instance - Processes
Background Processes Background Processes
service common functions that are needed for user
requests from the database . Each Oracle
instance, depending on configuration, must have
at least the following services DBWR Writes
changes to the database. LGWR Writes changes
registered in the redo log buffer to the
database. SMON Checks for consistency and
initiate recovery of the the database when
the database is opened. PMON Manages resources
and user processes. CKPT Updates database
status whenever changes in the buffer cache
are permanently written to the database.
10
The Physical structure of the Database
Oracle database is identified by its name DB_NAME
and is composed of operating system files.
Database Files 1- Data files These files
store the data dictionary, user objects, and
before_images (Rollback Segments) of data that
are modified by current transactions. 2- Redo
log files Contain a record of change made to the
database to ensure reconstruction of the data in
case of a failure. 3- Control file contain
information about the database its physical
structure and location of database files 4-
Parameter file This file contains the parameters
that are used to define an instance. 5-
Archived redo log files They are offline copies
of the redo log files used to recover a failed
instance.