Saturday, December 21, 2024

Workflow debugging scripts wfstat.sql

 Understanding the Workflow debugging scripts wfstat.sql


The wfstat.sql script is a valuable tool used in Oracle E-Business Suite (EBS) for debugging and understanding the status and flow of workflows. It helps track, analyze, and debug workflow-related issues by providing detailed insights into the workflow statuses, activities, notifications, and related details.

Here’s how to understand and use the wfstat.sql script for debugging workflows:

Purpose of wfstat.sql

  • To fetch the status of a specific workflow instance.
  • To provide detailed information on the workflow's current state, activities, notifications, and their statuses.
  • To debug issues in workflow execution, such as stuck or errored activities.

Key Parameters of wfstat.sql

  1. ITEM_TYPE: The type of workflow item (e.g., REQAPPR for requisition approval, POAPPRV for purchase order approval).
  2. ITEM_KEY: The unique identifier for a specific workflow instance.
  3. USER_ID (optional): Used to filter workflow details specific to a user.
  4. ACTIVITY_STATUS: Filters activities based on their status (e.g., OPEN, COMPLETED, ERROR).

Steps to Use wfstat.sql

  1. Locate the script: The wfstat.sql script is typically located in $FND_TOP/sql/ or your database administrator may provide access to the script.
  2. Run the script: Execute the script using SQL*Plus or a similar SQL client.
    SQL> @wfstat.sql
  3. Provide Input: Enter the required parameters such as ITEM_TYPE and ITEM_KEY when prompted.
  4. Analyze the Output:
    • Workflow Instance Information: Details about the workflow, including creation date, status, and current owner.
    • Activity Statuses: Shows activities in the workflow, their status (OPEN, COMPLETED, ERROR), and timestamps.
    • Notifications: Information on notifications sent as part of the workflow, including status (SENT, CANCELED, ERROR).
    • Error Details: If an activity is in error, the output will indicate the error message or stack trace.

Debugging Common Issues

  1. Stuck Activities:
    • Check for OPEN activities that have not progressed.
    • Identify the ACTIVITY_NAME and trace the issue in the corresponding PL/SQL procedure or function.
  2. Errored Activities:
    • Look for activities with the status ERROR.
    • Review the error messages or stack trace in the output.
  3. Notification Issues:
    • Check the notification statuses (SENT, CANCELED, ERROR).
    • If notifications are not being sent, verify the workflow mailer service and notification preferences.
  4. Workflow Ownership:
    • Ensure that the workflow item has the correct owner (RUNNING or DEFERRED).
    • Reassign ownership if necessary using administrative workflow tools.

Proactive Measures

  • Schedule regular audits of critical workflows using scripts like wfstat.sql.
  • Monitor notifications and activity logs for potential issues.
  • Automate repetitive tasks in workflow monitoring using shell scripts or custom reports.

By using the wfstat.sql script effectively, you can identify, troubleshoot, and resolve workflow-related issues, ensuring seamless operation of Oracle E-Business Suite workflows.


While logging a bug in Workflow area, it is always advisable to provide output of some of the diagnostic scripts such as wfstat.sql. This blog provides a detailed description about the usage of and the information this script provide. Output of wfstat.sql script provides workflow activity details for a given workflow process. Output of wfmlrdbg.sql provides details about a particular notification that are used to debug any issues with a Workflow Notification. wfbesdbg.sql is used to debug the event and subscription related issues in Business Event System.

wfstat.sql

This script can be used to debug the workflow activity issues for a given workflow. It can be run using the below command. Use spool command to generate the output of this into a text file.


SQL>spool wfstat.txt
SQL>sqlplus apps/apps @$FND_TOP/sql/wfstat.sql <item type> <item key>
SQL>spool off;


It generates a text file called wfstat.txt that contains output of this script. You can get the following information from this:

  1. Workflow item type and itemkey
  2. Workflow activities' start time and end time
  3. Parent process and child process information
  4. Status and result of each activity
  5. Start date and End date of each activity
  6. Errored Activities and Error Process Activity Statuses
  7. Notification details with notification id and notification recipient
  8. Performer of each activity.

The following section explains each item from the output file:

WorkFlow Item

It provides item type, item key, start date and end date for the workflow item. It also provides the root activity details for the same.

Activity Statuses

It provides start date, end date, status, result for each of the activities. In addition to that it provides the performer user and notification ID for Notification activities. It also provides the sequence of execution of the activities.

Activity Statuses History

It provides status history of the all the actions performed on the different activities

Notifications

It provides information about a notification such as Notification ID, status, message name, begin date, end date, from-user, to-user, subject.

Errored Activities

It provides all the activities information that are resulted into ERROR status.

Posted by at No comments:
Labels: , ,

Friday, December 20, 2024

EBS 12.2 Certified with Database Release Update 19.25 - All Platforms (October 2024)

 EBS 12.2 Certified with Database Release Update 19.25 - All Platforms (October 2024)


Oracle announced the release of the October 2024 updates to the E-Business Suite (EBS) Technology Codelevel Checker (ETCC) - Part II.  The latest October 2024 updates for ETCC include the certification of Oracle Database Release Update (DBRU) 19.25(19.25.0.0.241015-Oct2024) for all UNIX and Windows platforms with Oracle E-Business Suite Release 12.2 on-premises.

DBRU 19.25

For additional details, refer to Oracle E-Business Suite Release 12.2: Consolidated List of Oracle Database Patches and Technology Bug Fixes (MOS Note 1594274.1)

References

Related Articles

Posted by at No comments:
Labels: ,

Cost Management Strategies for Oracle Cloud Infrastructure

 

Cost Management Strategies for Oracle Cloud Infrastructure


Cost Management Strategies for Oracle Cloud Infrastructure

Efficient cost management is one of the critical aspects of cloud adoption for enterprises. While Oracle Cloud Infrastructure (OCI) offers robust features and high performance, organizations must implement effective strategies to manage and optimize costs. This blog provides a detailed guide for CIOs, finance teams, and IT managers to understand and control their OCI expenses.

Understanding OCI Pricing Models

Oracle Cloud Infrastructure uses a transparent and predictable pricing model designed to offer flexibility and control. Here are the key components:

  1. Pay-as-You-Go (PAYG):

    • Allows you to pay only for what you use without upfront commitments.
    • Ideal for organizations with variable workloads or short-term projects.

  2. Bring Your Own License (BYOL):

    • Enables customers to leverage existing on-premises Oracle licenses.
    • Reduces overall costs by reusing licenses in OCI.

  3. Universal Credits:

    • A prepaid model offering flexibility to use any OCI service.
    • Provides cost predictability and discounts for committed usage.

  4. Always Free Tier:

    • Includes free access to compute, storage, and other resources for development and experimentation.

Understanding these models and aligning them with your organization’s needs is the first step toward cost optimization.

Tips for Workload Optimization and Cost Monitoring

Efficient cost management involves optimizing workloads and continuously monitoring expenses. Here are practical strategies:

  1. Right-Sizing Resources:

    • Analyze usage patterns and scale compute and storage resources to actual needs.
    • Use OCI's Auto Scaling feature to dynamically adjust resources.

  2. Instance Flexibility:

    • Choose the right instance type (e.g., VM.Standard, BM.Standard) based on workload requirements.
    • Migrate to Ampere A1 instances for cost-efficient performance.

  3. Leverage Reserved Instances:

    • Commit to reserved instances for predictable, long-term workloads to receive significant discounts.

  4. Utilize Tagging:

    • Implement tagging for resources to track and allocate costs by department, project, or business unit.

  5. Enable Budget Alerts:

    • Set up budgets and notifications in OCI to monitor and control spending.

  6. Optimize Storage:

    • Use Object Storage lifecycle policies to automatically archive or delete unused data.
    • Leverage OCI’s tiered storage options for cost savings.

Tools for Cost Analysis and Management

Oracle Cloud provides a suite of tools to help organizations monitor and manage costs effectively:

  1. Oracle Cloud Cost Analysis:

    • Offers detailed insights into usage and spending trends.
    • Visualize costs by service, region, or resource over time.

  2. Budgets and Alerts:

    • Set thresholds for budgets and receive alerts when spending exceeds limits.
    • Helps prevent cost overruns.

  3. Usage Reports:

    • Access detailed usage reports for billing and cost analysis.
    • Integrate reports with third-party tools for advanced analytics.

  4. Resource Manager:

    • Automates resource provisioning and helps ensure that cost-saving configurations are consistently applied.

Real-World Scenarios of Cost Savings

  1. Case Study: Optimizing Compute Costs:

    • A mid-sized e-commerce company transitioned from on-demand compute instances to reserved instances.
    • Result: Achieved 30% cost reduction while maintaining performance.

  2. Case Study: Storage Optimization:

    • A healthcare provider implemented Object Storage lifecycle policies to archive older medical records.
    • Result: Reduced storage costs by 40%.

  3. Case Study: Leveraging BYOL:

    • A global manufacturing firm utilized BYOL to migrate its Oracle Database licenses to OCI.
    • Result: Saved 25% in licensing costs while improving database performance.

Conclusion

Effective cost management in Oracle Cloud Infrastructure requires a combination of understanding pricing models, optimizing workloads, and leveraging OCI tools. By implementing these strategies, CIOs, finance teams, and IT managers can maximize the value of their OCI investments and achieve substantial cost savings.

Start your cost optimization journey today by exploring the powerful tools and resources available within Oracle Cloud Infrastructure.

is this enough or more to wrirte 

Posted by at No comments:
Labels: ,

Wednesday, December 18, 2024

How to Change the Oracle OEM Domain in a URL

 How to Change the Oracle OEM Domain in a URL


Oracle Enterprise Manager (OEM) is a robust tool used for monitoring and managing Oracle environments. Occasionally, you may need to update the domain name in the OEM URL, such as when transitioning to a new network, updating a Fully Qualified Domain Name (FQDN), or integrating with new DNS settings. This blog outlines the step-by-step process to change the domain name in the OEM URL.


Why Change the OEM Domain?

There are several reasons you might need to update the domain in the OEM URL:

  • Network migration: Moving OEM to a new network.

  • Domain standardization: Aligning with organizational naming conventions.

  • SSL updates: Adopting a new domain for secure connections.

For example, changing the URL from 

https://oem.old-domain.com:7801/em   to 

https://oem.new-domain.com:7801/em.

Steps to Change the Domain

Step 1: Update Hostname in Configuration Files

  1. Locate the OEM Configuration Directory: Navigate to the configuration directory in your Oracle Management Service (OMS) installation:

    cd $OMS_HOME/sysman/config

  2. Modify the emoms.properties File: Open the emoms.properties file:

    vi emoms.properties

    Update the URL to reflect the new domain:

    oracle.sysman.eml.mntr.emdURL=https://<new-domain>:<port>/emd/main

    Save and exit the file.

  3. Check the emomslogging.properties File: Review this file for any references to the old domain and update them if needed.

  4. Backup Configuration Files: Before proceeding further, ensure that you back up all configuration files in the directory:

    cp *.properties /backup/location/

Step 2: Update the WebLogic Server Configuration

  1. Access the WebLogic Admin Console: Navigate to the WebLogic Admin Console at:

    http://<current-domain>:7001/console

    Log in using your WebLogic admin credentials.

  2. Update the Server URL:

    • Go to Servers > AdminServer > Protocols > HTTP.

    • Update the Listen Address to the new FQDN.

    • Save and apply the changes.

  3. Restart WebLogic Services: Stop and restart the WebLogic Admin and Managed Servers:

    $DOMAIN_HOME/bin/stopWebLogic.sh
$DOMAIN_HOME/bin/startWebLogic.sh

  4. Verify Changes in WebLogic Logs: Monitor the WebLogic logs to ensure there are no errors during the restart:

    tail -f $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log

Step 3: Update the Repository Information

  1. Stop OMS:

    $OMS_HOME/bin/emctl stop oms

  2. Update the OMS Configuration: Run the following command to update the domain name in the repository:

    $OMS_HOME/bin/emctl config oms -repos_pwd <repository_password>

  3. Restart OMS:

    $OMS_HOME/bin/emctl start oms

  4. Synchronize Agents: Ensure that all OEM agents are synchronized with the new domain by reconfiguring them:

    <AGENT_HOME>/bin/emctl stop agent
<AGENT_HOME>/bin/emctl clearstate
<AGENT_HOME>/bin/emctl start agent

Step 4: Validate the New URL

  1. Access the New OEM URL: Open a browser and visit:

    https://<new-domain>:<port>/em

    Ensure the page loads correctly.

  2. Test Agent Connectivity: Verify that all agents can communicate with the new OMS URL:

    <AGENT_HOME>/bin/emctl status agent
<AGENT_HOME>/bin/emctl upload

  3. Validate Reporting: Test report generation and dashboard views to ensure the changes did not disrupt critical OEM functionality.

Step 5: Update DNS and SSL Certificates (if Applicable)

  1. Update DNS Records: Ensure the new domain is mapped to the OEM server's IP address in the DNS system.

  2. Regenerate SSL Certificates: If SSL is enabled, regenerate certificates with the new domain name using Oracle Wallet Manager or a tool like OpenSSL. Replace the old certificates on the server.

  3. Restart HTTPS Services: After updating SSL certificates, restart HTTPS services to apply changes:

    $OMS_HOME/bin/emctl secure oms -remove
$OMS_HOME/bin/emctl secure oms -enable

Verification

  1. Monitor Logs for Errors: Check the OEM logs for any errors during or after the update:

    tail -f $OMS_HOME/sysman/log/emctl.log

  2. Validate with RMAN: Run an RMAN validation to ensure no issues in agent communication:

    RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

  3. Perform a Full System Health Check:

    • Run a database diagnostic check using OEM.

    • Confirm all critical alerts and notifications are working as expected.

Best Practices to Prevent Future Issues

  • Backup Configuration Files Regularly: Always take a backup before making any configuration changes.

  • Monitor Undo Tablespace and Redo Logs: Maintain sufficient space to avoid database corruption during recovery.

  • Apply Oracle Patches: Keep your Oracle environment updated with the latest patches.

  • Document Changes: Maintain detailed documentation of all changes made to facilitate troubleshooting and audits.

By following these steps, you can seamlessly change the domain name in the Oracle OEM URL while ensuring minimal disruption to monitoring and management activities. 

Posted by at No comments:
Labels: ,

Tuesday, December 17, 2024

Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

 Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

The following error is occurring in the alert.log right before the database crashes.

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

This issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.


This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details:
Undo corruption may be caused after a shrink and the same undo block may be used
for two different transactions causing several internal errors like:
ORA-600 [4193] / ORA-600 [4194] for new transactions
ORA-600 [4137] for a transaction rollback.

Solution
----------

Best practice to create a new undo tablespace.
This method includes segment check.

1. Create pfile from spfile to edit
SQL> Create pfile='/tmp/initsid.ora' from spfile;

2. Shutdown the instance

3. set the following parameters in the pfile /tmp/initsid.ora
    undo_management = manual
    event = '10513 trace name context forever, level 2'

4. SQL>>startup restrict pfile='/tmp/initsid.ora'

5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR.  There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.

If all offline then continue to the next step

6. Create new undo tablespace - example
SQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;

7. Drop old undo tablespace
SQL>drop tablespace <old undo tablespace> including contents and datafiles;

8. SQL>shutdown immediate;

9 SQL>startup nomount;  --> Using your Original spfile

10. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = '<new tablespace created in step 6>' scope=spfile;

11. SQL>shutdown immediate;

12. SQL>startup;  --> Using spfile
 


The reason we create a new undo tablespace first is to use new undo segment numbers that are higher then the current segments being used.  This way when a transaction goes to do block clean-out the reference to that undo segment does not exist and continues with the block clean-out.

Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)