Looks like concerns about Oracle’s Sun acquisition are being fulfilled. After suing Google using a loophole in the Java license and losing James Gosling Oracle has now cut back significantly on the openness of Solaris development. Whether or not this was a good business decision this is surely damaging to Oracle’s reputation amongst supporters of FOSS, and great news for Microsoft. Like MySQL and MariaDB there is already now a community effort sponsored by Nexenta to fork OpenSolaris outside Oracle.
Tag: oracle
linux-iscsi with NetApp
SLES includes an iSCSI initiator, linux-iscsi. The initiator is configured using a daemon that is managed just like any other service, with a script in /etc/init.d/ and configuration files in /etc. After installing linux-iscsi using YaST I edited /etc/iscsi.conf to set the DiscoveryAddress and ran ‘/etc/init.d/iscsi start’ to start the service (‘chkconfig iscsi on’ ensures it is restarted after a reboot also). Logging onto my NetApp filer I could see the SLES initiator also logged in (‘igroup show’), and was able to create and map a LUN to the host. Back on the host ‘iscsi-ls’ yielded nothing useful, so I downloaded and installed the linux host utilities from NetApp’s support site. ‘sanlun show all’ now shows the lun-pathname and local SCSI devicename (eg. /dev/sdx) to which it is mapped. Oracle’s Orion benchmark indicates a random I/O throughput dramatically better for the iSCSI LUN compared to using a VMware virtual disk.
Gosling Quits Oracle
Gosling blogged "As to why I left, it’s difficult to answer: Just about anything I could say that would be accurate and honest would do more harm than good…" Ouch.
sqlunit & dbUnit
Now that I have my database schema creation and maintenance automated with Incanto and dbMaintain I have been looking at testing tools for stored procedures. Perhaps the best known for Oracle PL/SQL are Feuerstein’s utPLSQL and subsequent Quest Code Tester for Oracle. Other options include plunit, PLUTO and ruby-pl-spec. My main requirement, however, has been to invoke tests from an Ant project continuously integrated using Hudson and for now I have started using sqlunit, which is both mature and portable across different databases, as well as allowing relatively simple testcases to be specified in entirely XML.
My build-common.xml now contains
classname="net.sourceforge.sqlunit.ant.SqlunitTask"
classpathref="sqlunit-lib"/>
In addition to sqlunit I’ve found
dbUnit to be a useful tool for complex data setup prior to running a test.
Initilization of the dbUnit ant task is done as follows:
classname="org.dbunit.ant.DbUnitTask"
classpathref="dbunit-lib"/>
dbMaintain
Since reading about database refactoring some while ago I’ve been seeking to adopt a tool to support automated tracking and application of database changes. The best known solution for this is LiquiBase (fka Sundog Refactoring Tool), with over 10,000 downloads of LiquiBase Core recorded on SourceForge as I write this. In order to adopt LiquiBase on an existing project, however, I was faced with replacing dozens of SQL DDL scripts with LiquiBase specific XML files. There is a generateChangeLog command, but it has limitations: for example it does not recognize the primary key of Oracle index organized tables, and the output is not arranged anything like the documented best practices.
Instead, for now, I have adopted a newer tool called dbMaintain, which was presented at a Belgian Java User Group last April (see below) and a ServerSide news article last July. dbMaintain provides similar functionality to LiquiBase, but instead of XML it simply passes through native SQL scripts using JDBC, making it much easier to adopt with confidence on an existing project. I did run into one known issue with setting the default schema, but I’m happy to say Tim Ducheyne responded to my emails and provided a fixed snapshot within days. I hope this project gains momentum, and I’m looking forward to the next release.
Technorati Tags: Database development
PS. Other less mature alternatives include SQL based Thoughtworks dbdeploy and Carbon Five’s c5-db-migration. Another custom XML based solution is MIGRATEdb. Sony Pictures Imageworks have ported ActiveRecord migrations to Scala and there is a relatively inactive port of ActiveRecord migrations to Groovy called Bering.
Oracle SQLDeveloper 2.1 vs Quest TOAD
This past week I’ve been seeing whether I can wean myself off TOAD to the new SQLDeveloper 2.1 that was officially released last month. SQLDeveloper is a Java based IDE and so, unlike TOAD, it can be used on OS X and Ubuntu. SQLDeveloper is not open source, but it is licenced for free, whereas TOAD costs almost a thousand dollars per developer for the entry level version.
Version 2.1 of SQLDeveloper shows considerable improvement compared to the original release, but there are still some rough edges. TOAD presents database connections (connection configuration) and sessions (active connections) clearly and separately, with schema browsers and worksheets for each session in separate windows, and highlighting of windows that share the same session. SQLDeveloper has a single connection navigator, perhaps more familiar to users of other IDEs, but each connection may have only one session: you have to duplicate and rename a connection if you want to establish another session using the same connection parameters. Working with TOAD there is nearly always a default current session context, so it was annoying with SQLDeveloper reports to be prompted repeatedly for the connection to use to run a report.
One of the features of SQLDeveloper that first interested me was Subversion support. Again, this is similar to other Java IDEs. I’ve used Team Coding with TOAD in the past but the integration with CVS or PVCS was not as seamless as one would like. Now I just use TortoiseSVN and make sure I open the local source file whenever I want to edit a stored procedure and never edit the stored procedure source in the database directly. Oracle provides a tutorial on Subversion with SQLDeveloper but in my opinion it completely misses the point by failing to explain how to maintain stored procedures.
In general TOAD still appears a little more polished: for example favorites support in the File Open dialog, and a popup calendar control for setting date bind variables when executing a query. TOAD provides better feedback: for example it automatically displays the row count in a status line for every worksheet query, but with SQLDeveloper you have to select a popup menu option. For long running procedure execs TOAD provides a script runner with animated graphics and a timer, but the default behavior for SQLDeveloper is to exec the procedure like sqlplus in a small log window that is not immediately obvious. On the other hand the dockable tabs of SQLDeveloper are easier to manage than the more dated MDI design of TOAD.
Perhaps the biggest surprise, however, was when I clicked Tools/Monitor SQL and got ‘The use of this feature requires that the Oracle Tuning Pack be licensed.’ Oracle is not giving everything away for free after all, and it looks like for more advanced features I may still be going back to TOAD for a little while.
Oracle Completes Sun Acquisition
On the day that Apple announced iPad the bigger news for corporate developers was the completion of Oracle’s acquisition of Sun. The announcement was accompanied by a five hour press briefing and claims the company will be hiring two thousand more employees. Personally, however, I am inclined to agree with this downbeat assessment made when the acquisition was first announced last year.
Technorati Tags: Oracle
Installing Oracle XE on Ubuntu
Oracle XE is available for 32-bit architecture (i386) Linux only, although it may be possible to force installation on a 64-bit host. It will run in 512KB RAM but requires a 1GB swap space. Add:
deb http://oss.oracle.com/debian unstable main non-free
to /etc/apt/sources.list and then as root:
wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add - apt-get update apt-get install oracle-xe
‘libaio’ and ‘bc’ are in the repository, so dependencies will pull them in if the user doesn’t have them.
Incanto
After months of delay I’ve finally switched all my database creation scripts to Incanto. The most obvious benefit is that my Ant build scripts are now more portable, as I am no longer exec’ing a shell script wrapper to sqlplus, and I no longer need Cygwin to run the build scripts on my Windows workstation. One of the nice features about Incanto is that it allows you to pass in an Ant propertyset which is used to define SQL*Plus variables for the invoked scripts. I find this useful for overriding schema names and allowing multiple sets of application schemas to be created in the same developer database, for example when researching support issues alongside new development. The Incanto website has a page of best practices including a <macrodef> which I placed in a shared build file and <import>ed into my various Ant scripts.
Incanto requires sqlplus to be on the PATH, so I handle that and setting login parameters in .antrc. One strange issue I encountered on SLES while making this transition was that even although sqlplus was on my PATH and executable from my shell scripts, when I used Incanto I got
java.io.IOException: java.io.IOException: sqlplus: not found
Changing the permissions on sqlplus from 750 to 755 resolved the issue, even though the executable already belonged to a secondary group of which I was a member. Note that if you don’t need the advanced features of sqlplus you can still execute PL/SQL with Ant’s sql task.
Oracle tablespace utilization
I stole this from VS Babu
SELECT a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round( ( ( a.BYTES - b.BYTES ) / a.BYTES ) * 100, 2 ) percent_used
FROM ( SELECT TABLESPACE_NAME, sum( BYTES ) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME ) a,
( SELECT TABLESPACE_NAME, sum( BYTES ) BYTES, max( BYTES ) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME ) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME
ORDER BY ( ( a.BYTES - b.BYTES ) / a.BYTES ) DESC