Manage ASM - Some tests

Test with ASM
================

1. Login to the ASM instance

NTBK-/home/Denis/e_oracle/Practise_Oracle/asm_practice>export ORACLE_SID=+ASM
NTBK-/home/Denis/e_oracle/Practise_Oracle/asm_practice>sqlplus / as sysdba


2. Disk discovery
select name, header_status, path from v$asm_disk;

NAME                           HEADER_STATU PATH
------------------------------ ------------ ------------------------------
ORCL_DG1_0000                  MEMBER       E:\ASMDISKS\_FILE_DISK1
ORCL_DG1_0001                  MEMBER       E:\ASMDISKS\_FILE_DISK2
ORCL_DG1_0002                  MEMBER       E:\ASMDISKS\_FILE_DISK3
ORCL_DG1_0003                  MEMBER       E:\ASMDISKS\_FILE_DISK4

3.  Create a diskgroup

idle> ;
  1  CREATE DISKGROUP orcl_dg2 external REDUNDANCY
  2  DISK 'e:\asmdisks\_file_disk10',
  3*      'e:\asmdisks\_file_disk11'

idle> @disk_discover

NAME                           MOUNT_S HEADER_STATU STATE    PATH
------------------------------ ------- ------------ -------- ------------------------------
ORCL_DG1_0000                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK1
ORCL_DG2_0000                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK10
ORCL_DG1_0001                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK2
ORCL_DG1_0002                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK3
ORCL_DG1_0003                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK4
ORCL_DG2_0001                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK11


4. Add a disk

ALTER DISKGROUP orcl_dg2 ADD DISK
'e:\asmdisks\_file_disk12';

idle> @disk_discover.sql

NAME                           MOUNT_S HEADER_STATU STATE    PATH
------------------------------ ------- ------------ -------- -----------------------------
ORCL_DG1_0000                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK1
ORCL_DG1_0001                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK2
ORCL_DG1_0002                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK3
ORCL_DG1_0003                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK4
ORCL_DG2_0000                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK10
ORCL_DG2_0001                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK11
ORCL_DG2_0002                  CACHED  MEMBER       NORMAL   E:\ASMDISKS\_FILE_DISK12


5. Drop a disk

idle> select name, total_mb - free_mb  "USED_MB" from v$asm_disk order by 1;

NAME                              USED_MB
------------------------------ ----------
ORCL_DG1_0000                          82
ORCL_DG1_0001                          74
ORCL_DG1_0002                          79
ORCL_DG1_0003                          77
ORCL_DG2_0000                          18
ORCL_DG2_0001                          18
ORCL_DG2_0002                          18

7 rows selected.

idle> alter diskgroup orcl_dg2 drop disk orcl_dg2_0002;

Diskgroup altered.
idle> select name, total_mb - free_mb  "USED_MB" from v$asm_disk order by 1;

NAME                              USED_MB
------------------------------ ----------
ORCL_DG1_0000                          82
ORCL_DG1_0001                          74
ORCL_DG1_0002                          79
ORCL_DG1_0003                          77
ORCL_DG2_0000                          26
ORCL_DG2_0001                          26
                                      100

6. Resize a diskgroup

idle> select name, total_mb from v$asm_diskgroup;

NAME                             TOTAL_MB
------------------------------ ----------
ORCL_DG1                              396
ORCL_DG2                              200


idle> alter diskgroup orcl_dg2 resize all size 80M;

Diskgroup altered.

idle> select name, total_mb from v$asm_diskgroup;

NAME                             TOTAL_MB
------------------------------ ----------
ORCL_DG1                              396
ORCL_DG2                              160


7. Mount/Dismount ASM diskgroup

idle> select name, state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ORCL_DG1                       MOUNTED
ORCL_DG2                       MOUNTED


idle> alter diskgroup orcl_dg2 dismount;

Diskgroup altered.

idle> alter diskgroup orcl_dg1 dismount;
alter diskgroup orcl_dg1 dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "ORCL_DG1" precludes its dismount

Notes: If you try to dismount a disk group that contains open files,
       the statement will fail, unless you also specify the FORCE clause.

idle> select name, state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ORCL_DG1                       MOUNTED
ORCL_DG2                       DISMOUNTED

idle> alter diskgroup orcl_dg2 mount;



8. Observing rebalancing when add a disk

idle> select name, total_mb, free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
ORCL_DG1                              496        131
ORCL_DG2                              160        107

SQL> create tablespace asm_users datafile '+ORCL_DG2' size 100m;

idle> select name, total_mb, free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
ORCL_DG1                              496        131
ORCL_DG2                              160          5

idle>  select name, total_mb - free_mb  "USED_MB" from v$asm_disk order by 1;

NAME                              USED_MB
------------------------------ ----------
ORCL_DG2_0000                          78
ORCL_DG2_0001                          77


alter diskgroup orcl_dg2 add disk 'e:\asmdisks\_file_disk13';
alter diskgroup orcl_dg2 add disk 'e:\asmdisks\_file_disk14';

idle>  select name, total_mb - free_mb  "USED_MB" from v$asm_disk order by 1;

NAME                              USED_MB
------------------------------ ----------
ORCL_DG2_0000                          43
ORCL_DG2_0001                          44
ORCL_DG2_0002                          20
ORCL_DG2_0003                          52