Home > Storage > Unity XT > Virtualization, Cloud & Applications > Dell Unity XT: Oracle Database Best Practices > Oracle Automatic Storage Management
Dell Technologies and Oracle recommend using Oracle Automatic Storage Management (ASM) to manage Dell Unity LUNs for the database and clusterware. This section reviews the general guidelines and additional considerations for an Oracle database.
LUNs intended for Oracle ASM must have their user and group ownership and permissions set correctly. Group and ownership must be set to the group and owner of the ASM instance. Permissions must be set to read/write. For example, if user grid with primary group oinstall is the owner of the ASM instance, grid:oinstall should be assigned to the LUNs. There are different methods to set the ownership and permissions and keep these settings persistent across host reboot.
Persistent device ownership and permission can be managed through various software. Some of the commonly used software is:
The Linux udev facility comes with every Linux distribution and is easy to set up for persistent device ownership and permission by creating rules in the udev rule file. System rule files are in directory /usr/lib/udev/rules.d, and user-defined rule files are in directory /etc/udev/rules.d. There are many ways to define a device in the rule file. Two examples are provided as follows.
Example 1: Set device ownership and permission by WWNs
Define a rule for each Dell Unity LUN using its unique WWN. With this approach, each LUN requires an udev rule. Rules are defined in /etc/udev/rules.d/99-oracle-asmdevices.rules. The following example shows an udev rule that sets grid:oinstall ownership and 660 permission on a dm (multipath) device that matches the WWN 36006016010d04200b584ce59557ba84a.
# cat /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="dm-*",PROGRAM=="/lib/udev/scsi_id --whitelisted --device=/dev/$name",RESULT=="36006016010d04200b584ce59557ba84a",ACTION=="add|change",OWNER="grid",GROUP="oinstall",MODE="0660"
<snippet>
# udevadm control --reload-rules
# udevadm trigger
If PowerPath is used, change KERNEL=="dm-*" to KERNEL=="emcpower*".
Example 2: Set device ownership and permission by device name pattern
The udev rule can be simplified if multipath device aliases are created with a consistent string pattern. For example, use prefix ORA- in all multipath device aliases for LUNs intended for Oracle ASM. A single udev rule can be used to set ownership and permission on all ORA* multipath devices. See section Creating aliases.
# cat /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="dm-*",ENV{DM_NAME}=="ORA*",OWNER="grid",GROUP="oinstall",MODE="0660"
# udevadm control --reload-rules
# udevadm trigger
The advantage of this approach is that only multipath.conf needs to be updated when new LUNs are added to the system for Oracle ASM.
Oracle ASMLib simplifies storage management and reduces kernel resource usage. It provides device file name, ownership, and permission persistency and reduces the number of open file handles required by the database processes. No udev is required when ASMLib is used.
When LUNs are initialized with ASMLib, special device files are created in the /dev/oracleasm/disks folder with proper ownership and permission automatically. When the system reboots, the ASMLib driver restarts and re-creates the device files. ASMLib consists of three packages:
Each Linux vendor maintains their oracleasm kernel driver (oracleasm-kernel-version.arch.rpm). With Oracle Linux, the kernel driver is already included with Oracle Linux Unbreakable Enterprise Kernel. For more information about ASMLib and to download the software, go to http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html.
The ownership of the ASMLib device's is defined in configuration file /etc/sysconfig/oracleasm which is generated by running:
/etc/init.d/oracleasm configure
If necessary, update file /etc/sysconfig/oracleasm to reflect proper ownership and the disk scanning order.
# cat/etc/sysconfig/oracleasm
# ORACLEASM_ENABLED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true
# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=grid
# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=oinstall
# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="dm"
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"
# If the physical block size of block devices is 4K, then
# uncomment the following line:
#ORACLEASM_USE_LOGICAL_BLOCK_SIZE=true
# However, if the physical block size is 512, then
# uncomment the following line:
#ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false
This configuration file indicates grid:oinstall for the ownership, and it searches for multipath devices (dm) and excludes any single path devices (sd).
If PowerPath devices are used, set ORACLEASM_SCANORDER to “emcpower” rather than to “dm”:
ORACLEASM_SCANORDER="emcpower"
Note: The asterisk (*) cannot be used in the value for ORACLEASM_SCANORDER and ORACLEASM_SCANEXCLUDE.
Oracle requires the LUNs to be partitioned for ASMLib use. First, create a partition with parted, and then use oracleasm to label the partition. ASMLib does not provide multipath capability and relies on native or third-party multipath software to provide the function. The following example shows creating an ASMLib device on a partition of a Linux Multipath device. Command oracleasm writes the ASMLib header to /dev/mapper/mpathap1 and generates the ASMLib device file in /dev/oracleasm/disks/DATA01 with ownership as indicated in /etc/sysconfig/oracleasm.
# oracleasm createdisk DATA01 /dev/mapper/mpathap1
Oracle ASM Filter Driver (ASMFD) is a kernel module that sits between the operating system kernel and Oracle ASM. Oracle intends to replace Oracle ASMLib with ASMFD and recommends using ASMFD in Oracle 12c and above. ASMFD includes all the ASMLib benefits of storage device name, ownership, and permission persistency, and better kernel usage by reducing the number of open file handles. Also, it provides storage protection by rejecting non-oracle I/Os and hence prevents inadvertent overwrite of the ASM disks.
In a cluster environment, without ASMFD, when a cluster node is fenced, the host must be rebooted to ensure the integrity of the data. With ASMFD, the fenced node does not need to be rebooted. It is possible to restart the clusterware stack which reduces the time to recover the node.
Unlike ASMLib, ASMFD comes with the Grid Infrastructure software and there is no additional software to download. Starting with Oracle ASM 12c Release 2, the installation and configuration for Oracle ASMFD have been simplified by integrating the option into the Oracle Grid Infrastructure installation. Administrators need to select the option Configure Oracle ASM Filter Driver during the Grid installation.
The installation of ASMFD automatically creates an udev rule file in /etc/udev/rules.d/53-afd.rules that sets the afd devices with the proper ownership and permission. Do not attempt to modify or delete this file directly. Use command asmcmd adf_configure to make updates instead.
# cat /etc/udev/rules.d/53-afd.rules
#
# AFD devices
KERNEL=="oracleafd/.*", OWNER="grid", GROUP="oinstall", MODE="0775"
KERNEL=="oracleafd/*", OWNER="grid", GROUP="oinstall", MODE="0775"
KERNEL=="oracleafd/disks/*", OWNER="grid", GROUP="oinstall", MODE="0664"
Either whole LUNs or LUN partitions can be used for ASMFD devices. Dell Technologies recommends using whole LUNs because of certain restrictions with partitions which affect database availability during storage expansion. See the section Expand Oracle ASM storage for more details.
The following example shows creating an ASMFD device on a Linux multipath device. The asmcmd afd_label command writes the ASMFD header to /dev/mapper/mpathb and generates the ASMFD device file in /dev/oracleafd/disks/DATA01. The udev rule ensures the afd devices are set to grid:oinstall and 0664 permission.
# asmcmd afd_label DATA01 /dev/mapper/mpatha
The other advantage of using ASMFD is that it supports thin-provisioned disk group starting in Oracle release 12.2.0.1.
To find out which operating system platforms ASMFD is supported on, see Oracle KB Doc ID 2034681.1 at Oracle Support.
For more information about installing and configuring ASMFD, refer to the Oracle Automatic Storage Management Administrator’s Guide.
ASM instance parameter asm_diskstring tells ASM the location of the ASM devices. During the Grid Infrastructure installation, it defaults to null and it should be updated to reflect the correct location of the device files.
Device files managed by | asm_diskstring setting |
Linux native multipath | asm_diskstring=’/dev/mapper/ORA*’ |
Dell PowerPath | asm_diskstring=’/dev/emcpower*’ |
Oracle ASMLib | asm_diskstring=’ORCL:*’ |
Oracle ASMFD | asm_diskstring=’AFD:*’ |
Dell Technologies and Oracle recommend using Oracle ASM as the preferred storage management solution for either single-instance or Real Application Clusters (RAC) databases. ASM takes the place of the traditional Linux Logical Volume Manager (LVM) and file system and manages the disks and disk groups where Oracle datafiles reside.
ASM offers many advantages over the traditional Linux LVM. The main benefits include:
When creating an Oracle ASM disk group, consider the following guidelines:
create tablespace DATATS
datafile '+DATADG'
size 10G
autoextend on
next 1024M
maxsize unlimited;
ALTER DISKGROUP DATADG
SET ATTRIBUTE 'THIN_PROVISIONED'='TRUE';
ALTER DISKGROUP DATADG
SET ATTRIBUTE '_rebalance_compact'='FALSE';
For more information about ASM compact phase rebalancing, see Oracle KB Doc ID 1902001.1 on Oracle Support.
Table 5 demonstrates an example of how ASM disk groups are organized. Figure 5 illustrates the storage layout on the database, ASM disk group, and Dell Unity system levels.
Database | ASM disk group | Number of LUNs | LUN size | Dell Unity consistency group | Description |
Clusterware | GIDATA | 2 | 10 GB | N/A | Clusterware-related information such as the OCR and voting disks |
Grid Infrastructure Infrastructure Management Repository | MGMT | 2 | 50 GB | mgmt_cg | In 12cR2, a separate disk group created for the GI Managemnt Repository data. |
Test database (testdb) | DATADG | 2 | 200 GB | testdb_cg | Disk group that holds the database files, temporary table space, and online redo logs; contains system-related table spaces such as SYSTEM and UNDO Contains only testdb data |
Test database (testdb) | FRADG | 2 | 100 GB | testdb_cg | Disk group that holds the database archive logs and backup data Contains only testdb logs |
Development database (devdb) | DATA2DG | 2 | 200 GB | devdb_cg | Disk group that holds the database files, temporary table space, online redo logs; contains system-related table spaces such as SYSTEM and UNDO Contains only devdb data |
Development database (devdb) | FRA2DG | 2 | 100 GB | devdb_cg | Disk group that holds the database archive logs and backup data Contains only devdb logs |
For performance reasons, it is common for a database to span multiple LUNs to increase I/O parallelism to the storage devices. Dell Technologies recommends grouping the LUNs into a consistency group for a database to ensure data consistency when taking storage snapshots. The Dell Unity system snapshot feature is a quick and space-efficient way to create a point-in-time snapshot of the entire database. Section Data protection and recovery discusses using Dell Unity system snapshots and thin clones to reduce database recovery time and create space-efficient copies of the database.
In Figure 5, for example, the RAC database consists of disk group +DATADG and +FRADG. All ASM volumes in those disk groups are configured in a single consistency group, testdb_cg. The single instance database consists of disk groups +DATA2DG and +FRA2DG. The ASM devices of both disk groups are configured in a consistency group, devdb_cg.
The consistency group feature allows taking a database-consistent snapshot across multiple LUNs. On the database side, use the ALTER DATABASE BEGIN BACKUP clause before the snapshot is taken and END BACKUP clause after the snapshot is taken.
Note: Storage snapshots taken on a multiple-LUN database without a consistency group might be irrecoverable by Oracle during database recovery.
As the storage consumption grows over time, it is necessary to increase and grow the existing storage capacity both in the Dell Unity system and in the database. It is most desirable to add capacity online with minimal business interruptions. The Dell Unity system has the flexibility to expand the current storage system with no interruption to the application. The following nondisruptive operations can be performed online in Unisphere:
The following subsections discuss the different ways to increase ASM storage capacity.
More storage capacity can be added to an ASM disk group by adding new LUNs to the disk group. The advantage of this method is that the process is relatively simple and safe because no changes are made to the existing LUNs.
The following outlines the general process:
Since ASM automatically rebalances the data after new LUNs are added, it is recommended to add the LUNs in a single operation to minimize the amount of rebalancing work. The following example shows the SQL*Plus ALTER DISKGROUP ADD DISK statement to add multiple devices to a disk group.
ALTER DISKGROUP DATADG
ADD DISK 'AFD:DATADG_VOL1',
'AFD:DATADG_VOL2'
REBALANCE POWER 10
NOWAIT;
asmcmd lsdsk -gk -G datadg
asmcmd lsdg –g datadg
Note: Adding or removing LUNs in a consistency group is not allowed when there are existing snapshots of the consistency group. To add or remove LUNs in a consistency group, delete all snapshots and retry the operation.
The Dell Unity system can extend the size of existing LUNs online. However, depending on the operating system, disk partition configuration, and Oracle software chosen, resizing ASM disks online might not be possible. Customers should consult with each vendor to fully understand the capability and limitations of their software.
Note: Resizing LUNs on the operating system can cause loss of data or corruption. It is recommended to back up all data before attempting to resize the LUNs.
When not using ASMFD or ASMLib, and only using whole LUNs, it is possible to resize the devices online on a wide range of operating systems and Oracle versions. Customers should consult with each vendor to fully understand the capability and limitations of their software.
The following outlines the general steps to resize ASM devices online when ASMFD or ASMLib are not used to manage the ASM devices.
multipathd -k"resize map DATA03"
If PowerPath is used instead of dm-multipath, the new size is automatically updated.
# multipath -ll ORA-TEST3 | egrep size
size=400G features='2 queue_if_no_path retain_attached_hw_handler' hwhandler='1 alua' wp=rw
# multipath -ll ORA-TEST3 | awk '/sd/ {print $(NF-4)}' | xargs -i fdisk -l /dev/{} | egrep "^Disk"
Disk /dev/sdfd: 429.5 GB, 429496729600 bytes, 838860800 sectors
Disk /dev/sdff: 429.5 GB, 429496729600 bytes, 838860800 sectors
Disk /dev/sdfh: 429.5 GB, 429496729600 bytes, 838860800 sectors
[snipped]
# fdisk –l /dev/emcpowerc
# powermt display dev=all|awk '/sd/ {print $3}'|xargs -i fdisk -l /dev/{}|egrep "^Disk"
# asmcmd lsdsk –k
Inst_ID Total_MB Free_MB OS_MB Name Failgroup Site_Name Site_GUID Site_Status Failgroup_Type Library Label Failgroup_Label Site_Label UDID Product Redund Path
1 204800 409464 409600 TEST3DG_0000 TEST3DG_0000 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/mapper/ORA-TEST3
2 204800 409464 409600 TEST3DG_0000 TEST3DG_0000 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/mapper/ORA-TEST3
Total_MB represents the current size before the resize operation.
OS_MB represents the new maximum size ASM can expand to.
ALTER DISKGROUP TEST3DG
RESIZE DISK TEST3DG_0000
SIZE 409600M
REBALANCE POWER 10;
# asmcmd lsdsk -k
Inst_ID Total_MB Free_MB OS_MB Name Failgroup Site_Name Site_GUID Site_Status Failgroup_Type Library Label Failgroup_Label Site_Label UDID Product Redund Path
1 409600 409464 409600 TEST3DG_0000 TEST3DG_0000 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/mapper/ORA-TEST3
2 409600 409464 409600 TEST3DG_0000 TEST3DG_0000 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/mapper/ORA-TEST3
Run asmcmd lsdg to confirm the Total_MB value on the disk group has increased.
# asmcmd lsdg
Inst_ID State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
1 MOUNTED EXTERN N 512 512 4096 4194304 409600 409464 0 409464 0 N TEST3DG/
2 MOUNTED EXTERN N 512 512 4096 4194304 409600 409464 0 409464 0 N TEST3DG/
When Oracle 12.2 and newer ASMFD is used with ASM devices, it is possible to resize the ASM device online without impacting the database. For Oracle version 12.1, the option afd_refresh is not available.
Resize ASM devices online:
The following outlines the general steps to resize ASM devices with ASMFD online.
multipathd -k"resize map ORA-TEST3"
If PowerPath is used instead of dm-multipath, the new size is automatically updated.
# multipath -ll ORA-TEST3 | egrep size
# multipath -ll ORA-TEST3 | awk '/sd/ {print $(NF-4)}' | xargs -i fdisk -l /dev/{} | egrep "^Disk"
# fdisk –l /dev/emcpowerc
# powermt display dev=all|awk '/sd/ {print $3}'|xargs -i fdisk -l /dev/{}|egrep "^Disk"
# asmcmd afd_refresh
# asmcmd lsdsk –k
ALTER DISKGROUP DATADG
RESIZE DISK DATA03
SIZE $OS_MB
REBALANCE POWER 10;
# asmcmd lsdsk -k
Run asmcmd lsdg to confirm the Total_MB value on the disk group has increased.
# asmcmd lsdg
Resize ASM devices offline:
As mentioned previously in this section, the online resize capability of ASMFD is available in Oracle 12.2. With Oracle 12.1, either restart the host to refresh the LUN size, or restart the clusterware, ASM instance, and the AFD driver on the host to minimize the outage window. In a cluster environment, refreshing the LUN size can be done in a rolling fashion to further minimize the impact of the outage.
# crsctl stop crs
# afdload stop
# afdload start
# asmcmd afd_scan
# crsctl start crs
Another alternative to restarting the node or software is to unlabel and label the AFD devices. The database associated with the devices must be stopped, and the disk groups and devices must be unmounted before they can be relabeled. This approach increases the risk of data loss and corruption and requires extra caution.
ASMLib required partitioned LUNs. To increase the size of the partition after expanding the LUN, the partition is first removed and then re-created with the new size. The database associated with the device will be impacted.
The following outlines the general steps to resize ASM devices with ASMLib online.
# rescan-scsi-bus.sh --resize
multipathd -k"resize map TEST67_ASMLIB"
If PowerPath is used instead of dm-multipath, the new size is automatically updated.
# multipath -ll TEST67_ASMLIB | egrep size
# multipath -ll TEST67_ASMLIB | awk '/sd/ {print $(NF-4)}' | xargs -i fdisk -l /dev/{} | egrep "^Disk"
# fdisk –l /dev/emcpowerc
# powermt display dev=all|awk '/sd/ {print $3}'|xargs -i fdisk -l /dev/{}|egrep "^Disk"
$ srvctl stop db -d demodb
ALTER DISKGROUP TEST67_ASMLIBDG DISMOUNT;
parted /dev/mapper/TEST67_ASMLIB rm 1
parted /dev/mapper/TEST67_ASMLIB mkpart primary 2048s 100%
rescan-scsi-bus.sh –resize
multipathd -k"resize map TEST67_ASMLIB"
If PowerPath is used instead of dm-multipath, the new size is automatically updated.
partprobe /dev/mapper/TEST67_ASMLIB
parted /dev/mapper/TEST67_ASMLIB u GB p
parted command should show the new size.
oracleasm scandisks
oracleasm listdisks
ALTER DISKGROUP TEST67_ASMLIBDG MOUNT;
# asmcmd lsdsk –k –g –G TEST67_ASMLIBDG
ALTER DISKGROUP TEST67_ASMLIB
RESIZE DISK TEST67_ASMLIB
SIZE $OS_MB
REBALANCE POWER 10;
# asmcmd lsdsk -k
Run asmcmd lsdg to confirm the Total_MB value on the disk group has increased.
# asmcmd lsdg
$ srvctl start db -d demodb
Dell Unity system supports the SCSI TRIM/UNMAP feature which allows operating systems to inform which data blocks are no longer in use and can be released for other uses. For space reclamation to work, the LUNs must be thin provisioned in the Dell Unity system and the Linux kernel, and Oracle ASM must also support the feature. The TRIM/UNMAP feature has been introduced in Linux kernel 2.6.28-25 and newer. With Oracle 12.2 ASMFD, thin-provisioned ASM diskgroups allow deleted space in datafiles to be reclaimed.
To verify the availability of the feature on the Linux operating system, query /sys/block/$disk/queue/discard_granularity. If the value is zero, it means the device does not support discard functionality. For example, since device sdx has a non-zero discard_granularity value, its free space will be reclaimed with TRIM/UNMAP.
# cat /sys/block/sdx/queue/discard_granularity
8192
The following outlines the general steps to reclaim storage space in Oracle ASM.
ALTER TABLE <$TABLE_NAME> ENABLE ROW MOVEMENT;
ALTER TABLE <$TABLE_NAME> SHRINK SPACE;
# cat find_datafile_hwm.sql
set verify off line 200 pages 100
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a85 word_wrapped
select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
The prior script generates the following output.
VALUE
--------------------------------------------------------------------------
8192
Smallest
Size Curr Poss.
FILE_NAME Poss. Size Savings
---------------------------------------------- -------- ------ --------
+DATADG/DEMODB/DATAFILE/system.257.952265543 841 850 9
+DATADG/DEMODB/DATAFILE/demots.289.952606307 16,328 17,014 686
+DATADG/DEMODB/DATAFILE/undotbs1.259.952265593 12,932 24,708 11,776
+DATADG/DEMODB/DATAFILE/undotbs2.265.952265669 37 1,024 987
+DATADG/DEMODB/DATAFILE/demots.337.952606345 17,220 17,652 432
+DATADG/DEMODB/DATAFILE/demots.320.952606331 16,712 17,462 750
+DATADG/DEMODB/DATAFILE/sysaux.258.952265577 2,008 2,030 22
+DATADG/DEMODB/DATAFILE/users.260.952265593 1 5 4
--------
sum 14,666
8 rows selected.
CMD
-------------------------------------------------------------------------------------
alter database datafile '+DATADG/DEMODB/DATAFILE/system.257.952265543' resize 841m;
alter database datafile '+DATADG/DEMODB/DATAFILE/demots.289.952606307' resize 16328m;
alter database datafile '+DATADG/DEMODB/DATAFILE/undotbs1.259.952265593' resize
12932m;
alter database datafile '+DATADG/DEMODB/DATAFILE/undotbs2.265.952265669' resize 37m;
alter database datafile '+DATADG/DEMODB/DATAFILE/demots.337.952606345' resize 17220m;
alter database datafile '+DATADG/DEMODB/DATAFILE/demots.320.952606331' resize 16712m;
alter database datafile '+DATADG/DEMODB/DATAFILE/sysaux.258.952265577' resize 2008m;
alter database datafile '+DATADG/DEMODB/DATAFILE/users.260.952265593' resize 1m;
8 rows selected.
ALTER DISKGROUP DATADG REBALANCE POWER 10;
Note: Deleted space is not released until either the datafiles are deleted or shrunk and a rebalance operation is run against the ASM disk groups.