Tuesday, December 24, 2013

ASM Management


Here We will discuss 2 topics:
1. ASM Management : Create & Drop Diskgroup, Add & Delete Disks to/from Diskgroup, Mount/Unmount Diskgroup, Resizing disk within Diskgroup, ASM rebalance ideal practice, Real life examples
2. How to change ASM Sys password (at last of this post).


.             ASM (Automatic Storage Management) MANAGEMENT                     .


-  The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit.
-    The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files.
-    The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
-    In summary ASM provides the following functionality:
o    Manages groups of disks, called disk groups.
o    Manages disk redundancy within a disk group.
o    Provides near-optimal I/O balancing without any manual tuning.
o    Enables management of database objects without specifying mount points and filenames.
o    Supports large files.


Important parameters for ASM management: 

-         ASM_POWER_LIMIT - The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

-         ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
-      
   ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

-          Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

o    NORMAL REDUNDANCY    : Two-way mirroring, requiring two failure groups.
o    HIGH REDUNDANCY      : Three-way mirroring, requiring three failure groups.
o    EXTERNAL REDUNDANCY  : No mirroring for disks that are already protected using hardware                                               mirroring or RAID.

Important Notes:

1.  It is recommended to set "asm_power_limit" to zero when you want to add more than 1 disk. 
If you add disk(s) keeping asm_power_limit value greater than zero, rebalance starts immediately.
When more disks are being added, after 1 disk addition, rebalance starts; if you add another disk
at that moment, rebalance stops for first disk and starts for second disk. So, it is recommended
to set asm_power_limit to zero when you want to add more than 1 disk.

2. After adding all the disks, you can set "asm_power_limit" to value (5 or 6). But setting this value
does not start rebalancing. You need to issue the following command to start rebalance:
 ALTER DISKGROUP disk_group_name REBALANCE POWER 6;

3. When rebalcing is being run, you will get an entry if you issue following command:
select * from v$asm_operation; When "no rows returned" is shown, that means rebalancing is done.


Necessary Views
V$asm_diskgroup
V$asm_disk
V$asm_operation

SQL> show parameter asm_diskstring
SQL> select name, path, mode_status,header_status,state, disk_number from v$asm_disk;
SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

Example:
In this example, we used 2 Disk Groups: DATA_AREA (disk01 and disk02), LOG_AREA (disk03)

DROPPING DISK from DISKGROUP DATA_AREA

SQL> select GROUP_NUMBER,DISK_NUMBER,name, path, header_status from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           PATH                               Header_status
------------ ----------- ------------------------------ --------------------------------------------------------                ---------------------
           1           0 DATA_AREA_0000                 /dev/erpdbtest/asm/rdsk/disk001         MEMBER
           1           1 DATA_AREA_0001                 /dev/erpdbtest/asm/rdsk/disk002         MEMBER
           2           0 LOG_AREA_0000                  /dev/erpdbtest/asm/rdsk/disk003          MEMBER

SQL> alter diskgroup data_area drop disk DATA_AREA_0001;
Diskgroup altered.

SQL> select GROUP_NUMBER,DISK_NUMBER,name, path, header_status from v$asm_disk;
GROUP_NUMBER DISK_NUMBER NAME                           PATH                               Header_status
------------ ----------- ------------------------------ --------------------------------------------------------                 ---------------------
           1           0 DATA_AREA_0000                 /dev/erpdbtest/asm/rdsk/disk001          MEMBER
           1           1 DATA_AREA_0001                 /dev/erpdbtest/asm/rdsk/disk002       FORMER
           2           0 LOG_AREA_0000                  /dev/erpdbtest/asm/rdsk/disk003           MEMBER

ADDING DISK to DISKGROUP

SQL> alter diskgroup log_area add disk ‘/dev/erpdbtest/asm/rdsk/disk002’
Diskgroup altered.

SQL> select GROUP_NUMBER,DISK_NUMBER,name, path, header_status from v$asm_disk;
GROUP_NUMBER DISK_NUMBER NAME                           PATH                           HEADER_STATUS
------------ ----------- ------------------------------ --------------------------------------------------                  ----------------------------
           1           0 DATA_AREA_0000                 /dev/erpdbtest/asm/rdsk/disk001       MEMBER
           2           0 LOG_AREA_0000                  /dev/erpdbtest/asm/rdsk/disk003        MEMBER
           2           1 LOG_AREA_0001                  /dev/erpdbtest/asm/rdsk/disk002        MEMBER

RESIZING LOG_AREA DISK from 10 GB to 5 GB

SQL> select name, group_number, state, total_mb, free_mb from v$asm_diskgroup;
NAME                           GROUP_NUMBER   STATE         TOTAL_MB    FREE_MB
------------------------------       ---------------------------   -----------         -----------------    ----------
DATA_AREA                       1                       MOUNTED          10237      8908
LOG_AREA                         2                       MOUNTED          20474      20106

(Resizing a specific disk)

SQL> alter diskgroup LOG_AREA resize disk LOG_AREA_0001 size 5G;
Diskgroup altered.

To resize all disks in a diskgroup: 

SQL> alter diskgroup LOG_AREA resize all size 5G

SQL> select * from v$asm_operation;
GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
           2 REBAL RUN           1          1         43        135        171           0

SQL> select * from v$asm_operation;
no rows selected

SQL> select name, group_number, state, total_mb, free_mb from v$asm_diskgroup;
NAME                           GROUP_NUMBER   STATE         TOTAL_MB    FREE_MB
------------------------------       ---------------------------    -----------        ----------------    ----------------
DATA_AREA                       1                       MOUNTED          10237       8908
LOG_AREA                         2                       MOUNTED          15357      14989


DROPPING ENTIRE DISKGROUP
SQL> select name, group_number, state, total_mb, free_mb from v$asm_diskgroup;
NAME                           GROUP_NUMBER   STATE           TOTAL_MB    FREE_MB
------------------------------ ---------------------------   -----------          ----------------     ----------
DATA_AREA                      1                           MOUNTED      10237            8908
LOG_AREA                         2                          MOUNTED      15357           14989

SQL> drop diskgroup LOG_AREA including contents;
drop diskgroup LOG_AREA including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "LOG_AREA" precludes its dismount

Diskgroup can not be dropped as it is being used. So, change the logfile to other diskgroup and then try dropping.

sql> alter database drop logfile group 3
sql> alter database add logfile group 3 '+DATA_AREA'

sql> alter system switch logfile
sql> alter system checkpoint

sql> alter database drop logfile group 2
sql> alter database add logfile group 2 '+DATA_AREA'

sql> alter database drop logfile group 1
sql> alter database add logfile group 1 '+DATA_AREA'

SQL> drop diskgroup LOG_AREA including contents;
Diskgroup dropped.


UNMOUNT /MOUNT DISKGROUP (After shutdown database)

SQL> ALTER DISKGROUP ALL DISMOUNT;
Diskgroup altered.

SQL> select name, group_number, state, total_mb, free_mb from v$asm_diskgroup;
NAME                           GROUP_NUMBER     STATE                 TOTAL_MB    FREE_MB
------------------------------ ---------------------------     -----------                 ----------------    ---------------
DATA_AREA                         0                          DISMOUNTED           0                     0

SQL> ALTER DISKGROUP ALL MOUNT;
Diskgroup altered.

SQL> select name, group_number, state, total_mb, free_mb from v$asm_diskgroup;
NAME                           GROUP_NUMBER    STATE            TOTAL_MB     FREE_MB
------------------------------ ---------------------------    -----------            ----------------     --------------
DATA_AREA                              1                    MOUNTED          10237           8593


CREATING DISKGROUP

SQL> show parameter asm_disk
NAME                                 TYPE        VALUE
------------------------------------      -----------      ------------------------------
asm_diskgroups                    string       DATA_AREA
asm_diskstring                      string      /dev/erpdbtest/asm/rdsk/dis*

SQL> select name, path, group_number from v$asm_disk;
NAME                                      PATH                                           GROUP_NUMBER
--------------------------             -----------------------------------                          ---------------------------
                                         /dev/erpdbtest/asm/rdsk/disk002                0
                                         /dev/erpdbtest/asm/rdsk/disk003                0
DATA_AREA_0000             /dev/erpdbtest/asm/rdsk/disk001                1

SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;
NAME                           STATE                TYPE              TOTAL_MB              FREE_MB
------------------------------      -----------                -------------          ----------------              ----------------
DATA_AREA              MOUNTED            EXTERN             10237                    8583

SQL> create diskgroup LOG_AREA external redundancy disk '/dev/erpdbtest/asm/rdsk/disk002';
Diskgroup created.

SQL> select name, path, group_number from v$asm_disk;
NAME                                         PATH                                       GROUP_NUMBER
------------------------------           -----------------------------------                       --------------------------
                                             /dev/erpdbtest/asm/rdsk/disk003                0
DATA_AREA_0000                 /dev/erpdbtest/asm/rdsk/disk001                1
LOG_AREA_0000                  /dev/erpdbtest/asm/rdsk/disk002                 2

SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                      STATE           TYPE            TOTAL_MB            FREE_MB
------------------------------          -----------             ------                  - ---------               ----------
DATA_AREA                       MOUNTED       EXTERN              10237                  8583
LOG_AREA                         MOUNTED       EXTERN              10237                  10187

SQL> alter diskgroup LOG_AREA add disk '/dev/erpdbtest/asm/rdsk/disk003';
Diskgroup altered.

SQL> select name, path, group_number from v$asm_disk;
NAME                                             PATH                                       GROUP_NUMBER
------------------------------               -----------------------------------                ------------
DATA_AREA_0000                 /dev/erpdbtest/asm/rdsk/disk001                1
LOG_AREA_0000                  /dev/erpdbtest/asm/rdsk/disk002                 2
LOG_AREA_0001                  /dev/erpdbtest/asm/rdsk/disk003                 2

SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;
NAME                                   STATE            TYPE            TOTAL_MB          FREE_MB
------------------------------              -----------            ------------         ----------------          ----------------
DATA_AREA                         MOUNTED      EXTERN          10237                    8583
LOG_AREA                           MOUNTED      EXTERN           20474                   20422


==========================  Real Life Example:================================

SQL> select name,path,total_mb from v$asm_disk where name is null;
/dev/test/rdsk/disk38
/dev/test/rdsk/disk39
/dev/test/rdsk/disk40
/dev/test/rdsk/disk41

-- Above 4 disks we will add now to diskgroup DATA_AREA

SQL> show parameter power
asm_power_limit
integer
4

SQL> alter system set asm_power_limit=0 scope=both;

SQL> alter diskgroup DATA_AREA add disk '/dev/fms/rdsk/disk38';
SQL> alter diskgroup DATA_AREA add disk '/dev/fms/rdsk/disk39';
SQL> alter diskgroup DATA_AREA add disk '/dev/fms/rdsk/disk40';
SQL> alter diskgroup DATA_AREA add disk '/dev/fms/rdsk/disk41';

SQL> alter diskgroup DATA_AREA drop disk DATA_AREA_0000;
SQL> alter diskgroup DATA_AREA drop disk DATA_AREA_0001;
SQL> alter diskgroup DATA_AREA drop disk DATA_AREA_0002;
SQL> alter diskgroup DATA_AREA drop disk DATA_AREA_0003;

SQL> alter system set asm_power_limit=5 scope=both;

SQL> select * from v$asm_operation;

SQL> ALTER DISKGROUP DATA_AREA REBALANCE POWER 5;

SQL> select * from v$asm_operation;

=======================================================================

What happens if ASM shutdown immediate when database is up
SQL> shutdown immediate;
ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance

What happens if ASM shutdown abort when database is up
Ans: database also shutdown with asm
SQL> select INSTANCE_NAME from v$instance;

INSTANCE_NAME
----------------
+ASM
SQL> shutdown abort;
ASM instance shutdown
SQL>

What happens when startup database keeping ASM in SHUTDOWN
ORACLE_SID=TEST
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA_AREA/GPTEST/spfileTEST.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA_AREA/TEST/spfileTEST.ora
ORA-15077: could not locate ASM instance serving a required diskgroup


.            How to change ASM Sys Password                                         .

Coming ...
=================================== End ================================


2 comments: