/*
|| Oracle 10g ASM Listing 3
||
|| Demonstrates various features of Oracle 10g's Automatic Storage
|| Manager (ASM), including:
|| - Managing ASM file names, templates and aliases
|| - Managing ASM disks and ASM disk groups
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle
|| 10g's new ASM features and should be carefully proofread before
|| executing it against any existing Oracle database to insure that
|| no potential damage can occur.
||
*/

SET PAGESIZE 50
SET LINESIZE 100

----- 
-- Listing 3.1: Maintaining ASM templates 
-----

-- Adding a new template to an existing ASM disk group
ALTER DISKGROUP asm1dg1
    ADD TEMPLATE doppleganger
    ATTRIBUTES (MIRROR);

-- Show all non-system ASM file templates
TTITLE 'Non-System ASM Templates (From V$ASM_TEMPLATE)'
COL group_number        FORMAT 99999    HEADING 'ASM|Disk|Grp #' 
COL entry_number        FORMAT 99999    HEADING 'ASM|Entry|#'
COL redundancy          FORMAT A06      HEADING 'Redun-|dancy'
COL stripe              FORMAT A06      HEADING 'Stripe'
COL system              FORMAT A03      HEADING 'Sys|?'
COL name                FORMAT A30      HEADING 'ASM Template Name' WRAP
SELECT
     group_number
    ,entry_number
    ,redundancy
    ,stripe
    ,system
    ,name
  FROM v$asm_template
 WHERE system <> 'Y'
;

>>> Results:

Tue Jan 17                                                       page    1
             Non-System ASM Templates (From V$ASM_TEMPLATE)
 
   ASM    ASM
  Disk  Entry Redun-        Sys
 Grp #      # dancy  Stripe ?   ASM Template Name
------ ------ ------ ------ --- ------------------------------
     1     13 MIRROR COARSE N   DOPPLEGANGER
 

-- Dropping an existing template from an existing ASM disk group
ALTER DISKGROUP asm1dg1 
    DROP TEMPLATE doppleganger;
    
----- 
-- Listing 3.2: Maintaining ASM file aliases
-----

-- Adding a new directory
ALTER DISKGROUP asm1dg1    
    ADD DIRECTORY '+asm1dg1/dbfs';

-- Adding a new alias for an existing datafile
ALTER DISKGROUP asm1dg1      
    ADD ALIAS '+asm1dg1/dbfs/tbsasm.dbf'     
    FOR '+asm1dg1.257.1';    

-----
-- Show all ASM file aliases
-----
TTITLE 'ASM Disk Group Aliases (From V$ASM_ALIAS)'
COL name                FORMAT A32              HEADING 'Disk Group Alias' WRAP
COL group_number        FORMAT 9999999999       HEADING 'ASM|File #' 
COL file_number         FORMAT 9999999999       HEADING 'File #'
COL file_incarnation    FORMAT 9999999999       HEADING 'ASM|File|Inc#'
COL alias_index         FORMAT 9999999999       HEADING 'Alias|Index'
COL alias_incarnation   FORMAT 999999           HEADING 'Alias|Incn#'
COL alias_directory     FORMAT A4               HEADING 'Ali|Dir?'
COL system_created      FORMAT A4               HEADING 'Sys|Crt?'
SELECT
     name
    ,group_number
    ,file_number
    ,file_incarnation
    ,alias_index
    ,alias_incarnation
    ,alias_directory
    ,system_created
  FROM v$asm_alias
;

>>> Results:


Tue Jan 17                                                        page    1
                  ASM Disk Group Aliases (From V$ASM_ALIAS)
 
                                                                 ASM
                  ASM                 File        Alias   Alias  Ali  Sys
Disk Group Alias  File #  File #      Inc#        Index   Incn#  Dir? Crt?
----------------- ------- ----------- ----------- ------- ------ ---- ----
ORCL                    1  4294967295  4294967295       0      3 Y    Y
DATAFILE                1  4294967295  4294967295      53      3 Y    Y
TBS_ASM1.256.3          1         256           3     106      3 N    Y
TBSASM1.257.1           1         257           1     107      1 N    Y
dbfs                    1  4294967295  4294967295       2      1 Y    N
tbsasm.dbf              1         257           1     212      1 N    N

-- Dropping an existing alias
ALTER DISKGROUP asm1dg1      
    DROP ALIAS '+asm1dg1/dbfs/tbsasm.dbf';      

-- Dropping an existing directory
ALTER DISKGROUP asm1dg1      
    DROP DIRECTORY '+asm1dg1/dbfs';

----- 
-- Listing 3.3: Creating a new ASM disk group.
-- 1.) The CONTROLLER directive tells ASM which common disk controller
--     controls the listed disks.
-- 2.) The FAILGROUP directive tells ASM into which failure group each
--     ASM disk should be placed.
-- 3.) The default redundancy characteristic is NORMAL REDUNDANCY.
-- 4.) Optional directives include disk names and disk sizes. If
--     neither is supplied, ASM assigns a default name will attempt to
--     determine the size of the disk. If the size cannot be determined
--     an error is returned.
-- 5.) The FORCE directive tells ASM that a specified disk should be
--     added to the specified disk group even if the disk is already
--     formatted as a member of another ASM disk group. Using the FORCE
--     option for a disk that's not formatted as a member of an ASM
--     disk group returns an error, and it may take a long time to
--     complete. The default (shown here) is NOFORCE.
-----
CREATE DISKGROUP asm1dg2
    NORMAL REDUNDANCY
    FAILGROUP ctlr3
    DISK 
        '/u03/asmdisks/disk4' NAME dbg2_1 NOFORCE
       ,'/u03/asmdisks/disk5' NAME dbg2_2 NOFORCE
    FAILGROUP ctlr4
    DISK 
        '/u03/asmdisks/disk6' NAME dbg2_3 NOFORCE
       ,'/u03/asmdisks/disk7' NAME dbg2_4 NOFORCE;

----- 
-- Listing 3.4: Adding new disks to an existing ASM disk group. Note
--              this will cause an immediate and implicit rebalancing
--              of all disks in this disk group!
-----

ALTER DISKGROUP asm1dg2
    ADD 
        FAILGROUP ctlr3 
        DISK '/u03/asmdisks/disk8' NAME dbg2_5 NOFORCE;
    
ALTER DISKGROUP asm1dg2
    ADD 
        FAILGROUP ctlr4 
        DISK '/u03/asmdisks/disk9' NAME dbg2_6 NOFORCE;
    
----- 
-- Listing 3.5: Resizing n ASM disk group. Note that resizing disks
--              invokes an implicit rebalancing operation!
-----

-- Resize all disks in a specific disk group
ALTER DISKGROUP asm1dg2 RESIZE ALL SIZE 32M;

-- Resize all disks in a specific failure group
ALTER DISKGROUP asm1dg2 RESIZE DISKS IN FAILGROUP CTLR4 SIZE 48M;

-- Resize specific all disks in a specific disk group
ALTER DISKGROUP asm1dg2 RESIZE DISK dbg2_1 SIZE 48M;
ALTER DISKGROUP asm1dg2 RESIZE DISK dbg2_2 SIZE 48M;

----- 
-- Listing 3.6: Validate existing disks in an existing disk group.
--              Note that this operation does +not+ cause an implicit
--              rebalancing operation!
-----

-- Check all disks in a specific disk group; don't repair them 
-- even if a problem is found
ALTER DISKGROUP asm1dg1 CHECK ALL NOREPAIR;

-- Check a specific disk in a specific disk group, and repair 
-- the disk if any repairs are needed
ALTER DISKGROUP asm1dg2 CHECK DISK dbg2_4;

----- 
-- Listing 3.7: Manually rebalancing an ASM disk group using 
--              a specific amount of resources. The maximum value 
--              available is 11.
-----

ALTER DISKGROUP asm1dg2 REBALANCE POWER 10;

----- 
-- Listing 3.8: Mounting and dismounting ASM disk groups
-----

-- Dismount a currently-mounted ASM disk group
ALTER DISKGROUP asm1dg2 DISMOUNT;

-- Mount an unmounted ASM disk group
ALTER DISKGROUP asm1dg2 MOUNT;

----- 
-- Listing 3.9: Removing disks from an existing ASM disk group. 
--              Note that removing disks invokes an implicit 
--              rebalancing operation!
-----

-- Drop just one disk
ALTER DISKGROUP asm1dg2 
    DROP DISK dbg2_5;

-- Drop all disks in a specific failure group
ALTER DISKGROUP asm1dg2 
    DROP DISKS IN FAILGROUP ctlr4;

-- "Undrop" a recently-dropped disk
ALTER DISKGROUP asm1dg2 
    UNDROP DISK dbg2_5;


----- 
-- Listing 3.10: Dropping an existing ASM disk group. The INCLUDING 
--               CONTENTS directive tells ASM to drop all files in the 
--               disk group. This directive +must+ be specified if the 
--               disk group contains +any+ files.
-----

DROP DISKGROUP asm1dg2 INCLUDING CONTENTS;

----- 
-- Listing 3.11: RMAN command file for manually migrating an existing
--               database from "regular" to ASM storage.
-----
RUN{
BACKUP AS COPY DATABASE FORMAT '+asm1dg1';
SWITCH DATABASE TO COPY;
# Repeat command for all online redo log members ...
SQL "ALTER DATABASE RENAME '/u01/app/oracle/oradata/orcl/log1' TO '+asm1dg1'";
ALTER DATABASE OPEN RESETLOGS;
# Repeat command for all temporary tablespaces
SQL "ALTER TABLESPACE temp ADD TEMPFILE";
}