Exadata에서 1개의 ASM Disk는 8개의 ASM Partner Disk를 가집니다.
ASM 인스턴스에서 아래 쿼리를 수행하면 ASM Partner Disk를 조회할 수 있습니다. 1번 셀의 0번 디스크는 2번 셀의 0, 2, 5, 6번 디스크, 3번 셀의 0, 6, 7, 10번 디스크와 파트너링되어 있습니다.
-- 1
SELECT disk
, LISTAGG (partner_disk, ',') WITHIN GROUP (ORDER BY partner_disk) AS partner_disk
FROM (SELECT cell || '[' || disk || ']' AS disk
, partner_cell || '[' || LISTAGG (partner_disk, ',') WITHIN GROUP (ORDER BY number_kfdpartner) || ']' AS partner_disk
FROM (SELECT LPAD (CEIL ((b.disk + 1) / 12), 2, '0') AS cell
, LPAD (MOD (b.disk, 12), 2, '0') AS disk
, LPAD (CEIL ((b.number_kfdpartner + 1) / 12), 2, '0') AS partner_cell
, LPAD (MOD (b.number_kfdpartner, 12), 2, '0') AS partner_disk
, number_kfdpartner
FROM v$asm_disk a
, x$kfdpartner b
WHERE a.name LIKE 'DATA%'
AND b.number_kfdpartner = a.disk_number
AND b.grp = 1)
GROUP BY cell
, disk
, partner_cell)
GROUP BY disk
ORDER BY disk;
DISK PARTNER_DISK
------ -------------------------------
01[00] 02[00,02,05,06],03[00,06,07,10]
01[01] 02[01,03,05,08],03[01,06,08,11]
01[02] 02[02,04,07,08],03[00,02,08,09]
01[03] 02[03,06,07,09],03[01,03,09,10]
01[04] 02[04,05,09,10],03[02,05,10,11]
01[05] 02[00,05,07,11],03[00,01,05,07]
01[06] 02[01,02,07,10],03[02,03,07,08]
01[07] 02[06,08,10,11],03[00,03,04,11]
01[08] 02[00,01,08,09],03[01,02,04,06]
01[09] 02[02,03,09,11],03[03,05,06,09]
01[10] 02[00,03,04,10],03[04,05,08,10]
01[11] 02[01,04,06,11],03[04,07,09,11]
02[00] 01[00,05,08,10],03[00,02,04,07]
02[01] 01[01,06,08,11],03[01,03,06,07]
02[02] 01[00,02,06,09],03[02,05,06,08]
02[03] 01[01,03,09,10],03[03,04,08,09]
02[04] 01[02,04,10,11],03[05,07,09,10]
02[05] 01[00,01,04,05],03[00,07,08,11]
02[06] 01[00,03,07,11],03[04,06,10,11]
02[07] 01[02,03,05,06],03[01,02,08,10]
02[08] 01[01,02,07,08],03[00,01,06,09]
02[09] 01[03,04,08,09],03[02,03,09,11]
02[10] 01[04,06,07,10],03[00,03,05,10]
02[11] 01[05,07,09,11],03[01,04,05,11]
03[00] 01[00,02,05,07],02[00,05,08,10]
03[01] 01[01,03,05,08],02[01,07,08,11]
03[02] 01[02,04,06,08],02[00,02,07,09]
03[03] 01[03,06,07,09],02[01,03,09,10]
03[04] 01[07,08,10,11],02[00,03,06,11]
03[05] 01[04,05,09,10],02[02,04,10,11]
03[06] 01[00,01,08,09],02[01,02,06,08]
03[07] 01[00,05,06,11],02[00,01,04,05]
03[08] 01[01,02,06,10],02[02,03,05,07]
03[09] 01[02,03,09,11],02[03,04,08,09]
03[10] 01[00,03,04,10],02[04,06,07,10]
03[11] 01[01,04,07,11],02[05,06,09,11]
36 행이 선택되었습니다.
아래 쿼리는 JSON 형식으로 ASM Partner Disk를 조회합니다. 참고로 ASM 인스턴스에서 XML 타입을 사용하면 "ORA-04043: object SYS.XMLTYPE does not exist" 에러가 발생합니다.
-- 2
SELECT JSON_OBJECT (KEY 'cell' VALUE cell
, KEY 'disk' VALUE disk
, KEY 'partner' VALUE JSON_ARRAYAGG (JSON_OBJECT (KEY 'cell' VALUE partner_cell, KEY 'disk' VALUE partner_disk)
ORDER BY number_kfdpartner)) AS partner_disk
FROM (SELECT LPAD (CEIL ((disk + 1) / 12), 2, '0') AS cell
, LPAD (MOD (disk, 12), 2, '0') AS disk
, LPAD (CEIL ((number_kfdpartner + 1) / 12), 2, '0') as partner_cell
, LPAD (MOD (number_kfdpartner, 12), 2, '0') AS partner_disk
, number_kfdpartner
FROM v$asm_disk a
, x$kfdpartner b
WHERE a.name LIKE 'DATA%'
AND b.number_kfdpartner = a.disk_number
AND b.grp = 1)
GROUP BY cell
, disk
ORDER BY cell
, disk;
PARTNER_DISK
-------------------------
{
"cell": "01",
"disk": "00",
"partner": [
{
"cell": "02",
"disk": "00"
},
{
"cell": "02",
"disk": "02"
},
{
"cell": "02",
"disk": "05"
},
{
"cell": "02",
"disk": "06"
},
{
"cell": "03",
"disk": "00"
},
{
"cell": "03",
"disk": "06"
},
{
"cell": "03",
"disk": "07"
},
{
"cell": "03",
"disk": "10"
}
]
}
...
36 행이 선택되었습니다.