Apps DBA - Apps DBA Diaries

How to check if MRP Process is Running

Below Query is Used to verify if MRP process is running fine on Standby Database, after any maintenance activity that requires disconnect to Primary and Standby database.

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

Below is the sample output from the query:

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

CLIENT_P PROCESS THREAD# SEQUENCE# STATUS

——– ——— ———- ———- ————

ARCH ARCH 2 101072 CLOSING

ARCH ARCH 0 0 CONNECTED

ARCH ARCH 1 298101 CLOSING

ARCH ARCH 2 103073 CLOSING

ARCH ARCH 3 95398 CLOSING

ARCH ARCH 1 296100 CLOSING

ARCH ARCH 3 95399 CLOSING

ARCH ARCH 3 95400 CLOSING

ARCH RFS 0 0 IDLE

LGWR RFS 2 101074 IDLE

LGWR RFS 1 296102 RECEIVING

UNKNOWN RFS 0 0 IDLE

LGWR RFS 3 95401 IDLE

UNKNOWN RFS 0 0 IDLE

17 rows selected.

Convert Physical database into a Snapshot Standby Database

What are the benefits of Snapshot standby database?

Snapshot Standby Database is introduced from Oracle 11gR1. A Snapshot Standby database is a physical standby that is temporarily disconnected from Data Guard configuration, and started in READ-WRITE mode.It functions as a updateable Stand-alone Database, which is an exact Replica of Primary Database which is normally used for testing purpose. We can revert the Snapshot standby database to Physical Standby database after the testing is completed.

Steps to convert Physical database into a Snapshot Standby Database:

1. First step to convert to Snapshot standby database is to enable FLASHBACK feature on the Physical Standby, if not already enabled.

Ensure there is sufficient space available in Flash Recovery Area (FRA) for Flashback Logs.

2. Stop redo apply on the physical standby database:

SQL>alter database recover managed standby database cancel;

3. Turn on flashback logging:

SQL>alter database flashback on;

4. Convert the standby database into a snapshot standby database:

( For RAC Configurations: Shutdown all other instances except one, which is used as Snapshot standby database)

SQL>alter database convert to snapshot standby;

5.Shut down the snapshot standby database and startup the database (it will be opened for read/write access):

SQL>shutdown immediate;

SQL>startup

After the Snapshot mode is enabled on the database, perform the required testing and revert back to Physical Standby mode after the testing is complete.

Steps to Revert the Physical Standby Database Back to its Original State

1. Change the standby database back into its standby mode (from snapshot mode) as follows:

SQL>startup mount force;

SQL>alter database convert to physical standby;

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

2. Enable redo log apply.

SQL>alter database recover managed standby database using current logfile disconnect;

3.On the primary database, issue the following statement to re-enable archiving to the physical standby database:

SQL>alter system set log_archive_dest_state_2=enable scope=both;

Protection Modes in Oracle Dataguard

We have 3 Protection modes available in Oracle DataGuard

1. Maximum Performance :

This is the default protection mode. It provides the highest level of data protection that is possible

without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

2. Maximum Availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

3. Maximum Protection :

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

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

SQL> GRANT READ,WRITE ON DIRECTORY PLSQL_DIR TO APPS;

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: ===============
ERROR MESSAGE STACK FOLLOWS ===============

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.

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

rman auxiliary /
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to ‘DEV’ backup location ‘/Daily_Backup/Backup_16042018’;
}
exit;
EOF

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.

Analysis:

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 c.pid = 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’;

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]

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

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