Thursday, 19 April 2018

How to create Oracle directory?

Database Directories are required to read/write to a filesystem location from plsql code.

Below is the syntax to create database directory

SQL> create directory PLSQL_DIR AS '/oratmp/code';

Directory created.

Grant Read/write privileges to Required Schema


Grant succeeded.

Oracle Recovery Manager Troubleshooting ::ORA-27211: Failed to load Media Management Library

This is an error during Database Clone using RMAN.

I was trying to restore database using RMAN Backup

RMAN-00571: ===========================================================

RMAN-00569: ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 

Cause: Backup Configuration was modified.

Use the below command to Resolve the error and continue with Database Restore

rman auxiliary / 
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to 'DEV' backup location '/Daily_Backup/Backup_16042018';

Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing

Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.


Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer 

Identify the Concurrent Requests which are in Running Status.

Get the Database Session Details of the currently running concurrent requests using the below Query:

SELECT DISTINCT  a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';

Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.

select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;

-- sid value to be taken from output of Sql Query in Step 1

If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.

I will cover more details about SQL Tuning in another post.

Oracle Workflow:How to modify workflow administrator role in Oracle EBS?

By default,Oracle 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 Oracle 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 applications 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

Oracle Workflow System Administrator can be changed in below ways.

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

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]

Oracle Applications Patching :How to Run hrglobal driver in Oracle EBS 12.2.X Version.

Applying hrglobal driver is needed during an oracle applications upgrade or when the  payroll data is required to be analyzed as per latest code pack.

Below are the steps to apply hrglobal driver in a Oracle EBS environment (12.2.x)

Before applying hrglobal driver download the latest hrglobal patch from My Oracle Support.

Start an Oracle online EBS Patching cycle.

1.adop phase=prepare

2. Apply latest hrglobal patch

adop phase=apply patches=<patchnum>

3. Run Datainstall and hrglobal driver using below commands

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

+Run DataInstall

java oracle.apps.per.DataInstall apps appspassword thin

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
------------------------------ ------------- -------------

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

++Apply hrglobal.drv using below syntax.

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

4. adop phase=finalize

5. adop phase=cutover

6. adop phase=cleanup

Friday, 13 April 2018

EBS Plugin Deployment for Oracle Enterprise Manager 13c Cloud Contol (

Application Management Suite is certified as a plug-in with Oracle Enterprise Manager Cloud Control 13c Release 2.
Steps to Deploy the Application Management Suite 
1.Pre-Requisite Patches

Apply the Pre Requisite patches as mentioned in Metalink Doc ID 2045552.1

2.Download the Plugin

Plugin can be downloaded in two ways.
2.1 Download the EBS plugin from the below link
Size of Plugin - ~27MB
Download the EBS plugin software to a location in OMS Home as oracle user.
Run the below command to update the plugin into Oracle Enterprise Manager
emcli import_update -file="<Location of downloaded Plugin file>" -omslocal

2.2 Download from Oracle Enterprise Manager Console 13c console using Self Update feature.

3.Deployment of EBS Plugin for Oracle Enterprise Manager  OEM 13c
[EBS Plug-in Deployment to be implemented on OMS Agent node and on all EBS Nodes where EM Monitoring is Required]
3.1 Deploy EBS Plugin to OMS Agent

Login to Oracle Enterprise Manager  OEM console to deploy plugin to OMS Agent
 Go to Setup => Extensibility => Plug-ins:
Select Oracle E-business Suite and Click Deploy-On
Provide the Required details and proceed.
[OMS Restart Required for this activity- Implemented through console]
Monitor the deployment status using below command

emctl status oms -details

3.2 Deploy Plug-in to Management Agent
 The process is similar to the deployment procedure to the OMS Agent, but in this case the EBS host targets must be selected.
[Note: Add new management agents for EBS Targets if not already available.]

Oracle Database Recovery Manager Concepts:Script to Restore Oracle Database from RMAN Backup

During Oracle Database Cloning,restore Oracle database from RMAN backup taken on Source environment to the Target environment.

Steps for Database restore using Oracle Recovery Manager (RMAN)

1.Connect to target database 

Startup the Target database in Nomount stage

SQL> Startup nomount

2.Run the below Command to Restore Oracle database using RMAN 

rman auxiliary / 
duplicate database to 'DEV' backup location '/DB_BACKUP/Daily_Backup/daily_bkp_04102018';

Where DEV is the target instance for Restore
Backup Location - Directory where RMAN backup is located

3. Verify that Oracle database is up and running.