March 2008 – Persistent Storage Solutions

ORA-12705: Cannot access NLS data files or invalid environment specified

Published on March 31, 2008 by Advait4 Comments

While running autoconfig in oracle apps on database side, you might encounter this error. Also you can see this when you do sqlplus from command prompt.

Cause:

Either your NLS_LANG parameter is not set correctly or NLS_ORA10 parameter is not set correctly.

Solution :

check $ORA_NLS10 path. This should point to ORACLE_HOME/nls/data/9idata

if this is not set correct, then you need to set the same manually using export command. Also if this is an oracle application database ( either 11i or R12), then make sure to change the variable s_db_oranls in CONTEXT_FILE and then run autoconfig.

If ORACLE_HOME/nls/data/9idata path is not present then you can create the same using $ORACLE_HOME/nls/data/old/cr9idata.pl script. Run this script and it will create the desired directories and after that set ORA_NLS10 parameter.

If this doesn’t resolve the issue then you need to check NLS_LANG parameter setting. This should be (Language)_(Territory).(character set). Example American_America.UTF8.

Hope this helps !!

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

Published on March 31, 2008March 31, 2008 by Advait17 Comments

Some time we encounter this error while starting the database. Below is the solution for recovering through this error.

Error:

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264916 bytes
Variable Size 264241900 bytes
Database Buffers 801112064 bytes
Redo Buffers 7122944 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []

Encountered in : 10.2.0.2.0 Database

Cause:

Oracle is unable to perform instance recover but it works when is invoked manually.

Solution:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264916 bytes
Variable Size 264241900 bytes
Database Buffers 801112064 bytes
Redo Buffers 7122944 bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

References

Metalink note ID: 393984.1

Using Net::LDAP perl Module

Published on March 26, 2008 by AdvaitLeave a comment

Hi All, this post is related to using Net::LDAP perl module to register, search and delete the users in OID (oracle internet directory). I have been struggling to get this perl code for me. Finally I was able to create a perl script for doing the same.

Initially I tried the same thing using ldapadd command (for adding users in OID). But this has some dependencies. Like for using ldapadd command you need AS10g Oracle home installed where you can reference to these commands. Even if AS10g home is not present you can still use the perl binaries for LDAP. But this again depends on our perl installation. Not all perl installation is having these binaries installed.

So best approach is to use Net::LDAP module for the same. Remember there is a URI::LDAP module also. But this cannot be used, we need Net::LDAP module. You can check if this module is present in your perl installation or not Possibly this should be present as a file LDAP.pm in one of the location present in @INC). If the module is not present then you can install the same by following the below simple steps.

You can download the Net::LDAP perl module from CPAN

Instruction for install

Unzip the module and run the following steps

perl Makefile.PL
make
make test
su root
make install

Once you install the module you can connect to LDAP server and add the user. Below is the perl script I have used for the same.

Perl script for using Net::LDAP


#!/local/bin/perl -I /oracle/iAS/perl_modules/lib/perl5/site_perl/5.8.0
#use strict;
#use warnings;
use Net::LDAP;

$ldap = Net::LDAP->new("ap6059rt.us.oracle.com") or die "$@";
$mesg = $ldap->bind("cn=orcladmin", password=>"welcome1");

%mon = ("Jan","01","Feb","02","Mar","03","Apr","04","May","05","Jun","06","Jul","07","Aug","08","Sep","09","Oct","10","Nov","11","Dec","12");

my $gen_time= localtime();
my @TimeStamp = split(/ /, $gen_time);
my $TimeStamp=$TimeStamp[4].$mon{$TimeStamp[1]}.$TimeStamp[2]."000000z";

$arg = "$ARGV[$0]";

$dn="uid=$arg,cn=users,dc=ap6059rt,dc=us,dc=oracle,dc=com";
$uid=$arg;
$cn=$arg;
$sn=$arg;
$mail=$arg."\@ap6059rt.us.oracle.com";
$givenName=$arg;
$orcltimezone="Asia/Calcutta";
$userpassword="welcome1";
$orclisenabled="ENABLED";

$mesg = $ldap->search(filter=>"(uid=$uid)", base=>"dc=ap6059rt,dc=us,dc=oracle,dc=com");
$mesg->count && $ldap->delete($dn);

$result = $ldap->add($dn, 
                attr => [ 'uid' => $uid,
                          'cn' => $cn,
                          'sn' => $sn,
                          'mail' => $mail,
                          'givenName' => $givenName,
                          'userpassword' => $userpassword,  
                          'orcltimezone' =>  $orcltimezone,      
                          'orclactivestartdate' => $TimeStamp,     
                          'orclisenabled' => $orclisenabled,            
                          'objectclass' => [ 'person', 'inetOrgPerson', 'organizationalperson', 'orcluser', 'orcluserv2', 'ctCalUser', 'orclUserProvStatus']
                        ]
           );
$result->code && warn "error: ", $result->error;

$ldap->unbind;

You need to just pass the username which you want to register in OID and this script will register the same.

/oracle/iAS/perl_modules/lib/perl5/site_perl/5.8.0 is the location where my Net::LDAP module is installed and I am including the same in my script as first line using -I option.

Hope this helps !!

References:

http://search.cpan.org/~gbarr/perl-ldap/lib/Net/LDAP/FAQ.pod

http://www.linuxjournal.com/article/7086

Creating Oracle Home Inventory – Oracle Database 10g

Published on March 21, 2008 by Advait4 Comments

Some times you clone an oracle home manually using OS level commands and this might cause problem in your existing inventory. Your new ORACLE_HOME will not be registered in your existing registry.

As far as running of database is concerned, we wont face any problem, trouble comes when in future you want to apply a patch to an oracle home which is not registered in inventory. The patch can be applied to an ORACLE_HOME with -no_inventory option, but then you wont have the record of patches applied to that ORACLE_HOME.

For such situation, you can register your new ORACLE_HOME manually to the existing inventory (or create a new inventory). Below is the command for the same.

cd $ORACLE_HOME/oui/bin

./runInstaller -attachHome ORACLE_HOME=”/slot/ems1696/oracle/db/tech_st/11.1.0″ ORACLE_HOME_NAME=”Oracle11g_home1″

Here ORACLE_HOME will contain the complete path for ORACLE_HOME and ORACLE_HOME_NAME will be the name you want to give for your ORACLE_HOME

Hope this helps !!

Using TABLE_TO_COMMA in PLSQL

Published on March 13, 2008March 13, 2008 by Advait3 Comments

Some times the situation arises where we want to output the result from the table as a comma separated string. This situation can be answered using many methods. The most efficient method, however, is using TABLE_TO_COMMA procedure of DBMS_UTILITY package. Below is the example that I encountered.

set echo on serveroutput on
declare
cursor c1 is select dept_no, dept_name, location
from dept12;

output_table dbms_utility.uncl_array;
cnt binary_integer;
output_string varchar2(80);
counter integer:= 0;
begin
for i in c1 loop
output_table(counter+1) := I.dept_no;
output_table(counter+2) := I.dept_name;
output_table(counter+3) := I.location;
counter := counter + 3;
end loop;
DBMS_UTILITY.TABLE_TO_COMMA(output_table, cnt, output_string);
dbms_output.put_line(output_string);
end;

Hope this helps !!

Creating users in OID (Oracle Internet Directory) – Oracle Collabsuite 10g

Published on March 11, 2008March 11, 2008 by Advait3 Comments

Introduction:

his port is regarding creation of users in central OID (Oracle Internet Directory) instance. We call it a central because of its architecture. Usually for any business setup, there will be many instances of application and a single installation of OCS or Oracle AS. Either of these techstack is having OID as one of the component. So the users of all the instances are basically registered in this central OID instance.

So here we will see, how to create users in OID. We can create users either through UI or through command line API. We will see both the methods here.

Creating users using UI:

Navigate to the OID self service console, the URL will be in the form as given below.

http://(hostname):(infra-http-port)/oiddas
Example:
http://ap6059rt.us.oracle.com:7779/oiddas

1) Click on the right hand top corner to login

2) use the super user ID having all privileges to create a user. In my case its orcladmin

3) Once you login, click on directory tab and then click on create button

4) First screen will ask for general information. Here you need to enter password for user.

5) You can provision the various component for this user. Example in case of mail, if its provisioned, then by default a mail account will be created for user.

6) On third screen, you can check the information and also keep the default information as it is. You can also decide the quota for mail server.

7) Review screen. You can review and finish

If you search for the user you created you should be able to see the record.

Creating users using command line

You can use the following command for creating users from command line.

These commands needs to be run on the host where the OID is installed. Also make sure to source the environment before creating the user.

Example:

ORACLE_HOME=/slot03/oracle/product/ocs10g/infra
TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_SID=ocs10g
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_HOME TNS_ADMIN ORACLE_SID PATH LD_LIBRARY_PATH

Creating a user:

1. Create an ldif file called orcl.ldif that includes the following content:

dn: cn=orcladmin, User_Search_Base
changetype: add
uid: orcladmin
mail: orcladmin
givenName: orcladmin
cn: orcladmin
sn: orclAdmin
description: Seed administrative user for subscriber.
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: inetorgperson
objectClass: orcluser
objectClass: orcluserV2

Example:

cn=avdeo,cn=users,dc=us,dc=oracle,dc=com
objectclass=top
objectclass=person
objectclass=inetorgperson
objectclass=organizationalperson
objectclass=orcluser
objectclass=orcluserv2
objectclass=ctCalUser
objectclass=orclUserProvStatus
givenname=Advait
sn=Deo
orcltimezone=Asia/Calcutta
mail=advait.deo@us.oracle.com
uid=avdeo
orclactivestartdate=20080310000000z
cn=avdeo
orclisenabled=ENABLED

2. Execute the following command (the following command is one continuous line):

ldapadd -h OID_host -p non-SSL_port -D OID_superuser -w OID_superuser_password -v -f orcl.ldif

Example:

ldapadd -p 389 -h ap6059rt -D “cn=orcladmin” -w “welcome1” -v -f test.ldif

sh-2.05b$ ldapadd -p 389 -h ap6059rt -D “cn=orcladmin” -w “welcome1” -v -f test.ldif
do modify ****
add objectclass:
top
person
inetorgperson
organizationalperson
orcluser
orcluserv2
ctCalUser
orclUserProvStatus
add givenname:
Advait
add sn:
Deo
add orcltimezone:
Asia/Calcutta
add mail:
advait.deo@us.oracle.com
add uid:
avdeo
add orclactivestartdate:
20080310000000z
add cn:
avdeo
add orclisenabled:
ENABLED
adding new entry cn=avdeo,cn=users,dc=us,dc=oracle,dc=com
modify complete

Searching for the user:

ldapsearch uid=avdeo

sh-2.05b$ ldapsearch uid=avdeo
cn=avdeo,cn=users,dc=us,dc=oracle,dc=com
objectclass=top
objectclass=person
objectclass=inetorgperson
objectclass=organizationalperson
objectclass=orcluser
objectclass=orcluserv2
objectclass=ctCalUser
objectclass=orclUserProvStatus
givenname=Advait
sn=Deo
orcltimezone=Asia/Calcutta
mail=advait.deo@us.oracle.com
uid=avdeo
orclactivestartdate=20080310000000z
cn=avdeo
orclisenabled=ENABLED

Deleting users from LDAP:

ldapdelete -p non-SSL_port -h OID_host -D OID_superuser -w OID_superuser_password username

Example:

ldapdelete -p 389 -h ap6059rt -D cn=orcladmin -w welcome1 “cn=avdeo,cn=users,dc=us,dc=oracle,dc=com”

References:

Metalink Note ID: 125301.1

Oracle OID Admin Guide

Open LDAP Manual page

Installing OCS (Oracle Collabsuite) 10g – Linux

Published on March 7, 2008March 11, 2008 by AdvaitLeave a comment

Introduction:This document gives a step by step approach for installing OCS (Oracle Collabsuite) 10g. This is created from the Oracle documentation available on OTN. This serves as a simple guide for installation of OCS 10g.

Setup Details:

OS Detail :

bash-2.05b$ uname -a
Linux ap6059rt 2.4.21-37.0.0.1.4.ELhugemem #1 SMP Fri Feb 24 18:01:54 PST 2006 i686 i686 i386 GNU/Linux
bash-2.05b$ cat /etc/issue
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)
Kernel \r on an \m
OCS 10g Version : Oracle Collabsuite 10g Release I (10.1.2.0.0)

Pre – Installation Steps

Download the required OCS software zip files to one of the directory on slot and unzip the same in a separate directory.

bash-2.05b$ ls -rlttotal 12
drwxrwxrwx 2 oracle oracle 4096 Mar 5 16:08 OCS-Supp-DVD
drwxr-xr-x 2 oracle oracle 4096 Mar 5 16:08 OCS-Main-DVDs

Perform the following pre-reqs steps before starting the installation. These are the pre-installation steps.

1) Check the free space on slot and also under /tmp directory

[oracle@ap6059rt /slot03]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
ap1079nap-vlan901:/vol/rtvol37/slot1474
140509184 4416256 136092928 4% /SLOTS/slot03
[oracle@ap6059rt /slot03]$ df -k /tmp
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 46354176 183516 43815936 1% /tmp
[oracle@ap6059rt /slot03]$

2) Check the swap memory available. It should be atleast twice that of RAM memory.

[oracle@ap6059rt /slot03]$ grep SwapTotal /proc/meminfoSwapTotal: 24587472 kB
visual:
visual id: 0x32
class: TrueColor
depth: 16 planes
available colormap entries: 64 per subfield
red, green, blue masks: 0xf800, 0x7e0, 0x1f
significant bits in color specification: 6 bits

3) Check the Linux Red Hat release and important packages

Note:- Login as root and then perform these tasks.

[jmyang@ap6059rt /slot03]# cat /etc/redhat-release
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

[jmyang@ap6059rt /slot03]# rpm -q glibc-2.3.2
glibc-2.3.2-95.37

[jmyang@ap6059rt /slot03]# rpm -q glibc-common-2.3.2
glibc-common-2.3.2-95.37

[jmyang@ap6059rt /slot03]# rpm -q binutils-2.14.90.0.4
binutils-2.14.90.0.4-39

[jmyang@ap6059rt /slot03]# rpm -q compat-db-4.0.14
compat-db-4.0.14-5.1

[jmyang@ap6059rt /slot03]# rpm -q compat-glibc-7.x
compat-glibc-7.x-2.2.4.32.6

[jmyang@ap6059rt /slot03]# rpm -q compat-libstdc++-7.3
compat-libstdc++-7.3-2.96.128

[jmyang@ap6059rt /slot03]# rpm -q compat-libstdc++-devel
compat-libstdc++-devel-7.3-2.96.128

[jmyang@ap6059rt /slot03]# rpm -q gcc-3.2.3
gcc-3.2.3-53

[jmyang@ap6059rt /slot03]# rpm -q gcc-c++-3.2.3
gcc-c++-3.2.3-53

[jmyang@ap6059rt /slot03]# rpm -q libstdc++-3.2.3
libstdc++-3.2.3-53

[jmyang@ap6059rt /slot03]# rpm -q libstdc++-devel-3.2.3
libstdc++-devel-3.2.3-53

[jmyang@ap6059rt /slot03]# rpm -q openmotif21-2.1.30
openmotif21-2.1.30-9.RHEL3.6

[jmyang@ap6059rt /slot03]# rpm -q pdksh-5.2.14
pdksh-5.2.14-21

[jmyang@ap6059rt /slot03]# rpm -q setarch-1.3
setarch-1.3-1

[jmyang@ap6059rt /slot03]# rpm -q make-3.79.1
make-3.79.1-17.1

[jmyang@ap6059rt /slot03]# rpm -q gnome-libs-1.4.1.2.90
gnome-libs-1.4.1.2.90-34.2

[jmyang@ap6059rt /slot03]# rpm -q sysstat-5.0.5
sysstat-5.0.5-5.rhel3 4) Checking Kernel parameters.

4) Checking Kernel parameters

Note:- All these values should be updated as a root user. So login as root and then perform these tasks.

shmall is -sh-2.05b# cat /proc/sys/kernel/shmall
2097152
it is smaller than 3279547
so we need to modify the /etc/sysctl.conf

Once this file is modified we can run /sbin/sysctl -p for this to take effect.
You can check all the kernel parameters using

sh-2.05b# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
kernel.sysrq = 1
kernel.core_uses_pid = 1
fs.file-max = 1677721
vm.pagecache = 1 15 15
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.shmall = 3279547
net.ipv4.tcp_tw_recycle = 1
kernel.sem = 1100 32000 100 1100
kernel.msgmnb = 65535
kernel.msgmni = 2878
kernel.msgmax = 8192
net.ipv4.ip_local_port_range = 10000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
kernel.sysrq-key = 64

5) To improve the performance of the software on Linux system, you must increase the following shell limits for oracle user

– login as root

– Add the following lines to /etc/security/limits.conf file:

soft nproc 4096
hard nproc 16384
soft nofile 16384
hard nofile 65536

– Add the following lines to the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

– Add the following lines to the /etc/pam.d/su file, if you use su to switch to oracle account

6) Check your shell and add the below content accordingly

sh-2.05b# echo $SHELL
/usr/local/bin/tcsh

For the C or tch shell, add the following lines to the /etc/csh.login file:

if ( $USER == “oracle” ) then
limit maxproc 16384
limit descriptors 65536
endif

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file:

if [ $USER = “oracle” ]; then
if [ $SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

7) Location of the Default Identity Management Realm has been set in /etc/hosts
The hosts file should use the following format:

ip_address fully_qualified_hostname short_hostname

Example:

123.45.67.89 primaryHost.mydomain.com primaryHost

sh-2.05b$ cat /etc/hosts

127.0.0.1 localhost.localdomain localhost

140.87.205.80 ap6059rt.us.oracle.com ap6059rt

This completes all pre-reqs steps. Now starting with installation.

Installation Steps

Unzip the downloaded DVD in one of the directories before starting the installation.

For Example in my case the location for ZIP was

sh-2.05b$ pwd/slot03/ocs10g/downloads/OCS-Main-DVDs
sh-2.05b$ ls -lrt
total 3725448
-rw-r–r– 1 oracle oracle 1840273814 Mar 5 14:10 B24986-01_1of2.zip
-rw-r–r– 1 oracle oracle 1967099379 Mar 5 14:42 B24986-01_2of2.zip

Created a directory call setup at /slot03/ocs10g/downloads location and unziped both the zip files in setup directory.

sh-2.05b$ cd /slot03/ocs10g/downloads/setup/
sh-2.05b$ unzip /slot03/ocs10g/downloads/OCS-Main-DVDs/B24986-01_1of2.zip

sh-2.05b$ unzip /slot03/ocs10g/downloads/OCS-Main-DVDs/B24986-01_2of2.zip

Your setup directory will now look like this

sh-2.05b$ cd /slot03/ocs10g/downloads/setup/
sh-2.05b$ ls -lrt
total 36
-rwxrwxrwx 1 oracle oracle 4715 Oct 26 2005 runInstaller
-rw-r–r– 1 oracle oracle 4410 Oct 27 2005 welcome.html
drwxrwxrwx 2 oracle oracle 4096 Nov 2 2005 response
drwxrwxrwx 5 oracle oracle 4096 Nov 2 2005 install
drwxrwxrwx 8 oracle oracle 4096 Nov 2 2005 doc
drwxrwxrwx 4 oracle oracle 4096 Nov 2 2005 calendar_standalone
drwxrwxrwx 8 oracle oracle 4096 Mar 5 22:06 stage

Another important thing before starting the installation is to rename a file /etc/tnsname.ora. If this file is present then installation will give error and will be terminated. So make sure to login as root user and rename this file to some other name

sh-2.05b$ mv /etc/tnsname.ora /etc/tnsname.ora.backup

Start the installation by running runInstaller present in unzipped directory (/slot03/ocs10g/downloads/setup/ in my case).

Make sure that the installation is started as oracle user and not as root.

sh-2.05b$ cd /slot03/ocs10g/downloads/setup

sh-2.05b$ ./runInstaller -invPtrLoc /slot03/oracle/product/ocs10g/oraInst.loc

Screen 0) A welcome screen will come. Click on next and go to next screen

Screen 1) Specify Inventory directory and Credential Specify the inventory directory and the group which will be th owner of inventory. In our case we want the user oracle to be the owner of inventory and its primary group is DBA. So we are providing the group for inventory as DBA

Screen 2) Specify File Location
Specify the ORACLE_HOME location where the software will be installed. Under this location 1 or 2 directories will be created (depending on your installation). Once will be for infra and other for application.

Screen 3) Select a product to install
select the product you want to install. We are here doing the complete installation. So we will be installing infra as well as application. Depending on your requirement you can choose either or both of them.

Screen 4) Installer will carryout the pre-reqs checking and will flag an error if any of the pre-reqs are not satisfied.

Screen 5) Language Selection
Select the language you want to install

Screen 6) This screen will summarize the selection done till now. Validate and click on next to go to next screen.

Screen 7) Select component to configure
You can select one or more component you want the installer to configure for you. In our case we are going for all the components.

Screen 8) Specify Namespace in Internet Directory
The distinguished name (DN) that you specify in the Specify Namespace in Internet Directory screen will be designated as the namespace in Oracle Internet Directory, where users and groups are administered.

A suggested namespace appears on the screen. The installer determines the suggested namespace from the /etc/hosts file. If it meets your deployment requirements, then select it. If not, enter another DN in the Custom Namespace field.

Screen 9) Specify database configuration option
Enter a name for the Oracle Collaboration Suite Database. You can give any name (less than 8 chars) for database or even accept the default one.

Screen 10) Specify database schema password
Its good to have common password for all account. This is to avoid confusion.

Screen 11) Specify Application Passwords
Its good to have same passwords as database user passwords. This is to avoid confusion.

Screen 12) Specify Oracle Mail Domain Information

Screen 13 – a) Specify port Configuration Options
In this screen specify the port you want your application to use. You can go with default port numbers of you can specify your own port numbers. When specifying custom ports, make sure that the port is not in use. You can check if the port is in use or not using netstat -an | grep <port no> command. Also if specifying the custom ports, then make sure that port numbers are above 1024. In our case, we changed the HTTP port number from 80 to 7779. Also SSl port has been changed from 443 to 4443. The ranges for the port is given in 3rd column.
Also note that these ports are for infra installation. When installing application, it will again ask for the ports.

Screen 13 – b) Also the port number of SMTP server was already in use. So you can change the port number from 25 to some unused port (example 30).

Screen 14) Warning
This screen will show a warning, since some of the ports are below 1024. Only root user is having access to ports below 1024. You can select the check box “I authorize SUID root actions” and click on next.

Screen 15) Installation summary.
Click on “Install” button to begin installation of infra.

Screen 16) Shows the installation progress for infra tier. Remember that in our installation, we have selected to install infra as well as application. So first it will install infra and then application. Installing infra also involves installing database also. This is shown in screen 17 below.

After this installation progress screen, when the installation comes to end, installer will ask to run root.sh as root user.
When such message appears, start another session and login as root. Run the required script and then come to this screen, click on OK. Do not click on OK before running root.sh.

During our installation, we faced some issue while running root.sh. We logged a bug 6870418 for the same and as per server team, the underlying filer was not having root privileges. After this bug is resolved, we were able to run root.sh

Screen 17) Shows all the components of infra getting installed in the back screen. Database installation is in progress and is shown in front screen.

Screen 18) Shows the component progress during installation. After this screen infra installation completes and installer automatically starts application (mid tier) installation.

Screen 19) Specify port configuration options

Installer will ask for ports for the second time. These ports are for applicaiton (mid tier). Example there will be 2 HTTP server process that will run, one for infra and one for mid tier. So we have to specify HTTP port for both. Similar is the case with other ports as well. If you remember we have given 7779 initially for HTTP port of infra. We can change that here to 7778 or some thing else. Even if we keep the ports same as that of infra (example 7779), it will automatically select different ports. In our case we have kept the same port number as that of infra. (that is 7779 for HTTP, 4443 for SSL and 30 for SMTP. All other ports are having default values).After clicking next, it will show the installation progress for each component similar to screen Screen 18) and after the installation of all components of mid tier, installer will ask to run root.sh again. After running root.sh the installation will end.

================================================================= Use the following URL to access the Oracle Collaboration Suite Console :

http://ap6059rt.us.oracle.com:1810

Use the following URL to access the Oracle Collaboration Suite 10.1.2.0.0
Release Notes:
http://ap6059rt.us.oracle.com:7779/relnotes/toc.htm

Global Database Name: ocs10g.us.oracle.com
================================================================

Post Installation steps

For accessing database from UNIX prompt, you need to source the environment using the following export commands.

This will source the required ORACLE_HOME (infra) and other parameters mentioned below.

export ORACLE_HOME=/slot03/oracle/product/ocs10g/infra

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_SID=ocs10g

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/LIB:$LD_LIBRARY_PATH

OID account can be unlocked as given below

sh-2.05b$ oidpasswd connect=ocs10g unlock_su_acct=true
OID DB user password:
OID super user account unlocked successfully.

Performing Component-Specific Tasks

sh-2.05b# export ORACLE_HOME=/slot03/oracle/product/ocs10g/infra
sh-2.05b# export PATH=$ORACLE_HOME/bin:$PATH
sh-2.05b# tnslsnr listener_es -user 8000 -group 8000 &

Configuring the Time-out Value in the sqlnet.ora File

Set following parameter in sqlnet.ora
SQLNET.EXPIRE_TIME = 10 References:

Oracle® Collaboration Suite Installation Guide

Oracle® Collaboration Suite Administrator’s Guide

Compiling JSPs in Oracle Applications

Published on March 5, 2008March 7, 2008 by Advait2 Comments

Many times we faces an issue which needs JSPs to be compiled, specially in case of development. Also for example, if some of the class files are missing then, in that case we can compile the JSPs and get the required class files.

In Oracle Applications (11i as well as R12), we have a utility called ojspCompile.pl. This script carries out compilation of JSPs. Below is some information regarding this script.

Oracle Apps 11i

In 11i version, this script is present in JTF_TOP/admin/scripts

===========================================================

(appmgr01) scripts – -bash $ ./ojspCompile.pl
syntax: ./ojspCompile.pl COMMAND {ARGS}
COMMAND –compile               update dependency, compile delta
–create                rebuild entire dependency file
-delta.out <file>       update dependency, list delta to file
-dep.out <xmlfile>      update dependency, output heirarchy to file

ARGS    -s <regex>      matching condition for JSPs filenames
-p <procs>      number of parallel compilations
-log <file>     to override logfile from ojspCompile.conf
-conf <file>    to override ojspCompile.conf
–retry         retry previously failed compilation attempts
–flush         forces recompilation of all parent JSPs
–quiet         do not provide an actively running progress meter
–fast          instantly fail jsps that are *possibly* invalid

example1: ojspCompile.pl –compile -s ‘jtf%’ -p 20 –retry
example2: ojspCompile.pl –compile -s ‘jtflogin.jsp,jtfavald.jsp’ –flush
example3: ojspCompile.pl –compile –fast –quiet

===========================================================

We can run the script using the above parameters. Example

===========================================================

(appmgr01) scripts – -bash $ ./ojspCompile.pl –compile –quiet
identifying apache_top…/slot01/appmgr/plmmtqaeora/iAS
identifying apache_config_top…/slot01/appmgr/plmmtqaeora/iAS
identifying java_home…/local/java/jdk1.4.2_04
identifying jsp_dir…/slot01/appmgr/plmmtqaecomn/html
identifying pages_dir…/slot01/appmgr/plmmtqaecomn
identifying classpath…file:///slot01/appmgr/plmmtqaeora/iAS/Apache/Jserv/etc/jserv.properties
auto-logging to /slot01/appmgr/plmmtqaecomn/_pages/ojsp_error.log
starting…(compiling delta)
using 8i internal ojsp ver: 1.1.3.5.1
including compatibility flag -whiteSpaceBetweenScriptlet
synchronizing dependency file:
enumerating jsps…15390
parsing jsp…15390
writing deplist…15390
initializing compilation:
eliminating children…12220 (-3170)
searching uncompiled…12199
translating and compiling:
searching untranslated…12199
translating jsps…12199/12199 [failed: 3] in 3m43s
compiling jsps… 51% complete: 6250/12196 ETA: 4m47s
compiling jsps…12196/12196 in 8m44s
Finished!

===========================================================

Using parameters –compile –quiet will compile only those JSPs which are invalid and needs compilation.

If you want to compile all JSPs (irrespective of status), then you need to use –compile –flush as parameters to osjpCompile.pl.

Doing these will replace all the .class files in _pages directory under COMMON_TOP (which is a kind of cache for JSPs).

Also in case of 11i, automatic recompilation of JSPs are done when we access the JSP. This is the default and only behavior. So in case if you delete _pages directory under COMMON_TOP, we can always recreate the same by running osjpCompile.pl or accessing the application directly.

In case we access application directly without running ojspCompile.pl, the access will be little slower, since it will be doing compilation as well.

Oracle E-Business Suite R12

In case of Oracle E-Business suite R12, same ojspCompile.pl script is present for compiling JSPs, but this script is present under FND_TOP/patch/115/bin directory.

Also, you cannot delete _pages directory under COMMON_TOP. Deleting this will make your application non-workable.

The reason for the same being that, in case of R12, JSPs are not compiled as and when you access them. So dynamic compilation of JSPs are not present. This is basically done to improve the performance of application system. But in case _pages directory has been deleted, then you can recreate the same by running ojspCompile.pl –compile –flush command.

Also some times, its required to have dynamic compilation of JSPs, specially in case of development environment, so that oacore will pick the latest changes. This setting can be done using a parameter in CONTEXT_FILE. The paramter name is s_jsp_main_mode.

This parameter is by default having a value of justrun, this can be changed to recompile. Once changed, you can run autoconfig and Verify that the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has

      <init-param>
         <param-name>main_mode</param-name>
         <param-value>recompile</param-value>
      </init-param>

Doing this will make new JSP to get compiled before it is shown on browser.

References:

Metalink Note ID 458338.1

Upgrading Oracle Application 11i to E-Business Suite R12

Published on March 4, 2008March 19, 2008 by Advait23 Comments

Overview of upgrade to R12

Upgrading an application from 11i to R12 involves, upgrading the database side, upgrading the middleware techstack and upgrading the application side.

Supported upgrade path for application side upgrade is as given below.

In case of database upgrade, you have to upgrade the database to 10gR2 (10.2.0). Because application R12 can be used only with 10g database.

Upgrade Process

All upgrade functionality has been consolidated into a single unified upgrade driver that performs the upgrade without reliance on the information formerly captured on the AutoUpgrade screens.

Rapid Install provides the most up-to-date, certified version of Oracle Applications products, along with the certified technology stack components. In an upgrade, it creates the new file system for the application (middle) tier components and the new file system for the database. After the upgrade, you run Rapid Install again to configure servers and start services.

An upgrade also includes various manual steps, including those that direct you to run scripts or apply patches. You rely on AutoPatch to apply all patches, including the unified driver that performs the upgrade to Release 12.

Upgrade Steps in brief

Here are the 4 simple steps, briefly presented below for upgrade. These steps are at very high level of abstraction. We will detailed each steps as we move on further.

1) Understand installed components, system sizing information, NLS considerations
2) Prepare for upgrade using Upgrade Manual Script(TUMS).
3) Upgrading to R12. This includes upgrading the database and applying the required patches through AutoPatch.
4) Post-Upgrade process. Complete the upgrade process by applying the latest RUP patches to keep the system most current.

We wont be considering the functional upgrade task here.

Upgrade steps in detail

1) Understanding installed components

Technology Stack Components

Rapid Install automatically installs and configures the required technology stack components for both the database tier and the application tier.
The database tier technology stack for both a new installation and for a system upgrade is based on Oracle10g Release 2.
The technology stack installed on the application tier includes, among other components:
– Oracle 10g Application Server (AS) 10.1.2
– Oracle 10g Application Server (AS) 10.1.3
– Oracle Developer 10g (includes Oracle Forms)
– Java (J2SE) native plug-in 1.5.0_08
– Java Developer Kit (JDK) 5.0

Memory Requirements

To calculate the memory requirements for an upgrade, consider the following:
– Number of concurrent users
– Infrastructure requirements for multi-tiered architecture
For example:
A test upgrade of the largest Oracle production system (oraprod) used the following:
– Database tier machine – 48 GB of memory
– Application tier machine – 12 GB of memory
A test upgrade of the Vision database and application tier machine used 6 GB of memory.

Database Size

To estimate the increase in required disk space for upgrading, consider the products, the number of languages being installed, and changes in the data model.

For example:
In a test upgrade of the largest Oracle production system (oraprod), the database increased 10-20 percent. In a test upgrade, the Vision database increased 5 percent. For guidelines based on an upgrade of the Oracle production system (oraprod), see E-Business Suite Release 12 Upgrade Sizing and Best Practices (Doc ID: 399362.1).

Database Backup

*** We strongly recommend that you back up your entire system before you begin the upgrade. ***

Database Initialization Parameters

Initialization parameters required at each stage of the upgrade may vary depending on when you upgrade your database. Review the requirements for these parameters before you begin. Refer to metalink note ID 396009.1 for initialization parameters.
Change the following initialization parameters as specified below for upgrade process. Once the upgrade process completes, reset the parameters back.

db_file_multiblock_read_count – Remove this parameter. (this is not required).
_db_file_optimizer_read_count = 8 (default setting is 8. Keep default setting).
job_queue_processes (set the value of this parameters equal to number of CPUs).
parallel_max_servers (set the value of this parameters equal to twice the number of CPUs).
pga_aggregate_target (refer to metalink note ID 396009.1 for recommended value).

Make sure that the temporary tablespace you have is locally managed and not dictionary managed. You can check this information using below query.

select CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name=’TEMP’;

CONTENTS EXTENT_MANAGEMENT ALLOCATION_TYPE
———— —————– —————
TEMPORARY LOCAL UNIFORM

Else if the extent management is not local, you can drop and recreate temp tablespace using the below command.

NLS Upgrade Considerations

For NLS considerations, please refer to Applications upgrade docs.

Character Sets

You have to be careful while selecting the character set for APPL_TOP. Depending on whether your Applications system connects to the database during the upgrade process, you may be able to select a new character set for the Release 12 APPL_TOP on the Rapid Install wizard upgrade screens. However, if you do, the new set must be either identical to, or compatible with, the existing database character set. If you change the character set in the APPL_TOP to one that is not compatible with the current database character set, the upgraded system will be corrupted.

SQL> create TEMPORARY tablespace TEMP tempfile ’ts_p_temp1.dbf’ size 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2) Prepare for upgrade using Upgrade Manual Script(TUMS) and upgrading database

R12 upgrade process involve replacing 11i Tech stack (9iAS & 806) to Fusion Middleware (10g Application Server)
Basic upgrade process involves Rapid Install & Autopatch
Rapid Install involves installing new R12 tech stack as mentioned in first point
Auto patch process involves upgrading E-Business Suite database compatible to R12 (Data Model)
Final upgrade process is of updating data model using enhanced version of AutoPatch
Minimum version from which you can upgrade to R12 is 11.5.7 and higher
Minimum database version from which you can upgrade to R12 is 9i

As per Oracle R12 Upgrade Documentation, Apps 11i Instance is classified in Two Categories based on Apps & DB Version

Category 1 – 11.5.7, 11.5.8, 11.5.9 (CU1), 11.5.10 (CU1)
Category 2 – 11.5.9 (CU2), 11.5.10 (CU2) or 11.5.10.2

Why we cannot upgrade database to 10.2.0.2 before for category 1 ?
This is because there is no Interoperability patch for above release & 10.2.0.2 was supported for 11.5.9 (CU2) & 11.5.10(CU2) only.

What are advantages of Upgrading database to 10.2.0.2 before R12 upgrade ?
Downtime can be broken down to two small downtimes (one for Database upgrade another one for R12 upgrade) and can be achieved during weekends or long weekends (depending on your system & resources)

The following table lists the paths available for each of the E-Business Suite 11i releases supported for an upgrade to R12

PATH A:

Plan Upgrade to R12
- Follow the standard R12 upgrade path as documented in the Oracle Applications Upgrade Guide: Release 11i to Release 12. Perform all requirements documented in Chapter 1 and all applicable steps in Chapter 2.
Install the R12 Technology Stack and 10.2.0.2 Oracle home
Upgrade the Database to 10.2.0.3
Apply Database Patches
Apply the database patches as per the metalink note ID 403339.1. (6319846 for Linux)
Apply Oracle Service patch 5880762 (conditional)
Complete the R12 upgrade
Perform the remaining steps in Chapter 3 and all applicable steps in Chapter 4 to complete the upgrade to R12.

PATH B:

Plan Upgrade to R12
Apply Database Patches
Apply the database patches as per the metalink note ID 403339.1. (6319846 for Linux)
Apply Oracle Service patch 5880762 (conditional)
Complete the R12 upgrade

PATH C:

Plan Upgrade to R12
Upgrade Database to 10.2.0
Continue with the R12 Upgrade or Use the 11i/10.2.0 System
Apply Database Patches
Apply the database patches as per the metalink note ID 403339.1. (6319846 for Linux)
Apply Oracle Service patch 5880762 (conditional)
Complete the R12 upgrade

In our case, we are following path C. We were having 11.5.10 + CU2 application with 9i database. We will upgraded the database to 10GR2.

Then we will apply the database patches as per metalink note ID 403339.1 followed by service pack and then will upgrade the application to R12.

Now preparing the application system is till main step # 2 which includes upgrading the database as well. From main step 3, upgrade process for R12 starts. You may wish to stop after carring out the upgrade for database, run your business for some time (may be few months) and then go for R12 upgrade. This gives a comparatively less downtime for your existing application as you dont have to do all at the same time and can be done in steps.

The Upgrade Manual Script (TUMS) examines your current configuration and creates a report that lists upgrade tasks that do not apply to your system. This report contains information that is unique to your system configuration, so its output is relevant to your individual upgrade. Omitting the steps listed in the TUMS report can significantly reduce upgrade downtime.You create the TUMS report by applying a Release 11i patch, which loads objects into your APPS schema that TUMS uses to examine your Applications configuration. Your current Applications environment is not affected.

Below are the list of steps I am mentioning, which are the required steps. I am skipping the conditional steps here, just to make it brief. You can as well check the oracle documentation for the complete steps.

Step 1) Apply latest AD patch level. The latest AD patch is 11i.AD.I.6 and checkin number is 6502082.
SQL> select patch_level from fnd_product_installations
2 where patch_level like ‘%AD%’;

PATCH_LEVEL
——————————
11i.AD.I.6

Step 2) Run TUMS utility
– Download and apply TUMS patch (5120936). This will supply you one script “adtums.sql”, which you can run to generate the report.
– To generate the report
cd $AD_TOP/patch/115/sql
sqlplus <APPS username>/<APPS password> @adtums.sql <DIRECTORY>
<DIRECTORY> is where the report file will get generated. You need to create directory in database using create directory command. Also the directory path you are mentioning here should exists in UTL_FILE_DIR.

SQL> create directory APPS_DIR as ‘/usr/tmp’;

Directory created.

[applmgr@ocvmrh2081 sql]$ sqlplus apps/apps @adtums.sql APPS_DIR

step 3) Convert to Multiple Organizations architecture

Step 4) Review sizes of old and new tablespaces

Step 5) Run AD preparation scripts
Download patch 5726010. This will provide 3 scripts adgncons.sql, adgrants_nt.sql, adgrants.sql. Script adgrants_nt.sql is not for Linux and so can be ignored.
– First run the script adgncons.sql. This will create script adcrtbsp.sql. This script (adcrtbsp.sql) creates the new tablespaces, allocates unlimited tablespace to all APPS users, updates fnd_product_installation table with correct data and index tablespace information, assigns default tablespace to all APPS users, and sets the new_ts_mode flag in fnd_product_groups to Y.

[applmgr@ocvmrh2081 5726010]$ sqlplus apps/apps @adgncons.sql apps apps

Run adcrtbsp.sql with system user ID and password from database side

[applmgr@ocvmrh2081 5726010]$ sqlplus system/manager @adcrtbsp.sql

– adgrants.sql will grants SYS privileges needed by Applications, and creates required views in SYS.

You need to run this command “as sysdba”. This will prompt you for FND_ORACLE_USERID owner. You need to enter “applsys” when it prompts.

Step 6) Gather schema statistics for CBO
You can do this by Submitting “Gather Schema Statistics” concurrent request for “ALL” schemas.

Step 7) Backup Database
Take the backup of database before we go further with upgrade process

Step 8 ) Upgrading the database from 9i to 10g. You can refer to upgrade database in Oracle applications 11i post to have detailed steps. The database version should be 10.2.0.2

Step 9) Prepare the application for upgrade. You need to run the rapid wizard in upgrade mode. This will create the required file system, install the required techstack components. Below are the screen flow for the same.

After running this wizard, you will find that your existing application is intact and also a new file system has been created for you.

All your services should be up and running. Your 11i application should be intact. You might face an issue, that services (specially Apache) wont come up and your URL wont open. In this case you can check if the services get started from “inst” directory that got created after you run the wizard.

You can go to location /u01/app/applmgr/inst/apps/<TWO_TASK>/admin/scripts and run adstpall.sh script from that location. If the services were started from this location, then it will be stopped. Then you can start the services back from $COMMON_TOP/admin/script/<TWO_TASK> location.

Also during running of the wizard configuration information will be written to following files. you can check at your prompt and it will list the configuration files.

Configuration file written to: /u01/app/oracle/db/tech_st/10.2.0/appsutil/conf_PROD.txt
Configuration file written to: /u01/app/applmgr/apps/apps_st/appl/admin/ocvmrh2081/conf_PROD.txt
Configuration file written to: /u01/app/applmgr/inst/apps/PROD_ocvmrh2081/conf_PROD.txt

3) Upgrade Process

Below are the steps for upgrade process from 11.5.10.CU2 to 12.0.0. Till now we have just prepared the application for upgrade. Creating the neccessary filesystem layout and database upgrade was part of preparing for upgrade.

Step 1) Shut down application tier listeners and concurrent managers

Step 2) Back up the database – Once again take a backup. I am not crazy asking backup so many times, but in case we face any issue and if we dont have latest stable backup, we will be helpless.

Step 3) Ensure that Maintenance Mode is enabled – Put your application in maintenance mode

Step 4) Apply Release 12 AD minipack (4502962)
For applying this patch, you need to go in R12 APPL_TOP directory and source the env file present in that APPL_TOP. This is becasue you will be applying this patch to your new APPL_TOP and not 11i APPL_TOP. So make sure to source env file present in new APPL_TOP.

Step 5) Run the American English upgrade patch driver (u4440000.drv)
For applying this patch also, you need to go in R12 APPL_TOP directory and source the env file present in that APPL_TOP.

Step 6) Run the NLS upgrade patch driver (conditional)

For other product related steps and NLS synchronization, please check the Oracle Upgrade docs for 11i to R12.

Step 7) Disable Maintenance Mode

Once these step are carried out last step is finishing the upgrade process. Follow the below steps.

1) generate appsutil.zip on appmgr side and copy the same to new ORACLE_HOME. The new ORACLE_HOME directory structure will be created as /u01/app/oracle/db/tech_st/10.2.0
2) unzip appsutil.zip in new ORACLE_HOME
3) run autoconfig in new ORACLE_HOME

Once these steps are done, run rapid install again by provding the config file which was generated before. The file location as we noted down before is /u01/app/applmgr/apps/apps_st/appl/admin/ocvmrh2081/conf_PROD.txt

After these steps are performed, the application system will be configured and ready for use. This completes the upgrade activity from 11i to R12.

References:

Metalink Note ID : 394692.1
Metalink Note ID : 403339.1
Metalink Note ID : 396009.1
Metalink Note ID : 329476.1
Metalink Note ID : 215527.1