How to Configure ASM Diskgroup with SQL Commands

Information about How To Configure ASM Diskgroup with SQL Commands

This document will explain how to Configure ASM Diskgroups through sqlplus

Document Creator

Todd Walters 

 How to  Configure ASM Diskgroups with SQL Commands

This next step will be passed off to the DBA’s to complete.

1. Switch User to Oracle and set SID to +ASM1
           ssh csrv0dbx01
           su - oracle
   . oraenv
   ORACLE_SID = ? +ASM1
   Sqlplus sys as sysdba
   Enter password:
    
2. select * from v$asm_disk
   ORCL:F_15960030
   ORCL:G_15960010
   ORCL:H_15960021
   ORCL:I_15960041
   ORCL:J_15960050
3. Now we will create PWAKDB_DATA_01
   select label,path,total_mb,header_status from v$asm_disk ORDER by TOTAL_MB,LABEL;
   
   create diskgroup PWAKDB_DATA_01 EXTERNAL REDUNDANCY Disk ‘ORCL:F_15960030‘;
   select * FROM V$ASM_DISKGROUP
   alter diskgroup PWAKDB_DATA_01 add disk ‘ORCL:G_15960010’;
   alter diskgroup PWAKDB_DATA_01 add disk ‘ORCL:H_15960021’;
   alter diskgroup PWAKDB_DATA_01 add disk ‘ORCL:I_15960041’;
    
4. SQL>  select name,total_mb from v$asm_diskgroup;
   NAME                             TOTAL_MB
   ------------------------------ ----------
   PWAKDB_DATA_01                     272788
   PWAKDB_BACKUP_01                    20479
    
5. select label,path,total_mb,header_status from v$asm_disk ORDER by 3,1;
6. Create backup diskgroup for Archive Logs
7. create diskgroup PWAKDB_BACKUP_01 EXTERNAL REDUNDANCY Disk ‘ORCL:J_15960050‘;
8. Mount ASM diskgroup on each additional node:
           alter diskgroup PWAKDB_DATA_01 mount;
           alter diskgroup PWAKDB_DATA_01 mount;
9. Add diskgroups to init.ora file:
           cd /u00/app/oracle/admin/+ASM/pfile
           vi init.ora file and add the following:
    
           asm_diskgroups='PWAKDB_DATA_01','PWAKDB_BACKUP_01'
           save and exit. Do this on all nodes.
   
    
10. Diskgroups can be deleted using drop diskgroup statement

•  ALTER DISKGROUP ALL DISMOUNT;  (On all Nodes but one)
• SQL> drop diskgroup dsandb_data_01 including contents;

Atlanta shared commands

ALTER DISKGROUP DATA DROP DISK DATA_0002
alter diskgroup <Diskgroup_name> dismount on all nodes;
drop diskgroup <Diskgroup_name>;

select 'alter diskgroup DOIC_DBF_DG add disk ''' || path || ''';'

from v$asm_disk

where path like 'ORCL:%' and header_status <>'MEMBER' and rownum <= &Number_Disks

create diskgroup TERPTRN_DBF_DG EXTERNAL REDUNDANCY Disk 'ORCL:AD_05700107';

alter diskgroup PERP_DBF_DG add disk 'ORCL:XN_046903D5';
alter diskgroup PERP_DBF_DG add disk 'ORCL:XD_046903E5';
alter diskgroup PERP_DBF_DG mount; on each additional node