LinuxLab.dk logo IT University of Copenhagen
HOME CALENDAR PROJECTS   SITEMAP / SEARCH

[ Print friendly ] 

Installing Oracle 8i release 3 (version 8.1.7) on Redhat Linux

News

Introduction

This Tech Note describes the installation of Oracle version 8.1.7 on RedHat 7.1, or more precisely the Fried Chicken Linux distributed at LinuxLab. It probably works on other RedHat releases including RedHat 6.2. However, a glibc problem appeared in RedHat version 7.0 and 7.1, and a fix is described below. You should skip the fix, if you install on a RedHat version less than 7.0.

Hardware

The directions in this note has been successfully carried out on my IBM ThinkPad T21 running the Fried Chicken Linux which is basically a RedHat 7.1.

Documentation and Downloads

Oracle is a big thing with lots of documentation. A full installation is at least one day work - well that is what I used to get my first Oracle up and running.

To obtain Oracle 8i release 3 (version 8.1.7.0.1) you visit http://technet.oracle.com/software/products/oracle8i/content.html.

After signing up, you download a 500Mb tar file: linux81701.tar (into directory /usr/src); I do not know why Oracle does not gzip the file! You also need win817client.zip if you want to connect to Oracle from Windows clients. I also downloaded some documentation.

  • linux_installguide_817.pdf: you should probably, browse this guide if this is your first Oracle install.
  • linux_relnotes_817.pdf: you should also read this.
  • linux_adminguide_817.pdf: you can skip this till after you have installed Oracle.

You can get an overwhelming amount of information at the Oracle Technology Network Library.

I also downloaded a few other installation-guides:

On Oracle Technology Network you find the Oracle Documentation Library for Oracle 8i, release 3.

Unpack the Oracle Distribution

You unpack the file linux81701.tar into directory /usr/src/oracle as user root:

# whoami
root
# cd /usr/src/
# mkdir oracle
# cd oracle/
# tar xvf ~nh/Install/Oracle/linux81701.tar

Before You Install

There are a number of issues to consider before you start the installation.

  • RAM: You must have at least 128 Mb RAM (I had 384 Mb RAM).
  • Kernel version must be at least 2.2; you can check this with the uname command:

    # uname -a
Linux nh.itu.dk 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown
    • Executables that must be in the /usr/bin directory: 
    # /usr/bin/which make
/usr/bin/make
# /usr/bin/which ar
/usr/bin/ar
# /usr/bin/which ld
/usr/bin/ld
# /usr/bin/which nm
/usr/bin/nm
  • JDK: The Oracle Installer needs the JDK 1.1.8 to be installed in /usr/local/java/. You can get this from http://www.blackdown.org:

    1. Goto the download area using a site that is geographicaly near you.
    2. Click JDK-1.1.8, then i386 and then v3. On that page you download jdk118_v3-glibc-2.1.3.tar.bz2.
    3. Login as root.
    4. In a terminal window you enter the /usr/local/ directory, extracts the tar file and makes a symbolic link to /usr/local/java:

      tar xvIf jdk118_v3-glibc-2.1.3.tar.bz2
ln -s /usr/local/jdk118_v3 /usr/local/java
  • GLIBC (fix): The Oracle Installer does not work with the glibc 2.2 used in RedHat 7.0 and 7.1. If you use Red Hat 7.0 or above, you must make sure that the RedHat Linux 6.2 compatibility packages are installed (compat-egcs, compat-glibc and compat-libs): 
    # rpm -q compat-egcs
compat-egcs-6.2-1.1.2.14
# rpm -q compat-glibc
compat-glibc-6.2-2.1.3.2
# rpm -q compat-libs 
compat-libs-6.2-3
  • Make sure that you have the file glibc-2.1.3-stubs.tar.gz which is necessary to complete the installation.

Pre-Installation

Chapter 2 of the Oracle Installation Guide (Pre--Installation) mentions various kernel parameters to be changed in order to run Oracle. I did not recompile the kernel because all the parameters were already above the required values except for the SHMMAX parameter which can be set in the file /proc/sys/kernel/shmmax.

In file /usr/src/linux-2.4/include/asm/shmparam.h I found the following values:

  • SHMMAX: 0x2000000 corresponding to 64 Mb RAM. I have 384 Mb RAM and should therefore set SHMMAX to 0xC400000. I changed the value by adding 
    #
# For Oracle, half of physmem, Niels 2001-06-18
#
kernel.shmmax = 205520896
    to the file /etc/sysctl.conf which is read at boot-time. You can also execute 
    echo 205520896 > /proc/sys/kernel/shmmax
    before you start the Oracle server.
  • SHMMIN: 1 which is fine
  • SHMMNI: (1<<_SHM_ID_BITS) with _SHM_ID_BITS which is above the recommended 100.
  • SHMSEG: SHMMNI which is above the recommended 10.
In file /usr/src/linux-2.4/include/linux/sem.h I found the following values:
  • SEMMNI: 128
  • SEMMSL: 250
  • SEMMNS: SEMMNI*SEMMSL
  • SEMOPM: 32 (In the manual it should be 100 but I did not change this parameter)
  • SEMVMX: 32767

Users and Groups

We create three groups:
  • orainstall is used to install the software (i.e., will own the Oracle Universal Installer's oraInventory directory.
  • oradba is the group used by the database administrator
  • oraoper is the management group. 
# whoami
root
# groupadd orainstall
# groupadd oradba
# groupadd oraoper
We create the oracle user account that runs the installation with primary group orainstall and secondary groups oradba and oraoper. 
# useradd oracle -g orainstall -G oradba,oraoper
# passwd oracle
Changing password for user oracle
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully

Creating Mount Points

The file system used by Oracle can be organized in various ways. A simple approach is to use two mount points with one containing the software and one containing the database files. Another approch is to use four mount points in accordance with the Optimal Flexible Architecture (OFA); one for the software and three for the database files. If you use the three mount points then you should put them on different discs to get optimal performance.

I use the simple approach and use one mount point for the datafiles. I have a 4 Gb. Windows partition which I convert to a ext2 partition. Before fdisk 

   Device Boot    Start       End    Blocks   Id  System
/dev/hda1   *         1       555   4195768+   7  HPFS/NTFS
/dev/hda2           556       691   1028160   82  Linux swap
/dev/hda3           692      4134  26029080   83  Linux
and after fdisk: 
   Device Boot    Start       End    Blocks   Id  System
/dev/hda1             1       555   4195768+  83  Linux
/dev/hda2           556       691   1028160   82  Linux swap
/dev/hda3           692      4134  26029080   83  Linux
The partition /dev/hda1 is mounted as /ora01. We update owner and access permissions: 
# whoami
# chown oracle.oradba /ora01
# chmod 755 /ora01

The Oracle User Environment

Open a terminal window and log in as user oracle: 
# su - oracle
Password:
Verify that umask defaults to 022 (if not you add umask 022 to the file .bash_profile below): 
$ umask
022
I added the following lines to the file /home/oracle/.bash_profile (umask only necessary if it does not default to 022, see above). 
JAVA_HOME=/usr/local/java; export JAVA_HOME
CLASSPATH=${JAVA_HOME}/lib/classes.zip; export CLASSPATH
ORACLE_BASE=/ora01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/8.1.7; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
# NLS_LANG=UTF8; export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
ORACLE_SID=odb; export ORACLE_SID
if [ "${LD_LIBRARY_PATH:-}" == "" ]; then
  LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/usr/lib:/usr/local/lib
else
  LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
fi
export LD_LIBRARY_PATH

# umask 022 (uncomment if umask is not 022 above)
You check the file .bash_profile by logging out and then in again as oracle and test the environment variables: 
$ env | grep ORA
ORACLE_SID=odb
ORACLE_BASE=/ora01/app/oracle
ORACLE_HOME=/ora01/app/oracle/product/8.1.7
ORA_NLS33=/ora01/app/oracle/product/8.1.7/ocommon/nls/admin/data

$ env | grep PATH 
LD_LIBRARY_PATH=/ora01/app/oracle/product/8.1.7/lib:/usr/lib:/usr/local/lib
CLASSPATH=/usr/local/java/lib/classes.zip
PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:
     /home/oracle/bin:/ora01/app/oracle/product/8.1.7/bin
$
You check that /bin, /usr/bin and /usr/local/bin is in the PATH.

Installation

You are finally ready to install Oracle. If you are on a RedHat 7.0 or 7.1, then issue the following command in the terminal windows where you will start the Oracle Installer as user oracle: 
export LD_ASSUME_KERNEL=2.2.5
. /usr/i386-glibc21-linux/bin/i386-glibc21-linux-env.sh
This will fix the glibc problem mentioned above. You can read more abount this in the RedHat 7.1 Release Notes. 
# su - oracle
Password: 
$ env | grep ORA
ORACLE_SID=odb
ORACLE_BASE=/ora01/app/oracle
ORACLE_HOME=/ora01/app/oracle/product/8.1.7
ORA_NLS33=/ora01/app/oracle/product/8.1.7/ocommon/nls/admin/data
$ env | grep PATH
LD_LIBRARY_PATH=/ora01/app/oracle/product/8.1.7/lib:/usr/lib:/usr/local/lib
CLASSPATH=/usr/local/java/lib/classes.zip
PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:
     /usr/X11R6/bin:/home/oracle/bin:/ora01/app/oracle/product/8.1.7/bin
$ export LD_ASSUME_KERNEL=2.2.5
$ . /usr/i386-glibc21-linux/bin/i386-glibc21-linux-env.sh
Enter the directory /usr/src/oracle/Disk1 and execute the Oracle Installer runInstaller: 
$ cd /usr/src/oracle/Disk1/
$ ./runInstaller
  1. A window appears: Oracle Universal Installer. Click the Next button.
  2. A window with File Locations appears. Make sure that the Path is set to /usr/src/oracle/Disk1/stage/products.jar and Destination is set to $ORACLE_HOME (i.e., /ora01/app/oracle/product/8.1.7). Click the Next button.
  3. At the UNIT Group Name type orainstall
  4. A window asks you to run the script orainstRoot.sh: 
    # pwd
/ora01/app/oracle/product/8.1.7
# whoami
root
# ll
total 4
-rwx------    1 oracle   orainsta      710 Jun 18 23:37 orainstRoot.sh
# ./orainstRoot.sh 
Creating Oracle Inventory pointer file (/etc/oraInst.loc)
Changing groupname of /ora01/app/oracle/oraInventory to orainstall.
    Now go back to the window and click Retry.
  5. In the Available Products window you choose Oracle 8i Enterprise Edition 8.1.7.0.1 and click Next.
  6. In the Installation Types window you choose Typical and click Next.
  7. In the Privileged Operating System Groups window type oradba in both the OSDBA and OSOPER boxes. Click Next.
  8. In the Choose JDK Home Directory make sure that it says /usr/local/java.
  9. In the Database Identification window enter a Global Database Name (e.g., oradb.localdomain). Check that the SID is set to odb. Click Next.
  10. In the Summary window click Install.
Now files are copied and it is time for a strong cup of coffee. You can find a installation log in file /ora01/app/oracle/oraInventory/logs/installActions.log.

Got error after 97%: 

Error in invoking target install of makefile 
/ora01/app/oracle/product/8.1.7/ctx/lib/ins_ctx.mk
At this point you need the file glibc-2.1.3-stubs.tar.gz. As user oracle do the following: 
$ cd $ORACLE_HOME
$ tar -xvzf /tmp/glibc-2.1.3-stubs.tar.gz
./
./lib/
./lib/stubs/
./lib/stubs/libc-2.1.3-stub.so
...
./lib/stubs/libnss_files.so
./setup_stubs.sh
./README.stub
$ ./setup_stubs.sh
Setting up patch files...done.
Patching makefiles as necessary:
  checking file '/ora01/app/oracle/product/8.1.7/rdbms/demo/demo_rdbms.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/rdbms/demo/ociucb.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/rdbms/lib/ins_rdbms.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/rdbms/lib/env_rdbms.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_net_client.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/env_network.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_cman.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_names.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_oemagent.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/env_oemagent.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_net_server.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/network/lib/ins_nau.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/plsql/lib/env_plsql.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/plsql/lib/ins_plsql.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/plsql/demo/demo_plsql.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/precomp/lib/ins_precomp.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/precomp/lib/env_precomp.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ldap/lib/env_ldap.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ldap/lib/ins_ldap.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/ldap/demo/demo_ldap.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/sqlplus/lib/env_sqlplus.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/sqlplus/lib/ins_sqlplus.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/ord/im/lib/env_ordim.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ord/img/demo/demo_ordimg.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ord/ts/demo/oci/src/demo_ordts.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/ord/ts/demo/proc/src/demo_ordts.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ord/ts/lib/env_ordts.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/md/demo/examples/demo_sdo.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/md/demo/unix/motif/src/demo_motif.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/md/demo/unix/motif/src/db_src/demo_dbsrc.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/md/demo/unix/motif/src/main_src/demo_mainsrc.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/otrace/demo/atmoci.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/otrace/lib/env_otrace.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/otrace/lib/ins_otrace.mk'...OK.
  checking file '/ora01/app/oracle/product/8.1.7/ctx/lib/env_ctx.mk'...patched.
  checking file '/ora01/app/oracle/product/8.1.7/ctx/lib/ins_ctx.mk'...OK.
Rebuilding client shared library...done.
Relinking executables:
  running 'ins_rdbms.mk'...done.
  running 'ins_net_client.mk'...done.
  running 'ins_cman.mk'...done.
  running 'ins_names.mk'...done.
  running 'ins_oemagent.mk'...done.
  running 'ins_net_server.mk'...done.
  running 'ins_nau.mk'...done.
  running 'ins_plsql.mk'...done.
  running 'ins_precomp.mk'...done.
  running 'ins_ldap.mk'...done.
  running 'ins_sqlplus.mk'...done.
  running 'ins_otrace.mk'...done.
  running 'ins_ctx.mk'...done.
$
and then continue the installation, that is, click Ignore.

A popup window then appears asking to execute the script /ora01/app/oracle/product/8.1.7/root.sh as user root. Before we execute the script we correct a few errors (taken from this note). Line 102 

RMF=/bin/rm -f
is changed into 
RMF="/bin/rm -f"
and line 156 
RUID=`/usr/bin/id|$AWK -F\( '{print $2}'|$AWK -F\) '{print $1}`
is changed into 
RUID=`/usr/bin/id|$AWK -F\( '{print $2}'|$AWK -F\) '{print $1}'`
Execute the script root.sh as user root: 
# whoami
root
# /ora01/app/oracle/product/8.1.7/root.sh
IMPORTANT NOTE: Please delete any log and trace files previously
                created by the Oracle Enterprise Manager Intelligent
                Agent. These files may be found in the directories
                you use for storing other Net8 log and trace files.
                If such files exist, the OEM IA may not restart.
Running Oracle8 root.sh script...
\nThe following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /ora01/app/oracle/product/8.1.7
    ORACLE_SID=   odb

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Hit Enter 
\nCreating /etc/oratab file...
Entry will be added to the /etc/oratab file by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@nh 8.1.7]#
  • In the Net8 Configuration Assistant: Welcome window choose Perform typical configuration and click Next.

The Net8 Configuration Assistant hang for a while and then the window Database Creation Progress appeared. After a while a pupop window with database information appeared: 

Database creation completed.

Database information:

  global database name: oradb.localdomain
  database system identifier(SID): odb
  SYS account passwrod: change_on_install
  SYSTEM account password: manager
A window End of installation appears; click Exit, and then Yes.

You can now start the sqlplus program and check that the database server is running: 

$ sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 10 22:59:52 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
$

Setting up the representation of dates

By default, Oracle does not store dates in ANSI-compliant date format which is YYYY-MM-DD: 
SQL> select sysdate from dual;

SYSDATE
---------
10-JUL-01

SQL>
You correct this by adding 
nls_date_format = "YYYY-MM-DD"
at the end of file $ORACLE_HOME/dbs/initodb.ora (e.g., we used odb as our sid). After restarting the server, you can check the new setting using sqlplus: 
SQL> select sysdate from dual;

SYSDATE
----------
2001-07-10

SQL>

Manually start the server

  1. svrmgrl
  2. connect internal
  3. startup
  4. quit

Manually stop the server

  1. svrmgrl
  2. connect internal
  3. shutdown
  4. quit

Automating Startup and Shutdown

The script dbstart is used to start the database and the script dbshut is used to stop the database. To activate the scripts automatically, do the following:
  1. The file /etc/oratab configures all databases; the format is as follows: 
    sid:$ORACLE_HOME:[Y|N]
    To automatically load a database type Y. Now, edit /etc/oratab, as user root, such that it contains the following line: 
    odb:/ora01/app/oracle/product/8.1.7:Y
  2. Save the file oracle8i.txt (based on another oracle8i.txt from ArsDigita) in directory /etc/rc.d/init.d with name oracle8i. Make sure that the following two variables are as you expect: 
    ORA_HOME=/ora01/app/oracle/product/8.1.7
ORA_OWNER=oracle
  3. Make sure that /etc/rc.d/init.d/oracle8i has the right owner and modifications: 
    # chmod 700 /etc/rc.d/init.d/oracle8i 
# ls -al /etc/rc.d/init.d/oracle8i 
-rwx------    1 root     root         1329 Jul 11 00:11 /etc/rc.d/init.d/oracle8i
  4. Test, that you can stop the database using the script oracle8i: 
    # /etc/rc.d/init.d/oracle8i stop
Shutting down Oracle8i: 
Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SVRMGR> Connected.
SVRMGR> Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> 
Server Manager complete.
Database "odb" shut down.

#
  5. Check that you can start the database using the script oracle8i: 
    # /etc/rc.d/init.d/oracle8i start
Starting Oracle8i: 
Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SVRMGR> Connected.
SVRMGR> ORACLE instance started.
Total System Global Area                         72704160 bytes
Fixed Size                                          73888 bytes
Variable Size                                    55681024 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
Database opened.
SVRMGR> 
Server Manager complete.

Database "odb" warm started.

#
  6. You then use chkconfig to make it happen automatically at startup and shutdown: 
    # whoami
root
# cd /etc/rc.d/init.d/
# chkconfig --add oracle8i
# chkconfig --list oracle8i
oracle8i       	0:off	1:off	2:off	3:on	4:on	5:on	6:off
#
  7. Now reboot your system and check that Oracle is automatically started, that is, it should be possible to start sqlplus: 
    # su - oracle
Password: 
[oracle@nh oracle]$ sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jul 11 00:41:56 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL>

Acceptance test

At ArsDigita I found the following acceptance test which you can download into /tmp/acceptance.sql and run as follows: 
# su - oracle
Password: 
$ sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jul 11 00:47:01 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL> @/tmp/acceptance.sql;

Table created.


Index created.

...



SYSDATE
----------
2001-07-11

SQL>
It should end with the current date in ANSI compliant format (i.e., YYYY-MM-DD).

I also found a more system oriented test at ArsDigita. Download it into /tmp/system-test.sql and try it out. It must be run by a user with DBA priviliges(e.g., user SYSTEM): 

$ sqlplus SYSTEM/manager

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jul 11 01:07:15 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL> @/tmp/system-test.sql;

  COUNT(*)
----------
	 0


  COUNT(*)
----------
	 0


  COUNT(*)
----------
	 8


LOWER_BOUND
-----------
 1.5061E+10

SQL>
The test file contains comments on why you get the above results and how you change them; if you wish to do that.

Remove an Oracle Installation

  1. Make sure, that the oracle server is not running.
  2. Start the Oracle Universal Installer (as described above)
  3. Click the "De-install Products" button and select all the products that you want to de-install.
  4. Click the "Remove" button
  5. Manually remove the ORACLE_HOME directory if it is still available. 
    $ pwd
/ora01
$ whoami
oracle
$ rm -rf app/

    # whoami
root
# rm /etc/oratab  
rm: remove `/etc/oratab'? y
# rm /etc/oraInst.loc 
rm: remove `/etc/oraInst.loc'? y
#

Net8 configuration

When you have computers connected via a network and want to access an Oracle server, you need to setup Net8. There are various ways to setup Net8 and I describe the simplets setup that I could come up with.

There are various good documents that describe Net8 configuration:

Listeners

Your Oracle database server must contain a listener.ora file listing the names and addresses of all of the listener processes on the server. An example listener.ora file:

# LISTENER.ORA Network Configuration File: /ora01/app/oracle/product/8.1.7/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
   (ADDRESS_LIST=
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.big-company.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
   )

SID_LIST_LISTENER=
  (SID_LIST=
       (SID_DESC=
         (SID_NAME=PLSExtProc)
         (ORACLE_HOME=/ora01/app/oracle/product/8.1.7)
         (PROGRAM=extproc)
       )
       (SID_DESC=
         (GLOBAL_NAME=oradb)
         (SID_NAME=odb)
         (ORACLE_HOME=/ora01/app/oracle/product/8.1.7)
       )
  )
The first part says that the listener is listening on TCP and IPC requests. The second part identifies the Oracle software used for each of the above addresses (i.e., IPC and TCP).

Starting the Listener

First you must start a listener process on the Oracle database server. Put a listener.ora file in directory $ORACLE_HOME/network/admin/listener.ora (as user oracle):

[oracle@host admin]$ ll listener.ora
-rw-rw-r--    1 oracle   orainsta      607 Nov  5 14:57 listener.ora
[oracle@host admin]$
Options:

  • You may replace the name LISTENER with another name.
  • Replace the hostname
  • You must also edit the file initsid.ora if you do not use the port 1521.
  • Edit the ORACLE_HOME entries appropriately.
To test the listener you log into the Oracle database server as user oracle and type lsnrctl start: 
[oracle@nh oracle]$ lsnrctl start

LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 12-DEC-2001 09:41:23

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Starting /ora01/app/oracle/product/8.1.7/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 8.1.7.0.0 - Production
System parameter file is /ora01/app/oracle/product/8.1.7/network/admin/listener.ora
Log messages written to /ora01/app/oracle/product/8.1.7/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host.big-company.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=host.big-company.com)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 8.1.7.0.0 - Production
Start Date                12-DEC-2001 09:41:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /ora01/app/oracle/product/8.1.7/network/admin/listener.ora
Listener Log File         /ora01/app/oracle/product/8.1.7/network/log/listener.log
Services Summary...
  PLSExtProc has 1 service handler(s)
  odb has 1 service handler(s)
The command completed successfully
The listener started successfully and has two listener instances (i.e., tcp and ipc). It also shows you the parameter and log file used. The listener reports one service handler for each instance; if you get the message The listener supports no services it may still work as service handlers are created dynamically as needed (i.e., when you log into the database).

You stop the listener with lsnrctl stop: 

[oracle@host oracle]$ lsnrctl stop

LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 12-DEC-2001 09:46:53

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=host.big-company.dk)(PORT=1521))
The command completed successfully
[oracle@host oracle]$
You can get the listener status as follows: 
[oracle@host admin]$ lsnrctl status

LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 12-DEC-2001 10:34:58

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=host.big-company.com)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 8.1.7.0.0 - Production
Start Date                12-DEC-2001 10:34:56
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /ora01/app/oracle/product/8.1.7/network/admin/listener.ora
Listener Log File         /ora01/app/oracle/product/8.1.7/network/log/listener.log
Services Summary...
  PLSExtProc has 1 service handler(s)
  odb has 1 service handler(s)
The command completed successfully
[oracle@host admin]$

Connect Descriptors and Service Names

A connect descriptor specifies the communiacation protocol (e.g., TCP), server name (host) and port (default is 1521) to use when communicating to the Oracle server (file tnsnames.ora):

DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.big-company.com)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = odb)
  )
Above we specify a TCP based communication protocol to host host.big-company.com using the default port 1521. The CONNECT_DATA section specifies the database we want access to using the SERVICE_NAME; this should be odb if you followed the installation above.

Users should not type in the connect descriptors each time they log on, so we want to set up service names that refer to the connection descriptors. A service name is an alias for a connection descriptor. This is done in the tnsnames.ora located in directory $ORACLE_HOME/network/admin/tnsnames.ora.

You can also install it in directory /etc and set the TNS_ADMIN variable appropriately.

I have included a sample tnsnames.ora file that works for me (you must edit the host name).

Configuring Local Naming Method

There are serveral naming methods to choose among and you will probably use either local naming method or Oracle Names Server. I use the simplets, local naming method, which require you to install the tnsnames.ora file and another file sqlnet.ora locally on each client connecting to the database. This is not required with Oracle Names Server.

To setup local naming method you must install a tnsnames.ora file in directory $ORACLE_HOME/network/admin/ on your UNIX client machine (as user oracle):

[oracle@host admin]$ ll tnsnames.ora
-rw-rw-r--    1 oracle   orainsta     1064 Nov  5 15:03 tnsnames.ora
[oracle@host admin]$
Windows clients running the Oracle client programs (i.e., win817client.zip) are described below. Options to tnsnames.ora:

  • odb.host.big-company.com is a name that you may change
  • Replace the host entry with your server
  • Keep port 1521

An example sqlnet.ora file 

# SQLNET.ORA Network Configuration File: /ora01/app/oracle/product/8.1.7/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = big-company.com
SQLNET.EXPIRE_TIME = 10
NAMES.DIRECTORY_PATH= (TNSNAMES)
The NAMES.DEFAULT_DOMAIN parameter specifies that any unqualified name (e.g., odb) should have big-company.com appended. The NAMES.DIRECTORY_PATH specified that Net8 uses tnsnames to resolve connect identifiers (e.g., odb to connect descriptors, that is, tcp and all the other information stored in a connect descriptor). The SQLNET.EXPIRE_TIME entry determines time interval in minutes to send a probe to verify the session is alive.

Listener Log Files

The listener log file is located in directory $ORACLE_HOME/network/log/ assuming that you have not specified otherwise in the listener configuration file listener.ora above (e.g., with a LOG_FILE entry). You can use the command tail -f listener.log to view changes in the log file.

Testing a UNIX client

Assuming, that you have installed the listener.ora, sqlnet.ora and tnsnames.ora on the Oracle server and started the listener with lsnrctl start you are ready to connect from another UNIX client.

On the UNIX client you install the Oracle client software (or the entire database which is what I did) and then installs the sqlnet.ora and tnsnames.ora files in the $ORACLE_HOME/network/admin/ directory on your client.

You can then try ping'ing the server from the client: 

[16:34-Oracle]# tnsping odb 4

TNS Ping Utility for Linux: Version 8.1.7.0.0 - Production on 12-DEC-2001 16:34:30

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=host.big-company.com)(PORT=1521))
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)
[16:34-Oracle]#
The listener.log on the server shows something like this: 
12-DEC-2001 16:04:55 * ping * 0
12-DEC-2001 16:04:55 * ping * 0
12-DEC-2001 16:04:55 * ping * 0
12-DEC-2001 16:04:55 * ping * 0
The real test is to connect to a database using sqlplus. From the client we try connecting to the scott database: 
[16:34-Oracle]# sqlplus scott/tiger@odb

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 12 16:37:12 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL>
The listener.log on the server shows the connection (I have erased the host and port entry): 
12-DEC-2001 16:07:37 * (CONNECT_DATA=(SERVICE_NAME=odb)(CID=(PROGRAM=)
  (HOST=localhost.localdomain)(USER=nh))) * 
  (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=xxxxx)) * establish * odb * 0

Windows Clients

On Windows clients you install the win817client.zip file as follows:

Creating Oracle Users With Read Permissions Only

Often you install the Windows client software for users that need read only access to a limited number of tables in the database, maybe they use a report-tool (e.g., Crystal Reports) to access tables and make queries.

The following command creates a user with no priviledges (with sqlplus system/manager): 

create user joe identified by joe;
Joe can't even log onto Oracle: 
[23:45-~]# sqlplus joe/joe

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 12 23:45:14 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

ERROR:
ORA-01045: user JOE lacks CREATE SESSION privilege; logon denied
We grant Joe the create session privilege (with sqlplus system/manager): 
SQL> grant create session to joe;
Joe can now log into Oracle, but Joe has not access to any tables - Joe can't even create a table: 
[23:46-~]# sqlplus joe/joe

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 12 23:47:14 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL> select table_name from user_tables;

no rows selected

SQL> create table test (id int primary key);
create table test (id int primary key)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>
We now grant Joe access rights (i.e., select priviledge) on each table that Joe must access. This is done by defining a new role, say DW_JOE holding the select priviledges and then assign Joe that role (with sqlplus system/manager): 
SQL> create role DW_JOE;

Role created.
We now add select priviledges on one table auth_user to the role DW_JOE (using an Oracle user that owns the example table auth_user): 
SQL> grant select on auth_user to DW_JOE;

Grant succeeded.
We grant Joe the DW_JOE role and then makes it the default role when Joe logs in (with sqlplus system/manager). 
SQL> grant DW_JOE to joe;

Grant succeeded.

SQL> alter user joe default role DW_JOE;

User altered.
nh@it.edu

[ View comment(s) ]   [ Post comment ]



Home         About LinuxLab Contact us