Oracle Database - Troubleshooting Failure to Create New Data File

Background

• Operating System: AIX
• Database Version: Oracle 11.2.0.3 RAC

A customer reported issues with creating data files for a tablespace in their RAC environment. The error indicated insufficient space, even though the disk group had 1TB of free space.

1. Issue Analysis

The engineer quickly responded and, based on the customer’s information, initially suspected uneven data distribution as the root cause. On arrival, it was confirmed that the disk group was in NORMAL mode with disks of varying sizes: 80GB and 160GB. Some 80GB disks had only around 100MB of free space remaining.

The issue stemmed from using disks of different sizes in a NORMAL-mode disk group, which caused uneven data distribution and led to space exhaustion on some disks. Additionally, while 160GB disks were available, they could only utilize up to 80GB due to the disk group configuration, leading to significant space wastage. A review of change records revealed that, a month earlier, eight 160GB disks were added to the disk group.

Change Plan

To resolve the issue, the engineer proposed mapping sixteen 80GB disks from each storage unit to the disk group, then using ASM’s online disk management to add the new 80GB disks and remove the 160GB disks, followed by data rebalancing. The change operation was scheduled for a low-activity window (6:00 pm–11:00 pm) to minimize impact on business operations, which typically ran between 8:00 am–6:00 pm, with data imports occurring from 11:00 pm–6:00 am.

Change Execution

The process followed these steps:

1. Disk Mapping and Scanning: Mapped disks from Storage 1 and Storage 2, scanning and verifying each new disk path and WWID.
2. Setting Reserve Policy: Configured reserve policy to no_reserve.
3. Disk Setup: Checked disk size, ownership, and permissions.
4. ASM Disk Group Update: Used SQL commands to add the new disks to the disk group, remove the old disks, and initiate rebalancing.

Monitoring and RebalancingThe rebalancing operation was set at a POWER level of 7 to balance performance and business continuity, taking around five hours to complete. After rebalancing, the data distribution was confirmed to be even.

Key Takeaways

1. Uniform Disk Size in ASM Disk Groups: For NORMAL-type ASM disk groups, ensure uniform disk sizes to avoid space wastage and striping issues, as uneven disks can result in inefficient space usage and performance issues.
2. Change Validation: Any change operation should be carefully reviewed for its necessity, accuracy, and potential business impact, with testing in a pre-production environment wherever possible.