Query to find the User preferences set for a User in Oracle Apps/EBS
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');
Some useful Commands/Scripts for ASM
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;
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;
How to submit a concurrent program using the CONCSUB utility in Oracle Apps/EBS
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
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
Attention: Using WAIT=Y or WAIT=
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.
Attention: Do not use REPEAT_TIME with other resubmission parameters except for the optional parameters REPEAT_END and START.
Attention: Do not use REPEAT_INTERVAL and REPEAT_INTERVAL_UNIT with REPEAT_TIME.
Attention: Use REPEAT_INTERVAL_TYPE only if one uses REPEAT_INTERVAL.
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
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.
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.
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.
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)
How to Start/Stop services in Oracle Apps R12.2 on all nodes through single command
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.
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
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
Query to identify printers set with the "Printer" profile, at the all profile levels in Oracle Apps
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');
How to Deploy SSL Certificates on Oracle Weblogic
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
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
Query to Check All Concurrent Manager Status
We can use below query to check all concurrent managers status with node name and number of actual/target processes.
Script:
SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
,DECODE (b.control_code
,'D', 'Deactivating'
,'E', 'Deactivated'
,'N', 'Node unavai'
,'A', 'Activating'
,'X', 'Terminated'
,'T', 'Terminating'
,'V', 'Verifying'
,'O', 'Suspending'
,'P', 'Suspended'
,'Q', 'Resuming'
,'R', 'Restarting'
) status
FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.running_processes = a.max_processes
ORDER BY a.max_processes DESC;
Introduction to Oracle Data Gaurd - Chapter 2
In this post I am going to cover the concepts of Oracle Data Gaurd.
Datagaurd is part of MAA and used for Data protection which provides a disaster recovery solution for Oracle Databases. It also be considered as a High Availability Solution*.
For Datagaurd we can keep the DR on different servers,data centers or geographical location to provide best data protection.
In the DR environment the source database from where data is synced to target database is called as PRIMARY Database and the target database is know as Standby Database. The above picture represents simple architecture for a basic DR configuration.
DR sends the redo log files to standby database so it is also know as log shipping based replication.
DR is a unidirectional replication which means that any changes to data made in Primary would only move to standby. No changes can be done at standby side
Oracle Datagaurd is part of Oracle Maximum Availability Architecture(MAA) which includes various Oracle technologies like RAC,ASM,Flashback, Active DataGaurd and Goldengate.
Datagaurd is part of MAA and used for Data protection which provides a disaster recovery solution for Oracle Databases. It also be considered as a High Availability Solution*.
For Datagaurd we can keep the DR on different servers,data centers or geographical location to provide best data protection.
In the DR environment the source database from where data is synced to target database is called as PRIMARY Database and the target database is know as Standby Database. The above picture represents simple architecture for a basic DR configuration.
DR sends the redo log files to standby database so it is also know as log shipping based replication.
DR is a unidirectional replication which means that any changes to data made in Primary would only move to standby. No changes can be done at standby side
Oracle DataGaurd Tutorial Series Details-Chapter 1
Hello and welcome to the Oracle DataGaurd Tutorial Series of post. In these post I would be sharing the guide to start understanding and working on data gaurd.
You should be having some prior knowledge on
a) Oracle Database administration Basics
b) Basic of Linux Command
c) Have knowledge of working on virtual box and setting up server.
I will be covering topics related to below in this series.
1) Introduction to Oracle DataGaurd- Understanding the concepts.
2) Create and Manage Physical and Logical Standby database.
3) Understanding and Configuring Data Gaurd Broker and using it to monitor Data Gaurd.
4) How to do role transition (Switch Over and Failover)
5) How to setup Fast-Start Failover(FSFO)
6) How to setup Active Data Gaurd
7) How to setup Snapshot Standy Database
8) Implement Client Failover
9) How to Use RMAN for datagaurd environments
I would be using Oracle Virtual Box to setup the Oracle Data Gaurd Environment.
Environment Requirement
1. Virtual Box software latest version
2. Database software 12cR1(12.1.0.2)
3. Oracle Enterprise Linux 6.4 64 bit
4 Putty or ssh terminal(In case of Linux)
5. Using the above , I have setup two Machines on virtual box.(How to create Virtual Machine and Install Oracle database). I would not separately mention, how to install oracle database as part of this series. I would assume you would have already completed that. In case of any issue, Please mention in comment section.
You should be having some prior knowledge on
a) Oracle Database administration Basics
b) Basic of Linux Command
c) Have knowledge of working on virtual box and setting up server.
I will be covering topics related to below in this series.
1) Introduction to Oracle DataGaurd- Understanding the concepts.
2) Create and Manage Physical and Logical Standby database.
3) Understanding and Configuring Data Gaurd Broker and using it to monitor Data Gaurd.
4) How to do role transition (Switch Over and Failover)
5) How to setup Fast-Start Failover(FSFO)
6) How to setup Active Data Gaurd
7) How to setup Snapshot Standy Database
8) Implement Client Failover
9) How to Use RMAN for datagaurd environments
I would be using Oracle Virtual Box to setup the Oracle Data Gaurd Environment.
Environment Requirement
1. Virtual Box software latest version
2. Database software 12cR1(12.1.0.2)
3. Oracle Enterprise Linux 6.4 64 bit
4 Putty or ssh terminal(In case of Linux)
5. Using the above , I have setup two Machines on virtual box.(How to create Virtual Machine and Install Oracle database). I would not separately mention, how to install oracle database as part of this series. I would assume you would have already completed that. In case of any issue, Please mention in comment section.
How to install/configure Oracle EBS R12.2 on Multiple Server/Nodes (Database and Application Separately)
In this post I am going to share how to install Oracle EBS R12.2 on multiple nodes. Which means Database on one server and Application on one server.
I am using Oracle Virtual Box and configured 2 servers, one for Database and one for Application.
Virtual Server/Machine Details
Database Node
Memory: 6 GB
Harddisk: 200 GB
Hostname: fundb.lab
IP: 192.168.56.80
OSuser: oracle
Stage Area has been created separately on nodes and is non shared
Application Node
Memory: 7 GB
Harddisk: 270 GB
Hostname: funapp.lab
IP: 192.168.56.81
OSuser: applmgr
Stage Area has been created separately on nodes and is non shared
Please perform all the pre-reqs on both nodes as described in earlier post.
1) user creation
2) rpm installation and limits configuration
3) oraInst.loc
Refer https://www.funoracleapps.com/2017/02/installing-oracle-apps-r122-step-by.html for steps by step pre-installation method setup.
Also make sure hostname are pingable from each other. This can be achieved by putting the entries in /etc/hosts file.
Once both the servers are ready to installation. Please follow below steps
1) Kick-off rapidwiz on Database node first. We need to give all information for our db node and apps node. Once the installation on DB node completes, our database and listener would be ready.
2) There would be a configuration file created on DB node ($ORACLE_HOME/appsutil/conf_
3) Now we need to kickoff rapidwiz on application node.
Please provide the path of configuration file copied in step 2.
This completes our installation of EBS on 2 nodes.
Workflow Services are not Running or Starting in R12 Oracle Apps
Oracle Workflow Services in Oracle Applications are encountering problems.The workflow components are not starting when I was trying to start up the services from front end web-form using system administrator responsibility.These services appear with a Target value of 1 and an Actual value of 0. Even I tried from Concurrent>Manager>Administer form but still these were not starting.
Workflow components was not coming Up and showing as below
Target - 1 Actual - 0
Workflow service Components and there shotnames.
Workflow Agent Listener Service - WFALSNRSVC
Workflow Mailer Service - WFMLRSVC
Workflow Document Web Services Service - WFWSSVC
We can find the same using below query as well.
Workflow Agent Listener Service:
SQL> select CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_queues_tl where USER_CONCURRENT_QUEUE_NAME='Workflow Agent Listener Service';
CONCURRENT_QUEUE_NAME
------------------------------
WFALSNRSVC
Workflow Mailer Service:
SQL> select CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_queues_tl where USER_CONCURRENT_QUEUE_NAME='Workflow Mailer Service';
CONCURRENT_QUEUE_NAME
------------------------------
WFMLRSVC
Workflow Document Web Services Service:
SQL> select CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_queues_tl where USER_CONCURRENT_QUEUE_NAME='Workflow Document Web Services Service';
CONCURRENT_QUEUE_NAME
------------------------------
WFWSSVC
Now to fix the issue we will follow below steps
1) Make the the processes value zero for these services.
UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0
where concurrent_queue_name in ('WFWSSVC','WFALSNRSVC','WFMLRSVC');
3 rows updated.
2) Update Control codes with NULL
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE concurrent_queue_name in ('WFWSSVC','WFALSNRSVC','WFMLRSVC')
AND control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
0 rows updated.
3) Make Target node as Null option
UPDATE fnd_concurrent_queues
SET target_node = null
where concurrent_queue_name in ('WFWSSVC','WFALSNRSVC','WFMLRSVC');
3 rows updated.
4) Commit
SQL>commit;
Commit complete.
5) Check Workflow Concurrent Managers
Wait for few minutes, the Internal concurrent manager will bring up services automatically.
We can check the components status using following SQL statements.
SQL> select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name='WFALSNRSVC';
C RUNNING_PROCESSES MAX_PROCESSES
- ----------------- -------------
1 1
SQL> select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name='WFALSNRSVC';
C RUNNING_PROCESSES MAX_PROCESSES
- ----------------- -------------
1 1
SQL> select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name='WFWSSVC';
C RUNNING_PROCESSES MAX_PROCESSES
- ----------------- -------------
1 1
Subscribe to:
Posts
(
Atom
)
3 comments :
Post a Comment