Listener does not currently know of SID given in connect descriptor.
After upgrading Oracle grid infrastructure from 11.2.0.4 to 19c; we have detected that +ASM and +APX(ASM Proxy) instances used for advm are not registered with listener and OEM target status shows down.
They were only accessible from private-network(192.168.*.*).We have checked configuration attributes for asm instance with srvctl command.
| * Problematic config | |
| [oracle@exadb01 ~]$ srvctl config asm | |
| ASM home: <CRS home> | |
| Password file: +DATA/orapwASM | |
| Backup of Password file: +RECO/orapwasm_backup | |
| ASM listener: —> This attribute should not be empty. | |
| ASM instance count: ALL | |
| Cluster ASM listener: ASMNET1LSNR_ASM | |
| * How it should be. (Correct one) | |
| [oracle@s001db01 dbs]$ srvctl config asm | |
| ASM home: <CRS home> | |
| Password file: +DATA/orapwasm | |
| Backup of Password file: +RECO/orapwasm_backup | |
| ASM listener: LISTENER | |
| ASM instance count: ALL | |
| Cluster ASM listener: ASMNET1LSNR_ASM |
The easy and first step to take was to set it with “srvctl modify” command. Oops something went wrong and that is not possible.
| [oracle@exadb03 ~]$ srvctl modify asm -listener LISTENER | |
| PRKO-2207 : Warning:-listener option has been deprecated and will be ignored. | |
| [oracle@exadb01 trace]$ srvctl config asm -proxy -detail | |
| ASM home: <CRS home> | |
| ADVM proxy is enabled | |
| ADVM proxy is individually enabled on nodes: | |
| ADVM proxy is individually disabled on nodes: | |
| Spfile: |
It looks like this command is not working anymore, searched docs.oracle and checked oracle support but no easy way out. Checked asm proxy instance local_listener parameter and it was not set. According to the “Real Application Clusters Installation Guide for Linux and UNIX” , If you do not set LOCAL_LISTENER, then the Database Agent process automatically updates the database associated with the local listener in the Grid home, even when the ports or IP address of that listener are changed.
We found the warnings below in the asm instance’s alert.log.
| … | |
| 01-APR-2022 10:57:20 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))(SID=+APX1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=40650)) * establish * +APX1 * 12505 | |
| TNS-12505: TNS:listener does not currently know of SID given in connect descriptor | |
| 2022-04-01T10:57:23.208266+03:00 | |
| … |
Bug 30594507(Internal) is related with our issue. According to the bug, start_dependencies are not set for resourcegroup “asmgroup”.
In a clean installed and correctly configured cluster, start_dependencies are set as below. I have also shown the state for our problematic cluster.
| * Correct one | |
| [oracle@s001db01 u02]$ crsctl stat resourcegroup ora.asmgroup -p | |
| NAME=ora.asmgroup | |
| TYPE=ora.asmgroup.gtype | |
| … | |
| START_DEPENDENCIES=weak(global:ora.gns,ora.LISTENER.lsnr) dispersion:active(site:type:ora.asmgroup.gtype) | |
| START_DEPENDENCIES_RTE_INTERNAL=<xml><Arg name="crslistener" type="ResList">ora.LISTENER.lsnr</Arg></xml> | |
| START_DEPENDENCIES_TEMPLATE=<xml>weak(global:ora.gns,<ResList>{crslistener}</ResList>) dispersion:active(site:type:ora.asmgroup.gtype)</xml> | |
| … | |
| * Problematic one | |
| [oracle@exadb01 admin]$ crsctl stat resourcegroup ora.asmgroup -p | |
| NAME=ora.asmgroup | |
| TYPE=ora.asmgroup.gtype | |
| … | |
| START_DEPENDENCIES= –> EMPTY | |
| START_DEPENDENCIES_RTE_INTERNAL= –> EMPTY | |
| START_DEPENDENCIES_TEMPLATE=<xml>weak(global:ora.gns,<ResList>{crslistener}</ResList>) dispersion:active(site:type:ora.asmgroup.gtype)</xml> | |
| … |
We have set start_dependencies for asmgroup with the command below and +ASM and +APX instances used for advm are now registered with listener and OEM target status shows up.
| [oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "START_DEPENDENCIES='weak(global:ora.gns,ora.LISTENER.lsnr) dispersion:active(site:type:ora.asmgroup.gtype)'" -unsupported | |
| [oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "START_DEPENDENCIES_RTE_INTERNAL='<xml><Arg name=\"crslistener\" type=\"ResList\">ora.LISTENER.lsnr</Arg></xml>'" -unsupported | |
| [oracle@exadb03 ~]$ crsctl modify resourcegroup ora.asmgroup -attr "AUTO_START='always'" -unsupported |
Hope it helps.
Osman’s DBlog 
Leave your comment