drop disk | Derya Oktay's Weblog

Add and drop disks concurrently to a diskgroup in Oracle ASM, as to gain hours by one time rebalance

October 18, 2011 deryaoktay Leave a comment

Adding a disk to a disk group needs rebalancing the data across newly formed diskgroup consisting newer disks. Dropping also needs similar rebalancing across diskgroup as you guess.

If you need to add and drop disks at the same time, then this trick is what you are looking for or bumped into.

As you could understood from below real world example SQL, the dgdata diskgroup is changed by adding 13 disks from new cabinet, and dropping 13 disks from old cabinet. And it worked smothly, thus we gained at least 15 hours from one time rebalancing instead of two times rebalancing. Also it worths noting that no intervention is needed, because you need to execute one SQL instead of 2.

-- Concurrently execute add/drop disk in order gain from rebalance time.

ALTER DISKGROUP DATADG
ADD DISK
    '/dev/ORACLE/ASMDISK/DATA7' NAME DATA_0007,
    '/dev/ORACLE/ASMDISK/DATA8' NAME DATA_0008,
    '/dev/ORACLE/ASMDISK/DATA9' NAME DATA_0009,
    '/dev/ORACLE/ASMDISK/DATA10' NAME DATA_0010,
    '/dev/ORACLE/ASMDISK/DATA11' NAME DATA_0011,
    '/dev/ORACLE/ASMDISK/DATA12' NAME DATA_0012,
    '/dev/ORACLE/ASMDISK/DATA13' NAME DATA_0013,
    '/dev/ORACLE/ASMDISK/DATA14' NAME DATA_0014,
    '/dev/ORACLE/ASMDISK/DATA15' NAME DATA_0015,
    '/dev/ORACLE/ASMDISK/DATA16' NAME DATA_0016,
    '/dev/ORACLE/ASMDISK/DATA17' NAME DATA_0017,
    '/dev/ORACLE/ASMDISK/DATA18' NAME DATA_0018,
    '/dev/ORACLE/ASMDISK/DATA19' NAME DATA_0019
DROP DISK
    DATADG_0000,
    DATADG_0001,
    DATADG_0002,
    DATADG_0003,
    DATADG_0004,
    DATADG_0005,
    DATADG_0006,
    DATADG_0007,
    DATADG_0008,
    DATADG_0010,
    DATADG_0011,
    DATADG_0012
REBALANCE POWER 5;

Categories: tips Tags: add disk, ASM, drop disk, optimization, rebalance

How to drop diskgroup giving ORA-15027

August 3, 2011 deryaoktay 1 comment

Problem

While removing all the contents of a database using ASM you may encounter error from acmca or sqlplus:

ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup “DATA” precludes its dismount

Cause

The reason behind is the parameter file residing in the ASM disk:

Solution

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME                           TYPE     TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
—————————— —— ———- ———- ———————– ————–
DATA                           EXTERN    7168000    7167858                       0        7167858
FRA                            EXTERN     512000     511222                       0         511222

SQL>

SQL> column HEADER_STATUS format a20
SQL> set pages 100
SQL> col PATH format a25

SQL> r
1* SELECT name, header_status, path FROM V$ASM_DISK

NAME                           HEADER_STATUS        PATH
—————————— ——————– ————————-
DATA_0004                      MEMBER               /dev/rdisk/disk38
DATA_0005                      MEMBER               /dev/rdisk/disk39
DATA_0006                      MEMBER               /dev/rdisk/disk40
DATA_0007                      MEMBER               /dev/rdisk/disk41
DATA_0008                      MEMBER               /dev/rdisk/disk42
DATA_0009                      MEMBER               /dev/rdisk/disk43
DATA_0010                      MEMBER               /dev/rdisk/disk44
DATA_0011                      MEMBER               /dev/rdisk/disk45
DATA_0012                      MEMBER               /dev/rdisk/disk46
DATA_0000                      MEMBER               /dev/rdisk/disk60
DATA_0001                      MEMBER               /dev/rdisk/disk61
DATA_0002                      MEMBER               /dev/rdisk/disk62
DATA_0003                      MEMBER               /dev/rdisk/disk63
DATA_0013                      MEMBER               /dev/rdisk/disk64
FRA_0000                       MEMBER               /dev/rdisk/disk65

15 rows selected.

SQL>
SQL>
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup “DATA” precludes its dismount

SQL>
SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;
DROP DISKGROUP data FORCE INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15230: diskgroup ‘DATA’ does not require the FORCE option

SQL> create pfile=’/tmp/init.ora’ from spfile;

File created.

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup pfile=’/tmp/init.ora’;
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size                  2169104 bytes
Variable Size             256595696 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup “DATA” does not exist or is not mounted

SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;

Diskgroup dropped.

SQL>

Categories: problem-cause-solution Tags: ASM, drop disk, ORA-15027

ORA-15032 Problem while dropping disks from disk group in (HPUX 11.31 ASM 11gR2 standalone db)

October 14, 2010 deryaoktay Leave a comment

ORA-15032 Problem while dropping disks from disk group in ASM.

PROBLEM:

SQL> ALTER DISKGROUP DATA DROP DISK DATA_0003;

Diskgroup altered.

SQL>

SQL> SELECT name, header_status, path FROM V$ASM_DISK

2 ;

NAME HEADER_STATU PATH

—————————— ———— ——————————

FORMER /dev/rdisk/disk14

DATA_0001 MEMBER /dev/rdisk/disk15

DATA_0002 MEMBER /dev/rdisk/disk16

DATA_0003 MEMBER /dev/rdisk/disk17

DATA_0004 MEMBER /dev/rdisk/disk38

DATA_0005 MEMBER /dev/rdisk/disk39

DATA_0006 MEMBER /dev/rdisk/disk40

DATA_0007 MEMBER /dev/rdisk/disk41

DATA_0008 MEMBER /dev/rdisk/disk42

DATA_0009 MEMBER /dev/rdisk/disk43

DATA_0010 MEMBER /dev/rdisk/disk44

DATA_0011 MEMBER /dev/rdisk/disk45

DATA_0012 MEMBER /dev/rdisk/disk46

DATA_0013 MEMBER /dev/rdisk/disk47

14 rows selected.

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

—————————— —— ———- ———- ———————– ————–

DATA EXTERN 5888000 5748724 0 5748724

SQL>

SQL> ALTER DISKGROUP DATA rebalance wait;

ALTER DISKGROUP DATA rebalance wait

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15001: diskgroup “DATA” does not exist or is not mounted

SQL> ALTER DISKGROUP DATA rebalance;

ALTER DISKGROUP DATA rebalance

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15001: diskgroup “DATA” does not exist or is not mounted

SQL>

CAUSE :

To be honest I cannot find the real cause, only the side effect. I tried running asmca, if it caused this, but cannnot reproduce the case. All I know is I had rebooted the OS, and then login to asmcmd, made some alterations, run asmca, and after a while I got this error.

trlogdb01:/dev/rdisk#ll

total 0

crw-r—– 1 bin sys 13 0x00000a Oct 13 16:48 disk14

crw-r—– 1 bin sys 13 0x00000b Oct 13 16:53 disk15

crw-r—– 1 bin sys 13 0x00000c Oct 13 16:53 disk16

crw-r—– 1 bin sys 13 0x00000d Oct 13 16:53 disk17

crw-r—– 1 bin sys 13 0x000000 May 13 12:44 disk3

crw-r—– 1 bin sys 13 0x00000f Oct 13 16:56 disk38

crw-r—– 1 bin sys 13 0x000010 Oct 13 16:56 disk39

crw-r—– 1 bin sys 13 0x000006 May 13 12:44 disk3_p1

crw-r—– 1 bin sys 13 0x000007 May 13 12:44 disk3_p2

crw-r—– 1 bin sys 13 0x000008 May 13 12:44 disk3_p3

crw-r—– 1 bin sys 13 0x000001 May 13 12:44 disk4

crw-r—– 1 bin sys 13 0x000011 Oct 13 16:56 disk40

crw-r—– 1 bin sys 13 0x000012 Oct 13 16:56 disk41

crw-r—– 1 bin sys 13 0x000013 Oct 13 16:53 disk42

crw-r—– 1 bin sys 13 0x000014 Oct 13 16:53 disk43

crw-r—– 1 bin sys 13 0x000015 Oct 13 16:53 disk44

crw-r—– 1 bin sys 13 0x000016 Oct 13 16:53 disk45

crw-r—– 1 bin sys 13 0x000017 Oct 13 16:53 disk46

crw-r—– 1 bin sys 13 0x000018 Oct 13 16:56 disk47

crw-r—– 1 bin sys 13 0x000002 May 13 12:44 disk5

crw-r—– 1 bin sys 13 0x000003 May 13 12:44 disk5_p1

crw-r—– 1 bin sys 13 0x000004 May 13 12:44 disk5_p2

crw-r—– 1 bin sys 13 0x000005 May 13 12:44 disk5_p3

SOLUTION:

trlogdb01:/dev/rdisk#chown oracle:dba disk14 disk15 disk16 disk17

…

trlogdb01:/dev/rdisk#ll

total 0

crw-r—– 1 oracle dba 13 0x00000a Oct 13 16:48 disk14

crw-r—– 1 oracle dba 13 0x00000b Oct 13 16:53 disk15

crw-r—– 1 oracle dba 13 0x00000c Oct 13 16:53 disk16

crw-r—– 1 oracle dba 13 0x00000d Oct 13 16:53 disk17

crw-r—– 1 bin sys 13 0x000000 May 13 12:44 disk3

crw-r—– 1 oracle dba 13 0x00000f Oct 13 16:56 disk38

crw-r—– 1 oracle dba 13 0x000010 Oct 13 16:56 disk39

crw-r—– 1 bin sys 13 0x000006 May 13 12:44 disk3_p1

crw-r—– 1 bin sys 13 0x000007 May 13 12:44 disk3_p2

crw-r—– 1 bin sys 13 0x000008 May 13 12:44 disk3_p3

crw-r—– 1 bin sys 13 0x000001 May 13 12:44 disk4

crw-r—– 1 oracle dba 13 0x000011 Oct 13 16:56 disk40

crw-r—– 1 oracle dba 13 0x000012 Oct 13 16:56 disk41

crw-r—– 1 oracle dba 13 0x000013 Oct 13 16:53 disk42

crw-r—– 1 oracle dba 13 0x000014 Oct 13 16:53 disk43

crw-r—– 1 oracle dba 13 0x000015 Oct 13 16:53 disk44

crw-r—– 1 oracle dba 13 0x000016 Oct 13 16:53 disk45

crw-r—– 1 oracle dba 13 0x000017 Oct 13 16:53 disk46

crw-r—– 1 oracle dba 13 0x000018 Oct 13 16:56 disk47

crw-r—– 1 bin sys 13 0x000002 May 13 12:44 disk5

crw-r—– 1 bin sys 13 0x000003 May 13 12:44 disk5_p1

crw-r—– 1 bin sys 13 0x000004 May 13 12:44 disk5_p2

crw-r—– 1 bin sys 13 0x000005 May 13 12:44 disk5_p3

Categories: problem-cause-solution Tags: drop disk, HPUX, ORA-15032

	dedicated smtp serve… on “Prerequisite check…
	Solaire of Astora on rename table
	deryaoktay on expdp with sys without knowing…
	james huang on Steps to migrate database mach…
	Vincent menard on expdp with sys without knowing…

Derya Oktay's Weblog

Archive

Add and drop disks concurrently to a diskgroup in Oracle ASM, as to gain hours by one time rebalance

How to drop diskgroup giving ORA-15027

ORA-15032 Problem while dropping disks from disk group in (HPUX 11.31 ASM 11gR2 standalone db)

Categories

Blogs I Follow

Recent Comments