NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Query to find Concurrent Request Details from Concurrent Program Name

Himanshu

Query to find Concurrent Request Details from Concurrent Program Name






Script

SELECT
   distinct user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type,
    hold_flag,
    user_name
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    --and user_name = upper('HIMSINGH')
    and user_concurrent_program_name in 
('Active Users')
    and Phase_code='P'
ORDER BY REQUEST_DATE DESC;

The where conditions can be modified as per your need.
Read More
,

Query to find the User preferences set for a User in Oracle Apps/EBS

Himanshu

Query to find the User preferences set for a User in Oracle Apps/EBS


We can use below query to check preferences set for a user.  In this example I am checking the preferences set for user related to workflow/mails.


select * from fnd_user_preferences where PREFERENCE_NAME='MAILTYPE' and MODULE_NAME = 'WF' and user_name in  ('HIMANSHU.SINGH');


select preference_value
from fnd_user_preferences
where user_name='HIMANSHU.SINGH'
and preference_name='MAILTYPE';

select distinct PREFERENCE_VALUE from fnd_user_preferences where PREFERENCE_VALUE  like '%MAI%'

select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;


select * from wf_local_roles
where notification_preference in ('DISABLED','QUERY')
and orig_system='PER' and name in ('HIMANSHU.SINGH');
Read More
, ,

Some useful Commands/Scripts for ASM

Himanshu

Some useful Commands for ASM


I am sharing few useful commands related to ASM which can be used for DBA's.

1) Check if the ASM disk is a Member or not.

In the below example I am using condition to check the path because all my DISK name are configured with Instance name.

select path, header_status,name from v$asm_disk where header_status='MEMBER' and PATH like '%PROD%';
select path, header_status,name from v$asm_disk where header_status='FORMER' and PATH like '%PROD%';

2) List all DISK in ASM.

/usr/sbin/oracleasm listdisks 

3)  Find Serial Number for a DISK.

sudo /usr/local/bin/asm2serial "PROD_DATA01 PROD_DATA02"

PROD_DATA01 : 742b00000ac084 (/dev/mapper/inf01_asm_data08)
PROD_DATA02: 742b0000ac092 (/dev/mapper/inf01_asm_data09)

4) Find free and used space in DISKGROUP.

 SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB", free_mb/total_mb*100 "%FREE" FROM v$asm_diskgroup;

 5) Monitoring ASM Disk Operations

 select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation;

 If there is a operating going on (like rebalancing) the query will return some rows.

 GROUP_NUMBER OPERA STAT    ACTUAL  SOFAR      EST_MINUTES
------------ ----- ---- ---------- ---------- -----------
1           REBAL RUN           1         10          12


6) Find ASM Disks

 select GROUP_NUMBER, NAME, PATH, STATE, TOTAL_MB, FREE_MB from v$asm_disk;
Read More
, ,

How to submit a concurrent program using the CONCSUB utility in Oracle Apps/EBS

Himanshu

In this post we are going to see how to submit a concurrent program using the CONCSUB utility 


Syntax for CONCSUB utility

CONCSUB / \
\
\
\
[WAIT=N|Y|] \
CONCURRENT \
\
\
[PROGRAM_NAME=] \
LANGUAGE - R12 onwards only \
TERRITORY - R12 onwards only
[ORG_ID=<#>] - R12 onwards only
[REPEAT_TIME=] \
[REPEAT_INTERVAL= ] \
[REPEAT_INTERVAL_UNIT=< resubmission unit>] \
[REPEAT_INTERVAL_TYPE=< resubmission type>] \
[REPEAT_END=] \
[START=] \
[IMPLICIT=< type of concurrent request> \
[ ... ]

How to pass a parameter through CONCSUB:

Example:

'"08JUN96 23:55:00"'

Parameters and Meaning

 Required. The ORACLE username and password that provides access to the data that the program uses.

 Required. The application short name of the responsibility whose concurrent processing options to be used.

 Required. The name of the responsibility. If the name of the responsibility includes spaces, enclose that name in double quotes.

 Required. The uppercase username of the application user whose concurrent processing options to use.

 Optional. A flag that indicates whether to wait for the submitted request to complete. If one leaves this parameter out, the default value of N makes CONCSUB return to the operating system prompt without waiting for the request to complete.
Set WAIT=Y to have CONCSUB check the request status every 60 seconds and returns to the operating system prompt when the request is completed. One can also enter an integer value for a number of seconds, as in WAIT=30, for CONCSUB to check for request completion every seconds.

Attention: Using WAIT=Y or WAIT= requires that the request completes before CONCSUB returns to the operating system. If the concurrent manager is down, the CONCSUB process waits indefinitely until the concurrent manager is started and the request completes.

 Required. A flag that separates the program specific parameters from the operating system parameters.

 Required. The application short name of the concurrent program.

 Required. The uppercase name of the program. It must be the short name that was enter in the Concurrent Programs window when defining a concurrent program.

 Optional. A descriptive name for your program. The program field on the View Requests form displays this as the user friendly program name. The concurrent program short name passed to CONCSUB is often hard for end users to understand, so the PROGRAM_NAME parameter allows one to pass a more easily remembered name for a concurrent program. If one does not specify a PROGRAM_NAME, the View Requests form displays the user friendly program name specified in the Concurrent Programs window.
One may also use the PROGRAM_NAME parameter to indicate the batch that your request processes for programs that process a set of data, where there could be several requests for a given program that are active at the same time.

 Optional. Introduced in R12, set to org id required for the report to be run with.

 Optional. The time of day to resubmit the request.  The format for the time is HH24:MI or HH24:MI:SS. For example, REPEAT_TIME=14:30 resubmits the request daily at 2:30 p.m.

Attention: Do not use REPEAT_TIME with other resubmission parameters except for the optional parameters REPEAT_END and START.

 Optional. The interval between resubmission (a positive integer or real number). Use this parameter along with REPEAT_INTERVAL_UNIT to specify the time between resubmissions.

 Optional. The unit of time used for the interval between resubmissions. The available units are MINUTES, HOURS, DAYS or MONTHS. Use this parameter along with REPEAT_INTERVAL to specify the time between resubmissions. For example, setting REPEAT_INTERVAL=12 and REPEAT_INTERVAL_UNIT=HOURS resubmits the request every twelve hours. The default value is DAYS.

Attention: Do not use REPEAT_INTERVAL and REPEAT_INTERVAL_UNIT with REPEAT_TIME.

 Optional. Whether to time the resubmission interval from the requested start time of the request or from its completion. Set this parameter either to START or END. The default value is START.

Attention: Use REPEAT_INTERVAL_TYPE only if one uses REPEAT_INTERVAL.

 Optional. The date and time to stop resubmitting the concurrent request. Use one of the following for the format of the end date:

DDMONRR HH24:MI:SS (as in 07“APR02 18:32:05€™)
or
DDMONRRRR HH24:MI:SS (as in 07€“APR2002 18:32:05€™)
Note that because this date format includes a space, one must enclose the date in double quotation marks and single quotation marks. One can also specify just the date:
DDMONRR
or
DDMONRRRR

 Optional. The NLS language for the request. (LANGUAGE=SIMPLIFIED CHINESE \)

 Optional. The NLS territory for the request. (TERRITORY=CHINA \)

 Optional. A start date and time for the program in this format:
DDMONRR HH24:MI:SS (as in 07€“APR02 18:32:05€™)
Because this date format includes a space, one must enclose the date in double quotation marks and single quotation marks. If one does not specify a start time, the program submits immediately and is processed by the next available concurrent manager. The default value is the current time.

 Optional. Whether to show this concurrent request on the View Requests form. Specify NO, YES, ERROR or WARNING. The value IMPLICIT=NO allows the request to appear on the View Request form. The default value is NO.
The value IMPLICIT=YES means that only the System Administrators privileged View Concurrent Requests form displays this request. Use this value if the request is not interesting to the user.
Specify IMPLICIT=ERROR or IMPLICIT=WARNING, respectively, if one wants the request to appear only if it fails or completes with warnings.

 Optional. The number of days after which to repeat the concurrent request, calculated from the last requested start date. The number can be a positive integer or real number. For example,
REPEAT_DAYS=1.5 resubmits the request every 36 hours.

Attention: Do not use REPEAT_DAYS with other re-submission parameters except for the optional parameters REPEAT_END and START.
Suggestion: REPEAT_DAYS will become obsolete in a future release. One may therefore want to use REPEAT_INTERVAL, REPEAT_INTERVAL_TYPE and REPEAT_INTERVAL_UNIT instead of REPEAT_DAYS.

... Optional. The program specific parameters. If a parameter includes spaces, enclose that parameter in double quotes, then in single quotes. If a parameter contains a double quotation mark as part of the argument, precede that mark with a backslash [\].

Please note that the program specific parameters must be explicitly passed to CONCSUB,  any default values that may be used when the program is submitted via the normal screens (ie Standard Report Submission or SRS) will not be used by CONCSUB.

Sample Examples:

In this example we are submitting concurrent request to gather stats of few schemas.

CONCSUB apps/apps FND 'System Administrator' SYSADMIN wait=N CONCURRENT FND FNDGSCST PROGRAM_NAME='"Gather Schema Statistics"' GL        10 '""' NOBACKUP '""' LASTRUN GATHER '""' N
CONCSUB apps/apps FND 'System Administrator' SYSADMIN wait=N CONCURRENT FND FNDGSCST PROGRAM_NAME='"Gather Schema Statistics"' XLA        10 '""' NOBACKUP '""' LASTRUN GATHER '""' N
CONCSUB apps/apps FND 'System Administrator' SYSADMIN wait=N CONCURRENT FND FNDGSCST PROGRAM_NAME='"Gather Schema Statistics"' ZX        10 '""' NOBACKUP '""' LASTRUN GATHER '""' N
CONCSUB apps/apps FND 'System Administrator' SYSADMIN wait=N CONCURRENT FND FNDGSCST PROGRAM_NAME='"Gather Schema Statistics"' AP        10 '""' NOBACKUP '""' LASTRUN GATHER '""' N
CONCSUB apps/apps FND 'System Administrator' SYSADMIN wait=N CONCURRENT FND FNDGSCST PROGRAM_NAME='"Gather Schema Statistics"' AR        10 '""' NOBACKUP '""' LASTRUN GATHER '""' N





Ref: How To Submit A Concurrent Request Using CONCSUB Syntax (Doc ID 457519.1)
Read More
,

How to Start/Stop services in Oracle Apps R12.2 on all nodes through single command

Himanshu
If we have a EBS instance in  R12.2.x and it is configured with multiple nodes, then we can use the below commands to stop and start all services at one time.

To stop all services on all nodes

adstpall.sh apps/apps_password -mode=allnodes


To Start all services on all nodes

adstrtal.sh apps/apps_password -mode=allnodes



(The above commands will ask for WebLogic password)

 This command will internally connect all the nodes via ssh as you would have enabled ssh as part of R12.2.x pre-requisites setup. If you have not enabled ssh, you will not be able to apply patches in remote nodes and it will get failed.

 If we want to skip stopping of weblogic admin services then we can use below commands


adstpall.sh apps/apps -skipNM -skipAdmin

Read More
,

Query to identify printers set with the "Printer" profile, at the all profile levels in Oracle Apps

Himanshu

Query to identify printers set with the "Printer" profile, at the all profile levels in Oracle Apps





Script:


select distinct
t.user_profile_option_name "Profile Option Name",
decode(v.level_id, 10001,'Site Level',
10002,'Application Level --> ' ||application_name ,
10003,'Responsibility level-->'||responsibility_name,
10004,'User Level-->' ||u.user_name,
'XXX') "Profile Option Level",
profile_option_value "Value"
from fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t,
fnd_responsibility_tl r,
fnd_application_tl a,fnd_user u
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and a.application_id(+) = decode(level_id,10002,level_value,null)
and r.responsibility_id(+)= decode(level_id,10003,level_value,null)
and u.user_id(+) = decode(level_id,10004,level_value,null)
--and nvl(u.end_date, sysdate) >= sysdate
and t.user_profile_option_name = 'Printer'
and profile_option_value='' --replace with printer name
order by 2, --v.level_id,
t.user_profile_option_name,
decode(v.level_id, 10001,'Site Level',
10002,'Application Level --> ' ||application_name ,
10003,'Responsibility level-->'||responsibility_name,
10004,'User Level-->' ||u.user_name,
'XXX');
Read More
,

How to Deploy SSL Certificates on Oracle Weblogic

Himanshu

How to Deploy SSL Certificates on Oracle Weblogic


Oracle Weblogic uses the Java Keystore architecture to manage and deploy SSL Certificates. We need to get the certificate from the certifying authority and download the root certificate, intermediate and main certificate out of that.


Steps to deploy:


1) Set Weblogic Environment

/wlserver_10.3/server/bin/setWLSenv.sh

2) Concatenate Certificate with Root Certificate(s) into 1 file

Sequence would as below

Main Signed Cert + CA Intermediate(s) + CA Root

and Save file as: fullcertificate.crt

3) Import Full Cert into identity.jks Keystore
keytool -import -trustcacerts -alias funoracle -file fullcertificate.crt -keystore identity.jks

4) Create Empty Trust Store
This Trust Store will be used to store the Root and Intermediate certificates alone

keytool -genkey -keyalg RSA -alias dummy -keystore truststore.jks

## Answers DO NOT MATTER you will delete this dummy record next

What is your first and last name?
[Unknown]:
What is the name of your organizational unit?
[Unknown]:
What is the name of your organization?
[Unknown]:
What is the name of your City or Locality?
[Unknown]:
What is the name of your State or Province?
[Unknown]:
What is the two-letter country code for this unit?
[Unknown]:
Is CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown correct?
[no]:  yes

Enter key password for
(RETURN if same as keystore password):
Re-enter new password:
 

5) Delete the DUMMY record
Here we remove the “dummy” alias before importing the Trust chain in the next step.

keytool -delete -alias dummy -keystore truststore.jks
 

6) Import the Trust Cert Chain only from the CRT format
CA Intermediate(s) + CA Root

keytool -import -alias root -file inter.crt -keystore truststore.jks
 

7) In Weblogic Console -> Server
Set SSL Listen Port to 443 and disable Listen port for 80 (if you want to force the server to SSL only). Else no action required



8) In Weblogic Console -> Server
Keystore Tab -> Change to Custom Identity and Trust

For Identity set:

Custom Identity Keystore == C:\Oracle\keystore\identity.jks
Custom Identity Keystore Type == JKS
Custom Identity Keystore Passphrase ==
For Trust set:

Custom Trust Keystore == C:\Oracle\keystore\truststore.jks
Custom Trust Keystore Type == JKS
Custom Trust Keystore Passphrase ==

9) In Weblogic Console -> Server
In SSL Tab

Set Private Key Alias == portal
Set Private Key Passphrase ==
In the Advanced Section set Use JSSE SSL on


Read More
, ,
Subscribe to: Posts ( Atom )

Consult US

Name

Email *

Message *

Follow by Email