Oracle Database Instance, Access and Utilities

Muchake Brian
Phone: 0701178573
Email: bmuchake@cis.mak.ac.ug, bmuchake@gmail.com

Do not Keep Company With Worthless People

Psalms 26:11
Oracle Instance Review
Oracle Instance
 An Oracle database server consists of an Oracle database and an Oracle
instance. Note: Every time a database is started, a system global area
(SGA) is allocated and Oracle background processes are started. The
combination of the background processes and memory buffers is called
an Oracle instance.
Real Application Clusters: Multiple Instance Systems
 Some hardware architectures (for example, shared disk systems) enable
multiple computers to share access to data, software, or peripheral
devices.
 Real Application Clusters (RAC) takes advantage of such architecture by
running multiple instances that share a single physical database.
 In most applications, RAC enables access to a single database by users
on multiple machines with increased performance.
Oracle Instance [Cont’d]
 An Oracle database server uses memory structures and processes to
manage and access the database.
 Note: All memory structures exist in the main memory of the computers
that constitute the database system. Processes are jobs that work in the
memory of these computers.
Instance Memory Structures
 Oracle creates and uses memory structures to complete several jobs. For
example, memory stores program code being run and data shared
among users.
 Two basic memory structures are associated with Oracle: the system
global area and the program global area.
Oracle Instance [Cont’d]
System Global Area
• The System Global Area (SGA) is a shared memory region that contains
data and control information for one Oracle instance.
• Oracle allocates the SGA when an instance starts and deallocates it when
the instance shuts down. Each instance has its own SGA.
• Users currently connected to an Oracle database share the data in the
SGA. For optimal performance, the entire SGA should be as large as
possible (while still fitting in real memory) to store as much data in
memory as possible and to minimize disk I/O.
• The information stored in the SGA is divided into several types of memory
structures, including the database buffers, redo log buffer, and the
shared pool.
Oracle Instance [Cont’d]
 Database Buffer
• Cache of the SGA
• Database buffers store the most recently used blocks of data.
• The set of database buffers in an instance is the database buffer cache. The
buffer cache contains modified as well as unmodified blocks.
• Because the most recently (and often, the most frequently) used data is kept in
memory, less disk I/O is necessary, and performance is improved.
 Redo Log Buffer of the SGA
• The redo log buffer stores redo entries—a log of changes made to the database.
• The redo entries stored in the redo log buffers are written to an online redo log,
which is used if database recovery is necessary. The size of the redo log is
static.
Oracle Instance [Cont’d]
 Shared Pool of the SGA
• The shared pool contains shared memory constructs, such as shared
SQL areas.
• A shared SQL area is required to process every unique SQL statement
submitted to a database.
• A shared SQL area contains information such as the parse tree and
execution plan for the corresponding statement.
• A single shared SQL area is used by multiple applications that issue the
same statement, leaving more shared memory for other uses.
Oracle Instance [Cont’d]
 Statement Handles or Cursors
• A cursor is a handle or name for a private SQL area in which a parsed
statement and other information for processing the statement are kept.
(Oracle Call Interface, OCI, refers to these as statement handles.)
• Although most Oracle users rely on automatic cursor handling of Oracle
utilities, the programmatic interfaces offer application designers more
control over cursors.
• For example, in precompiler application development, a cursor is a
named resource available to a program and can be used specifically to
parse SQL statements embedded within the application.
• Application developers can code an application so it controls the phases
of SQL statement execution and thus improves application performance.
Oracle Instance [Cont’d]
Program Global Area
• The Program Global Area (PGA) is a memory buffer that contains data
and control information for a server process.
• A PGA is created by Oracle when a server process is started. The
information in a PGA depends on the Oracle configuration.
 Oracle Background Processes
• An Oracle database uses memory structures and processes to manage
and access the database.
• Note: All memory structures exist in the main memory of the computers
that constitute the database system. Processes are jobs that work in the
memory of these computers.
Oracle Instance [Cont’d]
• The architectural features discussed here enable the Oracle database to
support:
a)Many users concurrently accessing a single database
b)The high performance required by concurrent multiuser, multiapplication
database systems.
• Oracle creates a set of background processes for each instance. The
background processes consolidate functions that would otherwise be
handled by multiple Oracle programs running for each user process.
• They asynchronously perform I/O and monitor other Oracle process to
provide increased parallelism for better performance and reliability.
• There are numerous background processes, and each Oracle instance
can use several background processes.
Oracle Instance [Cont’d]
 Process Architecture
• A process is a "thread of control" or a mechanism in an operating system
that can run a series of steps.
• Some operating systems use the terms job or task. A process generally has
its own private memory area in which it runs.
• An Oracle database server has two general types of processes: user
processes and Oracle processes.
1. User (Client) Processes
 User processes are created and maintained to run the software code of an
application program (such as an OCI or OCCI program) or an Oracle tool
(such as Enterprise Manager).
 User processes also manage communication with the server process
through the program interface.
Oracle Instance [Cont’d]
2. Oracle Processes
Oracle processes are invoked by other processes to perform functions on
behalf of the invoking process.
Note: In Oracle db, Oracle creates server processes to handle requests
from connected user processes.
A server process communicates with the user process and interacts with
Oracle to carry out requests from the associated user process.
For example, if a user queries some data not already in the database
buffers of the SGA, then the associated server process reads the proper
data blocks from the datafiles into the SGA.
Oracle Instance [Cont’d]
• Oracle can be configured to vary the number of user processes for each
server process.
• In a dedicated server configuration, a server process handles requests for a
single user process.
• A shared server configuration lets many user processes share a small
number of server processes, minimizing the number of server processes and
maximizing the use of available system resources.
• On some systems, the user and server processes are separate, while on
others they are combined into a single process.
• If a system uses the shared server or if the user and server processes run on
different machines, then the user and server processes must be separate.
• Client/server systems separate the user and server processes and run them
on different machines.
Oracle Instance [Cont’d]
 In summary Oracle instance is an interface between client applications
(users) and database.
 An Oracle instance consists of three main parts:
1. System Global Area (SGA),
2. Program Global Area (PGA),
3. And background processes.
Oracle Instance [Cont’d]
Oracle Instance [Cont’d]
 The SGA is a shared memory structure allocated when the instance
started up and released when it is shut down.
 The SGA is a group of shared memory structures that contain data and
control information for one database instance.
 Different from the SGA, which is available to all processes, PGA is a
private memory area allocated to each session when the session started
and released when the session ends.
 PMON is the process monitor that regulates all other processes.
• PMON cleans up abnormally connected database connections and
automatically registers a database instance with the listener process.
PMON is a single most process that must be alive in an Oracle database.
Oracle Instance [Cont’d]
 SMON is the system monitor process that performs system level clean-
up operation.
• It has two primary responsibilities including automatically instance
recovery in the event of a failed instance, e.g., power failure; and
cleaning up of temporary files.
 DBWn is the database writer. Oracle performs every operation in
memory instead of the disk because processing in memory is faster than
on disk.
• The DBWn process reads data from disk and writes it back to the disk.
An Oracle instance has many database writers DBW0, DBW1, DBW2, and
so on.
Oracle Instance [Cont’d]
 CKPT is the checkpoint process. In Oracle, data that is on disk is called
block and the data which in memory is called buffer.
• When the block is written to the buffer and changed, the buffer becomes
dirty, and it needs to be written down to the disk.
• The CKPT process updates the control and data file headers with
checkpoint information and signals writing of dirty buffers to disk. Note
that Oracle 12c allows both full and incremental checkpoints.
Oracle Instance [Cont’d]
Oracle Instance [Cont’d]
 LGWR is the log writer process which is the key to the recoverability
architecture.
• Every change occurs in the database is written out to a file called redo
log for recovery purposes.
• And these changes are written and logged by LGWR process. The LGWR
process first writes the changes to memory and then disk as redo logs
which then can be used for recovery.
 ARCn is the archiver process that copies the content of redo logs to
archive redo log files.
 The archiver process can have multiple processes such as ARC0, ARC1,
and ARC3, which allow the archiver to write to various destinations such
D: drive, E drive or other storage.
Oracle Instance [Cont’d]
 MMON is the manageability monitor process that gathers performance
metrics.
 MMAN is the memory manager that automatically manages memory in
an Oracle database.
 LREG is the listener registration process that registers information on
the database instance and dispatcher processes with the Oracle Net
Listener.
Oracle Database Access
Oracle Database Access
Network Connections
 Oracle Net Services is Oracle’s mechanism for interfacing with the
communication protocols used by the networks that facilitate distributed
processing and distributed databases.
 Communication protocols define the way that data is transmitted and received
on a network.
• Oracle Net Services supports communications on all major network protocols,
including TCP/IP, HTTP, FTP, and WebDAV.
 Using Oracle Net Services, application developers do not need to be
concerned with supporting network communications in a database application.
• If a new protocol is used, then the database administrator makes some minor
changes, while the application requires no modifications and continues to
function.
Oracle Database Access [Cont’d]
 Oracle Net, a component of Oracle Net Services, enables a network
session from a client application to an Oracle database server.
• Once a network session is established, Oracle Net acts as the data
courier for both the client application and the database server.
• It establishes and maintains the connection between the client
application and database server, as well as exchanges messages
between them.
• Oracle Net can perform these jobs because it is located on each
computer in the network.
Oracle Database Access [Cont’d]
Starting Up the Database
 The three steps to starting an Oracle database and making it available
for systemwide use are:
1. Start an instance.
2. Mount the database.
3. Open the database.
 A database administrator can perform these steps using the SQL*Plus
STARTUP statement or Enterprise Manager.
• When Oracle starts an instance, it reads the server parameter file
(SPFILE) or initialization parameter file to determine the values of
initialization parameters. Then, it allocates an SGA, and creates
background processes.
Oracle Database Access [Cont’d]
How Oracle Works
• The following example describes the most basic level of operations that Oracle
performs.
 This illustrates an Oracle configuration where the user and associated server process
are on separate machines (connected through a network).
1.An instance has started on the computer running Oracle (often called the host or
database server).
2.A computer running an application (a local machine or client workstation) runs the
application in a user process. The client application attempts to establish a connection
to the server using the proper Oracle Net Services driver.
3.The server is running the proper Oracle Net Services driver. The server detects the
connection request from the application and creates a dedicated server process on
behalf of the user process.
4.The user runs a SQL statement and commits the transaction. For example, the user
changes a name in a row of a table.
Oracle Database Access [Cont’d]
5. The server process receives the statement and checks the shared pool for any
shared SQL area that contains a similar SQL statement.
• If a shared SQL area is found, then the server process checks the user’s access
privileges to the requested data, and the previously existing shared SQL area is
used to process the statement. If not, then a new shared SQL area is allocated for
the statement, so it can be parsed and processed.
5. The server process retrieves any necessary data values from the actual datafile
(table) or those stored in the SGA.
6. The server process modifies data in the system global area. The DBWn process
writes modified blocks permanently to disk when doing so is efficient. Because
the transaction is committed, the LGWR process immediately records the
transaction in the redo log file.
7. If the transaction is successful, then the server process sends a message across
the network to the application. If it is not successful, then an error message is
transmitted
Oracle Database Access [Cont’d]
9. Throughout this entire procedure, the other background processes
run, watching for conditions that require intervention.
• In addition, the database server manages other users’ transactions
and prevents contention between transactions that request the
same data.
Oracle Database Utilities
Oracle Utilities
 Oracle provides several utilities for data transfer, data maintenance, and
database administration, including Data Pump Export and Import, SQL*Loader,
and LogMiner.
 Oracle’s database utilities let you perform the following tasks:
1. High-speed movement of data and metadata from one database to another using
Data Pump Export and Import
2. Extract and manipulate complete representations of the metadata for database
objects, using the Metadata API
3. Move all or part of the data and metadata for a site from one database to
another, using the Data Pump API
4. Load data into Oracle tables from operating system files using SQL*Loader or
from external sources using external tables
5. Query redo log files through a SQL interface with LogMiner
6. Perform physical data structure integrity checks on an offline (for example,
backup) database or datafile with DBVERIFY.
Oracle Utilities [Cont’d]
 Maintain the internal database identifier (DBID) and the database name
(DBNAME) for an operational database, using the DBNEWID utility
 Data Pump Export
• Data Pump Export is a utility for unloading data and metadata into a set
of operating system files called a dump file set.
• The dump file set can be moved to another system and loaded by the
Data Pump Import utility.
• The dump file set is made up of one or more disk files that contain table
data, database object metadata, and control information.
• The files are written in a proprietary, binary format, which can be read
only by Data Pump Import.
• During an import operation, the Data Pump Import utility uses these
files to locate each database object in the dump file set.
Oracle Utilities [Cont’d]
 Data Pump Import
• Data Pump Import is a utility for loading an export dump file set into a
target system.
• The dump file set is made up of one or more disk files that contain table
data, database object metadata, and control information. The files are
written in a proprietary, binary format.
• Import can also be used to load a target database directly from a
source database with no intervening files, which allows export and
import operations to run concurrently, minimizing total elapsed time.
This is known as network import.
• Import also enables you to see all of the SQL DDL that the Import job
will be executing, without actually executing the SQL. This is
implemented through the Import SQLFILE parameter.
Oracle Utilities [Cont’d]
 Data Pump API
• The Data Pump API provides a high-speed mechanism to move all or part of
the data and metadata for a site from one database to another.
• To use the Data Pump API, you use the procedures provided in the
DBMS_DATAPUMP PL/SQL package.
• The Data Pump Export and Data Pump Import utilities are based on the Data
Pump API.
 Metadata API
• The Metadata application programming interface (API), provides a means for
you to do the following:
• Retrieve an object's metadata as XML
• Transform the XML in a variety of ways, including transforming it into SQL
DDL
• Submit the XML to re-create the object extracted by the retrieval
Oracle Utilities [Cont’d]
 SQL*Loader
• SQL*Loader loads data from external files into tables of an Oracle database.
• It has a powerful data parsing engine that puts little limitation on the format of the
data in the datafile.
 External Tables
• The external tables feature is a complement to existing SQL*Loader functionality.
• It lets you access data in external sources as if it were in a table in the database.
 LogMiner
• Oracle LogMiner enables you to query redo log files through a SQL interface.
• All changes made to user data or to the database dictionary are recorded in the Oracle
redo log files.
• Therefore, redo log files contain all the necessary information to perform recovery
operations.
• LogMiner functionality is available through a command-line interface or through the
Oracle LogMiner Viewer graphical user interface (GUI).
Oracle Utilities [cont’d]
 DBVERIFY Utility
 DBVERIFY is an external command-line utility that performs a physical data
structure integrity check.
 It can be used on offline or online databases, as well on backup files.
 You use DBVERIFY primarily when you need to ensure that a backup database
(or datafile) is valid before it is restored or as a diagnostic aid when you have
encountered data corruption problems.
 Because DBVERIFY can be run against an offline database, integrity checks are
significantly faster.
 DBVERIFY checks are limited to cache-managed blocks (that is, data blocks).
Because DBVERIFY is only for use with datafiles, it will not work against control
files or redo logs.
 There are two command-line interfaces to DBVERIFY. With the first interface,
you specify disk blocks of a single datafile for checking. With the second
interface, you specify a segment for checking.
Oracle Utilities [Cont’d]
 DBNEWID Utility
 DBNEWID is a database utility that can change the internal, unique
database identifier (DBID) and the database name (DBNAME) for an
operational database. The DBNEWID utility lets you change any of the
following:
a) Only the DBID of a database
b) Only the DBNAME of a database
c) Both the DBNAME and DBID of a database
 Therefore, you can manually create a copy of a database and give it a
new DBNAME and DBID by re-creating the control file, and you can
register a seed database and a manually copied database together in
the same RMAN repository.