Sunday, July 27, 2014

All About ASM


In this post I'll try to provide a brief summary for a huge topic like Automatic Storage Management (ASM), hopefully the reader find it useful in understanding ASM.

-----------------
What is ASM:
-----------------
Automatic Storage Management (ASM) is a new feature in Oracle 10g onward provides a state of the art storage for Oracle database files.

-------------
Why ASM:
-------------
I'll illustrate "Why ASM?" through Pro's & Con's approach to show you the full image and let you easily judge ASM ...

ASM Pro's:
> Provides shared filesystem for RAC.
> Provides MIRRORING & STRIPPING in the same time for all data across all disks [inside the diskgroup],without any additional work by the DBA, [DBA will only add/remove disks then ASM will take care of re-balancing the data (less human errors)].

> Adding new disk to the diskgroup will not only increase the space but will improve throughput of that diskgroup.
> Unlike other vendors, hot spare disks are not required for mirroring, the thing provides more efficient use of the storage capacity.
> Mirroring level is into Allocation Unit size (AU is the smallest unit in ASM) (1MB by default), opposed to other vendors that do it at disk level.
> Striping level is into Allocation Unit size (AU is the smallest unit in ASM) (1MB by default), opposed to other vendors that do it at disk level, the thing provides better performance and I/O latency, for ASM. I/O is spread evenly across all available disk to maximize performance.
> When you add/remove disks from a disk group, ASM will automatically redistributes the file contents and eliminates the need for downtime.
> Raw device performance, can improve I/O performance.
> Ability to Dynamically Add and Remove Disk/Space while database is running.
> Mirroring / Triple mirroring equivalence & Stripping across different storage vendors.
> ASM can coexist with other storage types.
> ASM can work with any storage (SAN, NAS, SSD, Direct Attached Storage).
> Flexible Redundancy Options.
> ASM can automatically handle persistent naming for partitions.
> Works with Oracle Managed Files (OMF).
> ASM files cannot be accessed from OS, more secure and less human errors.
> The database can contain ASM and NON-ASM files.
> Safe, If a permanent disk IO error is incurred during an Oracle write operation, then the affected disk is removed from the diskgroup by ASM, thus preventing more application failures. If the loss of a disk results in data loss, ASM will automatically dismount the diskgroup to protect the integrity of the diskgroup data. 

> If read error occur because of a corrupted block, ASM will automatically read the good copy of the extent from other mirrored disks and then copy it to the disk that had read errors.
> Free of cost, NO license required.

ASM Con's:
> Hardware RAID 1 mirroring [same storage type] avoid the overhead on the server unlike ASM mirroring.
> RMAN is the only way to backup the database (NO cold OS backups), datapump still can be used for data transfer.
> OS Utilities like "dd" command are not recommended for backing up/restoring ASM diskgroups.
> ASM files are not accessible from OS, you need to use ASMCMD built-in tool for that purpose which still doesn't have all file/directory management commands.
> Each time you upgrade your kernel, you must upgrade ASM packages a long with it, as it is a kernel-version-specific.
> ASM have an instance that must start first before the DB instance start. [Allocate memory on the server]
> Require specific ASM home installation for 11gr1 backward, for 11gr2 grid infrastructure onward RAC & ASM installation are on the same place.
> Difficult to move files (e.g. archives, RMAN backup, dump files) between servers, as it need to be extracted out of ASM first.

> I usually consider using ASM as an additional point of failure & complexity to the system due to its dependency on the ASM instance.

--------------
ASM Facts:
--------------
 > ASM FS will use an ASM instance running on the server to mount the diskgroups and make it available for databases.
> The MAXIMUM SIZE of ASM disk is 2TB [10g,11g], 32 petabytes (PB) [12c].

> The MINIMUM SIZE of ASM disk is 4Mb.
> ASM size limits: 63 disk groups | 10,000 ASM disks | 1 million files for each disk group.
> ASM allocation Unit (AU) is from 1MB to 64MB, the larger the better for Data warehouse DB and vice versa for OLTP database the smaller the better.
> ASM disks inside the diskgroup can be varied sized (Not recommended).
> Recommended to have 4 disks [same size/performance] for each diskgroup.
> Oracle Clusterware 12c, no longer support the use of raw (block) storage devices.
> ASM instance name starts with "+".
> only one ASM instance is required per node regardless of the number of database instances on the node, and one ASM instance for each RAC node.
> ASM instance is similar to normal DB instance except that ASM instance highest mode is the MOUNT mode where it mounts all diskgroups.
> ASM instance restriction mode will prevent DB instances from connecting to ASM instance.   
> ASM instance doesn't have controlfiles like DB instance.
> ASM instance should start first before the DB instance to make the diskgroups available for the DB instance.
> ASM can be on a higher or lower version than it's client databases.
> A shutdown/crash of standalone [NON RAC] ASM instance will cause all client-database instances to shutdown as well.
> Both ASM & DB instance should be on the same server, DB instance cannot connect remotely to ASM instance.
> Most of DB instance parameters are not valid for ASM instance, ASM instance should have parameter INSTANCE_TYPE=ASM.
> When upgrading ASM instance (similar to DB) there is NO persistent changes to disk groups as part of upgrade.
> ASM’s SGA is 256M by default [11gr1 onwards] which more than enough for ASM, no need to adjust the SGA whatever the number of supported diskgroups or databases.
> ASM does not require that all disks have consistent names across servers, as it using ASM disk names.
> ASM disks inside diskgroup should have the same size, to avoid imbalances and should have the same speed, whereas I/O distribution is constrained by the slowest drive.
> Oracle ASM only discovers disk partitions. Oracle ASM does not discover partitions that include the partition table.
> Multiple databases can share the same diskgroup.


Redundancy in ASM: 
Data redundancy in ASM are three levels (High, Normal and External):

High     => Data will be mirrored 3 times & stripped on all disks[require at least 3 failure group]. Failure Group store mirror copies of data inside Disk Group.
Normal   => Data will be mirrored 2 times & stripped on all disks[require at least 2 failure group].
External => Data will not be mirrored nor stripped. [No failure group]

Note: It's recommended that each failure group disk should be on a different disk controller to avoid single point of failure.
Note: Extent distributions are coarse and fine: [Both are predefined and cannot be adjusted by the DBA]
COARSE: For datafiles, redologs [11gr2+] and archivelogs, each coarse grain extent file extent is mapped to a single allocation unit. 
FINE: For controlfiles, reduces latency by spreading the load more widely.

------------------------------
 ASM Implementation:
------------------------------

OS Pre-requisities:
================

>At least 1GB of free available memory on server
>At least 1.8GB of free swap space
>The following Linux packages should be installed:
    rpm -Uvh libaio-0
    rpm -Uvh libaio-devel-0.3.105-2.i386.rpm
    rpm -Uvh elfutils-libelf-0
    rpm -Uvh elfutils-libelf-devel-0.91.1-3.i386.rpm
    rpm -Uvh unixODBC-2.2.11-1.RHEL4.1.i386.rpm

>Oracle user
>Oracle dba group
>Ensure the following lines exist in the /etc/security/limits.conf
    * hard nproc 16384
    * hard nofile 65536

>Ensure the limits are configured to set for new sessions by ensuring the following are in the /etc/pam.d/system-auth file.
    session required pam_limits.so
    session required pam_unix.so

>Ensure the following lines exist in the .bash_profile for the oracle user
    ulimit -n 65536
    ulimit -u 16384

>Ensure oracle primary group dba
    $ usermod –g dba oracle
>set the following in the /etc/sysctl.conf
    kernel.msgmnb = 65536
    kernel.msgmax = 65536
    kernel.shmmax = 4294967295
    kernel.shmall = 268435456
    kernel.sem=250 32000 100 128
    net.ipv4.ip_local_port_range = 1024 65000 < 11gR1
    net.ipv4.ip_local_port_range = 9000 65500 >= 11gR2
    net.core.rmem_default=4194304
    net.core.wmem_default=262144
    net.core.rmem_max=4194304
    net.core.wmem_max=1048576
    fs.file-max = 6815744
    fs.aio-max-nr = 1048576

>As a best practice, ensure to install the latest version of ASM even if DB version is lower.

Disk preparation:  [All steps will run by ROOT user]
==============

Partition the disks with fdisk utility: Create RAW partitions:
---------------------------------
# fdisk /dev/sda
    n
    p
    1

    Specify the size of the partition e.g. +10000M or accept the default to allocate the whole disk
    p
    w


A new device with name sda1 will appear under /dev
Repeat the same fdisk steps for the other disks...

# partprobe     =>To start use the new partitions and avoid rebooting the system.

# fdisk -l
# chmod 660 /dev/sda
# chmod 660 /dev/sda1
# chown oracle:dba /dev/sda
# chown oracle:dba /dev/sda1

Add these lines (owner,permissions) to /etc/rc.d/rc.local
# vi /etc/rc.d/rc.local
chmod 660 /dev/sda
chmod 660 /dev/sda1
chown oracle:dba /dev/sda
chown oracle:dba /dev/sda1



    In case you are preparing a test machine [For Testing Purposes]: (Create files to act as dummy disks 100m size for each)
    --  ----------------------------------------------------
        # dd if=/dev/zero of=/u02/asmdisk1 bs=1024k count=100
        # dd if=/dev/zero of=/u02/asmdisk2 bs=1024k count=100
        # dd if=/dev/zero of=/u02/asmdisk3 bs=1024k count=100
        # dd if=/dev/zero of=/u02/asmdisk4 bs=1024k count=3000
        # /sbin/losetup /dev/loop1 /u02/asmdisk1
        # /sbin/losetup /dev/loop2 /u02/asmdisk2
        # /sbin/losetup /dev/loop3 /u02/asmdisk3
        # /sbin/losetup /dev/loop4 /u02/asmdisk4
        # chmod 660             /dev/loop1 /dev/loop2 /dev/loop3 /dev/loop4
        # chown oracle:oinstall /dev/loop1 /dev/loop2 /dev/loop3 /dev/loop4
            => The following not mandatory:
            # raw /dev/raw/raw1  /dev/loop1
            # raw /dev/raw/raw2  /dev/loop2
            # raw /dev/raw/raw3  /dev/loop3
            # raw /dev/raw/raw3  /dev/loop4
            # chmod 660             /dev/raw/raw1 /dev/raw/raw2 /dev/raw/raw3 /dev/raw/raw4
            # chown oracle:oinstall /dev/raw/raw1 /dev/raw/raw2 /dev/raw/raw3 /dev/raw/raw4

        # vi /etc/rc.local
         # Add these lines
         /sbin/losetup /dev/loop1 /u02/asmdisk1
         /sbin/losetup /dev/loop2 /u02/asmdisk2
         /sbin/losetup /dev/loop3 /u02/asmdisk3
         /sbin/losetup /dev/loop4 /u02/asmdisk4
         chmod 660 /dev/loop1
         chmod 660 /dev/loop2
         chmod 660 /dev/loop3
         chmod 660 /dev/loop4
         chown oracle:oinstall /dev/loop1
         chown oracle:oinstall /dev/loop2
         chown oracle:oinstall /dev/loop3
         chown oracle:oinstall /dev/loop4
             raw /dev/raw/raw1 /dev/loop1
             raw /dev/raw/raw2 /dev/loop2
             raw /dev/raw/raw3 /dev/loop3
             raw /dev/raw/raw3 /dev/loop4
             chmod 660 /dev/raw/raw1
             chmod 660 /dev/raw/raw2
             chmod 660 /dev/raw/raw3
             chmod 660 /dev/raw/raw4
             chown oracle:oinstall /dev/raw/raw1
             chown oracle:oinstall /dev/raw/raw2
             chown oracle:oinstall /dev/raw/raw3
             chown oracle:oinstall /dev/raw/raw4

            => The following not mandatory: [Disk Labeling]
            # /etc/init.d/oracleasm createdisk ASMDISK1 /dev/loop1

 

ASM with Multipathing: [Doc ID 294869.1]
-----------------------
ASM produces an error if ASM discovers multiple disk device paths [Two or more HBAs or initiators provide load-balancing & failover ].
Because a single disk can appear multiple times in a multipath configuration, you must configure ASM to discover only the multipath disk, by setting ASM_DISKSTRING equal to the name of the pseudo device that represents the multipath disk.
E.g.
For EMC PowerPath    set ASM_DISKSTRING='/dev/rdsk/emcpower*'
[hp] EMC PowerPath   set ASM_DISKSTRING='/dev/rdsk/cxtydz*'
[SUN/LNX] EMC PowerPath    set ASM_DISKSTRING='/dev/rdsk/emcpowerx'
IBM                             set ASM_DISKSTRING='/dev/rhdiskx*'
Fujitsu                          set ASM_DISKSTRING='/dev/rhdisk*'
[SUN/LNX] Fujitsu     set ASM_DISKSTRING='/dev/FJSVmplb/[r]dsk/mplbI*1s*'
Hitachi HDLM             set ASM_DISKSTRING='/dev/rdsk/dlmfdrvx*'
[hp/SUN] Hitachi HDLM    set ASM_DISKSTRING='/dev/rdsk/cHtydz*'
Veritas DMP                 set ASM_DISKSTRING='/dev/vx/rdsk/[dg-name]/[rlvolname]*'
HP Native MP              set ASM_DISKSTRING='/dev/rdisk/disk*'
HP SecurePath              set ASM_DISKSTRING='/dev/rdsk/cxtydz*'


Install ASM OS packages:
=======================
Download oracleasm packages:
http://www.oracle.com/technetwork/server-storage/linux/asmlib/index-101839.html
[Select the Linux distribution]

Install oracleasm utility packages: [On 64x]
# rpm -ivh oracleasm-support-2.1.7-1.el5.i386.rpm


# rpm -ivh oracleasm-2.6.18-128.1.6.el5-2.0.5-1.el5.i686.rpm

# rpm -ivh oracleasmlib-2.0.4-1.el5.i386.rpm

# rpm -ivh oracleasm-2.6.18-128.1.6.el5debug-2.0.5-1.el5.i686.rpm

# rpm -ivh oracleasm-2.6.18-128.1.6.el5-debuginfo-2.0.5-1.el5.i686.rpm

# rpm -ivh oracleasm-2.6.18-128.1.6.el5PAE-2.0.5-1.el5.i686.rpm

# rpm -ivh oracleasm-2.6.18-128.1.6.el5xen-2.0.5-1.el5.i686.rpm

Or: Download & install [If the system registered with ULN]:
--
# up2date -i oracleasm-support oracleasmlib oracleasm-`uname -r`
Note: ASM-Lib API improves CPU utilization.

Configure oracleasm:
==================
The following script (start ASM library, load oracleasm module, fix disk permissions, mount ASM filesystem, scan ASM disk headers).

# /etc/init.d/oracleasm configure

    User: oracle
    Group:dba
start on boot: y
scan on boot:  y

 

Enable oracleasm when server boots up:
# /etc/init.d/oracleasm enable   

# $ORACLE_HOME/bin/localconfig add            #=> This step not required on RAC environment.

 

Disk labeling: [Optional]
# /etc/init.d/oracleasm createdisk DATA1 /dev/sda1    #=> Execute On One node only in RAC.

# /etc/init.d/oracleasm createdisk FRA1 /dev/sdb1   

 Note: For each created disk an entry will be added under:
/dev/oracleasm/disks


Troubleshooting oracleasm:
========================
Ensure the existence of oracleasm:

# lsmod |grep -i asm        #Check that oracleasm kernel module loaded.
oracleasm 48020 1

# insmod /lib/modules/`uname -r`/kernel/drivers/addon/oracleasm/oracleasm.ko    #Load oracleasm kernel module if not exist

# /etc/init.d/oracleasm status

# /etc/init.d/oracleasm enable    #Enable ASMLIB at boot time.

# cat /proc/filesystems |grep -i asm
nodev asmdisk
nodev oracleasmfs

# df -ha |grep asm
oracleasmfs 0 0 0 - /dev/oracleasm

Search for ASM disks:

# /etc/init.d/oracleasm scandisks            #=> You can use v$ASM_DISK if ASM instance available.
# /etc/init.d/oracleasm listdisks            #=> You can use v$ASM_DISK if ASM instance available.
# /etc/init.d/oracleasm querydisk DATA1         #=> You can use v$ASM_DISK if ASM instance available.


Re-name Disk:
# /etc/init.d/oracleasm force-renamedisk DATA1 DATA2
Or:
# /etc/init.d/oracleasm force-renamedisk /dev/sda1 FRA1

Delete Disk Label: [Header]
# /etc/init.d/oracleasm deletedisk DATA1

Note: In RAC oracleasm createdisk command will be issued from one node only.
Note: ASM configuration recorderd in file: /etc/sysconfig/oracleasm

-----------------------------------------------
 Create ASM Instance & Disk Groups:
-----------------------------------------------

 Software installation:
===================

Create OS asmadmin group:
----------------------------
[By ROOT user]
# groupadd asmadmin
# usermod -G oinstall,asmadmin -g dba oracle
Logout/Login Oracle user: [By Oracle user]
# id

Starting from 11gr2 Grid Infrastructure, ASM software will be be installed automatically during Grid Infrastructure installation under GRID HOME.

Software installation: For [11gr1 Backwards]: (not required for 11gr2 +)
-------------------------
[By Oracle user]
Install ASM on a seperate ORACLE HOME [/u01/oracle/11.1.0.6/asm].
# ./runInstaller
    => Select the Advanced Installation option.
    => Install Software Only.
    => Configure ASM.
    => Privileged OS Group: ASM Administrator [asmadmin]


startup the listener.

ASM Instance Creation: [GUI Mode] For [11gr1 & Lower]  Recommended
=====================
Note: ASM in RAC or standalone requires that Cluster Synchronization Services (CSS) is installed and started before ASM becomes available.

Configure CSS service: [that connect DB instance with ASM instance]
# $ORACLE_HOME/bin/localconfig add    #=> This step not required on RAC environment.

[By Oracle]
Run DBCA:
# dbca
  Configure Automatic Storage Management ->
  SYS passowrd & ASM instance parameters ->
  Create New -> create DATA disk group & FRA disk group


ASM Instance Creation: [GUI Mode] For [11gr2 +]  Recommended
=====================
Note: ASM in RAC or standalone requires that Cluster Synchronization Services (CSS) is installed and started before ASM becomes available.

[By ROOT]
# crsctl start resource ora.cssd    #=> For RAC setup.
# crsctl start has            #=> For RAC one Node setup only.

[By Oracle]
# cd $GRID_HOME/bin
# ./asmca
  Provide SYS & ASMSNMP password.
  Specify Disk Group:
    Disk Group Name: DATA1
    Redundancy:     Normal
    Change Disk Discovery Path:    /dev/*
    Select At least TWO disks to comply with the Normal Redundancy.
  Create ASM
  *ASM instance will be created automatically.

   
Note:   Oracle recommends to have only two disk groups for each ASM instance:
       DATA: For DATA & INDEX datafiles, redologs, controlfiles and change tracking files.
       FRA:  For Archives, Flashback logs, Backups, multiplexed redologs & controlfiles.  [At least TWICE the size of DATA]

    Redundancy:
    High     => Data will be mirrored 3 times & stripped on all disks[require at least 3 failure group]. 

    *Failure Group store mirror copies of data inside Disk Group.
    Normal   => Data will be mirrored 2 times & stripped on all disks[require at least 2 failure group].
    External => Data will be not be mirrored nor stripped.
    Note: It's recommended that each failure group disk be on a diffrent disk controller to avoid single point of failure.

  Note: trace files location for ASM will be set under: $ORACLE_BASE/diag/asm/+asm/+ASM/trace


ASM Instance Creation: [Command Line Mode]
=====================
Note: ASM in RAC or standalone requires that Cluster Synchronization Services (CSS) is installed and started before ASM becomes available.

[By ROOT]
# $ORACLE_HOME/bin/localconfig add    #=> For RAC [11gr1 backward] only.
# crsctl start resource ora.cssd    #=> For RAC setup.
# crsctl start has            #=> For RAC one Node setup only.

[By Oracle]
Create ASM instance Password File:
    # orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=changeIt entries=5

Create required directories:
    # mkdir -p $ORACLE_BASE/admin/+ASM
   
# cd $ORACLE_BASE/admin/+ASM
   
# mkdir cdump bdump udump pfile
   


Create the init+ASM.ora file:
    # vi $ORACLE_HOME/dbs/init+ASM.ora
        *.asm_diskgroups='PROD_DB_GRP'
        #*.asm_diskstring='/dev/loop*, /dev/rdsk/*'
        *.asm_diskstring='ORCL:*'
        *.background_dump_dest='/u01/app/admin/+ASM/bdump'
        *.user_dump_dest='/u01/app/admin/+ASM/udump'
        *.core_dump_dest='/u01/app/admin/+ASM/cdump'
        *.instance_type='asm'
        *.db_cache_size = 64M         #buffer cache area is used to cache metadata blocks.
        *.shared_pool_size=128M     #standard memory usage (control structures, etc.) to manage the instance.
        *.large_pool_size=64M
        #store extent maps.
        *.remote_login_passwordfile='exclusive'
        *.asm_power_limit=1


Notes: 

> Memory parameters dba_cache_size, shared/large pool are not required starting from 11gr1 as SGA will be 256M by default which is more than enough for ASM.
> ASM’s SGA is not influenced or impacted by the number of database being serviced, no need to adjust it.
> When using SPFILE ASM automatically adds a disk group to ASM_DISKGROUPS parameter when the disk group is created or mounted.
> when using PFILE you have to add/remove diskgroup manually to ASM_DISKGROUPS parameter.

>"asm_diskstring" parameter is the one will tell ASM where to search to find new ASM disks."asm_power_limit" parameter determines the speed with which re-balancing operations occur when add/remove/resize disk, value from [0 to 11] in 11.2.0.1 Backwards and from [0 to 1024] in 11.2.0.2 onwards. 0 is no re-balance "assumed to be done later".

Startup ASM instance:
--------------------
    # export ORACLE_HOME=/u01/oracle/11.2.0.3/grid
   
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
   
# export ORACLE_SID=+ASM
   
# export PATH=$PATH:$ORACLE_HOME/bin
   
# cd $ORACLE_HOME/dbs
   
# sqlplus "/ as sysdba"    --10g
   
# sqlplus "/ as sysasm"    --11g
   
# sqlplus sys/oracle123@host3:1521/+ASM AS SYSASM    --Remotely using Ezconnect (Passwd file must exist).

    SQL> create spfile from pfile;
    SQL> startup mount


Diskgroups Creation:
==================
Note: Once you create the diskgroup you cannot change it's REDUNDANCY level, you have to create a new diskgroup
      with the required redundancy and move data to it. [Note: 438580.1]

    SQL> create diskgroup data_grp  normal redundancy failgroup data_grp_f1 disk 'ORCL:ASMDISK1'    NAME ASMDSK1 
failgroup data_grp_f2 disk 'ORCL:ASMDISK2' NAME ASMDSK2;
           -->such disks ORCL:ASMDISK* have been created by oracleasm (ASMLIB) in an early step

    SQL> create diskgroup PRDATA external redundancy
                    disk '/dev/sdc1', '/dev/sdc2',
                    '/dev/sdc3', '/dev/sdc4';


    SQL> ALTER DISKGROUP ALL MOUNT;

    SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;
    SQL> select name, path, mode_status, state from v$asm_disk where name is not null;

Notes: 

> When using SPFILE ASM automatically adds a disk group to ASM_DISKGROUPS parameter when the disk group is successfully created or mounted.
> When using PFILE: You should add the diskgroup to ASM_DISKGROUPS parameter to automatic mount diskgroups when instance startup:
      SQL> ALTER SYSTEM SET ASM_DISKGROUPS='PROD_DB_GRP, data_grp, PRDATA';

Add oratab entry:
    # vi /etc/oratab
    +ASM:/u01/app/oracle/product/11.2.0/db_1:Y



---------------------------
 ASM Administration:
---------------------------
 WARNING: DISMOUNTING a disk group will SHUTDOWN any open database instance that using that disk group.

Before you login to the ASM instance, You must export ORACLE_HOME to point to ASM/GRID HOME:

# export ORACLE_HOME=/u01/oracle/11.2.0.3/grid
# export ORACLE_SID=+ASM
# export PATH=$PATH:$ORACLE_HOME/bin
# sqlplus "/ as sysdba"    --[10g]
# sqlplus "/ as sysasm"    --[11g & higher]

Discover disks:
ASM_DISKSTRING initialization parameter should point to the right path.
    SQL> col path for a40
         SELECT NVL(a.name, '[CANDIDATE]')disk_group_name, b.path, b.name disk_file_name,b.failgroup  FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) ORDER BY a.name;


Create disk group: External redundancy
    SQL> create diskgroup UNDO external redundancy disk 'ORCL:UNDO1' ATTRIBUTE 'COMPATIBLE.RDBMS' = '11.2';
       
Note: COMPATIBLE.RDBMS= minimum DB version to use the group. COMPATIBLE.RDBMS must not be bigger than COMPATIBLE.ASM.

Create disk group: Normal redundancy
    SQL> create diskgroup test_failgroup normal redundancy
                    FAILGROUP fg1 DISK 'ORCL:ASMDSK1' NAME ASMDSK1
                    FAILGROUP fg2 DISK 'ORCL:ASMDSK2' NAME ASMDSK2 [ATTRIBUTE 'au_size'='4M']; 

                    --au_size from 1 to 64m default is 1m, Oracle recommend 4m.

Create disk group: High redundancy
    SQL> create diskgroup test_failgroup high redundancy
              FAILGROUP TEST_fg1 DISK '/dev/oracleasm/disks/ASMDSK1' NAME TEST_DSK1
              FAILGROUP TEST_fg2 DISK '/dev/oracleasm/disks/ASMDSK2' NAME TEST_DSK2
              FAILGROUP TEST_fg3 DISK '/dev/oracleasm/disks/ASMDSK3' NAME TEST_DSK3;

Note: Oracle recommend AU size=4m for better I/O throughput, increase file size limits, reduce database open time.

Mount Diskgroup:    SQL> ALTER DISKGROUP DATA1 MOUNT [FORCE];    --[FORCE] will mount [Normal/High redundant] diskgroup in case there is missing/damaged disk.

Un-Mount Diskgroup:    SQL> ALTER DISKGROUP DATA1 DISMOUNT [FORCE];    --[FORCE] will lead to termination of DB instance associated with ASM.

Mount all Diskgroups:    SQL> ALTER DISKGROUP ALL MOUNT [FORCE];

Un-Mount all Diskgroups:SQL> ALTER DISKGROUP ALL DISMOUNT [FORCE];

Drop diskgroup:        SQL> ALTER DISKGROUP DATA1 dismount force;
                                  SQL> DROP DISKGROUP DATA1 FORCE INCLUDING CONTENTS;

Rename diskgroup: 11gR2    # renamedg dgname=data1 newdgname=fra verbose=true asm_diskstring='/dev/sdb'
                                               asm_diskstring => should point to the disk path, you can use /dev/sdb*

Drop disk from diskgroup: SQL>ALTER DISKGROUP DATA1 drop disk ASMDSK2;
                                            Note: Use the disk name appears in v$asm_diskgroup NOT the path.

Drop file:[Not accessed]    SQL> ALTER DISKGROUP DATA1 DROP FILE '+data/orcl/my_undo_ts';

Un-Drop disk [Pending]:    SQL> ALTER DISKGROUP DATA1 UNDROP DISKS;
Add disk to diskgroup:        SQL> ALTER DISKGROUP DATA1 add disk 'ORCL:ASMDSK5'; 

Note: ASM re-balancing operations will move data onto the new disk. for better performance add all new disks at once.

Resize all disks:       SQL> ALTER DISKGROUP data1 RESIZE DISKS IN FAILGROUP failgrp1 SIZE 100G;

Create tablespace under ASM diskgroup: 

                                 SQL> CREATE TABLESPACE my_ts DATAFILE '+DATA1' SIZE 100M AUTOEXTEND ON;

Re-balance Power:    SQL> ALTER DISKGROUP DATA1 REBALANCE POWER 5;
                                  SQL> alter diskgroup DATA1 add disk '/dev/rdsk/c3t19d39s4' rebalance power 11; 

Note: Manual rebalance is not required, as Oracle automatically rebalances disk groups when their configuration changes.

Check errors:        SQL> ALTER DISKGROUP data1 check all;

Repair errors:        SQL> ALTER DISKGROUP data1 check all repair;

Add an alias:         SQL> alter diskgroup data add alias '+DATA/orcl/controlfile/control01.ctl' for '+DATA/orcl/controlfile/current.263.852473145';

V$ASM_DISKGROUP:     SQL> select NAME,STATE,TYPE,TOTAL_MB,FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP; 

Note: Quering v$ASM_DISKGROUP is an expensive operation as it search disks each time, you can substitute it with V$ASM_DISKGROUP_STAT. 

V$ASM_DISK:         SQL>      col path for a40
                                                   select FAILGROUP,name,path,MODE_STATUS,STATE,REDUNDANCY,

                                                   OS_MB,TOTAL_MB,FREE_MB from v$asm_disk order by 1;
 Note: Quering v$ASM_DISK is an expensive operation as it search disks each time, you can substitute it with V$ASM_DISK_STAT.

V$ASM_FILE:         SQL> select GROUP_NUMBER,FILE_NUMBER,BYTES/1024/1024,TYPE,REDUNDANCY,STRIPED from V$ASM_FILE;
                                  Show all files paths under ASM instance:
                                  SQL> SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex;

V$ASM_OPERATION:    Displays long running ASM operations.
V$ASM_ATTRIBUTE:
    View diskgroups attributes.
V$ASM_CLIENT:      SQL> select INSTANCE,NAME,STATUS,SOFTWARE_VERSION,COMPATIBLE_VERSION from v$asm_client;
                                     --Client DB instances are the instances connect to ASM instance.

------------------------
 Database ON ASM:
------------------------
 Once ASM instance created all databases on the same host can use it.

Create New Database on ASM:
==========================
GUI: DBCA
When creating a new DB use DBCA to create a new DB select "ASM" as the storage mechanism, when ask for DB files location select OMF "+DATA",
 select "+FRA" for Flash Recovery Area.

Command line:
Ensure that the parameter file include the following parameters with same format:

    *.control_files=(+DATA, +FRA)
    *.db_recovery_file_dest=+FRA
    *.db_recovery_file_dest_size=2147483648
    *.db_create_file_dest=+DATA
    *.db_create_online_log_dest_1=+FRA
    *.db_create_online_log_dest_2=+DATA    -- optional
    *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
    *.background_dump_dest=/u01/oracle/admin/orcl/bdump
    *.core_dump_dest=/u01/oracle/admin/orcl/cdump
    *.user_dump_dest=/u01/oracle/admin/orcl/udump



   SQL> create database DEVDB
    user sys identified by oracle123
    user system identified by oracle123
    maxdatafiles 1021
    maxinstances 4
    maxlogfiles 16
    character set WE8MSWIN1252
    national character set AL16UTF16
    datafile '+DATA' size 1000M autoextend off extent management local
    sysaux datafile '+DATA' size 1000m
    default temporary tablespace temp tempfile '+DATA' size 4000m uniform size 1m
    undo tablespace undo_rbs datafile '+DATA' size 4000m
    logfile
    ('+DATA','+FRA') size 300M,
    ('+DATA','+FRA') size 300M,
    ('+DATA','+FRA') size 300M,
    ('+DATA','+FRA') size 300M;

   => Run additional scripts such catalog.sql, catproc.sql and catexp.sql from the $ORACLE_HOME/rdbms/admin

Add Tablespace|Datafile:
-----------------------
   SQL> alter tablespace [tablespace_name] add datafile '+data' size 1g;
Move Datafile to ASM Diskgroup: [From ASM/Non-ASM to ASM]
------------------------------
   SQL>  ALTER DATABASE DATAFILE '+DATA1/orcl/datafile/aa.256.852212097' offline;
   RMAN> COPY DATAFILE '+DATA1/orcl/datafile/aa.256.852212097' to '+loop';
   RMAN> switch  datafile '+DATA1/orcl/datafile/aa.256.852212097' to copy;
   SQL>  select file_name from dba_data_files;
   SQL>  RECOVER datafile '+LOOP/orcl/datafile/aa.257.852212479';
   SQL>  ALTER DATABASE DATAFILE '+LOOP/orcl/datafile/aa.257.852212479' online;


Migrate the database to ASM:
=========================
Migrate already exit DB to use ASM: (Using RMAN)

    1- Disable Block change tracking if enabled:
        SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
     - If the database is Physical Standby DB, Stop Managed Recovery :
        SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    2- Generate PFILE:
        SQL> create pfile='/u01/oracle/11.2.0.3/db/dbs/initorcl.ora' from spfile;

    3- Edit the pfile to include the following parameters pointing to ASM diskgroups:
        *.control_files=(+DATA, +FRA)        
        *.db_recovery_file_dest=+FRA          
        *.db_recovery_file_dest_size=536870912000  #500GB
        *.db_create_file_dest=+DATA
             
        *.db_create_online_log_dest_1=+FRA
        *.db_create_online_log_dest_2=+DATA      

    4- Backup the database as COPY to +DATA:
        # rman target /
        RMAN> run{                                  
              allocate channel c1 device type disk;
              allocate channel c2 device type disk;
              allocate channel c3 device type disk;
              BACKUP AS COPY DATABASE FORMAT '+DATA';  
              }

    5- Shutdown & Migrate the DB using RMAN: [Downtime STEP]
        RMAN> shutdown immediate;
        RMAN> startup nomount pfile=/u01/oracle/11.2.0.3/db/dbs/initorcl.ora
        RMAN> restore controlfile from '/database/datafiles/orcl/control01.ctl';
        RMAN> alter database mount;
        RMAN> SWITCH DATABASE TO COPY;
        RMAN> run{                                  
              allocate channel c1 device type disk;
              allocate channel c2 device type disk;
              allocate channel c3 device type disk;
              recover database;
              }   

        RMAN> alter database open;

    6- Migrate TEMP FILES:
        SQL> alter tablespace TEMP add tempfile '+DATA' SIZE 10M;
        SQL> alter database tempfile '/database/datafiles/orcl/temp01.dbf' drop; 

    7- Migrate REDOLOGS:
        >Run the following procedure to automatically drop/re-create logfiles:
        SQL> declare
              cursor orlc is
              select lf.member, l.bytes from v$log l, v$logfile lf
              where l.group# = lf.group# and lf.type = 'ONLINE' order by l.thread#, l.sequence#;
              type numTab_t is table of number index by binary_integer;
              type charTab_t is table of varchar2(1024) index by binary_integer;
              byteslist numTab_t; namelist charTab_t;
              procedure migrateorlfile(name IN varchar2, bytes IN number) is
                 retry number;
                 stmt varchar2(1024);
                 als varchar2(1024) := 'alter system switch logfile';
              begin
                 select count(*) into retry from v$logfile;
                 stmt := 'alter database add logfile size ' || bytes;
                 execute immediate stmt;
                 stmt := 'alter database drop logfile ''' || name || '''';
                 for i in 1..retry loop
                    begin execute immediate stmt;
                    exit;
                    exception
                       when others then
                        if i > retry then raise;
                        end if;
                     execute immediate als;
                   end;
                 end loop;
               end;
               begin
               open orlc;
               fetch orlc bulk collect into namelist, byteslist;
               close orlc;
               for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));
               end loop;
               end;
               /


        >In case there are still logfiles didn't move, do switch logfiles many times then re-run the procedure again:
            SQL> select member from v$logfile;
                 alter system switch logfile;
                 alter system switch logfile;

                 -- Run the above procedure again to move the rest files ... or:
                 -- alter database drop logfile group 3;
                 -- alter database add logfile group 3 ('+data', '+fra') size 100m;

        >Check if there still files in old location:
            SQL> select file_name from dba_data_files;
                            select file_name from dba_temp_files;
                            select member from v$logfile;

    8- Delete original old datafiles:
        RMAN> DELETE NOPROMPT COPY OF DATABASE;
    9- REMOVE old Redologs, TEMP, control files manually from FS.

    10-Enable back the block change tracking:
        SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
      -If the database is Physical Standby:
        SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
      -In case you want to migrate old archives/ RMAN backups/ Copy backups:
        RMAN> run{
             ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
             ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
             ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
             ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
             BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
             BACKUP BACKUPSET ALL DELETE INPUT;
             BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
            }

-------------------------------------
 Transfer Files FROM/TO ASM:
-------------------------------------

Transfer files from/to ASM using [RMAN]
-------------------------------------------
 Copy datafile From ASM to OS:
   SQL>  ALTER DATABASE DATAFILE '+LOOP/orcl/datafile/aa.257.852212479' offline;
   RMAN> COPY DATAFILE   '+LOOP/orcl/datafile/aa.257.852212479' to '/database/datafiles/orcl/bb.dbf';
   RMAN> switch datafile '+LOOP/orcl/datafile/aa.257.852212479' to copy;
   SQL>  RECOVER datafile '/database/datafiles/orcl/bb.dbf';
   SQL>  ALTER DATABASE DATAFILE '/database/datafiles/orcl/bb.dbf' online;

 Copy Controlfile & Archives from to ASM to OS:
   RMAN> copy current controlfile to '/u01/controlfile.ctl';
   RMAN> copy archivelog '+diskgr/dbname/archivelog/archivelog.n' to '/u01/archivelog.n';


 Copy the SPFILE from/To ASM:
   SQL> CREATE pfile='/u01/oracle/11.2.0.3/db/dbs/init+ASM.ora' from spfile;
   SQL> CREATE SPFILE ='+DATA/asmspfile.ora' FROM PFILE ='/u01/oracle/11.2.0.3/db/dbs/init+ASM.ora';
 


Transfer files from/to ASM using [ASMCMD]
----------------------------------------------
   # export ORACLE_HOME=$GRID_HOME
   # asmcmd -p
     ASMCMD> cp +loop/orcl/datafile/AA.256.852292031 /u01/aa.dbf
     ASMCMD> cp /u01/aa.dbf +loop/orcl/datafile/AA.dbf


Note: ASM instance should be up.
Note: Use fully qualified names.

Transfer files from/to ASM using [DBMS_FILE_TRANSFER]
--------------------------------------------------------------
    [On Database Instance]
    > Create the source directory [Where file located]
      SQL> create or replace directory SOURCE_DIR as '+DATA/orcl/datafile/';
    > Create the destination directory [where file will be copied]:
      SQL> create or replace directory ORACLE_DEST as '/u02';
    > Execute the dbms_file_transfer package: [Tablespace of copied datafile should be offline]
      SQL>    BEGIN
                         dbms_file_transfer.copy_file(source_directory_object =>      'SOURCE_DIR',
                         source_file_name =>             'USERS.259.852463895',
                         destination_directory_object => 'ORACLE_DEST',
                         destination_file_name =>        'users.dbf');
                         END;
                         /


------------------------------------------------------------
Managing files inside ASM using ASMCMD utility:
 ------------------------------------------------------------

ASMCMD is a command line utility allow you to manage the files inside ASM disks like any Filesystem API.
*Before using ASMCMD utility, ASM instance must be started and Diskgroups are mounted.

Note: Before using ASMCMD you must:
    # export ORACLE_SID=+ASM
    # export ORACLE_HOME=[ORACLE_HOME that house asmcmd command "GRID_HOME"]
    # asmcmd -p    => -p to show the current path in the prompt

    Or: in case the ASM entry already exist in /etc/oratab
    # . oraenv
    +ASM

    Or: you can use the NON-Interactive "script" mode: e.g.
    # asmcmd ls -l +data/orcl/datafile

File Management commands:

-----------------------------
cd     Change the current directory to the specified directory.
du     Display the total disk space occupied by ASM files in the specified ASM directory and all of its sub-directories, recursively.
find   List the paths of all occurrences of the specified name (with * wildcards) under the specified directory:
           ASMCMD [+] > find -t datafile + *sys*
           +data/DBADEV/DATAFILE/SYSAUX.257.655588275
           +data/DBADEV/DATAFILE/SYSTEM.258.655588211
           + sign is like / in linux, you can search in +DATA or +FRA
           -t option specifies the type of file, templates can found in TYPE column in V$ASM_FILE.

ls      List the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups.
lsof   List the open files.
mkalias Create an alias for system-generated filenames:  ASMCMD> mkalias REGISTRY.253.851615101 spfile.ora
mkdir   Create ASM directories.
pwd      Display the path of the current ASM directory.
rm        Delete the specified ASM files or directories.
rmalias  Delete the specified alias, retaining the file alias points to.

File Access commands:

----------------------
chgrp      Change the group of a file.     e.g. asmcmd> chgrp –R backup_users +data/* ;
chmod    Change permissions of a file. e.g. ASMCMD> chmod –R 600 +data/orcl
chown    Change the owner of a file.    e.g. ASMCMD> chown  -R db1:backup_user +data/orcl/datafile/*  &
grpmod  Add or removes users from an existing user group.
lsgrp       List ASM access control list (ACL) groups.
lsusr       List users in a disk group.
mkgrp   Create a new user group.    e.g. asmcmd> mkgrp dg1 mygroup ;
mkusr    Add a user to disk group.    e.g. asmcmd> mkusr dg1 myuser ;
passwd  Change the password of a user in a disk group.
rmgrp    Delete a user group.            e.g. asmcmd> rmgrp dg1 mygroup ;
rmusr     Delete a user from a disk group.    e.g. asmcmd> rmusr dg1 myuser ;

Disk Group Management commands:
-------------------------------------
chdg      Change a disk group (add, drop, or rebalance).
chkdg    Check or repairs a disk or failure group.
cp          Enable you to copy files between ASM disk groups on a local/remote instances, ASM diskgroup from/to OS filesystem.
dgdrop  Drop a disk group.    e.g. asmcmd> dgdrop -r dg1 ;
lsattr      List the attribute and attribute settings of a disk group.
lsdg       List disk groups and their information.    e.g. asmcmd> lsdg -g
lsdsk     List disks visible to ASM and it's groups.
             -k option displaysthe TOTAL_MB, NAME, FAILGROUP, and PATH info.
             -t option display when the disk was added to the diskgroup, when it was mounted, how long the repair timer is.
             -s option display Read/Write Information.
lsgrp      List ASM access control list (ACL) groups.
md_backup   Create a backup of all of the mounted disk groups.
md_restore    Restore disk groups from a backup.
mkdg     Create a disk group.
mount    Mount a disk group.    e.g. asmcmd> mount FRA ;
offline    Offline a disk or a failure group that belongs to a disk group.
online     Online a disk or a failure group that belongs to a disk group.
rebal       Re-balance a disk group.
remap     Repair a range of physical blocks on a disk.
renamedg  Rename diskgroup.(datafiles will need to be relocated manually) 

                  e.g. ASMCMD> renamedg dgname=[olddiskgroup] newdgname=[newdiskgroup]
setattr     Set attributes in an ASM disk group.
umount  Dismount a disk group.    e.g. asmcmd> umount FRA ;

Instance Management commands
:

---------------------------------
iostat       Display statistics for mounted devices.
lsct          List information about current ASM clients.
lsop         List the current operations on a disk group, database, or ASM instance.
lspwusr   List the users from an Oracle PWFILE file.
orapwusr Add, drop, or change an Oracle PWFILE user.
shutdown Shut down an ASM instance.
spget       Locate the spfile on asm.
spcopy    Copy SPFILE.
startup     Start up an ASM instance.

Using ASMCMD in Script mode:
# asmcmd rm +DATA/DBATOOLS/archivelog/2008_03_10/thread_1*
 

REFERENCES:
Complete ASM documentation for 11.2.03: http://docs.oracle.com/cd/E11882_01/server.112/e18951.pdf
Master Note for Automatic Storage Management (ASM) [Doc ID 1187723.1]
ASM Best Practices. [Doc ID 265633.1]

ASM Tuning by Thiru Vadivelu: http://www.orafaq.com/papers/tuning_asm.pdf
Database High Availability Best Practices | Configuring Storage: 
https://docs.oracle.com/cd/E11882_01/server.112/e10803/config_storage.htm#HABPT4790