Oracle ASM: CREATE and Manage Tablespaces and Datafiles on ASM

CREATE and Manage Tablespaces and Datafiles on ASM

1.       Create New Tablespace on ASM Diskgroup

CREATE TABLESPACE TEST_TBS DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON;

2.       Add new Datafile in Tablespace on ASM Diskgroup

ALTER TABLESPACE TEST_TBS ADD DATAFILE '+DATA' SIZE 100M;

3.       Alter Datafile in Tablespace in ASM

ALTER DATABASE DATAFILE '+DATA/rpst12/datafile/qnamicrmd.259.668957419' RESIZE 150M;

4.       Create new Diskgroup with Redundancy

create diskgroup DATADG external redundancy disk '/dev/mapper/mpathab1'

ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';

5.       Dynamic SQL to add newly added disks into Diskgroup

select 'alter diskgroup DATA1 add disk ''' || PATH || ''';' from v$asm_disk

where GROUP_NUMBER=0 and rownum<=&Num_Disks_to_add;

6.       Dynamic SQL to add newly added disks into Diskgroup

select 'alter diskgroup DATA add disk ''' || PATH || ''';' from v$asm_disk

where GROUP_NUMBER=0 and rownum<=&Num_Disks_to_add;

7.       Remove ASM header

select 'dd if=/dev/zero of=''' ||PATH||''' bs=8192 count=100' from v$asm_disk

where GROUP_NUMBER=0;

8.       Sql to find candidate disks

SELECT

    NVL(a.name, '[CANDIDATE]')      disk_group_name

  , b.path                          disk_file_path

  , b.name                          disk_file_name

  , b.failgroup                     disk_file_fail_group

FROM

    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY

    a.name;