A) ** APPS INTERVIEW QUESTIONS PART 1 **

Oracle Apps dba interview questions

Q What is forms server? 

Explain briefly, how the connection of the forms server works?

The forms server is that server which the forms are hosted. It’s a component  of middle tier. The forms server can be hosted from more than one node and the load balancing can be implemented with the forms. The forms user  interface is used in the desktop clients for working in Oracle Applications.

This is how the forms server works.

• Browser sends request (URL) to HTTP Listener (Apache)

• HTML page is retrieved (static) or generated (dynamic)

When the Websever receives the URL, it interprets it. If the URL points to a static file, the file will be retrieved from storage. If the URL points to a CGI script, the file will essentially be the same as the static version, but some pieces of that file will essentially be the same as the static version, but some pieces of that file will be dynamically generated by the CGI script. If dynamic, CGI script asks load balancing server for least loaded server. If the URL points to a CGI script, the CGI script will poll the load balancing server.

The CGI script asks the load balancing server for the least loaded forms server. The load balancing server returns the answer, and that answer is used in the generation of the HTML file returned to the browser.

   HTTP Listner sends HTML page back to browser

   Browser decodes HTML page, and detects the <APPLET> tag, indicating a Java Applet.

As the browser decodes the HTML file returned by the Web server, it detects the <APPLET> tag. This is the designator that indicates a Java Applet. Specifically, this is the thin client that will connect to the forms server.

The <APPLET> tag contains the name Applet, along with numerous parameters including (a)the name of the form to run  (b)the name of the forms server to use,  (c)login information, and  (d)any other parameters you need to pass to your forms session. 

• Browser sends request(URL) to HTTP Listener for Java Applet

The browser asks the Web server to send it to the Java Applet. Java Applets are stored in .class or Java Archive (JAR) files. Oracle Applications use JAR files. JAR files are compressed archives that contain multiple .class files. Oracle Applications use JAR files because they speed up the downloading of the Java Applet. There are many JAR files that Applications must download to run. 

• HTTP Listener returns Applet (JAR files) to browser

Browser receives Java Applet (JAR files) and begins to run them in its JVM(JInitiator). The JVM JInitiator checks the version of the files being sent. If the version of the JAR files is newer than the version cached on the client, JInitiator will continue the download. If the version is the same or older, JInitiator will begin to run the cached Java files. Java Applet is now running in the JVM. Browser is no longer part of the equation. The Java thin client connects to the forms listener via a TCP/IP socket or an HTTP port. The forms listener is already started, and listens for these requests. 

• Forms Listener allocates a forms runtime engine

When the Forms Listener gets the request, it starts a new forms runtime engine for this thin client. This started forms runtime engine can either be a newly spawned process, or it can be an allocation of an already running process (which greatly spends up the connection process). 

• Java Applet connection is passed from Forms Listener to forms runtime engine.

The forms Listener hands-off the connection to thing client, and then has no further role in the process. Forms runtime engine loads module(s) needed to run the requested form. When the thin client connected, it passed a parameter entry, serverArgs. In that parameter entry, there was a name of a form to run. At this point, the forms runtime engine loads the form and any libraries and/or menus required by that form. 

• Forms runtime engine opens a connection to the database.

The details of this connection depend on whether the Forms Runtime Engine is a newly spawned process, or if it was allocated from a pool of already running processes.

Q. Reorg? How and Why and for which objects generally?

MOVE is easy. That moves an object from one tablespace (and set of data files) to another tablespace (and another set of data files). That may or may not decrease the size of the table. It would be used when you need to temporarily or permanently relocate an object, which is what would need to happen if you need to shrink a data file.

COALESCE is only related to index-organized tables or hash partitioned tables. It does the same thing for an index-organized table that coalescing an index does, it merges the contents of an index block.

SHRINK SPACE tells Oracle to physically move rows around in a table (this is why row movement must be enabled for the table) in order to release free space in the table back to the tablespace. You'd do this if, for example, you have a log table that had been storing 9 months of data and you've decided to permenently decrease that to 3 months of data, deleted the old 6 months worth of data, leaving the table permanently smaller (assuming no increase in the log generation rate) and 67% empty.

ALTER TABLE SHRINK is a two phase operation:

1st. step: reads table from the back and deletes first row found , then read table from the front and uses first free slot to reinsert it.

This is reiterated until the position for reading from back and reading from fron tis the same. Then the segtment is compact, right. This is DML and indexes are maintained.

2. step adjusts high water mark. This need Exclusive Table lock.

On the other hand the ALETR TABLE MOVE is similar to a CREATE TABLE AS SELECT which does not use the SGA but the PGA. Here arrays are filled in temporary segments and then dumped into newly formatted blocks.

> Indexes are not maintained, the rows change their physical address and the indexes must be rebuilt afterwards.

> Alter table move is not an online operation. No DMLs allowed in between.

> alter table move is to be used to reduce the no of empty blocks

In general ways of reorg are

=======================

• • Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.

  • Online reorg - Using the dbms_redefinition package you can use parallel CTAS to reorganize tables, while the tables continue to accept updates.

• Data pump (expdp, impdp) - Rarely used in high-speed production apps, except for backups.

  • Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.

  • Alter table shrink space - Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

Q. Does the Internal manager schedule requests to be run or does it put requests into queues to be run by other managers?

This is a very common misconception. The ICM really does not have any  such scheduling responsibilities. It has NOTHING to do with scheduling

requests, or deciding which manager will run a particular request.  Its function is only to run 'queue control' requests, which are  requests to startup or shutdown other managers. It is responsible for startup and shutdown of the whole concurrent processing facility, and   it also monitors the other managers periodically, and restarts them if  they should go down. It can also take over the Conflict Resolution manager's job, and resolve incompatibilities.

If the ICM itself should go down, requests will continue to run normally, except for 'queue control' requests. You can restart it with 'startmgr', you do not need to kill the other managers first.

Q. Where do concurrent request or manager logfiles and output files go?

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. 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  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. Concurrent manager logfiles follow the same convention, and will be

found in the $APPLLOG directory

Q  When a user submits a concurrent request having no printer or style selected in the concurrent request definition, how does the printer style gets selected ?

In the Concurrent > Program > Define form there is an entry for Style. This determines the default value.  If Style Required is checked, this value cannot be changed at submission.

Q  When no Style is defined for a program, where is the default derived from?

The Columns and Rows values defined for the program are used as a filter when selecting Styles. Styles also have a Sequence Number assigned when they are defined.

When a random Style is selected, such as with FSG reports or when the Platform of the associated Driver does not match what is defined, the FIRST Style that meets the report requirements IN SEQUENCE ORDER is used.

oracle allows users to set conflicting default print parameters.  There is a profile option 

(PRINTER) that sets a default printer, but there is not corresponding profile option that specifies a VALID default print style for the printer. instead, the concurrent requests refers to the concurrent request definition, which specifies a default or required print style. the concurrent request definition may not be VALID for the profile option, which forces the  user to access the printer options pop-up on the standard report submission form and select a valid style.  our enhancement request it to allow the setting of a profile for print style, that is required if the default printer is set, and is validated to be a defined style for the default printer.  this style should supercede what is set in the concurrent request definition, unless the print style is defined as "required."

Q Is A Postscript Printer Required For Printing EBS BI Publisher PDF Reports

"Few printers understand the raw PDF format.

E-Business Suite (EBS) does not contain the necessary code nor does it deliver any tools to convert PDF files to a format that a printer understands, such as Postscript or RTF.

...

The Adobe Acrobat Reader converts a PDF file to another format (RTF / PCL /Postscript) before sending the file to a printer. At the present time, EBS does not have the needed code to perform the same conversion process; however, the same conversion process can be achieved with third party software and custom drivers."

Commonly used Postscript tools are: acroread, pdftops, and psdf2ps. Both the PDF format and the Postscript format are proprietary to / owned by Adobe; therefore, conversion between the PDF and Postscript formats renders a quality output—other tools may not.

However, any tool can be used that can effectively convert the PDF file to another format; simply, setup the preprocessing section of the Pasta configuration file with the desired third party tool.

Q. I hit the Restart button to start the Standard manager, but it still did not start?

Telling a manager to restart just sets the status to Restart. The ICM  will start it the next process monitor session or the next time the  ICM starts. Use Activate to start a manager immediately.  When a manager is deactivated manually, the ICM will not restart it, you will need to set it to Restart, or activate it manually.

Q. How do I process more concurrent requests concurrently?

The Concurrent Manager parameters, (Query the concurrent manager by  Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for

the relevant concurrent manager), should be modified to handle more  concurrent requests concurrently, this can be done in two steps:

(i) Increase the Number of Target processes for the manager

(ii) Change the cache size of the concurrent manager as this determines how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.

Q Why we cannot change through alter user statement and change the apps passwd in those two files.

Because Oracle Application use APPS PASSWORD to encrypt end user's password in fnd_user and oracle user's password in fnd_oracle_userid,you can have a test when you use FNDCPASS to change

password of APPS,the column encrypted_oracle_password in two tables changed too.But alter don't do this actions.

Q Describe Purge programs and their significance:

1) Purge Concurrent Requests and/or Manager Data

We had a customer who, when we first started working with them, complained that whenever they submitted a request, they had to wait for about half a minute for the request to commit, and when they tried to query up the status of records in the concurrent manager, they often waited for more than a minute. Our investigation showed that they hadn’t run the “Purge Concurrent Requests and/or Manager Data” concurrent program in the three years that they had been running the applications. Performance improved dramatically once they started running this program.  The “Purge Concurrent Requests and/or Manager Data” concurrent program will delete information about completed concurrent requests from a set of tables. It can also delete the log and output files for those

concurrent requests from your UNIX file system. Most companies run this report and delete all records older than 7 -31 days. 

If you choose Entity ALL, the purge program  deletes rows for completed requests from the

FND_CONCURRENT_REQUESTS,

FND_RUN_REQUESTS,

FND_CONC_REQUEST_ARGUMENTS,

FND_CONC_STAT_LIST,

FND_CONCURRENT_PROCESSES,

FND_CONC_STAT_SUMMARY,

FND_CONC_PP_ACTIONS,

FND_RUN_REQ_PP_ACTIONS

and FND_DUAL tables.

It also deletes your older log and out files. Using this program rather than deleting log and out files using UNIX commands works better, because the Purge will check against the FND tables and only delete files that have a status of completed.In the example, we’ve chosen ALL for Entity. Valid choices are ALL, MANAGER and REQUEST. If you choose MANAGER, then the log information stored in APPLLOG will be deleted, but the output files stored in APPLOUT will not. If you choose REQUEST, then the output files will be deleted but the log files will not be deleted, and the associated rows in the FND_ENV_CONTEXT table will not be deleted. Rows are written to FND_ENV_CONTEXT when a new concurrent manager process or service is started so this table can get very large if you don’t select ALL for Entity. For Mode, we chose Age so we could delete files older than the number of days specified in Mode Value.

You could also choose Count for Mode – you might use a Mode of Count after cloning an instance to delete all the old completed concurrent request data that carried over from the source system. In that case, you would choose Mode of Count and Mode Value of 0 to delete all completed records.There are two schools of thought on the best way to clean out the concurrent manager tables after cloning an instance. Some folks recommend that you simply truncate the FND_CONCURRENT_REQUESTS table on the target instance after cloning and before starting the concurrent manager. We prefer to run the “Purge Concurrent Requests” concurrent program rather than truncate the tables because truncating the table will remove all of the scheduled programs along with all the completed programs – we put all scheduled requests on hold before cloning to save the scheduled requests and then allow users to either

cancel the scheduled requests or take them off hold after cloning on the theory that this is easier for users than resubmitting all of their requests.

Applications System Administrator would schedule the “Purge Concurrent Request and/or Manager Data” program to run nightly, deleting all completed requests older than 7 days. Since you run nightly backups (you do, don’t you?), deleting data and files shouldn’t be a problem – you can always retrieve them from tape if necessary.  What if you delete too much data? It certainly can happen – you need to keep completed concurrent request information on your system long enough so users can see  the output! Don’t delete everything older than one day, then, or the user who schedules a request to run on Saturday won’t get to see the results on Monday when they come in to work. Generally folks keep 7-31 days worth of concurrent request output.  What if you want to hold onto historical information to assess performance? Let’s say your manager wants to know what performance is like during the month-end close, or say you know you need to tune the different concurrent managers and want to know how the concurrent managers are performing. Youhave two options – hold onto the data for a longer period of time, which risks performance issues for everyone who needs to submit or query on a submitted request, or save the  historical information elsewhere. You could, for example, create a delete trigger on the fnd_concurrent_requests table that puts data into a historical table whenever a delete occurs. Whenever the purge runs, then, the data would be deleted from fnd_concurrent_requests after landing in another table, say, fnd_concurrent_requests_history. You could then run programs that gather performance information against a view that joins fnd_concurrent_requests with fnd_concurrent_requests_history. 

2) Purge Obsolete Workflow Runtime Data

Similar to the FND tables that are purged with the “Purge Concurrent Request and/or Manager Data” concurrent program, the workflow  tables also need to be purged depending on the complexity of the workflows and how often they are used. Oracle provides the WF_PURGE package to assist with this effort, and a concurrent program  called “Purge Obsolete Workflow Runtime Data”. MetaLink Note: 132254.1: “Speeding up and Purging Workflow v2.5 & 2.6”, gives more details on how to improve workflow performance.

Workflow records data about each step of a running workflow in the

WF_ITEMS,

WF_ITEM_ACTIVITY_STATUSES,

WF_ITEM_ACTIVITY_STATUSES_H,

WF_ACTIVITY_ATTR_VALUES,

WF_NOTIFICATIONS, and

WF_NOTIFICATION_ATTRIBUTES

tables. The size of these tables grows very rapidly and if not managed can cause performance degradation. The “Purge Obsolete Workflow Runtime Data” concurrent program is very similar to purging concurrent manager history with the “Purge Concurrent Requests and/or Manager Data” concurrent program. When you first set up workflow, you can choose scheduling timings and history retention period to be the same as your concurrent programs. Even if you are running

the “Purge Obsolete Workflow Runtime Data” concurrent program, you should continue to monitor the size of your tables and whether  you are experiencing performance  issues and adjust your parameters accordingly.

The parameters for the “Purge Obsolete Workflow Runtime Data” concurrent program are:

a)  Item Type - You can specify Item Type when you  are trying to get rid of  a specific workflow (for example, the FA accounts generator  is seeded to be permanent and if you are de-bugging it, then you would run the purge specifying the item-type, age=0, and persistence type = Permanent to get rid of all of

the debug history). 

b) Item Key - We haven't really found a reason to use the Item Key - that gets rid of a specific instance of a specific workflow. Since this program only works on workflows that are completed, there isn’t much call for removing data for a specific instance. For the "generic" purge, Item Type and Item Key should be left

blank. 

c) Age - The Age field is for how long AFTER the workflow finishes you want to keep the data before purging it. That depends on what a company does with the workflow data - are they gathering stats on number of workflows finished? Are they moving the approval history to other tables where Oracle didn't keep an approval history? We believe the history ought to be purged often, at least for data older than 30 days. Many companies purge data older than a week. If your company is doing statistics gathering and moving approval history, it will do so by using custom programs, so these programs could be run prior to the purge by including the custom programs and the purge in a request set. 

d) Persistence Type - Persistence type is assigned to a workflow when you define it (look at the properties page of the Item Type in the builder tool). The choices are temporary or permanent. Most of the Oracle seeded workflows have a persistence type of temporary. 

Just as you don't keep concurrent manager data forever, you don't want to keep workflow data forever either. It makes administration burdensome to have to sort through all that history. The System Administrator should set up the “Purge Obsolete Workflow Runtime Data”  concurrent program for items with a Persistence Type of Temporary, and should schedule this program to run daily, choosing an appropriate Age for data retention, and leaving Item Type and Item Key blank. 

If you’ve been running the Applications for some time and did not know you were supposed to run the “Purge Obsolete Workflow Runtime Data” concurrent program, your Database Administrator may need to resize the affected tables once you’ve gotten a  significant amount of obsolete data removed. Your

Database Administrator may conclude that exporting and importing the Workflow tables is necessary to release the empty space that the Purge frees up. This  is not a difficult task, but does require that the database be made unavailable to users temporarily while your DBA runs the export/import processes. Each company must decide how long they wish to save the history of completed workflows. While for some workflows (such as Journal Batch Approval) the workflow runtime tables provide the only history of the approvals, these workflow tables are not the appropriate place for approval history storage. If you wish to save the approval history, the approval records can be extracted to custom tables prior to purging the workflow history. If you only need to keep approval history for a short time, then just adjust the age parameter. In order to extract this history, you will need to know the internal name of the activity that asks for approval. 

3) Purge Signon Login data

The “Purge Signon Login data” concurrent program deletes audit information from the

FND_LOGINS

FND_UNSUCCESSFUL_LOGINS

FND_LOGIN_RESPONSIBILITIES

FND_LOGIN_RESP_FORMS

tables. You should schedule this program to run daily. You should pick a date for the Audit Date based on your company’s security data retention requirements. Most companies pick a date a week or two prior to the current date.

Check the Increment Date Parameters box so that  the Audit date will advance a day each time the program runs.

What happens if you don’t run the purge? We had one customer who ran the applications for several years without running the “Purge Signon Audit data” program. After migrating from RDBMS Version 8.0.6 to Version 8.1.7.4, they found that the System Administrator’s Security | User | Monitor screen hung trying to pull up records. Testing showed the problem was caused because the FND_LOGINS table had more than 1.5 million rows in it. Once we ran the “Purge Signon Audit data” program, performance returned to normal.

4) Delete data from temporary tables

This program deletes data from the

icx_sessions,

icx_transactions,

icx_text, 

icx_context_results_temp,

icx_failures

icx_requisitioner_info tables.

The program hard codes the deletes to remove all data older than the current time minus 4 hours, but the program code says you can change how much time you wait to delete by modifying the code. Of course, that would be a customization, subject to being overwritten in the future, so unless you’re very uncomfortable with the 4 hour number, we recommend leaving the code, which is located in $ICX_TOP/sql/ICXDLTMP.sql, alone. These tables will get very large over time – the icx_sessions table has an entry for every time someone logs into the self services web applications. You should schedule this program to run daily. 

Delete temporary data of PO revisions in Self Service Applications

The “Delete temporary data of PO revisions in Self Service Applications” concurrent program deletes records from the temporary table ICX_PO_REVISIONS_TEMP, where all the records for differences are stored. When you run this concurrent request, it asks for a date and purges all records that are older than this date. We recommend scheduling this report to run nightly, with the date set back one or two weeks. When you schedule this report, set it up to run daily and click on the “Increment date parameters each run” so the date will advance each time the concurrent program runs. You should work with your Purchasing Functional Users to ensure that they concur on how much data to save. While both the “Delete data from temporary tables” and “Delete temporary data of PO revisions in Self Service Applications” concurrent programs can be run from the Self Service Web Applications Manager responsibility, it might be easier to run all administrative reports such as these from the System Administrator responsibility. To add these reports to the System Administrator’s Report Group, choose Security | Responsibility | Request and query the Group “System Administrator Reports”.

Conclusion

If you haven’t been running these five concurrent programs in your environment, there’s a good chance you’ll look like a hero once you discover them.  You should schedule these concurrent programs, understand what happens if they don’t run, and document their significance so that they don’t slip through the cracks if you move on to another position. Also, if the affected tables have grown very large before you began purging from them, consider exporting/importing those tables to bring their high water mark back down.

Q What happens if you don’t give cache size while defining concurrent manager? 

Most often when …a request goes "inactive/no manager" and is then processed a short time  later, the solution is to either increase the cache size for your Standard manger, or increase  the actual number of Standard Manager processes that can run. Cache Size is set in the Concurrent/Manager/Define form. Basically, this regulates how many requests a manager  will pick up for each sleep cycle.

Increasing the cache size within reason allows you to decrease the sleep cycle. In turn, your managers will wake up and scroll through the tables less frequently, which reduces the  amount of work they have to do as well as the amount of system resources utilized. We see sleep cycles set to 5 seconds at some customer sites and recommend increasing the value, since the managers are waking up and re-reading the same table far too frequently. For reports that for the most part take a minimum of 1 minute to run, the queue wakes up 12  times to check for runnable processes before even one running request completes.

Q Why should Apps & Applsys passwords always be the same? 

The need to have the same password for Apps and Applsys is because when you sign on to apps, intially it connects to a public schema called APPLSYSPUB. This validates AOL username and password that we enter (operations/welcome using guest user account.  Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to APPS schema. 

Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login. 

Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc) 

Applsys schema has applications technology layer products like FND and AD etc.

Why do we need to shutdown concurrent manager while changing Apps Password?

The concurrent managers should be shutdown so you don't have the problem of custom concurrent programs that connect to the database using an old password (passed in as a parameter) running that can potentially lock the apps account by submitting the wrong password more than the allowed number of times the database profile that APPS belongs to indicates.

Q: What scripts can be used to compile apps  schema , which one is used in adadmin compile apps schema ?

Adadmin in-turn calls the procedure UTL_RECOMP.RECOMP_PARALLEL which might be in-turn issues the following commands based on the object types

if object is package body 

alter package <package_name> compile ; 

alter package <packae_ame> compile body; 

alter view <view_name> compile;

 $AD_TOP/sql/adcompsc.pls

Q : Why DB-CM-ADMIN are always insatlled on the same machine in Oracle  Applications in Multi Node Installation ?

As such there is no restriction to install all of them on a single machine,but if we install them on 3 different machines then when we will run any AD utility on admin node or perform any upgradation it needs to access the database so there will be lot of overhead in accessing the database node on network so to avoid this overhead we install them on same machine. Similar is the case when we run any concurrent request on the CM node as Concurrent manager also updates the database objects.

Q. How will you find discoverer version in Apps ?

Version

$ORACLE_HOME/Discwb4

$ string

Also Checking the version of any File 

You can use the commands like the following:

strings -a $XX_TOP/filename |grep '$Header'

Q: I'm getting a Yellow Warning Bar. How do I get rid of this?

a.  What Does "Warning: Applet Window" Mean?

Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode, and uses J-Initiator to run Java applets on a desktop client. If an applet is "trusted," however, Java will extend the privileges of the applet. The Yellow Warning Bar is a warning that your applet is not running in a trusted mode. To indicate that an applet is trusted, it must be digitally signed using a digital Certificate, so Oracle Applications requires that all Java archive files must be digitally signed.

b.  Who Does This Affect?

This affects all users that try to access Oracle Applications Rel 11i using Jinitiator that have a different identitydb.obj on their client. Clients have an "identity database" that is maintained by J-Initiator called IDENTITYDB.obj. When a jar file is downloaded, the owner of the digital signature is compared against the entry in the identity databases. If they match, the code contained in the archive is allowed to run in a trusted mode. The users will need to fix their client PC

in one of two ways:

i.  

a.  Uninstall Jinitiator and clear browser cache 

b.  Log back into Applications to get the new plugin,  (oajinit.exe) including the new identitydb.obj 

c.  Install the Jinitiator on the Client PC and then Log into the

Oracle Applications to download the new signed JAR files 

a.  Copy the IDENTITYDB.OBJ file to C:\Program Files\Oracle after saving the old one as IDENTITYDB.old. 

Q: if you want to check the URL of the Application in the database in which table  you can check ? 

 Method 1

select  *  from icx_parameters

Method 2 

SELECT 

PROFILE_OPTION_VALUE 

FROM 

FND_PROFILE_OPTION_VALUES 

WHERE 

PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT') AND  LEVEL_VALUE=0  ;

select * from FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT'

Q Can you tell me a few tests you will do to troubleshoot self-service login problems?  Which profile options and files will you check?

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. 

How will you change the apps password?

Use FNDCPASS to change APPS password.  Manually modify wdbsvr.app/cgiCMD.dat files. Change any DB links pointing from other instances. 

If you changed the APPS (and APPLSYS) password, update the password in these files:

•  iAS_TOP/Apache/modplsql/cfg/wdbsvr.app 

•  ORACLE_HOME/reports60/server/CGIcmd.dat

If you changed the APPLSYSPUB password, update the password in these files:

•  FND_TOP/resource/appsweb.cfg 

•  OA_HTML/bin/appsweb.cfg 

Q Conflict resolution managers resolves the conflicts yes , but how it knows that there  are conflicts? why conflicts occur? 

Concurrent managers read request to start concurrent programs running. The  Conflict Resolution Manager checks concurrent program definitions for  incompatibility rules. 

If a program is identified as Run Alone, then the Conflict Resolution Manager  prevents the concurrent managers from starting other programs in the same  conflict domain. 

When a program lists other programs as being incompatible with it, the Conflict Resolution Manager prevents the program from starting until any incompatible  programs in the same domain have completed running.

Q What is adovars.env file ?

The adovars.env file, located in $APPL_TOP/admin, specifies the location of variousfiles such as Java files, HTML files, and JRE (Java Runtime Environment) files. It is called  from the main applications environment file.

Q Tell in brief abut the various ICX profiles in Oracle APPS.

The Personal Homepage allows Forms, Reports and Discoverer sessions to be launched from a single web page. You must set these site–level profile options to identify the base URL to use when launching each of these applications. Set the Site level value of each option to the base URL to launch the appropriate application.

1) ICX: Forms Launcher

This ICX: Forms Launcher profile option is used to launch forms directly using the URL given.

http://oracle-qapt-int-qa.security.ge.com:80/dev60cgi/f60cgi

 2) ICX: Report Launcher:

http://oracle-qapt-int-qa.security.ge.com:80/dev60cgi/rwcgi60

 3) ICX: Discoverer Launcher:

http://amuscinsesapp05.gesm.ge.com:80/discoverer/plus?Connect=[APPS_SECURE]

4) ICX: Session Timeout:

How does AutoConfig set the profile option ICX: Session Timeout?

AutoConfig uses the variable s_sesstimeout from the $APPL_TOP/admin/<context>.xml to determine the ICX: Session Timeout Profile Option. AutoConfig runs the script afwebprf.sql which does the following:

      Setting ICX_SESSION_TIMEOUT to correspond to Jserv Session Timeout

      set_profile(178, 'ICX_SESSION_TIMEOUT', 10001, 0, '%s_sesstimeout%'/(1000*60), NULL);

Update the .xml file so that s_sesstimeout/(1000*60)[input in minutes] equals the value you want it to be.  Keep in mind that the s_sesstimeout is used in the zone.properties file as well. The session timeout in zone.properties is basically set to the same value as the profile option, except for the fact that in zone.properties it is specified in milliseconds and the profile option is in minutes.

Edit $APACHE_TOP/Apache/Jserv/etc/zone.properties and set the session.timeout in milliseconds:

session.timeout=600000

This is Apache Jserv Session timeout and should not be any higher than 30 minutes. Longer idle sessions will drain the JVM resources and can also cause out of memory errors.

Login into Oracle Applications as SYSADMIN and search for profile ICX: Session Timeout; set this Profile Option to the same value as the one specified by session.timeout in zone.properties.

Q How to check which programs have trace enabled?

select a.application_id,a.concurrent_program_id,a.LAST_UPDATE_DATE,

b.USER_CONCURRENT_PROGRAM_NAME,a.ENABLE_TRACE

from fnd_concurrent_programs a,fnd_concurrent_programs_tl b

where a.application_id=b.application_id

and a.concurrent_program_id=b.concurrent_program_id

and a.enable_trace='Y';

==================================================

select user_concurrent_program_name

from apps.fnd_concurrent_programs_vl where enable_trace='Y';

===================================================

if you want to disable trace then use >>

update fnd_concurrent_programs set ENABLE_TRACE='N' where APPLICATION_ID=&app_id and ENABLE_TRACE='Y';

Q What is the log file location for Report services?

$COMMON_TOP/admin/log/<SID>_<HOSTNAME>/rep60_<SID>_<HOSTNAME>.txt

/qat/apps/qatcomn/admin/log/qat_tned35n $ ls -ltr rep*

-rw-r--r--   1 applqait dbaqa      12583 Sep 30 10:07 rep60_qat_tned35n.txt

This log file contains the information about the startup and stopping of report services.

Q What are the Oracle Applications patch types?

All Applications patches are organized by aggregation level.

Standalone (one-off) Patch:

Addresses a single fix or enhancement. Standalone patches are released only when there is an immediate need for a fix or enhancement that cannot wait until an aggregate bundling is available. Although standalone patches are intended to be as small as possible, they usually include any dependent files that have changed since the base release in order to form a complete patch that can be applied by any customer. The actual number of files changed will depend on the current code level on the system to which the patch is being applied.

Rollup Patch (RUP):

An aggregation of patches that may be at the functional level, or at a specific product/family release level. For example, a Flexfields rollup patch contains all the latest patches related to Flexfields at the time the patch was created. A Marketing Family 11.5.10 rollup patch contains all the latest Marketing patches released since, and applicable to, 11.5.10.

Minipack:

An aggregation of patches at the product level. For example, Inventory Minipack G (11i.INV.G) contains all the latest patches for the Inventory product at the time the minipack was created. Minipacks are named in alphabetical sequence such as 11i.INV.E, 11i.INV.F, 11i.INV.G, and so on. Minipacks are cumulative. In other words, 11i.INV.G contains everything in 11i.INV.F, which contains everything in 11i.INV.E, and so on. The terms patchset and minipack are often used interchangeably.

Family Pack:

An aggregation of patches at the product family level. For example, Financials Family Pack C (11i.FIN_PF.C) contains all the latest patches for products in the Financials family at the time the family pack was created. Family product codes always end in "_PF" and family packs are given alphabetical sequence such as 11i.HR_PF.B, 11i.HR_PF.C, and 11i.HR_PF.D. Family packs are cumulative. In other words, Discrete Manufacturing Family Pack G (11i.DMF_PF.G) contains everything in 11i.DMF_PF.F, which contains everything in 11i.DMF_PF.E, and so on.

Maintenance Pack: An aggregation of patches for all products in the E-Business Suite. For example, Release 11.5.10 Maintenance Pack contains all the latest code level for all products at the time 11.5.10 was created. Maintenance packs are numbered sequentially such as 11.5.8, 11.5.9, 11.5.10, and are cumulative. In other words, 11.5.10 contains everything in 11.5.9, which contains everything in 11.5.8, and so on.

Q  Describe a patch driver file.

Some patches contain multiple patch driver files that split the actions of the patch. The copy (c) driver changes Oracle Applications files, the database (d) driver changes Oracle Applications database objects, and the generate (g) driver generates forms, reports, graphics, or message files.

Some patches can combine the actions of the driver files into a single driver called a unified driver (u). A unified driver performs all the functions of the copy, database, and generate drivers.

The unified driver, named u<patch_num>.drv, contains the commands necessary to change files and database objects, and to generate new objects. It contains a sequential list of copy, database, and generate instructions, which are arranged in sections.

Q How to apply a  patch without applying maintenance mode.?

You can run AutoPatch by using options=hotpatch on the command line when Maintenance mode is disabled. However, applying a 'hot patch' may result in significant degradation of system performance.

Adpatch runs AutoConfig to update configuration files if any template files are introduced or updated by the patch

Adpatch perform the required action on the $APPL_TOP. In a shared APPL_TOP system, changes made during patching sessions on one node are immediately available on all nodes, but if $APPL_TOP is not shared then you have to apply the patch individually to all the nodes.

 It is very important that you back up the file system and database before you apply large patches (like minipacks, family packs, or maintenance packs).

In adpatch interactive session, instead of giving the required prompt if you type abort then adpatch session will end, however when patch application is started you cannot abort the session. Please not once the patch application is started and if you kill the worker through adctrl then all the changed that are done so far will remain changed up to that point

Q What are important patch log files and location?

Patch Log File: -       $APPL_TOP/admin/<SID>/log/******.log

                                    /qat/apps/qatappl/admin/qat/log/5012557.log

 adpatch.log:           Main AutoPatch log file (default name)

adpatch.lgi:            For AutoPatch informational messages (default name)

adrelink.log:           For relinking

adworkxxx.log:       For database operations run in parallel

Q  Explain various functions of adpatch command/utility ?

v  Backs up all existing files that will be changed by the patch

v  Copies files

v  Archives files in libraries

v  Relink executables

v  Generates forms, reports, message, graphics, and Java archive (JAR) files

v  Compiles JSP files and invalid database objects

v  Updates database objects

v  Maintains Multiple Reporting Currencies (MRC) schema, if needed

v  Runs AutoConfig to update configuration files, if any template files are introduced or updated by the patch

v  Saves patch information to the database

Q What are the various adpatch options ?

Apply Only Database Portion:

I received a patch that contains a unified driver; however, the instructions state that I run only the database portion of the patch.

 $ adpatch options=nocopyportion,nogenerateportion

Testing a Patch before Applying:

Running AutoPatch in test mode lists each file it will copy, generate, relink, or execute, but it doesn’t actually perform these actions.

$ adpatch apply=no

Enabling Password Validation:

In order to reduce the time it takes to apply a patch, AutoPatch (by default) does not validate passwords. If you need to enable password validation, you can do so by supplying the validate option (options=validate) on the command line when you run AutoPatch.

$ adpatch options=validate

Applying Patch Online:

Run AutoPatch using options=hotpatch and apply the patch. You may not have to shut down the server processes.

$ adpatch options=hotpatch,nocompiledb,nocompilejsp,noautoconfig

Compiledb: - Tells AutoPatch to automatically compile invalid objects in the database after running actions normally found in the database driver

Default: compiledb for standard patches, nocompiledb for standard patch translations, documentation patches, and documentation patch translations

$ adpatch options=nocompiledb

Compilejsp: - Tells AutoPatch whether to automatically compile out-of-date JSP files, JSP files are only compiled if the patch contains copy actions for at least one JSP file.

Default: compilejsp for standard patches, nocompilejsp for standard patch translations, documentation patches, and documentation patch translation

$ adpatch options=nocompilejsp

Copyportion: - Tells AutoPatch whether to run commands normally found in a copy driver

$ adpatch options=nocopyportion

Databaseportion: - Tells AutoPatch whether to run commands normally found in a database driver

$ adpatch options=nodatabaseportion

Generateportion: - Tells AutoPatch whether to run commands normally found in a generate driver

$ adpatch options=nogenerateportion

Nointegrity: - Tells AutoPatch whether to verify that the version of each file referenced in a copy action matches the version present in the patch. Using options=nointegrity is safe and avoids some AutoPatch overhead.

Parallel: - Tells AutoPatch whether to run actions that update the database in parallel (like sql) and actions that generate files in parallel (like genform).Oracle does not recommend changing the default, as Oracle Applications patches are tested on systems using parallel processing.

Prereq: - Tells AutoPatch whether to check that prerequisite patches have been applied prior to running patch driver files that contain actions normally found in the copy driver. Oracle does not recommend changing the default.

Novalidate: - Tells AutoPatch whether to connect to all registered Oracle Applications schemas at the start of the patch. Validate is useful for finding problems with incorrectly registered Oracle Applications schemas or schemas with invalid passwords.

$ adpatch options=validate

Q Describe the various steps of patch application.

Steps of Applying Application Patch:

1. Do the patchimpact Analysis:

$ perl patchimpact.pl <patch_number> db

2. Download the patch in /patches_dev directory and unzip the same.

3.  Review the Readme file and determine to prerequisites and manual steps.

4. Stop all application services on all internal nodes and then external nodes.

$ cd /<oracle_sid>/apps/<oracle_sid>comn/admin/scripts/<oracle_sid>_<hostname>

$ adstpall.sh apps/<apps_pwd>

5. Run adadmin and enable the maintenance mode [5 - 1]

$ sqlplus -s &un_apps/***** @/prj5/apps/prj5appl/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE

6. Check the invalids before applying a patch

SQL> SELECT COUNT (*) FROM ALL_OBJECTS WHERE STATUS='INVALID';

SQL> SELECT OWNER, STATUS, COUNT (*) FROM ALL_OBJECTS WHERE STATUS='INVALID' GROUP BY OWNER, STATUS;

SQL> SELECT OWNER, OBJECT_TYPE, COUNT (*) FROM ALL_OBJECTS WHERE STATUS='INVALID' GROUP BY OWNER, OBJECT_TYPE;

SQL> SELECT OWNER, STATUS, OBJECT_NAME, TIMESTAMP, COUNT (*) FROM DBA_OBJECTS WHERE STATUS='INVALID' GROUP BY OWNER, STATUS, OBJECT_NAME, TIMESTAMP;

7. Start autopatch

Go to the patch location run the adpatch in internal server1 as $APPL_TOP is shared the patch needs to be applied in any one of the nodes

$ cd /patches_dev/<patch_num>

$ adpatch options=nocompiledb,nocompilejsp,noautoconfig

$ adpatch options=hotpatch,nocompiledb,nocompilejsp,noautoconfig

$ adpatch options=forcecopy,nocompiledb,nocompilejsp,noautoconfig

In case of hotpatch, the applications are not brought down and Maintenance mode is not enabled. (This is only for very small patches not affecting the DB Objects)

 8. Respond to the Autopatch Options and Apply Drivers:

If multiple driver files then sequence to apply would be c-d-g

 9. Review the log file and Invalid Count:

Monitor the patch application process using adctrl in a parallel window. Also for big patches check the table fnd_install_processes.

Later on review the log file and the invalid count, and if necessary run with proper adadmin options [$APPL_TOP/admin/<SID>/log/adpatch.log]. If the invalid count in apps schema has increased post patching, run adadmin to compile apps schema. In case the invalids apart from the apps schema has increased too then run the following script from DB Tier:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> exec utl_recomp.recomp_parallel (80);

 10. Check From AD_BUGS:

SQL> SELECT COUNT (*) FROM AD_BUGS WHERE BUG_NUMBER ='<PATCH_NUM>';

 11. Review customizations.

Customized files must be registered in the $APPL_TOP/admin/applcust.txt file. If AutoPatch displays a message indicating that previously registered, customized files will be replaced by the patch, review those files to determine if customizations need to be re-applied or merged.

 12. Pre-allocate space for packages, functions, and sequences (optional):

If AutoPatch has modified Oracle Applications database objects, you may want to run ADXGNPIN.sql and ADXGNPNS.sql to allocate space ("pin") for new packages and sequences in the Oracle System Global Area. These scripts are located in AD_TOP/sql.

 13. Disable the maintenance mode

 14. Start the application services

 15. Login to the front end and submit a dummy Conc. Prog "Work Shifts Report" or "Active Users" to verify if everything is fine.

========================================================

 There is no general method of backing out changes a patch makes to the Oracle Applications database.

 ========================================================

What is maintenance mode?

Maintenance Mode is a new mode of operation introduced with Release 11.5.10, in which the Oracle Applications system is made accessible only for patching activities not allowing the users to login to any responsibility. This provides optimal performance for AutoPatch sessions, and minimizes downtime needed.

Maintenance mode is Enabled or Disabled from adadmin.

When you Enable or Disable 'Maintenance Mode', adadmin will execute the script:

$ sqlplus apps/******* @adsetmmd.sql ENABLE

$ sqlplus apps/******* @adsetmmd.sql DISABLE

 Basically it sets the profile option "Applications Maintenance Mode" to "MAINT" to Enable Maintenance Mode and to "NORMAL" to disable it.

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

Location of adsetmmd.sql:

$AD_TOP/patch/115/sql/adsetmmd.sql

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

Backend Script to Check Maintenance Mode Status:

SQL> SELECT FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE') FROM DUAL;

FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE')

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

MAINT

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

Maintenance Mode Enable Back End Script:-

$ sqlplus -s apps/***** @/qat/apps/qatappl/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE

Note: Apache must be re-started in normal mode after disabling 'Maintenance Mode' by using the Script adapcctl.sh

What is merge patch utility?

Patches that affect the Applications DBA (AD) product must be handled separately. AD patches can be merged with other AD patches, but AD patches and non-AD patches cannot be merged because AD patches may change the AutoPatch utility itself. Merged AD patches must be created separately and applied before you apply non-AD patches.

 The merged patch is created in the destination directory. Run AD Merge Patch and supply the arguments for the destination directory name, and the source directory name. Specify the merged patch name, or accept the default.

$ admrgpch -s <source directory> -d <destination directory>

 $ admrgpch -s <source directory> -d <destination directory> \

-merge_name <name> [-manifest <manifest filename>]

Note: If you don’t want to create a manifest file, unzip all the patches to be merged into the source directory. Omit the manifest file argument.

 Check AD Merge Patch log files: - The file is located in the current working directory (where AD Merge Patch was run).

Q Give a brief description of copy driver in autopatch.

Description of Copy Driver:

copy             fnd               admin/sql                fnddold.sql  110.4

copy             fnd               forms/US                FNDADDTC.fmb 110.4

libout            fnd                        lib                          afdict.o

copy             fnd                         lib                          afdict.o110.1

libin              fnd                        lib                          afdict.o

forcecopy      fnd                        media                              FNDIEROR.gif

link               fnd                        bin                                   FNDCLOAD

Copy            fnd                     forms/US                              FNDADDTC.fmb 110.4

ADPATCH is smart and it does version checks.  It will not overlay a higher version object or sql files. If the version on the file system is the same or higher it skips the copy. In the above example, notice the version to the right of the filename.  It uses this version to do the compare.  The “fnd” next to the copy tells ADPATCH what applications top to use and “forms” points adpatch to the proper directory.  With the above statement ADPATCH will copy the file FNDADDTC.fmb (from the patch directory) to $FND_TOP/forms/US.  The copy actually moves the existing FNDADDTC.fmb file to FNDADDTC.fmbO and then physically copies the new file version to FNDADDTC.fmb.  The file FNDADDTC.fmbO will remain on the file system.

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

Libout      fnd     lib     afdict.o

Libout is a utility that removes files from the library archive.  The archive file resides in the lib directory under the TOP directory of the application. The name of the file is lib<application>.a.  So the archive file for FND is located in $FND_TOP/lib and is named libfnd.a.  The actual command that libout executes is:

$ ar  d libfnd.a afdict.o

This removes the object file (afdict.o) from the library file (libfnd.a).

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

Libin        fnd     lib     afdict.o

Libin is the utility that adds an object file to the library file.  The actual command that libin executes is:

$ ar rv libfnd.a afdict.o

The object file (afdict.o) is appended to the bottom of the library file. Libin is executed even if the copy did NOT take place because the information was already removed with the libout command.

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

Forcecopy    fnd     media           FNDIEROR.gif

Similar to copy, but no version checking is done. Forcecopy copies the target file irregardless of the presence or version of the file currently on the target system.

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

Link         fnd     bin     FNDCLOAD

The link process is similar to a compile.  It links new object files together with the other objects files to create the executable.  The command that the above link is using:

$ adrelink force=y "fnd FNDCLOAD"

The "fnd" is the top that the executable is being relinked in.  The executable FNDCLOAD has to be in the proper case that it would normally appear in the $FND_TOP/bin directory.

========================================================

Q What dos the d( database) driver do?

The “d” (database) drivers alter the tables in the database. These patches can create tables, add indexes and alter the APPS schema.  The only way to back-out the changes for “d” driver is with a database backup.  Even if the executables are restored (on the file system), they will not work because information in the database has already been changed.

Q Give brief description of G ( generate) driver.

Description of Generate (G) Driver:

 The “g” (generate) drivers generate forms, .plls (PL/SQL libraries), reports and menus. The following is an excerpt from a “g” driver:

genform                 fnd                        forms/US                FNDWFNOT.fmb

genrep                    fnd               reports                   FNDCPWKM.rdf

genfpll                    fnd               resource                           JL.pll

genmenu                 fnd               resource/US            FNDMENU.mmb

1. Genform uses f45gen to generate forms.

f45gen userid=APPS/APPS module=<form name>.fmb \

output_file=<full path of schema top>/forms/US/<form name>.fmx \

module_type=form batch=no compile_all=special

2. Genrep uses r25convm to generate reports

r25convm userid=APPS/APPS source=/<full path of schema top>/reports/<report name>.rdf \

dest=/<full path of schema top>/reports/<report name>.rdf stype=rdffile \

dtype=rdffile logfile=<report name>.log overwrite=yes batch=yes \

dunit=character

3. Genfpll uses f45gen to generate .plls (forms libraries)

f45gen userid= APPS/APPS module=<library name>.pll module_type=library \

output_file=/<full path of schema top> /resource/<library name>.pll \

compile_all=Yes \

4. Genmenu uses FNDMDGEN to generate menus

FNDMDGEN APPS/APPS 0 Y <language codename> \

DB_TO_RUNTIME <application shortname> <message filename>.msb

Q How patches can be backed out if there is a problem?

 We always recommend a cold backup of a system prior to applying any patch.  We also recommend applying patches to a test system first.  However, for those cases where you haven’t done these two steps; you should know that database drivers “d” CANNOT be backed out without a system backup.  If the “c” or “g” drivers need to be backed out, reverse the steps from the “c” driver and then relink, then reverse the steps from the “g” driver. This will require the user to manually review the driver files and create commands that will do the opposite of the process.  There is no utility you can use to reverse the patch.

Q Auto patch had failed and you want to start a new patching session now. How to do it?

To start a completely new AutoPatch session when there is an existing failed session, specify interactive=no and abandon=yes on the AutoPatch command line. With this command, AutoPatch deletes the restart files and any leftover database information from the failed session.

Q How to recompile the ad_patch package?

Recompile the ad_patch package by running the following:

$AD_TOP/patch/115/sql/adphpchs.pls

$AD_TOP/patch/115/sql/adphpchb.pls

Q What is the AutoPatch checkfile feature?

 The checkfile feature reduces patch application downtime by checking to see if a given database action has been performed previously for the associated file contained in the patch. If an action has been performed using the current (or higher) version of a file, AutoPatch omits the action from the current patch application.

How to use adrelink command?

$ cd AD_TOP/bin

$ adrelink.sh force=y ranlib=y "PO RVCTP"

 Logfile Location: /prj3/apps/prj3appl/admin/log/adrelink.log

 ========================================================

 In case of Error While Opening Log Files | View Log:

Stop the application listeners, and then relink through adadmin & startup the application listener. This should work.

 $ cd $FND_TOP/bin

$ mv FNDFS FNDFS.bak

$ adrelink.sh force=y "fnd FNDFS" (As APPLMGR account user)

If not working then go ahead and bounce all the application services

Q Guest user password is very important for the functioning of Application SSWA.

Self service login used this password to verify the user password.Selfservice will not come up

if this password is incorrect.Direct form login will still work if the password is incorrect 

It is stored in following places in 11i

$FND_TOP/secure/

GUEST_USER_PWD profile

--It is a application user not a DB user while APPLSYSPUB is a db user

--11i never use this for login ...this is used to obtain apps info

--It should be consistent on both the places,otherwise application will not work.

--FNDCPASS also make use of this password for changing the password.

--Following command could be use to check it correctness

select apps.fnd_web_sec.validate_login('GUEST','ORACLE') FROM DUAL; 

--GUest user password cannot be changed through FNDCPASS.Most appropiate way to change it through user define form.You can get into User define form through forms login

--It should never be enddated

--Following query could be use to obtain the guest user profile

SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual;

--All the guest user and password manipulation is done through FND_WEB_SEC package stored in apps schema

Q What is the use of Application listener?

Apps Listener is used for various purposes, one is to check output & log files in concurrent requests. Check if apps listener is fine by clicking on log file & output file for Active Users request. If you encounter any issues while trying to access Log & Output file issue might be with Oracle Apps Listener. Check if adalnctl.sh (where ad is APPS DBA, aln is apps listener and ctl is control) on application tier started successfully.

Q What happens if ICM goes down?

If the ICM   goes down, requests will continue to run normally, except for “Queue Control” requests. “Queue Control” requests are those programs for which QUEUE_CONTROL_FLAG=Y In table fnd_concurrent_programs

SQL> select CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where QUEUE_CONTROL_FLAG='Y';

 If the ICM goes down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.

Q Describe the work of standard manager.

Standard Managers communicates with the Service Manager and any client application process. The Standard Manager is a worker process that initiates, and executes client requests on behalf of Applications batch, and OLTP clients. All concurrent programs by default go to this Manager until and unless we include/exclude that in specialization rule.

Q What is service manager? FNDSM process?

Service Manager Communicates with the Internal Concurrent Manager, Concurrent Manager, and non-Manager Service processes.

Once GSM is enabled, the ICM uses Service Managers to start all concurrent managers and activated services. If the ICM is successfully starting the managers, then GSM has been configured properly. If managers and/or services fail to start, errors should appear in the ICM logfile.

Q How to Create Service Manager?

Execute the script $FND_TOP/patch/115/sql/afdcm037.sql as apps user.

Note: Service Manager "FNDSM" can not be created from frontend Concurrent > Manager > Define under Sysadmin Responsibility. 

Logfile for Service Manager:

$ ls -ltr $APPLCSF/log/FNDSM*mgr

Q How to Terminate a Conc. Request from Backend?

 1. Update the request status to completed – terminated.

SQL> UPDATE fnd_concurrent_requests SET phase_code = 'C', status_code = 'X' WHERE request_id=&REQUEST_ID;

Please note, sometime this update may create lock in the DB and update may not be accomplished until and unless you clear the lock.

 2. Clear all the Oracle & UNIX processes.

SQL> SELECT request_id, phase_code, status_code, oracle_process_id, logfile_node_name, 'ps -ef|grep '||oracle_process_id

FROM fnd_concurrent_requests WHERE request_id=&request_id;

Q How to purge concurrent requests which are in pending state?

The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed before purging them.

 SQL> UPDATE fnd_concurrent_requests SET phase_code = 'C' WHERE phase_code = 'P'

Q Describe various phases and status of concurrent requests.

Concurrent Request Status Table:-

What  is XLIB error in context of cncurrent request?

XLIB Error | xhost + Not Working | refused by server

Request failed with following error:

Xlib: connection to "alpgaappqi01sec:1.0" refused by server

Xlib: Invalid MIT-MAGIC-COOKIE-1 key

Xlib: connection to "alpgaappqi01sec:1.0" refused by server

Xlib: Invalid MIT-MAGIC-COOKIE-1 key

REP-3000: Internal error starting Oracle Toolkit.

REP-3000: Internal error starting Oracle Toolkit.

Solution:

1. Check DISPLAY in .profile and set the display accordingly.

$ export DISPALY=alpgaappqi01sec:1.0

$ export DISPLAY=`hostname`:"1".0

2. Delete form /tmp/.X11-unix

$ ls -al /tmp/.X11-unix/X*

$ rm -rf /tmp/.X11-unix/X*

4. Delete *.pid and *.log form $HOME/.vnc

$ ls -al /export/users/applqait/.vnc/*.pid /export/users/applqait/.vnc/*.log

$ rm -rf /export/users/applqait/.vnc/*.pid /export/users/applqait/.vnc/*.log

5. Start vncserver from /usr/local/vnc

6. Bounce the report serveices

$ /qait/apps/qaitcomn/admin/scripts/qait_alpgaappqi01sec/adrepctl.sh start

Q What are the parameteres given for Gather Schema Statistics request?

Gather Schema Statistics Request Submission:-

Name: - Gather Schema Statistics

Parameter Listing:-

1. Schema Name                                   : MSC

2. Estimate Percent                                         : 20

3. Degree                                            : NULL

4. Backup Flag                                      : NOBACKUP

5. Restart Request ID                                      : NULL

6. History Mode                                     : LASTRUN

7. Gather Options                                  : GATHER

8. Modifications Threshold                       : NULL

9. Invalidate Dependent Cursors               : Y

Q Describe the differences between Character-mode Reports VS Bit-mapped Reports

Every report is defined to Oracle Applications as a Concurrent Program. In that definition of the report, it is possible for the System Administrator to decide on the output format of the report. The choices are - Text/Postscript/HTML/PDF/Binary

 For report with output format as TEXT, Oracle Applications will run a program called, ar60run. If you chose any other formats Oracle Applications will run a program called, ar60runb. The difference in the 2 programs is one handles character-mode reports (ar60run) and the other handles bit-mapped reports (ar60runb).

Bit-mapped reports also differ in significant ways from character-mode reports. Character-mode reports take their dimensions (rows and columns) and orientation from the Print Style. Bit-mapped reports take their dimensions and orientation from the report definition, and can be overridden by the "Execution Options" field. To override dimensions, put a value into the

"Execution Options" field as follows:

PAGESIZE= <width>x<height>

If the dimensions specified with the PAGESIZE parameter are smaller than what the report was designed for, you will generate a "REP-1212" error.

ORIENTATION=LANDSCAPE

ORIENTATION=PORTRAIT

When entering more than one execution option, each option should be separated by a single space. There should be no spaces before or after the options. For example:

ORIENTATION=LANDSCAPE PAGESIZE=7.5x9

In Oracle Reports, when defining a report, the units and size of the report are specified in the menu under Report->Global Properties->Unit of Measurement.

For bitmapped reports, <width>x<height> for PAGESIZE is usually in inches

Q What is Parallel Concurrent Processing (PCP) and how it works?

Configuring parallel concurrent processing allows you to distribute concurrent managers, and workload across multiple nodes in a cluster, or networked environment. PCP can also be implemented in a RAC environment in order to provide automated failover of workload should the primary (source), or secondary (target) concurrent processing nodes, or RAC instances fail.

The basic failure scenarios are:

1. The database instance that supports the CP, Applications, and Middle-Tier processes such as Forms, or iAS can fail.

2. The Database node server that supports the CP, Applications, and Middle-Tier processes such as Forms, or iAS can fail.

3. The Applications/Middle-Tier server that supports the CP (and Applications) base can fail.

The concurrent processing tier can reside on the Applications, Middle-Tier, or Database Tier nodes.  In a single tier configuration, non PCP environment, a node failure will impact Concurrent Processing operations due to any of these failure conditions. In a multi-node configuration the impact of any these types of failures will be dependent upon what type of failure is experienced, and how concurrent processing is distributed among the nodes in the configuration. Parallel Concurrent Processing provides seamless failover for a Concurrent Processing environment in the event that any of these types of failures takes place.

Q How PCP Works:

The current Concurrent Processing architecture with Global Service Management (GSM) consists of the following processes and communication model

ICM (FNDLIBR process) - Communicates with the Service Manager.

The Internal Concurrent Manager (ICM) starts, sets the number of active processes, monitors, and terminates all other concurrent processes through requests made to the Service Manager, including restarting any failed processes.  The ICM also starts and stops, and restarts the Service Manager for each node.  The ICM will perform process migration during an instance or node failure.  The ICM will be active on a single node.  This is also true in a PCP environment, where the ICM will be active on at least one node at all times.

Service Manager (FNDSM process) - Communicates with the ICM, Concurrent Manager Service processes, and non-Manager Service processes.

The Service Manager (SM) spawns, and terminates manager and service processes (these could be Forms, or Apache Listeners, Metrics or Reports Server, and any other process controlled through Generic Service Management).  When the ICM terminates the SM that resides on the same node with the ICM will also terminate.  The SM is ‘chained’ to the ICM.  The SM will only reinitialize after termination when there is a function it needs to perform (start, or stop a process), so there may be periods of time when the SM is not active, and this would be normal.  All processes initialized by the SM inherit the same environment as the SM.  The SM’s environment is set by APPSORA.env file, and the gsmstart.sh script.  The TWO_TASK used by the SM to connect to a RAC instance must match the instance_name from GV$INSTANCE.  The apps_<sid> listener must be active on each CP node to support the SM connection to the local instance.  There should be a Service Manager active on each node where a Concurrent or non-Manager service process will reside.

Internal Monitor (FNDIMON process) - Communicates with ICM 

The Internal Monitor (IM) monitors the Internal Concurrent Manager, and restarts any failed ICM on the local node.  During a node failure in a PCP environment the IM will restart the ICM on a surviving node (multiple ICM's may be started on multiple nodes, but only the first ICM started will eventually remain active, all others will gracefully terminate).  There should be an Internal Monitor defined on each node where the ICM may migrate.

Standard Manager (FNDLIBR process) - Communicates with the Service Manager and any client application process

The Standard Manager is a worker process, which initiates, and executes client requests on behalf of Applications batch, and OLTP clients.

Transaction Manager - Communicates with the Service Manager, and any user process initiated on behalf of a Forms, or Standard Manager request.

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

Concurrent Manager Setup | PCP Setup for RAC Environment:

1. Check the setup using Note- 165041.1

2. <APPLDCP oa_var="s_appldcp">ON</APPLDCP> this should be the value in application action. Normal value is OFF

3. Take backup of tnsnames.ora, listener.ora and sqlnet.ora and $COMMON_TOP/admin/scripts (service related) files.

8.0.6 ORACLE_HOME/ network/admin/<SID_HOSTNAME>/*.ora

4. Regenerate the configuration using autoconfig

5. Merge Changes in tnsnames.ora, listener.ora, sqlnet.ora:

After regenerating the configuration merge any changes back into the tnsnames.ora, listener.ora and sqlnet.ora files in the network directories, and the startup scripts in the COMMON_TOP/admin/scripts/<SID> directory.  Each nodes tnsnames.ora file must contain the aliases that exist on all other nodes in the cluster. When merging tnsnames.ora files ensure that each node contains all other nodes tnsnames.ora entries.  This includes tns entries for any Applications tier nodes where a concurrent request could be initiated, or request output to be viewed.

6. In the tnsnames.ora file of each Concurrent Processing node ensure that there is an alias that matches the instance name from GV$INSTANCE of each Oracle instance on each RAC node in the cluster.  This is required in order for the SM to establish connectivity to the local node during startup.  The entry for the local node will be the entry that is used for the TWO_TASK in APPSORA.env (also in the APPS<SID>_<HOSTNAME>.env file referenced in the Applications Listener [APPS_<SID>] listener.ora file entry "envs='MYAPPSORA=<some directory>/APPS<SID>_<HOSTNAME>.env) on each node in the cluster (this is modified in step 12).

7. Verify that the FNDSM_<SID> entry has been added to the listener.ora file under the 8.0.6 ORACLE_HOME/network/admin/<SID> directory.

For Further details follow Note: 165041.1 for instructions regarding configuring this entry.

8. Run Autoconfig

9. Startup listener on all nodes

10. Navigate to System Administrator > Install > Nodes and ensure that each node is registered.

11. Navigate to System Administrator > Concurrent > Manager > Define, and set up the primary and secondary node names for all the concurrent managers according to the desired configuration for each node’s workload. The Internal Concurrent Manager should be defined on the primary PCP node only.  When defining the Internal Monitor for the secondary (target) node(s), make the primary node (local node) assignment, and assign a secondary node designation to the Internal Monitor, also assign a standard work shift with one process.

12. Prior to starting the Manager processes it is necessary to edit the APPSORA.env file on each node in order to specify a TWO_TASK entry that contains the INSTANCE_NAME parameter for the local nodes Oracle instance, in order to bind each Manager to the local instance.  This should be done regardless of whether Listener load balancing is configured, as it will ensure the configuration conforms to the required standards of having the TWO_TASK set to the instance name of each node as specified in GV$INSTANCE.  Start the Concurrent Processes on their primary node(s).  This is the environment that the Service Manager passes on to each process that it initializes on behalf of the Internal Concurrent Manager.  Also make the same update to the file referenced by the Applications Listener APPS_<SID> in the listener.ora entry "envs='MYAPPSORA=<some directory>/APPS<SID>_<HOSTNAME>.env" on each node.

13. Navigate to Concurrent > Manager > Administer and verify that the Service Manager and Internal Monitor are activated on the secondary node, and any other additional nodes in the cluster. The Internal Monitor should not be active on the primary cluster node.

14. Stop and restart the Concurrent Manager processes on their primary node(s), and verify that the managers are starting on their appropriate nodes. On the target (secondary) node in addition to any defined managers you will see an FNDSM process (the Service Manager), along with the FNDIMON process (Internal Monitor).

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

Failover Considerations:

In order to have log and output files available to each node during an extended node failure, each log and out directory needs to be made accessible to all other CP nodes in the cluster (placed on shared disk). In order to view log and output files from a failed node during an outage/node failure, FNDFS_<HOST> entry in tnsnames.ora on each node should be configured using an ADDRESS_LIST or another option is each node tnsnames.ora FNDFS_<HOST> entry should contain entries for all nodes.

FNDFS_CINOHAPPPI01SEC=

        (DESCRIPTION=

                (ADDRESS_LIST=

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI01SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI02SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI03SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI04SEC.security.ge.com)(PORT=3001)))

            (CONNECT_DATA=

                (SID=FNDFS)

            )

        )

FNDFS_CINOHAPPPI02SEC=

        (DESCRIPTION=

                (ADDRESS_LIST=

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI01SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI02SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI03SEC.security.ge.com)(PORT=3001))

                (ADDRESS=(PROTOCOL=tcp)(HOST=CINOHAPPPI04SEC.security.ge.com)(PORT=3001)))

            (CONNECT_DATA=

                (SID=FNDFS)

            )

        )

Without configuring connect time failover using the 8.0.6 ORACLE_HOME tnsnames.ora by specifying the ADDRESS_LIST entry the only other alternative is to perform a manual update of the fnd_concurrent_requests table for each request, in order to reflect the change in outfile_node_name, and logfile_node_name from the failed to the surviving node.

SQL> select outfile_node_name from fnd_concurrent_requests where request_id=87521760;

OUTFILE_NODE_NAME

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

CINOHAPPPI04SEC

SQL> select logfile_node_name from fnd_concurrent_requests where request_id=87561106;

LOGFILE_NODE_NAME

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

CINOHAPPPI04SEC

SQL> update fnd_concurrent_requests set outfile_node_name = 'CINOHAPPPI01SEC' where request_id=87521760;

SQL> update fnd_concurrent_requests set logfile_node_name = 'CINOHAPPPI01SEC' where request_id=87561106;

Using the ADDRESS_LIST rather than updating fnd_concurrent_requests is the recommended method to establish failover access for Concurrent Manager Log and output files.

Q Let me know the flow of application when user login?

when you login :

> Your login gets authenticated against a table named fnd_user for your username and password.

> Oracle internally uses a login named GUEST, prior to invoking validation of actual username. Some people regard this as a security threat, but it isn’t. (By default, its password is oracle.) Your DBA’s can change the “guest” password from its default value after installation.

> Oracle uses a DB User account named applsyspub to which it first connects during validation of LOGIN. This user account has very restricted privileges and has access to below objects (primarily for authentication purposes):-

FND_APPLICATION

FND_APPLICATION_TL

FND_APPLICATION_VL

========================================================

If your client has 2 languages [Oracle Apps being used in both US- and germany-DE]

in that case, if user logs in from US responsibility, then they should see descriptions in English, whereas person logging in from German responsibility must see the description in German language.

Hence, in FND_APPLICATION_TL you will have 2 records[one for each language]

Record 1....LANGUAGE:- DE

Record 1....APPLICATION_NAME:-Oracle Kaufen

Record 1....APPLICATION_ID:-10001 [assume]

Record 2....LANGUAGE:- US

Record 2....APPLICATION_NAME:-Oracle Purchasing

Record 2....APPLICATION_ID:-10001 [assume]

FND_APPLICATION will not store the Application Name. ..Hence it will simply store FND_APPLICATION = 10001

FND_APPLICATION_VL is a view. It will look at applicable language for the current logged in user, and display the relevant description from either "Record 1" or "Record 2" depending upon whether user is logged in from "German Responsibility" or "US responsiblity".

==========================================================

FND_UNSUCCESSFUL_LOGINS

FND_SESSIONS

FND_PRODUCT_INSTALLATIONS

FND_PRODUCT_GROUPS

FND_MESSAGES

FND_LANGUAGES_TL

FND_LANGUAGES_VL

FND_SIGNON

FND_PUB_MESSAGE

FND_WEBFILEPUB

FND_DISCONNECTED

FND_MESSAGE

FND_SECURITY_PKG

FND_LOOKUPS

Q When was Autoconfig last run?

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_xxxxxx.log

$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTier_xxxxxx.log

where xxxxxxx is date and time.

###Check the time stamp for the files.

cinohapppi01sec:/prod/apps/prodappl/admin/prod_cinohapppi01sec/log $ ls -ltr A*

-rw-r--r-- 1 applprod dbaprod 687581 Oct 21 2006 ApplyAppsTier_10210935.log

Q What is log file location for AUTOCONFIG?

APPLCATION :-

$APPL_TOP/admin/<oracle_sid>_<hostname>/log/<date_time_format>/adconfig.log

/<oracle_sid>/apps/<oracle_sid>appl/admin/<oracle_sid>_<hostname>/log/<date_time_format>/adconfig.log

DB :-/<oracle_sid>/oracle/product/10.2.0/<oracle_sid>/appsutil/log/<oracle_sid>_<hostname>/<date_time_format>/adconfig.log

/<oracle_sid>/oracle/product/10.2.0/<oracle_sid>/appsutil/bin/adconfig.sh

Q What is AutoConfig Template Files | INSTANTIATION?

AutoConfig template files are used as the starting point for creating instance-specific configuration files. AutoConfig evaluates the context variables in a template file, determines the actual values required, and creates a configuration file with these values substituted. This process is known as "INSTANTIATION".

There is one template file for each configuration file. Template files are located in the various $PROD_TOP/admin/template directories on the application tier, and in the $ORACLE_HOME/appsutil/template directory on the database tier.

Application Context File:             $APPL_TOP/admin/<CONTEXT_NAME>.xml

DB Context File:                         $ORACLE_HOME/appsutil/<CONTEXT_NAME>.xml

Q What are driver files used for AUTOCONFIG?

The driver files list the names and locations of the files that need to have context variables replaced. They also define the phases into which instantiation is divided, and specify the commands that are to be executed for specific products. When AutoConfig runs, it cycles through the various <PROD>_TOP/admin/driver directories looking for driver files such as adtmpl.drv, fndtmpl.drv, and icxtmpl.drv.

Application Driver Files Location:                   $PROD_TOP/admin/driver

Database Driver Files Location:                      $ORACLE_HOME/appsutil/template

Q What are auto config Configuration Files ?

AutoConfig configuration files, such as httpd.conf, are created as a result of AutoConfig instantiating the corresponding template files. Configuration files contain values corresponding to the settings specified for a particular site. After AutoConfig has been run, numerous configuration files will have been created in various directories.

========================================================================================

                                                               AUTO CONFIG ENGINE

Application Context file  + Driver file +   Template file ------------------------------------------------>>     CONFIGURATION FILES

 =======================================================================================

Q How do we Roll back an AutoConfig session?

Rolling back AutoConfig is achieved by utilizing the backup copies of the configuration files that are created when AutoConfig is run.

Backup copies are created in the same directory where restore.sh is located.

$APPL_TOP/admin/<CONTEXT_NAME>/out/<MMDDhhmm>/restore.sh

$ORACLE_HOME/appsutil/out/<CONTEXT_NAME>/<MMDDhhmm>/restore.sh

Q How to know which files will AUTOCONFIG will change?

Script adchkcfg.sh generates a report [cfgcheck.html] that highlights differences between existing configuration files and the new ones that AutoConfig will generate.

$AD_TOP/bin/adchkcfg.sh

$ORACLE_HOME/appsutil/bin/adchkcfg.sh

Q How to know if system is autoconfig enabled and if it is, then which products are installed?

Script adcfginfo.sh is a utility report to check if an applications system is AutoConfig-enabled (which will always be the case for 11.5.10). In addition, it can optionally list the installed products that are maintained by AutoConfig.

$AD_TOP/bin/adcfginfo.sh

$ORACLE_HOME/appsutil/bin/adcfginfo.sh

Q How do you create soft link in UNIX ?

$ ln -s <AAAAAA> <BBBBBB>

$ ln -s /vendor/lib/oracle_lib.so $ORACLE_HOME/lib/libobk.so

AAAAA = the file name with path where the file actually exists

BBBBB = the file name with path where you want the soft link to be created

Q  What is traceroute command?

traceroute command is used to check for network issues in system if any.

$ traceroute cinohdbd01sec.security.ge.com

traceroute to cinohdbd01sec.security.ge.com (3.112.45.191), 30 hops max, 40 byte packets

 1  cinohdbd01sec (3.112.45.191)  0.432 ms  0.312 ms  0.259 ms

Q How to find sleeping processes in UNIX

$ ps -el | grep S | grep FNDLIBR | more

$ ps -el | grep S | grep FNDLIBR | wc -l

$ ps -efl | grep S | grep applprj2 | wc -l

$ ps -el | more

F S    UID   PID  PPID   C PRI NI     ADDR     SZ    WCHAN TTY         TIME CMD

1 T      0     0     0   0   0 SY        ?      0          ?           1:05 sched

0 S      0     1     0   0  40 20        ?    321        ? ?           0:21 init

1 S      0     2     0   0   0 SY        ?      0        ? ?           0:00 pageout

S denotes sleeping processes.

Q What is top ( topas in AIX) command in UNIX?

Details of TOP Command:

Case-I:         - Memory: 32G phys mem, 16G free mem, 58G swap, 58G free swap

Case-II:        - Memory: 32G phys mem, 536M free mem, 58G swap, 37G free swap

Free Physical Memory should be always in good shape or else server will be very slow, even opening a putty session can take long times. Request will fail as new OS process will not be spawned due to shortage of memory. This has happened in Dev internal server for Case-II. Most FNDLIBR processes were consuming up the CPU usage. I have tried with clearing the FNDLIBR processes, but Physical Memory was not getting released, so wend for server reboot.

4205 applprj4   1  59    0   23M   19M sleep  365:05 36660% FNDLIBR

2181 applprj2   1  60    0   10M 6464K sleep   22:37  7808% FNDLIBR

2184 applprj2   1  60    0   10M 6728K sleep   22:28  7797% FNDLIBR

2185 applprj2   1  60    0   10M 6800K sleep   23:50  7796% FNDLIBR

23793 applprj2   1  59    0   10M 7976K sleep    6:54  7793% FNDLIBR

#### Percentage of CPU usage is highlighted with red colour.

In such scenarios idle % of CPU will drastically come down and also load average will go as high as 80-100%.

Q What is CRONTAB in UNIX?

Time Definition for Crontab:

The five fields separated by blanks represent date and time.

[Minute] [Hour] [Day of Month] [Month of Year] [Day of Week]

0 0 * * 0                          -à At Midnight Everyday Sunday (0)

0 0 * * 1-5                        -à At Midnight Every Weekdays

0 0 1,15 * *                      -à At Midnight 1st and 15th of the Month

0 0 1 * 5                          -à At Midnight of 1st of Month and Every Friday

0 10 * * 0                         -à At Every Sunday 10 AM

01 04 * * 6                       -à At Every Saturday 04:01 AM

15,45 * * * *                     -à At Every 30 Mins

It is a daemon running continuously and after every minute checks into usr/spool/cron/crontabs for submitted jobs.

# cat /usr/spool/cron/crontabs/applprj4

# MWA bounce on Every Saturday 4:01AM MDT

01 04 * * 6 /custom/apps/scripts/unix/monitoring/mwabounce.sh prj4 > /scripts/unix/audit/mwabounce_prj4.log 2>&1

# Get patchsets info every Sunday

00 10 * * 0 /prj4/apps/prj4comn/admin/scripts/custom/_patch_list > /prj4/apps/prj4comn/admin/scripts/custom/audit/_patch_list.out 2>&1

0,15,30,45 * * * * JAVA_HOME=/usr/java /prj4/apps/prj4ora/iAS/ccr/bin/emCCR -silent start

# ipayment setup script to run every 30 minutes

15,45 * * * * /custom/apps/scripts/unix/monitoring/ipayment.sh > /scripts/unix/audit/ipayment-prj4.log 2>&1

# Check if MWA Services availability.

#02 * * * * /custom/apps/scripts/unix/monitoring/mwa_check.sh > /custom/apps/scripts/unix/monitoring/mwa_check.log 2>&1

# Check if ipayment Services availability.

15,45 * * * * /custom/apps/scripts/unix/monitoring/ipayment.sh > /custom/apps/scripts/unix/monitoring/ipayment-prj4.log 2>&1

What is dbc file ? location? how to create and verify?

A DBC (.dbc) file holds information used by application servers to identify and authenticate with an application database. DBC files must be created/perfect on all tiers if you run a multi–tier system.

The files are located in $FND_TOP/secure of your Oracle Applications installation and are named as follows:

$FND_TOP/secure/<db_host>_<db_sid>.dbc

How to verify/test dbc file functionality:-

java oracle.apps.fnd.security.AdminAppServer STATUS DBC=<full_path_of_dbc_file>

 cinohappdi01sec:/export/users/applprj2 $ java oracle.apps.fnd.security.AdminAppServer STATUS DBC=/prj2/apps/prj2appl/fnd/11.5.0/secure/cinohdbd01sec_prj2.dbc

Enter username/password: apps/********

 Database Server

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

DATABASE_ID: cinohdbd01sec_prj2

AUTHENTICATION: OFF

 Application Server

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

APPL_SERVER_STATUS: VALID

APPL_SERVER_ID: 70D61C813794634FE0440003BA38B15B17717434948943883172261749301883

 If the utility returns with the APPL_SERVER_ID and other status information, the DBC file is good.

SELECT FND_WEB_SEC.VALIDATE_LOGIN ('GUEST','ORACLE') FROM DUAL;

 This should return Y if GUEST can login to frontend.

This will validate all the FND_USER account that we create.

========================================================

 During autoconfig adgendbc.sh recreates the dbc file.

========================================================

 Profile Option Name:                    Guest User Password

Value at Site Level:           GUEST/ORACLE

This profile option value should be the username/password of a valid applications user account (not an Oracle user). Any valid account will do, but we recommend that this account not be assigned any actual responsibilities.

========================================================

What is FRD trace log?

FRD Log file will be created under $FORMS60_TRACE_PATH.

alpgaappqi01sec:/qait/output/log $ echo $FORMS60_TRACE_PATH

/qait/apps/qaitcomn/admin/log/qait_alpgaappqi01sec

 Set the system profile value at user level for ICX: Forms Launcher

 ICX: Forms Launcher = http://<host.domain.com>:<port>/dev60cgi/f60cgi?&record=collect&log=/<FORMS60_TRACE_PATH>/<USER_NAME>.FRD.log

 Replace these values looking at the value of ICX: Forms Launcher at Site Level.

http://oracle-qait-int-qa.security.ge.com:80/dev60cgi/f60cgi

 When you set FRD Logging, user should logout and log back in to the application. (“Logging FRD” – User will see this message)

Let the user run the required navigation and FRD log being capture.

Remember to remove the "ICX: Forms Launcher" profile at USER LEVEL after getting the FRD, as it will run every time you login otherwise.

Note ID 335872.1: - Creating an FRD Trace Log on 11.5.10 Instances

What is SCP command ?

SCP is a utility which allows files to be copied between remote hosts without starting an FTP session or logging into the remote systems explicitly. The SCP command uses SSH to transfer data, so it requires a password or pass phrase for authentication. Unlike rcp or FTP, SCP encrypts both the file and any passwords exchanged so that anyone "snooping" on the network can't view them.

SCP is an updated version of an older utility named rcp. It works the same, except that information (including the password used to log in) is encrypted. If you have set up your .shosts file to allow you to SSH between machines without using a password you will be able to SCP files between machines without entering your password.

GENERAL COMMAND: scp source-specification destination-specification

For directory: scp -r <directory_name> <dest_user_name>@<dest_server_name>:<dest_path>

 SCP will overwrite an existing destination file. In addition, if the destination is an existing directory, the copied material will be placed beneath the directory.

 ========================================================

When using wildcards (e.g., * and ?) to copy multiple files from a remote system, be sure to enclose the filenames in quotes. This is because the UNIX shell, not the SCP command, expands unquoted wildcards.

========================================================

scp [-p] [-v] [-r] [[username@]host:]file ... [[username@]host:]file_or_dir

========================================================

scp <filename> <ipaddress>:/backup/oradata>

========================================================

scp <filename, separated by spaces> <dest_user_name>@<dest_server_name>:<dest_path>      

cinohdbp01sec:prod1:/prod/oradata/recovery/arch01/prod1 $ scp prod_558278488_1_306963.arc.Z prod_558278488_1_306964.arc.Z oraprod@alpgadbq01sec:/dg/prod/oradata/recovery/arch01/prod1

Password:

prod_558278488_1_306 100% |******************************************************|   425 MB    06:44

prod_558278488_1_306 100% |******************************************************|   272 MB    04:07

========================================================

How to find invalid object and then compile them?

Invalid Count:-

SQL> SELECT COUNT (*) FROM ALL_OBJECTS WHERE STATUS='INVALID';

SQL> SELECT OWNER, STATUS, COUNT (*) FROM ALL_OBJECTS WHERE STATUS='INVALID' GROUP BY OWNER, STATUS;

Invalid Compilation Scripts:-

SQL> exec utl_recomp.recomp_parallel(80);

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

Compile Invalid Manually:

SET FEEDBACK OFF

SET HEAD OFF

SET PAGESIZE 15000

SELECT 'ALTER PACKAGE '

|| OWNER

|| '.'

|| OBJECT_NAME

|| ' COMPILE '

|| 'BODY'

|| ';'

FROM DBA_OBJECTS

WHERE STATUS ='INVALID'

AND OBJECT_TYPE='PACKAGE BODY';

SQL> ALTER PACKAGE CZ_IMP_SINGLE_DEBUG COMPILE;

SQL> ALTER PACKAGE CZ_IMP_SINGLE_DEBUG COMPILE BODY;

What is the role of applsys and applsyspub

All the technical products' database objects are consolidated into a single schema called Applsys

Applsyspub is used for authentication by having read only views ( pub here means for public)

How can we delete a Concurrent Program which is already registered?

Through the Application Front end its not possible to delete CP. But from backend we can delete it.

TO DELETE CONCURRENT PROGRAM

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

begin

fnd_program.delete_program('SHORTNAME','APPLICATION');

fnd_program.delete_executable('SHORTNAME','APPLICATION');

end;

What is a concurrent program and a concurrent request set ?

A concurrent program is an executable file that runs simultaneously with other concurrent programs and with online operations, fully utilizing your hardware capacity. Typically, a concurrent program is a long-running, data-intensive task, such as posting a journal or generating a report.

A concurrent request is a request that you submit to run a concurrent program as a concurrent process.

A request set is a collection of reports and/or programs that you group together. You can submit the reports and/or programs in a request set all at once using a single transaction.

Why does Help->Tools->Examine ask for a password?

The profile option Utilities:Diagnostics is set to NO

This profile option controls whether users can use the Examine utility.

The password should be the APPS password.

How do I generate a message file (usaeng.msb)?

Use: FNDMDCMF applsys/pwd 0 Y APP usaeng

where: applsys/pwd is the APPLSYS user and password

and APP is the short name of the application (like PO or INV

How do I find the name of a form?

GUI: Use Help->About Oracle Applications

Scroll down to find the form name

Character: Use Help->Version

How do I find out what the currently installed release of Applications is?

SELECT release_name FROM fnd_product_groups

Where do concurrent request log files and output files go?

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.

Q How many Parameters can pass to Concurrent Program

100

Who is the owner of FND_TABLES and Why we shouldn't create table in APPS Schema?

APPLSYS is the owner of FND tables.

APPS schema contains the package code for all the E-Biz products.

and needs to be kept secure.

So we can't create tables in APPS schema if we want to make use of them in our custom code.

What can we find TEMPLATE.FMB file ?

Template.fmb file can be found in AU_TOP resource directory. This file contains all the Common characterstics all the forms. And also Contains Diffrent libraries. like CUSTOM.pll APPCORE APPCOREE2 FNDSQF JE JL JA VERT GLOBE etc.. And Template.fmb cotains Diffrent propery classes for all the objects. This Template.fmb can be used for developing the new form.

We can number of libraries in that which are useful while developing a form. Nearly 18 libraries we can find.In that Custom.pll is used when we r going to customize a form.

What is FlexField?

Oracle Application uses Flexfield to capture information about Your organization. Flexfield have flexible structure for storing key information. Like Company, Cost Center, and Account. They also give u highly adaptable Structure for storing customized information in oracle Applications.

Q. Suppose my concurrent manager status is showing Inactive ? what is the reason ?

This could be because of multiple reasons, to name few

a) Processes value for Concurrent Manager is 0  (System Administrator > Concurrent > Manager > Define > search for Manager > Click on the Work Shifts button and check value of Processes)

b) Concurrent Manager was shut down by some one (Check using System Administrator > Concurrent > Manager > Administer)

c) There is no workshift defined against Concurrent Manager (or workshift removed from Manager)

Q. What is difference between shared APPL_TOP and shared Application Tier ?

- In shared APPL_TOP, only APPL_TOP & COMMON_TOP are shared across multiple middle tier where as in Shared Application Tier ORACLE_HOME (Middle Tier 806 & iAS directory) are also shared except configuration files (iAS).

Q. How would you know about which servers are running on a particular node ?

i) Check value of variable, isConc, isWeb, isForms, isAdmin in CONTEXT FILE

ii) Control scripts - only the services which are running from particular node will have the corresponding control scripts installed on that node (This answer is partially right, depending on AD & FND version).

iii) Check FND_NODES table

Q. Adsplice - I have a 3 node installation: 1. Forms 2. Web 3. CM/Admin/Reports and DB which node I will run the adsplice on, to install a new product ?

– You must run adsplice on all nodes (APPL_TOPs) so that application utilities recognize new product.

Q. There are four Oracle Databases running on a mahine. How would you check the location of their Oracle Homes ?

- Check entry in oratab file

Q. How would you check if the tablespace is in backup mode ?

- Check in v$backup

SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;

Q. When Database is up, listener is up. listener.ora and tnsnames.ora both are configured properly, still client is not being able to make a connection to the database. What may be the possible issues ?

– Firewall blocking db port between client and server

– Authentication restricted in sqlnet.ora (Client allowed from only selected machines)

Q. What is “Custom TOP” in apps ?

- Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP

Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?

– System picks up dbc file based on profile option value “Applications Database ID“

Q. what are mandatory users in oracle apps?

Ans: applsys,applsyspub,apps

Q What are adadmin utilities? and Its location?

$AD_TOP/bin

Ans: 1.adadmin

2.adpatch

3.adsplice

4.adident

5.adrelink

6.adlicmgr

Q How do you compile a schema?

Ans: using utlrp.sql (location is $ORACLE_HOME/rdbms/admin/) or going adadmin, compile schema option

Q How do you validate apps schema?

Ans: To validate synonyms, missing sysnonyms and all grant. You can do it in adadmin. after validating it iwll produce

a report in the location $APPL_TOP\admin\sid\out\*.out

Q  what is the utility to clean the concurrent manager?

Ans: @cmclean.sql ( You have download from metalink)

Q You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.

Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”

Internally Oracle Forms issues a statement similar to below:-

alter session set events='10046 trace name context forever, level 12' ;

This will enable the trace with Bind Variable values being shown in the trace file.The screen in Oracle Apps will also provide the name of the trace file which is located in directly identified by

select value from v$parameter where name like '%us%r%dump%'

Doing a tkprof with explain plan option, reviewing plans  and stats in trace file can help identify the slow performing SQL.

Q What is session time out parameter & where all you define these values ?

Ans: In order to answer first you have to understand what kind of seesions are in Apps 11i and what is Idle timeout ?

In Apps there are two broad categories of session

- Self Service Application Session ( Server by Web Server iAS Apache & Jserv, like iRecruitment, iProcurement)

-Forms session ( served by your form session, like system Administrator)

Q What is difference between GUEST_USER_PWD (GUEST/ORACLE) & GWYUID ?

Ans: GUEST_USER_PWD(Guest/Oracle) is used by JDBC Thin Client where as GWYUID is used by Thick Clients like via Forms Connections.

Q How to check number of forms users at any time ?

Ans: Forms Connections initiate f60webmx connections so you can use

ps -ef | grep f60webmx | wc -l

Q What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where

0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form.

'Y' indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

Q While applying Apps patch using adpatch, if you want to hide the apps password, how will that be possible ?

Ans: using flags=hidepw

Q What is difference between Socket & Servlet Mode in Apps Forms ?

Ans: When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh). When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache . There will be additional JVM for Forms Request in that case and you won't start form via adfrmctl.sh.

Q   How to find OUI version ?

Ans: OUI

***

OUI stands for Oracle Universal Installer. In order to find Installer version you have to execute ./runInstaller -help ( From OUI location)

You will get output like

Oracle Universal Installer, Version 10.1.0.4.0 Production Copyright (C) 1999, 2005, Oracle. All rights reserved.

That means OUI version in above case is 10.1.0.4

OUI location is $ORACLE_HOME/oui/bin 

Q How to find Database version ?

DB 

**

select * from v$version;

Q How to find Oracle Workflow Cartridge Release Version ?

Oracle Workflow

***************

Log in to the database as the owf_mgr user and issue

select wf_core.translate('WF_VERSION') from dual;

Q How to find opatch Version ?

Opatch

*******

$ORACLE_HOME/OPatch/opatch version

Q How to find Version of Apps 11i ?

Apps

****

select RELEASE_NAME from fnd_product_groups; 

Q How to find  Discoverer Version installed with Apps ?

Discoverer

**********

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute

strings dis4ws | grep -i 'discoverer version'

Q How to find Workflow Version embedded in Apps 11i ?

Workflow embedded in 11i

************************

Run following SQL from apps user ;

SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like

TEXT

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

2.6.0

Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps. 

Q How to find version of JDK Installed on Apps ?

JDK in Apps

***********

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using

Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable

JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command

./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)

Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)

Which means you are using JDK 1.4.2 in Oracle Applications 11i.

Q  If by mistake you/someone deleted FNDLIBR can this executable be restored if Yes, How & if no, what will you do ?

Ans: Yes, you can restore FNDLIBR executables

Run adadmin on concurrent manager node

select option 2. Maintain Applications Files menu

then select 1. Relink Applications programs

when prompts for

Enter list of products to link ('all' for all products) [all]

select FND

when prompt for

Generate specific executables for each selected product [No] ? YES

select YES

& from list of executables select FNDLIBR

This will create new FNDLIBR executables.

Q What is .pls files which you see with apps ?

Ans: pls file stands for plsql files. In apps patch these files contain code to create package spec or package body or both.

Q What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

Q What are .odf file in apps patch ?

Ans: odf stands for Object Description Files used to create tables & other database objects.

Q What is location for Forms server log file

Ans: Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt

Forms Run Time Diagnostics default location is $ORACLE_HOME/forms60/log/$CONTEXT_NAME 

Q How to convert pll to pld file or pld file to pll ?

Ans: Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/ module_access=file output_file=MSCOSCW1.pld script=yes

Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/ module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

Q Is APPS_MRC Schema exists for MRC in 11.5.10 and higher ?

Ans: No , apps_mrc schema is dropped with 11.5.10 Upgrade & 11.5.10 new Install. This is replaced by more Integrated Architecture.

 Q If APPS_MRC schema is not used in 11.5.10 and higher then How MRC is working ?

Ans: For products like Payable, Recievables which uses MRC and if MRC is enabled then each transaction table in base schema related to currency now has an assoicated MRC Subtables.

Q When you apply C driver patch does it require database to be Up & Why ?

Ans: Yes , database & db listener should be Up when you apply any driver patch in apps. even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

Q Can C driver in apps patch create Invalid Object in database ?

Ans: No , C driver only copies files in File System. Database Object might be invalidated during D driver when these objects are created/dropped/modified.

Q What is difference between AD_BUGS & AD_APPLID_PATCHES ?

Ans: AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

Q What is Single Sign On ? ( If you are using portal 3.0.9 or 10G )?

Ans: As name says Single-Sign On Server is set of services (Software) which enables login to Application once which will allow you to login to Ppartner Applications with no need to login again. Lets assume I have configured single SSO Server for Portal , E-Business Suite, Collaboration Suite plus some other other applications, Now if I login to any one of them & after that if I wish to login to other applications I should be able to login without supplying passwords again.

Q  What is content of dbc file & why its important ?

Ans: 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 is

GUEST_USER_PWD

APPS_JDBC_URL

DB_HOST

Q What is a "Shared APPL_TOP"?

• 1. Answer:

  2. A traditional multi-node installation requires the Applications file system on each node in the system.

  3. In a Shared APPL_TOP installation, the APPL_TOP and the COMMON_TOP file systems are installed on a shared disk resource mounted to each node in the system. These nodes can be used to provide standard application tier services, such as Forms, Web, and Concurrent processing. Any changes made in the shared APPL_TOP file system are immediately visible on all nodes.

  4. Note that each node continues to have a separate Applications techstack installation (see also question 2).

Q What is a "shared application tier file system"?

Answer:

In a shared application tier file system installation, the APPL_TOP, the COMMON_TOP, and the Applications technology stack (ORACLE_HOMEs) are installed on a shared disk resource mounted to each node in the system. These nodes can be used to provide standard application tier services, such as Forms, Web, and Concurrent processing. Any changes made in the shared application tier file system are immediately visible on all nodes.

Q What is a FlexField ? What are Descriptive and Key Flexfields ?

A flexfield is a placeholder set of fields that can be configured by customers for use by their organizations. Once configured, the customer-defined fields (label/widget pairs) may appear in either form or tabular layouts. There are two main types of flexfields: Descriptive flexfields, which are configured as a set of fields that are indistinguishable from core (default) application fields, and key flexfields, which consist of multiple segments for entry of codes, such as a product serial numbers or bank account numbers.

Flex field is collectoin of fields or segments. There are 2 types of Flex feilds.

1) Discriptive Flex Feld:- We can extend the functionality of the form without changing the code.

2) Key flex field:- It is also known as Intelligent code which stores valid code combinations.

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.

Q  WHAT IS CONCURRENT PROGRAM, IT'S VARIOUS STATES OF RUNNING AND IMPORTANT TABLES?

The Concurrent Requests window displays a phase and status summary for each concurrent request listed

A concurrent request has a life cycle consisting of the following phases: pending, running, completed, and inactive.

During each phase, a concurrent request has a specific condition or status. lists each phase/status combination and describes its meaning in relation to a request.

If a request is pending, you can determine when it will start by using the Concurrent Managers For the Request window from the Requests window to review the request’s position in the queues of current managers that can run that request.

The database tables that are affected by running the Purge Concurrent Request and/or Manager Data program are:

FND_CONCURRENT_REQUESTS

This table contains a complete history of all concurrent requests.

FND_RUN_REQUESTS

When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS

This table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL

This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES

This table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST

This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

This table contains the concurrent program performance statistics generated by the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to compute these statistics.

Q WHAT IS FNDCPASS ?

Changing passwords periodically helps ensure database security. Oracle Applications provides a command line utility, FNDCPASS, to set Oracle Applications schema passwords. In addition to changing the schema password in the database, this utility changes the password registered in Oracle Applications tables (FND Tables). This utility can also change Applications End User passwords.

FNDCPASS changes

• • Oracle Applications Database System Users (APPS, APPLSYS)

  • Oracle Applications Product Schema Passwords (GL, AR, AP, etc,)

  • Oracle Applications End User Account Passwords (SYSADMIN, OPERATIONS etc)

Note: the utility, FNDCPASS, cannot be used for changing the database SYSTEM and SYS users. Only users that are registered in FND meta data tables need to be changed using FNDCPASS. Normally, the APPS database user password and APPLSYS password need to be the same. When changing the APPLSYS password using FNDCPASS, the APPS password is also changed.

Syntax of FNDCPASS command:

FNDCPASS logon 0 Y system/password mode username new_password

Where logon is username/password[@connect]

System/password is password of the system account of that database

Mode is SYSTEM/USER/ORACLE

Username is the username where you want to change its password

new_password is the new password in unencrypted format

Example:

$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

$ FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

$ FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

Note: The FNDCPASS has a new mode, “ALLORACLE”, in which all Oracle Application schema passwords can be changed in one call. Apply the patch (Patch No# 4745998) to have this option, if not available currently with your Apps.

Syntax:

FNDCPASS 0 Y ALLORACLE

Example:

$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME

To change APPS/APPLSYS password, we need to give mode as SYSTEM

To change product schema passwords, i.e., GL, AP, AR, etc., we need to give mode as ORACLE

To change end user passwords, i.e., SYSADMIN, OPERATIONS etc., we need give mode as USER

Note: Till 11.5.9 there is bug in FNDCPASS, which allows FNDCPASS to change APPS&APPLSYS passwords. Doing so will corrupt the data in FND meta data tables and cause to the application unusable. Because of that it is recommend taking backup of the tables FND_USER and FND_ORACLE_USERID before changing the passwords.

After changing the APPS/APPLSYS or APPLSYSPUB user, following extra manual steps needs to be done.

If you changed the APPS (and APPLSYS) password, update the password in these files:

• • iAS_TOP/Apache/modplsql/cfg/wdbsvr.app

• ORACLE_HOME/reports60/server/CGIcmd.dat

If you changed the APPLSYSPUB password, update the password in these files:

• FND_TOP/resource/appsweb.cfg

• OA_HTML/bin/appsweb.cfg

• FND_TOP/secure/HOSTNAME_DBNAME.dbc

Note: I would you suggest you to first try changing the passwords using FNDCPASS on your test Apps Instances, once you are done with this without any errors or issues then you can move this to production, and also request you to search in metalink for more information about FNDCPASS utility and it’s usage.

Note: With FNDCPASS you cannot decrypt the password, but can change the password only!

Q What is FNDLOAD and how to use it?

when we are working in oracle application development/implementation project? The equally important AOL data Migration takes place necessary to synchronize the data across databases instance during installation and upgarde.

Using FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.The Conversion between database format and text file format is specified by a configuration file.

What can be Done?

·It can be done following list

· Concurrent Programs, Executables

· Request Groups, Request Sets

· Profile Options

· Key and Descriptive Flexfields

· Menus and Responsibilities

· Forms and Form Functions

· Attachments

· Messages

· Value Sets and Values

· Lookup Types

· User Responsibilities

· Printer Definitions

· FND Dictionary

· Help Configuration

· Document Sequences

· Concurrent Manager Schedules

Advantages when using FNDLOAD

1. Because downloaded data is stored in a text file, version administration is possible

2. No learning curve. this is relief for developer/dbas

3. Fully supported and recommended by Oracle

4. Capture the migrations in a file and use it during installations(log file).

5. Pin-point when something happened and where (database) easily

6. AOL data migration process is now simplified!

Disadvantages

1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero

2. No validation against migrating database/instance sensitive data

The Syntax

To use FNDLOAD, the following syntax is needed.

FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]

· The mode is either DOWNLOAD or UPLOAD.

· The configfile is the file that Fndload needs to download on upload data.

· T he data file is the output file, in which the downloaded data is written

· The entity is the entity you want to download,

·

Example of download:

FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name

Example of Upload

FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt – CUSTOM_MODE=FORCE undocumented parameter

Where is Config File Located

· Configuration files with extension .lct

o On Unix – all the configuration files are in $FND_TOP/patch/115/import directory

o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory

·Data files with extension .ldt

· The configfiles (.lct) are delivered and maintained by Oracle

· It has entity definitions, parent-child relationships and user input parameters identified by :NAME

oDownloading a parent automatically downloads all children – (Example) Concurrent Program download

Sample Script Code for these Objects :

1 – Printer Styles

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name

Q WHAT IS TKPROF AND IT'S USE?

Oracle gives you this program called TKProf that reads your trace file and spits out somewhat meaningful output. My favorite option to tkprof is sort=prsela,fchela,exeela. This sorts the statements from longest running to shortest running. I prefer this format because I can concentrate on the top two or three statements for the most impact.

By tracing, I mean capturing all the SQL in a user’s session, or a SQL trace. As you can tell from the Oracle Performance Tuning Guide and Reference, there are many ways to enable tracing. Below are two methods I use as a DBA in my day-to-day routine.

Here is the script I often used to find the sql script using oracle trace utility for oracle applications forms, reports. Change the user id, password and database name to suit your environment.

Download TKprof shell script

#!/bin/sh

#***********************************************

# Script Name: tkp

#

# Developed by Jay

#***********************************************

# This script get the trace file name as argument and creates a tkprof file required for oracle analysis

#

# Usage : tkp "oracle_trace_file"

#

# Assumption: The oracle home environment set properly

#             The trace file suffix is .trc and is not provided as parameter argument

#             tkprof utility – unix – installed and oracle_home and path environment variables set

#             user id = apps, and password = apps

#***********************************************

if [ $@ > 0 ]

then

tkprof $1.trc $1.tkprof sort=exeela,fchela,prsela explain=apps/apps@PRODUCTION

else

echo ‘Usage : tkp "oracle_trace_file"’

fi

Sometimes, a session is already underway and you need to start a trace midway through it’s execution. Here, you need to be a DBA and enable the 10046 event in the user’s session. This is a little more tricky since you have to know the sid and serial# from v$session in order to enable the tracing. For example,

SQL> SELECT sid, serial#, username

2  FROM v$session

3  WHERE username = ‘JEFFH’;

     SID    SERIAL# USERNAME

———- ———- ——————————

      25          5 JEFFH

SQL> exec sys.dbms_system.set_ev(25, 5, 10046,8,”);

PL/SQL procedure successfully completed.

Statements get written to the trace file when they are first encountered after the trace is started. The statement that is executing may not be in the final trace file.

Trace with TKProf

PARAMETERS

You need 2 database parameters to trace sessions: TIMED_STATISTICS and USER_DUMP_DEST.

TIMED_STATISTICS should be TRUE to use statistics.

Also possible so set this in a session:

SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;

USER_DUMP_DEST points to the directory on the server where the tracefiles are being written.

Enable trace

You can enable tracing in the following ways:

SQL*Plus:

SQL> alter session set sql_trace true;

PL/SQL:

dbms_session.set_sql_trace(TRUE);

DBA

SQL> execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);

with: sid en serial# from the query:

Select username, sid, serial#, machine from v$session;

Oracle forms:

start forms with f45run32.exe statistics=yes

or make a PRE-FORM trigger with the statement:

forms_ddl(’alter session set sql_trace true’);

Oracle reports:

BEFORE-REPORT trigger with statement:

srw.do_sql(’alter session set sql_trace true’);

PRO*C

EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;

Use TKPROF

To make a tracefile readable, you need TKProf. Use the following command on the server:

TKPROF tracefile exportfile [explain=username/password] [table= …] [print= ] [insert= ] [sys= ] [record=..] [sort= ]

The statements between brackets are optional. Their meaning is:

explain=username/password: show an executionplan.

table= schema.tabelnaam : use this table for explain plan

print=integer restrict the number of shown SQL-statements.

insert=bestandsnaam Show SQL-statements and data within SQL statements

sys = NO Don’t show statements that are executed under the SYS-schema. Most of the times these are recursive SQL-statements that are less interesting.

Aggregate=NO Don’t aggregate SQL-statments that are executed more than once.

sort= Sort the SQL-statements. The option is made up of 2 parts:

part1:

Prs Sorteer op parse-values

Exe Sorteer op executie-values

fch Sorteer op fetch-values

Part 2:

Cnt Sort on number of calls

Cpu Sort on CPU-usage

Ela Sort on elapsed time

Dsk Sort on disk-reads

Qry Sort on consistent reads

Cu Sort on current reads

Mis Sort on library cache misses

row Sort on number of processed rows

I think Oracle uses tkprof as an acronym for Tom Kyte, Professor!

Q Give details on oracle applications SID.xml file

Oracle Applications - SID.xml File is the context file.Oracle applications use this context file and it contains the following information about the APPL_TOP

1) Name of application system

2) Information about various servers

3) Technology stack version

4) Guest user name password

5) DB host and port details

6) port number of all the components in the middle tier

7) Product top directories of all the installed products

In general xml file is organized based on xml namespace structure which is easy to parse. So .xml formats are used as context files.

Oracle applications DBA - Application context file is a global configuration file for storing all the configuration information of all components in the E-business suite.

Location - $APPL_TOP/admin directory

Application context file is a XML file named $CONTEXT_NAME.xml

Q Give brief details on Oracle Listener Architecture

We're going to discuss the fine-grained level of listener architecture. We know that listener is a process that runs on the Oracle database server machine.It hands off client requests to the server.The database server receives an initial connection from a client application through the listener. The listener is an application positioned on top of the Oracle Net foundation layer.The listener brokers client requests, handing off the requests to the Oracle database server. Every time a client requests a network session with a database server, a listener receives the initial request.

Each listener is configured with one or more protocol addresses that specify its listening endpoints. Clients configured with a protocol address can send connection requests to the listener.Once a client request has reached the listener, the listener selects an appropriate service handler to service the client's request and forwards the client's request to it.

The listener determines if a database service and its service handlers are available through service registration. During service registration, the PMON process—an instance background process—provides the listener with information about the following:

1) Names of the database services provided by the database

2) Name of the instance associated with the services and its current and maximum load

3) Service handlers (dispatchers and dedicated servers) available for the instance,including their type, protocol addresses, and current and maximum load This information enables the listener to direct a client's request appropriately.

If the listener is not running when an instance starts, PMON is not able to register the service information. PMON attempts to connect to the listener periodically, however, it may take up to 60 seconds before PMON registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement:

SQL> ALTER SYSTEM REGISTER;

This is especially useful in high-availability configurations.If a listener receives an incoming request before the respective instance has been registered, the listener rejects the request.If an instance is in restricted mode, then PMON instructs the listener to block all connections to the instance. Clients attempting to connect receive one of the following errors:

1) ORA-12526: TNS:listener: all appropriate instances are in restricted mode

2) ORA-12527: TNS:listener: all appropriate instances are in restricted mode or blocking new connections

3) ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Following steps happens when a browser connection from a HTTP client and a client from terminal(TTC) try to make connection with a database server.Their requests are brokeraged and handled by listener:

1. The database registers information about the services, instances, and service handlers with the listener.

2. The client makes an initial connection with the listener.

3. The listener parses the client request and forwards it to the service handler for the database service requested

Q. What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?

I'll again suggest you don't just remember answer & try to solve mystery behind this file. First where this file exists ? You can find this file under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &

Sample entry in wdbsvr.app

[DAD_VISION11I]

connect_string = VISION11I

password = apps

username = APPS

default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I , it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).

  So story about this file doesn't stop here , this url which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps

password .

  Q. What are various components in Application/Middle Tier.

In Application Tier various components are Web Server, Forms Server , Reports Server, Concurrent Manager, Admin Server & Discoverer Server.

Q. Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

 Q. Where would i find .rf9 file, and what execatly it dose ?  

These files are used during restart of patch in case of patch failure because of some reason.  

Q. LOCATION of appsweb_$CONTEXT.cfg stored & why its used ?

This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.

This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client. 

Q. What is multi node system ?

Multi Node System in Oracle

Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System. 

Q. Can you clone from multi node system to single node system & vice versa ?

Yes , this is now supported via Rapid Clone, Check if your system has all prereq. patches for Rapid Clone and you are on latest rapid clone patch.

Q. Does rapid clone takes care of Updating Global oraInventory or you have to register manually in Global OraInventory after clone ?

Rapid Clone will automatically Update Global oraInventory during configuration phase. You don't have to do any thing manually for Global oraInventory.

Q. What is .dbc file , where its stored , whats use of .dbc file ?

dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

Q. Whats things you do to reduce patch timing ? 

You can take advantage of following -

Merging patches via admrgpch

Use various adpatch options like nocompiledb or nocompilejsp

Use defaults file

Staged APPL_TOP during upgrades

Increase batch size (Might result into negative )

Q. Can you apply patch without putting Applications 11i in Maintenance mode ? 

Yes, by using  options=hotpatch with adpatch.

Q. What are various options available with adpatch ?

Various options available with adpatch depending on your AD version are

autoconfig, check_exclusive, checkfile, compiledb, compilejsp, copyportion, databaseprtion, generateportion, hotpatch, integrity, maintainmrc, parallel, prereq, validate

Q. adident utility is used for what ?

adident utility in oracle apps is used to find version of any file . AD Identification.

for ex. "adident Header <filename>

Q. What is adsplice utility ?

adsplice in oracle apps is utility to add a new product.

Q. How can you licence a product after installation ?

You can use ad utility adlicmgr to licence product in Oracle Apps.

Q. What is MRC ? What you do as Apps DBA for MRC ?

MRC also called as Multiple Reporting Currency in oracle Apps. Default you have currency in US Dollars but if your organization operating books are in other currency then you as apps dba need to enable MRC in Apps. How to enable MRC coming soon...

Q. What is access_log in apache , what entries are recored in access_log ? Where is default location of thsi file ?

access_log in Oracle Application Server records all users accessing oracle applications 11i. This file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs. Entries in this file is defined by directive LogFormat in httpd.conf Typical entry in access_log is

198.0.0.1 - - [10/Sep/2006:18:37:17 +0100] "POST /OA_HTML/OA.jsp?.... HTTP/1.1" 200 28035

where 200 is HTTP status code & last digits 28035 is bytes dowloaded as this page(Size of page)

Q. Where is Jserv configuration files stored ?

Jserv configuration files are stored in $IAS_ORACLE_HOME/Apache/Jserv/etc 

Q. Where is applications start/stop scripts stored ?

applications start/stop scripts are in directory $COMMON_TOP/admin/scripts/$CONTEXT_NAME

Q. What are main configuration files in Web Server (Apache) ?

Main configuration files in Oracle Apps Web Server are

httpd.conf, apps.conf, oracle_apache.conf, httpd_pls.conf

jserv.conf, ssp_init.txt, jserv.properties, zone.properties

plsql.conf, wdbsvr.app, plsql.conf

Q. How to check if Apps 11i System is Autoconfig enabled ?

Under $AD_TOP/bin check for file adcfginfo.sh & if this exists use

adcfginfo.sh contextfile=<CONTEXT> show=enabled

If this file is not there , look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like 

# AutoConfig automatically generates this file. It will be read and .......

Q. How to check if Oracle Apps 11i System is Rapid Clone enabled ?

For syetem to be Rapid Clone enabled , it should be Autoconfig enabled (Check above How to confirm if Apps 11i is Autoconfig enabled). You should have Rapid Clone Patches applied , Rapid Clone is part of Rapid Install Product whose Family Pack Name is ADX. By default all Apps 11i Instances 11.5.9 and above are Autoconfig & Rapid Clone enabled.

 Q. Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?

APPS<CONTEXT>.env is main environment file which inturn calls other environment files like <CONTEXT>.env under $APPL_TOP, <CONTEXT>.env under 806 ORACLE_HOME and custom<CONTEXT>.env for any Customized environment files.

Q. What is access_log in Apache ?

access_log file keeps record of users accessing Oracle Apps 11i Webserver.

Typical entry in access_log is like

198.0.0.1 - - [25/Aug/2006 :03:15:13 +0100] "GET /OA_JAVA /oracle /forms /registry/Registry.dat HTTP/1.1" 200 4117

Which means client with IP 198.0.0.1 requested for file mentioned above on 25 Aug 2006 at 03:15 AM , 200 is status code returned by Apache which means page returned successfully (Status Code 302 means page redirected , 404 page not found, 500+ Internal Server error)

last digit 4117 in above entry of access_log means file size which is 4117 bytes. This file is quite useful in monitoring your Web Server.

Please note above format might defer on your system as this is dependent log_format in Apache configuration file ( httpd.conf) .access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of this file is defined in httpd.conf by patameter CustomLog or TransferLog

Q. What is your Oracle Apps 11i Webserver Version and how to find it ?

From 11.5.8 to 11.5.10 Webserver version is iAS 1.0.2.2.2, In order to find version under $IAS_ORACLE_HOME/Apache/Apache/bin execute ./httpd -version

./httpd -version

Server version: Oracle HTTP Server

Powered by Apache/1.3.19

Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)

Q. What is plssql/database cache ?

In order to improve performance mod_pls (Apache component) caches some database content to file. This database/plssql cache is usually of type session & plsql cache

session cache is used to store session information.

plsql cache is used to store plsql cache i.e. used by mod_pls

Q. Where is database/plssql cache stored ?

plssql & session cache are stored under $IAS_ORACLE_HOME/ Apache/modplsql/cache directory.

 Q. What is RRA/FNDFS ?

Report Review Agent(RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing output files & log files. As most of apps dba's are not clear about Report Server & RRA, I'll discuss one on my blog and update link here .

 Q. What is PCP is Oracle Applications 11i ? 

PCP is acronym for Parallel Concurrurent processing. Usually you have one Concurrent Manager executing your requests but if you can configure Concurrent Manager running on two machines (Yes you need to do some additional steps in order to configure Parallel Concurrent Processing) . So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

Q Concurrent Processing Nodes or in what scenarios PCP is Used ?

Well If you are running GL Month end reports or taxation reports annually these reposrts might take couple of days. Some of these requests are very resource intensive so you can have one node running long running , resource intensive requests while other processing your day to day short running requets.

another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node , you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

Q. Output & Logfiles for requests executed on source Instance not working on cloned Instance

Here is exact problem description - You cloned an Oracle Apps Instance from PRODBOX to another box with Instance name say CLONEBOX on 1st of August. You can any CM logs/output files after 1st of August only becuase these all are generated on CLONEBOX itself, But unable to view the logs/output files which are prior to 1st August. What will you do & where to check ?

Log , Output file path & location is stored in table FND_CONCURRENT_REQUESTS. Check

select logfile_name, logfile_node_name, outfile_name, outfile_node_name from fnd_concurrent_requests where request_id=&requestid ;

where requestid is id of request for which you are not able to see log or out files. You should see output like

/u01/PRODBOX/log/l123456.req, host1,/u01/PRODBOX/out/o123456.out, host1

Update it according to your cloned Instance Variables.

Q. How to confirm if Report Server is Up & Running ?

Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like

ps -ef | grep rwmts60

You should get output like

applmgr ....... rwmts60 name=REP60_VISION

where VISION is your Instance name.

Else you can submit a request like "Active Users" with display set to PDF, check output & log file to see if report server can display PDF files.

Q. What is difference between ICM, Standard Managers & CRM in Concurrent Manager ?

Q. How to start Apps listener ?

In Oracle 11i, you have script adalnctl.sh which will start your apps listener. You can also start it by command

lsnrctl start APPS_$SID (Replace sid by your Instance SID Name)

Q. How to confirm if Apps Listener is Up & Running ? 

execute below command 

lsnrctl status APPS_$SID (replcae SID with your Instance Name)

so If your SID is VISION then use lsnrctl status APPS_VISION out put should be like

Services Summary...

FNDFS has 1 service handler(s)

FNDSM has 1 service handler(s)

The command completed successfully

Q. What is Web Listener ?

Web Listener is Web Server listener which is listening for web Services(HTTP) request. This listener is started by adapcctl.sh & defined by directive (Listen, Port) in httpd.conf for Web Server. When you initially type request like http://becomeappsdba.blogspot.com:80 to access application here port number 80 is Web Listener port.

Q. How will you find Invalid Objects in database ?

using query

SQLPLUS> select count(*) from dba_objects where status like 'INVALID';

Q. How to compile Invalid Objects in database ?

You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects.

Q. How to compile JSP in Oracle Apps ?

You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is

perl ojspCompile.pl --compile --quiet

Q. What is difference between adpatch & opatch ?

adpatch is utility to apply oracle apps Patches whereas

opatch is utility to apply database patches

Q. Where will you find forms configuration details apart from xml file ?

Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg (defined by environment variable FORMS60_WEB_CONFIG_FILE) for forms client connection used each time a user initiates forms connection.

Q. What is forms server executable Name ?

f60srvm

Q. What are different modes of forms in which you can start Forms Server and which one is default ?

You can start forms server in SOCKET or SERVLET by defualt Forms are configured to start in socket mode.

Q. How you will start Discoverer in Oracle Apps 11i ?

In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

Q. How many ORACLE HOME are Oracle Apps and whats significance of each ?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

ORACLE_HOME 1 : On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer. ORACLE_HOME should point to this ORACLE_HOME which applying Apps Patch.

ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

ORACLE_HOME 3: On Database Tier used by Database Software usually 8i,9i or 10g database.

Q. Where is HTML Cache stored in Oracle Apps Server ?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

Q. Where is plssql cache stored in Oracle Apps ?

Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

Q. How to retrieve SYSADMIN password ?

If forgot password link is enabled and sysadmin account is configured with mail id user forget password link else you can reset sysadmin password via FNDCPASS.

Q. If you have done two node Installation, First machine : Database and concurrent processing server. 2nd machine: form,web Which machine have admin server/node?

Admin Server will be on First machine with concurrent processing server. More on Admin Server coming soon.. 

Q. Whats is TWO_TASK in Oracle Database ?

TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don't want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

Q. What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

Q. Where GWYUID defined & what is its used in Oracle Applications ?

GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by thiCk clients.

Q. What is difference between GUEST_USER_PWD (GUEST/ORACLE) & GWYUID ?

GUEST_USER_PWD(Guest/Oracle) is used by JDBC Thin Client where as GWYUID is used by Thick Clients like via Forms Connections.

Q. How to check number of forms users at any time ?

Forms Connections initiate f60webmx connections so you can use

ps -ef | grep f60webmx | wc -l

Q. What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where

0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form.

'Y' indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

Q. In a Multi Node Installation, How will you find which node is running what SErvices ?

You can query for table FND_NODES and check for column , SUPPORT_CP ( for Concurrent Manager) SUPPORT_FORMS ( for forms server) , SUPPPORT_WEB (Web Server), SUPPORT_ADMIN( Admin Server), and SUPPORT_DB for database tier.

You can also check same from CONTEXT File (xml file under APPL_TOP/admin)

Q. If your system has more than one Jinitiator, how will the system know, which one to pick. ?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used .

Q. While applying Apps patch using adpatch, if you want to hide the apps password, how will that be possible ?

Use adpatch flags=hidepw while applying patches in apps to hide apps or system password being displayed on Users Screen.

Q. What is importance of IMAP Server in Java Notification Mailer ?

IMAP stands for Internet Message Access Protocol and Java Notification mailer require IMAP server for Inbound Processing of Notification Mails.

Q. What is difference between Socket & Servlet Mode in Apps Forms ?

When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh). When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache . There will be additional JVM for Forms Request in that case and you won't start form via adfrmctl.sh.

  Q. What is make program in Unix ?

make is utility in Unix/Linux to maintain , update & generate an file mainly executable.

Q. If by mistake you/someone deleted FNDLIBR can this executable be restored if Yes, How & if no, what will you do ?

Yes, you can restore FNDLIBR executables

run adadmin on concurrent manager node

select option 2. Maintain Applications Files menu

then select 1. Relink Applications programs

when prompts for

Enter list of products to link ('all' for all products) [all]

select FND

when prompt for

Generate specific executables for each selected product [No] ? YES

select YES

& from list of executables select FNDLIBR

This will create new FNDLIBR executables.

Q. What is .pls files which you see with apps ?

.pls file stands for plsql files. In apps patch these files contain code to create package spec or package body or both.

Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

.ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

Q. What are .odf file in apps patch ?

odf stands for Object Description Files used to create tables & other database objects.

Q. What to find Form Server log files in forms ?

Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt

Forms Run Time Diagnostics default location is $ORACLE_HOME/forms60/log/$CONTEXT_NAME

Q. How to convert pll to pld file or pld file to pll ?

Pll->Pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/<passwd> module_access=file output_file=MSCOSCW1.pld script=yes

Pld -> pll f60gen module=MSCOSCW3.pld userid=apps/<passwd> module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

Q. When you apply C driver patch does it require database to be Up & Why ?

Yes , database & db listener should be Up when you apply any driver patch in apps. even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

Q. What is dev60cgi & f60cgi ?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

Q. Why does a worker fails in Oracle Apps Patch and few scenarios in which it failed for you ?

This question sounds stupid but this is asked quite often in Apps DBA Interview. Apps Patch worker can fail in case it doesn't find expected data, object, files or any thing which driver is trying to update/edit/modify. Possible symptoms may be underlying tables/objects are invalid, a prereq patch is missing , login information is incorrect, inconsistency in seeded data...

Q. What is difference between mod_osso & mod_ose in Oracle HTTP Server ?

mod_osso is Oracle Single Sign-On Module where as mod_ose is module for Oracle Servlet Engine.

mod_osso is module in Oracle's HTTP Server serves as Conduit between Oracle Apache Server & Singl Sign-On Server where as mod_ose is also another module in Oracle's HTTP Server serves as conduit between Oracle Apache & Oracle Servlet Engine.

Q. What is GSM in Oracle application E-Business Suite ?

GSM stands for Generic Service Management Framework. Oracle E-Business Suite consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager ..

Earlier each service used to start at their own but managing these services (given that) they can be on various machines distributed across network. So Generic Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some service is down ICM through FNDSM & other processes will try to start it even on remote server) With GSM all services are centrally managed via this Framework.

Q. What is FNDSM ?

FNDSM is executable & core component in GSM ( Generic Service Management Framework discussed above). You start FNDSM services via APPS listener on all Nodes in Application Tier in E-Business Suite.

Q. What is iAS Patch ?

iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shiiped as Shell scripts & you apply iAS patches by executing Shell script. Note that by default ORACLE_HOME is pointing to 8.0.6 ORACLE_HOME and if you are applying iAS patch export ORACLE_HOME to iAS . You can do same by executing environment file under $IAS_ORACLE_HOME

Q. If we run autoconfig which files will get effected ?

In order to check list of files changes during Autoconfig , you can run adchkcfg utility which will generate HTML report. This report will list all files & profile options going to change when you run AutoConfig.

Q. What is difference between .xml file & AutoConfig ?

Autoconfig is Utility to configure your Oracle Application environment. .xml file is repository of all configuration from which AutoConfig picks configuration and polulates related files.

Q. What is .lgi files ?

lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done. Usually informative logs.  

Q. How will you skip worker during patch ?

If in your adctrl there are six option shown then seventh is hidden option.(If there are seven options visible then 8th option is to Skip worker depending on ad version).

Q. Which two tables created at start of Apps Patch & drops at end of Patch ?

FND_INSTALLED_PROCESSES &AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

Q. How to compile an Oracle Reports file ?

Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

Q. What is difference between AD_BUGS & AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

Q. What exactly happens when you put an Oracle Apps instance in maintenance mode ?

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature

a) shuts down the Workflow Business Events System and

b) sets up function security so that no Oracle Applications functions are available to users.

Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch. (Source Metalink Note: 233044.1)

Q. What happens if the ICM goes down?

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. 

Q. Provide a high-level overview of the cloning process and post-clone manual steps.

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.

  • Provide an introduction to AutoConfig.

Q. How does AutoConfig know which value from the XML file needs to be put in which file? 

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.

Q. Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?

• • 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.

Q. What could be wrong if you are unable to view concurrent manager log and output files?

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.

Q.How will you change the location of concurrent manager log and output files?

The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.

Q. If the user is experiencing performance issues, how will you go about finding the cause?

• • 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.

Q.How will you change the apps password?

• • Use FNDCPASS to change APPS password.

  • Manually modify wdbsvr.app/cgiCMD.dat files.

  • Change any DB links pointing from other instances.

Q.Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.

• • 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."

Q How do you roll back on autoconfig session?

Each execution of AutoConfig creates a rollback script in case you need to revert to the previous configuration settings. The script and all backup configuration files from each AutoConfig session are stored in:

where:<MMDDhhmm> = (month, day, hour, minute of AutoConfig run)

To roll back an AutoConfig session:

• • On UNIX 

  • restore.sh

  • On Windows 

  • restore.cmd

restore.sh is a simple script(shown below) which just do copy of backed up files to their original location

cp /dev/devNOAappl/admin/devNOA_devapp/out/10120759/adconfig.txt /dev/devNOAappl/admin/adconfig.txt

cp /dev/devNOAappl/admin/devNOA_devapp/out/10120759/adalldefaults.txt /dev/devNOAappl/admin/devNOA/adalldefaults.txt

cp /dev/devNOAappl/admin/devNOA_devapp/out/10120759/runform.htm /dev/devNOAora9i/8.0.6/tools/web60/html/runform.htm

cp /dev/devNOAappl/admin/devNOA_devapp/out/10120759/runrep.htm /dev/devNOAora9i/8.0.6/tools/web60/html/runrep.htm

--

-

Q How do you know which all application processes are running/enabled on an application node?

One way is to see in $COMMON_TOP/admin/scripts which all scripts are present.

Second and better way is to go to the context file and see which all processes are enabled to be running on the present node.

Like :

<oa_process_status oa_var="s_tnsstatus">enabled</oa_process_status>

<oa_process_status oa_var="s_concstatus">disabled</oa_process_status>

<oa_process_status oa_var="s_formsstatus">disabled</oa_process_status>

<oa_process_status oa_var="s_reptstatus">disabled</oa_process_status>

<oa_process_status oa_var="s_jtffsstatus">enabled</oa_process_status>

Q How to generate context XML file again?

The CONTEXT FILE can be regenerated by adbldxml.pl which is present under $AD_TOP/bin

[aixkd:appdev:/Dev/DevNOAappl/ad/11.5.0/bin:] ls -ltr adbld*

-rwxrwxr-x    1 appdev  dba             888 Jul 14 2005  adbldxml.sh

-rwxrwxr-x    1 appdev  dba            1175 Jul 14 2005  adbldxml.cmd

-rwxr-xr-x    1 appdev  dba           25453 Dec 11 2010  adbldxml.pl

perl adbldxml.pl tier=apps appsuser=<APPSuser> 

Q How do you find which all files and configuration will be changed by Autoconfig without actually running it?

The Check Config utility (adchkcfg) is located at:

Check the AutoConfig configuration files by executing the following command:

• • On UNIX

  • adchkcfg.sh contextfile=<CONTEXT>

  • On Windows

  • adchkcfg.cmd contextfile=<CONTEXT>

This script will generate both html and text reports that provide information about all file changes, profile option changes and other important database updates that will be done during the next normal execution of AutoConfig. 

The report generated will contain below two parts ( *****OUTPUT FILE ATTACHED TO THIS WEBPAGE*****)

1) File System Changes

> Displays information about the location of the context file, the content of the currently active context file, the content of the context file that will be generated in the next AutoConfig run.

> Displays a list of all the files that will be changed during an AutoConfig execution

> Displays a list of all the new files that will be created during an AutoConfig execution.

2) Database Changes

> Displays the details only for profiles whose value would be changed in the next AutoConfig run. 

> Displays the details as in previous section for all Apps Database profiles managed by AutoConfig.

> Displays the details for important database updates that will be done in the next run of AutoConfig. The table name, column name, the current column value in the Database and the new AutoConfig value is displayed along with the name of the updating AutoConfig script and a brief description.

Q How to Change Applications Passwords using Applications Schema Password Change Utility (FNDCPASS or AFPASSWD) ?

Note : AFPASSWD  is enhanced version of FNDCPASS in R12.

Changing passwords frequently helps ensure database security, Oracle Applications provides a command line utility, FNDCPASS, to change/reset Oracle Applications schema passwords. This utility changes the password registered in Oracle Applications tables, changes the schema password in the database and can also change user passwords.

> One cannot change a schema name, such as APPLSYS or GL, after a product is installed, with FNDCPASS. 

> Ensure that the entire Oracle Applications system has been shut down before changing any schema passwords. 

> All users should log out and the Applications system should be down before running this utility. 

> If Oracle Applications user passwords are being changed then the relevant users should not be logged in. 

> Before changing any passwords, you should make a backup of the tables FND_USER and FND_ORACLE_USERID.

> Environment should be sourced first

  cd $APPL_TOP >> Run APPSORA.env

  also run <sid>_<node>.env

  cd admin >> Run adovars.env

FNDCPASS <APPS username>/<APPS password> 0 Y <SYSTEM username>/<SYSTEM password> <token> <username> <new_password>

Note:

The SYSTEM token is used when changing the APPLSYS password.

The ORACLE token is used when changing a SINGLE Applications schema password.

The ALLORACLE token is used when changing ALL Applications schema passwords.

The USER token is used when changing an Applications USER password.

IMPORTANT : Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same. If you change the password for one, FNDCPASS automatically changes the others. When changing APPS (or APPLSYS) and APPLSYSPUB passwords, do not restart the system until the entire password change process has been completed.

A). To change the APPS and APPLSYS schema password

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

FNDCPASS apps/<appspass> 0 Y system/<systempass> SYSTEM APPLSYS WELCOME

This command does the following:

• 1. Validates APPLSYS.

  2. Re-registers password in Oracle Applications.

  3. Changes the APPLSYS and all APPS passwords (for multi-APPS schema installations) to the same password.

  4. Because everything with a Privilege Level [set to any of ('E', 'U', 'D')] in the FND_ORACLE_USERID table must always have the same password, FNDCPASS updates these passwords as well as APPLSYS's password.

  5. For example, the APPS password will be updated when the APPLSYS password is changed. 

  6. ALTER USER is executed to change the ORACLE password for the above ORACLE users.

B). To change an Oracle Applications schema password (other than APPS/APPLSYS):

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

For example, the following command changes the GL user password to 'GL1'.

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

When specifying the ORACLE token, FNDCPASS expects the next arguments to be an ORACLE username and the new password.

C). To change all ORACLE schema passwords:

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

FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME

When specifying the ALLORACLE token, FNDCPASS expects the next argument to be the new password.

D). To change an Oracle Applications user's password:

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

FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

When specifying the USER token, FNDCPASS expects the next arguments to be an Oracle Applications username and the new password.

Using the AFPASSWD Utility as of R12.1.2

=========================================

> When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm

> AFPASSWD can be run from the database tier as well as the application tier.

> 

Q Is there any need to change default password of applsyspub?

The public ORACLE id (APPLSYSPUB) should not have its password changed since this account has only Read-Only privileges on a few tables and views for signing on to Applications.

Besides, the password for this account can be seen from the OS in the environment variable or

logical GWYUID, so it would be pointless to change it.

.

Ensure that the APPLSYSPUB account does not have grants or privileges beyond what is necessary.  The only things that should be accessible from the APPLSYSPUB account are: 

INSERT ON FND_UNSUCCESSFUL_LOGINS 

INSERT ON FND_SESSIONS 

EXECUTE ON FND_DISCONNECTED 

EXECUTE ON FND_MESSAGE 

EXECUTE ON FND_PUB_MESSAGE 

EXECUTE ON FND_SECURITY_PKG 

EXECUTE ON FND_SIGNON 

EXECUTE ON FND_WEBFILEPUB 

SELECT ON FND_LOOKUPS 

SELECT ON FND_APPLICATION 

SELECT ON FND_APPLICATION_TL 

SELECT ON FND_APPLICATION_VL 

SELECT ON FND_LANGUAGES_TL 

SELECT ON FND_LANGUAGES_VL 

SELECT ON FND_PRODUCT_GROUPS 

SELECT ON FND_PRODUCT_INSTALLATIONS