Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 8, 2007

ASM Enhancements

By Sreeram Surapaneni

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.dbf
    
    11 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 succeeded
    
    SQL> 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.

    » See All Articles by Columnist Sreeram Surapaneni



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date