Dave's Technical Notes

Tuesday, May 25, 2010

Automatic Oracle Data Pump Backup Process

I have used the following process to automate a full Data Pump Export on a nightly basis. The backups will be saved to another server on the network. Backups will be kept locally on the Oracle server for a week when they will be removed. The network storage area will contain all previous backups.

1. On the Oracle server create a mount to the location in which the backups will be stored. In this case, a Windows share will be used to write the backups.

# mkdir -p /mnt/serverName
# mount -t cifs //serverName/shareName /mnt/serverName -o username=windowsUser,password=windowsPassword

2. Edit /etc/fstab to mount during startup.

# more fstab
/dev/VolGroup00/LogVol00 /                      ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                   swap    defaults        0 0
//serverName/shareName  /mnt/serverName         cifs    username=windowsUsername,password=windowsPassword        0       0
#

3. In the Oracle user's home directory create two directories, Scripts and Backups. The Scripts directory will host the Python script used to automate the commands, the Backups directory will host the backups locally for seven days.

$ pwd
/home/oracle
$ ls -l
total 32
drwxr-xr-x 2 oracle oinstall 4096 May 26 13:28 Backups
drwxr-xr-x 2 oracle oinstall 4096 Apr 26 09:47 Desktop
drwxr-xr-x 2 oracle oinstall 4096 May 26 13:28 Scripts
$

4. Ensure Python 3 has been installed.

$ python3 --version
Python 3.1.2
$

5. Create the following Python 3 scripts in the Oracle user's Scripts directory. The script will do the following:

Execute the expdp to run a full export.

expdp '/ as sysdba' directory=data_pump_dir dumpfile=fileNamePrefix.dmp logfile=fileNamePrefix_expdp.log full=y

Move the dump and log files to the Backups directory in the Oracle user's home.
```
mv  /opt/oracle/admin/orcl/dpdump/fileNamePrefix* /home/oracle/Backups/
```

Compress the backup.

tar cvfj /home/oracle/Backups/fileNamePrefix.tbz2 /home/oracle/Backups/fileNamePrefix.dmp /home/oracle/Backups/fileNamePrefix_expdp.log

Once compressed, remove the original dump and log files.

rm /home/oracle/Backups/fileNamePrefix.dmp /home/oracle/Backups/fileNamePrefix_expdp.log

Copy the files to the network drive using rsync. The destination directory will be in the format of YYYY-MM.
```
rsync -avz --no-o --no-g --no-p --no-t /home/oracle/Backups/ /mnt/serverName/`date +%Y-%m`
```

Go through the local Backups directory and remove the backups which are older than seven days.

Send an email showing the status of the commands.

Log all actions taken. The log file will be created as scriptName.log located in the same directory of the the script itself.

import datetime
import time
import os
import re
import sys
import smtplib

class RunCommand():
        
    def __init__(self):
        # Write the log in the same directory as the script. Logname will be ScriptName.log
        self.f = open(str(sys.path[0] + "/" + re.sub('\.py(c)?','.log',os.path.basename(sys.argv[0]))),'a')
        
        self.logger("Initializing", "Started")
        
        # Get the current time and set the fileprefix.
        now = datetime.datetime.now() 
        self.timestr = now.strftime("%Y-%m-%d_%Hh%Mm%Ss")
        self.fileprefix = "orcl_full_expdp_" + self.timestr
        
        self.dpdumpdir = "/opt/oracle/admin/orcl/dpdump/"
        self.backupsdir = "/home/oracle/Backups/"
        
        # Build Data Pump Full Export command. Need to initially set environment variables.
        self.expdpcmd =  "source /home/oracle/.bash_profile; "
        self.expdpcmd += "expdp \\'/ as sysdba\\' directory=data_pump_dir dumpfile="
        self.expdpcmd += self.fileprefix
        self.expdpcmd += ".dmp logfile="
        self.expdpcmd += self.fileprefix
        self.expdpcmd += "_expdp.log full=y"
        
        # Build Move Command
        self.movecmd =  "mv " + self.dpdumpdir + self.fileprefix + "* " + self.backupsdir
        
        # Build Compress Command
        self.compresscmd =  "tar cvfj " + self.backupsdir + self.fileprefix
        self.compresscmd += ".tbz2 " + self.backupsdir + self.fileprefix
        self.compresscmd += ".dmp " + self.backupsdir + self.fileprefix + "_expdp.log"
        
        # Remove the dmp and log files as they have been compress into the tgz file.
        self.removecmd =  "rm " + self.backupsdir + self.fileprefix
        self.removecmd += ".dmp " + self.backupsdir + self.fileprefix + "_expdp.log"
        
        # Rsync command.
        self.rsynccmd = "rsync -avz --no-o --no-g --no-p --no-t " + self.backupsdir + " /mnt/serverName/`date +%Y-%m`"
        
        self.status = {}
        self.status['expdpcmd'] = -1
        self.status['movecmd'] = -1
        self.status['compresscmd'] = -1
        self.status['removecmd'] = -1
        self.status['rsynccmd'] = -1
        self.status['removeOldBackups'] = []      
        
        # Setup Email
        self.smtpserver = 'mail.company.com'
        self.sender = 'david@company.com'
        self.receivers = ['david@company.com']
        self.emailheader =  "From: David \n"
        self.emailheader += "To: David \n"
        self.emailheader += "Subject: OracleBackup: orcl\n\n"
        
        self.logger("Initializing", "Finished")
        
    def logger(self,section,action,msg=""):
        self.f.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S,") + section + "," + action + "," + msg + "\n")
        
    def run(self):
        # Run the commands only if the previous commands were successful.
        self.logger("Running", "Started")
        self.logger("expdpcmd", "Started", str(self.expdpcmd))
        self.status['expdpcmd'] = os.system(self.expdpcmd)
        self.logger("expdpcmd", "Finished", str(self.status['expdpcmd']))
        if self.status['expdpcmd'] == 0:
            self.logger("movecmd", "Started", str(self.movecmd))
            self.status['movecmd'] = os.system(self.movecmd)
            self.logger("movecmd", "Finished", str(self.status['movecmd']))
            if self.status['movecmd'] == 0:
                self.logger("compresscmd", "Started", str(self.compresscmd))
                self.status['compresscmd'] = os.system(self.compresscmd)
                self.logger("compresscmd", "Finished", str(self.status['compresscmd']))
                if self.status['compresscmd'] == 0:
                    self.logger("removecmd", "Started", str(self.removecmd))
                    self.status['removecmd'] = os.system(self.removecmd)
                    self.logger("removecmd", "Finished", str(self.status['removecmd']))
        self.logger("Running", "Finished", str(self.status))
            
    def rsyncFiles(self):
        self.logger("Synchronizing", "Started")
        self.logger("rsynccmd", "Started", str(self.rsynccmd))
        self.status['rsynccmd'] = os.system(self.rsynccmd)
        self.logger("rsynccmd", "Finished", str(self.status['rsynccmd']))
        self.logger("Synchronizing", "Finished", str(self.status))

    def removeOldBackups(self):
        self.logger("BackupCleanup", "Started")
        # Remove local backup files which are over 7 days old only if the synchronization job ran.
        if self.status['rsynccmd'] == 0:
            for file in os.listdir(self.backupsdir):
                filetime = os.path.getmtime(self.backupsdir + file) 
                currenttime = time.time()
                fileage = currenttime - filetime
                if fileage > 604800:
                    try:
                        os.unlink(self.backupsdir + file)
                    except:
                        self.logger("BackupCleanup", "Failed Removing", str(self.backupsdir + file))
                        self.status['removeOldBackups'].append([str(self.backupsdir + file),"Failed"])
                    else:
                        self.logger("BackupCleanup", "Success Removing", str(self.backupsdir + file))
                        self.status['removeOldBackups'].append([str(self.backupsdir + file),"Success"])
            self.logger("BackupCleanup", "Finished", str(self.status['removeOldBackups']))
        else:
            self.logger("BackupCleanup", "Finished", "No backups removed due to synchronization failure!")
            
    def sendStatusEmail(self):
        self.logger("SendingEmail", "Started")
        self.message = self.emailheader + "Oracle Data Pump Backup: " + self.timestr + "\n"
        
        self.message += "\nCommand Status:\n"
        for s in self.status:
            if s != 'removeOldBackups':
                self.message += "\t" + s + ": "
                if self.status[s] == 0:
                    self.message += "Success\n"
                elif self.status[s] == -1:
                    self.message += "NOP\n"
                else:
                    self.message += "Failed\n"
        
        self.message += "\nOld Backup Removal:\n"
        for f in self.status['removeOldBackups']:
            self.message += "\t" + f[1] + " removing backup file: " + f[0] + "\n"
        
        try:
            smtpObj = smtplib.SMTP(self.smtpserver)
            smtpObj.sendmail(self.sender, self.receivers, self.message)
        except SMTPException:
            self.logger("SendingEmail", "Finished", "Failed to send email!")
        else:
            self.logger("SendingEmail", "Finished", str(self.status))

rc = RunCommand()
rc.run()
rc.rsyncFiles()
rc.removeOldBackups()
rc.sendStatusEmail()

6. Use cron to execute the Python script every morning at 1:00 AM. Login as the oracle user and add the job to the oracle user's crontab file.

Issue the following command to start the default editor to add the job.
```
$ crontab -e
```

Add the following line then save and quit.

0 1 * * * /usr/local/bin/python3 /home/oracle/Scripts/OracleBackup.py

List the jobs.

$ crontab -l
0 1 * * * /usr/local/bin/python3 /home/oracle/Scripts/OracleBackup.py
$

Wednesday, April 21, 2010

Reinstall of Oracle Agent Did Not Reconnect to Oracle Management Service

After the reinstall of the Oracle Agent on Oracle Enterprise Linux 5 Update 4, the agent did not start communicating with the Oracle Management Service as it did before. I ran through the following which started the communication again.

1. Stop the agent.

AGENT_HOME/bin/emctl stop agent

2. Remove the files from the following locations.

AGENT_HOME/sysman/emd/lastupld.xml
AGENT_HOME/sysman/emd/state/
AGENT_HOME/sysman/emd/recv/
AGENT_HOME/sysman/emd/uploads/
AGENT_HOME/sysman/log/

3. Re-secure the agent.

AGENT_HOME/bin/emctl secure agent

4. Start the agent.

AGENT_HOME/bin/emctl start agent

Friday, April 16, 2010

Oracle Universal Installer (OUI) Initialization Error on Oracle Enterprise Linux 5 (OEL5)

I ran into the following problem after a fresh install of Oracle Enterprise Linux 5 when starting the Oracle Universal Installer to install Oracle Enterprise Manager.

[oracle@ogre Disk1]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be enterprise-4, enterprise-5, redhat-3,
redhat-4, redhat-5, redhat-5.1, SuSE-9, SuSE-10, UnitedLinux-1.0, asianux-1 or
asianux-2
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-04-15_04-43-50PM. Please wait ...
[oracle@ogre Disk1]$ 
Exception in thread "main" java.lang.UnsatisfiedLinkError:
/tmp/OraInstall2010-04-15_04-43-50PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6:
cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.(Unknown Source)
        at java.awt.Component.(Unknown Source)
        at oracle.sysman.emgc.oneclick.OneClickWizard.getGuiDefaults(OneClickWizard.java:239)
        at oracle.sysman.emgc.oneclick.OneClickWizard.(OneClickWizard.java:205)
        at oracle.sysman.emgc.oneclick.OneClick.(OneClick.java:230)
        at oracle.sysman.emgc.oneclick.OneClickInstaller.(OneClickInstaller.java:116)
        at oracle.sysman.emgc.oneclick.OneClickInstaller.process(OneClickInstaller.java:268)
        at oracle.sysman.emgc.oneclick.OneStartup.startup(OneStartup.java:359)
        at oracle.sysman.emgc.oneclick.OneArgs.main(OneArgs.java:650)
        at oracle.sysman.emgc.oneclick.OneStartup.main(OneStartup.java:367)

[oracle@ogre Disk1]$

The problem is that the i386 version of the libXp package is missing and is not installed by default in Oracle Enterprise Linux 5. Run the following command to confirm the package is missing.

[root@ogre Server]# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libXp
libXpm-3.5.5-3(i386)
libXp-devel-1.0.0-8.1.el5(x86_64)
libXpm-3.5.5-3(x86_64)
libXpm-devel-3.5.5-3(x86_64)
libXp-1.0.0-8.1.el5(x86_64)

Navigate to the Oracle Enterprise Linux 5 media and install the package manually.

[root@ogre Server]# pwd
/media/Enterprise Linux dvd 20090908/Server
[root@ogre Server]# ls libXp*
libXp-1.0.0-8.1.el5.i386.rpm        libXp-devel-1.0.0-8.1.el5.x86_64.rpm  libXpm-devel-3.5.5-3.i386.rpm
libXp-1.0.0-8.1.el5.x86_64.rpm      libXpm-3.5.5-3.i386.rpm               libXpm-devel-3.5.5-3.x86_64.rpm
libXp-devel-1.0.0-8.1.el5.i386.rpm  libXpm-3.5.5-3.x86_64.rpm
[root@ogre Server]# rpm -ivh ./libXp-1.0.0-8.1.el5.i386.rpm 
warning: ./libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@ogre Server]# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libXp
libXpm-3.5.5-3(i386)
libXp-devel-1.0.0-8.1.el5(x86_64)
libXp-1.0.0-8.1.el5(i386)
libXpm-3.5.5-3(x86_64)
libXpm-devel-3.5.5-3(x86_64)
libXp-1.0.0-8.1.el5(x86_64)
[root@ogre Server]#

Wednesday, February 10, 2010

Oracle Enterprise Manager Grid Control Installation

Oracle Enterprise Manager 10.2.0.1.1 on Oracle Enterprise Linux 5 Update 4 with Oracle 11.1.0.6 as the OMS repository.

I ran into a problem starting the HTTP_Server via the opmnctl startall command.

[oracle@ovmm lib]$ opmnctl status

Processes in Instance: EnterpriseManager0.ovmm.support.opentext.net
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status  
-------------------+--------------------+---------+---------
DSA                | DSA                |     N/A | Down    
HTTP_Server        | HTTP_Server        |     N/A | Down    
LogLoader          | logloaderd         |     N/A | Down    
dcm-daemon         | dcm-daemon         |     N/A | Down    
OC4J               | home               |    6322 | Alive   
WebCache           | WebCache           |    6331 | Alive   
WebCache           | WebCacheAdmin      |    6323 | Alive   

[oracle@ovmm lib]$ opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=ovmm.support.opentext.net:6200
    0 of 1 processes started.

ias-instance id=EnterpriseManager0.ovmm.support.opentext.net
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=6470)
    failed to start a managed process after the maximum retry limit
    Log:
    /opt/oracle/product/10.2.0/oms10g/opmn/logs/HTTP_Server~1

Performing the following link solved the problem.

ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

Thursday, March 26, 2009

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'

Tuesday, March 17, 2009

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
#

Thursday, March 12, 2009

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'