Oracle Architecture and
The Oracle Architecture
The Oracle server consists of physical files and memory components.
The Oracle 10g Database product is made up three main components
The Oracle Server This is the Oracle database management system
that is able to store, manage and manipulate data. It consists of
all the files, structures, processes that form Oracle Database 10g.
The Oracle server is made up of an Oracle instance and an Oracle
The Oracle Instance Consists of the memory components of Oracle
and various background processes.
The Oracle database This is the centralized repository where the
data is stored. It has a physical structure that is visible to the
Operating system made up of operating system files and a logical
structure that is recognized only the Oracle Server.
The following figure displays the architecture of the Oracle
Database 10g. It is broadly divided into the memory components which
form the Oracle instance and the physical database components where
different kinds of data are stored.
The Oracle Instance
The Oracle instance is made up of a number of memory-related
components and background processes. The instance is the created in
memory every-time the database is started. The instance is
associated to only a single database at any time. The memory
components of the Oracle Database 10g are also known as the System
Global Area (SGA).
The instance and its components are configured using a file known as
the Parameter file.
In Oracle 10g there are two types of initialization files namely the
'Server Parameter file' (spfile) and 'The Parameter file' (pfile).
The parameter files hold parameters that can set the sizes of the
various buffers and pools of the SGA. You can also specify the name
of the instance, the name of the database and other size-related
parameters needed by the Oracle instance.
A typical parameter setting would be parameter_name =
For e.g. DB_NAME=moviedb
Here the name of the database is moviedb.
Memory Components of the SGA
The Oracle Instance is made up of the System Global area (SGA) and
processes. The SGA is made up of the
Redo Log Buffer
The Database Buffer cache
The Shared Pool
The Java Pool
The Large Pool
The Streams Pool
Redo Log Buffer A circular
buffer that stores all changes made in the database. It contains are
transferred periodically from memory to the online redo log files on
disk by the Log Writer (LGWR) background process. The contents of
the redo log buffer are essential for instance recovery purposes.
The size of the redo log buffer is determined by the LOG_BUFFER
The Database Buffer Cache
An area in memory that holds all the blocks read in from disk for
query or modification. Blocks that need to be modified, are modified
in memory and are written back to disk periodically. Modified blocks
that have not yet been written to disk are known as dirty blocks.
This buffer is managed in a manner that free blocks are always made
available for new blocks being read into memory. The contents of the
database buffer cache are shared by multiple user processes. The
contents of the database buffer cache are written to datafiles on
disk by the Database Writer (DBWR) background process. The size of
the default database buffer cache is sized by the DB_CACHE_SIZE
initialization parameter. The blocks that are part of the Database
buffer cache are sized by the DB_BLOCK_SIZE initialization
parameter. This is the default buffer cache.
Other caches can be created in addition to the default database
buffer cache: they are the Keep and Recycle cycle buffers. Oracle
uses the technique known as LRU algorithm, to create space for new
in-coming blocks that need room in buffer cache. The oldest blocks
are the first ones to leave. There maybe certain blocks that should
be retained in memory for a longer period of time, since they are
frequently accessed. The Keep cache is used to hold such. Though
they are also subject to the LRU algorithm, blocks are not released
as quickly as they would have in the default cache. Blocks from
standard and frequently accessed packages should be placed in the
keep cache. The size of the Keep cache can be specified by the
DB_KEEP_CACHE_SIZE initialization parameter. The Recycle cache on
the otherhand is used to hold blocks of objects that should be
released as soon as they are used if it is unlikely that they will
be reused. You can specify which cache will be used by an object
when the object is created.
The Shared Pool The
contents of this memory area are shared by multiple users and hence
the name shared pool. There are two specific caches form the shared
pool. The sizes of the two caches are not set individually and are
automatically determined by the Oracle Server. However it is
possible to set the overall size of the Shared pool by using the
SHARED_POOL_SIZE initialization parameter.
The Large Pool This is an
optional pool. The size of this memory area is determined by the
LARGE_POOL_SIZE initialization parameter. It is used for handling
large I/O requests of server processes. Its main functions are to
provide memory for session memory (UGA) for the shared server
environment, parallel execution message buffers (when
PARALLEL_AUTOMATIC_TUNING is set to TRUE). It is also used by the
Recovery Manager (RMAN) for its functioning when the BACKUP_DISK_IO
=n and BACKUP_TAPE_IO_SLAVE=TRUE initialization parameters have been
set. This pool does not use the LRU algorithm.
- Library Cache It is
responsible for storing and reusing frequently used SQL
statements, their execution plans and stored procedures. This
cache prevents SQL statements from being repeatedly parsed thus
improving performance during execution of SQL statements. When a
statement is executed, Oracle looks for an identical statement /
execution plan in the library cache. If one is found it is
reused, if not a new parse tree and execution plan are created.
The library cache also holds frequently accessed PL/SQL blocks.
- The Data Dictionary Cache
Sometimes referred to as the Row Cache. The cache consists of
blocks that hold data dictionary information read in from the
datafiles. Reads that are made to the data dictionary
information in the datafiles are also called recursive reads.
Typical information stored in this cache is user account
information, table, index and other object definitions,
privileges and other relevant information that is frequently
accessed. This cache is also managed using the Least Recently
Used (LRU) Algorithm, which is on a first-in first-out basis.
The Java Pool This memory
area is used by all session-specific Java code and data within the
Java Virtual Machine (JVM). Its size is determined by the
JAVA_POOL_SIZE initialization parameter.
The Streams Pool This
memory area is used by the Oracle Streams Product for its
Shared Memory Management
In Oracle 10g, a new feature known as Automatic Shared Memory
Management has been introduced. Using this feature all you need to
specify is the total amount of memory that will be used by all the
SGA components. The database then uses this value to redistribute
memory between the various memory components based on the current
workload. The parameter that needs to be configured for automatic
shared memory management is the SGA_TARGET initialization parameter.
In Oracle 10g, the SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE
and DB_CACHE_SIZE are known as the auto-tuned SGA parameters. If you
set the value for SGA_TARGET to a value greater than zero then the
total amount of memory to be allocated for the auto-tuned parameters
is obtained by subtracting the SGA_TARGET value from the total
amount of memory for manual SGA parameters.
Setting the SGA_TARGET to a value of zero will result in disabling
automatic shared memory management. The ASSM feature uses the MMAN
background process, which is responsible for coordinating the sizes
of the various pools dynamically based on the current workload in
The background processes of the Oracle instance are responsible for
performing asynchronous I/O functions between the Oracle Instance
and the physical files of the Oracle database that exist on disk.
There are 5 mandatory background processes in Oracle 10g. These are
the Database Writer (DBWR), the Log Writer (LGWR), the Checkpoint
process (CKPT), the System Monitor (SMON) and the Process Monitor
(PMON). Other background processes can be started based on certain
additional functionality required in the database. Given below is a
list of the most commonly started background process and the actions
they are responsible for in the database.
||Description and Function
|This background process is
mandatory. Its function is to transfer modified blocks from
the database buffer cache to the datafiles.
The DBWR writes to the datafiles when one of the following
events occur in the database:
Normal or incremental checkpoint
The number of dirty buffers reaches a pre-defined
The server process scans a pre-defined number of blocks
looking for free space needed by new blocks being read in.
Tablespace is taken offline in normal or immediate mode.
Tablespace is made read-only
Dropping or truncating a table
Online backup is done on a tablespace using ALTER
TABLESPACE... BEGIN BACKUP command.
||This background process is
mandatory. It transfers redo entries that have been written
to the redo log buffer to the online redo log files present
The LGWR writes to the online redo log files when one of the
following events occur in the database:
A commit is issued.
The Redo Log Buffer is one-third full.
More than 1MB of changes (updations) has been made in the
Every three seconds
Just before the DBWR writes from the database buffer cache
to the datafiles.
||This background process is
When the contents of the database buffer cache are
transferred to the datafiles by the DBWR background process,
the event is referred to as a CHECKPOINT. When a checkpoint
occurs the Checkpoint (CKPT) background process updates the
headers of the datafiles and control files to indicate the
most recent checkpoint.
This process is therefore responsible for synchronizing the
|This background process is
mandatory. It is responsible for performing recovery in the
event of an instance failure.
Instance failure occurs during an abnormal shutdown of the
database, such as if a shutdown is performed using the ABORT
option. During instance recovery the SMON process performs a
roll forward operation. During this step all committed
transactions that were not written out to the datafiles at
the time of failure were be completed. This step maybe
followed by a rollback during which time either the SMON or
individual user processes undo all the changes that were
uncommitted during the time of failure. Certain additional
functions performed by the System Monitor involve coalescing
of free space in dictionary-managed datafiles and releasing
temporary segments that are created in the datafiles.
|This background process is
mandatory. Users connect to the database to access data.
When a user connects to the database, a process known as a
User process is started on behalf of the user on the client
If the user terminates his/her session abnormally, resources
that were being used by the user session continue to remain
attached until the PMON background process cleans it up.
The PMON background process is responsible for cleaning the
resources such locks on tables being held by failure user
The process also restarts dead dispatcher processes.
|This is an optional
background process. It can be started when you wish to
operate the database in an archivelog mode.
This mode of database operation allows for complete recovery
of the database in an event of a failure.
The ARCH background process transfers the contents of the
online redo log files to archival media which could be disk
|The other optional processes
in Oracle 10g are the :
|CJQ0 Coordinator Job Queue
Dnnn Dispatcher process
Pnnn Parallel Query Slaves
QMNn Advanced Queuing
Snnn Shared Server Processes
MMON Memory Monitor
MMAN and MMNL.
A user trying to access data in the database needs to make a
connection with the database using an appropriate tool such as
SQL*Plus, iSQL*Plus and a front-end database interface tool. A
connection is a communication pathway created between a user process
and the Oracle Server. The user has to first supply proper
Once the user's credentials are validated a user session is
established and a user process is created on the client-side to act
on behalf of the user. This process remains active till the user
completes the session. In the event that a user process is
terminated abnormally the PMON background process cleans up the
resources that were held by the user process.
Server processes are created in Oracle to handle requests made by
user processes. Server processes are always started on the Server
side. They are responsible for responding to the user process with
appropriate results. Based on the mode in which a database operates
either a 1:1 ratio or m:n exists between user and server processes.
In Oracle, the instance can operate in one of two modes:
- Dedicated server
mode: Once a user process is started, a server process
is created on behalf of the user process. A one-to-one ratio is
maintained between user and server processes. The server process
remains attached to the user process as long as the user process
is active. The server process remains idle during periods of
inactivity. The server process is responsible for satisfying all
requests made by the user process and responding with
appropriate results. The server process communicates with the
Oracle server using the Oracle Program Interface (OPI). The
disadvantage of this is that in an OLTP environment where there
can be many thousands of users accessing the database
simultaneously, a large number of server processes would need to
- Shared Server Mode:
In this environment a pool of shared server processes
satisfy the required of user processes. A one-to-one ratio is
not maintained as in the dedicated server mode. A user process
can be serviced by any available server process. This mode of
operating is particularly useful in large multi-user online
transaction processing (OLTP) environments, where many users
concurrently access the database using short transactions.
The Program Global Area (PGA)
This is memory area that is associated with a server process. It
contains data and control information held by a single server
process or background process. It is created when a user process is
created and is released when the user process is terminated. This
memory area is not shared by server processes. The contents of the
PGA however vary in dedicated and shared server environments.
The PGA is used to process SQL statements and to hold logon and
other session information. The contents of the PGA include:
- Private SQL area
contains bind information and runtime memory structures. Each
session that issues a SQL statement has a private SQL area. The
private SQL area is in turn made up the Persistent area that
contains bind information and a Runtime area that is created as
soon as an execute request is initiated. In a dedicated server
environment the Private SQL area is created in the Program
Global Area (PGA) of their server process. In a shared server
environment, it is located in the System Global area. The number
of private SQL areas that a user process can allocate is limited
to the OPEN_CURSORS initialization parameter that takes a
default value of 50.
- Session Memory
This is a memory area that holds a session's variables and
session-related information. In a shard server environment, this
memory area is located in the SGA and is shared by server
- SQL Work area
This memory area is used for operations such as Sorting,
Hashjoins, Bitmap Merge and Bitmap Create operations.
The Physical Files of the Database
Recalling an earlier discussion, the Oracle Server is made up of the
Oracle Database and Oracle Instance. The Oracle database has a
physical and a logical structure. The physical structure of the
database is made up of the operating system files that comprise the
database. All the data of the database is stored in the physical
files. Storage management is a one of the primary functions of the
database administrator. In Oracle Database 10g, there are three
types of files namely:
These files contain the data of the database. All objects
created in the database by users have their data physically
stored in the datafiles. Datafiles contain the data dictionary
and user created data. In order to access the data that is
physically stored in the datafiles and reduce the overhead of
disk I/O, blocks from the datafiles are read into the database
buffer cache in memory. Once they are modified or are no longer
needed they are written back to the datafiles.
- Control Files
This is a very important file that is required for the Oracle
database to function. If any one of the control files is
unavailable the database is shutdown. Hence it recommended that
multiple copies of the control file are maintained in the
database on separate disks. The control file keeps a record of
the names, size and locations different physical files of the
database. It also hold other control information necessary to
keep the database functioning. This file is size by the values
specified for a number of parameters during
database creation, namely MAXLOGMEMBERS, MAXLOGFILES,
MAXLOGHISTORY, MAXDATAFILES and MAXINSTANCES.
The following is a list of the entries maintained in the control
- The database identifier and name
- Timestamp of database creation
- Tablespace names
- Names and locations of data files and online redo log files
- Current online redo log file sequence number
- Checkpoint information as and when checkpoints are made
- Begin and end of undo segments
- Redo log archive information
- Backup information generated by the recovery manager
- Redo Log Files
These files contain a record of all the changes made in the
database. These changes are referred to as redo entries. Redo
entries can be used to recover the database in the event of an
instance failure. Instance failure can occur when the contents
of the SGA are lost such as in the event of a power outage or an
abnormal shutdown of the database. Any change made in the
database is first recorded in the online redo log buffer before
it is actually implemented. Hence even if modified blocks were
not written in the event of an instance failure, the changes can
be recovered by using the online redo log files. In Oracle 10g,
a minimum of two redo logs should be available. These redo logs
files are known as redo groups.
Since the redo log files are very important for recovery
purposes, Oracle recommends that redo logs be multiplexed.
Multiplexing is maintaining multiple copies or additional
members. In other words, each group should have more than one
member. Members of the redo log files should be off the same
size and placed on separate disks. This will prevent loss of
redo data in the event of the loss of a disk.
Redo log groups are used in a circular fashion. Consider the
figure. There are two disks DISK1 and DISK2. There are two redo
log groups GROUP1 and GROUP2.
GROUP1 has two members Rlog1 and Rlog1b. GROUP2 has two members
Rlog2a and Rlog2b. When the LGWR performs a write, it first
writes simultaneously to Rlog1a and Rlog1b. When the group
becomes filled, it starts writing simultaneously to Rlog2a and
Rlog2b. The contents of the members are identical. Now, if one
of the disks is lost as a result of failure, the data is still
available in a member on another disk.
Since the contents of the redo log files are very important for
recovery purposes, you can transfer the redo entries to another
media before overwriting them. This process is known as
archiving. Archiving can be done automatically every-time a redo
log file becomes full and LGWR starts writing to another file.
The event when LGWR stops writing to one file and starts writing
to another is called a Log Switch. Archiving helps achieve
complete recovery in the event of media failures.
- Other physical files relevant to the database include:
- The password file
This file is used to hold the names of privileged users who
have been granted the SYSDBA and SYSOPER roles. These roles
contain the privileges needed to perform actions such as a
startup, shutdown or recovering the database. The password
will is used only when you use database authentication i.e.
the database is responsible for validating user credentials.
- The parameter file
The parameter file is a file which contains a number of
parameters that can be used to configure the Oracle 10g
instance. There are two kinds of parameter
files available. The first one is called the Server parameter
file (spfile) introduced in Oracle 9i, which allows you to
specify the values to parameters that can be enforced without
bouncing the database as well as after re-starting the
database. These are called persistent parameters. The second
kind of file is called Parameter file (pfile) which has been
around for a while now. It holds parameters and their values
however any change to a parameter's values requires you to
bounce or restart the database.
- The archive files
These are files that can be created by a process known as
archiving. When a database operates in archivelog mode, the
contents of the redo log file are transferred to an offline
file known as the archive file. This file is very useful in
the event of a failure and is used for recovery purposes.
Usually complete recovery up to the point of failure is
possible if all archive files are available. Archive files can
be created on disk and transferred to tape to create space for
new archive files.
Parameter Files in Oracle
The Oracle 10g server is made up of the Oracle Instance and the
Oracle database. The Oracle instance is created in real memory. Most
actions are first performed in memory and then transferred to disk.
It is therefore very important the Oracle instance be configured
properly. When a database administrator starts the Oracle database,
the first step involves creating the instance in memory. Various
memory structures and background processes are started.
To create the instance the Oracle server uses a file known as the
Initialization Parameter File. There are two types of initialization
The Static parameter file, PFILE, known as the initSID.ora file
Persistent parameter file, SPFILE, known as the spfileSID.ora file
The initialization parameter file consists of a list of parameters.
Each parameter has a name and a value. Most parameters have default
values. Explicit values can also be assigned to the parameters. The
initialization parameter file specifies information such as the
values for various memory structures of the instance, the database
name, the number of processes the can concurrently access the
instance, parameters associated with archiving and so on. A
comprehensive list of initialization parameters has been displayed
along with a brief description.
In Oracle 10g, the number of initialization parameters to be
configured has been greatly reduced compared to previous versions of
Oracle. Parameters have been categorized into basic and advanced
parameters. The basic parameters are a list of parameters that are
sufficient for day-to-day interaction. The most important ones
||Refers to the version of the
server with which the instance should be compatible.
|The maximum number of
operating system processes that can connect simultaneously
to the instance.
It is possible to dynamically change certain initialization
parameter. The change will however only affect the current instance.
Dynamic changes are done by using the ALTER SYSTEM command. An
example of a dynamic change that will affect the current instance
ALTER SYSTEM SET SGA_TARGET = 300;
This change will affect only the current instance. When the database
is restarted, the previous value present in the parameter file will
Any permanent change you make to a parameter requires that the
database be restarted. By default the parameter file is located in
the %ORACLE_HOME%\dbs directory. It has a name like initSID.ora
where SID is the system identifier of the database. It can be quite
difficult to remember the names of all the parameters and set them
properly. There is a sample file created in the Oracle database,
which can be copied and modified based on your requirements. It is
possible to create multiple initialization parameters files however
only one will be active at any time.
Persistent Parameter File
The persistent parameter file often called the spfile was introduced
in Oracle 9i. It is located at the server-side. The spfile cannot be
modified by the user and any manual change to the file will render
it useless. It is also located in the %ORACLE_HOME%\dbs directory.
When using the Database Configuration Assistant, you are given the
option of creating an SPFILE for the database. This option is not
available when you create a database manually using the CREATE
DATABASE command. If you are using the SPFILE file, it can be backed
up using Recovery Manager, since it is located on the server side.
In the case of the static parameter file any permanent change to a
parameter would require a bounce of the database. Using the spfile
you can modify parameters such that the change will not only affect
the current instance but also affect future instances of the
database. Hence the term persistent.
When changing a parameter dynamically, you can set the SCOPE option.
The values taken by the SCOPE option are MEMORY, SPFILE or BOTH. The
value BOTH is the default. When you set SCOPE=MEMORY, the change you
are making will only affect the current instance. If you set
SCOPE=SPFILE the change will be made in the parameter file and will
come into effect when the database is restarted. If you set
SCOPE=BOTH then the change will affect both the current instance and
will affect future instances for the database. An example of a
dynamic change only affecting the spfile is displayed below.
ALTER SYSTEM SET
ALTER SYSTEM command and the SPFILE file
The ALTER SYTEM SET command is used to modify the values of instance
parameters after the instance has been created. The complete syntax
of its usage is displayed below:
ALTER SYSTEM [SET|RESET] parameter_name=parameter_value [COMMENT
'text'] [SCOPE = MEMORY|SPFILE|BOTH] [SID='sid' | '*']
Parameter_name : the name of the parameter to be changed
Parameter_value : the new value to be assigned to the parameter
COMMENT: A comment to be added to the SPFILE next to the parameter
that is being altered.
SCOPE: Determines if the change should be made in MEMORY, SPFILE or
MEMORY: The change affects only the current running instance
SPFILE: The change affects only the SPFILE file
BOTH: The change affects both the current instance and the SPFILE.
SID : Identifies the system identifier (ORACLE_SID) for the SPFILE
being used currently.
'sid': Specific SID to be used in altering the SPFILE
'*': Uses the default SPFILE
RESET: This causes the parameter to revert to its default value
Example of dynamic changes the may or may not affect the changes:
1. Modify the size of the default database buffer cache to 20M, both
for the current and future instances.
ALTER SYSTEM SET
CREATING THE PARAMETER
As mentioned earlier it the DBCA tool gives us the option of
creating an SPFILE, for the database, during database creation.
However when a database is created manually, only the PFILE can be
created. If you wish to create an SPFILE using a PFILE you can do so
by issuing the command:
CREATE SPFILE [= 'location of the spfile'] FROM PFILE [= 'location
of the pfile'] ;
If you do not specify the location, which is optional it will be
created in the default location %ORACLE_HOME%\dbs directory.
It is possible to create the PFILE from the SPFILE by using the
CREATE PFILE FROM SPFILE;
An editable parameter file will be created in its default location
if a path is not specified.
Retrieving the initialization parameter values using the data
dictionary Parameter values stored in the Static parameter file can
be viewed using the V$PARAMETER view. The parameter values from the
Persistent parameter file can be viewed by issuing a query against
V$SPPARAMETER data dictionary view.
Starting and Stopping the Oracle Database
A database needs to be started before it can be accessed by users.
Starting up a database should be done by a privileged user. Users
who have been granted the SYSDBA or SYSOPER roles can perform a
startup or a shutdown of the database. When a database is started
three important steps are executed. The first involves creation of
the Oracle instance in memory, followed by mounting the database
where the control files are read and finally opening the database
where all the data stored in the database is accessible to the users
of the database. To get a complete understanding let us deal with
each stage individually.
1) Instance Creation This is the first step in starting the
database. The initialization parameter file is read, (spfile or
pfile depending on the options used in the STARTUP command) and the
System Global Area (SGA) is configured. The parameter file
identifies the name of the database, and various sizes for the
memory pools, the optional background processes and so on. During
instance creation, the mandatory and optional background processes
are started. The alert log file and other trace files are also
2) Mounting the Database After instance creation, the database is
mounted. When a database is mounted a database administrator can
perform certain maintenance or administrative tasks. For e.g.
putting a database in archivelog mode, renaming datafiles or
performing a full database recovery requires the database to be
mounted. During mounting, a database is associated with its
previously started instance. The control files of the database are
opened and read. The control file contains information about the
other files of the database, their status, location and
synchronizing information. This information is needed for the next
stage of startup which is opening of the database.
3) Opening the database This is the last stage in starting a
database. This phase has to be performed so that users of the
database can access the data in the database. Once the control file
has been read and the location of the physical files of the database
identified after mounting, the files are opened and made available
to the users. The files that are opened are the online datafiles and
the online redo log files. If any of the files are unavailable, an
error will be reported and the database will not be opened. It is at
this point that the Oracle server verifies the consistency of the
database. In case the database was shutdown improperly the last
time, it will be detected at this point and the SMON background
process will perform instance recovery.
The syntax of the STARTUP command with its important options has
STARTUP [FORCE] [RESTRICT] [PFILE= filename] [OPEN [RECOVER]
[database] [READ ONLY] | MOUNT | NOMOUNT]
FORCE: Used when a startup does not occur as a result of some kind
of problem, you can use this option to abort the running instance
and perform a normal startup.
RESTRICT: This option is used when you want only users who possess
the RESTRICTED SESSION privilege to access the database. This option
can be used is you wish to perform some kind of maintenance on the
database such as an export and import.
PFILE=filename: this option starts up the database using the static
non-default parameter file to startup the database.
OPEN [database_name]: The instance will be created; database will be
mounted and then opened for all the users. You can optionally
specify the database name. This is the default option.
MOUNT: The instance will be created and the database mounted. The
option can be used for performing certain maintenance tasks.
NOMOUNT: Only the instance is created. The SGA will be created in
memory and the background processes will be started. You may have to
use this option for certain operations such as when creating a
RECOVER: used to perform media recovery when starting the database.
READ ONLY: used to open the database for queries only. DML
statements are not allowed in the database.
You can also start a database using the EM Console.
ALTER DATABASE Command
If you have started the database using the NOMOUNT option, only the
instance is created. In order to then mount / open the database you
can use the following ALTER DATABASE command. It is not possible to
re-issue the STARTUP command with the MOUNT or OPEN options.
Current state of the database: NOMOUNT.
To change it to the MOUNT state, issue:
DATABASE [database_name] MOUNT;
To change it to the OPEN state directly, issue:
DATABASE [database_name] OPEN;
Current state of the database: MOUNT
To change it to the OPEN state, issue:
DATABASE [database_name] OPEN;
It is possible to open a database in a read only or read write mode
using the ALTER DATABASE command. To startup the database in a read
only or read write mode from a NOMOUNT or MOUNT stage you can issue;
ALTER DATABASE OPEN [READ ONLY | READ WRITE];
READ ONLY only queries are allowed on the database.
READ WRITE the database is opened for normal read and writes.
DOWN THE DATABASE
A database may be shutdown to make it unavailable for use. You can
either perform a proper or improper shutdown. During a proper
shutdown, three phases complementary to the startup are performed in
the reverse order. First the database is closed; this involves
performing a checkpoint on all available datafiles and closing the
files. Next the database is dismounted. At this time, the control
file is synchronized and closed. Finally the instance that was
created in memory is released. The SGA no longer exists in memory
and all background processes are stopped.
The syntax of the SHUTDOWN command
SHUTDOWN [NORMAL | IMMEDIATE | TRANSACTIONAL | ABORT]
NORMAL: This is the default mode for shutting down the database. The
Oracle server waits for all currently connected users to disconnect
their sessions. No new connections are permitted. A checkpoint is
performed on all the databases and the files are closed. When a
database is shutdown is this mode, an instance recovery will not
need to be done during the subsequent startup.
IMMEDIATE: When a database is shutdown in this mode, the Oracle
automatically rolls back all currently active transactions. After
the transactions have been rolled back the user sessions are
terminated. No new connections are allowed. The database is then
closed, dismounted and the instance released. No instance recovery
will be performed during subsequent startup.
TRANSACTIONAL When the database is shutdown using this option, all
currently active transactions will be allowed to complete. As soon
as a user's transaction completes the user is automatically
disconnected. No new user connections will be allowed. The database
is then closed, dismounted and the instance released. No instance
recovery will be performed during subsequent startup.
ABORT When the database is shutdown using this option, the
instance is shutdown down. This is a case of an improper shutdown.
No checkpointing is done. All user connections are abnormally
terminated. The database is not closed or dismounted. However, the
next startup will require an instance recovery to be performed by
the SMON background process.
Command and Parameter files
The command that is used to startup a database is:
If the database has an spfile, by default the spfileSID.ora will be
used to configure the instance. If the spfileSID.ora file does not
exist, Oracle will look for a default SPFILE on the server side. If
one cannot be found the initSID.ora file on the server side will be
used. There may be times when you wish to startup the database using
the PFILE. To do so, you issue the STARTUP command with the
PFILE='filename' option as shown:
SQL> STARTUP PFILE='location of the
If the SPFILE for a database is not located in its default location
%ORACLE_HOME%\dbs, then an entry in the PFILE pointing to it
location can be specified.
Example: SPFILE=<location of the SPFILE>