Important Tables and Profiles

The most important activities of an Apps DBA are applcation of Patches ( roughly its 50% of the daily work , what an Apps DBA performs ).The other main activity he is envolved in is troubleshooting Concurrent Manager or CM issues.
I have listed some important Tables which as an Apps DBA , need to look for any relevant info. regarding these Task.

Concurrent Manager
--------------------
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_REQUESTS_TL
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE


FND
------------
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_NODES
FND_DM_NODES
FND_TNS_ALIASES
FND_RESPONSIBILITY
FND_RESPONSIBILITY_TL
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES
FND_SVC_COMPONENTS
FND_SVC_COMPONENTS_PARAM_VALS



AD / Patches
---------------------
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS


Profile Options Useful for DBA
Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, all of this depends on your implementation..


Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

Flow of Concurrent Request
1. Once a concurrent request is submitted by the user, the table FND_CONCURRENT_Requests is automatically updated by the details of the request.

2. Once the request is scheduled to run, the CM’s checks the FND_CONCURRENT_TABLES to find the request compatibility with any other request.

3. If no incompatibilities found in the check, than request is assigned to the concerned Manager.
Note: If there is any special Manager to take care of the request , then it goes to queue of its manager else the standard Manager takes care of the request.

4. Once the Request is processed, the FND_CONCURRENT_REQUEST table is updated with the status.

5. A conc.request can have many PHASES , when its being processed by the Manager

6. Every Phase has corresponding STATUS.

PHASE 1.Completed

STATUS

Normal : The request completed normally.

Error :The request failed.Check the log file for details about the error.

Warning :Request completed with a warning.Warning is generated when upon completion task don't complete succesfully.

Cancelled :The request was cancelled before it was started.Terminated : When a running request was terminated.

PHASE 2.Running

STATUS

Normal: Request is running normal and no interfere is required.

Paused : Request is waiting for some other request to be finished.(Dependency )

Resuming :Paused request is resuming, this happens when dependent request completes.

Terminating :User or sysadmin chooses to terminate the request.

3.Pending

Normal : Request is waiting for CM.

Standby :Request is incompatible with some other request.CRM needs to resolve this.

Waiting :Dependency of request.

Scheduled :Request is scheduled to run at a point of time in future.

4.Inactive

No Manager : CM is down or No specialzed Manager is defined for this request

Disabled : Conc.Program rquested has not been enabled yet.

On Hold: Pending request has been kept on hold for time being.

X server

Configure an X server for Framework applications

To take advantage of the dynamic image generation support Framework applications require access to graphical capabilities on the middle tier. In practical terms this means you need to configure your Webserver to use an X server for dynamic gif generation.

X display servers can be broadly categorized into three groups

1. X display server from the OS Vendor
2. "Headless" X display server without video hardware (Xvfb and VNC )
3. Desktop X display server


Oracle software requires specific X display server settings. Oracle Toolkit, an internal component of Tools 6i, and the Installer orainst , require the following critical parameters to be set for all X display server software:

Option 2: Headless X display server - Xvfb or VNC

"Headless" X display servers provide the same services as the software in Options 1 & 3, however, these servers do not require video adapter hardware nor a monitor. These servers require the same settings for the default visual class, dpi and depth parameters as described in the introduction.

How to install and run Xvfb (X Virtual FrameBuffer)
Get the Xvfb binary. Some URLs are given below; alternatively, Xvfb can also compiled from the Xvfb source code available at http://www.x.org. As of April 2002, Xvfb binaries can be downloaded from these locations:
Solaris - Include with Solaris 9 and later. See Oracle TechNet for Solaris 2.6, 7 and 8
Linux - Included with most Linux distributions. Xvfb is also available as an RPM for RedHat and SUSE or from Xfree86.org
HPUX - HP patch PHSS_25277
AIX - Included with the operating system.
Tru64 - Included with the operating system; usually in /usr/bin/X11
Win32 - Not applicable.

On Solaris, verify that the all dynamically linked libraries can be resolved using the ldd command. On other platforms Xvfb is included with the OS and should already be properly configured:
% ldd Xvfb
This should display a list of resolved libraries as follows:
libsocket.so.1 => /usr/lib/libsocket.so.1
libnsl.so.1 => /usr/lib/libnsl.so.1
libc.so.1 => /usr/lib/libc.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Ultra-60/lib/libc_psr.so.1

Get a security policy file
A sample security policy file is included at the end of this document. The file is named SecurityPolicy.
If you have an existing file then add this line to the end of the policy file:
property WM_CHANGE_STATE WM_NAME ar

To run Xvfb use a command with the following format

Xvfb :x -screen 0 6x6x8 -pn -fp /lib/X11/fonts/misc -sp /tmp/SecurityPolicy
where
x is the desired display number. For instance 1 or 2.
fp points to the directory of the fixed font
sp points to the directory and filename of the security policy file
6x6x8 screen size works well for Reports and Graphics and minimizes the memory required by Xvfb

Set the DISPLAY environment variable to the value :x.0 or your_hostname:x.0 where x is the display number chosen when starting Xvfb. For example in Bourne shell:

DISPLAY=Xvfb_hostname:x.0; export DISPLAY

Start a window manager on the Xvfb display to automatically position windows.
twm &

Opatch (utility to apply RDBMS patches)

Opatch Application Steps :

1) Read the patch readme thoroughly and make sure everything is fine. Remember database patches are applied using opatch.

2) Check the invalids and take the object name and count of invalids.
col owner format a15
col object_name format a40
col object_type format a15
select owner,object_type,object_name from dba_objects where status = 'INVALID' order by owner,object_type;

3) Make sure you have the right opatch utility version.
Recent db patches require opatch version is 1.0.0.0.55 or higher

opatch version

Also make sure opatch executable used to apply the patch is in the PATH

export PATH=$PATH:$ORACLE_HOME/OPatch

4) Make sure opatch lsinventory is working for the opatch. If lsinventory is not working then you should not go ahead with opatch. This is the most important step, as most of Opatch issues are related to bad inventory etc.

opatch lsinventory

The possible causes could be inventory is not pointing correctly.(especially in dev and test servers, where you have more than one db sharing the same inventory) then make make sure inventory is pointing to the right path. Inventory location is mentioned in oraInst.loc under db home.
eg /opt/oracle/product/appl/9.2.0/oraInst.loc

ex Oracle Installer Location File

Location#inventory_loc=/opt/oracle/product/9iAS/oraInventory#inventory_loc=/opt/oracle/product/appl/oraInventoryinventory_loc=/opt/oracle/product/appl/oraInventoryinst_group=dba

Note :The OraInventory dir would be in one dir below db home

dir /opt/oracle/product/appl/oraInventory (]$ ls -rlttotal 10drwxr-xr-x 2 oramgr dba 96 Nov 16 2005 ContentsXMLdrwxr-xr-x 2 oramgr dba 1024 Nov 16 2005 Contentsdrwxr-xr-x 2 oramgr dba 3072 Dec 09 20:10 logs-rwxr-xr-x 1 oramgr dba 38 Dec 09 20:10 install.platform/opt/oracle/product/appl/oraInventory (]$<> and make sure the entries in files under ContentsXML has correct db entry.Also note these day's inventory are also known as XML inventory. )

RMAN -- Disk channel and tape channel

Disk channel and tape channel – how to associate sbt_tape with rmt0h

If allocate channel for Tape , then synatx will be :

allocate channel t1 device type 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=ORACLE_napdci28-bac)";or allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_POLICY=ORACLE_napdci28-bac)";

If allocate channel for device, then syntax will be :

allocate channel c1 device type disk format '/uo1/orabackup/robprod/robprod_%U';

If allocate channel for maintenace then syntax will be :

allocate channel for maintenace device type sbt;

NOTE: The number of channels that are automatically allocated depends on the default level of parallelism defined.
Configure command:Configuring Channel Default settings :

This configures the default backup/restore device to tape or disk

configure default device type to SBT;
configure default device type to DISK;

Once we have configured a default device type,ORACLE will use the default channel unless we override the default using the backup device type parameter.

Login issue resolution

If you can't login to oracle application by clicking on login link ,

check if

--- dbc file location
(wrapper.bin.parameters=- DFND_SECURE=/opt/appldba3/apps/fnd/11.5.0/secure)
as per parameter FND_SECURE (in jserv.properties) is correct.

--- dbc file name in the location specified by DFND_SECURE parameter in Jserv.properties file.

--- APPL_SERVER_ID in dbc file matches with server ID in FND_NODES table against respective Server .
eg.APPL_SERVER_ID=2CD2C25DFDCA80EAE0439C14342A80EA17875142042836050097327698082686

--- GUEST/ORACLE ( this a Application User and NOT DB user ) password is correct & user is not end dated .

--- APPS password is correct in wdbsvr.app file
( under $IAS_ORACLE_HOME/Apache/modplsql/cfg).

--- Check http://hostname.domainnamel:port/pls/$SID and see if package fnd_web can ping & you get output in screen from FND_WEB.ping with correct database_id ( and check if there is correct dbc file in FND_SECURE )

There is JSP tool test to find most of issues via http://host.domain:port/OA_HTML/jsp/fnd/aoljtest.jsp

Apart from this note at metalink which you can refer 342332.1 Troubleshooting Login Problems in Oracle Applications 11i (11.5.2 , 11.5.10+)

Hope this will help in our future issues.


Now let us understand , how an apps login actually takes place.
---------------------------------------------------------------
When user makes a request how request flow across different component in E-Business Suite or Oracle applications .There different Services/Component in Oracle apps these are ,
1. Oracle Web Server ( Apache, Jserv, plssql )
2. IAS_ORACLE_HOME 9iAS, Ver. 102222.
3. Forms Server ( 8.0.6 ) ORACLE_HOME & Report Server ( in side 8.0.6 ORACLE_HOME)
4. Concurrent Manager for Batch or Individual Job Processing
5. Admin Server ( These are used for application dba job like patching , cloning & lot more )
6. Database Server

Let's see how a request flow across different component in E-Business Suite or Oracle applications:

A) So when User makes a request from browser by typing URL
like http:// hostname : port
Here the port number they mention is WebServer Port, this request hit's to Oracle WebServer ( these requests are recorded in logs where wait for next post ).


B) Webserver checks that with http request there is no cookie attached (Cookie is message given by WebServer to Web Browser to identify client) so user is new & it passes login page to User .

C) User types his Username Password in login window & clicks submit button.

D) Webserver checks that username and passwords ( it need's to authenticate against database FND_USER table ), so it needs first some user GUEST/ORACLE to check client's username password in database .

E) Once user is authenticated its check against FND_RESPONSBILITY for Authorization about users responsibilities & assigned responsibilities are presented back to user.

F) Now these responsibilities are of two type Self Services(served by Jserv/Servlets) or Core Applications Server by Forms Server .

G) If this is Core responsibility , Apache transfers request to Forms Listener (Listening on Forms port),
From here request is on Sockets ( on forms servelets depending on forms config. ) which is persistense connection. From here onwards Client talks directly with Forms Server in forms session and if Client requested some data, forms server makes database connection.
( using tnsnames.ora in 806 HOME or using dbc Database Connector file in FND_SECURE_TOP or wdbsvr.apps in Apache/modplsql/cfg )

If User selected Self Service Responsibility like iProcurement, iRecruitement.., Apache forwards request to mod_jserv and request is fullfilled by jserv_component.

Note: why webserver donot ask client to login again until session timeout ??
( its because of cookies as User have valid cookies , try deleting cookies from browser & see Oracle Apache asks you to relogin .)

When your webserver tries to connect to database for plsql or any other type of connection it asks mod_pls to fulfill request but this connection information stored in ( dbc file or wdbsvr.app file )

DBC file in Oracle Apps

What is a .dbc and a .cfg file?

The .dbc file is mostly used to define database parameters,stands for database connect descriptor file, used to connectto database, it authenticate users against database in FND_USER table.

Let's see how it work's

The .dbc file is actually the database connector descriptor file used to connect to database and this file is by-default located in $FND_TOP/secure directory, this file is quite important as whenever any program likes to connect to database like forms it uses dbc file where there you find the Guest_user_pwd ,when the guest user connect it does not allow it to directly connect to the database but it first connect goes via this file and cross verify the password of the guest which is kept in this file.

DBC file is quite important as whenever Java or any other program like forms want to connect to database it uses dbc file.

Typical entry in dbc file are:
GUEST_USER_PWDAPPS_JDBC_URLDB_HOST


The dbc file is used to set up the connectivity for a given database. Each vendor requires different values to determine the server name, database name, user and password, plus any native options including special instructions for the vendor's client API or utility loader.When you drag an Output Table/Input Table etc. onto the canvas, the DBC file lets the component know where to direct the activity. The DBC file is also the mechanism through which a given table can be accessed and its column definition pulled automatically into the given component's DML definition.

There will be lot of dbc file under $FND_SECURE, how we determine which dbc file to use from $FND_SECURE ?

This value is determined from profile option "Applications Database ID"
Authenication:-

In order to authenticate your user against database in FND_USER table this request is passed from core Apache Module to mod_pls ( plsql) and it uses few files like wdbsvr.app in Apache/modplsql/conf $CONTEXT_NAME.dbc , tnsnames.ora in 806/network/admin/$CONTEXT_NAME , GUEST user & few other file so focus on these files & directories & see what changed recently.

.dbc file in R12 -->the dbc file in R12 is in the new $INSTANCE_TOP

$INST_TOP/appl/fnd/12.0.0/secure or $INSTANCE_TOP/apps//appl/fnd/12.0.o/secure.

Last but not the least .cfg is a unix-level file that is fed into a Korn Shell script as an auto-parameter feeder for a parameter-driven shell script. Cfg files are unix-defined where dbc files are AbI-defined.

A Database Connection (DBC) file holds information used by application servers to identify and authenticate with an application database. DBC files must be created on all tiers if it is a multi-tier system.

To recreate DBC file Login to your system as the APPLMGR, ensure that your environment is set correctly by running /.env.
Run common_top/admin/install/adgendbc.sh (UNIX)
This will create the DBC file for the current environment instance. Verify log file to check environment, which should be pointing to 806.Location of file is in /admin/install/adgendbc.txt.

DBC file Parameters

Verify dbc file exists and is in the right location. $FND_TOP/secure//_.dbc. Verify db_host using %uname -aVerify db_sid from v$instance or echo $ORACLE_SID.

Parameters:
APPL_SERVER_ID APPS_JDBC_DRIVER_TYPE=THIN DB_PORT <> DB_HOST GWYUID FNDNAM <> TWO_TASK GUEST_USER_PWD. this is application account and should exists in applications with out any responsibility.
Note:Make sure environment value specified for DB_HOST is db host not application host.
Optional parameters:

FND_MAX_JDBC_CONNECTIONS
Maximum number of open connections in the JDBC connection cache. This number is dependent on the amount of memory available, the number of processes specified in the init.ora file of the database.

FND_IN_USE_CONNECTION_TIMEOUT
Maximum number of seconds a connection can be in use. In order to avoid connections being locked up for too long, the connection cache uses this parameter to forcibly close connections that have been locked for over this time. If this parameter is unspecified,connections in use will not be cleaned up. This should be set to a number larger than the time taken to complete the largest transaction.It is difficult to estimate and recommendation is not to use this.

FND_UNUSED_CONNECTION_TIMEOUT
Maximum number of seconds an unused connection can remain in the cache. The connection cache will close and remove from the cache any connection that has been idle for longer than this specified limit.
Checking DBC file entries: Check Encrypted String for APPL_SERVER_ID parameter in the DBC file with entry in db using following sql stmt.These should be same.
Log in as apps/
Select server_id,server_address from FND_APPLICATION_SERVERS;

The output server_id should match APPL_SERVER_ID and server_address should match IPADDRESS of db host.

%nslookup Address: xxx.xx.xxx.xx Name: Address:
verify hostname above with db_host parameter in dbc file.

Checking db connectivity using DBC file :
------------------------------------------


syntax:

jre -v oracle.apps.fnd.security.AdminAppServer apps/ STATUS dbc=

Output :

Database Server
---------------
DATABASE_ID: *********
AUTHENTICATION: OFF

Application Server
------------------
APPL_SERVER_STATUS: VALID
APPL_SERVER_ID: 2CF3A427AB3AE01EE0439C143428E01E24739190873060609592242102215611


Status return's APPL_SERVER_ID and other db related info. This verifies DBC setup.