Month: November 2010

Analyze Index Vs. Gather_Index_Stats – Oracle Database 10gp

Published on by Advait5 Comments

One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command if updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

 

Create a table

SQL> create table t1 pctfree 99 pctused 1  
2  as  
3  select rownum id,  
4  trunc(100 * dbms_random.normal) val,  
5  rpad('X',100) padding  
6  from all_objects where rownum <= 10000;

Table created.

I intentionally created table with pctfree as 99 so that each block will have 1 row and it uses 10000 blocks to store 10000 rows.

Create an index

SQL> create index t1_i on t1(val) ;
Index created.

Check the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';

 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
1          21           563

We have around 1600 rows whose val > 100

SQL> select count(*) from t1 where val > 100;
 COUNT(*)
----------      
  1598

Delete rows from table, which will also delete entries from index and some of the leaf blocks will get empty.

SQL> delete from t1 where val > 100;
1598 rows deleted.

Analyze index

SQL> analyze index t1_i validate structure;
Index analyzed.

Check again the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          21           563

Number of leaf blocks reported are still 21

Gather stats on index

SQL> exec dbms_stats.gather_index_stats('ADVAITD_DBA','T1_I');
PL/SQL procedure successfully completed.

Check the number of leaf blocks again

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          18           379

Now if we see the number of leaf blocks reported are 18. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So its better to use gather_index_stats rather than analyze index.

Hope this helps !!

Advertisement

Oracle E-Business Suite R12 – Profiles

Published on by Advait4 Comments

Profile Options in Oracle Application Object Library

This section lists each profile option in Oracle Application Object Library. These profile options are grouped into categories based on their functional area and are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile’s setting.

Unless otherwise noted, a profile option uses the Security hierarchy type.

A table is provided for most profile options that lists the access levels for the profile option (at which levels the system administrator can set the profile option). For Security profile options, there are four possible levels at which system administrators can view and update a profile option value: site, application, responsibility, and user. This table lists whether the profile option’s value is visible at each of these levels, and whether it is updatable at each level.

Concurrent Processing Execution

The internal name for this profile category is FND_CP_EXECUTION.

Concurrent:Active Request Limit

You can limit the number of requests that may be run simultaneously by each user. or for every user at a site. If you do not specify a limit, no limit is imposed.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is CONC_REQUEST_LIMIT.

Concurrent:Attach URL

Setting this option to “Yes” causes a URL to be attached to request completion notifications. When a user submits a request, and specifies people to be notified in the Defining Completion Options region, everyone specified is sent a notification when the request completes. If this profile option is set to Yes, a URL is appended to the notification that enables them to view the request results online.

Only the System Administrator can update this profile option.

Users can see but not update this profile option.

This profile options is visible at all levels but can only updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_ATTACH_URL.

Concurrent:Conflicts Domain

Specify a conflict domain for your data. A conflict domain identifies the data where two incompatible programs cannot run simultaneously.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_CD_ID.

Concurrent:Collect Request Statistics

Set this profile option to “Yes” to have statistics for your runtime concurrent processes collected.

To review the statistics you must run the Purge Concurrent Request and/or Manager Data program to process the raw data and have it write the computed statistics to the FND_CONC_STAT_SUMMARY table. You can then retrieve your data from this table using SQL*PLUS or on a report by report basis using the Diagnostics window from the Requests window.

Users cannot see nor change this profile option.

This profile option is visible at all levels but can only be updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_REQUEST_STAT.

Concurrent:Date Parameter Increment Option

Use this profile to control how date parameters are automatically incremented for concurrent requests. In the Standard Request Submission window, the user can specify if to run a request periodically. The user can then specify that the interval be based on the start date of the requests, or specify the interval using a unit of time and number of units.

If this profile is set to “Start Date” then the date parameters for a given request will be incremented according to the difference between the requested start date of the request and the requested start date of the previous request. If this profile is set to “Resubmit” any date parameters are incremented according to the current request’s date parameter and the amount of time represented by the number of units (RESUBMIT_INTERVAL) and the unit of time (RESUBMIT_INTERVAL_UNIT_CODE).

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_DATE_INCREMENT_OPTION.

Concurrent:Hold Requests

You can automatically place your concurrent requests on hold when you submit them.

The default is “No”. The concurrent managers run your requests according to the priority and start time specified for each.

Changing this value does not affect requests you have already submitted.

“Yes” means your concurrent requests and reports are automatically placed on hold. To take requests off hold, you:

  • Navigate to the Requests window to select a request
  • Select the Request Control tabbed region
  • Uncheck the Hold check box

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_HOLD.

Concurrent:Multiple Time Zones

“Yes” sets the default value to ‘Sysdate-1’ for the ‘Schedules Start Date’ used by request submissions. Sysdate-1 ensures that you request is scheduled immediately regardless of which time zone your client session is running in. You should use this profile option when the client’s session is running in a different time zone than the concurrent manager’s session.

Users cannot see nor change this profile option.

This profile option is visible at all four levels and updatable at the Site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_MULTI_TZ.

Concurrent:Print on Warning

Set this profile option to “Yes” if you want concurrent request output to be printed if the requests completes with a status of Warning.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRINT_WARNING.

Concurrent:Report Copies

You can set the number of output copies that print for each concurrent request. The default is set to 1.

  • Changing this value does not affect requests that you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_COPIES.

Concurrent:Request Priority

This displays the default priority number for your concurrent requests. Only a system administrator can change your request priority.

Requests normally run according to start time, on a “first-submitted, first-run” basis. Priority overrides request start time. A higher priority request starts before an earlier request.

Priorities range from 1 (highest) to 99 (lowest). The standard default is 50.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRIORITY.

Concurrent:Save Output

The Concurrent: Save Output profile is used to determine whether the default behavior of certain concurrent programs should be to save or delete their output files. This only affects concurrent programs that were created in the character mode versions of Oracle Applications and that have a null value for “Save Output”.

  • “Yes” saves request outputs.
  • Some concurrent requests do not generate an output file.
  • If your request output is saved, you can reprint a request. This is useful when requests complete with an Error status, for example, the request runs successfully but a printer malfunctions.
  • Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SAVE_OUTPUT.

Concurrent:Sequential Requests

You can force your requests to run one at a time (sequentially) according to the requests’ start dates and times, or allow them to run concurrently, when their programs are compatible.

  • Concurrent programs are incompatible if simultaneously accessing the same database tables incorrectly affects the values each program retrieves.
  • When concurrent programs are defined as incompatible with one another, they cannot run at the same time.

“Yes” prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.

“No” means your requests can run concurrently when their concurrent programs are compatible.

Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SINGLE_THREAD.

Concurrent:Wait for Available TM

You can specify the maximum number of seconds that the client will wait for a given transaction manager (TM) to become available before moving on to try a different TM.

Users can see and update this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is CONC_TOKEN_TIMEOUT.

Concurrent Processing File Server

The internal name for this profile category is FND_CP_FILE_SERVER.

RRA:Delete Temporary Files

When using a custom editor to view a concurrent output or log file, the Report Review Agent will make a temporary copy of the file on the client. Set this profile to “Yes” to automatically delete these files when the user exits Oracle Applications.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_DELETE.

RRA:Enabled

Set this user profile to “Yes” to use the Report Review Agent to access files on concurrent processing nodes.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_ENABLED.

RRA: Service Prefix

Using this new profile option allows you to override the default service name prefix (FNDFS_) assigned to the Report Review Agent. By assigning a new prefix to the Report Review Agent you can avoid having multiple instances of the Applications share executables.

Valid values for this option must be nine characters or less and use only alphanumeric characters or the underscore. We recommend using the underscore character as the last character of your value as in the default value “FNDFS_”.

Users cannot see or update this profile option.

This profile option is visible and updatable at the site level only.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is FS_SVC_PREFIX.

Attention: GLDI will not support the “RRA: Service Prefix” profile until release 4.0 and so uses the default prefix “FNDFS_” regardless of the value entered for the profile option. Consequently, you must ensure that at least one of your Report Review Agents maintains the default prefix in order for GLDI to access the application executables.

RRA:Maximum Transfer Size

Specify, in bytes, the maximum allowable size of files transferred by the Report Review Agent, including those downloaded by a user with the “Copy File…” menu option in the Oracle Applications Report File Viewer and those “temporary” files which are automatically downloaded by custom editors. For example, to set the size to 64K you enter 65536. If this profile is null, there is no size limit.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_MAX_TRANS.

Concurrent Processing Manager

The internal name for this profile category is FND_CP_MANAGER.

Concurrent:Debug Flags

Your Oracle support representative may access this profile option to debug Transaction Managers. Otherwise, it should be set to null.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_DEBUG.

Concurrent:GSM Enabled

Use this profile option to enable Generic Service Management.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_GSM_ENABLED.

Concurrent:OPP Process Timeout

This profile option specifies the amount of time the manager waits for the OPP to actually process the request.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PP_PROCESS_TIMEOUT.

Concurrent:OPP Response Timeout

This profile option specifies the amount of time a manager waits for the OPP to respond to its request for post processing.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PP_RESPONSE_TIMEOUT.

Concurrent:PCP Instance Check

This profile option controls whether Parallel Concurrent Processing (PCP) will be sensitive to the state (up or down) of the database instance connected to on each middle-tier node.

When this profile option is set to “OFF”, PCP will not provide database instance failover support; however, it will provide middle-tier node failover support when a node goes down.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CP_INSTANCE_CHECK.

Concurrent Processing Submission

The internal name for this profile category is FND_CP_SUBMISSION.

Concurrent:Allow Debugging

This profile option allows debug options to be accessed by the user at submit time.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_CONC_ALLOW_DEBUG.

Concurrent:Enable Request Submission in View Mode

Use this profile option to enable Request Submission in View Requests mode.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_FNDRSRUN_MODE.

Concurrent:Report Access Level

Determines access privileges to report output files and log files generated by a concurrent program. This profile option can be set by a System Administrator to User or Responsibility.

If your Concurrent:Report Access Level profile option is set to “User” you may:

  • View the completed report output for your requests online
  • View the diagnostic log file for those requests online. (system administrator also has this privilege)
  • Reprint your completed reports, if the Concurrent:Save Output profile option is set to “Yes”.
  • If you change responsibilities, then the reports and log files available for online review do not change.

If your Concurrent:Report Access Level profile option is set to “Responsibility”, access to reports and diagnostic log files is based on the your current responsibility.

  • If you change responsibilities, then the reports and log files available for online review change to match your new responsibility. You can always see the output and log files from reports you personally submit, but you also see reports and log files submitted by any user from the current responsibility.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, responsibility, and user levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REPORT_ACCESS_LEVEL.

Concurrent:Request Start Time

You can set the date and time that your requests are available to start running.

  • If the start date and time is at or before the current date and time, requests are available to run immediately.
  • If you want to start a request in the future, for example, at 3:45 pm on June 12, 2002, you enter 2002/06/12 15:45:00 as the profile option value.

Attention: You must ensure that this value is in canonical format (YYYY/MM/DD HH24:MI:SS) to use the Multilingual Concurrent Request feature.

  • You must include both a date and a time.
  • Changing this value does not affect requests that you have already submitted.
  • Users can override the start time when they submit requests. Or, this profile option can be left blank and users will be prompted for a start time when they submit requests.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_START.

Concurrent: Show Requests Summary After Each Request Submission

Using this new profile option, you can choose to either have the Requests Summary displayed each time you submit a request, or retain the request submission screen.

The default is “Yes”. “Yes” means the Requests Summary screen is displayed each time you submit a request.

If you choose “No”, a decision window is opened asking you if you wish to submit another request. When you choose to submit another request you are returned to the submission window and the window is not cleared, allowing you to easily submit copies of the same request with minor changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_SUMMARY.

Concurrent:Validate Request Submission

This profile option prompts users in SRS form if no options or parameters have been changed from their defaults.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_VALIDATE_SUBMISSION.

Printer

You can select the printer which prints your reports. If a printer cannot be selected, contact your system administrator. Printers must be registered with Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is PRINTER.

Concurrent Processing View Requests

The internal name for this profile category is FND_CP_VIEW_REQUESTS.

Concurrent:Show Request Set Stages

Set this profile option value to Yes to show request set stages in the concurrent request screens.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SHOW_STAGES.

Concurrent:URL Lifetime

The numeric value you enter for this profile option determines the length of time in minutes a URL for a request ouput is maintained. After this time period the URL will be deleted from the system. This profile option only affects URLs created for requests where the user has entered values in the notify field of the Submit Request or Submit Request Set windows.

Attention: All request ouput URLs are deleted when the Purge Concurrent Requests and Manager… program is run even if the URL liftime has not expired.

Users can see and update this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_URL_LIFETIME.

FND: Default Request Days

This profile option specifies the default number of days to view requests.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_DEFAULT_REQUEST_DAYS.

Maximum Page Length

Determines the maximum number of lines per page in a report.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is MAX_PAGE_LENGTH.

Viewer: Application for HTML, PCL, PDF, Postscript, Text, and XML

These profile options determine the applications a user will use to view reports in the given output formats. For example, you could set Viewer: Application for Text to ‘application/word’ to view a Text report in Microsoft Word.

Valid values are defined by the system administrator in the Viewer Options form.

Users can see and update these profile options.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal names for these profile options are FS_MIME_HTML, FS_MIME_PCL, FS_MIME_PDF, FS_MIME_PS, FS_MIME_TEXT, and FS_MIME_XML.

Viewer:Default Font Size

Using this new profile option, you can set the default font size used when you display report output in the Report Viewer.

The valid values for this option are 6, 8, 10, 12, and 14.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FNDCPVWR_FONT_SIZE.

Viewer: Text

The Viewer: Text profile option allows you to send report output directly to a browser window rather than using the default Report Viewer. Enter “Browser” in this profile option to enable this feature.

Users can see and update the Viewer:Text profile option.

This profile option is both visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is EDITOR_CHAR.

Database

The internal name for this profile category is FND_DATABASE.

Database Instance

Entering a valid two_task connect string allows you to override the default two_task. This profile is specifically designed for use with Oracle Parallel Server, to allow different responsibilities and users to connect to different nodes of the server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is INSTANCE_PATH.

FND: Resource Consumer Group

Resource consumer groups are used by the Oracle8i Database Resource Manager, which allocates CPU resources among database users and applications. Each form session is assigned to a resource consumer group. The system administrator can assign users to a resource consumer group for all of their forms sessions and transactions. If no resource consumer group is found for a process, the system uses the default group “Default_Consumer_Group”.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_RESOURCE_CONSUMER_GROUP.

Two Task

This profile option should be set by AutoConfig. only.

The TWO_TASK for the database. This profile is used in conjunction with the Gateway User ID profile to construct a connect string for use in creating dynamic URLs for the Web Server. This should be set to the SQL*NET. alias for the database.

Note: The TWO_TASK must be valid on the node upon which the WebServer is running

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is TWO_TASK.

Debug

The internal name for this profile category is FND_DEBUG.

Account Generator:Debug Mode

This profile option controls Oracle Workflow process modes for the Account Generator feature in flexfields. This profile option should normally be set to “No” to improve performance. If you are testing your Account Generator implementation and using the Oracle Workflow Monitor to see your results, set this profile option to “Yes”.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ACCOUNT_GENERATOR:DEBUG_MODE.

BIS/AOL:Debug Log Directory

The directory for BIS debugging log files.

Users can see and change this profile option.

System administrators can see and update this profile option at the site level only.

The internal name for this profile option is BIS_DEBUG_LOG_DIRECTORY.

FND: Override Directory

The FND:Override Directory profile option is used by the Work Directory feature. The value of FND: Override Directory should be the directory containing your alternate files. Typically, this profile option should be set at the User level only.

Using the Work Directory and this profile option should be done for debugging only, as they present a security risk.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLWRK.

Utilities: Diagnostics

Utilities: Diagnostics determines whether a user can automatically use the Diagnostics features. If Utilities:Diagnostics is set to Yes, then users can automatically use these features. If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema to use the Diagnostics features.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DIAGNOSTICS.

Utilities:SQL Trace

This profile option is used by concurrent processing only. SQL trace files can be generated for individual concurrent programs. The trace can be enabled at the user level by setting the profile “Utilities:SQL Trace” to “Yes”. This profile can be enabled for a user only by System Administrator so that it is not accidentally turned on and disk usage can be monitored.

For more information on SQL trace, see the Oracle database documentation.

Users cannot see nor change this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SQL_TRACE.

Deployment

The internal name for this profile category is FND_DEPLOYMENT.

Forms Runtime Parameters

Use this profile to specify certain forms runtime parameters. The profile value must be entered in as parameter=value. Each parameter-value pair must be separated by a single space. For example:

record=collect log=/tmp/frd.log debug_messages=yes

In order for the parameters updated in this profile option to go into effect, you must exit and log back in to Oracle Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_MORE_FORM_PARAMS.

Gateway User ID

Oracle login for gateway account. This should be the same as the environment variable GWYUID. For example, applsyspub/pub.

Users cannot see or update this profile option.

This profile option is visible at all levels but can only be updated at the site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is GWYUID.

Site Name

Site Name identifies an installation of Oracle Applications. The value of this profile should be set via AutoConfig.

The Site Name appears in the title of the MDI window. If you want additional information on your installation to appear in the title, for example, “Test” or “Production”, you can add that information here.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SITENAME.

Socket Listener Port

This profile option defines the port number used by the Forms Client Controller.

The default value for this profile option is ‘6945’.

The E-Business Suite Home page uses the Socket Listener Port profile for launching forms from Framework HTML sessions. With this architecture, a user navigating through different forms/responsibilities in a Framework session will reuse the same Oracle Forms session instead of opening multiple ones. So a user will never have more than one Forms session open on his/her PC at any given time, for a given database.

It is possible to have multiple Oracle Forms sessions open where each is connected to a different database, but the Socket Listener Port profile must be set to a different value beforehand on each database. For example, set it to 6945 on database A, 6946 on database B, and 6947 on database C. This profile option must be set at the site level in advance of any users attempting to use this functionality, as it cannot be set on a per-user basis.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SOCKET_LISTENER_PORT.

TCF: HOST

Set this to the name of the host running the TCF Socket Server.

This profile option is visible at all levels and updatable at the site and application level only.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:HOST.

TCF: PORT

Set this profile option to the port number at which TCF Socket Server accepts connections.

Users can see and but not update this profile option.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:PORT.

Discoverer

The internal name for this profile category is FND_DISCOVERER.

ICX: Discoverer Launcher, Forms Launcher, and Report Launcher

These profile options are used by the Oracle Applications Personal Homepage.

Set the site level value of each of these profile options to the base URL for launching each application. The profile option value should be sufficient to launch the application, but should not include any additional parameters which may be supplied by the Personal Homepage.

Users can see these profile options, but they cannot update them.

These profile options are visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for these profile options are ICX_DISCOVERER_LAUNCHER, ICX_FORMS_LAUNCHER, and ICX_REPORT_LAUNCHER.

Document Sequencing

The internal name for this profile category is FND_DOC_SEQ.

Sequential Numbering

Sequential Numbering assigns numbers to documents created by forms in Oracle financial products. For example, when you are in a form that creates invoices, each invoice document can be numbered sequentially.

Sequential numbering provides a method of checking whether documents have been posted or lost. Not all forms within an application may be selected to support sequential numbering.

Sequential Numbering has the following profile option settings:

Always Used You may not enter a document if no sequence exists for it.
Not Used You may always enter a document.
Partially Used You will be warned, but not prevented from entering a document, when no sequence exists.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Note: If you need to control Sequential Numbering for each of your set of books, use the ‘Responsibility’ level. Otherwise, we recommend that you use either the ‘Site’ or ‘Application’ level to set this option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is UNIQUE:SEQ_NUMBERS.

Flexfields

The internal name for this profile category is FND_FLEXFIELDS.

Flexfields:AutoSkip

You can save keystrokes when entering data in your flexfields by automatically skipping to the next segment as soon as you enter a complete valid value into a segment.

  • “Yes” means after entering a valid value in a segment, you automatically move to the next segment.
  • “No” means after entering a valid value in a segment, you must press [Tab] to go to the next segment.

Note: You may still be required to use tab to leave some segments if the valid value for the segment does not have the same number of characters as the segment. For example, if a segment in the flexfield holds values up to 5 characters and a valid value for the segment is 4 characters, AutoSkip will not move you to the next segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:AUTOSKIP.

Flexfields:BiDi Direction

This profile option controls the appearance of the flexfields window in Applications running in Semitic languages. Possible values are “Left To Right” and “Right To Left”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:BIDI_DIRECTION.

Flexfields:Open Descr Window

You can control whether a descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.

  • “Yes” means that the descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.
  • “No” means that when you navigate to a customized descriptive flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the descriptive flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_DESCR_WINDOW.

Note: This profile option does not apply to descriptive flexfields in folders.

Flexfields:Open Key Window

You can control whether a key flexfield window automatically opens when you navigate to a key flexfield.

  • “Yes” means that the key flexfield window automatically opens when you navigate to a key flexfield.
  • “No” means that when you navigate to a key flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the key flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_KEY_WINDOW.

Flexfields:Shorthand Entry

If shorthand flexfield entry is defined for your flexfield, you can use a shorthand alias to automatically fill in values for some or all of the segments in a flexfield.

Not Enabled Shorthand Entry is not available for any flexfields for this user, regardless of whether shorthand aliases are defined.
New Entries Only Shorthand Entry is available for entering new records in most foreign key forms. It is not available for combinations forms, updating existing records, or entering queries.
Query and New Entry Shorthand Entry is available for entering new records or for entering queries. It is not available for updating existing records.
All Entries Shorthand Entry is available for entering new records or updating old records. It is not available for entering queries.
Always Shorthand Entry is available for inserting, updating, or querying flexfields for which shorthand aliases are defined.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHORTHAND_ENTRY.

Flexfields:Show Full Value

If an alias defines valid values for all of the segments in a flexfield, and Flexfields: Shorthand Entry is enabled, when you enter the alias the flexfield window does not appear.

“Yes” displays the full flexfield window with the cursor resting on the last segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHOW_FULL_VALUE.

Flexfields:Validate On Server

This profile option is set to “Yes” to enable server side, PL/SQL flexfields validation for Key Flexfields. This improves performance when using Key Flexfields over a wide area network by reducing the number of network round trips needed to validate the entered segment combinations.

You may find, however, that your validation’s performance is better with client side validation. In this case, set this profile option to “No”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:VALIDATE_ON_SERVER.

Folders

The internal name for this profile category is FND_FOLDERS.

Folders:Allow Customization

Your system administrator controls whether you can create or customize a folder definition layout in folder block.

  • “Yes” means that you can create or customize a folder definition, that is, the entire Folder menu is enabled in the folder block.
  • “No” means that you can only open an existing folder definition in a folder block, that is, only the Open option is enabled in the Folder menu.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FLEXVIEW:CUSTOMIZATION.

Forms UI

The internal name for this profile category is FND_FORMS_UI.

Flexfields:LOV Warning Limit

Use Flexfields:LOV Warning Limit to improve efficiency when retrieving a list of values.

Sometimes, particularly when no reduction criteria has been specified, an LOV can take a very long time to run if there is a very significant amount of data in it. Set this profile option to the number of rows to be returned before the user is asked whether to continue retrieving the entire list.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is QUICKPICK_ROWS_BEFORE_WARN.

FND: Enable Cancel Query

Oracle Applications allows end users to cancel certain long-running queries, such as retrieving data in a block. When these operations exceed a threshold of time, approximately ten seconds, a dialog will display that allows the user to cancel the query.

Set the FND: Enable Cancel Query profile option to Yes if you wish to enable the ability to cancel a form query. This profile option may be set at the site, application, responsibility or the user level.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_ENABLE_CANCEL_QUERY.

FND: Indicator Colors

The default for this profile option is null, which means “Yes.” When this profile option is set to Yes:

  • Required fields are displayed in yellow.
  • Queryable fields are displayed in a different color while in enter-query mode.
  • Fields that cannot be entered (read-only) are rendered in dark gray.

Users can see and update this profile option.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FND_INDICATOR_COLORS.

Forms Keyboard Mapping File

Use this profile option to define the path of the Keyboard Mapping File.

The “Keys” window displays the keystrokes to perform standard Forms operations, such as “Next Block” and “Clear Record.” This window can be viewed at anytime by pressing Ctrl+k. The keyboard mappings can be customized as follows:

  • The System Administrator must locate the Oracle Forms resource file on the middle tier, typically called fmrweb.res.
  • Make a copy of the file, name it as desired, and locate it in the same directory as the original
  • Open the new file in any text editor and make the desired keystroke mapping changes. Comments at the top of the file explain how the mappings are performed.
  • To run the new mapping file, specify the complete path and file name in this profile option.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_FORMS_TERM.

Indicate Attachments

This profile option allows you to turn off indication of attachments when querying records (for performance reasons).

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATCHMT_SET_INDICATOR.

Java Color Scheme

If the Java Look and Feel profile option is set to Oracle, the Java Color Scheme can be specified as follows:

  • Swan (default)
  • Teal
  • Titanium
  • Red
  • Khaki
  • Blue
  • Olive
  • Purple

The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Attention: Setting the Java Color Scheme profile option to a value other than ‘swan’ (the default value) can have a considerable impact on forms user response time performance.

For some users, setting this profile option to a value other than ‘swan’ may be desirable for accessibility reasons. See: Oracle Applications Accessibility Features and “Accessibility in Oracle Forms Applications” at http://www.oracle.com/accessibility/apps02.html.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_COLOR_SCHEME.

Java Look and Feel

Oracle Applications Professional User Interface (Forms-based applications) can be run with either the Oracle Look and Feel or the Generic Look and Feel. The Oracle Look and Feel consists of a new look and feel for each item, and a predefined set of color schemes. The Generic Look and Feel adheres to the native interface and color scheme of the current operating system.

To specify the look and feel set this profile to “generic” or “oracle”.

If the Oracle Look and Feel is used, the profile Java Color Scheme can be set. The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_LOOK_AND_FEEL.

Framework Logging and Alerting

The internal name for this profile category is FND_FWK_LOGGING_ALERTING.

FND: Log Filename for Middle-Tier

The file name for the file to hold debugging messages used in the Logging Service. If the value of this profile option is null, then the Logging Service is turned off.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_FILENAME.

FND: Log Level

The Logging Service can filter out debugging messages depending on their priority level.. There are five levels of the Debug/Trace Service:. In order from highest priority to lowest priority, they are: Errors, Exceptions, Events, Procedures, and Statements. The Debug Log Level is the lowest level that the user wants to see messages for.. The possible profile option values are Null (which means off), and the five priority levels above. For instance, if the “FND: Debug Log Level” profile is set to “EVENT”, then the file will get the messages that the programmer had marked as “EVENT”, “EXCEPTION”, or “ERROR”.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_LEVEL.

FND: Log Module

The Logging Service can filter out debugging messages depending on their module. Module names are unique across applications and coding languages. If a module is specified for this profile option, then only messages for that module will be written to the log file. If this profile option is left blank then messages for all modules will be written to the log file.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_MODULE.

iHelp

The internal name for this profile category is FND_IHELP.

Applications Help Web Agent

Applications Help Web Agent is optional and should only be used if you want to launch online help on a web server different from the one specified by the Applications Servlet Agent.

Attention: For most installations, this profile should be set to NULL. Only specify a value if you want to use a different web server than that for the Applications Servlet Agent.

Specify the entire online help URL for this profile’s value:

 http://<host name of servlet agent>:<port number of servlet                agent>/OA_HTML/jsp/fnd/fndhelp.jsp?dbc=<DBC file name>

If this profile option is not set, the online help tree navigator will default to starting up at the host name and port number that is specified by the Applications Servlet Agent profile option. The DBC file used will be that of the database where online help was invoked.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_WEB_AGENT.

Help Localization Code

This code determines which localized context-sensitive help files a user accesses.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the responsibility and user levels.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_LOCALIZATION_CODE.

Help Tree Root

This profile option determines which tree is shown in the navigation frame when context-sensitive help is launched.

If Help Tree Root is set to “null” or “NULL” (case insensitive), then the online help is launched in a single frame, without the navigation and search features.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_TREE_ROOT.

Help Utility Download Path

Use this profile option to define the directory into which the Help Utility downloads help files from the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_DL_PATH.

Help Utility Upload Path

Use this profile option to define the directory from which the Help Utility uploads help files to the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_UL_PATH.

Multi Organization Setup

The internal name for this profile category is FND_MULTI_ORG.

MO:Operating Unit

In Multiple Organization installations, Oracle Applications uses the profile option MO: Operating Unit to link an operating unit to a responsibility. You must set this profile option for each responsibility. For more information on setting this profile option, see: Multiple Organizations in Oracle Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at the responsibility level only.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User No No

The internal name for this profile option is ORG_ID.

NLS

The internal name for this profile category is FND_NLS.

Currency:Mixed Precision

Use Mixed Currency Precision to specify how many spaces are available to the right of the decimal point when displaying numbers representing different currencies.

  • Normally, currency numbers are right-justified.
  • Each currency has its own precision value that is the number of digits displayed to the right of a decimal point. For U.S. dollars the precision default is 2, so an example display is 345.70.
  • Set Mixed Currency Precision to be equal to or greater than the maximum precision value of the currencies you are displaying.

For example, if you are reporting on rows displaying U.S. dollars (precision=2), Japanese yen (precision=0), and Bahraini dinar (precision=3), set Mixed Currency Precision=3.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:MIXED_PRECISION.

Currency:Negative Format

You can use different formats to identify negative currency. The default identifier is a hyphen ( – ) preceding the currency amount, as in “-xxx”. You can also select:

Angle brackets < > < xxx >

Trailing hyphen – xxx –

Parentheses ( ) ( xxx )

Square Brackets [ ] [ xxx ]

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:NEGATIVE_FORMAT.

Note: Currency:Negative Format only affects the display of currency values . Non-currency negative numbers appear with a preceding hyphen regardless of the option selected here.

Currency:Positive Format

You can use different formats to identify positive currency values. The default condition is no special identifier.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:POSITIVE_FORMAT.

Currency:Thousands Separator

You can separate your currency amounts in thousands by placing a thousands separator. For example, one million appears as 1,000,000.

Users can see and update this profile option.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:THOUSANDS_SEPARATOR.

Default Country

This is the default source for the Country field for all address zones and is used by the Flexible Address Formats feature, the Flexible Bank Structures feature and the Tax Registration Number and Taxpayer ID validation routines.

The profile can be set to any valid country listed in the Maintain Countries and Territories form and can be set to a different value for each user.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DEFAULT_COUNTRY.

FND: Native Client Encoding

FND: Native Client Encoding indicates the character set that a client machine uses as its native character set. The value must be one of the Oracle character sets and should correspond to the client native character set. The character set used in a client machine varies depending on language and platform. For example, if a user uses a Windows machine with Japanese, the value should be JA16SJIS. But if a user uses a Solaris machine with Japanese, the value should be JA16EUC. The value is normally set in the user level since each user uses different machine, but it can be set in every level for a default value.

This profile option is used when storing text data. When a user uploads text files as attachments, the current value of FND: Native Client Encoding is stored along with the text data. With the value of this profile option, the server can then convert the text data to another character set as necessary when the text data is downloaded.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_NATIVE_CLIENT_ENCODING.

ICX: Preferred Currency

This profile determines in which currency a user will see the currency number in the UI.

For example, the source currency number might be stored in database such as 10.00 as US Dollar (USD), but the displayed currency number is based on the currency set in this profile option such as 1,200 as Japanese Yen (JPY). In this multi-currency conversion, USD is source currency and JPY is the profile option value.

This profile option is for currency display purpose especially for self-service type applications.

This profile option is a generic preference that a user can set through the Oracle Application Framework Preferences page. The profile option values is used across the Oracle E-Business Suite so that the user sees currency numbers in all applications based on the currency chosen.

The currencies must be set up through the Oracle General Ledger application properly (the following must be set properly: Enabled/Disabled, Active Date and Exchange ratio between currencies). Proper setup ensures that the currency chosen is available in the system, and the currency number can be converted from the source (functional) currency to the target currency (the currency chosen by a user as this profile option value) with the specified exchange ratio. This profile option is tightly linked to GL currency setup. For more information, see:

Defining Currencies.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_PREFERRED_CURRENCY.

Server Timezone

The time zone of the database server.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SERVER_TIMEZONE_ID.

Personalization

The internal name for this profile category is FND_PERSONALIZATION.

Initialization SQL Statement – Custom

This profile option allows you to add site-specific initialization code (such as optimizer settings) that will be executed at database session initialization. The value of this profile option must be a valid SQL statement.

The system administrator may set this profile option at any level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_INIT_SQL.

Security

The internal name for this profile category is FND_SECURITY.

AuditTrail:Activate

You can turn AuditTrail on or off (Yes or No). The default setting is No (Off).

When you enter or update data in your forms, you change the database tables underlying the forms you see and use.

AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s).

  • Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
  • AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
  • All the forms that support AuditTrail are referred to as an audit set.
  • Not all forms may be enabled to support AuditTrail.
  • To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developerresponsibility.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

Enable Security Groups

This profile option is used by the Security Groups feature, which is used by HRMS security only. For more information on Security Groups, see the Oracle HRMS documentation.

The possible values are ‘None’ (N), and ‘Service Bureau’ (Y).

Only the System Administrator can update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is ENABLE_SECURITY_GROUPS.

Hide Diagnostics Menu Entry

This profile option determines whether users can access the Diagnostics menu entry from the Help menu. The default value is Yes, with the Diagnostics menu entry is hidden. If it is set to No, the Diagnostics menu entry is visible.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_HIDE_DIAGNOSTICS.

ICX: Limit time

This profile option determines the absolute maximum duration (in hours) of a user’s session, regardless of activity.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_TIME.

ICX: Session Timeout

This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.

If this profile option to 0 or NULL, then user sessions will never time out due to inactivity.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_SESSION_TIMEOUT.

Node Trust Level

Determines the level of trust assigned to a Web server. This profile option uses the Server hierarchy type. This profile option is used in conjunction with the profile option Responsibility Trust Level. For more information on using these profile options, see: Restricting Access to Responsibilities Based on User’s Web Server.

Users can see but not update this profile option.

This profile option is visible and updatable at the site and server level only.

Level Visible Allow Update
Site Yes Yes
Server Yes Yes
User No No

The internal name for this profile option is NODE_TRUST_LEVEL.

Responsibility Trust Level

Responsibilities or applications with the specified level of trust can only be accessed by an application server with at least the same level of trust.

This profile option is used in conjunction with the profile option Node Trust Level. For more information on using these profile options, see:Restricting Access to Responsibilities Based on User’s Web Server.

Users can see this profile option, but they cannot update it.

The system administrator access is described in the following table:

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is APPL_SERVER_TRUST_LEVEL.

Sign-On:Audit Level

Sign-On:Audit Level allows you to select a level at which to audit users who sign on to Oracle Applications. Four audit levels increase in functionality: None, User, Responsibility, and Form.

None is the default value, and means do not audit any users who sign on to Oracle Applications.

Auditing at the User level tracks:

  • who signs on to your system
  • the times users log on and off
  • the terminals in use

Auditing at the Responsibility level performs the User level audit functions and tracks:

  • the responsibilities users choose
  • how much time users spend using each responsibility

Auditing at the Form level performs the Responsibility level audit functions and tracks:

  • the forms users choose
  • how long users spend using each form
  • System Administrator visible, updatable at all levels.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:LEVEL.

Sign-On:Notification

“Yes” displays a message at login that indicates:

  • If any concurrent requests failed since your last session,
  • How many times someone tried to log on to Oracle Applications with your username but an incorrect password, and
  • When the default printer identified in your user profile is unregistered or not specified.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:NOTIFY.

Signon Password Case

Oracle Applications gives you the ability to control case sensitivity in user passwords through this profile option. This profile has two possible settings:

  • Sensitive – Passwords are stored and compared as they are, with the password case preserved. During validation, the entered password must match the decrypted version otherwise an error message is displayed. With Release 12, this option is the default behavior. All newly created or changed passwords are treated as case sensitive.

Note: Users who have not changed their passwords since the installation of release 12 are not affected until they do change their passwords.

A password expiration utility is available if the System Administrator requires that all users convert to case sensitive passwords upon the next login. This utility expires all passwords in FND_USER, including that of SYSADMIN and default Vision accounts, and can be run as a SQL Script ($FND_TOP/sql/AFCPEXPIRE.sql) or as a Concurrent Program (FNDCPEXPIRE_SQLPLUS).

  • Insensitive (or unset) – Passwords are treated as case insensitive. In Insensitive mode, passwords are stored and compared in uppercase, similar to that in earlier releases. During validation, the entered password and the decrypted password are compared in uppercase. If the passwords do not match, an error is displayed.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SIGNON_PASSWORD_CASE.

Signon Password Failure Limit

The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess

The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules:

  • The password contains at least one letter and at least one number.
  • The password does not contain the username.
  • The password does not contain repeating characters.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length

Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse

This profile option specifies the number of days that a user must wait before being allowed to reuse a password.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Single Sign-On Account Settings

The internal name for this profile category is FND_SSO_ACCOUNT_SETTINGS.

ICX: Client IANA Encoding

This profile option is used to determine the character set of text displayed by Java Server pages. The value is the code set of the middle tier. It is used to allow the online help system to support languages other than American English. The default setting is the Western European character set (ISO-8859-1).

This profile option should be set only at the site level.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_CLIENT_IANA_ENCODING.

Web Server Deployment

The internal name for this profile category is FND_WS_DEPLOYMENT.

Applications Servlet Agent

This profile option must be set to the URL base for the servlet execution engine on Apache. Oracle Applications uses the value of this profile option to construct URLs for JSP and SERVLET type functions. The syntax is:

 https://<hostname>:<port>/<servlet_zone>

Example:

 https://ap523sun.us.oracle.com:8888/oa_servlets

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_SERVLET_AGENT.

Applications Web Agent

Provides the base URL for the Apps Schema’s WebServer DAD. You set this profile option during the install process.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is APPS_WEB_AGENT.

 

Oracle Apps 11i – Profiles

Published on by AdvaitLeave a comment

Profile Options in Oracle Application Object Library

This section lists each profile option in Oracle Application Object Library. These profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile’s setting.

Unless otherwise noted, a profile option uses the Security hierarchy type.

A table is provided for most profile options that lists the access levels for the profile option. For Security profile options, there are four possible levels at which system administrators can view and update a profile option value: site, application, responsibility, and user. This table lists whether the profile option’s value is visible at each of these levels, and whether it is updatable at each level.

Account Generator:Debug Mode

This profile option controls Oracle Workflow process modes for the Account Generator feature. This profile option should normally be set to “No” to improve performance. If you are testing your Account Generator implementation and using the Oracle Workflow Monitor to see your results, set this profile option to “Yes”.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ACCOUNT_GENERATOR:DEBUG_MODE.

Applications Help Web Agent

Applications Help Web Agent is optional and should only be used if you want to launch online help on a web server different from the one specified by the Applications Servlet Agent.

Attention: For most installations, this profile should be set to NULL. Only specify a value if you want to use a different web server than that for the Applications Servlet Agent.

Specify the entire online help URL for this profile’s value:

http://<host name of servlet agent>:<port number of servlet agent>/
OA_HTML/jsp/fnd/fndhelp.jsp?dbc=<DBC file name>

This new usage of HELP_WEB_AGENT provides one with the flexibility of reverting back to the previous Release 11i applet version of the tree navigator if desired. To do this, set this profile option to

http://<host name of PL/SQL agent>[:<portnumber of PL/SQL agent>]/<PL/SQL agent name>/
fnd_help.launch?par_root=

This is usually identical to the Applications Web Agent profile option but with the string “/fnd_help.launch?par_root=” appended at the end.

If this profile option is not set, the online help tree navigator will default to starting up at the host name and port number that is specified by the Applications Servlet Agent profile option. The DBC file used will be that of the database where online help was invoked.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_WEB_AGENT.

Applications Servlet Agent

This profile option must be set to the URL base for the servlet execution engine on Apache. Oracle Applications uses the value of this profile option to construct URLs for JSP and SERVLET type functions. The syntax is:

https://<hostname>:<port>/<servlet_zone>

Example:

https://ap523sun.us.oracle.com:8888/oa_servlets

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_SERVLET_AGENT.

Applications Web Agent

Provides the base URL for the Apps Schema’s WebServer DAD. You set this profile option during the install process.

This profile option must be set to the URL which identifies the mod_plsql PL/SQL Gateway Database Access Descriptor base URL for your Applications instance. Oracle Applications use the value of this profile option to construct URLs for ‘WWW’ type functions, Attachments, Export, and other features.

Use the following syntax to enter your URL:

        https://<hostname>:<port>/pls/<dad_name>

Users can see but not update this profile option.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_WEB_AGENT.

Applications Web Authentication Server

The web server used for authentication for Oracle Self-Service Web Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is WEB_AUTHENTICATION_SERVER.

Attachment File Directory

The directory in which file type attachments data is stored. You set this profile option during the install process.

Users can see but not update this profile option.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATTACHMENT_FILE_DIRECTORY.

AuditTrail:Activate

You can turn AuditTrail on or off (Yes or No). The default setting is No (Off).

When you enter or update data in your forms, you change the database tables underlying the forms you see and use.

AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s).

  • Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
  • AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
  • All the forms that support AuditTrail are referred to as an audit set.
  • Not all forms may be enabled to support AuditTrail.
  • To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developerresponsibility.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

BIS/AOL:Debug Log Directory

The directory for BIS debugging log files.

Users can see and change this profile option.

System administrators can see and update this profile option at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is BIS_DEBUG_LOG_DIRECTORY.

Concurrent:Active Request Limit

You can limit the number of requests that may be run simultaneously by each user. or for every user at a site. If you do not specify a limit, no limit is imposed.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes Yes

The internal name for this profile option is CONC_REQUEST_LIMIT.

Concurrent:Attach URL

Setting this option to “Yes” causes a URL to be attached to request completion notifications. When a user submits a request, and specifies people to be notified in the Defining Completion Options region, everyone specified is sent a notification when the request completes. If this profile option is set to Yes, a URL is appended to the notification that enables them to view the request results on-line.

Only the System Administrator can update this profile option.

Users can see but not update this profile option.

This profile options is visible at all levels but can only updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_ATTACH_URL.

Concurrent:Conflicts Domain

Specify a conflict domain for your data. A conflict domain identifies the data where two incompatible programs cannot run simultaneously.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_CD_ID.

Concurrent:Collect Request Statistics

Set this profile option to “Yes” to have statistics for your runtime concurrent processes collected.

To review the statistics you must run the Purge Concurrent Request and/or Manager Data program to process the raw data and have it write the computed statistics to the FND_CONC_STAT_SUMMARY table. You can then retrieve your data from this table using SQL*PLUS or on a report by report basis using the Diagnostics window from the Requests window.

Users cannot see nor change this profile option.

This profile option is visible at all levels bu can only be updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_REQUEST_STAT.

Concurrent:Debug Flags

Your Oracle support representative may access this profile option to debug Transaction Managers. Otherwise, it should be set to null.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_DEBUG.

Concurrent:Hold Requests

You can automatically place your concurrent requests on hold when you submit them.

The default is “No”. The concurrent managers run your requests according to the priority and start time specified for each.

Changing this value does not affect requests you have already submitted.

“Yes” means your concurrent requests and reports are automatically placed on hold. To take requests off hold, you:

  • Navigate to the Requests window to select a request
  • Select the Request Control tabbed region
  • Uncheck the Hold check box

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_HOLD.

Concurrent:Multiple Time Zones

“Yes” sets the default value to ‘Sysdate-1’ for the ‘Schedules Start Date’ used by request submissions. Sysdate-1 ensures that you request is scheduled immediately regardless of which time zone your client session is running in. You should use this profile option when the client’s session is running in a different time zone than the concurrent manager’s session.

Users cannot see nor change this profile option.

This profile option is visible at all four levels and updatable at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_MULTI_TZ.

Concurrent:PMON method

PMON refers to process monitor. The Internal Concurrent Manager monitors the individual concurrent managers’ processes to verify the managers are running.

This profile option should always be set to LOCK. Using the LOCK method, the Internal Concurrent Manager tries to get a lock on the individual concurrent manager’s process. The name of the lock is determined by a sequence (which is the ID of the individual manager) and the program in question. If the Internal Concurrent Manager is able to get the lock, then it knows the process is no longer running.

Users cannot see nor change this profile option.

This profile option is neither visible nor updatable from the System Profile Options form.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_PMON_METHOD.

Concurrent:Report Access Level

Determines access privileges to report output files and log files generated by a concurrent program. This profile option can be set by a System Administrator to User or Responsibility.

If your Concurrent:Report Access Level profile option is set to “User” you may:

  • View the completed report output for your requests online
  • View the diagnostic log file for those requests online. (system administrator also has this privilege)
  • Reprint your completed reports, if the Concurrent:Save Output profile option is set to “Yes”.
  • If you change responsibilities, then the reports and log files available for online review do not change.

If your Concurrent:Report Access Level profile option is set to “Responsibility”, access to reports and diagnostic log files is based on the your current responsibility.

  • If you change responsibilities, then the reports and log files available for online review change to match your new responsibility. You can always see the output and log files from reports you personally submit, but you also see reports and log files submitted by any user from the current responsibility.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, responsibility, and user levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REPORT_ACCESS_LEVEL.

Concurrent:Report Copies

You can set the number of output copies that print for each concurrent request. The default is set to 1.

  • Changing this value does not affect requests that you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_COPIES.

Concurrent:Request Priority

This displays the default priority number for your concurrent requests. Only a system administrator can change your request priority.

Requests normally run according to start time, on a “first-submitted, first-run” basis. Priority overrides request start time. A higher priority request starts before an earlier request.

Priorities range from 1 (highest) to 99 (lowest). The standard default is 50.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRIORITY.

Concurrent:Request Start Time

You can set the date and time that your requests are available to start running.

  • If the start date and time is at or before the current date and time, requests are available to run immediately.
  • If you want to start a request in the future, for example, at 3:45 pm on June 12, 2002, you enter 2002/06/12 15:45:00 as the profile option value.

Attention: You must ensure that this value is in canonical format (YYYY/MM/DD HH24:MI:SS) to use the Multilingual Concurrent Request feature.

  • You must include both a date and a time.
  • Changing this value does not affect requests that you have already submitted.
  • Users can override the start time when they submit requests. Or, this profile option can be left blank and users will be prompted for a start time when they submit requests.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_START.

Concurrent:Save Output

The Concurrent: Save Output profile is used to determine whether the default behavior of certain concurrent programs should be to save or delete their output files. This only affects concurrent programs that were created in the character mode versions of Oracle Applications and that have a null value for “Save Output”.

  • “Yes” saves request outputs.
  • Some concurrent requests do not generate an output file.
  • If your request output is saved, you can reprint a request. This is useful when requests complete with an Error status, for example, the request runs successfully but a printer malfunctions.
  • Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SAVE_OUTPUT.

Concurrent:Sequential Requests

You can force your requests to run one at a time (sequentially) according to the requests’ start dates and times, or allow them to run concurrently, when their programs are compatible.

  • Concurrent programs are incompatible if simultaneously accessing the same database tables incorrectly affects the values each program retrieves.
  • When concurrent programs are defined as incompatible with one another, they cannot run at the same time.

“Yes” prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.

“No” means your requests can run concurrently when their concurrent programs are compatible.

Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SINGLE_THREAD.

Concurrent: Show Requests Summary After Each Request Submission

Using this new profile option, you can choose to either have the Requests Summary displayed each time you submit a request, or retain the request submission screen.

The default is “Yes”. “Yes” means the Requests Summary screen is displayed each time you submit a request.

If you choose “No”, a decision window is opened asking you if you wish to submit another request. When you choose to submit another request you are returned to the submission window and the window is not cleared, allowing you to easily submit copies of the same request with minor changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_SUMMARY.

Concurrent:Wait for Available TM

You can specify the maximum number of seconds that the client will wait for a given transaction manager (TM) to become available before moving on to try a different TM.

Users can see and update this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is CONC_TOKEN_TIMEOUT.

Concurrent:URL Lifetime

The numeric value you enter for this profile option determines the length of time in minutes a URL for a request ouput is maintained. After this time period the URL will be deleted from the system. This profile option only affects URLs created for requests where the user has entered values in the notify field of the Submit Request or Submit Request Set windows.

Attention: All request ouput URLs are deleted when the Pruge Concurrent Requests and Manager… program is run even if the URL liftime has not expired.

Users can see and update this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_URL_LIFETIME.

Currency:Mixed Precision

Use Mixed Currency Precision to specify how many spaces are available to the right of the decimal point when displaying numbers representing different currencies.

  • Normally, currency numbers are right-justified.
  • Each currency has its own precision value that is the number of digits displayed to the right of a decimal point. For U.S. dollars the precision default is 2, so an example display is 345.70.
  • Set Mixed Currency Precision to be equal to or greater than the maximum
  • precision value of the currencies you are displaying.

For example, if you are reporting on rows displaying U.S. dollars (precision=2), Japanese yen (precision=0), and Bahraini dinar (precision=3), set Mixed Currency Precision=3.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:MIXED_PRECISION.

Currency:Negative Format

You can use different formats to identify negative currency. The default identifier is a hyphen ( – ) preceding the currency amount, as in “-xxx”. You can also select:

Angle brackets < > < xxx >

Trailing hyphen – xxx –

Parentheses ( ) ( xxx )

Square Brackets [ ] [ xxx ]

If you use the negative number formats of “(xxx)” or “[xxx],” in Oracle Applications Release 11, your negative numbers appear as “<xxx>”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:NEGATIVE_FORMAT.

Note: Currency:Negative Format only affects the display currency. Non-currency negative numbers appear with a preceding hyphen regardless of the option selected here.

Currency:Positive Format

You can use different formats to identify positive currency values. The default condition is no special identifier.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:POSITIVE_FORMAT.

Currency:Thousands Separator

You can separate your currency amounts in thousands by placing a thousands separator. For example, one million appears as 1,000,000.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:THOUSANDS_SEPARATOR.

Database Instance

Entering a valid two_task connect string allows you to override the default two_task. This profile is specifically designed for use with Oracle Parallel Server, to allow different responsibilities and users to connect to different nodes of the server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is INSTANCE_PATH.

Default Country

This is the default source for the Country field for all address zones and is used by the Flexible Address Formats feature, the Flexible Bank Structures feature and the Tax Registration Number and Taxpayer ID validation routines.

The profile can be set to any valid country listed in the Maintain Countries and Territories form and can be set to a different value for each user.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DEFAULT_COUNTRY.

Enable Security Groups

This profile option is used by the Security Groups feature, which is used by HRMS security only.

The possible values are ‘None’ (N), ‘Hosted’ (HOSTED), and ‘Service Bureau’ (Y).

Only the System Administrator can update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is ENABLE_SECURITY_GROUPS.

Flexfields:AutoSkip

You can save keystrokes when entering data in your flexfields by automatically skipping to the next segment as soon as you enter a complete valid value into a segment.

  • “Yes” means after entering a valid value in a segment, you automatically move to the next segment.
  • “No” means after entering a valid value in a segment, you must press [Tab] to go to the next segment.

Note: You may still be required to use tab to leave some segments if the valid value for the segment does not have the same number of characters as the segment. For example, if a segment in the flexfield holds values up to 5 characters and a valid value for the segment is 4 characters, AutoSkip will not move you to the next segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:AUTOSKIP.

Flexfields:BiDi Direction

This profile option controls the appearance of the flexfields window in Applications running in Semitic languages. Possible values are “Left To Right” and “Right To Left”. If the profile option is not defined on a particular installation, the default value is “Right To Left”, where the window appears in a normal, left to right fashion, and the text and layout are reversed to accommodate the right-to-left nature of the Semitic language environment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:BIDI_DIRECTION.

Flexfields:LOV Warning Limit

Use Flexfields:LOV Warning Limit to improve efficiency when retrieving a list of values.

Sometimes, particularly when no reduction criteria has been specified, an LOV can take a very long time to run if there is a very significant amount of data in it. Set this profile option to the number of rows to be returned before the user is asked whether to continue retrieving the entire list.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is QUICKPICK_ROWS_BEFORE_WARN.

Flexfields:Open Descr Window

You can control whether a descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.

  • “Yes” means that the descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.
  • “No” means that when you navigate to a customized descriptive flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the descriptive flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_DESCR_WINDOW.

Note: This profile option does not apply to descriptive flexfields in folders.

Flexfields:Open Key Window

You can control whether a key flexfield window automatically opens when you navigate to a key flexfield.

  • “Yes” means that the key flexfield window automatically opens when you navigate to a key flexfield.
  • “No” means that when you navigate to a key flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the key flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_KEY_WINDOW.

Flexfields:Shared Table Lock

This profile option is used by the Oracle Applications flexfields internal code only. You should not alter the value of this profile option unless instructed to do so by an Oracle representative.

The internal name for this profile option is FLEXFIELDS:SHARED_TABLE_LOCK.

Flexfields:Shorthand Entry

If shorthand flexfield entry is defined for your flexfield, you can use a shorthand alias to automatically fill in values for some or all of the segments in a flexfield.

Not Enabled Shorthand Entry is not available for any flexfields for this user, regardless of whether shorthand aliases are defined.
New Entries Only Shorthand Entry is available for entering new records in most foreign key forms. It is not available for combinations forms, updating existing records, or entering queries.
Query and New Entry Shorthand Entry is available for entering new records or for entering queries. It is not available for updating existing records.
All Entries Shorthand Entry is available for entering new records or updating old records. It is not available for entering queries.
Always Shorthand Entry is available for inserting, updating, or querying flexfields for which shorthand aliases are defined.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHORTHAND_ENTRY.

Flexfields:Show Full Value

If an alias defines valid values for all of the segments in a flexfield, and Flexfields: Shorthand Entry is enabled, when you enter the alias the flexfield window does not appear.

“Yes” displays the full flexfield window with the cursor resting on the last segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHOW_FULL_VALUE.

Flexfields:Validate On Server

This profile option is set to “Yes” to enable server side, PL/SQL flexfields validation for Key Flexfields. This improves performance when using Key Flexfields over a wide area network by reducing the number of network round trips needed to validate the entered segment combinations.

You may find, however, that your validation’s performance is better with client side validation. In this case, set this profile option to “No”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:VALIDATE_ON_SERVER.

FND: Debug Log Filename

The file name for the file to hold debugging messages used in the Logging Service. If the value of this profile option is null, then the Logging Service is turned off.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_FILENAME.

FND: Debug Log Level

The Logging Service can filter out debugging messages depending on their priority level.. There are five levels of the Debug/Trace Service:. In order from highest priority to lowest priority, they are: Errors, Exceptions, Events, Procedures, and Statements. The Debug Log Level is the lowest level that the user wants to see messages for.. The possible profile option values are Null (which means off), and the five priority levels above. For instance, if the “FND: Debug Log Level” profile is set to “EVENT”, then the file will get the messages that the programmer had marked as “EVENT”, “EXCEPTION”, or “ERROR”.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_LEVEL.

FND: Debug Log Module

The Logging Service can filter out debugging messages depending on their module. Module names are unique across applications and coding languages. If a module is specified for this profile option, then only messages for that module will be written to the log file. If this profile option is left blank then messages for all modules will be written to the log file.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_MODULE.

FND: Enable Cancel Query

Oracle Applications allows end users to cancel certain long-running queries, such as retrieving data in a block. When these operations exceed a threshold of time, approximately ten seconds, a dialog will display that allows the user to cancel the query.

Set the FND: Enable Cancel Query profile option to Yes if you wish to enable the ability to cancel a form query. This profile option may be set at the site, application, responsibility or the user level.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_ENABLE_CANCEL_QUERY.

FND: Indicator Colors

The default for this profile option is null, which means “Yes.” When this profile option is set to Yes:

  • Required fields are displayed in yellow.
  • Queryable fields are displayed in a different color while in enter-query mode.
  • Fields that cannot be entered (read-only) are rendered in dark gray.

Users can see and update this profile option.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FND_INDICATOR_COLORS.

FND: Native Client Encoding

FND: Native Client Encoding indicates the character set that a client machine uses as its native character set. The value must be one of the Oracle character sets and should correspond to the client native character set. The character set used in a client machine varies depending on language and platform. For example, if a user uses a Windows machine with Japanese, the value should be JA16SJIS. But if a user uses a Solaris machine with Japanese, the value should be JA16EUC. The value is normally set in the user level since each user uses different machine, but it can be set in every level for a default value.

This profile option is used when storing text data. When a user uploads a text file to be stored in the FND_LOBS table, the current value of FND: Native Client Encoding is stored along with the text data. With the value of this profile option, the server can then convert the text data to another character set as necessary when the text data is downloaded.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_NATIVE_CLIENT_ENCODING.

FND: Override Directory

The FND:Override Directory profile option is used by the Work Directory feature. The value of FND: Override Directory should be the directory containing your alternate files. Typically, this profile option should be set at the User level only.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLWRK.

FND: Resource Consumer Group

Resource consumer groups are used by the Oracle8i Database Resource Manager, which allocates CPU resources among database users and applications. Each form session is assigned to a resource consumer group. The system administrator can assign users to a resource consumer group for all of their forms sessions and transactions. If no resource consumer group is found for a process, the system uses the default group “Default_Consumer_Group”.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_RESOURCE_CONSUMER_GROUP.

Folders:Allow Customization

Your system administrator controls whether you can create or customize a folder definition layout in folder block.

  • “Yes” means that you can create or customize a folder definition, that is, the entire Folder menu is enabled in the folder block.
  • “No” means that you can only open an existing folder definition in a folder block, that is, only the Open option is enabled in the Folder menu.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FLEXVIEW:CUSTOMIZATION.

Forms Keyboard Mapping File

Use this profile option to define the path of the Keyboard Mapping File.

The “Keys” window displays the keystrokes to perform standard Forms operations, such as “Next Block” and “Clear Record.” This window can be viewed at anytime by pressing Ctrl+k. The keyboard mappings can be customized as follows:

  • The System Administrator must locate the Oracle Forms resource file on the middle tier, typically called fmrweb.res.
  • Make a copy of the file, name it as desired, and locate it in the same directory as the original.
  • Open the new file in any text editor and make the desired keystroke mapping changes. Comments at the top of the file explain how the mappings are performed.
  • To run the new mapping file, specify the complete path and file name in this profile option.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_FORMS_TERM.

Forms Runtime Parameters

Use this profile to specify certain forms runtime parameters. The profile value must be entered in as parameter=value. Each parameter-value pair must be separated by a single space. For example:

record=collect log=/tmp/frd.log debug_messages=yes

In order for the parameters updated in this profile option to go into effect, you must exit and log back in to Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_MORE_FORM_PARAMS.

Gateway User ID

Oracle login for gateway account. This should be the same as the environment variable GWYUID. For example, applsyspub/pub.

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at the site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is GWYUID.

Help Localization Code

Localized context-sensitive help files are preferred when your System Administrator sets this profile option.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the responsibility and user levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_LOCALIZATION_CODE.

Help Tree Root

This profile option determines which tree is shown in the navigation frame when context-sensitive help is launched.

If Help Tree Root is set to “null” or “NULL” (case insensitive), then the online help is launched in a single frame, without the navigation and search features.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_TREE_ROOT.

Help Utility Download Path

Use this profile option to define the directory into which the Help Utility downloads help files from the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_DL_PATH.

Help Utility Upload Path

Use this profile option to define the directory from which the Help Utility uploads help files to the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_UL_PATH.

Hide Diagnostics Menu Entry

This profile option determines whether users can access the Diagnostics menu entry from the Help menu. If it is set to Yes, the Diagnostics menu entry is hidden. If it is set to No, the Diagnostics menu entry is visible.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_HIDE_DIAGNOSTICS.

ICX: Client IANA Encoding

This profile option is used to determine the character set of text displayed by Java Server pages. This profile option must be set to match the character set of the Apache server on the Web tier in order for the online help system to support languages other than American English. The default setting is the Western European character set (ISO-8859-1).

This profile option should be set only at the site level.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_CLIENT_IANA_ENCODING.

ICX: Discoverer Launcher, Forms Launcher, and Report Launcher

These profile options are used by the Oracle Applications Personal Homepage.

Set the site level value of each of these profile options to the base URL for launching each application. The profile option value should be sufficient to launch the application, but should not include any additional parameters which may be supplied by the Personal Homepage.

Users can see these profile options, but they cannot update them.

These profile options are visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for these profile options are ICX_DISCOVERER_LAUNCHER, ICX_FORMS_LAUNCHER, and ICX_REPORT_LAUNCHER.

ICX: Limit connect

This profile option determines the maximum number of connection requests a user can make in a single session.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_CONNECT.

ICX: Limit time

This profile option determines the absolute maximum duration (in hours) of a user’s session, regardless of activity.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_TIME.

ICX: Session Timeout

This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.

If this profile option to 0 or NULL, then user sessions will never time out due to inactivity.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_SESSION_TIMEOUT.

Indicate Attachments

This profile option allows you to turn off indication of attachments when querying records (for performance reasons).

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATCHMT_SET_INDICATOR.

Initialization SQL Statement – Custom

This profile option allows you to add site-specific initialization code (such as optimizer settings) that will be executed at database session startup. The value of this profile option must be a valid SQL statement.

The system administrator may set this profile option at any level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_INIT_SQL.

Initialization SQL Statement – Oracle

This profile option is used to add application-specific code. The value is a valid SQL statement (or a PL/SQL block for more than one statement), that is executed at startup of every database session.

The value of this profile option is delivered as seed data and cannot be updated.

Attention: Do not attempt to modify the value of this profile option. Use the profile option Initialization SQL Statement – Custom to add custom initialization code.

This profile option is set at the application level only. The initialization code will be executed only for responsibilities owned by that application.

Level Visible Allow Update
Site Yes No
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is FND_APPS_INIT_SQL.

Java Color Scheme

If the Java Look and Feel profile option is set to Oracle, the Java Color Scheme can be specified as follows:

  • Teal
  • Titanium
  • Red
  • Khaki
  • Blue
  • Olive
  • Purple

The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_COLOR_SCHEME.

Java Look and Feel

Oracle Applications Professional User Interface can be run with either the Oracle Look and Feel or the Generic Look and Feel. The Oracle Look and Feel consists of a new look and feel for each item, and a predefined set of color schemes. The Generic Look and Feel adheres to the native interface and color scheme of the current operating system.

To specify the look and feel set this profile to “generic” or “oracle”.

If the Oracle Look and Feel is used, the profile Java Color Scheme can be set. The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_LOOK_AND_FEEL.

Maximum Page Length

Determines the maximum number of lines per page in a report.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is MAX_PAGE_LENGTH.

MO:Operating Unit

Determines the Operating Unit the responsibility logs onto.

Users can see and update this profile option.

This profile option is visible and updatable at the responsibility level only.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User No No

The internal name for this profile option is ORG_ID.

Node Trust Level

Determines the level of trust assigned to a Web server. This profile option uses the Server hierarchy type.

Users can see but not update this profile option.

This profile option is visible and updatable at the site and server level only.

Level Visible Allow Update
Site Yes Yes
Application NA NA
Responsibility NA NA
Server Yes Yes
User No No

The internal name for this profile option is NODE_TRUST_LEVEL.

Personnel Employee:Installed

When enabled, “Personnel Employee:Installed” allows you as System Administrator to link an application username and password to an employee name.

  • The “Person” field is usable on the Define Application User form (\ Navigate Security User).

Oracle Purchasing uses this capability to associate an employee in your organization with an Oracle Applications user.

The installation process enables this profile option. You cannot change the value of “Personnel Employee: Installed”.

Users cannot see nor change this profile option.

This profile option is visible at the site level, but cannot be updated.

Level Visible Allow Update
Site Yes No
Application No No
Responsibility No No
User No No

The internal name for this profile option is PER_EMPLOYEE:INSTALLED.

Printer

You can select the printer which prints your reports. If a printer cannot be selected, contact your system administrator. Printers must be registered with Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is PRINTER.

RRA:Delete Temporary Files

When using a custom editor to view a concurrent output or log file, the Report Review Agent will make a temporary copy of the file on the client. Set this profile to “Yes” to automatically delete these files when the user exits Oracle Applications.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_DELETE.

RRA:Enabled

Set this user profile to “Yes” to use the Report Review Agent to access files on concurrent processing nodes.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_ENABLED.

RRA: Service Prefix

Using this new profile option allows you to override the default service name prefix (FNDFS_) assigned to the Report Review Agent. By assigning a new prefix to the Report Review Agent you can avoid having multiple instances of the Applications share executables.

Valid values for this option must be nine characters or less and use only alphanumeric characters or the underscore. We recommend using the underscore character as the last character of your value as in the default value “FNDFS_”.

Users cannot see or update this profile option.

This profile option is visible and updatable at the site level only.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is FS_SVC_PREFIX.

Attention: GLDI will not support the “RRA: Service Prefix” profile until release 4.0 and so uses the default prefix “FNDFS_” regardless of the value entered for the profile option. Consequently, you must ensure that at least one of your Report Review Agents maintains the default prefix in order for GLDI to access the application executables.

RRA:Maximum Transfer Size

Specify, in bytes, the maximum allowable size of files transferred by the Report Review Agent, including those downloaded by a user with the “Copy File…” menu option in the Oracle Applications Report File Viewer and those “temporary” files which are automatically downloaded by custom editors. For example, to set the size to 64K you enter 65536. If this profile is null, there is no size limit.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_MAX_TRANS.

Self Service Personal Home Page Mode

This profile option determines the type of home page for users. The three possible values are: “Framework only”,” Personal Home Page”, and “Personal Home Page with Framework”.

Framework only The E-Business Suite Home page is used.
Personal Home Page The Personal Homepage is used.
Personal Home Page with Framework The Personal Homepage appears first when a user logs in. After the user chooses a responsibility, an Oracle Applications Framework page appears for navigation among the functions for that responsibility.

After this profile option is set, you need to bounce the middle tier server to clear its cache and to see your changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLICATIONS_HOME_PAGE.

Sequential Numbering

Sequential Numbering assigns numbers to documents created by forms in Oracle financial products. For example, when you are in a form that creates invoices, each invoice document can be numbered sequentially.

Sequential numbering provides a method of checking whether documents have been posted or lost. Not all forms within an application may be selected to support sequential numbering.

Sequential Numbering has the following profile option settings:

Always Used You may not enter a document if no sequence exists for it.
Not Used You may always enter a document.
Partially Used You will be warned, but not prevented from entering a document, when no sequence exists.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Note: If you need to control Sequential Numbering for each of your set of books, use the ‘Responsibility’ level. Otherwise, we recommend that you use either the ‘Site’ or ‘Application’ level to set this option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is UNIQUE:SEQ_NUMBERS.

Server Timezone

The time zone of the database server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is SERVER_TIMEZONE_ID.

Session ID

This runtime profile option contains the session ID number of the last database session that was created.

Users can see this profile option, but they cannot update it.

This profile option is neither visible nor updatable from the System Profile Options form.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User No No

The internal name for this profile option is DB_SESSION_ID.

Sign-On:Audit Level

Sign-On:Audit Level allows you to select a level at which to audit users who sign on to Oracle Applications. Four audit levels increase in functionality: None, User, Responsibility, and Form.

None is the default value, and means do not audit any users who sign on to Oracle Applications.

Auditing at the User level tracks:

  • who signs on to your system
  • the times users log on and off
  • the terminals in use

Auditing at the Responsibility level performs the User level audit functions and tracks:

  • the responsibilities users choose
  • how much time users spend using each responsibility

Auditing at the Form level performs the Responsibility level audit functions and tracks:

  • the forms users choose
  • how long users spend using each form
  • System Administrator visible, updatable at all levels.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:LEVEL.

Sign-On:Notification

“Yes” displays a message at login that indicates:

  • If any concurrent requests failed since your last session,
  • How many times someone tried to log on to Oracle Applications with your username but an incorrect password, and
  • When the default printer identified in your user profile is unregistered or not specified.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:NOTIFY.

Signon Password Failure Limit

The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess

The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules:

  • The password contains at least one letter and at least one number.
  • The password does not contain the username.
  • The password does not contain repeating characters.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length

Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse

This profile option specifies the number of days that a user must wait before being allowed to reuse a password.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Site Name

Site Name identifies an installation of Oracle Applications. The installation process sets this to “No Site Name Specified”.

You should set a value for “Site Name” after installation.

The Site Name appears in the title of the MDI window. If you want additional information on your installation to appear in the title, for example, “Test” or “Production”, you can add that information here

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SITENAME.

Socket Listener Activated

This profile option is a flag that indicates whether the FormsClient Controller (Socket Listener) should be started by the signon form.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SOCKET_LISTENER_ACTIVATED.

Socket Listener Port

This profile option defines the port number used by the Forms Client Controller.

The default value for this profile option is ‘6945’.

Oracle Workflow uses this profile option. When a user chooses to launch a form from a Workflow notification, Oracle Workflow will look for the value of this profile option and launch the form in the specified port.

If the socket listener port is not set at user level, Oracle Workflow launches attached forms at the default port set for the site. However, if users have set different ports, Oracle Workflow launches the forms for each user at the specified port. By using different socket listener ports, two different users logged into Oracle Applications on the same machine can both launch attached forms at the same time without interference from each other.

The E-Business Suite Home page also uses the Socket Listener Port profile for launching forms from Framework HTML sessions. With this architecture, a user navigating through different forms/responsibilities in a Framework session will reuse the same Oracle Forms session instead of opening multiple ones. So a user will never have more than one Forms session open on his/her PC at any given time, for a given database.

It is possible to have multiple Oracle Forms sessions open where each is connected to a different database, but the Socket Listener Port profile must be set to a different value beforehand on each database. For example, set it to 6945 on database A, 6946 on database B, and 6947 on database C. This profile option must be set at the site level in advance of any users attempting to use this functionality, as it cannot be set on a per-user basis.

Users can see but not update this profile option.

This profile option is visible and updatable at the site, application, and responsibility levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is SOCKET_LISTENER_PORT.

Stored Procedure Log Directory

Specifying a log directory enables stored procedures used with the Oracle database to generate and store log files. You must also set this log directory in the init.ora file of the database.

For example, if the Stored Procedure Log Directory is /rladev/rla/1.1/log and the Stored Procedure Output Directory is /rladev/rla/1.1/out, then the following entry should be made in the init.ora file of the database containing stored procedures that write to these directories:

UTL_FILE_DIR = /rladev/rla/1.1/log,/rladev/rla/1.1/out

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is UTL_FILE_LOG.

Stored Procedure Output Directory

Specifying a output directory enables stored procedures used with the Oracle database to generate and store output files. You must also set this output directory in the init.ora file of the database.

For example, if the Stored Procedure Log Directory is /rladev/rla/1.1/log and the Stored Procedure Output Directory is /rladev/rla/1.1/out, then the following entry should be made in the init.ora file of the database containing stored procedures that write to these directories:

UTL_FILE_DIR = /rladev/rla/1.1/log,/rladev/rla/1.1/out

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is UTL_FILE_OUT.

TCF: HOST

Together with the TCF:PORT profile, this profile identifies the network location of the TCF Server. The TCF Server supports various parts of the Oracle Applications UI by executing some of their associated server logic and providing access to the database.

In most configurations, these profiles will be set by the TCF Server’s administrative utility ‘ServerControl’ at the same time the TCF Server is started up. ServerControl will set these two profiles (TCF:HOST, TCF:PORT) at the site level.

For particularly complex environments, it may be appropriate to direct different users to separate TCF Servers by setting these profiles to distinct values at the Application level. Consult the post installation instructions for details on TCF Server configuration options.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:HOST.

See: Administering the TCF Server

TCF: PORT

Together with the TCF:HOST profile, this profile identifies the network location of the TCF Server. The TCF Server supports various parts of the Oracle Applications UI by executing some of their associated server logic and providing access to the database.

In most configurations, these profiles will be set by the TCF Server’s administrative utility ‘ServerControl’ at the same time the TCF Server is started up. ServerControl will set these two profiles (TCF:HOST, TCF:PORT) at the site level.

For particularly complex environments, it may be appropriate to direct different users to separate TCF Servers by setting these profiles to distinct values at the Application level. Consult Installing Oracle Applications for details on the TCF Server configuration options.

Users can see and but not update this profile option.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:PORT.

See: Administering the TCF Server

Two Task

TWO_TASK for the database. This profile is used in conjunction with the Gateway User ID profile to construct a connect string for use in creating dynamic URLs for the Web Server. This should be set to the SQL*NET. alias for the database.

Note: The TWO_TASK must be valid on the node upon which the WebServer is running

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is TWO_TASK.

Utilities: Diagnostics

Utilities: Diagnostics determines whether a user can automatically use the Diagnostics features. If Utilities:Diagnostics is set to Yes, then users can automatically use these features. If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema to use the Diagnostics features.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DIAGNOSTICS.

Utilities:SQL Trace

SQL trace files can now be generated for individual concurrent programs. The trace can be enabled at the user level by setting the profile “Utilities:SQL Trace” to “Yes”. This profile can be enabled for a user only by System Administrator so that it is not accidentally turned on and disk usage can be monitored.

For more information on SQL trace, see the Oracle database documentation.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

Viewer: Application for HTML, PCL, PDF, Postscript, and Text

These profile options determine the applications a user will use to view reports in the given output formats. For example, you could set Viewer: Application for Text to ‘application/word’ to view a Text report in Microsoft Word.

Valid values are defined by the system administrator in the Viewer Options form.

Users can see and update these profile options.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal names for these profile options are FS_MIME_HTML, FS_MIME_PCL, FS_MIME_PDF, FS_MIME_PS, and FS_MIME_TEXT.

Viewer:Default Font Size

Using this new profile option, you can set the default font size used when you display report output in the Report Viewer.

The valid values for this option are 6, 8, 10, 12, and 14.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FNDCPVWR_FONT_SIZE..

Viewer: Text

The Viewer: Text profile option allows you to send report output directly to a browser window rather than using the default Report Viewer. Enter “Browser” in this profile option to enable this feature.

Users can see and update the Viewer:Text profile option.

This profile option is both visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Ys

The internal name for this profile option is EDITOR_CHAR.

 

Row Chaining and Migeration

Published on by AdvaitLeave a comment

Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database. Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are: What is Row Migration & Row Chaining ? How to identify Row Migration & Row Chaining ? How to avoid Row Migration & Row Chaining ? Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Oracle Block

The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the «Data Base Block Size» as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this «Oracle block», and not the OS block. Once set, the «Data Base Block Size» cannot be changed during the life of the database (except in case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected. The database block has the following structure (within the whole database structure)

oracle_data_block

Header

Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It Also contains the information about table and the actual row (address) which that holds the data.

Free Space

Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSED parameters.

Data

Actual row data.

FREELIST, PCTFREE and PCTUSED While creating / altering any table/index, Oracle used two storage parameters for space control.

  • PCTFREE – The percentage of space reserved for future update of existing data.
  • PCTUSED – The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.
  • FREELIST – Structure where Oracle maintains a list of all free available blocks. Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value. Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

ora_row_migration_1.jpg

Full Table Scans are not affected by migrated rows

The forwarding addresses are ignored. We know that as we continue the full scan, we’ll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migrated rows don’t cause us to really do any extra work — they are meaningless.

Index Read will cause additional IO’s on migrated rows

When we Index Read into a table, then a migrated row will cause additional IO’s. That is because the index will tell us «goto file X, block Y, slot Z to find this row». But when we get there we find a message that says «well, really goto file A, block B, slot C to find this row». We have to do another IO (logical or physical) to find the row.

Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with long and long raw columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

ora_row_chained_1.jpg

Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query: select column1 from table where column1 is in Block 1, would not cause any «table fetch continued row». It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for: select column2 from table and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»

Example

The following example was published by Tom Kyte, it will show row migration and chaining. We are using an 4k block size:

select name,value from v$parameter where name = ‘db_block_size’;

NAME   VALUE
————– ——
db_block_size  4096

Create the following table with CHAR fixed columns:

create table row_mig_chain_demo (
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000),
e char(1000)
);

That is our table. The char(1000)’s will let us easily cause rows to migrate or chain. We used 5 columns a,b,c,d,e so that the total rowsize can grow to about 5K, bigger than one block, ensuring we can truly chain a row.

insert into row_mig_chain_demo (x) values (1);
insert into row_mig_chain_demo (x) values (2);
insert into row_mig_chain_demo (x) values (3);
commit;

We are not interested about seeing a,b,c,d,e – just fetching them. They are really wide so we’ll surpress their display.

column a noprint
column b noprint
column c noprint
column d noprint
column e noprint

select * from row_mig_chain_demo;

X
———-
1
2
3

Check for chained rows:

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                             VALUE
----------------------------     ----------
table fetch continued row        0

Now that is to be expected, the rows came out in the order we put them in (Oracle full scanned this query, it processed the data as it found it). Also expected is the table fetch continued row is zero. This data is so small right now, we know that all three rows fit on a single block. No chaining.

ora_row_example_1.jpg

Demonstration of the Row Migration

Now, lets do some updates in a specific way. We want to demonstrate the row migration issue and how it affects the full scan:

update row_mig_chain_demo set a = ‘z1’, b = ‘z2’, c = ‘z3’ where x = 3;
commit;
update row_mig_chain_demo set a = ‘y1’, b = ‘y2’, c = ‘y3’ where x = 2;
commit;
update row_mig_chain_demo set a = ‘w1’, b = ‘w2’, c = ‘w3’ where x = 1;
commit;

Note the order of updates, we did last row first, first row last.

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

Interesting, the rows came out «backwards» now. That is because we updated row 3 first. It did not have to migrate, but it filled up block 1. We then updated row 2. It migrated to block 2 with row 3 hogging all of the space, it had to. We then updated row 1, it migrated to block 3. We migrated rows 2 and 1, leaving 3 where it started.

So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 second and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the table fetch continued row is still zero. No chaining.

ora_row_example_2.jpg

So, lets see a migrated row affecting the «table fetch continued row»:

select * from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

This was an index range scan / table access by rowid using the primary key.  We didn’t increment the «table fetch continued row» yet since row 3 isn’t migrated.

select * from row_mig_chain_demo where x = 1;

X
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                      VALUE
————————— ———-
table fetch continued row       1

Row 1 is migrated, using the primary key index, we forced a «table fetch continued row».

Demonstration of the Row Chaining

update row_mig_chain_demo set d = ‘z4’, e = ‘z5’ where x = 3;
commit;

Row 3 no longer fits on block 1. With d and e set, the rowsize is about 5k, it is truly chained.

select x,a from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 1

We fetched column «x» and «a» from row 3 which are located on the «head» of the row, it will not cause a «table fetch continued row». No extra I/O to get it.

ora_row_example_3.jpg select x,d,e from row_mig_chain_demo where x = 3;

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 2

Now we fetch from the «tail» of the row via the primary key index. This increments the «table fetch continued row» by one to put the row back together from its head to its tail to get that data.

Now let’s see a full table scan – it is affected as well:

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

The «table fetch continued row» was incremented here because of Row 3, we had to assemble it to get the trailing columns.  Rows 1 and 2, even though they are migrated don’t increment the «table fetch continued row» since we full scanned.

select x,a from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

No «table fetch continued row» since we didn’t have to assemble Row 3, we just needed the first two columns.

select x,e from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 4

But by fetching for d and e, we incemented the «table fetch continued row». We most likely have only migrated rows but even if they are truly chained, the columns you are selecting are at the front of the table.

So, how can you decide if you have migrated or truly chained?

Count the last column in that table. That’ll force to construct the entire row.

select count(e) from row_mig_chain_demo;

COUNT(E)
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 5

Analyse the table to verify the chain count of the table:

analyze table row_mig_chain_demo compute statistics;

select chain_cnt
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT
———-
3

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of «table fetch continued row» since instance startup?

The V$SYSSTAT view tells you how many times, since the system (database) was started you did a «table fetch continued row» over all tables.

sqlplus system/<password>

SELECT ‘Chained or Migrated Rows = ‘||value
FROM v$sysstat
WHERE name = ‘table fetch continued row’;

Chained or Migrated Rows = 31637

You could have 1 table with 1 chained row that was fetched 31’637 times. You could have 31’637 tables, each with a chained row, each of which was fetched once. You could have any combination of the above — any combo.

Also, 31’637 – maybe that’s good, maybe that’s bad. it is a function of

  • how long has the database has been up
  • how many rows is this as a percentage of total fetched rows.
    For example if 0.001% of your fetched are table fetch continued row, who cares!

Therefore, always compare the total fetched rows against the continued rows.

select name,value from v$sysstat where name like ‘%table%’;

select name,value from v$sysstat where name like ‘%table%’;

NAME                                                                  VALUE
—————————————————————- ———-
table scans (short tables)                                           124338
table scans (long tables)                                              1485
table scans (rowid ranges)                                                0
table scans (cache partitions)                                           10
table scans (direct read)                                                 0
table scan rows gotten                                             20164484
table scan blocks gotten                                            1658293
table fetch by rowid                                                1883112
table fetch continued row                                             31637
table lookup prefetch client count                                        0

How many Rows in a Table are chained?

The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.

analyze table row_mig_chain_demo compute statistics;

select chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
———- ———– ———– ———- ———-
3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

How to avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

The ALTER TABLE … MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE … MOVE statement with the COMPRESS keyword to store the new segment using table compression.

  1. ALTER TABLE MOVEFirst count the number of Rows per Block before the ALTER TABLE MOVEselect dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1; Block-Nr        Rows
    ———- ———-
    2066          3Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:ALTER TABLE row_mig_chain_demo MOVE
    PCTFREE 20
    PCTUSED 40
    STORAGE (INITIAL 20K
    NEXT 40K
    MINEXTENTS 2
    MAXEXTENTS 20
    PCTINCREASE 0);Table altered. 

    Again count the number of Rows per Block after the ALTER TABLE MOVE

    select dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1;

    Block-Nr        Rows
    ———- ———-
    2322          1
    2324          1
    2325          1

  2. Rebuild the Indexes for the TableMoving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.analyze table row_mig_chain_demo compute statistics;ERROR at line 1:
    ORA-01502: index ‘SCOTT.SYS_C003228’ or partition of such index is in unusable
    stateThis is the primary key of the table which must be rebuilt.alter index SYS_C003228 rebuild;
    Index altered.analyze table row_mig_chain_demo compute statistics;
    Table analyzed. 

    select chain_cnt,
    round(chain_cnt/num_rows*100,2) pct_chained,
    avg_row_len, pct_free , pct_used
    from user_tables
    where table_name = ‘ROW_MIG_CHAIN_DEMO’;

    CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
    ———- ———– ———– ———- ———-
    1       33.33        3687         20         40If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

  1. Create the CHAINED_ROWS tablecd $ORACLE_HOME/rdbms/admin
    sqlplus scott/tiger
    @utlchain.sql
  2. Analyse all or only your Tablesselect ‘analyze table ‘||table_name||’ list chained rows into CHAINED_ROWS;’
    from user_tables
    /analyze table ROW_MIG_CHAIN_DEMO list chained rows into CHAINED_ROWS;
    analyze table DEPT list chained rows into CHAINED_ROWS;
    analyze table EMP list chained rows into CHAINED_ROWS;
    analyze table BONUS list chained rows into CHAINED_ROWS;
    analyze table SALGRADE list chained rows into CHAINED_ROWS;
    analyze table DUMMY list chained rows into CHAINED_ROWS;Table analyzed.
  3. Show the RowIDs for all chained rowsThis will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREEselect owner_name,
    table_name,
    count(head_rowid) row_count
    from chained_rows
    group by owner_name,table_name
    /OWNER_NAME                     TABLE_NAME                      ROW_COUNT
    —————————— —————————— ———-
    SCOTT                          ROW_MIG_CHAIN_DEMO                      1

Conclusion

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

  • Row migration is typically caused by UPDATE operation
  • Row chaining is typically caused by INSERT operation.
  • SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
  • To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view
  • To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

 

Understanding AutoConfig

Published on by Advait3 Comments

Introduction:

AutoConfig is the tool, which is used to configure oracle application systems. Its basically a perl script with a shell wrapper over it. At present there are more then 300 configuration files and environment files in application system. Whenever a change is made to any of the configuration parameter, the change needs to be propagated correctly to correct configuration and environment files. Managing such changes in such large number of files is really difficult.

Context File:

AutoConfig uses a file called context file, which is used to configure changes. Context file is a XML file having all the parameters of application system. These parameters are part of some configuration file for some services or it may be part of some environment file. When we create an application system (using Rapid install), context file gets created automatically will all the parameters and there values. Most of the parameters take default value, where as some parameters takes the value that is supplied at the time of installation.

Optionally we can create context file separately using adbldxml.sh/adbldxml.pl script. These scripts are present in $FND_TOP/bin directory. adbldxml.sh in turn runs oracle.apps.ad.context.GenerateContext java class.

Following is an example entry for the context variable “domainname” from the log file of adbldxml.pl run:

 [ s_domainname ]
SEVERITY         : INFO   
SOURCE SEARCHED  : Using System commands to get Domain name value   
SEARCH RESULTS   : Domainname could not be found using system commands.   
SOURCE SEARCHED  : Database table - GLOBAL_NAME   
SEARCH RESULTS   : Query on database table GLOBAL_NAME returned - XXXX.WORLD;  

                   Table is hosting incorrect value for domain.

SOURCE SEARCHED  : Prompting the user for the domain name.  
SEARCH RESULTS   : Value accepted from the user - its.yale.edu  
VALUE ASSIGNED   : its.yale.edu  
USER ACTION      : No action required          

The above entry shows the sources searched, search results for each search and the final assignment for the context variable domainname. As you can see adbldxml.pl provides the search information in great detail in its log file. This log file helps a lot in debugging adbldxml.pl behavior.

Each placeholder in the template file has a corresponding xml node (a context variable) in the context file. They all have a mandatory attribute called oa_var, the attribute value for this attribute is the placeholder name. Each node is primarily identified by the oa_var attribute value and can optionally have other attributes like oa_type, oa_enabled scope and default. The node’s only child is a text node, which stores the placeholder’s environment specific value. From here on I will refer to this text value as the “text value of the context variable”. The description of these context variables along with their oa_var value can be seen in adctxinf.tmp (stored in $AD_TOP/admin/template).

Context variables in the context file are organized in a hierarchical order, hence the choice of xml to store them. These context variables mainly lie under five top level parent nodes i.e. oa_system, oa_host, oa_install, oa_environments and oa_processes. Each top level parent node is further divided into further levels before you reach the real context variables.

To change any configuration option in the Oracle Applications environment, we need to change the value of corresponding context variable in the context file and then run autoconfig for our changes to take effect. For example if we need to change the web server port then we would change the text value of the context variable “webport” (oa_var value s_webport), and then run autoconfig. Since the context file is just an xml file, it can be updated in many ways. I will describe some of them later.

How it works:

Each configuration file has one (sometimes two, one for NT and one for UNIX) corresponding template file (provided by autoconfig patches, stored in $PROD_TOP/admin/template directory). Profile options and other instance specific information in the database is maintained by many sql scripts, called from wrapper shell/perl scripts. These scripts also have corresponding template files (also provided by autoconfig patches, `in $PROD_TOP/admin/templates). In these template files all the environment specific values are replaced by placeholders (like %s_webhost%).

Following entry in httpd.conf

Timeout 300 is replaced by following in its template $FND_TOP/admin/template/httpd_ux_ias1022.conf

Timeout %s_ohstimeout%

Environment specific values for this placeholder is stored in an environment specific xml file (called application context file) stored in $APPL_TOP/admin.

For e.g.

For the above placeholder the value stored in the xml file is:

<ohstimeout oa_var=”s_ohstimeout”>300</ohstimeout>

Each placeholder has a corresponding xml node in the context file. The placeholder name is the value for the “oa_var” attribute of the xml node, the placeholder name without the prefix “s_” (generally) is the node name for the xml node and the value is stored as a child text node. These xml nodes are termed as context variables by Oracle documentation and each node primarily is identified by its oa_var attribute value. So effectively we can say that in the above case we replace “300” by context variable “ohstimeout” whose oa_var value is “s_ohstimeout”.

Configuration files can be easily created from the template file by just replacing all the placeholders with the corresponding values from the context file. This process is termed as “instantiating the template” in Oracle documentation.

Driver files (stored in $PROD_TOP/admin/driver) store information about what to do with each template (e.g. instantiate it and replace the existing configuration file with it, instantiate it and run it). These files are named as <PROD_TOP>tmpl.drv (e.g. adtmpl.drv, fndtmpl.drv etc.) They contain one line for each template they manage.

When autoconfig (adconfig.pl or adconfig.sh) runs it just processes the driver file for each product, line by line doing what the driver file instructs it to do. The order of execution of each line is not sequential as we shall see later. The diagram below depicts the autoconfig functionality.

auto.jpg

Editing context file:

1. Editcontext

Editcontext is a java application provided by oracle for updating the values in the context file. It is provided as a patch (patch: 2873456). Editcontext provides a list of all the updatable context variable values along with their titles. The title is not the variable name or value of oa_var attribute, but it is derived from adctxinf.xml, which comes with the editcontext patch and stores the title along with the oa_var value for each context variable. Editcontext only lists those context variables, which are listed in its repository (adxmlctx.tmp). If you find that, not all variables are listed, then the version of this file is probably lower than the adxmlctx.tmp stored in $AD_TOP/admin/template. In that case just copy this file from $AD_TOP/admn/template to the editcontext repository (i.e. <patch unzip directory>/editcontext/etc).

After launching editcontext, we have to find the context variable that we want to update. Once we find it, we can just change the value and save the new value to the context file (To be precise editcontext rewrites the whole context file instead of just updating one value). Since the list of context variables is quite long, it may take a while to find the desired context variable in the list.

2.      Oracle Applications Manager

Oracle Applications Manager is a web-based portal for managing Oracle Applications. Along with many other features, this product has a section for updating the context files. This product lists everything for each context variable i.e. name, oa_var attribute value, description, and real updateable value. Moreover instead of giving us a long list of context variables to search from, it divides the context file into many sections and each section is displayed in the xml tree format making it easier to find a particular context variable. It also provides us with the option of adding custom context variables (which we will look at in the customization section).

One thing to remember while updating context variables using OAM is that what it displays is gotten; by parsing the context file stored in fnd_oam_context_files table (Autoconfig at each run uploads the context file in this table). When we try to update the context file, OAM first update’s the status flag from ‘S’ to ‘H'(History) for our context file record, it then inserts another row for the same context file with status ‘S’. (OAM inserts another row instead of updating the existing row to maintain change history for each context file) It then requests the specific node’s FNDFS listener for updating the file on the file system (autoconfig uses the file on the file system). So we have to make sure that the FNDFS listeners are running on all the nodes before using OAM to update the context file.

With all the features of Oracle Applications Manager mentioned above, updating context files using Oracle Applications Manager seems to be the direction Oracle is trying to move.

3.      Scripted Update

EditContext and Oracle Application Manager are good for updating context file if you need to update one or two context variables. If you need to modify quite a few variables (for e.g. if you need to update all the ports in the context file) then using these GUI tools do become cumbersome. It would be nice to have a scripted way of updating lots of values in the context file using a data source of changes. I will describe two ways of achieving this using Oracle supplied code.

a. Java class oracle.apps.ad.context.UpdateContext

This java class allows us to change one context variable at a time from the command line. But we can easily use a shell/perl/python/tcl/etc script to call this class multiple times for different context variables based on a changes file, which stores all our required changes

For example let’s say we need to change the following context variables. The first column is the oa_var values of the context variable and the second column is desired value. These values are stored in a file changes.txt

 s_webport 7777
 s_webport_pls 9999
 s_oprocmgr_port 9997
 s_active_webport 9996
 s_forms_servlet_portrange 1801-1810
 s_oacore_servlet_portrange 2801-2810

The following perl script loops through each record in changes.txt and updates the context file by calling the java class once for each line. The syntax it uses to call this java class is:

java oracle.apps.ad.context.UpdateContext CONTEXT NAME VALUE

where

CONTEXT – Context File Name
NAME – context variable name
VALUE – Desired Changed value

b. Using Perl Module TXK::XML:

The above technique works well but it is quite heavy on resource consumption, since we start a new jvm for every update. For every update the java code has to parse the xml file, create the DOM tree, do the update in memory and then write it back to the file system. It would be more efficient if we could just parse the xml file only once and do all our updates before writing the xml file to the file system. This can be achieved by using oracle supplied perl module TXK::XML (Found in $AU_TOP/perl/TXK). For using TXK::XML we load the context file as as TXK::XML object, change the configuration option values by using setOAVar method ot the TXK::XML object and then write the file back to the file system. The only thing to take note of is setOAVar expects a hash reference as the input variable. This hash will contain the list of context variables to be changed in a key value pair; the key is the oa_var value of the context variable and the value is the corresponding desired values.

If we use any programmatic method to update the context file, we are not allowing OAM to maintain a change history for all our context files, which is very important since context file is the central repository for all the configuration options. This can be easily taken care by updating the existing record for our context file to status ‘H’ in fnd_oam_context_files table and then inserting the changed context file into the table using executable FNDCPUCF (in $FND_TOP/bin).

Since the context file is just an xml file, you can basically use any xml parser or write your own xml parser to do the updates, but in my experience the above mentioned methods has been the least error prone and least time consuming. Moreover in all of the above methods I am using Oracle’s code to update the xml file.

The Template Files

Template files are files, which are merged with the context file (instantiated) to produce configuration files or scripts to update the database. They are stored in <PROD_TOP>/admin/templates directory. Autoconfig patches/Technology stack patches install new templates or upgrade existing templates. Now let’s look at how these template files are created.

Let’s take a small configuration file REP60_<SID>.ora (Reports server configuration file):

mailprofile="$Header: REP60_server.ora 115.5 2004/03/24 01:10:51 njoseph ship $"
nlssupport=yes
maxconnect=20
cachedir="/u02/app/oracle/product/8.0.6/reports60/server/cache"
cachesize=50
minengine=5
maxengine=10
initengine=5
maxidle=30
security=1
englife=50

If we replace all the environment specific values with context variables from the context file, then this is what we get:

mailprofile="$Header: REP60_server.ora 115.5 2004/03/24 01:10:51 njoseph ship $"
nlssupport=yes
maxconnect=20
cachedir="%s_tools_oh%/reports60/server/cache"
cachesize=50
minengine=%s_minengine%
maxengine=%s_maxengine%
initengine=0
maxidle=30
security=1
englife=50

And now we have a template file corresponding to the Reports server configuration file. The placeholder values enclosed by “%” correspond to the context variable in the context file. If we instantiate this template using the values from the context file we can easily recreate our configuration file. All the configuration files and sql scripts are converted to templates in similar fashion and supplied to us by autoconfig patches.

The Driver Files

Driver files, as the name suggests drives what autoconfig does (stored in <PROD_TOP>/admin/driver). They are named as <PROD_TOP>tmpl.drv i.e. adtmpl.drv, fndtmpl.drv etc. Autoconfig patches install and update these driver files. Each driver file has one line for each template it manages. Each line tells autoconfig what to do with that template. This is achieved by using a particular syntax that conveys the relevant information to autoconfig. Following is the syntax for the lines in the driver files.

<PROD> <Location> < Name> <Action> <Dest directory> <Dest file name> <File permission>

 For e.g.
 ad admin/template adconfig.txt INSTE8 /admin     adconfig.txt      600
Column Description
PROD Product Name
Location Directory underneath PROD_TOP where the template is located. Generally “admin/template”.
Name Name of the template File
Action Type of action to be performed on this template (Refer to the following table for description of different kinds of action)
Dest Directory Destination directory of the instantiated template file. We can use context variables enclosed In “” to specify the destination directory. For example if we want to specify $APPL_TOP/admin as the destination directory we would specify it as “<at>/admin” in the driver file.
Dest file name Destination configuration file name for the instantiated template file. We can use context variables enclosed in “” to specify the destination configuration file name. For example if the destination file has Database SID we can specify it as “<SID>.env” in the driver file.
File Permission Autoconfig generates the configuration file with the provided unix style permission.

Actions:

Action Description
INSTE8
  • Instantiate (replace context variables with values from xml context file)
  • Copy the result to the <Dest directory>/<Dest file name> overwriting the existing configuration file, if one exists.
  • Change the permission to <File permission>
INSTE8_SETUP
  • Instantiate (replace context variables with values from xml context file)
  • Copy the result to the <Dest directory>/<Dest file name> overwriting the existing file, if one exists.
  • Change the permission to <File permission>
  • Execute the instantiated file.
INSTE8_APPLY
  • Instantiate (replace context variables with values from xml context file)
  • Copy the result to the <Dest directory>/<Dest file name> overwriting the existing file, if one exists.
  • Change the permission to <File permission>
  • Execute the instantiated file.
INSTE8_PRF
  • Instantiate (replace context variables with values from xml context file)
  • Copy the result to the <Dest directory>/<Dest file name> overwriting the existing file, if one exists.
  • Change the permission to <File permission>
  • Execute the instantiated file.
INSTALL
  • Instantiate the template file only if the resulting configuration file does not already exist.

Though the description of three of the actions is same, they are there to get different execution order within autoconfig’s execution of each driver file. The execution order in which autoconfig processes each line is INSTE8, INSTALL, INSTE8_SETUP, INSTE8_APPLY and INSTE8_PRF. These actions mark the different phases of autoconfig i.e. Setup phase, Apply phase and Profile phase. It’s also possible to run autoconfig only up to a particular phase.

The driver file syntax also supports if then else branching based on type of node (i.e. web, admin, forms etc), which is useful in a multi node installation. The syntax also supports the branching based on the platform type (Unix or Windows). This allows having multiple templates for each configuration file and instantiating a particular template based on the platform.

For e.g.
if platform NT
ad admin/template APPLSYS_nt.env INSTE8 .env 600
endif

or

if installation-type admin node nodedev forms formsdev web webdev
ad admin/template adconfig.txt INSTE8 /admin adconfig.txt 600
end if

The driver file syntax also supports including other driver files which are processed by autoconfig recursively

For e.g.
#include        fnd     admin/driver    fndtmpl.drv

When autoconfig (adconfig.pl or adconfig.sh) runs it just processes each product’s driver file respecting the, if then else branching as it processes the lines in the driver files. It also processes all included driver files recursively.

 

Oracle Real Application Cluster 10g

Published on by Advait1 Comment

Introduction

Oracle Real Application Cluster (RAC) is a revolution in the database management system. It is an extension of Oracle single instance database. RAC is basically a cluster of instances working on the same database. As you know instance is nothing but the computer memory and some background processes, so in case of RAC we have multiple such instances which are installed and configured on different nodes and we have a single database (datafiles) which are accessed by these instances. This post explains the technical details about the RAC architecture and also I will discuss about the installation of RAC.

What is Oracle Real Application Cluster 10g?

Software Architecture

A RAC is a clustered database. A cluster is a group of independent servers that cooperate as a single system. In the event of system failure clustering ensure high availablity to the user. Access to mission critical data is not lost. Redundant hardware components, such as additional nodes, interconnects and disks, allow the cluster to provide high availability. Such redundant hardware architecture avoids a single point of failure and ensures high availability for the system.

1.jpg

Above figure shows the architecture for RAC. In RAC each instance runs on a seperate server which can access database made of multiple disks. For RAC to act as a sngle database, each seperate instance in a RAC should be a part of cluster. For the external users all the instance (nodes) which are part of cluster will look as single instance.

For each instance to be a part of cluster, we need to have some cluster software installed and all the instance should register in the cluster software. From Oracle Database 10g onwards, Oracle provides its own clusterware, A software to be installed on the nodes which are the part of cluster. Advantage with Oracle clusterware is that customer doesn’t have to purchase any third party clusterware. Also the clusterware provided by Oracle is integrated with OUI for easy installation. When a node in a Oracle cluster is started, all instances, listener and services are stared automatically. If an instance fail, the clusterware will automatically restart the instance so the services is often restored before the administrator notices it was down.

Network Architecture

Each RAC node should have at least one static IP address for the public network (Used by application) and one static IP address for the private cluster interconnect. Also we can have 1 virtual IP address(VIP) for each node.

The private networks are critical components of a RAC cluster. The private networks should only be used by Oracle to carry Cluster Manager and Cache Fusion (Explained Later) inter-node connection. A RAC database does not require a separate private network, but using a public network can degrade database performance (high latency, low bandwidth). Therefore the private network should have high-speed NICs (preferably one gigabit or more) and it should only be used by Oracle.

Virtual IPs are required for fail over. This is called TAF (Transparent Application Failover). Processes external to the Oracle 10g RAC cluster control the Transparent Application Failover (TAF). This means that the failover types and methods can be unique for each Oracle Net client. For failover to happen client connections are made using VIPs.

Hardware Architecture

Both Oracle Clusterware and Oracle RAC require access to disks that are shared by each node in the cluster. The shared disks must be configured using OCFS (1 or 2), raw devices or third party cluster file system such as GPFS or Veritas.

OCFS2 is a general-purpose cluster file system that can be used to store Oracle Clusterware files, Oracle RAC database files, Oracle software, or any other types of files normally stored on a standard filesystem such as ext3. This is a significant change from OCFS Release 1, which only supported Oracle Clusterware files and Oracle RAC database files. Note that ASM cannot be used to store the Oracle clusterware files, since clusterware is installed before installaing ASM and also clusterware have to be started before starting ASM.

OCFS2 is available free of charge from Oracle as a set of three RPMs: a kernel module, support tools, and a console. There are different kernel module RPMs for each supported Linux kernel.

Installing RAC 10g

Installing a RAC is a 5 step process as given below.

1) Complete Pre-Installation Task
Hardware Requirement
Software Requirement
Environment Configuration, Kernel parameter and so on.
2) Perform CRS installation
3) Perform Oracle Database 10g Installation
4) Perform Cluster Database creation
5) Complete post installation task

Pre-Installation Task

Check System Requirement

– Atleast 512MB of RAM
Run below command to check
# grep MemTotal /proc/meminfo
– Atleast 1G of swap space
Run below command to check
# grep SwapTotal /proc/meminfo
– /tmp directory should be 400M
Run below command to check
df -h /tmp

Check Software Requirement
– package Requirements

For installing RAC, the packages required for Red Hat 3.0 are:
gcc-3.2.3-2
compat-db-4.0.14.5
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-libstdc++-7.3-2.96.122
compat-libstdc++-devel-7.3-2.96.122
openmotif21-2.1.30-8
setarch-1.3-1

you can verify if these or higher version packages are present or not using following command
# rpm -q <package_name>

– Create Groups and Users

You can create unix user groups and user IDs using groupadd and useradd commands. We need 1 oracle user and 2 groups – “oinstall” being the primary and “dba” being secondary.

# groupadd -g 500 oinstall
# groupadd -g 501 dba
# useradd -u 500 -d /home/oracle -g “oinstall” -G “dba” -m -s /bin/bash oracle

Configure Kernel Paramters

– Make sure that following parameters are set in /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 658576
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 1048536
net.core.wmem_default = 262144
net.core.wmem_max = 1048536

To load the new setting run /sbin/sysctl –p

These are the minimum required values, you can have higher values as well if your server configuration allows.

Setting the system environment

– Set the user Shell limits

cat >> /etc/security/limits.conf << EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

cat >> /etc/pam.d/login << EOF
session required /lib/security/pam_limits.so
EOF

cat >> /etc/profile << EOF
if [ \$USER = “oracle” ]; then
if [ \$SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

cat >> /etc/csh.login << EOF
if ( \$USER == “oracle” ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF

– Configure the Hangcheck Timer

Hangcheck-timer module monitors the Linux kernal for extended operating system hangs that can affect the reliability of RAC node and cause database corruption. If a hang occurs, the module reboots the node.

You can check if the hangcheck-timer module is loaded by running lsmod command as root user.

/sbin/lsmode | grep -i hang

If the module is not running you can load it manually using below command.

modprobe hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
cat >> /etc/rc.d/rc.local << EOF
modprobe hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
EOF

– Configuring /etc/hosts

/etc/hosts contains the hostname and IP address of the server.

You will need 3 hostnames for each node in the cluster. One will be public hostname for primary interface. Second will be private hostname for cluster interconnect. Third will be virtual hostnames (VIP) for high availability.

For Node 1

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
140.87.222.169 ocvmrh2045.us.oracle.com ocvmrh2045 #node1 public
140.87.241.194 ocvmrh2045-nfs.us.oracle.com ocvmrh2045-nfs ocvmrh2045-a #node1 nfs
152.68.143.111 ocvmrh2045-priv.us.oracle.com ocvmrh2045-priv #node1 private
152.68.143.112 ocvmrh2053-priv.us.oracle.com ocvmrh2053-priv #node2 private
140.87.222.220 ocvmrh2051.us.oracle.com ocvmrh2051 # Node1 vip
140.87.222.225 ocvmrh2056.us.oracle.com ocvmrh2056 # Node2 vip

For Node 2

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
140.87.222.222 ocvmrh2053.us.oracle.com ocvmrh2053 # Node2 Public
140.87.241.234 ocvmrh2053-nfs.us.oracle.com ocvmrh2053-nfs ocvmrh2053-a # Node2 nfs
152.68.143.111 ocvmrh2045-priv.us.oracle.com ocvmrh2045-priv # Node1 Private
152.68.143.112 ocvmrh2053-priv.us.oracle.com ocvmrh2053-priv # Node2 Private
140.87.222.220 ocvmrh2051.us.oracle.com ocvmrh2051 # Node1 vip
140.87.222.225 ocvmrh2056.us.oracle.com ocvmrh2056 # Node2 vip

– Creating database Directories

You have to get the following directories created for you with a write permission for oracle user.

Oracle Base Directories
Oracle Inventory Directory
CRS Home Directory
Oracle Home Directory

In our case the directories are:

Oracle Base Directories – /u01/app/
Oracle Inventory Directory – /u01/app/oraInventory
CRS Home Directory – /u01/app/oracle/product/10.2.0/crs
Oracle Home Directory – /u01/app/oracle/product/10.2.0/db

Configure SSH for User Equivalence

The OUI detects whether the machine on which you are installing RAC is a part of cluster. If its a part of cluster then you have to select the other nodes which are the part of cluster and on which you want to install the patch. But when OUI tries to install the patch on other node while connecting from 1st node, it will ask for login credential and prompt for a password in between the installation, which we want to avoid. For this purpose we have to have user equivelence in place. User equivalence can be achieved by using SSH. First you have ot configure SSH.

Logon as the “oracle” UNIX user account

# su – oracle

If necessary, create the .ssh directory in the “oracle” user’s home directory and set the correct permissions on it:

$ mkdir -p ~/.ssh
$ chmod 700 ~/.ssh

Enter the following command to generate an RSA key pair (public and private key) for version 3 of the SSH protocol:

$ /usr/local/git/bin/ssh-keygen -t rsa

Enter the following command to generate a DSA key pair (public and private key) for version 3 of the SSH protocol:

$ /usr/local/git/bin/ssh-keygen -t dsa

Repeat the above steps for all Oracle RAC nodes in the cluster

Create authorized key file.

$ touch ~/.ssh/authorized_keys
$ cd ~/.ssh
bash-3.00$ ls -lrt *.pub
-rw-r–r– 1 oracle oinstall 399 Nov 20 11:51 id_rsa.pub
-rw-r–r– 1 oracle oinstall 607 Nov 20 11:51 id_dsa.pub

The listing above should show the id_rsa.pub and id_dsa.pub public keys created in the previous section

In this step, use SSH to copy the content of the ~/.ssh/id_rsa.pub and ~/.ssh/id_dsa.pub public key from all Oracle RAC nodes in the cluster to the authorized key file just created (~/.ssh/authorized_keys).

Here node 1 is ocvmrh2045 and node 2 is ocvmrh2053

ssh ocvmrh2045 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh ocvmrh2045 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh ocvmrh2053 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh ocvmrh2053 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

Now that we have the entry for all the public keys on both the node in this file, we should copy the file to all the RAC nodes. We done have to do this on all nodes, just copying the file to other nodes will do.

scp ~/.ssh/authorized_keys ocvmrh2053:.ssh/authorized_keys

Set permissions to the authorized file

chmod 600 ~/.ssh/authorized_keys

Establish User Equivalence

Once SSH is configured we can go ahead with configuring user equivalence.

su – oracle

exec /usr/local/git/bin/ssh-agent $SHELL
$ /usr/local/git/bin/ssh-add

Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

– Test Connectivity

Try the below command and it should not ask for the password. It might ask the password for the first time, but after that it should be able to execute the steps without asking for password.

ssh ocvmrh2045 “date;hostname”
ssh ocvmrh2053 “date;hostname”
ssh ocvmrh2045-priv “date;hostname”
ssh ocvmrh2053-priv “date;hostname”

Partitioning the disk

In order to use OCFS2, you need to first partition the unused disk. You can use “/sbin/sfdisk –s” as a root user to check the partitions. We will be creating a single partition to be used by OCFS2. As a root user, run the below command.

# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.

The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

You can verify the new partition now as

# fdisk -l /dev/sdc

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 1305 10482381 83 Linux

When finished partitioning, run the ‘partprobe‘ command as root on each of the remaining cluster nodes in order to assure that the new partitions are configured.

Configure OCFS2

We will be using OCFS2 here in this installation. OCFS is a cluster file system solution provided by Oracle, which is specially meant for RAC instllation. Once configure the disk for OCFS we can use the same for clusterware files (like OCR – Oracle Cluster Registry file and Voting file), as well as we can use the same disk for database files.

# ocfs2console

22.jpg
Select Cluster -> Configure Nodes

Click on Add on the next window, and enter the Name and IP Address of each node in the cluster.
Note: Use node name to be the same as returned by the ‘hostname’ command

33.jpg

Apply, and Close the window.

After exiting the ocfs2console, you will have a /etc/ocfs2/cluster.conf similar to the following on all nodes. This OCFS2 configuration file should be exactly the same on all of the nodes:

node:
ip_port = 7777
ip_address = 140.87.222.169
number = 0
name = ocvmrh2045
cluster = ocfs2

node:
ip_port = 7777
ip_address = 140.87.222.222
number = 1
name = ocvmrh2053
cluster = ocfs2

cluster:
node_count = 2
name = ocfs2

Configure O2CB to Start on Boot and Adjust O2CB Heartbeat Threshold

You now need to configure the on-boot properties of the O2CB driver so that the cluster stack services will start on each boot. You will also be adjusting the OCFS2 Heartbeat Threshold from its default setting of 7 to 601. All the tasks within this section will need to be performed on both nodes in the cluster as root user.

Set the on-boot properties as follows:

# /etc/init.d/o2cb offline ocfs2
# /etc/init.d/o2cb unload
# /etc/init.d/o2cb configure

Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.
The following questions will determine whether the driver is loaded on boot. The current values will be shown in brackets (‘[]’). Hitting <ENTER> without typing an answer will keep that current value. Ctrl-C will abort.

Load O2CB driver on boot (y/n) [y]: y
Cluster to start on boot (Enter “none” to clear) [ocfs2]: ocfs2
Specify heartbeat dead threshold (>=7) [7]: 601
Writing O2CB configuration: OK
Loading module “configfs”: OK
Mounting configfs filesystem at /config: OK
Loading module “ocfs2_nodemanager”: OK
Loading module “ocfs2_dlm”: OK
Loading module “ocfs2_dlmfs”: OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK
Starting O2CB cluster ocfs2: OK

We can now check again to make sure the settings took place in for the o2cb cluster stack:

Verify that ocfs2 and o2cb are started. Check this on both nodes. As root user:

# chkconfig –list |egrep “ocfs2|o2cb”
ocfs2 0:off 1:off 2:on 3:on 4:on 5:on 6:off
o2cb 0:off 1:off 2:on 3:on 4:on 5:on 6:off

If it doesn’t look like above on both nodes, turn them on by following command as root:

# chkconfig ocfs2 on
# chkconfig o2cb on

Create and format the OCFS2 filesystem on the unused disk partition

As root on each of the cluster nodes, create the mount point directory for the OCFS2 file system.

# mkdir /u03

Run the below command as a root user only on 1 node to create a OCFS2 file system on the unused disk /dev/sdc1, that you partitioned above.

# mkfs.ocfs2 -b 4K -C 32K -N 4 -L /u03 /dev/sdc1
mkfs.ocfs2 1.2.2
Filesystem label=/u03
Block size=4096 (bits=12)
Cluster size=32768 (bits=15)
Volume size=10733944832 (327574 clusters) (2620592 blocks)
11 cluster groups (tail covers 5014 clusters, rest cover 32256 clusters)
Journal size=67108864
Initial number of node slots: 4
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Formatting Journals: done
Writing lost+found: done
mkfs.ocfs2 successful

The meaning of the above command is partition with a volume label of “/u03” (-L /u03), a block size of 4K (-b 4K) and a cluster size of 32K (-C 32K) with 4 node slots (-N 4).

Once OCFS2 filesystem is configured on the disk, you can mount the same.

Mount OCFS2 filesystem on both nodes

Run the below command on all nodes to mount the disk having OCFS2 file system.

# mount -t ocfs2 -L /u03 -o datavolume /u03

You can verify if the disk is mounted correctly or not using below command

# df /u03

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sdc1 10482368 268736 10213632 3% /u03

Create the directories for shared files

As root user, run the following commands on node1 only. Since /u03 is on a shared disk, all the files added from one node will be visible on other nodes.

CRS files:

# mkdir /u03/oracrs
# chown oracle:oinstall /u03/oracrs
# chmod 775 /u03/oracrs

Database files:

# mkdir /u03/oradata
# chown oracle:oinstall /u03/oradata
# chmod 775 /u03/oradata

Installaing Oracle Clusterware

Before installing the Oracle RAC database, we need to install Oracle clusterware. Clusterware will create 2 important files. One is the OCR file (Oracle Cluster Registry) and other is Voting file. OCR file is used for registering the nodes involved in RAC installation and to store all the details about those nodes. Voting file is used to get the status of each node after a definite period of time. Each node will register its presence after a definite time into this voting file. This is called heart beat of RAC. If a node goes down, then it wont be able to register its presence in voting file and other instance will come to know. Other instance will then bring up the crashed instance.

Follow the below step to install clusterware.

From the setup directory run the ./runInstaller command

Below are the main screen and the inputs to be given.

Welcome page – Click Next

11.jpg

Specify Inventory Directory and Credentials – Enter the inventory location where it should create inventory

12.jpg

Specify Home Details – Specify the correct location of home. Provide the location for crs home. Note that this location may not be a shared location. This is the location for installing a crs software and not for OCR and voting file.

13.jpg
Product Specific Prerequisite Checks – OUI will perform the required pre-reqs checks. Once done, press Next.

14.jpg

Specify Cluster Configuration – On this screen you need to add all the servers that will be part of RAC installation. Basically this is a push install, where the installation will be pushed to all the nodes we are adding here. So that we “don’t’ have to install CRS again from node 2.

15.jpg
Specify Network Interface Usage – We need at least 1 network to be private and not to be used by application. So make 1 network as private network, so that we can use the same for interconnect.

16.jpg
Specify OCR Location – This is where you will provide the location for OCR file. Remember that this file should be shared and accessible to all the nodes. We know that we have a shared disk /u03. In the above step under “Create the directories for shared files”, we created a “/u03/oracrs” directory. This can be provided here.

17.jpg
Specify Voting Disk Location – On this screen you will provide the location for voting file. You need to provide the shared location here as well. You can provide the same shared location we created in above step.

18.jpg

Summary – Click on Install

You can verify the cluster installation by running olsnodes.

bash-3.00$ /u01/app/oracle/product/10.2.0/crs/bin/olsnodes
ocvmrh2045
ocvmrh2053

Create the RAC Database

You can follow the same steps that you follow for installing the single instance database, only couple of screens are new in this instllation as compared to single instance database installation.

111.jpg

4) Specify Hardware Cluster Installation Mode – Select cluster installation and click on select all to select all the nodes in the cluster. This will propogate the installation in all the nodes.

121.jpg

10) Specify Database Storage Options – In this case if you are not using ASM or Raw devices and using file system then specify the shared location we created above. This is important because all the instance should have access to the datafiles. We are creating multiple instances but we are having single database(database files).

At the end, it will give the summary and you click on install.

Congratulations! Your new Oracle 10g RAC Database is up and ready for use.

References

Oracle RAC Documentation – http://download.oracle.com/docs/html/B10766_08/toc.htm

Oracle Technical White Paper May 2005 by Barb Lundhild

Converting a single instance database to RAC – http://www.oracle.com/technology/pub/articles/chan_sing2rac_install.html#prelim

 

Oracle Database Lite 10g (OLite 10g)

Published on by Advait2 Comments

Introduction

Oracle Database Lite 10g extends the power of grid computing to a mobile workforce with a complete, integrated, secure database for building, deploying, and managing mobile applications for a broad range of mobile devices. Oracle Database Lite 10g enables persistent access to critical information and applications without requiring continuous connectivity to back-end enterprise systems. As a result, users increase the efficiency, productivity, and responsiveness of mobile workforces while reducing costs and improving customer satisfaction.

Oracle Database Lite is a complete, integrated, and secure infrastructure to build, manage, and provision mobile database application to a broad range of wireless devices. It is the leading platform to develop, deploy and manage mobile applications that store data locally on mobile devices (Smartphones, PDAs, Handheld Computers, and traditional Laptops) and synchronize data with central servers. Oracle Database Lite includes: scalable data synchronization architecture to thousands of mobile users; centralized management through easy-to-use graphical user interface; utilizes Oracle Real Application Clusters for maximum high-availability and failover; and revolutionary new mobile database development tools increases developer productivity. With Oracle Lite, your business cost-effectively extends enterprise data to occasionally connected mobile users. Oracle Lite is a complementary product optimized to work with Oracle Database Standard or Enterprise Edition.

Usability

Employees are working away from their desks and require access to corporate data normally found only on desktop devices connected to enterprise networks. Wireless connections for mobile users offer the promise of remote access of enterprise data but persistent wireless connections are not always possible, practical or desirable.

Wireless connections are not always possible because mobile workers may be in an environment that does not have wireless coverage. Often it is not practical to use a persistent wireless connection because applications do not have a real time data access requirement to justify the costs of communications. There are situations where a wireless connection is not desirable because the very presence of a wireless connection could compromise an application or a user’s security.

Developers require an infrastructure with application services that enable the development, delivery and operation of secure, personalized applications to mobile or embedded devices.

Database Lite Components

Oracle Database Lite 10g is a part of Oracle’s Database that extends the grid environment
and it makes your applications mobile. Oracle Database Lite describes a group of components that includes the Lite Database, the Mobile Server for Data Synchronization, Life Cycle Management and a Rapid Application Development environment.

Oracle Database Lite is a complete solution and includes more than a small database. The Oracle Lite database included in this solution is not an abbreviated version of the Oracle database but was designed from the ground up to be used with mobile applications in small and embedded devices.

untitled.jpg

Prerequisite Software for Lite

Since Oracle Database Lite stores and retrieves your information from an Oracle database, you must have a back-end Oracle database to run Lite. The Oracle database can be either Standard or Enterprise Edition, running a minimum version of 8.1.7 or higher.

Middle-Tier Option for Mobile Server

Oracle Database Lite uses a middle-tier to communicate between the clients and the back-end database. You must use one of the following as the middle-tier:

  • Stand alone OC4J – for development environments
  • Oracle Application Server – for production environments

Stand Alone OC4J

Stand Alone OC4J is automatically installed with Oracle Database Lite and this provides a standalone Mode for development environments.

Mobile Server in standalone mode uses the standalone version of Oracle Containers for J2EE (OC4J). This is the preferred configuration for development and testing but it will
support only a limited number of concurrent users.

Oracle Application Server

Production environments require either Oracle Application Server 10g. The application server is not installed with Oracle Database Lite and must be installed before installing Oracle Database Lite Mobile Server.

The Oracle Application Server production environment will support large numbers of  users, provides caching, load balancing, centralized management, security, and Single Sign On support.

Oracle Lite Database

The Oracle Lite Database is a small footprint, Java enabled, secure, relational database
management system created specifically for laptop computers, handheld computers, PDAs, and information appliances. Oracle Lite Database runs on Windows 2000/XP, Windows CE/Pocket PC, Symbian OS, and Embedded Linux.

Oracle Lite Database provides JDBC, ODBC, ADO.NET and Simple Object Data Access (SODA) APIs, for an easy-to-use C++ interface that is optimized for the object-oriented and SQL functionality of Oracle Database Lite.

Installation of the Mobile Development Kit (MDK) installs the Oracle Lite Database and all its utilities on your development machine.

Data Synchronization

A user’s data is synchronized between Oracle Lite Database and an Oracle database server.

Synchronization is accomplished by invoking the Mobile sync client, mSync, which interacts with the Mobile Server. The Mobile Server uses synchronization objects such as users, publications, publication items, and subscriptions to process client and server data changes. This technique is referred to as a publish/subscribe model.

Synchronization Process

Oracle Database Lite contains a subset of data stored in the Oracle database. This subset is stored in snapshots in the Oracle Lite database. Unlike a base table, a snapshot keeps track of changes made to it in a change log. Users can make changes in the Oracle Lite database while the device is disconnected, and can synchronize with the Oracle database
server.

There are basically three types of publication items that can be used to define synchronization; fast refresh, complete refresh, and queue based. The most common method of synchronization is a fast refresh publication item where changes are uploaded by the client, and changes for the client are downloaded.

Meanwhile, a background process called the Message Generator and Processor (MGP) periodically collects the changes uploaded by all clients and applies them to database tables. It then composes new data, ready to be downloaded to each client during the next
synchronization, based on predefined subscriptions.

Oracle Lite Sync is a two step asynchronous process:

untitled1.jpg

  1. Sync moves updated rows from the client database to the In Queue and from the Out Queue to the client database.
  2. MGP (Message Generator Processor) applies In Queue changes to base tables and Composes the changes to the base tables to client Out Queues.

Until recently, most business applications have only been accessible from network tethered PCs in offices, away from where a business’ productive assets and employees are at work, its customers are serviced, and its operations performed. A new generation of lightweight and powerful mobile devices combined with inexpensive, widely available wireless data connections promise to redefine the reach of Enterprise applications. Oracle is committed to servicing the mobile computing needs of businesses worldwide with solutions and software platforms that offer the depth of feature, scalability, reliability and performance.

Please note that above discussion is taken from Oracle white paper.

Below I have given the steps for installing Olite 10g in E- Business suite environment.

Installing OLite 10g

For installing Oracle database lite 10g for demo and checking out product features, you need to get the required software download from thislink.

Once downloaded, it need to be installed, very similar to our regular database installation. I have given the brief installation step for the same. As far as DBA is concern, the main resonsibility lies in installation and management. Please follow the below steps to install this software successfully. Also, please note that the steps below are the installtion steps for Linux OS. But though the basic steps are going to be almost same in any platform.
Note that the below discussion is for the installation on R12 instance.

Once the dump is extracted from zip file, before starting the OUI, you need to set the following environment variable.

ORACLE_HOME
JAVA13_HOME
TNS_ADMIN

For this software you need to set the path for ORACLE_HOME. You can create a directory called olite10g and set the path for this directoy as ORACLE_HOME.

JAVA13_HOME will be the location for your JAVA home. You can check the path for your java installtion, either 1.3 or 1.4 and set the path accordingly. Usually in Linux the path will be some thing like /local/java/jdk1.4.2.

TNS_ADMIN should point to $ORACLE_HOME/network/admin

Make sure you drop ‘mobileadmin’ schema with cascade option.

Connect to applications as system user and

SQL> Drop user mobileadmin cascade;

User Dropped.

SQL>

Once the above parameters are set and exported, check the DISPLAY environment variable(if installing on any of UNIX platform) and use the below command (from the software dump directory) to start the installation.

==========================================================
./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

Starting Oracle Universal Installer …

Checking requirements…

Checking operating system version: must be redhat-2.1, UnitedLinux-1.0 or redhat-3
Passed
All requirements met.

Checking if CPU speed is above 450 MHz.
Actual 3056 MHz Passed
Checking for Kernel version 2.4.21-4.EL Passed
Checking swap space: must be greater than 1536 MB.
Actual 23744MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-05-28_03-38-56AM. Please wait ..
sal Installer, Version 2.3.0.10.0
Copyright (C) 1999, 2003, Oracle. All rights reserved.

1.jpg

The above screen is just a welcome screen. Click next.

Next screen is a Inventory screen. Please note that we are here using the local inventory as specified by invPtrLoc attribute supplied, when we started the installation above. We also specified the location of oraInst.loc file. If the file doesnt exist, installer will create the file and the location of inventory will the one that we will provide in this Specify Inventory Directory screen. So basically oraInst.Loc file will be created with the inventory location as given in this screen. Give the location of your ORACLE_HOME, followed by a directory oraInventory. Even if the directory does not exist, installer will create the same.

1-1.jpg

2.jpg

In the above screen, we just specify the group of the user who is doing installation.

3.jpg

Specify File location page needs the product.jar file, which comes with software dump and name & location of ORACLE_HOME. Keep the default value and click next.

4.jpg

Press ‘Yes’ when you get such warning. The reason for this warning is that, the ORACLE_HOME directory that we are using is not empty, because we are creating the oraInventory directory in the same location. So by the time we come to this sceen, some components of inventory already got created in ORACLE_HOME directoy.

5.jpg

Select Installation Type as Mobile Server. This also includes Mobile Development Kit. Press Next.

6.jpg

On Mobile Server Repository Database Information page, you need to provide the information about, the hostname on which database resides, the tnsport of database and database sid. Once provided the info, click Next.

7.jpg

Mobile Server Repository screen will confirm your acceptance in installing the mobile server repository. This will create a schema, which will contain some schema and code objects specific to mobile administration. If you remember, before starting the installation, we have dropped mobileadmin schema for the same purpose. In case there is already existing repository, then the repository creation wizard will fail. So in usual practice, we drop the repository and create a fresh one when we install Olite 10g. ALso please note that upgrade repository option doesnt work with 10g Olite installation. Select Yes option if you want to create repository, else you can even select No.

If you select No at this point and later at some point of time, if you need to create a repository, you can do same by running just the respsitory wizard (repwizard) present in ORACLE_HOME/mobile/server/admin. But also note that, database information for creating repository cannot be given later, while creating the repository. But anyway you can change the information for the same by editing configuration file.

After the installtion, I will be mentioning the configuration file for OLite 10g installation in Oracle Apps R12.

At this point we will be going with repository creation option.

8.jpg

Port Number for OC4J HTTP Listener is the port we specify for webtogo URL for administration. We are use any of the unused port numbers, which are greater then 1024.

9.jpg

Demo Applications comes with product which can be used for testing and demo. Its totally upto the user to either install the same or skip. In this installation, we will be skipping the demo applicaitons.

10.jpg

Once all the parameters are provided, click on Install. The product installation will begin.

11.jpg

You can also see the progress, when the installation proceeds.

12.jpg

Once the product installation completes, Mobile Server Repository creation will start automatically. In this case the wizard will just have to create a schema(mobileadmin) and objects. For that it has to connect as system.

Since we have already specified the database details, where the wizard will connect, during installation part, it will just ask for system password. Please provide correct system password for your main database and click next.

13.jpg

When you click next, it will connect to database and check for mobileadmin schema, If the mobileadmin schema is found it will give upgrade option, else it will give the message as given in above screen and proceed further to create a new repository. Click next to continue.

14.jpg

When you click on next, it will ask for schema password, which you have to set in this screen. The schema name will be MOBILEADMIN, and password will be what ever you set here.

15.jpg

Administrator password if for administering the webtogo URL. Note that MOBILEADMIN is a database user, where are ADMINISTRATOR is a user for webtogo application. You can set the password for Administrator user on this screen.

16.jpg

As I said before, its going to install a new repository. Click next to continue.

17.jpg

You can see the Repository Creation progress.

18.jpg

Once the repository creation task is done. It will show the message as seen in the screen. Click on finish button to finish the wizard.

19.jpg

At the end you can just exit. Our Oracle Database Lite installation with repository creation was successful.

Next task that comes after installation is bringing up the services and sanity checking the  URL.

 

Configuration

At the starting of installation, we set 3 environment variables related to OLite 10g

ORACLE_HOME

JAVA13_HOME

TNS_ADMIN

Basically, when ever we have to manager Olite, we need to have these varaiables configuraed with correct values. Since we are talking about Olite installing in E-Buiness suite, we will talk about the configuration w.r.t R12.

The above three paramters can be set correct in a configuartion file called asgovars_ux.env present in $ASG_TOP/admin/template directory. Just edit the file and set these environment variables. Once that is done source the file. After sourcing when you see ORACLE_HOME, it will be pointing to Olite ORACLE_HOME (and not tools ORACLE_HOME).

You can go to $ORACLE_HOME/mobile/server/bin

and run the following command

(appmgr03) bin – -bash $ ./runmobileserver &
[1] 766
(appmgr03) bin – -bash $ 07/05/29 12:48:28 Warning: Error reading transaction-log file (/slot03/appmgr/olite10g/mobile_oc4j/j2ee/mobileserver/persistence/transaction.state) for recovery: premature end of file
07/05/29 12:48:28 Forced or abrupt (crash etc) server shutdown detected, starting recovery process…
07/05/29 12:48:28 Recovery completed, 0 connections committed and 0 rolled back…
07/05/29 12:48:31 Tutalii: /SLOTS/slot03/appmgr/olite10g/mobile/server/bin/jzlib.jar archive

(appmgr03) bin – -bash $

This will start the webtogo service. You can verify the same using following command

(appmgr03) bin – -bash $ ps -eaf | grep oc4j.jar
appmgr03   767   766  8 12:48 pts/3    00:00:04 /local/java/jdk1.4.2/bin/java -Xms256m -Xmx512m -jar oc4j.jar
appmgr03   971 32439  0 12:49 pts/3    00:00:00 grep oc4j.jar
(appmgr03) bin – -bash $

You can see, there is 1 oc4j.jar process that is running.

The configuration file for web-to-go is present in $ORACLE_HOME/mobile/server/bin directory, and the name is webtogo.ora.

This file basically contains the database details like, Olite ORACLE_HOME, JDBC connection string, encrypted password, maximum number of connection allowed and log & trace file details.

The port number, that we select for Olite (8080), wont be present in webtogo.ora. The port information will be present in http-web-site.xml file present in $ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/config directory. You can change the port any time in http-web-site.xml file and bounce the services, the new port will take effect.

For shutting down the service, there is no special command and we need to kill the oc4j.jar process, that is running. Thats the only way that is currently available for shutting down the Olite service.

The URL for accessing webtogo can be constructed as followes:

http://(hostname):(port)/webtogo.

The port here is webtogo port(8080), configured during installation.

Once the above installation is done, you can follow metalink note ID  386682.1 for post installation task. Note that these post installation task is required only if the installation is done for E-Business suite.

 

Oracle Database 9i Interview Questions

Published on by Advait4 Comments

Intention of this post is to get as much interview question for Oracle DBA as possible to help you get the idea about the type of questions you can expect in interviews and exams. This will help you in increasing the knowledge about oracle database 9i as some of the questions may be new to you. This post has questions related to oracle 9i DBA only.
1) Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2) You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3) How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command.

4) Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object. Extent may not be continuous.

5) Give two examples of how you might determine the structure of the table DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

6) Where would you look for errors from the database engine?

In the alert log.

7) Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8) Give the reasoning behind using an index.

Faster access to data blocks in a table.

9) Give the two types of tables involved in producing a star schema and the type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10) What type of index should you use on a fact table?

A Bitmap index.

11) Give two examples of referential integrity constraints.

A primary key and a foreign key.

12) A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13) Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14) What command would you use to create a backup control file?

Alter database backup control file to trace.

15) Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT – Instance startup. Control File is read here.

STARTUP MOUNT – The database is mounted. Datafiles are read for the status and checked with control file.

STARTUP OPEN – The database is opened. Normal users can access.

16) What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the information came from.

17) How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = ‘tst1’ into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

18) How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19) Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20) Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

21) How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

22) Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

23) Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

24) Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

25) Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

26) Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

27) Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

28) When a user process fails, what background process cleans up after it?

PMON

29) What background process refreshes materialized views?

The Job Queue Processes.

30) What is the Difference between OLTP and OLAP

OLTP is nothing but OnLine Transaction Processing ,which contains a normalised tables and online data,which have frequent insert/updates/delete.

But OLAP(Online Analtical Programming) contains the history of OLTP data, which is, non-volatile ,acts as a Decisions Support System and is used for creating forecasting reports.

31) How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

32) Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

33) How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

34) Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

35) What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

36) What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

37) Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38) When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

39) How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

40) How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41) What view would you use to look at the size of a data file?

DBA_DATA_FILES

42) What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

43) How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

44) How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

45) Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

46) You have just compiled a PL/SQL package but got errors, how would you view the errors?

SHOW ERRORS

47) How can you gather statistics on a table?

The ANALYZE command.

48) How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE;

49) What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

50) Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

References:

The above questions and answers are taken from http://www.dbasupport.com/

 

Oracle Application Express – Installtion Guide

Published on by Advait2 Comments

This article provides instructions for installing and configuring the Oracle Application Express (formerly HTML DB) software release 2.2. Please note that these instructions cover the standalone version only.

Installing Oracle Application Express is a two step process:

  1. Configure an Oracle HTTP Server (Release 9.0.3 or higher) with mod_plsql which is used to connect to the Oracle database where the Oracle Application Express objects will be installed. It is also possible to use Oracle 9i Application Server release 1 (1.0.2.2) or higher.
  2. Install the database objects that make up Oracle Application Express to a pre-existing Oracle database (Oracle9i Release 2 (9.2.0.3) or later).

Important Note: When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database software.

The Oracle HTTP Server cannot be installed into an existing Oracle Home. It must be installed into a new Oracle Home!

Prerequisites for the Installation:

This section describes the requirements for installing Oracle Application Express, Release 2.2.

Browser Requirements

To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers meet this requirement:

  • Microsoft Internet Explorer 6.0 or higher (Windows only)
  • Netscape Communicator 7.2 or higher
  • Mozilla 1.2 or higher
  • Firefox 1.0 or higher

Operating System Requirements

From what I have read and tested, Oracle Application Express can be installed on the following Operating System platforms:

Linux – Red Hat Enterprise version AS/ES 2.1 or higher; or SUSE Enterprise Server version SLES-8 or higher.

  • Solaris 9 or higher.
  • Windows 2000 Professions or higher (with service pack 3 or higher)
  • Windows XP Professional
  • Windows 2003 (32-bit systems)

Disk Space Requirements

Verify that the file system that contains the Oracle home directory contains at least 460MB of free disk space for the installation. Also during the installation process, about 110MB of temporary disk space will be required.

Database Requirements

For installing Application Express we need to have a database created before. Same database will be used for creating the objects required for Oracle Application Express.

The installation of Oracle Application Express requires certain objects to be created in an Oracle database. With Oracle Application Express 2.2, the database is required to be Oracle9i Release 2 (9.2.0.3) or later. All of the Oracle Application Express database objects should be created in a separate tablespace which we will create later on in this article.

Shared Pool Size Requirements

Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.

Determine the current value of the shared_pool_size parameter:

SQL> show parameter shared_pool_size

Verify JOB_QUEUE_PROCESSES

The initialization parameter JOB_QUEUE_PROCESSES for the Oracle database determines the maximum number of concurrently running jobs. Starting with Oracle Application Express Release 2.0, transactional support and SQL scripts require jobs. If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.

You can view the number of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement:

SQL> SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;

If you need to modify the JOB_QUEUE_PROCESSES initialization parameter, log into the database as SYSDBA using SQL*Plus and run the ALTER SYSTEM … statement. For example, to set the number of JOB_QUEUE_PROCESSES to 20, use:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

Installing Oracle HTTP Server

When installing Oracle Application Express, you will be required to install the Oracle HTTP Server which can be found on the Oracle10g Release 2 Companion CD. Although not a requirement, I will be installing the Oracle HTTP Server to the same machine which already includes an installation of the Oracle Database.

Important Note: The Oracle HTTP Server cannot be installed into an existing Oracle Home. If another Oracle product is installed on the same server, you will need to create a new Oracle home name and location for the Oracle HTTP Server

This section assumes you have access to and will be installing the Oracle HTTP Server from the Oracle10g Release 2 Companion CD. Note that with a few minor modifications, you can also install an earlier version of the Oracle HTTP Server from the Oracle10g Release 1 Companion CD.

You can perform the install from the CD or download it from Oracle Technology Network (OTN)

After downloading and unzipping the Oracle10g Release 2 Companion CD software to a temporary directory, follow the instructions below to install the Oracle HTTP Server:

Step 1) Start the Oracle Universal Installer from the Oracle10g Release 2 Companion CD

./runInstaller -invPtrLoc /u01/app/orsbox/OHS/oraInst.Loc
test.jpg

On Select a product to Install Select Oracle Database 10g Companion Products 10.2.0.1.0
2.jpg

3.jpg
On Specify Home Details Select Different Oracle Home path and name (other then ORACLE_HOME for database)

4.jpg

On Available Product Components screen select ‘Apache Standalone’ product only

5.jpg

Pre-Requisite check will run. Check if you get any error here. If there is some error then correct the same and repeat the above steps again.

6.jpg

Check the summary screen and press in ‘Install‘ button

7.jpg

Installer will show you the progress for installation

8.jpg

Configuration Assistant will configure Apache HTTP server and OC4J component.

9.jpg

End of installation. Press Exit and come out of the wizard.

Test the Oracle HTTP Server Installation

After the installation of the Oracle HTTP Server is complete, you should test it. Point your browser to the URL that was displayed on the “End of Installation” screen by the Oracle Universal Installer. For me, the URL was http://ocvmrh2124.us.oracle.com:7777/

Starting / Stopping / Restarting Oracle HTTP Server:

Start All: $ORACLE_HOME/opmn/bin/opmnctl startall
Stop All : $ORACLE_HOME/opmn/bin/opmnctl stopall
Start HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server
Stop HTTP Process : $ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
Restart HTTP Process: $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server

Installing Oracle Application Express:

Download Oracle Application Express 2.2

APEX 2.2 can be downloaded from official OTN site

Oracle Application Express was previously known as HTML DB. With the release of 2.0, Oracle officially changed the name from HTML DB to Oracle Application Express (APEX) on January 30, 2006.

Important Note: Note that the version of HTML DB shipped on the Oracle10g Release 2 Companion CD is version 1.6. With Oracle10g Release 1, the version of HTML DB is 1.5.

After downloading the file from OTN, unzip the same.

You need to create a new tablespace for APEX application.

Create Tablespace for Oracle Application Express Database Objects:

SQL> CREATE TABLESPACE htmldb DATAFILE ‘/u01/app/orsbox/oradata/htmldb_01.dbf’ SIZE 1000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT auto;

Tablespace created.

Create Oracle Application Express Database Objects:

After the new tablespace has been created, the next step is to create all required database objects for Oracle Application Express. These database objects are all created by running a single SQL script that can be found in the Oracle Application Express software distribution downloaded earlier in this section. The SQL script to run is named apexins.sql and can be found in the apex directory created when unzipping the software distribution. For the purpose of this example, I ran the script as follows (note that this install script can take quite awhile to complete):

Change the directory to APEX directory you have unziped

[orsbox@ocvmrh2124 conf]$ cd apex

Connect to database as sysdba and run following command

@apexins welcome htmldb htmldb temp /i/ orcl

Below is the brief explanation for this command.

Parameters to APEX Database Object Creation SQL Script
welcome The password for the Oracle APEX administrator account, the APEX schema owner (FLOWS_020200), the APEX files schema owner (FLOWS_FILES), and the APEX public user schema (APEX_PUBLIC_USER). The APEX schema owner is the user or schema into which Oracle Application Express database objects will be installed. The APEX files schema owner is the user or schema where uploaded files are maintained in Oracle Application Express.
htmldb Name of the default tablespace for the APEX schema owner – (FLOWS_020200).
htmldb Name of the default tablespace for the APEX files schema owner – (FLOWS_FILES). Note that Oracle Application Express creates a table namedFLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ in this tablespace used to store any uploaded files. Although this could have been a separate tablespace, it is just as easy having the all objects in a single tablespace.
temp Name of the temporary tablespace to be used for all schemas created by the Oracle Application Express install process.
/i/ Virtual directory that is used for images rendered by Oracle Application Express. To support future Oracle HTML DB upgrades, define the virtual image directory as/i/.
orcl Name of the Oracle Net connect string to the database where Oracle Application Express database objects are to be installed. If this is a local install, you can usenone or NONE.

During the Oracle Application Express install process, three schemas will be created. A description of each of these schemas is provided in the following table:

Oracle Application Express Database Schema Accounts
FLOWS_020200 This is basically the schema owner of all objects (tables, views, packages, functions, etc.) used by Oracle Application Express. The only exception is the table installed in the FLOWS_FILES schema (FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$) for storing uploaded files. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
FLOWS_FILES This schema is used to store uploaded files to Oracle Application Express. These can include scripts, documents, cascading style sheets, etc. Note that this account is locked at the end of the installation and cannot be used unless it is first unlocked.
APEX_PUBLIC_USER This schema is used by Oracle Application Express to login to the database and has access to database objects in the above two schemas for all application functionality.

Recompiling Invalid PL/SQL Packages:

After installing the Oracle Application Express database objects, it is recommended (however not required) to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

Run the utlrp.sql script from the Oracle Database home:

SQL> @?/rdbms/admin/utlrp.sql

Install Oracle Application Express Database Images:

After successfully creating all of the required Oracle Application Express database objects, the next step is to copy the necessary images, templates, cascading style sheets, themes, java scripts, (and several other file types) into the directory tree of the Oracle HTTP Server. The Oracle HTTP Server was installed earlier in this article and is located at:

$ORACLE_HOME = /u01/app/orsbox/OHS

The images and other file types to copy can be found in the Oracle Application Express software distribution downloaded earlier under the …/apex/images directory.

The files will need to be copied to the $ORACLE_HOME/Apache/Apache/images directory as illustrated in the following examples:

[orsbox@ocvmrh2124 orsbox]$ cd apex
[orsbox@ocvmrh2124 orsbox]$ cp -R images $ORACLE_HOME/Apache/Apache

Configure Database Access Descriptor:

Now that the Oracle Application Express images are in place, the next step is to configure the Oracle Database Access Descriptor (DAD) file. The configuration file is named dads.conf and should be located in the $ORACLE_HOME/Apache/modplsql/conf directory. The DAD is used by the Oracle HTTP Server and mod_plsql extension module to connect to the Oracle database.

Change the below text according to your setup and copy the below text to dads.conf

Parameters to be changed in below text are

Alias -> This is the alias for images directory under Apache Home
PlsqlDatabasePassword -> This is the password for APEX user, you can set any password here, which will be used further
PlsqlDatabaseConnectString -> This is the connect string for your database. This is of the form HOST.DOMAIN:PORT:DB_NAME

=============================================================== Alias /i/ “/u01/app/orsbox/OHS/Apache/Apache/images/”
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDatabasePassword welcome
PlsqlDatabaseConnectString ocvmrh2124.us.oracle.com:1522:ORCL
PlsqlAuthenticationMode Basic
PlsqlDefaultPage apex
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
</Location>
===============================================================

For a complete description of each of the parameters used in the dads.conf configuration file, read through the file dads.README located in the same directory. The following table provides a brief description of the key parameters:

Oracle DAD Configuration Parameters and Description
Alias This is set to the virtual directory you specified when running theapexins.sql file to create the Oracle Application Express database objects. This value must match the value you specified which is recommended to be /i/. The virtual directory should refer to the location where you copied the Oracle Application Express images to in the Oracle HTTP Server directory tree.
<Location /pls/apex> This is the name of the virtual path that will be used to access application in Oracle Application Express. For example,http://linux3:7777/pls/apex/f?p=100.
PlsqlDatabaseUsername This is the database username that will be used by the mod_plsqlextension module to connect to Oracle database. The database username should be APEX_PUBLIC_USER – the schema created by theapexins.sql script. All connections coming from an Oracle Application Express application, regardless of their login id and password, will be connected to the database with this username. This even includes applications that use a different parsing schema! Consider an application that uses a parsing schema of DEV_WS. The session user will be APEX_PUBLIC_USER but the Current User will be set to DEV_WS: 

SELECT
    sys_context('USERENV','SESSION_USER') "Session User"
  , sys_context('USERENV','CURRENT_USER') "Current User"
FROM dual;

Session User      Current User
----------------- ------------
APEX_PUBLIC_USER  DEV_WS
PlsqlDatabasePassword This must match the password you provided during the creation of the Oracle Application Express database objects (@apexins.sql). This is the password used by the PlsqlDatabaseUsername (above) to connect to the Oracle database.
PlsqlDatabaseConnectString The connection URL string used to connect to the Oracle database in the format server.domain:port:sid. If the database is located on the same server, you can use localhost.

Obfuscate the PlsqlDatabasePassword Parameter:

Notice that when we configured the dads.conf file, we put in the plain text password for the Oracle database user. Exposing plain text passwords is never good security practice, especially for this sensitive database account. Fortunately, Oracle provides an obfuscation utility that targets and encrypts the PlsqlDatabasePassword entry in the dads.conf file. This utility is named dadTool.pl and can be found in the $ORACLE_HOME/Apache/modplsql/conf directory.

Run the dadTool.pl utility as follows:

[orsbox@ocvmrh2124 orsbox]$ export ORACLE_HOME=/u01/app/orsbox/OHS
[orsbox@ocvmrh2124 orsbox]$ echo $LD_LIBRARY_PATH

[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH;export PATH
[orsbox@ocvmrh2124 orsbox]$ PATH=$ORACLE_HOME/perl/bin:$PATH:.;export PATH
[orsbox@ocvmrh2124 orsbox]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
[orsbox@ocvmrh2124 orsbox]$ PERL5LIB=$ORACLE_HOME/perl/lib:.;export PERL5LIB
[orsbox@ocvmrh2124 orsbox]$ cd $ORACLE_HOME/Apache/modplsql/conf
[orsbox@ocvmrh2124 conf]$ perl dadTool.pl -o

Information
—————————————————————————-
Backed up older dads.conf as /u01/app/orsbox/OHS/Apache/modplsql/conf/dads.conf.orig.2007-07-13_09-04

All passwords successfully obfuscated. New obfuscations : 1
[orsbox@ocvmrh2124 conf]$

Restart the Oracle HTTP Server:

[orsbox@ocvmrh2124 conf]$ $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server
opmnctl: restarting opmn managed processes…

Now you can access your APEX URL: http://ocvmrh2124.us.oracle.com:7777/pls/apex

 

Oracle – Stellent

Published on by Advait2 Comments

Oracle – Stellent

Oracle announced that it has agreed to acquire Stellent, Inc. (NASDAQ: STEL), a global provider of enterprise content management (ECM) software solutions, through a cash tender offer for $13.50 per share, or approximately $440 million. On December 14, 2006, Oracle completed its acquisition of Stellent Inc., a global provider of enterprise content management (ECM) software solutions.

The combination of Oracle and Stellent offers customers secure, reliable, and highly scalable content management solutions that meet the day-to-day needs of all users across the enterprise, while also delivering sophisticated ECM capabilities to professional users.

More details about the same in press release

Oracle Enterprise Content Management:

Oracle Enterprise Content Management is the industry’s most unified enterprise content management platform that enables you to leverage industry-leading document management, Web content management, digital asset management, and records management functionality to build your business applications. Building a strategic enterprise content management infrastructure for content and applications helps you to reduce costs, easily share content across the enterprise, minimize risk, automate expensive, time-intensive and manual processes, and consolidate multiple Web sites onto a single platform.

Here we will see the installation and use of Oracle Universal Content Management.

Pre-Requisite:

UCM (Universal Content Management) uses any of the existing database like Oracle, IBM DB2, Sybase, SQL Server etc and creates repository for document management.
Also UCM needs apache installtion version 2.0 or more.

Pre-Installation Steps:

Before starting the installation, make sure to create a new tablespace in your database for conctent management. Also create a new user for content management. This will be the user used by tool for entry into the database.

SQL> create tablespace cs_tbs datafile ‘/u01/app/oracle/product/oradata/cs_tbs01.dbf’ size 500M extent management local
2  segment space management auto;

Tablespace created.

SQL> create user csuser identified by welcome
2  default tablespace cs_tbs
3  temporary tablespace temp
4  quota unlimited on cs_tbs account unlock;

User created.

Installating Apache (version 2.0 or more):

You need to have Apache http server installed for accessing this product. The version for Apache should be 2.0 or more. You can install apache by following this URL.

Universal Content Management Installation:

[oracle@ocvmrh2023 linux]$ ./setup.sh

Please select your locale from the list.
*1. English-US
2. English-UK
Choice? 1

Throughout the install, when entering a text value, you can press Enter to accept the default that appears between square brackets ([]). When selecting from a list, you can select the choice followed by an asterisk by pressing Enter.

Select installation type from the list.
*1. Install new server
2. Update a server
Choice? 1

Content Server Installation Directory

Please enter the full pathname to the installation directory.
Content Server Core Folder [/stellent/server]:/u01/app/oracle/product/UCM/stellent/server

Create Directory
*1. yes
2. no
Choice? 1

Java virtual machine
*1. Sun Java 1.5.0_11 JDK
2. Specify a custom Java virtual machine
Choice? 1
Installing with Java version 1.5.0_11.

Enter the location of the native file repository. This directory contains the native files checked in by contributors.
Content Server Native Vault Folder [/u01/app/oracle/product/UCM/stellent/server/vault/]:

Create Directory
*1. yes
2. no
Choice? 1

Enter the location of the web-viewable file repository. This directory contains files that can be accessed through the web
server.
Content Server Weblayout Folder [/u01/app/oracle/product/UCM/stellent/server/weblayout/]:

Create Directory
*1. yes
2. no
Choice? 1

This server can be configured to manage its own authentication or to allow another master to act as an authentication proxy.
Configure this server as a master or proxied server.
*1. Configure as a master server.
2. Configure as server proxied by a local master server.
Choice? 1

During installation, an admin server can be installed and configured to manage this server. If there is already an admin server
on this system, you can have the installer configure it to administrate this server instead.
Select admin server configuration.
*1. Install an admin server to manage this server.
2. Configure an existing admin server to manage this server.
3. Don’t configure an admin server.
Choice? 1

Enter the location of an executable to start your web browser. This browser will be used to display the online help.
Web Browser Path [/usr/bin/mozilla]:

Content Server locale
*1. English-US
2. English-UK
Choice? 1

Please select the region for your timezone from the list.
*1. Use the timezone setting for your operating system
2. Pacific
3. America
4. Atlantic
5. Europe
6. Africa
7. Asia
8. Indian
9. Australia
Choice? 1

If you are working with multilingual content, you may want to set the file encoding for the content server to UTF-8. If not set
to UTF-8, the server will use the native encoding of your operating system.
Use UTF-8 file encoding
1. yes
*2. no
Choice? 2

Please enter the port number that will be used to connect to the Content Server. This port must be otherwise unused.
Content Server Port [4444]:

Please enter the port number that will be used to connect to the Admin Server. This port must be otherwise unused.
Admin Server Port [4440]:

Enter a security filter for the Content Server port. Hosts which are allowed to communicate directly with the Content Server
port may access any Content Server managed resource. Insure that hosts which need access are included in the filter. See the
installation guide for more details.
Incoming connection address filter [127.0.0.1]:127.0.0.1|140.87.222.145

*** Content Server URL Prefix

The URL prefix specified here is used when generating HTML pages that refer to the contents of the weblayout directory within
the installation. This prefix must be mapped in the web server Additional Document Directories section of the Content Management
administration menu to the physical location of the weblayout directory.

For example, “/stellent/” would be used in your installation to refer to the URL http://stellent.company.com/stellent which
would be mapped in the web server to the physical location /stellent/server/weblayout.
Web Server Relative Root [/idc/]:

Enter the name of the local mail server. The Content Server will contact this system to deliver email.
Company Mail Server [mail]:

Enter the e-mail address for the system administrator.
Administrator E-Mail Address [sysadmin@mail]:

*** Web Server Address

Many generated HTML pages refer to the web server you are using. The address specified here will be used when generating those
pages. The address should include the host and domain name in most cases. If your webserver is running on a port other than 80,
append a colon and the port number.

Examples: http://www.stellent.com, stellent.company.com:90
Web Server HTTP Address [ocvmrh2023]:

Enter the name for this instance. This name should be unique across your entire enterprise. It may not contain characters other
than letters, numbers, and underscores.
Content Server Instance Name [idc]:

Enter a short label for this instance. This label is used on web pages to identify this instance. It should be less than 12
characters long.
Content Server Instance Label [idc]:

Enter a long description for this instance.
Content Server Description [Content Server idc]:

Web Server
*1. Apache
2. Sun ONE
3. Configure manually
Choice? 1

Please select a database from the list below to use with the Content Server.
Content Server Database
*1. Oracle
2. Microsoft SQL Server 2005
3. Microsoft SQL Server 2000
4. Sybase
5. DB2
6. Custom JDBC settings
7. Skip database configuration
Choice? 1

Manually configure JDBC settings for this database
1. yes
*2. no
Choice? 2

Manually configure JDBC settings for this database
1. yes
*2. no
Choice? n
Choice? 2

Oracle Server Hostname [localhost]:ocvmrh2023

Oracle Listener Port Number [1521]:

*** Database User ID

The user name is used to log into the database used by the content server.
Oracle User [user]:csuser

*** Database Password

The password is used to log into the database used by the content server.
Oracle Password []:welcome

Oracle Instance Name [ORACLE]:orcl

Configure the JVM to find the JDBC driver in a specific jar file
1. yes
*2. no
Choice? 2

The installer can attempt to create the database tables or you can manually create them. If you choose to manually create the
tables, you should create them now.
Attempt to create database tables
1. yes
*2. no
Choice? 1

The database you selected supports using Unicode (UCS-2) text fields instead of native encoded text fields. If you are working
with multilingual content, you may want to use Unicode text fields instead of native encoding text fields.
Use Unicode text fields
1. yes
*2. no
Choice? 2

Select components to install.
1. CheckOutAndOpen: Checkout and Open component
*2. CheckSCSHealth: Content Server health checking support
*3. comptool: Command-line component installation tool
*4. ConfigMigrationUtility: Configuration packaging and installation utility
5. ExtranetLook: Extranet website support
6. FileStoreProvider: Alternate FileStoreProvider Implementation
7. LinkManager8: Hypertext link management support
8. OracleQueryOptimizer: Tool for configuring database query hints
*9. PopUpCalendar: Popup calendar for selecting dates
10. ProxyConnections8: Proxy connections plugin support
11. ThreadedDiscussions: Threaded discussion management
*12. CoreWebdav: Content Server Core WebDAV support
*13. WebUrlMapPlugin: Web URL mapping tool
*14. WsdlGenerator: WSDL generator tool
*15. YahooUserInterfaceLibrary: Yahoo User Interface components
16. DBSearchContainsOpSupport: Database Search ‘contains’ operator support.
Enter numbers separated by commas to toggle, 0 to unselect all, F to finish:

Checking configuration. . .

Configuration OK.

Review install settings. . .
Content Server Core Folder: /u01/app/oracle/product/UCM/stellent/server
Java virtual machine: Sun Java 1.5.0_11 JDK
Content Server Native Vault Folder: /u01/app/oracle/product/UCM/stellent/server/vault/
Content Server Weblayout Folder: /u01/app/oracle/product/UCM/stellent/server/weblayout/
Proxy authentication through another server: no
Install admin server: yes
Web Browser Path: /usr/bin/mozilla
Content Server locale: English-US
Content Server Port: 4444
Admin Server Port: 4440
Incoming connection address filter: 127.0.0.1|140.87.222.145
Web Server Relative Root: /idc/
Company Mail Server: mail
Administrator E-Mail Address: sysadmin@mail
Web Server HTTP Address: ocvmrh2023
Content Server Instance Name: idc
Content Server Instance Label: idc
Content Server Description: Content Server idc
Web Server: Apache
Content Server Database: Oracle
Manually configure JDBC settings for this database: false
Oracle Server Hostname: ocvmrh2023
Oracle Listener Port Number: 1521
Oracle User: csuser
Oracle Password: zOM7/dPTbxKhdCRQzA4jSV9eBjlsXKu4A4fJY/StGPU=
Oracle Instance Name: orcl
Configure the JVM to find the JDBC driver in a specific jar file: false
Attempt to create database tables: yes
Use Unicode text fields: no
Components:
CheckSCSHealth,comptool,ConfigMigrationUtility,PopUpCalendar,CoreWebdav,

WebUrlMapPlugin,WsdlGenerator,YahooUserInterfaceLibrary

Proceed with install
*1. Proceed
2. Change configuration
3. Recheck the configuration
4. Abort installation
Choice? 1
Finished install type Install at 7/26/07 8:20 AM.

Post Install Steps:

Carry out the following changes in Apache configuration file httpd.conf. Add following entry (after making the changes specific to your installation) at the end of file.

LoadModule IdcApacheAuth /u01/app/oracle/product/UCM/stellent/server/shared/os/linux/lib/IdcApache22Auth.so
IdcUserDB idc “/u01/app/oracle/product/UCM/stellent/server/data/users/userdb.txt”

Also set the alias for weblayout directory present in install base. Add the below content (after making the changes specific to your installation) in the lines of “location” settings in configuration file.

Alias /idc “/u01/app/oracle/product/UCM/stellent/server/weblayout”
<Location /idc>
Order allow,deny
Allow from all
DirectoryIndex portal.htm
IdcSecurity idc
</Location>

Login Access:

http://ocvmrh2023.us.oracle.com:7771/idc/