ORA-00942: table or view does not exist

[aprakash]

Hi Team,

We are using oracle DB for Zabbix server.

While trying to start Zabbix server, we are getting below error
27703:20170306:084424.889 In zbx_db_get_database_type()
27703:20170306:084424.889 In DBconnect() flag:0
27703:20170306:084424.936 query without transaction detected
27703:20170306:084424.936 query [txnlev:0] [alter session set nls_numeric_characters='. ']
27703:20170306:084424.937 End of DBconnect():0
27703:20170306:084424.937 query [txnlev:0] [select * from (select userid from users) where rownum<=1]
27703:20170306:084424.939 [Z3005] query failed: [-1] ORA-00942: table or view does not exist [select * from (select userid from users) where rownum<=1]
27703:20170306:084424.939 cannot select records from "users" table
27703:20170306:084424.940 End of zbx_db_get_database_type():ZBX_DB_UNKNOWN
27703:20170306:084424.940 cannot use database "OMSALD": database is not a Zabbix database


Please let us know whether we need to create any table in DB End before start the zabbix server. If yes, please provide some more information

From ORACLE DB End, we just created schema alone

[Atsushi]

Please do not only create the schema but also register the initial data.
It is also necessary to load images.sql and data.sql.

https://www.zabbix.com/documentation/3.0/manual/appendix/install/db_scripts

[aprakash]

Followed and create all DB scripts.. still i am facing same error while

Please do not only create the schema but also register the initial data.
It is also necessary to load images.sql and data.sql.
https://www.zabbix.com/documentation...all/db_scripts

Followed and create all DB scripts.. still i am facing same error while trying to start zabbix server

21187:20170312:053507.753 In zbx_db_get_database_type()
21187:20170312:053507.753 In DBconnect() flag:0
21187:20170312:053507.771 [Z3001] connection to database '//xxxx:1521/xxx' failed: [-1]
21187:20170312:053507.771 Cannot connect to the database. Exiting...

[aprakash]

Any update ? i am still getting same issue

10577:20170330:060258.541 Starting Zabbix Server. Zabbix 3.2.3 (revision 64610).
10577:20170330:060258.549 ****** Enabled features ******
10577:20170330:060258.549 SNMP monitoring: NO
10577:20170330:060258.549 IPMI monitoring: NO
10577:20170330:060258.549 Web monitoring: YES
10577:20170330:060258.549 VMware monitoring: NO
10577:20170330:060258.549 SMTP authentication: NO
10577:20170330:060258.549 Jabber notifications: NO
10577:20170330:060258.549 Ez Texting notifications: YES
10577:20170330:060258.549 ODBC: NO
10577:20170330:060258.549 SSH2 support: NO
10577:20170330:060258.549 IPv6 support: NO
10577:20170330:060258.549 TLS support: NO
10577:20170330:060258.550 ******************************
10577:20170330:060258.550 using configuration file: /tools/appsw/oracle/zabbix/etc/zabbix_server.conf
10577:20170330:060258.550 In zbx_load_modules()
10577:20170330:060258.550 End of zbx_load_modules():SUCCEED
10577:20170330:060258.550 In init_database_cache()
10577:20170330:060258.550 In zbx_mem_create() descr:'history cache' param:'HistoryCacheSize' size:16777216
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272039993
10577:20170330:060258.550 valid user addresses: [0x7f64c5dc3170, 0x7f64c6dc2ff0] total size: 16776832
10577:20170330:060258.550 End of zbx_mem_create()
10577:20170330:060258.550 In zbx_mem_create() descr:'history index cache' param:'HistoryIndexCacheSize' size:4194304
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272072762
10577:20170330:060258.550 valid user addresses: [0x7f64c59c3180, 0x7f64c5dc2ff0] total size: 4193904
10577:20170330:060258.550 End of zbx_mem_create()
10577:20170330:060258.550 In init_trend_cache()
10577:20170330:060258.550 In zbx_mem_required_size() size:0 chunks_num:1 descr:'trend cache' param:'TrendCacheSize'
10577:20170330:060258.550 End of zbx_mem_required_size() size:422
10577:20170330:060258.550 In zbx_mem_create() descr:'trend cache' param:'TrendCacheSize' size:4194304
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272105531
10577:20170330:060258.550 valid user addresses: [0x7f64c55c3170, 0x7f64c59c2ff0] total size: 4193920
10577:20170330:060258.550 End of zbx_mem_create()
10577:20170330:060258.550 End of init_trend_cache()
10577:20170330:060258.550 End of init_database_cache()
10577:20170330:060258.550 In init_configuration_cache() size:8388608
10577:20170330:060258.550 In zbx_mem_create() descr:'configuration cache' param:'CacheSize' size:7130317
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272138300
10577:20170330:060258.550 valid user addresses: [0x7f64c4ef6170, 0x7f64c55c2cc0] total size: 7129936
10577:20170330:060258.550 End of zbx_mem_create()
10577:20170330:060258.550 In zbx_strpool_create()
10577:20170330:060258.550 In zbx_mem_create() descr:'string pool' param:'CacheSize' size:1258291
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272171069
10577:20170330:060258.550 valid user addresses: [0x7f64c4dc2168, 0x7f64c4ef5328] total size: 1257920
10577:20170330:060258.550 End of zbx_mem_create()
10577:20170330:060258.550 End of zbx_strpool_create()
10577:20170330:060258.550 End of init_configuration_cache()
10577:20170330:060258.550 In init_selfmon_collector()
10577:20170330:060258.550 init_selfmon_collector() size:7344
10577:20170330:060258.550 zbx_shmget() removing existing shm_id:272203838
10577:20170330:060258.550 End of init_selfmon_collector() collector:0x7f64c4dc0000
10577:20170330:060258.550 In zbx_vc_init()
10577:20170330:060258.551 In zbx_mem_required_size() size:0 chunks_num:1 descr:'value cache size' param:'ValueCacheSize'
10577:20170330:060258.551 End of zbx_mem_required_size() size:427
10577:20170330:060258.551 In zbx_mem_create() descr:'value cache size' param:'ValueCacheSize' size:8388608
10577:20170330:060258.551 zbx_shmget() removing existing shm_id:272236607
10577:20170330:060258.551 valid user addresses: [0x7f64c45c0170, 0x7f64c4dbfff0] total size: 8388224
10577:20170330:060258.551 End of zbx_mem_create()
10577:20170330:060258.551 End of zbx_vc_init()
10577:20170330:060258.551 In zbx_db_get_database_type()
10577:20170330:060258.551 In DBconnect() flag:0
10577:20170330:060258.582 [Z3001] connection to database '//abcda.abcd.com:1521/OMSALD' failed: [-1]
10577:20170330:060258.582 Cannot connect to the database. Exiting...

[Atsushi]

Have you set up the necessary environment variables to connect to Oracle?

https://www.zabbix.com/forum/showthread.php?t=49925

[aprakash]

Yes.. How to verify that..

Have you set up the necessary environment variables to connect to Oracle?
https://www.zabbix.com/forum/showthread.php?t=49925

when i am starting zabbix server.. getting below error

./zabbix_server: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

so i am exporting export LD_LIBRARY_PATH=/tools/utilsw/oracle/11gclient/lib

then i started the server.. After startup.. i am seeing those exception in logs

During zabbix server compilation, i used below syntax

./configure --prefix=/tools/appsw/oracle/zabbix --enable-server --enable-agent --with-oracle=yes --with-oracle-include=/tools/utilsw/oracle/11gclient/rdbms/public --with-oracle-lib=/tools/utilsw/oracle/11gclient/lib --with-libcurl


Update:
I followed below
export ORACLE_HOME=/tools/utilsw/oracle/11gclient
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
and i am seeing below logs
11235:20170330:082621.483 there is at least 1 record in "users" table
11235:20170330:082621.484 End of zbx_db_get_database_type():ZBX_DB_SERVER
11235:20170330:082621.484 In DBcheck_version()
11235:20170330:082621.484 In DBconnect() flag:0
11235:20170330:082621.504 query without transaction detected
11235:20170330:082621.504 query [txnlev:0] [alter session set nls_numeric_characters='. ']
11235:20170330:082621.504 End of DBconnect():0
11235:20170330:082621.504 query [txnlev:0] [select 1 from tab where tabtype='TABLE' and lower(tname)='dbversion']
11235:20170330:082621.517 query [txnlev:0] [select mandatory,optional from dbversion]
11235:20170330:082621.520 Cannot get the database version. Exiting ...
As per document, https://www.zabbix.com/documentation...all/db_scripts
All the scripts are created in DB server

[aprakash]

Any update on this ?

11235:20170330:082621.504 query [txnlev:0] [select 1 from tab where tabtype='TABLE' and lower(tname)='dbversion']
11235:20170330:082621.517 query [txnlev:0] [select mandatory,optional from dbversion]
11235:20170330:082621.520 Cannot get the database version. Exiting ...


when i check DB side.. i see TABLE Exists.. what else required ?
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

SQL> select 1 from tab where tabtype='TABLE' and lower(tname)='dbversion';

1
----------
1

SQL> select * from DBVERSION;

no rows selected

SQL> desc DBVERSION
Name Null? Type
----------------------------------------- -------- ----------------------------
MANDATORY NOT NULL NUMBER(10)
OPTIONAL NOT NULL NUMBER(10)

SQL> show user
USER is "ZABBIXPOC"