Roger Schrag, Database Specialists, Inc.
http://www.dbspecialists.com
LinuxWorld Conference and Expo ’99
August 9-12, 1999
San Jose, California
HTML Conversion by Michael Edwards
Contents
Why I Wrote This Tutorial · My Background
An Introduction to Oracle Technology
Platform Independence · Database Terminology · The Files That Control an Oracle Instance · The Files That Make Up an Oracle Database · The Oracle Process Architecture · Environment Variables · Installation Best Practices: OFA · Interacting with a Database · Oracle Quick Start
Installing the Oracle Software and Creating a Default
Database
Prepare the Server · Install the Oracle Software · Create an Oracle Database · Complete the Server Configuration · Summary
Starting and Stopping · Connectivity and Troubleshooting · Schemas, Tablespaces, and Segments · Fault Tolerance and Backups
Tuning Database Access
within an Application · Sizing the SGA · Balancing Disk I/O · Managing Database Connections · Tuning Sorts · Configuring Rollback
Segments
Oracle Documentation · Other Publications · Oracle Technology Network · Oracle User Groups · Free Web Resources
It’s
relatively easy to install an Oracle database on your Linux server. But to realize
the full scalability, availability, and performance potential that Oracle
offers, you need to know some important information about how Oracle works.
Most of us don’t have the time to read the dozens of books available on the
subject or attend weeks of Oracle training. This tutorial will teach Linux
system administrators or other technical folks new to Oracle the basics of how
to install and configure Oracle on Linux in such a way that the database will
offer good performance and reliability even as it grows very large.
First,
we’ll have a lightning fast introduction to Oracle specifically for system
administrators. We’ll look at the Oracle architecture, how it uses memory and
disk resources, the various files that make up an Oracle database, and a collection
of installation best practices known as the Optimal Flexible Architecture or
OFA.
Next,
we’ll discuss the steps required to install Oracle. We’ll talk about getting
your Linux server ready for Oracle, using the Oracle Installer, database creation
issues such as choosing a character set and a block size, resizing the default
database so that it will meet your needs, and creating your application schema.
Then
we’ll talk about administering your new database. This includes starting and
stopping the database, using administration tools, and troubleshooting
connectivity problems.
Next,
and perhaps most interesting, we’ll talk about how to keep your database
healthy as it grows. We’ll discuss tips for keeping your database manageable
and highly available, as well as how to tune it for optimal performance.
Finally,
we’ll look at resources available to people new to the Oracle arena in order to
learn more about the care and feeding of their Oracle databases and
applications.
Why
I Wrote This Tutorial
There
are several “how to” guides available on the internet today that will show you
how to install Oracle on Linux. Most of these guides were written by Linux
enthusiasts who are new to Oracle. Through persistence and sheer will (one
writer explains in his guide, “It took me 16 tries to get Oracle working”)
these folks have managed to get Oracle up and running, and they want to share
their knowledge and save you some misery.
I
want to share my knowledge also, but I approached the topic from a different
angle. I’ve worked with Oracle databases for over ten years, and I can almost
install Oracle on Solaris or HP-UX in my sleep. Linux was the new ingredient
for me.
I
wrote this tutorial to assist Linux users who are new to Oracle. But I am
hoping that this tutorial does more than just explain how to get Oracle to run
on your Linux box. My goal is to help you understand how it works, and to empower you to make it perform.
My
Background
I have been an Oracle DBA and application architect for
over ten years, and I am the founder of Database Specialists, Inc., a
consulting group specializing in business solutions based on Oracle technology.
We have extensive experience in e-commerce, Oracle Applications, and custom
applications involving Oracle databases.
An Introduction to Oracle
Technology
In
this section of the tutorial we will talk about the mantra of platform
independence, Oracle database terminology, the different files that make up an
Oracle database, the Oracle process architecture, the environment variables
Oracle cares about, a set of installation best practices, how administrators
interact with the database, and a quick start punch list to getting Oracle up
and running.
Platform
Independence
Oracle
Corporation strives toward platform independence for all of its products, and
to date they have been very successful. Oracle databases and application code
tend to be extremely portable, and Oracle skills garnered on one platform will
be extremely transferable to other platforms. This makes it possible for people
with lots of Oracle experience on other platforms to get up to speed very
quickly on Oracle on Linux.
Platform
independence manifests itself in the Oracle world in many ways. We’ll look at
documentation and platform abstraction.
Oracle
documentation comes in two varieties. There is the “generic” and the “platform
specific.” The bulk of Oracle’s documentation is the former and applies to all
platforms. When a generic manual needs to address a topic that is platform
specific, the manual will skirt the issue and refer the reader to the manual
specific to his or her platform. For each platform, Oracle publishes an
installation guide and an administrator’s reference manual that covers all the
platform specific details referenced in the generic documentation.
Oracle
implements platform independence by abstracting issues that tend to vary from
platform to platform. All application development, and as much database
administration as possible, will refer to the abstraction instead of the
underlying platform specific issue.
Consider
data storage as an example. Earlier versions of Windows were limited to eight
character case-insensitive file names, while Unix, VMS, and MVS all take
radically different approaches to naming and locating data files. Oracle gets
around this by storing data in “tablespaces.” A tablespace is a collection of
one or more operating system files, and each tablespace in the database has a
unique name not to exceed 30 characters. Applications and database administrators
almost always refer to tablespaces instead of their underlying operating system
files.
This
technique of abstraction has the side effect of introducing lots of
Oracle-specific terminology.
Database
Terminology
If
you invite a dozen Oracle database administrators over for dinner, you are
likely to get smothered in jargon and lingo. The terminology is inevitable, so
let’s go over some of the most commonly used terms. These are the terms you’ll
need to understand in order to read Oracle texts or get assistance from others
more familiar with Oracle:
DBA: These three letters stand
for “database administrator.” Everyone has their own idea of what a DBA does,
and many articles have been published on this single topic. A DBA is usually
involved in the logical design of a database, performs the physical design and
implementation, assists developers with difficult database issues, and is
responsible for ensuring good performance, proper security, and backup and
recovery strategies.
Database: In the Oracle arena, a
database is a collection of operating system files that make up one physical
data store or node. There can be many, many different types of data, each with
its own attributes, stored in one database. It’s not uncommon for users of
simple desktop database products to think of a database as one collection of
records, all with the same attributes. In Oracle, we would call that a table,
not a database.
Database Name: Every Oracle database has a
name, typically eight characters or less. There is also a global name, which is
the database name with a domain suffix. The default domain is “.WORLD” which is
fine for most purposes. In small to medium sized shops, it’s a good idea to
give each database a unique name and use one common domain.
Instance: An instance is a set of
operating system processes and shared memory structures that allow an
application to access data in a database. Database applications never access an
Oracle database directly. Rather, they communicate with Oracle server processes
that perform all database access on an application’s behalf. The processes that
make up the instance are responsible for such tasks as writing updates to disk,
coordinating distributed transactions with remote instances, and rolling back
incomplete transactions when an application crashes.
Note
that when a database is accessible to applications, it has an instance. In most
environments, there will be a one-to-one ratio between databases and instances.
But they are not the same thing and the two terms should not be used
interchangibly. When Oracle Parallel Server is used, multiple Oracle instances
(each running on a separate physical server) will access one common database.
Instance Name: Every Oracle instance has
a name. SID (standing for “system identifier”) is another term for instance
name. On Unix platforms instance names can be up to eight characters in length.
The purpose of the instance name is so that applications may identify which
instance they wish to access when multiple instances are running on one server.
(Consider a machine with four Oracle databases and four instances.) When Oracle
Parallel Server is not being used, the convention is to give the instance the
same name as the database it accesses. This will help preserve your sanity,
although it is technically not required.
SGA: The SGA, or system global
area, is a collection of shared memory structures created by an Oracle
instance. The SGA includes a cache of most recently accessed data blocks, a
cache of most recently executed SQL statements, latches for implementing
locking mechanisms, among many other things. The SGA is implemented on Unix
platforms as a collection of shared memory segments. Each process of the
instance, as well as each server process used by applications to access the
database, attaches itself to the SGA upon startup.
Starting the database: You open an Oracle
database to applications by starting up an instance and mounting it to the
database. This is often referred to as “starting the database.”
Net8 or SQL*Net: If an application wants to
access an Oracle database via an instance running on the same machine as the
application itself, an Oracle server process will be started and the
application will communicate with the server process through traditional
interprocess communication (IPC) mechanisms. But if the application needs to
access a database running on another server, then Oracle’s networking
infrastructure gets involved. Historically, this infrastructure was called
SQL*Net. But when Oracle introduced Oracle8, they changed the name to Net8.
Think of them as the same thing, although Net8 is newer and has some additional
features.
Client/Server: Oracle internally uses a
client/server architecture. Even if you are not developing a fat client
application using the client/server model, there still is a client/server
architecture at the database level. The application wishing to access Oracle is
seen as a client, and the Oracle server process is a server. The client sends requests
to the server, and the server satisfies them.
Username: Oracle supports many
authentication mechanisms, but the most common is a standard username and
password mechanism. Each user in an Oracle database has a unique name up to 30
characters in length. Separate usernames in the database, of course, don’t have
to map to separate real-life users. Twenty different application developers
could sign on to a database using the same username and password. Conversely,
one person could sign on to a database five times, each time specifying a
different username and password.
Schema: A schema is a separate
namespace for database objects such as tables, indexes, and stored procedures.
Every database object resides in exactly one schema. Each database user has
exactly one schema that bears the same name as their username. This makes the
term username synonymous with schema.
Data Dictionary: Each Oracle database has a
repository of information indicating all of the users defined in the database
and all of the objects they own (tables, indexes, stored procedures, and so
on). The data dictionary is actually a set of database tables with names like
user$ and tab$. You should never update these tables manually, but it’s fine
for you to query them if you like.
SYS and SYSTEM: Every Oracle database has two special users, SYS and
SYSTEM. Think of SYS the way you think of root on a Unix system. The SYS user
owns all of the internal structures that make up the data dictionary for the
database, and the SYS user also has every possible privilege on the entire
database. The SYSTEM user, meanwhile, has DBA privileges and is often used as a
generic DBA account.
Control Files, Data Files, Redo Logs,
and Parameter Files: These are the
physical files that make up an Oracle database. We’ll look at each of these
types of files in greater detail later in this section of the tutorial.
Tablespace: A tablespace is a named
collection of one or more physical files used for storing database objects.
Tablespaces mask certain implementation details from application developers.
For example, a DBA can rename a data file without affecting the name of a
tablespace, or an application developer can specify that certain data should be
stored in a particular tablespace without needing to know if the data will be
stored in one file or striped across several files.
Extent: An extent is one
contiguous chunk of physical storage within a tablespace. An extent can vary in
size from 2 Kb to 2 Gb, as long as the storage is contiguous.
Segment: A segment is a collection
of extents that belong to one object. Essentially, a segment is the physical
storage used to hold the data for an object. Most segments in a database will
hold the contents of a table or an index, but there are other types of segments
as well.
Rollback Segment: A rollback segment stores
“undo” information for a transaction that is still in progress. This allows
Oracle to back out the transaction if the application requests a rollback.
Rollback segments also allow Oracle to offer incredible concurrency by enabling
one session to update data while another session is querying the very same
data—the querying session can use the data in the rollback segment to
reconstruct what the data looked like before it was updated.
Temporary Segment: A temporary segment holds
a partial result set when too much data is involved to complete the operation
in memory. Temporary segments are most commonly used when sorting data for
ordering, grouping, or building an index.
Identifier: Most identifiers in
Oracle—whether names of users, tablespaces, tables, indexes, or even PL/SQL
variables—are limited to 30 bytes in length and are case-insensitive. To make
an identifier case-sensitive, or to use an Oracle reserved word as an
identifier, enclose the identifier in double quotes. Unless quoted, identifiers
must start with a letter and may include letters, digits, and certain (but not
all) special characters like _, $, and #.
The Files That
Control an Oracle Instance
An
Oracle instance is controlled by parameter files and optionally a password
file. Let’s look at these two types of files.
A
parameter file is a text file
containing a collection of name-value pairs that configure an Oracle instance.
There are over 200 configurable parameters. Each has a default value, and only
those that you wish to set to non-default values need to be listed in the
parameter file. When you start an Oracle instance, Oracle looks for a parameter
file named initSID.ora (where SID is the name of the instance) and uses the parameter
file to configure the instance. arameter files are read once when an instance
is started. Changes you make to a parameter file will not take effect until the
instance is shut down and restarted.
The
parameter file specifies such things as the location and name of the database
to mount, how much memory to set aside for a buffer cache, and the maximum
number of processes that will be able to access the instance. Figure 1 on the
next page shows a sample parameter file.
When
Oracle Parallel Server is not being used, all of the instance parameter
settings should be stored in one parameter file called initSID.ora. When Oracle
Parallel Server is used, some parameter settings will need to be the same for
all instances mounting the same database, while others can vary from instance
to instance. Therefore, when using Oracle Parallel Server, each instance should
have its own parameter file called initSID.ora containing settings for that
intance only, and all initSID.ora files should reference one common config.ora
file that contains the settings that need to be the same for all instances.
A password file indicates to the instance
the usernames and passwords of people authorized to start up and shut down the
instance. Once a database is open, a table in the database’s data dictionary is
used to authenticate users wishing to access the database. However, this
doesn’t help much if the database has not been opened yet. This is where the
password file comes in.
A
password file is often not used in smaller shops running Oracle on Unix,
because there is an alternative. Instead of checking a password file to see if
a user is authorized to start the instance, Oracle can ask the operation system
to authenticate the user. On Unix platforms this amounts to seeing if the user
belongs to a special Unix group, typically called “dba”. The net result is that
anybody with a Unix login that belongs to the dba group can start up and shut
down all instances running on the server.
Password
files are sometimes used in larger shops that have many databases. In such
shops the DBAs may use an Oracle tool called Enterprise Manager which allows
you to start up and shut down databases from a central location. In this
arrangement, the DBA never logs on to the actual server where the instance is
located, so operating system authentication cannot be used. Hence the need for
a password file.
#
# initMYDB.ora
# ============
#
# Parameter file for MYDB database.
#
#
# config parameters
#
control_files = (/u02/oradata/MYDB/control01.ctl,
/u03/oradata/MYDB/control02.ctl,
/u04/oradata/MYDB/control03.ctl)
background_dump_dest = /u01/app/oracle/admin/MYDB/bdump
core_dump_dest = /u01/app/oracle/admin/MYDB/cdump
user_dump_dest = /u01/app/oracle/admin/MYDB/udump
db_block_size = 8192
db_files = 80
db_name = MYDB
compatible = 8.0.5
rollback_segments = (r01,r02,r03,r04)
utl_file_dir = /u01/app/oracle/admin/MYDB/bdump
log_archive_dest = /u01/app/oracle/admin/MYDB/bk/arch
log_archive_format = %S.log
log_archive_start = true
#
# tuning parameters
#
shared_pool_size = 16000000
sort_area_size = 4096000
db_block_buffers = 2000
db_file_multiblock_read_count = 64
processes = 100
sessions = 100
log_checkpoint_interval = 10000
log_buffer = 163840
sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
job_queue_processes = 2
max_dump_file_size = 10240
Figure
1: A sample parameter file.
The Files That
Make Up an Oracle Database
An
Oracle database is made up of control files, data files, online redo logs, and
archived redo logs. Let’s look at each of these types of files.
Every
Oracle database has at least one control
file. If a database has multiple control files, then they are all identical
to each other. A control file is a relatively small (usually under 2 Mb) binary
file that contains information about the database such as its name, character
set, and the names of all data files and redo logs. Control files also contain
synchronization information used during recovery from a crash or media failure.
If
you lose every copy of the control file for the database, you stand to lose the
entire database. For this reason, an Oracle database will typically have three
control files located on separate physical devices. Since control files are
relatively small and incur little I/O, there is no reason not to use multiple
control files.
The
data files of a database hold all of
the segments of the database. This will include the actual application data, as
well as database structures such as the data dictionary, rollback segments, and
temporary segments. Each data file is part of exactly one tablespace. Every
Oracle database has at least one data file, belonging to the SYSTEM tablespace.
Data files take up the bulk of the physical storage used by the database. On
some versions of Unix including Linux, physical files are limited in size to 2
Gb. This won’t put much of a damper on the size of your Oracle database,
because one database can easily have over 1000 data files.
When
transactions update data in the database, Oracle updates blocks in the data
files. For fault tolerance purposes, Oracle also writes entries to the online redo log. These entries will
contain the minimal information needed by Oracle so that it could replay or
“redo” the transactions at a future time in order to bring a backup copy of the
database up to date.
The
online redo log consists of two or more physical files, typically the same
size, that form a circular buffer. The term “redo log files” refers to the
individual files that make up the online redo log. The online redo log will
typically consist of three or four such files, each having a fixed size
anywhere from 1 Mb to 100 Mb. The optimal size depends on the amount of
transaction activity and recoverability requirements for the database.
Initially
Oracle writes entries to the first redo log file. When this file fills up,
Oracle switches to the next. At this time Oracle begins flushing dirty buffers
from its buffer cache. After the last redo log file is filled, Oracle goes back
to the first one and overwrites it. Before Oracle overwrites a redo log file,
it will first make sure the flush of dirty buffers initiated when that log
filled has been completed.
The
online redo log allows Oracle to defer writes to data files while still being
able to recover the database with no data loss if the instance is terminated
abruptly without first flushing dirty buffers to disk.
In
production environments and other situations where data loss cannot be
tolerated, an archived redo log will
be maintained for the database in addition to the online redo log. The archived
redo log is a collection of physical files, each the exact size of the online
redo log files, that hold all redo log entries since the database was created.
Each file in the archived redo log will have a sequence number in the file name
so that you can quickly tell the proper ordering of the files.
When
an archived redo log is maintained for the database, the database is said to be
operating “in archivelog mode” (as opposed to “noarchivelog mode”). In
archivelog mode, Oracle will start copying an online redo log file to the
archived redo log as soon as it fills. Oracle will not begin overwriting the
online redo log until it has completed copying it to the archived redo log.
The Oracle
Process Architecture
An
Oracle database server will typically have an Oracle database, an instance,
Net8, and one or more database applications. The applications may run on the
same server as the database and instance (in which case Net8 might technically
not be required) but typically the applications are distributed among networked
computers.
An
Oracle instance consists of several daemon processes, all attached to the
shared global area or SGA. On Unix, the SGA is implemented as one or more
shared memory segments. On Windows NT, the entire Oracle instance is
implemented as one big process sharing one memory space which includes the SGA.
(On Windows NT the daemons are implemented as threads within the one process.)
The
daemons making up an Oracle instance are listed in Figure 2 below. Every Oracle
instance will have at least PMON, SMON, LGWR, and one DBWn. The rest are
optional and configurable. The “n” in a daemon name indicates a digit zero
through nine, meaning that there can be multiple copies of that daemon running.
|
Name |
Function |
Description |
|
PMON |
Process
monitor |
Process
watchdog; cleans up after crashed processes |
|
SMON |
System
monitor |
System
watchdog; performs system-wide management duties |
|
LGWR |
Log
writer |
Writes
entries to the online redo log |
|
DBWn |
Database
writer |
Writes
dirty buffers to the data files |
|
CKPT |
Checkpoint |
Updates
synchronization information in the control file and the headers of all data
files |
|
ARCH |
Archiver |
Copies
filled online redo log files to the archived redo log |
|
RECO |
Recoverer |
Coordinates
with remote instances in order to recover failed distributed transactions |
|
SNPn |
Job
queue |
Runs
queued jobs that have been scheduled to run at specified time intervals |
|
Dnnn |
Dispatcher |
Connects
an application requesting database services with a shared server that is
available to perform the services (multi-threaded server configuration only) |
|
Snnn |
Shared
server |
Services
database requests from applications (multi-threaded server configuration
only) |
|
LCKn |
Lock |
Coordinates
cache coherency with other instances that have mounted the same database
(Oracle Parallel Server only) |
Figure 2: The daemons making up an Oracle instance
A database
is made accessible to applications in three phases. First an instance is
started, then the database is mounted, and finally the database is opened.
Figure 3 on the next page shows a diagram of the Oracle process architecture,
and this will prove helpful for this discussion.
To start an instance Oracle reads the
parameter files for the instance, builds the SGA, and starts daemon processes
according to the parameter settings. Once the instance has been started, a number
of Oracle daemons will be running and each will be attached to the SGA. The SGA
will be correctly sized for when the database is opened, but at this time it
will be almost completely empty.
Figure 3: The Oracle process
architecture
To
mount the database, Oracle reads the
contents of the control file for the database and populates data structures in
the SGA. Parameters in the parameter files indicate the name of the database
and the location of the control files. This is how the instance determines
which database to mount. After the database has been mounted, most of the SGA
will still be empty and it will not yet be possible to access data in the
database or even the data dictionary for the database. However, it will be
possible to access control file type information in the SGA, such as the names
of the data files that make up the database.
To open the database, Oracle checks the
headers of all data files against synchronization information in the control
file in order to verify the state of the database. If the database was not shut
down cleanly the last time, Oracle will automatically perform what is called
“instance recovery” in order to make the database consistent again before
applications may access it. When performing instance recovery, Oracle will use
the online redo log as necessary to redo any transactions that had be committed
but were never written to disk, and the contents of the rollback segments to
undo any transactions that had not yet committed when the database was shut
down. (These two recovery steps are called “rolling forward” and “rolling
back,” respectively.)
Once
Oracle determines that the database is consistent, the database becomes
available for applications to access it. The database is now considered “open.”
Initially the buffer cache and shared SQL areas within the SGA will be nearly
empty. But they will fill, of course, as the database is accessed.
Applications
access an Oracle database by connecting to the instance, preparing and
submitting a request, and retrieving the results. The low-level API
applications use to access Oracle databases is called the Oracle Call
Interface, or OCI. There are a huge number of wrappers available to make the
API look different—Pro*C, JDBC, DBI/DBD, and ODBC are just a few of them. But since
these APIs ultimately boil down to OCI, they all will share similar concepts.
An
application connects to an Oracle instance by specifying a username, a
password, and optionally a Net8 identifier. If the desired instance is running
on the same server as the application itself, the application can set the
ORACLE_SID environment variable to the name of the instance. In this case, the
application will fork off an Oracle server process that attaches itself to the
SGA. Because the Oracle server process has its setuid bit set, the server
process will be able to read all of the data files for the database and read
and write to the SGA.
If
the application wishes to connect to an instance on a remote server, then Net8
will be involved. The application will either specify a Net8 alias for the
desired database or set the TWO_TASK environment variable to the Net8 alias.
Net8 will resolve the alias to a host name, instance name, and protocol
information (such as TCP/IP and a port number). The application will then send
a message using the correct protocol to the correct port number on the server
running the desired instance. A Net8 listener process running on the remote
server will fork off an Oracle server process and the application will relay
its database access requests through this server process.
Once
an application is connected to an Oracle instance, it submits database access
requests via its Oracle server process. Basically, the application prepares and
submits SQL or PL/SQL statements to the server process for execution. The
server process will read and write buffers in the SGA’s buffer cache as
required to satisfy the request, also reading blocks from data files into the
buffer cache when necessary and writing to the SGA’s redo log buffer as
necessary. It’s interesting to note, however, that the server process will not
write dirty buffers from the buffer cache back to data files. Nor will it write
redo log entries from the SGA’s redo log buffer to the online redo log files.
These tasks are relegated to DBWn and LGWR, respectively.
Applications
interact with the Oracle server process in a client/server fashion. The
application submits a request and the server process satisfies it. The Oracle
server process will only respond to the application’s requests, and will not
initiate activity on its own. In fact, if a DBA kills an idle database
connection, the application will not learn its connection has been terminated
until the next time it submits a request.
Applications
execute all PL/SQL calls and SQL statements other than queries by preparing the
statement and then submitting an instruction to execute the statement. Oracle
will return status information indicating success, how many rows were
processed, and so on. Oracle may also return values to the application.
Applications
execute SQL queries a little bit differently. First they prepare the statement
and then submit the instruction to execute it. The Oracle server process might
not do much processing at this point, however. The next step is for the
application to request a set of results. This may be one row or one hundred,
depending on what the application asks for. It is this request for results that
triggers the actual processing in the Oracle server process. The application
may ask for results repeatedly until the entire result set has been returned to
the application.
When
the application is finished accessing the Oracle database, it submits an
instruction to disconnect. This causes the Oracle server process to detach
itself from the SGA and exit.
Environment
Variables
When
an application connects to an Oracle instance, or when a DBA starts an instance
and opens a database, Oracle reads the settings of several environment
variables. These variables help Oracle find the correct libraries and determine
which database to access, among other things. Figure 4 shows the main
environment variables of interest to Oracle on Unix. (Windows NT uses the
registry to hold this information, instead of environment variables.)
|
Variable |
Purpose |
Sample Value |
Importance |
|
ORACLE_HOME |
Location
of Oracle software installation |
/u01/app/oracle/product/8.0.5 |
Required |
|
LD_LIBRARY_PATH |
Location
of Oracle shared libraries |
Include
$ORACLE_HOME/lib |
Required |
|
PATH |
Location
of Oracle executables |
Include
$ORACLE_HOME/bin |
Required |
|
ORACLE_SID |
Name
of local Oracle instance to access |
MYDB |
Optional |
|
TWO_TASK |
Net8
alias of Oracle instance to access |
MYDB |
Optional |
|
NLS_LANG |
Language
and character set of client application |
american_america.US7ASCII |
Optional |
|
ORA_NLS33 |
Location
of Oracle client NLS character set data |
$ORACLE_HOME/ocommon/nls/admin/data |
Optional |
|
ORACLE_BASE |
Location
of OFA directory structure |
/u01/app/oracle |
Optional |
|
DBA |
Location
of instance-related administrative files |
/u01/app/oracle/admin |
Optional |
Figure
4: Environment variables of interest to Oracle
ORACLE_HOME
must be set and LD_LIBRARY_PATH and PATH must include the right Oracle directory
in order for Oracle to locate the proper executables, libraries, and Net8
configuration files.
ORACLE_SID
or TWO_TASK may be set to allow an application to access a database without specifying
the desired instance explicitly. If both are set, then TWO_TASK will take
precedence. Also, if the application specifies a Net8 alias when connecting,
this will take precedence over both the ORACLE_SID and TWO_TASK environment
variables. Note that in Figure 4 both ORACLE_SID and TWO_TASK have the same
sample value. This can be misleading, as the ORACLE_SID contains the name of an
Oracle instance while TWO_TASK contains a Net8 alias. It just happens to be
good practice to name Net8 aliases the same as the Oracle instance they
reference.
NLS_LANG
and ORA_NLS33 indicate the language and character set of the client
application, and help Oracle locate resource files for converting between
character sets. These variables are not required if English and 7 bit ASCII are
to be used. For all other languages and character sets, however, both should be
set correctly. The formatting of the NLS_LANG variable is a bit odd—see the
platform specific installation guide for a list of supported values. Note that
early releases of Oracle for Linux may not support languages other than
English.
ORACLE_BASE
and DBA assist DBAs by allowing them to quickly navigate to certain important
directories in the Oracle hierarchy. The exact meaning of these variables will
become more clear when we look at installation best practices on the next page.
You
can set all of these environment variables in your login script—such as
.bashrc, .profile or .cshrc. You may choose to hardcode these variables to the
appropriate values for your implementation, or you may choose to integrate your
login script with scripts provided by Oracle expressly for setting up
environments. While it may seem easier at first to just hardcode everything,
integrating with the Oracle scripts is definitely the way to go. This will make
it much easier to support multiple instances running on one server, and will
minimize the complexity of future Oracle software upgrades. We will cover login
scripts in greater detail when we walk through the process of installing Oracle
software and building a default database.
Installation
Best Practices: OFA
Over
the years, the consulting group within Oracle Corporation developed a set of
best practices for installing and configuring Oracle databases. They called it
the Optimal Flexible Architecture, or OFA. Today the OFA is a relatively
compact set of rules that help lead to an Oracle installation that is easy to
support and highly scalable.
Around
1995 or so Oracle began integrating OFA concepts into the software installer on
the Unix platform so that performing a “default” installation would yield a
somewhat OFA compliant system. Oracle 7.3 and later install on Unix in a
reasonably good way (Oracle 8.0 and later on Linux, since Oracle 7.3 was never
released for Linux). It’s interesting to note that the Windows NT group at
Oracle was apparently off in their own world; Windows NT Oracle installations
don't approach any semblance of OFA compliance until Oracle8i.
The
administrator’s reference manual in Oracle’s platform specific documentation
contains a thorough explanation of the OFA. The basic benefits of the OFA are:
·
A
standard methodology that will be familiar to experienced Oracle DBAs
·
A
naming convention that is simple and makes sense
·
A
standard for locating files that makes things easy to find
·
Support
for multiple databases and/or versions of Oracle software on one server
·
A
separation between Oracle software and database files, making upgrades easier
·
A
well thought out operating system security policy
·
Distributing
I/O across many physical storage devices
The
OFA guidelines cover the following areas:
·
Assigning
and naming mount points
·
Directory
hierarchy for Oracle software, database files, and administrative files
·
Naming
conventions for all files that make up a database or control an instance
·
Naming
tablespaces
·
Assigning
segments to tablespaces
Figure
5 shows the basic directories of an OFA compliant Oracle installation, and
Figure 6 shows sample directory and file names for an OFA compliant Oracle
database.
|
Directory |
Description |
|
$ORACLE_BASE |
Root of the OFA directory structure |
|
$ORACLE_BASE/product/<version> |
Oracle software installation, or ORACLE_HOME for a specific version
of Oracle software |
|
$ORACLE_BASE/admin/$ORACLE_SID |
Administrative area for a specific instance. Subdirectories include: - pfile - bdump - cdump - udump - create |
|
<mount point>/oradata/<dbname> |
Data files, online redo logs, and control files for a specific
database |
Figure
5: Basic directories of an OFA compliant Oracle installation
|
File or Directory |
Location |
|
ORACLE_BASE |
/u01/app/oracle |
|
ORACLE_HOME |
/u01/app/oracle/product/8.0.5 |
|
MYDB admistrative file tree |
/u01/app/oracle/admin/MYDB |
|
MYDB parameter file |
/u01/app/oracle/admin/MYDB/pfile/initMYDB.ora |
|
MYDB alert log |
/u01/app/oracle/admin/MYDB/bdump/alert_MYDB.log |
|
MYDB control files |
/u02/oradata/MYDB/control01.ctl /u03/oradata/MYDB/control02.ctl /u04/oradata/MYDB/control03.ctl |
|
MYDB online redo logs |
/u02/oradata/MYDB/redo01.log /u02/oradata/MYDB/redo03.log /u03/oradata/MYDB/redo02.log /u03/oradata/MYDB/redo04.log |
|
MYDB data files |
/u04/oradata/MYDB/system01.dbf /u04/oradata/MYDB/temp01.dbf /u05/oradata/MYDB/rbs01.dbf /u06/oradata/MYDB/table01.dbf /u07/oradata/MYDB/index01.dbf |
Figure
6: Sample directory and file names for an OFA compliant Oracle database
Interacting
with a Database
Oracle
provides two very basic tools for interacting with an Oracle database. While
application developers will probably interact through a wide selection of APIs
and end users will access data through applications or query tools, the DBA is
often left to use Oracle’s basic tools.
SQL*Plus is a simple character mode
tool that lets you connect to an Oracle instance and submit arbitrary SQL
statements and PL/SQL code. The tool offers rudimentary output formatting
capabilities, allowing SQL*Plus to be used as a very crude reporting tool.
SQL*Plus also has scripting capabilities. Although the scripting features seem
prehistoric compared to modern languages and tools, you can do a surprising
amount of automation with SQL*Plus scripting.
Server Manager is an even more bare-bones
character mode tool. Like SQL*Plus, it lets you connect and submit arbitrary
commands. The output formatting and scripting capabilities of Server Manager
are even more limited than those of SQL*Plus. However, Server Manager allows you
to startup and and shut down Oracle instances—something you cannot do with
SQL*Plus.
DBAs
who want to get beyond basic tools have an increasing number of options
available. Oracle now offers Enterprise Manager, a GUI tool for managing
multiple Oracle databases. Depending on your Oracle licensing arrangement,
certain modules of Enterprise Manager may be available to you free of charge.
Unfortunately, the Enterprise Manager that comes with Oracle releases prior to
Oracle8i is quite limited. For one thing, the earlier version only runs on the
Windows platform. For another, it stores a lot of important information on the
Windows client, undermining the possibilities of remote administration or
having a team of Oracle DBAs share duties.
The
newer version of Enterprise Manager shipping with Oracle8i is Java based, and
is supposed to run from a web browser. This might make it a more appealing
database administration tool.
Besides
Enterprise Manager, there are a host of other DBA tools available today. Some
of the more basic ones are available for free on the internet, while a whole
host of companies are marketing very complex and sophisticated products for
equally sophisticated prices.
Oracle
Quick Start
In
order to get up and running on Oracle, you’ll first need to install the Oracle
software and create a default database. Next you’ll want to adjust the default
database to improve its OFA compliance and make the database more usable. (The
default database won’t get you very far, unless you will just be playing around
with a few Kb of data.)
Once
you’ve got a viable database running, you are ready to create your application
schemas and establish connectivity between your applications and the database.
Now you are set to do your application development.
Soon
it becomes time to think about real world issues such as capacity planning,
performance, security, backup and recovery, and implementing schema or
application changes while you are already in production.
In
the next section of this tutorial, we’ll walk through the process of installing
Oracle software and creating a default database. We’ll go through this in
blistering detail, because there are many issues and lots of “gotchas” that
lead people new to Oracle technology to the conclusion that Oracle is overly
complicated and difficult to install. Actually, it’s all very simple if you
have the right information.
Installing the Oracle
Software and Creating a Default Database
In
this section of the tutorial we will walk through the steps of installing Oracle
8.0.5 Standard Edition in a Red Hat 5.2 Linux environment and creating a
default database. Subsequent sections will look at how to manage the database
and optimize performance. In this section we will drill down to a great level
of detail so that even the system administrator who is new to Oracle can get up
and running quickly.
Linux
is extremely similar to flavors of Unix such as Solaris. About 95% of the
material here is directly applicable to other Unix platforms. The main areas of
divergence are the setting of the operating system kernel parameters and the
locations of the oratab file and local bin directory.
There
are four phases to getting Oracle up and running on your database server:
1.
Prepare
the server
2.
Install
the Oracle software
3.
Create
an Oracle database
4.
Complete
the server configuration
We
will walk through these phases one at a time, detailing all the steps involved.
The end result will be a very usable database that can be scaled up quite
large. Of course, every implementation is unique, and you will need to evaluate
each step carefully against your particular requirements. But this section of
the tutorial will get you off to a very solid start.
Prepare
the Server
These
steps configure your machine so that it will be ready to accept the Oracle
software and database. In this section, we will make sure the operating system
meets Oracle’s minimum requirements, create a special user and group to “own”
the software, and create some directories that will be used by the Oracle
software and database. All of the steps in this section are run as the root
user.
1.
Make
sure that your version of Linux is sufficient. Oracle works with many different
distributions of Linux. The key requirements are that the kernel be 2.0.33 or
higher, and GNU C libraries be 2.0.6 or higher. Many users have reported that
the GNU C libraries must in fact be 2.0.6 or 2.0.7–that the “or higher” is not
true. You will greatly simplify your life if you work with the Red Hat 5.2
distribution. This package is reasonably priced, readily available, and known
to work very well with Oracle 8.0.5 Standard Edition. Note that the Red Hat 6.0
distribution includes GNU C libraries 2.1, and many users have reported
difficulties installing Oracle on Linux systems that use GNU C libraries 2.1.
(Oracle has a patch available, if you really want to use the newer GNU C
libraries.)
2.
Make
sure that your hardware is sufficient. You’ll need at least 32 Mb RAM, a CD ROM
drive, and about 500 Mb of disk space. This will let you create a small database
for prototyping; a real implementation will likely require more RAM and more
disk space.
3.
Make
sure that the Linux kernel has parameters set sufficiently high for Oracle. The
Oracle architecture makes heavy use of shared memory segments for sharing data between
multiple processes and semaphores for handling locking. Many operating systems,
such as Solaris, do not by default offer sufficient shared memory or semaphores
for maintaining an Oracle database. Happily, Red Hat Linux 5.2, by default,
builds a kernel that will support most Oracle implementations.
|
Kernel Parameter |
Red Hat 5.2 Default |
Purpose |
|
SHMMAX |
0x2000000 (32 Mb) |
Maximum size of a single shared memory segment |
|
SHMMIN |
1 |
Minimum size of a single shared memory segment |
|
SHMMNI |
128 |
Maximum number of shared memory segments in entire system |
|
SHMSEG |
128 |
Maximum number of shared memory segments one process can attach |
|
SEMMNS |
4096 |
Maximum number of semaphores in entire system |
|
SEMMNI |
128 |
Maximum number of semaphore sets in entire system |
|
SEMMSL |
32 |
Maximum number of semaphores per set |
The first four kernel parameters configure shared
memory segments. The Red Hat 5.2 defaults allow your database SGAs to get as big as you could possibly want,
when you consider that Linux is limited to 2 Gb of physical memory. If a
database has an SGA bigger than 32 Mb, it will be spread across multiple shared
memory segments. This is not a concern.
The last three kernel parameters configure
semaphores. Each Oracle instance requires one semaphore for each process.
Oracle does not seem to care how many semaphores are in each set, but it
appears that one instance cannot handle more than 30 sets. The Red Hat 5.2
defaults allow you to have four instances on one server, with each instance
having about 960 processes. This should be sufficient for most implementations.
Recompiling a Linux kernel is somewhat complicated
and definitely requires that you know what you are doing. We won’t cover it
here, but you probably won’t need to recompile your kernel to install Oracle,
anyway.
4.
Create
a Linux group that will be used by the Oracle software owner and database administrators.
You can call it what you like, but the standard is “dba”. If you will be
installing Oracle on multiple Linux servers on your network, you might want to
keep the groupid the same on all servers. I created my dba group with the
command:
groupadd -g 300 dba
5.
Create
a Linux user that will be the Oracle software owner. You can call it what you
like, but the standard is “oracle”. If you will be installing Oracle on
multiple Linux servers on your network, you might want to keep the userid the
same on all servers. Note that this user’s home directory will not be the
ORACLE_HOME or where the actual Oracle software is installed; this user’s home
directory should be in the same place as other users’ home directories. This
user’s initial group should be the dba group created in the previous step. I
created my oracle user with the commands:
useradd -c 'Oracle software owner' -d /home/oracle -g dba -m -n \
-s /bin/bash -u 300 oracle
passwd oracle
6.
Create
mount points for the Oracle software and the Oracle database. Each mount point
should correspond to a separate physical disk device. You’ll need at least one
mount point. Typically you use one mount point for the Oracle software and as
many as you can afford for each database. (More physical devices allow better
performance.) A nice convention is to call the mount points /u01, /u02, and so
on. Because mount points are typically owned by root and the Oracle installer
will run as the oracle user and not root, you should create some subdirectories
now to avoid permissions problems later. Create an app subdirectory below the
software mount point, and oradata subdirectories below the mount points to be
used for databases. (You can put software and a database on the same mount
point if you wish.) Make these subdirectories owned by the oracle user and dba
group, and give them 755 permissions.
7.
Choose
a directory that the Oracle software will refer to as the “local bin”
directory. A common choice is /usr/local/bin, and your installation will go
more smoothly if you stick with this choice. Make sure this directory is on
users’ path by default.
8.
If
you downloaded a trial version of Oracle off of the internet, then untar the
distribution. If you have the software on CD ROM, then mount the CD ROM now. I
use automount to mount my CDs, but alternatively you can use a command like:
mount -t iso9660 /dev/cdrom /cdrom
9.
Create
the /etc/oratab file. After installation, this will be a plain text file that
briefly describes the Oracle software installations and databases on the
server. The file must exist before installation can begin. The easiest way to
create the file is simply to create an empty file called /etc/oratab and change
the ownership to oracle, the group to dba, and the permissions to 664.
Alternatively, you can set the ORACLE_OWNER environment variable and execute a
shell script on the CD-ROM:
ORACLE_OWNER=oracle export ORACLE_OWNER cd /cdrom/orainst /oratab.sh
Install the Oracle Software
These
steps install the Oracle database software onto your server so that you will
then be able to create databases and use server-side tools like SQL*Plus and
Server Manager. In this section, we will prepare the oracle user’s environment,
run the Oracle Installer, and tidy up a few minor messes that the installer
leaves behind. We will create a database later by invoking the Oracle Installer
a second time. This allows us to choose our own database block size instead of
being forced to use the default. All of the steps in this section, except where
noted, are run as the oracle user.
1.
Edit
the oracle user’s login file so that the environment will be configured
automatically on login. If you are using Bash, then edit the .bashrc file. If
you are using Bourne or Korn shell, then edit .profile. For now, we will
hardcode certain things. But after the software and database are installed, we
will come back and eliminate all hardcodings. Here is what I added to my
.bashrc:
umask 022
# Substitute your Oracle software mount point in the line below.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.0.5
# Substitute the name of your Oracle database below.
export ORACLE_SID=MYDB
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
# Substitute terminal type in line below. vt100 or vt220 is
# recommended.
export ORACLE_TERM=vt100
# Following two lines are not required if you’ll be using the
# default character set, 7-bit ASCII. To use another character
# set, see Appendix C of the Installation Guide and change
# NLS_LANG accordingly.
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG=american_america.US7ASCII
# Fill in the following line as you wish, but make sure that
# $ORACLE_HOME/bin, /bin, /usr/bin, and “local bin” are all
# in the PATH.
export PATH=...
# If your /var/tmp doesn’t have at least 20 Mb free for the
# Oracle Installer to use (or is not writable by oracle), then
# complete the following line.
export TMPDIR=...
# Ensure that SRCHOME and TWO_TASK are not set.
# If you will be using Java, include the following two lines:
export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes111.zip
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdbc/lib
2.
Log
out and log back in as the oracle user so that the environment is set
correctly.
3.
Use
ftp to transfer a small file to a remote host to prove to yourself that TCP/IP
networking is installed and working properly on your server.
4.
Ensure
that the mount point you designated for the Oracle software has sufficient free
space. Allow 400 Mb as a bare minimum.
5.
Double
check that you are logged in as oracle and not root. Then start the Oracle
Installer. I recommend that with this release of Oracle, you simplify your life
and use the character mode version of the installer. To do this, make sure your
DISPLAY environment variable is not set, and that ORACLE_TERM is set
accordingly. Start the installer with these commands:
cd /cdrom/orainst ./orainst
We’ll
walk through the installer prompts one at a time:
a.
Choose
Custom Install.
b.
Read
two not very interesting readme files.
c.
Choose
Install, Upgrade, or De-Install Software.
d.
Choose
Install New Product – Do Not Create DB Objects.
e.
Confirm
settings for ORACLE_BASE and ORACLE_HOME. These should be correct, because you
set your environment variables beforehand.
f.
Confirm
log file locations. The Oracle Installer will write four log files in the $ORACLE_HOME/orainst directory.
g.
Choose
Install from CD-ROM.
h.
Choose
the language you want your Oracle installation to support. This is the language
that banners, prompts, and error messages will be displayed in. The languages
available for your data stored in the database will be dictated by the
character set you choose when creating the database, and not by how you answer
this installer prompt. I chose American English.
i.
Confirm
the root.sh location. This file will contain post-installation steps to be
performed by the root user. If you run the installer multiple times, you’ll be
prompted to move the previous root.sh file to another location.
j.
On
the Software Asset Manager screen, select the products you wish to install and
choose the Install button. Note that selecting products that have sub-products
(like “Net8 Protocol Adapters”) doesn’t do anything—you need to select the
sub-products themselves. Here’s what I installed:
· Client Software
· Net8
· TCP/IP Protocol Adapter
· Unix Installer
· JDBC Drivers
· Server Documentation
· Standard RDBMS
· PL/SQL
· Pro*C/C++
· SQL*Plus
I recommend installing the Unix Installer. This will
make it easier to perform maintenance operations (such as relinking) in the
future without having to dig out the CD ROM. Also, note that the Server
Documentation selection will only install Linux platform-specific
documentation; the generic Oracle product documentation is contained on a
separate CD ROM.
k.
Choose
the Linux group you created in the previous section as the DBA group. Any Linux
user belonging to this group will have full database administration privileges
over all databases on this server.
l.
Choose
the Linux group you created in the previous section as the osoper group. Any
Linux user belonging to this group will have “operator” privileges over all
databases on this server. You could use a separate Linux group for this
purpose, but most installations do fine with using the same Linux group for
both DBA and osoper.
m.
Choose
the JDBC drivers you wish to install (if you selected JDBC drivers on the
Software Asset Manager screen). There is support for JDK 1.0 and 1.1, and there
are drivers that require Net8 or drivers that run on a thin client.
n.
Confirm
the directory for your online documentation, if you chose to install it. The
default is $ORACLE_HOME/doc, and this seems like a good choice.
o.
Choose
whether to install documentation in HTML format, PDF format, or both. Remember,
this only applies to the Linux platform-specific documentation.
p.
At
this point, the Oracle Installer installs software onto your server.
q.
I
encountered one glitch during the software installation. You will not encounter
this problem unless you chose to install Server Documentation. The installer
reported a write error, which may have occurred because the installer tried to
copy a file without creating the directory first. I fixed this problem easily
by going to another window on the server and creating the directory specified
in the error message. Then I chose the Retry option in the Oracle Installer,
and things continued normally.
r.
When
the installation is complete you’ll receive a message that the requested
actions have been performed. Choose OK, and you will be returned to the
Software Asset Manager screen.
s.
Exit
the installer.
6.
In
$ORACLE_HOME/bin you will find a shell script called oraenv. This script can be
called from .bashrc or .profile to set up a user’s environment. Unfortunately,
there are several variables that the script does not set—some handy, some very
important. Make a backup copy of the script and then edit it, adding the
following lines to the very end:
# Begin customizations
ORACLE_BASE=`dirname $ORACLE_HOME`
ORACLE_BASE=`dirname $ORACLE_BASE`
DBA=$ORACLE_BASE/admin
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
# Substitute character set you plan to use in following line.
NLS_LANG=american_america.US7ASCII
export ORACLE_BASE DBA ORA_NLS33 NLS_LANG
case "$LD_LIBRARY_PATH" in
*$OLDHOME/lib*) LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | \
sed "s;$OLDHOME/lib;$ORACLE_HOME/lib;g"` ;;
*$ORACLE_HOME/lib*) ;;
*:) LD_LIBRARY_PATH=${LD_LIBRARY_PATH}$ORACLE_HOME/lib: ;;
"") LD_LIBRARY_PATH=$ORACLE_HOME/lib ;;
*) LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib ;;
esac
# Following case statement only required if using JDBC.
case "$LD_LIBRARY_PATH" in
*$OLDHOME/jdbc/lib*) LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | \
sed "s;$OLDHOME/jdbc/lib;$ORACLE_HOME/jdbc/lib;g"` ;;
*$ORACLE_HOME/jdbc/lib*) ;;
*:) LD_LIBRARY_PATH=${LD_LIBRARY_PATH}$ORACLE_HOME/jdbc/lib: ;;
"") LD_LIBRARY_PATH=$ORACLE_HOME/jdbc/lib ;;
*) LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdbc/lib ;;
esac
export LD_LIBRARY_PATH
# End customizations
7.
I
made a further edit to $ORACLE_HOME/bin/oraenv that you may or may not need. After
editing my .bashrc to call oraenv each time a new shell is created (we’ll do
this in a future step) I found that sometimes my server would run out of
processes. I have not seen this behavior on other Linux servers, so I might
have something peculiar in my configuration. If your server starts giving you
vfork errors further down in the installation process, then come back and
perform this step. Otherwise, you may wish to try and get by without it. The
edit is as follows: Find the line in $ORACLE_HOME/bin/oraenv that contains a
call to dbhome. Replace it with the following:
# Begin customizations
#ORAHOME=`dbhome "$ORACLE_SID"`
# Following two lines taken from dbhome:
ORATAB=/etc/oratab
ORAHOME=`awk -F: "/^${ORACLE_SID}:/ {print \\$2; exit}" \
ORATAB 2>/dev/null`
# End customizations
8.
In
$ORACLE_HOME/bin you’ll find a script called dbshut. This is a utility that you
can run to shut down databases on the server. Unfortunately, it shuts down databases
with normal priority. This means that if any users are logged into a database,
the shutdown will hang until they log out. You might want to change this script
to shut down databases with immediate priority. To do this, find the two lines
that contain just the word “shutdown”. Change these to read “shutdown
immediate”.
9.
Look
at the script $ORACLE_HOME/orainst/root.sh. This is a script that the installer
created during software installation. It’s a collection of commands that need
to be run as the root user. You should review the script carefully and decide
whether to run it as is, or hand-pick the commands you wish to run as root.
Running the script as is creates more problems than it fixes. Really, all you
need to do as root is copy oraenv, dbhome, and coraenv from $ORACLE_HOME/bin to
the local bin directory. If you do choose to run root.sh, please be advised
that there is a documented serious security problem. root.sh will set the
setuid bit on several files in $ORACLE_HOME/bin, and change some of them to be
owned by root. Nothing in
$ORACLE_HOME/bin should be owned by root, and only the oracle and dbsnmp
executables should have a setuid bit turned on. Oracle Corporation has released
an emergency patch called setuid_patch.sh to address this problem. The problem
is also present on all Unix releases of Oracle8 and Oracle8i through and
including 8.1.5.
10.
Whether
or not you run root.sh, double-check a few file permissions. The permissions on
$ORACLE_HOME/bin/oracle should be 6751. My installation came out as 4755. Also,
oraenv in $ORACLE_HOME/bin and the local bin directory should have permissions
755. My installation came out as 777.
Create
an Oracle Database
These
steps create an Oracle database on your server. In this section we will create a
default Oracle database, tweak the default configuration so that the database
is actually usable, create application users and tablespaces in the database,
and configure Net8. All of the steps in this section are run as the oracle
user.
1.
Choose
a block size for your Oracle database. The default is 2 Kb, and this is
appropriate for very small demo or “sand box” databases. If you anticipate your
database growing beyond perhaps 100 Mb in size, you should use a block size of
8 Kb or 16 Kb.
2.
The
Oracle Installer will not ask you what block size you want, and you cannot
change a database’s block size after creation. If you want your database to
have a block size other than 2 Kb, then you will need to edit the file
$ORACLE_HOME/rdbms/install/rdbms/cnfg.orc to set the db_block_size to 8192 or
16384. Make sure the line is not commented out.
3.
Double
check that you are logged in as oracle and not root. Then start the Oracle
Installer. Again, I recommend that with this release of Oracle you simplify
your life and use the character mode version of the installer. To do this, make
sure your DISPLAY environment variable is not set, and that ORACLE_TERM is set
accordingly. This time we will run the Oracle Installer installed on your
system; we no longer have a need for the software CD ROM. Start the installer
with these commands:
cd $ORACLE_HOME/orainst ./orainst
We’ll walk through the installer prompts one at a
time:
a.
Choose
Custom Install.
b.
Read
two not very interesting readme files.
c.
Choose
Create/Upgrade Database Objects.
d.
Choose
Create Database Objects.
e.
Confirm
settings for ORACLE_BASE and ORACLE_HOME. These should be correct, because you
set your environment variables beforehand. Note that the installer will want
the parent directory above ORACLE_BASE to be writable by the oracle user. This
does not make a lot of sense, but the easiest thing to do is humor the
installer and open up permissions on that directory (for example, /u01/app)
temporarily. After the database is created, you can lock down the permissions
again.
f.
Confirm
log file locations. The Oracle Installer will write four log files in the
$ORACLE_HOME/orainst directory.
g.
Confirm
the setting for ORACLE_SID. This should be correct, because you set your
environment variables beforehand.
h.
On
the Software Asset Manager screen, you’ll see a list of all products that have
been installed. Select every product except the Online Text Viewer and choose
Install. This will cause Oracle to do whatever database setup is required for
each product. Many products (like the TCP/IP protocol adapter) don't have any
database objects associated with them, but some (like SQL*Plus and PL/SQL) do.
It’s safest to select all products, but don't select the Online Text Viewer
because the installer will give you an error message if you do.
i.
Choose
Create Product DB Objects for Server Manager.
j.
Choose
Filesystem-Based Database (unless you want to deal with raw devices, in which
case you are on your own).
k.
Choose
No to Distribute control files over three mount points. The default database
that the installer builds for you will need tweaking whether you distribute
files across three mount points or not. The mess is a little easier to clean up
if it’s all in one directory instead of spread across three.
l.
Enter
the mount point where the installer should create the database—for example,
/u01. You should have already created an oradata subdirectory under the mount
point and made it owned by the oracle user, or else you will run into file
permission problems.
m.
Choose
a character set for the database. US7ASCII is the default on Linux and Unix.
(It is not the default on Windows NT.) If you want to use a different character
set, then you should have set NLS_LANG and ORA_NLS33 before invoking the
installer. You cannot change a database’s character set after creation. If you
want to store non-English text in your database, such as umlauts and
diacritical marks, then you should definitely not use US7ASCII as your
character set. See Appendix C of the Installation Guide for a list of
recognized character sets.
n.
Choose
a national character set for the database. This is the character set to use for
storing data of type NCHAR, NVARCHAR2, and NCLOB. Again, see Appendix C of the
Installation Guide for a list of recognized character sets.
o.
Enter
passwords for the SYSTEM and SYS users. Remember that Oracle passwords are not
case-sensitive, must start with a letter, and certain special characters are
not allowed. I’ve heard that putting a period in the SYSTEM or SYS password
will cause the installer to crash, but I’ve never tried it.
p.
If
you want to be able to start up and shut down this database without actually
logging on to the server, then choose Yes when asked, “Do you want to set the
passwords for the internal users (dba and operator)?” This will cause the
installer to create a password file external to the database. This is necessary
if you will be controlling your database remotely from Enterprise Manager.
q.
Enter
a password for the Net8 listener.
r.
Choose
whether or not you want the installer to configure the multi-threaded server
(MTS) on your database. MTS is typically not appropriate unless there will be
large numbers of OLTP users.
s.
Choose
Yes and OK repeatedly to accept the names and sizes that the installer is
proposing for your database files. Actually these defaults are pretty awful,
but it’s much easier to fix later.
t.
You
might be asked which JDK drivers you wish to install. This is not relevant to
database creation, so just choose them all and humor the installer.
u.
You
will be asked if you would like to load the SQL*Plus help facility. You should
probably choose No, as the HTML and PDF documentation is infinitely more
useful. But if you do like the SQL*Plus help facility, feel free to install it.
v.
You
will be asked if you would like to load the SQL*Plus demo tables. These include
the venerable emp and dept tables in the scott/tiger schema. If you are new to
Oracle or would like an easy schema to start testing with, then choose Yes. In
most cases, however, you’ll chose No.
w.
The
installer now creates the database and leaves it running.
x.
When
the database creation is complete you’ll receive a message that the requested
actions have been performed. Choose OK, and you will be returned to the
Software Asset Manager screen.
y.
Exit
the installer.
4.
The
Oracle Installer probably told you to run root.sh again, but you definitely
should not.
5.
Configure
and run the Net8 listener. Edit $ORACLE_HOME/network/admin/listener.ora to suit
your needs. You’ll need to fill in the ORACLE_SID. You might need to change the
host name or IP. (In my case my server is multi-homed, but I only want the
database to accept connections from the internal network.) You will probably
want to leave the extproc settings as they are; extproc is part of the
mechanism that allows PL/SQL to call out to procedures outside the database.
You may set global_name to the host name. This will allow clients to connect to
the database by specifying the host name as the Net8 alias—in this way clients
don’t need to have a tnsnames.ora file. My listener.ora file looks like this:
#
# Filename: Listener.ora
#
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= MYDB))
(ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY))
(ADDRESS= (PROTOCOL= TCP)(Host= 192.168.1.1)(Port= 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= myhost.dbspecialists.com.)
(ORACLE_HOME= /u01/app/oracle/product/8.0.5)
(SID_NAME = MYDB)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /u01/app/oracle/product/8.0.5)
(PROGRAM = extproc)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
6.
Prepare
a tnsnames.ora file in $ORACLE_HOME/network/admin on the server and distribute
it to all clients. Edit the default file to suit your needs. Fill in the ORACLE_SID. Change the host name
or IP if needed. My tnsnames.ora file looks like this:
#
# Filename: Tnsnames.ora
#
extproc_connection_data =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = MYDB))
(CONNECT_DATA = (SID = extproc))
)
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= 192.168.1.1)(Port= 1521))
(CONNECT_DATA = (SID = MYDB))
)
MYDB_BEQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = BEQ)(PROGRAM = /u01/app/oracle/product/8.0.5)
(argv0 = oracleMYDB)
(args = '(DESCRIPTION = (LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
(envs = 'ORACLE_HOME=/u01/app/oracle/product/8.0.5,ORACLE_SID=MYDB')
)
)
7.
Create
bdump, cdump, and udump directories in $ORACLE_BASE/admin/$ORACLE_SID. The
installer on Linux violates the OFA standard in the placement of these three
directories, and we will move them to where they belong.
8.
Shut
down the database.
9.
The
default database was created with three control files all in one directory.
Move two of the control files to other directories that are on separate
physical devices.
10.
The
default parameter file that the installer created for the database is deficient
in several ways. You should move the three parameter files in
$ORACLE_BASE/admin/$ORACLE_SID/pfile to a backup directory and create a new
parameter file from scratch. Some of the things you should correct or improve
upon are:
a.
You
might want to merge the config.ora file contents into the init.ora file. Unless
you are using Oracle Parallel Server, there is not a lot of value in spreading
the parameters between two files.
b.
Update
the control_files parameter to reflect the new control file locations.
c.
Update
the background_dump_dest, user_dump_dest, and core_dump_dest parameters to
comply with the OFA standard.
d.
Update
the shared_pool_size, db_block_buffers, and sort_area_size parameters to
reasonable values based on how much physical memory your server has available.
e.
Update
the sessions and processes parameters based on how many concurrent sessions you
anticipate.
#
# initMYDB.ora
# ============
#
# Parameter file for MYDB database.
#
# config parameters
#
control_files = (/u02/oradata/MYDB/control01.ctl,
/u03/oradata/MYDB/control02.ctl,
/u04/oradata/MYDB/control03.ctl)
background_dump_dest = /u01/app/oracle/admin/MYDB/bdump
core_dump_dest = /u01/app/oracle/admin/MYDB/cdump
user_dump_dest = /u01/app/oracle/admin/MYDB/udump
db_block_size = 8192
db_files = 80
db_name = MYDB
compatible = 8.0.5
rollback_segments = (r01,r02,r03,r04)
#
# tuning parameters
#
shared_pool_size = 16000000
sort_area_size = 4096000
db_block_buffers = 2000
db_file_multiblock_read_count = 64
processes = 100
sessions = 100
log_checkpoint_interval = 10000
log_buffer = 163840
sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
job_queue_processes = 2
max_dump_file_size = 10240 # limit trace file size
# to 5 Meg each
11.
Restart
the database so that the new parameter settings take effect.
12.
Tweak
the default install so that the database will be usable for more than trivial
tasks and will be capable of offering decent performance. While later sections
of this tutorial will go into this in greater detail, here is an overview of
some of the things you’ll probably want to do:
a.
Relocate
data files to spread them over multiple physical devices.
b.
Enlarge
the online redo logs—the default log size of 500 Kb is often too small. Since
you can’t resize online redo logs, you’ll need to drop and recreate them. At
the same time, you can give the logs OFA compliant names, and you can relocate
them as appropriate to distribute I/O.
c.
Resize
the temporary tablespace—the default size of 1 Mb won’t be big enough unless
your database will be extremely small.
d.
Alter
the temporary tablespace to give it a content type of TEMPORARY and appropriate
default storage parameters. Also, make sure all users including SYS and SYSTEM
have the TEMP tablespace designated as their temporary tablespace.
e.
Resize
the rollback segment tablespace—the default size of 15 Mb won’t be big enough
unless your database is somewhat small.
f.
Depending
on the anticipated size of your database and the expected number and type of
concurrent users, you may want to adjust the number of rollback segments and their
storage parameters. At the very least, you’ll probably want to set the OPTIMAL
storage parameter for each rollback segment except SYSTEM.
g.
Adjust
the sizing and default storage of the TOOLS and USERS tablespaces as needed if
you will be using these tablespaces.
13.
Create
new tablespaces for holding application segments. Create separate tablespaces
with data files on separate physical devices for tables and indexes. You may
want to split your application segments into several tablespaces, based on
object size, permanence, volatility, I/O volume, or any of a number of other
criteria.
14.
Choose
default storage parameters carefully for each application tablespace. One
strategy I highly recommend is as follows:
a.
Set
INITIAL to either 128k, 4m, or 128m, depending on the planned sizes of the
objects to be placed in the tablespace.
b.
Set
NEXT the same as INITIAL.
c.
Set
MINEXTENTS to 1 and MAXEXTENTS to 1024.
d.
Set
PCTINCREASE to 0.
15.
Create
application roles if desired. Alternatively, you can use the default roles
CONNECT, RESOURCE, and DBA.
16.
Create
your application users that will own the application schemas. Set the default
tablespace to one of your application tablespaces designated to hold tables,
and set the temporary tablespace to TEMP. Assign quotas on all of the application
tablespaces where the user will need to be able to create schema objects. (You
can use the keyword UNLIMITED.) You should not set any quota on the temporary
tablespace. Do not plan to create any application objects in the SYS or SYSTEM
schemas, or store any application objects in the SYSTEM or TEMP tablespaces.
17.
Grant
roles and/or system privileges to the application users. Note that if you grant
the RESOURCE role to a user, that user will also receive the UNLIMITED
TABLESPACE system privilege. This will let the user create objects in any
tablespace they wish, regardless of quotas. I recommend you revoke UNLIMITED
TABLESPACE from all users other than SYS.
Complete the
Server Configuration
These
steps complete the configuration of your server for smooth Oracle operation.
These steps could have been performed earlier, but are more straightforward if
performed after a database has been created. In this section we will configure
the server to start the database and Net8 listener automatically whenever the
server is rebooted, change the oracle user’s login script to eliminate
hardcoding, and create individual operating system accounts for each database
user.
1.
Edit
the /etc/oratab file to verify that the entry for your database is correct. All
of the lines in the file should be comments (starting with a pound symbol)
except for one. This one line should contain the name of your Oracle instance,
its ORACLE_HOME, and a Y or N. A Y indicates that the database should be
started automatically on server reboot, and an N indicates that it should not.
The three fields should be separated by colons. A sample /etc/oratab file looks
like this:
# # /etc/oratab # =========== # MYDB:/u01/app/oracle/product/8.0.5:Y
2.
Edit
the login file for the oracle user to eliminate hardcodings and call the oraenv
script to set the environment instead. The following should work with Bash,
Bourne shell, or Korn shell:
# Settings for Oracle environment
ORACLE_SID=MYDB
ORAENV_ASK=NO
export ORACLE_SID ORAENV_ASK
. oraenv
3.
Create
separate Linux accounts for DBAs and database users who will log onto the
server. You should only log in as oracle when installing or patching software.
The Linux accounts for DBAs should be members of the dba group, and other users
should not be members of the dba group. Give each of these accounts a login
file like oracle’s so that their environment initializes correctly when they
log in.
4.
To
make the database and Net8 listener start up automatically when the server
reboots, you’ll need to create a dbora file in /etc/rc.d/init.d and link it to
/etc/rc.d/rc3.d. You’ll need to do this as the root user. First create a file
called dbora in /etc/rc.d/init.d as follows:
#!/bin/sh
ORA_HOME=/u01/app/oracle/product/8.0.5
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start') # Start the Oracle databases and Net8 listener
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart" &
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" &
;;
'stop') # Stop the Oracle databases and Net8 listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop" &
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut" &
;;
esac
Note
that the spacing around the brackets shown here is different from what appears
in the Oracle documentation.
After
creating the dbora file, you need to link it to /etc/rc.d/rc3.d:
ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc0.d/K10dbora ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc3.d/K10dbora ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc3.d/S99dbora
Summary
This
section of the tutorial walked you through all of the intricate details of getting
Oracle up and running on the Linux platform. It may look complicated, but
that’s only because this document goes down to a nitty gritty level of detail.
Please
keep in mind, though, that the requirements are different for every Oracle
implementation. I am extremely confident that if you follow these steps to
install Oracle 8.0.5 Standard Edition on a server running Red Hat 5.2 Linux,
then the process will go very smoothly for you. However, no single document can
address every specific hardware configuration and every set of business needs.
Please use these steps as a starting point to get Oracle up and running in your
shop. Next, you’ll want to keep your database managable and make it perform—and
the next sections in this tutorial will help you with that.
In
this section we’ll cover the basics of managing an Oracle database. First we’ll
look at starting and stopping the database. Then we’ll look at connectivity
issues and general troubleshooting tactics. Then we’ll discuss how to organize
schemas, tablespaces, and segments. Finally, we’ll have a basic overview of
fault tolerance and backups.
Starting
and Stopping
As
we discussed in an earlier section, there are three steps to making an Oracle
database accessible to applications. We call the whole process “starting” or
“opening” the database. You most commonly start a database using Server Manager
or Enterprise Manager. Server Manager is the lowest common denominator; it
exists (in one form or another) in all Oracle environments and on all
platforms.
On
Unix platforms you invoke Server Manager by entering svrmgrl at the shell
prompt. (The “l” stands for “line mode.” There once was a hideous version of
Server Manager that tried to simulate a GUI in a character mode environment.
Thankfully, this was abandoned.)
You
should set your Oracle environment before starting Server Manager. ORACLE_HOME
and the Oracle directories included in PATH and LD_LIBRARY_PATH will allow
Oracle to locate the proper binaries and shared libraries. ORACLE_SID and
TWO_TASK will tell Oracle which instance you wish to access.
In
Server Manager you’ll need to authenticate yourself before you can do anything
useful. If your database is already open to applications, you can simply use
the CONNECT command to connect to an instance. In this case you’ll need to
provide a username and password, and the instance will check these against the
list of users held in the data dictionary of the database.
But
what do you do if the database isn’t open yet? With the database closed, there
is no access to the data dictionary for authentication. There are two options:
operating system authentication or a password file external to the database.
Shops
that run Oracle on Unix and do not use Enterprise Manager tend to take the
operating system authentication route. In this scenario, the DBA logs on to the
database server using a Unix account that is a member of a privileged group,
typically called “dba”. In Server Manager, the DBA gives the special CONNECT
command CONNECT / AS SYSDBA. This tells Server Manager to have the operating
system authenticate the user for the purposes of starting up or shutting down
the database. Back in Oracle V6 the command was CONNECT INTERNAL. You’ll still
hear old-time DBAs refer to CONNECT INTERNAL and it does still work, but Oracle
is promising to de-support it soon. So go with CONNECT / AS SYSDBA instead.
Shops
that run Oracle on less secure operating systems, or use Enterprise Manager for
centralized database startup and shutdown, cannot rely on the operating system
for authentication—because the DBA never logs onto the database server at all.
In these situations a password file is used. As part of database creation, the
orapwd utility is used to generate a file external to the database that contains
passwords. In Server Manager the DBA connects with the command CONNECT
username/password AS SYSDBA. Server Manager then checks this information
against the contents of the password file.
Once
you have performed the appropriate CONNECT command, Server Manager will respond
with “Connected to an idle instance.” This basically means that you are
authenticated, but there is no instance running. You may now start up the
database using the STARTUP command. Typically you won’t use any of the options
that come with STARTUP, but it’s good to know the most common options because
they are useful in certain situations. Figure 1 shows the most common options.
|
Command |
Description |
|
STARTUP |
Takes a database all the way from being closed to being fully accessible
to applications (creates an instance, mounts the database, opens the
database) |
|
STARTUP NOMOUNT |
Creates an instance, but does not mount or open the database |
|
STARTUP MOUNT |
Creates an instance and mounts the database, but does not open it |
|
STARTUP RESTRICT |
Same as STARTUP, but only allows database users with the “restricted
session” system privilege to access the database |
|
STARTUP PFILE=’filename’ |
Same as STARTUP, but uses the specified parameter file to configure
the instance, instead of the usual initSID.ora |
Figure 1: Common Server
Manager commands for opening an Oracle database
Shutting
down an Oracle database is equally easy. First, authenticate yourself in Server
Manager just like before. Then issue the desired shutdown command. Figure 2
shows the different options available. It’s very common to use the first two.
Hopefully you won’t need the third.
|
Command |
Description |
|
SHUTDOWN |
Prevents any new logins to the database, and shuts the database down
cleanly after the last existing session logs out |
|
SHUTDOWN IMMEDIATE |
Kills all existing sessions and shuts the database down cleanly |
|
SHUTDOWN ABORT |
Abruptly kills all Oracle processes and frees up all system resources
used by the instance |
Figure
2: Server Manager commands for closing an Oracle database
The
SHUTDOWN command without any options (also called a “normal shutdown”) could potentially
hang indefinitely, because it only takes one user who fails to log out to hold
things up. An immediate shutdown also may not be so immediate, because Oracle
will first kill all sessions and clean up after them. An abort shutdown is
always instantaneous because Oracle indiscriminantly kills everything off. You
shouldn’t use SHUTDOWN ABORT unless you really need to, because it basically
crashes the instance and forces Oracle to perform crash recovery the next time
you open the database. (The crash recovery is automatic, but could take a while
if you aborted the instance in the middle of a huge transaction.)
If you choose to use
Enterprise Manager instead of Server Manager, you will need to have a password
file external to the database and you’ll also need to have Net8 configured on
both the Enterprise Manager client and the database server. Enterprise Manager
is a GUI tool allowing you to point and click your way toward doing DBA tasks.
Although the user interface is substantially different from Server Manager’s
command line interface, the concepts and features available are basically the
same.
If all of your database
applications will run on the same physical server as the Oracle databases they
access, then opening the database is the only thing you need to do in order for
your applications to be able to access the database. However, it’s more typical
these days to distribute processing among networked servers. If any database
applications will be running on separate servers from the database server, you
will need to start the Net8 listener before these applications can access the
database.
The Net8 listener runs on
the same server as the database. You only need to run one Net8 listener per
physical server, even if you have multiple databases running on the server. You
need to configure the Net8 listener before you can start it. The most common
way to configure Net8 is to edit a listener.ora file manually. We did this
while setting up the default database in the previous section. Alternatively,
Oracle does offer GUI network configuration tools that will generate the
configuration files for you. In smaller shops, it will probably be easier to
just edit the listener.ora file manually.
The configuration files for
Net8 tell the listener where and with which protocols to listen for connection
requests from clients, and also which databases are available on the server. On
Unix it is most common to have Net8 listen on port 1521 for connection requests
using TCP/IP protocol. However, many different protocols are supported, and
protocol interchange is even possible.
You control the Net8
listener with the lsnrctl command. You may enter lsnrctl on the command line by
itself, in which case you will be prompted to enter a listener command.
Alternatively, you may enter a listener command directly on the command line
after the lsnrctl. Figure 3 shows the most common listener control commands.
|
Command |
Description |
|
START |
Starts the Net8 listener |
|
STOP |
Stops the Net8 listener |
|
RELOAD |
Reads the configuration files anew |
|
STATUS |
Shows the status of the Net8 Listener |
|
SERVICES |
Lists the listener services currently running, along with connection statistics
for each service |
Figure
3: Common commands for controlling the Net8 listener
The
Net8 listener’s primary function is to establish a connection between a
database application (typically running on a remote server) and an Oracle instance
(typically running on the local server). Once the Net8 listener has established
a connection between the application and the instance, the two communicate
directly without involving the Net8 listener. The key point to note is that
stopping and restarting the Net8 listener while a database is open will not
disrupt existing database sessions. While the listener is down applications
will not be able to connect, but existing connections will not be affected.
In
most situations, you want the Oracle databases to be open and the Net8
listeners to be running at all times. Typically, these services are started
automatically when the server starts up. We saw how to do this when we set up a
default database in the previous section.
Connectivity
and Troubleshooting
Each
Oracle instance maintains a log known as the “alert log.” This is a plain text
file called alert_SID.log, where SID is the name of the instance. It resides in
the directory specified by the background_dump_dest initialization parameter.
Oracle writes to this log when an instance is started or stopped, when a
significant database change is made (such as adding a new tablespace), and when
something significantly goes wrong that affects the instance or database as a
whole (such as a disk failure).
Whenever
you have doubts about what’s going on in your instance, the alert log is a good
place to look. It’s also a good idea to review the alert log periodically for
problems you might not be aware of. You can archive or compress your alert log if
it gets too big. This can be done without shutting down the instance—the
instance will create a new alert log if the old one is missing.
When
something bad happens in a particular Oracle daemon or server process, a trace
file will be dumped and a message will be written to the alert log. Trace files
for daemons are written to the background_dump_dest, while trace files for
server processes are written to the user_dump_dest. Trace files are plain text,
although the information sometimes is not very intelligible unless you work in
Oracle’s Worldwide Technical Support division.
When
an application is unable to connect to a database due to a networking or
connectivity problem, the Net8 client called by the application will write a
sqlnet.log file. This file will be located on the client (the machine running
the application) in the working directory of the application trying to connect.
The file will be plain text, containing diagnostic information you can use to
troubleshoot the problem.
Some
of the most common Net8 connectivity problems are as follows:
ORA-12154: TNS:could not
resolve service name: This means that the Net8 client (running on the application’s server)
was unable to find what server and database the Net8 alias provided by the
application maps to. You should check the tnsnames.ora file on the client to
make sure it includes the Net8 alias you wish to use. Also, make sure the
tnsnames.ora file is in the right location—typically
$ORACLE_HOME/network/admin. You should be able to connect to the database
immediately after fixing the problem with tnsnames.ora.
ORA-12203: TNS:unable to
connect to destination: This indicates a networking connectivity problem between the server
running the database application and the server running the database. If you
tried to ping the database server from the client, you would probably find 100%
packet loss.
ORA-12224: TNS:no listener: The Net8 listener on the
database server is not running. Start the listener and try again.
ORA-01034: ORACLE not
available:
This indicates that the database is not open. Start the database and try again.
ORA-12505: TNS:listener
could not resolve SID given in connect descriptor: This indicates one of two
possible configuration problems. Either the tnsnames.ora file on the client
contains an incorrect instance name (SID) in the CONNECT_DATA clause, or the
Net8 listener running on the database server is not aware of the specified
instance. You’ll need to edit either the tnsnames.ora file on the client or the
listener.ora file on the database server and try again. If you edit the
listener.ora file, you’ll need to restart or reload the Net8 listener on the
database server.
Schemas,
Tablespaces, and Segments
Creating
database users and schemas is an important DBA responsibility—both the organizational
planning and the actual creating and dropping of the accounts.
How
you divide your end user population into database users and how you separate
your database objects into individual schemas will have huge impacts on the
security, maintainability, and scalability of your system. You should come up
with a coherent plan before creating a single database user.
For
simple applications, DBAs will typically create one Oracle user as the “application
owner.” All of the database objects to be used by the application—such as
tables, indexes, and stored procedures—are created in this user’s schema.
Complicated applications are usually divided into separate modules or
components, with a separate owner user created for each.
In
applications where the database will authenticate the end users directly, a
separate Oracle user is created for each end user. The schemas for these users
will typically be empty except for synonyms allowing the user to reference
database objects in the application owner schema. Appropriate privileges on the
application owner’s schema objects are granted to the end user database users,
typically through database roles.
In
applications where the application will authenticate the end users, one
“application runtime” Oracle user is usually created. The application always
accesses the database via this generic database user. A table of users in the
application owner schema or some other mechanism is used to allow the application
to authenticate end users. As with the individual end user schemas, the generic
schema will typically be empty except for synonyms.
Client/server
applications with a small to medium number of end users are typically developed
using the individual database user model, as are applications that require very
high security. Internet applications are typically developed using the generic
application user model, due to the potentially huge user community and the
expense of having Oracle create new sessions and authenticate users on every
HTTP request.
Once
a strategy has been devised for users and schemas, a plan should be developed
for the tablespaces that will make up the physical storage of the database.
Tablespace planning is more straightforward than user and schema planning.
Every
Oracle database should have seven classes of tablespaces. The default database
created earlier in this tutorial will have the first five. It’s up to you to
create the other two, and possibly enhance some or all of the first five.
Figure 4 lists these seven types of tablespaces.
|
Tablespace Contents |
Name in Default Database |
|
Data
dictionary and Oracle internals |
SYSTEM |
|
Rollback
segments |
RBS |
|
Temporary
segments |
TEMP |
|
Oracle
and third party tools |
TOOLS |
|
Unclassified
user segments |
USERS |
|
Application
tables |
n/a |
|
Application
indexes |
n/a |
Figure 4:
Seven types of tablespaces
Every
Oracle database must have a SYSTEM tablespace for the data dictionary and internals.
You’ll never need to create an additional tablespace in this class. Most
database are fine with one tablespace each for rollback segments and temporary
segments. On larger, high concurrency databases, however you may choose to add
additional tablespaces for rollback and temporary segments in order to
distribute I/O load effectively.
Tablespaces
for tools and unclassified user segments are not always necessary, especially
if you are not installing any Oracle or third party tools that create database
schema objects. However, it is definitely a good idea to have a USERS
tablespace to serve as a general dumping ground for those segments that will
only be around for a little while or you aren’t sure where to put.
The
remaining two tablespace classes are where you’ll want to focus your planning.
Choosing how to distribute your application segments among tablespaces will
strongly impact performance, scalability, and manageability of your database.
Rule
number one is to always store tables and indexes in separate tablespaces. One
of the most common ways to access data in a query is via an index scan followed
by a fetch from the table by rowid. If a table is stored in the same tablespace
as its indexes, then it is likely that both will be stored on the same physical
device. This would lead to fierce I/O contention every time the table is
accessed via its index.
From
there, the rules get more vague. In general, it is a good idea to use separate tablespaces
for separate applications or schemas. This will allow you to perform
maintenance on one application’s data without impacting other applications
using the same database. It’s also a good idea to separate highly volatile
segments or segments temporary in nature (such as a temporary table you create
and drop whenever a report is run) from the more static or permanent data.
Further, large tables and indexes (say, over 500 Mb or 1 Gb) should be given
exclusive tablespaces of their own.
Small
databases for simple applications will often have just two application
tablespaces—one to hold tables and one to hold indexes. It’s not uncommon,
however, for large databases serving complex applications to have 50 or more
tablespaces. A greater number of tablespaces may seem like more of a management
overhead, but the ability to isolate outages and manage free space allocation
on a more granular level become increasingly important as the size and
complexity of a database grows.
Every
segment in every tablespace of an Oracle database has a wealth of individual
sizing parameters, collectively called “storage parameters.” Figure 5 lists the
different parameters.
|
Name |
Purpose |
|
initial |
Size
of the initial extent |
|
next |
Size
of the next extent to be allocated |
|
pctincrease |
Factor
by which to increase the size of each successive next extent |
|
minextents |
Initial
number of extents to allocate |
|
maxextents |
Maximum
number of extents the segment is allowed to have |
|
pctfree |
Space
to leave empty in each new block to allow for updates that cause data to take
up more space |
|
pctused |
How
much space in a block must be free before additional data will be inserted |
Figure 5:
Segment storage parameters
The
degree to which you can fine tune the storage of each individual segment in the
database can boggle the mind. There are a number of old wives’ tales and urban
legends floating around about the best way to set storage parameters. The more
complex strategies are based on limitations of older versions of Oracle which
have long since been overcome. Sadly, some are based on plain old
misinformation.
The
segment sizing strategy I use is based on heavy-duty research performed
internally at Oracle Corporation. This strategy also happens to be the simplest
around. I mentioned it briefly in the steps for creating a default database.
The underlying principles are as follows:
·
If
all extents in a tablespace are the same size, the free space will never be
fragmented.
·
Oracle’s
extent management overhead is minimal as long as segments have fewer than 1024
extents.
·
Storage
parameters are easier to administer if all segments in a tablespace have the
same settings.
The
strategy is to set the default storage parameters for each application
tablespace, and let all application segments inherit their storage parameters
from the defaults for the tablespace to which they are assigned. The default
storage parameters for each tablespace should be set as follows:
a.
Set
INITIAL to either 128k, 4m, or 128m, depending on the planned sizes of the
objects to be placed in the tablespace.
b.
Set
NEXT the same as INITIAL.
c.
Set
MINEXTENTS to 1 and MAXEXTENTS to 1024.
d.
Set
PCTINCREASE to 0.
Fault
Tolerance and Backups
If
the data you will be storing in your database has any value to you whatsoever,
you’ll want to take steps to make your database fault tolerant. You’ll also
want to back up the data in your database so that you are protected against
disasters—both of the hardware failure and user error variety.
To
make your database fault tolerant, you should eliminate single points of
failure wherever possible. The control file and online redo log are critical to
an Oracle database—the entire database will fail with the loss of either.
Therefore, you should always have redundant copies of your control file and
online redo log as an absolute minimum.
You
can use RAID to keep redundant copies of the files, or you can have Oracle
perform software mirroring (where Oracle writes identical blocks to multiple
files). A benefit of having Oracle mirror these files for you is that you’ll be
covered if somebody accidentally deletes a file from the file system. (Unless,
of course, they delete all mirrored copies!) Software mirroring of the control
file incurs very little I/O or overhead. Mirroring online redo log files can
cause a significant increase in I/O on update-intensive systems, but disk
storage is cheap these days.
RAID
is common enough these days that you should consider using it for all of the
data files in your database as well. But you should not think that RAID
eliminates the need for a backup and recovery strategy. It is still possible
for RAID systems to fail, and RAID won’t help you in the case of user error or
a catostrophic event.
Backup
strategies for Oracle can be varied and complex. Heavy tomes have been
published on the subject. Figure 6 gives a very high level view (say, at 40,000
feet or more) of some of the options available.
|
Method |
Description |
How To |
|
Export |
Write
database contents to an operating system file |
Use
the exp utility |
|
Cold
Backup |
Capture
a point-in-time snapshot of the entire database |
Shut
down the database cleanly and use operating system commands to copy the
control file, online redo log, and all data files |
|
Hot
Backup |
Capture a rolling snapshot
of individual data files while the database is in use |
Use
operating system commands to copy data files while the database is open,
informing the instance along the way so that additional logging measures can
be used to ensure a viable backup |
|
Recovery
Manager |
Oracle’s
new tool to simplify hot and cold backups |
Use
the rman utility |
Figure
6: High level overview of Oracle database backup options
Here
are a few pearls of wisdom to start you along the path toward a proper backup
and recovery strategy:
·
If
you’ve shut the database down cleanly, you can use ordinary operating system
commands to take a cold backup of the database. For the backup to be usable,
you will need to capture one copy of the control file, one copy of each online
redo log file, and every data file. All files need to be captured while the
database is closed, and without opening the database in between copying files
·
It
is very easy to use the exp utility to export the contents of a database to a
single operating system file. You can use an export file to reload an empty
database or migrate a database between Oracle versions or operating system
platforms. Understand, however, that this technique is not very scalable. It
gets resource intensive and slow if your database is bigger than a few Gb in
size.
·
It’s
easy to restore individual schema objects from an export file. Hot and cold
backups, on the other hand, are typically used to restore an entire database
and not individual schema objects.
·
In
order to take hot backups, your database needs to be running in archivelog
mode. This requires that you manage the archived redo log.
·
Unless your database is
running in archivelog mode and you submit the correct commands to the instance
before and after copying each tablespace, you cannot copy data files while the
database is open and expect them to be viable.
·
An
export file only allows you to recover a database to the point in time of the
export. (The point in time will be fuzzy unless the consistent=y export option
is used.) A cold backup of a database
not running in archivelog mode also only allows you to recover the database to
one point in time. Only a hot or a cold backup taken from a database running in
archivelog mode allows you to recover the database from a backup and “roll it
forward” up to the point of failure.
·
In
order to roll a hot or cold backup forward to the point of failure, you’ll need
all archived redo log files since the beginning of the backup of the oldest
file in the backup set. If an archived redo log is missing from the sequence,
you will not be able to roll forward past that point.
Please
take the fifth bullet item very seriously. Because of the way Oracle optimizes
writes, you can never assume that a file is quiesced. Even if there is no
activity on the database, you cannot assume that all data files are in a
consistent state. Oracle tracks the
state of each data file in both the control file and each data file’s header.
The
Oracle database is a phenomenally tunable and configurable product. The
difference in performance between a vanilla install and a well tuned system can
be amazing. It’s not unheard of to reduce a report’s running time from 18 hours
to ten minutes by changing one line of code. In this section of the tutorial
we’ll take a high level look at several areas where you can optimize your
Oracle system. In each area we’ll take a peek at the most common tuning issues.
To learn more about each of these tuning areas, consult the Oracle8 tuning
manual.
Tuning
Database Access within an Application
The
highest impact tuning you can do in an Oracle system is application tuning.
Optimize the queries and application logic to get the most out of Oracle.
Oracle SQL and PL/SQL are very powerful, and the Oracle engine is very capable
when it comes to processing and sorting large volumes of data. When SQL or PL/SQL
offers a certain functionality your application needs, you should use Oracle’s
native capabilities instead of engineering your own application logic to do the
same thing.
The
v$sqlarea view in every Oracle database allows DBAs to see the statements that
are currently cached inside the SGA. Figure 1 shows a simple SQL*Plus query you
can use to zero in on resource-intensive statements within your application. A
“logical read” is a request for a block of data from a data file. The request
may result in a cache hit (if the block is already in the buffer cache inside
the SGA) or a physical read from disk. “Execs” is the number of times the
statement has been executed since it entered the SGA.
SQL> COLUMN sql_text FORMAT a40 SQL> SELECT disk_reads phys_rds, buffer_gets logicl_rds, 2 executions execs, sql_text 3 FROM v$sqlarea 4* ORDER BY buffer_gets; PHYS_RDS LOGICL_RDS EXECS SQL_TEXT ---------- ---------- ---------- ---------------------------------------- 0 0 15 BEGIN dbms_sql.variable_value@REMOTE (:b1, :b2, :b3); END; ... 351 29276 7 SELECT RPAD(SUBSTR("A1"."OWNER",1,15),15 ),RPAD(SUBSTR("A1"."TABLE_NAME",1,30),30 ),RPAD(SUBSTR(NVL(TO_CHAR("A1"."LAST_ANA LYZED",'Month dd, yyyy'),'None'),1,20),2 0) FROM "SYS"."DBA_TABLES" "A1" WHERE "A 1"."OWNER"<>'SYS' AND "A1"."OWNER"<>'SYS TEM' AND ("A1"."NUM_ROWS" IS NULL OR "A1 "."LAST_ANALYZED"<SYSDATE-30) ORDER BY " A1"."OWNER","A1"."TABLE_NAME" 81 79994 3734 begin run_requests (SYSDATE); end; 614 rows selected. SQL>
Figure 1: Looking for resource-intensive statements from within SQL*Plus
When
the same statement is executed multiple times, Oracle only needs to parse it
once. The parsed representation, including execution plan, will be cached in the
SGA’s shared SQL area. This can dramatically cut down processing time when
applications execute huge numbers of simple queries.
A
key to maximizing the benefit of the shared SQL area is to use bind variables
in your SQL statements wherever possible. Most database APIs these days support
bind variables, although the syntax varies from one to the next. JDBC, Perl
DBI/DBD, and Pro*C all support bind variables.
Depending
on the API involved, each SQL statement executed in an application can result
in multiple network roundtrips between the application’s server and the
database server. You can often reduce network traffic and boost performance by
pushing logic into the database. For queries, there could be as many network
roundtrips as rows fetched if you don’t use arrays for fetching multiple rows
at once.
Sizing
the SGA
You
can boost database performance across the board by sizing the SGA optimally. If
you make the SGA too small, your cache hit ratios will be low and you’ll have
more physical I/O and parsing overhead than necessary. If the SGA is too big,
the system may run low on physical memory and resort to swapping.
The
SGA is composed of many different memory structures and areas. You can query
v$sgastat to see the names and sizes of the different components that make up
the SGA. Oracle database software is optimized on the assumption that the
entire SGA for an instance is always in physical memory at all times. It is
imperative, therefore, that you not make the SGA so big that physical memory gets
exhausted.
The
sizing of many of an SGA’s components are either directly or indirectly
configurable via the parameter file for the instance. The two parameters that
will have the greatest impact on the overall size of the SGA are
db_block_buffers and shared_pool_size.
The
db_block_buffers parameter specifies the size of the buffer cache in the SGA,
used to hold the most recently used blocks from the data files of the database.
The actual size of the buffer cache will be equal to the block size for the database
times the number of buffers. Figure 2 shows a query in SQL*Plus that will
determine the buffer cache hit ratio cumulative since the instance was started.
Ratios vary based on the application, but hit ratios in the buffer cache are
usually over 90% on well tuned systems. You can also set the
db_block_lru_statistics and db_block_lru_extended_statistics parameters to
cause the instance to maintain statistics about how the cache hit ratio would
have differed if there were fewer or more buffers.
SQL> SELECT (S1.value + S2.value - S3.value) 2 / (S1.value + S2.value) hit_ratio 3 FROM v$sysstat S1, v$sysstat S2, v$sysstat S3 4 WHERE S1.name = 'db block gets' 5 AND S2.name = 'consistent gets' 6* AND S3.name = 'physical reads'; HIT_RATIO ---------- .986270344 SQL>
Figure 2: Determining the buffer cache hit ratio
The
shared_pool_size parameter determines the size of the shared SQL area in the
SGA. The shared SQL area is where the instance caches the parsed representations
of the most recently used SQL statements. Also held inside the shared pool is
the dictionary cache, a separate cache that holds the recently used portions of the data dictionary.
The
optimal setting for shared_pool_size varies greatly from one situation to the
next. The amount of PL/SQL code stored in the database, the degree to which the
application uses bind variables, and the general complexity and size of the
application will all influence the demands put on the shared SQL area. A simple
application using bind variables and no stored procedures or database triggers
may only need a shared_pool_size of 8 Mb, but a heavily used application which
does not use bind variables could conceivably suffer performance degraation
with a shared_pool_size under 64 Mb.
Balancing
Disk I/O
Because
I/O involves access to a physical storage device which is slow compared to CPU
speed, many databases are often I/O bound. Once you’ve sized the SGA optimally
to minimize physical I/Os, you should strive to balance those physical I/Os
across multiple devices so that no one device is a bottleneck and so that
contention is minimized.
You
may query v$filestat and SYS.dba_data_files in order to find out how many
physical reads and writes have taken place in each data file since the instance
was started. If you have timed statistics enabled for the instance (either via
the initialization parameter or the ALTER SYSTEM SET TIMED_STATISTICS = TRUE
command) you can also see the time spent to perform these I/Os.
You
can use this information to spread data files across disks in a way to
distribute the I/O evenly. If one data file has more than its share of the
activity, you may want to split its tablespace into multiple tablespaces and
distribute the “hot” segments across multiple files on different devices.
You
should also distribute data files across physical devices in a manner to avoid
contention. Oracle very often needs to read from a table and its indexes in
alternating fashion, so placing the table and index on the same physical device
could cause severe contention. For this reason, tables should always be
separated from their indexes.
Oracle
also writes to the online redo log whenever a transaction commits. Placing the
online redo log files on the same device as heavily used tables or indexes can
cause contention. Online redo log files can even contend with each other if
Oracle is archiving one redo log while the next one is being written to by
current transaction activity. For this reason online redo logs should be spread
across two or more physical devices in a ping pong fashion with no data files
sharing these devices.
Managing
Database Connections
Connecting
to an Oracle instance that is running in the default architecture can be
somewhat expensive. First, Oracle must fork a new process. Then the process
must attach itself to the SGA and authenticate the user. The stateless nature
of HTTP lends itself to an environment with a huge number of incredibly short
database sessions. If each of these sessions involved a brand new connection,
the overhead of connecting and disconnecting would be severe.
For
this reason, many application server architectures and middleware products
support connection caching and pooling. Net8 also offers new features for
multiplexing and pooling database connections. Many shops using Oracle for
internet-driven applications have chosen to develop their own database
connection management servers in Java or Perl. Whenever using an architecture
where database connections will be reused, it is important to ensure that each
user of a database connection properly ends its transactions and frees up all
system resources used. Also consider how security and authentication will be
performed.
Tuning Sorts
Sorts
are necessary when building an index or when querying data with a DISTINCT,
GROUP BY, or ORDER BY clause. Oracle has a very powerful and efficient sort
algorithm. All sorts begin in memory. If the amount of memory required for a
sort reaches a certain threshold, then Oracle splits the sort in two, writing
half to a temporary segment in a temporary tablespace while performing the
other half in memory. Alfter both halves are sorted, they are merged together.
This “divide and conquer” approach can be repeated infinitely many times so
that the hugest sort can still be performed in a bounded amount of memory.
The
sort_area_size initialization parameter determines the maximum amount of memory
one database session should ever use for sorting. This establishes the
threshold at which sorts will involve temporary segments and writes to disk.
Unfortunately, the default setting for sort_area_size in the default database
is 65536 bytes. You should definitely bump this parameter to at least 1 Mb, and
probably more. How high you go will depend on the amount of physical memory on
the database server and how many concurrent sessions will typically be
performing sorts at once.
You
can override the sort_area_size setting for an individual session with the
ALTER SESSION SET sort_area_size command. This is handy for speeding up huge
indexing operations.
When
sorts are too big to be performed in memory, you want the disk writes to be as
efficient as possible. It’s important to set each database user’s temporary
tablespace designation correctly. Never use the SYSTEM tablespace as a
temporary tablespace. Most databases will have one tablespace used only for
temporary segments, although you may wish to have several such tablespaces if
many users will be doing frequent large sorts and you want to distribute I/O widely.
All
tablespaces used for temporary segments should have their content type set to
TEMPORARY instead of the default of PERMANENT. This causes Oracle to use an
enhanced space allocation mechanism that is optimized for the frequent creation
and destruction nature of temporary segments.
In
addition to making sorts as efficient as possible, you should also tune
application logic to minimize the amount of sorting required. This includes examining
join conditions carefully to avoid Cartesian products and avoiding overuse of
the DISTINCT keyword.
Configuring
Rollback Segments
Rollback
segments are among the most elusive and misunderstood structures in an Oracle
database. Rollback segments hold “undo” information so that a transaction may
be rolled back. But they also allow a database session to query a table in a
read-consistent way even while another database session is updating the very
same table.
Rollback
segments are used like circular buffers, except that they can expand as well.
When Oracle reaches the end of a rollback segment, it sees if any of the
earlier blocks in the segment can be reused. If not, the segment extends. When
a transaction commits, all of the blocks in the rollback segment allocated to
that one transaction are marked as no longer being needed and available for
reuse.
When
a transaction begins, Oracle assigns it to one rollback segment, probably the
rollback segment currently hosting the fewest transactions. Optionally, a
transaction may specify which rollback segment it wants to use. Multiple
transactions can share one rollback segment, but if too many transactions share
one rollback segment, contention for the rollback segment header can occur.
The
optimal number of rollback segments for a database depends on the typical
number of concurrent transactions. Note that a session performing queries only
does not constitute a transaction and does not get assigned to a rollback
segment. It’s reasonable to expect a few transactions to share one rollback
segment, but you shouldn’t have 50 concurrent transactions share just two
rollback segments.
The
optimal size of rollback segments varies depending on the application. OLTP and
web-based e-commerce applications tend to have many small transactions. This
situation is suited to many small rollback segments in order to minimize
rollback segment header contention. Batch applications are usually more suited
to a smaller number of large rollback segments, because the system will have
few concurrent transactions but each transaction may be large.
In
reality, most systems have a mix of both large and small transactions. You
should create enough rollback segments so that header contention is avoided.
Rollback segments should be moderate in size, but should be able to grow when
large transactions occur. It may be a good idea to set the OPTIMAL storage
parameter for rollback segments so that if one segment swells enormously due to
a long transaction, it will be able to shrink and release storage for use by
other rollback segments in the future.
There
is a lot of great information about Oracle technology available. You can find
much of it on the internet for free.
Oracle
Documentation
Don’t
overlook the Oracle documentation CD that comes with all Oracle software. It
can be overwhelming, but there is a lot of excellent reference information
there. Figure 1 gives you a roadmap to help you get started.
|
What You Are Looking For |
Oracle Manual |
Type |
|
General
Oracle concepts |
Oracle8 Concepts |
Generic |
|
How
to install Oracle |
Oracle8
Installation Guide |
Platform
specific |
|
Details
about Oracle particular to your platform |
Oracle8
Administrator’s Guide |
Platform
specific |
|
General
DBA duties |
Oracle8
Administrator’s Guide |
Generic |
|
init.ora
parameters and data dictionary views |
Oracle8
Reference |
Generic |
|
How
to use the export, import, and SQL*Loader utilities |
Oracle8
Utilities Guide |
Generic |
|
Performance
and tuning |
Oracle8
Tuning Guide |
Generic |
|
SQL
language syntax |
Oracle8
SQL Reference |
Generic |
|
PL/SQL
language syntax |
Oracle8
PL/SQL User’s Guide and Reference |
Generic |
|
How
to use SQL*Plus |
SQL*Plus
User’s Guide |
Generic
|
Figure 1: Where to look for answers in the Oracle
documentation set
Other
Publications
There
are numerous books in print covering all facets of Oracle technology. I like
the O’Reilly books the best. You can view their offerings at http://oracle.oreilly.com. Kevin Loney
has written some of the Oracle Press books which are highly regarded. You can
read Kevin’s plugs about his books at http://www.kevinloney.com.
You can also learn more about all of the Oracle Press books at http://www.osborne.com/oracle/index.htm.
Oracle
Technology Network
The
Oracle Technology Network at http://technet.oracle.com
has a wealth of information specifically targeted for developers. Registering
to become a member gives you instant free access to nearly all online
documentation, a collection of white papers and technical presentations,
discussion forums, and free developer licenses for certain Oracle products.
The
discussion forums are also accessible at http://www.dejanews.com.
Oracle User
Groups
You
can get a huge amount of information by joining an Oracle user group. Different
groups meet monthly, quarterly, or annually. At these meetings you can attend
technical sessions presented by other Oracle users and swap tips and techniques
with other people in similar situations to you. Some of these groups also
maintain a repository of tips and techniques on their websites.
The
International Oracle User Group—Americas meets annually and has a website at http://www.ioug.org. The Northern California Oracle
User Group meets quarterly in the San Francisco Bay Area. Their website is http://www.nocoug.org.
Free
Web Resources
You
can also find a lot of useful information and a helpful online service at
my company’s website http://www.dbspecialists.com.
You’ll find handy scripts for Oracle DBAs at http://www.dbspecialists.com/download.html.
There are also white papers of a very technical “nuts and bolts” nature available
at http://www.dbspecialists.com/present.html.
Finally, and probably most unique, you can sign up for Database Rx at http://www.dbspecialists.com/dbrx.html.
Database Rx is a free web-based service that will examine your Oracle database
and give you expert tuning advice and recommendations. Database Rx can also
monitor your databases on a daily basis and send you email when it discovers
a potential problem you should investigate further.
The
handy scripts, white papers, and use of Database Rx are all free of charge.
* *
*
Roger
Schrag has been an Oracle DBA and application architect for over ten years,
starting out at Oracle Corporation on the Oracle Financials development team.
He is the founder of Database Specialists, Inc., a consulting group
specializing in business solutions based on Oracle technology. You can visit Database Specialists on the
web at http://www.dbspecialists.com,
and you can reach Roger by calling +1.415.344.0500 or via email at rschrag@dbspecialists.com.