Understanding The Change In REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB of ASM DISK GROUP After 19.16 And Its Impact On Various Tools (Doc ID 2932395.1)

 

APPLIES TO:

Gen 2 Exadata Cloud at Customer - Version All Versions to All Versions [Release All Releases]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine)
Oracle Cloud Infrastructure - Exadata Cloud Service
Oracle Exadata Hardware
Information in this document applies to any platform.

PURPOSE

 This document discusses the change in the REQUIRED_MIRROR_FREE_MB AND USABLE_FILE_MB of ASM DISK GROUP after applying 19.16 GI RU  and its impact on various tools.

DETAILS

 Understanding REQUIRED_MIRROR_FREE_MB  and USABLE_FILE_MB:

                 REQUIRED_MIRROR_FREE_MB is the amount of Free Space required to restore redundancy after a single disk failure on Exadata systems. 

                 USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure.

                     USABLE_FILE_MB is computed by subtracting REQUIRED_MIRROR_FREE_MB from the total free space in the disk group and then adjusting the value for mirroring.

The above definitions indicate that the USABLE_FILE_MB is dependent on REQUIRED_MIRROR_FREE_MB. Hence the key here is to understand the calculation of REQUIRED_MIRROR_FREE_MB.

Calculation of REQUIRED_MIRROR_FREE_MB :
              REQUIRED_MIRROR_FREE_MB is the amount of Free Space required to restore redundancy after a single disk failure on Exadata systems.
              How this number is calculated? Oracle recommends having free space in the diskgroup equal to or greater than the percentage of the total diskgroup capacity as follows: 

 Grid Infrastructure Version.Number of Failgroups.Required % Free of Diskgroup Capacity.
12.1.0Any15
12.2, 18.1+less than 515
12.2, 18.1+5 or more9

 

The Change in 19.16

     Until 19.16 DB RU, the display of REQUIRED_MIRROR_FREE_MB and hence USABLE_FILE_MB was not based on the above-referenced calculation. A combination of failure group space and disk space was used to calculate the REQUIRED_MIRROR_FREE_MB before 19.16.
     This was determined not optimal and was decided to change in 19.16 to reflect the values based on the recommended calculation posted above.
     With this change, either 9% or 15% (depending on the configuration, refer to the table above) free space is marked as needed for restoring the redundancy after a failure. 

Impact of the change in 19.16

    With the above change, the amount of space reported in REQUIRED_MIRROR_FREE_MB has changed, and a corresponding change in USABLE_FILE_MB happened as well. This change may cause a decrease in the value of USABLE_FILE_MB reported by ASM.
    In some cases, this even leads to USABLE_FILE_MB being negative.
    Certain tools, like dbca, dbaascli, do a check for sufficient free space in the diskgroup, as a prerequisite check when activities like DB creation or cloning are initiated from those tools.
    These tools depend on USABLE_FILE_MB value to decide if there is enough free space for the intended operation.
    If, after the change (with 19.16) , the USABLE_FILE_MB value has gone below the required free space for the specific operation, it will be reported as an error by such tools.

What will happen if the USABLE_FILE_MB is negative :

       If   USABLE_FILE_MB is negative, restoring redundancy can potentially fail after a disk failure.  Please note that the same situation existed even prior to 19.16, prior to 19.16  since the  REQUIRED_MIRROR_FREE_MB reported was not sufficient to cover all cases of disk failures, it was possible to have failure in restoring redundancy even with a positive usable_file_mb. However, there is an exachk check called “Verify there is enough diskgroup freespace for a rebalance operation”, which checks for this condition and alerts users. Also, note 1551288.1 had published a PL/SQL script to find the  usable_file_mb accounting for this needed reservation. With 19.16, ASM calculation of REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB now by default uses the same calculation used in the referenced note and in the exachk check which is in existence for years.

 How to find what would be the  REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB ahead of applying 19.16?

          Use the PL/SQL  script attached to (Doc ID 1551288.1)     

Important Points

There is no change in the FREE_MB (free space) available in the diskgroup .
The only change is in the decision of how much space needs to be kept free for Failure coverage in the event of a disk failure.
This number, REQUIRED_MIRROR_FREE_MB, which was reported in versions prior to 19.16 was not optimal and it is changed now, which also affects the value of USABLE_FILE_MB.
From a purely ASM perspective, USABLE_FILE_MB is an advisory value.
ASM does not make any allocation determination based on this value. Please note that Oracle has supplied an Exachk check for many years to ensure systems have sufficient free space to rebalance diskgroups after a disk failure.

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more