Wednesday, 15 November 2017

Internal Server Error

Intermittent Login issue R12.1 - 500 Internal Server Error

Problem Description:

Users are unable to get application login page.
Users are frequently getting below error while accessing the EBS application R12.1.3 URL.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, ohs_admin@acs.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.

Cause:

This issue occurred due to low oacore heap size.

Solution:

Increase the oacore heap size and restart application services.

Change the parameters in $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
of both form nodes as shown below:

From:
-Xmx512M -Xms128M -XX:MaxPermSize=160M

To:
-Xmx1024M -Xms128M -XX:MaxPermSize=160M


Reference:

JVM: Guidelines to setup the Java Virtual Machine in Apps E-Business Suite 11i and R12 (Doc ID 362851.1)


Tuesday, 14 November 2017

How to run Gather Statistics for a Schema in R12.1.3

How to run Gather Schema Statistics concurrent program

1. Log on to Oracle Applications with
    Responsibility = System Administrator

2. Submit Request Window
    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program


Parameters :

Schema Name:
  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:
  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree: 
 The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.

Backup Flag:  NO BACKUP is used,
 then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID: 
 In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  
GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

Monday, 30 October 2017

How to check network latency in oracle R12.1.3


1. Navigate System Administrator Responsibility.
2. Navigate to Application
     Click on Network Test.




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

4. Specify the number of Trials and the Iterations for each trial for both the latency and bandwidth blocks. The default settings for both are 5 trials of 100 iterations each.

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

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

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




Note :- Oracle Recommends Network latency should be low and bandwidth should be high.

Tuesday, 17 October 2017

ORA-32004

Facing below issue on Oracle 12c database.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance 
ORACLE instance started.

solution:-

Down Database.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Edit PFILE:-

Go to PFILE location.
[sujeet@oracle ~]$ cd $ORACLE_HOME/dbs


[sujeet@oracle dbs]$ vi initORCL.ora

commit below parameter.
#sec_case_sensitive_logon = FALSE

save pfile.

start database with pfile.

SQL> startup pfile=/apps/dba/db/tech_st/12.1.0.2/dbs/initORCL.ora
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             872417400 bytes
Database Buffers         1258291200 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;

File created.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down


SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             872417400 bytes
Database Buffers         1258291200 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Issue resolved.


Thursday, 12 October 2017

Upgrade Timezone version 10 to 18 in oracle

Upgrading timezone_file version manually during Oracle 12c upgrade

Some of us might have faced a situation to upgrade timezone_file version manually during Oracle 12c upgrade. Here are the steps to follow in Windows.

When you upgrade Oracle to 121020, you will have have an option in DBUA to upgrade the timezone data.





If you don’t check it, then, when you run “post_upgrade_checks.sql”, it will give you the below warning message.

**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use
     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.2.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.

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

upgrade it manually

Upgrade Time zone version 10 to 18.

SQL> SELECT version FROM v$timezone_file;
  
  VERSION
----------
        10

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME                                                             VALUE
------------------------------------------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION                                             10
DST_SECONDARY_TT_VERSION                                        0
DST_UPGRADE_STATE                                                       NONE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             889194616 bytes
Database Buffers         1241513984 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (18);

PL/SQL procedure successfully completed.

SQL> shut immediate

SQL> startup

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        18


Wednesday, 20 September 2017

Enable Trace for Concurrent Programs in Oracle Apps R12.1.3

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.


Agenda :

 To enable the Trace for Concurrent Programs
 To Debug the issues in Concurrent Programs or its dependent objects.

Steps :

 1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

save it.

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

save it.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL
 select oracle_process_id from fnd_concurrent_requests where request_id= Request_id;

8.And the Trace file path can be derived using the below

SQL> Select * from v$parameter where name=’user_dump_dest’;

Trace file location:-
/apps/sonu/db/tech_st/11.1.0/admin/SONU_oracle/diag/rdbms/sonu/SONU/trace

How ro convert .trc to .out file :-
tkprof ACSDEV_ora_15529_SEKARS.trc ACSDEV_ora_15529_SEKARS.out  explain=apps/apps  sys=no sort=PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU


9. Get the trace file to your local machine.Understanding the raw trace is very complex so use
TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
 TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.
Analyse the Output file to know the answers for your problem.


Sql queries to find the log files by Request id


select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name,
prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name ,
req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest,
apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like '17352717'

and dest.name= 'user_dump_dest'

and request_id like & Request_id;


Sql query find the log files by username


select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name,
prog.user_concurrent_program_name, req.request_date,  req.phase_code, req.status_code, req.logfile_name ,
req.outfile_name, dest.value as user_dump_dest

from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like '17352717'

and dest.name= ‘user_dump_dest’

and usr.user_name like ‘sujeet%jha%’

order by request_date desc



SQL> select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;

 SQL> select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps .fnd_concurrent_programs_vl prog
where  req. concurrent_program_id = prog.concurrent_program_id and request_id like '17352717' and dest.name= ‘user_dump_dest’

Tuesday, 5 September 2017

How to add employee detail in oracle apps R12

Employee creation and user mapping
Employee creation, Employee role assignments and user mapping:

Issue reason:
       
Some times we are getting permission issue while accessing some of the module/responsibility like
 'iReceivable, Service, Credit management Credit analyst' etc. For that we have to create new employee,import that employee,
 and assign roles (Not mandatory) finally map that employee into Application login user "Person field".

Creating an employee
An employee is the most important entity in an organization. Before creating an employee, the HR officer must know the date from which the employee will be active in the organization. In Oracle terminology, you can call it the employee's hire date. Apart from this, the HR officer must know basic details of the employee such as first name, last name, date of birth, and so on.

Navigation: Global HRMS Manager -->People -->Enter and Maintain.





Select new and enter employee details like name,gender from action tab select "Create Employment
" option for employee creation. And enter identification employee number and save it , then enter the address details.





 Import the Employee:

CRM Administrator --> Resource Manager -->import Resource

From report category page enter your employee name and search employee.
 After finding employee select the employee and click start import button. and click ok Finally select save resource button.

You want to assign any role for this employee means click details button and assign the required roles.
(Credit analyst, credit manager, collection agent, collection manager etc..)

Employee Mapping:

If employee creation process is done, map this employee with application user.
Navigation:  System administrator -->Security --> user --> Define

Query the application user and select this employee in person field, and save it.