Calculate number of concurrent users of an existing instance

The view v$license keeps track of concurrent sessions and users.

SQL> desc v$license

 Name                                      Null?    Type

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

 SESSIONS_MAX                                       NUMBER

 SESSIONS_WARNING                                   NUMBER

 SESSIONS_CURRENT                                   NUMBER

 SESSIONS_HIGHWATER                                 NUMBER

 USERS_MAX                                          NUMBER

 CPU_COUNT_CURRENT                                  NUMBER

 CPU_CORE_COUNT_CURRENT                             NUMBER

 CPU_SOCKET_COUNT_CURRENT                           NUMBER

 CPU_COUNT_HIGHWATER                                NUMBER

 CPU_CORE_COUNT_HIGHWATER                           NUMBER

 CPU_SOCKET_COUNT_HIGHWATER                         NUMBER

select sessions_current from v$license;

The above query will give you the number of concurrent users right now.

You can write a small job which will capture this information every hour for a week.  Once you have this data, you can take an average of this data to get the number of concurrent users. 

FND_STATS doesn't collect statistics on external tables

Recently we ran Gather Schema Statistics for all tables:

1.Log in to Oracle Applications with the System Administrator responsibility.

2.Navigate to the Submit Request window (Request > Run).

3.Submit the Gather Statistics program.

Set the schema name to ALL to gather statistics for all Oracle Applications schemas (having an entry in the FND_PRODUCT_INSTALLATIONS table). In addition to gathering index and table-level statistics, the program gathers column-level histogram statistics for all columns listed in the FND_HISTOGRAM_COLS table.

4. Use GATHER_AUTO option in the program.  This option gathers statistics for objects that are either lacking statistics or whose rows have changed significantly (default 10%) since the last time you gathered statistics.

GSS got stuck when it was collecting statistics on external tables.  GSS is not designed to gather statistics on external tables.  Patch 5876047 needs to be applied to solve this problem.  This patch modifies FND_STATS package to exclude gathering statistics on external tables.  However after applying this patch GSS will again fail with Ora-1 Errors 

Metalink Note:470556.1 gives this workaround:

Take a backup of the FND_STATS_HIST table

Truncate table FND_STATS_HIST

Re-submit Gather Schema Statistics program.

You do NOT need to truncate this table after each run of Gather Schema Statistics. Doing it once will be enough, because each run of GSS will use a new request_id

Bugs introduced in 10.2.0.4 which will be fixed in 10.2.0.5

Metalink Note 555579.1 describes 30 bugs newly introduced in 10.2.0.4. However if I go to metalink advance search and give classification as Any, then I see > 500 patches. Some of these would be fixed in the next point release 10.2.0.5. The list of recommended patches is fewer in number. You should aim to get your instance patched with at least the recommended patches:

7164095 (For RAC instances)

7375644

7496435

7573282 (For RAC instances)

Oracle has since published another article which has list of all the bugs fixed in 10.2.0.5:

10.2.0.5 Patch Set - List of Bug Fixes by Problem Type [ID 1088172.1]

NUMA and 10.2.0.4

NUMA stands for Non uniform memory access.  It is a computer memory design used in multiprocessors, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors.

Here's a real world analogy from www.techgalaxy.net:

Imagine that you are baking a cake. You have a group of ingredients (=memory pages) that you need to complete the recipe(=process). Some of the ingredients you may have in your cabinet(=local memory), but some of the ingredients you might not have, and have to ask a neighbor for(=remote memory). The general idea is to try and have as many of the ingredients in your own cabinet as possible, since this reduces your time and effort in making the cake.

You also have to remember that your cabinets can only hold a fixed amount of ingredients(=physical nodal memory). If you try and buy more, but you have no room to store it, you may have to ask your neighbor to keep it in his/her cabinet until you need it(=local memory full, so allocate pages remotely).

Bug # 7171446 (Metalink Note 7171446.8) describes the problem of 10.2.0.4 with NUMA:

In 10.2.0.4 NUMA features are enabled by default and this can lead to a number of unexpected problems such as  skewed CPU usage and ORA-600 errors.

Workaround:

Unless the system has been specifically set up and tuned for NUMA then disable Oracle NUMA optimizations  by setting the following in the pfile / spfile / init.ora used to start the instances:

   

   _enable_NUMA_optimization=FALSE 

   _db_block_numa=1

Bug # 68680680 (Metalink Note 68680680.8) says:

ORA-4031 can occur when the NUMA option is used due to imbalance between shared pool subpools.  Subpool 1 shows memory allocated to "NUMA pool X" where X is a number.

This is fixed in 10.2.0.5.  Strangely, patch 68680680 is available for 10.2.0.3 but not for 10.2.0.4.

Fatal error: Don't know how to make target

Recently the DBAs faced this issue when they were applying TXK Rollup S patch and it was trying to relink:

Undefined first referenced

symbol in file

wfmlrsumpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

wfmlrgpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

ld: fatal: Symbol referencing errors. No output written to 

$ALR_TOP/bin/ALPPIM

*** Error code 1

make: Fatal error: Command failed for target 

`$ALR_TOP/bin/ALPPIM'

Done with link of alr executable 'ALPPIM' on Mon Nov 17 14:05:36 EST 2008

Relink of module "ALPPIM" failed.

make: Fatal error: Don't know how to make target 

`$ALR_TOP/bin/ALPPNR'

Done with link of alr executable 'ALPPNR' on Tue Nov 18 22:50:26 EST 2008

Relink of module "ALPPNR" failed.

make: Fatal error: Don't know how to make target 

`$ALR_TOP/bin/ALPPWF'

Done with link of alr executable 'ALPPWF' on Tue Nov 18 22:50:26 EST 2008

Relink of module "ALPPWF" failed

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAGDA'

Done with link of fa executable 'FAGDA' on Tue Nov 18 22:50:31 EST 2008

Relink of module "FAGDA" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPRET'

Done with link of fa executable 'FAMPRET' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPRET" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPSLTFR'

Done with link of fa executable 'FAMPSLTFR' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPSLTFR" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPTFR'

Done with link of fa executable 'FAMPTFR' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPTFR" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMRCL'

Done with link of fa executable 'FAMRCL' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMRCL" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMRST'

Done with link of fa executable 'FAMRST' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMRST" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FATAXUP'

Done with link of fa executable 'FATAXUP' on Tue Nov 18 22:50:33 EST 2008

Relink of module "FATAXUP" failed.

$ORACLE_HOME/rdbms/lib/ssbbded.o 

Undefined first referenced

symbol in file

inltev 

$WIP_TOP/lib/libwip.a(wilctv.o)

inltvu 

$WIP_TOP/lib/libwip.a(wilctv.o)

ld: fatal: Symbol referencing errors. No output written to 

$WSM_TOP/bin/WSCMTI

*** Error code 1

make: Fatal error: Command failed for target 

`$WSM_TOP/bin/WSCMTI'

make: Fatal error: Don't know how to make target 

`$WSM_TOP/bin/WSCMTM'

Done with link of wsm executable 'WSCMTM' on Tue Nov 18 22:51:05 EST 2008

Relink of module "WSCMTM" failed.

Following undefined symbol errors were appearing:

make: Fatal error: Don't know how to make target 

Undefined first referenced

symbol in file

inltev 

$WIP_TOP/lib/libwip.a(wilctv.o)

inltvu 

Undefined first referenced

symbol in file

wfmlrsumpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

wfmlrgpl

When we relinked them with force option, they relinked fine:

adrelink.sh "force=y" "WSM WSCMTI"

adrelink.sh "force=y" "ALR ALPPIM"

The other binaries still failed.  On a hunch we checked the make file used to relink the binaries:

grep FAGDA $FA_TOP/lib/fa.mk

grep WSCMTM $WSM_TOP/lib/wsm.mk

The make files did not have any record of the binaries which were failing relink.  The relink command was unable to relink these binaries because they were not present in make file.

We took a backup of the existing make files and copied higher version of the make files from a different instance:

$ adident Header $FA_TOP/lib/fa.mk.old

fa.mk.old:

$Header fa.mk 115.3 99/07/16 01:53:07 porting ship $

$ adident Header $FA_TOP/lib/fa.mk

fa.mk:

$Header fa.mk 115.9 2002/05/20 03:43:30 pkm ship    $

The new make files had the binaries which were failing relink.

When we relinked again after copying the make files, it went fine.

alter java class "oracle/apps/per/proxy/client/forms/UrlThread" compile hangs

Mohan pinged me today and said:

Two workers are hung and log file says 

Loading contents of archive file into database with arguments

-Xmx512M -XX:+UseParallelGC -XX:ParallelGCThreads=4 -XX:LargePageSizeInBytes=4M oracle.aurora.server.tools.loadjava.LoadJavaMain -f -thin -user "APPS/*****@someserver.justanexample.com:1521:dba" /$APPLCSF/$APPLOUT/p001ldjva.jar 

I asked him to check v$session_wait, and he reported back:

COUNT(*) EVENT

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

1 Streams AQ: waiting for messages in

the queue

2 row cache lock

4 Streams AQ: qmn slave idle wait

3 library cache lock

1 Streams AQ: qmn coordinator idle wa

it

1 Streams AQ: waiting for time manage

ment or cleanup tasks

SQL_TEXT

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

alter java class "oracle/apps/per/proxy/client/forms/UrlThread" compile 

I asked him to check the DB alert log.  This is what he found:

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 

There are a lot of metalink notes if you search for the above phrase.  All of them indicate latch contention of some sort.

I asked Mohan to stop the patch.  Bounce the database and restart the patch.

After bouncing the database and restarting the patch, the issue went away.

Unable to Flush Restart File in AIURWL()

Yesterday, Akhilesh pinged me with this error while applying a patch:

After I renamed the $APPL_TOP/admin/log/$TWO_TASK/restart directory, the error changed to:

Adpatch error Unable to Flush Restart File in AIURWL()

After a lot of trial and error I noticed that there was a file in $APPL_TOP/admin/log/$TWO_TASK directory called adpatch_sessions_to_clean.txt.  On a hunch I renamed this file as adpatch_sessions_to_clean.txt.old.

Voila,  the patch proceeded without any issues and got applied too.

There is no reference of this file in Metalink anywhere.  Inside the files were session numbers of some old sessions.

Script to check what sqls are being run by users

Metalink Note 135749.1 gives a great script to check what sqls are being run by users at any given time:

set pagesize 66

col c1 for a9

col c1 heading "OS User"

col c2 for a9

col c2 heading "Oracle User"

col b1 for a9

col b1 heading "Unix PID"

col b2 for 9999 justify left

col b2 heading "SID"

col b3 for 99999 justify left

col b3 heading "SERIAL#"

col sql_text for a35

break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,

a.sql_text

  from v$sqltext a, v$session b, v$process c

   where a.address    = b.sql_address

--   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE

--                                  ACTVE TRANSACTION ON THAT MOMENT */

   and b.paddr      = c.addr

   and a.hash_value = b.sql_hash_value

 order by c.spid,a.hash_value,a.piece

/        

Check Metalink Note 131704.1 for Oracle's Database scripts library index

Pasta mandatory for printing reports with UTF8 characterset

Sanjeev called today to find out when Pasta printing is required.

Metalink Note 99495.1 says:

1. If the datbase character set is UTF8, this Postscript setup document CANNOT

   be utilized.

   select value from nls_database_parameters 

   where parameter = 'NLS_CHARACTERSET';

   Pasta and IX Library is required for UTF8 character set environments and/or 

   multi-lingual environments. The printing chapter of the Oracle Applications

   11i System Administrator's Guide states, "In order to print reports with the 

   UFT8 character set, you 'must' configure PASTA". See the following documents 

   for more details. 

   Note 240864.1 "Activating and Configuring IX Library"

   Note 189708.1 "Oracle Reports 6i Setup Guide for Oracle Applications 11i"

   Note 239196.1 "PASTA 3.0 Release Information"--particular the "Pasta User's Guide 3.0"

   Note 356501.1 "How to Setup Pasta Quickly and Effectively"

Node id does not exist for the current application server id.

I am doing sanity checks on an instance which has been converted to shared application file system.  The DBA was working on it till late night, so he is not available now.  I was unable to access the home page.  I saw some old Apache and Jserv process.  When I tried to stop apache, I got the following error:

$ adapcctl.sh stop

adapcctl.sh version 115.55

Node id does not exist for the current application server id.

Apache Web Server Listener is not running.

Node id does not exist for the current application server id.

Node id does not exist for the current application server id.

Apache Web Server Listener (PLSQL) is not running.

Node id does not exist for the current application server id.

A search on metalink yielded note 359998.1 which suggested a mismatch in fnd_nodes.server_id and APPLICATION_SERVER_ID variable in the dbc file.  Running adgendbc.sh was suggested as a solution.  So I manually ran $COMMON_TOP/admin/install/adgendbc.sh.  This recreated the dbc file.  After this I was able to successfully stop and restart Apache and get to the instance.

How to generate server_id for a new node manually

During a server move, Anand asked me this question:

How to generate server_id for a new server to which the instance has moved, without running autoconfig. The answer is the AdminAppServer utility. As per metalink note 93590.1:

The AdminAppServer class will perform three functions:

- Create an encoded SERVER_ID for this machine
- Create a _.dbc file in the $FND_TOP/secure
directory that contains the SERVER_ID as well as other machine - and
database-specific information.
- Add an row in the database table FND_APPLICATION_SERVERS with the
SERVER_ID and other machine-specific information.

As per Metalink Note 150475.1:

There are 3 'AdminAppServer' commands in the adgendbc.sh script. The order in the adgendbc.sh file (from the beginning of the file) should be:

AdminAppServer DELETE
AdminAppServer ADD
AdminAppServer UPDATE

Metalink note 187403.1 says:

Adding, Updating or Deleting Server IDs

Application servers can be registered as trusted machines with
a database server. This works by generating a large random ID
number and storing that in both the application server and the
database. When the application server attempts to connect to the
database it will supply its server ID and the database will verify
that it matches a trusted machine. The server ID is stored as a
parameter in the DBC file for each application server. It can
also be defined as an environment variable. The AdminAppServer
utility is used to generate server IDs and then register them
with a database server.

To add a server ID:

Server ID values are generated by the AdminAppServer utility, and
therefore cannot be supplied on the command line. They will be
added to the database automatically when the AdminAppServer is used
to create a dbc file:

jre oracle.apps.fnd.security.AdminAppServer apps/apps \
ADD [SECURE_PATH=$FND_TOP/secure] \
DB_HOST= \
DB_PORT= \
DB_NAME=

See the section on Creating DBC files in Administering Oracle
Applications Security in Release 11i for more details.

However the above command fails. We can get the correct command, if we open $COMMON_TOP/admin/install/adgendbc.sh:

$COMMON_TOP/util/jdk1.6.0_05/bin/java
-classpath "$COMMON_TOP/util/jdk1.6.0_05/lib/dt.jar:$COMMON_TOP/util/jdk1.6.0_05/lib/tools.jar:
$COMMON_TOP/util/jdk1.6.0_05/jre/lib/rt.jar:$COMMON_TOP/util/jdk1.6.0_05/jre/lib/charsets.jar:$COMMON_TOP/java/appsborg2.zip:$COMMON_TOP/java/apps.zip:$ORACLE_HOME/forms60/java:$COMMON_TOP/java" oracle.apps.fnd.security.AdminAppServer ADD FNDNAM=$USERNAME GWYUID=APPLSYSPUB/PUB "$DBC_CONNECT_ID" TWO_TASK=STAGE11I GUEST_USER_PWD=GUEST/ORACLE SECURE_PATH=$FND_TOP/secure
APPS_JDBC_DRIVER_TYPE=THIN DB_PORT=1521 DB_HOST=stage11i.justanexample.com NODE_NAME=$HOST_NAME FND_MAX_JDBC_CONNECTIONS=500 FND_JDBC_STMT_CACHE_SIZE=200 -nopromptmsg

550 5.7.1 Relaying Denied

This is one of the most common errors after setting up your mail server.   All mail servers deny relaying by default due to the worldwide problem of spams.  In Oracle Apps if you see this error in your workflow mailer notification logs or in /var/log/syslog, you should check for these:

The Workflow mailer works in this way:

A connection is made from application tier server to the mail server on port 25 (SMTP)

When the smtp server gets the mail from application server, it first checks whether the application server's IP exists in /etc/hosts of the sendmail server.  In our environments, we usually configure the concurrent tier as the mail server by starting the sendmail server on it.

Unless and until you have the entries for your application web servers in /etc/hosts, sendmail will not accept the mail for relay to any destination except its own domain.

For example, if I connect to port 25 and send a mail to a different domain

telnet vip.justanexample.com 25

Trying 192.168.1.17...

Connected to dbtier.justanexample.com

Escape character is '^]'.

220 physicalserver.justanexample.com ESMTP Sendmail 8.13.8+Sun/8.13.8; Sat, 15 Nov 2008 10:33:45 -0500 (EST)

MAIL FROM: john.doe@justanexample.com

250 2.1.0 john.doe@justanexample.com... Sender ok

RCPT TO: jane.doe@someotherdomain.com

550 5.7.1 jane.doe@someotherdomain.com... Relaying denied

However if the @ in the mail is the mail server name then relaying is ok.

RCPT TO: mailuser@vip.justanexample.com

250 2.1.5 mailuser@vip.justanexample.com... Recipient ok

The rule is 

1. Mail will be accepted from all IPs as long as the server name after the @ in email address is the mail server or the hostnames present in /etc/mail/local-host-names.  For our example, make sure that you have the logical and physical hostnames of all application servers and the DB cluster name in /etc/mail/local-host-names.

2. Mail will be accepted for relay from those IPs whose IP and hostnames are correctly mentioned in /etc/hosts file of the mail server in this format:

IP address   servername.domainname   servername

192.168.1.17    vip.justanexample.com    vip

If you take care of these two things, you'll not see the 550 5.7.1 Relaying denied message in Oracle Apps 11i.

/undefinedfilename GPL Ghostscript 8.54 unrecoverable error

Recently I got this error:

$ pdf2ps how_to_chose.pdf

Error: /undefinedfilename in (how_to_chose.pdf)

Operand stack:

Execution stack:

   %interp_exit   .runexec2   --nostringval--   --nostringval--   --nostringval--   2   %stopped_push   --nostringval--   --nostringval--   --nostringval--   false   1   %stopped_push

Dictionary stack:

   --dict:1120/1686(ro)(G)--   --dict:0/20(G)--   --dict:70/200(L)--

Current allocation mode is local

Last OS error: 2

GPL Ghostscript 8.54: Unrecoverable error, exit code 1

This error comes if pdf2ps is unable to find the file.  Check whether you have given complete path or whether you are currently in the directory where the file is located.

The AutoConfig CVM script returned an error code

Raju pinged me today with this error:

adpatch options=prereq logfile=u6831988.log driver=u6831988.drv 

 

Running AutoConfig to instantiate templates which affect JAR files....

Running AutoConfig CVM to maintain the context file...

AutoPatch error:

The AutoConfig CVM script returned an error code.

AutoConfig Return code : [1]

Command used was : [$AD_TOP/bin/adcvm.sh contextfile=$CONTEXT_FILE appspass=***** logfile=$APPL_TOP/admin/$TWO_TASK/log/autoconfig_17931.log -noprompt]

An error occurred while

Running AutoConfig to instantiate templates that affect the generation of

JAR files. If you choose to continue now, you may have to re-generate JAR

files using the AD Administration utility later.

Continue as if it were successful [No] :

An error occurred while

Running AutoConfig to instantiate templates that affect the generation of

JAR files. If you choose to continue now, you may have to re-generate JAR

files using the AD Administration utility later.

Continue as if it were successful [No] :

You should check the file

$APPL_TOP/admin/$TWO_TASK/log/u6831988.log for errors. 

I asked Raju to run adcvm.sh manually to see what is the error:

$AD_TOP/bin/adcvm.sh contextfile=$APPL_TOP/admin/erppgra1_erppgra1.xml appspass=first123 logfile=$APPL_TOP/admin/$TWO_TASK/log/autoconfig_17931_manual.log 

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/ad/autoconfig/PerformanceProfileReport

at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:700)

Caused by: java.lang.ClassNotFoundException: oracle.apps.ad.autoconfig.PerformanceProfileReport

at java.net.URLClassLoader$1.run(URLClassLoader.java:200)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:188)

at java.lang.ClassLoader.loadClass(ClassLoader.java:306)

at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:276)

at java.lang.ClassLoader.loadClass(ClassLoader.java:251)

at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)

adcvm: Error code returned from CVM - 1

As per above message adcvm.sh is failing because of this:

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/ad/autoconfig/PerformanceProfileReport

I asked Raju to check for the existance of this class :

$OA_JAVA/oracle/apps/ad/autoconfig/PerformanceProfileReport.class

Sure enough this was missing.  I did an ls -ltr |wc -l to check the no. of files and it was 14 in this instance.  When I compared it with another instance on same patch level, I found that the directory had 17 files.   I sent the 3 missing files to Raju.  After he copied the files, adcvm.sh worked fine.

I asked Raju to apply TXK Rollup S patch to reinstate any seeded files which are missing.

OATM doesn't drop some seeded tablespaces

During our OATM migration, we found that fndtsmig.pl did not drop some seeded tablespaces which were empty:

  1  select segment_type,segment_name

  2  from dba_segments

  3  where tablespace_name in

  4  (

  5  'ZXX',

  6  'ZXD',

  7  'ZPBX',

  8  'ZPBD',

  9  'XLEX',

 10  'XLED',

 11  'MSTX',

 12  'MSTD',

 13  'LNSX',

 14  'LNSD',

 15  'IZUX',

 16  'IZUD',

 17  'IAX',

 18  'IAD',

 19  'GCSX',

 20  'GCSD',

 21  'FUNX',

 22  'FUND',

 23  'FPAX',

 24  'FPAD',

 25  'ASNX',

 26  'ASND',

 27  'AMWX',

 28  'AMWD'

 29* )

SQL> /

no rows selected

In an SR logged with Oracle, they said:

Hi,

Sometimes this happens with OATM.

But the cause is yet to be determioned.

If no objects are present i these tablespaces they can be dropped manually without issues.

Based on this, we dropped these empty tablespaces manually.