Protocol Not Supported Error During OAuth IMAP Configuration

Issue:

While setting up OAuth for IMAP in Oracle E-Business Suite R12.2, you may encounter the following error during the IMAP connection test:

IMAP connection testing failed with: Protocol not supported

This typically occurs after following the Oracle documentation Doc ID 2884072.1 for OAuth configuration.

Solution:

1. To resolve this, explicitly instruct the JVM to use the Sun HTTPS handler and enforce TLS 1.2 for outbound connections by updating the application tier startup parameters.

-DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2

2. Apply the changes by stopping and starting the application services:

adstpall.sh & adstrtal.sh




Create a Custom Concurrent Manager in Oracle EBS R12.2

 Please follow the steps to create a custom concurrent Manager on the Oracle EBS R12.2 Application.

1. Stop the Concurrent manager services
2. Login to the application using sysadmin user.
3. Navigate to the Concurrent -> Manager -> Define
4. Create a custom manager as Custom Standard Manager.
- Set cache size as 50
- in Program Library -> Name: Library
- in Workshift -> mention 3 processes 
 - in Specialization rule -> include program's name as customizations and then save.
5. Exclude the concurrent program from the standard maanger
Concurrent-> Manager-> Define -> Standard Manager -> Specializations Rules -> Exclude all the above added concurrent programs.
6. Restart the concurrent Manager service
7. Activate the custom Concurrent Manager.

8. Submit the concurrent program and verify the same.

--Use the following query to identify which manager ran the concurrent request.

select distinct c.request_id, b.USER_CONCURRENT_QUEUE_NAME,e.USER_CONCURRENT_PROGRAM_NAME
from 
fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, 
fnd_concurrent_requests c,
fnd_concurrent_programs d,
FND_CONCURRENT_PROGRAMS_TL e
where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and    a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.concurrent_program_id=d.concurrent_program_id AND d.concurrent_program_id=e.concurrent_program_id 
and    c.request_id in ('1234567','1234567','1234567');

Query to Monitor RMAN Restoration Progress in Oracle

 SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM v$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

Note: For RAC database use, gv$session_longops in the above query.

Query to Update EBS Profile Values for Selective Users at the User Level

 SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

    l_success BOOLEAN;

    CURSOR uid IS

        SELECT DISTINCT fpov.level_value AS user_id

        FROM fnd_profile_option_values fpov

        JOIN fnd_profile_options fpo ON fpov.profile_option_id = fpo.profile_option_id

        WHERE fpo.profile_option_name = 'ICX_FORMS_LAUNCHER'

          AND fpov.level_id = 10004  -- User level

          AND fpov.profile_option_value IS NOT NULL;

    v_counter NUMBER := 0;  -- Counter for users processed

BEGIN

    FOR i IN uid LOOP

        l_success := FND_PROFILE.save(

            'ICX_FORMS_LAUNCHER',

            'http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws',

            'USER',

            i.user_id

        );

        v_counter := v_counter + 1;

        IF MOD(v_counter, 100) = 0 THEN  -- Print output every 100 users to avoid overflow

            DBMS_OUTPUT.PUT_LINE(v_counter || ' users updated...');

        END IF;

        COMMIT; -- Commit after each update

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Profile update completed successfully.');

END;

/

Query to Update EBS Profile Values for All Users at the User Level

set serveroutput on;

DECLARE

l_success  BOOLEAN;

    CURSOR uid IS

    SELECT user_id from fnd_user;

BEGIN

dbms_output.disable;

 dbms_output.enable(100000);

FOR i IN uid LOOP

        l_success := FND_PROFILE.save('ICX_FORMS_LAUNCHER','http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws','USER',i.user_id);

        IF l_success

THEN

 DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Updated successfully ');

DBMS_OUTPUT.put_line('---');

 ELSE

  DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Update Failed . Error:'||sqlerrm);

 DBMS_OUTPUT.put_line('---');

 END IF;

  commit;

 end loop;

end;

/

Protocol is not supported Error in IMAP While Configuring OAuth in EBS Workflow

When configuring OAuth for the EBS workflow application, you may encounter the following error message in IMAP:

Cause:

The error is triggered due to missing or incorrect protocol settings in the WebLogic Server configuration. By default, some WebLogic instances may not be set to use the required HTTP handler or TLS protocol version, leading to connectivity issues.

Solution:

1) Add -DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2 to the Server Start arguments for all OACORE servers and ADMIN server.

2) Restart the OACORE services.

Note: Not required to add parameters on DMZ environment.

How to Check and Update Email Addresses for Workflow Notifications in Oracle EBS

Step 1: Verify the User’s Email Address in FND_USER

Run the following query to check if the user has a valid email address assigned in FND_USER:

SELECT user_name, email_address, employee_id FROM fnd_user WHERE user_name LIKE 'KUMAR';

If the email_address is missing or incorrect, update it using the System Administrator responsibility:

Navigate to System Administrator → Security → User → Define

Search for the user and update the email address

Step 2: Check User Preferences and Email Address in WF_ROLES

The WF_ROLES table determines User preferences for workflow notifications. Run the following query:

SELECT name, email_address, NVL(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference) AS preference FROM wf_roles WHERE name = UPPER('KUMAR');

If preference is MAIL or MAILHTML, email notifications should be sent.

If the email_address is missing, proceed to the next step.

Step 3: Update the Email Address in WF_LOCAL_ROLES

If the email is missing in wf_roles view, update it using the following query:

UPDATE applsys.wf_local_roles SET email_address = 'kumar@xxxx.xxx' WHERE name = 'KUMAR';

COMMIT;

Find DMZ Enabled Responsibility in Oracle EBS Application

SELECT po.profile_option_id,po.USER_PROFILE_OPTION_NAME,usr.user_name,rspt.responsibility_name,rsp.end_date

FROM apps.fnd_profile_options_vl po, apps.fnd_profile_option_values pov,apps.fnd_user usr, apps.fnd_application app, apps.fnd_responsibility rsp,

apps.fnd_responsibility_tl rspt,

(select name,organization_id from HR_OPERATING_UNITS) org

WHERE  pov.application_id = po.application_id

AND pov.profile_option_id =   po.profile_option_id

AND rsp.application_id(+) =  pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+)   = pov.level_value

AND rspt.application_id(+) =  pov.level_value_application_id

AND rspt.responsibility_id(+) = pov.level_value

and usr.user_id=pov.LAST_UPDATED_BY

    and pov.profile_option_value=to_char(org.organization_id (+))

     and po.PROFILE_OPTION_NAME='APPL_SERVER_TRUST_LEVEL'

    -- and rsp.end_date is null

     and language='US';

How to Find the Responsibility Name for a Concurrent Program

SELECT frt.responsibility_name, frg.request_group_name,

frgu.request_unit_type,frgu.request_unit_id,

fcpt.user_concurrent_program_name

FROM fnd_Responsibility fr, fnd_responsibility_tl frt,

fnd_request_groups frg, fnd_request_group_units frgu,

fnd_concurrent_programs_tl fcpt

WHERE frt.responsibility_id = fr.responsibility_id

AND frg.request_group_id = fr.request_group_id

AND frgu.request_group_id = frg.request_group_id

AND fcpt.concurrent_program_id = frgu.request_unit_id

AND frt.LANGUAGE = USERENV('LANG')

AND fcpt.LANGUAGE = USERENV('LANG')

AND fcpt.user_concurrent_program_name = :conc_prg_name

ORDER BY 1,2,3,4

Query to take a backup/Details of all database links in oracle

 

SELECT dl.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',dl.DB_LINK,dl.OWNER) FROM dba_db_links dl;

select dl.OWNER,dl.DB_LINK,dl.USERNAME,dl.HOST,CREATED from dba_db_links dl;

DROP Database Links:

drop database link <db_link_name>;              --Needs to run as a specific user (Owner of the db links)

drop public database link <db_link_name>;    --Needs to run as a sys user.