===============================================
CREATE
===============================================
---CREATE DISKGROUP
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
---create tablespace
CREATE TABLESPACE DATA1 DATAFILE '+' SIZE 100M AUTOEXTEND ON;
•NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
•HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
•EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.
-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
SQL> alter diskgroup DATA1 add directory '+DATA1/oradata/orcl;
#Now create the alias
SQL> alter diskgroup DATA1 add alias '+DATA1/oradata/orcl/nitin01.dbf' for
+DATA1/orcl/datafile/nitin.263.3;
-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';
-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';
-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;
==============================================
DROP
==============================================
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;
-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';
-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';
-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';
==============================================
RESIZE
==============================================
-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;
-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;
-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;
ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;
-- Check metadata for a specific file.
ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'
-- Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;
==============================================================================
MIGRATION TO ASM USING RMAN
===============================================================================
1> Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;•Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
2>•Modify the parameter file of the target database as follows:
set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically.
If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
3>•Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
4>Restore the controlfile into the new location from the old location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
5>Mount the database.
RMAN> ALTER DATABASE MOUNT;
6>Copy the database into the ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
7>Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
8>Open the database.
RMAN> ALTER DATABASE OPEN;
9>Create new redo logs in ASM and delete the old ones.
10>Enable change tracking if it was being used.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
=================================================================
11g NEW FEATURE
=================================================================
Oracle 11g ASM includes two new compatibility attributes that determine the version of the ASM
and database software that can use specific disk groups:
--------------------------------------------------------------------
The compatibility versions of a disk group can only be */increased/*
•COMPATIBLE.ASM - The minimum version of the ASM software that can access the disk group.
In 11g, the default setting is 10.1.
•COMPATIBLE.RDBMS - The minimum COMPATIBLE database initialization parameter setting for any database
instance that uses the disk group. In 11g, the default setting is 10.1.
CREATE DISKGROUP DATA1 DISK '/dev/raw/*'
ATTRIBUTE 'compatible.asm' = '11.1';
CREATE DISKGROUP DATA1 DISK '/dev/raw/*'
ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
ALTER DISKGROUP DATA1 SET ATTRIBUTE 'compatible.asm' = '11.1';
ALTER DISKGROUP DATA1 SET ATTRIBUTE 'compatible.rdbms' = '11.1';
---------------------------------------------------------------------
Fast Mirror Resync
ASM keeps track of the changed extents that need to be applied to the offline disk.
*/Once the disk is available, only the changed extents are written to resynchronize the disk, /*
rather than overwriting the contents of the entire disk.
This can speed up the resynchronization process considerably
-- Set using the hours unit of time.
ALTER DISKGROUP disk_group_1 SET ATTRIBUTE 'disk_repair_time' = '4.5h';
-- Set using the minutes unit of time.
ALTER DISKGROUP disk_group_1 SET ATTRIBUTE 'disk_repair_time' = '300m';
---------------------------------------------------------------------
ROLLING MIGRATION
ALTER SYSTEM START ROLLING MIGRATION TO 11.2.0.0.0;
-----CAN BE DONE ONLY
•Mount and dismount of the disk groups.
•Open, close, resize, and delete of database files.
•Access to local fixed views and fixed packages
-----CURRENT STATUS
SELECT SYS_CONTEXT('sys_cluster_properties', 'cluster_state') FROM dual;
If a disk goes offline during a rolling upgrade, the timer is not started until after the rolling upgrade is complete.
--------------------------------------------------------------------------------
UPGRADE
Clustered ASM instances for 11g onwards can be upgraded using a rolling upgrade.
The ASM cluster is placed in rolling upgrade mode by
issuing the following command from one of the nodes.
ALTER SYSTEM START ROLLING MIGRATION TO 11.2.0.0.0;
Once the cluster is in rolling upgrade mode each node in turn can be shutdown, upgraded and started.
The cluster runs in a mixed version environment until the upgrade is complete.
In this state, the cluster is limited to the following operations:
•Mount and dismount of the disk groups.
•Open, close, resize, and delete of database files.
•Access to local fixed views and fixed packages.
The current status of the ASM cluster can be determined using the following query.
SELECT SYS_CONTEXT('sys_cluster_properties', 'cluster_state') FROM dual;
Once the last node is upgraded, the rolling upgrade is stopped by issuing the following command,
which checks all ASM instances are at the appropriate version,
turns off rolling upgrade mode and restarts any pending rebalance operations.
ALTER SYSTEM STOP ROLLING MIGRATION;
Restrictions and miscellaneous points about the rolling upgrade process include:
•The Oracle clusterware must be fully patched before an ASM rolling upgrade is started.
•Rolling upgrades are only available from 11g onwards,
so this method is not suitable for 10g to 11g upgrades.
•This method can be used to rollback to the previous version if the rolling upgrade fails before completion.
•If the upgrade fails, any rebalancing operations must complete before a new upgrade can be attempted.
•New instances joining the cluster during a rolling upgrade are automatically placed in rolling upgrade mode.
•If all instances in a cluster are stopped during a rolling upgrade,
once the instances restart they will no longer be in rolling upgrade mode. The upgrade must be initiated as if it were a new process.
The "ALTER DISKGROUP ... MOUNT" statement allows disk groups to be mounted in restricted mode.
======================================================================
ALLOCATION_UNIT AU_SIZE
======================================================================
When the disk group compatibility attributes are set to 11.1 or higher,
the extent size will automatically grow as the file grows.
The first 20,000 extents match the allocation unit size (1*AU).
The next 20,000 extents are made up of 8 allocation units (8*AU).
Beyond that point, the extent size becomes 64 allocation units (64*AU).
CREATE DISKGROUP disk_group_2
EXTERNAL REDUNDANCY
DISK '/dev/sde1'
ATRRIBUTE 'au_size' = '32M';
----------------------------------------------------------------------------
SYSASM Privilege and OSASM OS Group
$ sqlplus / as sysasm
---------------------------END OF 11G FEATURE--------------------------------
=============================================================================
V$VIEWS
=============================================================================
View ASM Instance DB Instance
V$ASM_ALIAS Displays a row for each alias present in every disk group mounted by the ASM instance.
Returns no rows
V$ASM_CLIENT Displays a row for each database instance using a disk group managed by the ASM instance.
Displays a row for the ASM instance if the database has open ASM files.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
Displays a row for each disk in disk groups in use by the database instance.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM instance. Displays a row for each disk group mounted by the local ASM instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the ASM instance.
Displays no rows.
V$ASM_OPERATION Displays a row for each file for each long running operation executing in the ASM instance. Displays no rows.
V$ASM_TEMPLATE Displays a row for each template present in each disk group mounted by the ASM instance. Displays a row for each template present in each disk group mounted by the ASM instance with which the database instance communicates.
select name, state, type, total_mb, free_mb from v$asm_diskgroup;
NAME STATE TYPE TOTAL_MB FREE_MB
------------------------------ ----------- ------ ---------- ----------
DATA1 MOUNTED EXTERN 34512 34101
select name, path, mode_status, state, disk_number from v$asm_disk
NAME PATH MODE_ST STATE DISK_NUMBER
------------ ---------------------- ------- -------- -----------
DATA_0000 /dev/rdsk/c3t19d5s4 ONLINE NORMAL 0
DATA_0001 /dev/rdsk/c3t19d16s4 ONLINE NORMAL 1
DATA_0002 /dev/rdsk/c3t19d17s4 ONLINE NORMAL 2
DATA_0003 /dev/rdsk/c3t19d18s4 ONLINE NORMAL 3
SQL> select INSTANCE,NAME,STATUS,SOFTWARE_VERSION,COMPATIBLE_VERSION from v$asm_client;
------- ------------ ------------ ------------
cubs1 CONNECTED 10.2.0.1.0 10.2.0.1.0
FROM +ASM (ASM instance)
select file_number , sum(bytes)/(1024*1024) from v$asm_file group by
file_number
FROM ORCL (database instance)
SQL> select name from v$datafile
NAME
----------------------------------------
+DATA1/orcl/datafile/sysaux.256.3
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------------------------------
-
+DATA1/orcl/onlinelog/group_3.264.3
To show the hierarchical tree of files stored in the diskgroup
SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;
==============================================================
ASMCMD
==============================================================
he lsdsk command lists information about ASM disks from the V$ASM_DISK_STAT and V$ASM_DISK views
lsdsk [-ksptcgHI] [-d ] [pattern]
cp [-ifr] <[\@connect_identifier:]src> <[\@connect_identifier:]tgt>
md_restore -b [-li]
[-t (full)|nodg|newdg] [-f ]
[-g ',,...']
[-o ':,...']
lsdg dgroup2 Lists all disk groups and their attributes.
lsct dgroup1 LIST ABOUT CLIENTS ACCESS
lsdsk -kspt '/dev/sdb6'
===============================================================
INIT+ASM.ORA
===============================================================
SQL> select instance_name from v$instance
INSTANCE_NAME
----------------
+ASM
ASM init.ora parameters
*.background_dump_dest='/opt/app/admin/+ASM/bdump'
*.core_dump_dest='/opt/app/admin/+ASM/cdump'
*.instance_type=asm
*.asm_diskgroups=+DATA1
*.large_pool_size=12M
*.asm_diskstring='/dev/rdsk/c3t19d*s4'
*.remote_login_passwordfile='SHARED'
*.user_dump_dest='/opt/app/admin/+ASM/udump'
==================================================================
KNOWLEDGEBASE
==================================================================
ASMLIB
Oracle has developed a storage management interface called the ASMLIB API. ASMLIB is not required to
run ASM; it is simply an add-on module that simplifies the management and discovery of ASM disks. The
ASMLIB provides an alternative, to the standard operating system interface, for ASM to identify and
access block devices. The ASMLIB API provides storage and operating system vendors the opportunity to
supply extended storage-related features and exploit the capabilities and strengths of vendors‘ storage
array.
The ASMLIB API provides two major feature enhancements over standard interfaces:
• Disk discovery | Providing more information about the storage attributes to the Database and the DBA
• I/O processing | To enable more efficient I/O
Oracle is providing an ASMLIB, called the Oracle ASMLIB, for the Linux platform (current version as of
this writing is 2.0).
