Oracle ASM

11G ASM Admin Guide

Configuring Automatic Storage Management


Managing Automatic Storage Management Disk Groups

asmlib/oracleasm and san_disks/multipath

asm on san-disks 

# yum install kmod-oracleasm -y
# yum install oracleasm-support -y
# yum install sg3_utils -y

# oracleasm init

# oracleasm configure -i 

/etc/init.d/oracleasm status

/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks
-- ---------------------------------------------
# rescan-scsi-bus.sh
-- ---------------------------------------------
vim /etc/multipath.conf

# wwwid = LUN ID         // This need to match scanned one and provided by storage team.
# Alias = OCS_DATA_089   // This is just a reference for you, Name requested by DBA team.

multipath {
                  wwid 36ac61751004ce7d050208e2900000146
                  alias  OCS_DATA_089
          }

multipath {
                  wwid 36ac61751004ce7d050208e7d00000147
                  alias  OCS_ARCHIVE_006
          }

# -- ---------------------------------------------

# sudo /etc/init.d/multipathd reload

# sudo multiapth -ll

# sudo multipath -ll OCS_DATA_089

# /etc/init.d/oracleasm createdisk OCS_DATA_089 /dev/mapper/OCS_DATA_089

# -- ---------------------------------------------

# cat /etc/sysconfig/oracleasm-_dev_oracleasm
# ORACLEASM_UID=oracle
# ORACLEASM_GID=oinstall

-- ---------------------------------------------

# /etc/init.d/oracleasm status
# /etc/init.d/oracleasm listdisks
# /etc/init.d/oracleasm scandisks
# dmesg
# fdisk -l

-- ---------------------------------------------

Install and configure oracleasm

-- installation oracleasm
asmlib:
oracleasmlib-2.0.4-1.el4.i386.rpm
oracleasm-support-2.1.3-1.el4.i386.rpm
oracleasm-2.6.9-78.EL-2.0.5-1.el4.i686.rpm

root@rac1 oracleasm]# cd /tmp/oracleasm/
oracleasm-2.6.18-164.el5-2.0.5-1.el5.x86_64.rpm
oracleasm-2.6.18-164.el5debug-2.0.5-1.el5.x86_64.rpm
oracleasm-2.6.18-164.el5-debuginfo-2.0.5-1.el5.x86_64.rpm
oracleasm-2.6.18-164.el5xen-2.0.5-1.el5.x86_64.rpm
oracleasmlib-2.0.4-1.el5.x86_64.rpm
oracleasm-support-2.1.8-1.el5.x86_64.rpm

rpm -ivh oracleasm-support-2.1.8-1.el5.x86_64.rpm
rpm -ivh oracleasm-2.6.18-164.el5-2.0.5-1.el5.x86_64.rpm
rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm

-- configue oracleasm
root@rac1 ~]# /usr/sbin/oracleasm configure -i


[root@rac1 ~]# /usr/sbin/oracleasm configure -i

Configuring the Oracle ASM library driver.


This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting without typing an

answer will keep that current value.  Ctrl-C will abort.


Default user to own the driver interface []: grid

Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

--  initialized oracleasm
[root@rac1 ~]# /usr/sbin/oracleasm init

Creating /dev/oracleasm mount point: /dev/oracleasm

Loading module "oracleasm": oracleasm

Mounting ASMlib driver filesystem: /dev/oracleasm

-- create, scan, list disk
/etc/init.d/oracleasm --help
/etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}

/etc/init.d/oracleasm createdisk VOL1 /dev/sdc1 
/etc/init.d/oracleasm scandisks 
/etc/init.d/oracleasm listdisks  

asmca

asmca: asm configuration assistant  

asmcmd

asmcmd: lsdg  

asmcmd
ASMCMD> ls
ASMCMD> help
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias

Using dbca, Database Configuration Assistant

Creating an ASM Instance and a Disk Group with DBCA 

Tablespace

create tablespace tbs1 datafile '+DGROUP1' size 20M;

CREATE UNDO TABLESPACE myundo DATAFILE '+data(my_undo_template)/orcl/my_undo_ts' SIZE 200M; 

CREATE TABLESPACE mytblspace DATAFILE '+data' SIZE 200M AUTOEXTEND ON;

alter tablespace xtablespace_name add datafile '+DISKGROUPNAME' 200M;

ASM Instance

2. set ORACLE_SID=*ASM
SPFILE=$ORACLE_HOME/dbs/spfile+ASM.ora
SPFILE='+DATA/asm/asmparameterfile/asmspfile.ora'

STARTUP PFILE=/u01/oracle/dbs/spfileasm_init.ora

-- ---------------------------------------------

-- After the instance is running, use the ASMCMD spset command to update the SPFILE path in the GPNP profile. See "spset".
asmcmd
ASMCMD> spset +DATA/asm/asmparameterfile/asmspfile.ora

-- ----------------------------------------------

ASM_DISKSTRING=/dev/rdsk/mydisks/*
ASM_DISKSTRING=/dev/rdsk/*disk3, /dev/rdsk/*disk4

ASM_POWER_LIMIT = 10
INSTANCE_TYPE = ASM


CREATE SPFILE = '+DATA/asmspfile.ora' FROM PFILE = '$ORACLE_HOME/dbs/asmpfile.ora';

ALTER SYSTEM SET ASM_DISKGROUPS = DATA, FRA;
ASM_DISKGROUPS = DATA, FRA

-- --------------------------------------------------------

SQLPLUS /NOLOG
SQL> CONNECT SYS AS SYSASM
SQL> STARTUP
-- ---------------------------------------------------------
sys@\"myhost.mydomain.com:1521/+ASM\" AS SYSASM
-- ---------------------------------------------------------
REM create a new user, then grant the SYSASM privilege
SQL> CREATE USER new_user IDENTIFIED by new_user_passwd;
SQL> GRANT SYSASM TO new_user;

REM connect the user to the ASM instance
SQL> CONNECT new_user AS SYSASM;
Enter password:

REM revoke the SYSASM privilege, then drop the user
SQL> REVOKE SYSASM FROM new_user;
SQL> DROP USER new_user;
-- ----------------------------------------------------------
SQLPLUS /NOLOG
SQL> CONNECT SYS AS SYSASM
Enter password: sys_password
Connected.
SQL> SHUTDOWN NORMAL

ASM Instance File Directory Location

ls $ORACLE_BASE/diag/asm/+asm/+ASM

Diskgroup

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

drop diskgroup dbgroup1 including contents;

CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/diska1',
'/devices/diska2',
'/devices/diska3',
'/devices/diska4'
FAILGROUP controller2 DISK
'/devices/diskb1',
'/devices/diskb2',
'/devices/diskb3',
'/devices/diskb4'
ATTRIBUTE 'compatible.asm' = '11.2', 'compatible.rdbms' = '11.2',
          'sector_size'='4096';

CREATE DISKGROUP ocr_data NORMAL REDUNDANCY
   FAILGROUP fg1 DISK '/devices/diskg1'
   FAILGROUP fg2 DISK '/devices/diskg2'
   QUORUM FAILGROUP fg3 DISK '/devices/diskg3'
   ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';

ALTER DISKGROUP data DROP FILE '+data/orcl/my_undo_ts';

ALTER DISKGROUP data ADD ALIAS '+data/orcl/second.dbf' FOR '+data/orcl/datafile/mytable.342.123456789';

ALTER DISKGROUP data RENAME ALIAS '+data/orcl/datafile.dbf'  TO '+data/payroll/compensation.dbf';

-- failed: it attempts to drop a system-generated filename and this syntax is not allowed.
ALTER DISKGROUP data DROP ALIAS '+data/payroll/compensation.dbf';

-- ok
ALTER DISKGROUP data DROP ALIAS '+data/orcl/datafile/mytable.342.123456789';

--  Dropping a file and alias from a disk group using the alias name
ALTER DISKGROUP data DROP FILE '+data/payroll/compensation.dbf';

-- Dropping a file and alias from a disk group using the system-generated name
ALTER DISKGROUP data DROP FILE '+data/orcl/datafile/mytable.342.123456789';

-- renameing a directory
ALTER DISKGROUP data RENAME DIRECTORY '+data/mydir'  TO '+data/yourdir';

-- Dropping a Directory
ALTER DISKGROUP data DROP DIRECTORY '+data/yourdir' FORCE;

-- Creating a new directory
ALTER DISKGROUP data ADD DIRECTORY '+data/orcl';

-- Creating a new subdirectory
ALTER DISKGROUP data ADD DIRECTORY '+data/orcl/first_dir/second_dir';

ALTER DISKGROUP data ADD    TEMPLATE reliable   ATTRIBUTES (HIGH FINE);
ALTER DISKGROUP data ADD    TEMPLATE unreliable ATTRIBUTES (UNPROTECTED);

ALTER DISKGROUP data MODIFY TEMPLATE reliable   ATTRIBUTES (COARSE);

ALTER DISKGROUP data DROP   TEMPLATE unreliable;

ALTER  SYSTEM SET DB_CREATE_FILE_DEST = '+data(mytemplate)';
CREATE TABLESPACE mytblspace;

Status, Stats and Dictionary View

select table_name from dict where table_name like '%ASM%';

V$ASM_CLIENT 

SELECT name, value FROM V$ASM_ATTRIBUTE WHERE name = 'sector_size' AND group_number = 1;

SELECT group_number, sector_size FROM V$ASM_DISKGROUP WHERE group_number = 1;

-- ----------------------------------------------------------

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name,
     SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg, 
     V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA'
     AND dg.group_number = a.group_number;

-- ----------------------------------------------------------

SELECT name AS diskgroup, compatibility AS asm_compat, database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d 
WHERE dg.group_number = d.group_number;

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
    SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
    SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 
    FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c  
    WHERE dg.group_number = c.group_number;

SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name 
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u 
     WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
      ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug 
      WHERE dg.group_number = ug.group_number AND dg.name = 'DATA' 
      AND ug.owner_number = u.user_number;

-- checck permission
SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name, 
     um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um, 
     V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND 
     dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA' 
     AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

-- checck permission
SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
     f.usergroup_number, ug.NAME FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug, 
     V$ASM_FILE f, V$ASM_ALIAS a WHERE dg.group_number = f.group_number AND 
     dg.group_number = u.group_number AND dg.group_number = ug.group_number AND 
     dg.name = 'FRA' AND f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number
     AND f.file_number = a.file_number;

-- Viewing Intelligent Data Placement information with V$ASM_FILE
SELECT dg.name AS diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads,
   f.hot_writes,  f.cold_reads, f.cold_writes
   FROM V$ASM_DISKGROUP dg, V$ASM_FILE f 
   WHERE dg.group_number = f.group_number and dg.name = 'DATA';

-- Viewing Intelligent Data Placement information with V$ASM_TEMPLATE
SELECT dg.name AS diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region 
       FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t 
       WHERE dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name;

SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;

SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#;

SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE  WHERE status='ONLINE'; 

SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath 
    FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v 
    WHERE dg.group_number = v.group_number and dg.name = 'DATA';

-- exadata
ASM:
select dg.name, a.value from v$asm_diskgroup dg, v$asm_attribute a
where dg.group_number=a.group_number and a.name='disk_repair_time';

-- alter diskgroup data_dm02 set attribute 'disk_repair_time'='10h';

# Oracle Grid Infrastructure
GRID_HOME/root.sh

GRID_HOME/rootupgrade.sh

srvctl status listener
srvctl status asm

Otherwise, start the Oracle ASM instance with SRVCTL.
$ srvctl start asm

Using Rman to Move Oracle Datafiles to ASM

STEP 1: Backup
-- use rman to move to asm, backup datafile as image copy

RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
  BACKUP AS COPY
    INCREMENTAL LEVEL 0
    DATABASE
    FORMAT '+DATA'
    TAG 'ORA_ASM_MIGRATION';
}


-- optionally plus incremental 1
RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
  BACKUP INCREMENTAL LEVEL 1 
    FOR RECOVER OF COPY WITH TAG 'ORA_ASM_MIGRATION' 
    DATABASE;
}

Step 2: Disable some database features

RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
RMAN> BACKUP AS BACKUPSET SPFILE;

-- disable change tracking
RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";

-- disable flashback
RMAN> SQL "ALTER DATABASE FLASHBACK OFF";

-- drop restore point 
RMAN> SQL "DROP RESTORE POINT Q106";

RMAN> SHUTDOWN IMMEDIATE;

Step 3: Restore from backup to asm datafile

RMAN> STARTUP MOUNT;
RMAN> RESTORE SPFILE TO '+DATA/spfilesid.ora';
RMAN> SHUTDOWN IMMEDIATE;

SQL> CREATE SPFILE='+DATA/spfilesid.ora' FROM PFILE='?/dbs/initsid.ora';

SPFILE='+DATA/spfilesid.ora'

-- -------------------------------------------------------------------

-- receovery area
DB_CREATE_FILE_DEST 
DB_CREATE_ONLINE_LOG_DEST_n 

SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA' SID='*';

SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+FRA' SCOPE=SPFILE SID='*';

SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+DATA' SCOPE=SPFILE SID='*';

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'original_cf_name';
RMAN> ALTER DATABASE MOUNT;


RMAN> SWITCH DATABASE TO COPY;
RMAN> RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
  RECOVER DATABASE;
}



-- Enable the following features
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

-- Drop And Re-create tempfile
SQL> ALTER DATABASE TEMPFILE 'tempfile_name' DROP;
SQL> ALTER TABLESPACE temp_tbs_name ADD TEMPFILE;

-- Moving Redo files

SET SERVEROUTPUT ON;
DECLARE
   CURSOR rlc IS
      SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
      FROM   V$LOG
      UNION
      SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
      FROM   V$STANDBY_LOG
      ORDER BY 1;
   stmt     VARCHAR2(2048);
BEGIN
   FOR rlcRec IN rlc LOOP
      IF (rlcRec.srl = 'YES') THEN
         stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
                 rlcRec.thr || ' SIZE ' || rlcRec.bytes;
         EXECUTE IMMEDIATE stmt;
         stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
         EXECUTE IMMEDIATE stmt;
      ELSE
         stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
                 rlcRec.thr || ' SIZE ' ||  rlcRec.bytes;
         EXECUTE IMMEDIATE stmt;
         BEGIN
            stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
            DBMS_OUTPUT.PUT_LINE(stmt);
            EXECUTE IMMEDIATE stmt;
         EXCEPTION
            WHEN OTHERS THEN
               EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
               EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
               EXECUTE IMMEDIATE stmt;
         END;
      END IF;
   END LOOP;
END;
/

-- Move Archive Logfiles and rman backup to asm

RMAN> DELETE REMOTE ARCHIVELOG ALL;

RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

  BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
  BACKUP BACKUPSET ALL DELETE INPUT;
  BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
}

-- References:
andby6