Showing posts with label ORACLE APPS QUESTIONS. Show all posts
Showing posts with label ORACLE APPS QUESTIONS. Show all posts

GATHER SCHEMA STATS

 Why sometimes gather stats runs for longer time than normal.

There are many possible causes but the most common are:

a)  Database/application process or processes are running which are updating the database. When GSS runs it will invalidate all of the stats on the object it is analyzing. If a process then trying to update or use that table it will start doing full table scan which will affect the overall database performance, which in turn impact GSS.

b)  Large amount of data has been added to the database. The more records you have the longer the GSS will take to complete the process.

c)  Recollecting CBO stats on tables that have not changed. If a table has 100 million rows then gathering stats on that table will take a long time, however if no or little changes are made then there is no need to delete all of the old stats and regather them (which is what you are doing)

d)  Gathering statistics invalidates cursors which can hamper performance.( Unless you use the ‘No Invalidate’ option)

 

IMPORTANT TABLES/VIEWS RELATED TO GATHER SCHEMA STATISTICS

1)      FND_STATS_HIST

To record the time taken for gathering the statistics for the different types of objects.

2)      FND_HISTOGRAM_COLS

Gather Schema stats create the histogram for the specified columns in the tables.

 

IMPORTANT SCRIPTS RELATED TO GATHER SCHEMA STATISTICS

1) If the custom schema is not registered, it will not show up in the LOV for schema selection for the mentioned concurrent programs.

You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:

select distinct(upper(oracle_username)) sname

from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id order by sname;

 

How to verify if the current gathered statistics are correct?

 We use the Verify Stats report to determine whether the current statistics are accurate.

This report is a utility provided with FND_STATS, and can be run as follows:

SQL> set server output on

SQL> set long 10000

SQL> exec fnd_stats.verify_stats(‘schema’, ‘object_name’);

 

GATHER SCHEMA STATISTICS NOT RUNNING FOR CUSTOM MODULES. WHY?

When we submit Gather Schema Stats with Parameter  ALL, concurrent request will complete successfully but  custom schemas may not get analyzed.

Script to check if custom schema is analyzed :

Sql > select count(table_name)  from  dba_tables  where  last_analyzed  is  not null  and  owner= <custom_schema_name>;

Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.

Reason:

Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables

ie  FND_ORACLE_USERID  and  FND_APPLICATIONS_TL

However , when Gather schema statistics is submitted it uses the below query to get schema information

Sql > select distinct upper(oracle_username) sname

from fnd_oracle_userid a,

fnd_product_installations b

where a.oracle_id = b.oracle_id

order by sname;

Note : When custom schemas are created the entry is not made in  FND_PRODUCT_INSTALLATIONS  and hence it is not picked up in the above query.

Solution :

Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.

Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.

Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.



http://expertoracle.com/2013/02/01/gather-statistics-in-r12-and-11i/



 why we run datapatch:

The datapatch utility will run the necessary apply scripts to load the modified SQL files into the database. Entires will be added to both DBA_REGISTRY_HISTORY and DBA_REGISTRY_SQLPATCH views automatically.

DEBAM1> SELECT patch_id, status FROM dba_registry_sqlpatch;


  PATCH_ID STATUS

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

  22139226 SUCCESS

  21948354 SUCCESS

  20204035 SUCCESS

  22139226 SUCCESS

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

  30805558 END

  20204035 END

  31219939 END

  31113348 BEGIN



it will update like begin then end then sucess





Why we use open resetlogs?

Qn 1 :

A RESETLOGS is required in either of


a. Recovery using BACKUP CONTROLFILE

b. Incomplete Recovery


If you are using a Backup controlfile (whether from a Binary Backup or actually via a CREATE CONTROLFILE), the Controlfile is not current -- therefore it does not have the database SCN and LogSequenceNumbers. The RESETLOGS updates this information back to the controlfile (normally, a Recovery is the other way round with the controlfile's SCN, being the highest, driving the Recovery).

Also, a Resetlogs is required in both cases to ensure that the older Redo Logs (e.g. they might still be on disk) are not used -- the Resetlogs creates a new Incarnation of the database.


Qn 2:

There is no difference between an ALTER DATABASE OPEN; and an ALTER DATABASE OPEN NORESETLOGS;.

The NORESETLOGS is the default action in an OPEN if you do not specify RESETLOGS.


(Obviously, Oracle check to see if you have used a Backup Controlfile and/or done an Incomplete Recovery, in which case it does not allow you to OPEN without the RESETLOGS).




AUTOCONFIG IS NOT RUN FOR ALL PATCHES

  ###############WHY AUTOCONFIG IS NOT RUN DURING PATCH########

 Skipping ...

 Running AutoConfig since none of its templates were

 patched during this run of adpatch.

Difference between apps and applsys

 Apps is centralised schema for all products in oracle.it has only access of views, synonyms...etc.

Applsys is only used to Handel FND and AOL other products base tables (ap,ar...etc)which required for selected responsibility is is valid or not after user clicked on particular responsibility.

Applsyspub/pub is dB user which is used to authenticate our Sso/anyuser when we login to EBS.

Guest user is dummy user which connects jdbc driver to connect users which doesn't have any roles.ex--istore,iprocurement..etc.


Role of APPLSYSPUB user/schema in Oracle Applications:

When we login to applications,initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.

  • The public ORACLE username and password that grants access to the Oracle E-Business Suite initial sign-on form. The default is APPLSYSPUB/PUB.
  • Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.
  • In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.

When Autoconfig is not being used:

If you are not using Autoconfig you must manually edit the following configuration files :

1) FND_TOP/resource/appsweb.cfg
2) OA_HTML/bin/appsweb.cfg
3) FND_TOP/secure/HOSTNAME_DBNAME.dbc


To change password of APPLSYSPUB with FNDCPASS:

$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] ORACLE APPLSYSPUB [new_passs].


0 & Y are flags for FNDCPASS
0 is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form)
'Y' indicates that this method is directly invoked from the command-line and not from the Submit Request Form.

  • All application tier processes (Apaches) must be restarted following the password change.


Role of GUEST user/schema in Oracle Applications:

  • GUEST is a dummy schema.
  • By default it has ORACLE as password.
  • GUEST/ORACLE password is present in DBC file at $FND_TOP/secure directory as well as at $FND_TOP/secure/SID_hostname directory.
  • If a user logs in without any role mappings, the user will get the Guest role, which has a default permission of "R".
  • GUEST user is used by JDBC Drivers and Oracle Self Service Web Applications like istore, irecruitment, iprocurement, ipayables, ireceivables etc to make initial Connection.

Role of APPLSYS & apps user/schema in Oracle Applications:

  • APPLSYS user is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc. In the same manner APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
  • Applsys schema has applications technology layer products like FND and AD etc.
  • Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
  • APPS is central Schema which holds synonyms for all other Users Database Objects.

Note: APPLSYS and APPS should have same password.

Reason why these contains same password.

Both apps & applsys need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.

During signon process it uses both applsys and apps, hence this expects both the password to be identical. If the password for applsys & apps are not identical (Different) Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login.

Difference B/W APPLSYSPUB & GUEST:

  • APPLSYSPUB/PUB - is DB user which is used by any utility to retrieve APPS schema password for further logins.
  • GUEST/ORACLE - is EBS user with no or max limited privileges to execute authorization function.

DIFFERENCE BETWEEN SYS and SYTEM

The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
  • SYS
    This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.
    The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
  • SYSTEM
    This account can perform all administrative functions except the following:
    • Backup and recovery
    • Database upgrade


SYS

  • Automatically created when Oracle database is installed
  • Automatically granted the DBA role
  • Has a default password: CHANGE_ON_INSTALL (make sure you change it)
  • Owns the base tables and views for the database data dictionary
  • The default schema when you connect as SYSDBA
Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.

SYSTEM

  • Automatically created when Oracle database is installed
  • Automatically granted the DBA role
  • Has a default password: MANAGER (make sure you change it)
  • Used to create additional tables and views that display administrative information
  • Used to create internal tables and views used by various Oracle database options and tools
Never use the SYSTEM schema to store tables of interest to non-administrative users.

To Get Forms Runtime Diagnostics also know as FRD logs

 ######Tracing And Logging For Forms In Oracle Applications [ID 438652.1]######

How to check Forms are Implemented in socket mode or servlet mode. 

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

 1. R12.1 Forms can be implemented in servlet mode or socket mode. Check file $FORMS_WEB_CONFIG_FILE (or $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg) to find which mode forms runs:


In Servlet mode:
serverURL=/forms/lservlet
connectMode=servlet    

In Socket mode:
serverURL=(should be blank)
connectMode=Socket

This can be also verified by Profile option "ICX: Forms Launcher" value https://hostname.domain:port/forms/frmservlet on site level.

How To Enable FRD logs?

1: Obtain FRD Trace Using Appsweb.cfg
2: Obtain FRD Trace Using Profile Options
3: Obtain FRD in an ADHOC way

1) To enable FRD on Site level:
In appsweb.cfg, set "record=collect" as shown below (under ENVIRONMENT SPECIFIC PARAMETERS section)

# Sub argument for other params
record=collect

also can specify the log name
log=site1.log 

2) To enable FRD on User level:
Change Profile option "ICX: Forms Launcher" on user level to https://hostname.domain:port/forms/frmservlet?record=collect

Then, launch forms after logging onto EBS (usually without any services downtime, but may need to bounce Apache or clear cache). Forms shall popup a note "Forms Runtime Diagnostics is enabled, Please note this can affect performance." before forms shows up.

By default, trace file collect_<pid> gets written in folder $FORMS_TRACE_DIR, where <pid> is the process identifier.  "grep" the pid to find which os process created it.

Optionally, in appsweb.cfgs, specify the log name
log=user1.log
(Note: Log file site1.log or user1.log will be saved in folder $FORMS_TRACE_DIR. This folder may need manually cleaning from time to time.)

3) Enabling FRD (in R12.1) by URL https://hostname.domain:port/forms/frmservlet?record=collect ( or https://hostname.domain:port/forms/frmservlet?record=collect+log=user1.log )
Enter EBS userID/password to access forms directly. But it may give error:
APP-FND-01542: This Applications Server is not authorized to access this database.

To get this working, modify current context file by changing “s_appserverid_authentication” value from SECURE to OFF. Then shutdown apps and run Autoconfig.

3. When QA uses Vugen 12 for scripting and uses HP Performance Center (PC) 12 to run the scripts to test EBS R12.1.3 site performance, I was asked to change Profile option "ICX: Forms Launcher" on user level from https://hostname.domain:port/forms/frmservlet to
https://hostname.domain:port/forms/frmservlet?play=&record=names

I did not know what that profile value really does. But after the change, performance testing worked by PC 12 for our QA.

NOTES:  For JWS, do NOT use Method 2 of Option 1 in Doc ID 438652.1 or follow Doc ID 373548.1 (How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12) to enable FRD by setting up profile option Forms Runtime Parameters to "record=forms tracegroup=0-97". It will give error "FRM-90926: Duplicate Parameter on Command Line" without launching forms.


OUTPUT
=======
When a form is run with FRD enabled, a combination of external user-application interactions and internal Forms processing events are written in chronological order to a log file. These events can be analyzed to determine user actions and corresponding system responses, which aid in problem diagnosis and issue resolution. Kindly refer below display for a brief extract from a frd log file.


Extracts from a frd log file :

File Name: /oracle1/PROD/inst/apps/PROD_vkaria/logs/ora/10.1.2/forms/collect_32188
Process ID: 22334
Client IP: 141.19.111.99
Forms 10.1 (Forms Runtime) Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.5.0 (Production)
Oracle Virtual Graphics System Version 10.1.2.0.0 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.4.0 Production


Opened file: /oracle1/PROD/apps/apps_st/appl/fnd/12.0.0/forms/US/FNDSCSGN.fmx

ON-LOGON Trigger Fired:
Form: FNDSCSGN

State Delta:
FNDSCSGN, 1, Trigger, Entry, 2016355936, ON-LOGON

FNDSCSGN, 2, Prog Unit, Entry, 2017421936, /FNDSCSGN-1/P53_04_JAN_200703_41_43

FNDSCSGN, 3, Prog Unit, Entry, 2018075936, /FNDSCSGN-1/DO_LOGON


Executing DEFAULT_VALUE Built-in:
In Argument 0 - Type: String Value: NULL
In Argument 1 - Type: String Value: GLOBAL.FNDSCSGN_UNAME

Executing GET_APPLICATION_PROPERTY Built-in:
In Argument 0 - Type: Number Value: 73
Out Argument 0 - Type: String Value: NULL

Note:http://appsdbastuff.blogspot.com/2010/04/forms-runtime-diagnostics-frd-tracing.html

Forms Servlet mode vs Socket mode

how can we check forms are in socket mode or servelet mode?
[appogfh1@ogerpl3851dv orpogfh1_ogerpl3851dv]$ grep connectMode $FORMS60_WEB_CONFIG_FILE
connectMode=socket

also from formsweb.cfg file also we can check.

 

Oracle Forms can be run in either servlet mode or socket mode. 

§  Oracle Applications 11i is based on Forms 6i and is configured to run in socket mode by default. 

§  Oracle Applications 12i is based on Forms 10g and is configured to run in servlet mode by default.  


What is Forms Socket Mode?The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection. 

§  The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks.

§  For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.

§  Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it’s not the best choice for application deployment via unsecured network paths via the Internet.

§  A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks.

Oracle Applications Release12 by default configures Forms 10g stack in servlet mode, as this is the preferred and recommended deployment model for forms on the web.  In this mode a java servlet called the Forms Listener servlet manages the communication between the Forms Java Client and the Oracle AS Forms Services.

The Forms Listener Servlet communicates through the HTTP server port and does not need extra ports to handle the communication between the client and the Oracle Application Server Forms Services.

The Forms Servlet architecture is also compatible with web applications industry standards and supports different advanced network configurations such as Load balancing effortlessly.

Although Forms Servlet is the preferred deployment method, there may be circumstances where customers need to switch from Forms Servlet mode to Forms Socket mode, which allows desktop clients to access the Forms Server directly.

https://img1.blogblog.com/img/video_object.png

REPORT THIS AD

 

This may be required in the following situations:

§  Customers’ network topology is multimode and the Forms Services are configured on a node different from the node on which Web services (Web Entry Point and Web Applications) are configured.

§  Customers constrained by network bandwidth, or machine resources may consider socket mode as an alternative to improve performance.

§  To reduce network traffic. The servlet mode uses http protocol on each transaction between a client and the Forms Server requiring the exchange of cookies and http headers which increases network traffic.

§  To reduce consumption of resources use by the JVMS needed in servlet mode architecture.

 Socket Mode Advantages

1.     Uses up to 40% less bandwidth than Forms servlet mode.  This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.

2.     Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.

Enabling Forms Socket Mode
Execute the following steps to switch from Forms Servlet mode to Forms Socket mode:

1.     Source the environment on the application tier.

2.     Stop all the application tier services using adstpall.sh

3.     Run the following command to enable Forms Socket Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=<CONTEXT_FILE>] \
-mode=socket \
[-port=<Forms port number>] \
-runautoconfig=<No or Yes> \
-appspass=<APPS password>

Parameter

Description

-contextfile

Full path to application tier context file, using the syntax:On UNIX:$INST_TOP/appl/admin/<CONTEXT_NAME>.xml

-mode

servlet – to enable Forms Servlet mode. This is the default value.socket – to enable Forms Socket mode

-port

Port number used to run Forms in socket mode. The default port number is 9095.A port number is not needed if servlet mode is used.

-runautoconfig

Specify whether AutoConfig should be run after changing the forms mode. Possible values are:No – Do not run AutoConfig after enabling Forms servlet/socket mode. This is the recommended value.Yes – Run AutoConfig after enabling Forms servlet/socket mode.

-appspass

Password for the applications user. Required only if -runautoconfig=Yes.

 4. If Autoconfig was not automatically executed as part of the preceding step, then run AutoConfig on the application tier:

On UNIX:

$INST_TOP/admin/scripts/adautocfg.sh

5. Start all the application tier services:

On UNIX:

$INST_TOP/admin/scripts/adstrtal.sh

6.Check whether Forms Server is running:

On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh status

1.     Log in to Oracle Applications and  launch a Forms-based application.

2.     Open the Sun Java Console (from Tools Menu in Internet Explorer).

3.     Check whether the “mode” directive displayed in Sun Java Console when launching forms-based applications is set to socket.

4.     The direct launch URL for Forms Socket Mode is:

<web_protocol>://<web_host>.<web_domain>:<web_port>/OA_HTML/frmservlet

 What is Forms Servlet Mode?

The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.

The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall. 

Servlet Mode Advantages

1.     HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis. 

2.     Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.

3.     More resilient to network and firewall reconfigurations.

4.     More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.

5.     Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.

6.     Performance traffic can be monitored via tools like Oracle Real User Experience Insight (RUEI).

7.     Socket mode is not supported on Windows-based server platforms.

Enabling Forms Servlet Mode

Carry out these steps to reenable Forms Servlet mode if Socket Mode is no longer required:

1.Source the environment on the application tier.

2. Stop all the application tier services:

On UNIX:

$INST_TOP/admin/scripts/adstpall.sh

3. Run the following script to disable Forms Socket Mode and re-enable Forms Servlet Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=<CONTEXT_FILE>] \
[-mode=servlet] \
-runautoconfig=<No or Yes> \
-appspass=<APPS password>

Parameter

Description

-contextfile

Full path to application tier context file, using the syntax:On UNIX:$INST_TOP/appl/admin/<CONTEXT_NAME>.xml

-mode

servlet – to enable Forms Servlet mode. This is the default value.socket – to enable Forms Socket mode

-port

Port number used to run Forms in socket mode. The default port number is 9095.A port number is not needed if servlet mode is used.

-runautoconfig

Specify whether AutoConfig should be run after changing the forms mode. Possible values are:No – Do not run AutoConfig after enabling Forms servlet/socket mode. This is the recommended value.Yes – Run AutoConfig after enabling Forms servlet/socket mode.

-appspass

Password for the applications user. Required only if -runautoconfig=Yes.

 

4.If Autoconfig was not automatically executed as part of the preceding step, then run AutoConfig on the application tier:

On UNIX:

$INST_TOP/admin/scripts/adautocfg.sh

5. Start all the application tier services:

On UNIX:

$INST_TOP/admin/scripts/adstrtal.sh

1.     Log in to Oracle Applications and  launch a Forms-based application.

2.     Open the Sun Java Console (from Tools Menu in Internet Explorer).

3.     Check whether the “mode” directive displayed in Sun Java Console when launching forms-based applications, is set to http,native

4.     Direct Forms Servlet Launch is:

web_protocol>://<web_host>.<web_domain>:<web_port>/forms/frmservlet

Step 4.1: Manually Starting Forms Server

On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh start

Step 4.2: Manually Stopping Forms Server

On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh stop

Step 4.3: Checking Status of Forms Server

On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh status

Switching Apps Deployments between Modes

Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web. 

There may be circumstances where you need to switch between the default Forms modes.  You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load.  You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.

If you’re running Apps 11i and would like to switch to servlet mode, see:

§  Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)

If you’re running Apps 12 and would like to switch to socket mode, see:

§  Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)