Mysql has a nice “if exists” addition to the drop table statement. If the table to be dropped does not exists, it doesn’t raise an exception but only creates a warning.
In Oracle RDMBS you can emulate this behavior like so:
BEGIN execute immediate 'drop table INSERT_TABLE_NAME_HERE'; EXCEPTION WHEN others THEN IF SQLCODE != -942 THEN RAISE; END IF; END; /
Ugly, but it works very well.
Worlds collide: Oracle and Sun JDK. Perfect start to ruin a not so bad Monday morning.
Background: Need to have a Tomcat Server deployed on a Oracle Enterprise Linux 5 system.
I was happy, when i saw a tomcat5 package in the repositories. Great, i thought. All i need. Well. Not.
Under Windows you’ll get between one and ten JVM versions installed with on Oracle product (slight exaggerated), within the OEL5 there was only one ancient 1.4.2 JDK. *sigh* Did i mention that the application that is supposed to run on that thing uses a buttload of Java 5 features?
“yum search java-”… No Java 5. WtF?
There is no Java 5.
Again, do it yourself:
This is a nice entry that describes howto build rpms for the “official” Sun Java 5 jdk.
I used the following steps to build my rpms:
rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-devel-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-src-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-demo-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-plugin-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-fonts-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-alsa-1.5.0.15-1jpp.i586.rpm rpm -Uvh /usr/src/redhat/RPMS/i586/java-1.5.0-sun-jdbc-1.5.0.15-1jpp.i586.rpm
After that, everything could be fine. Well, it wasn’t:
sun.misc.InvalidJarIndexException: Invalid index
Jehova! Finally not a NPE but something new, at least to me. Sometimes i wonder why i always run into bugs like these.
Some script changes and repackages all jar files in some weird ways so that a standard JDK has funny problems.
My solution to it: Reindex everything in /usr/share/java after you’ve chosen your newly installed java with the following command:
find /usr/share/java/ -iname "jakarta*commons*.jar" -exec jar -i {} \;
I opted to reindex only the jakarta commons files, that got Tomcat up and running with Java 5.
One last note: The /usr/bin/dtomcat5 is broken imho, at least when run from /etc/init.d/tomcat5. In ignores /etc/tomcat5/tomcat5.conf and therefore cannot stop Tomcat.
My solution: Replace
if [ -z "$CATALINA_HOME" ]; then TOMCAT_CFG="/etc/tomcat5/tomcat5.conf" fi
(in line 55 on my setup) with
if [ -z "$CATALINA_HOME" ]; then TOMCAT_CFG="/etc/tomcat5/tomcat5.conf" [ -r "$TOMCAT_CFG" ] && . "${TOMCAT_CFG}" fi
and remove
if [ -z "$CATALINA_HOME" ]; then [ -r "$TOMCAT_CFG" ] && . "${TOMCAT_CFG}" fi
(in line 105 on my setup).
I wonder why simple things like these always are a pain in the ass. Stupid nuisances that keeps people from getting their work done. *sigh* Not a good start for the week.
Not just for ruby but also the corresponding formats for
Java
public static final SimpleDateFormat RFC3339_FORMAT = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
and for Oracle
SELECT to_timestamp_tz('1979-21-09T06:54:00+01:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') FROM dual /
Oracle
What’s all the fuss about this SQL Injection thing?
It boils down getting some malicious crafted SQL code into the SQL code of an application, destroying data or authenticate yourself without knowing any real password. xkdc has a nice explanation.
The simple cases base on wrong escaped strings and the like. But as this SQL injection cheatsheet shows there are an infinity number of possibilities.
At day most of the time my database connection is an Oracle connection and so i found this Oracle whitepaper titled How to write injection-proof PL/SQL very interesting (via Bruce Schneier found at the gay bar).
I do not have a magic recipe for avoiding attack vectors all the time but as well as the whitepaper is written, it’s not a solution to expose all queries only via pl/sql to clients. In fact, it’s a nightmare to get this to work with JPA and other ORM mappers.
I try not to use dynamic sql in the sense of “concatenate some strings with one another and mysql_real_escape_string or DBMS_Assert. them” but use prepared statements with placeholders and explicit datatypes. Also if there’s a need for computing sql queries at runtime, do not ever let user supplied input come near them. I know that i’m relying to my api in this case but there is always a point on which i must rely on i guess.
As alway, the most important thing is: Be conscious about what you are doing and try to understand that, but at this point, i leave the discussion about software development and enter the depths of common sense…
Just a quick reminder for myself:
With the default installation of an Oracle Express (Oracle XE) comes two shell script with all the necessary environment variables to use sql*plus, exp, imp and the like on the command line:
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
respectively
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.csh
After staring at this
for about a day in various rotations and flips just to get Oracle GeoRaster work together with a homebrew GIS like application made me feel somewhat dizzy. To be cartesian or not cartesian, that is the question 
Otherwise, Oracle GeoRaster works quite well, at least for that bunch of german TK25 maps in GK3 coordinates that used to float around in the filesystem and are now being stored in the database.
This is really going the last post of my series on Oracle VM Server / VM Manager on inexpensive hardware.
Last week a second Dell Power Edge arrived, followed by a little NAS/iSCSI System, the ES-2100 from Eurostor, which is rebranded Thecus N5200 Pro. I do link Eurostor because i made some very nice contact with their tech support.
After running the Oracle VM Server on a 2.4 GHz Core 2 Duo Xeon with 4 Gb Ram for about 70 days non-stop, we decided to do the next step: Incarnating a second server with a shared storage.
The one server runs an paravirtualized OEL5 with 2 GB Ram which itself runs an Oracle 11g test instance with medium load, a hardware virtualized Windows XP with 512 MB Ram that runs a Jetty with a few services and since 2 weeks a hvm Debian that serves as a mailrelay for that Exchange of ours… Which has a now really less load as SpamAssasin takes care of all.
Setting up the second Dell was flawless, nothing new.
The iSCSI was another thing… First i deleted the RAID6 as we decided to go for RAID5. Stupid me set disk usage to 100%, went for the weekend, came back on monday and saw: Wow, no space for the iSCSI target. Damn it, all timeplans went bazoo… So deleting the RAID once again and back to start, this time with 20% for Disk Usage (you never know) and 80% for one iSCSI target (if this was my machine, i really had a purpose for 1.5TB storage… but here.. *sigh*).
So, another 8 hours later, i bought a cheap 8 port Gigabit switch, set up the ES-2100 for link aggregation and connected it to both Oracle VM Servers.
I roughly followed the steps described here, but as i changed some steps, let me describe them:
rpm -Uvh iscsi-initiator-utils-6.2.0.742-0.5.el5.i386.rpmiscsiadm -m discovery -t sendtargets -p 139.185.48.249
Example of removing a node:
iscsiadm -m node -p 10.2.0.250:3260,3 -T iqn.1992-04.com.emc:cx.apm00070202838.b0 -o delete
Listing the remaining:
iscsiadm -m nodeand having a new partition under /proc/partitions after
service iscsi restart
The ocfs2 cluster configuration is as simple as described in the linked Oracle document. I recommend adding names and ipaddresses corresponding to the one in /etc /ocfs2/cluster.conf to /etc /hosts, as the o2cb services won’t start otherwise. One thing Oracle forgot to mention is to open port 7777 on both machines in the iptables configuration.
At first i made the mistake to mkfs.ocfs2 the device and forgot to create a partition. This worked for whatever reason, but i destroyed the filesystem and created a partition with fdisk (new partition, primary, the whole thing).
Next, i didn’t follow Oracle but decided the following:
/dev/sdb1 /OVS ocfs2 defaults 1 0
So in the end we have a safe setup with hardware costs under 5k € and a setup time from about 6 or 7 days which brought some good knowledge and know-how. I think we wouldn’t have achivied this based on a VMWare solution brought by external consultants. Maybe that would have ended like the last Dilbert in that series .
To bring some variety to this blog, here’s a picture of the current setup:
I one of the google visitors or the 2 readers have any questions, feel free to ask 
Recently i needed windows clients to access a directory on the database server from which CLOBs where created. Since the server is an Oracle Enterprise Linux, i could follow the steps explained behind the following link to get Samba up and running with the Active Directory run by a Windows 2k3 server:
Which steps must be done to run Samba with AD-Integration
In short:
Setup Kerberos:
[logging] default = FILE:/var/log/krb5libs.log kdc = FILE:/var/log/krb5kdc.log admin_server = FILE:/var/log/kadmind.log [libdefaults] default_realm = WINDOWS.JARA23.CO.UK dns_lookup_realm = false dns_lookup_kdc = false ticket_lifetime = 24h forwardable = yes [realms] WINDOWS.JARA23.CO.UK = { kdc = server.windows.jara23.co.uk admin_server = server.windows.jara23.co.uk default_domain = windows.jara23.co.uk } [domain_realm] .kerberos.server = WINDOWS.JARA23.CO.UK .windows.jara23.co.uk = WINDOWS.JARA23.CO.UK [kdc] profile = /var/kerberos/krb5kdc/kdc.conf [appdefaults] pam = { debug = false ticket_lifetime = 36000 renew_lifetime = 36000 forwardable = true krb4_convert = false }
Test this with
kinit admin@WINDOWS.JARA23.CO.UKAfter entering a valid password, this command should give no error.
Setup a minimal Samba configuration:
#GLOBAL PARAMETERS [global] workgroup = MIDGARD realm = WINDOWS.JARA23.CO.UK preferred master = no server string = Linux Test Machine security = ADS encrypt passwords = yes log level = 3 log file = /var/log/samba/%m max log size = 50 printcap name = cups printing = cups winbind enum users = Yes winbind enum groups = Yes winbind use default domain = Yes winbind nested groups = Yes winbind separator = + idmap uid = 600-20000 idmap gid = 600-20000 ;template primary group = "Domain Users" template shell = /bin/bash [homes] comment = Home Direcotries valid users = %S read only = No browseable = No [printers] comment = All Printers path = /var/spool/cups browseable = no printable = yes guest ok = yes
Enable winbind, pam and other parameters with system-config-authentication.
Start Samba and join the domain with:
net ads join -U Administrator
ORA-01017: invalid username/password; logon denied
Yiipiee… Although i created my schema with the usually scripts we use, i got a logon denied… Switching context from Oracle Forms 6 to Reports 6… (Please, don’t say anything about legacy apps).
I didn’t notify that passwords are now case-sensitive in a Oracle database (which is a good thing), because of two reasons: I created the user via SQL*Plus and there is a environment variable called “sqlcase” that converts, if set to upper or lower, all SQL code to the according case. Bummer! Even quoted literals are converted… Stupid shit, i think. But in my case, this thing was set to mixed, so i ended up with a lower case password as with 11g “sec_case_sensitive_logon” is set to true as default:
SQL> SHOW parameter sec_case_sensitive_logon NAME TYPE VALUE ------------------------ ------- ----- sec_case_sensitive_logon BOOLEAN TRUE
Now with forms things are different, again. Forms runtime don’t convert the lowercase password in the connect string so i could log in but it converts the password to uppercase if you switch contexts to Reports runtime which couldn’t connect and ended up with the ORA-01017. Weird stuff, as everything else worked as expected, although Forms and Reports 6 are ages old compared to 11g.
In the end, i changed to user to a an uppercase password, so the password passed from Forms to reports is the right one. I didn’t set sec_case_sensitive_logon to false, as i think, its way better to have standard password handling and to me, this means case-sensitive and nothing else.
I found some valuable information on this topic in the following blog: Mark A. Williams :: Blog, didn’t know about that sqlcase shit before.
For more information see:
As a last post in the series (1, 2, 3, 4): Installing the latest and greatest incarnation of the Oracle Database named “Oracle 11g” on Oracle Enterprise Linux 5 running as a paravirtualized machine on Oracle VM Server.
Honestly, i was afraid of installing Oracle the first time under a linux environment but i must say, the installation was pleasantly easy, following the steps described here. No surprises at all. My host has a fixed IP adress, no problem with DHCP (although a don’t quite get why this should be any problem at all).
Fun started after stopping the database.
First: SELINUX is enabled per default under OEL5. I found the following tutorial which recommends turning SELINUX off. I disagree with that.
After installation complets, the following commands are necessary to enable Oracle to load its shared objects and to some text relocation with it:
chcon -t textrel_shlib_t /opt/app/oracle/product/11.1.0/db_1/lib/libnnz11.so chcon -t textrel_shlib_t /opt/app/oracle/product/11.1.0/db_1/lib/libclntsh.so.11 chcon -t textrel_shlib_t /opt/app/oracle/product/11.1.0/db_1/lib/libnque11.so chcon -t textrel_shlib_t /opt/app/oracle/product/11.1.0/db_1/lib/libsqlplus.so
otherwise you’ll some errors like
/opt/app/oracle/product/11.1.0/db_1/bin/ocrconfig.bin: error while loading shared libraries: /opt/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied
(Paths may vary) when starting the database.
Speaking of starting, Oracle comes with no batteries included, i.e. a startup script is missing.
I started with the one described here and added some lines to start the enterprise manager controll, see resources below.
In the meantime, the first import should be finished. The performance of 11g seems ok, on a virtual machine with a minimum of 1GB Ram. Maybe i’ll post some more impressions in a few weeks.