Friday, 29 May 2026

Eaxadata and Oracle Database interview question and answer 2026 Part3

Question : when Hash Partitioning is required and its use cases in Oracle rac 19c


Hash Partitioning in Oracle Database 19c is an algorithm-based strategy that randomly distributes data across a predetermined number of partitions to prevent bottlenecks, ensure even disk I/O, and resolve hot block contention. It does not follow business logic but optimizes performance at scale. 
When is Hash Partitioning Required?
While Hash Partitioning is technically optional, it is virtually required or highly recommended in scenarios where:
  • You want to avoid "hot block" contention (the 90-10 split problem): High-volume concurrent inserts into tables using sequence-generated primary keys (where the index only grows on one side) cause massive index leaf-block waits (e.g., buffer busy waits or enq: TX - Index contention). Hash partitioning distributes this write-load. 

  • You are using Oracle RAC: In an Oracle Real Application Clusters (RAC) environment, all nodes simultaneously insert and read from the same table. Hash partitioning distributes the data segments across the cluster, reducing cache fusion traffic and preventing single-node I/O bottlenecks. 
Core Use Cases in Oracle RAC 19c
1. Reducing Index Contention in High-Volume OLTP
In an OLTP system running on RAC, multiple instances might continuously insert data into a table based on a monotonically increasing sequence (e.g., ORDER or NOCACHE sequences). This causes severe contention on the last index block (right-growing index). 
  • Solution: Create a Hash Partitioned Global Index on the sequence column. This forces Oracle to spread the leaf blocks across multiple files/segments, heavily improving concurrent insert throughput


2. Balancing Balanced Disk I/O
If your table contains millions of rows but there is no natural range (like a date) or explicit category (like a region) to partition by, large unpartitioned tables cause I/O hotspots. 
  • Solution: Hash partitioning perfectly balances the load across multiple disks or ASM disks, ensuring that reads/writes are distributed evenly across the hardware. 
3. Parallel Partition-Wise Joins
In Data Warehouses or reporting tables stored on RAC, executing large joins on unpartitioned tables is extremely memory-intensive. []
  • Solution: By hash partitioning the large tables on the join key, Oracle can execute a partial or full partition-wise join. This means each RAC node can work on joining individual partitions in parallel, massively decreasing execution times and CPU usage. 
Best Practice Rule of Thumb
When implementing Hash Partitioning, always define the number of partitions as a power of \(2\) (e.g., 2, 4, 8, 16, 32). This ensures the most mathematically even distribution of hash values and reduces data skewing across partitions

For more Details
Recommendations for Choosing a Partitioning Strategy


Question : what is most common wait event and how to fix it in RAC

In Oracle Real Application Clusters (RAC), the most common wait events are related to Global Cache (GC) operations where instances request and share data blocks across the cluster interconnect. The frequent culprits are gc cr request (Read) and gc buffer busy (Contention). 
Resolving these issues involves optimizing application workloads and inter-instance traffic: []
1. gc cr request & gc current block busy
  • The Cause: A session on one node requests a data block (current or consistent read) from another node, but the transfer is delayed due to a heavy interconnect

     

  • workload, or the remote instance is slow at writing to its redo logs. 
  • How to Fix:
    • Bind Workloads: Pin heavily transactional application modules/users to specific nodes. This prevents the same table from being heavily modified across multiple nodes simultaneously (Cache Fusion pinging).
    • Partitioning: Partition your high-access tables or indexes across nodes. Ensure that transactions inserting or updating data route to the specific instance that owns that partition.
    • Tune LGWR: Optimize your Log Writer (LGWR) process. Slow log flushes prevent blocks from being released. Ensure redo logs are placed on fast I/O subsystems (e.g., SSDs).
    • Check Interconnect: Verify your physical private interconnect network speed, bandwidth, and latency. 

  • 2. gc buffer busy acquire & gc buffer busy release
    • The Cause: Multiple sessions across different nodes are trying to read or modify the exact same data blocks simultaneously. These "hot blocks" cause the cluster to queue requests. 
    • How to Fix:
      • Fix Hot Indexes: The most frequent offender is sequential inserts (e.g., primary key sequences). Use Hash Partitioning on sequences or indexes to distribute inserts across multiple blocks and prevent bottlenecks.
      • Reorganize Tables: Eliminate free-list contention or high row density on data blocks by adjusting parameters like INITRANS and PCTFREE on heavily updated tables. 
    3. read by other session
    • The Cause: Multiple sessions are concurrently requesting the same data that is not yet in the buffer cache, forcing them to wait on the first session to complete the disk read. []
    • How to Fix:
      • Tune Top SQL: Identify which queries are performing inefficient full table scans or fragmented index scans using the Oracle AWR Reports to prevent redundant heavy I/O. 
      • Diagnostic Steps
      To pinpoint which objects and SQL queries are triggering these waits, run the following diagnostic queries to see which blocks are affected:
      1. Identify the heavily contended objects via V$SEGMENT_STATISTICS filtered by GC CR BLOCKS RECEIVED or GC BUFFER BUSY.
      2. Isolate sessions experiencing Global Cache (GC) waits by running an ASH (Active Session History) script filtering WHERE event LIKE 'gc%'

    •      

    Question : most common wait event and how analyze and fix it with steps in rac  

     

    In an Oracle RAC (Real Application Clusters) environment, tuning revolves around minimizing inter-instance block transfers (Global Cache/gc waits) and resolving standard I/O/concurrency issues. The most common RAC-specific wait events center on Cache Fusion, including gc cr request, gc current block busy, and gc buffer busy acquire. 
    Step-by-Step Methodology to Analyze and Fix
    1. Analyze (Locate the Bottleneck): Use the Automatic Workload Repository (AWR) or run an ASH (Active Session History) report to identify the top wait events. Use GV$SESSION_WAIT and GV$ACTIVE_SESSION_HISTORY for real-time analysis to find which instance and SQL ID are suffering from the wait event.
    1. Examine SQL & Segment: Find the specific database objects (tables/indexes) associated with the SQL_ID using DBA_HIST_SQLTEXT and V$SQL.
    2. Remediate: Apply tuning methods (partitioning, indexing, or service alignment) to prevent inter-node block pinging. 
    Common RAC Wait Events & Fixes
    1. gc cr request (Global Cache Current Request)
    • Meaning: A session is waiting for a consistent read (CR) block to be shipped from another instance over the cluster interconnect. 
    • How to Fix:
      • Ensure your cluster interconnect is operating at maximum speed (e.g., dedicated 10Gb/100Gb private network).
      • Check for unselective indexes or full table scans spanning multiple nodes. Optimize the SQL to reduce the number of blocks required.
        • Implement Table Partitioning to logically segment your data so that nodes handle specific partitions, keeping block requests localized. 
        • 2. gc current block busy / gc cr block busy
        • Meaning: The local instance requested a block, but the block is "busy" on the remote instance (e.g., currently being modified or involved in another Global Enqueue Service operation). 
        • How to Fix:
          • High busy waits usually mean you have "Hot Blocks" (multiple instances concurrently trying to update the exact same block).
          • If using sequences for primary keys, alter the sequence to use the CACHE clause with ORDER to reduce contention on the sequence block itself.
          • Use Hash Partitioning or reverse-key indexes for tables where multiple nodes perform heavy insertions.
        3. gc buffer busy acquire / gc buffer busy release
        • Meaning: A session cannot access a buffer cache block because another session is actively reading or modifying it across instances (release/acquire synchronization). 
        • How to Fix:
          • This is commonly caused by frequent concurrent read/write operations on the same table or index leaf blocks.
          • Reduce the number of indexes on the table if INSERT or UPDATE performance is slow.
          • Increase INITRANS and FREELISTS on the hot table/indexes to reduce concurrency collisions at the block level. 
    General Oracle/RAC Best Practices
    • Optimize Interconnect: Network latency is the primary killer of RAC performance. Check your network configuration with oifcfg getif and monitor for dropped packets. 
    • Pin Applications to Nodes: If an application heavily queries and modifies a specific table or schema, use Instance-Specific Services to bind that workload to a single node. This effectively turns a distributed RAC ping into a localized L1 cache hit. 
    To automate and drill down further into cluster-wide wait metrics, generate and review an ADDM (Automatic Database Diagnostic Monitor) report by accessing the Advisor Central via Oracle Enterprise Manager. 

     

    Question : most common wait event and how analyze and fix it with steps in Oracle rac 19c

    In Oracle RAC 19c, wait events are categorized into wait classes to identify performance bottlenecks. The most common events center around Global Cache (Cache Fusion), Disk I/O, and Concurrency. 
    1. gc cr request / gc current block request (Global Cache/RAC Interconnect)
    Sessions wait to read (cr) or modify (current) a block of data that is currently cached in another RAC instance's memory. 
    • Cause: "Ping" storms due to high inter-instance block contention or an undersized buffer cache. 
    • How to Analyze:
      1. Check for high values in the V$CR_BLOCK_SERVER and V$CURRENT_BLOCK_SERVER views.
      2. Map the waited-on block to its table using DBA_EXTENTS and V$BUFFER_CACHE.


    • Steps to Fix:
      1. Partitioning: Hash or List partition the highly contested tables based on the instance accessing them.
      2. Services: Create specific services to ensure specific workloads (e.g., jobs for Node 1 vs Node 2) route transactions to the node modifying the data, localizing cache fusion.
      3. Network: Ensure your private interconnect (switch, cables, and MTU settings) operates at maximum speed without packet loss. 
    2. db file sequential read / db file scattered read (User I/O)
    Sessions are waiting for physical I/O operations to complete. Sequential = single block (often via indexes); Scattered = multi-block (often via Full Table Scans). 
    • Cause: Suboptimal SQL execution plans or slow disk sub-systems. 
    • How to Analyze
      1. Run an AWR Report to locate the Top SQL causing the most disk reads.
      2. Check the P1 and P2 parameters of the V$SESSION_WAIT view to find the file and block number being waited on.
    • Steps to Fix:
      1. Tune SQL: Gather fresh statistics (DBMS_STATS.GATHER_TABLE_STATS) and add missing indexes.
      2. Disk Performance: Identify sluggish LUNs or spindles using V$FILESTAT and migrate heavily hit datafiles to faster storage (e.g., NVMe/SSDs). 
      • 3. log file sync (Commit)
      A session is waiting for the Local Log Writer (LGWR) to finish writing redo log entries to disk after an application issues a COMMIT


    • Cause: Frequent, single-row COMMITs (bad application design) or slow redo log I/O. 
    • How to Analyze:
      1. Check the V$SYSSTAT or V$SESSTAT views for the exact count of "user commits".
      2. Validate your log buffer size.
    • Steps to Fix:
      1. Batching: Modify the application to batch commits rather than committing inside a row-by-row loop.
      2. I/O Tweaks: Move online redo logs to dedicated, fast ASM disks/SSDs (or increase LOG_BUFFER if it is too small). 
    4. buffer busy waits / gc buffer busy acquire (Concurrency)
    A process is waiting to access a data block in memory because another process is concurrently pinning, reading, or transferring that block.
  • Cause: Too many sessions attempting to update the same rows (hot blocks) or insufficient free lists for a segment. 
  • How to Analyze:
    1. Query V$WAITSTAT to determine what block types (e.g., data block, undo block) have high waits.
    2. Identify the hot object by looking at V$SESSION and V$ACTIVE_SESSION_HISTORY (ASH). 
  • Steps to Fix:
    1. Table Tuning: Decrease the rows per block (e.g., lower PCTFREE or increase INITRANS).
    2. Indexes: Rebuild or drop fragmented indexes with high clustering factors.
    3. Sequences: If caused by sequence increments, set the sequence CACHE size to a higher number (e.g., CACHE 1000 or ORDER/NOORDER). 

    • General Triage and Action Plan
    To systematically address any RAC performance issue in Oracle 19c, use the following step-by-step diagnostic process:
    1. Check for Alerts: Check for any specific node evictions or errors in the Oracle Clusterware log using crsctl query crs activeversion.
    2. Review Top Events: Run the AWR Report over the problematic time frame. Look at the "Top 5 Timed Foreground Events".
    3. Map to SQL: Cross-reference the wait event in the ASH views (V$ACTIVE_SESSION_HISTORY) to identify the exact SQL_ID causing the delay.
    4. Implement Recommendations: Generate an ADDM (Automatic Database Diagnostic Monitor) report using @$ORACLE_HOME/rdbms/admin/addmrpti.sql to get official Oracle-recommended tuning steps. 



    Question : installation of oracle 19c, 23ai and 26ai on oracle linux and its steps

    Installing Oracle 19c, 23ai, and 26ai on Oracle Linux follows the same fundamental steps: installing prerequisites, configuring the OS, unpacking the binaries, and running the installer (OUI) or DBCA. The key differences lie in the specific pre-installation RPMs, kernel parameters, and environment settings


    The installation methods differ across these versions based on how their software packages are distributed and configured.
    1. Operating System & Hardware Preparation
    Before downloading the software, ensure your Oracle Linux 8 or 9 environment is updated and meets minimum hardware and kernel requirements.
    • RAM: Minimum 8 GB.
    • Disk Space: At least 100 GB in the designated directory structure (e.g., /u01).
    2. Install Pre-Installation Packages
    Oracle provides pre-installation RPMs that automatically configure your Linux environment (creating the oracle user, oinstall groups, and adjusting kernel limits)

  • For Oracle 19c:
    bash
    sudo dnf install -y oracle-database-preinstall-19c
  • For Oracle 23ai:
    bash
    sudo dnf install -y oracle-database-preinstall-23ai

    • For Oracle 26ai:
      bash
      sudo dnf install -y oracle-ai-database-preinstall-26ai

    3. Create Oracle Directory Structure
    Create the required directory structure for the Oracle Base and Home paths.
    bash
    sudo mkdir -p /u01/app/oracle/product/<version>/dbhome_1
sudo chown -R oracle:oinstall /u01
sudo chmod -R 775 /u01

    • 4. Configure Environment Variables
    Log in as the oracle user and update their .bash_profile.
    • 19c / 23ai:
      bash
      export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/<version>/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
      26ai Specific Note: When using Oracle Linux 9, you may need to bypass certain distribution checks to prevent installer failures by exporting this additional flag before starting the GUI:


    • bash
      export CV_ASSUME_DISTID=OL8
      5. Download and Unpack Software
    Download the desired installation .zip file from the Oracle Software Delivery Cloud. Unzip it directly into the ORACLE_HOME directory as the oracle user. 
    bash
    cd $ORACLE_HOME
unzip /path/to/downloaded/linuxx64_version_db_home.zip
    6. Run the Installer (OUI)
    Depending on your preference, you can install the software using the interactive GUI or in silent mode via response files.
    • GUI Method: Launch the Graphical Installer (requires X11 forwarding).
      bash
      cd $ORACLE_HOME
./runInstaller
      Follow the graphical prompts to choose "Software Only", configure the Oracle inventory, and complete the installation.
    • Root Scripts: Once prompted by the installer, open a separate terminal as the root user and execute the required scripts:

    • bash
      sudo /u01/app/oracle/product/<version>/dbhome_1/root.sh
sudo /u01/app/oracle/oraInventory/orainstRoot.sh

    5. Create the Database
    After the software is successfully installed, you must create the database instance using the Database Configuration Assistant (DBCA). 
    1. Start DBCA from the oracle user terminal:
      bash
      dbca
      Follow the wizard steps to create a database:
      • Select Create a database.
      • Choose Advanced Configuration for better customization (e.g., character sets, memory allocation).
        • Define your Global Database Name and SID (e.g., ORCL).
        • Set secure passwords for the administrative accounts.
        • Select your preferred storage (File System) and Fast Recovery Area options.
        • Finish the wizard and allow it to generate the database. 
        • 6. Verification
        Once DBCA finishes, verify that your database is successfully running by checking its status via SQL*Plus:
        bash
        su - oracle
sqlplus / as sysdba
SQL> SELECT name, open_mode FROM v$database;



        Question : prerequisite and installation of oracle two RAC 19c, 23ai and 26ai on oracle linux and its steps


        Deploying an Oracle Two-Node RAC (19c, 23ai, or 26ai) on Oracle Linux follows 
        a consistent architecture across these versions.
         It requires thorough preparation of OS prerequisites, 
        shared storage/ASM, network infrastructure, and the sequential installation of Oracle Grid Infrastructure (GI), 
        the Database Home, and the RAC Database


        1. Prerequisites and System Requirements
        Hardware & OS
        • Operating System: Oracle Linux 8 or 9 (for 19c/23ai/26ai) 
        • Memory: At least 8 GB RAM per node 
        • Storage:
          • Shared disks for OCR/Voting Disks (usually 3 x 5GB)
          • Shared disks for Database, Redo Logs, and Flash Recovery Area (FRA)
        • Swap Space: 2GB minimum for each node 
        Networking (Two Nodes)
        Each node requires two Network Interface Cards (NICs):
        1. Public Network: For client connections and VIPs
        2. Private Network (Interconnect): For Cache Fusion and cluster synchronization. 
        • Host Configuration: Ensure /etc/hosts contains entries on both nodes for Public IP, Virtual IP (VIP), Private IP, and SCAN (Single Client Access Name) IP. [1, 2]
        2. OS Preparation
        Oracle automates almost all prerequisite packages and kernel parameters via pre-install RPMs. [1, 2]
        1. Install the Pre-install RPM (e.g., replace 19 with 23 or 26 depending on your version):
          bash
          sudo dnf install -y oracle-database-preinstall-19c
        Disable Firewall & SELinux:
        bash
        sudo systemctl disable firewalld --now
sudo setenforce 0

        sudo sed -i 's/^SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config
        1. Configure Users & Passwords: The pre-install RPM creates the grid and oracle users. Set passwords for both:
          bash
          sudo passwd grid
sudo passwd oracle


        3. Step-by-Step Installation Process
        Step 1: Install Oracle Grid Infrastructure (GI)
        You must install Grid Infrastructure before the Database Home. 
        1. Unzip the Grid Infrastructure software into the Grid home directory as the grid user on Node 1.

      • Launch the Grid Setup installer:
        bash
        ./gridSetup.sh

        Installer Prompts: Select "Configure Oracle Grid Infrastructure for a Cluster" -> "Configure a Standard Oracle RAC cluster".
      • Cluster Details: Add both nodes (Node 1 and Node 2) and specify SCAN details.
      • Storage: Select Oracle ASM (Automatic Storage Management). Create Disk Groups for OCR/Voting and Data.
      • Root Scripts: At the end of the installation, the installer will prompt you to run orainstRoot.sh 
      • and root.sh on both nodes (run on Node 1 first, then Node2

      • Step 2: Install Oracle Database Software
        1. Log in as the oracle user on Node 1.
        2. Unzip the Database software and launch the installer:
          bash
          ./runInstaller
        3. Configuration Options: Select "Set up Software Only" -> "Oracle Real Application Clusters (RAC) database installation".
        4. Select both nodes in the cluster.
        5. Root Scripts: Execute root.sh on both nodes when prompted. 
      • Step 3: Create the RAC Database
        1. Log in as the oracle user and launch the Database Configuration Assistant (DBCA):
          bash
          dbca

          Select "Create a Database" and choose "Oracle Real Application Clusters (RAC) database".
        2. Select both cluster nodes.
        3. Choose the Advanced Configuration to specify your character sets, memory allocation, and ASM disk groups for Data and FRA.
        4. Complete the wizard to generate and start the RAC database instances across your nodes


      • Question : prerequisite and upgradation of oracle two RAC 19c, 23ai and 26ai on oracle linux and its steps

      • Upgrading an Oracle RAC environment from 19c directly to 26ai 
      • (the long-term support AI release) or 23ai on Oracle Linux requires a structured, multi-phase approach. You must perform the Grid Infrastructure (GI) upgrade first, 
      • followed by the Database upgrade, ensuring zero-downtime rolling practices

      • 1. OS Prerequisites
        Before initiating the upgrade, you must ensure your Oracle Linux servers meet all infrastructure requirements.
        • Pre-install RPM: Use the Oracle-supplied pre-installation RPMs to automatically configure kernel parameters and create users.
        •  Run this on all RAC nodes:
          sudo dnf install -y oracle-ai-database-preinstall-26ai (or oracle-database-preinstall-23ai)           
        • Storage & Network: Ensure you have enough free space in the Grid home and that your Cluster Time Synchronization Service (CTSS) is running properly.
        • Backups: Take complete Recovery Manager (RMAN) backups of your databases and ensure your cluster is in a stable state.
        2. Grid Infrastructure (GI) Upgrade Steps (Rolling Method)
        Your GI must be upgraded to the target version (or later) before you upgrade the database

        1. Run CVU: Log in as the grid user and run the Cluster Verification Utility in rolling mode to ensure all prerequisites are met:
          /path/to/new_grid/runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/19.0.0/grid -dest_crshome /u01/app/26.0.0/grid -fixup -verbose
        2. Launch Installer: Unset your Oracle environment variables and start the OUI installer from your newly created GI home:
          ./gridSetup.sh
        3. Select Upgrade: In the GUI, select Upgrade Oracle Grid Infrastructure, and proceed through the prompts.
        4. Root Scripts: When prompted by the installer, execute the rootupgrade.sh script sequentially on each node. Wait for the script to finish on Node 1 before starting on Node 2. 

      • 3. Database Upgrade Steps (Using AutoUpgrade)
        Oracle strongly recommends using the AutoUpgrade utility to perform in-place upgrades to 23ai or 26ai. 
        1. Get the latest AutoUpgrade tool: Download the latest autoupgrade.jar file from Oracle Support (Doc ID 2185012.1). 
        2. Analyze: Create an AutoUpgrade configuration file (e.g., upg_config.cfg) and run it in analyze mode to check for invalid objects or pre-upgrade warnings:
          java -jar autoupgrade.jar -config upg_config.cfg -mode analyze           
        3. Deploy: Fix any issues highlighted in the analyze report, then run the upgrade in deploy mode:
          java -jar autoupgrade.jar -config upg_config.cfg -mode deploy           []
        4. Post-Upgrade: Once the utility marks the upgrade complete, run post-upgrade scripts,
        5.  recompile invalid objects, and update the oratab file on all RAC nodes.

      • Useful Documentation & References
        To ensure a smooth upgrade, consult the official Oracle documentation for specific parameter requirements and parameter file adjustments:
    on

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)