Using oracle explain plan

1) Make sure the plan table is present
desc plan_table

2) If not, create it
@?/rdbms/admin/utlxplan

3) If the plan_table is old version, drop it then re-create using (2).
drop table plan_table;

4) Run the "explain plan for my_sql_statement"
EXPLAIN PLAN FOR
select * from emp;

5) Query the plan
set linesize 132
SELECT * FROM TABLE( dbms_xplan.display);


Reference:
http://www.orafaq.com/wiki/SQL*Plus_FAQ

Some tips for sqlplus

1) Display current setting
SQL> show linesize
linesize 100

SQL> show all
...


2) Improve formatting
set linesize 120
set pagesize 20
column somestring format a30
column someint format 999,999,999
column somefloat format 999,999,999.99


3) Show database initialization parameters
SQL> set linesize 150
SQL> column NAME_COL_PLUS_SHOW_PARAM format a35
SQL> column VALUE_COL_PLUS_SHOW_PARAM format a35
SQL> show parameters


4) Current definitions
SQL> define
...
SQL> define _EDITOR="vi"


5) Generate HTML
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

XAException in WebSphere AS with Oracle DB

Solution:

1) Run 2 scripts in $ORACLE_HOME/javavm/install
initxa.sql
initjvm.sql

2) Grant correct permission to db user
grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to <>;


Reference:
http://www-1.ibm.com/support/docview.wss?uid=swg21215890
http://www-1.ibm.com/support/docview.wss?rs=0&q1=3979190&uid=swg21196663&loc=en_US&cs=utf-8&cc=us&lang=en

Alternatively, delete off the transaction logs
$WAS_HOME/DeploymentManager/tranlog/dmgr/transaction
$WAS_HOME/AppServer/tranlog/$instance/transaction

Using JRockIt Runtime Analyzer

1) Get the development license
http://commerce.bea.com/downloadproduct.jsp?family=JRMC&major=3.0&minor=0&delivery=1&os=All&intent=purchase

2) Place the license in $JROCKIT_HOME/jre/license.bea

3) Start JRA recording from command
cd $JROCKIT_HOME/bin
./jrcmd $PID startjrarecording recordingtime=600 filename=$NAME.`date +%Y%m%d%H%M`.xml

Note: Help can be found by running
./jrcmd $PID help

4) Analyze the result using JRA Analyzer
export DISPLAY=$IP:0
cd $JROCKIT_HOME/bin
./jrmc &


Reference:
http://dev2dev.bea.com/pub/a/2007/12/jrockit-tuning.html?page=3

Change WAS 5.1.x default log level

1) Create a file "commons-logging.properties" in /properties
org.apache.commons.logging.LogFactory=org.apache.commons.logging.impl.LogFactoryImpl
org.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger

2) Edit logging.properties in /jre/lib
Change the line .level = INFO to .level = ERROR|WARN

# For example, set the com.mypackage.myapp logger to only log SEVERE
# messages:
com.mypackage.myapp.level = SEVERE

Oracle on HP-UX - lock SGA

If oracle instance in HP-UX is set to lock sga as below:

ALTER SYSTEM SET lock_sga=TRUE SCOPE=SPFILE;

There will be error:-

SQL> ORA-27126: unable to lock shared memory segment in core
HP-UX Error: 1: Not owner

Solution:
1) Create a file /etc/privgroup with this entry
dba MLOCK RTSCHED RTPRIO

2) Run
# setprivgroup -f /etc/privgroup

3) Check that above is successful
# getprivgrp
global privileges: CHOWN
dba: RTPRIO MLOCK RTSCHED

Reference:
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=729423

Use WebSphere MQ 6 with WLS 9.2 MP1

0) Copy providerutil.jar & fscontext.jar from <MQM_HOME>/java/lib to <DOMAIN_HOME>/lib

1) Create .bindings file

$ jmsAdmin.sh -cfg myConfig.config
def qcf(myQCF) qmanager(myQM) host(hostname) transport(client) port(1414)
def q(myQ) queue(myQ)

=== jmsAdmin.sh ===
JAVA_HOME=[PATH_TO_JAVA]
MQLIB=/opt/mqm/java/lib
CP=$MQLIB

for i in $MQLIB/*.jar
do
CP=$CP:$i
done

$JAVA_HOME/bin/java -classpath $CP com.ibm.mq.jms.admin.JMSAdmin $@

=== myConfig.config ===
INITIAL_CONTEXT_FACTORY=com.sun.jndi.fscontext.RefFSContextFactory
PROVIDER_URL=file:[some_directory]
SECURITY_AUTHENTICATION=none


2) Define JMS module from admin console -> Services -> Messaging -> JMS Modules -> New
- enter a name and leave the rest empty

3) Create new resource type "Foreign Server"
Name: Anyname
JNDI Initial Context Factory: com.sun.jndi.fscontext.RefFSContextFactory
JNDI Connection URL: file:[some_directory]
Default Targeting Enabled: true

4) Add destination & connection factory
Name: [AS PER MDB]
Local JNDI Name: [SAME AS ABOVE]
Local JNDI Name: [SAME AS ABOVE]

Tracing system calls in HP-UX 11.x

The tool to use is tusc. Available from http://hpux.cs.utah.edu/hppd/hpux/Sysadmin/tusc-7.8/.

1) Download the file (it is in .shar)
2) Unpack is by running (may need to strip off the ^M characters first from the .shar file)
# sh tusc.xx.shar
3) There will be a binary named "tusc" and some other files.
4) tusc -help

Below is the output from tusc -help:

Usage: tusc [-]  -OR- 
       -a: show exec arguments
       -A: append to output file
       -b bsize: dump 'bsize' max bytes (-r/-w)
       -c: count syscalls instead of printing trace
       -d [+][!][fd | all]: select only syscalls using fd
       -e: show environment variables
       -E: show syscall entries
       -f: follow forks
       -F: show kernel's ttrace feature level
       -g: don't attach to members of my session
       -h: show state of all processes when idle
       -i: don't display interruptible syscalls
       -I start[/stop]: single-step and show instructions
       -k: keep alive (wait for *all* processes)
       -l: print lwpids
       -n: print process names
       -o [file|fd]: send trace output to file or fd
       -p: print pids
       -Q: be quiet about some warnings
       -r [!][fd | all]: dump read buffers
       -R: show syscall restarts
       -s [!]syscalls: [un]select these syscalls
       -S [!]signals: [un]select these signals
       -t: detach process if it becomes traced
       -T timestamp: print time stamps
       -u: print user thread IDs (pthreads)
       -v: verbose (some system calls only)
       -V: print version
       -w [!][fd | all]: dump write buffers
       -x: print raw (hex) arguments
       -z: only show failing syscalls

Similar system calls tracing tools exist for other unixes:
AIX: truss
Linux: strace
Linux 64bit: strace32
Solaris: truss

Certificate - Convert PEM to PFX

Required items:

1) Private key in pem format
2) Certificate in pem format
3) CA certificate in pem format
4) Openssl

Command:
openssl pkcs12 -export -in mycert.pem -inkey mykey.pem -certfile ca_cert.pem -out mycert.pfx

Implement tcp wrapper in aix 5.3

0) Ensure gcc is installed and cc is linked
# which gcc
# which cc
# cd /usr/bin
# ln -s gcc cc

1) Compile tcp wrapper (read README for info)
# cd /tmp/tcp_wrappers_7.6
# make aix

2) Copy tcpd to /usr/tcpwrapper
# mkdir /usr/tcpwrapper
# cp tcpd /usr/tcpwrapper

3) Edit service to use tcpd (in this case is ftpd)
# vi /etc/inetd.conf

Original
#ftp stream tcp6 nowait root /usr/sbin/ftpd ftpd

Becomes
ftp stream tcp nowait root /usr/tcpwrapper/tcpd ftpd

4) Add in /etc/hosts.allow and /etc/hosts.deny
hosts.deny
ALL: ALL

hosts.allow
ftpd: localhost

5) tail –f /var/log/mail.log

Possible tuning for oracle in linux

1) I/O Scheduler
- use the deadline scheduler (default is completely fair queueing CFQ)
Edit grub.conf boot parameter elevator=deadline must be passed to the kernel as below:

title Red Hat Enterprise Linux Server (2.6.18-8.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-8.el5 ro root=/dev/sda2 elevator=deadline
initrd /initrd-2.6.18-8.el5.img


2) Maximum open FD for oracle user
Edit /etc/security/limits.conf
oracle soft nofile 4096
oracle hard nofile 63536


3) Set "disk_asynch_io=TRUE" and "filesystemio_options=setall" in oracle parameter
SQL> show parameter disk_asynch_io filesystemio_options;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
filesystemio_options string setall


4) Huge Pages
/proc/sys/vm/nr_hugepages
Increase the ulimit parameter "memlock" for the oracle user (ulimit -l)
Edit /etc/security/limits.conf
oracle soft memlock 1048576
oracle hard memlock 1048576

WAS 5.1.1.x installation (Network Deployment + Base) in HPUX

Installation

As root, do below steps:

1) Unpack the Network Deployment installer "c53t9ml.tar"
# mkdir /home/wasadm/WASND
# cd /home/wasadm/WASND
# tar xvf ../installers/c53t9ml.tar

2) Perform installation
Start X on your PC
# export DISPLAY={IP_address}:0
# cd /home/wasadm/WASND/hpux
# ./install

3) Unpack the Base installer "c53inml.tar"
# mkdir /home/wasadm/WAS
# cd /home/wasadm/WAS
# tar xvf ../installers/c53inml.tar

4) Perform installation
Start X on your PC
# export DISPLAY={IP_address}:0
# cd /home/wasadm/WAS/hpux
# ./install

5) Unpack fixpack
# mkdir /home/wasadm/WASFIX
# cd /home/wasadm/WASFIX
# tar xvf ../installers/was51_fp1_hpux.tar

6) Install fixpack
# . /usr/WebSphere/DManager/bin/setupCmdLine.sh
# cd /home/wasadm/WASFIX
# ./updateWizard.sh

7) Unpack cumulative fix
# mkdir /home/wasadm/5-WASNDCFIX
# cd /home/wasadm/5-WASNDCFIX
# jar xvf ../installers/was511_nd_cf14_hpux.zip

8) Install cumulative fix
# ./updateWizard.sh

9) Unpack JDK patch
# mkdir 7-JDKSR7
# cd 7-JDKSR7/
# jar xvf ../installers/was511_SR7_jdk_hpux.zip

10) Install to ND and Base
# ./updateWizard.sh


Post-installation

1) Change ownership of all to wasadm:wasgrp
# chown –R –h wasadm:wasgrp /usr/WebSphere


Create cluster

1) Start ND
$ cd /usr/WebSphere/DManager/bin
$ ./startManager.sh

2) Set heap size and verbose GC for ND
Console -> System Administration -> Deployment Manager -> Process Definition -> Java Virtual Machine
Initial Heap Size = 256
Maximum Heap Size = 256
Generic JVM Arguments = -Xverbosegc:file=/usr/WebSphere/DManager/logs/dmgr/gc.log

3) Restart ND
$ cd /usr/WebSphere/DManager/bin
$ ./stopManager.sh
$ ./startManager.sh

4) Federate node to ND
$ cd /usr/WebSphere/AppServer/bin
$ ./addNode.sh raven -startingport 3000

5) Set heap and verbose GC for node agent
Console -> System Administration -> Node Agents -> nodeagent -> Process Definition -> Java Virtual Machine
Initial Heap Size = 128
Maximum Heap Size = 128
Generic JVM Arguments = -Xverbosegc:file=/usr/WebSphere/AppServer/logs/nodeagent/gc.log

6) Set heap and verbose GC for App Server
Console -> Application Servers -> server1 -> Process Definition -> Java Virtual Machine
Initial Heap Size = 512
Maximum Heap Size = 512
Generic JVM Arguments = -Xverbosegc:file=/usr/WebSphere/AppServer/logs/server1/gc.log

7) Create cluster from console
Console -> Cluster -> New

8) Start app server
Use console or
$ cd /usr/WebSphere/AppServer/bin
$ ./startServer.sh {server_name}

9) Optionally, create new app server with better naming convention and delete the old one
Console -> Application Servers -> New
Console -> Internal Replication Domains -> {name} -> Replicator Entries -> {name}
Replicator port = 7874
Client port = 7873


To enable security using FileRegistry

1) Create users.props in this format
# Format:
# name:passwd:uid:gids:display name
# where name = userId/userName of the user
# passwd = password of the user
# uid = uniqueId of the user
# gid = groupIds of the groups that the user belongs to
# display name = a (optional) display name for the user.
boot:password:199:100:admin
admin:password:100:100:admin
operator:password:200:200:operator
user:password:300:300:user

2) Create groups.props in this format
# Format:
# name:gid:users:display name
# where name = groupId of the group
# gid = uniqueId of the group
# users = list of all the userIds that the group contains
# display name = a (optional) display name for the group.
admins:100:admin:Administrative group
operators:200:operator:Operators group
users:300:user:Users Group

3) In custom user registry, enter
Server User ID = {Valid user in users.props}
Server User Password = {password for user as above}
Custom Registry Classname = com.ibm.websphere.security.FileRegistrySample

Custom properties name : value
usersFile : {location to users.props}
groupsFile : {location to groups.props}

4) Add relevant users to "Console Users"

5) users.props & groups.props need to exist in all servers


NOTE: After enabling security, need to put in “soap.client.props” to avoid getting errors during shutdown or client calls
# JMX SOAP connector identity
com.ibm.SOAP.loginUserid=boot
com.ibm.SOAP.loginPassword=password

To encode the above:
$ /usr/WebSphere/DManager/bin
$ ./PropFilePasswordEncoder.sh ../properties/soap.client.props com.ibm.SOAP.loginPassword

HP-UX system & performance info

uname -a > /tmp/HP_info
model >> /tmp/HP_info
uptime >> /tmp/HP_info
swlist -l bundle >> /tmp/HP_info
bdf >> /tmp/HP_info
swapinfo -ta >> /tmp/HP_info
cat /etc/fstab >> /tmp/HP_info
lvlnboot -v >> /tmp/HP_info
vgdisplay -v >> /tmp/HP_info
lanscan >> /tmp/HP_info
ioscan -fn >> /tmp/HP_info
cat /etc/inittab >> /tmp/HP_info
dmesg >> /tmp/HP_info
crontab -l >> /tmp/HP_info
swlist -l fileset >> /tmp/HP_info
/usr/bin/top -f /tmp/HP_perf_info
cat /stand/system >> /tmp/HP_perf_info
nohup sar -qM 1 600 > /tmp/HP_perf_info.report &
nohup sar -uM 1 600 > /tmp/HP_perf_info.cpu &
nohup sar -b 1 600 > /tmp/HP_perf_info.buffer &
nohup sar -d 1 600 > /tmp/HP_perf_info.disk &
nohup sar -w 1 600 > /tmp/HP_perf_info.swap &
nohup sar -v 1 600 > /tmp/HP_perf_info.inode &
sysdef >> /tmp/HP_perf_info

Linux kernel parameters for WLS

echo "500000" > /proc/sys/fs/file-max
echo "10000000" > /proc/sys/net/core/optmem_max
echo "10000000" > /proc/sys/net/core/rmem_default
echo "10000000" > /proc/sys/net/core/rmem_max
echo "10000000" > /proc/sys/net/core/wmem_default
echo "10000000" > /proc/sys/net/core/wmem_max
echo "2000000" > /proc/sys/net/ipv4/tcp_max_tw_buckets
echo "30000000 30000000 30000000" > /proc/sys/net/ipv4/tcp_mem
echo "30000000 30000000 30000000" > /proc/sys/net/ipv4/tcp_rmem
echo "30000000 30000000 30000000" > /proc/sys/net/ipv4/tcp_wmem
echo "0" > /proc/sys/net/ipv4/tcp_timestamps
echo "12000" > /proc/sys/net/ipv4/tcp_max_syn_backlog
echo "10000" > /proc/sys/net/core/somaxconn
echo "1" > /proc/sys/net/ipv4/ip_forward
echo "400000" > /proc/sys/net/core/netdev_max_backlog
echo "9663676416" > /proc/sys/kernel/shmmax
echo "9663676416" > /proc/sys/kernel/shmall
#/etc/init.d/irqbalance stop
ifconfig eth0 txqueuelen 30000
#ifconfig eth1 txqueuelen 30000
#eth0irq=`cat /proc/interrupts | grep eth0 | grep -v grep | awk '{print $1}' | sed s/://`
#eth1irq=`cat /proc/interrupts | grep eth1 | grep -v grep | awk '{print $1}' | sed s/://`
#megasasirq=`cat /proc/interrupts | grep megasas | grep -v grep | awk '{print $1}' | sed s/://`
#echo "01" > /proc/irq/${eth0irq}/smp_affinity
#echo "02" > /proc/irq/${eth1irq}/smp_affinity
#echo "04" > /proc/irq/${megasasirq}/smp_affinity

WAS 6.x installation

1) Install WAS and create Dmgr profile after the install
./wasprofile.sh \
-create \
-profileName Dmgr \
-profilePath <was_base>/profiles/Dmgr \
-templatePath <was_base>/profileTemplates/dmgr \
-nodeName myCellManager \
-cellName myCell \
-hostName <hostname>

2) Create appserver profile using command line
./wasprofile.sh \
-create \
-profileName MYAP \
-profilePath <was_base>/profiles/MYAP \
-templatePath <was_base>/profileTemplates/default \
-nodeName myCellNode01 \
-cellName myCell01 \
-hostName <hostname>

3) Federate appserver to Dmgr
./addNode.sh <hostname>


OPTIONAL
========
1) Install IBM HTTP Server

2) Install Plugins

3) Run webserver plugin configuration manually.
<was_base>/Plugins/bin/configurewebserver1.sh

4) Enable security

** Installation as root, ownership change after all done

WLS 6,7,8,9.x in HP-UX 11i

1) Check kernel parameter (below listed is minimum):
$ /usr/sbin/kmtune [-l] [[-q name]...]

max_thread_proc 4096 - 4096
maxfiles 4096 - 4096
maxfiles_lim 8192 - 8192
nfile 124898 - (15*NPROC+2048)
maxusers 512 - 512
nkthread 8192 - (2*MAX_THREAD_PROC)
nproc 8192 - 8192
ncallout 8208 - (NKTHREAD+16)

/usr/sbin/kmtune -q max_thread_proc -q maxfiles -q maxfiles_lim -q nfile -q maxusers -q nkthread -q nproc -q ncallout


2) Check OS patch
http://h18012.www1.hp.com/java/patches/index.html


3) Check ndd settings
/etc/rc.config.d/nddconf

TRANSPORT_NAME[0]=tcp
NDD_NAME[0]=tcp_time_wait_interval
NDD_VALUE[0]=10000

TRANSPORT_NAME[1]=tcp
NDD_NAME[1]=tcp_fin_wait_2_timeout
NDD_VALUE[1]=10000

TRANSPORT_NAME[2]=tcp
NDD_NAME[2]=tcp_keepalive_detached_interval
NDD_VALUE[2]=10000

TRANSPORT_NAME[3]=tcp
NDD_NAME[3]=tcp_keepalive_interval
NDD_VALUE[3]=10000

Intro

This blog is created to store technical notes from my day-to-day work. If anyone else find this useful, that would be a bonus.