227 lines
9.8 KiB
MySQL
227 lines
9.8 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata Cell Configuration
|
|
* Parameters : None
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------------
|
|
* 05-Mar-14 Vishal Gupta Added server mode to exadata model translation
|
|
* 20-Feb-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
PROMPT
|
|
PROMPT **************************************************
|
|
PROMPT * E X A D A T A C E L L C O N F I G
|
|
PROMPT **************************************************
|
|
|
|
|
|
COLUMN cell_path HEADING "CellPath" FORMAT a15
|
|
COLUMN cellname HEADING "CellName" FORMAT a18
|
|
COLUMN exa_model HEADING "Exa|Model" FORMAT a5
|
|
COLUMN makeModel HEADING "MakeModel" FORMAT a30
|
|
COLUMN upTime HEADING "UpTime" FORMAT a15
|
|
COLUMN cellVersion HEADING "CellVersion" FORMAT a35
|
|
COLUMN kernelVersion HEADING "KernelVersion" FORMAT a35
|
|
COLUMN releaseVersion HEADING "Release|Version" FORMAT a20
|
|
COLUMN releaseTrackingBug HEADING "Release|Tracking|Bug" FORMAT a20
|
|
COLUMN flashCacheMode HEADING "FlashCache|Mode" FORMAT a12
|
|
COLUMN flashCacheCompress HEADING "FlashCache|Compression" FORMAT a12
|
|
|
|
|
|
SELECT cell.name cellname
|
|
, CASE
|
|
WHEN cell.makeModel LIKE '%X4275%' THEN 'V2'
|
|
WHEN cell.makeModel LIKE '%X4270 M2%' THEN 'X2-2'
|
|
WHEN cell.makeModel LIKE '%X4270 M3%' THEN 'X3-2'
|
|
WHEN cell.makeModel LIKE '%X4270 M4%' THEN 'X4-2'
|
|
WHEN cell.makeModel LIKE '%X5-2%' THEN 'X5-2'
|
|
ELSE ''
|
|
END exa_model
|
|
, REPLACE(REPLACE(REPLACE(REPLACE(cell.makeModel
|
|
,'Oracle Corporation SUN FIRE ')
|
|
,'SUN MICROSYSTEMS SUN FIRE ')
|
|
,'Oracle Corporation ')
|
|
,'ORACLE SERVER ') makeModel
|
|
, cell.cellVersion
|
|
, cell.kernelVersion
|
|
, cell.releaseVersion
|
|
, cell.releaseTrackingBug
|
|
, cell.flashCacheMode
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output/cell' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, makeModel VARCHAR2(100) PATH 'makeModel'
|
|
, cellVersion VARCHAR2(100) PATH 'cellVersion'
|
|
, kernelVersion VARCHAR2(100) PATH 'kernelVersion'
|
|
, releaseVersion VARCHAR2(100) PATH 'releaseVersion'
|
|
, releaseTrackingBug VARCHAR2(100) PATH 'releaseTrackingBug'
|
|
, flashCacheMode VARCHAR2(100) PATH 'flashCacheMode'
|
|
, flashCacheCompress VARCHAR2(100) PATH 'flashCacheCompress'
|
|
) cell
|
|
WHERE cc.conftype = 'CELL'
|
|
ORDER BY cellname
|
|
;
|
|
|
|
|
|
--------------------------------
|
|
-- IORM Details
|
|
--------------------------------
|
|
COLUMN name HEADING "IORM Name" FORMAT a25
|
|
COLUMN catPlan HEADING "Cat Plan" FORMAT a15
|
|
COLUMN dbPlan HEADING "dbPlan" FORMAT a15
|
|
COLUMN objective HEADING "Objective" FORMAT a10
|
|
COLUMN status HEADING "IORM Status" FORMAT a12
|
|
|
|
SELECT clioutput.cellname
|
|
, iorm.name
|
|
, iorm.status
|
|
, iorm.catPlan
|
|
, iorm.dbPlan
|
|
, iorm.objective
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
cellname VARCHAR2(100) PATH 'context/@cell'
|
|
, interdatabaseplan XMLTYPE PATH 'interdatabaseplan'
|
|
) clioutput
|
|
, XMLTable('/interdatabaseplan' PASSING clioutput.interdatabaseplan
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, catPlan VARCHAR2(100) PATH 'catPlan'
|
|
, dbPlan VARCHAR2(100) PATH 'dbPlan'
|
|
, objective VARCHAR2(100) PATH 'objective'
|
|
, status VARCHAR2(100) PATH 'status'
|
|
) iorm
|
|
WHERE cc.conftype = 'IORM'
|
|
ORDER BY clioutput.cellname
|
|
;
|
|
|
|
|
|
--------------------------------
|
|
-- Hardware Details
|
|
--------------------------------
|
|
COLUMN id HEADING "System Id" FORMAT a12
|
|
COLUMN cpuCount HEADING "CPU|(#)" FORMAT a5
|
|
COLUMN memoryGB HEADING "Mem(GB)" FORMAT a3
|
|
COLUMN fanCount HEADING "Fan|(#)" FORMAT a5
|
|
COLUMN powerCount HEADING "Power|Supply|(#)" FORMAT a6
|
|
COLUMN bbuTempThreshold HEADING "Battery|Temp|Thresh" FORMAT a7
|
|
COLUMN bbuChargeThreshold HEADING "Battery|Charge|Thresh" FORMAT a7
|
|
COLUMN bmcType HEADING "Battery|Type" FORMAT a5
|
|
COLUMN interconnectCount HEADING "Inter|Connect|(#)" FORMAT a5
|
|
COLUMN interconnect1 HEADING "Inter|Connect1" FORMAT a8
|
|
COLUMN ipaddress1 HEADING "IPAddress1" FORMAT a17
|
|
COLUMN interconnect2 HEADING "Inter|Connect2" FORMAT a8
|
|
COLUMN ipaddress2 HEADING "IPAddress2" FORMAT a17
|
|
COLUMN diagHistoryDays HEADING "Diag|History|Days" FORMAT a7
|
|
COLUMN metricHistoryDays HEADING "Metric|History|Days" FORMAT a7
|
|
COLUMN notificationMethod HEADING "Notif|Method" FORMAT a10
|
|
COLUMN notificationPolicy HEADING "Notif|Policy" FORMAT a30
|
|
|
|
|
|
|
|
SELECT cell.name cellname
|
|
, cell.id
|
|
, cell.cpuCount
|
|
, cell.memoryGB
|
|
, cell.fanCount
|
|
, cell.powerCount
|
|
, cell.bbuTempThreshold
|
|
, cell.bbuChargeThreshold
|
|
, cell.bmcType
|
|
, cell.interconnectCount
|
|
, cell.interconnect1
|
|
, cell.ipaddress1
|
|
, cell.interconnect2
|
|
, cell.ipaddress2
|
|
, cell.diagHistoryDays
|
|
, cell.metricHistoryDays
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output/cell' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, id VARCHAR2(100) PATH 'id'
|
|
, cpuCount VARCHAR2(100) PATH 'cpuCount'
|
|
, memoryGB VARCHAR2(100) PATH 'memoryGB'
|
|
, fanCount VARCHAR2(100) PATH 'fanCount'
|
|
, powerCount VARCHAR2(100) PATH 'powerCount'
|
|
, bbuTempThreshold VARCHAR2(100) PATH 'bbuTempThreshold'
|
|
, bbuChargeThreshold VARCHAR2(100) PATH 'bbuChargeThreshold'
|
|
, bmcType VARCHAR2(100) PATH 'bmcType'
|
|
, interconnectCount VARCHAR2(100) PATH 'interconnectCount'
|
|
, interconnect1 VARCHAR2(100) PATH 'interconnect1'
|
|
, ipaddress1 VARCHAR2(100) PATH 'ipaddress1'
|
|
, interconnect2 VARCHAR2(100) PATH 'interconnect2'
|
|
, ipaddress2 VARCHAR2(100) PATH 'ipaddress2'
|
|
, diagHistoryDays VARCHAR2(100) PATH 'diagHistoryDays'
|
|
, metricHistoryDays VARCHAR2(100) PATH 'metricHistoryDays'
|
|
) cell
|
|
WHERE cc.conftype = 'CELL'
|
|
ORDER BY cellname
|
|
;
|
|
|
|
--------------------------------
|
|
-- Alert Notification Config
|
|
--------------------------------
|
|
COLUMN smtpUseSSL HEADING "smtpUseSSL" FORMAT a10
|
|
COLUMN smtpServer HEADING "smtpServer" FORMAT a10
|
|
COLUMN smtpPort HEADING "SMTP|Port" FORMAT a5
|
|
COLUMN smtpFrom HEADING "SMTP From" FORMAT a25
|
|
COLUMN smtpFromAddr HEADING "smtpFromAddr" FORMAT a35
|
|
|
|
|
|
SELECT cell.name cellname
|
|
, cell.smtpUseSSL
|
|
, cell.smtpServer
|
|
, cell.smtpPort
|
|
, cell.notificationMethod
|
|
, cell.notificationPolicy
|
|
, cell.smtpFrom
|
|
, cell.smtpFromAddr
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output/cell' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, notificationMethod VARCHAR2(100) PATH 'notificationMethod'
|
|
, notificationPolicy VARCHAR2(100) PATH 'notificationPolicy'
|
|
, smtpFrom VARCHAR2(100) PATH 'smtpFrom'
|
|
, smtpFromAddr VARCHAR2(100) PATH 'smtpFromAddr'
|
|
, smtpPort VARCHAR2(100) PATH 'smtpPort'
|
|
, smtpServer VARCHAR2(100) PATH 'smtpServer'
|
|
, smtpUseSSL VARCHAR2(100) PATH 'smtpUseSSL'
|
|
) cell
|
|
WHERE cc.conftype = 'CELL'
|
|
ORDER BY cellname
|
|
;
|
|
|
|
|
|
--------------------------------
|
|
-- Alert Notification Receiver
|
|
--------------------------------
|
|
COLUMN smtpToAddr HEADING "smtpToAddr" FORMAT a50
|
|
COLUMN snmpSubscriber HEADING "snmpSubscriber" FORMAT a90
|
|
|
|
SELECT cell.name cellname
|
|
, REPLACE(cell.smtpToAddr,',', ',' || chr(10)) smtpToAddr
|
|
, snmpSubscriber snmpSubscriber
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output/cell' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, smtpToAddr VARCHAR2(500) PATH 'smtpToAddr'
|
|
, snmpSubscriber VARCHAR2(500) PATH 'snmpSubscriber'
|
|
) cell
|
|
WHERE cc.conftype = 'CELL'
|
|
ORDER BY cellname
|
|
;
|
|
|
|
|
|
@@footer |