APPLIES TO:
Exadata Database Machine X2-2 Hardware - Version All Versions and laterExadata X3-8 Hardware - Version All Versions and later
Exadata X3-2 Hardware - Version All Versions and later
Oracle Exadata Storage Server Software - Version 11.2.1.2.0 to 11.2.2.3.0 [Release 11.2]
Exadata Database Machine X2-8
Linux x86-64
PURPOSE
Automatic Storage Management Best Practices for Sun Oracle Database Machine X2-2/X2-8, X3-2/X3-8,X4-2/X4-8, X5-2, X6-2/X6-8
SCOPE
People working with Sun Oracle Database Machine X2-2/X2-8, X3-2/X3-8,X4-2/X4-8, X5-2, X6-2/X6-8
DETAILS
Automatic Storage Management
Follow MAA Exadata ASM best practices
- Verify Proper ASM Failure Group Configuration
- Verify no ASM corruption is reported
- Optimize ASM Diskgroup Balance
- Check Diskgroup Balance
- Size ASM cache properly for large environments
- Manage ASM Audit Files
- Set ASM content type attributes for better failure protection
- Verify no ASM unprotected templates or external redundancy diskgroups exist
- Choose the ASM redundancy type based on your desired protection level and capacity demands
- Understanding ASM Redundancy State and Restoration Status
Follow MAA Exadata ASM best practices using Grid Infrastructure 12.1.0.2
- Use High Redundancy for all Production ASM Diskgroups on Exadata
- Verify important ASM instance parameters
- ASM rebalance and power limit considerations for GI 12.1.0.2
Follow MAA Exadata ASM best practices using Grid Infrastructure 11.2.0.x
- Verify there is enough diskgroup free space for a rebalance operation
- ASM power limit considerations and rebalance estimates for GI 11.2.0.x
- Ensure the ASM Disk Repair Timer is Set Correctly
- Check ASM rebalance forward progress if you suspect a problem
- Check forward progress on an long running ASM resync if a problem is suspected
Follow MAA Exadata ASM best practices
The following represent the consolidated list of MAA Exadata best practices for ASM applicable to any Grid Infrastructure version
Verify Proper ASM Failure Group Configuration
| Priority | Alert Level | Date | Owner | Status | Engineered System |
|---|---|---|---|---|---|
| Critical | FAIL | 03/11/15 | Vern Wagman | Production | Exadata, Exalogic |
| DB Version | DB Role | Engineered System Platform | Exadata Version | OS & Version | Validation Tool Version |
| 11.2.0.x | PRIMARY STANDBY | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2, X5 | 11.2.x + | Linux, Solaris | exachk 12.1.0.2.4 |
Benefit / Impact
ASM Failure groups are used to place mirrored copies of data so that each copy is on a disk in a different failure group so that the simultaneous failure of all disks in a failure group does not result in data loss. On Exadata, by default, all grid disks in a single Exadata Storage Server (or cell) belong to the same failure group to tolerate the failure of a single storage server.
Risk
If a single storage server contains grid disks for a disk group that reside in more than one failure group, then it is possible for the disk group to be taken offline, causing the database to crash, if that single storage server is taken offline (e.g. for planned maintenance) or fails.
In a rare circumstance, systems with 11.2.0.2 BP6 installed, but missing required overlay patch 12530803 (as documented in Support Note 888828.1), can have ASM disks in a disk group mistakenly added back into an incorrect failure group automatically, thus causing the grid disks for a disk group in one storage server to belong to more than one failure group.
Action / Repair
To verify the proper ASM failure group configuration, run exachk and examine the HTML report. The expected output is for the check to pass. If the check pases, no action is required.
If the check fails, following the manual corrective instructions in: Document 1351036.1. Re-run exachk after the manual repairs to validate that the check passes.
Verify no ASM corruption is reported
| Priority | Alert Level | Date | Owner | Status | Scope |
|---|---|---|---|---|---|
| Critical | FAIL | 3/1/2013 | Mike Nowak | Production | Exadata, SSC |
| DB Version | DB Role | Engineered System | Exadata Version | OS & Version | Validation Tool Version |
| ALL | ALL | X2-2(4170), X2-2, X2-8, X3-2, X3-8, EIGHTH, X4-2 | 11.2.2.2.0+ | Solaris -11 Linux x86-64 UEK5.8 | exachk 2.2.2 |
Benefit / Impact:
Verifying that ASM is not reporting corruption helps to avoid outages. If corruption is found, this proactive notification enables customers to perform root cause analysis and repair the issue before the corruption is encountered in their application.
Risk:
Application exposure to data corruption.
Action / Repair:
If an ASM rebalance or resync encounters an I/O error on the last mirror copy, ASM writes a well known string to the least number of blocks to avoid a diskgroup dismount. ASM provides notification on this event via it's alert log, but sometimes this notification can be missed.
To verify that no ASM corruption has occurred, execute the following command as the "grid infrastructure owner" userid with the environment properly set for the target ASM instance, on each database server:
alert_dest=$(echo -e "set heading off feedback off timing off lines 180\n select value from v\$parameter where name='background_dump_dest';"|$ORACLE_HOME/bin/sqlplus -s / as sysdba|grep -v ^$|tail -1); alert_name=$(find $alert_dest -name 'alert_*.log' 2>/dev/null); if [ `grep -iw "with BADFDATA" $alert_name | wc -l` -ne "0" ] then echo -e "The following ASM alert logs were found to contain the string \"with BADFDATA\":\n" echo -e `grep -iw "with BADFDATA" $alert_name` else echo "No ASM alert logs were found to contain the string \"with BADFDATA\"." fi
The expected result is:
No ASM alert logs were found to contain the string "with BADFDATA".
If the output is similar to:
The following ASM alert logs were found to contain the string "with BADFDATA": /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_dummyfile.log:with BADFDATA
Then a corruption has occurred. It is extremely important to identify the root cause and proactively deal with the corruption. For example, if it is an index block, the index can be recreated when convenient.
NOTE: There is no automatic correction of this condition. RCA MUST be conducted and the issue manually addressed. After you have cleared the condition, and you know the database is clean, if you do not want to fail this check again for previously detected and corrected occurrences, the following actions are recommended:For alert log files that are not the current active alert log:
1) gzip the file in which the error was reported, after it has been confirmed that all the detected corruption has been corrected. The detection command used will not search the gzip file.For the current active alert log:
1) Correct all detected corruptions
2) execute the manual commands to check for new detected corruption. If reported, correct.
3) repeat 2) until no new corruptions reported.
4) gzip the current active log. ASM will create a new one as soon as it needs to write to the alert log. The detection command used will not search the gzip file.
Optimize ASM Diskgroup Balance
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
It is important that files are equally balanced across all disks. The following sections ensure that this occurs.
Check Diskgroup Balance
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
If you want to perform an imbalance check for all mounted diskgroups, run the script in Metalink Note 367445.1.
If you want to determine if you already have imbalance for a file in an existing diskgroup, use the following query:
select disk_kffxp, sum(size_kffxp) from x$kffxp where group_kffxp=AAA and number_kffxp=BBB and lxn_kffxp=0 group by disk_kffxp order by 2;
Breakdown of input/output is as follows:
- AAA is the group_number in v$asm_alias
- BBB is file_number in v$asm_alias
- disk_kffxp gives us the disk number.
- size_kffxp is used such that we account for variable sized extents.
- sum(size_kffxp) provides the number of AUs that are on that disk.
- lxn_kffxp is used in the query such that we go after only the primary extents, not secondary extents
If you want to check balance from an IO perspective, query the statistics in v$asm_disk_iostat before and after running a large SQL statement. For example, if the running a large query that does just reads, the reads and read_bytes columns should be roughly the same for all disks in the diskgroup.
Size ASM cache properly for large environments
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
For environments where the number of disks and/or the number of files is very large, the default ASM buffer cache size (db_cache_size parameter) may not be adequate. To ensure the proper size use the following formula:
5 * (<# of disks> + <# of files>) * 4K
Do not change the default unless the formula indicates that it is too small. Note the default deployment does not set db_cache_size in the ASM instance becauseit is not required for that configuration.
Manage ASM Audit Files
| Priority | Alert Level | Date | Owner | Status | Scope |
|---|---|---|---|---|---|
| Critical | FAIL | 04/30/13 | Doug Utzig | Development | Exadata |
| DB Version | DB Role | Engineered System | Exadata Version | OS & Version | Validation Tool Version |
| 11.2.0.* | ASM | X2-2(4170), X2-2, X2-8, X3-2, X3-8 | 11.2+ | Linux x86-64 | exachk 2.2 |
Benefit / Impact:
The number of audit files for an ASM instance can grow to be very large number in the audit directory, which can cause the file system to run out of free disk space or inodes, or can cause Oracle to run very slowly due to file system directory scaling limits.
Risk:
Having a very large number of files can cause the file system to run out of free disk space or inodes, or can cause Oracle to run very slowly due to file system directory scaling limits, which can have the appearance that the ASM instance is hanging on startup.
Action / Repair:
Refer to MOS Document 1559573.1 to configure the ASM instance to write audit information to /var/log/asmaudit.log using the operating system syslog facility and configure logrotate to manage the log.
Set ASM content type attributes for better failure protection
| Priority | Alert Level | Date | Owner | Status | Scope |
|---|---|---|---|---|---|
| Critical | FAIL | 10/15/2014 | Michael Nowak | Draft | Exadata |
| DB Version | DB Role | Engineered System | Exadata Version | OS & Version | Validation Tool Version |
| 11.2.0.3+ | All | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | N/A | Solaris - 11 Linux x86-64 UEK5.8 |
Benefit / Impact:
If the Grid Infrastructure release is 11.2.0.3 or higher, and the ASM diskgroup compatible.asm attribute is 11.2.0.3 or higher, and the diskgroups are the standard DATA*, RECO*, and DBFS_DG*, then the content type attributes should be set for the standard three diskgroups, DATA, RECO, and DBFS_DG. Setting the content type attributes provides better recovery time objective (RTO) and recovery point objective (RPO or data loss tolerance) for half and full rack configurations, but still should be set for quarter rack configurations that meet the aforementioned criteria in case a future expansion occurs.
Risk:
Not setting the ASM diskgroup content type attributes leads to a greater exposure to downtime and data loss
Action / Repair:
The recommended ASM disk group configuration is to have the standard three diskgroups, DATA, RECO, and DBFS_DG where DATA uses the 'data' content type, RECO uses the 'recovery' content type, and DBFS_DG uses the 'system' content type. Do not use content type to distinguish the availability characteristics of diskgroups that are used for a different purpose, such as those created in support of a particular service.
To include the content type during diskgroup creation, simply add the attribute on the create command. Example:
create diskgroup data normal redundancy DISK 'o/*/DATA*' ATTRIBUTE 'content.type' = 'DATA', 'AU_SIZE' = '4M', 'cell.smart_scan_capable'='TRUE', 'compatible.rdbms'='11.2.0.2', 'compatible.asm'='11.2.0.4';
To add the content type to an existing diskgroup, simply add the attribute on the alter command, and run a rebalance. Note this rebalance can run quite long, but the diskgroup's data is fully redundant throughout. Example:
alter diskgroup reco set attribute 'content.type'='recovery'; alter diskgroup reco rebalance power <desired power setting> ;
To check that the content type is set properly, query v$asm_attribute. Example:
SQL> select dg.name,a.value from v$asm_diskgroup dg, v$asm_attribute a 2 where dg.group_number=a.group_number and a.name='content.type' 3 and (dg.name like 'DATA%' or dg.name like 'RECO%' or dg.name like 'DBFS_DG%'); NAME VALUE -------------------- -------------------- DATA data RECO recovery DBFS_DG system
Verify no ASM unprotected templates or external redundancy diskgroups exist
| Priority | Alert Level | Date | Owner | Status | Scope |
|---|---|---|---|---|---|
| Critical | FAIL | 1/8/13 | Mike Nowak | Published | Exadata, SSC, Exalogic |
| DB Version | DB Role | Engineered System | Exadata Version | OS & Version | Validation Tool Version |
| All | ASM | X2-2(4170), X2-2, X2-8, X3-2, X3-3, X4-2 | All | Solaris - 11 Linux x86-64 UEK5.8 | exachk TBD |
Benefit / Impact:
ASM redundancy prevents data loss
Risk:
Unprotected ASM templates and/or external redundancy diskgroups can cause data loss.
Action / Repair:
To check the types of ASM templates in use, use the following SQL statement in the ASM instance. The result should be HIGH or MIRROR.
SQL> select distinct redundancy from v$asm_template; REDUND ------ HIGH
To check the redundancy of mounted ASM diskgroups, use the following SQL statement in the ASM instance. The result should be HIGH or NORMAL
SQL> select distinct type from v$asm_diskgroup; TYPE ------ HIGH
If either ASM unprotected templates or external redundancy diskgroups exist, investigate and correct the condition.
Choose the ASM redundancy type based on your desired protection level and capacity demands
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
Exadata requires ASM redundancy which comes in two types - normal redundancy (double mirror the extents) and high redundancy (triple mirror the extents). ASM always ensures that copies of the same extent are stored in different failure groups, and a failure group is equivalent to an Exadata cell. ASM normal redundancy tolerates the failure of a single cell or any set of disks in a single cell. ASM high redundancy tolerates the failure of two cells or any set of disks in two cells. You should choose your redundancy setting based on your desired protection level. In doing so, you must also ensure that post-failure I/O capacity is sufficient to meet the redundancy requirements and performance service levels. Consider the following:
- From a redundancy perspective, if a cell or disk fails, ASM will automatically redistribute the cell or disk contents across the remaining disks in the disk group, as long as there is enough space to hold the data. For an existing disk group using ASM redundancy, the amount of usable space and space for redundancy can be determined by running the script given in the best practice,Verify there is enough diskgroup free space for a rebalance operation. Ideally, you should have enough free space to tolerate the failure of both a cell and a disk.
- From a performance perspective, if a cell or disk fails, the remaining disks should be able to generate the MBPS and IOPS necessary to sustain the performance service level agreement.
- When storing the ocr and vote devices in ASM on Exadata cells, three cells are required for a normal redundancy diskgroup and five cells are required for a high redundancy diskgroup.
For mission critical applications, use HIGH redundancy for DATA and RECO. If a Data Guard standby database has been deployed on a second independent Exadata system for failover purposes, then ASM normal redundancy is acceptable.
Understanding ASM Redundancy State and Restoration Status
ASM storage partnership exists at both the disk and fail group level, and is designed to reduce the possibility of data loss if multiple failures occur using a large storage configuration with many disks. Partnering also ensures that a given ASM extent set is sufficiently spread across storage to avoid single points of failure. For example, using ASM high redundancy on Exadata, every extent (primary, secondary and tertiary) in a given extent set is guaranteed to be on different Exadata cells, since every cell is a failure group. The content.type diskgroup attribute is available to minimize or eliminate partnership intersections across different diskgroups. One example of this with Exadata is having different content.type for the DATAC1 and RECOC1 diskgroups created at deployment time. With enough disks and cells to support it, this configuration ensures a single catastrophic full partner failure does not forcibly dismount both diskgroups thus losing both data and recovery files.
When storage goes offline, either temporarily or permanently, redundancy restoration is required. The ASM ARB0 process performs this rebalance work, asynchronously issuing the number of outstanding extent relocations defined by the specified or default ASM power limit. Specific phases of rebalance can be observed via gv$asm_operation.pass column when using Oracle Grid Infrastructure 12.1 or higher. If the pass is RESYNC or RESILVER, redundancy restoration is in process. If the pass is REBALANCE, redundancy restoration may be in process, depending on the sequence of events and the reason the storage went offline. Since there isn’t an airtight way to determine redundancy restoration status via the v$asm_operation view, it is recommended that the v$asm_disk.mode_status column be used instead as it works in all cases. If v$asm_disk.mode_status is not ‘ONLINE’ for all disks currently in a given diskgroup, then the redundancy of that diskgroup is compromised. When redundancy is compromised, care should be taken to ensure partner storage does not go offline as it would result in a forcible diskgroup dismount. Oracle tools such as patchmgr check v$asm_disk.mode_status before taking storage offline for planned software updates.
Here is an example query run in the ASM instance showing a case where not all disks are online:
SQL> select distinct mode_status from v$asm_disk where group_number=3;
MODE_ST
-------------
OFFLINE
ONLINE
Here is an example query run in the ASM instance showing a case where all disks are online:
SQL> select distinct mode_status from v$asm_disk where group_number=3;
MODE_ST
-------------
ONLINE
Follow MAA Exadata ASM best practices using Grid Infrastructure 12.1.0.2
The following represent the consolidated list of MAA Exadata best practices for ASM. They supersede all previous best practices for any prior Grid Infrastructure versions.
Use High Redundancy for all Production ASM Diskgroups on Exadata
ASM High Redundancy Exadata MAA Best Practice - Introduction
Configuring ASM diskgroups to use high redundancy, or triple mirroring of extents in an extent set, is an Exadata MAA (Maximum Availability Architecture) best practice because it provides the ultimate protection against downtime and data loss caused by partner storage failures and corruptions. High redundancy not only protects against the less likely case of a double partner disk failure, but more importantly protects from all of the other combinations of issues that can occur with partner storage. The following table details this benefit.
One discernible example from this table is having an Exadata storage cell offline for planned maintenance while encountering an IO error or corruption reading a secondary extent on a partner disk [1].
High redundancy is an MAA best practice for all Exadata configurations. The only MAA compliant alternative to high redundancy is normal redundancy with a DataGuard standby database that is ready to take over the production workload. This includes application support and fully tested operational procedures that have proven failover will work seamlessly. More information can be found on this MAA page.
ASM Rebalance Resources and Redundancy Restoration
An ASM rebalance is run when storage is dropped or added to an ASM disk group, most commonly when a disk fails or when adding storage servers to the configuration. The ASM power limit [2] defines the number of outstanding asynchronous IOs the ASM Rebalance Process (ARB0) process will issue during the rebalance. The Exadata MAA best practice default for ASM power limit is 4, and set at deployment time. The Exadata MAA best practice maximum for ASM power limit is 64. A higher power limit such as 64 enables rebalance to run faster, but the extra IOs can impact the application service level. The aforementioned best practice default of 4 has been tested in MAA labs and shown to have minimal impact on several different application service levels [3]. Internal tests have also shown that there is no IO throughput or rebalance runtime benefit beyond a power limit of 64.
When a rebalance is triggered by a planned maintenance operation using ASM redundancy (normal or high), for example the adding of a new Exadata storage server, the diskgroup’s redundancy is never compromised and thus running at the Exadata default ASM power limit of 4 is advised if maintaining the application service level is a priority. When a rebalance is triggered by a storage failure, for example a disk failure, the diskgroup’s redundancy is compromised until the rebalance is complete [4]. It is in this case where redundancy restoration via a timely rebalance completion is important since the permanent failure of remaining partner storage will cause the diskgroup to forcibly dismount and data will be lost [5]. This is particularly relevant when using a normal redundancy diskgroup since there is only one remaining copy of the extents from the extent sets on the failed disk, so one might want to use a higher ASM power limit to restore redundancy faster, potentially at the expense of the application service level. With high redundancy, two extent copies remain, diminishing the risk of losing all remaining partner storage, thus a lower ASM power limit, one that maintains application service levels, may be preferred.
ASM Rebalance Performance Factors
At this point, it should be clear that redundancy restoration is the most critical task of rebalance, and it is only necessary when storage is lost. It should also be clear why high redundancy is an Exadata MAA best practice that protects your application RTO (recovery time objective) and RPO (recovery point objective). Now let’s look at what factors into rebalance runtime.
Two primary factors influencing rebalance runtime are the amount of allocated space on the storage that caused the reconfiguration (ex: allocated space on disk for disk failure), and the ASM power limit. For example, using the same ASM power limit, the rebalance resulting from the failure of a fully allocated 8TB disk will take twice as long as if that disk was only half allocated. Increasing the ASM power limit (up to the Exadata MAA best practice maximum of 64) yields higher IO throughput and lower rebalance runtimes, and scales, though less so at the higher power limits.
The diskgroup’s redundancy type can also affect rebalance runtime. With the same amount of allocated storage, the rebalance of a high redundancy diskgroup moves up to twice the number of extents compared to a normal redundancy diskgroup. The extra extent movement is required to maintain disk partnerships and diskgroup balance for non primary extents. While the pure cost of rebalancing a high redundancy diskgroup is more expensive, the aforementioned benefits far outweigh this cost.
Lastly, IO is a shared resource, thus an IO intensive application workload can also impact ASM rebalance timing.
Rebalance Work and Time Estimations
To understand how many extents need to move for a given rebalance, the EXPLAIN WORK FOR ALTER DISKGROUP statement [6], which populates the V$ASM_ESTIMATE view, may be used. It is not possible to use this technique to estimate the amount of time a rebalance will take, because it does not have IO timings to take into consideration.
To understand how many extents have been processed and how much time remains for a running rebalance, the V$ASM_OPERATION view may be used. This view tracks the effective rate of rebalance and can fluctuate with the IOs coming from application workloads.
Normal and High Redundancy Comparison – an Example
The following table compares rebalance times using normal and high redundancy after the loss of a single hard disk in an Exadata HC quarter rack with fully populated 8TB disks [7]
For 4TB disks, the database space allocated and rebalance timings are simply half of the values provided in this table, and all other columns are the same.
ASM High Redundancy Exadata MAA Best Practice - Conclusion
Exadata MAA best practices exist to enable our most demanding customers to meet their most stringent application service level RTOs and RPOs. High Redundancy is an Exadata MAA best practice because it protects from the crashes and data loss associated with every type of partner storage loss, and it enables the repair from disk failure to run at a power limit that minimizes application service level impact.
[1] Exadata scrubbing runs on an intelligent, adaptive schedule to mitigate the risk of encountering latent bad sectors, but it cannot prevent it in all cases.
[2] The default ASM power limit is defined by the ASM instance’s asm_power_limit parameter. Alternate ASM power settings can be specified using the alter diskgroup command.
[3] It is impossible to simulate every possible customer workload but workloads generating sufficient IO within data sheet limits were used
[4] 15% of allocated diskgroup space should be reserved to ensure ASM rebalance completes without an out of space error such as ORA-15041. This number is factored into the allocated space section of the Exadata data sheets.
[5] When the storage failure is small and isolated, such as a single bad sector or set of bad disk sectors, ASM ensures that only the data affected by the failure is lost, not any of the other valid data on the disk. ASM also helps the Administrator to identify the affected storage by writing a well known string to a new extent location, and this well known string can be used to facilitate other methods of repair such as RMAN BMR (block media recovery).
[6] Available as of Oracle Database release 12.1
[7] For high redundancy, an assumption is made that no pathological condition exists that affects all three disk partners
[8] Rebalance times were generated in MAA labs with no competing application IOs to document expectations without a workload profile variable (which changes from customer to customer), and enable a pure comparison between normal and high redundancy
[9] This may be done in certain cases where the rebalance completion is a priority, for example if there is no application workload or one where service level impact doesn’t matter
Verify important ASM instance parameters
| Priority | Alert Level | Date | Owner | Status | Engineered System |
|---|---|---|---|---|---|
| Critical | WARN | 06/24/15 | Michael Nowak | Production | Exadata-User Domain, Exadata-Physical, SSC,ZDLRA |
| DB Version | DB Role | Engineered System Platform | Exadata Version | OS & Version | Validation Tool Version |
| 12.1.0.2+ | N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2, X4-8, X5-2 | 12.1+ | Solaris - 11 Linux x86-64 el5uek Linux x86-64 el6uek | TBD |
Benefit / Impact:
Aligning with MAA best practice parameters for the ASM instance improves stability, security, and performance of ASM operations. Check the ASM documentation for instructions and impact of changing specfic parameters.
Risk:
The risk of not using MAA best practice parameters for the ASM instances is security vulnerability, performance issues, and possible data loss.
Action / Repair:
To verify important ASM instance parameters, run the following script with the environment configured for the ASM instance. Note this script cannot check for all best practice parameters because some are customer specific. Please refer to the 12.1.0.2 ASM instance best practice parameter and attribute reference for a complete list as well as more details on why these parameters are best practices.
for record in asm_power_limit,4 audit_syslog_level,local0.info audit_sys_operations,true
do
parameter_name=`echo $record | awk -F, '{print $1}'`
pass_value=`echo $record | awk -F, '{print $2}'`
current_value=`sqlplus -S "/ as sysasm" << eof
set head off
select lower(value) from v\\$parameter where name='$parameter_name';
exit
eof`
current_value=`echo $current_value | tr -d '\r'`
if [ $current_value == $pass_value ]
then
ret_val=0
echo -e "SUCCESS: $parameter_name current value $current_value matches best practice value $pass_value"
else
echo -e "FAILURE: $parameter_name current value $current_value does not match best practice value $pass_value"
ret_val=1
fi
done
The output should look like this:
SUCCESS: asm_power_limit current value 4 matches best practice value 4 SUCCESS: audit_syslog_level current value local0.info matches best practice value local0.info SUCCESS: audit_sys_operations current value true matches best practice value true
If the output does not match please adjust any parameter listed with a FAILURE prefix
ASM rebalance and power limit considerations for GI 12.1.0.2
| Priority | Alert Level | Date | Owner | Status | Engineered System |
|---|---|---|---|---|---|
| Critical | WARN | 8/12/2015 | Michael Nowak | Production | Exadata-User Domain, Exadata-Physical, SSC |
| DB Version | DB Role | Engineered System Platform | Exadata Version | OS & Version | Validation Tool Version |
| 12.1.0.2+ | N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2, X4-8, X5-2 | 12.1+ | Solaris - 11 Linux x86-64 el5uek Linux x86-64 el6uek | TBD |
Benefit / Impact:
For physical deployments:
The default ASM power limit, ie the asm_power_limit parameter in the ASM instance, should be set to ensure the application performance is not impacted while rebalance is running. The default 12.1.0.2 deployment sets asm_power_limit=4 for all Exadata racks. Here are some other considerations for ASM rebalance and the setting of asm_power_limit
The default power of 4 was chosen after an internal MAA assessment showed it did not impact the application. Also the following bug fixes should exist to ensure efficient performance with minimal application impact
- All known ASM related fixes from MOS Exadata critical issues note 1270094.1
- Fixes for bugs 16087033 & 21218243 (GI side) and 13967870 (cell side)
For OVM deployments :
The default 12.1.0.2 deployment sets asm_power_limit to as below for OVM deployments :
deployments with 1 OVM RAC cluster - asm_power_limit=4
deployments with 2 OVM RAC clusters - asm_power_limit=2
deployments with more than 2 OVM RAC clusters - asm_power_limit=1
- The maximum power that should be used is 64 as anything higher adds no performance benefit, only risk.
- Setting asm_power_limit=0 disables ASM rebalance operations. In normal operation, asm_power_limit should not be set to 0.
- All MAA Exadata best practice attributes should be used to ensure an efficient rebalance. Amongst other things, these attributes ensure the compaction phase is disabled and appliance mode is set properly.
- Fixed extents will be allocated using Exadata and Grid Infrastructure 12.1.0.2 if the compatible.rdbms diskgroup attribute is set to 11.2.0.4 or higher at allocation time. Note this means no databases using versions < 11.2.0.4 can mount the diskgroup.
- During a rebalance that occurs after a failure where extent copies are lost, the most critical files are rebalanced first, and evidence of this can be found in the ASM alert log. For more information on priority rebalance, see MOS note 1968607.1.
- To determine the amount of work that a rebalance needs to do ahead of time, run the 'EXPLAIN WORK...' command and then query v$asm_estimate. Explain work is documented here.
- To monitor a running rebalance, query v$asm_operation. Along with the classic columns to assess work done so far and time remaining, the new 'PASS' column can be used to see what phase of rebalance is being executed.
Risk:
Not following MAA Exadata best practices with respect to asm_power_limit can cause application impact during rebalance and/or cause excessive rebalance runtimes.
Action / Repair:
Customers should evaluate the performance impact of different ASM power limit settings to their application while conducting an ASM rebalance operation under full application workload in a non-production environment and choose their ASM_POWER_LIMIT setting based on the results of the testing and the business requirements for the application performance and availability.
Follow MAA Exadata ASM best practices using Grid Infrastructure 11.2.0.x
The following represent the consolidated list of MAA Exadata best practices for ASM using Grid Infrastructure 11.2.0.x. They supersede all previous best practices for any prior Grid Infrastructure versions.
Verify there is enough diskgroup free space for a rebalance operation
| Priority | Alert Level | Date | Owner | Status | Engineered System | Engineered System Platform | Bug(s) |
|---|---|---|---|---|---|---|---|
| Critical | FAIL | 04/03/19 | <Name> | Production | Exadata - Physical, Exadata - User Domain | ALL | 29248427 - exachk 27733004 - exachk 27656375 - exachk 27092380 - exachk 25559650 - exachk |
| DB Version | DB Type | DB Role | DB Mode | Exadata Version | OS & Version | Validation Tool Version | MAA Scorecard Section |
| 11.2+ | ASM | N/A | N/A | ALL | Linux | exachk 19.2.0 | N/A |
Benefit / Impact:
Having enough diskgroup freespace available to accomodate a rebalance operation helps to prevent an outage or loss of data.
The Impact of verifying that there is enough diskgroup freespace available for a rebalance operation is minimal. The impact of correcting a lack of freespace will vary depending upon the reason for the lack of freespace, and cannot be estimated here.
Risk:
If there is not enough diskgroup freespace available for a rebalance operation, there is an increased risk of an outage or loss of data with an additional disk failure.
Action / Repair:
The following table displays the required free space for a complete rebalance operation after one disk failure:
| Grid Infrastructure Version | Number of Failgroups | Required % free |
| 12.1.0 | any | 15 |
| greater than or equal to 12.2 | less than 5 | 15 |
| greater than or equal to 12.2 | 5 or more | 9 |
To determine if there is sufficient free space to permit rebalancing after the loss of a single disk, as the Grid Infrastructure owner, run the following query in SQLPlus on any ASM instance in the cluster :
set serveroutput on
set linesize 160
declare
FGnum number;
gnum number;
gname varchar2(20);
freespace number;
totmb number;
freemb number;
usedmb number;
reqfmb number;
pctfree number;
reqpctfree number;
version varchar2(40);
cursor c1 is select x.group_number,count(x.numfg)
from ( select group_number,count(failgroup) numfg from v$asm_disk where failgroup_type='REGULAR' and group_number > 0 group by group_number,failgroup
) x
group by x.group_number;
begin
dbms_output.enable(500000);
open c1;
loop
fetch c1 into gnum,FGnum;
exit when c1%notfound;
select name into gname from v$asm_diskgroup where group_number=gnum;
select version into version from v$instance;
select to_number(substr(regexp_replace(version,'\.'),1,3)) into version from v$instance;
SELECT dg.name, dg.total_mb, dg.free_mb, dg.total_mb-dg.free_mb
INTO gname,totmb,freemb,usedmb
FROM v$asm_diskgroup dg WHERE dg.group_number= gnum;
if (version >= 122) then
if (FGnum < 5) then
reqpctfree := 0.15;
else
reqpctfree := 0.09;
end if;
else
reqpctfree := 0.15;
end if;
pctfree := round(freemb/totmb,3);
if (pctfree < reqpctfree) then
dbms_output.put_line('FAILURE: Diskgroup '||gname||' has '|| pctfree*100||'% free space which is lower than the '||reqpctfree*100||'% free space threshold. A rebalance may fail with ORA-15041'); dbms_output.new_line;
else
dbms_output.put_line('SUCCESS: Diskgroup '||gname||' has '|| pctfree*100||'% free space which is enough to complete the rebalance'); dbms_output.new_line;
end if;
end loop;
close c1;
end;
/
Examples of the output returned by the script:
FAILURE: Diskgroup DATAC1 has 13% free space which is lower than the 15% free space threshold. A rebalance may fail with ORA-15041 SUCCESS: Diskgroup DBFS_DG has 100% free space which is enough to complete the rebalance SUCCESS: Diskgroup RECOC1 has 99% free space which is enough to complete the rebalance SUCCESS: Diskgroup TEST has 50% free space which is enough to complete the rebalance SUCCESS: Diskgroup DATAC1 has 99% free space which is enough to complete the rebalance FAILURE: Diskgroup DBFS_DG has 6% free space which is lower than the 9% free space threshold. A rebalance may fail with ORA-15041 SUCCESS: Diskgroup RECOC1 has 100% free space which is enough to complete the rebalance
If a diskgroup does not have sufficient free space, you should do one of the following:
- evaluate the space usage within the diskgroup and free up space by removing tablespaces that are no longer needed
- move datafiles to another diskgroup that has enough space (datafile moves can be done on-line as of Oracle 12.1 )
- increase the size of the diskgroup, assuming that some other diskgroup can be shrunk or, there is unallocated freespace on celldisks
NOTE: Although it is not a recommended best practice, some customers may want to reserve enough space to tolerate a double disk failure in a high redundancy diskgroup. For those cases, we have the following free space requirements:
Grid Infrastructure Version Number of Failgroups Required % free greater than or equal to 12.2 less than 5 29 (see Exception 1) greater than or equal to 12.2 5 or more 15 (see Exception 2) Exception 1: A diskgroup having failgroups with less than 8 disks (ex: eighth rack configuration) requires 37% free space
Exception 2: A diskgroup having failgroups with less than 8 disks (ex: eighth rack configuration) requires 18% free space
ASM power limit considerations and rebalance estimates for GI 11.2.0.x
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| Important | 11/29/2011 | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
Benefit / Impact:
The default ASM power limit, ie the asm_power_limit parameter in the ASM instance, should be set to ensure the application performance is not impacted while rebalance is running. The default deployment sets asm_power_limit=1 for a eighth/quarter rack and asm_power_limit=2 for all other rack types. Here are some other considerations for ASM rebalance and the setting of asm_power_limit
- Redundancy is restored, and diskgroup data is protected, when the compaction phase of rebalance begins. The compaction phase of rebalance is active when the message "SUCCESS: refreshed membership for <diskgroup number>/<hex string> (<diskgroup name>) " is printed to the alert log, and v$asm_operation still shows a rebalance running, usually with est_minutes at 1 or 0. The compaction phase is removed in version 11.2.0.4.
- The highest power limit that should be used on a Database Machine running is 32. This assumes 11.2.0.2 GI_HOME and a diskgroup with the compatible.asm attribute set to 11.2.0.2.
- The anonymous PL/SQL block below* can be run against an ASM instance before executing a rebalance. It will provide a rough estimate runtime for rebalance at the default power and the maximum recommended power of 32. Note this estimate does not include the compaction phase of rebalance and assumes HP (high performance SAS) disks. Also, there are a number of variables associated with this kind of calculation which is why it is a rough estimate.
- If a rebalance is running at a lower power level than 32, and not proceeding fast enough according to v$asm_operation, the currently running rebalance may be replaced by executing a new rebalance command (ex: alter diskgroup <dg name> rebalance power 32;). Increasing the power should only be done when the application SLAs can tolerate the increased IO level. There is a some work that will repeated when a running rebalance is replaced with a new one, but an increased power level can make up for it, especially when the rebalance replacement is done toward the beginnning of the original rebalance.
- Setting asm_power_limit=0 disables ASM rebalance operations. In normal operation, asm_power_limit should not be set to 0.
- The semantics of the asm_power_limit have changed with 11202 software and a diskgroup with both rdbms and asm compatible attributes set to 11202. The power limit with this new software/config represents the number of outstanding relocations and is implemented with async IO (therefore you will only see a single process - ARB0 - executing the rebalance).
Risk:
Longer ASM rebalance time
Action / Repair:
Replacing a running rebalance to change the power limit.
* The following PL/SQL can be run against an ASM instance ahead of time to give a very rough estimate for rebalance time both with the current default power and the recommended DBM max power of 32. Note it assumes the diskgroup is already balanced. There are MOS notes available with scripts to check diskgroup balance, but imbalance should be an uncommon case.
set serveroutput on
declare
diskspace number;
cellspace number;
disk_rebal_time number;
cell_rebal_time number;
default_power_limit number;
max_rate_diff_cell number;
max_rate_diff_disk number;
failgroup_name varchar2(30);
diskgroup_name varchar2(30);
begin
dbms_output.put_line('---------------------------------------------------------------');
dbms_output.put_line('Estimates are provided for both the current default power ');
dbms_output.put_line('(asm_power_limit) and the recommended DBM max of 32');
dbms_output.put_line('The current recommended default power limit for a DBM is 4');
dbms_output.put_line(chr(10));
dbms_output.put_line('Note these estimates are for a DBM with High Performance drives');
dbms_output.put_line('and do not include the compaction phase of rebalance. The ');
dbms_output.put_line('compaction phase is not required for redundancy restoration');
dbms_output.put_line('---------------------------------------------------------------');
select value into default_power_limit from v$parameter where name='asm_power_limit';
max_rate_diff_cell:=32/default_power_limit;
max_rate_diff_disk:=16/default_power_limit;
for dg in (select group_number from v$asm_Diskgroup where group_number>0 and state='MOUNTED') loop
select failgroup into failgroup_name from v$asm_disk where group_number=dg.group_number and rownum=1;
select name into diskgroup_name from v$asm_diskgroup where group_number=dg.group_number;
select round(sum(total_mb-free_mb)/1024,0) into diskspace from v$asm_disk where disk_number=1 and group_number=dg.group_number;
select round(sum(total_mb-free_mb)/1024,0) into cellspace from v$asm_disk where failgroup=failgroup_name and group_number=dg.group_number;
disk_rebal_time:=round((diskspace/280)*60,0);
cell_rebal_time:=round((cellspace/1024)*60,0);
dbms_output.put_line(chr(10));
dbms_output.put_line('****************************************************************');
dbms_output.put_line('Rough time estimates for rebalance of diskgroup '||diskgroup_name||':');
dbms_output.put_line('DISK based rebalance at the default power of '||default_power_limit||': '||disk_rebal_time*max_rate_diff_disk||' minutes');
dbms_output.put_line('CELL based rebalance at the default power of '||default_power_limit||': '||cell_rebal_time*max_rate_diff_cell||' minutes');
dbms_output.put_line('DISK based rebalance at the maximum recommended power of 32: '||disk_rebal_time||' minutes');
dbms_output.put_line('CELL based rebalance at the maximum recommended power of 32: '||cell_rebal_time||' minutes');
dbms_output.put_line('****************************************************************');
end loop;
end;
/
Ensure the ASM Disk Repair Timer is Set Correctly
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| Critical | N/A | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
The ASM disk repair timer represent the amount of time a disk (or failure group..ie cell) will remain offline before ASM drops it and run the subsequent rebalance. While the disk or cell is offline, ASM is tracking the changed extents so the disk can be resynced if the problem was a temporary. The default disk repair timer is 3.6 hours and can be changed. If the default is inadequate, set the disk repair timer to the maximum amount of time it takes to detect and correct a temporary disk failure. Here is an example of changing the disk repair timer to 8.5 hours for the DATA diskgroup.
alter diskgroup data set attribute 'disk_repair_time'='8.5h';
It is important to understand that changing the disk_repair_timer parameter does NOT change the repair timer in use for disks currently offline. The repair timer for those offline disks is either the repair timer specified on the command line (if disks manually offlined) or the default repair timer in effect when the offline occurred. To change the repair timer for disks currently offlined, you can re-issue the offline command with the desired repair timer specified.
It is also important to understand that, typically, disks and cells have different failure and repair characteristics. Typically, a disk failure is permanent (ex: disk dies), and a cell failure is transient (ex: cell reboot). Starting with release 11.2.1.3, a feature was added to proactively drop disks that are predictive failure or failed. This allows the setting of disk_repair_time to be geared almost exclusively toward cell failure, thus easing the current aforementioned trade-off. In other words, you can set disk_repair_time primarily for cell failure.
Check ASM rebalance forward progress if you suspect a problem
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| n/a | Apr 2012 | X2-2(4170), X2-2, X2-8, X3-2, X3-8, X4-2 | Linux | 11.2.x + | 11.2.0.2 or above with diskgroup attribute compatible.asm=11.2.0.2 or higher |
Benefit / Impact:
This check can be used when one has a long running rebalance and want to ensure it is making forward progress. We have estimates in v$asm_operation but they can be misleading, especially when an IO intensive application is running alongside the rebalance.
Risk:
Apprehension that long rebalance runtime is stuck or won't finish.
Action / Repair:
Set the environment for ASM, and run the script below on the node running the rebalance. The considerations and requirements should be read before execution.
Script:
#!/bin/bash
# Goal
# 1. Provide assurance that rebalance is making progress by reporting the
# current file (Phase 1) or disk (Phase 2) being processed.
# Considerations
# 1. Works file by file so bigger files will be reported longer
# 2. Only works for diskgroups with compatible.asm=11.2.0.2 or higher
# 3. Only works with a running rebalance
# 4. Does not account for files added while the script is running
# Requirements to run:
# 1. Set environment for ASM instance before running
# 2. Execute with "./rebalance_file_progress.sh <optional check interval time where default is 10 minutes>"
function check_env {
if [[ $ORACLE_SID != +ASM* ]]; then
echo "Environment not set for ASM."
exit 1
fi
}
function check_running {
arbpid=`ps -ef | grep asm_arb0 | grep -v grep | awk '{print $2}'`
if [ -z "$arbpid" ]; then
echo "Rebalance is not running (no ARB0 ospid)"
exit 1
fi
}
function goto_trace_directory {
td=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select value from v\\$parameter
where name='background_dump_dest';
exit;
eof`
read -rd '' td <<< "$td"
cd $td
}
function check_trace_file {
tfile=${ORACLE_SID}_arb0_${arbpid}.trc
if [ ! -f $tfile ]
then
echo "The ARB0 trace file $tfile doesn't exist."
exit 1
fi
}
function collect_static_data {
dg=`grep 'relocating file' $tfile | tail -1 | awk -F "+" '{print $2}' | awk -F "." '{print $1}'`;
maxfile=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select max(file_number) from v\\$asm_file
where GROUP_NUMBER = (select group_number from v\\$asm_Diskgroup where name='$dg');
exit;
eof`
read -rd '' maxfile <<< "$maxfile"
disklistall=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
set feedback off
select DSKNUM_KFDDD from x\\$kfddd
where GRPNUM_KFDDD = (select group_number from v\\$asm_Diskgroup where name='$dg');
exit;
eof`
disklist=`echo $disklistall | tr ' ' '\012' | uniq`
i=1
for disk in ${disklist[@]}
do
dla[${disk}]=$i
let "i++"
done
}
function get_current_disk_compact {
currentdisk=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select disk_kfgmg from x\\$kfgmg
where NUMBER_KFGMG = (select group_number from v\\$asm_Diskgroup where name='$dg');
exit;
eof`
read -rd '' currentdisk <<< "$currentdisk"
if [ $currentdisk -eq "65535" ]; then
echo "Incorrect Phase 2 calculation..please rerun the script"
exit 1
fi
}
# Main
declare -a dla
declare -i oldfile
declare -i currentfile
declare -i maxfile
declare -i currentdisk
check_env
check_running
goto_trace_directory
check_trace_file
collect_static_data
currentfile=`grep 'relocating file' $tfile | tail -1 | awk -F "+" '{print $2}' | awk -F "." '{print $2}'`;
oldfile=$currentfile
if [ -z "$1" ]; then
check_interval=600
else
check_interval=$1
fi
echo "######################################################################"
echo "This script will monitor Phase 1 (rebalance) file by file and Phase 2"
echo "(compaction) disk by disk. Both phases should increment, showing progress."
echo "This script will *not* estimate how long the rebalance will take."
echo "######################################################################"
echo " "
echo "Diskgroup being rebalanced is $dg"
echo "ASM file numbers for databases start at 256. "
echo "Default check interval is 600 seconds. This run using $check_interval seconds..."
echo " "
while [ $currentfile -ge $oldfile ]
do
check_running
echo "`date`: PHASE 1 (of 2): Processing file $currentfile out of $maxfile"
sleep $check_interval
oldfile=$currentfile
currentfile=`grep 'relocating file' $tfile | tail -1 | awk -F "+" '{print $2}' | awk -F "." '{print $2}'`;
done
sleep 30
echo "*******************************************************"
echo "`date`: PHASE 1 (of 2) complete."
echo "*******************************************************"
while true
do
check_running
get_current_disk_compact
echo "`date`: PHASE 2 (of 2): ${dla[$currentdisk]} disks processed out of ${#dla[@]}"
sleep $check_interval
done
Sample output:
$ ./rebalance_progress.sh 300 ###################################################################### This script will monitor Phase 1 rebalance file by file and Phase 2 rebalance disk by disk. Both phases should increment, showing progress. This script will *not* estimate how long the rebalance will take. ###################################################################### Diskgroup being rebalanced is DATA ASM file numbers for databases start at 256. Default check interval is 600 seconds. This run using 300 seconds... Wed May 2 07:47:44 PDT 2012: PHASE 1 (of 2): Processing file 258 out of 365 Wed May 2 07:52:44 PDT 2012: PHASE 1 (of 2): Processing file 294 out of 365 Wed May 2 07:57:44 PDT 2012: PHASE 1 (of 2): Processing file 306 out of 365 Wed May 2 08:02:44 PDT 2012: PHASE 1 (of 2): Processing file 329 out of 365 Wed May 2 08:07:44 PDT 2012: PHASE 1 (of 2): Processing file 346 out of 365 Wed May 2 08:12:44 PDT 2012: PHASE 1 (of 2): Processing file 346 out of 365 Wed May 2 08:17:44 PDT 2012: PHASE 1 (of 2): Processing file 346 out of 365 Wed May 2 08:22:44 PDT 2012: PHASE 1 (of 2): Processing file 346 out of 365 Wed May 2 08:27:44 PDT 2012: PHASE 1 (of 2): Processing file 346 out of 365 Wed May 2 08:32:44 PDT 2012: PHASE 1 (of 2): Processing file 347 out of 365 Wed May 2 08:37:44 PDT 2012: PHASE 1 (of 2): Processing file 347 out of 365 Wed May 2 08:42:44 PDT 2012: PHASE 1 (of 2): Processing file 348 out of 365 Wed May 2 08:47:44 PDT 2012: PHASE 1 (of 2): Processing file 348 out of 365 Wed May 2 08:52:44 PDT 2012: PHASE 1 (of 2): Processing file 348 out of 365 Wed May 2 08:57:44 PDT 2012: PHASE 1 (of 2): Processing file 348 out of 365 ******************************************************* Wed May 2 09:02:49 PDT 2012: PHASE 1 (of 2) complete. ******************************************************* Wed May 2 09:02:51 PDT 2012: PHASE 2 (of 2): 44 disks processed out of 168 Wed May 2 09:07:52 PDT 2012: PHASE 2 (of 2): 44 disks processed out of 168 Wed May 2 09:12:53 PDT 2012: PHASE 2 (of 2): 76 disks processed out of 168 Wed May 2 09:17:54 PDT 2012: PHASE 2 (of 2): 76 disks processed out of 168 Wed May 2 09:22:55 PDT 2012: PHASE 2 (of 2): 108 disks processed out of 168 Wed May 2 09:27:57 PDT 2012: PHASE 2 (of 2): 140 disks processed out of 168 Wed May 2 09:32:58 PDT 2012: PHASE 2 (of 2): 168 disks processed out of 168 Rebalance is not running (no ARB0 ospid)
Check forward progress on an long running ASM resync if a problem is suspected
| Priority | Added | Machine Type | OS Type | Exadata Version | Oracle Version |
| Important | 5/16/2012 | X2-2(4170), X2-2, X2-8,X3-2, x3-8, X4-2 | Linux, Solaris | 11.2.x + | 11.2.x + |
Benefit / Impact:
Ensures a disk resync is making forward progress.
Risk:
Long running resyncs may be killed by customers if they are perceived to be stuck/hung/spinning
Action / Repair:
The following script can be run to check forward progress on running disk resyncs.
#!/bin/bash
# Goal
# 1. Provide assurance that resync is making progress by reporting the
# current file and extent being processed by each active resync slave.
# Considerations
# 1. Works file by file so bigger files will be reported longer
# 2. Only works with resyncs that are active; cannot be run ahead of time
# 3. Does not account for files added while the script is running
# 4. Interval < 16 seconds is not useful due to the tracing rate, therefore
# a minimum of 20 is enforced
# Requirements to run:
# 1. Set environment for ASM instance before running
# 2. Execute with "./resync_progress.sh <optional check interval
# greater than or equal to 20 seconds (default is 10 minutes)>"
function check_env {
if [[ $ORACLE_SID != +ASM* ]]; then
echo "Environment not set for ASM."
exit 1
fi
}
function check_running {
resyncrunning=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select count(*) from v\\$asm_operation where operation='ONLIN';
exit;
eof`
read -rd '' resyncrunning <<< "$resyncrunning"
if [ $resyncrunning -eq 0 ]; then
echo "Resync is not running on this instance (no ONLINE operation detected)"
exit 1
fi
}
function goto_trace_directory {
td=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select value from v\\$parameter
where name='background_dump_dest';
exit;
eof`
read -rd '' td <<< "$td"
cd $td
}
function process_updates {
while true
do
check_running
if [ $resyncrunning -ne $prior_running ]; then
echo " "
echo "*** $resyncrunning disk resyncs running on the local ASM instance ***"
echo " "
fi
# Things can change drastically in between intervals so we must collect everytime
slavepids=`ps -ef | grep 'asm_s[0-9][0-9][0-9]_' | sort -k 2 | grep -v grep | awk '{print $2}'`
if [ -z "$slavepids" ]; then
echo "Resync is not running on this instance (no slaves ospids detected)"
exit 1
fi
if [ $resyncrunning -gt 1 ]; then
echo "***"
fi
for slave in ${slavepids[@]}
do
slavename=`ps -ef | grep $slave | grep -v grep | awk '{print substr($8,5,4)}'`
read -rd '' $slavename <<< "$slavename"
sa[${slave}]=$slavename
tfile=${ORACLE_SID}_${sa[$slave]}_${slave}.trc
if [ ! -f $tfile ]
then
echo "The trace file $tfile doesn't exist."
exit 1
fi
slavegrp=`grep "NOTE: PST update grp =" $tfile | tail -1 | awk '{print $6}'`
sga[${slave}]=$slavegrp
maxfile=`sqlplus -s "/ as sysasm" << eof
set head off
set pagesize 0
select max(file_number) from v\\$asm_file
where GROUP_NUMBER = $slavegrp;
exit;
eof`
read -rd '' maxfile <<< "$maxfile"
smfa[${slave}]=$maxfile
secs_since_mod=`perl -e '@q=stat($ARGV[0]); print time-$q[9]' $tfile`
currentfile=`grep '^f[0-9][0-9][0-9]' $tfile | tail -1 | awk '{print $1}' | awk -F "f" '{print $2}'`
currentextent=`grep '^f[0-9][0-9][0-9]' $tfile | tail -1 | awk '{print $2}' | awk -F "x" '{print $2}' | awk -F"," '{print $1}'`
if [ $currentfile -eq 0 ] || [ $secs_since_mod -gt 20 ]; then
echo "`date`: RESYNC slave ${sa[$slave]}, ospid $slave : Not actively resyncing"
else
echo "`date`: RESYNC slave ${sa[$slave]}, ospid $slave : Processing diskgroup ${sga[$slave]}, file $currentfile (out of ${smfa[$slave]}), extent $currentextent"
fi
done
prior_running=$resyncrunning
sleep $check_interval
done
}
# Main
declare -a sa
declare -a sga
declare -a smfa
declare -i currentfile
declare -i maxfile
declare -i slavegrp
declare -i prior_running
declare -i secs_since_mod
check_env
check_running
goto_trace_directory
if [ -z "$1" ]; then
check_interval=600
else
if [ $1 -ge 20 ]
then
check_interval=$1
else
check_interval=20
fi
fi
echo "#########################################################################"
echo "This script will monitor disk resync, showing progress for each active"
echo "slave. This script will *not* estimate how long the resync will take."
echo "#########################################################################"
echo " "
echo "ASM file numbers for databases start at 256. "
echo "Default check interval is 600 seconds. This run using $check_interval seconds..."
prior_running=resync_running
process_updates
Sample output
$./resync_progress.sh 60 ######################################################################### This script will monitor disk resync, showing progress for each active slave. This script will *not* estimate how long the resync will take. ######################################################################### ASM file numbers for databases start at 256. Default check interval is 600 seconds. This run using 60 seconds... *** 1 disk resyncs running on the local ASM instance *** Tue May 15 10:18:43 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 15624 Tue May 15 10:19:44 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 21755 Tue May 15 10:20:46 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 22973 Tue May 15 10:21:48 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 23918 Tue May 15 10:22:49 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 24922 Tue May 15 10:23:51 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 36232 Tue May 15 10:24:53 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 43283 Tue May 15 10:25:55 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 43955 Tue May 15 10:26:56 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 48147 Tue May 15 10:27:58 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 48483 Tue May 15 10:29:00 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 48884 Tue May 15 10:30:01 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 49206 Tue May 15 10:31:03 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 60938 Tue May 15 10:32:05 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 61251 Tue May 15 10:33:07 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 61662 Tue May 15 10:34:08 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 61998 Tue May 15 10:35:10 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 62372 Tue May 15 10:36:11 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 62670 Tue May 15 10:37:13 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 63065 Tue May 15 10:38:14 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 63406 Tue May 15 10:39:16 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 63786 Tue May 15 10:40:18 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 325 (out of 387), extent 64122 Tue May 15 10:41:19 CDT 2012: RESYNC slave s000, ospid 106162 : Processing diskgroup 1, file 326 (out of 387), extent 824 Resync is not running on this instance (no ONLINE operation detected)
No comments:
Post a Comment