search

ikzelf / zbxdb

Zabbix database monitoring, the easy and extendable way
GNU General Public License v3.0
94 stars 45 forks source link

Connection fails to DG database #93

Closed thimself closed 2 years ago

thimself commented 2 years ago

Hi,

I have an issue while connecting to a database which is using Data Guard and is a PDB database. In this configuration i am trying to connect to the PDB and not to the CDB, is this possible with this setup? I am able to connect with sqlplus, but when trying to use failover string in monitoring, connection fails.

Scenario is that i want to monitor the PDB database which is in a failover configuration and with this i would like to connect to the active node each time since the service name switched sides with the active node.

String: db_url = (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=server2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)(SERVER=DEDICATED)))

Error: 2021-12-16 10:22:09,171_dbconnections.oracle_40_connect failed 1017 with ORA-01017: invalid username/password; logon denied 2021-12-16 10:22:09,172main40_(1.1)connection error: [1017] ORA-01017: invalid username/password; logon denied for C##USERNAME@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=server2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)(SERVER=DEDICATED)))

Sqlplus: sqlplus C##USERNAME@'(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=server2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)(SERVER=DEDICATED)))'

I was using template: zbxdb.oracle-cluster1-ASM.cfg, is this the correct one for this usecase?

thimself commented 2 years ago

I might have solved this, it seems to help to open a support request and every time after that i am able to spot the problem. Template had "role = sysdba", changed that to normal and it monitoring connected right after that. Will have to test and see that i am able to monitor everything what is needed since database is in DG configuration.

ikzelf commented 2 years ago

Hi, good that you found your way. Yes, when you always want to connect to the primary instance, it has to connect to a service that is only available on the primary instance. For the SYSDBA role you need to be granted explicitly the SYSDBA role what is needed for ASM instances. You made a nice config. An other one could have been where you monitored both databases independently so you can track the recovery. But what you found yourself is always better. ;-) Have fun!

BTW: since you always monitor the primary, it will just monitor as regular primary databases that are standalone. If you feel things are missing: shout!