Characteristics of Oracle Server

Oracle server

An Oracle server, a database management system, consists of an Oracle instance and an Oracle database.

The instance and the database together constitute an Oracle server.

The architecture of Oracle Server can be described in three phases:

    User login to the database
    Memory structures that are part of the Oracle instance
    Background processes that are part of the Oracle instance
    Physical structures of files forming the Oracle database.

User login to the database

Two processes allow a user to interact with the instance and ultimately with the database: the user process and the server process.


Each time a user runs an application, such as an application for human resources management, financial management, or just an SQL command, the client machine first launches a user process to establish a connection from the user to the Oracle instance.
The Oracle Listener Listening Process

The Oracle listener process is the primary server-side Oracle component that establishes the connection between client computers and an Oracle database. The listener can be seen as a big ear that listens for connection requests to Oracle services.

Theoretically, a server machine can host several Oracle databases and one listener and only one to allow a client to connect to the Oracle instance of his choice. The instance name is submitted by the client during the connection process (step 1).

Two cases are possible:

    A Listener process can serve multiple databases configured on the same server machine,
    Several listener can be configured on the same machine (for failover or load balancing purposes to support heavy loads of connection requests).

In a dedicated server configuration, the Listener starts a new server process for each client and gives it control of the client session. Each client connection is served by its own server process.

The diagram above corresponds to a dedicated server configuration and for a client / server application.

The connection process goes through the following steps:

    The customer contacts the Oracle listener by choosing the instance to which he wishes to connect (request for a service name).
    The listener starts a dedicated process called a server process
    The listener sends an acknowledgment to the client with the address of the dedicated server process
    The client establishes a connection with the dedicated server process
    The server process connects to the Oracle instance on behalf of the user process (creation of a user session)

It is the server process that connects to the Oracle instance to serve the user process throughout the client session.

The user process does not directly interact with the Oracle server. Rather, it is the server process that interacts with the Oracle server, responds to user requests and returns results to the user.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Oracle Database?

Oracle Database

Oracle Database  is one product among others forming the Oracle  suite (Oracle Database, Oracle Application Server, Oracle Developer Suite, Oracle Enterprise Manager Grid Control…).

Oracle Database  is the Oracle database management system that allows multiple users to access data simultaneously while ensuring availability and disaster recovery mechanisms.

Be aware that Oracle Database refers to the entire Oracle software, while database has a specific meaning in the Oracle architecture.

To guarantee a high level of performance, Oracle maintains the database thanks to memory structures (in random access memory: RAM) and physical structures (on hard disk) and uses processes for the storage of data in memory or on disk .

Writing to disk is only performed when necessary and under conditions while Memory is used as much as possible since memory access is faster than disk access (performance gain).


In the event of a power outage, for example, Oracle will be able to recover well if data in memory, at the time of the incident, is not written to disk. This is the recovery of the instance managed by the SMON process discussed later in the oracle architecture.

An instance failure requires recovery, while a physical failure (such as a disk crash) requires a restore followed by a recovery.

Restoration and recovery are two different terms that should be clearly distinguished. The restoration is made from backup files and the recovery allows to reapply to the restored data files, the modifications already made on the base to find the consistent state of the database before the incident.


In the example in the figure, recovery at time t2 requires restoring the last available S1 backup and reimplementing any changes that occurred in the interval t1-t2.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Cursors in Oracle?

What is the Cursors?

A cursor is a pointer to a private SQL memory area allocated (in PGA memory) for processing an SQL statement. The cursor is used to process one by one the records (rows of tables) returned by the SQL statement in question.

Two types of cursors can be distinguished:

    Implicit Cursor: When a user issues an SQL command, Oracle generates a cursor for processing that command. This cursor created and managed by Oracle is called an implicit cursor.
    Explicit cursor: if you want to manage an SQL command within your PL / SQL code, you can explicitly create a cursor to process the rows returned by the command one by one.

Here is an example :

DECLARED
name emp.ename% TYPE;
salary emp.sal% TYPE;

CURSOR C1 IS SELECT ename, NVL (sal, 0) FROM emp;

BEGIN
OPEN C1;

LOOP
FETCH C1 INTO name, salary;
EXIT WHEN C1% NOTFOUND;
DBMS_OUTPUT.PUT_LINE (name || 'earns' || salary || 'dollars');
END LOOP;

CLOSE C1;
END;

Explanations:

CURSOR C1: used to declare a cursor with the name C1
name emp.ename% TYPE means that the variable name is of the same type as the ename column of the emp table (Another predefined type% ROWTYPE can be used to declare a record variable of the same type as the record of a table)
Open C1: open the cursor
Fetch: fetch the current record and load it into the name and salary variables and then advance the pointer to the next record
C1% NOTFOUND: returns TRUE if the cursor points beyond the last record. Other attributes of the cursor can be used:

    % FOUND,
    % ROWCOUNT which returns the number of rows returned by the SQL order of the cursor,
    % ISOPEN which returns true if the cursor is open.

Close C1: used to close the cursor and free the resources.

For advance topics of Oracle DBA tutorials and tips. Stay connected with our following blogs. Dba tips and Dbametrix.