ASM Administration

 
** ASM Administration **
  
** Create ASM INSTANCES **
    To create an ASM instance first create pfile, init+ASM.ora, in the /tmp directory, containing the following parameter.

    The initialization parameters that are specific to an ASM instance are:
    INSTANCE_TYPE   - Set to ASM. The default is RDBMS.

    ASM_DISKGROUPS  - The list of diskgroups 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. 
        The default value is NULL allowing all suitable disks to be considered. 

    ASM_POWER_LIMIT - The maximum power for a rebalancing operation on an ASM instance. 
        The valid values range from 1 (default) to 11. 
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. 
A value of 0 disables rebalancing.

    ASM_PREFERRED_READ_FAILURE_GROUPS - This initialization parameter value (default is NULL) is a 

        comma-delimited list of strings that specifies the failure groups that should be preferentially 

        read by the given instance. 

        This parameter is generally used only for clustered ASM instances and its value can be 

        different on different nodes. This is from Oracle 11g.

    DB_UNIQUE_NAME  - Specifies a globally unique name for the database. 
        This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

** Next, connect to the ideal instance. **

    $ export ORACLE_SID=+ASM
    SQL> sqlplus "/as sysdba"

** Create a spfile using the contents of the init+ASM.ora file. **
    SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
    SQL> startup nomount
    ASM instance started
    Total System Global Area 130023424 bytes
    Fixed Size 2028368 bytes
    Variable Size 102829232 bytes
    ASM Cache 25165824 bytes

    The ASM instance is now ready to use for creating and mounting diskgroups.

** Startup of ASM Instances **
    ASM instances are started and stopped in a similar way to normal database instances.
    NOMOUNT - Starts the ASM instance without mounting any diskgroups.

    MOUNT   - Starts the ASM instance and mounts the diskgroups specified by the ASM_DISKGROUPS parameter.

    OPEN    - ASM instance does not have open stage.

    FORCE   - Performs a SHUTDOWN ABORT before restarting the ASM instance.

** Shutdown of ASM Instances **
    NORMAL        - The ASM instance waits for all connected ASM instances and SQL sessions to exit 

        then shuts down.

    IMMEDIATE     - The ASM instance waits for any SQL transactions to complete then shuts down. 

        It doesn't wait for sessions to exit.

    TRANSACTIONAL - Same as IMMEDIATE.

    ABORT         - The ASM instance shuts down instantly.

** Creating diskgroups **
    For two-way mirroring we would expect a diskgroup to contain two failure groups, so individual 

    files are written to two locations.
    SQL> CREATE DISKGROUP dg_asm_data NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1, '/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2;

    For three-way mirroring we would expect a diskgroup to contain three failure groups, 

    so individual files are written to three locations.

    SQL> CREATE DISKGROUP dg_asm_fra HIGH REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1, '/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2,
FAILGROUP failure_group_3 DISK
'/devices/diskc1' NAME diskc1, '/devices/diskc2' NAME diskc2;

    In the below command, database will create a diskgroup named dg_grp1 with the physical disks 

    named /dev/d1, /dev/d2, and so on. 

    Instead of giving disks separately, we can also specify disk names in wildcards in the DISK 

    clause as DISK '/dev/d*'.

    SQL> CREATE DISKGROUP dg_grp1 EXTERNAL REDUNDANCY
DISK '/dev/d1','/dev/d2','/dev/d3','/dev/d4' ... ...;
SQL> CREATE DISKGROUP archdg NORMAL REDUNDANCY
FAILGROUP fg1 DISK
'/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4'
FAILGROUP fg2 DISK
'/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4'

        ATTRIBUTE 'au_size'='4M','compatible.asm'='11.2',

        'compatible.rdbms'='11.2','compatible.advm'='11.2';

    SQL> select * from v$asm_diskgroup;
    SQL> select dg.name, d.name disk_name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
and dg.name = 'DATADG'
order by dg.name, disk_name;
    SQL> select name, free_mb, total_mb, free_mb/total_mb*100 as percentage from v$asm_diskgroup;
    SQL> DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

    SQL> DROP DISKGROUP disk_group_1 FORCE;

SQL> DROP DISKGROUP disk_group_1 FORCE INCLUDING CONTENTS;

** Altering diskgroups **
    Disks can be added or removed from diskgroups using the ALTER DISKGROUP statement.

** Adding disks **
    SQL> ALTER DISKGROUP dskgrp1 ADD DISK '/dev/d5';
    SQL> ALTER DISKGROUP dg1 ADD DISK '/devices/disk*3', '/devices/disk*4';

** Listing disks **
    The following command shows all the disks managed by the ASM instance for all the client databases.
    SQL> SELECT * FROM V$ASM_DISK;

** Check the status as former **
    col PATH format a30
    set lines 200 pages 200
    SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK
    where path like 'AFD:HDD_E0_S15%';

** Listing client databases **
    The following command shows all the database instances connected to the ASM instance.
    SQL> SELECT * FROM V$ASM_CLIENT;

** Dropping disks **
    We can remove a disk from diskgroup.
    SQL> ALTER DISKGROUP dg4 DROP DISK diska4;

** Resizing disks **
    Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement.
    SQL> ALTER DISKGROUP dg_data_1 RESIZE DISK diska1 SIZE 150G;

    Resizing all disks in a failure group
    SQL> ALTER DISKGROUP dg_data_1 RESIZE DISKS IN FAILGROUP fg_1 SIZE 50G;

    Resizing all disks in a diskgroup
    SQL> ALTER DISKGROUP dg_data_1 RESIZE ALL SIZE 100G;

** Undropping disks **
    The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone.
    It will not revert drops that have completed, or disk drops associated with the dropping of a diskgroup.
    SQL> ALTER DISKGROUP disk_group_1 UNDROP DISKS;

** Online disks **
    SQL> ALTER DISKGROUP data ONLINE DISK 'disk_0000', 'disk_0001';
    SQL> ALTER DISKGROUP data ONLINE DISKS IN FAILGROUP 'fg_99';
    SQL> ALTER DISKGROUP data ONLINE ALL;

** Offline disks **
    SQL> ALTER DISKGROUP data OFFLINE DISK 'disk_0000', 'disk_0001';
    SQL> ALTER DISKGROUP data OFFLINE DISKS IN FAILGROUP 'fg_99';
    SQL> ALTER DISKGROUP data OFFLINE DISK d1_0001 DROP AFTER 30m;

** Mounting diskgroups **
    Diskgroups are mounted at ASM instance startup and unmounted at ASM instance shutdown.
    Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as below.
    SQL> ALTER DISKGROUP ALL MOUNT;
    SQL> ALTER DISKGROUP dg_data2 MOUNT;
    SQL> ALTER DISKGROUP dg_data2 MOUNT RESTRICTED;

** Dismounting diskgroups **
    SQL> ALTER DISKGROUP ALL DISMOUNT;
    SQL> ALTER DISKGROUP dg_fra DISMOUNT;

** Changing attributes **
    SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'compatible.rdbms' = '11.1';
    SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'compatible.asm' = '11.2';
    SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'disk_repair_time' = '4.5h';

** Listing attributes **
    SQL> SELECT * FROM V$ASM_ATTRIBUTE;

** Rebalancing **
    Diskgroups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement.
    If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used.
    Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

    SQL> ALTER DISKGROUP disk_group_1 REBALANCE POWER 6;

** IO statistics of a diskgroup **
    SQL> SELECT * FROM V$ASM_DISK_IOSTAT;

** Directories **
    As in other file systems, an ASM directory is a container for files, and an ASM directory can be part of a tree structure of other directories.
    The fully qualified filename represents a hierarchy of directories in which the plus sign (+) represent the root directory.

** Creating a directory **
    SQL> ALTER DISKGROUP dg_1 ADD DIRECTORY '+dg_1/my_dir';

** Renaming a directory **
    SQL> ALTER DISKGROUP dg_1 RENAME DIRECTORY '+dg_1/my_dir' TO '+dg_1/my_dir_2';

** Deleting a directory **
    SQL> ALTER DISKGROUP dg_1 DROP DIRECTORY '+dg_1/my_dir_2' FORCE;

** Files **
    There are several ways to reference ASM files.
    Some forms are used during creation and some for referencing ASM files.
    Every file created in ASM gets a system-generated filename, known as fully qualified filename,
    this is same as complete path name in a local file system.

    The forms of the ASM filenames are:
    Filename Type Format
    Fully Qualified ASM Filename : +dgroup/dbname/file_type/ file_type_tag.file.incarnation
    Numeric ASM Filename : +dgroup.file.incarnation
    Alias ASM Filenames : +dgroup/directory/filename
    Alias ASM Filename with Template : +dgroup(template)/alias
    Incomplete ASM Filename : +dgroup
    Incomplete ASM Filename with Template : +dgroup(template)
    ASM generates filenames according to

the following scheme : +diskGroupName/databaseName/fileType/

                                           fileTypeTag.fileNumber.incarnation

e.g: +dgroup2/crm/CONTROLFILE/Current.256.541956473
     +dg_fra/hrms/DATAFILE/users.309.621906475

    ASM does not place system-generated files into user-created directories;
    it places them only in system-generated directories.
    We can add aliases or other directories to a user-created directory.

** Dropping Files **
    To find out all the diskgroups:
    
    Some forms are used during creation and some for referencing ASM files.
    Every file created in ASM gets a system-generated filename, known as fully qualified filename,
    this is same as complete path name in a local file system.
    

    Files are not deleted automatically if they are created using aliases,

    as they are not Oracle Managed Files (OMF), or if a recovery is done

    to a point-in-time before the file was created.

    

    For these circumstances it is necessary to manually delete the files, as shown below.

** Dropping file using an alias **

** Dropping file using a numeric form filename **
    SQL> ALTER DISKGROUP dg_2 DROP FILE '+dg_2.321.123456789';

** Dropping file using a fully qualified filename **
    SQL> ALTER DISKGROUP dg_2 DROP FILE '+dg_2/mydb/datafile/my_ts.292.265390671';

** Aliases **
    Aliases allow you to reference ASM files using user-friendly names,

rather than the fully qualified ASM filenames.

** Creating an alias **
    Creating an alias, using the fully qualified filename
    SQL> ALTER DISKGROUP dg_3 ADD ALIAS '+dg_3/my_dir/users.dbf' FOR '+dg_3/mydb/datafile/users.392.333222555';
    Creating an alias, using the numeric form filename
    SQL> ALTER DISKGROUP dg_3 ADD ALIAS '+dg_3/my_dir/my_file.dbf' FOR '+dg_3.317.111222333';

** Renaming an alias **
    SQL> ALTER DISKGROUP dg_3 RENAME ALIAS '+dg_3/my_dir/my_file.dbf' TO '+dg_3/my_dir/my_file2.dbf';

** Deleting an alias **
    SQL> ALTER DISKGROUP dg_3 DELETE ALIAS '+dg_3/my_dir/my_file.dbf';

** Checking Metadata **
    The internal consistency of diskgroup metadata can be checked in a number of ways using

the CHECK clause of the ALTER DISKGROUP statement.

** Checking metadata for a specific file **
    SQL> ALTER DISKGROUP dg_5 CHECK FILE '+dg_5/my_dir/my_file.dbf'

** Checking metadata for a specific disk in the diskgroup **
    SQL> ALTER DISKGROUP dg_5 CHECK DISK diska1;

** Checking metadata for a specific failure group in the diskgroup **
    SQL> ALTER DISKGROUP dg_5 CHECK FAILGROUP failure_group_1;

** Checking metadata for all disks in the diskgroup **
    SQL> ALTER DISKGROUP dg_5 CHECK ALL;
    SQL> ALTER DISKGROUP dg_5 CHECK;
    SQL> ALTER DISKGROUP dg_5 CHECK NOREPAIR;
    SQL> ALTER DISKGROUP dg_5 CHECK REPAIR;

** User Management **
From Oracle 11g release 2, we can create ASM users and usergroups and manipulate the permissions

and ownership of files.

** Creating an ASM usergroup **
    SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp1';
    SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp2' WITH MEMBER 'oracle1','oracle2';

** Listing ASM usergroups **
    SQL> SELECT * FROM V$ASM_USERGROUP;

** Dropping an ASM usergroup **
    SQL> ALTER DISKGROUP data_dg DROP USERGROUP 'grp1';

** Modifying(adding/deleting ASM users to/from) an ASM usergroup **
    SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' ADD MEMBER 'oracle3';
    SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' DROP MEMBER 'oracle3';

** Creating an ASM user **
    SQL> ALTER DISKGROUP data_dg ADD USER 'oracle1';

** Listing ASM users **
    SQL> SELECT * FROM V$ASM_USER;

** Listing ASM usergroups to which user belongs **
    SQL> SELECT * FROM V$ASM_USERGROUP_MEMBER;

** Dropping an ASM user **
    SQL> ALTER DISKGROUP data_dg DROP USER 'oracle1';

** Modifying permissions for a file **
    SQL> ALTER DISKGROUP data_dg SET PERMISSION OWNER=read write, GROUP=read only, OTHER=none

FOR FILE '+data_dg/controlfile.f';

** Modifying ownership of a file **
    SQL> ALTER DISKGROUP data_dg SET OWNERSHIP OWNER='oracle1', GROUP='grp1'

FOR FILE '+data_dg/controlfile.f';

** Volume Management **
    From 11g release 2, we can create Oracle ASM Dynamic Volume Manager (Oracle ADVM) volumes in a diskgroups.
    The volume device associated with the dynamic volume can then be used to host an (Oracle ACFS) file system.

** Creating a volume **
    SQL> ALTER DISKGROUP data_dg ADD VOLUME volume1 SIZE 20G;
    ASMCMD equivalent for this command is volcreate (11gR2 command).

** Listing volume information **
    SQL> SELECT * FROM V$ASM_VOLUME;

** Listing volume statistics **
    SQL> SELECT * FROM V$ASM_VOLUME_STAT;

** Dropping a volume **
    SQL> ALTER DISKGROUP data_dg DROP VOLUME volume1;

** Resizing a volume **
    SQL> ALTER DISKGROUP fra_dg RESIZE VOLUME volume1 SIZE 25G;

** Disabling a volume **
    SQL> ALTER DISKGROUP redo_dg DISABLE VOLUME volume1;
    SQL> ALTER DISKGROUP ALL DISABLE VOLUME ALL;

** Enabling a volume **
    SQL> ALTER DISKGROUP arch_dg ENABLE VOLUME volume1;

** Setting a volume **
    SQL> ALTER DISKGROUP asm_dg_data MODIFY VOLUME volume1 USAGE 'acfs';

** Listing the current operations **
    SQL> SELECT * FROM V$ASM_OPERATION;

** ASM Views **
    SELECT * FROM V$ASM_ALIAS;
    SELECT * FROM V$ASM_ATTRIBUTE;
    SELECT * FROM V$ASM_CLIENT;
    SELECT * FROM V$ASM_DISK;
    SELECT * FROM V$ASM_DISK_IOSTAT;    
    SELECT * FROM V$ASM_DISK_STAT;
    SELECT * FROM V$ASM_DISKGROUP;
    SELECT * FROM V$ASM_DISKGROUP_STAT;
    SELECT * FROM V$ASM_FILE;
    SELECT * FROM V$ASM_FILESYSTEM;
    SELECT * FROM V$ASM_OPERATION;
    SELECT * FROM V$ASM_TEMPLATE;
    SELECT * FROM V$ASM_USER;
    SELECT * FROM V$ASM_USERGROUP;
    SELECT * FROM V$ASM_USERGROUP_MEMBER;
    SELECT * FROM V$ASM_VOLUME;
** Listing diskgroups **
** Dropping diskgroups **
    Diskgroups can be deleted using the DROP DISKGROUP statement.
    SQL> ALTER DISKGROUP dg_2 DROP FILE '+dg_2/my_dir/my_file.dbf';

    SELECT * FROM V$ASM_VOLUME_STAT;