Oracle 10g installation on Ubuntu Linux

Steps followed for oracle installation and dbcreation for perf db in cj on ubuntu 8.04

Step One: -

Install some system requirements. There are a few packages that I had to install on this box (it was a recently installed system which didn’t have all these packages). After several attempts of installing Oracle, the equivalent command-line for installing all the necessary packages at once was something like this:
# apt-get install gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5 libstdc++5-3.3-dev libstdc++5 libc6-dev-i386 g++-multilib ia32-libs libc6-dev g++-4.1 xscreensaver
It’s possible that when installing the packages mentioned above, the installer will install some other prerequisites as well, as these packages themselves may have prerequisites.

Step Two: -

Choose where you are going to install your Oracle 10g server and create the ORACLE_BASE directory. This is the place where Oracle will be installed. Make sure there is at least 3 GB on the partition/mount point before moving to the next step. After installed, my basic installation took about 3.4 GB on disk (without the starter database!). As your database grows, it will need more space. Reserve a total of at least 6 GB for the unpacked installer and the basic installation. You can get rid of the installer files afterwards.
# mkdir -p /home/oracle/


Step Three: -

Add a few users and change groups to make the installer more comfortable. Remember, we are tricking the installer to think it’s installing on a Red Hat box.
# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
The usermod command is needed since because when running, the installer looks for a user called nobody which is part of a group named nobody (in Ubuntu, the user nobody it’s assigned to nogroup by default).

Step Four: -

Make some symlinks. Apparently, the installer uses absolute paths, so it must find the binaries in the right places.
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename

Step Five: -

We need to mimic the /etc/rc.d directory structure of a Red Hat box. We do this with more symlinks:
# mkdir /etc/rc.d
# ln -s /etc/rc0.d /etc/rc.d/rc0.d
# ln -s /etc/rc2.d /etc/rc.d/rc2.d
# ln -s /etc/rc3.d /etc/rc.d/rc3.d
# ln -s /etc/rc4.d /etc/rc.d/rc4.d
# ln -s /etc/rc5.d /etc/rc.d/rc5.d
# ln -s /etc/rc6.d /etc/rc.d/rc6.d
# ln -s /etc/init.d /etc/rc.d/init.d

Step Six: -

I’ve created a file called /etc/redhat-release and put only one line on it. The same can be achieved by issuing the following as root:
echo "Red Hat Linux release 4" > /etc/redhat-release

Step Seven: -

We tweak the system default limits on a few items. The shared-memory are specially important, since Oracle relies on shared memory for process communications. There is a file called /etc/sysctl.conf and it should have these lines on it:
fs.file-max = 65535
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Now that they are in a config file, these limits will be issued automatically at the next boot sequence. For now, we need to make the system re-read the config file:
# sysctl -p
Now, what do those parameters and values actually mean?
fs.file-max sets the maximum number of open files that can be handled by the Linux kernel.
kernel.shmall determines the total amount of shared memory to be allocated in pages. In this example, I’ve set it to 8GB, which is way above the amount of memory I can handle in my box, even with swap.
kernel.shmmax controls the maximum amount of memory to be allocated for shared memory which in this example is 2GB.
kernel.shmmni defines the maximum number of segments system-wide.
net.core.rmem_default and net.core.rmem_max define the default and maximum read buffer queue for network operations (1 MB in this example)
net.core.wmem_default and net.core.wmem_max define the default and maximum write buffer queue for network operations (256 KB in this example)
net.ipv4.ip_local_port_range tells the kernel the port ranges that will be used for outbound connections.
kernel.sem has four parameters:
1.SEMMSL - semaphores per array
2.SEMMNS - max semaphores system-wide (SEMMNI*SEMMSL)
3.SEMOPM - max operations per semop call
4.SEMMNI - max number of semaphore arrays
To check your current semaphores configuration, you can run cat /proc/sys/kernel/sem or ipcs -ls. On machine, after the modifications on sysctl.conf, these commands output:
# cat /proc/sys/kernel/sem
250 32000 100 128

# ipcs -ls

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767
(I really don’t know if these are enough or too much, but I’ll keep you posted.)
For a better understanding of these kernel-tweaking settings, I’d recommend these resources:
schogini.us/wordpress/index.php/2005/11/01/setting-semaphores/
performancewiki.com/linux-tuning.html
pythian.com/blogs/245/the-mysterious-world-of-shmmax-and-shmall

Step Eight: -

Add these lines to /etc/security/limits.conf, letting the oracle user use more resources than the defaults allowed. You may notice that all these values are a power of 2 minus one. When soft limits are exceeded, you’ll get a warning; the hard limits can’t be exceeded in any situation: you’ll get an error. I’m not completely sure, but I think these limits apply to each session/login (and since Oracle doesn’t exactly log in to the machine, my best guess is these limits apply per instance running).
oracle soft nproc 2047
oracle hard nproc 16383
oracle soft nofile 1023
oracle hard nofile 65535

Step Nine: -

Make sure the limits.conf is being interpreted as the oracle user logs in by adding these lines to /etc/pam.d/login. You will want to make sure that is actually happening, since the defaults are way lower and you may get all sorts of problems.
session required /lib/security/pam_limits.so
session required pam_limits.so

Step Ten: -

Unpack and prepare the installation.

Directions
1.Unzip the file: gunzip
2.2. Extract the file: cpio -idmv <

# cd /path/to/zipfile
# unzip 10201_database_linux_x86_64.cpio.gz
# cpio –idmv 10201_database_linux_x86_64.cpio.gz

(And wait… wait a bit more… go get a cup of coffee…)
After your second cup of coffee, you should have a multi-gigabyte set of files; this is our installer.
# chown -R oracle:oinstall /oracle
# chown -R oracle:oinstall /u01
# chown -R oracle:oinstall /u02


# sudo chmod -R 775 /oracle
# sudo chmod -R 775 /u01
# sudo chmod -R 775 /u02

Step Eleven: -

Fire up the installer as the oracle user itself. This is what you will probably see on the output window:
# su - oracle
$ cd /path/to/extracted/zip/file
$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB. Actual 58633 MB Passed
Checking swap space: must be greater than 150 MB. Actual 2900 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-07-11_04-38-56PM. Please wait ...
Oracle Universal Installer, Version 11.1.0.2.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

ulimit: 1: Illegal option -u
ulimit: 1: Illegal option -u
rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm
rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm
There are a few errors that can be safely ignored: the ulimit and the RPM-related errors, since the limits don’t restrict the installer and since we actually don’t have a RPM database on the machine — we are running on Ubuntu, remember?
After a few moments, you will be prompted to choose where to install the Oracle server. You’ll notice that I asked the installer to create a starter database — I can also create that later.
Choose the Oracle Base and correct the group if needed.
I personally recommend sticking with the defaults if you are a newbie like me.


./runInstaller

Graphical mode
For the graphical mode, no more simply, just follow the instructions if you have no idea of what you are doing, default options works fine.

Victory, now you can see that:

Warning : When you'll see the following image, execute the script that it demand
to.

At some point, it will ask you to run some commands as root. Do that when it asks, since the install depends on a few modifications on the base system (like creating the /etc/oratab file).

In a different console.
$ sudo -i
Password:

# /u01/app/oracle/oraInventory/orainstRoot.sh

Changing permissions of /oracle/app/oracle/oraInventory to 770.

Changing groupname of /oracle/app/oracle/oraInventory to oinstall.
The execution of the script is complete

# /oracle/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/app/oracle/product/10.2.0/db_1
[: 185: ==: unexpected operator
[: 189: ==: unexpected operator
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
After these scripts finish their execution (the errors seem to be ignorable)


Finally, you have finished the installation:

To finish
The installation is done, we have to reboot the system to kill some installation's daemons. After, you'll have to do the following to launch the Oracle server:
$ sudo su oracle
Password: enter your sudo password
[oracle] $ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JAN-2006 13:55:02

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /u01/app/oracle/oracle/product/10.2.0/db_1//bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admi n/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/l istener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(P ORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 19-JAN-2006 13:55:04
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_1/network/adm in/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_1/network/log /listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle] $ sqlplus connect as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 19 13:55:21 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password: enter the DB password
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle] $ isqlplusctl start
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.

[oracle] $ emctl start dbconsole
TZ set to Indian/Reunion
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ........................ started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/oracle/product/10.2.0/db_1/localhost.localdomain_orcl/sysman/log

Database Upgrade from 8i to 9i

1)Make sure that you have 9iR2 software (9.2.0 CD Dump) before starting the installation.

first need to download the software on the server. Following are the details of the software to be downloaded:

i. 9.2.0.1 for Solaris:

Disk1: Part# A99349-01
Disk2: Part# A99350-01
Disk3: Part# A99351-01

ii. 9.2.0.1 for Linux

Disk1: Part# A99339-01
Disk2: Part# A99340-01
Disk3: Part# A99341-01

2) Login to oracle user and make sure that you have minimum 4 GB free space in /u01/app/oracle mount point. If you do not have enough free space, you need to send the TAR to SA team for adding more space to /u01/app/oracle mount point.

3) Make sure that you have oraInst.loc file in the below given locations and it has 777 privileges over it. If the file is not there or it does not have 777 privileges, you need to send the TAR to SA team for creating the file or granting 777 on it.
a. On Solaris: /var/opt/oracle/oraInst.loc
b. On Linux: /etc/oraInst.loc

4) Look out for the oraInventory on your instance. As per EBSO standards the location of oraInventory is “/u01/app/oracle/product/oraInventory”. If it is not present in this location you need to search for it. Once you know the location of oraInventory and you have privileges to update oraInst.loc file, make sure that the contents of oraInst.loc is as shown below. If the file was already existing and had some other values, just update it as shown below:
inventory_loc=/u01/app/oracle/product/oraInventory
inst_group=oistall

5) Make sure that you have /igold symbolic link pointing to /u01 on the file system.

Preparing the System for Upgrade

1) Declare blackout on all the Components of APPS Instance and shutdown all Middle-Tier services of the instance. Keep Database server and DB listener up.

2) Check the free space in SYSTEM tablespace and if it is less than 1 GB free, add another datafile to create free space. Similarly, make sure that you have minimum 750 MB free in RBS tablespace. One example of how to add datafile is given below.

SQL> select tablespace_name, round(sum(bytes)/1024/1024) free_space from dba_free_space where tablespace_name in (‘SYSTEM’,’RBS’) group by tablespace_name;

SQL> alter tablespace SYSTEM add datafile ‘/xxxxxx/oradata02/data02/systemxx.dbf’ size 1000m autoextend on next 25m maxsize 1800m;

3) Make sure that the value of maxextents for all Rollback Segments is Unlimited. Run the below given query to check this. The value of “32765” means the Unlimited size. If the value is less than 32765 then alter the rollbacks segment to make maxextents unlimited.

SQL> select segment_name, max_extents,status from dba_rollback_segs;
SQL> alter rollback segment rbsXX storage (maxextents unlimited);

4) Set the values of following parameters in initXXXXXX.ora file as given below.
db_domain =
aq_tm_processes = 0
job_queue_processes = 0
log_archive_start = false
_system_trig_enabled = FALSE

5) Search for any “event=” set in initXXXXXX.ora or ifilecbo.ora files. If you find any event, comment that entry. Also, you may not find some of the initialization parameters given above in initXXXXXX.ora file. In that case check the parameter in ifilecbo.ora file.

6) Alter the database to NOARCHIVELOG mode and shut it down. Also shutdown the DB listener.

$ sqlplus “/ as sysdba”
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> archive log list
SQL> shutdown immediate
SQL> exit
$ lsnrctl stop XXXXXX


7) Create new ORACLE_HOME and set environment for that.

a. Create new directory “920” in product directory for the new HOME
$ cd /xxxxxx/oracle/product
$ mkdir 920

b. Copy the environment file from old ORACLE_HOME (817) to new ORACLE_HOME (920)
$ cd /xxxxxx/oracle/product/920
$ cp ../817/.env .

c. Edit the environment file in new ORACLE_HOME and change all the references from “817” to “920” by performing a global replace in “vi”.
$ vi .env
:1,$ s/817/920/g

d. Edit “.profile” file and “.bash_profile” (only in Linux) and edit all the references of “817” to “920”

$ cd $HOME
$ vi .profile
:1,$ s/817/920/g
$ vi .bash_profile
:1,$ s/817/920/g

e. Log out from orxxxxxx user, login again and make sure that the following environment variables are pointing to new ORACLE_HOME i.e. “/orxxxxxx/product/920”.
$ echo $ORACLE_HOME
$ echo $LD_LIBRARY_PATH
$ echo $TNS_ADMIN

8) At this stage we are ready to perform our upgrade. Just review all the steps in these first 2 sections and make sure that you have followed all of them. Then proceed to next section and perform the upgrade.

Performing 920 Upgrade

1) Login to orxxxxxx user and make sure that the environment variables like ORACLE_HOME, TNS_ADMIN and LD_LIBRARY_PATH are pointing to new ORACLE_HOME of 920. Also, make sure that /igold symbolic link is pointing to /xxxxxx and oraInst.loc file has been correctly updated. All these things had been discussed in the previous sections of the document.

2) Start a Reflection X session and connect to orxxxxxx user using Fsecure SSH client. Run xclock to see if you can run GUI. If you are performing the upgrade from a remote location (from India) do not run the Installer from your own PC but use VNC Viewer to connect to a Desktop in the US and then run the upgrade from the US PC. In case of Your Place customers, the normal SSH session is enabled to run GUI Installers and there is no performance hit from any location. You can run the installer without opening any Reflection or VNC viewer.

3) Start “runInstaller” from Disk1 of 9.2.0.1 CD Set which has been downloaded earlier and choose the following options while installation:

a. File Locations:
ORACLE_HOME name: IGOLD920_HOME
ORACLE_HOME path=/igold/oracle/product/920
Do not give the actual location of 920 ORACLE_HOME (/xxxxxx/oracle/product/920) here. We are deliberately using “igold” as it helps in patching of cloned instances

b. Select a Product:
Oracle 9i Database 9.2.0.1.0

c. Type of Installation:
Enterprise Edition

d. Database Configuration
Software Only

4) Download 9.2.0.4 PatchSet (Patch# 3095277) and unzip it in a temporary directory and run below given cpio command. It will create a new Disk1 directory.
Solaris:
$ unzip 9204_solaris_release.cpio.z
$ cpio -idmv < 9204_solaris_release.cpio
Linux:
$ cpio -idmv < 9204_lnx32_release.cpio

5) Start “runInstaller” from /xxxxxx/oracle/product/oui directory to install 9.2.0.4 PatchSet files and choose the following options:

a. Files Locations:
Source Path: /Disk1/stage/products.jar
ORACLE_HOME name: IGOLD920_HOME
ORACLE_HOME path=/igold/oracle/product/920

b. Choose OUI installation and complete it. Exit the installer, do not choose continue with “Next Install”. You have to restart installer.

c. Start installer again with the same File Location values as given above and choose 9.2.0.4 PatchSet installation.

6) Relink Oracle executables to remove igold dependencies and verify that libraries being referenced after relinking are from correct ORACLE_HOME location and not from igold link.
$ cd $ORACLE_HOME/bin
$ ./relink all
$ ldd lsnrctl
$ ldd sqlplus
$ ldd oracle

7) Copy initXXXXXX.ora and ifilecbo.ora files from old ORACLE_HOME to new 920 ORACLE_HOME. Do not change the value of any initialization parameter; this will be done in later steps.
$ cd $ORACLE_HOME/dbs
$ cp ../../817/dbs/initXXXXXX.ora .
$ cp ../../817/dbs/ifilecbo.ora .

8) Perform the DB upgrade from 8.1.7 to 9.2.0 using the manual scripts as given below. These upgrade scripts may take 4-5 hours to complete. “startup migrate” statement will throw “ORA-32004: obsolete and/or deprecated parameter(s) specified” exception. At this point of time ignore this error it will be taken care in later steps. Once the upgrade scripts complete, query “dba_registry” table to make sure that Oracle components have been upgraded.
$ sqlplus “/ as sysdba”
SQL> startup migrate
SQL> spool db_upgrade.log
SQL> @?/rdbms/admin/u0801070.sql
SQL> spool off
SQL> spool dbcmp_upgrade.log
SQL> @?/rdbms/admin/cmpdbmig.sql
SQL> spool off
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME STATUS VERSION
Oracle9i Catalog Views VALID 9.2.0.4.0
Oracle9i Packages and Types VALID 9.2.0.4.0
JServer JAVA Virtual Machine VALID 9.2.0.4.0
Oracle9i Java Packages VALID 9.2.0.4.0
Oracle XDK for Java UPGRADED 9.2.0.2.0
Oracle interMedia Text LOADED 8.1.7.4
Oracle9i Real Application Clusters INVALID 9.2.0.4.0
Oracle interMedia LOADED 8.1.6.0.0
Oracle Spatial LOADED 8.1.6.0.0

9) Shutdown the instance, start it up again and execute utl_recomp package to recompile invalid objects using parallel workers. This may take 3-4 hours.
a. SQL> shutdown immediate
b. SQL> startup
c. SQL> @?/rdbms/admin/utlrcmp.sql
d. SQL> exec utl_recomp.recomp_parallel(6)

10) Upgrade Oracle Text, Oracle interMedia and Orace Spatial as given in the following steps. Run “catpatch.sql” script to complete the installation of 9.2.0.4 patchset. Then query dba_registry table to verify the upgrade.
a. Upgrade Oracle Spatial
SQL> spool spatial_upgrade.log
SQL> connect / as sysdba
SQL> @?/md/admin/mdprivs.sql
SQL>connect mdsys/mdsys
SQL> @?/md/admin/c81Xu9X.sql
SQL> spool off
b. Upgrade Oracle interMedia
SQL> spool intermedia_upgrade.log
SQL> connect / as sysdba
SQL> @?/ord/im/admin/imdbma.sql
SQL> @?/ord/admin/u0801070.sql
SQL> @?/ord/im/admin/u0801070.sql
SQL> connect ordsys/ordsys
SQL> @?/ord/im/admin/imchk.sql
SQL> spool off
c. Upgrade Oracle Text
SQL> spool text_upgrade.log
SQL> connect / as sysdba
SQL> @?/ctx/admin/s0900010.sql
SQL> connect ctxsys/ctxsys
SQL> @?/ctx/admin/u0900010.sql
SQL> connect / as sysdba
SQL> @?/ctx/admin/s0902000.sql
SQL> connect ctxsys/ctxsys
SQL> @?/ctx/admin/u0902000.sql
SQL> spool off
d. Complete 9.2.0.4 Patchset
SQL> shutdown immediate
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off
e. Compile invalids
SQL> shutdown immediate
SQL> startup
SQL> exec utl_recomp.recomp_parallel(4)
f. Verify the upgrade
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME STATUS VERSION
Oracle9i Catalog Views VALID 9.2.0.4.0
Oracle9i Packages and Types VALID 9.2.0.4.0
JServer JAVA Virtual Machine VALID 9.2.0.4.0
Oracle9i Java Packages VALID 9.2.0.4.0
Oracle XDK for Java VALID 9.2.0.6.0
Oracle interMedia Text VALID 9.2.0.4.0
Oracle9i Real Application Clusters INVALID 9.2.0.4.0
Oracle interMedia VALID 9.2.0.4.0
Oracle Spatial VALID 9.2.0.4.0

11) Copy tnsnames.ora and listener.ora files from TNS_ADMIN directory of old ORACLE_HOME (817) to TNS_ADMIN directory of new ORACLE_HOME (920) and change references of 817 ORACLE_HOME to 920 ORACLE_HOME. Then start DB listener and make sure that “tnsping” works.

12) Update initXXXXXX.ora and ifilecbo.ora files as given below. These values have been taken from Note# 216205.1.

a. Update the following parameters in initXXXXXX.ora file:
Set the value of “aq_tm_processes” to the original value which was there before starting the upgrade
Set the value of “job_queue_processes” to the original value which was there before starting the upgrade
Set “compatible = 9.2.0”
Set “_system_trig_enabled = TRUE”
Set “log_archive_start = true”

b. Add the following new parameters in initXXXXXX.ora file:
nls_length_semantics = BYTE
pga_aggregate_target = 1000M
workarea_size_policy = AUTO

c. Comment the following parameters in initXXXXXX.ora as these are obsoleted in 9iR2 database:
DB_BLOCK_MAX_DIRTY_TARGET
sort_area_size
db_block_lru_latches
job_queue_interval
always_anti_join
always_semi_join

d. Update the following parameter in ifilecbo.ora file”
Set “optimizer_features_enable = 9.2.0”

e. Comment the following parameters in ifilecbo.ora as these are obsoleted in 9iR2 database:
_optimizer_undo_changes
_optimizer_mode_force
_complex_view_merging
_push_join_predicate
_use_column_stats_for_function
_or_expand_nvl_predicate
_push_join_union_view
_ordered_nested_loop
optimizer_percent_parallel=0
always_anti_join
always_semi_join
_new_initial_join_orders

13) Restart the database and alter it in arhivelog mode. Make sure that you do not get “ORA-32004: obsolete and/or deprecated parameter(s) specified” error while starting the database. If you get this error, check the erroring parameter name in alertXXXXXX.log file and comment that in init.ora.
$ sqlplus “/ as sysdba”
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> shutdown immediate
SQL> startup

14) Execute the post-install scripts
SQL> conn / as sysdba
SQL> @?/javavm/install/jvmsec3.sql
SQL> @?/javavm/install/jvmsec5.sql
SQL> conn apps/
SQL> @/patch/115/sql/adgrn9i.sql apps

15) Apply APPS patches required for 9iR2 database.

a. Apply FND Patch# 2838093
b. If adpatch hangs while executing “adinvset.pls” for more than 10 minutes then you may be hitting Bug# 2651057. Apply Patch# 2651057 to fix the issue.
c. Apply AD Patch# 2361208

16) Complete the upgrade and start APPS services

a. Run “Re-create grants and synonyms” from adadmin
b. Run “Compile APPS schema” from adadmin
c. Start all APPS services
d. Expire the blackout
e. Perform health checks and release the instance to the customer
f. Rename old ORACLE_HOME as 817_old
$ mv /xxxxxx/oracle/product/817 /xxxxxx/oracle/product/817_old

Oracle Commands

to get user_name and connect string as your SQL prompt:-

add below line to $ORACLE_HOME/sqlplus/admin/glogin.sql file...
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"

Instance has crashed and resources were not released:-

$ sysresv -i
IPC Resources for ORACLE_SID "orcl":
Shared Memory:
ID KEY
32768 0xdc70f4e4
Semaphores:
ID KEY
98304 0xb11a5934
Oracle Instance not alive for sid "orcl"
Remove ipc resources for sid "orcl" (y/n)? y
Done removing ipc resources for sid "orcl"

To find a free space in datafile wise: -

set linesize 150
column file_name format a50
column tablespace_name format a10
column free_space_in_mb format 9999.9999
select ddf.file_name,ddf.tablespace_name,
sum(dfs.bytes)/1024/1024 free_space_in_mb
from dba_data_files ddf, dba_free_space dfs
where ddf.file_id = dfs.file_id
and ddf.tablespace_name like '&tablespace_name_in_caps'
group by ddf.file_name,ddf.tablespace_name;


Displays Actual size, used & freespace within the datafiles: -

TTI "Allocated, Used & Free space within datafiles"

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

to kill inactive session: -
set heading off
set echo off
spool kill.sql
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where STATUS='INACTIVE';
spool off
@kill.sql

to get Current date in filename while spooling: -
set term OFF echo OFF doc OFF
set hea OFF feed OFF ver OFF
ttitle OFF
spool tst1.sql
select 'spool tst'||to_char(sysdate,'ddmmyy')||'.sql' from dual;
spool off;
@tst1.sql
select sysdate from dual;
spool off

To get the client address: -
select sys_context('USERENV','IP_ADDRESS') from dual;

To get the server address: -
select utl_inaddr.get_host_address from dual;

To get the IP address of another server: -
select utl_inaddr.get_host_address ('') from dual;

To get the server name from its IP: -
select utl_inaddr.get_host_name ('') from dual;

So to get all clients ip: -
select sid, machine, utl_inaddr.get_host_address(machine) ip
from v$session
where type='USER' and username is not null
/

Script to calculate the row length (approximately): -
1) As a sys user: -
select sum(de.bytes)/dt.num_rows "Avg Row Size"
from dba_extents de, dba_tables dt
where de.owner like 'OWNER_NAME' and de.segment_name like 'SEGMENT_NAME'
and de.segment_name=dt.table_name
group by dt.num_rows;

2) As a normal user: -
select sum(ue.bytes)/ut.num_rows "Avg Row Size"
from user_extents ue, user_tables ut
where ue.segment_name like 'SEGMENT_NAME' and ue.segment_name=ut.table_name
group by ut.num_rows;

Access your Alert Log via SQL with External Tables

Starting in Oracle9i you can map external flat files to Oracle tables.
This is especially useful for reading the Oracle alert log and mapping a Excel spreadsheet making the Oracle alert log accessible via SQL from Oracle:
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/orcl/bdump';

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_orcl.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:
select msg from alert_log where msg like '%ORA-00600%';

ORA-00600: internal error code, arguments: [17034],[2940981512],[0], [],[],[],[],[]
ORA-00600: internal error code, arguments: [18095],[0xC0000000210D8BF8],[],[],[],[]
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

ORA-00600: internal error code, arguments: [19004]

If you ever come across the error code "ORA-00600: internal error code, arguments: [19004], [], []...." while running query ralated to any table, view, index
here is the solution for it
login to your oracle database using pl/sql tool
run the below command on the command prompt
1. in case of error on querying table
ANALYZE TABLE table_name delete statistics;
2. in case of error on querying view
put the command above with related tables in the view. i.e run analyze tables for each table that form that view.

Ex:-

Create database with dbca (all default values):-
Created and imported two users in this database

And executed the below query

select d.mdl_code, substr(d.short_desc,1,22) short_desc,
d.version Prod, t.version UAT from (select * from PROD.module_versions) d,
(select * from UAT.module_versions) t where d.mdl_code = t.mdl_code(+)
and d.short_desc = t.short_desc(+)

Get the bellow error:-

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []
Deleted statistics for the both tables in databaseas a super user:-
ANALYZE TABLE prod.module_versions delete statistics;
ANALYZE TABLE uat.module_versions delete statistics;
Now query is working fine.
____________________________________________________________________________________

ORA-00600[4193]/[4194]: -

Option 1:- Support Method(Drop the undo tablespace).

There is no need to use Unsupported parameter like _offline_rollback_segments and _corrupted_rollback_segments to resolve ora-00600[4193]/[4194]

Option 1 :- Supported Method
======================

Drop the undo tablespace.

This error normally happens for a new transaction. The trace file actually shows an active transaction for the undo segment because this is the transaction created by the same process. If the undo segment happens to have an active transaction , then Oracle may recover it later with no problems .

Normally if the header is dumped after the error, the active transactin is gone.

So a Simpler option to resolve this issue is.

Step 1
SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;
SQL> Shutdown immediate;

Step 2
Modify the corrupt.ora and set Undo_managment=Manual
SQL> Startup mount pfile='/tmp/corrupt.ora'
SQL> Show parameter undo
it should show manual
SQL> Alter database open ;
If it comes up
SQL> Create rollback segment r01 ;
SQL> Alter rollback segment r01 online ;
Create a new undo tablespace
SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles

Step 3
SQL> Shutdown immediate;
SQL> Startup nomount ; ---> Using spfile
SQL>Alter system set undo_tablespace= scope=spfile;
SQL> Shutdown immediate ;
SQL> Startup

For 8i database and Below

SQL>Startup restrict
Drop the Manual rollback segments and recreate it
@Option 2 (Drop the Rollback segment)
@From the ora-00600[4194] trace file identify the undo segment
@For example
@ORA-00600: internal error code, arguments: [4194], [19], [33], [], [], [],
@In the above example
@ARGUMENTS:
@a----> 19
@Search for UNDO BLK in the trace file
@********************************************************************************
@cnt--->0x13 --> Decimal --> 19
@Option 3(System undo segment erroring with Ora-00600[4194/4193]
@Option 1 would fail if the undo segment involved is System undo segment.
@Please refer the note given below for patching the same.
____________________________________________________________________________________
ORA-00600 arguments: [keltnfy-ldmInit], [46]

Problem Description
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] If you try to create your database manually then also the command startup nomount fails with above error message.

Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.

Solution of the Problem
Step 01: Check permission on /etc/hosts

$ ls -l /etc/hosts
-rw-r--r-- 1 root root 153 Nov 24 2007 /etc/hosts
Note that you need read permission of all users.

Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.

IP Address fully qualified hostname simple or short hostname Alias

A simple example,
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 database localhost.localdomain localhost

Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database

$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms

If you get the following message,
$ ping database
ping: unknown host database

then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,

127.0.0.1 database localhost.localdomain localhost

And then ping database again. Make sure you are able to ping your host.

Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.

$nslookup www.google.com
The forward and reverse lookup should succeed.

Step 05: Check nsswitch.conf
$ cat /etc/nsswitch.conf
hosts: files dns

Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.

Step 06: Check resolv.conf
$ cat /etc/resolv.conf
nameserver 4.2.2.2