Month: June 2020

How to send mail from Linux system

Use the below command to send mail from a Linux server to a user email-id:

# echo “this is the body of the email” | mailx -s “test mail” -r “From” -S smtp=”your-smtp” xyz@domain123.com

-s =         Specifies the subject.
-r =         Email sent from.
-S =        Specifies the smtp server.

We can also add the configuration in mailx config file  /etc/mail.rc

set smtp=your.smtp.server

set from=”from email address”

Then run the mailx command and then enter the body of the email, hit enter, and finally press Ctrl+D to deliver it.


# mailx -vvv -s “email subject”  external-email-address

<Body of the email>


Ctrl D to deliver the message

How to use coe_xfr_sql_profile.sql to Improve performance of a sql query?

Oracle SQL Performance tuning is one of the frequently reported issues in a Oracle Database administrator’s daily job.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.


Steps to use the script coe_xfr_sql_profile.sql


1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.


2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.


To find the sql_id use below query


2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= ‘&sessionid’;


2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.


3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.


$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)


Enter value for 1: 0a3f7vuks8d7y   (–this is the sql_id of long running sql)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1484137450 15.6
3622468234 4560.76


[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]


Parameter 2:
PLAN_HASH_VALUE (required)


Enter value for 2: 1484137450 




++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output


coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


4. Run the script as sysdba


SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql 


5. Verify that Performance issue is resolved by Re-running the sql.

How to check if Oracle Database Vault is enabled?



Below sql query can help us to identify if Oracle Database Vault is enabled or Disabled


SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;


PARAMETER                                          VALUE        CON_ID
————————————————– ——– ———-
Oracle Database Vault                              FALSE             0




Database Vault is Disabled.

How to modify workflow administrator role in Oracle EBS?

By default, Workflow System administrator role is set to SYSADMIN User in Oracle EBS 11i/R12. This means only SYSADMIN user can have access to view Oracle workflow notifications of all the EBS users.
We can modify this to any other EBS user who has administrator rights or assign to a reponsibility. For Example, If we change Workflow System administrator to Responsinility name “Workflow Administrator Web Applications”, All users who are assigned with this responsibility can have access to view workflow details owned by other Oracle EBS users.


Let us identify the scenarios which require Workflow System administrator privilege.
– Workflow notification is errored and not processed to next approver
– There is an urgent requirement to delegate the workflow notification to another approver.
– Check the status of workflow notification
– Retry / Rewind the workflow notification
– Check the pending notifications for any Oracle EBS user
– Check Workflow Status Diagram


Steps to modify Workflow system administrator role in Oracle EBS 11i/R12


Workflow System Administrator can be changed in below ways.


1.update wf_resources set text=’&Enter_Admin_Name’ where name=’WF_ADMIN_ROLE’;


e.g
update wf_resources set text=’FND_RESP1:20420′ where name=’WF_ADMIN_ROLE’;




2. Change the value of Context file parameter s_wf_admin_role and run Autoconfig


$ cat $CONTEXT_FILE|grep wf_admin
         <username oa_var=”s_wf_admin_role” customized=”yes”>SYSADMIN</username>




3. Change it from Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration




[Note: Ensure that context file parameter “s_wf_admin_role” is updated with modified value to preserve changes during autoconfig Run]

How to Resolve ACFS-03008 : The volume expansion limit has been reached?

Issue: ACFS Filesystem resize operation fails with “ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.”


$acfsutil size +1G /oradata
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required version (11.2.0.4.0) for unlimited volume expansions.


Analysis:


Compatible attribute for advm and rdbms is below the required version.


 SQL>  select group_number, name, value from v$asm_attribute where name like ‘compatible%’ ;


GROUP_NUMBER
————
NAME
——————————————————————————–
VALUE
——————————————————————————–
           1
compatible.asm
12.1.0.0.0


           1
compatible.rdbms
11.2.0.0.0


           1
compatible.advm
11.2.0.0.0


           2
compatible.asm


12.1.0.0.0


           2
compatible.rdbms
10.1.0.0.0








Solution:


SQL> ALTER DISKGROUP DATA SET ATTRIBUTE ‘compatible.advm’=’11.2.0.4.0’ ;


Diskgroup altered.


SQL>  ALTER DISKGROUP DATA SET ATTRIBUTE ‘compatible.rdbms’=’11.2.0.4.0’ ;


Diskgroup altered.


Now verify the issue is resolved.
$ acfsutil size +10G /oradata
acfsutil size: new file system size: 603644231680 (575680MB)

ACFS-03171: Insufficient contiguous free ASM Diskgroup space

Issue:  


ACFS file resize operation operation fails with ACFS-03171: Insufficient contiguous free ASM Diskgroup space




[root@erptestdb01 ~]# cd /sbin
[root@erptestdb01 sbin]# acfsutil size +100M /oradata
acfsutil size: ACFS-03171: Insufficient contiguous free ASM Diskgroup space.  Check the ASM alert log.


Solution:


Increase size in smaller chunks until the required size is reached
[root@erptestdb01 bin]# acfsutil size +10M /oradata
acfsutil size: new file system size: 590625112064 (563264MB)

How to Run hrglobal driver in Oracle EBS 12.2.X Version?

Steps to apply hrglobal driver in Oracle EBS 12.2.x version

1. Start an Oracle online EBS Patching cycle.

adop phase=prepare

2.Source the patch filesystem environment file

ADOP utillity is intelligent enough to switch to Patch Filesystem depending on the ADOP phase being run.For any Other manual operations, we need to set the environment to point to Patch Filesystem explicitly.

. ./EBSapps.env PATCH

3. Run DataInstall

java oracle.apps.per.DataInstall apps appspassword thin test.domain.com:1521:DEV

Select the Required localisations and save the changes.

Example of Data Install Changes Summary is shown below.

          DataInstall – Actions confirmation

Do you really wish to exit and save your changes?

      [Y]      – Yes, save then exit
      [N]      – No, don’t save but exit
      [Return] – To return to the DataInstall Main Menu

Enter your choice (for example Y) : Y

          DataInstall – Actions summary
          —————————–

The following actions will be performed:

Localisation         Product(s)               Leg. Data? Action
——————– ———————— ———- ————-
Global               Human Resources          Installed  Install
United Arab Emirates Human Resources          Installed  Install
United Arab Emirates Payroll                  Installed  Install

Localisation   College Data? Action
————– ————- ————-
United Kingdom
United States

Option                         Data?         Action
—————————— ————- ————-
JIT/Geocode

Legislation                            Action
——————————         ————-
ALL  All Legislations

4. Apply hrglobal.drv using below syntax.

adop phase=apply patchtop=$PER_TOP/patch/115 patches=driver:hrglobal.drv workers=8

Complete the ADOP patching cycle by running finalize,cutover and cleanup phases

5. adop phase=finalize

6. adop phase=cutover

7. adop phase=cleanup

8. Run fs_clone to synchronize run and patch filesystems
adop phase=fs_clone

How to change default start Page in Oracle EBS

Application start page can changed for a Oracle EBS user in two ways.

1. Change settings in Preferences:

We can set the Application Start Page in Preferences. Click on Preferences and set the start page to preferred responsibility/page. After setting this preference, user will be taken to the above set page on successful login.

To reset the Responsibility Start Page, we can modify using same navigation.

2.  Change Profile option “Application Start Page”
Login as SYSADMIN user . Navigate to system adminstrator’s reponsibility > Profile > System

QUery for Profile Option name ‘Application Start Page” and User, then change or remove the value at User Level.