Sridhar's oracle page

Friday, March 24, 2006

Installing Oracle 9i on Red Hat Enterprise Linux Advanced Server 4, 3, 2.1, and on Red Hat 9, 8.0, 7.3, 7.2, 7.1 (x86)

Written by Werner Puschitz.

Installing Oracle 9i on Red Hat Enterprise Linux Advanced Server 4, 3, 2.1, and on Red Hat 9, 8.0, 7.3, 7.2, 7.1 (x86)

http://www.puschitz.com/

Here is a summary (HOWTO) how I installed:
Oracle 9iR2 (9.2.0.6.0) Database on Red Hat Advanced Server 4 (x86, kernel 2.6.9-5.EL, glibc-2.3.4-2)
Oracle 9iR2 (9.2.0) Database on Red Hat Advanced Server 3 (x86, kernel 2.4.21-4.EL, glibc 2.3.2-95.3)
Oracle 9iR2 (9.2.0) Database on Red Hat Advanced Server 2.1 (x86, kernel 2.4.9-e.3, glibc 2.2.4-26)
Oracle 9iR2 (9.2.0) Database on Red Hat 9 (x86, kernel kernel-2.4.20-6, glibc 2.3.2-5)
Oracle 9iR2 (9.2.0) Database on Red Hat 8.0 (x86, kernel 2.4.18-18.8.0, glibc 2.2.93-5)
Oracle 9iR2 (9.2.0) Database on Red Hat 7.3 (x86, kernel 2.4.18-3, glibc 2.2.5-34)
Oracle 9iR1 (9.0.1) Database on Red Hat 7.3 (x86, kernel 2.4.18-3, glibc 2.2.5-34)
Oracle 9iR1 (9.0.1) Database on Red Hat 7.2 (x86, kernel 2.4.7-10, glibc 2.2.4-13)
Oracle 9iR1 (9.0.1) Database on Red Hat 7.1 (x86, kernel 2.4.2-2, glibc 2.2.2-10)

People recommended this installation guide also for Red Hat Fedora Core 2.

Validation/Certification:

For Validations/Certifications, check the following links:
Oracle's Certification Matrices
Oracle Products on Red Hat Linux
Red Hat Enterprise Linux Application List

Errors and Problems:
Some of the Oracle errors and problems covered here were only experienced in connection with 9i (9.0.1) and some only with 9iR2 (9.2.0). But since I cannot say for sure that a 9i (9.0.1) installation error will never show up during 9iR2 (9.2.0) installation, I simply kept all errors and problems listed together, see Oracle Installation Errors and Oracle Installation Problems, Important Tips and Hints.

Red Hat Enterprise Linux Advanced Server 3 (RHEL AS 3)
In order to install an Oracle9iR2 database on RH AS 3, the "Oracle9iR2 Patch Set 3 9.2.0.4.0" patchset and some other patches must be applied. Some errors can only be fixed by applying the 9.2.0.4 patchset. For more information, see Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 3.

Red Hat 9:
Red Hat 9 includes the the Native POSIX Thread Library (NPTL) which is an improved implementation of POSIX threads for Linux. But using NPTL will cause several problems for Oracle applications. Note that Oracle9i has not been certified on Red Hat 9!
So to fix this problem, you can set the environment variable LD_ASSUME_KERNEL to 2.4.1, which means that the old "Linuxthreads with floating stacks" implementation will be used. Otherwise the Oracle installer runInstaller will hang, the Database Configuration Assistant dbca won't start etc.; see Oracle Installation Errors for more information. To see where this environment variable can be set, see Set Oracle Environments. For more information on LD_ASSUME_KERNEL, see Red Hat Linux 9 Release Notes.
NOTE: Before you install Oracle9iR2, make sure that you first read the information about the error message "Error in invoking target install of make file /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk" in the Oracle Installation Errors section!

Red Hat 8.0:
The only problem I experienced with Oracle 9iR2 (9.2.0) on Red Hat 8.0 was:
"Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk"
But this does not necessarily mean that you won't see other problems described here. See Oracle Installation Errors for more information.

This article covers the following subjects and steps:

* Documentations
* Downloading and Installing Red Hat Linux 7.1, 7.2, 7.3, 8.0, 9
* Unpacking Downloaded Oracle9i Installation Files and Burning Oracle9i CDs
* Setting Swap Space
* Setting Shared Memory
* Checking /tmp Space
* Sizing Oracle Disk Space
* The "binutils" Issue
* Checking Packages (RPMs)
* JDK
* Creating Oracle User Accounts
* Creating Oracle Directories
* Setting Oracle Environments
* Starting runInstaller
* Running Oracle Installation on RH 7.1, 7.2, 7.3, 8.0, 9, and on RH AS 2.1
* Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 3
Installing Oracle9iR2 on RH AS 3
Patching Oracle9iR2 on RH AS 3
Patching Oracle Intelligent Agent on RH AS 3
* Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 4
Installing Oracle9iR2 on RH AS 4
Patching Oracle9iR2 on RH AS 4
* Startup and Shutdown of the Oracle 9i Database
* Oracle Installation Problems, Tips and Hints
* Oracle Installation Errors

Documentations
Oracle9i Database Documentation for Linux
Tuning and Optimizing Red Hat Linux Advanced Server for Oracle9i Database
Oracle9iR2 on Linux: Performance, Reliability and Manageability Enhancements on Red Hat Linux Advanced Server 2.1
An Overview of Red Hat Advanced Server V2.1 Reliability, Availability, Scalability, and Manageability (RASM) Features
Downloading and Installing Red Hat Linux 7.1, 7.2, 7.3, 8.0, 9
To download Red Hat Linux 7.x, 8.0, 9, check the links at http://www.puschitz.com/LinuxDownload.shtml.
You can find the installation guides for installing Red Hat Linux under Red Hat Linux Manuals.

NOTE: You cannot download Red Hat Linux Advanced Server 2.1, you can only download the source code. If you want to get the binary CDs, you will have to buy it at http://www.redhat.com/software/linux/advanced/.

Installing Software Packages (RPMs)

You don't have to install all RPMs when you want to run an Oracle9i database on Red Hat Linux.

For instance, if you install Red Hat Advanced Server, you are fine when you select the Installation Type "Advanced Server" and when you don't select the Package Group "Software Development". There are only a few other RPMs that are required for installing Oracle9i. These other RPMs are covered in this article.

Or when you install Oracle9i on Red Hat Linux 7.x, 8.0, or 9, you are fine when you select the installation type "Server".
Unpacking Downloaded Oracle9i Installation Files and Burning Oracle9i CDs
Download Oracle9i for Linux from the following web site:
http://otn.oracle.com/software/products/oracle9i/htdocs/linuxsoft.html

Uncompress and unpack downloaded files:

For Oracle9i (9.2.0):

One step procedure (uses less disk space and is faster):
zcat lnx_920_disk1.cpio.gz | cpio -idmv
zcat lnx_920_disk2.cpio.gz | cpio -idmv
zcat lnx_920_disk3.cpio.gz | cpio -idmv
Two step procedure:
# Uncompress
gunzip lnx_920_disk1.cpio.gz lnx_920_disk2.cpio.gz lnx_920_disk3.cpio.gz Linux9i_Disk3.cpio.gz

# Unpack the downloaded files:
cpio -idmv < lnx_920_disk1.cpio
cpio -idmv < lnx_920_disk2.cpio
cpio -idmv < lnx_920_disk3.cpio

For Oracle9i (9.0.1):

One step procedure (uses less disk space and is faster):
zcat Linux9i_Disk1.cpio.gz | cpio -idmv
zcat Linux9i_Disk2.cpio.gz | cpio -idmv
zcat Linux9i_Disk3.cpio.gz | cpio -idmv
Two step procedure:
# Uncompress
gunzip Linux9i_Disk1.cpio.gz Linux9i_Disk2.cpio.gz Linux9i_Disk3.cpio.gz

# Unpack the downloaded files:
cpio -idmv < Linux9i_Disk1.cpio
cpio -idmv < Linux9i_Disk2.cpio
cpio -idmv < Linux9i_Disk3.cpio

Now you should have 3 directories containing installation files:
Disk1
Disk2
Disk3
I executed the following commands when I burned the 3 CDs:
mkisofs -r Disk1 | cdrecord -v --eject dev=0,0,0 speed=15 -
mkisofs -r Disk2 | cdrecord -v --eject dev=0,0,0 speed=15 -
mkisofs -r Disk3 | cdrecord -v --eject dev=0,0,0 speed=15 -
(You can get the dev numbers when you execute cdrecord -scanbus).
Setting Swap Space
In order to perform a typical Oracle 9i installation and to create a simple prototype database, Oracle says that you need a minimum of 512MB of RAM for the Oracle9i (9.0.1) Server, and the amount of disk space (swap space) should be equal to twice the amount of RAM or at least 400 MB, whichever is greater.
I tried to test the limits on an older PC with 256 MB of RAM and with 600 MB of swap space. I was able to install Oracle 9i (9.0.1 & 9.2.0) and Oracle's default database without any problems. But when I used less swap space on this PC (256MB RAM), I was runnig out of memory. So I definitely recommend to use more RAM and/or more swap space as specified in the Oracle installation guide.

NOTE: If you do not have enough swap space or RAM during the Oracle installation, in particular during the database creation, your Oracle server (Linux) will temporarily become unresponsive to any events for several minutes.

For more information on correctly sizing the swap space for your database, see Sizing Swap Space.

To check the memory, run:
grep MemTotal /proc/meminfo
To check the swap space, run:
cat /proc/swaps

You can also add temporary swap space by creating a temporary swap file instead of using a raw device. Here is the procedure:
su - root
dd if=/dev/zero of=tmpswap bs=1k count=900000
chmod 600 tmpswap
mkswap tmpswap
swapon tmpswap
To disable the temporary swap space execute the following commands:
su - root
swapoff tmpswap
rm tmpswap

Setting Shared Memory
For Oracle 9i (9.2.0) installation I had to increase the maximum shared memory size on my Linux server for all Red Hat versions. The Oracle Database Configuration Assistant displayed the following error message on my server:
ORA-27123: unable to attach to shared memory segment.
I temporarely increased the shmmax setting for the kernel by executing the following command:
$ su - root
# cat /proc/sys/kernel/shmmax
33554432
# echo `expr 1024 \* 1024 \* 1024` > /proc/sys/kernel/shmmax
# cat /proc/sys/kernel/shmmax
1073741824
It is recommended to increase the shmmax setting permanently for Oracle. For more information, see Setting Shared Memory.

For more information on optimizing shared memory settings for Oracle databases on Linux, see Setting Shared Memory. These parameters apply to all Red Hat Linux versions. But note that except for the shmmax parameter, these parameter do not need to be changed for installing Oracle on Linux. But you might want to adjust all shared memory settings later to optimize the server for Oracle.
Checking /tmp Space
The Oracle Universal Installer requires up to 400 MB of free space in the /tmp directory.

To check the space in /tmp, run:
$ df /tmp
If you do not have enough space in the /tmp directory, you can temporarily create a tmp directory in another filesystem. Here is how you can do this:
su - root
mkdir //tmp
chown root.root //tmp
chmod 1777 //tmp
export TEMP=/ # used by Oracle
export TMPDIR=/ # used by Linux programs like the linker "ld"
When you are done with your Oracle installation, shutdown Oracle and remove the temporary directory:
su - root
rmdir //tmp
unset TEMP
unset TMPDIR

Sizing Oracle Disk Space
You will need about 2.5 GB for the database software. If you perform a typical database installation and not a customized database installation, then you will need about 3.5 GB of disk space.
The "binutils" Issue
Skip this step for Oracle9iR2.

I did not experience this problem with Oracle 9i (9.2.0), but only with Oracle 9i (9.0.1).

The binutils package that comes with Red Hat 7.1, 7.2, 7.3, and with RedHat 2.1 Advanced Server doesn't work with Oracle 9i (9.0.1) Universal Installer. Here are the options you have for 9.0.1:
• I recommend the following approach:

Wait for the following Oracle installation error:
"Error invoking target install of makefile /u01/app/oracle/product/9.0.1/plsql/lib/ins_plsql.mk"
And fix this problem as described in Oracle Installation Errors.
I recommend this approach since it obviates the need to change binutils.
• I do not recommend the following approach:

Download the following binutil RPM version and downgrade binutil on the Oracle server:
ftp://ftp.redhat.com/pub/redhat/linux/7.0/en/os/i386/RedHat/RPMS/binutils-2.10.0.18-1.i386.rpm
• su - root
rpm -Uvh --force --nodeps binutils-2.10.0.18-1.i386.rpm
When you are done with the Oracle installation, you upgrade your binutil RPM back to the version you had before you downgraded. E.g. on the Red Hat 7.2 server I did:
rpm -Uvh --force --nodeps binutils-2.11.90.0.8-9.i386.rpm
• Here is Oracle's official solution for Oracle 9iR1 or 9iR1 iAS on RedHat 2.1 Advanced Server which I don't like:
http://otn.oracle.com/software/products/oracle9i/files/binutils_readme.html

Checking Packages (RPMs)
You will need some RPM development packages for the Oracle installer to build the Oracle modules, otherwise you will get error messages similar to this one:
Error in invoking target ntcontab.o of makefile
/u01/app/oracle/product/9.2.0/network/lib/ins_net_client.mk

NOTE: Always ensure to use the latest RPM versions!

Packages (RPMs) for RH 7.1, 7.2, and RH AS 2.1:

To see if these development packages are installed on your server, run the following command:
rpm -q gcc cpp compat-libstdc++ glibc-devel kernel-headers binutils
For instance, most of these packages will be missing when you installed RedHat 2.1 Advanced Server and if you did not select the "Software Development" package. For the RedHat 2.1 Advanced Server I executed the following commands to install the missing RPMs from the two CDs:
su - root
rpm -ivh cpp-2.96-108.1.i386.rpm glibc-devel-2.2.4-26.i386.rpm kernel-headers-2.4.9-e.3.i386.rpm gcc-2.96-108.1.i386.rpm binutils-2.11.90.0.8-12.i386.rpm

Packages (RPMs) for RH 7.3, 8.0, and 9:

To see if these development packages are installed on your server, run the following command:
rpm -q gcc cpp compat-libstdc++ glibc-devel glibc-kernheaders binutils
For instance, when I installed Red Hat 9.0 and when I used the default packages for the Installation Type "Server", I had to install the following RPMs afterwards:
su - root
rpm -ivh binutils-2.13.90.0.18-9.i386.rpm cpp-3.2.2-5.i386.rpm gcc-3.2.2-5.i386.rpm glibc-devel-2.3.2-5.i386.rpm glibc-kernheaders-2.4-8.10.i386.rpm
NOTE: Before you install Oracle9iR2 on Red Hat 9, make sure that you also read the information about the error message "Error in invoking target install of make file /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk" in the Oracle Installation Errors section!

Packages (RPMs) for Red Hat Enterprise Linux Advanced Server 3 (RHEL AS 3):

Ensure the following required packages are installed on your server by running the following command:
rpm -q make binutils gcc cpp glibc-devel glibc-headers glibc-kernheaders compat-db compat-gcc compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel gnome-libs openmotif21 setarch

Packages (RPMs) for Red Hat Enterprise Linux Advanced Server 4 (RHEL AS 4):

See also Oracle9i Release Notes Release 2 (9.2.0.4.0) for Linux x86 for the list of required RPMs.

Ensure the following required packages are installed on your server by running the following command:
rpm -q make compat-db compat-gcc-32 compat-gcc-32-c++ compat-oracle-rhel4 compat-libcwait compat-libgcc-296 compat-libstdc++-296 compat-libstdc++-33 gcc gcc-c++ gnome-libs gnome-libs-devel libaio-devel libaio make openmotif21 xorg-x11-deprecated-libs-devel xorg-x11-deprecated-libs

Many of these packages depend on other packages. For example, compat-gcc-32 requires binutils, gcc etc. Since I like to install a system with as few RPMs as possible I had to install the following RPMs to satisfy dependencies:
rpm -Uvh compat-db-4.1.25-9.i386.rpm compat-gcc-32-3.2.3-47.3.i386.rpm glibc-devel-2.3.4-2.i386.rpm glibc-headers-2.3.4-2.i386.rpm glibc-kernheaders-2.4-9.1.87.i386.rpm cpp-3.4.3-9.EL4.i386.rpm compat-gcc-32-c++-3.2.3-47.3.i386.rpm compat-libstdc++-33-3.2.3-47.3.i386.rpm gcc-3.4.3-9.EL4.i386.rpm cpp-3.4.3-9.EL4.i386.rpm gcc-c++-3.4.3-9.EL4.i386.rpm libstdc++-devel-3.4.3-9.EL4.i386.rpm openmotif21-2.1.30-11.RHEL4.2.i386.rpm xorg-x11-deprecated-libs-6.8.1-23.EL.i386.rpm compat-libgcc-296-2.96-132.7.2.i386.rpm compat-libstdc++-296-2.96-132.7.2.i386.rpm libaio-0.3.102-1.i386.rpm libaio-devel-0.3.102-1.i386.rpm

For xorg-x11-deprecated-libs-devel and xorg-x11-devel I had to install the following RPMs.
Note that these two packages are required for the Oracle patch 4198954 below.
rpm -Uvh xorg-x11-deprecated-libs-devel-6.8.1-23.EL.i386.rpm xorg-x11-devel-6.8.1-23.EL.i386.rpm fontconfig-devel-2.2.3-7.i386.rpm pkgconfig-0.15.0-3.i386.rpm freetype-devel-2.1.9-1.i386.rpm zlib-devel-1.2.1.2-1.i386.rpm

And for gnome-libs and gnome-libs-devel I had to install the following RPMs:
rpm -Uvh gnome-libs-1.4.1.2.90-44.1.i386.rpm gnome-libs-devel-1.4.1.2.90-44.1.i386.rpm ORBit-0.5.17-14.i386.rpm ORBit-devel-0.5.17-14.i386.rpm alsa-lib-1.0.6-4.i386.rpm audiofile-0.2.6-1.i386.rpm esound-0.2.35-2.i386.rpm esound-devel-0.2.35-2.i386.rpm gtk+-1.2.10-33.i386.rpm gtk+-devel-1.2.10-33.i386.rpm imlib-1.9.13-23.i386.rpm imlib-devel-1.9.13-23.i386.rpm libpng10-1.0.16-1.i386.rpm alsa-lib-devel-1.0.6-4.i386.rpm audiofile-devel-0.2.6-1.i386.rpm gdk-pixbuf-0.22.0-15.1.i386.rpm glib-devel-1.2.10-15.i386.rpm indent-2.2.9-6.i386.rpm libjpeg-devel-6b-33.i386.rpm libtiff-devel-3.6.1-7.i386.rpm libungif-4.1.3-1.i386.rpm libungif-devel-4.1.3-1.i386.rpm
HINT:
If you are using RHN, you could simply run:
up2date gnome-libs gnome-libs-devel
You can use the up2date command for any packages. It takes care of dependencies by installing all required packages automatically.

To install the compat-oracle-rhel4 and compat-libcwait packages you have to download the patch 4198954 from http://metalink.oracle.com/. Make sure to select the Linux x86 platform. To unzip the downloaded p4198954_21_LINUX.zip file, run:
$ unzip p4198954_21_LINUX.zip
Archive: p4198954_21_LINUX.zip
creating: 4198954/
inflating: 4198954/compat-oracle-rhel4-1.0-5.i386.rpm
inflating: 4198954/compat-libcwait-2.0-2.i386.rpm
inflating: 4198954/README.txt
#
Note that the compat-oracle-rhel4 and compat-libcwait packages require the xorg-x11-deprecated-libs and xorg-x11-deprecated-libs-devel packages, see above. To install the two RPMs from the 4198954 patch, run:
# rpm -Uvh 4198954/compat-oracle-rhel4-1.0-5.i386.rpm 4198954/compat-libcwait-2.0-2.i386.rpm

JDK
Skip this step for Oracle9iR2.

I successfully installed Oracle9iR2 without installing JDK on the system. Oracle comes now with its own Java. This means that you don't have to execute the following steps which were required for older Oracle versions:

Download JDK 1.3.1 or Blackdown 1.1.8_v3: (I usually used Blackdown)
http://www.blackdown.org/
http://java.sun.com/

According to the JDK documentation, install JDK under /usr/local. Then create a symbolic link to the JDK under /usr/local/java:
su - root
bzip2 -dc jdk118_v3-glibc-2.1.3.tar.bz2 | tar xf - -C /usr/local
ln -s /usr/local/jdk118_v3 /usr/local/java

Creating Oracle User Accounts
su - root
groupadd dba # group of users to be granted with SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c "Oracle software owner" -g oinstall -G dba oracle
passwd oracle
For more information on the "oinstall" group account, see When to use "OINSTALL" group during install of oracle.
Creating Oracle Directories
In this example, make sure that the /u01 filesystem is large enough, see Oracle Disk Space for more information. If /u01 is not on a separate filesystem, then make sure the root filesystem "/" has enough space.
su - root
mkdir -p /u01/app/oracle/product/9.2.0
chown -R oracle.oinstall /u01

mkdir /var/opt/oracle
chown oracle.dba /var/opt/oracle
chmod 755 /var/opt/oracle

Setting Oracle Environments
Set the following Oracle environment variables before you start runInstaller.

As the oracle user execute the following commands:
# Set the LD_ASSUME_KERNEL environment variable only for Red Hat 9,
# RHEL AS 3, and RHEL AS 4 !!
# Use the "Linuxthreads with floating stacks" implementation instead of NPTL:
export LD_ASSUME_KERNEL=2.4.1 # for RH 9 and RHEL AS 3
export LD_ASSUME_KERNEL=2.4.19 # for RHEL AS 4

# Oracle Environment
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=test
export ORACLE_TERM=xterm
# export TNS_ADMIN= Set if sqlnet.ora, tnsnames.ora, etc. are not in $ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

# Set shell search paths
export PATH=$PATH:$ORACLE_HOME/bin

I successfully installed Oracle9iR2 without setting the following CLASSPATH environment variable:
# CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
# CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
# export CLASSPATH

You can put these environment settings at the end of the ~oracle/.bash_profile file if you use bash. By this way you don't have to set the environment variables again when you login as "oracle", or when you switch to the user "oracle" by executing "su - oracle".
Starting runInstaller
Before you continue, make sure you have set the Oracle environment variables, see above.

Oracle no longer supports a character mode installer. Therefore, in order to execute runInstaller directly from a console of a machine you are logged into (in this example the node name where Oracle is running is called "oracleserver"), you need to set the DISPLAY environment variable. Before you do that, make sure that you also allow runInstaller on "oracleserver" to display X information on your Linux desktop machine (in this example, the PC name where you are running X Windows like KDE or GNOME is called "yourdesktop"), because programs running on remote machines cannot display information to your screen unless you give them the authority to do so. Note that the X display relink mechanism does not work for NT desktop machines unless you use Exceed.

Before you run runInstaller, execute e.g. 'xterm' to see if your X setup is really working! If you install Oracle on your desktop PC and not on a remote node, then you can skip step 1 and 3.

Step 1: Allow "oracleserver" to display X information to your desktop PC "yourdesktop":
yourdesktop:user$ xhost +oracleserver
Step 2: Open a new window and login to the Oracle server "oracleserver" as root. This window will be used for mounting and unmounting the Oracle CDs.
oracleserver:$ su - root
oracleserver:root# mount /mnt/cdrom
Step 3: From the console of your Oracle server "oracleserver" where you will run runInstaller, execute the following commands:
oracleserver:$ su - oracle
oracleserver:oracle$ export DISPLAY=yourdesktop:0.0
Step 4: Now execute runInstaller as "oracle". Do not cd to /mnt/cdrom !!
oracleserver:oracle$ /mnt/cdrom/runInstaller

NOTE 1:

If you use for example Red Hat Fedora Core 3 as your desktop and you want to install the database on another machine, then you need to set the DisallowTCP entry in /etc/X11/gdm/gdm.conf for the GNOME Display Manager to read:
DisallowTCP=false
After that you need to restart your X server. I usually do this with the init command:
su - root
init 3
init 5

NOTE 2:

Don't run runInstaller for Red Hat Enterprise Linux Advanced Server 3 (RHEL AS 3) yet! See Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 3 for more information.
Running Oracle Installation on RH 7.1, 7.2, 7.3, 8.0, 9, and on RH AS 2.1
Keep in mind that you may get one or more errors here during the Oracle installation! See Oracle Installation Errors for more information.

This is how I answered the questions for the runInstaller:

- What would you like as the base directory (Inventory Location):
/u01/app/oracle/oraInventory
- UNIX Group Name (permission for updating Oracle software):
oinstall
You could also use "dba" which I do not recommend for security reasons.
For more information on the "oinstall" group account, see
When to use "OINSTALL" group during install of oracle.

- Full path name for Oracle Home:
/u01/app/oracle/product/9.2.0
etc.
Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 3
In order to install an Oracle9iR2 database on RH AS 3, the "Oracle9iR2 Patch Set 3 9.2.0.4.0" patchset and some other patches must be applied. Some errors can only be fixed by applying the 9.2.0.4 patchset.

Installing Oracle9iR2 on RH AS 3

Install the following RPMs (see Oracle Note:252217.1 for more information):
su - root
rpm -ivh compat-db-4.0.14-5.i386.rpm compat-gcc-7.3-2.96.122.i386.rpm compat-gcc-c++-7.3-2.96.122.i386.rpm compat-libstdc++-7.3-2.96.122.i386.rpm compat-libstdc++-devel-7.3-2.96.122.i386.rpm openmotif21-2.1.30-8.i386.rpm setarch-1.3-1.i386.rpm tcl-8.3.5-92.i386.rpm
Relink gcc so that the older gcc will be used during the Oracle installation (see Oracle Note:252217.1 for more information):
su - root
mv /usr/bin/gcc /usr/bin/gcc323
ln -s /usr/bin/gcc296 /usr/bin/gcc
mv /usr/bin/g++ /usr/bin/g++323 # if g++ doesn't exist, then gcc-c++ was not installed
ln -s /usr/bin/g++296 /usr/bin/g++

When you execute runInstaller from the Oracle 9iR2 (9.2.0) CD, you will get the following error message:
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2003-10-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

To resolve the __libc_wait symbol issue, download the patch p3006854_9204_LINUX.zip from http://metalink.oracle.com/. See bug 3006854 for more information.

To apply the patch, run
su - root
# unzip p3006854_9204_LINUX.zip
Archive: p3006854_9204_LINUX.zip
creating: 3006854/
inflating: 3006854/rhel3_pre_install.sh
inflating: 3006854/README.txt

# cd 3006854
# sh rhel3_pre_install.sh
Applying patch...
Patch successfully applied
#
NOTE: If you get the following error when you run rhel3_pre_install.sh:
rhel3_pre_install.sh: line 36: gcc: command not found
Then you forgot to install or link gcc, see above. This means you can't start any binaries any more:
# ls
ls: error while loading shared libraries: /etc/libcwait.so: cannot open shared object file: No such file or directory
# rm /etc/ld.so.preload
rm: error while loading shared libraries: /etc/libcwait.so: cannot open shared object file: No such file or directory
#
To fix that, run the echo command which is a built-in shell command:
# echo "" > /etc/ld.so.preload
rm /etc/ld.so.preload
And start over again.

Now runInstaller can be started from the CD:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ /mnt/cdrom/runInstaller

- Welcome Screen: Click Next
- Inventory Location: Click Next
- Unix Group Name: Use "oinstall" and click Next
When asked to run /tmp/orainstRoot.sh, run it before you click Continue
- File Locations: Use default values
- Available Products: Select "Oracle9i Database 9.2.0.1.0"
- Installation Types: Select Custom since we only want to install the software for now
- Available Products: Click Next or add some more components.
- Components Locations: Accept default values and click Next
- Privileged Operating System Groups:
I used the default values: OSDBA Group = dba, OSOPER Group = dba
- Oracle Managent Server Repository:
I used the default choice
- Create database: Select NO since we first have to patch Oracle before a database can be created!
- Summary: Start the Install
- Configuration tools: Tools won't come up. Simply ignore it.
- At the end of the installation, exit runInstaller.

You may get the following errors:

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.

The /u01/app/oracle/product/9.2.0/install/make.log file reads:
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcln.o)(.text+0xa4e): In function `Nls_FormatCmd':
: undefined reference to `__ctype_b'
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcln.o)(.text+0x159d): In function `Nls_ScanCmd':
: undefined reference to `__ctype_b'
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcln.o)(.text+0x1603): more undefined references to `__ctype_b' follow
collect2: ld returned 1 exit status
make: *** [dbsnmp] Error 1
Click ignore. This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied. You won't be able to apply the patch 3119415 at this time since the file /u01/app/oracle/oraInventory/ContentsXML/comps.xml doesn't exist yet.

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk.

The /u01/app/oracle/product/9.2.0/install/make.log file reads:
/usr/bin/ld: ctxhx: hidden symbol `stat' in /usr/lib/libc_nonshared.a(stat.oS) is referenced by DSO
collect2: ld returned 1 exit status
make: *** [ctxhx] Error 1
Click ignore. This is fixed by applying the 9.2.0.4 patchset.

Patching Oracle9iR2 on RH AS 3

To patch Oracle9iR2, download the Oracle 9i Release 2 Patch Set 3 Version 9.2.0.4.0 for Linux x86 from http://metalink.oracle.com/.

Copy the downloaded "p3095277_9204_LINUX.zip" file to e.g. /tmp and run the following command:
su - oracle
$ cp p3095277_9204_LINUX.zip /tmp
$ cd /tmp
$ unzip p3095277_9204_LINUX.zip
Archive: p3095277_9204_LINUX.zip
inflating: 9204_lnx32_release.cpio
inflating: README.html
inflating: patchnote.css
$
$ cpio -idmv < 9204_lnx32_release.cpio
Disk1/stage/locks
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/bin.1.1.jar
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/lib.1.1.jar
...

To patch the runInstaller, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd /tmp/Disk1/
$ ./runInstaller

- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle Universial Installer 2.2.0.18.0 !"
- Components Locations: Accept default values and click Next
- Summary: Start the Install
- At the end of the installation, you must exit runInstaller!

To patch Oracle9iR2, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd $ORACLE_HOME/bin
$ ./runInstaller

- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle9iR2 Patch Set 3 9.2.0.4.0 !"
- Summary: Start the Install
- At the end of the installation, exit runInstaller

You may get the following error:

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.

The /u01/app/oracle/product/9.2.0/install/make.log file reads:
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcl.o)(.text+0x1cc): In function `get_ora_stmt_handle':
: undefined reference to `__ctype_b'
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcl.o)(.text+0x124e): In function `OraProcess_Oid':
: undefined reference to `__ctype_b'
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcl.o)(.text+0x176c): more undefined references to `__ctype_b' follow
collect2: ld returned 1 exit status
make: *** [dbsnmp] Error 1
Click ignore. This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied. The patch 3119415 cannot be applied while the patch process for the 9.2.0.4 patchset is running.

After the 9.2.0.4 patchset has been applied, download the patch p3119415_9204_LINUX.zip from http://metalink.oracle.com/. See bug 3119415 for more information. Also, download the opatch Release 2.2.0 utility from http://metalink.oracle.com/. See bug 2617419 for more information.

To install opatch, run:
su - oracle
$ cp p2617419_210_GENERIC.zip /tmp
$ cd /tmp
$ unzip p2617419_210_GENERIC.zip
Before you apply the 3119415 patch, you need to make sure the fuser binary can be found by the oracle user, see the PATH environment variable below. Otherwise the patch can't be applied because the fuser binary is used by opatch.

To apply the 3119415 patch, run
su - oracle
$ unzip p3119415_9204_LINUX.zip
$ cd 3119415
$ export PATH=$PATH:/tmp/OPatch
$ export PATH=$PATH:/sbin # the patch needs "fuser" which is located in /sbin
$ which opatch
/tmp/OPatch/opatch
$ opatch apply

Now you should be able to create a database with dbca:
su - oracle
dbca

Patching Oracle Intelligent Agent on RH AS 3

When you run "agentctl start" (Oracle 9.2.0.4), dbsnmp will crash:
$ su - oracle
$ agentctl start

DBSNMP for Linux: Version 9.2.0.4.0 - Production on 07-JAN-2004 19:11:14

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

Starting Oracle Intelligent Agent.../u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1855 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $*
>>$DBSNMP_WDLOGFILE 2>&1
/u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1868 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1880 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1892 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1

To resolve this problem, apply the patch p3238244_9204_LINUX.zip from http://metalink.oracle.com/. See bug/patch 3238244 for more information.

Before you apply the patch, make sure the instance is down!

Also make sure the opatch script appears in your $PATH. See "Patching Oracle9iR2 on Red Hat AS 3" for information on getting and installing opatch. To verify if opatch is in your $PATH, run the which command:
$ su - oracle
$ which opatch
/tmp/OPatch/opatch
$

To apply now the patch, run:
$ su - oracle
$ unzip p3238244_9204_LINUX.zip
$ cd 3238244
$ export PATH=$PATH:/sbin # the patch needs "fuser" which is located in /sbin
$ opatch apply

Now you need to relink dbsnmp. This is the binary that crashed when running agentctl start. To find which makefile handles the linking of dbsnmp, you can run:
$ su - oracle
$ find $ORACLE_HOME -name "*.mk" | xargs grep -l dbsnmp
/u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk
/u01/app/oracle/product/9.2.0/network/lib/env_oemagent.mk
$

I relinked dbsnmp and all associated executables which are maintained by the ins_oemagent.mk makefile:
$ su - oracle
$ cd $ORACLE_HOME/network/lib
$ make -f ins_oemagent.mk install

Now you should be able to start the agent:
$ su - oracle
$ agentctl start

NOTE: Don't forget to undo the changes (links) to /usr/bin/gcc and /usr/bin/g++ if you don't need it any more. Also don't forget the /etc/ld.so.preload file.
Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 4
In order to install Oracle9i Release 2 (9.2.0.6) I've applied the 9.2.0.6 patch set for the Oracle database server (patch number 3948480) after the Oracle9i Release 2 (9.2.0.4) installation. For more information, see Oracle9i Release Notes Release 2 (9.2.0.4.0) for Linux x86 - Red Hat Enterprise Linux 4 Certification Update.

Installing Oracle9iR2 on RH AS 4

Before you continue, ensure all the required RPMs are installed, see Packages (RPMs) for Red Hat Enterprise Linux Advanced Server 4 (RHEL AS 4).

Also ensure LD_ASSUME_KERNEL is set to 2.4.19 (see Setting Oracle Environments):
$ su - oracle
$ echo $LD_ASSUME_KERNEL
2.4.19
$

Now launch runInstaller:
su - oracle
$ echo $LD_ASSUME_KERNEL
2.4.19
$ /media/cdrom/runInstaller

- Welcome Screen: Click Next
- Inventory Location: Click OK
- Unix Group Name: Use "oinstall" and click Next
When asked to run /tmp/orainstRoot.sh, run it before you click Continue
- File Locations: Use default values
- Available Products: Select "Oracle9i Database 9.2.0.4.0"
- Installation Types: Select Custom since we only want to install the software for now
- Available Products: Click Next or add some more components.
- Components Locations: Accept default values and click Next
- Privileged Operating System Groups:
I used the default values: OSDBA Group = dba, OSOPER Group = dba
- Oracle Managent Server Repository:
I used the default choice
- Create database: Select NO since we first need to patch Oracle database software!
- Summary: Start the Install

Patching Oracle9i R2 (9.2.0.4) on RH AS 4

Download the patch 3948480 (Oracle9i Patch Set Release 2 (9.2.0.6) Patch Set 5) from http://metalink.oracle.com/ and execute the following commands:
su - oracle
$ cp p3948480_9206_LINUX.zip /tmp
$ cd /tmp
$ unzip p3948480_9206_LINUX.zip
Archive: p3948480_9206_LINUX.zip
creating: Disk1/
creating: Disk1/stage/
creating: Disk1/stage/Patches/
...

Now download the patch 4188455 from http://metalink.oracle.com/.
This patch is needed for launching the runInstaller that came with the patch 3948480 we just downloaded above.
su - oracle
$ cp p4188455_10103_LINUX.zip /tmp
$ cd /tmp
$ unzip p4188455_10103_LINUX.zip
Archive: p4188455_10103_LINUX.zip
inflating: oraparam.ini
inflating: README.txt
$
The /tmp/oraparam.ini file will now be used for launching the runInstaller that came with the patch 3948480.

To patch the runInstaller itself, run:
su - oracle
$ echo $LD_ASSUME_KERNEL
2.4.19
$ /tmp/Disk1/install/runInstaller -paramFile /tmp/oraparam.ini

- Welcome Screen: Click Next
- File Locations: Use default values (in my example: /tmp/Disk1/stage/products.xml)
- Available Products: Select "Oracle Universial Installer 10.1.0.3.0 !"
- Summary: Click Install
- At the end of the installation, you must exit runInstaller!

Ensure that no Oracle processes are running:
ps -ef | grep ora

Now to patch Oracle9iR2, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.19
$ /tmp/Disk1/install/runInstaller -paramFile /tmp/oraparam.ini

- Welcome Screen: Click Next
- File Locations: Use default values (in my example: /tmp/Disk1/stage/products.xml)
- Available Products: Select "Oracle 9iR2 Patchset 9.2.0.6.0"
- Summary: Click Install
When are asked to run root.sh, run it before you click Continue
- At the end of the installation, exit runInstaller.

After the 9.2.0.6 patchset has been applied, download the patch 4190568 from http://metalink.oracle.com/. Also, download the opatch utility for release 10.1.0.2 (patch 2617419) from http://metalink.oracle.com/.

To install opatch, run:
su - oracle
$ cp p2617419_10102_GENERIC.zip /tmp
$ cd /tmp
$ unzip p2617419_10102_GENERIC.zip
$ cp -a /tmp/OPatch/ $ORACLE_HOME

To apply the 4190568 patch, run
su - oracle
$ unzip p4190568_9206_LINUX.zip
$ cd 4193454
$ export PATH=$PATH:$ORACLE_HOME/OPatch
$ opatch apply

If you intend to use Direct I/O Support, you must also download and apply patch 2448994.

Now you should be able to create a database with dbca:
su - oracle
dbca

When dbca died on my system with the following error:
/u01/app/oracle/product/9.2.0/bin/dbca: line 124: 26649 Segmentation fault
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS
I executed the following command:
su - root
touch /etc/rac_on
and restarted dbca. If you know a better solution, let me know!
Startup and Shutdown of the Oracle 9i Database
sqlplus:

svrmgrl is not supported any more. You can now do everything with sqlplus.
For instance, to startup the database, run the following commands:
oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
The slash connects you to the schema owned by SYS. So in this example you will be connected to the schema owned by SYS with the privilege SYSDBA. SYSDBA gives you the following privileges:
- sysoper privileges WITH ADMIN OPTION
- create database
- recover database until

$ORACLE_HOME/bin/dbstart and $ORACLE_HOME/bin/dbshut

You can also use $ORACLE_HOME/bin/dbstart to startup the database, and $ORACLE_HOME/bin/dbshut to shutdown the database. You can place $ORACLE_HOME/bin/dbstart into the /etc/rc.d/rc.local boot script to automatically bring up the database at system boot time. To get $ORACLE_HOME/bin/dbstart and $ORACLE_HOME/bin/dbshut working, you need to change the third field for your Oracle SID in /etc/oratab from "N" to "Y".

For example, for the Oracle SID "test" I changed the line in /etc/oratab from:
test:/u01/app/oracle/product/9.2.0:N
to read:
test:/u01/app/oracle/product/9.2.0:Y

In some cases for 9.2.0 I also had to copy the init file for my SID "test" from /u01/app/oracle/admin/test/pfile to $ORACLE_HOME/dbs to get dbstart and dbshut working:
cp /u01/app/oracle/admin/test/pfile/inittest.ora.642002224936 $ORACLE_HOME/dbs/inittest.ora
But first make sure if your init file already exists in $ORACLE_HOME/dbs!
Oracle Installation Problems, Tips and Hints
Some of these problems apply only to 9.0.1!
• Do not cd to /mnt/cdrom to run ./runInstaller!
If you do so, the installation will fail because you won't be able to change the CDs.
• If you forgot to set the DISPLAY environment variable (e.g. export DISPLAY=oracleserver:0.0), or if you forgot to give the remote console - your Oracle Server - authority to display X information on your desktop PC (e.g. xhost +oracleserver), then you will get the following error:
• Xlib: connection to ":0.0" refused by server
Xlib: Client is not authorized to connect to Server
In this case, I always had to kill runInstaller in Oracle9iR1 (9.0.1) which was still running in the background. If I didn't do this in 9.0.1, runInstaller didn't completely come up any more without displaying any error messages. You might also want to clean up /tmp/OraInstall.
• When runInstaller starts to configure the tools ("Configuration Tools"), the "Oracle Net Configuration Assistant" will sometimes hang. Simply stop the Assistant and restart it, or continue the installation. When the rest of the installation is finished, do a "Retry" for "Oracle Net Configuration Assistant". This always worked for me.
• When the system stops responding during the Oracle installation in particular during the database creation, then that's probably because you don't have enough RAM or enough swap space. I saw the whole system not responding or to "hang" for several minutes when I did not have enough swap space. If this happens, simply wait until the system starts to respond again.
• The Oracle installation also runs make etc. In a production environment you might not have compilers and other development packages installed. Therefore make sure you have temporarily the following packages installed: gcc, cpp, glibc-devel, compat-libstdc++, kernel-headers (for RH 7.1, 7.2, 2.1AS), glibc-kernheaders (for RH 7.3, 8.0, 9.0), binutils. See also Checking Packages (RPMs) for more information.
• If for any reason the Oracle9i installation didn't finish successfully, you might want to clean up the following files and directories before you start over again: /etc/oraInst.loc /etc/oratab /tmp/ $ORACLE_BASE/*
• Other Problems:
You might want to check out the Oracle on Linux Discussion Forum.

Oracle Installation Errors
Here is a list of Oracle 9i (9.0.1 & 9.2.0) installation problems and issues. Some issues, errors, problems, and solutions apply only to 9.0.1 and some only to 9.2.0. Since I did not experience all of the problems here, I am not able to verify the correctness of all the solutions. However, I experienced most of the problems listed here. If you had other problems and you were able to resolve them, please drop me an email at webmaster_at_puschitz.com.

Here is a list of issues issues, errors, problems and solutions:
• Log Files
First check always the error logs for 9.2.0 in /tmp/OraInstall (e.g /tmp/OraInstall2002-07-04_09-50-19PM), and for 9.0.1 in /tmp/OraInstall. When you get make problems, check also the file $ORACLE_HOME/install/make.log.
• "Various make Problems"
Make sure that gcc is installed on your system:
$ which gcc
/usr/bin/gcc
Here is the command to find the RPM package name for /usr/bin/gcc:
$ rpm -qf /usr/bin/gcc
gcc-2.96-98
Check also the other error messages below. See also Checking Packages (RPMs) for more information.
• "Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk"
I saw this error only when I installed Oracle9iR2 (9.2.0). This was also the only problem I experienced with Oracle 9i R2 on Red Hat 8.0. However, this does not necessarily mean that you won't experience other problems described here.

When I had this problem, the following errors showed up in $ORACLE_HOME/install/make.log:
/lib/libdl.so.2: undefined reference to `_dl_addr@GLIBC_PRIVATE'
/lib/libdl.so.2: undefined reference to `_dl_open@GLIBC_PRIVATE'
/lib/libdl.so.2: undefined reference to `_dl_close@GLIBC_PRIVATE'
/lib/libdl.so.2: undefined reference to `_dl_sym@GLIBC_PRIVATE'
/lib/libdl.so.2: undefined reference to `_dl_vsym@GLIBC_PRIVATE'
This error comes up when the following step is executed:
/usr/bin/make -f ins_ctx.mk install ORACLE_HOME=/u01/app/oracle/product/9.2.0

Edit the file $ORACLE_HOME/ctx/lib/env_ctx.mk, go to "INSO_LINK =", and add a "$(LDLIBFLAG)dl" to the line and save it.

Here is the full line with the added "$(LDLIBFLAG)dl" flag:

INSO_LINK = -L$(CTXLIB) $(LDLIBFLAG)m $(LDLIBFLAG)dl $(LDLIBFLAG)sc_ca $(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut $(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c -Wl,-rpath,$(CTXHOME)lib $(CORELIBS) $(COMPEOBJS)

After that hit retry in the error popup.

If this didn't work, then try the following:

Edit the file $ORACLE_HOME/ctx/lib/env_ctx.mk again, go to "INSO_LINK =", remove the above entry you made and add a "`cat $(LIBHOME)/sysliblist`" to the line and save it.

Here is the full line with the added "`cat $(LIBHOME)/sysliblist`" string:

INSO_LINK = -L$(CTXLIB) $(LDLIBFLAG)m `cat $(LIBHOME)/sysliblist` $(LDLIBFLAG)sc_ca $(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut $(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c -Wl,-rpath,$(CTXHOME)lib $(CORELIBS) $(COMPEOBJS)

After that hit retry in the error popup.
• ORA-27123: unable to attach to shared memory segment.
I saw this error only when I installed Oracle 9i R2 (9.2.0).

This error message came up when the Oracle Database Configuration Assistant was running. I executed the following command to temporarily increase the maximum shared memory size:
su - root
# cat /proc/sys/kernel/shmmax
33554432
# echo `expr 1024 \* 1024 \* 1024` > /proc/sys/kernel/shmmax
# cat /proc/sys/kernel/shmmax
1073741824
#
Then click "Retry" for the Oracle Database Configuration Assistant.

It is recommended to increase the shmmax setting permanently for Oracle9i. So if you want to increase the maximum shared memory size permanently, add the following line to the /etc/sysctl.conf file:
kernel.shmmax=1073741824
For more information on setting shared memory parameters for Oracle, see Setting Shared Memory.
• ORA-03113: end-of-file on communication channel
I saw this error when I've run the "Database Configuration Assistant" and "sqlplus". When the "Database Configuration Assistant" gave me this error during Oracle9iR2 (9.2.0) installation on Red Hat 2.1 AS, I simply removed the shared memory segments owned by the Oracle user and I restarted the "Database Configuration Assistant". I'm not sure if this is the right way but it always worked for me. Here is what I did to get the "Database Configuration Assistant" running again:

Database Configuration Assistant:

I executed the ipcs command to get the address of the shared memory segments that have been allocated by Oracle:
$ su - root
# ipcs

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 600 196608 2
0x00000001 32769 root 600 655360 2
0x00000000 458755 oracle 660 4194304 0
0x00000000 491524 oracle 660 33554432 0
0x00000000 524293 oracle 660 33554432 0
0x00000000 557062 oracle 660 33554432 0
0x00000000 589831 oracle 660 33554432 0
0x00000000 622600 oracle 660 33554432 0
0x00000000 655369 oracle 660 33554432 0
0x00000000 688138 oracle 660 33554432 0
0x3ecee0b0 720907 oracle 660 4194304 0

------ Semaphore Arrays --------
key semid owner perms nsems status

------ Message Queues --------
key msqid owner perms used-bytes messages

#
Then I removed all shared memory segments that were owned by the Oracle user during the installation with the following command:
# ipcrm shm 458755 491524 524293 557062 589831 622600 655369 688138 720907
After that I restarted the "Database Configuration Assistant". Once the installation was done I immediately restarted the DB as well.
Caveat: I'm not sure if this procedure can cause any further problems if this is done during the installation. But so far I haven't seen any issues with this approach.

sqlplus:

If you get this problem in connection with sqlplus, then simply make sure that the database is down and exit sqlplus. After that, follow the procedure above by removing all shared memory segments that belong to the Oracle user. To my knowledge, this should not cause any problems.

For more information on shared memory segments, see Determining Which Semaphore Sets and Shared Memory Segments Belong to Each Oracle Database or Instance.

NOTE:
To solve this problem permanently, increase the kernel shmmax size. For more information, see Setting Shared Memory and Setting Shared Memory.
• "Error invoking target install of makefile /u01/app/oracle/product/9.0.1/plsql/lib/ins_plsql.mk"
"Error invoking target install of makefile /u01/app/oracle/product/9.0.1/precomp/lib/ins-precomp.mk"
"Error invoking target install of makefile /u01/app/oracle/product/9.0.1/precomp/lib/ins-net-client"
I saw this error only when I installed Oracle 9i (9.0.1). People have sent me emails pointing out that the following solution also works for Mandrake 8.1, Mandrake 8.2, and for SuSE 8.0.

Edit the file $ORACLE_HOME/bin/genclntsh and change the following line:
LD_SELF_CONTAINED="-z defs"
to read:
LD_SELF_CONTAINED=""
After that run the script $ORACLE_HOME/bin/genclntsh as the user "oracle" and not as the user "root". Also make sure you have all the Oracle environments set correctly!
$ su - oracle
$ $ORACLE_HOME/bin/genclntsh
Created /u01/app/oracle/product/9.0.1/lib/libclntst9.a
$
After that hit Retry in the error dialog window. This always worked for me.

Here is Oracle's official solution for Oracle 9iR1 and 9iR1 iAS on RedHat 2.1 Advanced Server:
http://otn.oracle.com/software/products/oracle9i/files/binutils_readme.html
• "Error in invoking target install of make file /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk"
If you see this error on Red Hat Enterprise Linux 3, follow the guideline at Running Oracle Installation on Red Hat Enterprise Linux Advanced Server 3.

On Red Hat 9 I performed the following steps here when the ORACLE_HOME/install/make.log file contained the error messages:
...
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcln.o)(.text+0x159d): In function `Nls_ScanCmd':
: undefined reference to `__ctype_b'
/u01/app/oracle/product/9.2.0/network/lib/libnmi.a(snmitcln.o)(.text+0x1603): more undefined references to `__ctype_b' follow

The issue here is that __ctype_b() is actually gone for __ctype_b_loc() because Red Hat uses a new locale model. However, in libc.so, __ctype_b is still exported as compatibility symbol; at least that's the case with RH 9 glibc-2.3.2-5. And here is the reason why some people have this problem with Red Hat 9 and why some don't:

When you bought the Red Hat 9 CDs in a store, then you will probably find glibc-2.3.2-5.i686.rpm on the first CD. This glibc version exports __ctype_b():
$ rpm -ql glibc-2.3.2-5 | grep libc.so
/lib/i686/libc.so.6
/lib/libc.so.6
/lib/tls/libc.so.6
$ nm -a /lib/i686/libc.so.6 | grep __ctype_b
001315f8 D __ctype_b
00022340 T __ctype_b_loc
$ nm -a /lib/libc.so.6 | grep __ctype_b
00133c58 D __ctype_b
000223a0 T __ctype_b_loc
$
But when you downloaded Red Hat 9 from redhat.com or from one of the mirror sites, then you will find glibc-2.3.2-11.9.i686.rpm on the image. This glibc version does not export __ctype_b(). This is also the case with glibc-devel-2.3.2-27.9.i386.rpm.
$ rpm -ql glibc-2.3.2-11.9 | grep libc.so
/lib/i686/libc.so.6
/lib/libc.so.6
/lib/tls/libc.so.6
$ nm -a /lib/i686/libc.so.6 | grep __ctype_b
00131718 D __ctype_b@GLIBC_2.0
000223a0 T __ctype_b_loc
$ nm -a /lib/libc.so.6 | grep __ctype_b
00133d58 D __ctype_b@GLIBC_2.0
000223f0 T __ctype_b_loc
$

Check the glibc version on your system:

First check if the glibc packages on your RH 9 system work with the Oracle installer:
$ rpm -q glibc-2.3.2-5 glibc-common-2.3.2-5 glibc-devel-2.3.2-5
If you got the following error mesages:
package glibc-2.3.2-5 is not installed
package glibc-common-2.3.2-5 is not installed
package glibc-devel-2.3.2-5 is not installed
then you have glibc packages on your system that don't work with the Oracle installer and you need to follow the "Work Around" procedure here.
But if your system has the 2.3.2-5 glibc versions installed, then you are fine and you don't need to follow the described "Work Around" procedure!

Work Around Procedure:

Since I was not able to find the glibc-2.3.2-5 RPMs available for download, I'm making the RPMs available on my website. These RPMs are copies of the glibc RPMs that came with the RH 9 CDs I bought in the store. I do not recommend to use any of the "compat" RPMs from older Red Hat distributions since RH 9 contains major changes.

Here is the procedure for installing glibc-2.3.2-5 temporarely on your RH 9 server:

Download the 2.3.2-5 glibc RPMs from here on my web site.

First make sure if these downloaded RPM's are not corrupt and if they were really built and signed by Red Hat. You never know if someone fiddled with these RPMs or replaced them. To ensure the integrity and origin of these Red Hat's RPMs, run the following commands:
$ su - root
# rpm --import /usr/share/rhn/RPM-GPG-KEY # add Red Hat's PGP public key to the RPM database
# rpm --checksig glibc-2.3.2-5.i686.rpm glibc-common-2.3.2-5.i386.rpm glibc-devel-2.3.2-5.i386.rpm
glibc-2.3.2-5.i686.rpm: (sha1) dsa sha1 md5 gpg OK
glibc-common-2.3.2-5.i386.rpm: (sha1) dsa sha1 md5 gpg OK
glibc-devel-2.3.2-5.i386.rpm: (sha1) dsa sha1 md5 gpg OK
#
Downgrade glibc, glibc-common, and glibc-devel:
# rpm -Uvh --oldpackage glibc-2.3.2-5.i686.rpm glibc-common-2.3.2-5.i386.rpm glibc-devel-2.3.2-5.i386.rpm
If you get the following error:
error: Failed dependencies:
glibc = 2.3.2-11.9 is needed by (installed) glibc-debug-2.3.2-11.9
glibc = 2.3.2-11.9 is needed by (installed) glibc-utils-2.3.2-11.9
glibc-devel = 2.3.2-11.9 is needed by (installed) glibc-debug-2.3.2-11.9
glibc-devel = 2.3.2-11.9 is needed by (installed) nptl-devel-2.3.2-11.9
then you can temporarily remove these RPMs (glibc-debug, glibc-utils, nptl-devel) from your system until you upgrade the glibc RPMs after your Oracle installation:
# rpm -e glibc-debug glibc-utils nptl-devel

Now try to run runInstaller again.

After Oracle has been installed, you can upgrade glibc, glibc-common, and glibc-devel again. For example:
# rpm -Uvh glibc-2.3.2-11.9.i686.rpm glibc-common-2.3.2-11.9.i386.rpm glibc-devel-2.3.2-11.9.i386.rpm

According to Red Hat, binary compatibility in Red Hat Linux is always guaranteed for binaries and shared libraries accross releases, but not for .o files nor .a files. However, compatibility is guaranteed for .o files and .a files. _within_ a realease. Since glibc-2.3.2-5 and glibc-2.3.2-11.9 are from the same release, compatibility should be guaranteed for .o files (Oracle's .o files which have been created during the Oracle installation) and .a files.
This means that Oracle should be fine when you upgrade glibc after the Oracle installation.

If you have any problems or issues with this solution, or if you have any comments, please let me know. You can find my email address at the bottom of this web site.
• $ agentctl start
•
• DBSNMP for Linux: Version 9.2.0.4.0 - Production on 07-JAN-2004 19:11:14
•
• Copyright (c) 2003 Oracle Corporation. All rights reserved.
•
• Starting Oracle Intelligent Agent.../u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1855 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $*
• >>$DBSNMP_WDLOGFILE 2>&1
• /u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1868 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
• /u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1880 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
• /u01/app/oracle/product/9.2.0/bin/dbsnmpwd: line 156: 1892 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
•
You are probably trying to start the agent on RH AS 3. See Patching Oracle Intelligent Agent on RH AS 3 how to resolve it.
• $ dbca
• SIGSEGV 11* segmentation violation
• stackbase=0x453da000, stackpointer=0x453d9d5c
• Full thread dump:
• "AWT-EventQueue-0" (TID:0x411d1e20, sys_thread_t:0x453d9e0c,
• state:R) prio=5 *current thread*
• java.lang.Object.wait(Object.java)
• java.awt.EventQueue.getNextEvent(EventQueue.java:126)
• ...
Or on e.g. RHEL4:
/u01/app/oracle/product/9.2.0/bin/dbca: line 124: 26649 Segmentation fault
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS
If this happens, try the following:
$ su - root
touch /etc/rac_on
Now try to restart dbca.

Another option is to edit $ORACLE_HOME/bin/dbca and to put the following lines under comment except the line marked in blue:
# if [ -f /etc/rac_on ]; then
# Run DBCA
$JRE_DIR/bin/jre -native -DORACLE_HOME=$OH ...
# else
# Run DBCA
# $JRE_DIR/bin/jre -DORACLE_HOME=$OH ...
# fi
Now try to restart dbca.
• gcc -o /u01/app/oracle/product/9.2.0/rdbms/lib/oracle -L/u01/app/oracle/product/9.2.0/rdbms/lib/ ...
• ...
• /usr/bin/ld: /u01/app/oracle/product/9.2.0/rdbms/lib/oracle: hidden symbol `__fixunssfdi' in /usr/lib/gcc-lib/i386-redhat-linux/3.2.3/libgcc.a(_fixunssfdi.oS) is referenced by DSO
•
• collect2: ld returned 1 exit status
• make: *** [/u01/app/oracle/product/9.2.0/rdbms/lib/oracle] Error 1
• /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/9.2.0
•
I've seen this error on RH AS 3. To fix the linking problem, I executed the following commands:
# mv /usr/bin/gcc /usr/bin/gcc323
# mv /usr/bin/g++ /usr/bin/g++323
# ln -s /usr/bin/gcc296 /usr/bin/gcc
# ln -s /usr/bin/g++296 /usr/bin/g++
Now you should be able to relink the oracle binary again.
Once you are done, make sure to revert back the changes you've made above:
# mv /usr/bin/gcc323 /usr/bin/gcc
# mv /usr/bin/g++323 /usr/bin/g++

• ./runInstaller: line 58: ./runInstaller: cannot execute binary file.
You are probably trying to run a 64-bit Oracle version on a 32-bit Linux system. Make sure you downloaded the right Oracle version for your Linux system.

To check if runInstaller is a 32-bit binary or a 64-bit binary, run the following command:
$ cd /mnt/cdrom
$ file install/linux/runInstaller
install/linux/runInstaller: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.0.0, dynamically linked (uses shared libs), not stripped
To check if your Linux system is 32-bit system or a 64-bit system, run e.g. the following command:
$ file /sbin/init
/sbin/init: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped

• The Oracle installer runInstaller hangs at: Installing Java Runtime Environment... Link pending... Copying README...
This problem comes up on RH 9 and on RH AS 3. You probably forgot to set the environment variable LD_ASSUME_KERNEL to 2.4.1.

To rectify this problem, run the following command and restart runInstaller:
oracle$ export LD_ASSUME_KERNEL=2.4.1
For more information on this issue, see Red Hat 9.
• Recovery Manager rman hangs
You are probably running the wrong rman binary which belongs to the XFree86-devel RPM:
$ which rman
/usr/X11R6/bin/rman

• Can't find init file for Database "SID".
I saw this error only with Oracle 9i R2 (9.2.0) when It tried to start the database with dbstart.

I copied the init file for my SID "test" from /u01/app/oracle/admin/test/pfile to $ORACLE_HOME/dbs to get dbstart and dbshut working:
cp /u01/app/oracle/admin/test/pfile/inittest.ora.642002224936 $ORACLE_HOME/dbs/inittest.ora

• "Error in setting permissions of file/directory /u01/app/oracle/jre/1.1.8/bin/i686/native_threads/.extract_args"
This happens if you didn't burn your CD correctly.
Either you burn your CD again to include dot files or you copy the .extract_args file from your downloaded image to where runInstaller complains it is missing.
• "jre was not found in /tmp/OraInstall/jre/bin/i586/green_threads/jre"
You are probably running runInstaller on a 586 machine, or your AMD CPU gets recognized as 586 (e.g. AMD K6-III-400). You can check your machine (hardware) type by executing "uname -m". If you are not running on a 586 or on a AMD machine, try to link jre to java and see if this solves your problem.

To rectify the problem with the 586 machine or with the AMD CPU, create a link for lib and bin from i586 to i686 and make the i686 directories read only. For example:
ln -s /tmp/OraInstall/jre/bin/i686 /tmp/OraInstall/jre/bin/i586
ln -s /tmp/OraInstall/jre/lib/i686 /tmp/OraInstall/jre/lib/i586
chmod u-w /tmp/OraInstall/jre/bin/i686/tmp/OraInstall/jre/lib/i686
Now restart runInstaller.
• ../jre/bin/i386/native_threads/java: error while loading shared libraries: libstdc++-libc6.1-1.so.2: cannot open shared object file: No such file or directory
You probably forgot to install the compat-libstdc++ RPM which is a package for "Standard C++ libraries for Red Hat Linux 6.2 backwards compatibility". To rectify this problem, install the compat-libstdc++ RPM. For example on Red Hat 9:
rpm -ivh compat-libstdc++-7.3-2.96.118.i386.rpm
See also Checking Packages (RPMs) for more information.
• /u01/app/oracle/jre/1.1.8/bin/../lib/i686/green_threads/libzip.so: symbol errno, version GLIBC_2.0 not defined in file libc.so.6 with link time reference (libzip.so)
Unable to initialize threads: cannot find class java/lang/Thread
Could not create Java VM
I experienced this problem when I was running the Database Configuration Assistant dbca on Red Hat 9 without setting the LD_ASSUME_KERNEL environment variable.

To rectify this problem, run the following command on Red Hat 9 and RHEL 3 and restart dbca:
oracle$ export LD_ASSUME_KERNEL=2.4.1
For more information on this issue, see Red Hat 9.
• $ lsnrctl start
• OR
• $ lsnrctl status
•
• LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 14-OCT-2004 14:33:10
• Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
• Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) TNS-12541: TNS:no listener
• TNS-12560: TNS:protocol adapter error
• TNS-00511: No listener
• Linux Error: 2: No such file or directory
• Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
• TNS-12541: TNS:no listener
• TNS-12560: TNS:protocol adapter error
• TNS-00511: No listener
• Linux Error: 111: Connection refused
•
One of the possibilities are that the /var/tmp/.oracle directory doesn't exist. This happened with fresh new Oracle 9.2.0.4.0 CDs on RH AS 3. If that's the case, run the following commands:
su - root
mkdir /var/tmp/.oracle
chown oracle:dba /var/tmp/.oracle
Now try to run lsnrctl start as oracle again.
• Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using 'alpha:0.0' as the value of the DISPLAY variable.
• at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
• at sun.awt.X11GraphicsEnvironment.(X11GraphicsEnvironment.java:59)
• at java.lang.Class.forName0(Native Method)
• at java.lang.Class.forName(Class.java:120)
• at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:58)
• at java.awt.Window.(Window.java:188)
• at java.awt.Frame.(Frame.java:315)
• at java.awt.Frame.(Frame.java:262)
• at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:593)
•
Ensure you followed the instructions at Starting runInstaller very closely.

NOTE: If you use for example Red Hat Fedora Core 3 as your desktop and you want to install the database on another machine, then you need to set the DisallowTCP entry in /etc/X11/gdm/gdm.conf for the GNOME Display Manager to read:
DisallowTCP=false
After that you need to restart your X server. I usually do this with the init command:
su - root
init 3
init 5

• Other Errors
You might want to check out the Oracle on Linux Discussion Forum.
© Copyright

The information provided in this article shows how I installed Oracle on my server(s) and is distributed AS IS. Every effort has been made to provide the information as accurate as possible, but no warranty or fitness is implied. The use of this information described herein is your responsibility, and to use it in your own environments do so at your own risk.

Transportable Tablespaces

Transportable Tablespaces

Oracle introduced Transportable Tablespace feature in Oracle 8i to quickly move tablespace across Oracle databases .Oracle suggests Moving data using transportable tablespaces is much faster than performing either an export/import of the same data. This is because the datafiles containing the actual data are copied to the destination location, and one can use an import utility to transfer only the metadata of the tablespace objects to the target database.But Oracle 8i failed to support transportation of tablespaces if the source and target tablespaces are in different block size.Oracle 9i took one step further and overcomes the block restriction by introducing support for multiple block size features in its core technology provided source and target databases should be same platform.Oracle adds platform independency for transporting tablespaces in Oracle 10g.

This article explains basic requirements and implementation of Tranportaion of tablespaces with detail examples and wide explanation on Cross Platform challenges. In my example I transported tablespace USERS from Instance TEST (Windows 2000 Advanced Server-Machine W2000) to Instance PRD(Windows XP SP2-Machine WXP).Both the database versions are 10.1.0.2.0
Let’s see what are all the minimum requirements to transport tablespace from one database to another database .I explained limitations exits only on Oracle 10g.

1)The source and target databases should be same character set and national character set.

2)The tablespace or tablespace sets should be self constrained .That is all the indexes partiotions and other dependents of tables in the tablespace must be inside the tablespace .doing so will lead to containment violations

3) System, undo, sysaux, and temporary tablespaces cannot be transported. Doing so will kick off the following errors.
ORA-29351: can not transport system, sysaux, or temporary tablespace

4) Users with tables in the exported tablespace should exist in the target database prior to initiating the import

Steps for Migration

1) Verify Tablespace Users is self constrained

sqlplus /nolog
SQL>Connect sys@TEST as sysdba
Connected
SQL> BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('USERS');
END;
SQL>/
PL/SQL procedure successfully completed.
SQL>
If any violations occur they reported in Transport_Set_Violations.To make sure just query the table
SQL>Select Violations from TRANSPORT_SET_VIOLATIONS;
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------
Index SCOTT.INDX_INVTAB in tablespace USERS points to table SCOTT.TINV in tables
pace INDX

The above information told me the tablespace is not self constrained .so I moved TINV table from INDX tablespace to USERS Tablespace.
SQL> ALTER TABLE SCOTT. TINV MOVE TABLESPACE USERS;
The above command will put the Table called TINV in USERS tablespace.

For Example If you are transporting Tablespaces as set ,just follow the below syntax

SQL> BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('USERS01,USERS02');
END;
SQL>/
PL/SQL procedure successfully completed.

2) Put the Tablespace in read only mode

In order to export meta data for tablespace,the tablespace should be in read only state.but strictly necessary for EXP Utility.To speed up this process checkpoint the system and then issue read only command.Doing so will flush all dirty blocks to disk.Make sure there is no active transactions in the database.Putting the tablespace read only state places tabespace in transactional read-only mode and waits for existing transactions to complete This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the Tablespace

SQL> alter system checheckpoint;
SQL> alter tablespace USERS Read Only;
Tablespace altered.
Make sure tablespace is in read only mode.If the tablespace is not in read only state ,oracle will through following error ORA-29335 Tablespace USERS not read only.
SQL>select status from DBA_TABLESPACES where TABLESPACE_NAME=’USERS’;
STATUS
READ ONLY

Finding out Tablespace Name and Data files associated with Tablespaces

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF
SYSAUX C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF
UNDOTBS1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF
SYSTEM C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF
MIG01 C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\MIG01.DBF

3)Exporting Meta Data of Tablespace

Once you performed above checks now it is time to export the meta data for tablespace using Oracle EXP Utility using TRANSPORT_TABLESPACES and TABLESPACES parameters
C:\> EXP TRANSPORT_TABLESPACE=YES TABLESPACES=USERS CONSTRAINTS=N GRANTS=Y TRIGGERS =N FILE=/ORACLE/USRMDATA.DMP OWNER=SCOTT LOG=/ORACLE/USRMDATA.LOG
USERNAME : SYS AS SYSDBA
PASSWORD :****
Below is the out put after successfully executing the above command

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
. . exporting table EMP
. . exporting table BONUS
. . exporting table SALGRADE
. . exporting table FAS_ACCMAS
. . exporting table INV_STKBDGT
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

4)Copying Dump file and Importing metadata into Target Database

Use Operating system copy commands to move Export dump files and datafiles related to tablespaces to the target system .once these files copied just run import on target database.

I copied USRMDATA.DMP and Datafile USERS.DBF into machine WXP. And now trying to plugging the tablespace into Target Database PRD by following way.

IMP TRANSPORT_TABLESPACE=Y DATAFILES=/ORACLE/TEST/USERS.DBF FILE=/ORACLE/DMP/USRMDATA.DMP
USERID : SYS/XXX AS SYSDBA

When the Import is successfully all the selected tablespace data is copied from source to target.
You need to put the tablespace in read write mode
SQL>ALTER TABLESPACE USERS READ WRITE

Check for newly imported tablespace

SQL> select TABLESPACE_NAME,PLUGGED_in from DBA_TABLESPACES;

TABLESPACE_NAME PLUGGED_IN
------------------------------ ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS YES

Now see all the data has been imported from source database

SQL>select count(*) from EMP;

14 ROWS SELECTED

After import the tablespace will be read only mode.Check the tablespace status.and change it read write mode.

SQL> select TABLESPACE_NAME ,STATUS from DBA_TABLESPACES where
TABLESPACE_NAME=’USERS’;

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY

SQL>alter tablespace USERS read write;

Now check the status

SQL> select TABLESPACE_NAME ,STATUS from DBA_TABLESPACES where
TABLESPACE_NAME=’USERS’;

5)Using Oracle Data Pump Import Utility(IMPDP)

In Oracle 10g You can do all 5 steps in just one go.You can achieve the same process by using oracle data pump Import Utility(IMPDP).Fisrt of all make the tablespace read only and create database link to source DB from target DB and do run the IMPDP from target database.

On target DB just do following steps to make import succcessfull.

SQL>create database link DBLINKTEST using ‘TEST’
Database link created

SQL>create directory DataPump as ‘C:/oracle/datapump’
Directory Created .Grant read and write operations on that directory.
Make the tablespace read only and run IMPDP as follows

> IMPDP DIRECTORY=DATAPUMP TRANSPORT_TABLESPACES=USERS NETWORK_LINK=DBLNKTEST DATA_FILES=’C:/COPIEDFILE/USERS.DBF’
LOG=IMPDP.LOG

Import: Release 10.1.0.2.0 - Production on Thursday, 17 March, 2005 18:39

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting 'SYS'.'SYS_IMPORT_TRANSPORTABLE_01': system/******** AS SYSDBA directory=datapump transport_tablespaces=USERS transport_datafiles=’C:/COPIEDFILE/USERS.DBF’ network_link= DBLNKTEST logfile=IMPDP.LOG

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Job 'SYS'.'SYS_IMPORT_TRANSPORTABLE_01' successfully completed at 18:40

The advantage of Oracle data pump IMPDP utility over Oracle EXP and IMP utilities is one need not create dump file for meta data extraction. Data Pump Import fetches the necessary metadata from the source across the database link and re-creates it in the target database

6)Cross Platform Challenges

In Oracle 9i and below one can’t transport tablespaces between databases running on different platforms.It means One can’t transport tablespaces between Solaris and HP-UX.But Oracle 10g relaxed this restriction and able to transport tablesp[aces between different OS as long as their OS byte orders are identical.

Let me put some information on byte order which is technically called as Endianness.

The byte order is termed as Endianness which is attribute of the system that indicates whether integers are represented from left to right or right to left Endianness are two types big and little.A Big Endian representation has a multi byte integer written with its most significant byte on the left and Little Endian representation has a multibyte integer written with its most significant byte on the right.so When a big-endian system tries to read data from a little-endian one, a conversion process is required—otherwise, the byte order will lead to an incorrect interpretation of the read data.

In order see the byte order of different Operating systems,just run this query

SQL>select PLATFORM_ID , PLATFORM_NAME, ENDIAN_FORMAT from

V$TRANSPORTABLE_PLATFORM

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little

15 rows selected.

The above query will give you some idea on byte order of different operating systems running on 32 bit and 64 bit.

To find out the details of platform of your source or target database just do following way

SQL>select NAME, PLATFORM_ID PLATFORM_NAME from V$DATABASE

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------------------------------------------------
TEST 7 Microsoft Windows IA (32-bit)

And on target database

SQL>select NAME, PLATFORM_ID PLATFORM_NAME from V$DATABASE

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------------------------------------------------
PRD 7 Microsoft Windows IA (32-bit)

Running EXPDP against source database to extract meta data file

>EXPDP DIRECTORY=DATAPUMP TRANSPORT_TABLESPACE=USERS DUMPFILE=C:\ORACLE\EXPDAT.DMP
USERID SYS/XXXX AS SYSDBA

Export: Release 10.1.0.2.0 - Production on Tuesday, 22 March, 2005 10:49

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": SYS/********@TEST AS SYSDBA directory=datapump transport_tablespaces=USERS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
C:\ORACLE\EXPDAT.DMP
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:50

So in this way I created dump file which holds metadata for tablespace USERS.

RMAN TARGET SYSTEM/SYSTEM@TEST
CONNECTED TO TARGET DATABASE :TEST(DBID=1847282363)

Make sure the tablespace under question is in read only mode

RMAN>CONVERT TABLESPACE ‘USERS’ TO PLATFORM =’Linux IA (32-bit)’

DB_FILE_NAME_CONVERT=’C:\ORACLE\DUMP\USERS.DBF’,’C:\ORACLE\DUMP\USERDT’;

A binary file will be created which can be recognized by Target platform OS.

Or You can do the same thing in following way as well

RMAN>CONVERT TABLESPACE USERS TO PLATFORM=’Microsoft Windows IA (32-bit)’
FORMAT=’C:\ORACLE\BKUP\%N_%F’ PARALLELISM=1

Note

The above one I just tested to simulate file conversion and see how it is doing.As I a matter of fact I don’t have to convert as my both platforms same endianness.

Starting backup at 21-MAR-05
Using channel ORA-DISK_1
Channel ORA_Disk_1 : starting datafile conversion
Input datafile fn=00007 name=C:\ORACLE\DATABASE\USERS.DBF
Converted datafile=C:\ORACLE\BKUP\DUMP\USERS_7
Channel ORA_DISK_1=datafile conversion complete ,elapsed time : 00:00:07
Finished backup at 21-MAR-05

Once you got the file converted ,simply copy it to the destination OS and run IMPDP.

C:> IMPDP DIRECTORY=DATAPUMP
TRANSPORT_DATAFILES='C:\ORACLE\DATABASE\BKUP\USER_7' DUMPFILE=EXP_TS.DMP

USERID : SYS/XXX AS SYSDBA

CONNECTED TO ORACLE 10G ENTERPRISE EDITION RELEASE 10.1.0.20. -PRODUCTION
WITH PARTIITON ,OLAP AND DATAMINING OPTIONS

Master table "SYS.SYS_IMPORT_TRANSPORTTABLE_01" successfully loaded/unloaded
starting "sys".SYS_IMPORT_TRANSPORTABLE_01": SYS/SYS AS SYSDBA DIRECTORY=DATAPUMP
TRANSPORT_DATAFILES='C:\ORACLE\BKUP\USER_7' DUMPFILE=EXP_TS.DMP

PROCESSING OBJECT tYPE TRANSPORT_EXPORT_PLUGTS_BLK
PROCESSING OBJECT TYPE TRANSPORT_EXPORT/TABLE
PROCESSING OBJECT TYPE TRANSPORTABLE_EXPORT/TTE_POSINST/PLUGTS_BLK
JOB "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:34

In essence, with transportable tablespaces in 10g you can now make better use of various hardware and OS mixes.

Conclusions

Though Oracle Transportable tablespaces is a nice feature for moving data from one database to another database and only limitation of this process is that the source tablespaces need to be in read only mode while the files being transferred,But In real 24*7 databases it might not be feasible solution For Example in OLTP systems the tables are subject to continuous read /write operations.Hope Oracle will remove this restriction in its fore coming versions.

Statspack

Database Statistics

Statspack is different from BSTAT/ESTAT
1. Statspack collects more data, including high-resource SQL
2. It precalculates many ratios such as cache hit ratios, rates and transaction statistics
3. It uses permanent tables owned by PERFSTAT user to store performance stats
4. It separates data collection from report generation
5. It makes data collection easy to automate using either DBMS_JOB or an OS utility to schedule collection
6. tasks

Different levels of Statspack for monitoring:

level 0 General Performance Stats : Wait stats, system events, system stats, rollback seg
data, row cache, SGA, bg events, lock stats, buffer pool stats and parent latch stats

level 5 SQL statements with high level resource usage. Larger the shared pool more time it takes to
take a snapshot.
1. No of executions of the SQL statement. Default: 100
2. No of disk reads performed by the SQL statement. Default: 1,000
3. No of parse calls performed by the SQL statement. Default: 1,000
4. No of buffer gets performed by the SQL statement. Default: 10,000
5. Size of the sharable memory used by the SQL statement. Default: 1 MB
6. Version count for the SQL statement. Default: 20

level 6 All the statistics + SQL Plans & SQL Plan usage. Pre-requisite is, the plan of the statement
which is to be gathered has to be in the shared pool at the time that snapshot is taken and it
must exceed one of the SQL thresholds & specify the executions thresholds to 0 for those
snapshots.

level 7 Lists all the segs that are heavily accessed and contended, thus, modifying the physical layout
of some seg or of the tablespaces they reside in.
1. Logical Reads
2. DB block changes
3. Physical Reads. Default: 10,000
4. Physical Writes. Default: 1,000
5. Physical Reads direct
6. Physical Writes direct
7. Global cache consistent read blocks served (RAC specific). Default: 1,000
8. Global cache current blocks served (RAC specific). Default: 1,000
9. Buffer busy waits. Default: 100
10. ITL waits. Default: 100
11. Row lock waits. Default: 100

level 10 Parent & child latch information, session specific stats can be calculated by specifying the
session id in the call to the Statspack.

To install Statspack run the following script: SPCREATE.SQL
To remove Statspack run the following script : SPDROP.SQL
Statspack reporting script: SPREPORT.SQL
Statspack reporting script for the specific SQL hash value specified: SPREPSQL.SQL
Statspack reporting script for the database and instance specified: SPREPINS.SQL
Statspack script for automating Statspack stats collection: SPAUTO.SQL
Statspack script for converting data from 9.0 to 9.2. Before running this backup the schema: SPUP90.SQL
Statspack script for upgrading data from 8.1.7: SPUP817.SQL
Statspack script for upgrading data from 8.1.6: SPUP816.SQL
Statspack script for purging a limited range of snapshot IDs for a given database instance: SPPURGE.SQL
Statspack script for truncating all performance data in Statspack tables: SPTRUNC.SQL
Statspack documentation: SPDOC.TXT

How to install and take a snapshot:
Set timed_statistics to TRUE to get the time at which the data was collected.

SQL> CONNECT / AS SYSDBA
SQL> define default_tablespace='TOOLS'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='my_perfstat_password'
SQL> @?/rdbms/admin/spcreate
SQL> CONNECT perfstat/my_perfstat_password
SQL> EXECUTE statspack.snap;

How to create a Statspack Report Without Prompts
SQL> connect perfstat/my_perfstat_password
SQL> define begin_snap=1
SQL> define end_snap=2
SQL> define report_name=batch_run
SQL> @?/rdbms/admin/spreport

How to gather optimizer stats on the PERFSTAT Schema:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNAME=>’PERFSTAT’, CASCADE=.TRUE):
or
EXECUTE DBMS_UTILITY.ANALYZE_SHCEMA(‘PERFSTAT’,’COMPUTE’);

Where to find the snapshot level and threshold information used by the package: STATS$STATSPACK_PARAMETER

Temporarily using new values at snapshot level and take a snapshot:
EXECUTE STATSPACK.SNAP(i_snap_level=>6);

Saving the values of the snapshot level permanently and take a snapshot:
EXECUTE STATSPACK.SNAP(i_snap_level=>6, i_modify_parameter=>’true’);

Saving the values of the snapshot level permanently without taking a snapshot:
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER
(i_snap_level=>6, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);

Sunday, March 19, 2006

Oracle&Oracle

Oracle&Oracle: "Sridhar Kasukurthi "

10 steps for cloning a database.

10 steps for cloning a database.
The following steps must be on Source -Production instance/Server
Step 1. Shutdown database in normal mode and start it up in restricted mode.
Step 2. Take the backup of control file
Step 3. Shutdown database again in normal mode.
Step 4. Copy /FTP init parameter file ,control file script and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.

The following step must be on destination – Clone instance/server
Step 5. Edit init parameter file and control file script.
Step 6. New Environment setup .
Step 7. Connect with svrmgrl and recreate control file
Step 8. Open the database in resetlogs.
Step 9. Shutdown the database in normal mode
Step 10. Take the cold backup and start the database in archive/non archive mode.

Details :-
Step 1 – 3 on the Source Server ( Server A)

SVRMGR> connect internal/oracle
Connected.
SVRMGR> shutdown ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup restrict
ORACLE instance started.
Total System Global Area 57124108 bytes
Fixed Size 70924 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR>
SVRMGR> show parameter user_dump_dest
NAME TYPE VALUE
----------------------------------- ------- -------------------------
user_dump_dest string E:\ora816\admin\ora816\udump

Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora.trc . Rename this file to Ctrlprod.sql

SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Copy all the parameter files (initsid.ora, configsid.ora ), Control files, Ctrlprod.sql ( created as above) and all data files to clone server . It is good to follow the OFA. If you are cloning a database on the same machine then create a different directory structure ( as OFA) for cloning instance and copied all the required files of primary instance here.

Step 4a : On Unix ( Let us say your cloning instance name is clone)
If you had followed OFA , your initsid.ora parameters files would be in $ORACLE_BASE/admin/clone/pfile/initclone.ora
and configsid.ora would be in $ORACLE_BASE/admin/clone/pfile/configclone.ora.

Create a symbolic link

Change the working directory to $ORACLE_HOME/dbs and create a symbolic link
$ ln –s $ORACLE_BASE/admin/clone/pfile/initclone.ora initclone.ora

Step 5 : On destination server

Change the following parameter in the initsid.ora of the cloning instance/server

InitSid.ora (initora816.ora) of the Source/production server.

InitSid.ora (initclone.ora) of the
destination/clone server.

db_name = "ora816"
instance_name = ora816
service_names = ora816
control_files = ("e:\ora816\oradata\ora816\control01.ctl", "f:\ora816\oradata\ora816\control02.ctl", "g:\ora816\oradata\ora816\control03.ctl")
Db_name= “clone”
Instance_name= clone
Service_name = clone
Control_file = (“C:\clone\control01.ctl”,”D:\clone\control02.ctl”)

The other parameter which is required to change is user_dump_dest, background_dump_dest,log_archive_dest

Oraxxxx.trc is copied from source/production instance to destination/clone instance and renamed it as ctrlclone.sql .

Edit ctrlclone.sql as follows.

Oraxxxx.trc in user dump dest of Source/Production instance

Ctrlclone.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA816" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'E:\ORA816\ORADATA\ORA816\REDO03.LOG' SIZE 1M,
GROUP 2 'E:\ORA816\ORADATA\ORA816\REDO02.LOG' SIZE 1M,
GROUP 3 'E:\ORA816\ORADATA\ORA816\REDO01.LOG' SIZE 1M
DATAFILE
'E:\ORA816\ORADATA\ORA816\SYSTEM01.DBF',
'E:\ORA816\ORADATA\ORA816\RBS01.DBF',
'E:\ORA816\ORADATA\ORA816\TEMP01.DBF',
'E:\ORA816\ORADATA\ORA816\TOOLS01.DBF',
'E:\ORA816\ORADATA\ORA816\INDX01.DBF',
'E:\ORA816\ORADATA\ORA816\DR01.DBF',
'E:\ORA816\DATABASE\NGAR5ORA81601.DBF',
'E:\ORA816\DATABASE\RBSTEST01.DBF'
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;

/* Delete everything upto startup nomount */
STARTUP NOMOUNT pfile=f:\clone\initclone.ora
CREATE CONTROLFILE set DATABASE "CLONE" resetlogs noarchivelog
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'F:\CLONE\ORA816\REDO03.LOG'
SIZE 1M,
GROUP 2 'F:\CLONE\ORA816\REDO02.LOG'
SIZE 1M,
GROUP 3 'F:\CLONE\ORA816\REDO01.LOG'
SIZE 1M
DATAFILE
'F:\CLONE\ORA816\SYSTEM01.DBF',
'F:\CLONE\ORA816\RBS01.DBF',
'F:\CLONE\ORA816\TEMP01.DBF',
'F:\CLONE\ORA816\TOOLS01.DBF',
'F:\CLONE\ORA816\INDX01.DBF',
'F:\CLONE\ORA816\DR01.DBF', 'F:\CLONE\DATABASE\NGAR5ORA81601.DBF',
'F:\CLONE\DATABASE\RBSTEST01.DBF'
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
#RECOVER DATABASE
# All logs need archiving and a log switch is needed.
#ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
#ALTER DATABASE OPEN;

STEP 6 :-

ON NT :- You have to create a windows NT services as follows.

C:\>oradim -new -sid clone -srvc oracleserviceClone -intpwd oracle -startmode auto -pfile f:\clone\initclone.ora

ON Unix :-

(A) (A) Change the working directory to /etc and edit the oratab file to put the entry for CLOBE instance.
(B) Setup login profile for the Oracle user having dba group.
ORACLE_SID=CLONE
Export ORACLE_SID
Or .oraenv

STEP 7 :- Create controlfile as follows :- Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid passwordfile
should exist in ORACLE_HOME/dbs or created using orapwd as
orapwd file=/u01/oracle/V816/dbs/orapwV722 password=oracle entries=1
where /u01/oracle/V816 is an oracle home

SVRMGR> @f:\clone\ctrlclone.sql
ORACLE instance started.
Total System Global Area 57123804 bytes
Fixed Size 70620 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Statement processed.

STEP 8 :- Open the database in resetlog mode as follows.

SVRMGR> alter database open resetlogs;
Statement processed.

STEP 9 :- Shutdown the database in Normal mode

SVRMGR> connect internal/oracle
Connected.
SVRMGR> shutdown ;
Database closed.
Database dismounted.
ORACLE instance shut down.

STEP 10 :- Take the cold backup and start the database in archive/non archive mode.

Note :-
You cannot move datafiles from one operating system and use them on a different operating system. Therefore, it is not possible to clone a database running on SUN on an HP machine. You would have to use export/import.

Parsing in Oracle

Parsing in Oracle
Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.

Types of Parsing
All statements, DDL or DML, are parsed whenever they are executed. The only key fact is that whether it was a Soft (statement is already parsed and available in memory) or a Hard (all parsing steps to be carried out) parse. Soft parse will considerably improve the system performance where as frequent Hard parsing will affect the system. Reducing Hard parsing will improve the resource utilization and optimize the SQL code.

Parsing process
Oracle internally does the following to arrive at the output of an SQL statement.
1. Syntactical check. The query fired is checked for its syntax.
2. Semantic check. Checks on the validity of the objects being referred in the statement and the privileges available to the user firing the statement. This is a data dictionary check.
3. Allocation of private SQL area in the memory for the statement.
4. Generating a parsed representation of the statement and allocating Shared SQL area. This involves finding an optimal execution path for the statement.
In point four, Oracle first checks if the same statement is already parsed and existing in the memory. If found, the parsed representation will be picked up and the statement executed immediately (Soft parse). If not found, then the parsed representation is generated and stored in a shared SQL area (Part of shared pool memory in SGA), the statement is then executed (Hard parse). This step involves the optimization of the statement, the one that decides the performance.

Identical statements
Oracle does the following to find identical statements to decide on a soft or a hard parse.
a. When a new statement is fired, a hash value is generated for the text string. Oracle checks if this new hash value matches with any existing hash value in the shared pool.
b. Next, the text string of the new statement is compared with the hash value matching statements. This includes comparison of case, blanks and comments present in the statements.
c. If a match is found, the objects referred in the new statement are compared with the matching statement objects. Tables of the same name belonging to different a schema will not account for a match.
d. The bind variable types of the new statement should be of same type as the identified matching statement.
e. If all of the above is satisfied, Oracle re-uses the existing parse (soft). If a match is not found, Oracle goes through the process of parsing the statement and putting it in the shared pool (hard).
Reduce hard parsing
The shared pool memory can be increased when contention occurs, but more important is that such issues should be addressed at the coding level. Following are some initiatives that can be taken to reduce hard parsing.
1. Make use of bind variables rather than hard-coding values in your statements.
2. Write generic routines that can be called from different places. This will also eliminate code repetition.
3. Even with stringent checks, it may so happen that same statements are written in different formats. Search the SQL area periodically to check on similar queries that are being parsed separately. Change these statements to be look-alike or put them in a common routine so that a single parse can take care of all calls to the statement.
Identifying unnecessary parse calls at system level

select parse_calls, executions,substr(sql_text, 1, 300) from v$sqlarea where command_type in (2, 3, 6, 7);
Check for statements with a lot of executions. It is bad to have the PARSE_CALLS value in the above statement close to the EXECUTIONS value. The above query will fire only for DML statements (to check on other types of statements use the appropriate command type number). Also ignore Recursive calls (dictionary access), as it is internal to Oracle.

Identifying unnecessary parse calls at session level

select b.sid, a.name, b.value from v$sesstat b, v$statname a where a.name in ('parse count (hard)', 'execute count') and b.statistic# = a.statistic# order by sid;
Identify the sessions involved with a lot of re-parsing (VALUE column). Query these sessions from V$SESSION and then locate the program that is being executed, resulting in so much parsing.select a.parse_calls, a.executions, substr(a.sql_text, 1, 300)from v$sqlarea a, v$session bwhere b.schema# = a.parsing_schema_idand b.sid = <:sid>order by 1 desc;
The above query will also show recursive SQL being fired internally by Oracle.
4. Provide enough private SQL area to accommodate all of the SQL statements for a session. Depending on the requirement, the parameter OPEN_CURSORS may need to be reset to a higher value. Set the SESSION_CACHED_CURSORS to a higher value to allow more cursors to be cached at session level and to avoid re-parsing.
Identify how many cursors are being opened by sessions

select a.username, a.sid, b.valuefrom v$session a, v$sesstat b, v$statname cwhere b.sid = a.sidand c.statistic# = b.statistic#and c.name = 'opened cursors current'order by 3 desc;
The VALUE column will identify how many cursors are open for a session and how near the count is to the OPEN_CURSORS parameter value. If the margin is very small, consider increasing the OPEN_CURSORS parameter.

Evaluate cached cursors for sessions as compared to parsing

select a.sid, a.value parse_cnt, (select x.value from v$sesstat x, v$statname y where x.sid = a.sid and y.statistic# = x.statistic# and y.name = 'session cursor cache hits') cache_cntfrom v$sesstat a, v$statname bwhere b.statistic# = a.statistic#and b.name = 'parse count (total)'and value > 0;
The CACHE_CNT ('session cursor cache hits') of a session should be compared to the PARSE_CNT ('parse count (total)'), if the difference is high, consider increasing the SESSION_CACHED_CURSORS parameter.
The following parse related information is available in V$SYSSTAT and V$SESSTAT views, connect with V$STATNAME using STATISTIC# column.

SQL> select * from v$statname where name like '%parse%';

STATISTIC# NAME CLASS

---------- ------------------------- ----------

217 parse time cpu 64

218 parse time elapsed 64

219 parse count (total) 64

220 parse count (hard) 64

221 parse count (failures) 64

5. Shared SQL area may be further utilized for not only identical but also for some-what similar queries by setting the initialization parameter CURSOR_SHARING to FORCE. The default value is EXACT. Do not use this parameter in Oracle 8i, as there is a bug involved with it that hangs similar query sessions because of some internal processing. If you are on 9i, try out this parameter for your application in test mode before making changes in production.
6. Prevent large SQL or PL/SQL areas from ageing out of the shared pool memory. Ageing out takes place based on Least recently used (LRU) mechanism. Set the parameter SHARED_POOL_RESERVED_SIZE to a larger value to prevent large packages from being aged out because of new entries. A large overhead is involved in reloading a large package that was aged out.
7. Pin frequent objects in memory using the DBMS_SHARED_POOL package. This package is created by default. It can also be created explicitly by running DBMSPOOL.SQL script; this internally calls PRVTPOOL.PLB script. Use it to pin most frequently used objects that should be in memory while the instance is up, these would include procedure (p), functions (p), packages (p) and triggers (r). Pin objects when the instance starts to avoid memory fragmentation (Even frequently used data can be pinned but this is a separate topic).
To view a list of frequently used and re-loaded objects

select loads, executions, substr(owner, 1, 15) "Owner", substr(namespace, 1, 20) "Type", substr(name, 1, 100) "Text" from v$db_object_cache order by executions desc;
To pin a package in memory

SQL>exec dbms_shared_pool.keep('standard', 'p');
To view a list of pinned objectsselect substr(owner, 1, 15) "Owner",substr(namespace, 1, 20) "Type",substr(name, 1, 100) "Text" from v$db_object_cache where kept = 'YES';
8. Increasing the shared pool size is an immediate solution, but the above steps need to be carried out to optimize the database in the long run. The size of the shared pool can be increased by setting the parameter SHARED_POOL_SIZE in the initialization file.

Conclusion
Reduce Hard parsing as much as possible! This can be done by writing generic routines that can be called from different parts of the application, thus the importance of writing uniform and generic code.

MANAGING JOB QUEUES

MANAGING JOB QUEUES

DBMS_JOB Package:
There are no database privileges associated with using job queues. Any
user who can execute the job queue procedures can use the job queue.

Procedures:
SUBMIT: Submits a job to the job queue.
REMOVE: Removes a specified job from the job queue.
CHANGE: Alters a specified job that has already been submitted to the job
queue. You can alter the job description, the time at which the
job will be run, or the interval between executions of the job.
WHAT: Alters the job description for a specified job.
NEXT_DATE :Alters the next execution time for a specified job.
INTERVAL: Alters the interval between executions for a specified job.
BROKEN: Sets or resets the job broken flag. If a job is marked as broken,
Oracle does not attempt to execute it.

RUN: Forces a specified job to run.

Submitting a job to the Job Queue
To submit a new job to the job queue, use the SUBMIT procedure.
Parameter:
JOB : An output parameter. This is the identifier assigned to the job
you are creating. You must use this job number whenever you
want to alter or remove the job.
WHAT : This is the PL/SQL code you want to have executed.
NEXT_DATE : This is the next date when the job will be run. The default value
is SYSDATE.

INTERVAL : This is the date function that calculates the next time to execute
the job. The default value is NULL. INTERVAL must evaluate to a
future point in time or NULL.

NO_PARSE : This is a flag. If NO_PARSE is set to FALSE (the default), Oracle
parses the procedure associated with the job. If NO_PARSE is set
to TRUE, Oracle parses the procedure associated with the job the
first time that the job is executed. If, for example, you want to
submit a job before you have created the tables associated with
the job, set NO_PARSE to TRUE.

Ex: submit a new job to the job queue, then prints the job number.
The job is run every 24 hours.

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''HR'',''EMPLOYEES'',''ESTIMATE'', NULL, 50);',SYSDATE,'SYSDATE + 1');
COMMIT;
END;
/

PRINT jobno
JOBNO
----------
14144

For the submitted job to run, you must issue a COMMIT statement immediately after the DBMS_JOB.SUBMIT statement.

Job Environment
When you submit a job to the job queue or alter a job’s definition, Oracle records:
The current user
The user submitting or altering a job
The current schema (may be different from current user or submitting user if
ALTER SESSION SET CURRENT_SCHEMA statement has been issued)

NLS_LANGUAGE
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT

Oracle restores all of these environment characteristics every time a job is executed.
NLS_LANGUAGE and NLS_TERRITORY parameters determine the defaults for unspecified NLS parameters.

You can change a job’s environment by using the DBMS_SQL package and the ALTER SESSION statement.

Job Owner
When you submit a job to the job queue, Oracle identifies you as the owner of the
job. Only a job’s owner can alter the job, force the job to run, or remove the job from
the queue.

Job Number
A queued job is identified by its job number. When you submit a job, its job number
is automatically generated from the JOBSEQ sequence owned by user SYS.

Job Definition
The job definition is the PL/SQL code specified in the WHAT parameter of the SUBMIT
procedure. Normally, the job definition is a single call to a procedure. The procedure
call can have any number of parameters.

In the job definition, use two single quotation marks around
strings. Always include a semicolon at the end of the job definition.

examples of valid job definitions:
'myproc(''10-JAN-99'', next_date, broken);'
'scott.emppackage.give_raise(''JFEE'', 3000.00);'
'dbms_job.remove(job);'

Running a job from a job is not supported.
"ORA-32317 cannot run a job from another job" error message in the alert file.

Job Execution Interval
If a job should be executed periodically at a set interval, use a date expression
similar to 'SYSDATE + 7' in the INTERVAL parameter.

The INTERVAL date function is evaluated immediately before a job is executed.
When the job completes successfully, the date calculated from INTERVAL becomes
the new NEXT_DATE. For example, if you set the execution interval to 'SYSDATE +
7' on Monday, but for some reason (such as a network failure) the job is not
executed until Thursday, 'SYSDATE + 7' then executes every Thursday, not
Monday. If the INTERVAL date function evaluates to NULL and the job completes
successfully, the job is deleted from the queue.

Database Links and Jobs:
If you submit a job that uses a database link, the link must include a username and
password. Anonymous database links will not succeed.

How Jobs Execute
Jnnn processes execute jobs.
To execute a job, the process creates a session to run the job.
When a Jnnn process runs a job, the job is run in the same environment in which it was submitted and with the owner’s default privileges.

When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process and with your default privileges only.
Privileges granted to you through roles are unavailable.
You must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

Job Queue Locks:
Oracle uses job queue locks to ensure that a job is executed in only one session at a time.
When a job is being run, its session acquires a job queue (JQ) lock for that job.

You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ';

SID TY ID1 ID2
--------- -- --------- ---------
12 JQ 0 14144

ID1 column is always 0 for JQ locks.
ID2 column is the job number of the job the session is running.

Job Execution Errors
When a job fails, information about the failure is recorded in a trace file and the alert log.
ORA-12012 and includes the job number of the failed job.

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again.

The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling
between each attempt.

If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

Removing a job from the Job Queue
To remove a job from the job queue,
BEGIN
DBMS_JOB.REMOVE(14144);
END;
/

Restrictions:
You can remove currently executing jobs from the job queue.
The job will not be interrupted, and the current execution will be completed.

You can remove only jobs you own.
If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

Altering a Job
To alter a job that has been submitted to the job queue, use the procedures
CHANGE,
WHAT,
NEXT_DATE,
INTERVAL

Restriction:
You can alter only jobs that you own.
If you try to alter a job that you do not own, you receive a message that states the job is not in the job queue.

CHANGE:
You can alter any of the user-definable parameters associated with a job by calling the DBMS_JOB.CHANGE procedure.

ex:
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/

If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the
procedure DBMS_JOB.CHANGE, the current value remains unchanged.

When you change a job’s definition using the WHAT parameter in the procedure DBMS_JOB.CHANGE, Oracle records
your current environment. This becomes the new environment for the job.

WHAT:
You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure.

ex:
BEGIN
DBMS_JOB.WHAT(14144,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''DEPARTMENTS'',
''ESTIMATE'', NULL, 50);');
END;
/

When you execute the procedure DBMS_JOB.WHAT, Oracle records your current environment. This becomes the new
environment for the job.

NEXT_DATE:
You can alter the next execution time for a job by calling the DBMS_JOB.NEXT_DATE procedure,

BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/

INTERVAL:
BEGIN
DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/

In this case, the job will not run again after it successfully executes and it will be
deleted from the job queue.

Broken Jobs
A job is labeled as either broken or not broken.
Oracle does not attempt to run broken jobs.

you can force a broken job to run by calling the procedure DBMS_JOB.RUN.

How a Job Becomes Broken:

When you submit a job it is considered not broken.
There are two ways a job can break:
1) Oracle has failed to successfully execute the job after 16 attempts.
2) You have marked the job as broken, using the procedure DBMS_JOB.BROKEN:

BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/

Once a job has been marked as broken, Oracle will not attempt to execute the job
until you either mark the job as not broken, or force the job to be executed by calling
the procedure DBMS_JOB.RUN.

The following example marks job 14144 as not broken and sets its next execution
date to the following Monday:
BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/

Restriction:
You can mark as broken only jobs that you own.

Running Broken Jobs
If a problem has caused a job to fail 16 times, Oracle marks the job as broken.
1) Forcing the job to run by calling DBMS_JOB.RUN
2) Marking the job as not broken by calling DBMS_JOB.BROKEN and waiting for Oracle to execute the job

If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the
job immediately. If the job succeeds, then Oracle labels the job as not broken and
resets its count of the number of failed executions for the job to zero.
Once you reset a job’s broken flag (by calling either RUN or BROKEN), job execution
resumes according to the scheduled execution intervals set for the job.

Forcing a Job to Execute
To force a job to execute immediately, use the procedure RUN in the DBMS_JOB package.
When you run a job using DBMS_JOB.RUN, Oracle recomputes the next execution date.

The following statement runs job 14144 in your session and recomputes the next execution date:
BEGIN
DBMS_JOB.RUN(14144);
END;
/

When you force a job to run, the job is executed in your current session. Running the job reinitializes your session’s packages.

Restrictions:
You can only run jobs that you own. If you try to run a job that you do not own,
you receive a message that states the job is not in the job queue.
The procedure RUN contains an implicit commit. Once you execute a job using
RUN, you cannot roll back.

Terminating a Job
You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session.

You should mark the job as broken, so that Oracle does not attempt to run the job again.

After you have identified the session running the job (using V$SESSION or V$LOCK, as shown earlier), you can disconnect the session using the SQL statement ALTER SYSTEM.

Viewing Job Queue Information
DBA_JOBS
DBA view describes all the jobs in the database.

ALL_JOBS
ALL view describes all jobs that are accessible to the current user.

USER_JOBS
USER view describes all jobs owned by the current user.

DBA_JOBS_RUNNING
Lists all jobs in the database that are currently running.
This view can be joined with V$LOCK to identify jobs that have locks.

________________________________________________________________________________
Displaying Information About a Job:
________________________________________________________________________________

Query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;

JOB NEXT_DATE NEXT_SEC FAILURES B
------- --------- -------- -------- -
9125 01-JUN-01 00:00:00 4 N
1414 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y

________________________________________________________________________________
Displaying Information About Running Jobs
________________________________________________________________________________
The following query lists the session identifier, job number, user who submitted the job,
and the start times for all currently running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;

SID JOB LOG_USER THIS_DATE THIS_SEC
----- ---------- ------------- --------- --------
12 14144 HR 24-OCT-94 17:21:24
25 8536 QS 24-OCT-94 16:45:12