Chapter01 Oracle Architecture

Exploring Oracle Database Administration Oracle Architectural Components and Database Creation
Oracle Architectural Components and

Database Creation
This chapter provides an overview of the Oracle architecture and talks about the various
components that make an Oracle Instance. It also talks about the creation of database and
various modes of starting and shutting down a database.
SCOPE
1.1 Oracle Architecture and its main components
1.1.1 System Global Area Overview
1.1.2 Program Global Area (PGA) Overview
1.1.3 Oracle Processes Overview
1.2 Oracle Instance
1.3 Managing an Oracle Instance
1.4 Start up and shut down
1.4.1 Starting up the database
1.4.2 Starting up an instance
1.4.3 Shutting down a database
1.5 Alert Log File
1.6 Setup Password File Authentication
1.7 Oracle9i Installation Procedure
1.8 Configure OMF
1.9 Prerequisites for database creation
1.9.1 Planning for database creation
1.9.2 Meeting Prerequisites
1.10 Create and manage Initialization parameter files
1.10.1 Determine the global database name
1.10.2 Specify Control Files
1.10.3 Specify database block size
1.10.4 Setting Initialization parameters that affect the size of SGA
1.10.5 Specifying the Maximum Number of Processes
1.10.6 Specifying a method of Undo Space Management
1.10.7 Managing Initialization parameter files using a server parameter file
1.11 Creating a database manually
1.12 Create a database using Oracle Database Configuration Assistant
1.12.1 Advantages of using Oracle Database Configuration Assistant:
1.12.2 Creating a database
1.12.3 Global Database name and Parameters
1.12.4 Completing Database Creation
1.12.5 Deleting a database
1.13 Monitor the use of Diagnostic Files
© Copyright 2006, Tata Consultancy Services Limited (TCS). 1

1.1Oracle Architecture and its main components
An Oracle Database is comprised of physical files, memory areas and processes. The
distribution of these components varies depending on the database architecture chosen.
The data in the database is stored in physical files (called data files) on a disk. As it is
used that data is stored in the memory. Oracle uses memory areas to improve
performance and to manage the sharing of data between users. The main memory area
in the database is called the System Global Area (SGA). To read and write data between
SGA and data files, Oracle uses a set of background processes that are shared by all
users.
The basic memory structures of Oracle include:
System Global Area (SGA), which is shared by all server and background processes and
holds the following:
 Database buffer cache

 Redo log buffer
 Shared pool
 Large pool (if configured)
Program Global Areas (PGA), which is private to each server and background process;
there is one PGA for each process. The PGA holds the following:
 Stack areas
 Data areas
Fig. 1.1 – Oracle Memory Structure

1.1.1 System Global Area Overview
A system global area (SGA) is a group of shared memory structures that contain data
and control information for one Oracle database instance. If multiple users are

concurrently connected to the same instance, then the data in the instance’s SGA is
shared among the users. Consequently, the SGA is sometimes called the shared global
area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically

allocates memory for an SGA when you start an instance, and the operating system
reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can
read information contained within the instance’s SGA, and several processes write to the
SGA during execution of Oracle.
The SGA contains the following data structures:

 Database buffer cache
 Redo log buffer
 Shared pool
 Java pool
 Large pool (optional)
 Data dictionary cache
 Other miscellaneous information
Dynamic SGA
Beginning with Oracle9i, Release 1 (9.0.1), Oracle can change its SGA configuration
while the instance is running. With the dynamic SGA infrastructure, the sizes of the
buffer cache, the shared pool, and the large pool can be changed without shutting down
the instance.
Dynamic SGA also allows Oracle to set, at run time, limits on how much virtual memory
Oracle will use for the SGA. Oracle can start instances under configured and allow the
instance to use more memory by growing the SGA components, up to a maximum of
SGA_MAX_SIZE. If SGA_MAX_SIZE specified in INIT.ORA is less than the sum of
all components specified or defaulted at initialisation time, then the setting of
SGA_MAX_SIZE in INIT.ORA is ignored.
The size of the SGA is determined by several initialisation parameters. The parameters
that most affect SGA size are:
DB_CACHE_SIZE: The size of the cache of standard blocks.
LOG_BUFFER: The number of bytes allocated for the redo log buffer.
SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and
PL/SQL statements.
LARGE_POOL_SIZE: The size of the large pool; the default is 0.
1.1.1.1 Data Base Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read
from data files. All user processes concurrently connected to the instance share access to
the database buffer cache.
The database buffer cache and the shared SQL cache are logically segmented into
multiple sets. This organization into multiple sets reduces contention on multiprocessor
systems.
Size of the Database Buffer Cache
Oracle9i, Release 1 (9.0.1), supports multiple block size in a database. This is the default
block size—the block size used for the system table space. You specify the standard
block size by setting the initialisation parameter DB_BLOCK_SIZE.
Legitimate values are from 2K to 32K.
To specify the size of the standard block size cache, you set the initialisation parameter
DB_CACHE_SIZE. Optionally, you can also set the size for two additional buffer pools,
KEEP and RECYCLE, by setting DB_KEEP_CACHE_SIZE and
DB_RECYCLE_CACHE_SIZE. These three parameters are independent of one another
in Oracle9i, Release 1 (9.0.1).
The sizes and numbers of non-standard block size buffers are specified by the
following parameters:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
Each parameter specifies the size of the cache for the corresponding block size.
1.1.1.2 Redo Log Buffer
The redo log buffer is a circular buffer in the SGA that holds information about changes
made to the database. This information is stored in redo entries. Redo entries contain the
information necessary to reconstruct, or redo, changes made to the database by INSERT,
UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for
database recovery, if necessary.
Redo entries are copied by Oracle server processes from the user’s memory space to the
redo log buffer in the SGA. The redo entries take up continuous, sequential space in the
buffer. The background process LGWR writes the redo log buffer to the active online
redo log file (or group of files) on disk.

The initialisation parameter LOG_BUFFER determines the size (in bytes) of the redo log
buffer. In general, larger values reduce log file I/O, particularly if transactions are long or
numerous. The default setting is four times the maximum data block size for the host
operating system.
1.1.1.3 The Shared Pool
The shared pool portion of the SGA contains three major areas: library cache, dictionary
cache, buffers for parallel execution messages, and control structures.
The total size of the shared pool is determined by the initialisation parameter
SHARED_POOL_SIZE. The default value of this parameter is 8M on 32-bit platforms
and 64M on 64-bit platforms. Increasing the value of this parameter increases the amount
of memory reserved for the shared pool.
Library Cache
The library cache includes the shared SQL areas, private SQL areas (in the case of a
multiple transaction server), PL/SQL procedures and packages, and control structures
such as locks and library cache handles.
Shared SQL Areas
A shared SQL area contains the parse tree and execution plan for a given SQL statement.
Oracle saves memory by using one shared SQL area for SQL statements executed
multiple times, which often happens when many users execute the same application.
PL/SQL Program Units and the Shared Pool
Oracle processes PL/SQL program units (procedures, functions, packages, anonymous

blocks, and database triggers) much the same way it processes individual SQL
statements. Oracle allocates a shared area to hold the parsed, compiled form of a program
unit. Oracle allocates a private area to hold values specific to the session that executes the
program unit, including local, global, and package variables (also known as package
instantiation) and buffers for executing SQL. If more than one user executes the same
program unit, then a single, shared area is used by all users, while each user maintains a
separate copy of his or her private SQL area, holding values specific to his or her session.
Dictionary Cache
The data dictionary is a collection of database tables and views containing reference
information about the database, its structures, and its users. Oracle accesses the data
dictionary frequently during SQL statement parsing. This access is essential to the
continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory
are designated to hold dictionary data. One area is called the data dictionary cache, also
known as the row cache because it holds data as rows instead of buffers (which hold

entire blocks of data). The other area in memory to hold dictionary data is the library
cache. All Oracle user processes share these two caches for access to data dictionary
information.
1.1.1.4 Large Pool
The database administrator can configure an optional memory area called the large pool
to provide large memory allocations for:
 Session memory for the shared server and the Oracle XA interface (used where
transactions interact with more than one database)
 I/O server processes
 Oracle backup and restore operations
 Parallel execution message buffers, if the initialisation parameter PARALLEL_
AUTOMATIC_TUNING is set to TRUE (otherwise, these buffers are allocated to
the shared pool)
By allocating session memory from the large pool for shared server, Oracle XA, or
parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL
and avoid the performance overhead caused by shrinking the shared SQL cache.
In addition, the memory for Oracle backup and restore operations, for I/O server
processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes.
The large pool is better able to satisfy such large memory requests than the shared pool.
The large pool does not have an LRU list. It is different from reserved space in the shared
pool, which uses the same LRU list as other memory allocated from the shared pool.
1.1.2 Program Global Area (PGA) Overview
A program global area (PGA) is a memory region, which contains data and control
information for a server process. It is a nonshared memory created by Oracle when a
server process is started. Access to it is exclusive to that server process and is read and
written only by Oracle code acting on behalf of it. The total PGA memory allocated by
each server process attached to an Oracle instance is also referred to as the aggregated
PGA memory allocated by the instance.
Contents of PGA:
Private SQL Area
A private SQL area contains data such as bind information and runtime memory
structures. Each session that issues a SQL statement has a private SQL area. Each user
that submits the same SQL statement has his/ her own private SQL area that uses a single
shared SQL area. Thus, many private SQL areas can be associated with the same-shared
SQL area.
Cursors and SQL Areas

The application developer of an Oracle precompiler program or OCI program can

explicitly open cursors, or handles to specific private SQL areas, and use them as a
named resource throughout the execution of the program.
Recursive cursors that Oracle issues implicitly for some SQL statements also use shared
SQL areas. The management of private SQL areas is the responsibility of the user
process. The allocation and deallocation of private SQL areas depends largely on which
application tool you are using, although the number of private SQL areas that a user
process can allocate is always limited by the initialisation parameter OPEN_CURSORS.
The default value of this parameter is 50.
1.1.3 Oracle Processes Overview
This section describes the two types of processes that execute the Oracle server code
(server processes and background processes). It also describes the trace files and alert
file, which record database events for the Oracle processes.
Server Processes
Oracle creates server processes to handle the requests of user processes connected to the
instance. In some situations when the application 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 application and Oracle
operate on different machines, a user process always communicates with Oracle through
a separate server process.
Server processes (or the server portion of combined user/server processes) created on
behalf of each user’s application can perform one or more of the following:
 Parse and execute SQL statements issued through the application

 Read necessary data blocks from data files on disk into the shared database
buffers of the SGA, if the blocks are not already present in the SGA
 Return results in such a way that the application can process the information
Background Processes
To maximize performance and accommodate many users, a multiprocess Oracle system

uses some additional Oracle processes called background processes.
An Oracle instance can have many background processes; not all are always present. The
background processes in an Oracle instance include the following:
 Database Writer (DBW0 or DBWn)
 Log Writer (LGWR)
 Checkpoint (CKPT)
 System Monitor (SMON)
 Process Monitor (PMON)

 Archiver (ARCn)
 Recoverer (RECO)
 Lock Manager Server (LMS) - Real Application Clusters only
 Queue Monitor (QMNn)
 Dispatcher (Dnnn)
 Server (Snnn)
On many operating systems, background processes are created automatically when an

instance is started.
Figure 1.2 show how each background process interacts with the different parts of an
Oracle database, and the rest of this section describes each process.
Fig 1.2 Showing background process interacting with different parts of oracle database

1.1.3.1 Database Writer Process (DBWn)
The database writer process (DBWn) writes the contents of buffers to data files. The
DBWn processes are responsible for writing modified (dirty) buffers in the database
buffer cache to disk. Although one database writer process (DBW0) is adequate for most
systems, you can configure additional processes (DBW1 through DBW9) to improve
write performance if your system modifies data heavily. These additional DBWn
processes are not useful on uniprocessor systems.
1.1.3.2 Log Writer Process (LGWR)
The log writer process (LGWR) is responsible for redo log buffer management—
writing the redo log buffer to a redo log file on disk.
LGWR writes all redo entries that have been copied into the buffer since the last time it
wrote. The redo log buffer is a circular buffer. When LGWR writes redo entries from the
redo log buffer to a redo log file, server processes can then copy new entries over the
entries in the redo log buffer that have been written to disk. LGWR normally writes fast
enough to ensure that space is always available in the buffer for new entries, even when
access to the redo log is heavy.
LGWR writes one contiguous portion of the buffer to disk. LGWR writes:
 A commit record when a user process commits a transaction
 Redo log buffers
1. Every three seconds
2. When the redo log buffer is one-third full
3. When a DBWn process writes modified buffers to disk, if necessary
1.1.3.3 Checkpoint Process (CKPT)
When a checkpoint occurs, Oracle must update the headers of all data files to record the
details of the checkpoint. This is done by the CKPT process. The CKPT process does not
write blocks to disk; DBWn always performs that work.
The statistic DBWR checkpoints displayed by the System_Statistics monitor in

Oracle Enterprise Manager indicates the number of checkpoint requests completed.
1.1.3.4 System Monitor Process (SMON)
The system monitor process (SMON) performs crash recovery, if necessary, at instance
startup. SMON is also responsible for cleaning up temporary segments that are no longer
in use and for coalescing contiguous free extents within dictionary-managed table spaces.
If any dead transactions were skipped during crash and instance recovery because of file-
read or offline errors, SMON recovers them when the table space or file is brought back
online. SMON wakes up regularly to check whether it is needed. Other processes can call
SMON if they detect a need for SMON to wake up.

With Oracle9i Real Application Clusters, the SMON process of one instance can perform
instance recovery for a failed CPU or instance.
1.1.3.5 Process Monitor Process (PMON)
The process monitor (PMON) performs process recovery when a user process fails.
PMON is responsible for cleaning up the database buffer cache and freeing resources that
the user process was using. For example, it resets the status of the active transaction table,
releases locks, and removes the process ID from the list of active processes.
PMON periodically checks the status of dispatcher and server processes, and restarts any
that have died (but not any that Oracle has terminated intentionally).
PMON also registers information about the instance and dispatcher processes with the
network listener. Like SMON, PMON wakes up regularly to check whether it is needed
and can be called if another process detects the need for it.
1.1.3.6 Recoverer Process (RECO)
The recoverer process (RECO) is a background process used with the distributed
database configuration that automatically resolves failures involving distributed
transactions. The RECO process of a node automatically connects to other databases
involved in an in-doubt distributed transaction.
When the RECO process re-establishes a connection between involved database servers,
it automatically resolves all in-doubt transactions, removing from each database’s
pending transaction table any rows that correspond to the resolved in-doubt transactions.
If the RECO process fails to connect with a remote server, RECO automatically tries to
connect again after a timed interval. However, RECO waits an increasing amount of time
(growing exponentially) before it attempts another connection.
The RECO process is present only if the instance permits distributed transactions and if
the DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this
initialisation parameter is zero, RECO is not created during instance startup.
1.1.3.7 Job Queue Processes
Job queue processes are used for batch processing. They execute user jobs. They can be
viewed as a scheduler service that can be used to schedule jobs as PLSQL statements or
procedures on an Oracle instance. Given a start date and an interval, the job queue
processes try to execute the job at the next occurrence of the interval.
Beginning with Oracle9i, Release 1 (9.0.1), job queue processes are managed
dynamically. This allows job queue clients to use more job queue processes when
required. The resources used by the new processes are released when they are idle.

Dynamic job queue processes can execute a large number of jobs concurrently at a given
interval. The job queue processes execute user jobs as they are assigned by the CJQ
process. Here’s what happens:
1. The coordinator process, named CJQ0, periodically selects jobs that need to be
run from the system JOB$ table. New jobs selected are ordered by time.
2. The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to
execute the jobs.
3. The job queue process executes one of the jobs that were selected by the CJ
process for execution. The processes execute one job at a time.
4. After the process finishes execution of a single job, it polls for more jobs. If there
are no scheduled jobs for execution, it enters a sleep state, from which it wakes up
at periodic intervals and polls for more jobs. If the process does not find any new
jobs, it aborts after a preset interval.
The init.ora parameter JOB_QUEUE_PROCESSES represents the maximum number of

job queue processes that can concurrently run on an instance. However, clients should not
assume that all job queue processes are available for job execution.
1.1.3.8 Archiver Processes (ARCn)
The archiver process (ARCn) copies online redo log files to a designated storage device
after a log switch has occurred. ARCn processes are present only when the database is in
ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR
process starts a new ARCn process whenever the current number of ARCn processes is
insufficient to handle the workload. The ALERT file keeps a record of when LGWR
starts a new ARCn process.
1.1.3.9 Lock Manager Server Process (LMS)
In Oracle9i Real Application Clusters, a Lock Manager Server process (LMS) provides
inter-instance resource management.

1.1.3.10 Queue Monitor Processes (QMNn)
The queue monitor process is an optional background process for Oracle Advanced
Queuing, which monitors the message queues. You can configure up to 10 queue monitor
processes. These processes are different from other Oracle background processes in that
process failure does not cause the instance to fail.
1.2Oracle Instance
Introduction to an Oracle Instance
Every running Oracle database is associated with an Oracle instance. When a database is
started on a database server (regardless of the type of computer), Oracle allocates a
memory area called the System Global Area (SGA) and starts one or more Oracle
processes. This combination of the SGA and the Oracle processes is called an Oracle
instance. The memory and processes of an instance manage the associated database’s
data efficiently and serve the one or multiple users of the database.
Fig 1.3 shows an Oracle Instance
The Instance and the Database
After starting an instance, Oracle associates the instance with the specified database. This
is called mounting the database. The database is then ready to be opened, which makes it
accessible to authorized users.
Multiple instances can execute concurrently on the same computer, each accessing its
own physical database. In clustered and massively parallel systems (MPP), Oracle9i Real
Application Clusters enables multiple instances to mount a single database.

Only the database administrator can start up an instance and open the database. If a
database is open, the database administrator can shut down the database so that it is
closed. When a database is closed, users cannot access the information that it contains.
Security for database startup and shutdown is controlled through connections to Oracle
with administrator privileges. Normal users do not have control over the current status of
an Oracle database.
1.3Managing an Oracle Instance
Initialization Parameter Files
To start an instance, Oracle must read an initialisation parameter file—a file containing
a list of configuration parameters for that instance and database. Set these parameters to
particular values to initialise many of the memory and process settings of an Oracle
instance. Most initialisation parameters belong to one of the
following groups:
 Parameters that name things (such as files)
 Parameters that set limits (such as maximums)
 Parameters that affect capacity (such as the size of the SGA), which are called
variable parameters
Among other things, the initialisation parameters tell Oracle:
 The name of the database for which to start up an instance

 How much memory to use for memory structures in the SGA
 What to do with filled online redo log files
 The names and locations of the database’s control files
 The names of private rollback segments or undo table spaces in the database
The following is an example of a parameter file
DB_BLOCK_BUFFERS = 550
DB_NAME = ORAESD
DB_DOMAIN = US.ACME.COM
#
LICENSE_MAX_USERS = 64
#
CONTROL_FILES = filename1, filename2
#
LOG_ARCHIVE_DEST = C:\DEST
LOG_ARCHIVE_START = TRUE
LOG_BUFFER = 64512
UNDO_MANAGEMENT = TRUE

1.4Start up and shut down
1.4.1 Starting up the database
These are the preliminary steps to be performed before attempting to start an instance of
database using SQL*Plus.
1. Start SQL*Plus without connecting to the database:

SQLPLUS /NOLOG
2. Connect to Oracle as SYSDBA:

CONNECT username/password AS SYSDBA
Now you are connected to Oracle and ready to start up an instance of your database.
Using SQL*Plus to Start Up a Database
You use the STARTUP command to start up a database instance. To start an instance,
Oracle must read instance configuration parameters (the initialisation parameters) from
either a server parameter file or a traditional text initialisation parameter file.
When you issue the STARTUP command with no PFILE clause, Oracle reads the
initialisation parameters from a server parameter file (SPFILE) in a platform-specific
default location.
Note: For UNIX, the platform-specific default location (directory) for the server
parameter file (or text initialisation parameter file) is:
$ORACLE_HOME/dbs
For Windows NT and Windows 2000 the location is:

$ORACLE_HOME\database
In the platform-specific default location, Oracle locates your initialisation parameter file
by examining filenames in the following order:
1. spfile$ORACLE_SID.ora
2. spfile.ora
3. init$ORACLE_SID.ora
You can direct Oracle to read initialisation parameters from a traditional text initialisation
parameter file, by using the PFILE clause of the STARTUP command.

For example:
STARTUP PFILE = /home/oracle/product/9.0.1/dbs/inittest1.ora
1.4.2 Starting up an instance
You can start up an instance in these modes:
 Start the instance without mounting a database. This does not allow access to the
database and usually would be done only for database creation or the re-creation of
control files
 Start the instance and mount the database, but leave it closed. This state allows for
certain activities, but does not allow general access to the database.
 Start the instance, and mount and open the database. This can be done in
unrestricted mode, allowing access to all users, or in restricted mode, allowing
access for database administrators only.
1.4.2.1 Starting an Instance, and Mounting and Opening a Database
Normal database operation means that an instance is started and the database is mounted
and open. This mode allows any valid user to connect to the database and perform typical
data access operations.
Start an instance, read the initialisation parameters from the default server parameter file
location, and then mount and open the database by using the STARTUP command by
itself.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 235693104 bytes

Fixed Size 279600 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL>

1.4.2.2 Starting an Instance Without Mounting a Database
You can start an instance without mounting a database. Typically, you do so only during
database creation. Use the STARTUP command with the NOMOUNT option:
SQL> startup nomount

SQL>
1.4.2.3 Starting an Instance and Mounting a Database
Start an instance and mount the database, but leave it closed by using the STARTUP
command with the MOUNT option:
SQL> startup mount

Database mounted.
1.4.3 Shutting down a database
To shut down a database and instance, you must first connect as SYSOPER or SYSDBA.
There are various modes of shutting down a database.
 Shutting down with Normal option

 Shutting down with Immediate option
 Shutting down with transactional option
 Shutting down with abort option

1.4.3.1 Shutting Down with the NORMAL Option
To shut down a database in normal situations, use the SHUTDOWN command with the
NORMAL option:
SQL> shutdown normal;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Normal database shutdown proceeds with the following conditions:
 No new connections are allowed after the statement is issued.

 Before the database is shut down, Oracle waits for all currently connected users
to disconnect from the database.
The next startup of the database will not require any instance recovery procedures.
1.4.3.2 Shutting Down with the IMMEDIATE Option
The immediate database shutdown can be used in the following situations:
 To initiate an automated and unattended backup

 When a power shutdown is going to occur soon
 When the database or one of its applications is functioning irregularly and you
cannot contact users to ask them to log off or they are unable to log off
To shut down a database immediately, use the SHUTDOWN command with the
IMMEDIATE option:
SHUTDOWN IMMEDIATE
Immediate database shutdown proceeds with the following conditions:
 No new connections are allowed

 Any uncommitted transactions are rolled back.
 Oracle implicitly rolls back active transactions and disconnects all
connected users.
The next startup of the database will not require any instance recovery procedures.
1.4.3.3 Shutting down with Transactional option
To perform a planned shutdown of an instance while allowing active transactions to

complete first, use the SHUTDOWN command with the TRANSACTIONAL option:
SHUTDOWN TRANSACTIONAL

Transactional database shutdown proceeds with the following conditions:
 No new connections are allowed

 After all transactions have completed, any client still connected to the instance
is disconnected.
 At this point, the instance shuts down just as it would when a SHUTDOWN
IMMEDIATE statement is submitted.
1.4.3.4 Shutting down with ABORT Option
When you must do a database shutdown by aborting transactions and user connections,
issue the SHUTDOWN command with the ABORT option:
SHUTDOWN ABORT
An aborted database shutdown proceeds with the following conditions:
 No new connections are allowed, nor are new transactions allowed to be started, after
the statement is issued.
 Current client SQL statements being processed by Oracle are immediately
terminated.
 Uncommitted transactions are not rolled back.
 Oracle does not wait for users currently connected to the database to disconnect.
Oracle implicitly disconnects all connected users.
The next startup of the database will require instance recovery procedures.
1.5Alert Log File
Each server and background process can write to an associated trace file. When a process
detects an internal error, it dumps information about the error to its trace file. Some of the
information written to a trace file is intended for the database administrator, while other
information is for Oracle Support Services. Trace file information is also used to tune
applications and instances.
The alert file, or alert log, is a special trace file. The alert file of a database is a
chronological log of messages and errors, which includes the following:
 All internal errors (ORA-600), block corruption errors (ORA-1578), and

deadlock errors (ORA-60) that occur
 Administrative operations, such as CREATE, ALTER, and DROP statements and
STARTUP, SHUTDOWN, and ARCHIVELOG statements
 Several messages and errors relating to the functions of shared server and
dispatcher processes
 Errors occurring during the automatic refresh of a materialized view
 The values of all initialisation parameters at the time the database and instance
Start
Initialization parameters controlling the location and size of trace files are:

 BACKGROUND_DUMP_DEST
 USER_DUMP_DEST
 MAX_DUMP_FILE_SIZE
1.5.1 Specifying the location of Trace and Alert Files
All trace files for background processes and the alert file are written to the destination
directory specified by the initialisation parameter BACKGROUND_DUMP_DEST.
All trace files for server processes are written to the destination directory specified by the
initialisation parameter USER_DUMP_DEST. The names of trace files are operating
system specific, but each file usually includes the name of the process writing the file
(such as LGWR and RECO).
1.6Setup Password File Authentication
Password file can be created by a utility called ORAPWD. This section covers these
topics.
 Using ORAPWD
 Setting REMOTE_LOGIN_PASSWORDFILE
 Adding users to Password file
 Maintaining a password file
1.6.1 Using ORAPWD: Syntax for ORAPWD
orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
There are no spaces around the equal-to (=) character.
For example, the following command creates a password file named

acct.pwd that
allows up to 30 privileged users with different passwords:
ORAPWD FILE=acct.pwd PASSWORD=secret ENTRIES=30

Following are the description of parameters in ORAPWD utility:
File:
This parameter sets the name of the password file being created. You must specify
the full path name for the file. The contents of this file are encrypted, and the file
cannot be read directly. This parameter is mandatory.
Password:
This parameter sets the password for user SYS. If you issue the ALTER USER statement
to change the password for SYS after connecting to the database, both the password
stored in the data dictionary and the password stored in the password file are updated.
This parameter is mandatory.
Entries:
This parameter specifies the number of entries that you require the password file to
accept. This number corresponds to the number of distinct users allowed to connect
to the database as SYSDBA or SYSOPER.
1.6.2 Setting Remote_Login_PasswordFile
Apart from creating the Password file , you must also set the initialisation parameter
Remote_Login_Passwordfile to an appropriate value. The values are the following:
 None:
Setting this parameter to NONE causes Oracle to behave as if the password file does not
exist. NONE is the default value for this parameter.
 Exclusive:
An EXCLUSIVE password file can be used with only one database. Only an
EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE
password file allows you to grant SYSDBA and SYSOPER system privileges to
individual users and have them connect as themselves.
 Shared:
A SHARED password file can be used by multiple databases. However, the only user
recognized by a SHARED password file is SYS. You cannot add users to a SHARED
password file. All users needing SYSDBA or SYSOPER system privileges must connect
using the same name, SYS, and password. This option is useful if you have a single DBA
administering multiple databases.

1.6.3 Adding Users to Password File:
1. Create a password file using ORAPWD utility following the syntax

2. Set the Remote_Login_Passwordfile parameter to EXCLUSIVE
3. Connect with SYSDBA privilege as shown in example
Connect sys/password as sysdba
4. Start up the instance and create the database if necessary, or mount and open an
existing database.
5. Create users as necessary. Grant SYSDBA or SYSOPER privileges.
1.6.4 Maintaining a Password file:
This section describes how to:
 Expand the number of password file users if the password file becomes full
 Remove the password file
 Avoid changing the state of the password file
1.6.4.1 Expanding the number of Password file users:
If you receive the file full error (ORA-1996) when you try to grant SYSDBA or
SYSOPER system privileges to a user, you must create a larger password file and re-
grant the privileges to the users.
To replace a password file:
 Shutdown the database

 Delete the existing password file
 Create a new password file using ORAPWD utility with sufficient ENTRIES
parameter which u think might be used.
1.6.4.2 Remove the password file
If you determine that you no longer require a password file to authenticate users, you can
delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE
initialisation parameter to NONE. After you remove this file, only those users who can be
authenticated by the operating system can perform database administration operations.
1.6.4.3 Changing the state of password file:
The password file state is stored in the password file. When you first create a password
file, its default state is SHARED. You can change the state of the password file by setting
the initialisation parameter REMOTE_LOGIN_PASSWORDFILE. When you start up an
instance, Oracle retrieves the value of this parameter from the parameter file stored on
your client machine. When you mount the database, Oracle compares the value of this

parameter to the value stored in the password file. If the values do not match, Oracle
overwrites the value stored in the file.
1.7Oracle9i Installation Procedure
Oracle Universal Installer Overview
Oracle Universal Installer is a Java-based graphical user interface (GUI) tool that enables
you to install Oracle components from your CD-ROM. Oracle Universal Installer
provides the following capabilities:
 Component and suite installations

 Web-based installations
 National language and globalisation support
 Distributed installation support
 Unattended "silent" installations using response files
 Uninstallation of installed components
 Multiple Oracle homes support
1.7.1 Oracle9i products for Installation:
Oracle9i has these components.
 Oracle9i Database
 Oracle9i Client
 Oracle9i Management and Integration
1.7.1.1 Oracle9i database:
The Oracle9i Database is an object-oriented relational database management system,

which consists of an Oracle database and an Oracle instance. There are four installation
types: Enterprise Edition, Standard Edition, Personal Edition, or Custom.
Enterprise Edition: If you select this type, Oracle Universal Installer installs a
preconfigured seed database, networking services, licensable Oracle Options, database
environment tools, the Oracle Enterprise Manager framework of management tools,
including Console, Management Server, and Intelligent Agent, Oracle utilities, and online
documentation. It also installs those products most commonly used in data warehousing
and transaction processing environments.

Standard Edition: If you select this type, Oracle Universal Installer installs a
preconfigured seed database, networking services, Oracle Enterprise Manager framework
of management tools, including Console, Management Server, and Intelligent Agent, and
Oracle utilities.
Personal Edition: If you select this type, Oracle Universal Installer installs the same
software as the Enterprise Edition installation type, but supports only a single user
development and deployment environment that requires full compatibility with Enterprise
Edition and Standard Edition.
Personal Edition is the only Oracle9i Database installation type that is supported on
Windows 98. Note that Oracle Management Server and Intelligent Agent are not
supported on Windows 98.
Custom: If you select this type, Oracle Universal Installer prompts you to select
individual components to install from the components available with Enterprise Edition,
Standard Edition, and Personal Edition installations.
1.7.1.2 Oracle9i Client
The Oracle9i Client is a front-end database application that connects to the database
through one or more application servers. There are three Client installation types:
Administrator, Runtime, and Custom.
Administrator: If you select this type, Oracle Universal Installer installs the Oracle
Enterprise Manager Console, including enterprise management tools, networking
services, utilities, and basic client software.
Runtime: If you select this type, Oracle Universal Installer installs networking services
and support files.
Custom: If you select this type, Oracle Universal Installer prompts you to select
individual components to install from the components available with Administrator and
Runtime.
1.7.1.3 Oracle9i Management and Integration
Oracle Management Server is a central processing and distribution system for

management tasks. It enables distributed control between clients and managed nodes.
Oracle Internet Directory is an database-based LDAP v3 directory server. Oracle
Integration Server is a suite of software that provides Internet and intranet tools to
integrate applications within and across organizations.
There are four Management and Integration installation types: Oracle Management
Server, Oracle Internet Directory, Oracle Integration Server, and Custom.

 Oracle Management Server: If you select this type, Oracle Universal Installer
installs the Oracle Management Server, which processes all system management tasks
from the Enterprise Manager console and administers the distribution of these tasks to
Intelligent Agents on managed nodes across the enterprise. In addition, Oracle Universal
Installer installs basic client software.
 Oracle Internet Directory: If you select this type, Oracle Universal Installer installs
a Lightweight Directory Access Protocol (LDAP)-enabled Oracle Internet Directory
Server, LDAP-enabled client tools, and the Oracle Internet Directory database schema.
 Oracle Integration Server: If you select this type, Oracle Universal Installer installs
XML-enabled components that integrate enterprise applications. Components include
Oracle9i JVM, a workflow engine, and advanced queuing.
 Custom: If you select this type, Oracle Universal Installer prompts you to select
individual components to install from the components available with Oracle Management
Server, Oracle Internet Directory, and Oracle Integration Server.
1.8Configure OMF
Oracle managed files simplifies the administration of an Oracle Database.

The following initialisation parameters allow the database server to use the Oracle
managed files feature:
DB_CREATE_FILE_DEST: Defines the location of the default file system directory

where Oracle creates data files or tempfiles when no file specification is given in the
creation operation. Also used as the default file system directory for online redo log and
control files if DB_ CREATE_ONLINE_LOG_DEST_ n is not specified.
DB_CREATE_ONLINE_LOG_DEST_n: Defines the location of the default file system

directory for online redo log files and control file creation when no file specification is
given in the creation operation. You can use this initialisation parameter multiple times,
where n specifies a multiplexed copy of the online redo log or control file. You can
specify up to five multiplexed copies.
Include the DB_CREATE_FILE_DEST initialisation parameter in your initialisation

parameter file to identify the default location for the database server to create:
 Data files
 Tempfiles
 Online redo log files
 Control files
The following example sets /u01/oradata/payroll as the default directory

to use when creating Oracle-managed files.

DB_CREATE_FILE_DEST = '/u01/oradata/payroll'
Setting the DB_CREATE_ONLINE_LOG_DEST_n initialisation parameter in the

initialisation parameter file to identify the default location for the database server to
create:
 Online Redo log files

 Control Files
DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u02/oradata/payroll’
DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u02/oradata/payroll’
1.9 Prerequisites for database creation
The following topics are to be looked into for database creation
 Planning for data base creation

 Meeting certain prerequisites
1.9.1 Planning for database creation
 Plan the database tables and indexes and estimate the amount of space they will
require.
 Plan the layout of the underlying operating system files. Proper distribution of files
can improve database performance dramatically by distributing the I/O for accessing
the files.
 Consider using Oracle Managed Files feature to create and manage operating systems
files.
 Select the global database name, which is the name and location of the database
within the network structure. Create the global database name by setting both the
DB_ NAME and DB_DOMAIN initialisation parameters.
 Select the data base character set
 Select the standard database block size. This is specified at database creation by the
DB_BLOCK_SIZE initialisation parameter and cannot be changed after the database
is created.
 Use an undo table space to manage your undo records.

 Develop a backup and recovery strategy to protect the database from failure. It is
important to protect the control, file by multiplexing, to choose the appropriate
backup mode, and to manage the online and archived redo logs.

 Get Familiar with the options of starting and shutting down an instance and mounting
and opening a database.
1.9.2 Meeting Prerequisites
 The desired Oracle Software has to be installed.

 There should be sufficient memory available to start up the Oracle Instance
 There should be sufficient disk storage space available on the computer that executes
Oracle.
1.10 Create and manage Initialization parameter files
The following are the initialisation parameters one has to look into before creating a
database:
 Determine the global database name

 Specify control files
 Specify database block sizes
 Setting Initialization Parameters that affect the size of SGA
 Specify the maximum number of processes
 Specify the method of undo space management
 Setting License Parameters
1.10.1 Determine the global database name
DB_DOMAIN parameter indicates the domain (logical location) within a network

structure. The combination of the settings for these two parameters must form a database
name that is unique within a network.
For example, to create a database with a global database name of test.us.acme.com, edit
the parameters of the new parameter file as follows:
DB_NAME = test
DB_DOMAIN = us.acme.com
DB_NAME must be set to a text string of no more than eight characters. During database
creation, the name provided for DB_NAME is recorded in the data files, redo log files,
and control file of the database. If during database instance startup the value of the
DB_NAME parameter (in the parameter file) and the database name in the control file are
not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the database is
created. This is typically the name of the organization that owns the database.
1.10.2 Specify Control Files

CONTROL_FILES initialisation parameter has to be included in your new parameter file

and set its value to a list of control filenames to use for the new database. When you
execute the CREATE DATABASE statement, the control files listed in the CONTROL_
FILES parameter will be created.

1.10.3 Specify database block size

The DB_BLOCK_SIZE initialisation parameter specifies the standard block size for the
database. This block size is used in creation of SYSTEM table space and by default in
other table spaces.
Typically DB_BLOCK_SIZE is set to either 4K or 8K
The block size cannot be changed after database creation, except by re-creating the
database. If a database’s block size is different from the operating system block size,
make the database block size a multiple of the operating system’s block size.
For example, if your operating system’s block size is 2K (2048 bytes), the following
setting for the DB_BLOCK_SIZE initialisation parameter is valid:
DB_BLOCK_SIZE=4096
1.10.4 Setting Initialization parameters that affect the size of SGA
DB_CACHE_SIZE Initialization Parameter The DB_CACHE_SIZE initialisation

parameter replaces the DB_BLOCK_BUFFERS initialisation parameter that was used in
previous releases. The DB_CACHE_SIZE parameter specifies the size of the cache of
standard block size buffers, where DB_ BLOCK_SIZE specifies the standard block size.
DB_ nK_CACHE_SIZE Initialization Parameters
The sizes and numbers of non-standard block size buffers are specified by the following
initialisation parameters:
 DB_2K_CACHE_SIZE
Adjusting the Size of the Shared Pool
The SHARED_POOL_SIZE initialisation parameter is a dynamic parameter (in previous

releases it was not dynamic) that allows you to specify or adjust the size of the shared
pool component of the SGA. Oracle selects an appropriate default value.
Adjusting the Size of the Large Pool
The LARGE_POOL_SIZE initialisation parameter is a dynamic parameter (in previous

releases it was not dynamic) that allows you to specify or adjust the size of the large pool
component of the SGA. Oracle selects an appropriate default value.
Limiting the size of the SGA:

The SGA_MAX_SIZE initialisation parameter specifies the maximum size of the System
Global Area for the lifetime of the instance. You can dynamically alter the initialisation
parameters affecting the size of the buffer caches, shared pool, and large pool, but only to
the extent that the sum of these sizes and the sizes of the other components of the SGA
(fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by
SGA_MAX_SIZE.
SGA_MAX_SIZE >= DB_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE +
FIXED SGA + VARIABLE SGA + SIZE OF REDO LOG BUFFERS
1.10.5 Specifying the Maximum Number of Processes

The PROCESSES initialisation parameter determines the maximum number of operating
system processes that can be connected to Oracle concurrently. The value of this
parameter must be 6 or greater (5 for the background processes plus 1 for each user
process). For example, if you plan to have 50 concurrent users, set this parameter to at
least 55.
1.10.6 Specifying a method of Undo Space Management
Undo Management Initialization parameter determines whether an instance will start

up in automatic undo management mode where undo is stored in an undo table space, or
rollback segment undo mode, where undo is stored in rollback segments. A value of
AUTO enables automatic undo management mode, MANUAL enables rollback segment
undo mode.
UNDO_TABLESPACE Initialization Parameter
When the instance starts up in automatic undo management mode, it selects the first
available undo table space in the instance for storing undo. A default undo table space
named SYS_UNDOTBS is automatically created when you execute a CREATE
DATABASE statement and the UNDO_MANAGEMENT initialisation parameter is set
to AUTO. This is the undo table space that Oracle will normally select whenever you
start up the database.
Setting License parameters:
To set the maximum number of concurrent sessions for an instance set the initialisation
parameter LICENSE_MAX_SESSIONS in the initialisation parameter file that starts the
instance:

LICENSE_MAX_SESSIONS = 80
To limit the number of users created in a database, set the LICENSE_MAX_USERS

initialisation parameter in the database’s initialisation parameter file, as shown in the
following example:
LICENSE_MAX_USERS = 200
1.10.7 Managing Initialization parameter files using a server parameter file
Creating a server parameter file:
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/oracle/dbs/test_init.ora';
If a server parameter file of the same name already exists on the server, it is
Overwritten with the new information.
Exporting the server parameter file:
The following example creates a text initialisation parameter file from a server parameter
file where the names of the files are specified:
CREATE PFILE='/u01/oracle/dbs/test_init.ora'
FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';
1.11 Creating a database manually
Step 1: Decide on the instance identifier
Decide on a unique Oracle System Identifier and set the ORACLE_SID environment
variable accordingly.
% export ORACLE_SID=mynewdb
The value of DB_NAME initialisation parameter should match the SID setting.
Step 2: Establish the database administrator authentication method
To create a database one should have appropriate system privileges
Step 3: Create the initialisation parameter file
Have a look at the sample initialisation parameter file and edit it accordingly.
Store the initialisation parameter file in the Oracle’s default location.

Table 1.1 Default parameter file locations

Platform Default Name Default Location
Unix Init$ORACLE_SID.ora $ORACLE_HOME/dbs
NT Init$ORACLE_SID.ora $ORACLE_HOME/database
The initialisation parameter file initmynewdb.ora is stored in the following location:

/home/oracle/product/9.0.1/dbs/initmynewdb.ora
Step3.1: Sample Initialization parameter file

#Cache and I/O
DB_BLOCK_SIZE =4096
DB_CACHE_SIZE=20971520
# Cursors and Library Cache

CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300
# Diagnostics and Statistics

BACKGROUND_DUMP_DEST=/home1/oracle9i/mynewdb/ bdump
CORE_DUMP_DEST=/home1/oracle9i /mynewdb/cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=/home1/oracle9i/mynewdb/udump
# Control File configuration
CONTROL_FILES=("/home1/oracle9i/mynewdb/control/control01.ctl",
"/home1/oracle9i/mynewdb/control/control02.ctl",
"/home1/oracle9i/mynewdb/control/control03.ctl")
# Archive
LOG_ARCHIVE_DEST_1='LOCATION=/home1/oracle9i/mynewdb/archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE
# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
# "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)",
(PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.0.0
DB_NAME=mynewdb
# Distributed, Replication and Snapshot
DB_DOMAIN=til.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
# Network Registration
INSTANCE_NAME=mynewdb
# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800
# Processes and Sessions

PROCESSES=150
# Redo Log and Recovery

FAST_START_MTTR_TARGET=300
# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN
# Sort, Hash Joins, Bitmap Indexes

SORT_AREA_SIZE=524288
# System Managed Undo and Rollback Segments

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undotbs
Step4: Connect to the instance
Start SQL*Plus and connect to your Oracle instance AS SYSDBA.
$ SQLPLUS /nolog
CONNECT SYS /MANAGER AS SYSDBA
[oracle9i@esdlnx dbs]$ sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Tue Oct 16 16:13:26 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> connect sys/manage as sysdba

Connected.
SQL>
Step5: Start the Instance

Start the instance without mounting the database. This is done only during database
creation or while performing maintenance operations.
Use the Startup command without NOMOUNT option
STARTUP NOMOUNT
At this point, only instance is created and there is no database. Only SGA is created and
background processes are started in preparation for the creation of new database.
Step6: Issue the Create database statement
To create new database use CREATE DATABASE statement. This statement does the
following:
 Creates the data files for the database
 Creates the control files for the database
 Creates the redo log files for the database and establishes the ARCHIVELOG
mode.
 Creates the SYSTEM table space and the SYSTEM rollback segment
 Creates the data dictionary
 Sets the character set that stores data in the database
 Sets the database time zone
 Mounts and opens the database for use
The following statement creates database mynewdb:

CREATE DATABASE mynewdb
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE '/home1/oracle9i/mynewdb/system01.dbf' SIZE 325M REUSE
UNDO TABLESPACE undotbs DATAFILE '/home1/oracle9i/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/home1/oracle9i/mynewdb/temps1.dbf'
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/home1/oracle9i/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/home1/oracle9i/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/home1/oracle9i/mynewdb/redo03.log') SIZE 100M;
A database is created with the following characteristics:
 The database is named mynewdb

 Three control files are created as specified by CONTROL_FILES initialisation

parameter
 MAXINSTANCES specified that only one instance can have this database mounted
and open.
 MAXDATAFILES specifies the maximum number of data files that can be open in
the database.
 The SYSTEM table space, consisting of the operating system file
/home1/oracle9i/mynewdb/system01.dbf is created as specified by the DATAFILE
clause. If the file already exists, it is overwritten.
 The UNDO TABLE SPACE clause creates and names an undo table space to be used
for undo records for this database.
 The DEFAULT_TEMPORARY_TABLESPACE clause creates a temporary table
space.
 The US7ASCII character set is used to store data in this database.
 The AL16UTF16 character set is specified as the NATIONAL CHARACRTER SET
used to store data in columns of specifically defined as NCHAR, NCLOB, or
NVARCHAR2.
 The new database has three online redo log files as specified in the LOGFILE clause.
MAXLOGHISTORY, MAXLOGFILES, and MAXLOGMEMBERS define limits for
the redo log.
 As ARCHIVELOG clause is not specified in this CREATE DATABASE statement,
redo log files will not initially be archived.
 The default database time zone is the same as the operating system’s time zone.
 You set the database’s default time zone by specifying the SET TIME_ZONE clause
of the CREATE DATABASE statement. If omitted (as it is in this case), the default
database time zone is the operating system time zone.
Step7: Create Additional table spaces
To make the database functional, you need to create additional files and table spaces for
users. The following sample script creates some additional table spaces:
CONNECT SYS/ password AS SYSDBA
-- create a user table space to be assigned as the default table space for users
CREATE TABLESPACE users LOGGING

DATAFILE '/home1/oracle9i/mynewdb/users01.dbf'
EXTENT MANAGEMENT LOCAL;
-- Create a table space for indexes, separate from user table space
CREATE TABLESPACE indx LOGGING

DATAFILE '/home1/oracle9i/mynewdb/indx01.dbf'
EXTENT MANAGEMENT LOCAL;

Step8: Run scripts to create Data Dictionary Views:
Run the scripts necessary to build views, synonyms, and PL/SQL packages:
@/home/oracle/product/9.0.1/rdbms/admin/catalog.sql;
@/home/oracle/product/9.0.1/rdbms/admin/catproc.sql;
EXIT
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic
Performance views, and public synonyms for many of the views.
Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
Step9: Create Server parameter file:

-- Create the server parameter file
CREATE SPFILE=' /home/oracle/product/9.0.1/spfilemynewdb.ora' FROM

PFILE='/home/oracle/product/9.0.1/initmynewdb.ora';
SHUTDOWN
This time you will start up using the server parameter file

STARTUP
Step10: Back up the database
Take the back up of the database.

1.12 Create a database using Oracle Database Configuration Assistant
The Oracle Database Configuration Assistant (DBCA) is a graphical user interface

(GUI) tool that interacts with the Oracle Universal Installer, or can be used
Standalone, to simplify the creation of a database.
Using Oracle Database Configuration Assistant you can add or delete a database. The
Oracle Database Configuration Assistant can be used to create single instance databases,
or it can be used to create or add instances in an Oracle Real Application Clusters
environment.
1.12.1 Advantages of using Oracle Database Configuration Assistant:
 Its wizards guide you through a selection of options providing an easy means of
creating and tailoring your database.
 It builds efficient and effective databases that take advantage of Oracle’s new
features.
 It uses Optimal Flexible Architecture (OFA), whereby database files and

administrative files, including initialisation files, follow standard naming and placement
practices.
1.12.2 Creating a database

A database can be created from predefined templates provided by Oracle or from
templates that you or others have created. When you select a template, you can choose
either to include data files or not. If you select a template with data files, you
will be able to save the database creation information as a template or script. You
can run the script later to create a new database.
Oracle provides templates for the following options:
DSS: Users perform numerous, complex queries that process large volumes of data.
Response time, accuracy, and availability are key issues.
OLTP: Many concurrent users performing numerous transactions requiring rapid access
to data. Availability, speed, concurrence, and recoverability are key issues.
New Database: This template allows you maximum flexibility in defining a database.

1.12.3 Global Database name and Parameters
You are guided thorough a series of pages that allow you to further define your database
or to accept default parameter values and file locations as recommended by Oracle. You
provide a global database name, specify database options to include, determine mode
(dedicated server of shared server), and ultimately you can specify initialisation
parameter.
Typical: This creates a database with minimal user input. You do not specify specific
initialisation parameter values; instead, you specify the maximum number of concurrent
users, the percentage of physical memory reserved for Oracle, and a database type
(OLTP, Multipurpose or Data Warehousing).
Custom: Custom allows you to specify initialisation parameter values that affect the size
of the System Global Area (SGA). Very experienced database administrators who have
specific tuning needs can use it
1.12.4 Completing Database Creation
After you have completed the specification of the parameters that define your database
you can:
 Create the database

 Save the description as database template
 Generate database creation scripts.
1.12.5 Deleting a database
The Oracle Database Configuration Assistant enables you to delete a database.

When you do so, you delete the database instance and its control file(s), redo log files,
and data files. The initialisation parameter file is not deleted.
1.13 Monitor the use of Diagnostic Files
Trace Files: Background processes always write to a trace file when appropriate. In the
case of the ARCn background process, it is possible, through an initialisation parameter,
to control the amount and type of trace information that is produced.
Trace files are written on behalf of server processes whenever internal errors occur.
Additionally, setting the initialisation parameter SQL_TRACE = TRUE causes the SQL
trace facility to generate performance statistics for the processing of all SQL statements
for an instance and write them to the USER_DUMP_DEST directory.
Optionally, trace files can be generated for server processes at user request. Regardless of
the current value of the SQL_TRACE initialisation parameter, each session can enable or
disable trace logging on behalf of the associated server process by using the SQL

statement ALTER SESSION SET SQL_TRACE. This example enables the SQL trace
facility for a specific session:
ALTER SESSION SET SQL_TRACE TRUE;
The DBMS_SESSION and DBMS_SYSTEM packages can also be used to control SQL
tracing for a session.

SUMMARY
 An Oracle Data base is comprised of physical files, memory and processes.
 A system global area (SGA) is a group of shared memory structures that contain data
and control information for one Oracle database instance.
 The database buffer cache is the portion of the SGA that holds copies of data blocks
read from data files.
 The redo log buffer is a circular buffer in the SGA that holds information about
changes made to the database.
 The total size of the shared pool is determined by the initialisation parameter
SHARED_POOL_SIZE.
 A shared SQL area contains the parse tree and execution plan for a given SQL
statement.
 A program global area (PGA) is a memory region, which contains data and control
information for a server process.
 The database writer process (DBWn) writes the contents of buffers to data files.
The DBWn processes are responsible for writing modified (dirty) buffers in the
database buffer cache to disk
 The log writer process (LGWR) is responsible for redo log buffer management—
writing the redo log buffer to a redo log file on disk.
 The system monitor process (SMON) performs crash recovery, if necessary, at
instance startup.
 The process monitor (PMON) performs process recovery when a user process fails.
 The recoverer process (RECO) is a background process used with the distributed
transactions.
 The archiver process (ARCn) copies online redo log files to a designated storage
device after a log switch has occurred. ARCn processes are present only when the
database is in ARCHIVELOG mode, and automatic archiving is enabled.
 The queue monitor process is an optional background process for Oracle Advanced
Queuing, which monitors the message queues.
 Oracle allocates a memory area called the System Global Area (SGA) and starts one
or more Oracle processes. This combination of the SGA and the Oracle processes is
called an Oracle instance.
 The command for starting an instance without mounting the database is
STARTUP NOMOUNT.
 The command for starting an instance and mounting the database is
STARTUP MOUNT.
 To shut down the database in normal conditions the command used is SHUTDOWN
NORMAL.
 To shutdown the database immediately use SHUTDOWN IMMEDIATE command.
 The parameters which control the location and size of trace files are:
 BACKGROUND_DUMP_DEST
 USER_DUMP_DEST
 MAX_DUMP_FILE_SIZE
 Password file can be created by a utility called ORAPWD.

 Oracle Universal Installer is a Java-based graphical user interface (GUI) tool that
enables you to install Oracle components from your CD-ROM.
 The DB_BLOCK_SIZE initialisation parameter specifies the standard block size for
the database. This block size is used in creation of SYSTEM table space and by
default in other table spaces.
 The PROCESSES initialisation parameter determines the maximum number of
operating system processes that can be connected to Oracle concurrently.

SELF-ASSESSMENT
Fill in the blanks:
1. --SGA------ contains data and control information of an Oracle Instance.

2. --SGA------- and –ORACLE PROCESS-- constitute an Oracle Instance.
3. The size of the cache of the data blocks is determined by ---DB_BLOCK_SIZE-
parameter
4. –REDO LOG BUFFER -- holds information about changes made to the database.
5. –DATA DICTIONARY---- is a collection of database tables and views.
State whether the following are True/False
1. Log Writer Process writes contents of buffers to data files (T/F).

2. Database writer process writes data in redo log buffer to redo log file (T/F).
3. PMON process performs Crash Recovery (T/F).
4. SMON performs process recovery (T/F).
5. The recoverer process (RECO) is a background process used with the distributed
transactions (T/F).
6. The init.ora parameter JOB_QUEUE_PROCESSES represents the maximum
number of job queue processes that can concurrently run on an instance (T/F).
Answer the following Questions:
1. What is SGA and what does it comprise of?

2. What does background process RECO do?
3. What is an Oracle Instance?
4. What are various options of starting and shutting down a database ?
5. What is the role of alert file?
6. Write short notes on ORAPWD?
7. When are Oracle Managed files used?
8. What are the prerequisites for data base creation?

LAB EXERCISES
1. Write a script for creation of database with the following configuration:
i. Database name and Instance name: TESTDB
ii. One control file named control01.con located in the directory
"/home1/oracle9i/testdb/control/control01.ctl"
iii. Two redo log file groups each one 150K member named log1a.rdo and
log2a.rdo located in the directory '/home1/oracle9i/testdb/redo01.log'
The maximum number of five logs files groups and five log file Members for
each group
iv. A 20M data file named system01.dbf and located in
/home1/oracle9i/testdb/system01.dbf' directory
v. Maximum of 30 data files that can be created for the database
vi. A maximum number of 100 archived redo logs for automatic media
recovery
vii. The Character set WE8ISO8859P1
viii. The Trace file location should be in the BDUMP and CDUMP
directory.
2. Create additional table spaces called users and indx

3. Create the data dictionary views.
4. Where is the existing control file and what is its name
5. Try to start the database without the control file. What happens?

Chapter01 Oracle Architecture

Uploaded by

Copyright:

Available Formats

Chapter01 Oracle Architecture

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter01 Oracle Architecture

Uploaded by

Copyright:

Available Formats

Exploring Oracle Database Administration Oracle Architectural Components and Database Creation

Oracle Architectural Components and

© Copyright 2006, Tata Consultancy Services Limited (TCS). 1

1.1Oracle Architecture and its main components

The basic memory structures of Oracle include:

 Database buffer cache

Fig. 1.1 – Oracle Memory Structure

© Copyright 2006, Tata Consultancy Services Limited (TCS). 2

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically

The SGA contains the following data structures:

DB_CACHE_SIZE: The size of the cache of standard blocks.

LARGE_POOL_SIZE: The size of the large pool; the default is 0.

1.1.1.1 Data Base Buffer Cache

© Copyright 2006, Tata Consultancy Services Limited (TCS). 3

Size of the Database Buffer Cache

Legitimate values are from 2K to 32K.

1.1.1.2 Redo Log Buffer

© Copyright 2006, Tata Consultancy Services Limited (TCS). 4

1.1.1.3 The Shared Pool

Shared SQL Areas

PL/SQL Program Units and the Shared Pool

Oracle processes PL/SQL program units (procedures, functions, packages, anonymous

© Copyright 2006, Tata Consultancy Services Limited (TCS). 5

1.1.1.4 Large Pool

1.1.2 Program Global Area (PGA) Overview

Private SQL Area

Cursors and SQL Areas

© Copyright 2006, Tata Consultancy Services Limited (TCS). 6

The application developer of an Oracle precompiler program or OCI program can

1.1.3 Oracle Processes Overview

 Parse and execute SQL statements issued through the application

To maximize performance and accommodate many users, a multiprocess Oracle system

© Copyright 2006, Tata Consultancy Services Limited (TCS). 7

On many operating systems, background processes are created automatically when an

© Copyright 2006, Tata Consultancy Services Limited (TCS). 8

1.1.3.1 Database Writer Process (DBWn)

1.1.3.2 Log Writer Process (LGWR)

1.1.3.3 Checkpoint Process (CKPT)

The statistic DBWR checkpoints displayed by the System_Statistics monitor in

1.1.3.4 System Monitor Process (SMON)

© Copyright 2006, Tata Consultancy Services Limited (TCS). 9

1.1.3.5 Process Monitor Process (PMON)

1.1.3.6 Recoverer Process (RECO)

1.1.3.7 Job Queue Processes

© Copyright 2006, Tata Consultancy Services Limited (TCS). 10

The init.ora parameter JOB_QUEUE_PROCESSES represents the maximum number of

1.1.3.8 Archiver Processes (ARCn)

1.1.3.9 Lock Manager Server Process (LMS)

© Copyright 2006, Tata Consultancy Services Limited (TCS). 11

1.1.3.10 Queue Monitor Processes (QMNn)

Introduction to an Oracle Instance

Fig 1.3 shows an Oracle Instance

The Instance and the Database

© Copyright 2006, Tata Consultancy Services Limited (TCS). 12

1.3Managing an Oracle Instance

Initialization Parameter Files

Among other things, the initialisation parameters tell Oracle:

 The name of the database for which to start up an instance

The following is an example of a parameter file

© Copyright 2006, Tata Consultancy Services Limited (TCS). 13