Wednesday, November 28, 2012

OPatch

/opt/oracle/fmw11_1_1_5/oracle_common/OPatch/opatch lsinventory

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.


/opt/oracle/fmw11_1_1_5/oracle_common/OPatch/opatch lsinventory -invPtrLoc /opt/oracle/fmw11_1_1_5/oraInst.loc

Invoking OPatch 11.1.0.8.2

Oracle Interim Patch Installer version 11.1.0.8.2
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/fmw11_1_1_5/oracle_common
Central Inventory : /opt/oracle/orainventory
   from           : /opt/oracle/fmw11_1_1_5/oraInst.loc
OPatch version    : 11.1.0.8.2
OUI version       : 11.1.0.9.0
OUI location      : /opt/oracle/fmw11_1_1_5/oracle_common/oui
Log file location : /opt/oracle/fmw11_1_1_5/oracle_common/cfgtoollogs/opatch/opatch2012-11-28_18-58-45PM.log

Patch history file: /opt/oracle/fmw11_1_1_5/oracle_common/cfgtoollogs/opatch/opatch_history.txt


OPatch detects the Middleware Home as "/opt/oracle/fmw11_1_1_5"

Lsinventory Output file location : /opt/oracle/fmw11_1_1_5/oracle_common/cfgtoollogs/opatch/lsinv/lsinventory2012-11-28_18-58-45PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle AS Common Toplevel Component                                  11.1.1.2.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.








export MW_HOME=/opt/oracle/fmw11_1_1_5/
export ORACLE_HOME=/opt/oracle/fmw11_1_1_5/osb
export JDK_HOME=/opt/oracle/java

/opt/oracle/fmw11_1_1_5/oracle_common/OPatch/opatch apply -jdk /opt/oracle/java/ -invPtrLoc /opt/oracle/fmw11_1_1_5/oraInst.loc
Invoking OPatch 11.1.0.8.2

Oracle Interim Patch Installer version 11.1.0.8.2
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/fmw11_1_1_5/osb
Central Inventory : /opt/oracle/orainventory
   from           : /opt/oracle/fmw11_1_1_5/oraInst.loc
OPatch version    : 11.1.0.8.2
OUI version       : 11.1.0.9.0
OUI location      : /opt/oracle/fmw11_1_1_5/osb/oui
Log file location : /opt/oracle/fmw11_1_1_5/osb/cfgtoollogs/opatch/opatch2012-11-28_19-23-38PM.log

Patch history file: /opt/oracle/fmw11_1_1_5/osb/cfgtoollogs/opatch/opatch_history.txt


OPatch detects the Middleware Home as "/opt/oracle/fmw11_1_1_5"

ApplySession applying interim patch '12747853' to OH '/opt/oracle/fmw11_1_1_5/osb'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/fmw11_1_1_5/osb')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '12747853' for restore. This might take a while...
Backing up files affected by the patch '12747853' for rollback. This might take a while...

Patching component oracle.osb.top, 11.1.1.5.0...
Updating jar file "/opt/oracle/fmw11_1_1_5/osb/lib/transports/jcatransport.ear" with "/lib/transports/jcatransport.ear/APP-INF/lib/jcatransport.jar/com/bea/wli/sb/transports/jca/JCATransportAppListener.class"
Updating jar file "/opt/oracle/fmw11_1_1_5/osb/lib/transports/jcatransport.jar" with "/lib/transports/jcatransport.jar/com/bea/wli/sb/transports/jca/JCATransportAppListener.class"
ApplySession adding interim patch '12747853' to inventory

Verifying the update...
Inventory check OK: Patch ID 12747853 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12747853 are present in Oracle Home.

The local system has been patched and can be restarted.



Tuesday, November 27, 2012

Trying to teach myself security


Read in this order:

http://en.wikipedia.org/wiki/Digital_signature

http://en.wikipedia.org/wiki/Public-key_encryption

http://en.wikipedia.org/wiki/Public_key

http://en.wikipedia.org/wiki/RSA_%28algorithm%29
http://en.wikipedia.org/wiki/Digital_Signature_Algorithm
http://en.wikipedia.org/wiki/Advanced_Encryption_Standard

http://en.wikipedia.org/wiki/Symmetric-key_algorithm




http://en.wikipedia.org/wiki/Public_key_certificate






Monday, November 26, 2012

PL/SQL to transfer files in small chunks

When confronted to copying large amounts of data in a DB, normally you don't want to do it in a mammoth transaction, since this could severely damage running application, and even fill your redo logs.

This is a sample anonymous block to run the transfer in small chunks (in the example, 7 days worth of data are transferred at 600 seconds blocks)


declare v_now  date;
v_delay_in_seconds number;

begin 

v_now := sysdate;

for thedelay in reverse 1.. 7 * 24 * 6 loop
  v_delay_in_seconds := thedelay * 600;

  insert into WLI_REPORTING_ARCHIVE
(
   MSG_GUID, HOST_NAME, MSG_LABELS, ERRORCODE, ERRORDESCRIPTION,  
 INTERFACEID, BUSINESSID, BUSINESSUNIQUEID, EVENTTYPE, 
 EVENTOCCUREDON, TECHNICALMESSAGEID, PATHOFSERVICE, 
 FILENAME, ISERROR, EVENTOCCUREDON_EPOCH, DB_TIMESTAMP, DATA_VALUE
)
select A.MSG_GUID, A.HOST_NAME, A.MSG_LABELS, A.ERRORCODE, A.ERRORDESCRIPTION,  
 A.INTERFACEID, A.BUSINESSID, A.BUSINESSUNIQUEID, A.EVENTTYPE, 
 A.EVENTOCCUREDON, A.TECHNICALMESSAGEID, A.PATHOFSERVICE, 
 A.FILENAME, A.ISERROR, A.EVENTOCCUREDON_EPOCH, A.DB_TIMESTAMP, c.data_value 
from WLI_QS_REPORT_VIEW A, WLI_QS_REPORT_ATTRIBUTE B, WLI_QS_REPORT_DATA C
where A.MSG_GUID = B.MSG_GUID and C.MSG_GUID = B.MSG_GUID  and ( v_now - B.DB_TIMESTAMP) * 24 * 3600 > v_delay_in_seconds;

WLI_REP_ARCHIVE_LOG_INS('1:INSERT', To_char( SQL%ROWCOUNT ) || ' records inserted into WLI_REPORTING_ARCHIVE older than ' || v_delay_in_seconds || ' seconds');

delete from WLI_QS_REPORT_ATTRIBUTE B where ( v_now - B.DB_TIMESTAMP) * 24 * 3600 > v_delay_in_seconds ;


WLI_REP_ARCHIVE_LOG_INS('2:DELETE', To_char( SQL%ROWCOUNT ) || ' records deleted from WLI_QS_REPORT_ATTRIBUTE older than ' || v_delay_in_seconds || ' seconds');

commit;
end loop;

end;
/





rebuilding indexes to speed up the OSB reporting

Aparently there is a heated debate on whether rebuilding indexes is actually helpful. To me, it worked like a charm.
Before, a simple "select count(*) from WLI_QS_REPORT_ATTRIBUTE;" would take 15 seconds, even if my table was almost empty.
After the rebuild, it was taking milliseconds.



ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_DM REBUILD;
ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_IED REBUILD;


This wiki contrains a lot of valuable advice on how to monitor index usage: http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Friday, November 23, 2012

Oracle DB: deleting a HUGE table a chunk at a time in a loop

This is just an example, of course TRUNC will be much faster... but if you need to select specific records, you have no choice.....

DECLARE
  nCount  NUMBER; 
  sql1 VARCHAR2(2000);
BEGIN
  
  nCount := 0;
  sql1 := 'delete from WLI_QS_REPORT_DATA where rownum < 10000';
  LOOP
    
    EXECUTE IMMEDIATE sql1;
    nCount := sql%rowcount;
    DBMS_OUTPUT.PUT_LINE('deleted records: ' || to_char(ncount) );
    commit;    

    EXIT WHEN nCount = 0;

  END LOOP;
end;
/




Oracle DB: count rows in a table

select COUNT(*) from WLI_REPORTING_ARCHIVE;
1513854

TECHNICALMESSAGEID can be null, and there is an INDEX on it:
select COUNT(TECHNICALMESSAGEID ) from WLI_REPORTING_ARCHIVE;
1513824
The difference is 30
select COUNT(*) from WLI_REPORTING_ARCHIVE where TECHNICALMESSAGEID is NULL;
30

of course
select COUNT(TECHNICALMESSAGEID) from WLI_REPORTING_ARCHIVE where TECHNICALMESSAGEID is null;
will immediately return 0
(it's very fast because probably the index keeps track of NULL entries)

This is not very accurate

SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'WLI_REPORTING_ARCHIVE';

unless the table has just been analyzed

This is VERY inaccurate
SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'WLI_REPORTING_ARCHIVE';

This is very accurate and fast
SELECT COUNT(*) * 100 FROM WLI_REPORTING_ARCHIVE SAMPLE (1);

explain plan for select COUNT(*) from WLI_REPORTING_ARCHIVE;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                          | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                               |     1 |  2921   (1)| 00:00:36 |
|   1 |  SORT AGGREGATE       |                               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| WLI_REP_ARCHIVE_INDEX1 |  1499K|  2921   (1)| 00:00:36 |
-----------------------------------------------------------------------------------------------





see http://stackoverflow.com/questions/1840538/faster-alternative-in-oracle-to-select-count-from-sometable

WebLogic unlock account with WLST

/opt/oracle/fmw11_1_1_5/osb/common/bin/wlst.sh

connect('myuser', 'weblogic1', 't3://hqchnesoa104.acme.com:7001')

serverRuntime()

cd('ServerSecurityRuntime/osbts1as/DefaultRealmRuntime/myrealm/UserLockoutManagerRuntime/UserLockoutManager')

cmo.clearLockout('weblogic')

ls()

-r--   InvalidLoginAttemptsTotalCount               21171
-r--   InvalidLoginUsersHighCount                   2
-r--   LockedUsersCurrentCount                      1
-r--   LoginAttemptsWhileLockedTotalCount           20909
-r--   Name                                         UserLockoutManager
-r--   Type                                         UserLockoutManagerRuntime
-r--   UnlockedUsersTotalCount                      50
-r--   UserLockoutTotalCount                        51

-r-x   clearLockout                                 Void : String(userName)
-r-x   getLastLoginFailure                          Long : String(userName)
-r-x   getLoginFailureCount                         Long : String(userName)
-r-x   isLockedOut                                  Boolean : String(userName)
-r-x   preDeregister                                Void :



see also for Java code:
http://weblogic-wonders.com/weblogic/2010/11/12/userlockout-feature-of-weblogic-server/





OSB SFTP Business Service

http://hhendriks.wordpress.com/2012/08/13/create-a-sftp-polling-service-in-the-osb/

http://docs.oracle.com/cd/E17904_01/doc.1111/e15866/transport_level.htm#i1058815
102 and 200 are two hosts.

I have created a PV_POC_SFTP project on 102 7001, to transfer files to 200

on 200:

mkdir /home/soa/sftpdestination

/sbin/ifconfig

10.56.25.135

on 102:

cat /home/soa/.ssh/known_hosts | grep hqchpippo200

hqchpippo200,10.56.25.135 ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA6KpqfXWf6XdJBQFjYgc2rmapk2yba55BZrdLqbJjIIEb15Huc7zrIxAQeXJh8eohD5qCUGztOhJzmURuPP1oofxI1iERzp0QWED7ZnO1EPqMLp+FdLxTC03he1gn2Ng1ugYPZkpGrkaG/pYEs8LlTFadQ6ICa4fIAxQYNxa8R0MEkzxsCewP66ahzPTGVWp03QpPtUPQV0sOIOb7mZKPaxQ3JhxN+IFDDIKoWuiaz1B08oV51JtdBnQRR0JPIzx1EwwUL9x2tEKNKzcCzhFn8jwsxvKP882c5R6rWHDn1adS6F+rg3jPjmyV1h4J/CnAFmYlNMVdeLo7zhfh95JURw==

hqchpippo200.acme.com ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA6KpqfXWf6XdJBQFjYgc2rmapk2yba55BZrdLqbJjIIEb15Huc7zrIxAQeXJh8eohD5qCUGztOhJzmURuPP1oofxI1iERzp0QWED7ZnO1EPqMLp+FdLxTC03he1gn2Ng1ugYPZkpGrkaG/pYEs8LlTFadQ6ICa4fIAxQYNxa8R0MEkzxsCewP66ahzPTGVWp03QpPtUPQV0sOIOb7mZKPaxQ3JhxN+IFsdDDoWuiaz1B08oV51JtdBnQRR0JPIzx1EwwUL9x2tEKNKzcCzhFn8jwsxvKP882c5R6rWHDn1adS6F+rg3jPjmyV1h4J/CnAFmYlNMVdeLo7zhfh95JURw==

(official Oracle doc is WRONG!  it’s not $DOMAIN_HOME/osb/transports/sftp but $DOMAIN_HOME/config/osb/transports/sftp !!!!!)


mkdir –p /opt/oracle/domains/osbdv2do/config/osb/transports/sftp

cd /opt/oracle/domains/osbdv2do/config/osb/transports/sftp

vi known_hosts

paste the result of cat /home/soa/.ssh/known_hosts | grep hqchpippo200
(it's above)

create Service Account

PV_POC_SFTP_SA

static

soa

#thepassword


create the BS

anyxml

protocol sftp

endpoint uri

sftp://hqchpippo200:22/sftpdestination 


press ADD

User Authentication =Username Password

attach service account


test

if you get this:

The invocation resulted in an error: com.sshtools.j2ssh.transport.InvalidHostFileException: known_hosts file does not exist or it does not have read permissions..

check that you put the known_hosts in the right place /opt/oracle/domains/osbdv2do/config/osb/transports/sftp


if you get this:

The invocation resulted in an error: com.sshtools.j2ssh.transport.TransportProtocolException: Key Mismatch for host hqchpippo200,10.56.25.135.

make sure you got the right ssh-rsa kkeys from the /home/soa/.ssh/known_hosts file


upon successful invokation of the service, on 200 in /home/soa/sftpdestination you should see a file

How to specify a destination filename, and how to put some content into it, who knows.... we shall discover



Linux SFTP openSSH server


cat /etc/ssh/sshd_config
cat: /etc/ssh/sshd_config: Permission denied



which sftp
/usr/bin/sftp


ps -ef | grep sshd
root      4891  6172  0 02:10 ?        00:00:00 sshd: soa [priv]
soa       4893  4891  0 02:10 ?        00:00:00 sshd: soa@pts/0
root      6172     1  0 Jun26 ?        00:00:00 /usr/sbin/sshd
soa       7158  4894  0 02:13 pts/0    00:00:00 grep sshd


ls -ltr /usr/libexec/openssh/sftp-server
-rwxr-xr-x 1 root root 53312 Jan 4 2012 /usr/libexec/openssh/sftp-server
ps -ef | grep sftp
soa      29622 26654  0 02:41 pts/0    00:00:00 grep sftp


man sshd and man sshd_config



Wednesday, November 21, 2012

SYN_SENT hitting firewall, 2 minutes timeout

I am trying to connect to a Oracle RAC instance protected by a firewall.

jdbc_url='jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=theDNSentryforRAC.acme.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=srv_osb)))'
username='soainfra'
password='soainfra1'
driver = "oracle.jdbc.OracleDriver"
from com.ziclix.python.sql import zxJDBC
conn = zxJDBC.connect(jdbc_url, username, password, driver)

this request times out after 2 minutes, with an error

DatabaseError: The Network Adapter could not establish the connection [SQLCode: 20], [SQLState: 61000]

in the meantime, I monitor the connection with:
netstat -an | grep 1521
tcp 0 1 ::ffff:10.56.5.185:58597 ::ffff:10.56.13.112:1521 SYN_SENT

the IP 10.56.13.112 corresponds to one of the 2 RAC instances, and the initial :ffff represents a IPv4 address translated into IPv6

As very welle explained here http://www.faqs.org/docs/iptables/tcpconnections.html, the SYN_SENT is the first phase of the handshake to establish a connection, and its default timeout is 2 minutes.

The interesting thing is that if I specify port 1552, on which NOTHING is listening, the error
DatabaseError: The Network Adapter could not establish the connection [SQLCode: 20], [SQLState: 61000]

is returned IMMEDIATELY, which makes me think that I can actually contact the DB, but for some reason the DB is unable to send me back the SYN-ACK. Still investigating.

The connection waiting for an SYN-ACK is called an "embrionic connection" http://en.wikipedia.org/wiki/TCP_half-open

http://www.ibm.com/developerworks/aix/library/au-aixnetworkproblem2/index.html : a status of SYN_SENT indicates that a three-way handshake has been initiated by your host, but as yet no acknowledgement has been received from the target host. This could mean that there's a route to the target but no route back for this type of traffic. In this situation, ask the network administrator whether any firewalls on the route back are blocking this type of traffic.

WLST or python : how to print environment variables

import os
print print os.environ


{'WL_HOME': '/opt/oracle/fmw11_1_0_2/wlserver_10.3', 'DISPLAY': 'localhost:10.0', 'WEBLOGIC_CLASSPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/opt/oracle/java/lib/tools.jar:/opt/oracle/fmw11_1_0_2/utils/config/10.3/config-launch.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/weblogic_sp.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/weblogic.jar:/opt/oracle/fmw11_1_0_2/modules/features/weblogic.server.modules_10.3.2.0.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/webservices.jar:/opt/oracle/fmw11_1_0_2/modules/org.apache.ant_1.7.0/lib/ant-all.jar:/opt/oracle/fmw11_1_0_2/modules/net.sf.antcontrib_1.0.0.0_1-0b2/lib/ant-contrib.jar', 'G_BROKEN_FILENAMES': '1', 'LESSOPEN': '|/usr/bin/lesspipe.sh %s', 'XFILESEARCHPATH': '/usr/dt/app-defaults/%L/Dt', 'MEM_ARGS': '-Xms32m -Xmx200m -XX:MaxPermSize=128m', 'JAVA_USE_64BIT': 'true', 'POINTBASE_HOME': '/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase', 'PATH': '/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/bin:/opt/oracle/fmw11_1_0_2/modules/org.apache.ant_1.7.0/bin:/opt/oracle/java/jre/bin:/opt/oracle/java/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/myuser/bin', 'POINTBASE_TOOLS': '/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbtools57.jar', 'LOGNAME': 'myuser', 'MODULES_DIR': '/opt/oracle/fmw11_1_0_2/modules', 'SHLVL': '3', 'HISTSIZE': '1000', 'ANT_HOME': '/opt/oracle/fmw11_1_0_2/modules/org.apache.ant_1.7.0', 'JAVA_OPTIONS': ' -Xverify:none', 'INPUTRC': '/etc/inputrc', 'SHELL': '/bin/bash', 'SSH_TTY': '/dev/pts/0', 'WLS1032_PATCH_LIBPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/native', 'WLS1032_PATCH_PATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/native', 'PRODUCTION_MODE': '', 'FEATURES_DIR': '/opt/oracle/fmw11_1_0_2/modules/features', 'PATHSEP': ':', 'HOSTNAME': 'myhost', 'PATCH_PATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/native', 'WLS1032_PATCH_EXT_DIR': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sysext_manifest_classpath', 'PATCH_CLASSPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar', 'BEA_HOME': '/opt/oracle/fmw11_1_0_2', 'MAIL': '/var/spool/mail/myuser', 'LANG': 'en_US.UTF-8', 'LS_COLORS': 'no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:', 'LD_LIBRARY_PATH': '/usr/lib/jvm/java-1.6.0-sun-1.6.0.33.x86_64/jre/lib/amd64/server:/usr/lib/jvm/java-1.6.0-sun-1.6.0.33.x86_64/jre/lib/amd64:/usr/lib/jvm/java-1.6.0-sun-1.6.0.33.x86_64/jre/../lib/amd64:/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/native:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/native/linux/x86_64:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/native/linux/x86_64/oci920_8', 'SSH_CONNECTION': '10.140.21.90 50600 10.56.5.185 22', 'HOME': '/home/myuser', 'PWD': '/opt/oracle', 'WEBLOGIC_EXTENSION_DIRS': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sysext_manifest_classpath', 'PATCH_LIBPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/native', 'JAVA_VENDOR': 'Sun', 'POINTBASE_CLASSPATH': ':/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbembedded57.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbclient57.jar', 'NLSPATH': '/usr/dt/lib/nls/msg/%L/%N.cat', 'TMOUT': '900', 'ANT_CONTRIB': '/opt/oracle/fmw11_1_0_2/modules/net.sf.antcontrib_1.0.0.0_1-0b2', 'SSH_CLIENT': '10.140.21.90 50600 22', 'USER': 'myuser', 'JAVA_HOME': '/opt/oracle/java', 'CLASSPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/opt/oracle/java/lib/tools.jar:/opt/oracle/fmw11_1_0_2/utils/config/10.3/config-launch.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/weblogic_sp.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/weblogic.jar:/opt/oracle/fmw11_1_0_2/modules/features/weblogic.server.modules_10.3.2.0.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/server/lib/webservices.jar:/opt/oracle/fmw11_1_0_2/modules/org.apache.ant_1.7.0/lib/ant-all.jar:/opt/oracle/fmw11_1_0_2/modules/net.sf.antcontrib_1.0.0.0_1-0b2/lib/ant-contrib.jar:::/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbembedded57.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbclient57.jar:/opt/oracle/fmw11_1_0_2/wlserver_10.3/common/eval/pointbase/lib/pbtools57.jar', 'TERM': 'xterm', 'CLASSPATHSEP': ':', 'WLS1032_PATCH_CLASSPATH': '/opt/oracle/fmw11_1_0_2/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar', 'JAVA_VM': '-client', '_': '/bin/env'}

for a specific value:
print os.environ['JAVA_HOME']



Tuesday, November 20, 2012

Monitoring your Oracle DB Tablespace

To get Database utilization

select    round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
    round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
, round(((sum(used.bytes) - free.p) / sum(used.bytes)) * 100) || '% ' "PERC UTIL"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p;

--- To check TABLE SPACE UTILIZATION

-- Assumption: using "TBS_DATA" table space

select a.tablespace_name                                              name,
       b.tablespace_name                                              dummy,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) / 1024 / 1024 / 1024 || ' GB' "Database Size" ,
       ((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
       sum(a.bytes)/count( distinct b.file_id )) / 1024/ 1024/ 1024) || ' GB' "Used Table Space",
       round(sum(a.bytes)/count( distinct b.file_id ) / 1024/ 1024/ 1024) || ' GB' "Free Table Space",
       round( 100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
               (sum(a.bytes)/count( distinct b.file_id ) )) /
       (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))) || '%' "PERCENT UTIL"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = 'TBS_DATA'
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;


(thank you Sambhav for the scripts)

Another cool script :



select t.tablespace_name, f.file_name, round(bytes/1024/1024/1024) GB, AUTOEXTENSIBLE, round(maxbytes/1024/1024/1024) MAX_GB
from dba_tablespaces t, dba_data_files f
where t.tablespace_name=f.tablespace_name
order by 1,2;






Monday, November 19, 2012

Oracle DB: alter table add column after

Terrible news, in Oracle DB you cannot add a column in a specific position, it will always be added at the end.

Why would that matter? Because if you use this statement to copy data from one table to the other:

insert into ONE select * from TWO

you rely on ONE and TWO having the same position for each corresponding column.

Here some tips on how to work around (they all entail creating a new table, which sucks):
http://www.orafaq.com/faq/how_does_one_add_a_column_to_the_middle_of_a_table


The safest way is to add at the bottom and in order to copy use:

insert into ONE (COL1, COL2) select COL2, COL1 from TWO; 

and avoid the more generic statement

insert into ONE select * from TWO 




Sunday, November 18, 2012

Installing SQLPlus on Linux

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm


you end up with

/usr/bin/sqlplus64
/usr/lib/oracle/11.2/client64/bin/sqlplus


When you run them, you get:
/usr/bin/sqlplus64: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

add
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:$LD_LIBRARY_PATH
to your .bash_profile file

umask 022 or 002

We have 2 separate WebLogic domains, with 2 separate Linux users: soa and soa2.

It is important to SEPARATE these 2 users, so that if I login as soa, I can do operations (write access) only on the soa domain.

A possible approach is having soa and soa2 being member of the same group "soa", and imposing that both users have umask 022 .

umask 022
Normally you set this value in the /etc/profile startup file

UID=`id -ru`
if [ $UID -gt 99 ] && [ "`id -gn`" = "`id -un`" ]; then
    umask 002
else
    umask 022
fi


"id -ru" is the numeric ID (example: 1004)

-r print the real ID instead of the effective ID
-u print only the effective user ID
-g print only the effective group ID
-n print a name instead of a number




Thursday, November 15, 2012

Cheat sheet (quick tutorial) on JSTL

Insert tags

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

 _______

Set a JSTL variable from Java

<c:set scope="session" value="<%=propertyMap.get("debugsql")%>" var="debugsql"></c:set>

_______

Print value of all httprequest parameters:

<c:forEach var="par" items="${paramValues}">
    <c:out value="${par.key}=${par.value[0]}"></c:out>
</c:forEach>

_______

Read in Java a JSTL property with session scope

Object mydomainObj = session.getAttribute("mydomain");
String mydomain = (mydomainObj == null) ? "" : mydomainObj.toString();



_______

Check is a JSTL variable is null:

<c:if test="${not empty param.nesoadomain}">


_______

Run a SQL query:

<sql:query var="rsCMDBDS" dataSource="jdbc/cmdb">
select A.JDBCDRIVER, B.URL, B.USERNAME, B.ENCRYPTED_PASSWORD from DATASOURCES A, DATASOURCES_ENV B where A.DSNAME = B.DSNAME and A.DSNAME = 'wlsbjmsrpDataSource' and B.DOMAINNAME = '${mydomain}'
</sql:query>
_______

test for equal

<c:if test="${par.key eq 'synchronizeLocalTable'} ">

test for equal

<c:if test="${par.key ne 'synchronizeLocalTable'} ">
_______

increment a counter in a loop

<c:set var="count" value="0" scope="page" />
<c:set var="count" value="${count + 1}" scope="page"/>

_______



Java Management Extensions with Oracle WebLogic Server 12c Webcast

http://event.on24.com/eventRegistration/EventLobbyServlet?target=lobby.jsp&eventid=535211&sessionid=1&partnerref=WLS_Dev_9_MunzNL_11132012&key=C3C2FD74F5C578D7A01257D70FF828F0&eventuserid=72343812


a decent tutorial on WLST and JMX...

Wednesday, November 14, 2012

An error was encountered getting the attribute DatabaseProductVersion

<Warning> <JMX> <[ACTIVE] ExecuteThread: '41' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <BEA-149515> <An error was encountered getting the attribute DatabaseProductVersion on the MBean com.bea:ServerRuntime=ms1,Name=PIPPODataSourceASIA,Type=JDBCDataSourceRuntime during a call to getAttributes
since a few days we are getting this Warning in the logs.... no clue why... investigating.

With WLST:

wls:/mydomain/serverRuntime/JDBCServiceRuntime/ms1/JDBCDataSourceRuntimeMBeans/PIPPODataSourceASIA> ls
dr-- JDBCDriverRuntime
dr-- LastTask
dr-- WorkManagerRuntimes


Traceback (innermost last):
File "", line 1, in ?
File "", line 707, in ls
File "", line 1848, in raiseWLSTException
WLSTException: Error occured while performing ls : Error while retrieving attribute names and values : javax.management.RuntimeMBeanException: MBean getAttribute failed: java.lang.NullPointerException
Use dumpStack() to view the full stacktrace





dumpStack()

This Exception occurred at Wed Nov 14 17:47:00 CET 2012.
javax.management.RuntimeMBeanException: javax.management.RuntimeMBeanException: MBean getAttribute failed: java.lang.NullPointerException
        at weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:234)
        at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:223)
        at javax.management.remote.rmi.RMIConnectionImpl_1035_WLStub.getAttribute(Unknown Source)
        at weblogic.management.remote.common.RMIConnectionWrapper$12.run(ClientProviderBase.java:769)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
        at weblogic.security.Security.runAs(Security.java:61)
        at weblogic.management.remote.common.RMIConnectionWrapper.getAttribute(ClientProviderBase.java:767)
        at javax.management.remote.rmi.RMIConnector$RemoteMBeanServerConnection.getAttribute(RMIConnector.java:878)
        at weblogic.management.scripting.InformationHandler.la(InformationHandler.java:653)
        at weblogic.management.scripting.InformationHandler.ls(InformationHandler.java:604)
        at weblogic.management.scripting.WLScriptContext.ls(WLScriptContext.java:280)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.python.core.PyReflectedFunction.__call__(Unknown Source)
        at org.python.core.PyMethod.__call__(Unknown Source)
        at org.python.core.PyObject.__call__(Unknown Source)
        at org.python.core.PyObject.invoke(Unknown Source)
        at org.python.pycode._pyx52.ls$43(:696)
        at org.python.pycode._pyx52.call_function()
        at org.python.core.PyTableCode.call(Unknown Source)
        at org.python.core.PyTableCode.call(Unknown Source)
        at org.python.core.PyTableCode.call(Unknown Source)
        at org.python.core.PyFunction.__call__(Unknown Source)
        at org.python.pycode._pyx126.f$0(:1)
        at org.python.pycode._pyx126.call_function()
        at org.python.core.PyTableCode.call(Unknown Source)
        at org.python.core.PyCode.call(Unknown Source)
        at org.python.core.Py.runCode(Unknown Source)
        at org.python.core.Py.exec(Unknown Source)
        at org.python.util.PythonInterpreter.exec(Unknown Source)
        at org.python.util.InteractiveInterpreter.runcode(Unknown Source)
        at org.python.util.InteractiveInterpreter.runsource(Unknown Source)
        at org.python.util.InteractiveInterpreter.runsource(Unknown Source)
        at weblogic.management.scripting.WLST.main(WLST.java:173)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at weblogic.WLST.main(WLST.java:29)
Caused by: javax.management.RuntimeMBeanException: MBean getAttribute failed: java.lang.NullPointerException
        at weblogic.management.jmx.modelmbean.WLSModelMBean.getAttribute(WLSModelMBean.java:553)
        at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:666)
        at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:638)
        at weblogic.management.jmx.mbeanserver.WLSMBeanServerInterceptorBase$12.run(WLSMBeanServerInterceptorBase.java:326)
        at java.security.AccessController.doPrivileged(Native Method)
        at weblogic.management.jmx.mbeanserver.WLSMBeanServerInterceptorBase.getAttribute(WLSMBeanServerInterceptorBase.java:324)
        at weblogic.management.mbeanservers.internal.JMXContextInterceptor.getAttribute(JMXContextInterceptor.java:157)
        at weblogic.management.jmx.mbeanserver.WLSMBeanServerInterceptorBase$12.run(WLSMBeanServerInterceptorBase.java:326)
        at java.security.AccessController.doPrivileged(Native Method)
        at weblogic.management.jmx.mbeanserver.WLSMBeanServerInterceptorBase.getAttribute(WLSMBeanServerInterceptorBase.java:324)
        at weblogic.management.mbeanservers.internal.SecurityInterceptor.getAttribute(SecurityInterceptor.java:299)
        at weblogic.management.jmx.mbeanserver.WLSMBeanServer.getAttribute(WLSMBeanServer.java:279)
        at weblogic.management.mbeanservers.internal.JMXConnectorSubjectForwarder$5$1.run(JMXConnectorSubjectForwarder.java:326)
        at java.security.AccessController.doPrivileged(Native Method)
        at weblogic.management.mbeanservers.internal.JMXConnectorSubjectForwarder$5.run(JMXConnectorSubjectForwarder.java:324)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
        at weblogic.management.mbeanservers.internal.JMXConnectorSubjectForwarder.getAttribute(JMXConnectorSubjectForwarder.java:319)
        at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1404)
        at javax.management.remote.rmi.RMIConnectionImpl.access$200(RMIConnectionImpl.java:72)
        at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1265)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1367)
        at javax.management.remote.rmi.RMIConnectionImpl.getAttribute(RMIConnectionImpl.java:600)
        at javax.management.remote.rmi.RMIConnectionImpl_WLSkel.invoke(Unknown Source)
        at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:667)
        at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:522)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:146)
        at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:518)
        at weblogic.rmi.internal.wls.WLSExecuteRequest.run(WLSExecuteRequest.java:118)
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)
Caused by: java.lang.NullPointerException
        at weblogic.jdbc.sqlserverbase.BaseDatabaseMetaData.getDatabaseProductName(Unknown Source)
        at weblogic.jdbc.common.internal.DataSourceRuntimeMBeanImpl.getDatabaseProductName(DataSourceRuntimeMBeanImpl.java:773)
        at sun.reflect.GeneratedMethodAccessor18308.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at weblogic.management.jmx.modelmbean.WLSModelMBean.getAttribute(WLSModelMBean.java:525)
        ... 31 more
javax.management.RuntimeMBeanException: javax.management.RuntimeMBeanException: MBean getAttribute failed: java.lang.NullPointerException




Tuesday, November 13, 2012

Cheat sheet on how to write shell scripts

at the to, put:
#!/bin/bash


generate timestamp: `date +"%Y%m%d_%H%M"`

to append each command output to a log file AND send to console :
command | tee -a mylog.log

checking result of last command (it's the $? ):
if [ $? -ne 0 ]
then
echo OK
else
echo KO
fi


to assign positional parameters:
set -- BLA
the result is that $1=BLA





Sunday, November 11, 2012

javax.servlet.ServletException: java.sql.SQLException: Closed Connection

When using JSTL setDataSource:

<sql:setDataSource var="domainds" driver="${DS_JDBCDRIVER}" url="${DS_URL}" user="${DS_USERNAME}" password="${DS_ENCRYPTED_PASSWORD}" scope="session"/>

as documented here

http://docs.oracle.com/javaee/5/jstl/1.1/docs/tlddocs/sql/setDataSource.html

and then I use the var datasource as in:

<sql:query var="rsMessageCount" dataSource="${domainds}" scope="request"> select DATA_VALUE from ${datatable} where MSG_GUID='${theMSGGUIDvalue}' </sql:query>

it all works fine until I try to do a

 java.sql.Blob blob = (java.sql.Blob)((org.apache.taglibs.standard.tag.common.sql.ResultImpl)(request.getAttribute("rsMessageCount"))).getRowsByIndex()[0][0];
 if (blob != null) {
  byte[] bdata = blob.getBytes(1, (int) blob.length());
  String text = new String(bdata);



When I do the blob.getBytes bit, I get a

java.sql.SQLException: Closed Connection oracle.sql.BLOB.getDBAccess(BLOB.java:1087) oracle.sql.BLOB.getBytes(BLOB.java:331) oracle.sql.BLOB.getBytes(BLOB.java:217)

This ONLY if I use setDataSource. If I use a datasource declared in the context.xml file, I have no issue.

This is the only way I managed to make it work:



    public static String getBlobData(String driver, String url, String username, String password, String sqlQuery) throws Exception {
 String result = "";
 Class.forName(driver);
 Connection conn = DriverManager.getConnection(url, username, password);
 try {
     Statement stmt = conn.createStatement();
     try {
  ResultSet rset = stmt.executeQuery(sqlQuery);
  try {
      rset.next();
      java.sql.Blob blob = (java.sql.Blob) rset.getBlob(1);
      if (blob != null) {
   byte[] bdata = blob.getBytes(1, (int) blob.length());
   String text = new String(bdata);
   text = text.replace("<", "<"); // the second is ampersand followed by lt;
   result = text;
      } else {
   result = "empty body";
      }

  } finally {
      try {
   rset.close();
      } catch (Exception e) {
   e.printStackTrace();
      }
  }
     } finally {
  try {
      stmt.close();
  } catch (Exception e) {
      e.printStackTrace();
  }
     }
 } finally {
     try {
  conn.close();
     } catch (Exception e) {
  e.printStackTrace();
     }
 }
 return result;
    }




Friday, November 9, 2012

WebLogic bug: when setting Override Delivery Mode to No-Delivery

cvc-enumeration-valid: string value 'No-Delivery' is not a valid enumeration value for delivery-mode-type

An error occurred during activation of changes, please see the log for details. Message icon - Error [Management:141191]The prepare phase of the configuration update failed with an exception: Message icon - Error VALIDATION PROBLEMS WERE FOUND problem: cvc-enumeration-valid: string value 'No-Delivery' is not a valid enumeration value for delivery-mode-type in namespace http://xmlns.oracle.com/weblogic/weblogic-jms:<null>

see also https://forums.oracle.com/forums/thread.jspa?threadID=985941 where they suggest a manual workaround, and http://middlewaremagic.com/weblogic/?page_id=1976 ....






start-mdbs-with-application

http://docs.oracle.com/cd/E17904_01/web.1111/e15493/summary.htm


"Controls when MDBs start processing messages. When set totrue, an MDB starts processing messages as soon as it is deployed, even if WebLogic Server has not completed booting. This can cause an MDB application to access uninitialized services or applications during boot up and, therefore, to fail. Set to false to defer message processing until after WebLogic Server opens its listen port."

In OSB, a ProxyService consuming messages from a JMS queue is deployed as a MDB. Its weblogic-application.xml is:


<?xml version='1.0' encoding='UTF-8'?>
<web:weblogic-application xmlns:web="http://xmlns.oracle.com/weblogic/weblogic-application">
  <web:ejb>
    <web:start-mdbs-with-application>false</web:start-mdbs-with-application>
  </web:ejb>
  <web:application-param>
    <web:param-name>service-ref</web:param-name>
    <web:param-value>ProxyService$OSBProject$ProxyServices$MyJMSService</web:param-value>
  </web:application-param>
  <web:listener>
    <web:listener-class>com.bea.wli.sb.transports.jms.JmsEndPointAppListener</web:listener-class>
  </web:listener>
</web:weblogic-application>


The parameter is set to false for all the OSB JMS services.

Thursday, November 8, 2012

Shriking or Resetting a connection pool

http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13952/pagehelp/JDBCjdbcdatasourcesjdbcdatasourcemonitorstatisticstitle.html

Active Connections Current Count: Active connections are connections in use by an application.

http://docs.oracle.com/cd/E13222_01/wls/docs92/jdbc_admin/manage.html#wp1048129


Force suspending a Data Source:
Suspends a data source that has the health state of Running, including disconnecting all current connection users. All current connections are closed and recreated.

Shrink:
Shrinks the database connection pool in the data source to either the current number of reserved connections or the initial size of the connection pool, which ever is greater.



Wednesday, November 7, 2012

Buds sprouting in November (Lausanne, Switzerland)


Picture taken today, 7th of November, in Lausanne, Switzerland.
One can notice that, next to autumn leaves about to fall, there are already buds sprouting.

If you think this is cool, wait until the next major crop failure and widespread famine in countries less rich than Switzerland.

Welcome to the end of Anthropocene, the era of diverging climate crisis, of collapsing ecosystems and widespread violence.