can i login in oracle 10g dg with sysdba role?

[MichaelJochen]

hi,i try to login in oracle 10g dg with sysdba role. here is my config file:

[zbxdb@dhec-vm-264 etc]$ cat zbxdb.odb_2_154.cfg [zbxdb] db_url = //172.16.2.154/dhecrac username = zabbix password = db_type = oracle db_driver = cx_Oracle instance_type = rdbms role = SYSDBA out_dir = $HOME/zbxora_out hostname = orclsig2154 checks_dir = etc/zbxdb_checks site_checks = password_enc = WnNjMjAxOVphYmJpeA==

it's not a right way,here is the error messages:

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

WARNING:main:(2.21)connection error: [1031] ORA-01031: insufficient privileges for zabbix@//172.16.2.154/dhecrac

i had try it in sqlplus:

C:\Users\oracle>sqlplus zabbix/**@172.16.2.154/dhecrac as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 8月 6 18:12:37 2019

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

How can I analyze and resolve this issue? Thank you, I look forward to your reply.

[ikzelf]

Hi Michael, your config looks good. Is this all the output you got from this session? I assume that the output file in $HOME/zbxora_out/ had a line with '"zbxdb[connect,status]" 0' in it. If that is the case, one of the queries that gather connection info failed on permissions. That could be "select database_role from v$database" or "select substr(i.version,0,instr(i.version,'.')-1), s.sid, s.serial#, p.value instance_type, i.instance_name , s.username from v$instance i, v$session s, v$parameter p where s.sid = (select sid from v$mystat where rownum = 1) and p.name = 'instance_type'"

Why SYSDBA? is it a physical standby database you try to connect to? (at this moment I have no standby databases that do no use active data guard so I might have missed something here ...)

[MichaelJochen]

yes,you are right.look here:

[zbxdb@dhec-vm-264 zbxora_out]$ cat zbxdb.odb_2_154.zbx orclsig2154 "zbxdb[connect,status]" 1565091249 1031

and the full log is:

2019年 08月 06日 星期二 19:32:08 CST Starting etc/zbxdb.odb_2_154.cfg INFO:main:start python-3.6.5 zbxdb-2.01 pid=23196 Connecting ...

INFO:main:zbxdb found db_type=oracle, driver cx_Oracle; checking for driver

INFO:main:<module 'cx_Oracle' from '/home/zbxdb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/cx_Oracle.cpython-36m-x86_64-linux-gnu.so'> INFO:main:zbxdb driver cx_Oracle loaded

INFO:main:zbxdb driver drivererrors for cx_Oracle loaded

INFO:main:zbxdb dbconnections for oracle loaded

INFO:main:<module 'dbconnections.oracle' from '/home/zbxdb/zbxdb/bin/dbconnections/oracle.py'> INFO:main:<module 'drivererrors.cx_Oracle' from '/home/zbxdb/zbxdb/bin/drivererrors/cx_Oracle.py'> INFO:main:hostname in zabbix: orclsig2154 INFO:main:connect string : zabbix/**@//172.16.2.154/dhecrac

INFO:main:using sql_timeout : 60s

INFO:main:out_file : /home/zbxdb/zbxora_out/zbxdb.odb_2_154.zbx

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

WARNING:main:(1.1)connection error: [1031] ORA-01031: insufficient privileges for zabbix@//172.16.2.154/dhecrac

INFO:main:key=checks_01m.blocked ZBXDB-904: Db execution error: ORA-00904: "FINAL_BLOCKING_SESSION": invalid identifier

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

WARNING:main:(2.1)connection error: [1031] ORA-01031: insufficient privileges for zabbix@//172.16.2.154/dhecrac

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

WARNING:main:(3.1)connection error: [1031] ORA-01031: insufficient privileges for zabbix@//172.16.2.154/dhecrac

Why SYSDBA? it is a physical standby database i try to connect to.

So,should i do some change on "primary.10.cfg" file?

[MichaelJochen]

hi,i found it is ok like this:

[zbxdb@dhec-vm-264 etc]$ vi zbxdb.odb_2_154.cfg [zbxdb] db_url = //172.16.2.154/dhecrac username = sys password = db_type = oracle db_driver = cx_Oracle instance_type = rdbms role = SYSDBA out_dir = $HOME/zbxora_out hostname = orclsig2154 checks_dir = etc/zbxdb_checks site_checks = password_enc = b3JhY2xl

but only the user 'sys' can be used. even if the user 'system' can not be used.

[ikzelf]

No, the idea is that zbxdb determines the role of the database (primary or standby) and picks the checks file using the role. So, in your case it should have been etc/zbxdb_checks/oracle/standby.10.cfg. Only, zbxdb did not reach that point. cx_Oracle should be able to connect when in mount phase and even open/shutdown should work https://cx-oracle.readthedocs.io/en/latest/connection.html#Connection.startup

can you see zbxdb in v$session (select username, module from gv$session where module = 'zbxdb') ?

if it shows "zabbix zbxdb" the connect as SYSDBA failed, it it shows "sys zbxdb", the connect as SYSDBA was OK. In ASM instances I also connect as SYSDBA and that works like a charm. For now I pushed a new version of bin/dbconnections/oracle.py that should give a little more detailed logging. Can you try that?

Could it be that you are running sqlplus from 172.16.2.154 ?

[ikzelf]

Did you grant SYSDBA to zabbix?

[MichaelJochen]

yes,i did.

[MichaelJochen]

i grant sysdba to zabbix on my primary database.then i copy the passwordfile from primary db to standby db.

[ikzelf]

OK, that is correct. and the standby also runs with remote_login_passwordfile exclusive? (in all instances) sysdba should be able to be used for a 'regular' username. (system by default does not have sysdba (default only SYS has it))

[MichaelJochen]

yes,look here:

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME STATUS

---

dhecrac MOUNTED

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

---

remote_login_passwordfile string EXCLUSIVE

[ikzelf]

Your db is a v10 ..... cx_Oracle has a note about >= 10.2 for sysdba operations. Is yours a 10.2 or later?

[MichaelJochen]

yes

[ikzelf]

funny .... and what gives: 'select * from V$PWFILE_USERS;'?

[MichaelJochen]

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

---

SYS TRUE TRUE ZABBIX TRUE FALSE

[ikzelf]

Could you check with the latest version? I added some more logging in the dbconnection module that might give a hint.

[MichaelJochen]

ok,i will try it tomorrow，I got off work now.thanks,bye.

[MichaelJochen]

hi,the problem has been solved just now. The following is the solution to the entire problem： 1.when i got the ORA-01031,i did not copy the passwordfile from primary db to standby db. at that time,it shows:

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

---

SYS TRUE TRUE

SQL> grant sysdba to zabbix; grant sysdba to zabbix * 第 1 行出现错误: ORA-01109: 数据库未打开

2.then i copy the passwordfile from primary db to standby db.i got another error:

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

WARNING:main:(1.1)connection error: [1017] ORA-01017: invalid username/password; logon denied for zabbix@//172.16.2.154/dhecrac

After I checked carefully，i found the reason is that i missing a character when copying a password. Sorry,it is my fault. The problem was solved after using the correct password.

In addition,ronald,when i check with the latest version,it shows:

INFO:main:connecting to zabbix/**@//172.16.2.154/dhecrac

INFO:dbconnections.oracle:Connecting zabbix/XXX@//172.16.2.154/dhecrac as SYSDBA CRITICAL:dbconnections.oracle:connect failed 1017 with ORA-01017: invalid username/password; logon denied WARNING:main:(7.41)connection error: [1017] ORA-01017: invalid username/password; logon denied for zabbix@//172.16.2.154/dhecrac

At last,thank ronald,you are always so helpful to help me.

[ikzelf]

Thanks for the feedback. I am always happy to help.

[ikzelf]

BTW: since you are running v10 and I currently have no v10's under my fingers to be able to test the correctly: can you get me a copy of your *.10.cfg files you have? I assume you started them by copying my v11's? You could create a pull request for them, doing so, I automatically can give you the credits for it. With a little luck this could be brought back to v7 ;-)

[MichaelJochen]

ok,waite a minutes.

[MichaelJochen]

Is this okay? 10gcfg.zip

[MichaelJochen]

i got a error:

INFO:main:key=checks_01m.blocked ZBXDB-904: Db execution error: ORA-00904: "FINAL_BLOCKING_SESSION": invalid identifier

so I made some notes "#blocked: select 'blocked[topsid]', topsid||'('||blocked||')'……"

[ikzelf]

yes, thanks. Ah, looking at the alertlog modification you run in windows .... I will change that to also return on unix. v$session.final_blocking_session was introduced in v11. If you find some similar query .... keep me posted. Thanks, I will add them.

[MichaelJochen]

I got it.

[ikzelf]

What does "select platform_name from v$database;" return on windows?

[MichaelJochen]

two types,the one is:

SQL> select platform_name from v$database;

PLATFORM_NAME

Microsoft Windows IA (32-bit)

the another is:

SQL> select platform_name from v$database;

PLATFORM_NAME

Microsoft Windows x86 64-bit

[ikzelf]

OK, thanks, I will work with 'Microsoft Windows%'

[ikzelf]

does this work: select 'inst['||i.instance_name||',log]', p.value|| case when b.platformname like 'Microsoft Windows%' then '\' else '/' end || 'alert'||i.instance_name||'.log' from gv$instance i, gv$parameter p, v$database b where i.inst_id = p.inst_id and p.name = 'background_dump_dest'

?

[MichaelJochen]

[ikzelf]

looks like: yes :-D

[MichaelJochen]

Missed a backslash. it should be: "D:\ORACLE\PRODUCT\10.2.0\ADMIN\DHECRAC\BDUMP\alert_dhecrac.log"

[ikzelf]

select 'inst['||i.instance_name||',log]', p.value|| case when b.platformname like 'Microsoft Windows%' then '\' else '/' end || 'alert'||i.instance_name||'.log' from gv$instance i, gv$parameter p, v$database b where i.inst_id = p.inst_id and p.name = 'background_dump_dest'

looks like the past in GitHub killed the backslash ...

[ikzelf]

select 'inst['||i.instance_name||',log]', p.value|| case when b.platformname like 'Microsoft Windows%' then '\' else '/' end || 'alert'||i.instance_name||'.log' from gv$instance i, gv$parameter p, v$database b where i.inst_id = p.inst_id and p.name = 'background_dump_dest'

[MichaelJochen]

very good.

[ikzelf]

had to escape the backslash in this text window. Added to the repo and pushed.