Sysadmin

1. How to delete an Oracle Applications user

The ability to delete applications users is not planned. Applications users cannot be deleted since records belonging to users are kept for security and monitoring purposes. The only thing to do is to invalidate the user by assigning an end date to the effective period for the user. The user is recorded in Who columns, so deleting would cause foreign table data integrity problems. The only supported method for “deleting” users from Apps is to END DATE them from within Apps. Do so by navigating to Security -> User -> Define.

2. How to manually change the APPLSYS, APPS and APPLSYSPUB passwords for Applications

The APPLSYS and APPS passwords always have to be the same. Always change both at the same time. When changing

these passwords, it is important to change the APPLSYS password first and the APPS password second. If the correct

procedure, to change these passwords is not followed, users will not be able to login after the change. As a precaution, please back up the FND_ORACLE_ID and FND_USER tables before beginning.

1. Have all users log out of applications.

2. Shutdown the concurrent managers.

3. Log in to applications as System Administrator.

4. Navigate to Security> Oracle> Register

5. Query up all available Oracle Ids. (NOTE: do not log out of this session of Applications until you have verified that all the following steps have been completed successfully).

6. At the same time, login to SQL*PLUS as the SYSTEM user.

7. In the applications session, enter the new password for APPLSYS. Re-enter the same password when asked to re-enter the password to confirm.

8. Save the change.

Note: At this point of the procedure you will see a “Caution” dialog

box which states the following: You are changing the APPLSYS password. Doing so will affect the whole system.

NOTE: The first character of the APPLSYS password MUST NOT BE a numeric. Use only alpha characters for the first character in the APPLSYS password.

In Application Object Library release 10.7 or later, you must also change all APPS schema passwords to match the APPLSYS password. You should only change the APPLSYS password while no other users are signed on. After you change the APPLSYS and all APPS schema passwords, you should exit and sign-on again. Be sure to change the APPLSYS and APPS schema passwords in the database before signing on again. Use: ALTER USER <APPLSYS USERNAME> IDENTIFIED BY <NEW PASSWORD>& #059; While the instructions contained in this dialog box are critical to successfully changing the APPLSYS & APPS passwords the dialog box does not indicate that you have made an error. Once you have read and understand the caution information you can simply click OK and proceed.

9. In the same applications session, change the APPS password.

10. In the same applications session, change the APPLSYSPUB password if necessary.

11. Save the change.

Note:Re-query the form (query – run) every time after changing the APPS password. If it is not re-queried and the APPS password is changed again, it is

impossible to continue.

12. In the SQL*Plus session, issue the following statements to change the passwords in the database, where ‘XXX’ is the password assigned to that user in the previous steps:

sql>ALTER USER APPLSYS IDENTIFIED BY XXX;

sql>ALTER USER APPS IDENTIFIED BY XXX;

sql>ALTER USER APPLSYS IDENTIFIED BY XXX;

13. Log out of SQL*Plus.

14. Open a NEW session in applications, WITHOUT CLOSING THE PREVIOUS SESSION,

and log in. If log in is successful, the change was successful. Otherwise, repeat steps 5 onward, ensuring the same password is used for both APPLSYS and APPS, in both applications and SQL*Plus.

15. Restart the concurrent managers.

3. How to exclude menu choices from a new SSWA responsibility?

You are using Applications rel.11.03 and Self Service Web Applications. You want to exclude some menu choices from a new SSWA responsibility.

System Administrator’s responsibility Naviagtion: Security/Responsibility/Define

FNDSCRSP 11.0.15.1100.5 You defined a new responsibility for Web Expenses and assigned menu AP_WEB_EXP_REP_NAVIGATE to this responsibility. You want to exclude, for example, these menu choices:

a. Upload Expense spreadsheets

b. Download Expense spreadsheets

c. Valid projects and tasks

When you log back in these menu choices still available. You click on Upload Expense spreadsheets and following error appears on a

screen: ‘Your session is no longer valid’

Solution Description

Sysadmin responsibility Navigation: Application/Menu Open menus form. 11.0.3

Define a new menu and include only functions you want to use in Expense Reports.

Assign this menu to your new responsibility.

Explanation

Do not use menu AP_WEB_EXP_REP_NAVIGATE. Define responsibilities form (FNDSCRSP) does not support menu exclusions for Self-Service Web Applications responsibilities

4. How to delete a responsibility from Oracle Applications?

The ability to delete responsibilities is not planned. Responsibilities cannot be deleted since those records are kept for security and monitoring purposes. However, responsibilities can be disabled by assigning an end date to the effective period Responsibilities ValueSet assignation (FNDRSGRP)

5. How to Run Oracle Applications Network Test

The Network Test is used to evaluate the performance of the network with Oracle

Applications. It provides the following tests:

a. Latency Test. It examines the time taken for a single packet to make a round trip from the client side application to the server.

b. Bandwidth Test. It examines the data rate to see how many bytes per second the network can transfer from the server to the client.

1. Login to Core Applications as the Sysadmin Responsibility.

2. Navigate to Application > Network Test.

3. Click the ‘Clear Old Test Data’ button to purge previous test results from

the database.

4. Specify the number of Trials and the Iterations for each trial for both the

latency and bandwidth blocks. The default settings for both are 5 trials of 100 iterations each.

5. Select the Run Test button to perform the test.

6. You receive an APPLMNT error when trying to view output using the Report Review Agent.

Problem

When trying to view the output of a report from the PC, you receive the following error: Environment variable APPLMNT is not set.

Solution

Follow the steps listed below:

a) Log into Applications as system administrator.

b) Open the System Profiles form.

c) Set the Fileserver Enabled profile option to ‘Yes’ at the site level.

d) Stop and restart the concurrent manager.

e) Log out of Applications on the PC, and log back in for the change to take effect.

7. You receive an APP-0075 error when trying to delete an assigned printer. How can you find out who it is assigned to?

Problem

You try to delete a printer through the Register Printers form and receive the following error: APP-0075 printer is currently in use

CAUSE: A profile option currently uses this printer. You cannot delete the printer until it is no longer in use.

ACTION: Change the profile option value.

Solution

To determine where the printer is defined, and to correct the problem, perform

the following steps:

Note: Issue the commands in SQL*Plus logged in as apps.

a. sql> select profile_option_id,profile_option_name from fnd_profile_options where profile_option_name like ‘%PRINTER%’;

You see output similar to the following:

PROFILE_OPTION_ID PROFILE_OPTION_NAME

—————– ——————-

109 PRINTER

b. sql>select level_id,profile_option_id,profile_option_value,level_value from fnd_profile_option_values where profile_option_id=109

You see output similar to the following:

LEVEL_ID PROFILE_OPTION_ID PROFILE_OPTION_VALUE LEVEL_VALUE

——– —————– ——————– ———–

10004 109 colprt21 1036

c. sql>select user_id,user_name form fnd_user where user_id = 1036

The output looks similar to:

USER_ID USER_NAME

——- ———

1036 ACLARK

This is the responsible user profile that needs to have the Printer profile option deleted.

d. Log into Applications under the \navigate profile system screen.

e. Query up the user profile option for ACLARK. The printer profile option should correspond to the printer you need to delete.

f. Delete this line and then you can delete the printer.

8. You are unable to delete a printer because it has been referenced by a concurrent program.

Problem: You try to delete a printer in the Register Printers form and receive the message: This printer is referenced in a concurrent program definition. You cannot delete a printer while it is referenced.

Solution: You can use the following SQL statement to find which concurrent programs reference the printer.

Note: In this example you are looking for concurrent programs that are setup to run against the printer TEST

select USER_CONCURRENT_PROGRAM_NAME ,

PRINTER_NAME from FND_CONCURRENT_PROGRAMS

where PRINTER_NAME = ‘TEST’

9. Adadmin fails because it cannot find applcreg.txt with 10.7.

Problem: You try to run adadmin on 10.7 and it fails with the following message:

Ad Administrator error: Unable to open file for reading /u15/app/applmgr/10.7/applcreg.txt

You are logged into UNIX as applmgr and have read/write access to the APPL_TOP directory. You cannot find the applcreg.txt file on any of the other systems. What is this file and where can it be found?

Solution: You chose ‘Client’ configuration, but need to choose the same configuration that was chosen when you ran AutoInstall against this APPL_TOP. AutoInstall creates applcreg.txt when you choose ‘Client’ configuration during an installation or upgrade.

10. How can you display the name of the Applications user on a form?

Problem:How can the user name of the user currently signed into Applications be displayed on a form that they are accessing?

Solution:In GUI use: FND_PROFILE.GET(‘USERNAME’). In character mode use:#FND_GETPROFILE

NAME=”USERNAME”

FIELD=”<block>.<field>”

11. How can you find the name of a needed profile option?

Problem: If you want to find a value that is stored in a profile option, but you do not know what the profile is called, how can you find the profile option name?

Solution: To find the name of the profile option, go into Dev Utils examine and select $PROFILE$. Then search for the profile. It may be necessary to add Dev Utils to a character menu.

12. How do you tell what product is installed Full and Shared within Applications?

This information can be accessed within Applications from the Alert Manager Responsibility. Navigation Path: SYSTEM -> INSTALLATIONS

13. How to compile an Oracle Reports file or PLL?

UNIX: Oracle Reports

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 PLL File

adrepgen userid=apps\<psswd> source=<$PRODUCT_TOP>\srw\filename.rdf

dest=<$PRODUCT_TOP\srw\filename.rdf

stype=pllfile dtype=pllfile logfile=x.log overwrite=yes batch=yes

dunit=character

14. How do you test out the Initilization strings used in Applications Printer Drivers?

UNIX

– Create a sample directory and copy a rpt file into that directory.

– Type: FNDPRESC “init_string” > foo (init_string = the exact

init string used wtihin applications)

– vi the report file and insert a line at the top of the report.

Shift + o

– vi the foo file and copy / paste the init string at the inserted

line of the report file.

– lp -d<printername> l#####.out

15. What is the proper way to change the Applications privileged passwords?

– Shutdown the Concurrent Manager

– Change the APPS, APPLSYS and APPLSYSPUB passwords at the

Database level

– Change the APPS, APPLSYS and APPLSYSPUB passwords at the

Application level

– Change the APPS password in any Concurent Manager startup

scripts

16. How to manually change the APPLSYS, APPS and APPLSYSPUB passwords for Applications?

The APPLSYS and APPS passwords always have to be the same. Always change both at the same time. When changing these passwords, it is important to change the APPLSYS password first and the APPS password second. If the correct procedure to change these passwords is not followed, users will not be able to login after the change. As a precaution, please back up the FND_ORACLE_ID and FND_USER tables before beginning.

1. Have all users log out of applications.

2. Shutdown the concurrent managers.

3. Log in to applications as System Administrator.

4. Navigate to Security> Oracle> Register

5. Query up all available Oracle Ids.

NOTE: do not log out of this session of Applications until you have verified that all the following steps have been completed successfully).

6. At the same time, login to SQL*PLUS as the SYSTEM user.

7. In the applications session, enter the new password for APPLSYS. Re-enter the same password when asked to re-enter the password to confirm.

8. Save the change. At this point of the procedure you will see a “Caution” dialog box which states the following:

You are changing the APPLSYS password. Doing so will affect the whole system.

NOTE: The first character of the APPLSYS password MUST NOT BE a numeric. Use only alpha characters for the first character in the APPLSYS password. In Application Object Library release 10.7 or later, you must also change all APPS schema passwords to match the APPLSYS password. You should only change the APPLSYS password while no other users are signed on. After you change the APPLSYS and all APPS schema passwords, you should exit and sign-on again. Be sure to change the APPLSYS and APPS schema passwords in the database before signing on again.

Use: ALTER USER <APPLSYS USERNAME> IDENTIFIED BY <NEW PASSWORD>& #059; While the instructions contained in this dialog box are critical to successfully changing the APPLSYS & APPS passwords the dialog box does not indicate that you have made an error. Once you have read and understand the caution information you can simply click OK and proceed.

9. In the same applications session, change the APPS password.

10. In the same applications session, change the APPLSYSPUB password if

necessary.

11. Save the change.

Note: Re-query the form (query – run) every time after changing the APPS password. If it is not re-queried and the APPS password is changed again, it is impossible to continue.

12. In the SQL*Plus session, issue the following statements to change the passwords in the database, where ‘XXX’ is the password assigned to that user in the previous steps:

sql>ALTER USER APPLSYS IDENTIFIED BY XXX;

sql>ALTER USER APPS IDENTIFIED BY XXX;

sql>ALTER USER APPLSYS IDENTIFIED BY XXX;

13. Log out of SQL*Plus.

14. Open a NEW session in applications, WITHOUT CLOSING THE PREVIOUS SESSION, and log in. If log in is successful, the change was successful. Otherwise, repeat steps 5 onward, ensuring the same password is used for both APPLSYS and APPS, in both applications and SQL*Plus.

15. Restart the concurrent managers.

17. How to Reset only the SYSADMIN Password?

PROBLEM DESCRIPTION

You have forgotten the SYSADMIN applications password and cannot login. You know the apps and applsys passwords but does not want to follow the steps to reset all passwords since resetting applsys would require re-registering all

applications user passwords.

How can you reset only the SYSADMIN password?

SOLUTION DESCRIPTION

The applsys password provides the basis for all user passwords so resetting applsys will require re-registering all user passwords.

If there are any other users with System Administrator privileges, then they can reset the SYSADMIN user’s password from within applications. Otherwise SYSADMIN can be reset without affecting other users as follows:

1. Log in to SQL*Plus as applsys

2. Backup FND_USER and FND_ORACLE_USERID tables using create table FND_USER_BAK as select * from FND_USER;

create table FND_ORACLE_USERID_BAK as select * from FND_ORACLE_USERID;

update FND_USER

set ENCRYPTED_FOUNDATION_PASSWORD =’ 2DF3E509EB6A33F9607959C0976E25D997166FAB694ACDDAE466414791A44411′,

ENCRYPTED_USER_PASSWORD=’ D2FCA9810D86BCA9BE944D3E2E7A4A9E6CDF89AAD633179B701774083F907C13′

where user_name=’SYSADMIN’;

sql>commit;

The SYSADMIN password is now set to WELCOME.

18. Profile Options Concurrent Manager Release 11

PURPOSE

The intent of this note is to list and provide a brief explanation of the system administration profile options that relate to the concurrent manager in Oracle Applications release 11.

SCOPE & APPLICATION

To access the system profile options, log into Oracle Applications and select responsibility ‘System Administrator’. Within the System Administrator responsibility navigate to:

Profile > System

Within the ‘Find System Profile Values’ (FNDPOMPV) form and in column ‘Profile’ query using the following, %concurrent:%”

Note: This is to query all the concurrent manager system profile options.

CONCURRENT MANAGER PROFILE OPTIONS

1. Concurrent: Active Request Limit

Value: Numeric

This profile options will restrict the number of concurrent requests that may be run simultaneously by each user. If you do not specify a limit, then no limit is imposed.

2. Concurrent: Attach URL

Values: YES/NO

If you set this option to YES, then this causes a URL to be attached to request completion notifications. For example, when a user submits a request and uses the ‘Defining Completion Options’ region to specify people to be notified,

a notification is sent each of the people designated.

3. Concurrent: Collect Request Statistics

Values: YES/NO

If you set this option to YES, this causes applications to collect statistics on your run-time concurrent processes.

4. Concurrent: Conflicts Domain

Values: LOV (list of values: domains) This option identifies the domain within which all the incompatibilities between programs has to be resolved

5. Concurrent: Debug Flags

This option is used to debug the transactions managers. You should only use this option at the request of Oracle Support.

6. Concurrent: Directory for Copy

You can identify a directory on your operating system to store copies of your report output or log files. This directory is used when a copy operation is requested in character mode of the applications.

7. Concurrent: Enable Request Submission in View Mode

Values: YES/NO

Setting this option to YES will enable the ‘Submit a New Request’ button when users invoke the form FNDRSRUN (Find Requests Form). Navigation path is either: (1) Menu: Help > View My Requests, or (2) navigate (with system administrator responsibility) to, Requests > View.

8. Concurrent: Hold Requests

Values: YES/NO

This option enables you to automatically place requests on hold after submission.

9. Concurrent: Multiple Time Zones

Values: YES/NO

When the client’s session and the concurrent manager are running in different times zones, use this option to ensure that the request is scheduled immediately regardless of you client session’s time zone.

10. Concurrent: PMON Method

This option is presented for documentation purposes only. Users cannot see or alter this profile option. This option is not visible or cannot be updated from the System Profile Option (FNDPOMPV) form. The PMON method refers to the process monitor. The Internal Concurrent Manager (ICM) monitors the individual concurrent managers’ processes to verify the managers are running. Normally, the PMON method must be set to LOCK. To change the profile option setting, you must execute the SQL script “afimpmon.sql” which resides in directory “$FND_TOP/sql/”.

11. Concurrent: Report Access Level

Values: RESPONSIBILITY/USER

This option determines access privileges to report output files and log files generated by a concurrent program. This option can be set by a system administrator or by the user.

12. Concurrent: Report Copies

Values: Numeric

This option determines the number of default copies that print for each submitted concurrent request.

13. Concurrent: Request Priority

Values: Numeric

Concurrent requests in Oracle Applications are queued. Requests normally run according to a start time on a first submitted, first run basis. Priority overrides this request start time. A higher priority request starts before an earlier request. This option displays the default priority for you concurrent requests. Only a system administrator can change your request priority.

14. Concurrent: Request Start Time

Values: Numeric (Date/Time)

With this profile option, you can set the date and time that your requests are available to start running

* If the start time is at or before the current date and time, requests are available to run immediately

* Start a request in the future, for example, at 6:00 PM on December 31, 2000, enter the following value,

31-DEC-2000 18:00

* This profile option requires the date and time, for example, 31-DEC-2000 18:00

* Changing values does not affect request already submitted

15. Concurrent: Save Output

Values: YES/NO

Setting this option to YES will save the output from a concurrent request to a file.

16. Concurrent: Sequential Requests

Values: YES/NO

Setting this option to YES will force concurrent requests to run sequentially in the order in which they were submitted.

17. Concurrent: Show Requests Set Stages

Values: YES/NO

Set this option to YES to show request set stages in the concurrent request screens.

18. Concurrent: Show Requests Summary After Each Request Submission

Values: YES/NO

By default the Request Summary (FNDRSRUN) form appears after every request submission. Set this option to NO if you have multiple requests to submit and do not wish to see the Request Summary form after each submission.

19. Concurrent: URL Lifetime

Value: Numeric

If the profile option ‘Concurrent: Attach URL’ has been set to YES, a URL is associated the output of an execution and is passed to anyone flagged for notification during the request submission. This option specifies how long (in minutes) the URL is to be kept active.

20. Concurrent: Use ICM

Values: YES/NO

ICM (Internal Concurrent Manager)

Set this option to YES to use the Internal Concurrent Manager to resolve request conflicts (result from programs that are defined to be incompatible) instead of using the Conflict Resolution Manager (CRM).

21. Concurrent: Wait for Available TM

Value: Numeric

TM (Transaction Manager)

Use this option to specify the number of minutes that a client will wait for a given transaction manager to become available before trying a different transaction manager.

19. Why do I have invalid objects? What causes them?

Invalid objects can and will occur for many reasons. You will usually find invalid objects after running (or failing to run) adprepdb, doing an export/import, upgrading, or applying patches. Invalid objects are usually caused by missing grants, synonyms, views, tables or packages, but can also be caused by corrupted packages.

20. Why does Oracle Support always tell me to recompile my invalid objects?

Compiling invalid objects on your database is almost the equivalent of running scandisk on a PC hard drive. This should be one of the first things you check if you start experiencing problems with your Oracle database. It is also a good idea to schedule regular checks for invalid objects.

When you call in to Oracle Support with a database or installation issue, one of the first questions they will probably ask is whether you have checked for and resolved any invalid objects.

21. Are invalid objects ever acceptable? How many is too many?

If the invalid objects exist for a product or application that is not installed, it may be ok to have some, but it is preferable to have no invalid objects existing at all. If invalid objects exist for a product or application that you do have installed and are using, then it should be considered unacceptable and any existing invalid objects should be resolved before further issues can occur.

There is no set number of invalid objects that could be considered ‘acceptable’ as each situation will vary widely from one database to the next. You could just have a few invalid objects or they could number in the hundreds or even thousands, but every effort should be made to resolve them one way or another.

22. How can I get a quick count of my invalid objects (if any) for regular maintenance?

To get a quick count of the number of existing invalids (if any), use the following select statement:

SELECT COUNT(*)

ROM DBA_OBJECTS

HERE STATUS = ‘INVALID’;

For a more detailed query, use the following script:

ELECT OWNER, OBJECT_TYPE, COUNT(*)

FROM DBA_OBJECTS

HERE STATUS = ‘INVALID’

ROUP BY OWNER, OBJECT_TYPE;

23.How can I manually recompile individual invalid objects?

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps). Use one of the following depending on the object type:

SQL> alter package <package_name> compile; (package specification)

SQL> alter package <package_name> compile body; (package body)

SQL> alter view <view_name> compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings:

SQL> show errors or

SQL> select * from user_errors where name = ‘<OBJECT_NAME>’;

24. How can I recompile all my invalid objects using ADCOMPSC.pls?

Within Applications, there is a script to compile INVALID objects – called ADCOMPSC.pls Arguments for ADCOMPSC.pls:

1 – Schema to run in

2 – Password for schema

3 – Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. If you encounter an ORA-1555 error while running adcompsc.pls, just restart the script.

The script can be run as follows:

cd $AD_TOP/sql

sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example: SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but you still have invalid objects, you should run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

25. What if I still have invalid objects than can not be resolved by ADCOMPSC.pls?

If there are any objects still left INVALID, you can verify them by using aderrchk.sql to record the remaining INVALID objects. Aderrchk.sql use the same syntax as adcompsc.pls. This script is also supplied with the Applications. You can send the aderrchk.sql to a file using the spool <file> command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects will not compile, try the following:

select text

from user_source

where name = ‘OBJECTNAME’

and text like ‘%Header%’;

This script will give you the sql that creates the packages. You can then recreate the packages. SQL>@packageheader SQL>@packagebody If recreating the package does not make the package valid you will have to analyze the user_errors table to try to determine the cause of the invalid package.

select text

from user_errors

where name = ‘PACKAGENAME’;