Oracle ASM

I am in the process of putting my notes into web pages, please keep checking in later for more contents

top   prev   next  
11G ASM Admin Guide

Configuring Automatic Storage Management


Managing Automatic Storage Management Disk Groups

top   prev   next  

Using dbca, Database Configuration Assistant

Creating an ASM Instance and a Disk Group with DBCA 

top   prev   next  

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;


top   prev   next  

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

top   prev   next  

ASM Instance File Directory Location

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

top   prev   next  

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';



top   prev   next  
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';


top   prev   next  
-- 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;


top   prev   next  
-- 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';


top   prev   next  
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;

top   prev   next  

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';

top   prev   next  
# Oracle Grid Infrastructure
GRID_HOME/root.sh

GRID_HOME/rootupgrade.sh

top   prev   next  
srvctl status listener
srvctl status asm

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

top   prev   next  

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;
}


top   prev   next  
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;


top   prev   next  
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;
/


top   prev   next  
-- 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;
}