DATA MIGRATION - USING - ASM DISK MIGRATION

Submitted by jp_vijaykumar on Tue, 2022-01-04 11:30
articles: 
RDBMS Server

DATA MIGRATION - USING - ASM DISK MIGRATION
--------------------------------------------
Author: JP Vijaykumar
Date: Jan 4th 2022

This article/script is provided for educational purpose ONLY.
Please understand the scripts, test thoroughly before use. Please modify the scripts as may be required,
and use the scripts at your own discretion.
Pls note, the number of exceptions mentioned here below are more than the sql queries provieded herewith.
That said, pls read the exceptions carefully and use the scripts judiciously.

EXCEPTIONS:
-------------------------------------------------------------------------------------------------------------
01) This script assumes that currently existing ASM disks in the ASM instance are older by (sysdate -2).
If not, the user need to modify the where condition appropriately, to make the scripts fetch correct output.
02) This script uses the following replace filtering conditions to map the old luns' path with new luns' path.
regexp_replace(lower(m.path),'new|lun0|_0|ive|_|lun|log0|log','') =
regexp_replace(lower(c.path),'new|lun0|_0|ive|_|lun|log0|log','')
Where m.path is member disks' path and c.path is candidate disks' path.
If the naming convention is different from the above mentioned convention, the user assumes the responsibility
to modify the above condition inside the scripts to map the old luns' path with new luns' path in their respective environments.

There were some cases that these scripts did not work, due to non-standard naming conventions used in some projects.
In such scenarios, the only alternative is to map each old lun's path manually with its counter part(new lun)
and compile the scripts manually, which is a time consuming and labourious process.
03) I had intentionally created some scripts by prefixing comments('--') before commands in few procedures' output.
The user should understand the scripts' output, then uncomment('--') the required scripts before use.
04) I omitted the ";" at the end of each command.
The user should understand the scripts, then provide the ";" at end of each sql script before use.
05) I omitted the "/" at the end of pl/sql scripts/procedures.
The user should understand the pl/sql scripts thoroughly and then provide the "/" at end of each pl/sql script before use.
06) For the drop disk commands' I had displayed the old disk's path and create_date with comments, to provide extra details for verification
purpose, before dropping any disks from the ASM instance.
07) Adding new luns and dropping old luns from an ASM instance can be executed within the same sql statement.
As our project management required separate sql commands to be executed to accomplish the purpose, I seperated them.
08) These scripts are to be tested thoroughly on dev/lower environment, before using in any higher environment.
09) These scripts were tested/executed in Oracle 11g,12c extensively.
In other versions of Oracle, these scripts may not work. The user assumes the complete responsibility to modify these scripts
accordingly to suit their respective environments.
10) These scripts are only useful, if the migration is performed using ASM disk migration/rebalance operations.
If any other method of migration is being used(like Open Replicator method), then these scripts are of no use.
11) The rebalance power is set at "7" in the scripts. Which was recommended by project management for our migration environments.
The user need to change the rebalance power to a required limit as may be applicable in their respective environments.
12) If few disks are to be added or dropped, then these scripts may not be of much help.
I was adding few hundred new luns and dropping equal number of old luns on multiple ASM instances per week.
This heavy activity necessiated the creation of these scripts, which helped me in carrying out my task.
13) I had included the output from each sql query at different times of migration process.
The display of output from each sql command at different stages of migration will explain, as to how a specific
sql query's output is to be analyzed/intepreted and prepare for the next migration steps' execution.
14) ORA-15410: Disks in disk group GRID_1 do not have equal size.
Any size mismatch among old and new disks result in the above error.
Make sure grid disks are matching in size and numbers between old & new disks group-wise.
Followup with Storage Admins,Linux Admins to create new luns equal in size with their counter parts(old luns)
15) The order of adding & dropping disks from various disk_groups is purely dependent on existing protocols/standards/approvals
in the users' respective environments. Pls refer to the procedures followed in your environment.
16) In this script, I used regexp_replace function to map old disks' path with new disks' path.
There are few other options, that are available, for the users to explore and experiment within their environments.
17) On any given working-day, I will be working on multiple sessions, performing ASM disk migrations.
To asses the current situtation like what actions completed and what actions should follow next to complete the task
on a given server session, I regularly run the scrtips 03,04 and 06.
Based on the output from these scripts on a given servers' session, I analyse and take further actions.
18) Asses the state the migration process is in.
Run all required checks and make sure you understand clearly what are the pending actions to be executed.
Review the commands to be executed thoroughly on a given servers' session, before executing any commands.
19) These scripts ONLY work with ASM disks' migration.
Fon non-asm disks'/files' migration, these scripts WILL NOT work.
20) I was checking the last 200 lines of the alert log file for "ORA-" errors.
Pls modify the scripts as per your requirement for verification of any other errors/issues also.
21) There are other ways/methods to process ASM data migration tasks efficiently.
Always test the scripts, analyze the status, excersize caution.
22) Pls note, against each sql script, I pasted multiple results collected at different stages of ASM disk migration.
The script is given at the begining, followed by multiple resultsets collected at different times with explanation.

SCRIPTS:
-------------------------------------------------------------------------------------------------------------
connect to a test or dev ASM instance

--------------------------------------------------------------------------------------
01) --CONSOLIDATED REPORT(SHOWING COUNT OF LUNS PER DISK GROUP AND TOTAL ALLOCATED SIZE, TOTAL FREE SIZE IN GB)
**************************************************************************************
column name format a10
select g.name,mount_status,header_status,count(*) num_files, round(sum(d.total_mb)/1024,2) alloc_gb,
round(sum(d.free_mb)/1024,2) free_gb from v$asm_disk d,
--v$asm_diskgroup g
v$asm_diskgroup_stat g
where d.group_number=g.group_number group by g.name,mount_status,header_status
UNION ALL
select null,mount_status,header_status,count(*) num_files,round(sum(total_mb)/1024,2) alloc_gb,
round(sum(free_mb)/1024,2) free_gb from v$asm_disk group by mount_status,header_status

NAME MOUNT_S HEADER_STATU NUM_FILES ALLOC_GB FREE_GB
---------- ------- ------------ ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.7
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675 422.96

6 rows selected.
--Consolidated disks' info before starting ASM Disk Migration.

NAME MOUNT_S HEADER_STATU NUM_FILES ALLOC_GB FREE_GB
---------- ------- ------------ ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.67
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675 422.94
CLOSED CANDIDATE 20 0 0

7 rows selected.
--Here new luns were allocated at the OS level and are discovered by the ASM instance and are ready for adding.

NAME MOUNT_S HEADER_STATU NUM_FILES ALLOC_GB FREE_GB
---------- ------- ------------ ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.68
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675 422.95
CLOSED FORMER 4 0 0
CLOSED CANDIDATE 16 0 0

8 rows selected.
--ARCH_1 disk group was added with new luns, and old luns were dropped from ASM instance.
--This can be inferred by analysing output from sql query 06) here.

NAME MOUNT_S HEADER_STATU NUM_FILES ALLOC_GB FREE_GB
---------- ------- ------------ ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.69
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675 422.96
CLOSED FORMER 12 0 0
CLOSED CANDIDATE 8 0 0

8 rows selected.

--ARCH_1,DATA_1 disk group was added with new luns, and old luns were dropped from respective disk groups.
--This can be inferred by analysing output from sql query 06) here.

MOUNT HEADER
NAME STATUS STATUS NUM_FILES ALLOC_GB FREE_GB
---------- ------ --------- ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.68
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675.01 422.96
CLOSED FORMER 20 0 0

7 rows selected.

--All disk groups' old luns were replaced with new luns in the ASM instance.
--This can be inferred by analysing output from sql query 06) here.

NAME MOUNT_S HEADER_STATU NUM_FILES ALLOC_GB FREE_GB
---------- ------- ------------ ---------- ---------- ----------
FRA_1 CACHED MEMBER 4 50 13.43
ARCH_1 CACHED MEMBER 4 100 92.24
DATA_1 CACHED MEMBER 8 500 296.95
GRID_1 CACHED MEMBER 3 15 14.29
MGMT_1 CACHED MEMBER 1 10 5.6
CACHED MEMBER 20 675.01 422.51

6 rows selected.

--The old luns (20) were removed as os level.
--------------------------------------------------------------------------------------
02) --ADDNG DISKS MAPPED TO NEW LUNS('%new%') TO ASM DISKSGROUPS
**************************************************************************************
--This script ONLY works, after new luns were added to the server at OS level by SEs and
--the new luns were discovered by the ASM instance.
--It prints the commands for adding the mapped new luns to respective disk-groups in the ASM instance
--and dropping of correspondind old luns removal from respective disk-groups in the ASM instance
--Pls review the commands carefully before use.

set serverout on size 1000000 linesize 200
declare
v_str char(1);
v_pwr number:=7;
v_log varchar2(200);
begin
execute immediate 'alter session set nls_date_format='''||'yyyy-mm-dd hh24:mi:ss'||''' ';
select '!tail -200 '||value||'/diag/asm/+asm/'||instance_name||'/trace/alert_'||instance_name||'.log|grep "ORA-"' into v_log
from v$parameter p, v$instance i where p.name='diagnostic_dest';

for c0 in (select name,group_number from --v$asm_diskgroup
v$asm_diskgroup_stat order by 1) loop
dbms_output.put_line('------------------------');
dbms_output.put_line('alter diskgroup '||c0.name||' add disk ');
v_str:='Y';
for c1 in (
select m.disk_number,c.path,m.name from v$asm_disk m,v$asm_disk c
where m.group_number = c0.group_number and m.mount_status = 'CACHED' and m.header_status = 'MEMBER'
and c.mount_status = 'CLOSED' and c.header_status ='CANDIDATE' and
--and c.mount_status = 'CLOSED' and c.header_status ='FORMER' and
regexp_replace(lower(m.path),'new|lun0|_0|ive|_|lun|log0|log','') =
regexp_replace(lower(c.path),'new|lun0|_0|ive|_|lun|log0|log','')
order by disk_number
) loop
if (v_str='Y') then
--dbms_output.put_line(chr(39)||c1.new_path||chr(39));
dbms_output.put_line(chr(39)||c1.path||chr(39));
else
--dbms_output.put_line(','||chr(39)||c1.new_path||chr(39));
dbms_output.put_line(','||chr(39)||c1.path||chr(39));
end if;
v_str:='N';
end loop;
dbms_output.put_line('REBALANCE POWER '||v_pwr||' ');
dbms_output.put_line('alter session set nls_date_format='''||'yyyy-mm-dd hh24:mi:ss'||''' ');
dbms_output.put_line('select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION ');
dbms_output.put_line(v_log);
dbms_output.put_line('!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"');
dbms_output.put_line('------------------------');
dbms_output.put_line('alter diskgroup '||c0.name||' drop disk ');
v_str:='Y';
for c2 in (select path,name,create_date from v$asm_disk d where d.group_number = c0.group_number and d.mount_status = 'CACHED' and d.header_status = 'MEMBER'
and create_date <=(sysdate -2)
--and path not like '%new_%'
order by name ) loop
if (v_str='Y') then
dbms_output.put_line('--'||c2.name||' --'||c2.path||' --'||c2.create_date);
else
dbms_output.put_line('--'||','||c2.name||' --'||c2.path||' --'||c2.create_date);
end if;
v_str:='N';
end loop;
dbms_output.put_line('REBALANCE POWER '||v_pwr||' ');
dbms_output.put_line('alter session set nls_date_format='''||'yyyy-mm-dd hh24:mi:ss'||''' ');
dbms_output.put_line('select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION ');
dbms_output.put_line(v_log);
dbms_output.put_line('------------------------');
end loop;
end;

------------------------
alter diskgroup ARCH_1 add disk
'/u00/product/asm/new_arch_01'
,'/u00/product/asm/new_arch_02'
,'/u00/product/asm/new_arch_03'
,'/u00/product/asm/new_arch_04'
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
alter diskgroup ARCH_1 drop disk
--ARCH_1_001 --/u00/product/asm/archive_lun01 --2005-09-08 09:09:45
--,ARCH_1_002 --/u00/product/asm/archive_lun02 --2005-09-08 09:09:45
--,ARCH_1_003 --/u00/product/asm/archive_lun03 --2005-09-08 09:09:45
--,ARCH_1_004 --/u00/product/asm/archive_lun04 --2005-09-08 09:09:45
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
------------------------
alter diskgroup DATA_1 add disk
'/u00/product/asm/new_data_01'
,'/u00/product/asm/new_data_02'
,'/u00/product/asm/new_data_03'
,'/u00/product/asm/new_data_04'
,'/u00/product/asm/new_data_06'
,'/u00/product/asm/new_data_07'
,'/u00/product/asm/new_data_08'
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
alter diskgroup DATA_1 drop disk
--DATA_1_001 --/u00/product/asm/data_lun01 --2005-09-08 09:14:35
--,DATA_1_002 --/u00/product/asm/data_lun02 --2005-09-08 09:14:35
--,DATA_1_003 --/u00/product/asm/data_lun03 --2005-09-08 09:14:35
--,DATA_1_004 --/u00/product/asm/data_lun04 --2005-09-08 09:14:35
--,DATA_1_006 --/u00/product/asm/data_lun06 --2005-09-08 09:14:35
--,DATA_1_007 --/u00/product/asm/data_lun07 --2005-09-08 09:14:35
--,DATA_1_008 --/u00/product/asm/data_lun08 --2005-09-08 09:14:35
--,DATA_1_013 --/u00/product/asm/data_lun13 --2005-09-08 09:14:35
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
------------------------
alter diskgroup FRA_1 add disk
'/u00/product/asm/new_fra_01'
,'/u00/product/asm/new_fra_02'
,'/u00/product/asm/new_fra_03'
,'/u00/product/asm/new_fra_04'
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
alter diskgroup FRA_1 drop disk
--FRA_1_001 --/u00/product/asm/fra_lun01 --2005-09-08 09:16:22
--,FRA_1_002 --/u00/product/asm/fra_lun02 --2005-09-08 09:16:22
--,FRA_1_003 --/u00/product/asm/fra_lun03 --2005-09-08 09:16:22
--,FRA_1_004 --/u00/product/asm/fra_lun04 --2005-09-08 09:16:22
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
------------------------
alter diskgroup GRID_1 add disk
'/u00/product/asm/new_ocr_css_01'
,'/u00/product/asm/new_ocr_css_02'
,'/u00/product/asm/new_ocr_css_03'
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
alter diskgroup GRID_1 drop disk
--GRID_1_0000 --/u00/product/asm/ocr_css1 --2005-09-07 05:09:43
--,GRID_1_0001 --/u00/product/asm/ocr_css2 --2005-09-07 05:09:43
--,GRID_1_0002 --/u00/product/asm/ocr_css3 --2005-09-07 05:09:43
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
------------------------
alter diskgroup MGMT_1 add disk
'/u00/product/asm/new_mgmt_01'
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------
alter diskgroup MGMT_1 drop disk
--MGMT_1_0000 --/u00/product/asm/mgmt1 --2005-09-07 05:24:16
REBALANCE POWER 7
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES,sysdate from GV$ASM_OPERATION
!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"
------------------------

PL/SQL procedure successfully completed.

--Pls review the output from the pl/sql procedure and make necessary modifications before use.
--Pls make sure the output of "tail -200" command points to the right path of alert*.log file in your environment.
--------------------------------------------------------------------------------------
03) --AT WHAT STAGE THE ASM DISK MIGRATION PROCESS IS(AT WHAT STAGE THE ASM DISK MIGRATION PROCESS CURRENTLY IS)?
**************************************************************************************
select o.group_number,g.name,old_luns_count,n.group_number,new_luns_count from v$asm_diskgroup g,
(select group_number,count(*) old_luns_count from v$asm_disk where group_number > 0 and create_date <=(sysdate -2) group by group_number) o,
(select group_number,count(*) new_luns_count from v$asm_disk where group_number > 0 and create_date >=(sysdate -2) group by group_number) n
where g.group_number = o.group_number and o.group_number = n.group_number(+) --BEFORE DROPPING ASM DISKS

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ ---------- -------------- ------------ --------------
1 ARCH_1 4
2 DATA_1 8
3 FRA_1 4
4 GRID_1 3
5 MGMT_1 1

--Here, no new disks were added to any existing disk groups in the asm instance

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ ---------- -------------- ------------ --------------
1 ARCH_1 4 1 4
2 DATA_1 8
3 FRA_1 4
4 GRID_1 3
5 MGMT_1 1

--New disks were added to ARCH_1 disk group. Make sure the number of disks between old luns count and new luns count match.
--Pls verify the above outpt with the output of sql query 06)
--Here the old disks are ready for drop from DATA_1 disk group.
--Pls run sql query 04) for further verification of old luns and new luns before executing a drop command.

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ ---------- -------------- ------------ --------------
2 DATA_1 8
3 FRA_1 4
4 GRID_1 3
5 MGMT_1 1

--After adding of new disks/dropping the old disks from ARCH_1 disk group, it will not appear here.

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ ---------- -------------- ------------ --------------
2 DATA_1 8 2 8
3 FRA_1 4
4 GRID_1 3
5 MGMT_1 1

--New disks were added to DATA_1 disk group. Make sure the number of disks between old luns count and new luns count match.
--Pls verify the above outpt with the output of sql query 06)
--Here the old disks are ready for drop from DATA_1 disk group.
--Pls run sql query 04) for further verification of old luns and new luns before executing a drop command.

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ --------------- -------------- ------------ --------------
3 FRA_1 4 3 4
4 GRID_1 3
5 MGMT_1 1

--After adding of new disks/dropping the old disks from DATA_1 disk group, it will not appear here.
--Pls verify the above outpt with the output of sql query 06)
--Here the old disks are ready for drop from DATA_1 disk group.
--Pls run sql query 04) for further verification of old luns and new luns before executing a drop command.

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ --------------- -------------- ------------ --------------
4 GRID_1 3
5 MGMT_1 1 5 1

--New disks were added to MGMT_1 disk group. Make sure the number of disks between old luns count and new luns count match.
--Pls verify the above outpt with the output of sql query 06)
--Here the old disks are ready for drop from DATA_1 disk group.
--Pls run sql query 04) for further verification of old luns and new luns before executing a drop command.

GROUP_NUMBER NAME OLD_LUNS_COUNT GROUP_NUMBER NEW_LUNS_COUNT
------------ --------------- -------------- ------------ --------------
4 GRID_1 3 4 3

--New disks were added to GRID_1 disk group. Make sure the number of disks between old luns count and new luns count match.
--Pls verify the above outpt with the output of sql query 06)
--Here the old disks are ready for drop from DATA_1 disk group.
--Pls run sql query 04) for further verification of old luns and new luns before executing a drop command.

01) While performing multiple migrations simultaneously, there is a greater risk of dropping a group's disks without adding new luns.
Performing such an action on a live application will be catastrophic.
02) The above sql always helped me, in such scarry situations.
03) How to interpret the output of the above sql - read the narrative against each output of the above sql's execution
at various stages. Make sure, you understand the current situation, where the migration is and the next steps to perform carefully.

--------------------------------------------------------------------------------------
04) --VALIDATING THE OLD CANDIDATE LUNS FOR DROP.
**************************************************************************************
column path format a45
select d.name||',--',d.path,d.create_date from v$asm_disk d where d.group_number=4 order by d.create_date

D.NAME||',--' PATH CREATE_DA
--------------------------------- --------------------------------------------- ---------
GRID_1_0000,-- /u00/product/asm/ocr_css1 07-SEP-05
GRID_1_0001,-- /u00/product/asm/ocr_css2 07-SEP-05
GRID_1_0002,-- /u00/product/asm/ocr_css3 07-SEP-05
GRID_1_0003,-- /u00/product/asm/new_ocr_css_01 12-SEP-18
GRID_1_0004,-- /u00/product/asm/new_ocr_css_02 12-SEP-18
GRID_1_0005,-- /u00/product/asm/new_ocr_css_03 12-SEP-18

6 rows selected.
--Prints all the disks that were existing in disk group of GRID_1.
--Note the "path" and "create_date" of each of the existing disks in the disk group.
--Make sure to validate the old disks before drop.
--Each time, before executing the above query, pls modify the group_number with required number in the where clause.

select d.name||',--',d.path,d.create_date from v$asm_disk d,v$asm_disk a where d.group_number = 4 and d.create_date <= (sysdate -2)
and d.group_number = a.group_number and a.create_date >= (sysdate -2) and
lower(regexp_replace(d.path,'new|lun0|_0|ive|_|lun|log0|log','')) =
lower(regexp_replace(a.path,'new|lun0|_0|ive|_|lun|log0|log',''))

NAME||',--' PATH CREATE_DATE
--------------------------------- --------------------------------------------- -------------------
ARCH_1_001,-- /u00/product/asm/archive_lun01 2005-09-08 09:09:45
ARCH_1_002,-- /u00/product/asm/archive_lun02 2005-09-08 09:09:45
ARCH_1_003,-- /u00/product/asm/archive_lun03 2005-09-08 09:09:45
ARCH_1_004,-- /u00/product/asm/archive_lun04 2005-09-08 09:09:45

--Each time, before executing the above query, pls modify the group_number with required number in the where clause.
--This query returns no rows, if new disks were not added in the last two days to the specified disk_group mentioned in the above query.
--Corelate the output data between sql queries 03 and 04, after assessing these outputs, decide next steps.
--Use extreme caution, while dropping old luns.
--Users' descretion is advised.

--------------------------------------------------------------------------------------
05) --DROPPING DISKS MAPPED TO OLD LUNS( not '%new%')
**************************************************************************************
--This script only works after new luns were discovered by ASM instance.

set serverout on size 1000000
declare
v_str char(1);
v_pwr number:=7;
v_log varchar2(200);
v_numn number; --NEW DISKS
v_numo number; --OLD DISKS
begin
select '!tail -200 '||value||'/diag/asm/+asm/'||instance_name||'/trace/alert_'||instance_name||'.log|grep "ORA-"' into v_log
from v$parameter p, v$instance i where p.name='diagnostic_dest';
for c0 in (select name,group_number from --v$asm_diskgroup
v$asm_diskgroup_stat order by 1) loop
select count(*) into v_numn from v$asm_disk
where group_number=c0.group_number and mount_status='CACHED' and header_status='MEMBER' and path like '%new_%';
select count(*) into v_numo from v$asm_disk
where group_number=c0.group_number and mount_status='CACHED' and header_status='MEMBER' and path not like '%new_%';
if (v_numn = v_numo) then
dbms_output.put_line('------------------------');
for c1 in (select name,path from v$asm_disk d
where d.group_number = c0.group_number and d.mount_status = 'CACHED' and d.header_status = 'MEMBER' --'CANDIDATE'
and d.path LIKE '%new%' --keep all luns with string "%new%"
order by path ) loop
dbms_output.put_line('--'||' '||chr(39)||c1.path||chr(39)||','||' '||c1.name||', ');
end loop;
dbms_output.put_line('select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION');
dbms_output.put_line('------------------------');
dbms_output.put_line('alter diskgroup '||c0.name||' drop disk ');
v_str:='Y';
for c2 in (select path,name,create_date from v$asm_disk d where d.group_number = c0.group_number and d.mount_status = 'CACHED' and d.header_status = 'MEMBER'
and d.path NOT LIKE '%new%' --remove all luns without string "%new%"
order by name ) loop
if (v_str='Y') then
dbms_output.put_line(' --'||c2.name||' '||'--'||' '||chr(39)||c2.path||chr(39)||' '||c2.create_date);
else
dbms_output.put_line(',--'||c2.name||' '||'--'||' '||chr(39)||c2.path||chr(39)||' '||c2.create_date);
end if;
v_str:='N';
end loop;
dbms_output.put_line('REBALANCE POWER '||v_pwr||'');
dbms_output.put_line(v_log);
dbms_output.put_line('!tail -200 /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log|grep "ORA-"');
dbms_output.put_line('select INST_ID,OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION');
dbms_output.put_line('------------------------');
end if;
end loop;
end;

--------------------------------------------------------------------------------------
06) --VERIFY EXISTING OLD LUNS WITH NEW LUNS. COMPARE ONE TO ONE WITH OLD, NEW LUNS
**************************************************************************************
--Make sure the mount_status and header_status are changing during the disk migration after each disk groups' migration.
--Pls cross check this output with the output from query 03)

set linesize 200 pagesize 300
column path format a36
column d_name format a15
column g_name format a10
column sz_gb format 99,999.99
column group_number format 999 heading "GRP|NUM"
column disk_number format 999 heading "DSK|NUM"
column header_status format a9 heading "HEADER|STATUS"
select g.name g_name,m.disk_number,m.name d_name,m.path,m.header_status, m.sz_gb, --c.group_number,
c.disk_number,c.path,c.header_status,c.sz_gb
from v$asm_diskgroup_stat g,
(SELECT a.*, regexp_replace(lower(a.path),'new|lun0|_0|ive|_|lun|log0|log','') new_path,round(a.os_mb/1024,2) sz_gb FROM v$asm_disk a) m,
(SELECT a.*, regexp_replace(lower(a.path),'new|lun0|_0|ive|_|lun|log0|log','') new_path,round(a.os_mb/1024,2) sz_gb FROM v$asm_disk a) c
where g.group_number = m.group_number
and m.mount_status = 'CACHED' and m.header_status='MEMBER' and c.mount_status='CLOSED' and c.header_status!='MEMBER' and
m.new_path = c.new_path(+)
and m.os_mb <> c.os_mb
order by 1,2

GRP DSK MOUNT HEADER MOUNT HEADER
NUM NUM NAME PATH STATUS STATUS PATH STATUS STATUS
---- ---- --------------- ----------------------------------- -------- ------------ ----------------------------------- -------- ------------
1 4 ARCH_1_0004 /u00/product/asm/new_arch_01 CACHED MEMBER /u00/product/asm/archive_lun01 CLOSED FORMER
1 5 ARCH_1_0005 /u00/product/asm/new_arch_02 CACHED MEMBER /u00/product/asm/archive_lun02 CLOSED FORMER
1 6 ARCH_1_0006 /u00/product/asm/new_arch_03 CACHED MEMBER /u00/product/asm/archive_lun03 CLOSED FORMER
1 7 ARCH_1_0007 /u00/product/asm/new_arch_04 CACHED MEMBER /u00/product/asm/archive_lun04 CLOSED FORMER
2 8 DATA_1_0008 /u00/product/asm/new_data_01 CACHED MEMBER /u00/product/asm/data_lun01 CLOSED FORMER
2 9 DATA_1_0009 /u00/product/asm/new_data_02 CACHED MEMBER /u00/product/asm/data_lun02 CLOSED FORMER
2 10 DATA_1_0010 /u00/product/asm/new_data_03 CACHED MEMBER /u00/product/asm/data_lun03 CLOSED FORMER
2 11 DATA_1_0011 /u00/product/asm/new_data_04 CACHED MEMBER /u00/product/asm/data_lun04 CLOSED FORMER
2 12 DATA_1_0012 /u00/product/asm/new_data_05 CACHED MEMBER /u00/product/asm/data_lun05 CLOSED FORMER
2 13 DATA_1_0013 /u00/product/asm/new_data_06 CACHED MEMBER /u00/product/asm/data_lun06 CLOSED FORMER
2 14 DATA_1_0014 /u00/product/asm/new_data_07 CACHED MEMBER /u00/product/asm/data_lun07 CLOSED FORMER
2 15 DATA_1_0015 /u00/product/asm/new_data_08 CACHED MEMBER /u00/product/asm/data_lun08 CLOSED FORMER
3 0 FRA_1_001 /u00/product/asm/fra_lun01 CACHED MEMBER /u00/product/asm/new_fra_01 CLOSED CANDIDATE
3 1 FRA_1_002 /u00/product/asm/fra_lun02 CACHED MEMBER /u00/product/asm/new_fra_02 CLOSED CANDIDATE
3 2 FRA_1_003 /u00/product/asm/fra_lun03 CACHED MEMBER /u00/product/asm/new_fra_03 CLOSED CANDIDATE
3 3 FRA_1_004 /u00/product/asm/fra_lun04 CACHED MEMBER /u00/product/asm/new_fra_04 CLOSED CANDIDATE
4 0 GRID_1_0000 /u00/product/asm/ocr_css1 CACHED MEMBER /u00/product/asm/new_ocr_css_01 CLOSED CANDIDATE
4 1 GRID_1_0001 /u00/product/asm/ocr_css2 CACHED MEMBER /u00/product/asm/new_ocr_css_02 CLOSED CANDIDATE
4 2 GRID_1_0002 /u00/product/asm/ocr_css3 CACHED MEMBER /u00/product/asm/new_ocr_css_03 CLOSED CANDIDATE
5 0 MGMT_1_0000 /u00/product/asm/mgmt1 CACHED MEMBER /u00/product/asm/new_mgmt_01 CLOSED CANDIDATE

20 rows selected.

MOUNT HEADER MOUNT HEADER
GROUP_NUMBER DISK_NUMBER NAME PATH STATUS STATUS PATH STATUS STATUS
------------ ----------- --------------- ----------------------------------- -------- ------------ ----------------------------------- -------- ---------
1 4 ARCH_1_0004 /u00/product/asm/new_arch_01 CACHED MEMBER /u00/product/asm/archive_lun01 CLOSED FORMER
1 5 ARCH_1_0005 /u00/product/asm/new_arch_02 CACHED MEMBER /u00/product/asm/archive_lun02 CLOSED FORMER
1 6 ARCH_1_0006 /u00/product/asm/new_arch_03 CACHED MEMBER /u00/product/asm/archive_lun03 CLOSED FORMER
1 7 ARCH_1_0007 /u00/product/asm/new_arch_04 CACHED MEMBER /u00/product/asm/archive_lun04 CLOSED FORMER
2 8 DATA_1_0008 /u00/product/asm/new_data_01 CACHED MEMBER /u00/product/asm/data_lun01 CLOSED FORMER
2 9 DATA_1_0009 /u00/product/asm/new_data_02 CACHED MEMBER /u00/product/asm/data_lun02 CLOSED FORMER
2 10 DATA_1_0010 /u00/product/asm/new_data_03 CACHED MEMBER /u00/product/asm/data_lun03 CLOSED FORMER
2 11 DATA_1_0011 /u00/product/asm/new_data_04 CACHED MEMBER /u00/product/asm/data_lun04 CLOSED FORMER
2 12 DATA_1_0012 /u00/product/asm/new_data_05 CACHED MEMBER /u00/product/asm/data_lun05 CLOSED FORMER
2 13 DATA_1_0013 /u00/product/asm/new_data_06 CACHED MEMBER /u00/product/asm/data_lun06 CLOSED FORMER
2 14 DATA_1_0014 /u00/product/asm/new_data_07 CACHED MEMBER /u00/product/asm/data_lun07 CLOSED FORMER
2 15 DATA_1_0015 /u00/product/asm/new_data_08 CACHED MEMBER /u00/product/asm/data_lun08 CLOSED FORMER
3 4 FRA_1_0004 /u00/product/asm/new_fra_01 CACHED MEMBER /u00/product/asm/fra_lun01 CLOSED FORMER
3 5 FRA_1_0005 /u00/product/asm/new_fra_02 CACHED MEMBER /u00/product/asm/fra_lun02 CLOSED FORMER
3 6 FRA_1_0006 /u00/product/asm/new_fra_03 CACHED MEMBER /u00/product/asm/fra_lun03 CLOSED FORMER
3 7 FRA_1_0007 /u00/product/asm/new_fra_04 CACHED MEMBER /u00/product/asm/fra_lun04 CLOSED FORMER
4 3 GRID_1_0003 /u00/product/asm/new_ocr_css_01 CACHED MEMBER /u00/product/asm/ocr_css1 CLOSED FORMER
4 4 GRID_1_0004 /u00/product/asm/new_ocr_css_02 CACHED MEMBER /u00/product/asm/ocr_css2 CLOSED FORMER
4 5 GRID_1_0005 /u00/product/asm/new_ocr_css_03 CACHED MEMBER /u00/product/asm/ocr_css3 CLOSED FORMER
5 1 MGMT_1_0001 /u00/product/asm/new_mgmt_01 CACHED MEMBER /u00/product/asm/mgmt1 CLOSED FORMER

20 rows selected.

set linesize 200 pagesize 200
column name format a15
column path format a38
column mount_status format a6 heading "MOUNT|STATUS"
column header_status format a9
column sector_size format 9999 heading "SCTR|SIZE"
column size_gb format 9999.99
select m.name,m.path,m.mount_status,m.header_status,round(m.os_mb/1024,2) sz_gb,m.sector_size,c.path,c.mount_status,c.header_status,round(c.os_mb/1024,2) sz_gb,c.sector_size
from
(select a.*,
regexp_replace(lower(a.path),'_0|lun0|lun|ive|new|_','') new_path --Replace multiple substitutions with one expression(reqexp_replace)
from v$asm_disk a where a.mount_status='CACHED' and a.header_status='MEMBER') m,
(select a.*,
regexp_replace(lower(a.path),'_0|lun0|lun|ive|new|_','') new_path --Replace multiple substitutions with one expression(reqexp_replace)
from v$asm_disk a where a.mount_status='CLOSED' and a.header_status in ('CANDIDATE','FORMER')) c
where m.new_path = c.new_path(+) --any disk is not 1:1 mapped
order by 2
MOUNT HEADER SCTR MOUNT HEADER SCTR
NAME PATH STATUS STATUS SZ_GB SIZE PATH STATUS STATUS SZ_GB SIZE
--------------- -------------------------------------- ------ --------- ---------- ----- -------------------------------------- ------ --------- ---------- -----
ARCH_1_0000 /u00/product/asm/arch_lun1 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_01 CLOSED CANDIDATE 128.00 512
ARCH_1_0001 /u00/product/asm/arch_lun2 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_02 CLOSED CANDIDATE 128.00 512
ARCH_1_0002 /u00/product/asm/arch_lun3 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_03 CLOSED CANDIDATE 128.00 512
ARCH_1_0003 /u00/product/asm/arch_lun4 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_04 CLOSED CANDIDATE 128.00 512
DATA_1_0000 /u00/product/asm/data_lun1 CACHED MEMBER 96.00 512 /u00/product/asm/new_data_01 CLOSED CANDIDATE 96.00 512
.....................................
.....................................
FRA_1_0002 /u00/product/asm/fra_lun3 CACHED MEMBER 128.00 512 /u00/product/asm/new_fra_03 CLOSED CANDIDATE 128.00 512
FRA_1_0003 /u00/product/asm/fra_lun4 CACHED MEMBER 128.00 512 /u00/product/asm/new_fra_04 CLOSED CANDIDATE 128.00 512
MGMT_1_0000 /u00/product/asm/mgmt1 CACHED MEMBER 10.00 512 /u00/product/asm/new_mgmt_01 CLOSED CANDIDATE 10.00 512
GRID_1_0000 /u00/product/asm/ocr_css1 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_01 CLOSED CANDIDATE 5.00 512
GRID_1_0001 /u00/product/asm/ocr_css2 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_02 CLOSED CANDIDATE 5.00 512
GRID_1_0002 /u00/product/asm/ocr_css3 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_03 CLOSED CANDIDATE 5.00 512

44 rows selected.

--------------------------------------------------------------------------------------
07) --TO CREATE SCRIPT TO ADD NEW DISKS TO ASM DISKGROUPS & REMOVE OLD DISKS FROM ASM DISKGROUPS --USE THE OUTPUT FROM ABOVE SQL QUERY AT 06) TO PROCESS DATA
**************************************************************************************
--There are other ways to acheive these results.
--Explore better options as that may be suitable.
--Pls make sure, on the left side all member disks are displayed and on the right side all candidate disks are displayed.
--Then only run this script, if any disks with closed and former status are present, then this script is of no use.
--Use your judgement.

cat tmp|awk '{print $1}'|sort|uniq|while read GRPdo echo "alter diskgroup $GRP add disk" \
grep $GRP tmp|awk '{if (($9 == "CANDIDATE" || $9 == "FORMER") && $5 == "MEMBER") {print "@"$8"#"}}'|sed "s/@/'/gs/#/',/g"\
echo "rebalance power 7"echo "-------------------------------"\
echo "alter diskgroup $GRP drop disk"grep $GRP tmp|awk '{if (($9 == "CANDIDATE" || $9 == "FORMER") && $5 == "MEMBER") {print $3",--"$4}}'\
echo "rebalance power 7"echo "-------------------------------"\
done

alter diskgroup ARCH_1 add disk
'/u00/product/asm/new_arch_01',
'/u00/product/asm/new_arch_02',
'/u00/product/asm/new_arch_03',
'/u00/product/asm/new_arch_04',
rebalance power 7
-------------------------------
alter diskgroup ARCH_1 drop disk
ARCH_1_0000,--/u00/product/asm/arch_lun1
ARCH_1_0001,--/u00/product/asm/arch_lun2
ARCH_1_0002,--/u00/product/asm/arch_lun3
ARCH_1_0003,--/u00/product/asm/arch_lun4
rebalance power 7
-------------------------------
alter diskgroup DATA_1 add disk
'/u00/product/asm/new_data_01',
'/u00/product/asm/new_data_02',
'/u00/product/asm/new_data_03',
'/u00/product/asm/new_data_04',
'/u00/product/asm/new_data_05',
'/u00/product/asm/new_data_06',
'/u00/product/asm/new_data_07',
'/u00/product/asm/new_data_08',
'/u00/product/asm/new_data_09',
'/u00/product/asm/new_data_10',
'/u00/product/asm/new_data_11',
'/u00/product/asm/new_data_12',
'/u00/product/asm/new_data_13',
'/u00/product/asm/new_data_14',
'/u00/product/asm/new_data_15',
'/u00/product/asm/new_data_16',
'/u00/product/asm/new_data_17',
'/u00/product/asm/new_data_18',
'/u00/product/asm/new_data_19',
'/u00/product/asm/new_data_20',
'/u00/product/asm/new_data_21',
'/u00/product/asm/new_data_22',
'/u00/product/asm/new_data_23',
'/u00/product/asm/new_data_24',
'/u00/product/asm/new_data_25',
'/u00/product/asm/new_data_26',
'/u00/product/asm/new_data_27',
'/u00/product/asm/new_data_28',
'/u00/product/asm/new_data_29',
'/u00/product/asm/new_data_30',
'/u00/product/asm/new_data_31',
'/u00/product/asm/new_data_32',
rebalance power 7
-------------------------------
alter diskgroup DATA_1 drop disk
DATA_1_0000,--/u00/product/asm/data_lun1
DATA_1_0001,--/u00/product/asm/data_lun2
DATA_1_0002,--/u00/product/asm/data_lun3
DATA_1_0003,--/u00/product/asm/data_lun4
DATA_1_0004,--/u00/product/asm/data_lun5
DATA_1_0005,--/u00/product/asm/data_lun6
DATA_1_0006,--/u00/product/asm/data_lun7
DATA_1_0007,--/u00/product/asm/data_lun8
DATA_1_0008,--/u00/product/asm/data_lun9
DATA_1_0009,--/u00/product/asm/data_lun10
DATA_1_0010,--/u00/product/asm/data_lun11
DATA_1_0011,--/u00/product/asm/data_lun12
DATA_1_0012,--/u00/product/asm/data_lun13
DATA_1_0013,--/u00/product/asm/data_lun14
DATA_1_0014,--/u00/product/asm/data_lun15
DATA_1_0015,--/u00/product/asm/data_lun16
DATA_1_0016,--/u00/product/asm/data_lun17
DATA_1_0017,--/u00/product/asm/data_lun18
DATA_1_0018,--/u00/product/asm/data_lun19
DATA_1_0019,--/u00/product/asm/data_lun20
DATA_1_0020,--/u00/product/asm/data_lun21
DATA_1_0021,--/u00/product/asm/data_lun22
DATA_1_0022,--/u00/product/asm/data_lun23
DATA_1_0023,--/u00/product/asm/data_lun24
DATA_1_0024,--/u00/product/asm/data_lun25
DATA_1_0025,--/u00/product/asm/data_lun26
DATA_1_0026,--/u00/product/asm/data_lun27
DATA_1_0027,--/u00/product/asm/data_lun28
DATA_1_0028,--/u00/product/asm/data_lun29
DATA_1_0029,--/u00/product/asm/data_lun30
DATA_1_0030,--/u00/product/asm/data_lun31
DATA_1_0031,--/u00/product/asm/data_lun32
rebalance power 7
-------------------------------
alter diskgroup FRA_1 add disk
'/u00/product/asm/new_fra_01',
'/u00/product/asm/new_fra_02',
'/u00/product/asm/new_fra_03',
'/u00/product/asm/new_fra_04',
rebalance power 7
-------------------------------
alter diskgroup FRA_1 drop disk
FRA_1_0000,--/u00/product/asm/fra_lun1
FRA_1_0001,--/u00/product/asm/fra_lun2
FRA_1_0002,--/u00/product/asm/fra_lun3
FRA_1_0003,--/u00/product/asm/fra_lun4
rebalance power 7
-------------------------------
alter diskgroup GRID_1 add disk
'/u00/product/asm/new_ocr_css_01',
'/u00/product/asm/new_ocr_css_02',
'/u00/product/asm/new_ocr_css_03',
rebalance power 7
-------------------------------
alter diskgroup GRID_1 drop disk
GRID_1_0000,--/u00/product/asm/ocr_css1
GRID_1_0001,--/u00/product/asm/ocr_css2
GRID_1_0002,--/u00/product/asm/ocr_css3
rebalance power 7
-------------------------------
alter diskgroup MGMT_1 add disk
'/u00/product/asm/new_mgmt_01',
rebalance power 7
-------------------------------
alter diskgroup MGMT_1 drop disk
MGMT_1_0000,--/u00/product/asm/mgmt1
rebalance power 7
-------------------------------
--------------------------------------------------------------------------------------
08) --SIMILAR SQL REPORT AS 06) I INCLUDED THE SIZE ALSO.
**************************************************************************************
set linesize 200 pagesize 200
column name format a15
column path format a38
column mount_status format a6 heading "MOUNT|STATUS"
column header_status format a9
column sector_size format 9999 heading "SCTR|SIZE"
column size_gb format 9999.99
select m.name,m.path,m.mount_status,m.header_status,round(m.os_mb/1024,2) sz_gb,m.sector_size,c.path,c.mount_status,c.header_status,round(c.os_mb/1024,2) sz_gb,c.sector_size
from
(select a.*,
regexp_replace(lower(a.path),'_0|lun0|lun|ive|new|_','') new_path --Replace multiple substitutions with one expression(reqexp_replace)
from v$asm_disk a where a.mount_status='CACHED' and a.header_status='MEMBER') m,
(select a.*,
regexp_replace(lower(a.path),'_0|lun0|lun|ive|new|_','') new_path --Replace multiple substitutions with one expression(reqexp_replace)
from v$asm_disk a where a.mount_status='CLOSED' and a.header_status in ('CANDIDATE','FORMER')) c
where m.new_path = c.new_path(+) --any disk is not 1:1 mapped
order by 2
MOUNT HEADER SCTR MOUNT HEADER SCTR
NAME PATH STATUS STATUS SZ_GB SIZE PATH STATUS STATUS SZ_GB SIZE
--------------- -------------------------------------- ------ --------- ---------- ----- -------------------------------------- ------ --------- ---------- -----
ARCH_1_0000 /u00/product/asm/arch_lun1 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_01 CLOSED CANDIDATE 128.00 512
ARCH_1_0001 /u00/product/asm/arch_lun2 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_02 CLOSED CANDIDATE 128.00 512
ARCH_1_0002 /u00/product/asm/arch_lun3 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_03 CLOSED CANDIDATE 128.00 512
ARCH_1_0003 /u00/product/asm/arch_lun4 CACHED MEMBER 128.00 512 /u00/product/asm/new_arch_04 CLOSED CANDIDATE 128.00 512
DATA_1_0000 /u00/product/asm/data_lun1 CACHED MEMBER 96.00 512 /u00/product/asm/new_data_01 CLOSED CANDIDATE 96.00 512
DATA_1_0009 /u00/product/asm/data_lun10 CACHED MEMBER 96.00 512 /u00/product/asm/new_data_10 CLOSED CANDIDATE 96.00 512
DATA_1_0010 /u00/product/asm/data_lun11 CACHED MEMBER 96.00 512 /u00/product/asm/new_data_11 CLOSED CANDIDATE 96.00 512
.......................................
.......................................
FRA_1_0001 /u00/product/asm/fra_lun2 CACHED MEMBER 128.00 512 /u00/product/asm/new_fra_02 CLOSED CANDIDATE 128.00 512
FRA_1_0002 /u00/product/asm/fra_lun3 CACHED MEMBER 128.00 512 /u00/product/asm/new_fra_03 CLOSED CANDIDATE 128.00 512
FRA_1_0003 /u00/product/asm/fra_lun4 CACHED MEMBER 128.00 512 /u00/product/asm/new_fra_04 CLOSED CANDIDATE 128.00 512
MGMT_1_0000 /u00/product/asm/mgmt1 CACHED MEMBER 10.00 512 /u00/product/asm/new_mgmt_01 CLOSED CANDIDATE 10.00 512
GRID_1_0000 /u00/product/asm/ocr_css1 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_01 CLOSED CANDIDATE 5.00 512
GRID_1_0001 /u00/product/asm/ocr_css2 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_02 CLOSED CANDIDATE 5.00 512
GRID_1_0002 /u00/product/asm/ocr_css3 CACHED MEMBER 5.00 512 /u00/product/asm/new_ocr_css_03 CLOSED CANDIDATE 5.00 512

44 rows selected.

»