/*
|| 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";
}