DBA Tips Archive for Oracle

  


Install Oracle Database 11g R1 on Linux - (RHEL 5)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Hardware Overview
  3. Install the Linux Operating System
  4. Install Required Linux Packages for Oracle
  5. Disk Configuration
  6. Operating System Configuration
  7. Network Configuration
  8. Create "oracle" User and Directories
  9. Download the Oracle Database 11g Release 1 Software
  10. Pre-Installation Tasks
  11. Install the Oracle Database 11g Release 1 Software
  12. Install the Oracle Database 11g Examples (formerly Companion) Software
  13. Apply the Database Patchset
  14. Configure Oracle Networking
  15. Create the Oracle Database
  16. Post-Installation Tasks
  17. Creating / Altering Tablespaces
  18. Setting up Automatic Database Starting and Stopping
  19. Miscellaneous Options
  20. About the Author



Overview

This article is a comprehensive guide for installing Oracle Database 11g Release 1 on the Red Hat Enterprise Linux 5 (RHEL5) operating environment.

Both 32-bit (x86) and 64-bit (x86_64) architectures are covered in this guide. Unless otherwise noted, the installation steps are the same for either. Having said that, one of the first decisions to make before continuing with this guide is which architecture you will be using! Both Oracle and Linux must be installed on the same operating system architecture. For example, 32-bit Oracle is only supported to run on 32-bit Linux O/S and 64-bit Oracle is only supported to run on 64-bit Linux O/S.

  Oracle 64-bit (x86-64) for Linux is supported on AMD64/EM64T and Intel Processor Chips that adhere to the x86-64 architecture with supported Linux releases. Running Oracle 32-bit on AMD64/EM64T (64-bit O/S) is not supported.

Oracle 32-bit (x86) running on AMD64/EM64T with a 32-bit O/S is, however, supported.

Please keep in mind that this article should not be considered a substitution for completely reading and understanding the official installation guide and release notes from Oracle. The following links can be used to download the official installation guides for Oracle11g Release 1 (11.1.0):

32-bit (x86) Installations

  Oracle Database Installation Guide 11g Release 1 (11.1) for Linux - (B32002-04)
  Oracle Database Quick Installation Guide 11g Release 1 (11.1) for Linux x86 - (B32281-03)
64-bit (x86_64) Installations
  Oracle Database Installation Guide 11g Release 1 (11.1) for Linux - (B32002-04)
  Oracle Database Quick Installation Guide 11g Release 1 (11.1) for Linux x86_64 - (B32285-01)
Here is a short introduction to some of the configuration parameters that will be used for installing the Oracle Database Software and creating a fully functional Oracle11g database:


By the time you finish this article, the following will be installed and configured:


The following is a list of items NOT covered in this article:


  Although this article is titled and introduced to work with Red Hat Enterprise Linux 5.1, I will actually be using a Red Hat Linux clone named CentOS. To read more about about CentOS, please visit their website at www.centos.org. I will also provide additional details about CentOS along with installation instructions later in this article.



Hardware Overview

For the purpose of this document, I will be utilizing a Pentium 4 (32-bit Clone) running CentOS Release 5.1. The Pentium 4 server will consist of three 36GB internal disks and 1GB of RAM. The first hard disk (/dev/sda) will be used to store the Linux operating system while the second hard disk (/dev/sdb) will be used to store the Oracle Database software. Finally, the third hard disk (/dev/sdc) will be used for all physical database files (data files, control files, online redo log files, flash recovery area)

The Pentium 4 server is configured as follows:

Oracle Database Server
Machine Name: vmlinux2.idevelopment.info
IP Address: 192.168.1.112
Processor: Pentium 4 - (1.8 GHz.)
Operation Environment: CentOS Release 5.1 - (32-bit)
Memory: 1 GB
Network: Intel 10/100/1000Mbps PCI Desktop Adapter - (PWLA8391GT)
Internal Disks: 3 x 36GB SCSI hard drives identified as follows:
  • /dev/sda
  • /dev/sdb
  • /dev/sdc

After discussing the hardware being utilized for this article, I end this section by describing how each of the disks will be used for the database software installation and database:

Software and Database Storage
Device Type Size - (GB) Usage Mount Point
/dev/sda Internal 36 CentOS Release 5.1 Operating System /
/dev/sdb Internal 36 Oracle Database 11g Software.
  • ORACLE_BASE: /u01/app/oracle
  • ORACLE_HOME: /u01/app/oracle/product/11.1.0/db_1
/u01
/dev/sdc Internal 36 Physical Database and Backup Files.
  • /u02/oradata/<ORACLE_SID>
  • /u02/flash_recovery_area
/u02



Install the Linux Operating System

This section provides a summary of the screens used to install the Linux operating system. As already mentioned, this article will use CentOS 5.1. Although I have used Red Hat Fedora and Red Hat Enterprise Linux in the past, I wanted to switch to a Linux environment that would guarantee all of the functionality contained with Oracle. This is where CentOS comes in. The CentOS Project takes the Red Hat Enterprise Linux 5 source RPMs, and compiles them into a free clone of the Red Hat Enterprise Server 5 product. This provides a free and stable version of the Red Hat Enterprise Linux 5 (AS/ES) operating environment that I can now use for testing different Oracle configurations. I have moved away from Fedora as I need a stable environment that is not only free, but as close to the actual Oracle supported operating system as possible. While CentOS is not the only project performing the same functionality, I tend to stick with it as it is stable and reacts fast with regards to updates by Red Hat.

Downloading CentOS

Use the links (below) to download CentOS 5.1. After downloading CentOS, you will then want to burn each of the ISO images to CD.

  CentOS.org

32-bit (x86) Installations

64-bit (x86_64) Installations

  If you are downloading the above ISO files to a MS Windows machine, there are many options for burning these images (ISO files) to a CD. You may already be familiar with and have the proper software to burn images to CD. If you are not familiar with this process and do not have the required software to burn images to CD, here are just two (of many) software packages that can be used:

  UltraISO
  Magic ISO Maker


Installing CentOS

This section provides a summary of the screens used to install CentOS. For more detailed installation instructions, it is possible to use the manuals from Red Hat Linux http://www.redhat.com/docs/manuals/. I would suggest, however, that the instructions I have provided below be used for this Oracle 11g configuration.

After downloading and burning the CentOS images (ISO files) to CD, insert CentOS Disk #1 into the database server (vmlinux2 in this example), power it on, and answer the installation screen prompts as noted below.

Boot Screen

The first screen is the CentOS boot screen. At the boot: prompt, hit [Enter] to start the installation process.
Media Test
When asked to test the CD media, tab over to [Skip] and hit [Enter]. If there were any errors, the media burning software would have warned us. After several seconds, the installer should then detect the video card, monitor, and mouse. The installer then goes into GUI mode.
Welcome to CentOS
At the welcome screen, click [Next] to continue.
Language / Keyboard Selection
The next two screens prompt you for the Language and Keyboard settings. Make the appropriate selection for your configuration and click [Next] to continue.
Detect Previous Installation
Note that if the installer detects a previous version of CentOS, it will ask if you would like to "Install CentOS" or "Upgrade an existing Installation". Always select to "Install CentOS".
Disk Partitioning Setup
If prompted to initialize any of the drives (i.e. the partition table on device /dev/sdX was unreadable), click [Yes] to acknowledge the warning.

Keep the default selection to [Remove linux partitions on selected drives and create default layout] and check the option to [Review and modify partitioning layout].

Note: When the installer detects multiple initialized drives, it will check all of them to be used for the Linux installation. For this installation, I only want to install the Linux software on the first hard disk (/dev/sda) so I will keep it selected (checked). Uncheck all other drives that the installer selected which for my installation was /dev/sdb and /dev/sdc. I will manually partition and create a file system for /dev/sdb and /dev/sdc later in this article.

Click [Next] to continue.

You will then be prompted with a dialog window asking if you really want to remove all partitions. Click [Yes] to acknowledge this warning.

Partitioning
The installer will then allow you to view (and modify if needed) the disk partitions it automatically selected.

For most automatic layouts, the installer will choose 100MB for /boot, double the amount of RAM (systems with < 2GB RAM) or an amount equal to RAM (systems with > 2GB RAM) for swap, and the rest going to the root (/) partition. Starting with EL 4, the installer will create the same disk configuration as just noted but will create them using the Logical Volume Manager (LVM). For example, it will partition the first hard drive (/dev/sda for my configuration) into two partitions — one for the /boot partition (/dev/sda1) and the remainder of the disk dedicate to a LVM named VolGroup00 (/dev/sda2). The LVM Volume Group (VolGroup00) is then partitioned into two LVM partitions - one for the root filesystem (/) and another for swap.

The main concern during the partitioning phase is to ensure enough swap space is allocated as required by Oracle (which is a multiple of the available RAM). The following is Oracle's requirement for swap space:

Available RAM Swap Space Required
Between 1 GB and 2 GB 1.5 times the size of RAM
Between 2 GB and 8 GB Equal to the size of RAM
More than 8 GB .75 times the size of RAM

For the purpose of this install, I will accept all automatically preferred sizes. (Including 2GB for swap since I have 2GB of RAM installed.)

If for any reason, the automatic layout does not configure an adequate amount of swap space, you can easily change that from this screen. To increase the size of the swap partition, [Edit] the volume group VolGroup00. This will bring up the "Edit LVM Volume Group: VolGroup00" dialog. First, [Edit] and decrease the size of the root file system (/) by the amount you want to add to the swap partition. For example, to add another 512MB to swap, you would decrease the size of the root file system by 512MB (i.e. 36,032MB - 512MB = 35,520MB). Now add the space you decreased from the root file system (512MB) to the swap partition. When completed, click [OK] on the "Edit LVM Volume Group: VolGroup00" dialog.

Finally, verify that no partitions or file systems exist for /dev/sdb and /dev/sdc. If any do exist, click on the partition and [Delete] it so that the entire disk has a single free entry with all "Free space" available. When all complete, the only partitions that should be created are the two partitions on the first SCSI disk (/dev/sda1 and /dev/sda2) described earlier in this section.

Once you are satisfied with the disk layout, click [Next] to continue.

Boot Loader Configuration
The installer will use the GRUB boot loader by default. To use the GRUB boot loader, accept all default values and click [Next] to continue.
Network Configuration
Before starting the Linux installation, I verified the NIC interface (card) was installed. This screen should have successfully detected the network device. Since we will be using this machine to host an Oracle database, there will be several changes that need to be made to the network configuration. The settings you make here will, of course, depend on your network configuration. The key point to make is that the machine should never be configured with DHCP since it will be used to host the Oracle database server. You will need to configure the machine with a static IP address. You will also need to configure the server with a real host name.

First, make sure that the network device is checked to [Active on boot]. (This should already be checked by the installer.)

Second, [Edit] eth0 as follows. Verify that the option "Enable IPv4 support" is selected. Click off the option for "Use dynamic IP configuration (DHCP)" and configure a static IP address and Netmask for your environment. Click off the option to "Enable IPv6 support". You may choose to use a different IP address for eth0 that I have documented in this guide and that is OK. Ensure eth0 is configured for your public network:

eth0:
- Check ON the option to [Enable IPv4 support]
- Check OFF the option to [Use dynamic IP configuration (DHCP)] - (select Manual configuration)
   IPv4 Address: 192.168.1.112
   Prefix (Netmask): 255.255.255.0
- Check OFF the option to [Enable IPv6 support]

Continue by manually setting your hostname. I used "vmlinux2" for the node. Finish this dialog off by supplying your gateway and DNS servers.

Time Zone Selection
Select the appropriate time zone for your environment and click [Next] to continue.
Set Root Password
Select a root password and click [Next] to continue.
Package Installation Defaults
By default, CentOS installs most of the software required for a typical server. There are several other packages (RPMs), however, that are required to successfully install the Oracle Database software. For the purpose of this article, select the radio button [Customize now] and click [Next] to continue.

This is where you pick the packages to install. Most of the packages required for the Oracle software are grouped into "Package Groups" (i.e. Application -> Editors). Since this node will be hosting the Oracle Database software, verify that at least the following package groups are selected for install. Note that for many of the Linux package groups, not all of the packages associated with that group get selected for installation. (Note the "Optional packages" button after selecting a package group.) So although the package group gets selected for install, some of the packages required by Oracle do not get installed. In fact, there are some packages that are required by Oracle that do not belong to any of the available package groups (i.e. libaio-devel). Not to worry. A complete list of required packages for Oracle Database 11g will be provided at the end of this section. These packages will need to be manually installed from the CentOS CDs after the operating system install. For now, install the following package groups:

  • Desktop Environments
    • GNOME Desktop Environment
  • Applications
    • Editors
    • Graphical Internet
    • Text-based Internet
  • Development
    • Development Libraries
    • Development Tools
    • Legacy Software Development
  • Servers
    • Server Configuration Tools
  • Base System
    • Administration Tools
    • Base
    • Java
    • Legacy Software Support
    • System Tools
    • X Window System

In addition to the above packages, select any additional packages you wish to install for this node. After selecting the packages to install click [Next] to continue.

About to Install
This screen is basically a confirmation screen. Click [Continue] to start the installation. During the installation process, you will be asked to switch CDs depending on which packages you selected to install.
Congratulations
And that's it. You have successfully installed CentOS on the database server. The installer will eject the CD from the CD-ROM drive. Take out the CD and click [Reboot] to reboot the system.
Post Installation Wizard Welcome Screen
When the system boots into CentOS for the first time, it will prompt you with another Welcome screen for the "Post Installation Wizard". The post installation wizard allows you to make final O/S configuration settings. On the "Welcome" screen, click [Forward] to continue.
Firewall
On this screen, make sure to select the [Disabled] option and click [Forward] to continue.

You will be prompted with a warning dialog about not setting the firewall. When this occurs, click [Yes] to continue.

SELinux
On the SELinux screen, choose the [Disabled] option if you plan to use the Oracle Cluster File System (OCFS2). For Oracle installations, I typically opt to disable SELinux. Click [Forward] to continue.

You will be prompted with a warning dialog warning that changing the SELinux setting will require rebooting the system so the entire file system can be relabeled. When this occurs, click [Yes] to acknowledge a reboot of the system will occur after firstboot (Post Installation Wizard) is completed.

Kdump
Accept the default setting on the Kdump screen (disabled) and click [Forward] to continue.
Date and Time Settings
Adjust the date and time settings if necessary and click [Forward] to continue.
Create User
Create any additional (non-oracle) operating system user accounts if desired and click [Forward] to continue. For the purpose of this article, I will not be creating any additional operating system accounts. I will be creating the "oracle" user account during the Oracle database installation later in this guide.

If you chose not to define any additional operating system user accounts, click [Continue] to acknowledge the warning dialog.

Sound Card
This screen will only appear if the wizard detects a sound card. On the sound card screen click [Forward] to continue.
Additional CDs
On the "Additional CDs" screen click [Finish] to continue.
Reboot System
Given I changed the SELinux option (to disabled), I am prompted to reboot the system. Click [OK] to reboot the system for normal use.
Login Screen
After rebooting the machine, you are presented with the login screen. Login using the "root" user account and the password you provided during the installation.



Install Required Linux Packages for Oracle

After installing CentOS, the next step is to verify and install all packages (RPMs) required by Oracle Database 11g. The Oracle Universal Installer (OUI) performs checks on your machine during installation to verify that it meets the appropriate operating system package requirements. To ensure that these checks complete successfully, verify the software requirements documented in this section before starting the Oracle installs.

Although many of the required packages for Oracle were installed during the CentOS installation, several will be missing either because they were considered optional within the package group or simply didn't exist in any package group!

32-bit (x86) Installations

The packages listed in this section (or later versions) are required for Oracle Database 11g Release 1 running on the x86 (32-bit) CentOS 5 platform.

Each of the packages listed above can be found on CD #1, CD #2, or CD #3 on the x86 (32-bit) CentOS 5.1 CDs. While it is possible to query each individual package to determine which ones are missing and need to be installed, an easier method is to run the rpm -Uvh PackageName command from the three CDs as follows. For packages that already exist and are up to date, the RPM command will simply ignore the install and print a warning message to the console that the package is already installed.

# From CentOS 5.1 - [CD #1]
mkdir -p /media/cdrom
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
cd /
eject

# From CentOS 5.1 - [CD #2]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-2.*
cd /
eject

# From CentOS 5.1 - [CD #3]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh compat-libstdc++-33*
rpm -Uvh libaio-devel-0.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject
64-bit (x86_64) Installations
The packages listed in this section (or later versions) are required for Oracle Database 11g Release 1 running on the x86_64 (64-bit) CentOS 5 platform.

  Note that there are a number of packages where both the 64-bit and 32-bit RPMs must be installed. Fortunately, both versions are provided on the 64-bit CentOS 5 installation media.

Each of the packages listed above can be found on CD #1, CD #2, CD #3, or CD # 5 on the x86_64 (64-bit) CentOS 5.1 CDs. While it is possible to query each individual package to determine which ones are missing and need to be installed, an easier method is to run the rpm -Uvh PackageName command from the three CDs as follows. For packages that already exist and are up to date, the RPM command will simply ignore the install and print a warning message to the console that the package is already installed.

# From CentOS 5.1 - [CD #1]
mkdir -p /media/cdrom
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
cd /
eject

# From CentOS 5.1 - [CD #2]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh unixODBC-2.*
cd /
eject

# From CentOS 5.1 - [CD #3]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject

# From CentOS 5.1 - [CD #5]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/CentOS
rpm -Uvh sysstat-7.*
cd /
eject



Disk Configuration

As noted in the section "Hardware Overview", I discussed the disks that will be used for the Linux operating system, the Oracle Database 11g Software installation, and the physical database files. This section discusses the steps required to partition (and mount) the two internal SCSI hard drives to be used by the Oracle software and database files.

Before using the second and third SCSI hard disk, we need to first partition the disks and then create a file system (ext3) on them. After partitioning and creating the file system, each of the disks will need to be mounted.

The Linux operating system was installed on the first disk /dev/sda. The second hard disk (/dev/sdb) will be used to store the Oracle Database 11g Software and mounted to the directory /u01. The third internal SCSI disk (/dev/sdc) will be used to store the physical database files and mounted to the directory /u02.

  The steps in this section are completely optional as it is possible to simply create the directories required for installing the Oracle Database 11g software and database files off of the root directory (i.e. mkdir /u01 /u02). For the sake of simplicity, this is often done in test environments but however is not recommended as a general practice.

In a production environment, these directories would be created on a separate file system.

The following steps require use of the root user account:


# [ LOGIN AS ROOT ] # su -
# [ CREATE SINGLE PARTITION ON SECOND INTERNAL SCSI HARD DISK ] # fdisk /dev/sdb Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-4699, default 1): 1 Last cylinder or +size or +sizeM or +sizeK (1-4699, default 4699): 4699 Command (m for help): p Disk /dev/sdb: 38.6 GB, 38654705664 bytes 255 heads, 63 sectors/track, 4699 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdb1 1 4699 37744686 83 Linux Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks.
# [ CREATE SINGLE PARTITION ON THIRD INTERNAL SCSI HARD DISK ] # fdisk /dev/sdc Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-4699, default 1): 1 Last cylinder or +size or +sizeM or +sizeK (1-4699, default 4699): 4699 Command (m for help): p Disk /dev/sdc: 38.6 GB, 38654705664 bytes 255 heads, 63 sectors/track, 4699 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdc1 1 4699 37744686 83 Linux Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks.
# [ CREATE NEW EXT3 FILE SYSTEM ON SECOND INTERNAL SCSI HARD DISK ] # mkfs.ext3 -b 4096 /dev/sdb1 mke2fs 1.39 (29-May-2006) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) 4718592 inodes, 9436171 blocks 471808 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=0 288 block groups 32768 blocks per group, 32768 fragments per group 16384 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000, 7962624 Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done This filesystem will be automatically checked every 38 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override.
# [ CREATE NEW EXT3 FILE SYSTEM ON THIRD INTERNAL SCSI HARD DISK ] # mkfs.ext3 -b 4096 /dev/sdc1 mke2fs 1.39 (29-May-2006) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) 4718592 inodes, 9436171 blocks 471808 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=0 288 block groups 32768 blocks per group, 32768 fragments per group 16384 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000, 7962624 Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done This filesystem will be automatically checked every 35 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override.
# [ CREATE MOUNT POINT DIRECTORIES ] # mkdir /u01 # mkdir /u02
# [ INSERT THE FOLLOWING ENTRIES INTO /etc/vfstab ] # echo "/dev/sdb1 /u01 ext3 defaults 1 1" >> /etc/fstab # echo "/dev/sdc1 /u02 ext3 defaults 1 1" >> /etc/fstab
# [ MOUNT THE NEW FILE SYSTEMS ] # mount /u01 # mount /u02
# [ CHECK FOR NEW FILE SYSTEMS ] # df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/VolGroup00-LogVol00 34470656 2787292 29904064 9% / /dev/sda1 101086 11784 84083 13% /boot tmpfs 517660 0 517660 0% /dev/shm /dev/sdb1 37152364 180240 35084892 1% /u01 /dev/sdc1 37152364 180240 35084892 1% /u02



Operating System Configuration

This section describes hardware/software requirements and the steps that should be performed by the DBA as it relates to the Linux operating system before performing the Oracle Database 11g software installation. The following steps need to be performed as the root user account.

Login as root

% su -


Kernel Requirements

The following are the kernel requirements for Oracle Database 11g Release 1 running on Asianux 3.0, Oracle Linux 5.0, Red Hat Enterprise Linux 5.0, and CentOS 5:

32-bit (x86) Installations

Required kernel version: 2.6.18. This kernel, or any of the kernels supplied in updates, is certified to work with Oracle Database 11g Release 1.

Check your kernel version by running the following command:

# uname -rm
2.6.18-53.el5 i686
64-bit (x86_64) Installations
Required kernel version: 2.6.18. This kernel, or any of the kernels supplied in updates, is certified to work with Oracle Database 11g Release 1.

Check your kernel version by running the following command:

# uname -rm
2.6.18-53.el5 x86_64


RAM Memory

Installing Oracle Database 11g on Linux requires a minimum of 1GB of memory.

To check the amount of memory you have installed, type:

# cat /proc/meminfo | grep MemTotal
MemTotal:      1035324 kB


Swap Space

The following is Oracle's requirement for swap space:

Available RAM Swap Space Required
Between 1 GB and 2 GB 1.5 times the size of RAM
Between 2 GB and 8 GB Equal to the size of RAM
More than 8 GB .75 times the size of RAM

(An inadequate amount of swap during the installation will cause the Oracle Universal Installer to either "hang" or "die")

To check the amount of memory you have, type:

# cat /proc/meminfo | grep MemTotal
MemTotal:      1035324 kB

To check the amount of swap you have allocated, type:

# cat /proc/meminfo | grep SwapTotal
SwapTotal:     2031608 kB

  If you have less than 1GB of memory (between your RAM and SWAP), you can add temporary swap space by creating a temporary swap file. This way you do not have to use a raw device or even more drastic, rebuild your system.

As root, make a file that will act as additional swap space, let's say about 500MB:
# dd if=/dev/zero of=tempswap bs=1k count=500000

Now we should change the file permissions:
# chmod 600 tempswap

Finally we format the "partition" as swap and add it to the swap space:
# mke2fs tempswap
# mkswap tempswap
# swapon tempswap


Checking /tmp Directory

Ensure enough disk space in the /tmp directory. An amount of disk space equal to 400 MB (or greater) needs to be available for the Oracle Database installation.

To check the amount of free disk available in the /tmp directory, type:

# df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       33G  2.7G   29G   9% /

  If 1 GB or more is not available in /tmp, you can create a "tmp" directory in another file system, then set the environment variables TMP (used by Oracle) and TMPDIR (used by operating system programs like the linker "ld" and library archiver "ar") to point to this location. For example:
# mkdir /u01/app/oracle/tmp
# TEMP=/u01/app/oracle/tmp; export TEMP
# TMPDIR=/u01/app/oracle/tmp; export TMPDIR


Configuring Kernel Parameters

This section documents the checks and modifications to the Linux kernel that should be made by the DBA to support Oracle Database 11g. Before detailing these individual kernel parameters, it is important to fully understand the key kernel components that are used to support the Oracle Database environment.

The kernel parameters and shell limits presented in this section are recommended values only as documented by Oracle. For production database systems, Oracle recommends that you tune these values to optimize the performance of the system.

Verify that the kernel parameters shown in this section are set to values greater than or equal to the recommended values. Also note that when setting the four semaphore values that all four values need to be entered on one line.

Shared Memory

Shared memory allows processes to access common structures and data by placing them in a shared memory segment. This is the fastest form of Inter-Process Communications (IPC) available - mainly due to the fact that no kernel involvement occurs when data is being passed between the processes. Data does not need to be copied between processes.

Oracle makes use of shared memory for its Shared Global Area (SGA) which is an area of memory that is shared by all Oracle backup and foreground processes. Adequate sizing of the SGA is critical to Oracle performance since it is responsible for holding the database buffer cache, shared SQL, access paths, and so much more.

To determine all current shared memory limits, use the following:

# ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1

The following list describes the kernel parameters that can be used to change the shared memory configuration for the server:

  • shmmax - Defines the maximum size (in bytes) for a shared memory segment. The Oracle SGA is comprised of shared memory and it is possible that incorrectly setting shmmax could limit the size of the SGA. When setting shmmax, keep in mind that the size of the SGA should fit within one shared memory segment. An inadequate shmmax setting could result in the following:
    ORA-27123: unable to attach to shared memory segment

    You can determine the value of shmmax by performing the following:

    # cat /proc/sys/kernel/shmmax
    4294967295
    For most Linux systems, the default value for shmmax is 32MB. This size is often too small to configure the Oracle SGA. The default value for shmmax in CentOS 5 is 4GB which is more than enough for the Oracle configuration described in this article. Note that this value of 4GB is not the "normal" default value for shmmax in a Linux environment — CentOS 5 inserts the following two entries in the file /etc/sysctl.conf:
    # Controls the maximum shared segment size, in bytes
    kernel.shmmax = 4294967295

    I highly recommend removing this entry from the /etc/sysctl.conf file and replacing it with the recommended values document by Oracle. All recommended Oracle kernel parameter values are documented later in this section.

    Oracle recommends sizing the shmmax parameter as the minimum of (4GB - 1 byte), or half the size of physical memory (in bytes), whichever is lower. Given the database server used for this example i is configured with 1GB of physical RAM, I will configure shmmax to 512MB.

  • shmmni - This kernel parameter is used to set the maximum number of shared memory segments system wide. The default value for this parameter is 4096. This value is sufficient and typically does not need to be changed.

    You can determine the value of shmmni by performing the following:

    # cat /proc/sys/kernel/shmmni
    4096

  • shmall - This parameter controls the total amount of shared memory (in pages) that can be used at one time on the system. The value of this parameter should always be at least:

    ceil(SHMMAX/PAGE_SIZE)

    You can determine the value of shmall by performing the following:

    # cat /proc/sys/kernel/shmall
    268435456
    For most Linux systems, the default value for shmall is 2097152 and is adequate for most configurations. The default value for shmall in CentOS 5 is 268435456 (see above) which is more than enough for the Oracle configuration described in this article. Note that this value of 268435456 is not the "normal" default value for shmall in a Linux environment — CentOS 5 inserts the following two entries in the file /etc/sysctl.conf:
    # Controls the maximum number of shared memory segments, in pages
    kernel.shmall = 268435456

    I highly recommend removing this entry from the /etc/sysctl.conf file and replacing it with the recommended values document by Oracle. All recommended Oracle kernel parameter values are documented later in this section.

  • shmmin - This parameter controls the minimum size (in bytes) for a shared memory segment. The default value for shmmin is 1 and is adequate for the Oracle configuration described in this article.

    You can determine the value of shmmin by performing the following:

    # ipcs -lm | grep "min seg size"
    min seg size (bytes) = 1
Semaphores
After the DBA has configured the shared memory settings, it is time to take care of configuring the semaphores. The best way to describe a semaphore is as a counter that is used to provide synchronization between processes (or threads within a process) for shared resources like shared memory. Semaphore sets are supported in System V where each one is a counting semaphore. When an application requests semaphores, it does so using "sets".

To determine all current semaphore limits, use the following:

# ipcs -ls

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
You can also use the following command:
# cat /proc/sys/kernel/sem
250     32000   32      128

The following list describes the kernel parameters that can be used to change the semaphore configuration for the server:

  • semmsl - This kernel parameter is used to control the maximum number of semaphores per semaphore set. Oracle recommends setting semmsl to the largest PROCESS instance parameter setting in the init.ora file for all databases on the Linux system plus 10. Also, Oracle recommends setting the semmsl to a value of no less than 100.

  • semmni - This kernel parameter is used to control the maximum number of semaphore sets in the entire Linux system. Oracle recommends setting semmni to a value of no less than 100.

  • semmns - This kernel parameter is used to control the maximum number of semaphores (not semaphore sets) in the entire Linux system. Oracle recommends setting the semmns to the sum of the PROCESSES instance parameter setting for each database on the system, adding the largest PROCESSES twice, and then finally adding 10 for each Oracle database on the system. Use the following calculation to determine the maximum number of semaphores that can be allocated on a Linux system. It will be the lesser of:
    SEMMNS  -or-  (SEMMSL * SEMMNI)

  • semopm - This kernel parameter is used to control the number of semaphore operations that can be performed per semop system call. The semop system call (function) provides the ability to do operations for multiple semaphores with one semop system call. A semaphore set can have the maximum number of semmsl semaphores per semaphore set and is therefore recommended to set semopm equal to semmsl in some situations. Oracle recommends setting the semopm to a value of no less than 100.
File Handles
When configuring the Linux server, it is critical to ensure that the maximum number of file handles is large enough. The setting for file handles denotes the number of open files that you can have on the Linux system.

Use the following command to determine the maximum number of file handles for the entire system:

# cat /proc/sys/fs/file-max
102312

Oracle recommends that the file handles for the entire system be set to at least:

512 * PROCESSES
where PROCESSES is the value specified for the instance(s) running on the server. I typically use a value no less than 65536.

  You can query the current usage of file handles by using the following:
# cat /proc/sys/fs/file-nr
3072    0       102312
The file-nr file displays three parameters:
  • Total allocated file handles
  • Currently used file handles
  • Maximum file handles that can be allocated

  If you need to increase the value in /proc/sys/fs/file-max, then make sure that the ulimit is set properly. Usually for Linux 2.4 and 2.6 it is set to unlimited. Verify the ulimit setting my issuing the ulimit command:
# ulimit
unlimited

IP Local Port Range
Oracle strongly recommends to set the local port range ip_local_port_range for outgoing messages to "1024 65000" which is needed for systems with high-usage. This kernel parameter defines the local port range for TCP and UDP traffic to choose from.

The default value for ip_local_port_range is ports 32768 through 61000 which is inadequate for a successful Oracle configuration.

Use the following command to determine the value of ip_local_port_range:

# cat /proc/sys/net/ipv4/ip_local_port_range
32768   61000
Networking Settings
With Oracle 9.2.0.1 and later, Oracle makes use of UDP as the default protocol on Linux for inter-process communication (IPC), such as Cache Fusion and Cluster Manager buffer transfers between instances within the RAC cluster.

Oracle strongly suggests to adjust the default and maximum receive buffer size (SO_RCVBUF socket option) to 4MB and the default and maximum send buffer size (SO_SNDBUF socket option) to 256KB.

The receive buffers are used by TCP and UDP to hold received data until it is read by the application. The receive buffer cannot overflow because the peer is not allowed to send data beyond the buffer size window. This means that datagrams will be discarded if they don't fit in the socket receive buffer, potentially causing the sender to overwhelm the receiver.

Use the following commands to determine the current buffer size (in bytes) of each of the IPC networking parameters:

# cat /proc/sys/net/core/rmem_default
109568

# cat /proc/sys/net/core/rmem_max
131071

# cat /proc/sys/net/core/wmem_default
109568

# cat /proc/sys/net/core/wmem_max
131071
Oracle 11g Required Kernel Parameter Settings
Now let's review what has been covered thus far. The Oracle Database 11g documentation defines the following parameters and limits that should be validated before creating an Oracle database.

Parameter Recommended Value Default Value How to Check
shmmax Minimum of the following values:
  • Half the size of the physical memory
  • 4GB - 1 byte
  • 33554432 cat /proc/sys/kernel/shmmax
    shmmni 4096 4096 cat /proc/sys/kernel/shmmni
    shmall 2097152 2097152 cat /proc/sys/kernel/shmall
    shmmin 1 1 ipcs -lm | grep "min seg size"
     
    semmsl 250 250 cat /proc/sys/kernel/sem | awk '{print $1}'
    semmns 32000 32000 cat /proc/sys/kernel/sem | awk '{print $2}'
    semopm 100 32 cat /proc/sys/kernel/sem | awk '{print $3}'
    semmni 128 128 cat /proc/sys/kernel/sem | awk '{print $4}'
     
    file-max 512 * PROCESSES 102696 cat /proc/sys/fs/file-max
     
    ip_local_port_range 1024   65000 32768   61000 cat /proc/sys/net/ipv4/ip_local_port_range
     
    rmem_default 4194304 109568 cat /proc/sys/net/core/rmem_default
    rmem_max 4194304 131071 cat /proc/sys/net/core/rmem_max
    wmem_default 262144 109568 cat /proc/sys/net/core/wmem_default
    wmem_max 262144 131071 cat /proc/sys/net/core/wmem_max

      If the current value for any kernel parameter is higher than the value listed in this table, do not change the value of that parameter.

    Setting Kernel Parameters for Oracle
    If the value of any kernel parameter is different to the recommended value, they will need to be modified. For this article, I identified and provide the following values that will need to be added to the /etc/sysctl.conf file which is used during the boot process.

      Prior to adding the following kernel parameter values to the /etc/sysctl.conf file, you will need to remove the two shared memory kernel parameters (kernel.shmmax and kernel.shmall) put in by CentOS 5.

    
    # ......................... < SNIP> .........................
    
    # +---------------------------------------------------------+
    # | KERNEL PARAMETERS FOR ORACLE 11g                        |
    # +---------------------------------------------------------+
    # | Configure the kernel parameters for all Oracle Linux    |
    # | servers by setting shared memory and semaphores,        |
    # | setting the maximum amount of file handles, setting the |
    # | networking parameters, and finally setting the IP local |
    # | port range.                                             |
    # +---------------------------------------------------------+
    
    # +---------------------------------------------------------+
    # | SHARED MEMORY                                           |
    # +---------------------------------------------------------+
    kernel.shmmax = 536870912
    kernel.shmmni = 4096
    kernel.shmall = 2097152
    
    # +---------------------------------------------------------+
    # | SEMAPHORES                                              |
    # | ----------                                              |
    # |                                                         |
    # | SEMMSL_value  SEMMNS_value  SEMOPM_value  SEMMNI_value  |
    # |                                                         |
    # +---------------------------------------------------------+
    kernel.sem = 250 32000 100 128
    
    # +---------------------------------------------------------+
    # | FILE HANDLES                                            |
    # ----------------------------------------------------------+
    fs.file-max = 102696
    
    # +---------------------------------------------------------+
    # | LOCAL IP RANGE                                          |
    # ----------------------------------------------------------+
    net.ipv4.ip_local_port_range = 1024 65000
    
    # +---------------------------------------------------------+
    # | NETWORKING                                              |
    # ----------------------------------------------------------+
    net.core.rmem_default = 4194304
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 262144

    After adding the above lines to the /etc/sysctl.conf file, they persist each time the system reboots. If you would like to make these kernel parameter value changes to the current system without having to first reboot, enter the following command:

    # /sbin/sysctl -p
    net.ipv4.ip_forward = 0
    net.ipv4.conf.default.rp_filter = 1
    net.ipv4.conf.default.accept_source_route = 0
    kernel.sysrq = 0
    kernel.core_uses_pid = 1
    net.ipv4.tcp_syncookies = 1
    kernel.msgmnb = 65536
    kernel.msgmax = 65536
    kernel.shmmax = 536870912
    kernel.shmmni = 4096
    kernel.shmall = 2097152
    kernel.sem = 250 32000 100 128
    fs.file-max = 102696
    net.ipv4.ip_local_port_range = 1024 65000
    net.core.rmem_default = 4194304
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 262144
    Review the output from the above command and verify that the values are correct. If the values are not correct, edit the /etc/sysctl.conf to correct the values, re-run the sysctl -p command and verify the values.


    Configuring Shell Limits for the oracle User

    To improve the performance of the software on Linux systems, Oracle recommends you increase the following shell limits for the oracle user:

    Shell Limit Item in limits.conf Hard Limit
    Maximum number of open file descriptors nofile 65536
    Maximum number of processes available to a single user nproc 16384

    To make these changes, run the following as root:

    cat >> /etc/security/limits.conf <<EOF
    oracle soft nproc 2047
    oracle hard nproc 16384
    oracle soft nofile 1024
    oracle hard nofile 65536
    EOF
    
    cat >> /etc/pam.d/login <<EOF
    # Added for Oracle Shell Limits
    session    required     /lib/security/pam_limits.so
    session    required     pam_limits.so
    EOF
    Update the default shell startup file for the "oracle" UNIX account.

    • For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file by running the following command:
      cat >> /etc/profile <<EOF
      if [ \$USER = "oracle" ]; then 
          if [ \$SHELL = "/bin/ksh" ]; then
              ulimit -p 16384
              ulimit -n 65536
          else
              ulimit -u 16384 -n 65536
          fi
          umask 022
      fi
      EOF
    • For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file by running the following command:
      cat >> /etc/csh.login <<EOF
      if ( \$USER == "oracle" ) then
          limit maxproc 16384
          limit descriptors 65536
      endif
      EOF


    Check and Turn off UDP ICMP Rejections

    During the Linux installation process, I indicated to not configure the firewall option. (By default the option to configure a firewall is selected by the installer.) I like to do a double-check that the firewall option is not configured and to ensure udp ICMP filtering is turned off.

    1. Check to ensure that the firewall option is turned off. If the firewall option is stopped (like it is in my example below) you do not have to proceed with the following steps.
      # /etc/rc.d/init.d/iptables status
      Firewall is stopped.

    2. If the firewall option is operating you will need to first manually disable UDP ICMP rejections:
      # /etc/rc.d/init.d/iptables stop
      
      Flushing firewall rules: [  OK  ]
      Setting chains to policy ACCEPT: filter [  OK  ]
      Unloading iptables modules: [  OK  ]

    3. Then, to turn UDP ICMP rejections off for next server reboot (which should always be turned off):
      # chkconfig iptables off 



    Network Configuration

    During the Linux operating system install we already configured the IP address and host name for the database node. We now need to configure the /etc/hosts file.

    Note that the Oracle database server should have a static IP address configured for the public network (eth0 for this article). Do not use DHCP naming for the public IP address; you need a static IP address!

    Confirm the Node Name is Not Listed in Loopback Address

    Ensure that the node name (vmlinux2) is not included for the loopback address in the /etc/hosts file. If the machine name is listed in the in the loopback address entry as below:
        127.0.0.1        vmlinux2 localhost.localdomain localhost
    it should be removed as shown below:
        127.0.0.1        localhost.localdomain localhost


    Remove IPv6 Entries

    Remove any entry that has to do with IPv6 (for example, ::1 localhost6.localdomain6 localhost6).


    Sample /etc/hosts File

    The following is an example /etc/hosts file I used for this article:

    Sample /etc/hosts File
    # Do not remove the following line, or various programs
    # that require network functionality will fail.
    127.0.0.1       localhost.localdomain localhost
    #
    192.168.1.102   alex              alex.idevelopment.info
    192.168.1.105   bartman           bartman.idevelopment.info
    192.168.1.120   cartman           cartman.idevelopment.info
    192.168.1.106   melody            melody.idevelopment.info
    192.168.1.190   george            george.idevelopment.info
    #
    192.168.1.1     router            router.idevelopment.info
    192.168.1.245   accesspoint       accesspoint.idevelopment.info
    #
    # ------------- [  LINUX  ] -------------------------------------
    192.168.1.111   vmlinux1          vmlinux1.idevelopment.info
    192.168.1.112   vmlinux2          vmlinux2.idevelopment.info
    192.168.1.131   vmlinux3          vmlinux3.idevelopment.info
    192.168.1.132   vmlinux4          vmlinux4.idevelopment.info



    Create "oracle" User and Directories

    This section covers the steps required to create the UNIX groups (oinstall, dba, and oper) and user (oracle) that will be used to install the Oracle Database 11g software.

    The following O/S groups will be created:

    Description Oracle Privilege Oracle Group Name UNIX Group name
    Oracle Inventory and Software Owner     oinstall
    Database Administrator SYSDBA OSDBA dba
    Database Operator SYSOPER OSOPER oper
    ASM Administrator SYSASM OSASM asm
    OSDBA Group for ASM     asmdba

    Note that members of the UNIX group oinstall are considered the "owners" of the Oracle software. Members of the dba group can administer Oracle databases, for example starting up and shutting down databases. New to Oracle 11g is the SYSASM privilege that is specifically intended for performing ASM administration tasks. Using the SYSASM privilege instead of the SYSDBA privilege provides a clearer division of responsibility between ASM administration and database administration. OSASM is a new operating system group that is used exclusively for ASM. Members of the OSASM group can connect as SYSASM using operating system authentication and have full access to ASM. The final group (asmdba) is the OSDBA Group for ASM. You must create an OSDBA group for ASM to provide access to the ASM instance. This is necessary if OSASM and OSDBA are different groups. In this article, we are creating the oracle user account to have all responsibilities!

      Note that although we will be creating the ASM O/S group and granting it to the oracle user account, this article does not make use of ASM. Any mention of ASM in this article is for documentation purposes only!

    The UNIX group oinstall is used by organizations that have separate teams responsible for maintaining the Oracle software and the database(s). In situations like this, the group membership prevents unauthorized access to the database by personal who only maintain the Oracle software. At the same time, it also prevents database administrators from making modifications to the database software and the installation's Inventory directory.

    If you intend to use the oinstall group during the installation process, then you must set the default group to the one that will be maintaining the database before creating the database or permission problems will arise. In this article, we will be taking care of this issue when we install the database software.

    The thing to keep in mind is that the UNIX oinstall group (as well as oper, and asm) is completely optional and is not required if the same individuals will be maintaining the Oracle software, database, and ASM (if used). Some consider it unnecessary work which adds another layer of complexity to the maintenance of the installation. In this type of scenario, all that would be required is the UNIX group dba (and assigning dba as the primary UNIX group for the "oracle" user account).

    This article (along with other Oracle installation guides on this site) adhere to the Optimal Flexible Architecture (OFA) and create both the oinstall and dba UNIX groups.

      This guide adheres to the Optimal Flexible Architecture (OFA) for naming conventions used in creating the directory structure.


    Create Group and User for Oracle

    Lets start this section by creating the UNIX O/S groups and oracle user account:

    # groupadd -g 501 oinstall
    # groupadd -g 502 dba
    # groupadd -g 503 oper
    # groupadd -g 504 asm
    # groupadd -g 506 asmdba
    # useradd -m -u 501 -g oinstall -G dba,oper,asm -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle
    # id oracle
    uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asm)
    Set the password for the oracle account:
    # passwd oracle
    Changing password for user oracle.
    New UNIX password: xxxxxxxxxxx
    Retype new UNIX password: xxxxxxxxxxx
    passwd: all authentication tokens updated successfully.


    Verify That the User nobody Exists

    Before installing the Oracle software, complete the following procedure to verify that the user nobody exists on the system:

    1. To determine if the user exists, enter the following command:
      # id nobody
      uid=99(nobody) gid=99(nobody) groups=99(nobody)
      If this command displays information about the nobody user, then you do not have to create that user.

    2. If the user nobody does not exist, then enter the following command to create it:
      # /usr/sbin/useradd nobody


    Create the Oracle Base Directory

    The next step is to create a new directory that will be used to store the Oracle Database software. When configuring the oracle user's environment (later in this section) we will be assigning the location of this directory to the $ORACLE_BASE environment variable.

    After the directory is created, you must then specify the correct owner, group, and permissions for it.

    # mkdir -p /u01/app/oracle
    # chown -R oracle:oinstall /u01/app
    # chmod -R 775 /u01/app

    At the end of this procedure, you will have the following:

    • /u01 owned by root.
    • /u01/app owned by oracle:oinstall with 775 permissions. This ownership and permissions enable the OUI to create the oraInventory directory, in the path /u01/app/oraInventory.
    • /u01/app/oracle owned by oracle:oinstall with 775 permissions.


    Create Directory for Database Files

    Let's now create the directory that will be used to store the Oracle database files and backup files (on the /u02 file system):
    # mkdir -p /u02/oradata
    # chown oracle:oinstall /u02/oradata
    # chmod 775 /u02/oradata
    
    # mkdir -p /u02/flash_recovery_area
    # chown oracle:oinstall /u02/flash_recovery_area
    # chmod 775 /u02/flash_recovery_area


    Create Login Script for oracle User Account

    To ensure that the environment is setup correctly for the "oracle" UNIX user account, use the following .bash_profile:

    Login to the database server as the oracle user account:

    # su - oracle
    .bash_profile for Oracle User
    # .bash_profile
    
    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
          . ~/.bashrc
    fi
    
    alias ls="ls -FA"
    alias s="screen -DRRS iPad -t iPad"
    
    export JAVA_HOME=/usr/local/java
    
    # User specific environment and startup programs
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
    export ORACLE_PATH=$ORACLE_BASE/dba_scripts/sql:.:$ORACLE_HOME/rdbms/admin
    export ORACLE_SID=TESTDB
    
    export PATH=.:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
    export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
    export PATH=${PATH}:$ORACLE_BASE/dba_scripts/bin
    export ORACLE_TERM=xterm
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ORA_NLS10=$ORACLE_HOME/nls/data
    export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
    export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc6.jar
    export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
    export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
    export THREADS_FLAG=native
    export TEMP=/tmp
    export TMPDIR=/tmp



    Download the Oracle Database 11g Release 1 Software

    The next logical step is to install Oracle Database 11g Release 1 (11.1.0.6.0) and optionally the Oracle Database 11g Examples for Linux software. However, we must first download and extract the required Oracle software packages from the Oracle Technology Network (OTN).

      If you do not currently have an account with Oracle OTN, you will need to create one. This is a FREE account!

    Oracle offers a development and testing license free of charge. No support, however, is provided and the license does not permit production use. A full description of the license agreement is available on OTN.

    In this section, we will be downloading and extracting the required software from Oracle to the database server. Login to the database server as the "oracle" user account. In this article, I will be downloading the required Oracle software to vmlinux2 and saving it to "~oracle/orainstall".

    Oracle Database 11g Release 1 (11.1.0.6.0) for Linux

    Download the Oracle Database 11g Release 1 (11.1.0.6.0) Software for Linux.

    32-bit (x86) Installations

      Oracle Database 11g Release 1 (11.1.0.6.0) for Linux x86
    64-bit (x86_64) Installations
      Oracle Database 11g Release 1 (11.1.0.6.0) for Linux x86-64


    Oracle Database 11g Examples (formerly Companion) for Linux

    Next, we should download the Oracle Database 11g Examples software. This can be downloaded from the same page used to download the Oracle Database software.

    32-bit (x86) Installations

      Oracle Database 11g Examples (formerly Companion) for Linux x86
    64-bit (x86_64) Installations
      Oracle Database 11g Examples (formerly Companion) for Linux x86-64

    As the "oracle" user account, extract the two packages you downloaded to a temporary directory. In this example, I will extract the 32-bit Oracle software to "~oracle/orainstall".

    Extract the Oracle Database 11g Software:

    # su - oracle
    $ mkdir -p ~oracle/orainstall
    $ cd ~oracle/orainstall
    $ unzip linux_11gR1_database.zip

    Extract the Oracle Database 11g Examples Software:

    $ cd ~oracle/orainstall
    $ unzip linux_11gR1_examples.zip



    Pre-Installation Tasks

    All pre-installation tasks have already been performed to successfully install and configure the Oracle Database 11g software.



    Install the Oracle Database 11g Release 1 Software

    We are now ready to install the Oracle Database 11g Release 1 software. This section will provide the steps necessary to configure the database server to successfully run the Oracle Universal Installer (OUI).

      For the purpose of this example, we will forgo the "Create Database" option when installing the Oracle Database software. We will, instead, create the database using the Database Configuration Assistant (DBCA) after all of the software and patches have been installed.

    X Windows Server Requirements

    Beginning with Oracle version 8i, the Oracle Universal Installer (OUI) is a Java application and uses a JRE (Java runtime Environment) shipped on the media. Other Java applications that utilize the JRE are the Database Configuration Assistant and the Oracle Net Assistant. The JRE shipped by Oracle is the only one supported to run with these applications. Installations can no longer be performed using character mode.

      You must install the Oracle database software from an X windows workstation, an X terminal, or a PC or other system with X server software installed.

    Before starting the Oracle Universal Installer, you should first verify you are logged onto the server you will be running the installer from (i.e. vmlinux2) then run the xhost + command as root from the console to allow X Server connections.

    Next, login as the oracle user account. If you are using a remote client to connect to the node performing the installation (SSH or Telnet to vmlinux2 from a workstation configured with an X Server), you will need to set the DISPLAY variable to point to your local workstation:

    # hostname
    vmlinux2
    
    # xhost +
    access control disabled, clients can connect from any host
    
    # su - oracle
    
    $ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
    $ # NODE PERFORMING THE INSTALL
    $ DISPLAY=<your local workstation>:0.0
    $ export DISPLAY

      If you are using Hummingbird's Exceed X-Windows emulator while installing and using Oracle 11g, set the window manager to run in "Native" mode so that Microsoft windows functions as the window manager. See your Exceed documentation for instructions on configuring the window manager.


    Install the Oracle Database 11g Release 1 Software

    Install the Oracle Database 11g Release 1 software as follows:

    $ cd ~oracle
    $ /home/oracle/orainstall/database/runInstaller

    Oracle Database 11g Software Installation Screen Responses
    Screen Name Response
    Select Installation Method Select the Advanced Method option and click Next to start the installation process.
    Specify Inventory directory
    and credentials
    Accept the default values:
       Inventory directory: /u01/app/oraInventory
       Operating System group name: oinstall
    Select Installation Type Select the type of installation to perform (Enterprise Edition, Standard Edition, or Custom). I selected the Custom option for this example given I wanted to install Oracle Label Security and other components not available with the Enterprise or Standard Edition installation option.
    Specify Home Details Set the Oracle Base, Software Location Name and Software Location Path as follows:

       Install Location
       Oracle Base: /u01/app/oracle

       Software Location
       Name: OraDb11g_home1
       Path: /u01/app/oracle/product/11.1.0/db_1

    Product-Specific
    Prerequisite Checks
    The installer will run through a series of checks to determine if the node meets the minimum requirements for installing and configuring the Oracle database software. If any of the checks fail, you will need to manually verify the check that failed by clicking on the checkbox.

    For my installation, all checks passed with no problems.

    It is possible to receive an error about the available swap space not meeting its minimum requirements:

    Checking available swap space requirements...
    Expected result: 3036MB
    Actual Result: 1983MB

    In most cases, you will have the minimum required swap space (as shown above) and this can be safely ignored. Simply click the check-box for "Checking available swap space requirements..." and click Next to continue.

    Available Product Components
    (Custom Database Installs Only)
    If you selected the Custom installation type, the next screen (Available Product Components) allows you to select from all available components. Select the appropriate components for your environment and click Next to continue.
    Privileged Operating
    System Groups
    Select the UNIX groups that will be used for each of the Oracle group names as follows:

       Database Administrator (OSDBA) Group: dba
       Database Operator (OSOPER) Group: oper
       ASM Administrator (OSASM) Group: asm

    Create Database Select the option to Install database Software only.

    Remember that we will create the database as a separate step using DBCA.

    Oracle Configuration
    Manager Registration
    I kept the default option to not enable Oracle Configuration Manager.
    Summary Click Install to start the installation!
    Root Script Window - Run root.sh After the installation has completed, you will be prompted to run the orainstRoot.sh and root.sh script. Open a new console window as the "root" user account.

    Navigate to the /u01/app/oraInventory directory and run orainstRoot.sh.

    NOTE: After executing the orainstRoot.sh script, verify the permissions of the file "/etc/oraInst.loc" are 644 (-rw-r--r--) and owned by root:root. Problems can occur during the installation of Oracle if the oracle user account does not have read permissions to this file - (the location of the oraInventory directory cannot be determined). If the permissions to /etc/oraInst.loc are not set correctly, it is possible you didn't run orainstRoot.sh before running root.sh. Also, the umask setting may be off - it should be 0022.


    Navigate to the /u01/app/oracle/product/11.1.0/db_1 directory and run root.sh accepting all default values.

    After running the root.sh script go back to the OUI and acknowledge the "Execute Configuration scripts" dialog window.

    End of installation The last screen of a successful Oracle Database 11g Release 1 installation is the "End of Installation" screen. At the end of the installation, exit from the OUI.



    Install the Oracle Database 11g Examples (formerly Companion) Software

    After successfully installing the Oracle Database software, the next step is to install the Oracle Database 11g Examples (11.1.0.6.0). Please keep in mind that this is an optional step.

    Login as the oracle User Account and Set DISPLAY (if necessary)

    As discussed in the previous section, (Install Oracle Database 11g Software), the terminal shell environment needs to run an X Windows application as the "oracle" user account. Note that you can utilize the same terminal shell session used in the previous section which in this case, you do not have to take any of the actions described below with regards to setting the DISPLAY variable:
    # su - oracle
    
    $ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
    $ # NODE PERFORMING THE INSTALL
    $ DISPLAY=<your local workstation>:0.0
    $ export DISPLAY


    Oracle Database 11g Examples Software

    Install the Oracle Database 11g Examples Software Software as follows:
    $ cd ~oracle
    $ /home/oracle/orainstall/examples/runInstaller

    Oracle Database 11g Examples Installation Screen Responses
    Screen Name Response
    Welcome Screen Click Next
    Specify Home Details Set the destination for the ORACLE_HOME Name and Path to that of the previous Oracle11g Database software install as follows:
       Name: OraDb11g_home1
       Path: /u01/app/oracle/product/11.1.0/db_1
    Product-Specific
    Prerequisite Checks
    The installer will run through a series of checks to determine if the node meets the minimum requirements for installing and configuring the Oracle Examples CD. If any of the checks fail, you will need to manually verify the check that failed by clicking on the checkbox. For my installation, all checks passed with no problems.

    Click Next to continue.

    Summary On the Summary screen, click Install to start the installation!
    End of installation The last screen of a successful Oracle Database 11g Examples installation is the "End of Installation" screen. At the end of the installation, exit from the OUI.



    Apply the Database Patchset

    At the time of this writing, the latest patch set for Oracle Database 11g Release 1 is (11.1.0.7). While installing the latest database patch set is optional, it is highly recommended by Oracle Corporation in order to take advantage of the latest bug fixes and as well as possible new features.

      Oracle Database patch sets are available from My Oracle Support (formerly Metalink). If you do not have a My Oracle Support account, you can register yourself. A successful registration, however, will require a valid CPU Support Identification Number (CSI Number). Oracle support entitles you to phone, e-mail, and web-based support, as well as patches and no-cost upgrade to future releases of the same product.

    See http://myoraclesupport.oracle.com for more details.

    Download the Oracle Database 11g Release 1 (11.1.0.7.0) Patch Set for Linux from My Oracle Support.

    11.1.0.7.0 Patch Set For Oracle Database Server
    Patchset Platform Last Updated File Name Size
    6890831 Linux x86 18-SEP-2008 p6890831_111070_Linux-x86.zip 1.5G
    6890831 Linux x86-64 18-SEP-2008 p6890831_111070_Linux-x86-64.zip 1.5G

    The terminal shell environment needs to run an X Windows application as the "oracle" user account. Note that you can utilize the same terminal shell session used in the previous section which in this case, you do not have to take any of the actions described below with regards to setting the DISPLAY variable:

    # su - oracle
    
    $ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
    $ # NODE PERFORMING THE INSTALL
    $ DISPLAY=<your local workstation>:0.0
    $ export DISPLAY

    Extract and install the Oracle Database 11g Release 1 (11.1.0.7.0) Patch Set for Linux.

    $ unzip p6890831_111070_Linux-x86.zip
    $ cd Disk1
    $ ./runInstaller

    Oracle Database 11g (11.1.0.7.0) Patch Set Installation Screen Responses
    Screen Name Response
    Welcome Screen Click Next
    Specify Home Details Set the destination for the ORACLE_HOME Name and Path to that of the previous Oracle11g Database software install as follows:
       Name: OraDb11g_home1
       Path: /u01/app/oracle/product/11.1.0/db_1
    Metalink Account Information Provide information regarding your "My Oracle Support" account (formerly Metalink). Entering your account information is an optional task.
    Product-Specific
    Prerequisite Checks
    The installer will check for any Oracle Home incompatibilities. Verify that all required prerequisite checks were successful.

    Click Next to continue.

    Summary On the Summary screen, click Install to start the installation!
    End of installation The last screen of a successful Oracle Database 11g Patch Set 1 installation is the "End of Installation" screen. At the end of the installation, exit from the OUI.



    Configure Oracle Networking

    The Database Configuration Assistant (DBCA) requires the Oracle TNS Listener process to be configured and running on the database server before it can create the database.

    Bring up the Network Configuration Assistant (NETCA) and run through the process of creating a new TNS listener process and to also configure the node for local access.

    The terminal shell environment needs to run an X Windows application as the "oracle" user account. Note that you can utilize the same terminal shell session used in the previous section which in this case, you do not have to take any of the actions described below with regards to setting the DISPLAY variable:

    # su - oracle
    
    $ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
    $ # NODE PERFORMING THE INSTALL
    $ DISPLAY=<your local workstation>:0.0
    $ export DISPLAY

    To start the NETCA, run the following:

    $ netca &
    Oracle Network Configuration Installation Screen Responses
    Screen Name Response
    Welcome Select Listener configuration.
    Listener Configuration
    (Next 6 Screens)
    The following screens are now like any other normal listener configuration. You can simply accept the default parameters for the next six screens:
       What do you want to do: Add
       Listener name: LISTENER
       Selected protocols: TCP
       Port number: 1521
       Configure another listener: No
       Listener configuration complete! [ Next ]
    You will be returned to this Welcome (Type of Configuration) Screen.
    Welcome Select Naming Methods configuration.
    Naming Methods Configuration The following screens are:
       Selected Naming Methods: Local Naming
       Naming Methods configuration complete! [ Next ]
    You will be returned to this Welcome (Type of Configuration) Screen.
    Welcome Click Finish to exit the NETCA.

    The Oracle TNS listener process should now be running:

    $ ps -ef | grep lsnr | grep -v 'grep' | awk '{print $9}'
    LISTENER



    Create the Oracle Database

    We will be using the Oracle Database Configuration Assistant (DBCA) to create a database / instance named TESTDB.

    Before executing the Database Configuration Assistant, make sure that $ORACLE_HOME and $PATH are set appropriately for the $ORACLE_BASE/product/11.1.0/db_1 environment.

    You should also verify that the Oracle TNS listener service is running before attempting to start the database creation process.

    Login as the oracle User Account and Set DISPLAY (if necessary)

    As discussed in the previous section, the terminal shell environment needs to run an X Windows application as the "oracle" user account. Note that you can utilize the same terminal shell session used in the previous section which in this case, you do not have to take any of the actions described below with regards to setting the DISPLAY variable:
    # su - oracle
    
    $ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
    $ # NODE PERFORMING THE INSTALL
    $ DISPLAY=<your local workstation>:0.0
    $ export DISPLAY


    Create the Database

    To start the database creation process, run the following:

    $ dbca &
    Screen Name Response
    Welcome Screen Click Next
    Operations Select Create a Database.
    Database Templates Select Custom Database
    Database Identification Select:
       Global Database Name: TESTDB.IDEVELOPMENT.INFO
       SID: TESTDB

      I used IDEVELOPMENT.INFO for the database domain. You may use any domain. Keep in mind that this domain does not have to be a valid DNS domain.

    Management Option Leave the default options here which is to Configure Enterprise Manager / Configure Database Control for local management
    Database Credentials I selected to Use the Same Administrative Password for All Accounts. Enter the password (twice) and make sure the password does not start with a digit number.
    Storage Options For this article, we will select to use File System.
    Database File Locations Select the option to use Use Oracle-Managed Files:
       Database Area: /u02/oradata
    Recovery Configuration Check the option for Specify Flash Recovery Area. For this example, I will create a Flash Recovery Area of 10GB.
       Flash Recovery Area: /u02/flash_recovery_area
       Flash Recovery Area Size: 10240
    Database Content I left all of the Database Components (and destination tablespaces) set to their default value, although it is perfectly OK to select the Sample Schemas. This option is available since we installed the Oracle Database 11g Examples software.
    Initialization Parameters Change any parameters for your environment. I left them all at their default settings.
    Security Settings Oracle highly recommends using the new enhanced security settings for Oracle 11g. For the purpose of this article, I choose the default setting to Keep the enhanced 11g default security settings. Note that these security settings can be modified after the database creation using DBCA.
    Automatic Maintenance Tasks I kept the default to Enable automatic maintenance tasks.
    Database Storage Change any parameters for your environment. I left them all at their default settings.
    Creation Options Keep the default option Create Database selected. I also always select to Generate Database Creation Scripts. Click Finish to start the database creation process. After acknowledging the database creation report and script generation dialog, the database creation will start.

    Click OK on the "Summary" screen.

    End of Database Creation At the end of the database creation, exit from the DBCA.

    When the Oracle Database Configuration Assistant has completed, you will have a fully functional Oracle database running!

    If you selected to configure Enterprise Manager during the database creation process, the OUI will provide the URL. For example:

    https://vmlinux2:1158/em

      I like to review the log files that were created by the DBCA process. For the purpose of the example in this article, my log files were created in "/u01/app/oracle/cfgtoollogs/dbca/TESTDB".

    Also note that the "Management Repository" will be placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption for my configuration was placed in the file: /u01/app/oracle/product/11.1.0/db_1/vmlinux2_TESTDB/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost



    Post-Installation Tasks

    This section contains several tasks that can be applied to your new Oracle 11g environment in order to enhance availability as well as database management.

    Re-compile Invalid Objects

    Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
    $ id -a
    uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asm)
    
    $ sqlplus "/ as sysdba"
    SQL> @?/rdbms/admin/utlrp.sql


    Enabling Archive Logs - (Optional)

    Oracle tracks and logs all changes to database blocks in online redolog files. An Oracle instance (i.e. TESTDB) will have its own set of online redolog files known as a thread. The Oracle instance (or thread) will use its group of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one. If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs (or online redolog groups).

    As already mentioned, Oracle writes to its online redolog files in a circular manner. When the current online redolog fills, Oracle will switch to the next one. To facilitate media recovery, Oracle allows the DBA to put the database into "Archive Log Mode" which makes a copy of the online redolog after it fills (and before it gets reused). This is a process known as archiving.

    The Database Configuration Assistant (DBCA) allows users to configure a new database to be in archive log mode, however most DBA's opt to bypass this option during initial database creation. In cases like this where the database is in no archive log mode, it is a simple task to put the database into archive log mode. Note however that this will require a short database outage. Use the following tasks to put your new database into archive log mode.

    1. Login to the database server as the "oracle" user account and shutdown the database:
      $ id -a
      uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asm)
      
      $ sqlplus "/ as sysdba"
      SQL> shutdown immediate

    2. Next, MOUNT the database:
      SQL> startup mount

    3. Enable archiving:
      SQL> alter database archivelog;

    4. Open the database for read/write which is now in archivelog mode:
      SQL> alter database open;

    5. Login to the database and verify Archive Log Mode is enabled:
      SQL> archive log list
      Database log mode              Archive Mode
      Automatic archival             Enabled
      Archive destination            USE_DB_RECOVERY_FILE_DEST
      Oldest online log sequence     52
      Next log sequence to archive   54
      Current log sequence           54


    Download and Install Custom Oracle Database Scripts - (Optional)

    DBA's rely on Oracle's data dictionary views and dynamic performance views in order to support and better manage their databases. Although these views provide a simple and easy mechanism to query critical information regarding the database, it helps to have a collection of accurate and readily available SQL scripts to query these views.

    In this section you will download and install a collection of Oracle DBA scripts that can be used to manage many aspects of your database including space management, performance, backups, security, and session management. The Oracle DBA scripts archive can be downloaded using the following link http://www.idevelopment.info/data/Oracle/DBA_scripts/dba_scripts_archive_Oracle.zip. As the oracle user account, download the dba_scripts_archive_Oracle.zip archive to the $ORACLE_BASE directory. For the purpose of this example, the dba_scripts_archive_Oracle.zip archive will be copied to /u01/app/oracle. Next, unzip the archive file to the $ORACLE_BASE directory.

    For example, perform the following as the oracle user account:

    $ mv dba_scripts_archive_Oracle.zip /u01/app/oracle
    $ cd /u01/app/oracle
    $ unzip dba_scripts_archive_Oracle.zip
    The final step is to verify (or set) the appropriate environment variable for the current UNIX shell to ensure the Oracle SQL scripts can be run from SQL*Plus while in any directory. For UNIX verify the following environment variable is set and included in your login shell script:
    ORACLE_PATH=$ORACLE_BASE/dba_scripts/sql:.:$ORACLE_HOME/rdbms/admin
    export ORACLE_PATH

      Note that the ORACLE_PATH environment variable should already be set in the .bash_profile login script that was created in the section Create Login Script for oracle User Account.

    Now that the Oracle DBA scripts have been unzipped and the UNIX environment variable ($ORACLE_PATH) has been set to the appropriate directory, you should now be able to run any of the SQL scripts in your $ORACLE_BASE/dba_scripts/sql while logged into SQL*Plus. For example, to query tablespace information while logged into the Oracle database as a DBA user:

    SQL> @dba_tablespaces
    
    Status    Tablespace Name TS Type      Ext. Mgt.  Seg. Mgt.    Tablespace Size    Used (in bytes) Pct. Used
    --------- --------------- ------------ ---------- --------- ------------------ ------------------ ---------
    ONLINE    SYSAUX          PERMANENT    LOCAL      AUTO             602,865,664        574,357,504        95
    ONLINE    UNDOTBS1        UNDO         LOCAL      MANUAL           277,872,640        274,464,768        99
    ONLINE    USERS           PERMANENT    LOCAL      AUTO               5,242,880             65,536         1
    ONLINE    SYSTEM          PERMANENT    LOCAL      MANUAL           723,517,440        717,029,376        99
    ONLINE    TEMP            TEMPORARY    LOCAL      MANUAL            40,894,464         39,845,888        97
                                                                ------------------ ------------------ ---------
    avg                                                                                                      78
    sum                                                              1,650,393,088      1,605,763,072
    To obtain a list of all available Oracle DBA scripts while logged into SQL*Plus, run the help.sql script:
    SQL> @help.sql
    
    ========================================
    Automatic Shared Memory Management
    ========================================
    asmm_components.sql
    
    ========================================
    Automatic Storage Management
    ========================================
    asm_alias.sql
    asm_clients.sql
    asm_diskgroups.sql
    asm_disks.sql
    asm_disks_perf.sql
    asm_drop_files.sql
    asm_files.sql
    asm_files2.sql
    asm_templates.sql
    
    < --- SNIP --- >
    
    perf_top_sql_by_buffer_gets.sql
    perf_top_sql_by_disk_reads.sql
    
    ========================================
    Workspace Manager
    ========================================
    wm_create_workspace.sql
    wm_disable_versioning.sql
    wm_enable_versioning.sql
    wm_freeze_workspace.sql
    wm_get_workspace.sql
    wm_goto_workspace.sql
    wm_merge_workspace.sql
    wm_refresh_workspace.sql
    wm_remove_workspace.sql
    wm_unfreeze_workspace.sql
    wm_workspaces.sql



    Creating / Altering Tablespaces

    When creating the Oracle database, we left all tablespaces set to their default size. If you are using a large set of drives for database storage, you may want to make a sizable testing database.

    Below are several optional SQL commands for modifying and creating all tablespaces for the test database.

    NOTE: Please keep in mind that the database file names (OMF files) being listed in these examples may differ from what the Oracle Database Configuration Assistant (DBCA) creates for your environment. When working through this section, substitute the data file names that were created in your environment where appropriate. The following query can be used to determine the file names for your environment:

    SQL> select tablespace_name, file_name
      2  from dba_data_files
      3  union
      4  select tablespace_name, file_name
      5  from dba_temp_files;
    
    TABLESPACE_NAME     FILE_NAME
    --------------- ---------------------------------------------------------
    SYSAUX          /u02/oradata/TESTDB/datafile/o1_mf_sysaux_3ry6vhwc_.dbf
    SYSTEM          /u02/oradata/TESTDB/datafile/o1_mf_system_3ry6tr3d_.dbf
    TEMP            /u02/oradata/TESTDB/datafile/o1_mf_temp_3ry6w1tw_.tmp
    UNDOTBS1        /u02/oradata/TESTDB/datafile/o1_mf_undotbs1_3ry6vq9y_.dbf
    USERS           /u02/oradata/TESTDB/datafile/o1_mf_users_3ry6xn9w_.dbf

    $ sqlplus "/ as sysdba"
    
    SQL> create user scott identified by tiger default tablespace users;
    
    SQL> grant dba, resource, connect to scott;
    
    SQL> alter database datafile '/u02/oradata/TESTDB/datafile/o1_mf_users_3ry6xn9w_.dbf' resize 1024m;
    
    SQL> alter tablespace users add datafile size 1024m autoextend off;
    
    SQL> create tablespace indx datafile size 1024m
      2  autoextend on next 100m maxsize unlimited
      3  extent management local autoallocate
      4  segment space management auto;
    
    SQL> -- SYSTEM Tablespace
    SQL> alter database datafile '/u02/oradata/TESTDB/datafile/o1_mf_system_3ry6tr3d_.dbf' resize 1024m;
    
    SQL> -- SYSAUX Tablespace
    SQL> alter database datafile '/u02/oradata/TESTDB/datafile/o1_mf_sysaux_3ry6vhwc_.dbf' resize 1024m;
    
    SQL> -- UNDO Tablespace
    SQL> alter database datafile '/u02/oradata/TESTDB/datafile/o1_mf_undotbs1_3ry6vq9y_.dbf' resize 1024m;
    
    SQL> -- Temporary Tablespace
    SQL> alter database tempfile '/u02/oradata/TESTDB/datafile/o1_mf_temp_3ry6w1tw_.tmp' resize 1024m;

    Here is a snapshot of the tablespaces I have defined for my test database environment:

    SQL> @dba_tablespaces
    
    Status    Tablespace Name TS Type      Ext. Mgt.  Seg. Mgt.    Tablespace Size    Used (in bytes) Pct. Used
    --------- --------------- ------------ ---------- --------- ------------------ ------------------ ---------
    ONLINE    SYSAUX          PERMANENT    LOCAL      AUTO           1,073,741,824        574,357,504        53
    ONLINE    UNDOTBS1        UNDO         LOCAL      MANUAL         1,073,741,824        274,464,768        26
    ONLINE    USERS           PERMANENT    LOCAL      AUTO           2,147,483,648            131,072         0
    ONLINE    SYSTEM          PERMANENT    LOCAL      MANUAL         1,073,741,824        717,029,376        67
    ONLINE    INDX            PERMANENT    LOCAL      AUTO           1,073,741,824             65,536         0
    ONLINE    TEMP            TEMPORARY    LOCAL      MANUAL         1,073,741,824         39,845,888         4
                                                                ------------------ ------------------ ---------
    avg                                                                                                      25
    sum                                                              7,516,192,768      1,605,894,144
    
    6 rows selected.



    Setting up Automatic Database Starting and Stopping

    This section contains the recommended method defined by Oracle for automating database startup and shutdown of Oracle10g and Oracle11g instances.

    Automatic Database Startup/Shutdown in Oracle11g

    Once the instance is created, edit the /etc/oratab file setting the restart flag (the last field) to "Y" for the instance you want to automatically startup and shutdown. For the purpose of this example, the only instance defined in the /etc/oratab file is TESTDB:

    /etc/oratab
    ...
    TESTDB:/u01/app/oracle/product/11.1.0/db_1:Y
    ...

    Next, create a text file named /etc/init.d/dbora as the root user, containing the following:

    /etc/init.d/dbora
    #!/bin/sh
    # chkconfig: 345 99 10
    # description: Oracle auto start-stop script.
    #
    # Set ORA_HOME to be equivalent to the $ORACLE_HOME
    # from which you wish to execute dbstart and dbshut;
    #
    # Set ORA_OWNER to the user id of the owner of the 
    # Oracle database in ORA_HOME.
    
    ORA_HOME=/u01/app/oracle/product/11.1.0/db_1
    ORA_OWNER=oracle
    
    if [ ! -f $ORA_HOME/bin/dbstart ]
    then
        echo "Oracle startup: cannot start"
        exit
    fi
    
    case "$1" in
        'start')
            # Start the Oracle databases:
            # The following command assumes that the oracle login 
            # will not prompt the user for any values
            su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
            ;;
        'stop')
            # Stop the Oracle databases:
            # The following command assumes that the oracle login 
            # will not prompt the user for any values
            su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
            ;;
    esac

      Note that the /etc/init.d/dbora script listed above may look a little different from a similar one used for Oracle9i — most notably the omission of the commands to start/stop the Oracle TNS listener process. As of Oracle 10g Release 2 the dbstart script includes the commands to automatically start/stop the listener.

    Use the chmod command to set the privileges to 750:

    # chmod 750 /etc/init.d/dbora

    Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

    # chkconfig --level 345 dbora on
    The relevant instances should now startup/shutdown automatically at system startup/shutdown!


    Final Notes

    The /etc/init.d/dbora script defined in this section uses the "su -" command to run the Oracle scripts dbstart and dbshut:
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
    
    (and...)
    
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
    Starting with Oracle10g, Oracle switched from recommending the "su" command to the "rsh" command of which I completely disagree with. When using the rsh method, the /etc/init.d/dbora script would be defined as follows:

    /etc/init.d/dbora - (rsh method)
    #!/bin/sh
    # chkconfig: 345 99 10
    # description: Oracle auto start-stop script.
    #
    # Change the value of ORACLE_HOME to specify the correct Oracle home
    # directory for your installation.
    
    ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
    #
    # Change the value of ORACLE to the login name of the
    # oracle owner at your site.
    #
    ORACLE=oracle
    
    PATH=${PATH}:$ORACLE_HOME/bin
    HOST=`hostname`
    PLATFORM=`uname`
    export ORACLE_HOME PATH
    #
    if [ ! "$2" = "ORA_DB" ] ; then
       if [ "$PLATFORM" = "HP-UX" ] ; then
          remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"
          exit
       else
          rsh $HOST -l $ORACLE  $0 $1 ORA_DB
          exit
       fi
    fi
    #
    case $1 in
    'start')
            $ORACLE_HOME/bin/dbstart $ORACLE_HOME
            ;;
    'stop')
            $ORACLE_HOME/bin/dbshut $ORACLE_HOME
            ;;
    *)
            echo "usage: $0 {start|stop}"
            exit
            ;;
    esac
    #
    exit

    Note that this method relies on the presence of an RSH server, which requires additional packages and configuration. The RSH server does not get installed by default on many Linux distributions (and for good reason!).

    Given the RSH server (and other r* packages) do not get installed by default, the DBA would need to locate and install them before this method could be used:

    # -- Install the rhs and rsh-server packages from the OS CD/DVD --
    # rpm -Uvh --force rsh-*
    
    # -- Enable rsh and rlogin --
    # chkconfig rsh on
    # chkconfig rlogin on
    # service xinetd reload

    I completely disagree with the recommendation to use rsh and prefer to stick with using the su command method. Furthermore, using the rsh method can be problematic when attempting to use it under Fedora Core 5 and Fedora Core 6 where rsh is deprecated.



    Miscellaneous Options

    This final section of the article contains several miscellaneous options that may be of use to newcomers of Oracle 11g.

    Enterprise Manager - Database Console

    During the database creation section, we asked for DBCA to create the Enterprise Manager Database Console application. In almost all cases, the DBCA will automatically start the OEM Database Console application. To check for the process, type the following:
    $ emctl status dbconsole
    Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
    Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.
    https://vmlinux2:1158/em/console/aboutApplication
    Oracle Enterprise Manager 11g is running.
    ------------------------------------------------------------------
    Logs are generated in directory /u01/app/oracle/product/11.1.0/db_1/vmlinux2_TESTDB/sysman/log
    If you receive something similar to the above output, then OEM Database Console is running. If you need to manually start this application, login as the UNIX oracle user account and type the following:
    $ emctl start dbconsole
    Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
    Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.
    https://vmlinux2:1158/em/console/aboutApplication
    Starting Oracle Enterprise Manager 11g Database Control ........... started.
    ------------------------------------------------------------------
    Logs are generated in directory /u01/app/oracle/product/11.1.0/db_1/vmlinux2_TESTDB/sysman/log
    The OEM DB Console application may take several minutes to start.

    Once the DB Console application is up and running, point your web browser to https://<Database_Server>:1158/em as in the following:

    https://vmlinux2:1158/em
    Login:
    User Name: SYSTEM
    Password: <The password you chose during installation>
    Connect As: Normal

      The emctl start dbconsole / emctl stop dbconsole commands can be placed in the /etc/init.d/dbora script to be started and stopped when the database server is cycled.


    Changing Your Oracle Database Environment

    In some cases, you may have more than one database running on your single database server. It is even possible to be using two different databases running on different releases of the Oracle Database software. (i.e. Oracle 8.1.7, Oracle 9.2.0, 10.2.0, and 11.1.0). For example, you may have two database - ORA920 (using Oracle release 9.2.0) and TESTDB (using Oracle release 11.1.0). Whatever the case may be, you need an efficient way to modify your environment variables to switch between these two databases. This requires you update environment variables like ORACLE_HOME, ORACLE_SID, PATH, LD_LIBRARY_PATH, etc. Although this can be done manually at the command line, it can get old real quick. Instead you can use a single command line script to switch between all database environments declared in your /etc/oratab file.

    If you have been following along with this article, the required scripts for switching your Oracle database environment is already included (in /usr/local/bin). From the command prompt, simply run the following:

    $ . oraenv
    ORACLE_SID = [ORA920] ? TESTDB
    Notice that in the above example, we didn't just run the oraenv script, but rather, we sourced it by using a dot followed by a space and then the script name oraenv. When sourcing the oraenv script, it will prompt you for the ORACLE_SID, (defined in your /etc/oratab file), you want to switch to. In the above example, if an entry exists for TESTDB, then all required environment variables would be reset to access this database.



    About the Author

    Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



    Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

    All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

    I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

    Last modified on
    Monday, 14-Jul-2014 18:04:35 EDT
    Page Count: 117373