Script to find free space in tablesapces

Script to find free space in tablesapces



select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB, 
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
group by a.tablespace_name;

32bit to 64bit migration on same oracle database version 9.2.0.6

Plan
-----

1. Raise an SR with oracle support to get 9.2.0.6 patch set.
2. Download base release 9.2.0.1 from edelivery.oracle.com
3. Create a new folder and install 9.2.0.1 base release
Note: OPatch folder will not be available with 9.2.0.1 base release

4. Apply 9.2.0.6 patch set on top of 9.2.0.1 base release.
5. set environment to point to new oracle home and apply patches which are already there on 32 bit oracle home

opatch apply may fail with error 73

OPatch detects your platform as 453 while this patch is for 23

To resolve this you have to set env  variable OPATCH_PLATFORM_ID=23 <>

6. Shut down 32bit db, copy directories dbs, admin, network make appropriate changes (oracle home location) to configuration files, increase SGA and PGA size .

7. startup migration and run utlrp.sql

8. run catpatch.sql

9. Bounce DB and release for testing.


Regards
Manoj

Find current SCN number in Database

select current_scn from v$database;
select dbms_flashback.get_system_change_number() from dual;

-- find where current SCN lives in SGA:

SQL> select ksmfsadr from x$ksmfsv where ksmfsnam = 'kcsgscn_';

KSMFSADR
--------
20009104

-- on 32bit environments this is the most significant half word of 8 byte SCN. To get the other, least significant half, you have to add 4 to the address and combine the results:

SQL> l
 1  select dbms_flashback.get_system_change_number flashback_scn,
 2         current_scn,
 3         (select to_number(ksmmmval,'XXXXXXXX')
 4         from x$ksmmem where addr = hextoraw('20009104')) * power(2,32) +
 5         (select to_number(ksmmmval,'XXXXXXXX')
 6         from x$ksmmem where addr = hextoraw('20009108')) direct_scn
 7* from v$database
SQL> /

FLASHBACK_SCN CURRENT_SCN DIRECT_SCN
------------- ----------- ----------
     2633692     2633692    2633692

The SCN lives in fixed part of SGA, thus its location doesn't change over instance bounces or SGA size changes. It might change only if you relink Oracle binary or change SGA mapped base address.


source:freelists.org 
Regards
Manoj 

Database Size Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)
   2,048                      8              512 

   4,096                     16            1,024

   8,192                     32            2,048

  16,384                     64            4,096

  32,768                    128            8,192

The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).

 Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048                  8,192          524,264 

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

Logical Database limit

Logical Database Limits

Item	Type of Limit	Limit Value
GROUP BY clause	Maximum length	The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
Indexes	Maximum per table	Unlimited
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
Columns	Per bitmapped index	30 columns maximum
Constraints	Maximum per column	Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause
Partitions	Maximum length of linear partitioning key	4 KB - overhead
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K - 1
Rows	Maximum number per table	Unlimited
Stored Packages	Maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. See Also: Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically 32
Users and Roles	Maximum	2,147,483,638
Tables	Maximum per clustered table	32 tables
Tables	Maximum per database	Unlimited

Process and Runtime Limits

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	32 KB; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

*Source : OTN
 

Regards

Manoj

Oracle Database Upgrade Path

Oracle Database Upgrade Path
----------------------------

8.1.7.X.X

8.1.7.X.X TO 8.1.7.4.0
1. Install the 8.1.7.4.0 patchset on top of the existing 8.1.7.X.X ORACLE_HOME
Patchset number is : 2376472
||Patch 2376472||

2. Finish the post installation steps as per the patchset README (patch_note.htm).



8.1.7.X.X TO 9.2.0.1.0
1. Install the 9.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/oracle9i/index.html
For installation requirements, refer to the following note :
||Note 169706.1|| : Oracle® Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.1)

2. Upgrade the database to 9.2.0.1.0.
Refer to the following note for different upgrade methods :
||Note 419550.1|| : Different Upgrade Methods For Upgrading Your Database
||Note 159657.1|| : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.1 ||Note 353912.1||


8.1.7.X.X TO 9.2.0.2.0
1. Install the 9.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/oracle9i/index.html
For installation requirements refer to the following note :
||Note 169706.1|| : Oracle® Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.1)

2. Install the 9.2.0.2.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : 2632931
||Patch 2632931||

3. Upgrade the database to 9.2.0.2.0.
Refer the following note for different upgrade methods :
||Note 419550.1|| : Different Upgrade Methods For Upgrading Your Database
||Note 159657.1|| : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.2 ||Note 246010.1||
List of fixes added in 9.2.0.2 ||Note 246017.1||



8.1.7.X.X TO 9.2.0.3.0
1. Install 9.2.0.1.0 software
The software can be downloaded from the following link :
http://www.oracle.com/technology/software/products/oracle9i/index.html
For installation requirements refer to the following note :
||Note 169706.1|| : Oracle® Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.1)

2. Install the 9.2.0.3.0 patchset on top of the existing 9.2.0.1.0 ORACLE_HOME
Patchset number is : 2761332
||Patch 2761332||

3. Upgrade the database to 9.2.0.3.0.
Refer to the following note for different upgrade methods :
||Note 419550.1|| : Different Upgrade Methods For Upgrading Your Database
||Note 159657.1|| : Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)

REFERENCE:
Known issues and alerts affecting 9.2.0.3||Note 246001.1||
List of fixes added in 9.2.0.3 ||Note 245939.1||

How to Find out available (current & old) log file names of concurrent Manager.

How to Find out available (current & old) log file names of concurrent Manager.

Script provided by oracle

afcmcreq.sql
Displays the concurrent manager and the name of its log file that processed a request.



Tables:

fnd_concurrent_processes (LOGFILE_NAME --> logfile name)
fnd_concurrent_queues (CONCURRENT_QUEUE_NAME --> short name of manager)
fnd_concurrent_queues_tl (USER_CONCURRENT_QUEUE_NAME --> Name of manager, as seen on front end screen)

Foreign Key: CONCURRENT_QUEUE_ID.

Query
-----

set linesize 350
set pagesize 100
col Name for a30
col "Short Name" for a10
col Host for a13
col "Log File" for a40

select b.USER_CONCURRENT_QUEUE_NAME "Name", b.CONCURRENT_QUEUE_NAME "Short Name", a.PROCESS_START_DATE "Start Date", a.NODE_NAME "Host", a.LOGFILE_NAME "Log File" from fnd_concurrent_processes a, fnd_concurrent_queues_tl b where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and b.CONCURRENT_QUEUE_NAME like '%&Short_Name%';

Note: Change b.CONCURRENT_QUEUE_NAME to b.USER_CONCURRENT_QUEUE_NAME if you want to query through concurrent manager name. CONCURRENT_QUEUE_NAME is manager short name

Regards
Manoj

Rotate oracle Database listener log file.

Problem
-------

On 64 bit box, listener log file stops logging when size reaches 4.1 gb.

There could be some internal limitation from oracle side because of which it is not able to update log file.

Solution
--------
We can rotate log file, below are the steps.


1. take a back up of listener log file, check available disk space before going this. If you want to preserve time stamp use cp -p

cp -p log file name log file name_bkp

2. truncate listener log file

cat "" > log file name

3.
lsnrctl
set current_listener
set log_status off
set log_status on


4. check if listener log file is getting updated.


Additional command that could help.

fuser : will give pid of the listener, check is listener is running with same pid.


You can write small script to rotate listener log file and schedule cronjob to be executed every one month or so. Instead of using "log file_bkp" use "log_file_`date`"

Regards

sql to check archive's generated per hour for last 7 days

//sql to check archive's generated per hour for last 7 days

set linesize 140

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
where to_date(first_time) > sysdate -20
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time);

Check Versions of Oracle Applications

1. Application release version

select release_name from apps.fnd_product_groups;

2. Latest ATG RUP level

select creation_date, bug_id, decode(bug_number,
4334965, '11i.ATG_PF.H RUP3',
4676589, '11i.ATG_PF.H RUP4',
5473858, '11i.ATG_PF.H RUP5',
5903765, '11i.ATG_PF.H RUP6',
6241631, '11i.ATG_PF.H.RUP.7')
from apps.ad_bugs where bug_number in('4334965','4676589','5473858','5903765','6241631');

3.jdbc driver version

select bug_number, decode(bug_number,
'3043762','JDBC drivers 8.1.7.3',
'2969248','JDBC drivers 9.2.0.2',
'3080729','JDBC drivers 9.2.0.4 (OCT-2003)',
'3423613','JDBC drivers 9.2.0.4 (MAR-2004)',
'3585217','JDBC drivers 9.2.0.4 (MAY-2004)',
'3882116','JDBC drivers 9.2.0.5 (OCT-2004)',
'3966003','JDBC drivers 9.2.0.5 (OCT-2004)',
'3981178','JDBC drivers 9.2.0.5 (NOV-2004)',
'4090504','JDBC drivers 9.2.0.5 (JAN-2005)',
'4201222','JDBC drivers 9.2.0.6 (MAY-2005)') Patch_description
from ad_bugs
where bug_number in
(
'3043762',
'2969248',
'3080729',
'3423613',
'3585217',
'3882116',
'3966003',
'3981178',
'4090504',
'4201222'
)
order by 2;

How to generate the excel format in sqlplus

How to generate the excel format:

===================================
1. How to generate the excel format:
===================================
sql>set linesize 32000
sql>set pagesize 32000
sql>set head on
sql>set echo off
sql>set colsep','
sql>spool /tmp/output222.csv
sql>query
sql>spool off
: wc

Check if OS is 32/64 bit..

Check 32/64 bit
AIX: getconf -a | grep KERN OR file
Sun: isainfo -v
Linux: uname -a

'file' command works on all unix platforms

Oracle workflow notification mailer sql's

Oracle workflow notification mailer sql's
=========================================

1. Workflow: version
2. check workflow status.
3. check if workflow is used by only one instance
4. check if processor_read_timeout_close is set to 'Y'
5. check for bad e-mail address
6. How to know mail sent to a user with details:
7. How to know whether it is set to correct url from porfile options:
8. How to know reqid, process id, sid..
9. workflow patches
10. Workflow: To see failed, open notifications
11. To check if email address, notification preference, display_name
12. How to know workflow responsibility from backend:
13. Steps to drop and recreate WF_CONTROL queue:


=================
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql

----------------------------------------------------
2. check workflow status.
----------------------------------------------------
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

Huge Pages

Linux Huge Pages
================

Classification of Technologies used in Oracle E-business suite

Classification of Technologies used in Oracle E-business suite

• Oracle Application Object Library (AOL)
• Java Technology Foundation (JTF)

AOL applications are Oracle Forms developed using Oracle Developer and are usually
referred to as Forms-based applications. JTF applications are Java Server Pages (JSPs)
developed using Oracle JDeveloper and are usually referred to as HTML-based
applications. Each type of application accesses the same database and can share
information with the other.

The product interfaces are accessed by providing the Uniform Resource Locator (URL)
for the environment in an Oracle Applications 12-compliant Web browser and
navigating to the hyperlink for the login page for the specific technology stack. You can
also provide the URL for the specific login page. This URL is referred to as your login
URL.

Oracle Applications URL
Use this URL to navigate to the Personal Home Page URL or the CRM Home page URL.
http://:/
* To navigate to the Personal Home Page URL, choose Apps Logon Links >Personal
Home Page.
* To navigate to the CRM Home Page URL, choose Apps Logon Links >CRM Home
Page.

CRM Home Page URL
This URL is sometimes referred to as the Apache or JTF login URL. Use this URL to
open the login page for HTML-based applications.
http://:/OA_HTML/jtflogin.jsp

Personal Home Page URL
This URL is sometimes referred to as the Self-Service Web Applications or SSWA login
URL. Use this URL to open the login window for Oracle Applications via the Personal
Home Page. You can access Forms-based or HTML-based applications from the
Personal Home Page.
http://:/OA_HTML/US/ICXINDEX.htm

Forms URL
Use this URL to open the login page for Forms-based applications. This login URL is
Dependencies and Integration Points 2-11
typically used by system administrators, not end users.
http://:/dev60cgi/f60cgi

User Accounts
An application user is an authorized user of Oracle Applications and is uniquely
identified by a username. After the user account has been defined, the application user
can sign on to Oracle Applications at the CRM Home Page, Personal Home Page, or
Forms login.
Note: Oracle Applications is installed with a system defined username
and password.
* Username: sysadmin
* Password: sysadmin

An application user enters a username along with a password to sign on to Oracle
Applications. The password assigned by the system administrator is temporary. When
signing on for the first time, the application user will be prompted to change the
password. Access to specific functionality and data will be determined by the
responsibilities assigned to your user account.

Regards
Manoj

Broken Pipe

Broken Pipe
-----------

Create two named pipes, p1 and p2. Run the commands:

echo -n x | cat - p1 > p2 &
cat p1

On screen, it will not appear that anything is happening, but if you run top (a command similar to ps for showing process status), you'll see that both cat programs are running like crazy copying the letter x back and forth in an endless loop.

After you press ctrl-C to get out of the loop, you may receive the message “broken pipe”. This error occurs when the process reading the pipe closes its end while a process is writing to a pipe. Since the reader is gone, the data has no place to go. Normally, the writer will finish writing its data and close the pipe. At this point, the reader sees the EOF (end of file) and executes the request.

Whether or not the “broken pipe” message is issued depends on events at the exact instant the ctrl-C is pressed. If the second cat has just read the x, pressing ctrl-C stops the second cat, pipe1 is closed and the first cat stops quietly, i.e., without a message. On the other hand, if the second cat is waiting for the first to write the x, ctrl-C causes pipe2 to close before the first cat can write to it, and the error message is issued. This sort of random behavior is known as a “race condition”.

Source (online linux journal)

Regards
Manoj

Apps Account Locking

Topic
------
"Apps" Account Locking in E-business suite.

ADI(Application Desktop Integrator) is a software runs on Windows systems and uses the net8 client to talk to the database. However, user's logon as their application ID and password, not directly to the database.

In order for this to work, the application goes to the database with a public username/password that must never be changed for the application to function. The username/password is APPLYSYSPUB and the password is PUB (this is openly documented). This database account is able to find the APPS schema and encrypted password in the database. It then unencrypts the password and uses it to connect to the database.


Problem
---------
There is a know issue with customers using ADI(Application Desktop Integrator).

When ever there are unsuccessful logins (failed login attempts count) number of attempts by an application user using desktop ADI, apps account will get locked (timed). This can be confirmed by checking by checking database listener log.

Solution
--------
1. Increase failed login attempts.

2. Recommend customers to use web ADI instead of Desktop ADI.

3. Educate ADI users not to make unsuccessful attempts to login more than than times that you have specified in "failed login attempts".

Metalink users can educate thyself through note:400743.1

Security Issues
---------------

The version of ADI (Application Desktop Integrator) 7.1.1.10.1, contains a major security breach.

Whenever the software is launched, it creates a file called dbg.txt on the
local hard drive on the system which contains in PLAIN TEXT the usernames
and passwords for both the application user and the APPS schema!

further readings
----------------
http://www.oracle.com/technology/deploy/security/pdf/secalert_adi.pdf

Regards
Manoj