1. Introducing the Issue
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.
2. Implementing the Solution
2.1. Adding the ASM_Listener (second listener) to listener.ora on both nodes of the EXACC 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))
)
)
2.2. Setting the LOCAL_LISTENER and REMOTE_LISTENER in the ASM Instance
[grid@ exacc2db1 admin]$ sqlplus / as sysasm
SQL> ALTER SYSTEM RESET LOCAL_LISTENER SCOPE = BOTH sid='*';
SQL> ALTER SYSTEM RESET REMOTE_LISTENER SCOPE = BOTH sid='*';
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
[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
2.3. Remove the ASM cluster from OEM
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.
2.4. Re-add it using ASM_LISTENER and port 1531.
First, go to OEM → Setup → Add Target → Add Targets ManuallySelect 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 NextOn 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.
No comments:
Post a Comment