Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Thursday, April 18, 2019

Primary node No Longer Transmits Archive Log Files To the physical standby database, MRP Stuck and No RFS Process on Standby



Issue:

Recently I faced strange issue where I observed that one of the standby node was not showing the RFS process, checked in V$MANAGED_STANDBY
V$MANAGED_STANDBY  - showing only ARCH and MRP processes.

Archive logs not shipping from Primary to Standby Node and there are no errors observed in primary and standby alert logs.

Tried to copy Archive logs manually from Primary and Standby but MRP recovery process not applying the further archive logs on Standby.



Cause:

This caused due to outage on the standby server for the network activity.



Observations:

On Primary:

ARCH process stuck on Primary instance
SELECT PROCESS, PID, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;



DB Mode Showing UNKNOWN
select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs",
standby_logfile_active ACTIVE,
archived_seq#
from v$archive_dest_status
where dest_id =2;


On Standby:
No RFS Process showing when query below:
SELECT PROCESS, PID, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Solution:

1. On Primary:

- Set log transport state to DEFER status:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;


2. On standby server:

- Shutdown Database Listener

- Cancel Managed Recovery
SQL> alter database recover managed standby database cancel;

- Shutdown the Standby Database

SQL> shutdown immediate



3. On the Primary

Kill the ARCx Processes and the Database will respawn them automatically immediately without harming it.


ps -ef | grep -i arc

kill -9 <ospid of ARC process> <another ospid of ARC process> ...



4. On standby server

- Startup Standby Database and resume Managed Recovery

SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;


- Start Database Listener


5. Set log transport state to ENABLE status:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;


6. Monitor the alert logs at each site and ensure log shipping and
apply is occurring again.

Also, verify the DB Mode on Primary -
select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs",
standby_logfile_active ACTIVE,
archived_seq#
from v$archive_dest_status
where dest_id =2;

For Information: In case after Terminating ARCH process on primary node,
if it doesn't restart automatically, you can do the following :

On Primary:

SQL> alter system set log_archive_max_processes=4;
(increase log_archive_max_processes accordingly)


Sunday, April 14, 2019

Query to get the Discoverer Scheduled jobs with User details


Oracle Discoverer Reports Schedule details with User for SOX Audit

You may want to be able to audit log which user has scheduled a specific workbook/ Discoverer report.

If you want to query information about user that has scheduled a report / discoverer workbook:

Query:

SELECT ebr.br_job_id, dj.job "DBA_JOB", ebr.br_created_by
      ,CASE
         WHEN INSTR (ebr.br_created_by, '#') = 0
           THEN ebr.br_created_by
         WHEN INSTR (ebr.br_created_by, '#') > 0 AND INSTR (ebr.br_created_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_created_by, 2, 9))
         ELSE NULL
       END "BR Owner/Creator - USER"
      ,dj.what,dj.broken, ebr.br_name, ebr.br_workbook_name, ebr.br_description, ebr.br_created_date, ebr.br_next_run_date, dj.next_date "DBA_JOB_NEXT_DATE", ebr.br_completion_date, ebr.br_updated_by
      ,CASE
         WHEN INSTR (ebr.br_updated_by, '#') = 0
           THEN ebr.br_updated_by
         WHEN INSTR (ebr.br_updated_by, '#') > 0 AND INSTR (ebr.br_updated_by, '#', 2) = 0
           THEN (SELECT fu.user_name
                   FROM fnd_user fu
                  WHERE fu.user_id = SUBSTR (ebr.br_updated_by, 2, 9))
         ELSE NULL
       END "BR Updated by - USER"
      ,ebr.br_updated_date
  FROM EUL_OWNER.eul5_batch_reports ebr, dba_jobs dj
WHERE ebr.br_job_id = dj.job AND dj.broken = 'N';

Note : Replace EUL_OWNER with corresponding EUL owner of your environment in above query.




Thursday, July 5, 2018

Oracle EBS Workflow Email Approvals are not working in R12.2.6


Issue:

It has been observed that workflow email approvals are not working on Oracle EBS R12.2.6.
When user approve any PO through mail, its get stuck in INBOX of mailer account.


Cause:

Workflow mailer is not able to connect to IMAP account causing notifications to get stuck in INBOX of mailer account.

This is bug identified by an Oracle

IMAP server is running in SSL mode and listening on 993 port.

Below errors are captured in the Workflow Mailer log file:

oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.openNewConnection]:Unable to connect to the email store when opening the new connection at attempt #2


Solution:

Please apply the patch(Applicable for R12.2.x) as mentioned in below Note and retest:

Doc ID 2201170.1 : Workflow Notifications Are Not Getting Processed From INBOX


Reference Notes:

Doc ID 2051827.1: Configuring Oracle E-Business Suite Workflow Mailer with TLS for Microsoft Office365, Gmail, or Cloud Based Email Services for 12.1.3 and 12.2
Doc ID 2077434.1: Configuring an Oracle Workflow Notification Mailer with Cloud E-Mail Servers
Doc ID 578578.1: What Is The Correct Value of Mailer Parameter "HTML Agent" In R12?


Assign the Workflow System Administrator Privilege to a specific user or to all users

By default, the Workflow System Administrator is set to SYSADMIN which allows access to all workflow functions for all users associated to the Workflow Administration role.

Query to Check:

select *
from wf_resources
where name = 'WF_ADMIN_ROLE'; 

Sample Output:





To change the Workflow System Administrator to the role intended to have administrator privileges, perform the following steps: 

Navigate to Responsibility -> "Workflow Administrator Web Applications"
-> Click on "Administrator Workflow"
-> Administration  > Workflow Configuration

Select the user intended in the Workflow System Administrator field and Apply

OR

Set the value to ' * ' to grant administrative privileges to all users and Apply


For Specific User: Select User as required and Apply











For All Users: Set the value to ' * ' 











Note :
The Workflow System Administrator privilege is granted to all users which is not recommended for Production Environments. You can set it for non-prod instances if needed.


Wednesday, July 4, 2018

R12.2: E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'

Issue:

R12.2 E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'.

When attempting to access the XML Gateway Inbound URL  -

Getting below error:












The URL with localhost is working i.e. on VNC Server


http://localhost:8000/webservices/ECXOTAInbound













Cause:

By default, AOL/J diagnostic pages and ECXOTAInbound Servlet Page are only accessible from the local host where Oracle HTTP Server is running.

We can verify it by checking content of the s_admin_ui_access_nodes parameter in $CONTEXT_FILE.

$ grep -i s_admin_ui_access_nodes $CONTEXT_FILE

<admin_ui_access_nodes oa_var="s_admin_ui_access_nodes">localhost</admin_ui_access_nodes>


Solution:

To resolve the issue, perform the following steps :

1.    Comment out the ECXOTAInbound entries in $FND_TOP/admin/template/trusted_conf_FMW.tmp

These are the entries:

<Location ~ "^(/)+webservices(/)+ECXOTAInbound">
   Order deny,allow
   Deny from all
   Allow from %s_admin_ui_access_nodes%
</Location>

Comment out as below:

# <Location ~ "^(/)+webservices(/)+ECXOTAInbound">
#  Order deny,allow
#  Deny from all
#  Allow from %s_admin_ui_access_nodes%
# </Location>

2.     Run autoconfig so the changes are processed.

3. Bounce the oafm services or all application tier services

4. Check          $IAS_ORACLE_HOME/instances/EBS_web_<SID>_OHS1/config/OHS/EBS_web_<SID>/trusted.conf
to verify if the changes are reflected.

5. Try to access the XML Gateway Inbound URL http://<hostname>:<port>/webservices/ECXOTAInbound

This should work now.

Note: As a temporary workwround we can just try to comment out the lines in $IAS_ORACLE_HOME/instances/EBS_web_<SID>_OHS1/config/OHS/EBS_web_<SID>/trusted.conf then bounce oafm services if autoconfig cannot be executed.


Additional Checks:

Query to check if OTA is running:

select machine,action, decode(count(*),0,'Error: OTA is Not Running','OTA is Running')
from gv$session
where action like '%OXTA%'
group by machine, action; 



Reference Notes: 

Doc ID 2397274.1: Unable to Access XML Gateway ECXOTAInbound Page after Applying AD / TXK Delta 10 Patches
Doc ID 2335074.1: R12 E-Business Suite XML Gateway Inbound Test Page ECXOTAInbound Url Fails For All Users With 'Error 403 Forbidden' Or '404 Page Not Found'
Doc ID 2324193.1: R12 E-Business Suite XML Gateway Users Are Unable To Access Due To 'Error 403 Forbidden' When Accessing /webservices/ECXOTAInbound To Start Inbound XML Gateway Process


Tuesday, July 3, 2018

OBIEE 12c: How To Change The Repository (RPD) Password

We can Change the Oracle BI Repository Password Using the obieerpdpwdchg Utility

Use the following steps to Change The Repository (RPD) Password:

1. Navigate to the obieerpdpwdchg utility, which is located under $BI_DOMAIN_HOME/bitools/bin directory.

Type the following arguments for obieerpdpwdchg:

-I name_and_path_of_existing_repository
-O name_and_path_of_new_repository

2. Then, enter the current (old) password and the new password when prompted. The repository password must be longer than five characters and cannot be empty.

$ obieerpdpwdchg -I my_repos.rpd -O my_changed_repos.rpd

Please enter the repository password:

Please enter a new repository password:

Note that passwords are masked on the command line unless you include the -C option in the command to disable masking.

3. Use the uploadrpd command to upload the repository to Oracle BI Server.



OBIEE 12c: Download and Upload Repository(RPD) Commands

OBIEE 12c: Download Repository(RPD) Command :

Use the downloadrpd command to download the repository used by the service instance.

Execute the utility through a launcher script, datamodel.sh on UNIX and datamodel.cmd on Windows.

Location of the launcher script:
$DOMAIN_HOME/bitools/bin/datamodel.sh

Command:

./datamodel.sh downloadrpd -O /<RPD PATH>/<RPD_file_name_to_download.rpd> -U <weblogic user> -p <Weblogic Passwd> -si <service_instance>

Example:

$ cd $DOMAIN_HOME/bitools/bin/

$ ./datamodel.sh downloadrpd -O /tmp/myrpd_latest.rpd -U weblogic -p welcome123 -si ssi

This will prompt for RPD Password. Please provide RPD passwd as per your instance.


OBIEE 12c: Upload Repository(RPD) Command :

Use the uploadrpd command to upload the repository to Oracle BI Server.

execute the utility through a launcher script, datamodel.sh on UNIX and datamodel.cmd on Windows.


Location of the launcher script:
$DOMAIN_HOME/bitools/bin/datamodel.sh

Command:

./datamodel.sh uploadrpd -I /<RPD PATH>/<RPD_file_name_to_upload.rpd> -SI <service_instance> -U <weblogic user> -P <Weblogic Passwd>

Example:

$ cd $DOMAIN_HOME/bitools/bin/

$ ./datamodel.sh uploadrpd -I /tmp/myrpd_updated.rpd -SI ssi -U weblogic -P welcome123

This will prompt for RPD Password. Please provide RPD passwd as per your instance.