Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Exadata

Exadata and ASM

截止目前,对于Oracle Exadata数据库一体机来说,ASM磁盘组支持三种冗余Normal,High,Extend(在12.2用于支持Extend RAC)。从12.1.0.1开始,ASM磁盘组disk_repair_time从原来的3.6H调整为12H。在Note1551288.1中,Oracle提供了一个脚本(dg_space.sql)用于计算磁盘组的空间。对非Exadata环境也适用。感觉还挺有用,记录于此。

Disk Required Mirror Free MB

Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)
Disk Usable File MB

Usable space available after reserving space for disk failure and accounting for mirroring
PCT Util

Percent of Total Diskgroup Space Utilized
DFC

Disk Failure Coverage Check (PASS = able to rebalance after loss of single disk)

Exadata下的输出


非Exadata下的输出


asmcmd lsdg输出信息


Req_mir_free_MB

是指在出现单个或两个磁盘故障后(对于Normal或High冗余),重新平衡满足冗余度的定义,磁盘组中必须有多少空间可用。简单地说就是如果ASM磁盘或ASM磁盘组出现了故障,要重新满足冗余度的定义,需要有多少的空闲空间

Usable_file_MB

简单地说就是在满足冗余度之后,还有多少的剩余空间可以用。这个值可能会是负值


对Normal冗余模式来说计算公式是
USABLE_FREE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB)/2

对High冗余模式来说计算公式是
USABLE_FREE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB)/3

Exadata中ASM冗余模式

ASM disk groups in Exadata are defined as either normal, high, or extended redundancy (Extended redundancy is available in 12.2 for extended clusters.  External redundancy is NOT supported with Exadata storage). Normal redundancy provides for two copies of file extents (database files are stored as one or more "file extents" in ASM); high redundancy provides for three copies of file extents. Extended redundancy provides for four copies by default.  Each disk is partnered with a set of other disks in other failure groups to ensure that file extent copies are stored in separate failure groups and the disk group can tolerate the loss of one disk (or one cell) in normal redundancy or two disks (or two cells) in high redundancy disk groups. For extended redundancy diskgroups, the loss of an entire site's cells plus one more cell in a surviving site is tolerated. The choice of making a disk group either normal or high redundancy in Exadata depends on the following considerations:
1.Is Oracle Data Guard available and tested; are DBAs confident in switching to it and back? If Oracle Data Guard is available, administrators are confident in switchover and switchback, and the recovery time objectives (RTO) of the business can be met when switching over or back, then disk groups can be set as normal redundancy.
2.Does the business wish to maximize data protection and tolerance of failure during rolling cell patching? If patching, or other cell maintenance is required to be performed without downtime to the cluster, and Oracle Data Guard is not available, then high redundancy disk groups are recommended to maintain two copies of data at all times.  Otherwise with normal redundancy, only one copy of data will be available during the maintenance window; an unfortunate disk failure could result in the need to restore and recover databases.  If this risk is too high, then high redundancy would be a better choice.
对于Exadata一体机,可能有两种故障:
1.Disk failure coverage (DFC) will refer to having enough free space to allow data to be re-mirrored (rebalanced) after a single disk failure in a normal redundancy disk group, or single or dual disk failure in a high redundancy disk group.

For DFC, Oracle recommends having free space equal to or greater than 15% of the diskgroup capacity to enable rebalancing after the loss of a single disk.

DFC(Disk failure coverage )是指在Normal冗余磁盘组中的单个磁盘故障或High冗余磁盘组中的单个或两个磁盘故障后,有足够的空闲空间允许数据重新镜像(rebalance)。对于这种情况,通过一个脚本dg_space.sql可以计算剩余空间是否能容忍磁盘故障


2.Cell failure coverage (CFC) will refer to having enough free space to allow data to be re-mirrored after the loss of one entire cell.
Cell failures are extremely rare and rebalancing is typically not desired; it is better to simply repair the cell and bring it back online.

对于整个cell故障,这种情况发生的概率几乎为零。这个就不做讨论了。如果真的坏了,需要修复并恢复到联机状态。

dg_space.sql内容(略作修改

SET SERVEROUTPUT ON
SET LINES 155
SET PAGES 0
SET TRIMSPOOL ON

DECLARE
   v_space_reserve_factor NUMBER := 0.15;
   v_num_disks    NUMBER;
   v_group_number   NUMBER;
   v_max_total_mb   NUMBER;
   v_max_used_mb    NUMBER;
   v_fg_count   NUMBER;

   v_required_free_mb   NUMBER;
   v_usable_mb      NUMBER;
   v_cell_usable_mb   NUMBER;
   v_one_cell_usable_mb   NUMBER;
   v_enuf_free      BOOLEAN := FALSE;
   v_enuf_free_cell   BOOLEAN := FALSE;

   v_req_mirror_free_adj_factor   NUMBER := 1.10;
   v_req_mirror_free_adj         NUMBER := 0;
   v_one_cell_req_mir_free_mb     NUMBER  := 0;

   v_disk_desc      VARCHAR(10) := 'SINGLE';
   v_offset      NUMBER := 50;

   v_db_version   VARCHAR2(8);
   v_inst_name    VARCHAR2(1);

   v_cfc_fail_msg VARCHAR2(500);

BEGIN

   SELECT substr(version,1,8), substr(instance_name,1,1)    INTO v_db_version, v_inst_name    FROM v$instance;

   IF v_inst_name <> '+' THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: THIS IS NOT AN ASM INSTANCE!  PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.');
      GOTO the_end;
   END IF;

    DBMS_OUTPUT.PUT_LINE('------ DISK and CELL Failure Diskgroup Space Reserve Requirements  ------');
    DBMS_OUTPUT.PUT_LINE(' This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ');
    DBMS_OUTPUT.PUT_LINE(' available when reserving space for disk or cell failure (loss of cell is rare and not usually a concern).  ');
    DBMS_OUTPUT.PUT_LINE(' These required mirror and usable space assume space utilized to full capacity - a worst case condition.');
    DBMS_OUTPUT.PUT_LINE(' Please see MOS note 1551288.1 for more information.  ');
    DBMS_OUTPUT.PUT_LINE('.  .  .');
    DBMS_OUTPUT.PUT_LINE(' Description of Derived Values:');
    DBMS_OUTPUT.PUT_LINE(' Disk Required Mirror Free MB     : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)');
    DBMS_OUTPUT.PUT_LINE(' Disk Usable File MB              : Usable space available after reserving space for disk failure and accounting for mirroring');
    DBMS_OUTPUT.PUT_LINE(' PCT Util                         : Percent of Total Diskgroup Space Utilized');
    DBMS_OUTPUT.PUT_LINE(' DFC                              : Disk Failure Coverage Check (PASS = able to rebalance after loss of single disk)');
    
   DBMS_OUTPUT.PUT_LINE('.  .  .');

   DBMS_OUTPUT.PUT_LINE('ASM Version is '||v_db_version);


-- Set up headings
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------------------------------------------------');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|         ');
      DBMS_OUTPUT.PUT('|     ');
      DBMS_OUTPUT.PUT('|     ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|                ');
      DBMS_OUTPUT.PUT('|                ');
      DBMS_OUTPUT.PUT('|                ');
      DBMS_OUTPUT.PUT('|Disk Req''d      ');
      DBMS_OUTPUT.PUT('|                ');
      DBMS_OUTPUT.PUT('|       |');    
      DBMS_OUTPUT.PUT_LINE('    |');
      -- next row
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|DG       ');
      DBMS_OUTPUT.PUT('|Num  ');
      DBMS_OUTPUT.PUT('|Num  ');     
      DBMS_OUTPUT.PUT('|Disk Size   ');
      DBMS_OUTPUT.PUT('|DG Total        ');
      DBMS_OUTPUT.PUT('|DG Used         ');
      DBMS_OUTPUT.PUT('|DG Free         ');
      DBMS_OUTPUT.PUT('|Mirror Free     ');
      DBMS_OUTPUT.PUT('|Disk Usable     ');
      DBMS_OUTPUT.PUT('|PCT    |');
      DBMS_OUTPUT.PUT_LINE('    |');
      -- next row
      DBMS_OUTPUT.PUT('|DG Name   ');
      DBMS_OUTPUT.PUT('|Type     ');
      DBMS_OUTPUT.PUT('|FGs  ');
      DBMS_OUTPUT.PUT('|Disks');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB              ');
      DBMS_OUTPUT.PUT('|MB              ');
      DBMS_OUTPUT.PUT('|MB              ');
      DBMS_OUTPUT.PUT('|MB              ');
      DBMS_OUTPUT.PUT('|File MB         ');
      DBMS_OUTPUT.PUT('|Util   ');
      DBMS_OUTPUT.PUT_LINE('|DFC |');
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------------------------------------------------');

   FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

      v_enuf_free := FALSE;

      -- Find largest amount of space allocated to a cell
      SELECT sum(disk_cnt), max(max_total_mb), max(sum_used_mb), count(distinct failgroup)
     INTO v_num_disks,v_max_total_mb, v_max_used_mb, v_fg_count
      FROM (SELECT failgroup, count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb - free_mb) sum_used_mb
      FROM v$asm_disk
     WHERE group_number = dg.group_number and failgroup_type = 'REGULAR'
     GROUP BY failgroup);

   -- Amount to reserve depends on version and number of FGs
   IF  ((v_db_version like '12.2%') or (v_db_version like '18%')) THEN
     IF v_fg_count < 5 THEN
         v_space_reserve_factor := 0.15 ;
     ELSE
       v_space_reserve_factor := 0.09 ;
     END IF;
   ELSIF ( (v_db_version like '12.1%' ) or (v_db_version like '11.2.0.4%') ) THEN
       v_space_reserve_factor := 0.15 ;     
   ELSE
       v_space_reserve_factor := 0.15 ;
   END IF;

   v_required_free_mb := v_space_reserve_factor * dg.total_mb;
   IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

    IF dg.type = 'NORMAL' THEN

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/2);

    ELSIF dg.type = 'HIGH' THEN
         -- HIGH redundancy
         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/3);
        
    ELSIF dg.type = 'EXTEND' THEN
         -- EXTENDED redundancy for stretch clusters

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/4);

     ELSE
        -- We don't know this type...maybe FLEX DG - not enough info to say
        -- Maybe FLEX OR EXTERN
        v_usable_mb := NULL;

    END IF;
      
      DBMS_OUTPUT.PUT('|'||RPAD(dg.name,v_offset-40));
      DBMS_OUTPUT.PUT('|'||RPAD(nvl(dg.type,'  '),v_offset-41));
      DBMS_OUTPUT.PUT('|'||LPAD(TO_CHAR(v_fg_count),v_offset-45));
      DBMS_OUTPUT.PUT('|'||LPAD(TO_CHAR(v_num_disks),v_offset-45));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(v_max_total_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb,'999,999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb - dg.free_mb,'999,999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.free_mb,'999,999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_required_free_mb),'999,999,999,999'));
      DBMS_OUTPUT.PUT('|'||NVL(TO_CHAR(ROUND(v_usable_mb),'999,999,999,999'),'      N/A       '));

     -- Calc Disk Utilization Percentage
      IF dg.total_mb > 0 THEN
         DBMS_OUTPUT.PUT('|'||TO_CHAR((((dg.total_mb - dg.free_mb)/dg.total_mb)*100),'999.9')||CHR(37));
      ELSE
         DBMS_OUTPUT.PUT('|       ');
      END IF;

      IF v_enuf_free THEN
         DBMS_OUTPUT.PUT_LINE('|'||'PASS|');
      ELSE
         DBMS_OUTPUT.PUT_LINE('|'||'FAIL|');
      END IF;


   END LOOP;

     DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------------------------------------------------');
   <<the_end>>

   IF v_cfc_fail_msg is not null THEN
      DBMS_OUTPUT.PUT_LINE('Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.');
      DBMS_OUTPUT.PUT_LINE(v_cfc_fail_msg);
   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');
   DBMS_OUTPUT.PUT_LINE('Script completed.');   
   DBMS_OUTPUT.PUT_LINE('Note:If Disk Usable File MB is N/A,the DiskGroup redundancy is FLEX or EXTERNAL.');  

END;
/

WHENEVER SQLERROR EXIT FAILURE;

Reference

Understanding ASM Capacity and Reservation of Free Space in Exadata (Doc ID 1551288.1)


关键词:asm exadata 

相关文章

Exadata最权威最完整的学习资料
Exadata上收集Cell节点的日志
Exadata上如何重置Cell节点root密码当你忘记时
Oracle性能加速之Write-Back Flash Cache
Oracle ASM from 10g to 18c
在18c中通过ASM Flex DiskGroup克隆PDB
Exadata and ASM
Oracle ASM Storage Limits
Oracle ZDLRA (Zero Data Lost Recovery Appliance) FAQ
Oracle ZDLRA (Zero Data Lost Recovery Appliance) Features
Oracle Database 18c On Exadata
Migrate Oracle Database on AIX to Exadata
Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com