ORACLE APPS : Invalid object in Database

Today I had issue where object in EB 11.5.10 are invalid even after recompiling.

The following error message we see when we try to recompile the object

PACKAGE BODY APPS.CZ_RUNTIMEOn line: 1041PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:Text continued in next action...

After looking at code for the pkg body we find the offending SQL is of sub query in column list of select statement.

I also try to copy pkg body form working SID to non working SID. Which leave me to believe that the problem is inside Database binary and patches or database setup specially initialization parameter.

Here is a sample code we wrote to stimulate the issue.

create or replace procedure test_prc ( devl_prj in number)
as
rul_typ number := 200;
dt date;
begin
FOR i in ( SELECT 'A','B', (SELECT SYSDATE FROM DUAL) dt from dual)
loop
select sysdate into dt from dual;
end loop;
end;

This code is working on One environment and not on other.

Here is the v$version from two two Instance

SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 ProductionTNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 – Production


After comparing the initialization file I find that the init file for the SECOND SID have two section for
# Remove after upgrade to 11.5.9 #
event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context level 16384"

One was commented out and second uncommented. Looks like during upgrade process APPS DBA (that’s me ) does not expected duplicate block in init file.

Similar issue was also reported in metalink Doc ID 330426.1

After editing the init file and restarting the database. I recompile all invalid objects and it work fine.

Note: Just to Opposite the Issue we had 3 week before. I am going to summaries that one too.

ORACLE DB : What to do with this dump file

I was asked in short notice to check the dump file received from third party can be converted in any meaningful data? The

only information I was given a location of file.

There was no more information available and no contact to get more information.

Here how I proceed:

Check the file type

% type webconfig.dmp

webconfig.dmp: ascii.text

% head webconfig.dmp

UWEBCONFIG
RUSERS
1024
0
28
0
#G#A°#G#A°#CREATE SEQUENCE "SEQ_GUIBASECONFIGTABLE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 226 CACHE

3 NOORDER NOCYCLE
CREATE SEQUENCE "SEQ_GUIFEATURETABLE" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 921 CACHE 4

NOORDER NOCYCLE

%

This give me that this a oracle dump file from my past experience. Also I know that this is created for user level and the

schema name is WEBCONFIG

Now I have to find a working oracle database to find more information. I use my test database and perform following



$ imp system/***** show=y FILE=webconfig.dmp FULL=Y

Import: Release 9.2.0.1.0 - Production on Wed Jun 28 21:25:37 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by WEBCONFIG, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)

. importing WEBCONFIG's objects into SYSTEM
"CREATE TABLE "ATATTRIBUTETYPE" ("NAME" VARCHAR2(80) NOT NULL ENABLE, "DESCR"
"IPTION" VARCHAR2(80) NOT NULL ENABLE, "DATATYPE" NUMBER(10, 0) NOT NULL ENA"
"BLE, "ISSUMMABLE" NUMBER(1, 0) NOT NULL ENABLE, "STATUS" VARCHAR2(1) NOT NU"
"LL ENABLE, "LAST_UPDATED_BY" VARCHAR2(30) NOT NULL ENABLE, "TIME_STAMP" DAT"
"E) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 4"
"0960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREEL"
"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WEBCONFIG_DATA""
. . skipping table "ATATTRIBUTETYPE"


The outcome is truncated

From the above outcome I search for TABLESPACE and find out what tablespace are needed to make it successful load and the

character set information.

Now I have information to proceed. I created the required tablespaces and created a new user let’s call it CWP with connect

and resource role.

% imp system/****** FILE=webconfig.dmp FROMUSER=WEBCONFIG TOUSER=NEWUSER

The import terminated successfully without warnings.

One less thinng to worry about.

ORACLE APPS : APPS DBA vs ORACLE DBA

What the difference between an Oracle APPS dba and a regular Oracle dba?

From my point of view an APPS dba is an Oracle dba +. That means as an APPS dba , I believe you are an Oracle dba, but with additional tasks.

From OTN applications forum I found the following, which I believe is a correct summary of what an APPS dba should be capable of performing

1)Oracle DBA
2)Oracle Applications Concepts (Manual Available in oracle.com)
3)Concurrent Processing Architecture ( Use www.dbatoolbox.com or medallions if you have one)
4)Web and Forms Server Architecture (9iAS Doc's)
5)Using AD utilities and AD procedures (Manual available)
6)Installing and Upgrading Applications (manual available )
7)Cloning Oracle Applications(White paper)

ORACLE APPS : FAQ 1

Questions

1. What happens if the ICM goes down?
   
2. How will you speed up the patching process?
   
3. How will you handle an error during patching?
   
4. Provide a high-level overview of the cloning process and post-clone manual steps.
   
5. Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?
   
6. Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?
   
7. What could be wrong if you are unable to view concurrent manager log and output files?
   
8. How will you change the location of concurrent manager log and output files?
   
9. If the user is experiencing performance issues, how will you go about finding the cause?
   
10. How will you change the apps password?
    
11. Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.
    
    

Answers

1. All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.
   
   
2. 
   
   
   • You can merge multiple patches.
     
   • You can create a response file for non-interactive patching.
     
   • You can apply patches with options (nocompiledb, nomaintainmrc,
     nocompilejsp) and run these once after applying all the patches.
     
   
   
3. Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.
   
   
4. Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
   

   Manual steps (there can be many more):
   
   

   • Change all non-site profile option values (RapidClone only changes site-level profile options).
     
   • Modify workflow and concurrent manager tables.
     
   • Change printers.
     
   
   
5. AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is
   the centralized repository.
   

   When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files.
   

   For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.
   

   
   

6. 
   
   
   • Check guest user/password in the DBC file, profile option guest user/password, the DB.
     
   • Check whether apache/jserv is up.
     
   • Run IsItWorking, FND_WEB.PING, aoljtest, etc.
     
   
   
7. Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_ entry in tnsnames.ora.
   
   
8. The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.
   
   
9. 
   
   • Trace his session (with waits) and use tkprof to analyze the trace file.
     
   • Take a statspack report and analyze it.
     
   • O/s monitoring using top/iostat/sar/vmstat.
     
   • Check for any network bottleneck by using basic tests like ping results.
     
   
   
10. 
    
    • Use FNDCPASS to change APPS password.
      
    • Manually modify wdbsvr.app/cgiCMD.dat files.
      
    • Change any DB links pointing from other instances.
      
    
    
11. 
    
    • Location: $FND_TOP/secure directory.
      
    • Significance: Points to the DB server amongst other things.
      
    • The application knows the name of the DBC file by using profile option "Applications Database Id."
    • 
      
    • 
      
    • 
      

Ref and Ack : http://searchoracle.techtarget.com/tip/1,289483,sid41_gci1111676,00.html

ORACLE APPS : Concurrent Manager : troubleshooting

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase


Below are several different possible solutions to the problem where concurrent
requests are stuck in pending status:

1. When shutting down the concurrent manager are there any FNDLIBR processes still running at the OS level? If so, do a kill -9 on them. Then restart the concurrent manager.

2. Try Relinking $FND_TOP.

3. Rebuild the concurrent manager views. As applmgr run the following from
the OS:

This is non-destructive. Concurrent Manager views can be rebuild by running the following command at the command line:

Ensure that concurrent manager is shutdown.

FNDLIBR FND FNDCPBWV apps/apps SYSADMIN 'System Administrator' SYSADMIN

restart the concurrent mgr.

4. Another possibility is that the Profile Option "Concurrent: OPS Request Partitioning" is set to OFF (by default). Unless Concurrent OPS is being used, the setting for this profile option should be NULL.

5. Yet another possibility is that System Profile Option: Concurrent Active
Requests is set to 0.


1. Log into Oracle Applications as SYSADMIN.
2. Select System Administrator responsibility.
3. Navigate to PROFILE --> SYSTEM.
4. Query for %CONC%ACTIVE%.
5. Change the profile option for Concurrent: Active Request Limit to Null (blank).
6. Exit Oracle Applications and log in again for the change to take affect.
7. Run a new concurrent request.


6. Another possibility is that Concurrent managers were brought down, while an
outstanding request was still running in the background. In which case, Update
the FND_CONCURRENT_REQUESTS table as follows:
.
sql> update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
sql> commit;

7. The control_code for concurrent_queue_name = 'FNDCRM' is 'N' in the FND_CONCURRENT_QUEUES table, which means 'Target node/queue unavailable'. This value should be NULL (CRM is running; target and actual process amount are the same), or 'A' ('Activate concurrent manager' control status).

Set the control_code to 'A' in fnd_concurrent_queues for the Conflict Resolution Manager:

1. Logon to Oracle Applications database server as 'applmgr'.
2. Verify the Applications environment is setup correctly ($ORACLE_HOME and $ORACLE_SID).

3. Logon to SQL*Plus as 'APPS' and run the following SQL statement:

update fnd_concurrent_queues
set control_code = 'A'
where concurrent_queue_name = 'FNDCRM';

commit;

4. Verify the status of the concurrent managers through the
Concurrent -> Manager -> Administer form.

If the CRM is still not active, bounce (deactivate, activate) the Internal Concurrent Manager. This is done through the Concurrent -> Manager ->

Administer form from the 'System Administrator' responsibility. It can also be done through the CONCSUB command at the command level.

Setting the control_code to 'A' in the fnd_concurrent_queues table for the Conflict Resolution Manager indicates that this concurrent manager is to be activated with the parameter values specified through this table for this manager (MAX_PROCESSES, CACHE_SIZE, etc).

8. What is the cache size? Try increasing it then bounce the concurrent manager.

If concurrent requests are rarely prioritized and there are managers that service short-running requests, consider setting the cache size to equal at least twice the number of target processes. This increases the throughput of the concurrent manaagers by attempting to avoid any sleep time. For example, if more than one manager or worker processes the same type of requests with only a small cache size, it may be unable to process any jobs in a single processing cycle, because other processes have already run the cached requests. When this happens, it is important to note that the manager will sleep before refreshign its cache. To increase manager throughput where there are sufficient requests of the required type in the queue, increase the cache size to improve the chance of the manager finding work to process and thus avoid having to enter a sleep phase.

TIP: Ensure that the system is not resource-constrained before attempting to increase the rate of concurrent processing in this way; otherwise, these changes may actually reduce concurrent processing throughput because jobs take longer to run..

Enter the number of requests your manager remembers each time it reads which requests to run. For example, if a manager's workshift has 1 target process and a cache value of 3, it will read three requests,, and will wait until these three requests have been run before reading new requests.

In reading requests, the manager will only put requests it is allowed to run into its cache. For example, if you have defined your manager to run only Order Entry reports then the manager will put only Order Entry requests into its cache.

If you enter 1, the concurrent manager must look at its requests list each time it is ready to process another request. By setting the cache size at a higher number, the concurrent manager does not have to read its requests list each time it runs a request. However, the manager does not recognizea nay priority changes you make for a particular request if it has already read that request into its cache. Further, even if you give a higher priority to a new request, that new request must wait until the buffer isempty and the manager returns to look at the requests list. That request may have to wait a long time if you set the buffer size to a high number.

You should use cache size to tune your concurrent managers to work most efficiently for you site's needs. If your organization tends to reprioritize jobs going to a certain manager, that manager should have its buffer size set fairly low.

*Suggestion: *Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.



9. Please check the sleep seconds set for the concurrent manager which runs the report that is taking long time for completion. Reducing the sleep time will improve performance.

1. Check for the value set for the sleep seconds for the concurrent manager using the following path: System Administrator Responsibilty -> Concurrent -> Manager -> Define.

2. Check the processes running for the concurent manager using the following command:

ps -ef | grep 'INVLIBR'

As all concurrent programs are dealt by INVLIBR, the number of processes returned here must tally with the value specified for the processes defined for the concurrent manager.

3. Also, check for the duration for which the processes are sleeping. Reducing the value assigned for it would improve performance.

As a workaround to improve performance, you could change the priority of concurent request.

Explanation
-----------
The sleep state would be assigned to some tasks when the program running it has to handle more tasks than it has been configured to process. For example, if the number of processes has been defined as say 10, then at any point of time there is a provision to run 10 tasks. Now say, 12 tasks arrive for the program, then time slots are alloted for 10 and the rest 2 are put to sleep. So, if the concurrent program running the problem report is handling larger number of processes the sleep and run behaviour can be observed.

ORACLE APPS : Concurrent manager Basic :Where is log and out files ?

The concurrent manager first looks for the environment variable $APPLCSF
If this is set, it creates a path using two other environment variables:
$APPLLOG and $APPLOUT
It places log files in $APPLCSF/$APPLLOG
Output files go in $APPLCSF/$APPLOUT
So for example, if you have this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out
The concurrent manager will place log files in /u01/appl/common/log, and
output files in /u01/appl/common/out
Note that $APPLCSF must be a full, absolute path, and the other two are
directory names.
If $APPLCSF is not set, it places the files under the product top of the application associated with the request. So for example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
Logfiles go to: /u01/appl/po/9.0/log
Output files to: /u01/appl/po/9.0/out
Of course, all these directories must exist and have the correct permissions.
Note that all concurrent requests produce a log file, but not necessarily an output file

How to register tables and columns in AOL?

eg.SQL> EXEC AD_DD.REGISTER_TABLE (‘SQLGL’, ‘EXAM’,’T’)

where SQLGL is aplication short name and EXAM is the table name and T stands for Transaction Data

SQL> EXEC AD_DD.REGISTER_COLUMN (‘SQLGL’, ‘EXAM’,’SNO’, 1, ‘NUMBER’, 5, ‘N’, ‘Y’);

‘EXAM’ – Table Name

‘SNO’ – Column Name

1- Sequence one or column one

‘NUMBER’- Number Data type

5-Size

‘N’ – Null able

‘Y’ – Translate (Y/N)

ORACLE APPS : What is a FlexField ? What are Descriptive and Key

A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment.Each segment has a name and a set of valid values..

Two types of Flex field..

Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want wothout programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..

Descriptive Flexfield: They provide customizable "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

ORACLE APPS : AP Quicktip

Please run a count(*) on the following tables to ensure there is no data to be accounted, just to be on the safer side:

select count(*) from ap_invoice_distributions_all;
select count(*) from ap_invoice_payments_all;
select count(*) from ap_payment_history_all;

If no rows are returned for any of the above 3 queries, you can safely skip this step during the upgrade process.

ORACLE APPS : Multiorg Quicktip

MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit's transaction data separate and secure.

 select multi_org_flag from fnd_product_groups;


Use the Above query to determine if MuliOrg is intalled:

Set of Books : means Currency,Chart-of-accounts,Calender.

Currency: The currency which is being used by the company

Chart-of-accounts: the type of accounts follwed by the company

calender: the company follows which calender.i.e from jan1-Dec31 or April1-mar31.



The Multi Reporting Currency Feature allows you to report and maintain records at the transaction level in more than one functional currency. You can do by defining one or more set of books in addition to primary set of books.

What causing parser to choose table scan vs index scan

I'm trying to tune a query, and I am surprised by a relatively simple thing. I have a large (~1M rows) table ec_bug which

has an index on ( id, snapshot_date ). You would think that it would be faster to get at the IDs by doing an INDEX (FAST

FULL SCAN) than by looking at the table itself. In fact, that's what happens for this query:

> select _count(unique id)_ from j2se.ec_bug;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=593 Card=1 Bytes=5)
1 0 SORT (GROUP BY)
2 1 _INDEX (FAST FULL SCAN)_ OF 'EC_ID_IX' (INDEX)
(Cost=593 Card=875305 Bytes=4376525)


But if I want the actual IDs instead of just the count, the optimizer switches to a full scan of the table itself, at a much

greater cost:

> select _unique id_ from j2se.ec_bug;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2262 Card=13054
Bytes=65270)
1 0 SORT (UNIQUE) (Cost=2262 Card=13054 Bytes=65270)
2 1 _TABLE ACCESS (FULL)_ OF 'EC_BUG_TBL' (TABLE)
(Cost=2128 Card=875305 Bytes=4376525)


This is especially surprising since the index is already sorted by ID, so the sort would incur no cost.

Multiple failures to use this index in a more complex query are causing it to time out; does anyone know what might be

causing the optimizer to ignore this index if I want the actual ID value?

Followup 1:

I believe the issue is NULLs. If you allow NULLs in the "id" column, you will
have to sample the whole table to find them. Any function, such as "count"
will ignore rows with NULLs. But select unique id will include the NULL.

For example:

SQL> create table x (id number, dt date);

Table created.

SQL> insert into x values (NULL,sysdate);

1 row created.

SQL> insert into x values (2, sysdate);

1 row created.

SQL> insert into x values (3, sysdate);

1 row created.

SQL> insert into x values (4, sysdate);

1 row created.

SQL> insert into x values (5, sysdate);

1 row created.

SQL> insert into x values (6, sysdate);

1 row created.

SQL> select count (unique id) from x;

COUNT(UNIQUEID)
---------------
5

SQL> select unique id from x;

ID
----------
2
3
4
5
6


6 rows selected.

So the count can use the index and ignore any NULL rows (which are not in
the index, I believe). But the direct select has to include the NULL so it
has to go against the table itself.


Followup 2:

Those fields were part of several indexes, but not the primary key, and weren't otherwise constrained to be null. Changing them to NOT NULL caused the index to be used.

Referance : oracle interest alias

KB : ORACLE_Db\TUNING\Table_scan_vs_index_scan_example1.txt

ORACLE APPS : Database JDBC session pool setup

While monitoring the database connections, I observe that inactive JDBC Connections keeps on growing and are not getting dropped. You could use the SQL below to monitor these connections

REM SQL to check number of JDBC connections

set echo on
set feedback on
set pagesize 132
set linesize 80
col username format a10
col how_many format 9999
col machine format a25

select s.machine, s.username, s.module, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module from V$SESSION) s,
v$process p
where s.paddr = p.addr and s.program = 'JDBC Thin Client'
group by s.machine,s.username, s.module;

REM end of SQL

NOTE - it is quite normal for JDBC connections to show as INACTIVE status. This in itself does not mean there is a problem .


In brief, to tune the JDBC Connection Pool to minimise database connections (possibly at the expense of performance) you could modify these parameters as suggested below. Then run AutoConfig to regenerate the configuration files and re-check the DBC file to confirm your changes have been made. You then need to restart iAS for these parameters to take effect.

FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_DECAY_SIZE=100



Ref erance : Oracle metalink :
Note 278868.1 "Diagnosing and tuning AOL/J JDBC Pool in Oracle e-Business Suite 11i" will soon be available to provide documentation on how these parameters interact, however until this is published you can refer to Note 264599.1 "How to interpret parameters for Applications Database Connection Pooling" for an understanding of the JDBC Connection Pool parameters

KB : ORACLE APP/How_to_tune_JDBC_connection_pool.txt

Apache web server Mutiple Jserv setup

ApJservManual determines how JServ is started.

The directive ApJServManual directive in $ORACLE_HOME/Apache/Jserv/etc/jserv.conf can have 3 different values :

* off : In this case the Jserv engine is started up together with apache i.e. apachectl start will start up a series of http-processes and one or more Jserv process.
* on : This basicly means that Jserv needs to be started up manually.
* auto : This means that one or more Jserv processes will be started automatically together with the apache listener.

How many Jserv processes?
-------------------------

How many Jserv processes are started? Well, Apache scans the jserv.conf file for a series of mount points. These mount points are found on the basis of two series of directives :

=== if ApJServManual = auto
directives ApJServGroup, ApJServGroupSecretKey and ApJServGroupMount.

ApJServGroup , so if you want 2 jvm for group1 set
ApJServGroup group1 2 1 $OH/Apache/Jserv/etc/jserv.properties


In jserv.properties file:
- If the total number of processes specified in jserv.conf is n, then at least
n ports should be specified. Take into consideration that n is the number of
processes for all the groups. You can set an higher value, for example:
port=8007-8017