Introduction:
ASM enhances the performance that raw disks provide, without the pain or difficulty of administrating the raw disks. Moreover, ASM provides storage mirroring policies that can be configured on a file level rather than at the volume level that traditional volume managers provide. Therefore, a combination of files that are mirrored and non-mirrored in the same ASM disk group can co-exist.
ASM spreads Oracle datafiles across all disks in the disk group, thereby eliminating any Hotspots (storage areas where tables that are being used aggressively are located). Above all, it allows adding and dropping disks online. All this can be done without any extra cost. Therefore, whenever we need to use a clustered filesystem, ASM is an important component as its resources, such as disks and files, are sharable and fit into shared storage architecture whether it is RAC or non-RAC clusterware.
ASM is widely deployed in companies where data volumes are constantly growing, for example cellular and telephone companies, large retail environments like Amazon and even some banking applications. I personally think in the future, ASM might replace all vendor products for database storage management.
In this article, I will summarize the new features of ASM in 10G R2 and discuss in more detail the features that I have been exploring recently.
New Features:
Release 2 expands the ASM capabilities to support multiple database versions but be aware that ASM utilizes the functionality of the lowest available version. What that means is, if you have 10.2 databases running on an ASM instance version 10.1, ASM does not use the functionality of 10.2 features of ASM and vice versa.
When you query the v$ASM_CLIENT view you can see two additional columns, compatible_Version and software_version, to incorporate this feature.
SQL> select * from v$asm_client;
GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS
———— —————————————– ——– ————
SOFTWARE_VERSION
————————————————————
COMPATIBLE_VERSION
————————————————————
1 +ASM1 QPRV CONNECTED
10.1.0.2.0
10.2.0.0.0
Release 2 also supports the consolidation of databases on a single node. What this means is with earlier versions of 10G, you can’t manage single instance databases and RAC instances of databases on a single node using one ASM instance on that node. In 10g R2, one ASM instance on a given node can manage the storage for a combination of database instances on that particular node, which makes the DBA’s storage administration much simpler. If you are already using two ASM instances to administer multiple database instances storage on a given node, you will need to migrate first to a single instance of ASM with the new 10g R2 binaries installed. Without this upgrade, we can’t have the simplicity of a single ASM instance.
Oracle Database 10g Release 2 offers an ASM command-line interface (ASMCMD) utility to access and manage files from a command prompt. Earlier versions have to use SQL*Plus or OEM to manage ASM.
Some examples of this command line Interface.
$ asmcmd
asmcmd: command disallowed by current instance type
# ORACLE_SID is wrong
$ echo $ORACLE_SID
ORCL
$ export ORACLE_SID=+ASM1
$ asmcmd
ASMCMD>
Identifying Diskgroups using ASMCMD:
ASMCMD> lsdg
State Type Rebal Unbal Sector Block
AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN
N N 512 4096 1048576 122876 46775 0 46775 0 DATABASE_DG/
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name
ORCL CONNECTED 10.2.0.1.0 10.2.0.1.0 ORCL1
Identifying data files using ASMCMD:
ASMCMD> cd +DATABASE_DG/qprv
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
devices_01.dbf
operations_01.dbf
users01.dbf
Finding out where controlfile is located on ASM:
ASMCMD> cd CONTROLFILE/
ASMCMD> ls
Current.260.563388683
Current.261.563388685
Current.262.563388685
Reclaiming Disk space using ASMCMD :
Let us assume you have three node RAC instances. Assume your database archive logs and backup sets are located on ASM storage. When you find that obsolete database archive logs and backups are taking up the ASM space and want delete them to reclaim space in ASM, do the following.
ASMCMD > CD +BACKUPDEST # change to the disk group
ASMCMD > PWD # current directory
ASMCMD > find . -t BACKUPSET.* # to find out backupset files in the diskgroup
Once the files have been listed, you can use the operating system rm command to delete them and use RMAN to crosscheck the backups that you have deleted.
Please note that — the -t flag can have values like DATAFILE, ARCHIVELOG, CONTROLFILE, ONLINELOG
ASMCMD> help
asmcmd [-p] [command]
The environment variables ORACLE_HOME and ORACLE_SID determine the instance to which the program connects, and ASMCMD establishes a bequeath connection to it, in the same manner as a SQLPLUS / AS SYSDBA. The user must be a member of the SYSDBA group.
Specifying the -p option allows the current directory to be displayed
in the command prompt, like so:
ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >
[command] specifies one of the following commands, along with its parameters.
Type “help [command]” to get help on a specific ASMCMD command.
Commands:
——–
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias
10g R2 offers more flexibility moving Files between and within databases: The preferred way of doing the file movement among ASM DISKGROUPS is using RMAN. RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.
However, in some cases, you may want to move files from a remote database server that does not use RMAN or to setup datagaurd; in these cases, we have the DBMS package called DBMS_FILE_TRANSFER. 10g R2 supports ASM to OS file AND OS File to ASM using this package. Earlier versions of 10G already support between ASM to ASM and OS to OS transfer of files.
Using this feature, a DBA can move data files from within Oracle without ‘physically’ logging into the operating system itself. This feature provides one more option when doing common database administrative tasks such as simple storage management, where we need to free up space on a disk. Let us see in the below example how we can move an ASM file to OS directory.
SQL> select tablespace_name,file_name from dba_Data_files;
TABLESPACE_NAME
——————————
FILE_NAME
————————————————————————————————————————
USERS
+DATABASE_DG/qprv/users01.dbf
SYSAUX
+DATABASE_DG/qprv/sysaux01.dbf
UNDOTBS1
+DATABASE_DG/qprv/undotbs01.dbf
SYSTEM
+DATABASE_DG/qprv/system01.dbf
UNDOTBS2
+DATABASE_DG/qprv/undotbs02.dbf
DEVICES
+DATABASE_DG/qprv/devices_01.dbf
SERVICES
+DATABASE_DG/qprv/services_01.dbf
OPERATIONS
+DATABASE_DG/qprv/operations_01.dbf
BBIDS_TBS
+DATABASE_DG/qprv/bbids_tbs_01.dbf
USERS
+DATABASE_DG/qprv/users02.dbf
TEST
+DATABASE_DG/qprv/test.dbf11 rows selected.
SQL> create or replace directory oracle_10g as ‘+DATABASE_DG/qprv/’;
Directory created.SQL> grant read,write on directory oracle_10g to ram;
Grant succeeded.SQL> create or replace directory oracle_dest as ‘+db04/oradata/qprv/’;
Directory created.
SQL> grant read,write on directory oracle_dest to ram;
Grant succeededSQL> ALTER DATABASE DATAFILE ‘+DATABASE_DG/qprv/test.dbf’
offline;
Database altered.
Now copy the file from the source ASM diskgroup to a normal OS filesystem directory.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘ORACLE_10G’,
source_file_name => test.dbf,
destination_directory_object => ‘ORACLE_DEST’,
destination_file_name => ‘TEST.DBF);
END;
Database altered
ASM/XDB FTP support is another new feature that allows the use of XDB and ASM with a user interface for getting files into and out of ASM-managed storage. When you select XML DB feature during the installation you will be able to create an ASM virtual folder. This folder is empty and is mounted on /sys/asm within XML DB hierarchy. Any operation on ASM virtual folder is transparently handled by ASM. DBA’s can explore this feature to configure different folders with different diskgroups. Each disk group folder can have one sub-folder and it may contain several files and folders. The FTP feature is quite useful to move the files in and out of these folders.
New ASM views and columns:
V$asm_disk_stat and v$asm_diskgroup_stat
These two views are identical to v$asm_disk and v$asm_diskgroup respectively but rather polled from memory instead of meta data from diskgroup. For accurate real-time information it would be good to query from v$asm_disk and v$asm_diskgroup but not during peak hours.
- new columns added to v$asm_diskgroup:
USABLE_FREE_SPACE: Indicates the amount of free space usable. In earlier releases, free space calculated from this view did not take mirroring into account.
REQUIRED_MB_FREE: This column is an estimate of space required during the disk recovery process when one or more disk failed.
REDUNDANCY_LOWERED: When one of the extents of data file is not mirrored then redundancy for a file becomes reduced from a level specified by the DBA. This column is added to v$asm_file to identify this reduction in redundancy. Values for this column are YES or NO.
Conclusion:
New features in ASM make the DBA’s administration simpler, especially node administration with multiple instances on a given node. ASM features eliminate the need for any third party software for volume management and file system for database files. Additional automation in the ASM features makes ASM a more cost effective stable solution.
- new columns added to v$asm_diskgroup:
