Wednesday, August 24, 2016

Check DB connection to an Oracle DBMS from a remote machine

Using SQLPLUS:

Execute following command in a command line:
sqlplus "<DB_USERNAME>/<DB_PASSWORD>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<HOST_NAME>)(Port=1521))(CONNECT_DATA=(SID=<SID>)))"



You will be able to login to the database in the remote machine, if connection is successful.

Using Weblogic DBPing utility

Execute following command in a command line:
java -classpath <WL_HOME>/<WL_SERVER>/server/lib/weblogic.jar utils.dbping ORACLE_THIN <DB_USERNAME> <DB_PASSWORD> <HOST_NAME>:<DB_PORT>:<SID>

You will get following message if connection is successful.
**** Success!!! ****

You can connect to the database in your app using:

  java.util.Properties props = new java.util.Properties();
  props.put("user", "<DB_USERNAME>");
  props.put("password", "<DB_PASSWORD>");
  java.sql.Driver d =
    Class.forName("oracle.jdbc.OracleDriver").newInstance();
  java.sql.Connection conn =
    Driver.connect("jdbc:oracle:thin:@<HOST_NAME>:<DB_PORT>:<SID>", props);


References:

  •  https://docs.oracle.com/cd/E13222_01/wls/docs/techstart/dbping.html
  • http://javaeesupportpatterns.blogspot.sg/2011/03/network-adapter-could-not-establish.html
  • http://dba.stackexchange.com/questions/13075/how-to-use-sqlplus-to-connect-to-an-oracle-database-located-on-another-host-with

Saturday, April 2, 2016

Using Tkprof tool for Analysing Oracle trace files

What is TkProf?

TkProf is an Oracle tool which helps to convert Oracle trace files to human readable format.

Where to find the tool?

TkProf tool is located in %ORACLE_HOME%/bin folder.

How to use it?

0. login to SQLPlus.
1. First find the trace file dump folder usign following sql command.
select value from v$parameter where name = 'user_dump_dest';
The output:
/<PATH TO YOUR DB>/YOUR_SID/trace
2. Then enable the sqltrace (This can affect to performance).
alter session set sql_trace=true;


3. Run your sql
4. Check the trace file by doing a "ls -ltr | grep _ora_". You can run a shell command within SQLPlus by prepending "!" to the command.
SQL> !ls -ltr /<PATH TO YOUR DB>/trace | grep _ora_
-rw-rw---- 1 oracleuser dbagroup     1386 Sep 28 14:30 YOUR_SID_ora_27758.trm
-rw-rw---- 1 oracleuser dbagroup     3628 Sep 28 14:30 YOUR_SID_ora_27758.trc
-rw-rw---- 1 oracleuser dbagroup     1359 Sep 28 14:55 YOUR_SID_ora_29733.trm
-rw-rw---- 1 oracleuser dbagroup     3779 Sep 28 14:55 YOUR_SID_ora_29733.trc
-rw-rw---- 1 oracleuser dbagroup     1355 Oct 21 16:07 YOUR_SID_ora_32487.trm
-rw-rw---- 1 oracleuser dbagroup     3613 Oct 21 16:07 YOUR_SID_ora_32487.trc
-rw-rw---- 1 oracleuser dbagroup     122 Oct 25 14:38 YOUR_SID_ora_6253.trm
-rw-rw---- 1 oracleuser dbagroup     3506 Oct 25 14:38 YOUR_SID_ora_6253.trc


5. The last updated ".trc" file of above output is the one related to our SQL query analysis.
6. Run the tkprof command to get the formatted result. The tkprof tool is located at bin folder of %ORACLE_HOME% folder.

/home/oracleuser[YOUR_SID]:tkprof  /<PATH TO YOUR DB>/YOUR_SID/trace/YOUR_SID_ora_6253.trc  tkprof_out.txt
TKPROF: Release 11.2.0.3.0 - Development on Sun Oct 25 14:41:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
/home/oracleuser[YOUR_SID]:less tkprof_out_3.txt

More Details:

Please see following websites for more detailed information about how to use TkProf tool and analysing the output.

Example output of TkProf:


TKPROF: Release 11.2.0.3.0 - Development on Sun Oct 25 14:41:58 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: /<PATH TO YOUR DB>/YOUR_SID/trace/<YOUR_SID>_ora_6253.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 4tk6t8tfsfqbf Plan Hash: 0

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.03          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.03          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
********************************************************************************

<YOUR_SQL_QUERY>

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01          0         53          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.01          0         53          1           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  NESTED LOOPS  (cr=53 pr=0 pw=0 time=14025 us)
         0          0          0   NESTED LOOPS  (cr=53 pr=0 pw=0 time=14018 us cost=12 size=162 card=1)
      2502       2502       2502    PARTITION HASH SINGLE PARTITION: 1 1 (cr=53 pr=0 pw=0 time=10393 us cost=12 size=170136 card=2502)
      2502       2502       2502     TABLE ACCESS FULL <YOUR_TABLE_1> PARTITION: 1 1 (cr=53 pr=0 pw=0 time=5007 us cost=12 size=170136 card=2502)
         0          0          0    PARTITION HASH SINGLE PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=6275 us cost=0 size=0 card=1)
         0          0          0     INDEX UNIQUE SCAN <A_PK_INDEX> PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 15488)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID <ANOTHER_TABLE_2> PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=94 card=1)




********************************************************************************

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.03          0          0          0           0
Fetch        1      0.01       0.01          0         53          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.05          0         53          1           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: /<PATH TO YOUR DB>/YOUR_SID/trace/<YOUR_SID>_ora_6253.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      59  lines in trace file.
      78  elapsed seconds in trace file.

 

Wednesday, March 23, 2016

JPA ISSUE - Execution of Double (or Multiple) UPDATE SQL Statements for the Updates done on the same Entity Object

Issue:


In a JPA transaction, Updating of the same entity is split into two separate UPDATE SQL statement execution.

Reason:


  1. Some properties of an entity is updated in a transaction.
  2. Then there is a db query to retrieve some data.
  3. This query execution cause the above entity modification to flush to the db. (Due to the JPA implementation).
    •  This cause the execution of the first UPDATE SQL statement.
  4. Update another set of properties of the same entity.
  5. At the next flush event (at the end of TX or execution of another query), The execution of the second UPDATE SQL statement on the same record executed for the last change..
E.g:
// Start modifying bankAccount entity
bankAccount.setName(name);
// Execute a Query which causes flush
List<PaymentOrganization>  paymentOrganizations = paymentOrganizationFacade.getAllOrganizations();
// modify another attribute of same entity
bankAccount.setPaymentOrganizations(paymentOrganizations);

Solution:

Do not put any query execution in between the modifications of different attributes of the same entity object.

E.g:
Move the query before starting the modification of the entity.
List<PaymentOrganization>  paymentOrganizations = paymentOrganizationFacade.getAllOrganizations();
bankAccount.setName(name);
bankAccount.setPaymentOrganizations(paymentOrganizations);

Monday, March 7, 2016

Remote debugging in Weblogic server using Eclipse

In Weblogic server,
Select relevant managed server and go to "Server Start" tab/ Then in the "Arguments:" text box, append following parameters
   -Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n

Then, in Eclipse,
  1. Add a new "Remote Java Application" in the Debug Configurations (via the menu: Run -> Debug Configurations).
  2. Make sure the correct project is selected (Browse button) and fill in server name in "Host" and 8000 in "Port".
  3. To avoid Eclipse stopping debug at "Daemon Thread [Timer-xxx] (Suspended (exception RuntimeException)) TimerThread.run()",
    • Go to Preferences -> Java -> Debug
    • Uncheck "Suspend execution on uncaught exceptions" 
Note: It is not necessary to wait until managed server finish startup for the debugging. You can start the debugging after you initiate the managed server startup. This means you can debug post start/activate actions of the managed server also.

Friday, February 12, 2016

How to check whether LDAP username and password is correct - LDAP/OpenDJ

You can use "ldapwhoami" command to check whether a ldap username and password is correct.

ldapwhoami -vvv -h <host> -p <port> -D "<binddn>" -x -w <password>

"bindDN" is the LDAP credential you used to authenticate with a LDAP server.

Example of binddn;

cn=Manager,ou=example,dc=com,dc=au

Command line options used in this example:

-H ldapuri - Specify URI(s) referring to the ldap server(s); only the protocol/host/port fields are allowed; a list of URI, separated by whitespace or commas is expected.
-h ldaphost - Specify an alternate host on which the ldap server is running. Deprecated in favor of -H.
-p ldapport - Specify an alternate TCP port where the ldap server is listening. Deprecated in favor of -H.
-v - Run in verbose mode, with many diagnostics written to standard output.
-w passwd - Use passwd as the password for simple authentication. 
-x - Use simple authentication instead of SASL
-D binddn -Use the Distinguished Name binddn to bind to the LDAP directory. For SASL binds, the server is expected to ignore this value.

More details:

Tips on Tools Usage - SqlPlus

Editing a previously executed sql statement:

Type "ed" in the sqlplus. Then sqlplus will open the previous command in an editor (e.g: in vi). Then you can edit the sql command and save it using "Escape" + ":wq" as you normally does in "vi" editor.
Then type / to run the edited command.

Executing the previous sql statement:

Type "/" or "r" in the sqlplus terminal to run the previously executed sql statement again.

Show the previously executed sql statement without executing it:

Type "list" in the sqlplus terminal.

Formatting of the sql output:

Change the width of a column in the sql output, for a character field:

column <filed_name> format a<width_number_of_characters>

E.g:
column user_name format a100

Change the width of a column in the sql output, for a numeric field:

column <field_name> format <number_format>

E.g:
column salary format 9999.99

Set the line size of the sql output:

set linesize <line_size>

E.g:
set linesize 300

More Details:

http://www.comp.nus.edu.sg/~ooibc/courses/sql/sqlplus.htm
http://ugweb.cs.ualberta.ca/~c391/manual/chapt4.html
http://www.orafaq.com/wiki/SQL*Plus

Tips on Tools Usage - WLST (For viewing MBeans)

Locate the wlst.sh

solaris:

find <a_parent_folder_in_path_to_wls> -name wlst.sh

linux:

locate wlst.sh

Storing weblogic admin username password in a file for easy access:

Use storeUserConfig([userConfigFile], [userKeyFile], [nm]) command.
After this, you do not have to specify username and password when connecting to a wlst session.

Example session for viewing custom MBeans (MBeans in your application):


cd /<path_to_wls>/wlserver_10.3/common/bin
sh ./wlst.sh
wls:/offline> connect(url='t3://hostname:managedserverpot')
custom()
ls()

Tuesday, February 9, 2016

MySQL - An alternative for using constants in MySQL stored procedures

MySQL does not support declaring constants to be shared between different stored procedures. But some other RDBMS like Oracle supports this feature. To still have something similar to constants in Oracle PL/SQL in MySQL, I found that following workaround acts as an alternative to declaring constant variables.

In MySQL, you can add some characteristics of the your stored function in the stored function definition. you can mark a stored function as a DETERMINISTIC or NON-DETERMINISTIC.
DETERMINISTIC means for same input, the function returns the same output every time it invokes.
In addition to this, you can mark a stored function to say whether the function has SQL or not, whether the SQL in the function does any update or read only.

What I am going to use as an alternative to constant is to use a DETERMINISTIC NO SQL function (without any input argument) with the same name as the constant I want and which returns the value of the constant variable I want to use.

Example: Following example shows how this alternative solution is applied for a constant named "OPERATION_TYPE_ABORT" with the value "ABORT". 
DROP FUNCTION IF EXISTS OPERATION_TYPE_ABORT;
DELIMITER //
CREATE FUNCTION OPERATION_TYPE_ABORT() RETURNS VARCHAR(6) 
DETERMINISTIC NO SQL
BEGIN
    RETURN ("ABORT");
END//
DELIMITER ;

Output:

mysql> select "test" FROM DUAL WHERE "ABORT" = OPERATION_TYPE_ABORT();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> select "test" FROM DUAL WHERE "ABORT2" = OPERATION_TYPE_ABORT();
Empty set (0.00 sec)

How to check the JDK version used for compiling a java class

You can use javap command to find the compiled version of a class. javap (Java Class File Disassembler) tool is a tool provided with JDK to disassemble class files.

Unix:

Open a terminal and change the working directory to the place where your class is located.
javap -verbose HelloWorld.class| grep "major"
 

Windows:

Open a command prompt and change the working directory to the place where your class is located.
javap -verbose HelloWorld.class | findstr "major" 
 
E.g.: 
 
 
 
 Following is the mapping between the major version and the relevant JDK version.
Major VersionComipled JDK version
461.2
471.3
481.4
495
506
517
528
 
 

Tuesday, October 20, 2015

Oracle: Finding the value for "log_history" in DBMS_SCHEDULER

In Oracle, The "log_history" attribute in DBMS_SCHEDULER is used to specify the number of days  of log history to keep.
The default value of this attribute is 30 days. Scheduler purges the logs (job logs and windows logs) which are older than "log_history" number of days, once per day.

Execute following anonymous block in sqlplus to find the current value of 'log_history" attribute.

set serveroutput on
DECLARE
  val VARCHAR2(100);
BEGIN
  dbms_scheduler.get_scheduler_attribute('log_history', val);
  dbms_output.put_line(val);
END;
/

To change the "log_history" value, you can use
dbms_scheduler.set_scheduler_attribute('log_history','40');