How to configure command history within SQL*Plus

Refer to the following blog for a full description.

http://sysdba.wordpress.com/2006/10/08/how-to-use-rlwrap-to-get-a-command-history-in-sqlplus/

For a short summary, the rlwrap (readline wrapper) utility can be used to wrap the sqlplus command to allow for command history.

1. Download the rlwrap utility from here.

http://utopia.knoware.nl/~hlub/uck/rlwrap/

2. After downloading and unpacking the tar.gz run the following as root.

./configure
make
make check
make install

3. The rlwrap utility can now be used to call sqlplus.

$ rlwrap sqlplus user/password

4. Create an alias.

alias sqlp ='rlwrap sqlplus'

Automatic Startup of Oracle Instances, Listeners and Enterprise Managers on Linux

This process will go through the setup and configuration to automate the startup and shutdown of the Oracle instances, listeners and Enterprise Managers (Database Control). We will be configuring the use of ssh as it is more secure than rsh. A lot of Linux distributions do not have rsh configured for their default installation. If we use ssh the data will be encrypted and we can avoid installing and configuring rsh.


1. Determine the state of the SSH service.

To determine if the sshd daemon is running use the following command.

# chkconfig --list sshd
sshd            0:off   1:off   2:on    3:on    4:on    5:on    6:off

The daemon is configured to run in system state 2, 3, 4 and 5. Ensure the sshd daemon is configured to automatically start with the system. To start the sshd daemon run the following command as root.

# chkconfig sshd on

As ssh uses port 22, ensure this port is accessible through the firewall.


2. Configure SSH for the root and oracle users. As the root user will need to ssh to the oracle user to startup the Oracle instances, listeners and Enterprise Managers.

a. Login as the root user and generate the private and public keys. Use the default file name of id_rsa.pub and do not enter a passphrase as this will not allow for automatic login.

[root@server ~]# cd $HOME/.ssh
[root@server .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): ENTER
Enter passphrase (empty for no passphrase): ENTER
Enter same passphrase again: ENTER
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
92:20:c7:8f:86:69:43:80:9a:48:a1:68:62:c5:03:62 root@server
[root@server .ssh]#

b. Secure the root user's .ssh directory and contents.

[root@server .ssh]# chmod go-w $HOME
[root@server .ssh]# chmod 700 $HOME/.ssh
[root@server .ssh]# chmod 600 $HOME/.ssh/*

c. Copy the id_rsa.pub file to the oracle user's .ssh directory.

[root@server .ssh]# scp id_rsa.pub oracle@server:/home/oracle/.ssh
The authenticity of host 'server (127.0.0.1)' can't be established.
RSA key fingerprint is 24:8b:b0:70:b1:00:81:13:a7:38:91:5a:39:c6:1c:ef.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server' (RSA) to the list of known hosts.
id_rsa.pub                                    100%  412     0.4KB/s   00:00  
[root@server .ssh]# 

d. Login as the oracle user and navigate to the .ssh directory and copy the contents of the id_rsa.pub file into a file called authorizated_keys2 and delete the id_rsa.pub file.

[oracle@server ~]$ cd $HOME/.ssh
[oracle@server .ssh]$ cat id_rsa.pub >> authorized_keys2
[oracle@server .ssh]$ rm id_rsa.pub

e. Secure the oracle user's .ssh directory and contents.

[oracle@server .ssh]$ chmod go-w $HOME
[oracle@server .ssh]$ chmod 700 $HOME/.ssh
[oracle@server .ssh]$ chmod 600 $HOME/.ssh/*

f. Login as root and test ssh to the oracle user. It should not prompt for a password or passphrase.

[root@server ~]# ssh oracle@server
Last login: Thu Apr 22 14:24:40 2010 from server
[oracle@server ~]$

3. Create startup and shutdown scripts.

a. Create two files in $ORACLE_BASE called dbora_start.sh and dbora_shut.sh. The dbora_start.sh will be used to start the instances, listeners and Enterprise Managers. The dbora_shut.sh will be used to shutdown the instances, listeners and Enterprise Managers. Change the permissions to allow them to be executed only by the Oracle owner and group.

$ chmod 750 dbora_start.sh
$ chmod 750 dbora_stop.sh

b. Edit the dbora_start.sh and use the following script as an example. This script will start the following:

• Oracle 9i instance and listener
• Oracle 10g instance, listener and Enterprise Manager
• Oracle 11g instance, listener and Enterprise Manager

$ORACLE_BASE/dbora_start.sh

#!/bin/bash
#
# Start the listeners and Enterprise Manager Database Control.
#
# Start the Oracle 9i listener.
#
ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER

#
# Start the Oracle 11g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER
$ORACLE_HOME/bin/emctl start dbconsole

#
# Start the Oracle 10g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER
$ORACLE_HOME/bin/emctl start dbconsole

#
# Start the instances. The dbstart script will look in the oratab
# and will start all instances having a Y in the third column.
#
$ORACLE_HOME/bin/dbstart
exit

c. Edit the dbora_shut.sh script and use the following script as an example. This script will shutdown the following:

• Oracle 9i instance and listener
• Oracle 10g instance, listener and Enterprise Manager
• Oracle 11g instance, listener and Enterprise Manager

$ORACLE_BASE/dbora_shut.sh

#!/bin/bash
#
# Stopping the listeners and Enterprise Manager Database Control.
#
# Stop the Oracle 9i listener.
#
ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER

#
# Stop the Oracle 11g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER
$ORACLE_HOME/bin/emctl stop dbconsole

#
# Stop the Oracle 10g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER
$ORACLE_HOME/bin/emctl stop dbconsole

#
# Stop the instances. The dbshut script will look in the oratab
# and will stop all instances.
#
$ORACLE_HOME/bin/dbshut
exit

4. Create a Linux script to be automatically called during system start and shutdown.

a. Login as the root user change to the /etc/init.d directory and create a file called dbora. Change the owner to the Oracle owner and the dba group. Ensure the permissions are set to 750.

# touch dbora
# chown oracle:dba dbora
# chmod 750 dbora

b. Edit the dbora file and use the following script as an example. This script will be configured to run during system startup and system shutdown. The script will determine if a startup or shutdown is in progress and then call the appropriate $ORACLE_BASE/dbora_start.sh or $ORACLE_BASE/dbora_shut.sh script. Change the ORACLE_BASE, ORACLE_HOME, ORACLE variables to match your $ORACLE_BASE, $ORACLE_HOME and Oracle owner respectively. Notice that the script uses SSH to login as the oracle to run the scripts.

/etc/init.d/dbora

#! /bin/sh -x
#
# Change the value of ORACLE_BASE and ORACLE_HOME to specify the
# correct Oracle base and home directories for your installation.
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

#
# Change the value of ORACLE_OWNER to the login name of the
# oracle owner.
#
ORACLE_OWNER=oracle; export ORACLE_OWNER
PATH=${PATH}:$ORACLE_HOME/bin; export PATH
HOST=`hostname`
PLATFORM=`uname`

if [ ! "$2" = "ORA_DB" ] ; then
    ssh $ORACLE_OWNER@$HOST $0 $1 ORA_DB
fi

case $1 in
'start')
    ssh $ORACLE_OWNER@$HOST $ORACLE_BASE/dbora_start.sh
    ;;
'stop')
    ssh $ORACLE_OWNER@$HOST $ORACLE_BASE/dbora_stop.sh
    ;;
*)
    echo "usage: $0 {start|stop}"
    exit
    ;;
esac

exit

c. Create symbolic links to the dbora script within the appropriate run level directories.

# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

5. Finally, do not forget to edit the oratab file and change the last column to Y. Otherwise the instances will not be started by the $ORACLE_HOME/bin/dbstart script nor shutdown by the $ORACLE_HOME/bin/dbshut script. Edit the /etc/oratab file as the root user and change the last column to Y for the SIDs you want to automatically startup and shutdown.

/etc/oratab

# more /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ora10:/opt/oracle/product/10.2.0/db_1:Y
ora9:/opt/oracle/product/9.2.0/db_1:Y
ora11:/opt/oracle/product/11.1.0/db_1:Y
#

Changing Oracle Environment Variables Using A Bash Script

I have a Linux machine with three different Oracle versions installed. Oracle 9i, Oracle 10g and Oracle 11g. All have been installed under one Linux account. Here are some scripts to set the environment variables for the instance to be accessed.

Oracle 9i Environment: SID ora9

ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

Oracle 10g Environment: SID ora10

ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

Oracle 11g Environment: SID ora11

ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

To run the scripts one must use the "source" command in order to set the environment variables in your own environment. Without running the script through source the environment variables will be set in the script's environment and not your own.

[oracle@oratest ~]$ source ora10.sh

Environment variables for ora10
------------------------------
ORACLE_SID=ora10
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
TNS_ADMIN=/opt/oracle/product/10.2.0/db_1/network/admin
LD_LIBRARY_PATH=/opt/oracle/product/10.2.0/db_1/lib
PATH=/opt/oracle/product/10.2.0/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin
------------------------------

[oracle@oratest ~]$

Another method is to create aliases to set the enviroment variables. The tricky part is the PATH. Specify a PATH_BASE variable to use when setting the new paths. Here's a sample bash_profile file. The ora10 instance will be the default, with ora9, ora10 and ora11 aliases to switch to different environments.

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
umask 022

ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
PATH_BASE=/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin; export PATH_BASE
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH

alias ora9='ORACLE_SID=ora9; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH; 
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

alias ora10='ORACLE_SID=ora10; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

alias ora11='ORACLE_SID=ora11; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

Oracle Installation Issues on Linux

I ran into a couple issues when installing Oracle 10g on Oracle Enterprise Linux 5.

The first problem was the runInstaller would not start the Oracle Universal Installer. The problem ended up being the libXp. Installing libXp-1.0.0.8.i386.rpm solved the problem. The package can be downloaded from http://rpm.pbone.net/.

The second problem occurred during installation where the Network pre-installation check failed due to the /etc/hosts not containing the correct information. Changing the /etc/hosts to match the following format solved the problem.

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.1.115   myhost.mydomain.com myhost

Installing Oracle 9i on Oracle Enterprise Linux 5 (which is based on Red Hat Enterprise Linux 5) is not supported by Oracle. However, thanks to nirajkvinit's blog, I was able to install Oracle 9i on Oracle Enterprise Linux 5 with success.

http://nirajkvinit.blogspot.com/2008/10/installing-oracle-9i-on-rhel5.html