Recently, we encountered an issue in our OEM while trying to monitor the EXACC ASM Cluster. This is my EXACC cluster configuration in OCI.
The OEM displayed the cluster status as down, despite all configurations being correct. The error was as follows:
"failed to connect: java.sql.sqlrecoverableexception: listener refused the connection with the following error: ora-12514, tns:listener does not currently know of service requested in connect descriptor"
We created an SR with Oracle, and they recommended that we create a new listener on a new port and use it to monitor the EXACC ASM Cluster.
Since the listener.ora file is under the Grid user, we need to connect as the Grid user to add a second listener. I added it with the name ASM_LISTENER.
Note: For the host, I used the VIP for each node in the EXACC cluster.
ON NODE1 (exacc2db1):
[grid@exacc2db1 ~]$ cd $ORACLE_HOME/network/admin [grid@ exacc2db1 admin]$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.11)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ASM_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.11)(PORT = 1531)) ) )
ON NODE2 (exacc2db2):
[grid@exacc2db1 ~]$ cd $ORACLE_HOME/network/admin [grid@ exacc2db2 admin]$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.13)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ASM_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.13)(PORT = 1531)) ) )
NOTE: First, to prevent any conflicts, unset these two parameters with SID='*'
[grid@ exacc2db1 admin]$ sqlplus / as sysasm SQL> ALTER SYSTEM RESET LOCAL_LISTENER SCOPE = BOTH sid='*'; SQL> ALTER SYSTEM RESET REMOTE_LISTENER SCOPE = BOTH sid='*';
Now, we can set these parameters with SID='+ASM1' and SID='+ASM2' on one node and restart the Cluster and ASM instance on both nodes.
ON NODE1 (exacc2db1):
SQL> ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.11)(PORT=1521))", "(ADDRESS=(PROTOCOL=TCPS)(HOST=10.17.6.31)(PORT=2484))", "(ADDRESS=(PROTOCOL=TCP)(HOST=10.17.6.31)(PORT=1531))" SCOPE=BOTH sid='+ASM1'; SQL> ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.13)(PORT=1521))", "(ADDRESS=(PROTOCOL=TCPS)(HOST=10.17.6.33)(PORT=2484))", "(ADDRESS=(PROTOCOL=TCP)(HOST=10.17.6.33)(PORT=1531))" SCOPE=BOTH sid='+ASM2'; SQL> ALTER SYSTEM SET REMOTE_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.11)(PORT=1531))' SCOPE=BOTH sid='+ASM1'; SQL> ALTER SYSTEM SET REMOTE_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.13)(PORT=1531))' SCOPE=BOTH sid='+ASM2'; SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=1 0.10.1.11)(PORT=1521)), (ADDRE SS=(PROTOCOL=TCPS)(HOST=10.10. 1.11)(PORT=2484)), (ADDRESS=(P ROTOCOL=TCP)(HOST=10.10.1.11)( PORT=1531)
Restart the Cluster and ASM instance on NODE1
[opc@exacc2db1 ~]$ sudo su - [root@exacc2db1 ~]# cd /u01/app/23.0.0.0/grid/bin [root@exacc2db1 bin]# ./crsctl stop cluster -n exacc2db1 [root@exacc2db1 bin]# ./crsctl start cluster -n exacc2db1 [opc@exacc2db1 ~]$ sudo su - grid [grid@exacc2db1 ~]$ sqlplus / as sysasm SQL> ALTER SYSTEM REGISTER; System altered. SQL> Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.6.0.24.10
We should add both LISTENER and ASM_LISTENER to the ASM configuration using the srvctl command.
[grid@exacc2db1 ~]$ srvctl modify asm -l "LISTENER,ASM_LISTENER" PRKO-2207 : Warning:-listener option has been deprecated and will be ignored. PRKF-1125 : multiple values specified for the single value option "listener": LISTENER,ASM_LISTENER [grid@exacc2db1 ~]$ srvctl config asm ASM home: Password file: +DATAC3/orapwASM Backup of Password file: +DATAC3/orapwASM_backup ASM listener: LISTENER,ASM_LISTENER ASM instance count: ALL Cluster ASM listener: ASMNET1LSNR_ASM [grid@exacc2db1 ~]$ srvctl stop asm -n exacc2db1 [grid@exacc2db1 ~]$ srvctl start asm -n exacc2db1
Check the ASM_LISTENER status on the first node
ON NODE2 (exacc2db2):
[opc@exacc2db2 ~]$ sudo su - [root@exacc2db2 ~]# cd /u01/app/23.0.0.0/grid/bin [root@exacc2db2 bin]# ./crsctl stop cluster -n exacc2db2 [root@exacc2db2 bin]# ./crsctl start cluster -n exacc2db2 [opc@exacc2db2 ~]$ sudo su - grid [grid@exacc2db2 ~]$ sqlplus / as sysasm SQL> ALTER SYSTEM REGISTER; System altered. SQL> Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.6.0.24.10 [grid@exacc2db2 ~]$ srvctl stop asm -n exacc2db2 [grid@exacc2db2 ~]$ srvctl start asm -n exacc2db2
Check the ASM_LISTENER status on the second node
NOTE: To ensure that changes to the ASM instance's local_listener do not affect CDB instances, configure the local_listener in the CDBs first, followed by the ASM instances.
For example, in CDBs, I only want to have listener in my instance and do not want to use ASM_LISTENER.
[opc@exacc2db1 ~]$ sudo su - oracle [root@exacc2db1 ~]# cd /u01/app/23.0.0.0/grid/bin [oracle@exacc2db1 ~]$ . MYCDB.env [oracle@exacc2db1 ~]$ sqlplus / as sysdba SQL> ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.11)(PORT=1521))", "(ADDRESS=(PROTOCOL=TCPS)(HOST=10.10.1.11)(PORT=2484))" SCOPE=BOTH sid='MYCDB1'; SQL> ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.13)(PORT=1521))", "(ADDRESS=(PROTOCOL=TCPS)(HOST=10.10.1.13)(PORT=2484))" SCOPE=BOTH sid='MYCDB2'; System altered. System altered. SQL> Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.6.0.24.10 [oracle@exacc2db1 ~]$ srvctl stop database -d MYCDB [oracle@exacc2db1 ~]$ srvctl start database -d MYCDB
First, go to OEM → Targets → All Targets.
Then, in the search bar, type '+ASM' and delete the related ASM cluster (Right-click on the ASM cluster name → Target Setup → Remove Target). This will also remove the associated ASM instance.
First, go to OEM → Setup → Add Target → Add Targets Manually
Click Add Using Guided Process button
Select Oracle Database, Far Sync Instance, Listener and Automatic Storage Management and click ADD
Click the search button and type for example the first instance name
Select the Third option and click Next
On this page, enter the user 'asmsnmp' and its password (or any user with SYSASM or SYSDBA privileges). Then, click 'Next,' and on the following page, click 'Save'.
Now, if you go to OEM → Targets → All Targets and search for '+ASM', you will see that the ASM cluster and its ASM instances have been added to OEM, and their status is up and running.