DX Application Performance Management

  • Private Community
 View Only
Back to Library

Configuring APM Oracle PowerPack/Extension for Oracle to use Oracle JDBC OCI driver & TNSNames Alias Syntax(enables use of Oracle RAC & Oracle Secure External Password Store) 

Jan 05, 2016 08:29 PM

Background:

  • Out of the box the Oracle PP only supports the use of the Oracle JDBC Thin driver with Thin-style Service Name Syntax (@//host_name:port_number/service_name) when building the DB connection string.
  • This document covers how to use the Oracle JDBC OCI driver with TNSNames Alias Syntax (@MyTNSAlias)

 

Details:

  • A small change needs to be made to the getConnectionString() method for com.wily.introscope.oracle.util.DBConnectionData  contained in the PPOracleDB.jar file.The change simply modifies the Oracle JDBC connection string to just take the hostname part of the  hostname-port-db property in the PPOracleDB.properties field and uses that as the TNS alias.
  • To avoid a syntax error at PP startup the hostname-port-db property still needs to contain the 3 parameters for "Hostname/Port/Database" e.g. orcl_iscope/1521/orcl where orcl_iscope is the TNS alias from the tnsnames.ora file

 

Benefits:

          So that means the username & password fields can be set to null in the PPOracleDB.properties file for improved security.

 

Attachments:

  • Modified version of DBConnectionData.java, compiled class DBConnectionData.class and APM 10.1 version of updated PPOracleDB.jar
  • Example PPOracleDB.properties file

 

Feedback:

Please post any questions/problems back to this page

 

Disclaimer:

Please note that like any other Field pack this change is not officially supported\tested\certified.

Statistics
0 Favorited
2 Views
1 Files
0 Shares
0 Downloads
​
Attachment(s)
zip file
PPOracleDB_oci_support_v2.zip   33 KB   1 version
Uploaded - May 29, 2019
 
Download

Tags and Keywords

Comments

Haruhiko Davis
Oct 16, 2017 12:39 PM

Please have your DBA check this answer for the Oracle error message: http://www.dbasupport.com/forums/showthread.php?61511-ORA-12504-TNS-listener-was-not-given-the-SERVICE_NAME-in-CONNECT_DATA

 

You also need to check your TSNAMES.ORA on your monitoring server where the agent is running from.

nacef.henchi
Oct 16, 2017 10:48 AM

Here the logs: 

10/16/17 03:29:42 PM WEST [INFO] [OracleDBAgent] Starting Wily Oracle Database Monitor
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Instance Name;SELECT instance_name FROM V$INSTANCE;StringEvent
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Host Name;SELECT host_name FROM V$INSTANCE;StringEvent
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Version;SELECT version FROM V$INSTANCE;StringEvent
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Startup Time;SELECT startup_time FROM V$INSTANCE;StringEvent
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Status;SELECT status FROM V$INSTANCE;StringEvent
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Deadlocks:Enqueue Deadlock Count;SELECT value FROM V$SYSSTAT WHERE name='enqueue deadlocks';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Full Table Scans:Large Table Count;SELECT value FROM V$SYSSTAT WHERE name='table scans (long tables)';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Redo Log Files:Wait Count;SELECT total_waits FROM V$SYSTEM_EVENT WHERE event='log file parallel write';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Redo Log Files|Log File Switch:Completion Count;SELECT total_waits FROM V$SYSTEM_EVENT WHERE event LIKE 'log file switch completion%';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Redo Log Files|Log File Switch:Checkpoint Incomplete Count;SELECT total_waits FROM v$system_event WHERE event like 'log file switch (check%';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Redo Log Files|Log File Switch:Archiving Needed Count;SELECT total_waits FROM v$system_event WHERE event like 'log file switch (arch%';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Rollback Segments:Get Count;SELECT sum(gets) FROM APM_OPP_ROLLSTAT_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Rollback Segments:Wait Count;SELECT sum(waits) FROM APM_OPP_ROLLSTAT_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Rollback Segments:Ratio Wait/Get (%);SELECT sum(waits)/sum(gets)*100 FROM APM_OPP_ROLLSTAT_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA:Fixed Size;SELECT value FROM APM_OPP_SGA_TEMP WHERE name='Fixed Size';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA:Redo Buffers;SELECT value FROM APM_OPP_SGA_TEMP WHERE name='Redo Buffers';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA:Variable Size;SELECT value FROM APM_OPP_SGA_TEMP WHERE name='Variable Size';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA:Database Buffers;SELECT value FROM APM_OPP_SGA_TEMP WHERE name='Database Buffers';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA:Total SGA;SELECT SUM(value) FROM APM_OPP_SGA_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Buffer Cache:Buffer Busy Waits;SELECT total_waits FROM v$system_event WHERE event='buffer busy waits';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Buffer Cache:Free Buffer Waits;SELECT total_waits FROM v$system_event WHERE event='free buffer waits';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Buffer Cache:Free Buffer Inspected;SELECT value FROM v$sysstat WHERE name='free buffer inspected';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Buffer Cache:Hit Ratio (%);SELECT (1 - (phy.value - lob.value - dir.value)/ses.value) * 100 FROM V$SYSSTAT ses, V$SYSSTAT lob, V$SYSSTAT dir, V$SYSSTAT phy WHERE ses.name='session logical reads' AND dir.name='physical reads direct' AND lob.name='physical reads direct (lob)' AND phy.name='physical reads';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Log Buffer:Allocation Retries Ratio (%);select (rbar.value/re.value)*100 from v$sysstat rbar, v$sysstat re where rbar.name like 'redo buffer allocation retries' and re.name like 'redo entries';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Log Buffer:Redo Allocation Retries;SELECT value FROM v$sysstat WHERE name like 'redo buffer allocation retries';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Log Buffer:Redo Entries; SELECT value FROM v$sysstat WHERE name like 'redo entries';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Log Buffer:Space Waits Count;SELECT count(*) FROM v$session_wait WHERE event like 'log buffer space%';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Dictionary Cache:Cache Miss Ratio (%);SELECT (SUM(getmisses)/SUM(gets))*100 FROM APM_OPP_ROWCACHE_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Library Cache:Hit Ratio (%);SELECT gethitratio*100 FROM APM_OPP_LIBRARYCACHE_TEMP WHERE namespace='SQL AREA';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Library Cache:Reload Ratio (%);SELECT (sum(reloads)/sum(pins))*100 FROM APM_OPP_LIBRARYCACHE_TEMP1;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Library Cache:Sharable Mem Per User;SELECT SUM(250 * users_opening) FROM APM_OPP_SQLAREA_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Library Cache:Sharable Mem Per SQL Stmts;SELECT SUM(sharable_mem) FROM APM_OPP_SQLAREA_TEMP1 WHERE executions > 5;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|Library Cache:Sharable Mem Stored Obj;SELECT SUM(sharable_mem) FROM APM_OPP_DB_OBJECT_CACHE_TEMP;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: SGA|Shared Pool|UGA:Total Memory;SELECT SUM(value) FROM APM_OPP_SESSTAT_TEMP sesstat, APM_OPP_STATNAME_TEMP statname WHERE name = 'session uga memory max' AND sesstat.statistic#=statname.statistic#;LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Sorts:Disk;SELECT value FROM v$sysstat WHERE name = 'sorts (disk)';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Sorts:Memory;SELECT value FROM v$sysstat WHERE name = 'sorts (memory)';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Sorts:Ratio Disk/Mem (%);SELECT (disk.value/mem.value)*100 FROM v$sysstat mem, v$sysstat disk WHERE mem.name = 'sorts (memory)' AND disk.name = 'sorts (disk)';LongCounter
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Tablespaces|<tablespace_name>|<file_name>:Physical Reads;SELECT f.phyrds, d.tablespace_name, d.file_name FROM APM_OPP_FILESTAT_TEMP f, APM_OPP_DBA_DATA_FILES_TEMP d WHERE f.file# = d.file_id;LongCounter;com.wily.powerpack.oracle.formatter.PlaceholderFormatter;phyrds
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Processing directive: Tablespaces|<tablespace_name>|<file_name>:Physical Writes;SELECT f.phywrts, d.tablespace_name, d.file_name FROM APM_OPP_FILESTAT_TEMP f, APM_OPP_DBA_DATA_FILES_TEMP d WHERE f.file# = d.file_id;LongCounter;com.wily.powerpack.oracle.formatter.PlaceholderFormatter;phywrts
10/16/17 03:29:42 PM WEST [INFO] [OracleDBAgent] Parsed 39 directives.
10/16/17 03:29:42 PM WEST [INFO] [OracleDBAgent] Starting monitoring for databases:
10/16/17 03:29:42 PM WEST [INFO] [OracleDBAgent] 172.31.5.12: vente
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent]
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Reporting metrics for DB vente...
10/16/17 03:29:42 PM WEST [DEBUG] [OracleDBAgent] Getting DB Connection for 172.31.5.12:1521/vente
10/16/17 03:29:42 PM WEST [ERROR] [OracleDBAgent] Connection error for host/database: 172.31.5.12/vente. Reporting no data values. ORA-12504: TNS : le processus d'écoute n'a pas obtenu de SERVICE_NAME dans CONNECT_DATA

Haruhiko Davis
Oct 16, 2017 06:12 AM

Good to know even if you aren't required to the client installed.

 

Have you checked with Oracle on that message?

nacef.henchi
Oct 16, 2017 06:06 AM

i installed the oracle 12c client on the machine where i have the PowerPack Agent.Apparently now it loads the missing library files now so that error disappeared but there's an error of connection: ORA-12504: TNS : listener was not given the SERVICE_NAME in CONNECT_DATA.

Haruhiko Davis
Oct 13, 2017 02:30 PM

I'm looking into this error now. I do see a similar error while testing against a number of recent JREs from Oracle.

Haruhiko Davis
Oct 13, 2017 11:24 AM

The error message is for a *nix .SO file which makes no sense. Is this deployed on *nix or Windows?

Can you share you agent log and configuration files?

nacef.henchi
Oct 13, 2017 11:01 AM

The customer is on 12.1.0.2

yes i got the agent from the standalonePPagentinstaller and im on version 9.7 and the Java version is 7 also i updated the startMonitor.bat to call the OJDBC7 and replaced the old PPOracleDB.jar with the new one downloaded from here.

Haruhiko Davis
Oct 13, 2017 10:49 AM

Are you on Relase 2 yet?
What version of Java is being used to start the agent?
Drop the JAR into the agent 'lib' folder and update the wrapper.conf file to use the updated JAR. Don't forget to remove the old reference. Also, did you get the agent from the standalonePPagent installer?

nacef.henchi
Oct 13, 2017 10:39 AM

The Oracle version is 12.1.0.2 and so i download ojdbc7 from here Oracle Database 12c Release 1 JDBC Driver Downloads 

The Powerpack agent is installed on a Windows VM and according to the exception generated, i thought maybe it needs the client lib folder  and parse it using the option -Djava.library.path=  into the startMonitor script.

Haruhiko Davis
Oct 13, 2017 10:20 AM

DLLs are for Windows. That is not needed for a Java application.
What version of Oracle DBA do you have? Did
You check the compatibility chart on which driver bests suits your installation? You should always choose the highest version of the driver that will match. You should be looking at the 11g or 12c driver. Anything before that is out of support.

nacef.henchi
Oct 13, 2017 10:15 AM

I did that : Oracle Database 12c Release 1 JDBC Driver Downloads 
i think i should download the Lib folder of Oracle client and call it on the startup script of the agent with the option  -Djava.library.path= .
Maybe cause it needs the dll files like the ocijdbc12.dll.

Haruhiko Davis
Oct 13, 2017 09:37 AM

You need to download the Oracle 12c driver.
You need the driver that matches your database version.

nacef.henchi
Oct 13, 2017 05:23 AM

i downloaded ojdbc7.jar and adapted startMonitor.bat, i still get the exception java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path

Haruhiko Davis
Oct 11, 2017 11:19 AM

Apart from the instructions, the biggest thing to do is make sure you remove the old JDBC driver (odbc6.jar) and download the latest one from Oracle.

 

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

 

 

nacef.henchi
Oct 11, 2017 06:16 AM

Hello,

any idea about how to deploy this on 9.7 and is it tested?

Thank you.

Haruhiko Davis
Oct 03, 2017 09:39 AM

Make sure you didn't leave the old ODBC driver in <epa_home>/lib folder. Use the latest drivers available from Oracle.

Legacy User
Aug 03, 2017 03:08 AM

I have the following APM Enterprise Version installed : 10.3.0.19 (Build 990300) on RHEL 7.2 (x64)

 

I have downloaded the attached package and copied the respective files to the original Oracle power pack agent installed location.

Once I start the agent service, I see the following error in logs:

 

OpenJDK 64-Bit Server VM warning: You have loaded library /opt/oracle/product/11.2.0/client_1/lib/libocijdbc11.so which might have disabled stack guard. The VM will try to fix the stack guard now.
It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'.
Exception in thread "Thread-0" java.lang.UnsatisfiedLinkError: /opt/oracle/product/11.2.0/client_1/lib/libocijdbc11.so: /opt/oracle/product/11.2.0/client_1/lib/libocijdbc11.so: wrong ELF class: ELFCLASS32 (Possible cause: architecture word width mismatch)
    at java.lang.ClassLoader$NativeLibrary.load(Native Method)
    at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1938)
    at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1854)
    at java.lang.Runtime.loadLibrary0(Runtime.java:870)
    at java.lang.System.loadLibrary(System.java:1122)
    ......

 

I am using Oracle client version 11.2.0.4 (x64) to connect to the Oracle RAC DB and the same has been added to the LD_LIBRARY_PATH before starting the agent service.

 

Am I missing something here?

Related Entries and Links

No Related Resource entered.