Thursday, January 2, 2025

Step By Step Oracle Database 19c Release Update (DB+OJVM) for Single Instance (19.24 to 19.25)

 

Step By Step Oracle Database 19c Release Update   (DB+OJVM) for Single Instance (19.24 to 19.25)


Step by Step Document  how to perform Oracle Database 19c Release Update (DB+OJVM) for Single Instance (19.24 to 19.25). I hope it will be useful for those who need it!

High Level Steps

1-Prerequisites

2-Download Patches

3- Copy Patches to Servers

4- Check & Update OPATCH Utility

5- Verify the Database Current Version

6-Take Necessary Backups

7-Pre Patch Steps

8-Patch Steps

9-Post Patch Steps

10-Patch Deinstallation Steps (If Needed)

Low Level Steps

1-Prerequisites

2-Download Patches

3-Copy Patches to Servers

I have copied patches to /u01/software/ directory.

4-Check & Update OPATCH Utility

You must use the OPatch utility version 12.2.0.1.43 or later to apply this patch.

Check OPATCH version

Check Version

sudo su -
su - oracle
. oraenv
cd $ORACLE_HOME/OPatch
./opatch version

# Sample Output
[oracle@oraclelinux810-test OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.44
OPatch succeeded.
[oracle@oraclelinux810-test OPatch]$

Backup Current OPatch (Skip if you have the latest version of OPatch)

cd $ORACLE_HOME
mv OPatch OPatch_12.2.0.1.43

Upgrade OPATCH (Skip if you have the latest version of OPatch)

cd /u01/software/
unzip /u01/software/p6880880_190000_Linux-x86-64 -d $ORACLE_HOME/

Check Version

cd $ORACLE_HOME/OPatch
./opatch version

5-Verify the Database Current Version

Using the below command verify the current version details and some other information related to the database.

sqlplus / as sysdba

select BANNER_FULL from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

COMP_ID    COMP_NAME                             VERSION     STATUS
---------- ------------------------------------- ----------- ----------
CATALOG    Oracle Database Catalog Views         19.0.0.0.0  VALID
CATPROC    Oracle Database Packages and Types    19.0.0.0.0  VALID
RAC        Oracle Real Application Clusters      19.0.0.0.0  OPTION OFF
JAVAVM     JServer JAVA Virtual Machine          19.0.0.0.0  VALID
XML        Oracle XDK                            19.0.0.0.0  VALID
CATJAVA    Oracle Database Java Packages         19.0.0.0.0  VALID
APS        OLAP Analytic Workspace               19.0.0.0.0  VALID
XDB        Oracle XML Database                   19.0.0.0.0  VALID
OWM        Oracle Workspace Manager              19.0.0.0.0  VALID
CONTEXT    Oracle Text                           19.0.0.0.0  VALID
ORDIM      Oracle Multimedia                     19.0.0.0.0  VALID
SDO        Spatial                               19.0.0.0.0  VALID
XOQ        Oracle OLAP API                       19.0.0.0.0  VALID
OLS        Oracle Label Security                 19.0.0.0.0  VALID
DV         Oracle Database Vault                 19.0.0.0.0  VALID

15 rows selected.

6-Take Necessary Backups

Due to any type of error if patching got failed, then you must have a backup plan.

  • Take Oracle Home Backup
  • Take Central Inventory Backup
  • Create Guaranteed Restore Point

Take Oracle Home Backup (if you have enough disk space)

How to Perform ORACLE_HOME Backup? (Doc ID 565017.1)

# Backup
# (Suggested but not mandatory) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are taking backup.
# Make sure there is enough free disk space before doing the backup.
cd /u01/app/oracle/product/<version>/
tar -pcvf /u01/backup/oracle_home_bkup_DDMMYYYY.tar <dbhomename>


# Restore
# (This step would be mandatory in the case of restore) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are restoring.
cd /u01/app/oracle/product/<version>/
mv <dbhomename> <dbhomename_DDMMYYYY>
tar -pxvf /u01/backup/oracle_home_bkup_DDMMYYYY.tar

Take Central Inventory Backup

# Backup
# (Suggested but not mandatory) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are taking backup.
more /etc/oraInst.loc
cd /u01/app/
tar -pcvf /u01/backup/oraInventoryBackup_DDMMYYYY.tar oraInventory

# Restore
# (This step would be mandatory in the case of restore) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are restoring.
cd /u01/app/
mv oraInventory oraInventory_DDMMYYYY
tar -pxvf /u01/backup/oraInventoryBackup_DDMMYYYY.tar

Create Guaranteed Restore Point

# Create Guaranteed Restore Point
1. $> su – oracle
2. $> sqlplus / as sysdba;
3. Find out if ARCHIVELOG is enabled
   SQL> select log_mode from v$database;
   If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> alter database archivelog;
7. SQL> alter database open;
8. SQL> create restore point before_upgrade guarantee flashback database;
   where CLEAN_DB is the name given to the guaranteed restore point.
9. Viewing the guaranteed restore point
   SQL> select * from v$restore_point;
   Verify the information about the newly created restore point. Also, note down the SCN# for
   reference and we will refer to it as "reference SCN#"

# Flashback to the Guaranteed Restore Point
1. $> su – oracle
2. $> sqlplus / as sysdba;
3. SQL> select current_scn from v$database;
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> select * from v$restore_point;
7. SQL> flashback database to restore point before_upgrade;
8. SQL> alter database open resetlogs;
9. SQL> select current_scn from v$database;
Compare the SCN# from step 9 above to the reference SCN#.
NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough.

# Drop Guaranteed Restore Point
drop restore point before_upgrade;

7-Pre Patch Steps

Unzip the Patches

cd /u01/software/

unzip /u01/software/p36912597_190000_Linux-x86-64.zip -d /u01/software/

unzip /u01/software/p36878697_190000_Linux-x86-64.zip -d /u01/software/

Check Conflict

cd /u01/software/36912597

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

cd /u01/software/36878697

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Stop All Database Services

Check how many instances & listener is running on the machine.

ps -ef|grep pmon

oracle      2124       1  0 Dec16 ?        00:00:01 ora_pmon_orcl

Check the Listener status.

ps -ef|grep tns

oracle      6524       1  0 00:48 ?        00:00:00 /u01/app/oracle/product/19.3/db_home/bin/tnslsnr LISTENER -inherit

On my machine, one instance and one listener are currently running, letting down both services.

lsnrctl stop LISTENER

Shutdown Database

sqlplus / as sysdba

shut immediate

8-Patch Steps

Make sure 7 GB of disk space is free in your binary location otherwise the patch will be failed.

Execute opatch apply

cd /u01/software/36912597

$ORACLE_HOME/OPatch/opatch apply

cd /u01/software/36878697

$ORACLE_HOME/OPatch/opatch apply

Start All Database Services

Once opatch apply successfully, then start all the database services.

Start Listener

lsnrctl start LISTENER

Start Database

sqlplus / as sysdba

startup

Check Database

select instance_name ||'  '||version||'  '||startup_time||'  '||database_status from gv$instance;

INSTANCE_NAME||''||VERSION||''||STARTUP_TIME||''||DATABASE_STATUS
---------------------------------------------------------------------------
orcl  19.0.0.0.0  2024-12-17:12:51:04  ACTIVE


select name, open_mode, database_role, log_mode from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE
--------- -------------------- ---------------- ------------
ORCL      READ WRITE           PRIMARY          NOARCHIVELOG

9-Post Patch Steps

Now it’s time to run datapatch -verbose which updates the patch information at the database binary & dictionary level as well as binary files.

Run datapatch -verbose

Important Note: For each separate database running on the same shared Oracle home being patched, run the datapatch utility

. oraenv

cd $ORACLE_HOME/OPatch

./datapatch -sanity_checks (optional)

./datapatch -verbose

Execute utlrp.sql

Once datapatch -verbose is completed then execute the utlrp script to validate the invalid objects.

sqlplus / as sysdba

@?/rdbms/admin/utlrp.sql

Verify the Patch at OS Level

Check applied patch details using lsinventory command.

cd $ORACLE_HOME/OPatch

./opatch lsinventory | grep 36912597

./opatch lsinventory | grep 36878697

Verify the Patch at DB Level

sqlplus / as sysdba

col action for a8
col status for a10
col action_time for a28
col description for a60
set line 999 pages 999
select patch_uid,patch_id,SOURCE_VERSION, TARGET_VERSION, action, status,action_time,description from dba_registry_sqlpatch order by action_time desc;

Check Invalid Objects

COLUMN object_name FORMAT A30
SELECT owner,object_type,object_name,status FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

no rows selected

Check DBA_REGISTRY

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

COMP_ID    COMP_NAME                             VERSION     STATUS
---------- ------------------------------------- ----------- ----------
CATALOG    Oracle Database Catalog Views         19.0.0.0.0  VALID
CATPROC    Oracle Database Packages and Types    19.0.0.0.0  VALID
RAC        Oracle Real Application Clusters      19.0.0.0.0  OPTION OFF
JAVAVM     JServer JAVA Virtual Machine          19.0.0.0.0  VALID
XML        Oracle XDK                            19.0.0.0.0  VALID
CATJAVA    Oracle Database Java Packages         19.0.0.0.0  VALID
APS        OLAP Analytic Workspace               19.0.0.0.0  VALID
XDB        Oracle XML Database                   19.0.0.0.0  VALID
OWM        Oracle Workspace Manager              19.0.0.0.0  VALID
CONTEXT    Oracle Text                           19.0.0.0.0  VALID
ORDIM      Oracle Multimedia                     19.0.0.0.0  VALID
SDO        Spatial                               19.0.0.0.0  VALID
XOQ        Oracle OLAP API                       19.0.0.0.0  VALID
OLS        Oracle Label Security                 19.0.0.0.0  VALID
DV         Oracle Database Vault                 19.0.0.0.0  VALID
15 rows selected.

10-Patch Deinstallation Steps (If Needed)

Patch Deinstallation Instructions

Shut down all instances and listeners associated with the Oracle home that you are updating.

sudo su -
su - oracle
. oraenv
lsnrctl stop LISTENER
sqlplus / as sysdba
shut immediate
quit
cd $ORACLE_HOME/OPatch
opatch rollback -id 36912597
opatch rollback -id 36878697

Rollback SQL Changes from the the Database

Datapatch is run to complete the post-deinstall SQL deployment for the patch being deinstalled.

Important Note: For each separate database running on the same shared Oracle home being patched, run the datapatch utility.

lsnrctl start LISTENER
sqlplus / as sysdba
startup
quit
cd $ORACLE_HOME/OPatch
./datapatch -sanity_checks (optional)
./datapatch -verbose

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.

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

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 

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. 

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.