Thursday, August 2, 2012

What is Oracle ASM - Automatic Storage Management?


PROBLEM
What is Oracle Automatic Storage Management (ASM)?

  1. ASM Advantages
  2. ASM Basic Concepts
    • Disk Group (DG)
    • Allocation Unit (AU)
    • ASM Instance
    • ASM Templates 
    • ASM Striping
    • Disk Group Dynamic Rebalancing
    • ASM Mirroring & Failure Groups
  3. ASM Instance Management
  4. ASM initialization parameters
  5. ASM Shutdown / Startup
  6. ASM Instance Architecture
  7. Common ASM V$ Views
  8. ASM Fast Mirror Resync
  9. Bad block repair (11g)
  10. ASM Intallation Guidelines
  11. DEMO!
SOLUTION

-------------------------------------
-- Oracle Automatic Storage Management (ASM)
-------------------------------------
-- * An Oracle file system, built specifically for database related files.
-- * Can be used for single or multiple instance architecture (RAC).
--     (Eliminates the need for third party Clustered File System)
-- * Enables storage solutions such as Striping & Mirroring (for performance & Survivability)
-- * Stripes and Mirrors at file level (as opposed to volume level)
--     - Files are striped more efficiently.
--     - Mirroring can be enabled for specific files only, at different levels.
-- * Each DB can have both ASM and regular files. (Can also migrate some files to ASM)
-- * ASM can store all Oracle file types. (Not just Data Files).

-------------------------------------
-- ASM Advantages
-------------------------------------
-- 1. it is aware of the nature of Oracle database files. This means it can make
--    more intelligent decisions about how to manage the files than a third-party product.
-- 2. ASM can handle files individually, whereas all other LVMs work at the
--    volume level: they are not aware of the files within the volume. So with a third-party
--    LVM, you have to specify RAID attributes per volume.
--    ASM can specify the attributes per file, so you can for instance have three-way
--    mirroring for your SYSTEM tablespace datafiles but no mirroring at all for your
--    temporary tablespaces’ tempfiles, all within the same logical volume.

-------------------------------------
-- ASM Basic Concepts
-------------------------------------

-----------------------
-- * Disk Group (DG)
-----------------------
--   * A group of disks which are managed as a single logical unit:
--     * If I need to add more space to a DG, I'll add another disk to it.
--     * Although we may be constantly adding ASM Files and Disks to a growing system, the number of Disk Groups to manage remains constant.
--   * ASM files are assigned to a Disk Group, instead of to a specific disk.
--   * All data will be striped and mirrored between the disks of this DG.
--   * DG >-< DB:
--     * Each DG can contain files from multiple DBs.
--     * Each DB can store his files on different DGs.
--   * Each DG is self describing, and contains all information about his Disks and Files.


CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4

-----------------------
-- * Allocation Unit (AU)
-----------------------
--   *Each datafile is striped to Allocation Units (AU).
--   * AUs are spread evenly across the disks of the Disk Group.
--   * AUs are rebalanced automatically across the disks whenever disks are added or removed from the DG.
--   * AU size:
--     * 10g: 1MB
--     * 11g: Determined at DG creation. (1MB, 2, 4, 8, 16, 32, 64MB)

CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4
ATTRIBUTE 'au_size'='4M';

-----------------------
-- * ASM Instance
-----------------------
--   * ASM metadata is managed by a single ASM instance, which can serve many DBs and Instances.
--   * The instance mounts his DGs instead of a specific database.
--   * Created and configured via the DBCA. ("Configure ASM")
--   * The instances, however, work directly with the ASM files.

----------------------------------
-- Creating a Disk Group
-- (Performed in the ASM instance)
----------------------------------

conn /@ASM AS SYSASM

-- See current Disk Groups:
SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

col path for a50
-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- Join:
SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
FROM v$asm_diskgroup dg JOIN v$asm_disk d
USING (GROUP_NUMBER)
GROUP BY GROUP_NUMBER, dg.name ;


-- Create a new disk group DG2:
CREATE DISKGROUP DG2
DISK
'\\.\ORCLDISK_LABEL_G' NAME DG2_0,
'\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;


-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- DROP DISKGROUP DG2;

-------------------------------------
-- Create a tablespace with ASM file
-- (Performed in the DB instance)
-------------------------------------
conn sys/oracle@orcl as sysdba

CREATE TABLESPACE ASM_TBS
  DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

SELECT name FROM v$datafile;

-- DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;


-- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
--   $oracle_grid_home\BIN\asmca.bat
-- * for stamping and name changing :
--   SHUTDOWN ASM => stamp/change names => STARTUP ASM


-------------------------------------
-- ASM Templates
-------------------------------------

-- * A template is a collection of file attributes that are applied to the ASM files.
--   It is associated with a Disk Group.
-- * Whenever a file is created the file attributes specific to the template are applied to 
--   the files.
-- * For eg., if we want to create a users tablespace that contains data file we use the
--   DATAFILE template.
-- * Default Template: When you create a disk group, Oracle creates system default template
--   for the disk group.

Select * From V$ASM_TEMPLATE

-------------------------------------
-- ASM Striping
-------------------------------------
-- * ASM stripes the file to AUs, and devides them between the DG disks.
-- * Two types of striping:
--      1. Coarse-based striping:
--          - Each stripe size is one AU.
--          - Classic striping. Suitable for most file types.
--          - Used for load balancing.
--      2. Fine-Grained striping
--            - Each stripe size is 128KB. (Wider distribution of data)
--            - Used for decreasing IO Latency (Waiting for IO).
--            - Suitable for small IO operations such as Redo Log Files.

/*
ALTER DISKGROUP DG2
      ATTRIBUTE 'template.tname.stripe' = 'COARSE',
            'template.tname.redundancy' = 'MIRROR';

ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
ATTRIBUTES (FINE);

ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
ATTRIBUTES (COARSE);

Select * From V$ASM_TEMPLATE
WHERE name = ('DATAFILE', 'ONLINELOG')
AND group_number = 2

*/

-------------------------------------
-- Disk Group Dynamic Rebalancing
-------------------------------------
-- * ASM performs automatic rebalancing online whenever we add / remove disks from a DG.
-- * ASM_POWER_LIMIT:
--   - Controls IO overhead during rebalancing.
--   - Values are between 1 and 11. (Minimum overhead (1) .. Minumum time (11))
--   - The higher the limit, the faster rebalancing will complete.
--   - Lower values will take longer, but consume fewer processing and I/O resources.

show parameter ASM_POWER_LIMIT

-------------------------------------
-- ASM Mirroring & Failure Groups
-------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk,
--       a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents
--      from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group,
--     which could fail at the same time because they share hardware.
--     (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated
--      on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents
--      from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for --        protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A protected disk group:
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1,
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6';

col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A Disk Group with 2 Failure Groups (one for each controller):
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  FAILGROUP controller1 DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1
  FAILGROUP controller2 DISK
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6' NAME DG2_3;


col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;



-------------------------------------
-- ASM Instance Management
-------------------------------------

-------------------------------------
-- ASM initialization parameters
-------------------------------------
-- * Important ASM related Parameters:
--    - INSTANCE_TYPE  : should be set to ASM. This is the only parameter that MUST be --      defined.
--    - ASM_DISKGROUPS : List of disk groups to be mounted at startup.
--                        (Or during ALTER DISKGROUP ALL MOUNT)
--    - ASM_POWER_LIMIT: The speed for a rebalance operation (1-11. default is 1).
-- * Additional parameters can be set...

-------------------------------------
-- ASM Shutdown / Startup
-------------------------------------

-------------
-- STARTUP
-------------
-- * The options for the STARTUP command are:
--   - NOMOUNT - Starts the ASM instance without mounting any disk groups.
--   - MOUNT   - Starts the ASM instance and mounts the disk groups specified
--               by the ASM_DISKGROUPS parameter.
--   - FORCE   - Performs a SHUTDOWN ABORT before restarting the ASM instance.
--   - OPEN    - Not a valid option.
--   - RESTRICT- DB instances cannot use the DGs. (Used for maintenance)

-- * If the ASM_DISKGROUPS parameter is empty, no DGs are mounted.
--    (Can be mounted manually using ALTER DISKGROUP...MOUNT)

-------------
-- SHUTDOWN
-------------
-- * ASM instance shutdown is possible only after shutting down all instances connected to it
--     (Otherwise: ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance)

-- * The options for the SHUTDOWN command are:
--   - NORMAL    - Waits for all connections to finish (DB instances & SQL sessions)
--   - TRANSACTIONAL - Waits for any active transactions to complete. (Doesn't wait for sessions)
--   - IMMEDIATE - Same as TRANSACTIONAL.
--   - ABORT     - The ASM instance shuts down instantly (Crash).

-- * Shutdown ABORT will require crash recovery upon next startup:
--   - All connected instances will also shutdown.
--       (ORA-15064: communication failure with ASM instance)
--   - Each DG has log files for recovery of ASM metadata.
--   - Actual data is recovered using the DB Redo Logs.

------------------------------
-- ASM Instance Architecture
------------------------------
-----------------
-- MEMORY
-----------------
-- * SGA:
--    - Shared Pool: Used for metadata information
--    - Large Pool : Used for parallel operations
--    - ASM Cache  : Used for data blocks during rebalance operations
--    - Free Memory: Unallocated memory available
-- * Minimum recommended size is 256 MB. (Depends on the used disk space size)
-- * AMM (Automatic memory management) is enabled by default.

------------------------------
-- Background Processes
------------------------------
-----------------------
-- In the ASM instance
-----------------------
-- * The ASM instance contains some special ASM background processes:
--    - RBAL - Manages the Rebalancing process across the DG.
--    - ARBn - Perform the actual relocation of the AU during rebalancing.
--    - GMON - (Group Monitor) - Disk-level activities (Offline, Drop etc).
--    - MARK - Marks ASM allocation units as stale when needed
--    - Onnn - pool of connections to the ASM instance for exchanging messages

-----------------------
-- In the DB instance
-----------------------
-- * In addition, each regular instance that uses ASM has some special background processes:
--    - ASMB - Connects to the ASM instance at startup, and works with it (Data file management).



--------------------------------------------
-- Common ASM V$ Views
--------------------------------------------
-- V$ASM_FILE - Files of disk groups mounted by the ASM instance.

SELECT file_number, name, ROUND(bytes/1024/1024) SIZE_MB
FROM v$asm_alias JOIN v$asm_file
USING (file_number)
ORDER BY file_number, name;


-- V$ASM_CLIENT - DB instances using the ASM instance.
--   (In the DB - ASM instance data if the database has open ASM files)


-- V$ASM_DISK   - List of discovered Disks. (even if not part of any disk group).
--   (In the DB - Disks of DGs used by the database instance).
col name for a10
col failgroup for a11
col path for a20
col redundancy for a10
SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH
FROM v$asm_disk
ORDER BY path;

-- V$ASM_DISKGROUP - List of discovered disk groups.
--   (In the DB - DGs mounted by the local ASM instance)

-- V$ASM_OPERATION - Files with executing long running operations.

-- V$ASM_TEMPLATE - Templates of DGs mounted by the ASM instance.

-- V$ASM_ALIAS  - Aliases that belong to mounted disk groups.
--              - Files with aliases will have 2 rows. (Original & alias)

SELECT file_number, name, CASE ALIAS_DIRECTORY WHEN 'Y' THEN 'Directory' ELSE 'File' END Type
FROM v$asm_alias
ORDER BY type, file_number;

-------------------------------------
-- SYSASM
-------------------------------------
-- * Meant for ASM management. (Separate from SYSDBA for security)
-- * As ASM has no Data Dictionary, users are authenticated via OS or password-file.

-- * SYSOPER, enables basic administration, but not CREATE/ALTER/DROP DGs.
--     (Startup/Shutdown, Mount/Dismount, Rebalance, Online/Offline disk, v$ASM_*)
-- * SYSDBA enables all SYSASM privs, but will be limited in the future.

-- * Via OEM (Only if connected via SYSASM):
--    ASM => Users => Create/Edit/Delete
-- (Connecting as SYSASM: Preferences => Preferred Credentials => ASM:Set Credentials => Specify...

-- In CMD:
SET ORACLE_SID=+ASM
sqlplus /nolog
conn / as sysasm

SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

-- Or:
CREATE USER asm_mgr IDENTIFIED BY asm_mgr;
GRANT SYSASM TO asm_mgr;
SELECT * FROM v$pwfile_users;
conn asm_mgr/asm_mgr AS SYSASM


-------------------------------------
-- Interaction Between Database Instances and ASM
-------------------------------------

-- * The Database Instance and ASM Instance work together in a coordinated fashion.
--   A Database instance must interact with ASM to map database files to ASM extents.
-- * A Database instance also receives a constant stream of messages relating to ASM operations
--   (such as disk group rebalancing) that may lock or move ASM extents.
-- * Database I/O is not channeled through the ASM instance. In fact, the database conducts I/O
--   operations directly against ASM files.

-------------------------------------
-- Disk Group Maintenance - SYNTAX...
-------------------------------------
-- * Should be performed from the ASM instance by a user with a SYSASM privilege.

set oracle_sid=+ASM
sqlplus / as sysasm

col name for a10
col path for a30

SELECT GROUP_NUMBER, NAME, STATE, TYPE, TOTAL_MB
FROM V$ASM_DISKGROUP
ORDER BY 1;

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

-----------------------
-- CREATE DG
-----------------------
-- * We can optionally name and size each disk.
--    (Otherwise a system generated name is given, and ASM determines its size automatically)
-- * FORCE - Move the disk from another DG to this one.

CREATE DISKGROUP DG2
  NORMAL REDUNDANCY
  DISK
   '//./ORCLDISKDATA3' NAME DG2_0,
   '//./ORCLDISKDATA4' NAME DG2_1,
   '//./ORCLDISKDATA5' NAME DG2_2,
   '//./ORCLDISKDATA6' NAME DG2_3;

-----------------------
-- DROP DG
-----------------------
-- * INCLUDING CONTENTS - Must be specified if it contains files.
-- * The DG must be mounted, and its files inactive.
-- * The header of each disk is formatted, so it will be available
--     to other DGs or to the OS.

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-----------------------------
-- Adding disks to a DG
-----------------------------
-- * Disks are added and Rebalaced online while data is fully accessible.
-- * The rebalancing load is determined according to ASM_POWER_LIMIT.

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk
WHERE group_number=0
ORDER BY path;

-- Add two disks to a "DG1":
ALTER DISKGROUP DG1 ADD DISK
   '//./ORCLDISKDATA3' NAME DG1_3,
   '//./ORCLDISKDATA4' NAME DG2_4;


-- Special discovery characters can be used to add a group of disks.
ALTER DISKGROUP DG1 ADD DISK '//./ORCLDISKDATA*';

------------------------------
-- ASM DG Compatability
------------------------------
-- * Determines which Oracle version can run the ASM or work with him.
-- * Determined at Disk Group level.
-- * Two types:
--    1. RDBMS Compatibility (Compatible.RDBMS)
--       - Refers to the regular instances which work with the ASM.
--       - The compatability version of the instance must be equal or higher.
--       - Determines the format of the massages between the ASM and the regular instance.
--       - ASM can operate with instances of different compatability versions.
--    2. ASM Compatability (Compatible.ASM)
--       - The compatability version of the ASM instance (ASM Metadata).
--       - Must be greater or equal to the RDBMS comp. of the FG.

------------------------------
-- ASM DG Attributes
------------------------------
-- * We can specify DG attributes when creating and altering the DG in the ATTRIBUTE clause.
--   - AU_SIZE (C)           - Allocation Unit size (1MB ,2, 4, 8, 16, 32, 64MB)
--   - COMPATIBLE.RDBMS (AC) - Minimal Oracle version for the regular instances using the DG.
--   - COMPATIBLE.ASM   (AC) - Minimal Oracle version for the ASM instance mounting the DG.
--   - DISK_REPAIR_TIME (AC) - Length of time before removing a disk once OFFLINE
--   - TEMPLATE.t-name.REDUNDANCY (A) - Redundancy level of a specifid template.
--                                      (UNPROTECTED / MIRROR / HIGH)
--   - TEMPLATE.t-name.STRIPE (A) - Striping attribute of specified template. (COARSE / FINE)

ALTER DISKGROUP DG2
ATTRIBUTE 'compatible.asm'='11.1';

-- * We can view all attributes of the DGs in the V$ASM_ATTRIBUTE view.
-- * Via the OEM: Create/Edit DG => Advanced Attributes
col name for a15
col value for a10
SELECT group_number DG#, name, value
FROM v$asm_attribute;

--------------------------------
-- ASM Fast Mirror Resync
--------------------------------
-- * Reduces the time required to resynchronize failed disk data.
-- * When a disk goes offline due to a failure, his mirrored extents are still available.
-- * ASM tracks the extents that were modified during the outage using a bitmap
--   (Bit for each extent), and resynchronizes only these extents when the disk is back online.
--
-- * When a disk becomes unavailable due to a failure, ASM places him OFFLINE automatically.
-- * DISK_REPAIR_TIME - Determines the length of time before removing a disk once OFFLINE.
--      (Length of outage which still enables auto-recovery of the disk).
-- * To manually place a DG or FG Offline/Online via the OEM (For maintenance):
--     - ASM Management => Click the DG usage pie-chart => View by disk/FG => Offline
--     - We can override the default value of DISK_REPAIR_TIME here.

--------------------------------------------
-- Bad block repair (11g)
--------------------------------------------
-- * Performed automatically when in Normal/High Redundancy.
-- * When a block access fails, ASM recovers it automatically from its mirror.
-- * If recovery to the original location fails, its recovered to an alternative location.
-- * Can perform manually from ASMCMD using repair/remap.

--------------------------------------------
-- Miscellaneous ALTER Commands
--------------------------------------------
-- Remove a disk from a DG:
ALTER DISKGROUP dgroupA DROP DISK A5;

-- Removing and adding disks in a single statement (Efficient for rebalancing):
ALTER DISKGROUP dgroupA
 DROP DISK A6
 ADD FAILGROUP fred
         DISK '/dev/rdsk/c0t8d0s2' NAME A9;

-- Cancel a disk drop operation (Only if the drop operationis not yet completed)
ALTER DISKGROUP dgroupA UNDROP DISKS;

-- Manually rebalance a DG:
--   - Useful to change the default Power level.
--       (Also for a rebalace that is already active in the background)
--   - Setting the Power level to 0, stop the rebalancing.
ALTER DISKGROUP dgroupB REBALANCE POWER 5;

-- Mount/Dismount a DG:
--   - Makes it unavailable to the DB.
ALTER DISKGROUP DG1 DISMOUNT;
ALTER DISKGROUP DG1 MOUNT;
ALTER DISKGROUP ALL DISMOUNT;

--------------------------------------------
-- ASMCMD
--------------------------------------------
-- * Manage ASM from the command prompt.
-- * Works with ASM Files, Directories & Aliases.
-- * Like other file systems, ASM also has directories which contain the ASM Files.
-- * Each file has a "Fully Qualified File Name", which is the path to it:
--     - The + sign represents the Root directory of the Disk Group:
ASMCMD> ls -l +DGROUP1/ORCL/DATAFILE

-- * mkdir - Add new directories:
ASMCMD> mkdir +dgroup1/sample/mydir

-- * md_backup / md_restore - backup and restore ASM Metadata:
--    - This enables us to recreate a DG with the same directories and templates (attributes):
ASMCMD> md_backup –b /tmp/dgbackup070222 –g admdsk1 –g asmdsk2
ASMCMD> md_restore –t full –g asmdsk1 –i backup_file

-- * lsdsk - Displays ASM disk information.
--     - When connected - uses V$/GV$ tables.
--     - When not-connected - scans the disk headers to get the information.
--         (can use a disk string to limit the discovery set)

--------------------------------------------
-- ASM Scalability and Performance
--------------------------------------------

--------------------------------------------
-- ASM Directories
--------------------------------------------
-- * A directory heirarchy can be defined using the ALTER DISKGROUP statement
--     to support ASM file aliasing:

-- Create a directory
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/my_dir';
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/emps';

-- Rename a directory.
ALTER DISKGROUP DG1 RENAME DIRECTORY '+DG1/emps' TO '+DG1/employees';

-- Delete a directory and all its contents.
ALTER DISKGROUP DG1 DROP DIRECTORY '+DG1/employees';

--------------------------------------------
-- Aliases
--------------------------------------------

-- * Aliases allow you to reference ASM files using user-friendly names,
--     rather than the fully qualified ASM filenames.
-- *
SELECT name FROM v$datafile;

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1/mydb/datafile/asm_tbs.256.718138495';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1.256.718138495';

-- Rename an alias.
ALTER DISKGROUP DG1 RENAME ALIAS '+DG1/my_dir/my_file.dbf'
  TO '+DG1/my_dir/my_file2.dbf';


-- Delete an alias.
ALTER DISKGROUP DG1 DROP ALIAS '+DG1/my_dir/my_file2.dbf';

Attempting to drop a system alias results in an error

--------------------------------------------
-- Create an ASM-based tablespace
--------------------------------------------

conn /@orcl as sysdba
CREATE TABLESPACE ASM_TBS DATAFILE '+DG1/asm_tbs01.dbf' size 10m;
ALTER TABLESPACE ASM_TBS ADD DATAFILE '+DG1' size 10m;

SELECT name FROM v$datafile;

--------------------------------------------
-- ASM Intallation Guidelines
--------------------------------------------

-- 1. Using Third-Party tools such as EASEUS Partition Master 8.0.1 Home Edition
--    Create Unallocated space on your hard drive
-- 2. Create free space out of the anallocated space
-- 3. Create at least 2 raw devices (without any file-system)
-- 4. download Oracle Grid Infrastructure from oracle.com
-- 5. Install Oracle Grid Infrastructure must be installed before Oracle Database
--    NOTE : Oracle Grid Infrastructure must be installed before Oracle Database
-- 6. Install Oracle Database


-- * Networking (tnsnames / listener / sqlnet) should be configured in grid_home
-- TNSNAMES.ORA Should look something like this:
/*
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )
*/

-- Listener should look something like this :
/*

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = +ASM)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\grid)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    )
  )

*/


------------------------------------------------
DEMO!
------------------------------------------------

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 21 08:04:08 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

@> conn /@ASM AS SYSASM
Connected.
SYS@ASM>
SYS@ASM> -- See current Disk Groups:
SYS@ASM> SELECT GROUP_NUMBER, NAME, TOTAL_MB
  2  FROM v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           1 DG1                                  9998

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DG#=0 means that specific path does not have any Disk Group
SYS@ASM>
SYS@ASM> -- Join:
SYS@ASM> SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
  2  FROM v$asm_diskgroup dg JOIN v$asm_disk d
  3  USING (GROUP_NUMBER)
  4  GROUP BY GROUP_NUMBER, dg.name ;

GROUP_NUMBER NAME                                 Size      Disks
------------ ------------------------------ ---------- ----------
           1 DG1                                  9998          2

SYS@ASM>
SYS@ASM> -- Create a new disk group DG2:
SYS@ASM> CREATE DISKGROUP DG2
  2  DISK
  3  '\\.\ORCLDISK_LABEL_G' NAME DG2_0,
  4  '\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;

Diskgroup created.

SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         2 \\.\ORCLDISK_LABEL_G
         2 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DROP DISKGROUP DG2;
SYS@ASM>
SYS@ASM> -------------------------------------
SYS@ASM> -- Create a tablespace with ASM file
SYS@ASM> -- (Performed in the DB instance)
SYS@ASM> -------------------------------------
SYS@ASM>
SYS@ASM> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl>
SYS@orcl> CREATE TABLESPACE ASM_TBS
  2    DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

Tablespace created.

SYS@orcl> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
+DG2/asm_tbs01.dbf

6 rows selected.

SYS@orcl>
SYS@orcl> --   DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;
SYS@orcl>
SYS@orcl>
SYS@orcl> -- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
SYS@orcl> --   $oracle_grid_home\BIN\asmca.bat
SYS@orcl> -- * for stamping and name changing :
SYS@orcl> --   SHUTDOWN DB => SHUTDOWN ASM => stamp/change names => STARTUP ASM => STARTUP DB
SYS@orcl> -- * SHUTDOWN ASM => stamp/change names => STARTUP ASM
SYS@orcl>

SYS@orcl> -------------------------
SYS@orcl> -- ASM Templates
SYS@orcl> -------------------------
SYS@orcl>
SYS@orcl> -- * A template is a collection of file attributes that are applied to the ASM files.
SYS@orcl> --   It is associated with a Disk Group.
SYS@orcl> -- * Whenever a file is created the file attributes specific to the template are applied to
SYS@orcl> --   the files.
SYS@orcl> -- * For eg., if we want to create a users tablespace that contains data file we use the
SYS@orcl> --   DATAFILE
SYS@orcl> --   template.
SYS@orcl> -- * Default Template: When you create a disk group, Oracle creates system default template
SYS@orcl> --   for the disk group.
SYS@orcl>

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 PARAMETERFILE                  COARSE MIRROR
           1 ASMPARAMETERFILE               COARSE MIRROR
           1 ASMPARAMETERBAKFILE            COARSE MIRROR
           1 DUMPSET                        COARSE MIRROR
           1 CONTROLFILE                    FINE   HIGH
           1 FLASHFILE                      COARSE MIRROR
           1 ARCHIVELOG                     COARSE MIRROR
           1 ONLINELOG                      COARSE MIRROR
           1 DATAFILE                       COARSE MIRROR
           1 TEMPFILE                       COARSE MIRROR
           1 BACKUPSET                      COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 AUTOBACKUP                     COARSE MIRROR
           1 XTRANSPORT                     COARSE MIRROR
           1 CHANGETRACKING                 COARSE MIRROR
           1 FLASHBACK                      COARSE MIRROR
           1 DATAGUARDCONFIG                COARSE MIRROR
           1 OCRFILE                        COARSE MIRROR
           1 OCRBACKUP                      COARSE MIRROR
           1 ASM_STALE                      COARSE HIGH
           2 PARAMETERFILE                  COARSE MIRROR
           2 ASMPARAMETERFILE               COARSE MIRROR
           2 ASMPARAMETERBAKFILE            COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 DUMPSET                        COARSE MIRROR
           2 CONTROLFILE                    FINE   HIGH
           2 FLASHFILE                      COARSE MIRROR
           2 ARCHIVELOG                     COARSE MIRROR
           2 ONLINELOG                      COARSE MIRROR
           2 DATAFILE                       COARSE MIRROR
           2 TEMPFILE                       COARSE MIRROR
           2 BACKUPSET                      COARSE MIRROR
           2 AUTOBACKUP                     COARSE MIRROR
           2 XTRANSPORT                     COARSE MIRROR
           2 CHANGETRACKING                 COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 FLASHBACK                      COARSE MIRROR
           2 DATAGUARDCONFIG                COARSE MIRROR
           2 OCRFILE                        COARSE MIRROR
           2 OCRBACKUP                      COARSE MIRROR
           2 ASM_STALE                      COARSE HIGH

38 rows selected.

SYS@orcl>

SYS@orcl> ------------------------
SYS@orcl> -- ASM Striping
SYS@orcl> ------------------------
SYS@orcl>
SYS@orcl> -- * ASM stripes the file to AUs, and devides them between the DG disks.
SYS@orcl> -- * Two types of striping:
SYS@orcl> --      1. Coarse-based striping:
SYS@orcl> --          - Each stripe size is one AU.
SYS@orcl> --          - Classic striping. Suitable for most file types.
SYS@orcl> --          - Used for load balancing.
SYS@orcl> --      2. Fine-Grained striping
SYS@orcl> --            - Each stripe size is 128KB. (Wider distribution of data)
SYS@orcl> --            - Used for decreasing IO Latency (Waiting for IO).
SYS@orcl> --            - Suitable for small IO operations such as Redo Log Files.
SYS@orcl>
SYS@orcl> -- ONLINELOG
SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
  2  ATTRIBUTES (FINE);

Diskgroup altered.

SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
  2  ATTRIBUTES (COARSE);

Diskgroup altered.

  1  Select * From V$ASM_TEMPLATE
  2  WHERE name IN ('DATAFILE', 'ONLINELOG')
  3* AND group_number = 2
SYS@orcl> /

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM MIRR
------------ ------------ ------ ------ - ------------------------------ ---- ----
           2            7 MIRROR FINE   Y ONLINELOG                      COLD COLD
           2            8 MIRROR COARSE Y DATAFILE                       COLD COLD

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE
  3  WHERE name IN ('DATAFILE', 'ONLINELOG')
  4  AND group_number = 2 ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 ONLINELOG                      FINE   MIRROR
           2 DATAFILE                       COARSE MIRROR

SYS@orcl>
SYS@orcl> CONN /@ASM AS SYSASM
SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

---------------------------------------------------------------------------------------------
-- ASM Mirroring & Failure Groups
---------------------------------------------------------------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk, a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group, which could fail at the same time because they share hardware. (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

-- A protected disk group:

SYS@ASM>   CREATE DISKGROUP DG2
  1        NORMAL REDUNDANCY
  2        DISK
  3        '\\.\ORCLDISK_LABEL_G',
  4        '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   DG2_0000
         2          1 DG2_0001   DG2_0001
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001


SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

Diskgroup dropped.


SYS@ASM> CREATE DISKGROUP DG2
  2    NORMAL REDUNDANCY
  3    FAILGROUP controller1 DISK
  4   '\\.\ORCLDISK_LABEL_G'
  5    FAILGROUP controller2 DISK
  6    '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   CONTROLLER1
         2          1 DG2_0001   CONTROLLER2
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

1 comment: