Skip to main content

Posts

Showing posts from 2010

patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long

Following error occurs while running patchsets.sh supplied by Metalink Document Oracle Applications Current Patchset Comparison Utility - patchsets.sh [ID 139684.1]

patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
patchsets.sh[295]: /usr/bin/grep: 0403-027 The parameter list is too long.
Error is thrown not only for grep but even for all the OS utilities(sed,date,awk,tr,cp, rm) that are used in the script.

This happens because by default the ouput files are created under /tmp and if and only if /tmp has way too many f…

Standalone/In-process Reports Server High Availability

Lately i have been involved in multiple HA configurations for Ebusiness suite, stand alone forms, iAS. Each of these are well tried and tested methods and there are only few consequences where these fail during initial configuration. But with stand alone reports HA atleast to my knowledge there is very little documentation or customers who implement reports load balancing is very less.

Either customers choose to keep reports server on a different node and route all their requests to 1 node or use session persistance and run report on the same sever where forms requests are routed.

For standalone reports mainly ones needs to understand that reports can run as standalone reports process or in-process(oc4j BI Forms).

By default when you install 10GiAS(with forms and reports), oc4j services for forms and reports are configured into 1 single oc4j call oc4j_BIForms.
One can configure stand alone reports after default installation, use metalink document,
862546.1 : Is it possible to Rename …

Autoconfig -- Indetail

Autoconfig as the name says used for automatic configuration/regeneration/reconfiguration for most of/all  the configuration files in Oracle Applications. This is one of the AD tools that i will be starting to detail.

Autoconfig can either be started using a shell script in Unix or cmd executable in windows under $COMMON_TOP/admin/script/$CONTEXT_NAME/adautocfg.sh for Unix

This inturn calls a shell script adconfig.sh under $AD_TOP/bin. You can either use adautocfg.sh or adconfig.sh to run autoconfig. Only difference is that you will have to supply the context file(SID_host.xml) if you run adconfig.sh.

APPS password is mandatory for autoconfig, so make sure you have apps password handy before starting autoconfig.

Follwing are the details after adconfig.sh is invoked through adautocfg.sh.

1. Check Perl version
2. Locate Perl and its libraries(For application tier or for database tier  -- If application tier then locate perl in  APACHE_TOP, for database tier then ORACLE_HOME)
3. Check i…

Modifying OID startup parameter -- configset0

There are 2 ways to modify OID configuration set.

1. Through Oracle Directory Manager console.

A. Login to Oracle Directory Manager console,
B. Click on Server Management --> Default Configuration Set

Modify required values

2. From backend using ldapmodify.

A. To list configset from command prompt

ldapsearch -p  -h  -D cn=orcladmin -w  -L -b "cn=configset0,cn=osdldapd,cn=subconfigsubentry" -s base objectclass=*

dn: cn=configset0, cn=osdldapd, cn=subconfigsubentry
orclsslwalleturl: file:
orclsslversion: 3
orclsslport: 636
orclsslenable: 2
orclsslauthentication: 1
orclserverprocs: 4
orclsaslmechanism: DIGEST-MD5
orclsaslmechanism: EXTERNAL
orclsaslcipherchoice: rc4-56
orclsaslcipherchoice: des
orclsaslcipherchoice: 3des
orclsaslcipherchoice: rc4
orclsaslcipherchoice: rc4-40
orclsaslauthenticationmode: 1
orclnormdn: cn=configset0,cn=osdldapd,cn=subconfigsubentry
orclnonsslport: 389
orclmaxcc: 10
objectclass: top
objectclass: orclConfigSet
objectclass: orclLDAPSubConfig
cn:…

Checking Application versions

Checking Portal version:

select * from portal.wwc_version$;



SSO Version:

SQL> select version from orasso.WWC_VERSION$;

VERSION
--------------------------------------------------------------------------------
10.1.4.3.0


OID Version:

$ORACLE_HOME/ldap/bin>oidldapd -version
oidldapd: Release 10.1.4.3.0 - Production on fri jun 4 22:12:16 2010
Copyright (c) 1982, 2008 Oracle. All rights reserved.



Oracle Applications -- Ebussiness Suite:

SQL> select release_name from applsys.fnd_product_groups;
RELEASE_NAME
-------------------------------------------------
11.5.9

Changing user password in Oracle Applications 10.7

Unlike 11i 10.7 does not have any utility for changing passwords for application users. Follow the steps below to change the password of any application user on 10.7. Example is shown for sysadmin user.


1. Find the encrypted value that ICX_CALL.CRCHASH is returning for the SYSADMIN password

In SQL*Plus as APPS owner:

SQL> variable x number

SQL> execute :x :=icx_call.crchash('SYSADMIN', 'sysadmin');



PL/SQL procedure successfully completed



SQL> print x



2. Change SYSADMIN's encrypted password to the value returned for x.

SQL> update fnd_web_users
          set password = 'VALUE RETURNED FOR X'
          where username = 'SYSADMIN';


3. Login as SYSADMIN/sysadmin. This should now be successful.

NOTE: Take a backup of FND_WEB_USERS before changing the password.



If the above procedure does not work or if you do not find the scripts then you must be on a lower version of AOL, try the following (This should work).

1. Take a backup of table FND_…

Integrating Oracle Business Intelligence with eBS

Following are the brief steps:
Single Sign-On first into Oracle E-Business Suite and have that authentication passed through to Oracle BIEE Embed links to Oracle BI EE dashboard/catalog in Oracle E-Business Suite pages Create Action Links within Oracle BI EE reports and dashboards that take you back into Oracle E-Business Suite in context
 Follow metalink document: 555254.1 for step by step guide for implementation

Concurrent Request Status and Phase Codes

Concurrent Requests status can either be found under Find Requests form or from the database table applsys.fnd_concurrent_requests. Request status is determined by 2 colums Status and Phase Code. Following are the different entries and their meaning for both the columns.

STATUS_CODE Column:

A Waiting

B Resuming

C Normal

D Cancelled

E Error

F Scheduled

G Warning

H On Hold

I Normal

M No Manager

Q Standby

R Normal

S Suspended

T Terminating

U Disabled

W Paused

X Terminated

Z Waiting



PHASE_CODE column:

C Completed

I Inactive

P Pending

R Running

ORA-20000 -- Buffer Overflow

Often when we use dbms_output.putline in our sql's to print on the console after setting serveroutput on, it throws an error saying buffer overflow.

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 9

Option to get rid of this issue are,

1. SET SERVEROUTPUT ON SIZE 100000000
You might get an error on 9i as below,
SP2-0547: size option 276447232 out of range (2000 through 1000000)
In 10g you can specify UNLIMITED for SIZE
SET SERVEROUTPUT ON SIZE UNLIMITED

2. exec DBMS_OUTPUT.ENABLE(10000000);
This works for any value on 9i and it accepts UNLIMITED on 10gR2.

Else the other option is to use UTL_FILE to flush the output to a file.

Pre-requisite to use UTL_FILE is to create a database directory.

CREATE DIRECTORY 'DIRNAME' AS 'PATH' '';

Path should already be adde…

11.5.10.2 Cloning/Migration from Linux 4 to Linux 5

Lately i wanted to try out an upgrade from 11.5.10.2 to R12.1.1. For this i needed to clone APPS from Linux 4 to Linux 5. I tried to search for some document on metalink but nothing concrete. All i got was 287453.1 Oracle Applications 11.5.10 - Installation Update Notes for Linux x86, Solaris SPARC, MS Windows, HP-UX PA-RISC, HP-Tru64, IBM AIX - B13590-01

Apart from this they do not even have a document on metalink/support portal certification matrix. So here it is.

OS Version:

Source:
Red Hat Enterprise Linux AS release 4 (Nahant)
2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

Destination:

Red Hat Enterprise Linux Server release 5.3 (Tikanga)
2.6.18-128.el5 #1 SMP Wed Dec 17 11:42:39 EST 2008 i686 i686 i386 GNU/Linux



DB Cloning:
--------------------------

Copy all the files from source to destination. Set the following env variables.
FILES: ORACLE_HOME, ALL DB FILES, CONTROL FILES, REDO FILES


Now set the env to start the database, As an example following was m…

Create Control File

I keep forgetting the syntax for create control file, thought it would help me and others for the syntax and the steps to create.

STEPS:


1. Shutdown the database
2. sqlplus '/as sysdba'
3. startup nomount
4. Create Control file (syntax below)
5. alter database open

CREATE CONTROLFILE REUSE DATABASE "anydatabasename" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE
GROUP 1 'REDLOG PATH' SIZE 10M,
GROUP 2 'REDLOG PATH' SIZE 10M
DATAFILE
;


EXAMPLE:

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE
GROUP 1 '/APPS/proddata/log01a.dbf' SIZE 10M,
GROUP 2 '/APPS/proddata/log02a.dbf' SIZE 10M
DATAFILE
'/APPS/proddata/a_txn_data01.dbf' ,
'/APPS/proddata/a_txn_data02.dbf' …

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS.

Parameters to check for this are shmall.

shmall is the total amount of shared memory, in pages, that the system can use at one time.

$cat /proc/sys/kernel/shmmax
53687091200

$ getconf PAGE_SIZE
4096

As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


Once this value is calculated you can modify Linux system configuration file directly.

$ su - root
vi /etc/sysctl.conf file:
kernel.shmall=16777216

and

# sysctl -p

Once this is done the database was started without any problem.

Indexes -- Basics

Indexes provide faster access to rows. Indexes can be created on any column. Usually search on a column is faster if an index exists on that column.

Indexes are faster means to access database objects. By this defination it is clear that indexes are or
should be used for performance enhancements. But this is not always true, using indexes will not always help
in improving the performance.


Indexes are always independent of the physical or logical data/objects or even the database, meaning creating

and deleting(dropping) an index will harm the data or the functionality of your code. It only affects your
performance.

Though indexes are independent they require storage space.


I always get queries from my customers that we have an index but my database doesnt use it. I have created an index but after creating it my queries are slow.


SYNTAX: CREATE INDEX index_name ON table_name (column_name); EXAMPLE: CREATE INDEX anyname ON emp (sal); DROPPING INDEX: DROP INDEX anyname;

v$osstat

Need to know the OS stat on your database instance run,

>select * from v$osstat;

STAT_NAME VALUE OSSTAT_ID
---------------------------------------------------------------- ---------- ----------
NUM_CPUS 2 0
IDLE_TIME 29455741 1
BUSY_TIME 1188689 2
USER_TIME 281504 3
SYS_TIME 907185 4
AVG_IDLE_TIME 14725324 7
AVG_BUSY_TIME 9.2234E+18 8
AVG_USER_TIME 138792 9
AVG_SYS_TIME …

Confusion FY and CY

These are very important abbreviations when it comes to handling projects, so make sure that there is no confusion.

FY -- Financial Year or commonly called as Budget Year
CY -- Calendar Year

FY is country dependent and CY probably/definitely same for all the countries if they follow Gregorian Calendar.

FY for US starts from Oct, For Australia it starts from July, For India it starts from April likewise its different for different countries.

CY always starts from Jan.

Database Certification

Most common questions asked about database certification:
1. Whats the next patchset level
2. When is the next release
3. When will the next release for OS. etc

Answer to all these questions lies in a metalink(now Oracle Support Portal) document 161818.1 Oracle Server (RDBMS) Releases Support Status Summary which is published external so anybody who has access to Oracle Support Portal can access this.

This document talks about Current Patchset for each version of Oracle database and the next inline for release. This document also contains information about extended maintenance support(EMS).

Document 161818.1 points to Document 742060.1 Release Schedule of Current Database Releases, which contains information about release schedule for current releases be it patchset or a version release.