Oracle Zabbix by ODBC

 

 

Oracle Zabbix by ODBC

Oracle Database, version 12c2, 18c, 19c

·       Create an Oracle DB user for monitoring:

·       Create User on oracle db user for monitoring on target database

·       Install Oracle Client on Zabbix mysql database server

·       Setup environment  variables on bash_profile

·       Create tnsnames.ora file with sample connect descriptor

·       Add entry on odbc.ini

·       Test using i sql connection

·       Add host entry & select template options  

·       Add Macro Entry on Zabbix Console

 

https://www.zabbix.com/integrations/oracle

 

STEP 1:

 

CREATE USER zabbix_mon IDENTIFIED BY <PASSWORD>;

-- Grant access to the zabbix_mon user.

GRANT CONNECT, CREATE SESSION TO zabbix_mon;

GRANT SELECT ON v$instance TO zabbix_mon;

GRANT SELECT ON v$database TO zabbix_mon;

GRANT SELECT ON v$sysmetric TO zabbix_mon;

GRANT SELECT ON v$system_parameter TO zabbix_mon;

GRANT SELECT ON v$session TO zabbix_mon;

GRANT SELECT ON v$recovery_file_dest TO zabbix_mon;

GRANT SELECT ON v$active_session_history TO zabbix_mon;

GRANT SELECT ON v$osstat TO zabbix_mon;

GRANT SELECT ON v$restore_point TO zabbix_mon;

GRANT SELECT ON v$process TO zabbix_mon;

GRANT SELECT ON v$datafile TO zabbix_mon;

GRANT SELECT ON v$pgastat TO zabbix_mon;

GRANT SELECT ON v$sgastat TO zabbix_mon;

GRANT SELECT ON v$log TO zabbix_mon;

GRANT SELECT ON v$archive_dest TO zabbix_mon;

GRANT SELECT ON v$asm_diskgroup TO zabbix_mon;

GRANT SELECT ON sys.dba_data_files TO zabbix_mon;

GRANT SELECT ON DBA_TABLESPACES TO zabbix_mon;

GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix_mon;

GRANT SELECT ON DBA_USERS TO zabbix_mon;

 

 

 

 

 

STEP 2:

 

Install Oracle Client on Zabbix mysql Repository database server

 

 

STEP 3 :

 

Set Environment variable

 

export ORACLE_HOME=/usr/lib/oracle/12.2/client64

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:/usr/lib:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

 

 

STEP 4 :

 

Add tnsnames.ora entry on oracle client

 

cat =/usr/lib/oracle/12.2/client64/network/admin/tnsnames.ora

 

 

Add entry on tnsnames.ora file

 

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL.EXAMPLE.COM)(PORT = 1523))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

 

 

STEP 4 :

 

 

Add Value in /cat /odbc.ini file

 

 

[root@0209RUPAV006TVS etc]# cat odbc.ini

[db12c]

Driver = Oracle12c

Driver =/usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1

Servername = 10.33.104.254:1523/ ORCL

 

Database = ORCL

Port = 1523

UserID = zabbix_mon

Password = zabbix_mon

DatabaseCharacterSet=AL16UTF16

 

 

STEP 5:

 

Test on sql console

 

[root@test etc]# isql -v ORCL

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

 

SQL> select name from v$database;

+----------+

| NAME     |

+----------+

| ORCL   |

+----------+

SQLRowCount returns -1

1 rows fetched

 

 

 

STEP 6 :

 

 

Login Zabbix Console   : username/password

 

(Entry is added on mysql database repository server)

 

 

Graphical user interface, text, application

Description automatically generated

 

Graphical user interface, text, application, email

Description automatically generated

 

STEP 7 :

 

 

Go to configuration setting  and  select host option then create host  

 

 

 

 

 

 

 

 

 

 

 

 

 

·                Mention host group for monitoring  

 

Graphical user interface, application

Description automatically generated

 

 

·                Select Template

 

Graphical user interface, application

Description automatically generated

 

 

 

 

 

 

 

 

 

·                Select oracle ODBC Template

Graphical user interface, application

Description automatically generated

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·                Provide IP address DNS and Proxy server details

Graphical user interface, text, application, email

Description automatically generated

 

 

 

Graphical user interface, text, application

Description automatically generated

 

 

 

 

 

 

 

 

·                Go to Macro Entry and mentioned details same as in odbc.ini file

Graphical user interface, application

Description automatically generated

 

·                Advanced Macro Entry (optional)

 

Graphical user interface, application

Description automatically generated

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·                Template Details

Graphical user interface, text, application, email

Description automatically generated

 

 

·                Zabbix Graph Monitoring

 

Graphical user interface, text, application, email

Description automatically generated

 

 

A screenshot of a computer

Description automatically generated

 

 

 

 

 

 

 

Graphical user interface, chart, application

Description automatically generated

 

Graphical user interface, text, application, email

Description automatically generated

Table

Description automatically generated Table

Description automatically generated

PLM Teamcenter : Activity Tasks

 

Teamcenter repeated Activity

  

·       Update volume  path set

·       Deletion of PLM Teamcenter users at object level on TCPRODMANAGER schema and object name ASSIGNME_1773957227_113294481

·       Deletion of DBA group on infodba schema on ppom_group

·       IR#9912325 (P2) A Solution Proposal to the Incident Report Provided- ImanNextId counter is inconsistent

 

 

 

 

·       Update volume  path set

 

 

select * from PIMANVOLUME

 

 

1.         MAIN_Volume -  Unix path Name “/u01/SMRProdTC12/MAIN_Volume”

2.         Volume – Unix Path Name ““/u01/SMRProdTC12/volume”

 

UPDATE infodba.PIMANVOLUME  SET PUNIX_PATH_NAME ='/u01/SMRProdTC12/MAIN_Volume' WHERE puid='RsclYsjlaDPwJD';

 

UPDATE infodba.PIMANVOLUME  SET PUNIX_PATH_NAME ='/u01/SMRProdTC12/volume' WHERE puid='A4UhkecDaDPwJD';

commit;

 

  ·       Deletion of PLM Teamcenter users at object level on TCPRODMANAGER schema and object name ASSIGNME_1773957227_113294481

 

 Check Application user based on ESESSION_ID provided by client or application team

 

select * from TCPRODMANAGER.ASSIGNME_1773957227_113294481 where ESESSION_ID like '%cwezel%';

 

 

 

delete from TCPRODMANAGER.ASSIGNME_1773957227_113294481 where ESESSION_ID='cwezel~PSTG031.SharedSession';

 

commit;

   

·                Deletion of DBA group on infodba schema on ppom_group

 

 As per request of application team deletion and creation of DBA group

 Insert into infodba.ppom_group (PUID,PNAME,PPRIVILEGE,RPARENTU,RPARENTC,PGROUP_DATA_SOURCE,PGROUP_LAST_SYNC_DATE,VLA_12_6) values ('AvUl3RrgaDPwJD','DBA',0,'AAAAAAAAAAAAAA',-1,0,null,1);

 

 select * from infodba.ppom_group where puid='AvUl3RrgaDPwJD'

 select * from infodba.ppom_group where pname='DBA'

  Backup Name before activity : expdp-SMRPRODB22021-08-08_13-44-56.dmp

 

 Backup permanent location : /u04/MasterDB/expdp/onrequest_bkp

 

 

·                IR#9912325 (P2) A Solution Proposal to the Incident Report Provided- ImanNextId counter is inconsistent

 

Issue with the Database sequence default cache size of 20.

 

To resolve this issue on infodba schema only 

Create "seq_nocache.sql" file with following content:

 

/*Start of File*/

SET HEADING OFF;

SET ECHO OFF;

SET VERIFY OFF;

SET FEEDBACK OFF;

SET WRAP OFF;

SET LINESIZE 300;

SET SQLP "REM SQL"

 

spool update_sequences.sql

SELECT 'ALTER SEQUENCE ' || SEQUENCE_NAME || ' NOCACHE;' FROM USER_SEQUENCES;

spool off

 

@update_sequences.sql

 

SELECT 'All sequences updated.' FROM DUAL;

SET SQLP "SQL>"

/* End of File */

 

It is an SQL script which should be run from sqlplus logging in as the TC user. It will do the following:

 

Get a list of all sequences in that schema.

 

Construct the commands to alter them and write this to a temporary file called update_sequences.sql. This is created in the current working directory.

 

Update all sequences to NOCACHE.

 

To verify this has occurred you can run the following SQL:

select SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER from user_sequences;

 

The CACHE_SIZE column will have a value of 0 after the script has been run.

 

To run the script, please do the following:

Save the attached script and change into the directory containing this script.

Start sqlplus and log in as the TC user.

Run the following:

 

 

@seq_nocache.sql

 

Please perform above steps in Test Environment first.

 

 

 

DDL -Metadata commands and example

DDL -Metadata commands and example

SELECT 'CREATE PUBLIC SYNONYM ' || synonym_name || ' FOR '|| table_owner || '.' || table_name || ';' FROM dba_synonyms WHERE TABLE_OWNER='<owner_name>';

 SELECT text FROM user_source WHERE name = <PACKAGE_name>;

SELECT DBMS_METADATA.GET_DDL('PACKAGE','<PACKAGE_name>','<schema_name>') FROM DUAL;

SELECT dbms_metadata.get_ddl('FUNCTION','<FUNCTION_name>','<schema_name>') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL( 'OBJECT_GRANT','<schema_name>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;

SELECT dbms_metadata.get_ddl('PROCEDURE','<proc_name>','schema') FROM dual;

PROCEDURE

select dbms_metadata.get_ddl('PROCEDURE', '<proc_name>') from dual;

SET pages 9999 LONG 1000000 LONGCHUNKSIZE 200000 

PAGESIZE 0 LINESIZE 10000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SQL> 

SQL> set line 32767

SQL> 

SQL> 

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','PKG_IMPAIR','owner') FROM DUAL;

SQL> select DBMS_Metadata.Get_DDL('TRIGGER',t.trigger_name,t.owner) From DBA_Triggers t Where TRIGGER_NAME ='<>';

db start up time

SET LINES 200

SET PAGES 999

COLUMN INSTANCE_NAME FOR A20

SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"

FROM V$INSTANCE;

SELECT dbms_metadata.get_ddl('ROLE','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','YOUR_ROLE_NAME') from dual;