Installing
and Configuring Oracle9i on the Solaris Platform
by Roger Schrag
Database Specialists, Inc.
About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications.
Learn more about our
remote DBA,
database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.
Introduction
This paper will walk you through the steps of installing Oracle9i
release 2 (Oracle version 9.2.0) in a Sun Solaris SPARC environment. About 90%
of the material presented here applies to other platforms as well. Everything
you read in this paper is hands on, roll-up-your-sleeves-and-get-busy
material for Oracle users who want to get an Oracle database up and running
quickly without reading hundreds of pages of documentation and “readme”
files.
These steps are meant to get you up and running as fast as possible,
while leveraging best practices in order to set up a scalable, robust database
environment that offers high performance. In order to keep the steps reasonably
simple this paper does not cover Real Application Clusters
(RAC), nor does it cover Oracle Internet Directory or Oracle
Management Server installation.
There are four phases to getting Oracle up and running on your server:
-
Prepare the server
-
Install the Oracle software and create a starter database
-
Fine tune the starter database (optional)
-
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.
However, this paper will get you off to a very solid start.
Prepare the Server
These steps configure your database server so that it will be ready to accept
the Oracle software and database. In this section, we will make sure your
server meets Oracle’s minimum requirements, create a Unix 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.
-
Make sure that your Solaris system has all of the required operating system
patches installed. You must have Solaris 2.6 or Solaris 7 (32 bit) or Solaris
8 (32 or 64 bit) to run Oracle9i release 2. If you are running Solaris
2.6 or Solaris 7, the patch requirements are as follows:
| Patches for Solaris 2.6 (32 bit) |
Status |
| 106040-11 X Input and Output Method patch |
Required |
| 105181-15 Kernel patch |
Required |
| 105284-25 Motif Runtime Library Patch |
Recommended |
| 105490-07 Dynamic linker patch |
Recommended |
| 105633-21 OpenWindows 3.6: Xsun patch |
Recommended |
| 105568-13 Libthread patch |
Recommended |
| 105210-19 LibC patch |
Recommended |
| 105669-07 CDE 1.2: libDTSvc patch (dtmail) |
Recommended |
| 106409-01 Chinese TrueType fonts patch |
Recommended |
| Patches for Solaris 7 (32 bit) |
Status |
| 107636-01 X Input and Output Method patch |
Required |
| 106980-05 Libthread patch |
Recommended |
| 107607-01 Motif fontlist, fontset, libxm |
Recommended |
| 107078-10 Open Windows 3.6.1 Xsun patch |
Recommended |
You can verify your operating system version and see which patches are
installed with the following commands:
$ uname -a
$ showrev -p
If you are running Solaris 8, no specific patches are required. However,
if you are running the 64 bit version of Solaris 8, you should be using
the 07/01 release of the operating system. (I am running Oracle9i release 2
on the 04/01 release of 64 bit Solaris 8 without problems, but Oracle
Corporation recommends the 07/01 release.) You can check which release of
Solaris you are running with the following command:
$ cat /etc/release
-
Make sure that the following software packages have been installed.
| Required Packages |
| SUNWarc |
| SUNWbtool |
| SUNWhea |
| SUNWlibm |
| SUNWlibms |
| SUNWsprot |
| SUNWtoo |
| SUNWi1of |
| SUNWxwfnt |
You can use the following command to verify that a package has been
installed:
$ pkginfo -i <package name>
-
You will need to perform the installation from an X window environment.
You cannot perform the installation from a character mode environment such
as a telnet or ssh session. There is a facility for performing non-interactive
installations (“silent” installs), but we won’t be covering that technique
here. Besides, it appears that even the silent install still needs access to X
libraries. Your X environment can be the console on the database server, but
it does not need to be. You can also use a Windows X emulator like Hummingbird
Exceed, but see page 1-15 of the Oracle9i Installation Guide for UNIX for
possible issues with Hummingbird Exceed. I ran the installation from the
Common Desktop Environment (CDE) on my Solaris 8 desktop and had no problems,
but when I tried to use the X environment on my Linux workstations running
older versions of Red Hat, I ran into trouble with missing fonts.
-
The following executables must be present somewhere on your path: make,
ar, ld, nm.
-
Make sure that your hardware is sufficient. You’ll need at least 512 Mb
RAM, a swap space of at least 1 Gb or equal to the amount
RAM (whichever is larger), and a bare minimum of 4 Gb of disk space. This
will let you perform a typical Standard Edition software installation and
create a starter database. A production implementation
will almost always require more RAM and more disk space. My server has
1 Gb RAM, and it was swapping heavily during the installation. The following
commands will allow you to check RAM and swap space:
$ /usr/sbin/prtconf | grep size
$ /usr/sbin/swap -l
-
Make sure that the Solaris kernel has parameters set sufficiently high
for Oracle. The Oracle architecture makes extensive use of shared memory
segments for sharing data among multiple processes and semaphores for handling
locking. Many operating systems, including Solaris, do not by default offer
sufficient shared memory or semaphores for maintaining an Oracle database.
Happily, you can change kernel parameters in Solaris simply by editing
the /etc/system file and rebooting the server.
| Kernel Parameter |
Setting To Get
You Started |
Purpose |
| SHMMAX |
4294967295 |
Maximum size of a single shared memory segment |
| SHMMIN |
1 |
Minimum size of a single shared memory segment |
| SHMMNI |
100 |
Maximum number of shared memory segments in entire system |
| SHMSEG |
10 |
Maximum number of shared memory segments one process can attach |
| SEMMNS |
2000 |
Maximum number of semaphores in entire system |
| SEMMSL |
1000 |
Maximum number of semaphores per set |
| SEMMNI |
100 |
Maximum number of semaphore sets in entire system |
The first four kernel parameters configure shared memory segments. The
recommended settings shown here should be appropriate for almost any Oracle
database implementation. The SHMMAX setting may seem excessive, but there
is no penalty for setting SHMMAX larger than you actually need.
The last three kernel parameters configure semaphores. Each Oracle instance
requires one semaphore for each process, plus ten extras. Additionally,
the largest instance requires a second semaphore for each process. If you
will only be setting up one database on your server, the upshot is that
you will need two semaphores for each process plus ten extras.
The recommended settings for the first two semaphore kernel parameters,
SEMMNS and SEMMSL, should be appropriate for most Oracle implementations.
For systems with large numbers of concurrent database connections, you
may need to increase these values. The recommended setting shown here for
SEMMNI should be appropriate for just about any Oracle database implementation.
In general, if your Solaris kernel already has any of these parameters
set larger than recommended here, you should not reduce the settings. If
you do change any kernel parameter settings in /etc/system, then reboot
the server so that the new settings will take effect.
I added the following lines to the end of my /etc/system file:
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns=2000
set semsys:seminfo_semmsl=1000
set semsys:seminfo_semmni=100
-
Create a Unix group that will be used by the Oracle software owner and
database administrators. You can call it anything you like, but the standard
is “dba”. If you will be installing Oracle on multiple servers on your
network, you might want to keep the groupid the same on all servers. You
can use the admintool, or you can create your dba group with a command
like:
$ groupadd -g 300 dba
-
Create a Unix user that will be the Oracle software owner. You can call
it anything you like, but the standard is “oracle”. If you will be installing
Oracle on multiple 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. You should make dba the primary group, and the login
shell should be Bourne, Korn, or C shell. You can create your oracle user with
the admin tool, or with commands like:
$ useradd -c 'Oracle software owner' -d /home/oracle \
-g dba -m -u 300 -s /usr/bin/ksh oracle
$ passwd oracle
-
Create a Unix group and user that will be used by the Apache HTTP listener
integrated into the Oracle9i database.
Running the Apache HTTP listener as the Oracle software owner or a member
of the dba group can compromise security. You can call the group and user
anything you like. At this time there seems to be no clear standard for
what to call this group and user. You can create your group and user with
the admin tool, or with commands like:
$ groupadd -g 60300 apache
$ useradd -c 'Oracle Apache user' -d /home/apache -g apache \
-m -u 60300 -s /usr/bin/ksh apache
$ passwd apache
-
Create mount points for the Oracle software and the Oracle database. Each
mount point should correspond to a separate physical device or set of devices.
You’ll need at least one mount point. Typically you use one mount point
for the Oracle software and one or more mount points for each database.
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 as root, you should create some subdirectories
now to avoid permission problems later. Create an app subdirectory below
the software mount point, and oradata subdirectories below the mount points
to be used for database files. (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.
-
Oracle supports files larger than 2 Gb, but your shell must not impose a 2 Gb file size limit for this feature to work.
Note that Oracle does limit database data files to 4,194,304 Oracle blocks. You choose the Oracle block size
at the tablespace level, and the options range from 2 Kb to 32 Kb. This means that Oracle data files
are limited in size to a maximum of 8 Gb to 128 Gb, depending on the block size. It is also important to note that certain
Oracle utilities, notably the export utility, are still limited to 2 Gb files. Use the following commands to ensure
that the shell imposes no limits on file size:
$ ulimit -Sa
$ ulimit -Ha
-
If you downloaded a trial version of Oracle off of the Internet, then use
cpio to unpack the distribution. If you have the software on CD ROM, then
mount the first CD ROM now. Most Solaris systems will automatically mount
CD ROMs, but alternatively you can use a command like:
$ mount -r -F hsfs device_name /cdrom
-
Create the /var/opt/oracle directory and make it owned by the oracle user.
After installation, this directory will contain a few small text files
that briefly describe the Oracle software installations and databases on
the server. These commands will create the directory and give it appropriate
permissions:
$ mkdir /var/opt/oracle
$ chown oracle:dba /var/opt/oracle
$ chmod 755 /var/opt/oracle
Install the Oracle Software and Create
a Starter Database
These steps install the Oracle software on your server and create a
“starter” database. In this section, we will prepare the oracle user’s
environment, run the Oracle Universal Installer, and tidy up a few minor loose
ends. All of the steps in this section, except where noted, are run as the
oracle user.
-
Edit the oracle user’s login file on the database server so that the environment
will be configured automatically on login. If you are using Bourne or Korn
shell, then edit .profile.
You can also use C shell and edit .cshrc, but the syntax will be
different from the examples you see here. For now, we will hardcode certain
things. But after the Oracle software is installed we will come back and
eliminate all hardcodings. Here is what I added to my .profile for the
install:
umask 022
# Substitute your Oracle software mount point in the line below.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
# Substitute the name of your Oracle database below.
export ORACLE_SID=dev920
# Fill in the following line as you wish, but make sure that
# $ORACLE_HOME/bin, /usr/ccs/bin, /usr/bin, /etc, /usr/openwin/bin,
# and /usr/local/bin are all in the PATH (in that order).
# If you will be installing Pro*C/C++ then the C compiler must be
# on your path. The usual compiler executable on Solaris is located
# in /opt/SUNWspro/bin.
export PATH=...
# Ensure that TWO_TASK is not set.
unset TWO_TASK
-
Log out and log back in as the oracle user from an X window so that the
environment is set correctly.
-
Set your DISPLAY variable to the IP address of your X server plus the X
server and screen numbers. If you are working from a workstation (and not
the console of the database server where you are installing Oracle) do
not accidentally set the DISPLAY variable to the IP address of your database
server. You can set your DISPLAY variable with a command like:
$ export DISPLAY=myworkstation:0.0
-
If you are not using the console of the database server, then ensure that
the X server on your workstation will allow your database server to open
windows on your display. The easiest way to do this is to issue an xhost
command from a session on your workstation. (Don’t get confused and issue
the command in a window that is logged onto your database server.) You
can issue a command like:
$ xhost +mydatabaseserver
-
Use ftp to transfer a small file from your database server to a remote
host to prove to yourself that TCP/IP networking is installed, configured,
and working properly.
-
Ensure that the mount points you plan to use for the Oracle software and
starter database have sufficient free space. The starter database will
be created entirely on one mount point. For a Standard Edition installation,
allow 2.4 Gb for the software mount point and 1.3 Gb for the database mount
point as bare minimums. If you are installing the Enterprise Edition of
Oracle9i, you will need more space.
-
If you have an active installation of Oracle8i on the database server, then
you should make a backup copy of the jre and oui directories under ORACLE_BASE.
I found that installing Oracle9i overwrites the JRE and code used by the
Oracle8i installer. This means that after you install Oracle9i, you might have
difficulties using the Oracle8i tools (such as Database Configuration Assistant
or Net8 Configuration Assistant) to manage your Oracle8i databases. After I
installed Oracle9i, my Oracle8i tools continued to work properly. But according
to postings on Metalink, other users were not as lucky.
-
Double check that you are logged in as oracle and not root. Then change to
your home directory and start the Oracle Universal Installer with these
commands:
$ cd
$ <full path to first CD ROM>/runInstaller
We’ll walk through the installer prompts one at a time:
-
The Welcome window appears. Click Next.
-
If the Unix Group Name window appears, enter the name of your dba group and
click Next. You won’t see this window if you have previously installed
Oracle8i or Oracle9i software on the database server, or if your dba group is
called “dba”. (You won’t see this window the next time you run the installer
because Oracle saves this information in the /var/opt/oracle/oraInst.loc
file.)
-
The File Locations window appears. Leave the Source field unchanged. The
Destination field will show the ORACLE_HOME value you set in your environment.
Give your ORACLE_HOME a name if you like, and change the mount point if you
don’t like what you see. Click Next.
-
The Available Products window appears. Choose Oracle9i Database
and click Next.
-
The Installation Types window appears. We will perform a “typical” install
to get a basic set of Oracle software installed and a starter database.
You can rerun the installer again later and choose Custom to install additional
products individually. For now, choose Standard Edition or Enterprise Edition.
The Enterprise Edition of Oracle9i has some very sophisticated
features missing from Standard Edition, and the opportunity to purchase
additional options that might be valuable to a large enterprise. However, the
Enterprise Edition is much more expensive than Standard Edition. It is very
important that you choose the edition that matches your license, as this will
be difficult to fix later. I use the Standard Edition. Click Next.
-
The Database Configuration window appears. We will go the easy route
here and choose a General Purpose database and click Next. Alternatively,
you could choose Customized and tailor the starter database to your own
specifications. Or you could choose Software Only and not create a starter
database at all. (You can always run the Database Configuration Assistant
later to create, drop, or reconfigure databases.)
-
If you have any existing Oracle databases on your server that are at a
version prior to what you are now installing, the installer will ask if
you would like to run the Database Upgrade Assistant at the end of the
installation to migrate or upgrade these older databases to the current
version. Make your decision and click Next. (We won’t be covering the
Database Upgrade Assistant here.)
-
The Database Identification window appears. You need to specify both a
global database name and an SID (instance name) for the starter database
that will be created. The SID will default to the setting of the ORACLE_SID
environment variable, but you can override it here if you wish. You should
give your database a global name that is the same as the SID, with your
domain name appended. Click Next.
-
The Database File Location window appears. Enter the name of one of the
mount points you chose for holding your database, followed by “/oradata”. For
example, if your mount point is called /u02, then enter /u02/oradata. The
starter database will have all of its files in one directory under this mount
point. This may or may not be a good design for high performance and
availability, but is fine for a starter database. Click Next.
-
The Database Character Set window appears. Choose the Unicode character set
(AL32UTF8) or another character set from the dropdown list if you prefer not
to use Unicode. Note that AL32UTF8 complies with the Unicode 3.1 standard,
while the UTF8 character set used in Oracle8i complies with the older
Unicode 2.0 standard. Click Next.
-
The Summary window appears. Review all of the selections you have made
to confirm they are correct. Click Install.
-
If you are installing from CD ROM, you’ll be prompted to mount the second and
third CD ROMs at various points during the install. Use a separate window to
eject the current CD ROM and mount the next.
-
During the installation a Setup Privileges window will appear. (The
installation process took about half an hour to get to this point on my
server.) The installation will be paused at this point,
waiting for you to run a script as root. The script will be called root.sh and
can be found in the ORACLE_HOME directory. You should open another window, log
in to the database server as root, review the root.sh script thoroughly, run
the script, and click OK in the Setup Privileges window.
-
A Configuration Tools window appears a minute later and the Oracle Net
Configuration Assistant launches to configure networking so that your
database will be able to accept requests from remote clients. No action is
required on your part, and this step completes quickly.
-
The Database Configuration Assistant launches to create a starter database.
A progress window will show you how the database creation is going. Database
creation took about five minutes on my server, but will take substantially
longer if you chose a customized database configuration. When database
creation is complete, a window will appear telling you that most user accounts
on the database have been locked and that you must change the passwords for the
SYS and SYSTEM database users. You may enter the new passwords and click OK.
Alternatively, you may click the Password Management button and unlock accounts
and set passwords for all database users as you wish.
-
The Agent Configuration Assistant launches to configure Oracle’s “intelligent
agent”, a monitoring and job-running agent that you control through Oracle’s
Enterprise Manager tool. No action is required on your part, and this step
completes quickly.
-
The Apache HTTP listener will now start. No action is required on your part,
and this step completes quickly.
-
The End of Installation window appears. You may click Exit to exit the
installer or Next Install to begin another installation. You might click
Next Install, for example, to perform a custom installation to install
individual products that did not get installed as part of the “typical”
installation—such as Pro*C/C++.
-
It is important to note that the default Enterprise Edition install loads
certain extra cost options, such as table partitioning, onto your database
server. If you are not licensed to use these options, then you should
deinstall them. To deinstall products, click the Deinstall Products button on
the Welcome window.
-
Exit the installer when you have completed installations and deinstallations.
-
In $ORACLE_HOME/bin you will find a shell script called oraenv. This script
can be called from .profile to set up a user’s environment. Unfortunately,
there are a few 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
# Substitute the database character set you chose in following line.
NLS_LANG=american_america.AL32UTF8
export ORACLE_BASE DBA NLS_LANG
# End customizations
-
In the same directory you’ll also find a shell script called coraenv that
can be called from .cshrc. If you use C shell, you will want to back up
and edit coraenv with similar changes to the oraenv script.
-
The root.sh script copied oraenv and coraenv from $ORACLE_HOME/bin to your
local bin directory. You just updated these scripts in $ORACLE_HOME/bin.
Copy the updated versions to your local bin directory.
-
In $ORACLE_HOME/bin you’ll find a script called dbstart. This is a utility
that you can run to start up databases on the server. Later we will add
a call to this script from /etc/rc2.d so that the databases start up
automatically whenever the server reboots. Unfortunately, the dbstart script
has a bug that will cause it to fail with the error message “Can’t find init
file for Database” in certain situations. One way to fix this bug is to add
the following line immediately after line 55:
SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
and change line 117 to read:
if [ -f $PFILE -o -f $SPFILE ] ; then
-
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 lines in the script that contain just the
word “shutdown”. Change these to read “shutdown immediate”.
-
During the installation, the Agent Configuration Assistant started Oracle’s
intelligent agent. If you will not be using the Enterprise Manager tool at this
time, you might want to shut down the intelligent agent. On my system,
the intelligent agent had a memory leak. Within a week the dbsnmp process was
consuming over 1.7 Gb of memory. You can shut down the intelligent agent with
the following command:
$ agentctl stop
Fine Tune the Starter Database
These steps modify the configuration of the starter database to tailor it
to your needs and to make it better comply with industry-proven best practices.
You can skip this entire section if initially you just want to work with the
starter database as is. In this section we will change configured database
options, adjust file locations and server parameters, create application users
and tablespaces, and configure Oracle Net. All of the steps in this section are
run as the oracle user.
-
Set up your environment the same way you did when you ran the Oracle installer:
Log in as the oracle user on the database server from an X window. Set
your DISPLAY variable appropriately. Make sure that your ORACLE_HOME, PATH,
and other variables are set correctly based on your login file.
-
You may run the Database Configuration Assistant to configure database options
that were not pre-configured in the starter database, or remove options that
were included in the starter database which you don’t need. Launch the
Database Configuration Assistant with the following commands:
$ cd $ORACLE_HOME/bin
$ ./dbca
Choose to configure an existing database and select your starter database
from the list. You will be presented with a list of database options. The
options that are checked have already been configured in the database. Add a
check mark beside those options you wish to add, and uncheck those options which
you wish to remove. Note that some options will be grayed out. This can happen
for three reasons:
- The option has already been configured in the database and cannot be
removed once configured. In this situation you could choose to delete the
entire database and create a new one with only the options you wish.
- Software products required to support the database option have not been
installed. Run the Oracle Universal Installer again and install the appropriate
products before trying to configure the option in the database.
- The option is only available with the Enterprise Edition and you have
installed the Standard Edition software.
-
The starter database comes with 100 Mb online redo logs. These might be much
larger than you need. You cannot resize online redo logs, but you can drop
and recreate them with commands like:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> ALTER DATABASE DROP LOGFILE '/u02/oradata/dev920/redo01.log';
SQL> HOST rm -i /u02/oradata/dev920/redo01.log
SQL> ALTER DATABASE ADD LOGFILE GROUP 1
2 '/u02/oradata/dev920/redo01.log' SIZE 10m;
SQL> ALTER DATABASE DROP LOGFILE '/u02/oradata/dev920/redo02.log';
SQL> HOST rm -i /u02/oradata/dev920/redo02.log
SQL> ALTER DATABASE ADD LOGFILE GROUP 2
2 '/u02/oradata/dev920/redo02.log' SIZE 10m;
SQL> ALTER DATABASE DROP LOGFILE '/u02/oradata/dev920/redo03.log';
SQL> HOST rm -i /u02/oradata/dev920/redo03.log
SQL> ALTER DATABASE ADD LOGFILE GROUP 3
2 '/u02/oradata/dev920/redo03.log' SIZE 10m;
Do not answer an rm prompt affirmative unless the corresponding ALTER DATABASE
DROP LOGFILE command completed successfully without an error message. If you
get an error that a log file is in use when you try to drop it, switch the
database to the next online redo log with the command:
SQL> ALTER SYSTEM SWITCH LOGFILE;
-
The starter database has all data files and online redo logs in the same
directory. If you would like to move any of these files to another directory,
use commands like the following:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> HOST mv -i /u02/oradata/dev920/users01.dbf /u03/oradata/dev920/users01.dbf
SQL> ALTER DATABASE RENAME FILE
2 '/u02/oradata/dev920/users01.dbf' TO
3 '/u03/oradata/dev920/users01.dbf';
SQL> HOST mv -i /u02/oradata/dev920/redo01.log /u03/oradata/dev920/redo01.log
SQL> ALTER DATABASE RENAME FILE
2 '/u02/oradata/dev920/redo01.log' TO
3 '/u03/oradata/dev920/redo01.log';
SQL> ALTER DATABASE OPEN;
Note that this procedure does not work for control files. Relocating database
control files will be covered in a later step.
-
In the starter database, all data files have the “auto-extend” feature turned
on. This means that when a data file becomes full, it will automatically grow
larger as needed. The problem with this is that an application can get out of
control and fill up an entire disk partition. It also means that you need to
manage your free space at the operating system level. Many DBAs prefer to
manage free space at the database level by pre-allocating space to data files
and not using the auto-extend feature. You may resize data files and disable
auto-extend with commands like:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/system01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/undotbs01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/users01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/indx01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/tools01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/dev920/temp01.dbf' AUTOEXTEND OFF;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/dev920/system01.dbf' RESIZE 300m;
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/dev920/temp01.dbf' RESIZE 100m;
Note that if you are using the Enterprise Edition, you should not resize the system01.dbf data file smaller than 500 Mb.
-
Oracle uses a server parameter file or “spfile” to store configuration
settings that affect the instance. The parameter settings in the starter
database are not bad, but you will probably want to make some changes.
Unfortunately, you cannot edit the spfile. Instead, you must export the
contents of the spfile to a plain text file called a “pfile”. You can then
edit the pfile and convert it back to an spfile for use on your starter
database. (This might sound confusing, but is actually pretty
straightforward.)
Shut down the database and export the contents of the spfile into a pfile
that you can edit with commands like:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> CREATE PFILE='/home/oracle/dev920params.txt'
2 FROM SPFILE;
SQL> SHUTDOWN IMMEDIATE
-
Make a backup copy of the pfile you created in the previous step and edit
the pfile to change parameters as you wish, based on your needs and your
server’s capabilities. You can always change parameters again in the future,
so you are not locking yourself into anything right now. Here is the pfile
that I ended up with:
*.background_dump_dest='/u01/app/oracle/admin/dev920/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u02/oradata/dev920/control01.ctl','/u02/oradata/dev920/
control02.ctl','/u02/oradata/dev920/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/dev920/cdump'
*.db_block_size=8192
*.db_cache_size=24m
*.db_domain='dbspecialists.com'
*.db_name='dev920'
*.instance_name='dev920'
*.java_pool_size=0
*.job_queue_processes=10
*.max_dump_file_size=10240
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=24m
*.processes=50
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=64m
*.sort_area_size=1048576
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/dev920/udump'
(Note that I do not use the Oracle JVM, and that is why my java_pool_size is
set to 0. You will need to set your java_pool_size to 20m or more if you plan
to use the Oracle JVM or other Oracle features that use the JVM.)
-
The starter database has three control files. The control file is a pretty
small file that contains crucial configuration and synchronization information
that Oracle needs in order to locate all the files that make up the
database and keep them consistent. All three copies of the control file are
kept identical; whatever Oracle writes to one control file it also writes to
the other two. (Think of it like software mirroring.) It is a good idea to move
at least one of the control files to another location. With the database
shut down, you can go ahead and move the control files around as you wish. Be
sure to change the control_files entry in your pfile accordingly.
-
Remove the existing spfile that the Database Configuration Assistant created,
and the bogus pfile that it left behind, with the following commands:
$ rm -i $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
$ rm -i $ORACLE_BASE/admin/$ORACLE_SID/pfile/init.ora*
-
Create a symbolic link from the location where Oracle looks for the spfile to
the location where you will actually maintain the spfile:
$ ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora \
$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
-
Now convert the pfile that you edited back into an spfile that
Oracle can use with the following commands:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> CREATE SPFILE='$ORACLE_BASE/admin/$ORACLE_SID/pfile/spfile$ORACLE_SID.ora'
2 FROM PFILE='/home/oracle/dev920params.txt';
-
You are now ready to restart your database using your newly created spfile.
Use the following commands to start the database and view the parameters
that are in effect. These settings should match what you put in your
pfile a few steps back:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> SET PAGESIZE 100
SQL> SELECT name, value, isdefault
2 FROM v$parameter
3 ORDER BY isdefault, name;
-
You can follow the above few steps at any time to make further
changes to the parameters. However, if you only have a few changes to make,
there is a much easier way than exporting the spfile into a pfile,
editing the pfile, and converting back to an spfile. You can simply:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> ALTER SYSTEM SET parameter = value
2 SCOPE = SPFILE;
This will update the setting in your spfile, and the change will take effect
the next time you restart the database. Many parameters are dynamic, meaning
that you can change them on the fly without restarting the database. For
dynamic parameters, you can omit the SCOPE = line above and Oracle will change
the parameter setting immediately and in the spfile.
-
Adjust the configuration of the Oracle Net listener if necessary. You can edit
the listener.ora file in $ORACLE_HOME/network/admin to suit your needs,
although you may find the default file to be totally acceptable.
Depending on your network topology, you might want to change the hostname
or IP. (In my case my server is multi-homed, but I only want the database
to accept connections from the internal network.) You should 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. My listener.ora
file looks like this:
#
# Filename: listener.ora
#
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dev920.dbspecialists.com)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = dev920)
)
)
-
Prepare a tnsnames.ora file in $ORACLE_HOME/network/admin on the database
server and distribute it to all clients. Edit the default file to suit
your needs. Change the hostname or IP if needed. My tnsnames.ora file looks
like this:
#
# Filename: tnsnames.ora
#
EXTPROC_CONNECTION_DATA.DBSPECIALISTS.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
DEV920.DBSPECIALISTS.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dev920.dbspecialists.com)
)
)
-
At this point the database has two tablespaces available to hold your
application tables and indexes: USERS and INDX. However, I recommend that you
create new tablespaces for holding application segments instead of using these
two tablespaces. 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. In the past,
choosing storage parameters and allocation schemes for database objects was
extremely complex. Now it is quite simple because you can have Oracle do the
space allocation and management automatically and it will do a pretty good
job. Here is a sample tablespace creation statement:
CREATE TABLESPACE small_tables
DATAFILE '/u02/oradata/dev920/small_tables01.dbf' SIZE 500m
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-
Create application roles if desired. Alternatively, you can use the default
roles CONNECT, RESOURCE, and DBA.
-
Create your application users that will own the application schemas. Set
the default tablespace to one of your application tablespaces designated
to hold tables. 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. Here is
a sample application user creation statement:
CREATE USER bob IDENTIFIED BY bob123
DEFAULT TABLESPACE small_tables
QUOTA UNLIMITED ON small_tables QUOTA UNLIMITED ON large_tables
QUOTA UNLIMITED ON small_indexes QUOTA UNLIMITED ON large_indexes;
-
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 application users you create. Sample statements
to grant and revoke privileges are as follows:
GRANT connect, resource TO bob;
REVOKE unlimited tablespace FROM bob;
-
Review the overall security of your database. Oracle Corporation has published
a handy ten-page listing of security checks that you should perform against
your database. Download from the Oracle Technology Network at
http://otn.oracle.com/deploy/security/oracle9i/pdf/9iR2_checklist.pdf.
Complete the Server Configuration
These steps complete the configuration of your server for smooth Oracle
operation. In this section we will change the oracle user’s login script to
eliminate hardcoding, create individual operating system accounts for each
database user, and configure the server to start the database and listeners
automatically whenever the server is rebooted.
-
Edit the /var/opt/oracle/oratab file to verify that the entry for your
database is correct. Lines starting with a pound sign are considered comments
and are ignored. Each non-comment line contains the name of one 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
/var/opt/oracle/oratab file looks like this:
#
# /var/opt/oracle/oratab
# ======================
#
dev920:/u01/app/oracle/product/9.2.0:Y
-
Edit the login file (.profile) for the oracle user to eliminate
hardcodings and call the oraenv script to set the environment instead.
The following will work with Bourne shell or Korn shell:
# Settings for Oracle environment
ORACLE_SID=dev920 # Put your instance name here
ORAENV_ASK=NO
export ORACLE_SID ORAENV_ASK
. oraenv
Note that this script assumes that the /usr/local/bin directory is on your
path. Also, if you use C shell then you should edit .cshrc and have it source
coraenv.
-
Create separate Unix accounts for DBAs and database users who will log
onto the database server directly. You should only log in as oracle when
installing or patching software. The Unix 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.
-
The Database Configuration Assistant configured the HTTP listener that is
integrated into the Oracle9i database and started it up for you. Unfortunately,
the HTTP listener was started by the oracle Unix user. Running the HTTP listener
as the oracle user and a member of the dba Unix group can be a big security
hole. Earlier we created a separate Unix user and group to run the HTTP
listener. At this time we need to stop the HTTP listener, clean up file
ownership problems, and restart the HTTP listener as the apache user. The
steps are as follows:
-
Run the following command as the oracle user to stop the HTTP listener:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl stop
-
Review the httpd.conf file in $ORACLE_HOME/Apache/Apache/conf and comment out
modules that you will not be using. (The default httpd.conf file includes
everything under the sun.)
-
Change the ownership of certain files from oracle to apache so that the
apache Unix user will not get permission problems when trying to run the HTTP
listener. Which files need a change of ownership depends on which modules
you kept active in the httpd.conf file. In my system I use the mod PL/SQL
feature, but none of the others. Here are the commands I ran to change file
ownership:
$ cd $ORACLE_HOME/Apache/Apache
$ su
$ chown apache:apache logs logs/*
$ cd ../modplsql
$ chown apache:apache log log/* cfg/wdbsvr.app
-
Edit the login file (.profile) for the apache user to call the oraenv script
at login time. (This is the same as what we did for the oracle user's login
script in a previous step.) The following will work with Bourne shell or Korn
shell:
# Settings for Oracle environment
ORACLE_SID=dev920 # Put your instance name here
ORAENV_ASK=NO
export ORACLE_SID ORAENV_ASK
. oraenv
-
Start the HTTP listener from the apache Unix user by running the following
command while logged in as the apache user:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl start
-
To make the database and listeners start up automatically when the
server reboots and shut down automatically when the server shuts down,
you’ll need to create a dbora file in /etc/init.d and link it to /etc/rc2.d
and /etc/rc0.d. You’ll need to do this as the root user. First create a
file called dbora in /etc/init.d as follows:
#!/bin/sh
ORA_HOME=/u01/app/oracle/product/9.2.0
ORA_OWNER=oracle
HTTP_OWNER=apache
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start') # Start the Oracle databases and listeners
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $HTTP_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
;;
'stop') # Stop the Oracle databases and listeners
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $HTTP_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
;;
esac
After creating the dbora file, you need to link it to /etc/rc2.d and /etc/rc0.d:
ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
Note that this script starts the HTTP listener as the apache user. If you want
your HTTP listener to listen on a privileged port, then you will need to have
root start the HTTP listener and hand off ownership to the apache user by
setting the User and Group parameters in the Apache configuration file
$ORACLE_HOME/Apache/Apache/conf/httpd.conf.
Conclusion
This paper walks you through the intricate details of getting
Oracle9i up and running on a database server running SPARC Solaris. It may
look complicated, but that’s only because this paper 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 Oracle9i release 2 (Oracle version 9.2.0) on a server
running SPARC Solaris 2.6, 7, or 8, 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 this paper
as a starting point to get Oracle up and running in your shop. To get the
best performance and scalability, each system needs to be considered individually.
About the Author
Roger Schrag has been an Oracle DBA and application architect for over
twelve years. He started out at Oracle Corporation on the Oracle Financials
development team and moved into the roles of production DBA and database
architect at various companies in the San Francisco Bay Area. Roger is
a frequent speaker at Oracle World and the IOUG Live! conferences.
He is also vice-president of the Northern California Oracle Users Group.
In 1995, Roger founded Database Specialists, Inc.
Still Looking for Help on this Subject?
Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
Complimentary Newsletter
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.
Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com