Hello,
I am trying to get SCAN listener service name or SID from OEM repository. I used following query to get to SCAN listeners but did not see service name or SID property.
SELECT DISTINCT tp.*
FROM MGMT$TARGET t
INNER JOIN [dbo].MGMT$TARGET_PROPERTIES tp on t.TARGET_GUID = tp.TARGET_GUID
WHERE t.TARGET_TYPE in ('oracle_listener')
and tp.TARGET_GUID in (
SELECT tpr.TARGET_GUID
FROM MGMT$TARGET_PROPERTIES tpr
WHERE tpr.PROPERTY_NAME = 'lsnr_type' and tpr.PROPERTY_VALUE = 'SCAN'
)
The target type of 'rac_database' does have SID ServiceName property. I tried to link the records by using host name but that did not work. I used following query:
SELECT DISTINCT tpr2.*
FROM MGMT$TARGET t2
INNER JOIN MGMT$TARGET_PROPERTIES tpr2 on t2.TARGET_GUID = tpr2.TARGET_GUID
WHERE
tpr2.TARGET_TYPE = 'rac_database'
AND tpr2.PROPERTY_VALUE IN
(
SELECT DISTINCT tp.PROPERTY_VALUE
FROM MGMT$TARGET t
INNER JOIN MGMT$TARGET_PROPERTIES tp on t.TARGET_GUID = tp.TARGET_GUID
WHERE t.TARGET_TYPE in ('oracle_listener')
and tp.TARGET_GUID in (
SELECT tpr.TARGET_GUID
FROM MGMT$TARGET_PROPERTIES tpr
WHERE tpr.PROPERTY_NAME = 'lsnr_type' and tpr.PROPERTY_VALUE = 'SCAN'
)
AND PROPERTY_NAME = 'Machine'
)
AND PROPERTY_NAME = 'MachineName'
I tried to link them using target name instead of host name but that didn't work either.
Is there a way to get SID and ServiceName for SCAN listener?
Thanks,
Mihir