EasyReliableDBA: How to administer and patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D And Commands

Q1: How do you identify if an ExaCC X9M backup performance bottleneck is caused by the storage layer or the network target?

Answer: I execute an RMAN database validation check using BACKUP VALIDATE DATABASE. This reads all database blocks directly from the Exadata storage grid cells without writing any data to the final backup destination. If the validation completes quickly, the storage read paths are healthy, confirming that the bottleneck lies within the network routing, direct NFS configuration, or the target appliance write capacity.

Q2: Why is Oracle Direct NFS (dNFS) preferred over standard kernel OS mounts when backing up Exadata databases to a ZFS storage appliance?

Answer: Direct NFS operates directly within the Oracle Database kernel space, completely bypassing operating system network processing layers. This architecture dramatically minimizes CPU utilization on the Exadata compute nodes. It also offers advanced load balancing and high availability by distributing I/O across up to several thousand concurrent server threads automatically.

Q3: An RMAN backup to Data Domain using DD Boost is taking significantly longer than expected. What parameters and logs should you investigate?

Answer: I would start by reviewing the $ORACLE_HOME/rdbms/log/sbtio.log on the database server to check for connection timeouts or API handshake delays with the Data Domain plugin. On the target side, I would check ddboost show stats to verify if client-side deduplication is functioning as expected. Finally, I would verify that the RMAN channel allocations allocation script defines optimal parallelization values along with large block-size transfer parameters like BLKSIZE=1048576.

Question : Backup architecture and troubleshooting approach

1. Architectural Blueprint & Core Components

Backing up an Oracle Exadata Cloud at Customer (ExaCC) X9M system to an Oracle ZFS Storage Appliance (ZFSSA) combines engineered database infrastructure with high-throughput network-attached storage.

+----------------------------------------------------------------------------------------+

|                                    ExaCC X9M Rack                                      |
|                                                                                        |
|  +------------------------+  RoCE  +------------------------+                           |
|  |   Database Nodes (2)   |========|  Storage Servers (12)  |                           |
|  | (3rd Gen Intel Xeon,   | (100G) |   (Extreme Flash /     |                           |
|  |  Direct NFS Client)    |        |    High Capacity)      |                           |
|  +------------------------+        +------------------------+                           |
+--------------||-------------------------------------------------------------------------+
               || 
               || (Dual 100GbE / QDR InfiniBand LACP Bond) via Backup Network (bondeth1)
               ||
+--------------||-------------------------------------------------------------------------+

|              \/                                                                         |
|  +------------------------+                +-----------------------+                    |
|  | Customer Switch Fabric |==============  | Oracle ZFS Appliance  |                    |
|  |  (VLAN Segmentation)   |  100GbE/NFS    | (Storage Pools, dNFS) |                    |
|  +------------------------+                +-----------------------+                    |
|                                                                                         |
|                                  Enterprise Manager                                     |
|                               +-----------------------+                                 |
|                               |       OEM 13c         |                                 |
|                               | (Database Lifecycle)  |                                 |
|                               +-----------------------+                                 |
|                                                                                         |
|                            On-Premises Data Center Network                              |
+-----------------------------------------------------------------------------------------+

ExaCC X9M Compute Nodes: Run 3rd Generation Intel Xeon Scalable Processors. They act as the RMAN client utilizing [Oracle Direct NFS (dNFS)](1.1.3, 1.2.9) to bypass the OS kernel, maximizing I/O performance directly into user space. [1, 2]

Backup Network Line (bondeth1): Isolates high-throughput database backup traffic from critical client application paths.

Oracle ZFS Storage Appliance: Serves as the backup target. It leverages a flash-first Hybrid Storage Pool model (combining SSDs for write accelerators/caches and high-capacity HDDs).

Oracle Enterprise Manager (OEM) 13c: Acts as the centralized management pane used to schedule, monitor, and report on RMAN execution across the ExaCC fleet.

2. Concrete Example & Step-by-Step RMAN Configuration

This scenario configures a multi-channel RMAN backup utilizing dNFS over a ZFS network share targeting a 50TB Data Warehouse.

Step 1: Configure dNFS on ExaCC Compute Nodes

Create or update the configuration file on all ExaCC nodes to map out the ZFS storage controllers over the backup network.

bash

# Location: $ORACLE_HOME/dbs/oranfstab

server: zfs_pool1
local: 192.168.12.10 path 192.168.12.100  # Node 1 Backup IP -> ZFS Controller A
local: 192.168.12.11 path 192.168.12.101  # Node 2 Backup IP -> ZFS Controller B
export: /export/exacc_backup mount: /mnt/zfs_backup

Step 2: Validate the NFS Mount Permissions

Ensure permissions match the ExaCC deployment variables (oracle:dba).

bash

mount -t nfs -o rw,bg,hard,rsize=1048576,wsize=1048576,vers=3,nointr,proto=tcp 192.168.12.100:/export/exacc_backup /mnt/zfs_backup
chown -R oracle:dba /mnt/zfs_backup

Step 3: Configure Optimized RMAN Target Parameters

Execute inside the target environment to tune block size, multiplexing parameters, and parallelism features.

sql

-- Allocate channels distributed across both RAC nodes via the backup network
RMAN> 
CONFIGURE DEVICE TYPE DISK PARALLELISM 16 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/mnt/zfs_backup/%d_%U' CONNECT 'sys/Password@ExaCC_Node1_Backup';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/mnt/zfs_backup/%d_%U' CONNECT 'sys/Password@ExaCC_Node2_Backup';
-- Repeat allocation mapping up to 16 channels to balance the network controller
CONFIGURE MAXSETSIZE TO Unlimited;

3. Comprehensive Performance Analysis Strategy

Primary Tool Utilized: OEM 13c (Cloud Control) & AWR

OEM Interface Navigation: Target -> Database -> Availability -> Backup & Recovery -> Backup Report.

Analysis Approach: Pinpoint throughput bottlenecks using the Performance Hub inside OEM. We compare historical database wait events against system I/O metrics during the backup window.

Step-by-Step Diagnostic Method Using Scripts

1. Check dNFS Mounting State & Throughput Activity:
Verify if the database kernel is processing I/O through dNFS or dropping back to kernel NFS.

sql

SELECT svrname, export, path, status FROM v$dnfs_servers;
SELECT * FROM v$dnfs_stats;

2. Measure Block Execution Read/Write Speed:

Query RMAN session long operations directly to map execution velocities.

sql

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",
       ROUND(60*(TOTALWORK-SOFAR)/NULLIF(SOFAR,0),2) "MINS_REMAINING"
FROM v$session_longops 
WHERE OPNAME LIKE 'RMAN%' AND TOTALWORK > 0 AND SOFAR != TOTALWORK;

3. Identify RMAN Step Bottlenecks (Synchronous vs Asynchronous I/O):

Look for high waiting periods in the v$backup_sync_io and v$backup_async_io performance grids.

sql

SELECT device_type, type, filename, buffer_size, buffer_count, io_count,
       ready_na, short_waits, long_waits, effective_bytes_per_second
FROM v$backup_async_io;

Analysis Pointer: If long_waits divided by io_count is higher than 0.05, the backup storage target (ZFS) is bottlenecked on write ingestion.

4. Controlled Test Cases

Test Case 1: Baseline Network Validation (Raw Throughput)

Objective: Test network link performance outside Oracle architectures.

Execution Step:

bash

dd if=/dev/zero of=/mnt/zfs_backup/test_file bsf=1M count=50000 oflag=direct

Expected Metric: Rate should be near line limit (e.g., > 1.2 GB/sec on a 10GbE link or > 9 GB/sec on a 100GbE connection).

Test Case 2: RMAN Channel Scalability Test

Objective: Find the optimal multi-channel curve before performance degradation occurs.

Execution Step: Execute sequential database backups incrementally increasing parallelism options.

bash

# Test with 4, 8, 12, and 16 channels respectively
RMAN> BACKUP VALIDATE DATABASE SECTION SIZE 32G;

Expected Metric: Track overall compression rate and read speeds in v$rman_backup_job_details. Stop scaling when scaling channels stops adding throughput.

5. Troubleshooting Workflows & Log Analysis

Diagnostic Blueprint

[Backup Failure or Slowdown Identified]
                 |
                 v
    Review OCI Console / OEM Alert History 
                 |
                 +-----------------------------------+

                 |                                   |
                 v                                   v
      [Process-Level Failures]             [Performance Slowdown]

                 |                                   |
    Check /var/opt/oracle/log/dcs/       Query v$backup_async_io
    Check dcs-agent.log                  Check ZFS Analytics UI

                 |                                   |
                 v                                   v
    Look for TDE / ORA- errors            Identify Disk/Network limits

Vital Log Frameworks & File Directories

ExaCC Cloud Orchestration Agent Log: /var/opt/oracle/log/dcs/dcs-agent.log (Identifies OCI infrastructure orchestration communication faults).

Database Alert Log: /u02/app/oracle/diag/rdbms/<db_name>/<instance_id>/trace/alert_<instance_id>.log (Tracks instances dropping offline, archive log issues, or local system state faults).

RMAN Execution Output: Captured within OEM Job logs or specified when calling execution profiles manually.

ZFS Appliance Log Paths: Accessible through the ZFS Browser User Interface (BUI) under Maintenance -> Logs -> System Log.

Troubleshooting Steps for Common Failures

Symptom: RMAN Backups Hang Indefinitely

Approach: Check for stuck archiver processes due to a full Flash Recovery Area (FRA).
Resolution Query: Check select log_mode from v$database; and space allocations via v$recovery_file_dest. Fix missing archive logs or free up space.

Symptom: ORA-27054: NFS file system not mounted with correct options

Approach: Database checks formatting parameters strictly when writing backups.
Resolution: Ensure mount commands utilize vers=3, proto=tcp, and optimal payload values (rsize=1048576, wsize=1048576).

Symptom: Backup Performance Drops Intraday

Approach: Check the ZFS appliance via its BUI Analytics section (Analytics -> Open Worksheet). Track CPU usage, network interface saturation, or disk utilization.
Resolution: Increase the maximum number of NFS server threads on the ZFS appliance from the default 500 up to 1000 or 2000 (Configuration -> Services -> NFS -> Number of threads).

6. Mock Interview Questions and Answers

Q1: How do you isolate heavy backup traffic on an ExaCC X9M platform to prevent degrading online customer transactions?

Answer: On Exadata Cloud at Customer architectures, network segregation is managed via physical and virtual boundary interfaces. Online client transactions route through the client access network interfaces. Backup traffic is completely offloaded to the separate bondeth1 interface (Backup Network). This isolation prevents high packet volume or bandwidth saturation from increasing query latencies during backup windows.

Q2: Why is Oracle Direct NFS (dNFS) preferred over standard Kernel-level NFS when backing up an Exadata platform to a ZFS Storage Appliance?

Answer: Direct NFS (dNFS) bypasses the operating system's kernel cache layer, writing buffers straight from the Oracle database application memory space to network buffers. This dramatically cuts down CPU context-switching overhead. Additionally, dNFS establishes separate TCP connections per database process, allowing load balancing, lower processing latency, and automated network path failover directly at the kernel layer.

Q3: During a backup execution review, you notice v$backup_async_io displays a high count of long_waits relative to total IO operations. What does this mean, and how do you resolve it?

Answer: A high proportion of long_waits means that RMAN is filling memory buffers faster than the ZFS backup target can write them to disk, turning the storage array into an I/O bottleneck. To resolve this, I would check the ZFS Appliance configuration to make sure it matches best practices: verify that the share's record size matches the database block size (typically 128K for backups sets), check that synchronous write settings are optimized, and increase the ZFS NFS daemon thread count from 500 to 1000 or more to handle higher parallel write workloads.

Question : Troubleshooting backup running zfs for Exacc

This technical architecture and interview-focused guide explores optimizing and troubleshooting Oracle Recovery Manager (RMAN) backups originating from an Oracle Exadata Cloud@Customer (ExaCC) X9 system directed toward an external Oracle ZFS Storage Appliance.

Core Architecture Overview

In an ExaCC X9 configuration, the Database Compute Nodes (DomU) run the RMAN backup processes. The data blocks are read from the high-speed Exadata Storage Servers (Cells) via the internal 100 Gbps RoCE (RDMA over Converged Ethernet) network. The data is subsequently pushed out to the ZFS Storage Appliance over high-speed networks, ideally leveraging Oracle Direct NFS (dNFS) to bypass OS kernel buffer bottlenecks.

Concrete Performance Example

Scenario Architecture

Source: ExaCC X9 Quarter Rack (2x Database Compute Nodes, 3x Storage Cells).

Target: Oracle ZFS Storage Appliance ZS9-2 connected via a dedicated network.

Database Size: 20 TB.

Baseline vs. Optimized Metrics Table

Metric	Baseline Performance (Misconfigured)	Optimized Performance (Best Practices)	Bottleneck Cleared
Backup Throughput	~450 MB/s	~6.2 GB/s	Kernel NFS overhead & channel saturation
Total Duration	~12.8 Hours	~55 Minutes	Network pipe and CPU starvation
RMAN Configuration	2 channels, Standard OS NFS mount	16 channels, Oracle Direct NFS (dNFS)	Async I/O queue constraints
ZFS Pool Config	Single Share, No jumbo frames	Multiple Shares, 9000 MTU Enabled	TCP window limitations and ZFS lock contention

Analysis Tool: Oracle ZFS DTrace Analytics

The premier tool for analyzing backup performance on this stack is the native ZFS Storage Appliance DTrace Analytics UI/CLI. It provides live, production-safe profiling of protocols, cache, and disk subsystems without overhead.

Step-by-Step Profiling Procedure

Access the Interface: Log in to the Oracle ZFS BUI (Browser User Interface) and navigate to Maintenance > Analytics.

Establish the Network Baseline: Add a breakdown worksheet tracking Network: Device bytes broken down by device. Ensure traffic flows evenly across all aggregated interfaces (LACP/IPMP).

Isolate Protocol Throughput: Open a worksheet for Protocol: NFSv4 bytes broken down by client. Identify if all ExaCC X9 compute nodes are distributing the backup load evenly.

Inspect Disks and Latency: Open Disk: I/O bytes broken down by operation and Protocol: NFSv4 average latency.

If latency stays < 5ms but throughput is low, the bottleneck is upstream on the ExaCC compute nodes or network.
If latency spikes above 20ms, the ZFS disk pool is saturated.

Performance Test Cases (Benchmark Matrix)

To systematically evaluate the infrastructure, execute these four diagnostic validation test cases.

+-------------------------------------------------------------+

|                     Test Execution flow                     |
+-------------------------------------------------------------+
                               |
            [Test Case 1: Validation of Bare Network]
                               |
         [Test Case 2: Validation of Raw Target Storage]
                               |
         [Test Case 3: Validation of Source Infrastructure]
                               |
            [Test Case 4: Final Scale Optimization]

Test Case 1: Network Pipe Validation (Bypassing Storage)

Objective: Validate that the network infrastructure can handle wire-speed data throughput.

Execution Steps: Run an iperf3 multi-stream test from the ExaCC nodes to the ZFS storage controllers.

Expected Outcome: Total bandwidth should reach >90% of the physical network capacity (e.g., ~9 Gbps on a 10GbE line, or ~36 Gbps on a 40GbE link) with zero dropped packets.

Test Case 2: Storage Target Raw Write Validation

Objective: Confirm the ZFS Appliance can ingest data fast enough without RMAN overhead.

Execution Steps: Use dd from the ExaCC compute node shell to write a dummy file directly to the dNFS mount point:

bash

dd if=/dev/zero of=/mnt/zfs_backup/test_file.dbf bs=1M count=50000 oflag=direct

Expected Outcome: Write performance should match or closely approximate the target speed specified for your ZFS storage pool configuration.

Test Case 3: Source Reading Performance (Validation of Storage Cells)

Objective: Ensure ExaCC storage cells can read database data blocks at top speeds without backup interference.

Execution Steps: Execute a dummy RMAN validation pass that reads the database blocks without pushing them across the network:

ora

RMAN> BACKUP VALIDATE DATABASE ALL;

Expected Outcome: High read throughput visible on Exadata cell nodes via cellcli -e "LIST METRICCURRENT WHERE name LIKE 'CL_BY_AND_WI_...'".

Test Case 4: Scale and Concurrency Stress Test

Objective: Identify the peak saturation curve of the combined system.

Execution Steps: Scale up RMAN channels from 4 to 8, 12, and 16 while actively monitoring CPU utilization, wait events, and ZFS pool response times.

Troubleshooting Steps for Performance Degradation

If backups begin slowing down or stalling, follow this precise troubleshooting matrix:

[Isolate Performance Drop]
   |
   +---> Check Oracle RMAN Wait Events (v$session_wait)

   |        |-- 'net timeout' / 'NFS server not responding' -> Check Network/MTU
   |        |-- 'direct path read' -> Slow storage cells
   |        +-- 'backup file write' -> Network queue or ZFS target saturation
   |
   +---> Verify Mount Layer Protocol
   |        +-- Query v$dnfs_servers -> Ensure Direct NFS is active and bypassing OS kernel
   |
   +---> Evaluate Storage Layer Health
            +-- Check ZFS UI Alerts / 'zpool status -x' -> Review disk or controller health

Query RMAN Wait Events: Run this query on the ExaCC node during a slow backup:

sql

SELECT event, total_waits, time_waited FROM v$session_wait WHERE sid IN (SELECT sid FROM v$process WHERE program LIKE '%rman%');

If the dominant wait event is backup file write, the bottleneck resides in the network transport layer or the ZFS appliance target.
If the dominant wait event is direct path read, the bottleneck is on the Exadata storage cells reading the source files.

Verify Mount Layer Protocols: Inspect the Oracle alert log or run cat /etc/mtab to check your NFS parameters. Ensure rsize and wsize are explicitly set to 1048576 (1MB). Check v$dnfs_servers to confirm dNFS is successfully intercepting the I/O path.

Investigate Network Frame Drops: Run ifconfig or ip -s link on both the ExaCC host and ZFS ports. Check if the drop counters or error frames are ticking upward. A mismatch in MTU configuration (e.g., an ExaCC node sending 9000-byte jumbo frames to a switch configured for a 1500-byte standard frame) will trigger massive IP fragmentation and drop throughput by up to 80%.

Evaluate Storage Layer Health: Execute zpool status -x on the ZFS appliance controller. Ensure that no background disk reconstruction processes or scrub operations are consuming backend drive bandwidth.

1. Architectural Architecture Overview

In an Exadata Cloud@Customer (ExaCC) X9M environment, database compute nodes utilize Oracle Direct NFS (dNFS) or the Dell PowerProtect DD Boost library via the SBT interface to run parallelized RMAN backups to external appliances.

+-----------------------------------------------------------------------------+

|                           ExaCC X9M Compute Nodes                           |
|      (Direct NFS Client / DD Boost SBT Plugin / OEM Management Agent)       |
+-----------------------------------------------------------------------------+
          | (RoCE Network Fabric - 100 Gbps / InfiniBand Multi-Rail)
          |
          +-----------------------+-----------------------+

          |                                               |
          v                                               v
+-------------------------------+               +-------------------------------+

|  Oracle ZFS Storage Appliance |               |      Dell Data Domain         |
|  - Shares mounted via dNFS    |               |  - DD Boost Protocol          |
|  - Highly parallelized threads|               |  - Client-side deduplication  |
+-------------------------------+               +-------------------------------+

2. Tool Used for Performance Analysis

The primary tool used to diagnose performance and locate bottlenecks on Exadata platforms is RMAN Validation alongside the Operating System Utility iostat and Oracle Enterprise Manager (OEM) Metric History.

Steps for Analysis

Isolate the Bottleneck: Run a standard RMAN validation to determine if read performance from Exadata storage cells is the issue, or if write performance to the backup target is the culprit.

Examine Target Metrics: Check system execution stats using low-level target performance monitoring utilities.

Review OS Activity: Monitor CPU wait times, active network interfaces, and disk serialization across all ExaCC database nodes.

3. Real-World Test Cases

Test Case 1: Baselines and Read Validation (Oracle Storage Cells)

Objective: Verify Exadata Storage Cell read throughput capacity without writing data to the target appliance.

Execution Steps:

sql

-- Run an RMAN backup validation to verify read speeds
RMAN> RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
  BACKUP VALIDATE DATABASE;
}

Expected Result: Throughput matches near-peak performance capabilities over the storage network without network dropouts.

Test Case 2: Target Performance Validation (ZFS File System over dNFS)

Objective: Check if dNFS channels are distributing the workload properly across ZFS controllers.

Execution Steps: Verify operational statistics using the native file system tools on the compute layer.

bash

# Check if dNFS is active and utilizing channels
cat /proc/fs/oracle_dnfs/channels

Expected Result: Multiple concurrent TCP paths map successfully to target storage system interfaces.

Test Case 3: Target Performance Validation (Data Domain via DD Boost)

Objective: Validate that client-side deduplication is offloading processing cleanly without saturating database server CPUs.

Execution Steps: Run live statistics from the Data Domain command interface while an active RMAN backup streams data.

bash

# Execute on Data Domain OS to check stream behavior
ddboost show connections detailed
ddboost show stats interval 5

Expected Result: High deduplication ratios limit actual wire transfers while keeping network payload sizes small.

4. Log Details & System Locations

Component	Target File / Location	Diagnostic Purpose
Oracle Database Alert Log	`/opt/oracle/diag/rdbms/<db>/<instance>/trace/alert_<instance>.log`	Check for checkpoint lags, global networktimeouts, or storage cell errors.
RMAN SBT Interface Log	`$ORACLE_HOME/rdbms/log/sbtio.log` (or within the `user_dump_dest` trace directory)	Pinpoint handshakes, credentials, and API communication failures between RMAN and DD Boost / ZFS.
Cloud Framework Automation	`/var/opt/oracle/log/dtrs/jobs/<job_id>.log` `/var/opt/oracle/log/<dbname>/dtrs/rman/bkup/`	Trace backup control workflows executed directly via the OCI console or OEM agents.
Data Domain Engine Logs	Log viewable via: `log watch debug/ddfs.info` on the Data Domain OS	Investigate target side dropouts, write errors, or storage capacity constraints.

5. Troubleshooting Approach (Step-by-Step)

Check System Logs: Review the RMAN task history output using internal automation tools or command structures:

bash

dbaascli database backup --dbname <dbname> --showHistory

Investigate SBT Failures: If the task errors out immediately during initialization, review /opt/oracle/diag/rdbms/<db>/<inst>/trace/sbtio.log.

Verify Network Routing: Run ping and trace validations over the distinct backup network interface card to ensure data paths do not route over public or client networks.

Tune Parameters: Optimize block sizing and buffering if slow throughput issues persist:

Increase _backup_disk_bufcnt=64 and _backup_disk_bufsz=4194304 to handle multi-threaded RMAN writes smoothly.
Double the concurrent server threads inside the ZFS configuration interface from 500 to 1000.

Oracle Exadata Cloud@Customer (ExaCC) X9M leverages high-bandwidth 50 Gbps client/backup networks or RoCE (RDMA over Converged Ethernet) to stream RMAN backups. When using an on-premises Oracle ZFS Storage Appliance (ZFSSA) as the target, Direct NFS (dNFS) is implemented to bypass OS-level kernel overhead and maximize sequential streaming throughput.

Core Analysis Tools

The primary utility for evaluating and diagnosing backup performance in this environment is the Oracle ZFS Analytics Engine (BUI Analytics), supplemented by standard database infrastructure tools.

1. ZFS Storage Appliance Analytics (BUI)

What it details: Real-time breakdown of protocols (NFSv4/dNFS), per-network-interface throughput, disk spindle/SSD I/O latency, and cache hit ratios.

Why it is used: Isolates whether a bottleneck resides on the Exadata compute side (network/database engine) or the ZFS storage side (disk backend/controller pool).

2. Oracle RMAN Performance Views

What it details: Monitored via V$RMAN_BACKUP_JOB_DETAILS and V$BACKUP_ASYNC_IO / V$BACKUP_SYNC_IO.

Why it is used: Pinpoints asynchronous I/O bottlenecks and quantifies the effective megabytes-per-second (MB/s) processing rate for each allocated RMAN channel.

Performance Test Cases & Execution Steps

Evaluating performance requires testing across network, file system, and database tiers.

Test Case 1: ZFS Backend and dNFS Connection Validation

Objective: Ensure the dNFS path utilizes full available bandwidth without dropping packets.

Step 1: Verify the client configuration file oranfstab is properly initialized inside the ExaCC DB node's $ORACLE_HOME/dbs/ directory.

Step 2: Trigger a multi-channel RMAN validation command to stress-test the dNFS mounts without generating disk writes:

sql

RMAN> RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/mnt/zfs_share/%U';
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT '/mnt/zfs_share/%U';
  VALIDATE DATABASE;
}

Expected Result: High read throughput across Exadata cells and uniform traffic distribution across all ZFS network interfaces.

Test Case 2: Incremental Backup Performance (Level 1)

Objective: Verify Block Change Tracking (BCT) is optimized and ZFS handles modified blocks sequentially.

Step 1: Confirm BCT is enabled on the database side:

sql

SELECT status, filename FROM v$block_change_tracking;

Step 2: Execute a standard Level 1 incremental backup.

Step 3: Monitor the ZFS Analytics BUI dashboard filtered by "NFS operations by Type" and "Disk I/O bytes by operation".

Expected Result: Level 1 backup times should drop to a fraction of a full backup time. ZFS write bias must reflect sequential streaming profiles.

Log Locations & Systematic Review Approach

When troubleshooting sub-optimal throughput or total backup failures, look for logs across these environments:

Infrastructure Layer	Primary Log / Directory Location	What to Look For
ExaCC OCI Agent	`/var/log/oracle/dbcsagent/dbcsagent.log`	Failures in cloud-orchestrated backup workflows or API connection drops.
Oracle Database	Database Alert Log (`alert_<ORACLE_SID>.log`)	`ORA-19870` errors, archiving lockups, or TDE wallet connection drops.
Network Client	`/opt/oracle/dcs/log/`	System-level network drops or routing mismatches on the 50 Gbps backup vNIC interface.
ZFS Appliance	Appliance System Logs (`/ak/v1/logs/system`)	Controller failovers, pooled storage errors, or synchronous write delays.

Systematic Review Checklist:

Identify the Point of Failure: Check the cloud control plane to determine if the job hung in a Backup in Progress state or failed instantly.

Review DB/RMAN Log Output: Check V$RMAN_OUTPUT or the standard trace logs for I/O timeout exceptions.

Audit dNFS Telemetry: Run cat /proc/fs/oranfstab and query V$DNFS_SERVERS to ensure paths have not defaulted back to kernel-space NFS.

Question 1

We are executing an RMAN backup from an ExaCC X9M database cluster to an on-premises ZFS Storage Appliance. The 50 Gbps backup network shows less than 15% utilization, and backups are missing their scheduled window. How do you isolate and resolve this performance issue?

Answer

"I would approach this systematically from the Database Layer, moving down to the Network Layer, and finally inspecting the Storage Layer:

Database Layer: I will first query V$BACKUP_ASYNC_IO to check the SHORT_WAIT_TIME_COUNT and LONG_WAIT_TIME_COUNT. If these are high, the bottlenecks are synchronous read stalls on the Exadata storage cells. I will also check the channel allocation. For ZFS, it is best practice to parallelize using multiple channels (e.g., matching the number of CPU cores or ZFS target threads).

Network Layer: I will verify that Direct NFS (dNFS) is actively used by checking V$DNFS_SERVERS. If dNFS is misconfigured, Oracle falls back to standard Kernel NFS, which increases OS context-switching overhead and throttles performance. I will also check if the MTU is optimized at 9000 (Jumbo Frames) across the ExaCC backup network interface and ZFS ports to prevent packet fragmentation.

Storage Layer: On the ZFS Appliance side, I will launch the ZFS Analytics BUI. I will monitor the metric 'NFS bytes/sec broken down by client' and 'Disk I/O breakdowns'. A common issue on the ZFS side is incorrect share properties. For database backups, the share record size must be explicitly set to 128K, and the Synchronous Write Bias must be adjusted to Throughput rather than Latency. If deduplication is enabled without sufficient controller DRAM, it will also severely bottleneck write performance."

Q1: An RMAN backup from an ExaCC X9 system to a ZFS appliance via NFS is running remarkably slow. The network pipe is clear, and the ZFS CPU is under 15%. Where do you look first?

Answer: Look at Oracle Direct NFS (dNFS) configuration and RMAN allocation channels. By default, standard OS kernel NFS operations pass through a single kernel process lock, which chokes high-velocity multi-threaded writes. [

To fix this, enable dNFS within the Oracle home by linking the dNFS library (odm) so Oracle bypasses the OS kernel layer completely.

Concurrently, evaluate the RMAN script: if a 20 TB database is being written over only 2 channels, the database is starving the network pipes. Allocate additional channels (e.g., 8 to 16 channels) to fully utilize the parallel network interfaces.

Q2: What exact ZFS DTrace metrics would you use to distinguish an ExaCC database-side read bottleneck from a ZFS-side write bottleneck?

Answer: Open DTrace Analytics and track Protocol: NFS bytes broken down by operation paired with Protocol: NFS average latency. [

If the average latency for NFS writes is low (<2ms) but the total NFS bytes/sec throughput is small, it indicates that the ZFS appliance is processing data instantly but waiting on the client. The bottleneck is on the ExaCC side (either source cell reads or host CPU throttling).

Conversely, if the write latency spikes to >20ms while Disk: I/O operations matches the physical limits of the array, the bottleneck is definitively the ZFS write tier. [

Q3: Why is setting the correct MTU vital when hooking up ExaCC X9 to external ZFS storage units, and how do you confirm it?

Answer: ExaCC infrastructures rely heavily on high-throughput transfers. If Jumbo Frames (MTU 9000) are active on the ExaCC compute nodes and the ZFS appliance, but an intermediate network switch is set to a standard MTU 1500, packets will drop or fragment at the network interface layer. This dramatically degrades throughput and creates excessive net timeout error alerts in RMAN.

To confirm path MTU compliance, execute a non-fragmentation ping test from the ExaCC node shell directly to the ZFS target IP:

bash

ping -I bondeth0 -M do -s 8972 <ZFS_IP>

If it returns an error stating the packet is too large, the network path is not completely configured for end-to-end Jumbo Frames.

Q4: How does RMAN Backup Optimization interact with ZFS deduplication/compression, and what is your design recommendation?

Answer: If you use RMAN encrypted backups (which is mandatory for cloud backups or compliance regulations) or RMAN native binary compression, the data blocks become highly randomized. This randomization reduces the efficiency of downstream ZFS inline deduplication and appliance-level compression.

Recommendation: Offload your compression workloads logically. If compute power on the ExaCC nodes is abundant, use Oracle Advanced Compression options directly inside RMAN and turn off deduplication on the ZFS storage pool to save appliance CPU cycles. If saving database compute cycles is your primary goal, run uncompressed RMAN backups and let the ZFS hardware handle compression at the storage target layer.

Question : How connection pooling work in exacc

Connection pooling on Exadata X9-M optimizes database performance by reusing established database sessions, reducing CPU overhead, and minimizing latch contention. It improves throughput for high-concurrency OLTP workloads running on Exadata storage and persistent memory.

How Connection Pooling Works

Reduces Overhead: Bypasses the high cost of creating new TCP/IP connections and spawning dedicated server processes.

Manages Resources: Limits the total number of concurrent active connections to prevent database memory exhaustion.

Queue Management: Holds incoming requests in a queue until a pooled connection becomes free.

Practical Example

Scenario: A web application serving 1,000 concurrent users.

Without Pooling: Each request opens a new database connection, causing connection spikes, high process allocation overhead, and potential ORA-12519 errors.

With Pooling: A pool size of 50 persistent connections handles all 1,000 users seamlessly by borrowing and returning connections instantly.

Tools Used for Analysis

Oracle Enterprise Manager (OEM): Monitors active sessions and pool efficiency.

Database Resident Connection Pool (DRCP): Built-in tool for connection pooling inside the database tier.

AWR (Automatic Workload Repository) Reports: Analyzes wait events like sql*net more data from client or enqueue.

OS Watcher / Exachk: Checks Exadata hardware metrics and network latency.

Analysis Steps

Generate an AWR report during peak workload hours.

Check the Load Profile for "Executions" versus "Session Count".

Look at the Top Timed Events for network or connection-related waits.

Open OEM and navigate to Targets > Database > Performance.

Evaluate the "Response Time Breakdown" and active connection metrics.

Test Cases

Test Case 1 (Baseline): Simulate 500 concurrent requests without a connection pool; measure latency and CPU usage.

Test Case 2 (Stressed Pool): Set the pool size artificially low (e.g., 5 connections) for 500 users; observe queue time and timeout errors.

Test Case 3 (Optimal Pool): Scale the pool to the recommended size (e.g., 50 connections) under the same 500-user load; record throughput improvement.

Troubleshooting Steps

Check listener logs for connection refused or timeout errors.

Verify PROCESSES and SESSIONS parameters in the database initialization file (SPFILE).

Inspect firewall or network configurations between the application tier and Exadata database nodes.

Adjust session_cached_cursors if parsing overhead remains high despite pooling.

Interview Questions & Answers

Q: Why is connection pooling crucial on Exadata X9?

A: It prevents process-creation bottlenecks, allowing high-speed RDMA and persistent memory features of Exadata to process actual SQL execution rather than wasting cycles on session handshakes.

Q: How do you choose the right pool size?

A: Use the formula: Pool Size = (Core Count * 2) + Effective Spindle/Flash Count or test empirically using load testing tools while monitoring CPU queue times.

Connection pooling in Exadata X9M dramatically boosts performance by reusing database connections, cutting down expensive connection setup costs, saving CPU cycles, and reducing memory overhead on the database server.

Key Concepts

Connection Reuse: Apps borrow open sessions from a pool instead of building new network and database handshakes each time.

Reduced Latency: Lowers response times for high-volume, short-lived database transactions.

Exadata Optimization: Works alongside Exadata Smart Scan and RDMA to maximize hardware throughput without connection bottlenecks.

Code Example (Java / JDBC)

java

// Using Oracle Universal Connection Pool (UCP)
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//exadata-scan:1521/pdb1");
pds.setUser("app_user");
pds.setPassword("secure_password");
pds.setInitialPoolSize(10);
pds.setMaxPoolSize(100);

// Borrow and use connection
try (Connection conn = pds.getConnection();
     PreparedStatement stmt = conn.prepareStatement("SELECT * FROM dual")) {
    ResultSet rs = stmt.executeQuery();
}

Analysis Tools

Oracle Enterprise Manager (OEM): Monitors active connections, wait events, and pool efficiency.

AWR (Automatic Workload Repository): Tracks enqueue waits, Logon rates, and session counts.

ASH (Active Session History): Identifies real-time bottlenecks like buffer busy waits or connection wait spikes.

Analysis Steps

Access OEM or run an AWR report for peak load hours.

Check the "Load Profile" section for Logons/sec. High numbers mean poor pooling.

Inspect Top 5 Timed Foreground Events for connection-related waits.

Tune pool sizes based on active thread counts and Exadata core capacity.

Test Cases

Load Burst Test: Simulate 500 concurrent users logging in simultaneously; measure response time with vs. without pooling.

Exhaustion Test: Max out the pool limit (maxPoolSize) and verify graceful queueing or clean timeout exceptions.

Failover Test: Kill a database node and ensure the pool recovers connections to surviving Exadata nodes seamlessly.

Troubleshooting Steps

Check Leaks: Look for unclosed Connection or ResultSet objects in application code.

Analyze High Wait Times: If resched or SQL*Net more data from client spikes, check network latency or small pool limits.

Examine PROCESSES / SESSIONS: Ensure db_max_processes in Oracle matches or exceeds the cumulative pool sizes across all application servers.

Interview Q&A

Q: Why is connection pooling crucial on Exadata X9M?

A: Exadata processes data at extreme speeds. Without pooling, the CPU gets choked by frequent process creation (fork/exec overhead) rather than doing actual data processing.

Q: How do you size a connection pool for Exadata?

A: Use the formula: Pool Size = ((Core Count * 2) + Effective Spindle/Flash concurrency), adjusted based on application concurrency demands and PROCESSES limits.

Question : What is mon patching in exacc

"Mon patching" in Exadata Cloud at Customer (ExaC@C or ExaCC) typically refers to monthly critical infrastructure updates or monthly maintenance operations managed or scheduled by Oracle and operated via the Oracle Cloud Infrastructure Documentation. These monthly cycles address severe security vulnerabilities (CVSS > 7) across underlying cloud infrastructure components.

Key Aspects of ExaCC Maintenance

Oracle-Managed Infrastructure: Oracle handles lower-level elements including physical compute nodes (Dom0), Exadata storage cells, network switches, PDUs, and ILOM firmware.

Scheduling & Window: Monthly security updates come with advance notification windows (typically a 21-day rescheduling window for Cloud@Customer before auto-application).

Rolling vs. Non-Rolling: Infrastructure updates default to a rolling approach (one server/node at a time to maintain uptime), though non-rolling simultaneous updates are available.

Customer-Managed Layers: You remain responsible for triggering and managing upper-stack elements like VM clusters, Grid Infrastructure, and Database Home quarterly patch updates.

Maintenance (MON) patching in Oracle Exadata Database Service on Cloud@Customer (ExaCC) refers to infrastructure and software lifecycle updates (Operating System, Grid Infrastructure, and Database/DOMU) applied via cloud automation. Updates run in a rolling manner to prevent downtime. You can execute these via the OCI Console or command-line utilities like exadbcpatchmulti.

Details and Workflow

Scope: Managed components include Guest OS (domU), Grid Infrastructure (GI), and Database Homes. Physical hypervisors (dom0) and storage cells are generally scheduled and patched directly by Oracle or via coordinated infrastructure bundles.

Method: Rolling execution drains node services, relocates ASM resources/database instances temporarily if required, patches sequentially, and verifies health before moving to the next node.

Execution sequence: Precheck → Apply/Update → Post-validation.

Example Operation (GI / OS Update via OCI CLI)

List available updates for your VM cluster to obtain the update_id:

bash

oci db vm-cluster update list --vm-cluster-id <ocid>

Run the prerequisite precheck:

bash

oci db vm-cluster update patch --update-action PRECHECK --update-id <patch_ocid> --vm-cluster-id <ocid>

Apply the rolling update:

bash

oci db vm-cluster update patch --update-action APPLY --update-id <patch_ocid> --vm-cluster-id <ocid>

Test Cases

Prerequisite Validation Test: Confirm that Precheck returns a SUCCEEDED status with no blocking custom RPM conflicts or low disk space flags in /u01.

Rolling Continuity Test: Verify active application transaction continuity and redundant Clusterware/ASM resource failover capability while Node 1 is taken offline for the patch sequence. [

Post-Patch Verification Test: Confirm node membership in GI (olsnodes), status of ASM diskgroups, and successful execution of datapatch on target databases.

Troubleshooting Steps

Review Job Logs: Inspect work requests and detailed logs inside /var/log/ or cloud tooling log directories on the compute node.

Resolve Conflicting RPMs: If prechecks fail due to unmanaged customer-installed packages on the guest OS, back up and remove or upgrade conflicting RPMs.

Cluster Node Discrepancies: If a multi-node rolling patch fails midway on a secondary node, check cluster status, rectify the local node issue, and utilize exadbcpatchmulti or console controls to roll back or resume the sequence uniformly.

Database SQL Script Errors: If datapatch fails post-update, manually connect to the database via SQL*Plus and run @?/rdbms/admin/datapatch.sql to resolve registry inconsistencies.

Overview of ExaCC DOMU Patching

What it is: Updating software in the Guest VM (DOMU) cluster.

Components: Grid Infrastructure home, Database home, and OS packages.

Tool used: ExaCC Control Plane (OCI console/CLI) or orchestration scripts.

Goal: Zero or low downtime via rolling node-by-node updates.

Interview Q&A: Key Concepts

Q1: What is DOMU patching in ExaCC?

Updating the Guest VM operating system and software.

Runs on individual database server virtual machines.

Managed via cloud control plane orchestration.

Q2: What is the high-level workflow for GI patching in ExaCC?

Download patches using the OCI control plane.

Pre-checks run automatically on the cluster nodes.

Rolling execution updates one node at a time.

Post-checks verify cluster health after completion.

Example Scenario & Step-by-Step Patching

Environment: 2-node ExaCC RAC Cluster (Node1, Node2).

Target: Apply GI/DOMU quarterly update.

Execution Steps

Pre-check: Run cluster verification utility (cluvfy) and oakcli/exadata checks.

Node 1 Drain: Stop services on Node 1 safely.

Apply Patch: Update GI home binaries on Node 1 via opatchauto.

Node 1 Reboot: Restart Node 1 and verify GI status.

Node 2 Rolling Update: Repeat the drain, patch, and reboot process on Node 2.

Test Cases

TC-01: Pre-requisite Validation

Action: Run pre-patch checks.
Expected Result: Zero errors; space and active processes verified.

TC-02: Rolling Service Migration

Action: Evict database services from Node 1.
Expected Result: Services successfully relocate to Node 2 without client application failure.

TC-03: Post-Patch Cluster Verification

Action: Check crsctl check crs on all nodes.
Expected Result: All Oracle High Availability Services online and stable.

Troubleshooting Steps

Symptom: opatchauto fails due to active processes holding locks.

Step 1: Identify locking processes using fuser or lsof.
Step 2: Gracefully stop any rogue local database or agent processes.

Symptom: Node fails to rejoin the Grid Infrastructure cluster after reboot.

Step 1: Check log files in $GRID_HOME/log/[node]/crsd/.
Step 2: Verify network interconnect and ONS/GNS daemon status.
Step 3: Manually start CRS using crsctl start crs.

Question : How would you troubleshoot PL/SQL code in Exacc

"How do you investigate, troubleshoot, and optimize a slow or failing PL/SQL package/procedure in an Oracle Exadata Cloud@Customer (ExaCC) environment? Walk me through your methodology, diagnostic tools, a real-world test case, and resolving both logical and performance bottlenecks."

Direct Answer First

To investigate PL/SQL issues and performance on Oracle Exadata Cloud@Customer (ExaCC), you must utilize a structured 3-tier diagnostics layer: use DBMS_UTILITY and DBMS_ERRLOG to track logical exceptions; implement DBMS_PROFILER or DBMS_HPROF to isolate slow code rows; and leverage Automatic Workload Repository (AWR), Real-Time SQL Monitoring, and Cell Node Smart Scan metrics to tune the underlying Exadata storage layer. [1, 2, 3]

1. Core Troubleshooting & Investigation Steps

Step A: Identify & Isolate the Issue Type

Logical / Functional Failures: The execution terminates with an Oracle error (ORA-xxxxx).

Performance Bottlenecks: The code runs successfully but exceeds its runtime SLA window. [1]

Step B: Trace Code-Level Performance

Run the hierarchical profiler (DBMS_HPROF) or line-level profiler (DBMS_PROFILER) to extract structural run-times.

Pinpoint exactly which line number or sub-program call consumed the most CPU or wait time.

Step C: Analyze Database-Level Execution

Query V$SQL_MONITOR or use SQL Developer's Real-Time SQL Monitoring for PL/SQL calls running longer than 5 seconds.

Generate an AWR (Automatic Workload Repository) Report if the slow performance impacts the global cluster environment [1.25].

Step D: Check Exadata-Specific Offloading

Verify if SQL statements nested inside the PL/SQL engine are leveraging Smart Scans (Storage Cell Offloading).

Check V$SYSSTAT or V$SQL columns (CELL_OFFLOAD_ELIGIBLE_BYTES, IO_CELL_OFFLOAD_RETURNED_BYTES) to ensure heavy full-table scans are occurring on the storage cells rather than flooding the ExaCC compute layer.

2. Technical Test Case & Details

Scenario Description

An inventory update package (PKG_INVENTORY.PROCESS_ORDERS) processing 10 million rows is failing intermittently and executing slow. The package loops through an explicit cursor, issues single-row UPDATE statements, and crashes on mismatched data.

The Bad (Unoptimized) Code Example

sql

CREATE OR REPLACE PACKAGE BODY PKG_INVENTORY AS
  PROCEDURE PROCESS_ORDERS IS
    CURSOR c_orders IS SELECT order_id, qty, status FROM orders WHERE status = 'PENDING';
    v_order c_orders%ROWTYPE;
  BEGIN
    OPEN c_orders;
    LOOP
      FETCH c_orders INTO v_order;
      EXIT WHEN c_orders%NOTFOUND;
      
      -- Problem 1: Context switching (Row-by-Row processing)
      -- Problem 2: No robust error backtrace logic
      UPDATE inventory 
      SET stock_qty = stock_qty - v_order.qty 
      WHERE item_id = v_order.order_id; 
      
    END LOOP;
    CLOSE c_orders;
    COMMIT;
  END PROCESS_ORDERS;
END PKG_INVENTORY;
/

3. Troubleshooting & Tuning Implementation

Troubleshooting Step 1: Capturing Line-Level Errors

Modify the code to output the exact failure line number using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.

sql

EXCEPTION
  WHEN OTHERS THEN
    -- Logs the exact line number where the application crashed
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error Msg: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Error Line Trace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    ROLLBACK;

Troubleshooting Step 2: Running the Profiler to Catch Performance Sinks

Execute the profiler via an anonymous block to capture precise line execution times:

sql

DECLARE
  v_run_number NUMBER;
BEGIN
  DBMS_PROFILER.START_PROFILER('Inventory_Run_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'));
  
  PKG_INVENTORY.PROCESS_ORDERS;
  
  DBMS_PROFILER.STOP_PROFILER;
END;
/

Querying the Profiler Table to discover the worst line:

sql

SELECT line#, total_occur, total_time/1000000000 AS total_time_secs
FROM plsql_profiler_data
WHERE runid = &run_id AND total_time > 0
ORDER BY total_time DESC;

Observation: The query reveals that the row-by-row UPDATE statement inside the loop accounts for 92% of the execution time due to massive PL/SQL-to-SQL context switches.

Troubleshooting Step 3: Checking Exadata Optimization Metrics

Ensure the inner statements are utilizing Exadata capabilities instead of overloading the compute tier:

sql

SELECT sql_text, 
       cell_offload_eligible_bytes / 1024 / 1024 AS offload_mb,
       io_cell_offload_returned_bytes / 1024 / 1024 AS returned_mb
FROM v$sql 
WHERE sql_text LIKE '%UPDATE inventory%';

4. Resolution: Optimized Code Production Deployment

To fix the bottlenecks, apply BULK COLLECT with a FORALL statement to bundle row updates, include a LIMIT clause to protect database memory (PGA), and use SAVE EXCEPTIONS to prevent a single line error from crashing the entire batch.

sql

CREATE OR REPLACE PACKAGE BODY PKG_INVENTORY AS
  PROCEDURE PROCESS_ORDERS IS
    CURSOR c_orders IS SELECT order_id, qty FROM orders WHERE status = 'PENDING';
    
    TYPE t_order_list IS TABLE OF c_orders%ROWTYPE;
    l_orders t_order_list;
    
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381); -- For FORALL SAVE EXCEPTIONS
  BEGIN
    OPEN c_orders;
    LOOP
      -- Fetch in bulk sets of 5000 to manage memory footprint efficiently
      FETCH c_orders BULK COLLECT INTO l_orders LIMIT 5000;
      EXIT WHEN l_orders.COUNT = 0;
      
      BEGIN
        -- Bind the collection in one single engine context switch
        FORALL i IN 1..l_orders.COUNT SAVE EXCEPTIONS
          UPDATE inventory 
          SET stock_qty = stock_qty - l_orders(i).qty 
          WHERE item_id = l_orders(i).order_id;
      EXCEPTION
        WHEN dml_errors THEN
          -- Log individual row exceptions without killing the batch run
          FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 
                                 ' failed with ORA-' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
          END LOOP;
      END;
    END LOOP;
    CLOSE c_orders;
    COMMIT;
  END PROCESS_ORDERS;
END PKG_INVENTORY;
/

5. Interview Quick-Reference Summary Table

Metric / Tool	Diagnostic Value	Exadata / ExaCC Relevance
`DBMS_UTILITY.FORMAT_ERROR_BACKTRACE`	PINPOINTS exact failure line.	Eliminates manual hunting in deep code layers.
`DBMS_PROFILER`	Measures line-by-line runtime.	Identifies expensive functions or cursor loops.
`BULK COLLECT` / `FORALL`	Drastically reduces context switches.	Minimizes CPU overhead on ExaCC Compute nodes.
`V$SQL_MONITOR`	Real-time query metric tracing.	Monitors long-running batch sessions actively.
`CELL_OFFLOAD_ELIGIBLE_BYTES`	Checks Smart Scan statistics.	Confirms Exadata cell nodes are filtering storage I/O.

To investigate PL/SQL code issues and performance on an Oracle Exadata Cloud at Customer (ExaCC) X9 architecture, you must target both structural PL/SQL overhead (context switching, row-by-row processing) and Exadata-specific hardware advantages (Smart Scans, Storage Cell offloading).

Core Analysis Tools

DBMS_PROFILER / DBMS_HPROF (Hierarchical Profiler): Pinpoints line-by-line PL/SQL execution time and subprogram call counts.

Active Session History (ASH): Analyzes real-time or recent transient issues by breaking down PLSQL_ENTRY_OBJECT_ID and PLSQL_SUBPROGRAM_ID via views like V$ACTIVE_SESSION_HISTORY.

Real-Time SQL Monitoring: Tracks high-resource queries inside your PL/SQL blocks when they consume >5 seconds of CPU or run in parallel.

Exadata Storage Metrics: Evaluates wait events such as cell smart table scan to ensure queries within the PL/SQL blocks leverage Exadata Smart Scans rather than forcing full blocks back to the database servers.

Step-by-Step Scenario & Test Case

Scenario

A nightly batch package PKG_ORDER_PROCESSING.PROCESS_DAILY_ORDERS is missing its SLA. It updates historical order balances but runs slowly on ExaCC X9.

Test Case Setup

sql

-- Create an orders table with data skew to simulate an enterprise load
CREATE TABLE orders_mfg (
    order_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    customer_id NUMBER,
    order_date DATE,
    status VARCHAR2(20),
    amount NUMBER
);

-- Target Procedure containing architectural anti-patterns (Row-by-Row processing)
CREATE OR REPLACE PACKAGE PKG_ORDER_PROCESSING AS
    PROCEDURE PROCESS_DAILY_ORDERS;
END PKG_ORDER_PROCESSING;
/

CREATE OR REPLACE PACKAGE BODY PKG_ORDER_PROCESSING AS
    PROCEDURE PROCESS_DAILY_ORDERS IS
    BEGIN
        -- Anti-pattern: Implicit row-by-row cursor loop creating heavy context switching
        FOR r IN (SELECT order_id, amount FROM orders_mfg WHERE status = 'PENDING') LOOP
            UPDATE orders_mfg 
            SET amount = r.amount * 1.05, status = 'PROCESSED'
            WHERE order_id = r.order_id;
        END LOOP;
    END PROCESS_DAILY_ORDERS;
END PKG_ORDER_PROCESSING;
/

Troubleshooting & Optimization Steps

Step 1: Profile the PL/SQL Layer

Locate exactly which lines are consuming the time using the Hierarchical Profiler (DBMS_HPROF).

sql

-- Start profiling
EXEC DBMS_HPROF.START_PROFILING;

-- Execute the test case
EXEC PKG_ORDER_PROCESSING.PROCESS_DAILY_ORDERS;

-- Stop profiling
EXEC DBMS_HPROF.STOP_PROFILING;

Analyze the generated tables (dbmshp_runs, dbmshp_function_info). You will find that 95% of the execution time is spent on the UPDATE statement inside the loop, confirming a SQL/PL-SQL context switching bottleneck.

Step 2: Correlate with Exadata Session Wait History

Check ASH metrics to confirm what the database session was waiting on during execution.

sql

SELECT 
    session_state, event, 
    count(*) as samples,
    plsql_entry_object_id, plsql_subprogram_id
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY session_state, event, plsql_entry_object_id, plsql_subprogram_id
ORDER BY samples DESC;

Result Matrix: If you see high ON CPU with zero cell smart table scan events, your PL/SQL framework is preventing Exadata from using its storage-level offloading filters because it streams records one row at a time.

Step 3: Implement the Performance Fix

Refactor the procedural code to use bulk processing features (BULK COLLECT and FORALL) or, ideally, pure set-based SQL. This permits ExaCC X9 to execute the data block updates simultaneously across Storage Cells.

sql

CREATE OR REPLACE PACKAGE BODY PKG_ORDER_PROCESSING AS
    PROCEDURE PROCESS_DAILY_ORDERS IS
        TYPE t_order_id IS TABLE OF orders_mfg.order_id%TYPE;
        TYPE t_amount   IS TABLE OF orders_mfg.amount%TYPE;
        
        l_ids     t_order_id;
        l_amounts t_amount;
    BEGIN
        SELECT order_id, amount 
        BULK COLLECT INTO l_ids, l_amounts
        FROM orders_mfg 
        WHERE status = 'PENDING';

        -- Use FORALL for high-speed bulk binding to minimize context switches
        FORALL i IN 1..l_ids.COUNT
            UPDATE orders_mfg 
            SET amount = l_amounts(i) * 1.05, status = 'PROCESSED'
            WHERE order_id = l_ids(i);
    END PROCESS_DAILY_ORDERS;
END PKG_ORDER_PROCESSING;
/

Interview Question and Answer

Q: How do you approach troubleshooting a slow PL/SQL routine on an Exadata ExaCC X9 environment?

A:
"I break the investigation down into two primary layers: PL/SQL Engine Bottlenecks and Exadata Engine Optimization.

Isolate Code via Profilers: First, I run the package using DBMS_HPROF or DBMS_PROFILER to determine if the time is lost within procedural calculations, deep loops, or underlying SQL queries.

Context Switching & Set Processing Analysis: If the profiler points to looping SQL statements, I look for context switching overhead ('row-by-row' processing). I eliminate this by refactoring code to use BULK COLLECT and FORALL or rewriting it as a pure set-based SQL statement.

Exadata Storage Cell Validation: Next, I use V$ACTIVE_SESSION_HISTORY and Real-Time SQL Monitoring to verify if the underlying SQL is leveraging Exadata's hardware. I check for wait events like cell smart table scan and verify using V$SQL_MONITOR that the IO_CELL_OFFLOAD_ELIGIBLE_BYTES metric matches the total read bytes. If it does not offload, I check for blocking causes such as missing table statistics, function calls in the WHERE clause, or improper data type conversions that disable Smart Scans."

Question : Kubernetes with exacc

Running microservices on Kubernetes connecting to high-performance Oracle Exadata Database Service on Cloud@Customer (ExaCC) X9M requires balancing container network latency with sub-millisecond database I/O. Microservice pods connect via enterprise 10/25GbE networks to the ExaCC database grid, leveraging Exadata RDMA over Converged Ethernet (RoCE) and persistent memory (PMem).

Architecture & Scenario Example

The Setup: A Kubernetes cluster hosted on OCI or localized worker nodes processing high-volume transactional orders, calling an Oracle 19c database backend on an ExaCC X9M quarter-rack.

The Bottleneck: Sudden pod connection timeouts and high latency spikes during peak transaction injection from Kubernetes application pods into the external database.

Analysis Tools Used

Database & Storage Layer: Oracle Enterprise Manager (OEM), Automatic Workload Repository (AWR), Active Session History (ASH), and ExaCC Performance Hub.

Kubernetes & Network Layer: Prometheus, Grafana, kubectl top, and dnsutils (for CoreDNS latency checks).

Test Cases for Performance Verification

Connection Pool Saturation Test: Simulate 500 concurrent microservice threads opening and closing JDBC connections simultaneously.

High-Throughput SQL Scan Test: Run heavy analytical queries from the container layer to evaluate Exadata Smart Scan offload efficiency under heavy K8s ingress load.

Network Latency Under Load Test: Measure round-trip time (RTT) from inside the application Pod to the ExaCC VIP using continuous tcpping.

Step-by-Step Analysis Procedure

Capture Baseline Metrics: Pull container resource use via Prometheus (container_cpu_usage_seconds_total) and database wait events via ASH.

Isolate the Bottleneck Tier: Check if the delay resides in Kubernetes DNS resolution/connection pooling or database cell-offload processing.

Analyze ExaCC Metrics: Open the Performance Hub in the OCI Console to review flash/disk IOPS, Smart Scan ratios, and interconnect latency on the X9M RoCE fabric.

Inspect K8s Network Policies: Verify that Calico/Flannel network plugins or firewall rules on the 25GbE NICs are not introducing packet drops or socket queue blocking.

Troubleshooting Steps

Exhausted Connection Pools: Increase maxPoolSize in the application's data source configuration and tune sessions / processes parameters on the Oracle database.

DNS/Resolution Lag: Scale out CoreDNS replicas in Kubernetes or implement local node-cache (node-local-dns) to bypass cluster IP translation delays for external database VIPs.

Resource Throttling: Check for OOMKilled or CPU throttling (container_cpu_cfs_throttled_periods_total) on application pods hitting rigid limits during database payload parsing.

RoCE / Interconnect Saturation: Run exachk or check storage cell alerts via OEM to clear interface drops on the Exadata switch layer

Example Scenario & Test Case

Workload: Oracle/PostgreSQL container deployed via Kubernetes StatefulSets on an ExaCC X9 node.

Test Case: Run an OLTP read/write benchmark using pgbench or Swingbench targeting 500 concurrent connections, generating 10,000 IOPS to validate storage network performance and kernel parameters.

Sample Test Script (pgbench execution)

bash

pgbench -i -s 50 dbname
pgbench -c 50 -j 4 -t 10000 -P 10 dbname

Analysis Tools & Steps

Prometheus & Grafana: Monitor container CPU throttling (container_cpu_cfs_throttled_periods_total) and memory usage.

Oracle AWR / OSWatcher (ExaCC Host level): Correlate K8s node network latency with Exadata RDMA fabric metrics.

kubectl diagnostics: Run real-time performance checks:

bash

kubectl top pods --use-columns=NAME,CPU(cores),MEMORY(bytes)
kubectl top nodes

Troubleshooting Steps (High Latency / Throttling)

Step 1: Check Resource Constraints
Inspect if CPU limits cause throttling:

bash

kubectl describe pod <db-pod-name>

Step 2: Inspect Node Pressure
Look for memory or disk bottlenecks on the ExaCC node:

bash

kubectl describe node <node-name>

Step 3: Analyze Kubelet & Container Logs
Verify storage mount health and I/O wait times:

bash

kubectl logs <db-pod-name> --tail=100

Question : Unified Auditing and SOX Compliance on exacc

Oracle Unified Auditing logs database security events into a single audit trail (UNIFIED_AUDIT_TRAIL). On Exadata Database Service on Cloud at Customer (ExaCC X9M), it uses high-performance Automatic Storage Management (ASM) storage and offload capabilities to minimize overhead, helping meet Sarbanes-Oxley (SOX) compliance for financial data access tracking.

Architecture & Performance on ExaCC X9

Single Audit Trail: Combines audit records from traditional audit, fine-grained auditing, and RMAN into one immutable read-only table in the AUDSYS schema.

Queued Write Mechanism: Audit records queue in memory before flushing to disk asynchronously, reducing CPU and log-file sync latency.

ExaCC Smart Scan: Offloads audit table queries to Exadata storage cells, speeding up compliance report generation.

Tools Used for Analysis

SQL*Plus / SQL Developer: For running manual audit policy queries and configuration.

Oracle Enterprise Manager (OEM) 13c/14c: For centralized compliance dashboard monitoring and report generation.

Oracle Audit Vault and Database Firewall (AVDF): For external collection, secure archiving, and SOX reporting.

Implementation Steps

Enable Unified Auditing: Verify it is active by running SELECT * FROM v$option WHERE parameter = 'Unified Auditing';.

Create a SOX Policy: Create a policy to track sensitive schema access.

sql

CREATE AUDIT POLICY sox_finance_policy 
ACTIONS SELECT ON finance.accounts_receivable, UPDATE ON finance.accounts_receivable;

Enable the Policy: Assign the policy to specific or all users.

sql

AUDIT POLICY sox_finance_policy BY finance_user;

Query the Audit Trail: Review captured events.

sql

SELECT event_timestamp, dbusername, action_name, sql_text 
FROM unified_audit_trail 
WHERE audit_policy_names = 'SOX_FINANCE_POLICY';

Test Cases

Positive Test Case: Log in as finance_user, run SELECT * FROM finance.accounts_receivable;, and verify a record is generated in UNIFIED_AUDIT_TRAIL.

Negative Test Case: Attempt access with an unauthorized user, verify the failed login or unauthorized access attempt is logged with a return code (return_code != 0).

Troubleshooting Steps

High CPU/Latch Contention: Check if audit writes are delayed; switch to mixed/queued audit mode if synchronous writes cause slowdowns.

Missing Audit Records: Verify the AUDSYS tablespace has enough space and background queue flush processes are running properly.

Purge Queue Delays: Use DBMS_AUDIT_MGMT to manage and clean up old audit data regularly to avoid performance degradation.

Interview Questions & Answers

Q: Why use Unified Auditing for SOX on ExaCC X9 instead of traditional auditing?

A: It provides a tamper-resistant unified trail, better performance through queued writes, and lower CPU overhead leveraging Exadata storage optimizations.

Q: How do you verify audit record generation performance impact?

A: Monitor wait events like enq: JQ - serialization or audit-related log file syncs using AWR reports to ensure asynchronous queue flushing is working efficiently.

Oracle Unified Auditing simplifies tracking database activity for SOX compliance by combining all audit records into a single AUDIFIED_AUDIT_TRAIL table. Running on Exadata Cloud@Customer X9M (ExaCC X9M) provides high performance via Exadata hardware acceleration, RDMA, and smart scan capabilities.

Unified Auditing Setup for SOX

Enable Unified Auditing: Check status using SELECT * FROM v$option WHERE parameter = 'Unified Auditing';. If false, relink Oracle binary with make -f ins_rdbms.mk uniaud_on.

Create Audit Policy: Run CREATE AUDIT POLICY sox_fin_policy ACTIONS SELECT ON finance.accounts, UPDATE ON finance.accounts;.

Enable Policy: Run AUDIT POLICY sox_fin_policy;.

Query Audit Records: Access the trail via SELECT event_timestamp, dbusername, action_name, sql_text FROM unified_audit_trail WHERE policy_name = 'SOX_FIN_POLICY';.

ExaCC X9M Performance & Tools

Hardware: Exadata X9M leverages persistent memory (PMEM) and 100Gb RoCE network for ultra-low audit write latency.

Performance Impact: Unified auditing writes asynchronously to internal tables, minimizing impact on OLTP transactions.

Tools Used:

Oracle Enterprise Manager (OEM) for visual audit policy management.
Exadata Database Machine Health Check Tool (Exachk) for hardware and database performance validation.
SQL Developer for running audit trail analysis queries.

Test Cases

Positive Test: Perform an authorized SELECT on finance.accounts. Verify that a corresponding record is successfully logged in UNIFIED_AUDIT_TRAIL.

Negative Test: Attempt an unauthorized modification or access. Verify that the failure code and SQL text are captured instantly without locking the session.

Troubleshooting Steps

Missing Audit Records: Check if the audit policy is explicitly enabled (SELECT * FROM audit_unified_enabled_policies;).

Performance Lag: Inspect AWR reports for enq: CF - contention or high unified audit trail enqueue waits. Ensure cleanup jobs for the audit trail are scheduled properly.

Space Management: Purge old logs using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL to prevent the SYSTEM tablespace from filling up.

Interview Q&A

Q: How does Unified Auditing help with SOX compliance on ExaCC X9?

A: It centralizes tamper-resistant audit logs into one read-consistent view, and ExaCC X9 hardware ensures high-speed log writing without hurting database throughput.

Q: How do you troubleshoot high CPU usage related to auditing on Exadata?

A: Check AWR for audit-related waits, optimize overly broad audit policies (e.g., changing AUDIT ALL to targeted object policies), and verify automatic purging.

What is bkup_api in ExaCC X9?

Definition: A local command-line tool (/var/opt/oracle/bkup_api/bkup_api) used to control database backup and recovery operations on compute nodes. [

Key Functions: Starts on-demand backups (bkup_start), lists existing backup jobs (recover list), deletes backups (backup_delete), and toggles automated backup schedules (disable backup).

Common Commands:

Standard on-demand backup: # /var/opt/oracle/bkup_api/bkup_api bkup_start --dbname=dbname
Manual full Level 0 backup: # /var/opt/oracle/bkup_api/bkup_api bkup_start --level0 --dbname=dbname
Disable scheduled backups: /var/opt/oracle/bkup_api/bkup_api disable backup
Note: bkup_api is legacy on newer image frameworks; dbaascli database backup commands are preferred for newer deployments. [

bkup_api Failures

Symptom: bkup_api command fails with authentication or connection errors to object storage.

Resolution: Verify local network routing to the Object Storage / NFS backup destination and ensure you execute the command inside a root shell (sudo -s) as a member of the oracle or opc ecosystem with proper OCI IAM credential configuration.

Symptom: Backup logs not reporting to the OCI console correctly.

Resolution: Remember that direct command-line invocation of local helper scripts or deprecated bkup_api routines may desync manual tags from the OCI management dashboard; check local logs under /var/opt/oracle/dbaas_acfs/bkup/. [

Question : How to patch guest VM (DomU) and physical hypervisor (Dom0)

On Oracle Exadata Database Service / Cloud@Customer (ExaCC), dbaascli manages guest VM (DomU) software lifecycle, while physical hypervisor (Dom0) patching relies on patchmgr run from a separate control node. Use dbaascli for database/GI patching and patchmgr for Dom0 updates.

DomU and Dom0 Patching Commands & Use Cases

Dom0 Patching (Physical Hypervisor Layer via patchmgr)

Dom0 patches require passwordless SSH from an external management/console node; active DomUs must be migrated or shut down in a rolling procedure.

./patchmgr -dbnodes ~/dom0 -precheck -target_version <version> — Validates hardware, firmware, and dom0 prerequisites.

./patchmgr -dbnodes ~/dom0 -upgrade -target_version <version> — Executes rolling physical Dom0 and ILOM firmware updates.

Troubleshooting use case: If patchmgr hangs on SSH equivalence, verify root keyless access between the control console and target hypervisors.

DomU Patching & Management (dbaascli)

Run locally inside the guest VM as root.

dbaascli dbhome patch --executePrereqs --patching_version <ver> — Runs pre-checks for in-place Oracle Home / Release Update patching.

dbaascli dbhome patch --apply --patching_version <ver> — Applies the database home patch locally.

dbaascli grid patch --executePrereqs / --apply — Validates and applies Grid Infrastructure updates.

Troubleshooting use case (Resuming failed operations): When cloud automation or console patch workflows fail mid-way, inspect logs and re-invoke dbaascli once the underlying OS/sql block is corrected.

dbaascli Troubleshooting & General Issue Use Cases

Diagnostic Data & Health Checks

dbaascli diag run —-epic or log collection tools — Bundles core cloud-tooling diagnostic files and configuration details.

Troubleshooting use case: Use this when OCI control plane calls time out or fail to report accurate database status, capturing local tooling bugs.

Database & Pluggable Database (PDB) Control

dbaascli database status — Displays real-time open modes and instance topology.

dbaascli database bounce — Safely performs an automated soft stop and start sequence.

Troubleshooting use case: Use when standard srvctl or SQL*Plus actions behave unusually under cloud framework wrappers.

Credential & Security Management

dbaascli database changepassword --dbName <name> --usersys <pass> — Updates internal system passwords while keeping cloud metadata synchronized.

Troubleshooting use case: Resolves synchronization mismatches where OCI console actions fail because the local database password drifted from vault storage.

On Oracle Exadata Database Service on Cloud@Customer (ExaCC X9), patching is divided: Oracle manages physical hypervisor (Dom0) infrastructure updates, while you manage Guest VMs (DomU) OS, Grid Infrastructure (GI), and databases via the OCI Console or patchmgr/dbaascli. Always run prechecks immediately before applying updates.

Patching Guest VM (DomU) OS & Grid Infrastructure

Navigate to the VM Cluster Details page in your OCI Console.

Select Updates (OS) for DomU operating system packages or Updates (GI) for Grid Infrastructure.

Click the action menu (three dots) and select Run Precheck.

Review and confirm the precheck passes successfully (takes ~20 minutes).

Choose Apply Update (rolling or non-rolling) to push the release updates (RUs).

Physical Hypervisor (Dom0) and Infrastructure Updates

ExaCC infrastructure maintenance (including Dom0, switches, ILOM, and storage cells) is scheduled quarterly.

Coordinate and monitor maintenance windows via the cloud notification portal.

Oracle applies Dom0 updates in a rolling fashion across compute nodes to preserve high availability.

For non-cloud deployment (traditional Exadata X9M racks), run patchmgr from an independent control node with passwordless SSH configured.

Troubleshooting Common Patching Failures

Precheck or Patch Failures: Check job logs under associated resources (fsujob or patchmgr logs).

Multi-Node Inconsistency: If a node fails midway, roll back all nodes to match versions before fixing underlying constraints and retrying.

Idle SSH Timeouts: Expect SSH connections to drop after 600 seconds due to default STIG security hardening applied during X9 updates.

NetworkManager Conflicts: Never install NetworkManager on Exadata compute nodes; it breaks critical network connectivity during reboots.

Managing and patching Oracle Exadata Database Service on Dedicated Infrastructure (ExaDB-D/ExaCS) requires a combined approach where Oracle manages the physical infrastructure, and you manage the software stack from the hypervisor up. The OCI console simplifies patching via rolling, zero-downtime updates

1. Responsibility Matrix

Oracle: Maintains physical hardware, network fabric, PDU, switches, and hypervisors.
Customer: Responsible for patching the Guest VM OS, Grid Infrastructure (GI), Database Homes, and the databases themselv

1. Patching the Guest VM OS

Oracle manages the physical Exadata infrastructure, but you are responsible for patching the guest VM operating system

Via OCI Console: Go to the VM Cluster details, navigate to Updates (OS), select an available update, click Run Precheck, and once successful, click Apply Exadata OS Image Update.

Via CLI: Use the patchmgr utility on a designated Exadata compute node to drive operating system updates across all nodes in the cluster

2. Patching Oracle Grid Infrastructure (GI)

You must keep your Grid Infrastructure updated to match the latest quarterly Release Update (RU) requirements

Via OCI Console: Under your VM Cluster details in the OCI Console, click View Patches next to the Updates Available field. Select a patch, run a Precheck to validate prerequisites, and click Update Grid Infrastructure. This applies rolling patches to each node to prevent downtime

3. Patching the Oracle Database

Exadata databases are patched by applying Release Updates (RUs) using out-of-place patching, meaning you create or provision a new patched Oracle Home

Via OCI Console: Go to your Database Homes list, choose the database home, and review available updates. Select your target image, run a Precheck, and then move the database to the new patched home.

Via CLI: You can use dbaascli patch db apply to apply quarterly database patches, or use exadbcpatchmulti to handle multiple database patch operations directly from the command lin

3. Key Administration Commands

For command-line administration, you can utilize the dbaascli utility on the compute nodes

Check available database patches:
dbaascli patch db get_list

Apply a database patch (out-of-place or in-place):
dbaascli patch db apply --patchid <patch_id>

Run a precheck for patching operations:
dbaascli patch db precheck --patchid <patch_id>

OS / Grid Infrastructure Command Line: Use patchmgr from a driving node to orchestrate updates across compute nodes

4. Best Practices

Always back up your databases before executing any patching operations.
Follow the $N - 3$ versioning rule: Always use the current major version or one of the three most recent ones (N through N - 3).
Off-peak monthly patching is scheduled automatically by Oracle for critical infrastructure. Be sure to check the Maintenance Preferences in your Exadata Infrastructure settings to define rolling versus non-rolling schedules and reschedule if needed

Question : do we patch storage server on (ExaCS) and ExaDB-D

No, you do not manually patch the Exadata Storage Servers (Cells) on ExaCS (Exadata Cloud Service) or ExaDB-D (Exadata Database Service on Dedicated Infrastructure). Oracle manages the storage server patching and updates via cloud automation

Here is how maintenance responsibilities are divided:

Oracle-Managed Components: Oracle manages everything at and below the hypervisor. This includes the Storage Servers, hardware, firmware, and network fabric. Oracle updates these components in the background using rolling updates to ensure zero database downtime.
Customer-Managed Components: You are responsible for patching the software layers you control above the hypervisor. This includes:

Database Homes (Oracle Database software).
Grid Infrastructure (Clusterware and ASM).
Guest VM Operating System (the OS on your compute nodes)

How Storage Server Patching Works:

Rolling Execution: Updates to storage servers are applied in a rolling manner. Thanks to ASM High Redundancy and Exadata software design, this happens without impacting database or application availability.
Scheduling: While Oracle controls the underlying infrastructure patches, you can define maintenance windows and schedule your infrastructure updates using the Oracle Cloud Infrastructure Console

Question : what is general issue on patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Patch failures on Oracle Exadata Cloud Service (ExaCS) and ExaDB-D usually stem from outdated Cloud Tooling, lack of network connectivity to the Object Store, or improper cluster states. Addressing pre-checks and dependency conflicts early prevents most interruptions

The most common patching issues and how to tackle them include:

1. Outdated Cloud Tooling

The Issue: Attempting to patch the Grid Infrastructure (GI) or Database Homes without first updating the dbcli and dbaascli cloud tooling.
The Fix: Ensure all Exadata database nodes run the same, most current version of cloud tooling before initiating any upgrade sequence

2. Object Store Connectivity

The Issue: The virtual machine cannot reach the Oracle Cloud Infrastructure Object Store. This often happens if the service gateway or static route is misconfigured, causing patch downloads to stall.
The Fix: Verify your VCN route tables and ensure a static route exists for Object Storage on each compute node.

3. Database State and Custom Configurations

The Issue: Patching may fail if instances are down, ASM is not running properly, or custom wallet/listener files do not match across cluster nodes.
The Fix: Ensure the database instance status is Open and active on all nodes before starting operations. Temporarily restore standard configuration files (like custom wallets) if the patch process trips on them.

4. File System Space Constraints

The Issue: Insufficient disk space on the /u01 or /u02 partitions causes pre-checks to fail.
The Fix: Clear out old trace files, log files, or obsolete backups prior to patching

5. Custom OS Package Conflicts

The Issue: If you installed non-Exadata RPMs (extra OS packages) on the Guest VMs, the pre-check may flag conflicts with Oracle-installed RPMs.
The Fix: Resolve the RPM dependencies or uninstall the conflicting non-Exadata packages before trying the Guest VM upgrade again

Cloud Tooling and Administration (dbaascli)

dbaascli patch tools list: Displays the currently installed cloud tooling version and checks if any updates are available for your system.
dbaascli admin showLatestStackVersion: Returns the version number of the latest available dbaastools RPM stack update.
Context note: These commands are run as the root user after connecting to a compute node as the opc user

System Architecture and Environment Files

/var/opt/oracle/misc/platforminfo: A system file containing the deployment type identifier. On Exadata Cloud Service (ExaCS) or ExaDB-D, querying this file will return EXACS or EXACC (Cloud@Customer).
/usr/local/bin/imageinfo: An Exadata utility script used to generate a summary of the release versions and statuses of software, OS, and firmware components on your Exadata compute or storage nodes

1. Software Image Management

dbaascli cswLib listLocal: Lists the database software images and versions locally available in your environment for patching or prov isioning

2. Encryption & Wallet Commands

dbaascli tde status --dbname <dbname>: Checks the status of the Transparent Data Encryption (TDE) keystore (open, closed, auto-login).
dbaascli database verify_wallet --dbname <dbname>: Validates the integrity and accessibility of the database wallet

3. Database Metadata & Administration

dbaascli database getDetails --dbname <dbname>: Returns specific configuration and operational details for the specified database

4. Backup Operations & Troubleshooting

dbaascli database backup --dbname <dbname> --getSchedules: Displays the configured automated backup schedules.
dbaascli database backup --getConfig --dbName <dbname> --configFile /tmp/<dbname>_cfg.txt: Exports current backup configuration parameters to a text file for review or editing.
dbaascli database backup --dbname <dbname> --list: Lists all available backups taken for the database.
dbaascli database backup --dbName <dbname> --showHistory --all: Displays a comprehensive, historical log of all backup jobs.
dbaascli database backup --dbname <dbname> --status --uuid <uuid>: Checks the status of a specific, previously run backup job using its UUID.
dbaascli database backup --getLatestBackupJob --dbname <dbname>: Fetches the job details and status of the most recent backup execution

The GetExaWatcherResults.sh command extracts ExaWatcher performance data on Oracle Exadata servers. It gathers detailed OS metrics (like CPU, memory, and network) between your specified timestamps, and compiles them into a compressed archive (e.g., .zip or .tar.gz)

What happens next?

Locate the Output: The generated archive is typically saved in the current directory or a designated directory (e.g., /opt/oracle.ExaWatcher/archive/).
Reviewing the Data: The archive contains CSV/raw data files for OS tools like iostat, mpstat, and vmstat, along with a small subset of pre-built visual charts

Exawatcher report

To collect from/to a certain date and time:

example:

# ./GetExaWatcherResults.sh --from 01/25/2025_13:00:00 --to 01/25/2025_14:00:00

Use the tfactl diagcollect command with the -node flag to target specific nodes. By default, TFA collects data for the past 12 hours and from all nodes. To pinpoint the collection, restrict it to the exact nodes and timeframe when the issue occurred

Useful Parameters for Targeting:

Target Nodes: Specify -node local for just the server you are on, or -node node1,node2 for a comma-separated list.
Time Range: Use -from and -to for an exact window, or -last <n>h|d to gather logs for the past $n$ hours or days (e.g., -last 4h).
Specific Component: Add flags like -crs, -asm, or -database <db_name> to restrict collection to those specific sub-systems

TFA report collection from nodes that experienced the issue.

./tfactl diagcollect -from "Feb/05/2025 02:00:00" -to "Feb/05/2025 07:00:00"

For more details

Troubleshooting Exadata Cloud Infrastructure Systems

Using the dbaascli Utility on Exadata Cloud Infrastructure

Patching and Updating an Exadata Cloud Infrastructure System Manually

Question : How to administer Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Administering Oracle Exadata Cloud Infrastructure (ExaCS) and Exadata Database Service on Dedicated Infrastructure (ExaDB-D) combines Oracle Cloud Infrastructure (OCI) management with standard Exadata Database Administration. You manage physical and virtual infrastructure via the OCI Console, while using traditional commands (e.g., srvctl, SQL*Plus, or dcli) for databases

1. Administer Infrastructure via OCI Console

Compute & VM Clusters: Use the OCI Console to provision and scale Exadata VM Clusters, manage database homes, and allocate CPU/RAM.
Patching & Updates: OCI handles the lifecycle management for Grid Infrastructure and database software images. You can schedule automated updates or trigger them manually via the console.
Storage Configuration: Manage your Exadata storage, adjust Exadata I/O Resource Management (IORM), and monitor metrics directly through the OCI dashboards.
Backups: Configure automated OCI-managed backups (which offload directly to Object Storage) and manage retention policies at the VM cluster level

2. Administer Databases & Grid Infrastructure

Command Line Utilities: For Grid Infrastructure operations, connect via SSH to the compute nodes and use utilities like srvctl (for database and service management), crsctl (for clusterware), and dcli (for executing commands across all compute nodes).
Database Operations: Continue using native Oracle tools like RMAN for backups, Data Pump for migrations, and SQL*Plus/SQLcl for typical database administration.
Data Guard: Set up, monitor, and failover/switchover Oracle Data Guard configurations—including multi-standby deployments—either using OCI automation in the console or via traditional DGMGRL commands

3. Identity, Access, and Security

Compartments & Policies: Control who can view and modify Exadata resources by configuring specific IAM policies and organizing your infrastructure into Compartments.
Encryption & Keystores: Manage your master encryption keys natively using OCI Vault, or integrate with Oracle Data Safe to manage user security and auditing

Question : what will you check and analyze in exadata through AWR

In Exadata, an AWR report integrates database-tier metrics with low-level storage cell statistics, offering deep visibility into hardware and software performance. To analyze the system effectively, you should check and evaluate the following key Exadata-specific areas in the report

1. Exadata Storage Server Health & Configuration

Exadata Configuration Differences: Checks for mismatched hardware or software releases across your storage servers (cells). Discrepancies can lead to unpredictable I/O behavior.
Server Health Report: Reviews disk statuses and validates that no grid disks or cell disks are unexpectedly offline, which would reduce your total available I/O bandwidth.

2. Smart Scan & Offload Efficiency

Offload Efficiency Percentages: Analyzes how many I/O operations are being offloaded to the storage cells. Low offload statistics usually mean the database is pulling raw data blocks instead of leveraging Exadata Smart Scans for filtering.
Storage Index Usage: Checks the number of "Smart IO bytes saved by storage index." Higher savings mean Exadata is successfully skipping reading data blocks that do not meet your query criteria, saving I/O resources

3. Smart Flash Cache & Flash Log Performance

Flash Cache Hit Ratios: Assesses read requests satisfied by flash rather than traditional hard disks. You can review cache usage broken down by workload type (OLTP, Scan, Keep).
Smart Flash Log Statistics: Ensures that log file parallel write operations are being accelerated by flash. You should check for "Flash Log Skips" and redo write latency histograms to identify high-latency I/O outliers

4. I/O Resource Management (IORM)

Top Databases by I/O Requests: Shows which databases or workloads on the Exadata machine are consuming the bulk of the I/O throughput.
IORM Wait Time: Evaluates queue times for flash and disk devices. If queue times are high (e.g., greater than 5-10 ms), IORM plans may need tuning to prevent noisy neighbors from impacting critical databases

5. I/O Outlier Analysis

Exadata Outlier Summary: Exadata typically distributes I/O requests evenly across all cells. AWR's outlier analysis pinpoints which individual cell servers, grid disks, or host HBAs are experiencing disproportionately high latency or service times compared to the rest of the storage grid.

Question : what will you check and analyze in exadata x8m through AWR

Analyzing an Exadata X8M AWR report requires looking beyond standard database wait events. You need to investigate the specialized RDMA over Converged Ethernet (RoCE) network, Smart Flash Cache, and Intel Optane Persistent Memory (PMEM)

Because Exadata uses a scale-out storage grid, the AWR report consolidates and surfaces crucial metrics in the Exadata Statistics section

1. PMEM Cache & Commit Accelerator (The X8M Advantage)

Exadata X8M leverages Intel Optane PMEM to bypass the standard network and storage software layers

Smart PMEM Read & Write Latency: Look for $\mu s$ (microsecond) wait times rather than $ms$ (millisecond) latencies. If PMEM read/write events show elevated times, investigate network interconnect or hardware issues.

Log File Sync Waits: In X8M, the PMEM Commit Accelerator logs commits directly to PMEM. Verify that log file sync and cell single block physical read wait times drop dramatically compared to traditional all-flash architectures

2. Exadata Smart Flash Cache Efficiency

Check if your most active data is sitting in flash.

Flash Cache Hit Ratios: Review the Flash Cache User Reads and User Writes sections. High percentages of unoptimized read requests (reads that hit spinning hard disks) indicate your working set outgrows the flash cache.

Write-Back vs Write-Through: Look for the ratio of First Writes to Overwrites. High overwrites indicate your flash cache is absorbing I/O effectively, dramatically saving disk write operations.

3. Smart Scan & Offload Efficiency

Smart Scans reduce the volume of data traveling across the Exadata network by filtering rows and columns at the storage server layer

Interconnect vs Eligible I/O: Compare cell physical IO interconnect bytes returned by smart scan to cell physical IO bytes eligible for predicate offload. A large disparity proves that predicate filtering (column/row pruning) is working well.

Storage Index Savings: Review the IO Saved by Storage Index metrics. If savings are consistently low, queries are not skipping unnecessary Exadata I/O regions efficiently, which points to tuning opportunities on table clustering or data types

4. Wait Events (Foreground & Background)

Correlate traditional database wait times with Exadata-specific hardware events

Cell Single Block Physical Read / Multiblock Physical Read: Analyze the average wait time for these events. High times typically mean you are hitting spinning disks instead of the PMEM or Flash cache tiers.

Reliable Message: In Exadata, this event indicates internal cell communications or cluster channel syncs. Spikes here may indicate network contention or RoCE adapter congestion

. IORM (I/O Resource Management)

IORM Wait Events: Verify that no specific database or pluggable database (PDB) is being excessively throttled. Look at IORM transient bottleneck and db file sequential read waits to ensure your consumer groups are properly prioritized.

6. Health & Configuration Checks

Offline Disks: The Exadata Health Report section automatically alerts you if grid disks or cell disks are offline or degraded. Even one offline disk can halve your I/O bandwidth.
Storage Server Software Version: Ensure Exadata server versions are uniform across all storage cells to prevent mismatched offload algorithms or software limitations

Question : what will you check and analyze in exadata x8m if database is getting hanged

If an Exadata X8M database hangs, immediately identify if it is a global grid/cluster issue or an isolated database slowdown. Focus on cluster metrics, interconnect/network health (key to X8M's RoCE architecture), and storage cell bottlenecks

Review the following components systematically:

1. Database & Compute Nodes

Clusterware & High Availability: Run crsctl check cluster to see if the cluster is healthy. In X8M, hardware-based RDMA immediately catches severe node freezes; verify that the node hasn't been evicted.
Active Session History (ASH): Since the database is hung, generate a report using oradebug setmypid or Real-Time ADDM to identify the predominant wait classes (e.g., Cluster, Concurrency, System I/O).
Crucial Wait Events: Look for cell single block physical read (indicates storage tier bottlenecks) or log file sync (indicates log write issues).

2. Exadata X8M Network Fabric (RoCE)

The X8M replaces traditional interconnects with RDMA over Converged Ethernet (RoCE), making network latency the primary suspect

Switch Health: Check the Cisco or Mellanox switch ports for errors, drops, or packet discards using esxcfg-nics or native switch commands.

Interconnect Waits: Check for spikes in gcs drm freeze or gc cr block busy waits, which point to node-to-node communication stalls

3. Exadata Storage Cells

If the database isn't fully locked but queries are hanging, the issue may stem from the storage tier

Smart PMEM Log: In X8M, redo logs are written via RDMA directly to Persistent Memory (PMEM). Check v$sysstat or v$log to ensure PMEM commits are completing properly.

Storage CPU Utilization: Log into the cell servers via cellcli and run SCLI=list metriccurrent where objectType='CELL' and name like 'CPU_UTIL%' to ensure the cell server CPUs are not saturated.

Disk Queuing: Check for high I/O latencies using SCLI=list metriccurrent where objectType='GRIDDISK' and name like 'GD_IO_RQ_TM%' to see if grid disks are causing long waits

4. Diagnostics & Logs

Exachk: Run the Oracle Exachk health check tool to identify known hardware or software configuration bugs (e.g., node evictions not properly resetting).
ADRCI: Execute adrci and check the alert logs to capture specific incident numbers and trace files

Question : what will you check and analyze in exadata x8m if database is getting hanged with command

When an Oracle Exadata database hangs, troubleshooting requires looking past the database layer down into the Exadata-specific hardware (Storage Servers, InfiniBand/RoCE network

A robust troubleshooting workflow requires checking the following areas using specific tools and commands:

1. Database Tier Checks

If you can still connect to the database (even via sqlplus -prelim / as sysdba), investigate where sessions are spending their time.

Identify Critical Wait Events: Check for Exadata-specific wait events (e.g., cell single block physical read, cell smart table scan).

sql

SELECT event, SUM(wait_time), SUM(seconds_in_wait) 
FROM v$session_wait 
WHERE wait_class NOT IN ('Idle') 
GROUP BY event;

Look for Contention: Check for enqueue or locking issues.

Real-Time ADDM: If the database is completely unresponsive, use Real-Time ADDM to diagnose the hang without logging in.

2. Exadata Storage Level (PMEM & Smart Logging)

Exadata Smart PMEM Cache: Exadata X8M utilizes Persistent Memory (PMEM) and RoCE (RDMA over Converged Ethernet) to bypass traditional OS I/O stacks. Check for waits specifically related to the RDMA path: cell single block physical read: pmem cache or cell single block physical read: xrmem cache.
Cell Server (CellSRV) Metrics: Use the cellcli command line on the Exadata storage servers to check for any slow disk response times or interface issues on the RoCE network fabric

2. Storage Cell Tier Checks (Storage Servers)

If the database waits indicate I/O or cell-related issues, log into a compute node and use dcli or cellcli to interrogate the Exadata Storage Servers

Check Storage Server Health:

bash

dcli -c cell01,cell02,cell03 "cellcli -e list alertcurrent"

Verify Disk/Cell Health: Look for offline or critical disks or flash drives

bash
dcli -c cell01,cell02,cell03 "cellcli -e list griddisk attributes name,status"


Examine Quarantined Cells: Check if Exadata has quarantined any faulty offload operations that might be forcing the DB into slow single-block reads.bash
dcli -c cell01,cell02,cell03 "cellcli -e list quarantine"

3. Exadata X8M Persistent Memory (PMEM)

A key feature of the Exadata X8M is its RoCE network and NVDIMM/PMEM (Persistent Memory) write accelerators. If these hang, commit times drop to a crawl

Verify PMEM State: Use the cellcli tool to ensure the NVDIMM hardware and PMEM controllers are operating normally.

bash

dcli -c cell01,cell02,cell03 "cellcli -e list pmemdisk attributes name,status"

4. Fabric / Network (RoCE) Checks
Exadata X8M uses RDMA over Converged Ethernet (RoCE) for cluster communication and storage access. A network degradation here will look like a database hang

Check RoCE Switches: From the compute node, verify that there are no packet drops or latency spikes across the X8M network fabric.

5. Operating System / Hardware Layer

Log to ILOM: If the storage or database server host OS completely fails to respond, query the server's Integrated Lights Out Manager (ILOM) for hardware-level faults or system freezes.

bash

show /SP/logs/event/list

Are you getting any specific wait events (e.g., `cell single block physical read`)?
Have you checked the Exadata Alert Log (`/opt/oracle.SupportTools/em/cell_alert_log`)?
Is this a total database freeze or a severe performance slowdown?

Review Log and Trace Files

If the database is completely hung and you cannot run SQL, use Real-Time ADDM to analyze the hang from outside the database. Then, check:

Alert Log: Located in diag/rdbms/.../trace/alert_<SID>.log. Look for "LGWR is taking too long" warnings.
LGWR Trace Files: Check for I/O errors or timeouts in the storage layer.
Cell Server Logs: On the storage cells, use the cellcli tool to check LIST METRICCURRENT for flash or PMEM health alerts

If your Exadata X8M database is hanging, immediately generate an oradebug hanganalyze and check the alert log for critical events like ORA-00070 (deadlock), memory leaks, or storage-offline events

For a methodical, Exadata-specific approach, analyze the following components in order to pinpoint the bottleneck:

1. The Alert Log & Trace Files

ORA-Errors: Look for sequence errors such as ORA-04031 (shared pool exhaustion) or checkpoint not complete messages (ORA-00316).
Exadata Cell Alerts: Check for storage-related hardware errors, flash cache failures, or quorum disk drops.
Trace File Analyzer (TFA): Run tfactl diagcollect -since 1h to grab all relevant logs across the grid infrastructure

2. Foreground & Background Wait Events

Review V$SESSION_WAIT and V$SYSTEM_EVENT to see where the system is blocked

Log File Sync / Log File Parallel Write: These indicate log writer (LGWR) stalls. In an Exadata X8M, which features RDMA and Smart PMEM Log, high wait times indicate a failure in the persistent memory tier, interconnect networking, or cluster lock contention.

Cell Single Block Physical Read: If latency is excessively high, it means reads are bypassing the flash cache and hitting the slower HDDs

3. Exadata-Specific Metrics (using cellcli)

Log onto your storage cells (via dcli or cellcli) to verify hardware and cache integrity

PMEM / XRMEM Cache: Verify the Smart PMEM cache status. Issues here can stall the database.

Flash Log Stalls: Verify that flashlog performance is healthy, as it directly impacts commit processing.

I/O Resource Management (IORM): Check if an IORM plan or category is causing a specific database/PDB to suffer from I/O starvation

4. Grid Infrastructure & Clusterware

Hung Cluster: Run crsctl check cluster -all to ensure the cluster nodes are communicating.
Interconnect: A hang is often triggered by network drops. Review the interconnect (private network) for packet drops or latency issues. Exadata X8M uses RoCE (RDMA over Converged Ethernet); check for switch port flaps.

5. CPU & Memory

Check operating system stats (top, vmstat) to see if you are facing CPU starvation (runqueue size) or memory swapping (pi/po).
In the database, check for latch free or buffer busy waits (due to unoptimized SQL or heavy concurrency).

To help narrow down the cause and provide a specific mitigation, tell me:

What specific wait events are currently showing as the highest in V$SESSION?
Are there any ORA- errors printed in the alert log right before the freeze began?
Is this a Single-Instance database or a RAC (Real Application Clusters) environment?

1. Alert Log and Trace File Locations

Review the database and cluster diagnostic logs to trace the root cause:

Database Alert Log: Usually located in $ORACLE_BASE/diag/rdbms/{DB_NAME}/{SID}/trace/alert_{SID}.log.
Hang Manager Logs: Look for messages containing ORA-32701 or dia0 background process trace files in $ORACLE_BASE/diag/rdbms/{DB_NAME}/{SID}/incident/incdir_*.
Exadata Cell Alert Log: Verify Exadata storage server health by checking /opt/oracle/cell/log/diag/asm/cell/{cell_name}/trace/alert.log.

2. What to Check and Analyze in the Logs

Scan the alert logs for specific signatures during the time of the hang:

Log Write/Commit Bottlenecks: Look for checkpoint not complete or LGWR wait for redo copy messages, which could point to I/O stalls.
PMEM / RoCE Issues: The Exadata X8M relies on Smart PMEM (Persistent Memory) for fast commits and RDMA over Converged Ethernet. Check for errors related to PMEM hardware faults or network fabric stalls.
OOM (Out of Memory): Look for memory allocation failures or ORA-04030 / ORA-04031 errors

. Deeper Diagnostic Actions

If the alert log points to a hang, use database-level diagnostics to extract specific data: [1]

System State Dump: Execute oradebug dump systemstate 266 to get a precise snapshot of all processes and what they are waiting for.
Hang Analyzer: Run oradebug hanganalyze 3 to identify the blocking and waiting process chains.
AWR & ASH: If you can still log in, generate an AWR report or query the Active Session History (ASH) to review the top wait events

Question: How to enable Data Guard Oracle on Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

To enable Oracle Data Guard on Exadata Cloud Infrastructure (ExaCS) and Exadata Database Service on Dedicated Infrastructure (ExaDB-D), you can use the Oracle Cloud Infrastructure (OCI) Console. The process involves selecting your primary database and adding a standby database to create a Data Guard association or group

Steps to Enable Data Guard

Navigate to the Primary Database:
- Open the OCI navigation menu and go to Oracle Database, then select Exadata on Oracle Public Cloud (ExaDB-D) or Exadata Cloud@Customer (ExaCS).
- Select the Compartment and the VM Cluster containing the primary database.
- Click the name of the specific Database you want to protect.
Add a Standby Database:

Under the Resources section on the left, click Data Guard Associations (or Data Guard Group for newer versions like 19c+).
Click Add Standby or Enable Data Guard

Configure the Standby Settings:
- Select Peer VM Cluster: Choose the target region, availability domain, and the destination VM Cluster where the standby will reside.
- Data Guard Type: Select either Data Guard (standard) or Active Data Guard (requires additional licensing for features like real-time query).
- Protection Mode: Choose Maximum Performance (asynchronous) or Maximum Availability (synchronous).
- Database Credentials: Enter the SYS password for the primary database to authorize the creation.
Finalize and Monitor:

(Optional but recommended) Click Run Precheck to ensure the environment is ready before proceeding.
Click Add Standby or Enable Data Guard to start the provisioning process.
Monitor the progress via the Work Requests page. Once completed, the database role will reflect its new status (Primary or Standby)

Key Requirements & Best Practices

Infrastructure: For maximum fault isolation, configure the standby on a different Exadata Infrastructure than the primary.
Software Versions: Both the primary and standby VM Clusters must have identical DBaaS Tools and Agent versions.
Network: Ensure proper security rules are in place to allow network communication between the primary and standby client subnets

For more details

https://docs.oracle.com/iaas/exadatacloud/exacs/using-data-guard-with-exacc.html

Set up Oracle Data Guard Configuration between Databases on Oracle Exadata Database Service on Dedicated Infrastructure and Oracle Exadata Database Service on Exascale Infrastructure

Question : what is wait event gc cr block 2-way and gc current block 2-way and gc cr block busy

In Oracle RAC databases, the gc cr block 2-way event signifies a Consistent Read (CR) block requested by one instance being transferred directly from another instance over the cluster interconnect, involving exactly two nodes (the requestor and the holder) and a single network hop.

While 2-way transfers are the most efficient form of Cache Fusion, high waits for this event point to heavy inter-instance block contention.

To effectively troubleshoot and reduce these waits:

Identify the Object: Run an AWR (Automatic Workload Repository) report to check the "Segments by Global Cache Cr Blocks" section. Pinpointing the exact table or index causing the block transfers is step one.
Application Partitioning: Segregate workloads so that sessions modifying data (DML) run on the same instance that queries (SELECT) that same data, localizing block access and eliminating cross-node chatter.
Re-evaluate Index Usage: Frequent full-table scans or heavy index maintenance can trigger high block transfers. Optimize queries to use more localized or partitioned data access paths.
Optimize Interconnect: Ensure your cluster interconnect network is fast, reliable, and not acting as a bottleneck

The Oracle RAC wait event gc current block 2-way occurs when a session requests a data block in "Current" (DML/Exclusive) mode, and the block is transferred directly from a remote instance in 2 network hops (Requesting Instance $\rightarrow $ Holding Instance $\rightarrow $ Requesting Instance)

Meaning & Context

Current Mode: Indicates a request for the current block data (typically for DML like UPDATE, INSERT, DELETE, or SELECT FOR UPDATE) rather than a Consistent Read (CR) snapshot.
2-Way Transfer: The block is found in the cache of exactly one other remote instance and is sent directly over the interconnect. No third master instance is required for the transfer.
Normal Operation: In an Oracle RAC environment, block transfers are standard. This wait event alone does not necessarily indicate a problem, unless the wait times or total waits are excessively high and degrading performance

How to Diagnose and Tune

If this wait event is causing performance bottlenecks, it usually points to data/index contention across your cluster nodes. You can address it using the following steps:

Identify the Hot Objects: Use V$ACTIVE_SESSION_HISTORY or the Oracle AWR Report to find the specific segments (tables/indexes) associated with the waits.

Reduce Index Contention: Heavy INSERT operations (like appending to sequences) can cause "hot" blocks at the ends of indexes. Consider using partitioned indexes or increasing the number of sequence cache entries (e.g., CACHE 1000 NOORDER).

Tune Block Density: Increase PCTFREE on tables with high concurrency to reduce the number of rows per block. This helps minimize multiple instances hitting the exact same physical block simultaneously.

Review Cluster Interconnect: If the wait times are high, check the network infrastructure. Ensure your private interconnect is on a dedicated, high-bandwidth (10GbE or higher) network and verify that no network packets are being dropped

gc cr block busy :

The gc cr block busy is an Oracle RAC (Real Application Clusters) wait event indicating that a session requested a consistent read (CR) block, but the block transfer between instances was delayed. This means there is high contention for a "hot block" across nodes

Why Does It Happen?

Remote Pinning: The remote instance holding the block is actively modifying it (e.g., locking, updating) or has not yet finished writing its redo logs for that transaction.
Log Flush Delays: The transfer is held up because the holding instance cannot write to the online redo logs quickly enough.
Contention: Multiple instances are requesting the same block simultaneously

How to Diagnose & Fix

Identify the Hot Block: Use the V$SESSION_WAIT view to find the file and block number causing the waits (using parameters P1 and P2).
Find the Object: Map the file/block to a specific database table or index using DBA_EXTENTS.
Tune the Application:
- Optimize SQL queries to reduce large full table or index scans that span across nodes.
- If a single block holds too many small rows (e.g., sequence generators), consider increasing the cache size or partitioning the table.
Check I/O Performance: Review your redo log write times. If you see high log file sync waits alongside this event, your disk group for redo logs may be experiencing I/O bottlenecks

Command used

SRDC - Exadata Generic Required Diagnostic Data Collection for RMAN Duplicate (Doc ID 2658991.1)

=================

Please Upload a text file with the output for the following as root user.

Replace <dbname> with the database name having issue:

curl -v -X HEAD -u '<username>':'<passwd>' bkup_oss_url

curl -v -u <username>':'<passwd>' -s http://169.254.169.254/opc/v1/instance/ | egrep -v "user_data|ssh_authorized_keys|timeCreated"

rpm -qa | egrep -i 'dbaastools|dbaastools_exa|dcs' | cut -d- -f1

rpm -qa | grep dbaas

rpm -qa --last |egrep 'dbcs|dbaas|dtrs|dcs'

dbaascli patch tools list

dbaascli admin showLatestStackVersion

/var/opt/oracle/misc/platforminfo

/usr/local/bin/imageinfo

Upload /var/opt/oracle/creg/<dbname>.ini

hostname -f

dbaascli cswLib listLocal

dbaascli tde status --dbname <dbname>

dbaascli database verify_wallet --dbname <dbname>

dbaascli database getDetails --dbname <dbname>

dbaascli database backup --dbname <dbname> --getSchedules

dbaascli database backup --getConfig --dbName <dbname> --configFile /tmp/<dbname>_cfg.txt

dbaascli database backup --dbname <dbname> --list

dbaascli database backup --dbName <dbname> --showHistory --all

dbaascli database backup --dbname <dbname> --status --uuid <uuid from above for the failed job>

dbaascli database backup --getLatestBackupJob --dbname <dbname>

Collect logs specific to database and covering issue time as below

dbaascli diag collect --startTime <Format: YYYY-MM-DDTHH24:MM:SS> --endTime <Format: YYYY-MM-DDTHH24:MM:SS> --dbNames <dbname>

The command needs to be executed as the "root" user. Identify the timestamp of the failure and collect 2 hours before and 2 hours after covering the issue timeframe.

References :

SRDC - Exadata Cloud Mandatory Data Collection for Backup Cloud Services (Backup, Restore, Recovery) Issues (Doc ID 2886934.1)

Please zip & upload the logs

1) /var/opt/oracle/log/<dbname>

2) /opt/oracle/dcs/log/

3) /var/opt/oracle/log/dtrs/

===============================================================

TFA report collection from nodes that experienced the issue.

./tfactl diagcollect -from "Feb/05/2025 02:00:00" -to "Feb/05/2025 07:00:00"

sosreport

as root

#sosreport

Exawatcher report

To collect from/to a certain date and time:

example:

# ./GetExaWatcherResults.sh --from 01/25/2025_13:00:00 --to 01/25/2025_14:00:00

exacli cloud_user_syd1990clu03173@100.107.0.9> list ALERTHISTORY

See exacli_cell_ALERTHISTORY.txt for full deta

=====================================================================

What is the status of database right now ? Is it accessible to you ?

Provide below

srvctl config database -d <db_unique_name>

srvctl status database -d <db_unique_name>

Please do below on the standby database

srvctl stop database -d FMWROOT_iad1dg

srvctl start database -d FMWROOT_iad1dg

Then retry the the precheck

Patching fails with same error as below even after stopping and starting via srvctl ?

DCS-10061:Database FMWROOT is not running. Database is not running on node : ocivpsysofmw151

Also provide below

crsctl stat res -t

Looks like patch is applied on one of the nodes already

Please provide below from both nodes

opatch lsinv -detail

If node 1 and node 2 is already patched then why Grid Installed version is showing 19.23 instead of 19.25.

[root@ocivpsysofmw151 ~]# dbcli describe-component

System Version

---------------

25.1.1.0.0

Component Installed Version Available Version

---------------------------------------- -------------------- --------------------

GI 19.23.0.0.0 19.26.0.0

DB 19.23.0.0.0 19.26.0.0

[root@ocivpsysofmw152 ~]# dbcli describe-component

System Version

---------------

25.1.1.0.0

Component Installed Version Available Version

---------------------------------------- -------------------- --------------------

GI 19.23.0.0.0 19.26.0.0

DB 19.23.0.0.0 19.26.0.0

Please get me the output of below from both nodes

sudo su - grid

$ORACLE_HOME/OPatch/opatch lspatches

I can see you executed :

/opt/oracle/dcs/bin/dbcli update-server -p -v 19.25.0.0 -l

Please do as below on the second node ,as previous command is missing one 0

sudo su -

dbcli update-server -p -v 19.25.0.0.0 -l

dbcli describe-job -i <Prechecks_job_ID>

if successful ,do

dbcli update-server -v 19.26.0.0.0 -l

dbcli describe-job -i <job_ID>

Is that job from when you executed below

dbcli update-server -v 19.25.0.0.0 -l

If yes ,please get me

Job log

/opt/oracle/dcs/log/jobs/<JOBID>.log

Database alert log

/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_<sid>.log

==================Exadata =======================

ACTION PLAN

------------------------

Please check if CRS is up and running on all nodes. Execute below commands in all nodes and update us.

# <GI_HOME>/bin/crsctl check crs

# <GI_HOME>/bin/crsctl check cluster -all

# <GI_HOME>/bin/crsctl stat res -t# <GI_HOME>/bin/crsctl query css votedisk

Please execute below command in all nodes and upload the file "crsctl_stat_<Host Name>.out"

# <GI_HOME>/bin/crsctl stat res -t > crsctl_stat_<Host Name>.out

Please run the tfactl with "-all" argument to collect diagnostic collection from the database nodes when the issue had occur.

Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1

Use TFA Collector - Tool for Enhanced Diagnostic Gathering (Doc ID 1513912.1)

Under GRID HOME/tfa/bin

run as root

# /opt/oracle.ahf/tfa/bin/tfactl diagcollect -all -noclassify -node local -from "2024-07-30 16:00:00" -to "2024-07-29 18:30:00"

***Please change the time plus and minus 4 hours to your problem window ***

Action Plan

===========

1. Please try to cleanup socket files and try to start the CRS

---

A. Stop the CRS and all related resources on problem node:

# crsctl stop crs -f

# ps -ef | grep d.bin

> If any "d.bin" process remain running from the GRID_HOME, kill them:

# kill -9 <d.bin_pid>

B. Remove the file in the "/etc/oracle/maps" location:

# rm -rf /etc/oracle/maps/*

C. Remove the socket files;

# rm -rf /var/tmp/.oracle/*

D. Remove the "gipc" files in the location "/u01/app/grid/crsdata/*/output/"

# rm -rf /u01/app/grid/crsdata/node1/output/*

E. Start CRS

# crsctl start crs

---

2. Then upload TFA from both the nodes covering the time of CRS startup.

To Collect TFA

==============

# /opt/oracle.ahf/tfa/bin/tfactl diagcollect -all -noclassify -node local -from "2024-07-30 16:00:00" -to "2024-07-29 18:30:00"

***Please change the time plus and minus 4 hours to your problem window ***

Hi,

Modify the permission as below.

# chown grid:dbmusers /etc/oracle/cell/network-config/cellinit.ora

Retry CRS startup. Let me know the outcome.

Please provide me output of below command in text file for review.

$ cluvfy comp software -n all -verbose

Execute this command as grid user.

Thanks,

Kindly follow below document and restore the permissions from good node.

Script to capture and restore file permission in a directory (for eg. ORACLE_HOME) (Doc ID 1515018.1)

Kindly follow below doc to reset the file permissions:

How to check and fix file permissions on Grid Infrastructure environment (Doc ID 1931142.1)

1. Stop CRS on the problem node

crsctl stop crs -f

2. Reset the permissions of all files and directories under Oracle <GRID_HOME>.

For 12c and above:

For clustered Grid Infrastructure, as root user

# cd <GRID_HOME>/crs/install/

# ./rootcrs.sh -init

3. Start CRS

crsctl start crs -wait

Please share the results. If it doesnt work then we need to restore the permissions from good node.

Kindly provide output of below from both the nodes.

ls -lrt /u01/app/19.0.0.0/grid/lib/libserver19.a

Can you please remove all the socket files and reboot the problematic node?

Kindly change the permission to chmod 755 /u01/app/19.0.0.0/grid/lib/libserver19.a and share the complete result of below commands:

# crsctl stop crs -f

# crsctl start crs -wait

=============================================

1. When did the problem start?

The problem occurred only once, April 10th, and after restarting the process ran fine, and it ran every day after the incident without any errors as well.

2. Did this work before?

This job work before, and as I tell you is working fine after this problem, error has occurred only in one execution, but if we don't know the cause, we can't avoid the same error in the future.

3. How often is it reoccurring?

The job is executed daily during last month, and only April 10th job failed.

4 have you performed any recent activity?

No activity performed.

Please share below details

Question: Please select your Oracle Database version.

19.19.0

Question: What is your Tenancy OCID?

Question: What is your Database System OCID?

database is in a exadata vm cluster with OCID:

ocid1.cloudvmcluster.oc1.eu-frankfurt-1.antheljrkbqa6viaypdjrfcozvb6zrkdvxdes7gi433zdqcw7lemkut3ypzq

Question: What is the region where the Database System was created?

Availability domain: jyBc:EU-FRANKFURT-1-AD-1

Question: Please provide a summary of the issue faced.

As described int his SR, we have a daily job to execute a db duplicate, and April 10th job failed with this errors

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 04/10/2025 02:16:04

RMAN-05501: aborting duplication of target database

RMAN-06136: Oracle error from auxiliary database: ORA-06550: line 1, column 42:

PLS-00553: character set name is not recognized

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

after reexecute the process, it finished ok.

We need know the cause of the error to try to prevent it to happening again.

you can see attached log of the db duplicate and the alert log of the target database.

Duplicate database is executed with this command:

dbaascli database delete --dbname CDELSM2P > $LOG_SQL/delete_duplicate_$FECHA.log

dbaascli database duplicate --dbName CDELSM2P --dbUniqueName CDELSM2P_x73_fra --sourceDBConnectionString oc-pro-exa-04-clu-02-hdfdi-scan.prodb03.ocpro.oraclevcn.com:1521/s_delta_smile_clone_pro.prodb03.ocpro.oraclevcn.com --oracleHome /u02/app/oracle/product/19.0.0.0/dbhome_1 --sourceDBTDEWalletLocation /home/oracle/scripts/CDELSM2P/duplicate/ewallet.p12 --sourceDBTdeConfigMethod FILE --tdeConfigMethod FILE --rmanParallelism 64 --rmanSectionSizeInGB 64 --waitForCompletion false < /home/oracle/scripts/CDELSM2P/duplicate/pwd_duplicate > $REPLICA_DIR/duplicate.lck

1 ) Can you please update if the environment / Database is OCI or OCI Classic or Exadata or Autonomous or On-Prem ?

is Exadata

2) If OCI Base database , Can you please share the output of below commands:

ssh to DB node

sudo su -

hostname

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# hostname

oc-pro-exa-03-rep-03-ugwlh1

date

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# date

Mon Apr 14 18:44:23 CEST 2025

uptime

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# uptime

18:44:35 up 95 days, 4:07, 5 users, load average: 1.68, 1.95, 2.06

last|grep reboot

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# last|grep reboot

df -h

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 63G 0 63G 0% /dev

tmpfs 126G 2.3G 124G 2% /dev/shm

tmpfs 63G 9.9M 63G 1% /run

tmpfs 63G 0 63G 0% /sys/fs/cgroup

/dev/mapper/VGExaDb-LVDbSys1 15G 8.6G 6.4G 58% /

/dev/mapper/VGExaDb-LVDbKdump 20G 175M 20G 1% /crashfiles

/dev/mapper/VGExaDbDisk.u01.20.img-LVDBDisk 20G 4.8G 16G 24% /u01

/dev/mapper/VGExaDbDisk.grid19.0.0.0.241015.img-LVDBDisk 50G 12G 39G 24% /u01/app/19.0.0.0/grid

/dev/mapper/VGExaDb-LVDbVar1 20G 2.9G 18G 15% /var

/dev/mapper/VGExaDb-LVDbTmp 3.0G 67M 2.9G 3% /tmp

/dev/sda1 412M 118M 295M 29% /boot

/dev/mapper/VGExaDb-LVDbVarLog 18G 1.3G 17G 7% /var/log

/dev/mapper/VGExaDb-LVDbVarLogAudit 3.0G 173M 2.8G 6% /var/log/audit

/dev/mapper/VGExaDbDisk.u02_extra.img-LVDBDisk 124G 69G 49G 59% /u02

/dev/mapper/VGExaDb-LVDbHome 4.0G 84M 4.0G 3% /home

oc-pro-mt-com-01.proappfss.ocpro.oraclevcn.com:/oc-pro-oem-exa-03-rep-03-ugwlh1-fss 8.0E 2.8G 8.0E 1% /u01/app/oracle/product/13.1.0

/dev/asm/acfsvol01-39 720G 25G 696G 4% /acfs01

tmpfs 13G 0 13G 0% /run/user/2000

oc-pro-mt-com-01.proappfss.ocpro.oraclevcn.com:/oc-pro-fss-migraciones-01 8.0E 6.4T 8.0E 1% /oc-pro-fss-migraciones-01

tmpfs 13G 0 13G 0% /run/user/1000

tmpfs 13G 0 13G 0% /run/user/1001

free -h

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# free -h

total used free shared buff/cache available

Mem: 125Gi 86Gi 24Gi 2.1Gi 14Gi 30Gi

Swap: 15Gi 2.4Gi 13Gi

hostnamectl

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# hostnamectl

Static hostname: oc-pro-exa-03-rep-03-ugwlh1

Icon name: computer-vm

Chassis: vm

Machine ID: 61913792fabd4df9a340bd5be6dad5cb

Boot ID: 83e3117624444056bb8fa545d5886819

Virtualization: kvm

Operating System: Oracle Linux Server 8.10

CPE OS Name: cpe:/o:oracle:linux:8:10:server

Kernel: Linux 5.4.17-2136.330.7.5.el8uek.x86_64

Architecture: x86-64

ps -fel | egrep "smon|tns" | sort -k 15

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# ps -fel | egrep "smon|tns" | sort -k 15

4 S root 10431 1 4 30 - - 433249 hrtime Mar24 ? 21:07:47 /u01/app/19.0.0.0/grid/bin/osysmond.bin

0 S grid 49886 1 0 80 0 - 67943 ep_pol Jan09 ? 00:36:42 /u01/app/19.0.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit

0 S grid 50261 1 0 80 0 - 68744 ep_pol Jan09 ? 00:40:02 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit

0 S grid 70119 1 0 80 0 - 68560 ep_pol Jan09 ? 00:11:10 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit

0 S grid 70113 1 0 80 0 - 68533 ep_pol Jan09 ? 00:11:09 /u01/app/19.0.0.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit

1 I root 37 2 0 60 -20 - 0 rescue Jan09 ? 00:00:00 [netns]

0 S grid 43896 1 0 80 0 - 903727 do_sem Jan09 ? 00:02:26 asm_smon_+ASM1

0 S root 67545 48036 0 80 0 - 2321 pipe_w 18:46 pts/3 00:00:00 grep -E --color=auto smon|tns

0 S oracle 48508 1 0 80 0 - 1841535 do_sem 06:35 ? 00:00:03 ora_smon_CDELSM2P1

0 S oracle 134699 1 0 80 0 - 1834853 do_sem Jan13 ? 00:04:20 ora_smon_CDELTA3P1

0 S oracle 128789 1 0 80 0 - 1899898 do_sem Jan13 ? 00:04:21 ora_smon_CDELTA4P1

cat /etc/oracle-release

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# cat /etc/oracle-release

Oracle Linux Server release 8.10

uname -a

[root@oc-pro-exa-03-rep-03-ugwlh1 ~]# uname -a

Linux oc-pro-exa-03-rep-03-ugwlh1 5.4.17-2136.330.7.5.el8uek.x86_64 #3 SMP Mon May 27 12:51:19 PDT 2024 x86_64 x86_64 x86_64 GNU/Linux

cd /opt/oracle/dcs/bin

./dbcli list-dbhomes

N/A exadata

/opt/oracle/dcs/bin/dbcli describe-component

N/A exadata

./dbcli list-databases -j

N/A exadata

/opt/oracle/dcs/bin/dbcli list-pdbs -i $(/opt/oracle/dcs/bin/dbcli list-databases|awk 'NR==4 {print $1}')

N/A exadata

dbcli list-pdbs -i 512eb207-b4ff-4145-83e6-0212e08d8f3e

N/A exadata

dbcli describe-pdb -i 512eb207-b4ff-4145-83e6-0212e08d8f3e -n <PDB_NAME>

N/A exadata

./dbcli describe-database -in <db_name>

N/A exadata

/opt/oracle/dcs/bin/dbcli list-jobs -f `date --date='-3 day' '+%Y-%m-%d'`

N/A exadata

dbcli list-jobs|grep -i <dbname>

N/A exadata

not the last job ID listed with a status other than success

with the job ID you noted above check the details of that jobs

/opt/oracle/dcs/bin/dbcli list-jobs | grep 'Failure'

dbcli describe-job -i <id of failed job>

dbcli describle-job -i <job_ID> -j

N/A exadata

# /opt/oracle/dcs/bin/dbcli describe-job -i <failed_job_id> -l Verbose

Share log file ==> /opt/oracle/dcs/log/jobs/<failed_job_id>.log

/opt/oracle/dcs/log/dcs-agent.log

/opt/oracle/dcs/log/dcs-agent-debug.0.0.log

export DEVMODE=true

dbcli list-dbrs

dbcli list-vmshapes

sudo su - grid

crsctl check crs

[grid@oc-pro-exa-03-rep-03-ugwlh1 ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

crsctl stat res -t

[grid@oc-pro-exa-03-rep-03-ugwlh1 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATAC2.ACFSVOL01.advm

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.LISTENER.lsnr

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.chad

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.datac2.acfsvol01.acfs

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlmounted on /acfs01,S

h1 TABLE

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlmounted on /acfs01,S

h2 TABLE

ora.net1.network

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.ons

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.proxy_advm

ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.DATAC2.dg(ora.asmgroup)

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.RECOC2.dg(ora.asmgroup)

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.asm(ora.asmgroup)

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlStarted,STABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlStarted,STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.ccosmm2p_xxx_fra.db

1 OFFLINE OFFLINE STABLE

2 OFFLINE OFFLINE STABLE

ora.cdelsm2p_x73_fra.cdelsm2p_pdelsmip.paas.oracle.com.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelsm2p_x73_fra.db

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h1 racle/product/19.0.0

.0/dbhome_1,STABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h2 racle/product/19.0.0

.0/dbhome_1,STABLE

ora.cdelsm2p_x73_fra.s_delta_smile_batch_des.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelsm2p_x73_fra.s_delta_smile_online_des.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelta3p_5fc_fra.cdelta3p_pdelta3p.paas.oracle.com.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelta3p_5fc_fra.db

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h1 racle/product/19.0.0

.0/dbhome_2,STABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h2 racle/product/19.0.0

.0/dbhome_2,STABLE

ora.cdelta3p_5fc_fra.s_delta_bi_pro.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelta4p_86p_fra.cdelta4p_pdelta4p.paas.oracle.com.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelta4p_86p_fra.db

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h1 racle/product/19.0.0

.0/dbhome_2,STABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlOpen,HOME=/u02/app/o

h2 racle/product/19.0.0

.0/dbhome_2,STABLE

ora.cdelta4p_86p_fra.s_delta_batch_des.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cdelta4p_86p_fra.s_delta_online_des.svc

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

2 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.cvu

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.oc-pro-exa-03-rep-03-ugwlh1.vip

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.oc-pro-exa-03-rep-03-ugwlh2.vip

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.qosmserver

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.scan1.vip

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.scan2.vip

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

ora.scan3.vip

1 ONLINE ONLINE oc-pro-exa-03-rep-03-ugwlSTABLE

--------------------------------------------------------------------------------

sudo su - oracle

ps -ef|grep pmon

[oracle@oc-pro-exa-03-rep-03-ugwlh1 ~]$ ps -ef|grep pmon

grid 43585 1 0 Jan09 ? 00:07:38 asm_pmon_+ASM1

oracle 48281 1 0 06:35 ? 00:00:02 ora_pmon_CDELSM2P1

grid 50695 1 0 Jan09 ? 00:07:59 apx_pmon_+APX1

oracle 97283 95270 0 18:49 pts/4 00:00:00 grep --color=auto pmon

oracle 128610 1 0 Jan13 ? 00:09:41 ora_pmon_CDELTA4P1

oracle 132336 1 0 Jan13 ? 00:09:23 ora_pmon_CDELTA3P1

srvctl status database -d $ORACLE_UNQNAME

[oracle@oc-pro-exa-03-rep-03-ugwlh1 ~]$ srvctl status database -d $ORACLE_UNQNAME

Instance CDELSM2P1 is running on node oc-pro-exa-03-rep-03-ugwlh1

Instance CDELSM2P2 is running on node oc-pro-exa-03-rep-03-ugwlh2

srvctl config database -d $ORACLE_UNQNAME

[oracle@oc-pro-exa-03-rep-03-ugwlh1 ~]$ srvctl config database -d $ORACLE_UNQNAME

Database unique name: CDELSM2P_x73_fra

Database name: CDELSM2P

Oracle home: /u02/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATAC2/CDELSM2P_X73_FRA/PARAMETERFILE/spfile.336.1198390307

Password file: +DATAC2/CDELSM2P_X73_FRA/PASSWORD/pwdcdelsm2p_x73_fra.320.1198375667

Domain: prodb.ocpro.oraclevcn.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATAC2,RECOC2

Mount point paths:

Services: CDELSM2P_PDELSMIP.paas.oracle.com,s_delta_smile_batch_des,s_delta_smile_online_des

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: racoper

Database instances: CDELSM2P1,CDELSM2P2

Configured nodes: oc-pro-exa-03-rep-03-ugwlh1,oc-pro-exa-03-rep-03-ugwlh2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

sqlplus / as sysdba

SET LINESIZE 200

alter session set NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

SELECT NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,DB_UNIQUE_NAME,PRIMARY_DB_UNIQUE_NAME,CON_ID FROM V$DATABASE;

select instance_name,version,startup_time from v$instance;

select banner_full from v$version;

show pdbs;

SQL>

NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME CON_ID

--------- -------------------- -------------------- -------------------- ---------------- ------------------------------ ------------------------------ ----------

CDELSM2P READ WRITE MAXIMUM PERFORMANCE UNPROTECTED PRIMARY CDELSM2P_x73_fra 0

SQL>

INSTANCE_NAME VERSION STARTUP_TIME

---------------- ----------------- ------------------

CDELSM2P1 19.0.0.0.0 14-APR-25 06:35:38

SQL>

BANNER_FULL

----------------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

SQL>

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDELSMIP READ WRITE NO

Diagnostic and Troubleshooting Commands

dbaascli diag run -u: Collects guest VM DBaaS tooling logs and runs a health check to analyze failed automation tasks.

dbaascli database backup --dbname <db> --showHistory: Lists backup job IDs and history to trace broken RMAN or object storage backups.

cat /var/opt/oracle/log/dtrs/jobs/<job_id>.log: Inspects specific background execution logs when a cloud job fails.

Lifecycle and General Management

sudo dbaascli database changepassword --dbname <db> --user SYS: Updates internal SYS user credentials when out of sync with tooling.

sudo dbaascli tde changepassword --dbname <db>: Changes Transparent Data Encryption wallet passwords.

dbaascli database status --dbname <db>: Displays current database open mode and cluster deployment details.

dbaascli database bounce --dbname <db>: Shuts down and restarts database instances locally. [

Patching and Recovery Operations

dbaascli dbpatchm --help: Manages Grid Infrastructure and Database Home updates or rollbacks.

dbaascli database update --dbname <db> --setParameters <params>: Modifies database parameters in batch mode via command line

Useful ExaCC Commands

The dbaascli cswlib list command displays available Oracle Database software images and bundle patches ready for download in Oracle Exadata Cloud Service or Cloud at Customer environments. Run it as the root user via SSH on your compute node.

How to Run the Command

Connect via SSH as opc

Switch to root: sudo -s

Execute: dbaascli cswlib list

Useful Related Commands

dbaascli cswlib download: Downloads a chosen software image.

dbaascli cswlib showImages --product database: Filters available database software versions.

dbaascli dbhome create: Creates a new Oracle Home from available binaries.

Database Lifecycle Commands

dbaascli database start --dbName <name>: Starts and opens the specified database.

dbaascli database stop --dbName <name>: Shuts down the database.

dbaascli database bounce --dbName <name>: Restarts (stops and starts) the database.

dbaascli database status --dbName <name>: Checks the open mode and status of database deployments.

dbaascli database changepassword: Changes passwords for default database users.

Oracle Home & Software Management

dbaascli dbhome info: Displays details about installed Oracle Homes.

dbaascli dbhome create: Provisions a new Oracle Home directory.

dbaascli dbhome purge: Cleans up and deletes an unused Oracle Home.

dbaascli cswlib list: Lists available database software images for deployment.

dbaascli cswlib download: Downloads a specific software image to the environment.

Backup and Patching Operations

dbaascli backup --getConfig: Views current backup configurations and setups for databases.

dbaascli database update: Applies internal configuration changes or updates.

dbaascli database upgrade: Upgrades the core database software release

EasyReliableDBA

Monday, 25 May 2026

How to administer and patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D And Commands

Question : How to patch guest VM (DomU) and physical hypervisor (Dom0)

Question : do we patch storage server on (ExaCS) and ExaDB-D

Question : what is general issue on patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : How to administer Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : what will you check and analyze in exadata through AWR

Question : what will you check and analyze in exadata x8m through AWR

Question : what will you check and analyze in exadata x8m if database is getting hanged

bash
`dcli -c cell01,cell02,cell03 "cellcli -e list pmemdisk attributes name,status"`

4. Fabric / Network (RoCE) Checks
Exadata X8M uses RDMA over Converged Ethernet (RoCE) for cluster communication and storage access. A network degradation here will look like a database hang

Are you getting any specific wait events (e.g., `cell single block physical read`)?
Have you checked the Exadata Alert Log (`/opt/oracle.SupportTools/em/cell_alert_log`)?
Is this a total database freeze or a severe performance slowdown?

Question: How to enable Data Guard Oracle on Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : what is wait event gc cr block 2-way and gc current block 2-way and gc cr block busy

gc cr block busy :

Command used

Useful ExaCC Commands

No comments:

Post a Comment

Search This Blog

Monday, 25 May 2026

How to administer and patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D And Commands

Question : How to patch guest VM (DomU) and physical hypervisor (Dom0)

Question : do we patch storage server on (ExaCS) and ExaDB-D

Question : what is general issue on patch Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : How to administer Oracle Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : what will you check and analyze in exadata through AWR

Question : what will you check and analyze in exadata x8m through AWR

Question : what will you check and analyze in exadata x8m if database is getting hanged

bashdcli -c cell01,cell02,cell03 "cellcli -e list pmemdisk attributes name,status"

4. Fabric / Network (RoCE) ChecksExadata X8M uses RDMA over Converged Ethernet (RoCE) for cluster communication and storage access. A network degradation here will look like a database hang

Are you getting any specific wait events (e.g., cell single block physical read)?Have you checked the Exadata Alert Log (/opt/oracle.SupportTools/em/cell_alert_log)?Is this a total database freeze or a severe performance slowdown?

Question: How to enable Data Guard Oracle on Exadata Cloud Infrastructure (ExaCS) and ExaDB-D

Question : what is wait event gc cr block 2-way and gc current block 2-way and gc cr block busy

gc cr block busy :

Command used

Useful ExaCC Commands

No comments:

Post a Comment

bash
`dcli -c cell01,cell02,cell03 "cellcli -e list pmemdisk attributes name,status"`

4. Fabric / Network (RoCE) Checks
Exadata X8M uses RDMA over Converged Ethernet (RoCE) for cluster communication and storage access. A network degradation here will look like a database hang

Are you getting any specific wait events (e.g., `cell single block physical read`)?
Have you checked the Exadata Alert Log (`/opt/oracle.SupportTools/em/cell_alert_log`)?
Is this a total database freeze or a severe performance slowdown?