Posts

Unveiling the Mystery Behind a Critical Downtime Incident: Default TRANSMIT_MODE Behavior in Oracle Database 19c's Log Archive Destinations

Image
Recently, one of our valued customers experienced a significant downtime event when their complete 6-node RAC cluster fell into a hang state due to a massive pile-up of sessions, exceeding 6000. The gravity of the situation was immediately apparent as the downtime persisted intermittently for nearly four hours, from 2:10 PM to 5:30 PM, impacting critical transactions and leading to declines. Upon their reaching out to us for assistance, our troubleshooting journey commenced, aiming to uncover the root cause and provide recommendations to rectify the issue. Problem Statement: The 6-node cluster entered a hang state despite attempted reboots, resulting in a prolonged outage with severe implications for transaction processing. Unraveling the Culprit: The issue presented itself as a perplexing anomaly, requiring thorough analysis of diagnostic data within the live system. Initial investigations revealed a high occurrence of GC and logfile sync wait events amidst other contention factors. N...
Post a Comment
Read more

Resolving 19c Oracle Database Performance Woes: Unraveling the Mystery of 'Free Buffer Waits'

Image
  Introduction:   A recent upgrade from version 19.21 left a customer grappling with a perplexing performance decline, marked by a surge in "free buffer waits." Tasks that once took a manageable two hours were suddenly stretching beyond the tolerable 10-hour mark, prompting a closer examination of the underlying issues. The customer had implemented a multi-tenant architecture utilizing a single Pluggable Database (PDB).   Unveiling the Culprit:   The initial investigation pinpointed a conspicuous increase in "free buffer waits," casting a shadow on system efficiency and causing disruptions in the normal flow of operations. Recognizing the critical nature of this bottleneck, the focus shifted to conducting a thorough hanganalyze to unravel the intricacies of the problem. Chains most likely to have caused the hang:   [a] Chain 1 Signature: 'rdbms ipc message'<='free buffer waits'       Chain 1 Signature Hash: 0x8e1d98d...
Post a Comment
Read more

Oracle Database 23c: New Features

Image
In today's rapidly evolving digital landscape, developers are in constant pursuit of tools that make application development faster, simpler, and more versatile. Oracle Database 23c emerges as a game-changer, delivering a comprehensive and user-friendly converged database for developers seeking to craft a wide array of applications, including microservices, graph-based solutions, document management, and relational databases. In this blog, we'll provide an overview of some of the exciting features and improvements introduced in Oracle Database 23c. In subsequent blogs, we will delve deeper into each of these topics to help you unlock the full potential of this cutting-edge database release. Many Datatype and SQL Enhancements😊 Oracle Database 23c introduces a host of enhancements in terms of datatypes and SQL capabilities. These improvements include: - Boolean Datatype: Simplify logical expressions and improve data accuracy. - Direct Joins for Update: Streamline update operatio...
Post a Comment
Read more

Demystifying Oracle SQL Monitor: A Comprehensive Guide

Introduction Oracle Database's SQL Monitor is a powerful tool that provides deep insights into the execution of SQL statements, offering DBAs and developers crucial information for optimizing query performance. In this guide, we will delve into the various aspects of SQL Monitor, from understanding its default behavior to generating real-time and historical reports. Whether you're new to SQL Monitor or a seasoned user, this guide aims to provide valuable insights for effectively utilizing this tool. Default Behavior and Forcing Monitoring SQL Monitor automatically tracks SQL statements that run in parallel or consume at least 5 seconds of combined CPU and I/O time during a single execution. However, if you want to monitor specific SQL statements, you can include the `MONITOR` hint within the statement itself. For instance: SQL SELECT /*+ MONITOR */ count(*) from table you can force monitoring by setting the event "sql_monitor" at the system level: SQL ALTER SYSTEM SET...
Post a Comment
Read more

ORAdiff - Find the differences between two Oracle Database releases

Image
ORAdiff – Your Ultimate Tool for Database Release Comparison In the dynamic realm of database management, staying updated and informed about the differences between database releases is crucial. This is where ORAdiff steps in, empowering you to seamlessly compare two database releases, whether they include patch bundles or not. Getting Started is a Breeze: 1. Click on the inviting menu icon located in the upper left corner of the page. 2. Explore a plethora of object types via the convenient left-hand navigation menu. 3. Handpick the Source and Target versions along with their respective patch levels. 4. Immerse yourself in the insightful report that follows. And, should you desire, apply a filter to tailor your experience. Witness the Magic of ORAdiff: Unveiling distinctions is the forte of ORAdiff. Whether it's identifying "new tables," highlighting "added parameters," exposing "changed columns," or shedding light on "removed privileges," O...
Post a Comment
Read more

Free Certification for OCI | June 1 – August 31, 2023

Image
  🔔 Exciting News! 🔔 I'm thrilled to share that Oracle has just made a groundbreaking announcement that will surely benefit all technology professionals and cloud enthusiasts! Oracle has officially announced that they are offering FREE certifications for Oracle Cloud Infrastructure (OCI)! This is a game-changer in the world of cloud certifications. As the demand for cloud skills continues to skyrocket, Oracle has taken a bold step to empower individuals to enhance their expertise and stay ahead in the rapidly evolving technology landscape. By offering free OCI certifications, Oracle has opened up incredible opportunities for professionals to upskill, broaden their knowledge, and showcase their proficiency in Oracle Cloud Infrastructure. This move not only demonstrates Oracle's commitment to fostering a skilled and empowered cloud community but also reflects their dedication to supporting professionals on their career journeys. Obtaining an OCI certification will not only vali...
Post a Comment
Read more

Investigating a CPU Spike and Resource/cluster Hang in Oracle RAC Environment

Introduction: In a complex Oracle Real Application Clusters (RAC) environment, unexpected issues can arise, leading to system instability and performance degradation. In this blog post, we will delve into a specific incident involving a CPU spike and resource hang that occurred during the execution of a high parallelism SQL job. We will analyze the root causes and explore potential solutions to mitigate such problems in the future. The Incident: On a particular day between 2:00 PM and 6:30 PM IST, an SQL job with a Degree of Parallelism (DoP) set to 200 was initiated on Node#3 around 2:12 PM IST. Soon after, the CPU Idle% dropped to single digits, reaching as low as 1% at 3:10 PM IST. This performance degradation led to Node#6 being evicted following a dirty detach reconfiguration. During the incident, it was observed that the shutdown process remained hung for an extended period, and the system was only restarted at 4:44 PM as per the Syslog. The Cluster Ready Services (CRS) stack cam...
Post a Comment
Read more