Step 5 - Accessing the database
Learn Oracle - Accessing the Database
The database instance can be started (made available) only by an authorized database
administrator who has a special type of connection privilege to the Oracle instance.
After the database instance has been started, it is usually open for access by normal
users for whom database user accounts have been created.
This section describes the process of starting the Oracle instance and database and the
creation of server processes that enable user access.
How the Oracle Instance and Database are Started
The administrator who starts up the Oracle instance and database must connect to the
instance with a special kind of connection privilege. There are two of these privileges:
SYSDBA for fully empowered database administrators and SYSOPER for users who
operate the database, but have no privileges to access user objects. Authorization to
use these privileges occurs either through the operating system or through a special
When Oracle DBA create an Oracle database, there are two primary administrative user
accounts who are automatically created: SYS and SYSTEM. Both of these users have
full database administration privileges granted to them, but only user SYS can initially
connect with the SYSDBA privilege.
The Startup Process
The process of starting the instance and database is as follows:
Oracle DBA start the instance in one of the following ways:
Use Oracle Enterprise Manager. See "Shutting Down and Restarting the
Instance and Database".
Use the STARTUP statement using SQL*Plus.
On Windows, Oracle DBA can start the Oracle services. See "Starting and Shutting
Down the Database Instance on Windows".
Oracle reads the initialization parameter file, allocates SGA memory, and starts the
background processes for the instance.
. If Oracle DBA also specify that the database is to be mounted, then the instance opens the
database control file. The database is now said to be in the mount state. This state
enables administrators to perform certain administrative functions that cannot be
performed when other users are accessing the database.
. If Oracle DBA specify that the database is to be opened, then the instance opens the redo
log files and datafiles for the database. The database is now open and available for
all user access.
The default startup behavior is to complete the three stages as described earlier.
Unless explicitly specified otherwise, the instance will be started, database
mounted and opened.
The Shutdown Process
The process is reversed when Oracle DBA shut down the database.
You issue the statement to shutdown the database by similar means as explained
in "The Startup Process" . Any modified data blocks that are cached in
the SGA and have not been written to disk are now written to disk. The redo log
buffer is similarly flushed. Datafiles are checkpointed and their headers are
Overview of an Instance and Instance Management
Datafiles and log files are
closed. Users can no longer access the database.
The Oracle instance dismounts the database and updates relevant entries in the
control file to record a clean shutdown. The control file is closed. The database is
now closed and only the instance remains.
The Oracle instance stops the background processes of the instance and
deallocates the shared memory used by the SGA.
Server and Client Processes
In addition to background processes, Oracle creates server processes that handle the
requests of user or client processes that connect to the instance. A user connection is
composed of two distinct pieces:
A client program acting on behalf of the user, such as Oracle Enterprise Manager,
SQL*Plus, or an application
A server process that handles the connection to the database on behalf of the client
In some situations when the client and Oracle operate on the same machine, it is
possible to combine the user process and corresponding server process into a single
process to reduce system overhead. However, when the client and Oracle operate on
different machines, a user process always communicates with Oracle through a
separate server process.
Server processes can be either dedicated or shared. In dedicated server mode, each
client process has its own server process. While a dedicated server process is good for
long running queries and administrative tasks, an idle process or too many dedicated
processes can result in an inefficient use of resources.
Shared server mode eliminates the need for a dedicated server process for each
connection. A dispatcher directs multiple incoming network session requests to a pool
of shared server processes. An idle shared server process from a shared pool of server
processes picks up a request from a common queue, which means a small number of
shared servers can perform the same amount of processing as many dedicated servers.
Also, because the amount of memory required for each user is relatively small, less
memory and process management are required, and more users can be supported.
Shared server mode is more efficient at supporting multiple users and clients making
frequent short-running queries.
Oracle Net is a layer of software that allows different physical machines to
communicate to accessing an Oracle database. A version of Oracle Net runs on the
client machine and on the database server. The client machine and the database server
are often the same machine.
Oracle Net enables the client and server to communicate over a network using many
popular network protocols, and it provides location transparency such that the client
machine does not need to know the serverís location. When the database is moved to
another location, Oracle DBA only need to reconfigure Oracle Net. No changes are necessary
to client applications.
Oracle Net must be separately configured and started for it to handle client
connections to the database. You can configure Oracle Net using Oracle Enterprise
Manager or with a separately launched GUI tool called the Oracle Net Manager.
When an instance starts, a listener process establishes a communication pathway to
Oracle. When a user process makes a connection request, the listener determines
whether it should use a shared server dispatcher process or a dedicated server process
and establishes an appropriate connection.
About Initialization Parameters
Instance management involves configuring parameters that affect the basic operation
of the database instance. These parameters are called initialization parameters. The
Oracle database server reads these parameters at database startup and monitors them
while the database is running. They are stored in memory, where many of them can be
changed dynamically. There are two types of parameter files, and whether these
dynamic changes are persistent across database shutdown and startup depends upon
the type of parameter file Oracle DBA are using.
Server parameter file
This, the preferred form of initialization parameter file, is contained in a binary file
that can be written to and read by the database server. It must not be edited
manually. It resides on the machine that Oracle is running on, and is persistent
across shutdown and startup.
Text initialization parameter file
This type of initialization parameter file can be read by the database server, but it
is not written to by the server. In this file, you can set initialization parameters
with a text editor for them to be persistent across shutdown and startup.