search

akardapolov / ASH-Viewer

ASH Viewer provides a graphical view of active session history data within the Oracle and PostgreSQL DB
GNU General Public License v3.0
167 stars 72 forks source link

4.3.1 can't connect to 10g, while 11 works well. #44

Closed rasdba closed 3 years ago

rasdba commented 3 years ago

Hi. 3.5.1 connects to both 11 and 10 db's well. But 4.3.1 can't connect to the 10g, it throws the error ons sreenshot. Connections to 11g runs well. image Thanks!

akardapolov commented 3 years ago

Hi!

Try to get full error stack trace using https://github.com/akardapolov/ASH-Viewer/blob/feature/run.bat or https://github.com/akardapolov/ASH-Viewer/blob/feature/run.sh and put it here.

Thanks, Alex.

rasdba commented 3 years ago

Hi, sure:

run.bat: @REM ---------------------------------------------------------------------------- @REM Licensed to the GNU GENERAL PUBLIC LICENSE Version 3 @REM ----------------------------------------------------------------------------

@REM ---------------------------------------------------------------------------- @REM ASH Viewer start up batch script @REM @REM Required ENV vars: @REM JAVA_HOME - location of a JDK home dir @REM

SET JAVA_HOME=C:\Program Files (x86)\Java\jre1.8.0_281

SET JAVA_EXE="%JAVA_HOME%\bin\java.exe"

%JAVA_EXE% -Xmx128m -jar ASH-Viewer.jar

output in cmd: C:\Users\xxx\Downloads\ashv-4.3.1-bin>SET JAVA_HOME=C:\Program Files (x86)\Java\jre1.8.0_281

C:\Users\xxx\Downloads\ashv-4.3.1-bin>SET JAVA_EXE="C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe"

C:\Users\xxx\Downloads\ashv-4.3.1-bin>"C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe" -Xmx128m -jar ASH-Viewer.jar 27.03.21 22:15:52.670 [main] INFO Main - Start application 27.03.21 22:15:52.879 [main] INFO BasicFrame - Start instantiating new BasicFrame 27.03.21 22:15:55.031 [main] INFO ConnectToDbAction - ConnectToDbAction button pressed 27.03.21 22:16:26.886 [Thread-5] ERROR ConnectToDbArea - ASH Viewer error: java.util.NoSuchElementException: No value present java.util.Optional.get(Unknown Source) core.processing.GetFromRemoteAndStore.getColumnIdForCol(GetFromRemoteAndStore.java:528) core.processing.GetFromRemoteAndStore.lambda$loadSqlsMetadata$0(GetFromRemoteAndStore.java:129) java.util.LinkedList$LLSpliterator.forEachRemaining(Unknown Source) java.util.stream.ReferencePipeline$Head.forEach(Unknown Source) core.processing.GetFromRemoteAndStore.loadSqlsMetadata(GetFromRemoteAndStore.java:129) core.processing.GetFromRemoteAndStore.loadDataFromRemoteToLocalStore(GetFromRemoteAndStore.java:136) gui.connect.ConnectToDbArea.loadObjectsByConnectionName(ConnectToDbArea.java:514) gui.util.ProgressBarUtil$1.run(ProgressBarUtil.java:79)

akardapolov commented 3 years ago

Hi!

It seems the local profile file has been made from another database. Just remove profile by name (see configuration folder) and run ashv again. If it does not help - send me profile file and ddl script for v$active_session_history.

Thanks, Alex.

rasdba commented 3 years ago

Hi, Deleted vostok profile file, created new one and error still in place. Uploaded it (i masked ip and service name). user_privs.txt vostok.txt

"ddl script for v$active_session_history" - i used user_privs.sql included in version 3.5.1. attaching it too (renamed it to txt as github doesn't allow to upload .sql file.

akardapolov commented 3 years ago

Hi!

ddl script for v$active_session_history

Try this: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7217403308677

Thanks, Alex.

rasdba commented 3 years ago

Hi. Sorry, didn't understand question at the beginning. Sure: SQL> desc sys.v$active_session_history; Name Null? Type

SAMPLE_ID NUMBER SAMPLE_TIME TIMESTAMP(3) SESSION_ID NUMBER SESSION_SERIAL# NUMBER USER_ID NUMBER SQL_ID VARCHAR2(13) SQL_CHILD_NUMBER NUMBER SQL_PLAN_HASH_VALUE NUMBER FORCE_MATCHING_SIGNATURE NUMBER SQL_OPCODE NUMBER PLSQL_ENTRY_OBJECT_ID NUMBER PLSQL_ENTRY_SUBPROGRAM_ID NUMBER PLSQL_OBJECT_ID NUMBER PLSQL_SUBPROGRAM_ID NUMBER SERVICE_HASH NUMBER SESSION_TYPE VARCHAR2(10) SESSION_STATE VARCHAR2(7) QC_SESSION_ID NUMBER QC_INSTANCE_ID NUMBER BLOCKING_SESSION NUMBER BLOCKING_SESSION_STATUS VARCHAR2(11) BLOCKING_SESSION_SERIAL# NUMBER EVENT VARCHAR2(64) EVENT_ID NUMBER EVENT# NUMBER SEQ# NUMBER P1TEXT VARCHAR2(64) P1 NUMBER P2TEXT VARCHAR2(64) P2 NUMBER P3TEXT VARCHAR2(64) P3 NUMBER WAIT_CLASS VARCHAR2(64) WAIT_CLASS_ID NUMBER WAIT_TIME NUMBER TIME_WAITED NUMBER XID RAW(8) CURRENT_OBJ# NUMBER CURRENT_FILE# NUMBER CURRENT_BLOCK# NUMBER CAPTURE_OVERHEAD VARCHAR2(1) IS_CAPTURED VARCHAR2(1) PROGRAM VARCHAR2(48) MODULE VARCHAR2(48) ACTION VARCHAR2(32) CLIENT_ID VARCHAR2(64) MACHINE VARCHAR2(64) PORT NUMBER ECID VARCHAR2(64) FLAGS NUMBER

SELECT sample_id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_number, sql_plan_hash_value, force_matching_signature, sql_opcode, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, service_hash, session_type, session_state, qc_session_id, qc_instance_id, blocking_session, blocking_session_status, blocking_session_serial#, event, event_id, event#, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, time_waited, xid, current_obj#, current_file#, current_block#, capture_overhead, is_captured, program, module, action, client_id, machine, port, ecid, flags FROM GV$ACTIVE_SESSION_HISTORY WHERE inst_id = USERENV ('INSTANCE')

rasdba commented 3 years ago

gv$active_session_history: SELECT /+ no_merge ordered use_nl(s,a) / a.inst_id, s.sample_id, s.sample_time, a.session_id, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value, a.force_matching_signature, a.sql_opcode, DECODE (a.plsql_entry_object_id, 0, TO_NUMBER (NULL), a.plsql_entry_object_id), DECODE (a.plsql_entry_object_id, 0, TO_NUMBER (NULL), a.plsql_entry_subprogram_id), DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_object_id), DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_subprogram_id), a.service_hash, DECODE (a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'), DECODE (a.wait_time, 0, 'WAITING', 'ON CPU'), DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_session_id), DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_instance_id), (CASE WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER (NULL) ELSE a.blocking_session END), (CASE WHEN a.blocking_session = 4294967295 THEN 'UNKNOWN' WHEN a.blocking_session = 4294967294 THEN 'GLOBAL' WHEN a.blocking_session = 4294967293 THEN 'UNKNOWN' WHEN a.blocking_session = 4294967292 THEN 'NO HOLDER' WHEN a.blocking_session = 4294967291 THEN 'NOT IN WAIT' ELSE 'VALID' END), (CASE WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER (NULL) ELSE a.blocking_session_serial# END), DECODE (a.wait_time, 0, a.event, NULL), DECODE (a.wait_time, 0, a.event_id, NULL), DECODE (a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1, a.p2text, a.p2, a.p3text, a.p3, DECODE (a.wait_time, 0, a.wait_class, NULL), DECODE (a.wait_time, 0, a.wait_class_id, NULL), a.wait_time, a.time_waited, a.xid, a.current_obj#, a.current_file#, a.current_block#, DECODE (BITAND (a.flags, POWER (2, 5)), NULL, 'N', 0, 'N', 'Y'), DECODE (BITAND (a.flags, POWER (2, 0)), NULL, 'N', 0, 'N', 'Y'), a.program, a.module, a.action, a.client_id, a.machine, a.port, a.ecid, a.flags FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr AND s.sample_id = a.sample_id AND s.sample_time = a.sample_time

akardapolov commented 3 years ago

Hi!

Exactly what is needed. I will try to reproduce the error.

Thanks, Alex.

rasdba commented 3 years ago

Hi. Sorry for disturbing, any update on the bug? Have checked recent 4.3.3 version, looks like it is still in place.

akardapolov commented 3 years ago

Hi!

Try to get full error stack trace again. https://github.com/akardapolov/ASH-Viewer/issues/44#issuecomment-807110238

Thanks, Alex.

rasdba commented 3 years ago

C:\Users\xxx\Downloads\ashv-4.3.3-bin>run.bat

C:\Users\xxx\Downloads\ashv-4.3.3-bin>SET JAVA_EXE="C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe"

C:\Users\xxx\Downloads\ashv-4.3.3-bin>"C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe" -Xmx1024m -jar ASH-Viewer.jar 19.09.21 20:24:12.271 [main] INFO Main - Start application 19.09.21 20:24:12.486 [main] INFO BasicFrame - Start instantiating new BasicFrame 19.09.21 20:24:14.651 [main] INFO ConnectToDbAction - ConnectToDbAction button pressed 19.09.21 20:24:23.380 [Thread-5] ERROR ConnectToDbArea - ASH Viewer error: java.util.NoSuchElementException: No value present java.util.Optional.get(Unknown Source) core.processing.GetFromRemoteAndStore.getColumnIdForCol(GetFromRemoteAndStore.java:574) core.processing.GetFromRemoteAndStore.lambda$loadSqlsMetadata$52(GetFromRemoteAndStore.java:149) java.util.LinkedList$LLSpliterator.forEachRemaining(Unknown Source) java.util.stream.ReferencePipeline$Head.forEach(Unknown Source) core.processing.GetFromRemoteAndStore.loadSqlsMetadata(GetFromRemoteAndStore.java:149) core.processing.GetFromRemoteAndStore.loadDataFromRemoteToLocalStore(GetFromRemoteAndStore.java:156) gui.connect.ConnectToDbArea.loadObjectsByConnectionName(ConnectToDbArea.java:602) gui.util.ProgressBarUtil$1.run(ProgressBarUtil.java:79)

akardapolov commented 3 years ago

Hi!

Check db version please: select * from v$version;

Thanks, Alex.

rasdba commented 3 years ago

SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production

akardapolov commented 3 years ago

Hi!

Try the new bin file: ashv-4.3.3.1-bin.zip here https://github.com/akardapolov/ASH-Viewer/releases/tag/4.3.3

Thanks, Alex.

akardapolov commented 3 years ago

@rasdba Have you tried the ashv-4.3.3.1-bin.zip?

Thanks, Alex.

rasdba commented 3 years ago

Hi Alex, Unfortunately, the same. C:\Users\roman\Downloads>cd ashv-4.3.3.1-bin

C:\Users\roman\Downloads\ashv-4.3.3.1-bin>run.bat

C:\Users\roman\Downloads\ashv-4.3.3.1-bin>SET JAVA_HOME=C:\Program Files (x86)\Java\jre1.8.0_281

C:\Users\roman\Downloads\ashv-4.3.3.1-bin>SET JAVA_EXE="C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe"

C:\Users\roman\Downloads\ashv-4.3.3.1-bin>"C:\Program Files (x86)\Java\jre1.8.0_281\bin\java.exe" -Xmx1024m -jar ASH-Viewer.jar 23.09.21 21:57:04.713 [main] INFO Main - Start application 23.09.21 21:57:04.924 [main] INFO BasicFrame - Start instantiating new BasicFrame 23.09.21 21:57:06.920 [main] INFO ConnectToDbAction - ConnectToDbAction button pressed 23.09.21 21:57:19.109 [Thread-5] ERROR ConnectToDbArea - ASH Viewer error: java.util.NoSuchElementException: No value present java.util.Optional.get(Unknown Source) core.processing.GetFromRemoteAndStore.getColumnIdForCol(GetFromRemoteAndStore.java:580) core.processing.GetFromRemoteAndStore.lambda$loadSqlsMetadata$52(GetFromRemoteAndStore.java:150) java.util.LinkedList$LLSpliterator.forEachRemaining(Unknown Source) java.util.stream.ReferencePipeline$Head.forEach(Unknown Source) core.processing.GetFromRemoteAndStore.loadSqlsMetadata(GetFromRemoteAndStore.java:150) core.processing.GetFromRemoteAndStore.loadDataFromRemoteToLocalStore(GetFromRemoteAndStore.java:157) gui.connect.ConnectToDbArea.loadObjectsByConnectionName(ConnectToDbArea.java:602) gui.util.ProgressBarUtil$1.run(ProgressBarUtil.java:79)

akardapolov commented 3 years ago

Hi!

Create the new connection with OracleEE10g profile and try again.

Thanks, Alex.