tag:blogger.com,1999:blog-136687182024-03-13T13:54:54.813+00:00Halis wayHampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.comBlogger199125tag:blogger.com,1999:blog-13668718.post-4897692671188126792008-03-09T23:21:00.001+00:002010-08-12T08:31:18.673+01:00Backups, cheaper and faster?When did backups become easy and affordable?<br />I've been sort of slacking off in the backup game in the last year or so, our backups have been managed by other parts of the company and some backups have been outsourced to BSP's. I recently spec and deploy a basic <a href="http://www.symantec.com/">Netbackup</a> solution for our office servers at work and was amazed how extremely simple the whole thing was. The first backup system I worked with was Legato <a href="http://www.emc.com/products/detail/software/networker.htm">Networker</a> (now owned by <a href="http://www.emc.com/">EMC</a>) and it was at the time a pretty great product but it was quite difficult to manage, lots of things needed to be scripted and setting up a new tape library required quite a lot of tweaking.<br />Secondly, the budget was at first glance fairly limited but when we speced up what we needed and got a couple of quotes in I was amazed of how much we actually got for our money.<br />I wanted basic disk staging then duplication to tape. The obvious choice for tape was <a href="http://en.wikipedia.org/wiki/LTO4">LTO4</a>, we get pretty good price / performance, market leading capacity and it's a solid standard.<br />Most suppliers actually quoted the same tape library, just branded by different vendors. HP, Dell, IBM. All where <a href="http://www.ibm.com/">IBM 3573</a> tape libraries, it's a excellent and affordable library. Dell came in best in price as usual. We opted for SAS attached. The backup server, my first choice server these days, is the <a href="http://www.blogger.com/www.dell.com/content/products/productdetails.aspx/pedge_2950_3">Dell PowerEdge 2950</a>. I buy it for pretty much 75% of all requirements. Speced out with a pair of Quad-Core 1.86GHz processors, 4Gb RAM, 6x500Gb SATA drives (in RAID5) for the disk staging pool and a SAS5 to attach the library.<br />Software wise the server is running CentOS 5.1 and Symantec/Vertias Netbackup 6.5 “Linux Starter Edition (LSE)”. The LSE is a pretty good deal, you get a server license, tape library license and 5 client licenses (with bare metal, open file etc.) for a fairly low cost.<br />Installing and configuring Netbackup was as easy as it gets, the tape library was detected on boot, no sysctl.conf changes needed. Stepped through the netbackup installation process, added the tape library (less than 5 clicks!!), defined a couple of tape label policies, created a 2Tb disk staging pool with my new tape group as the target, just add water (ehm, clients actually).<br /><br />The total cost was under £13.000 (about $22.000), installation time was less than one day, included was:<br /><ul><br /><li>Veritas Netbackup 6.5 Linux starter edition<br /></li><li>Netbackup clients 5-pack</li><br /><li>Netbackup Exchange agent</li><br /><li>Dell PowerEdge 2950 server</li><br /><li>Dell PowerVault TL2000 library w/ one LTO4 SAS drive</li><br /><li>25x LTO4 tapes</li><br /><li>3x LTO cleaning tapes</li><br /><li>CentOS Linux 5.1 (free)</li><br /></ul>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com0tag:blogger.com,1999:blog-13668718.post-86895890601045538382008-02-20T17:52:00.000+00:002008-02-20T18:10:38.745+00:00How to delete an object with a special character in OracleThere are some things in <a href="http://www.oracle.com/">Oracle</a> that are possible but shouldn't be possible.<br />One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.<br /><b>I.e.:</b><pre><font color="blue">SQL> create table "My Fruit Table" (id number);<br /><br />Table created.<br /><br />SQL> select * from tab;<br /><br />TNAME TABTYPE CLUSTERID<br />------------------------------ ------- ----------<br />My Fruit Table TABLE</font></pre>Horrible! And what's even more horrible is that people actually do this. <br /><br />Now to the problem, a user created a table with a special character in the name. Not even sure what character but I need a way to drop it. PL/SQL to the rescue.<br /><b>Example:</b><pre><font color="blue">SQL> set serveroutput on<br /><font color="green">-- Lets create a dummy table with a bogus character in the name</font><br />SQL> declare<br /> a varchar2(500);<br />begin<br /> a:='create table "abctest'||chr(150)||'" (id number)';<br /> execute immediate a;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select * from tab;<br /><br />TNAME TABTYPE CLUSTERID<br />------------------------------ ------- ----------<br />abctest? TABLE<br /><br />SQL> desc "abctest?";<br />ERROR:<br />ORA-04043: object "abctest?" does not exist<br /><br /><font color="green">-- And a bit of PL/SQL to drop it</font><br /><font color="green">-- change the abctest% string to something matching your <b>single</b> table.</font><br />SQL> declare<br /> a varchar2(500);<br /> tname varchar2(50);<br />begin<br /> a:='select table_name from user_tables where table_name like ''abctest%''';<br /> execute immediate a into tname;<br /> dbms_output.put_line('Table name is: '||tname);<br /> execute immediate 'drop table "'||tname||'"';<br />end;<br />/<br /><br />Table name is: abctest?<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select * from tab;<br /><br />no rows selected</font></pre>Handy.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com6tag:blogger.com,1999:blog-13668718.post-12974794653794583382008-02-03T18:55:00.000+00:002008-02-03T19:20:34.165+00:00Buying marketshareOk, I'm way waay late on commenting on the two most recent IT company takeovers .<br /><br /><b>Sun buys MySQL</b><br />Tricky one, I have to honestly say I have a hard time seeing where <a href="http://www.mysql.com/">MySQL</a> fits in to <a href="http://www.sun.com/">Sun's</a> portfolio.<br />Sun is one of <a href="http://www.oracle.com/">Oracle</a> biggest partners (the biggest after <a href="http://www.dell.com/">Dell</a>?) and they are one of the biggest backers of <a href="http://www.postgresql.org/">PostgreSQL</a>, then even employee a few of head lead Postgres developers.<br />Sun has got two options with MySQL, they can either invest a lot of RnD in MySQL and actually turn it in to a really good product. MySQL is without a doubt a product with incredible potential, but is today pretty much a over-sized way to store CSV index files (bit harsh perhaps but hey).<br />Or they can ride out the profits by selling LAMP-stack boxes and let MySQL sustain itself, I doubt they can make up for the billion dollars they payed for it though.<br />Oh, and what will Sun make of the (rather evil) closed source <a href="http://www.mysql.com/products/enterprise/monitor.html">MySQL Enterprise Monitor</a> administration product from MySQL. Sun having new closed source software? Errr!<br /><br />Some advice for sun,<br />1) Add real constraint support. This is a show stopper for many, and the InnoDB approach isn't good enough. MySQL needs native solid from the ground up constraint support, independent of storage engine, version bla bla. If people don't want the "performance loss" of constraints, don't add the constraints!<br />2) Sort out proper I/O management, tablespaces, clean partitioning all that stuff. MySQL has improved a lot on this point over the last 12 months. But more work is needed, especially if they are planning on selling MySQL powered Thumpers.<br />3) 100% solid transactional support. MVCC would be fun. I've seen a few to many transactional problems in MySQL. Some sort of logging/undo/redo system is needed.<br /><br />On top of that I wouldn't mind a clean hot-backup tool, requires proper transactional support (with something like SCN's etc), 3rd party scripting language support (PgPerl anyone?).<br /><br />That's about 250.000 RnD hours, go get busy!<br /><br /><b>Oracle buys BEA</b><br />This is most likely very good news. I'm a big fan of the Oracle database (duh) and a fan of <a href="http://www.bea.com/">Weblogic</a> Application Server.<br />What I'm not a big fan of is Oracle Application Server (oc4j).<br />I hope Oracle realize that Weblogic is the superior product and works with Weblogic as the front runner and port oc4j features to Weblogic and not the other way around. I doubt that will be the case though. If not, I hope they don't kill off Weblogic any time soon.<br /><br />Hmm, that's all for now.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com4tag:blogger.com,1999:blog-13668718.post-16340086789030862772008-01-18T11:23:00.000+00:002008-01-18T11:34:15.937+00:00Websphere ND dmgr permission problemsRan in to a really weird problem with one of our Websphere 6.1 Network deployment setups yesterday and as I couldn't find one single page about the problem in google I thought I'd blog it.<br />I'm not much of a Websphere admin but managed to fix it after a while.<br /><br />The problem started when a datasource was updated and all of the sudden all node agents stopped trusting the deployment manager (dmgr). Syncing the nodes failed and hence pretty much everything failed to restart / deploy. Running servers where fine though.<br />We got this message in the logs:<pre><font color="blue">[1/17/08 13:30:52:020 GMT] 00000028 RoleBasedAuth A SECJ0305I: <br />The role-based authorization check failed for admin-authz operation <br />SSLAdmin:temporarilyDisableCertificateAuthentication:java.lang.Long.<br />The user UNAUTHENTICATED (unique ID: unauthenticated) was not granted<br />any of the following required roles: administrator.</font></pre>My best guess is that node agent configuration was corrupted in some way.<br />How do you fix the problem then?<br />Fairly easy actually.<br /># Stop all node-agents that seem broken (that would probably be all of them!).<br /># Go to the node agents bin directory on the node (usually something like $WAS_HOME/profiles/<profile_name>/bin/).<br /># Manually sync the node with syncNode.sh, point to the SOAP connector (default is 8879) on the DMGR server. See example<pre><font color="blue">./syncNode.sh dmgrhost 8879 -username websphere -password webfear</font></pre># Start the node agent and verify that the logs are happy. Kick off a cell sync from dmgr. You should see entries similar to this in the logs:<pre><font color="blue">[17/01/08 16:14:59:872 GMT] 0000002f NodeSyncTask A ADMS0003I: <br />The configuration synchronization completed successfully.</font></pre>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com7tag:blogger.com,1999:blog-13668718.post-20458090136659724572008-01-01T11:25:00.000+00:002008-01-01T11:48:32.649+00:00Solaris Express on a Toshiba Satellite Pro A200I bought myself one of those cheap laptops the other month. I needed a small machine for testing and since laptops are just as cheap (if not cheaper) as desktops these days I got a laptop.<br />The machine came with Vista but I wanted to triple boot Vista, <a href="http://www.ubuntu.com/">Ubuntu</a> and <a href="http://www.opensolaris.org/">Solaris Express Community Edition</a>.<br /><ul><br /><li> Use diskmgmt.msc in Vista to shrink the partition the machine came with, Windows can do this natively so there is no need to use Partition Magic or similar tools. Create at least three new partitions. One for Solaris, one for Linux and one for Linux swap.<br /><li> Secondly install Solaris, boot off the CD and go through the basic installer. The widescreen resolution worked out of the box (as usual). Do a full install, spending time "fixing" a smaller installer is just annoying. Solaris will install it's grub boot loader on both the MBR and superblock (on the Solaris partition). It probably makes sense to leave a large slice unused so it can be used with ZFS after the installation is done.<br /><li> Install Ubuntu. Nothing simpler than that.<br /><li> Edit Ubuntu's grub menu config (<b>/boot/grub/menu.lst</b>) to include Solaris. Simply point it to the Solaris parition (hd0,2 for me). Add these lines at the end of the file.<br /><pre>title Solaris<br />root (hd0,2)<br />chainloader</pre></ul><b>Done!</b><br /><br />I had to install the <a href="http://homepage2.nifty.com/mrym3/taiyodo/eng/">gani NIC driver</a> in Solaris to get the Ethernet card working and the <a href="http://www.4front-tech.com/download.cgi">Open Sound System</a> sound card driver to get sound working.<br />The Atheros WiFi card is supposed to be supported but I couldn't get it to work, even after adding the pci device alias to the driver. I'll post an update if I get it to work.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com36tag:blogger.com,1999:blog-13668718.post-16740322512898271632007-12-20T17:58:00.000+00:002007-12-20T18:09:14.206+00:00Solaris 10 on Dell PowerEdge 1950 and 2950I probably get an email every couple a weeks about this one, not sure why.<br />E-mails regarding something in <a href="http://www.sun.com/">Solaris</a> not working on <a href="http://www.dell.com/">Dell</a> PowerEdge 9th gen servers.<br /><br />For any device driver related issues with Solaris on x86, the first resource to check if you run in to trouble is the <a href="http://www.sun.com/bigadmin/hcl/data/sol/systems/views/all_servers_all_results.mfg.page1.html">Sun HCL</a>, the 'hardware compatability list'.<br />Solaris 10 works fine on most Dell boxes, but some need NIC or HBA drivers.<br /><br />For a <a href="http://www.sun.com/bigadmin/hcl/data/systems/details/2200.html">Dell 2950</a> that tells us that Solaris works, from release 11/06 upwards, it also tells us to download the ethernet driver <a href="http://www.broadcom.com/support/ethernet_nic/">here</a> and the MegaRAID SAS driver <a href="http://www.lsi.com/storage_home/products_home/internal_raid/megaraid_sas/megaraid_sas_8888elp/index.html?remote=1&locale=EN">here</a>.<br /><br /><b>NB</b>, the Sun HCL does not mention the new 'III' series 9-gen server yet. I would guess they are working on as Sun and Dell recently partenered to provide better Solaris support. The PERC6 card may need some new drivers.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com5tag:blogger.com,1999:blog-13668718.post-82365967697353039442007-11-28T21:20:00.000+00:002007-11-28T21:29:42.478+00:00Converting MySQL "on update current_timestamp" to OracleAnother short simple SQL for all out there in the process of converting old <a href="http://www.disney.com/">MySQL</a> schemas to <a href="http://otn.oracle.com">Oracle</a>.<br /><br />MySQL has got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestamp" feature can be quite handy if you have lazy developers who can't be bothered writing full insert statements. :)<br /><br />The MySQL create table statement would be something like this:<br /><pre><font color="blue">create table p (<br /> id int,<br /> a varchar(10),<br /> d timestamp DEFAULT CURRENT_TIMESTAMP<br /> ON UPDATE CURRENT_TIMESTAMP,<br /> constraint p_pk primary key (id)<br />);</font></pre><br />Not difficult to do in Oracle either, but we need a trigger to assist.<br /><b>Example:</b><br /><pre><font color="blue">SQL> alter session set nls_Date_format='HH24:MI:SS';<br /><br />Session altered.<br /><br />SQL> create table p (id number, a varchar2(10), d date default sysdate, <br /> constraint p_pk primary key (id));<br /><br />Table created.<br /><br />SQL> insert into p(id,a) values(1,'test');<br /><br />1 row created.<br /><br />SQL> host cat p_test.sql<br />CREATE OR REPLACE TRIGGER p_d_trig<br />BEFORE UPDATE ON p<br />FOR EACH ROW<br />BEGIN<br /> select sysdate into :new.d from dual;<br />END p_d_trig;<br />/<br /><br />SQL> @p_test<br /><br />Trigger created.<br /><br />SQL> select * from p;<br /><br /> ID A D<br />---------- ---------- --------<br /> 1 test <font color="green">21:15:05</font><br /><br />SQL> update p set a='foo' where id=1;<br /><br />1 row updated.<br /><br />SQL> select * from p;<br /><br /> ID A D<br />---------- ---------- --------<br /> 1 foo <font color="green">21:16:44</font><br /><br />SQL></font></pre>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com8tag:blogger.com,1999:blog-13668718.post-80135477474470630022007-11-04T21:03:00.000+00:002007-11-05T08:37:23.112+00:00Azul Systems Java applianceSo, I've been dragged more and more in to managing Java application containers like <a href="http://www.bea.com/">Weblogic</a>, <a href="http://www.ibm.com/">Websphere</a> and <a href="http://www.redhat.com/">JBoss</a>. These have a tendency to be hugely complex beasts, almost as complex as our favorite <a href="http://www.oracle.com/">database</a> and performance optimization is sometimes quite difficult, or simply it's to much effort to actually upgrade or replace the server.<br /><a href="http://www.azulsystems.com/">Azul</a> offers a quite neat (but a tad pricey) solution to this. They off-load Java computation to a separate appliance, the smallest model has 96 processing cores and 48Gb of ram. The big daddy has a massive 768 cores and 768Gb ram. It's a by Azul in house engineered hardware with custom software (I would guess the OS is semi-based on one of our open-source friends (have a look at the ifconfig output)). The application server still a normal server (Linux/Solaris etc), the small JVM on the server pretty much acts as a proxy between external resources such as JDBC sources and the actual JVM on the appliance.<br />Their marketing crew calls it a "turn key solution", it's not really that easy but it's pretty straight forward to use.<br /><img src="http://www.azulsystems.com/images/products/compute_appliance/5U_3200_enlarge.jpg" alt="Azul vega"><br />The appliance itself takes about 15 minutes to install, setup the networking and the compute domain name and you are pretty much done with the appliance itself.<br />The application side is almost as easy. Azul provides sort of a "JDK wrapper", you unpack the wrapper and run a simple shell script to integrate it with an existing JDK, the script asks for the path to the JDK to "mimic". Works with IBM and Sun JDK's, both 1.4 and 1.5 (I haven't tried with Jrockit).<br />Change your appserver init script's to use the new JAVA_HOME, give it a couple of azul specific JVM options, give it 10Gigs or so of heap and off you go. One thing to remember is that most garbage collection arguments are now obsolete, azul uses it's own custom "pausless gc".<br />The first thing that hit me when starting the first app-server was how incredibly slow it was to deply, deploying EJB's etc took ages, but hm, yes, that's an almost single threaded operation. The application itself felt "ok" when using it, now the cool part, we really couldn't get the application to run slowly with our benchmarks, at least not if we count out the fact that it eventually trashed the database sever (just some random 8-core thing) behind it. Bottlenecks in the application tiers where all gone! It doesn't matter if 5 users are online or 250, the application performs exactly the same.<br />The simple conclusion now is "we need a bigger Oracle box!".<br /><br />Azul provides a quite good web management console for the appliance, the gui provides functionality to manage compute pools if you need to throttle CPU and memory usage between applications or servers and also provides views to monitor applications and utilization.<br /><br />I guess one could call it a bit of a custom "throw hardware at the problem" solution, the box itself is quite expensive but for a medium sized company with loads of j2ee apps it makes sense.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com7tag:blogger.com,1999:blog-13668718.post-19334005477933803302007-11-01T12:15:00.000+00:002007-11-01T14:42:29.817+00:00Oracle SQL to return a alphabetical subsetYes, I know, I've been waaay bad at blogging lately. Been busy working on new projects at work, upgrading applications to Java1.5 containers (Websphere 6.1, Weblogic 9.2 etc). On the fun side we've got an <a href="http://www.azulsystems.com/">Azul</a> Java-acceleration box, that really needs a couple of blog entries!<br /><br />Anyway, got asked if there was a way to return a resultset of data based on the leading character, the use case was to ignore all strings starting with a,b, or d and return e to z.<br /><br />Fairly straight forward but a good SQL to have.<br />I simply grab the first character in the varchar and compare it's ASCII value to the ASCII value D.<br /><font color="blue"><pre>SQL> select * from t;<br /><br />A<br />----------<br />Atest<br />Btest<br />Etest<br />Htest<br />Wtest<br />Dtest<br />dtest<br />SQL> with data as (<br /> 2 select ascii(upper(substr(a,1,1))) a_val,a from t<br /> 3 )<br /> 4 select * from data where a_val not between ascii('A') and ascii('D') order by a<br /> 5 /<br /><br /> A_VAL A<br />---------- ----------<br /> 69 Etest<br /> 72 Htest<br /> 87 Wtest<br />SQL></pre></font>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com4tag:blogger.com,1999:blog-13668718.post-91265938149521735562007-08-21T08:42:00.000+01:002007-08-21T08:43:33.765+01:00Oracle 11g: Archive log alert log outputStarting with Oracle 11g the archivelog process is no longer logged to the alert log by default. For good and bad in my opinion, nice to get rid of the log entries on smaller databases but it can be quite useful to see the details on larger systems with multiple log destinations.<br /><br />The log level is simply a database parameter and can easily be changed, look at <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/archredo007.htm#i1006885">this</a> page to figure out what level you want. I figured 79 would be a good starting point (64+8+4+2+1). Databases running dataguard should probably have 207 (add 128) to include FAL service details.<br /><br /><b>How to set the trace level:</b><pre><font color="darkblue"><font color="green">[oracle@vm-rac1 ~]$ rsqlplus "/ as sysdba"</font><br /><br />SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 19 02:35:23 2007<br /><br />Copyright (c) 1982, 2007, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production<br />With the Partitioning, OLAP, Data Mining and Real Application Testing options<br /><br />SQL> show parameter log_archive_trace<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br />log_archive_trace integer 0<br />SQL> alter system set log_archive_trace=79;<br /><br />System altered.<br /><br />SQL> alter system switch logfile;<br /><br />System altered.<br /><br />SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production<br />With the Partitioning, OLAP, Data Mining and Real Application Testing options<br /><font color="green">[oracle@vm-rac1 ~]$ talert # my shell alias to tail the current alert log</font><br /> Current log# 1 seq# 7 mem# 0: /u01/app/oracle/oradata/test11g/redo01.log<br />Sun Aug 19 02:03:14 2007<br />ARC3: Evaluating archive log 3 thread 1 sequence 6<br />ARC3: Beginning to archive thread 1 sequence 6 (627362-628594) (test11g)<br />ARC3: Creating local archive destination LOG_ARCHIVE_DEST_10: <br />'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_%u_.arc' (thread 1 sequence 6)<br /> (test11g)<br />ARC3: Creating local archive destination LOG_ARCHIVE_DEST_1: <br />'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf' (thread 1 sequence 6)<br /> (test11g)<br />ARC3: Closing local archive destination LOG_ARCHIVE_DEST_10: <br />'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'<br /> (test11g)<br />Committing creation of archivelog <br />'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'<br />ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: <br />'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'<br /> (test11g)<br />Committing creation of archivelog '/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'<br />ARC3: Completed archiving thread 1 sequence 6 (627362-628594) (test11g)</font></pre><br />Read details about the log trace level <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/archredo007.htm#i1006885">here</a> and general alert log related stuff <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/monitoring001.htm#i1011629">here</a>.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com4tag:blogger.com,1999:blog-13668718.post-26645960544801043922007-08-19T21:09:00.000+01:002007-08-19T21:39:08.920+01:00Oracle 11g: basic rundownOk, so 11g has been out some time but I've been to busy to really dig in to it until now. 11g is probably the first release that doesn't really many new features; no, before you send me angry e-mails hear me out :)<br />Take a look at the <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#NEWFTCH1">new features</a> list. What do most of these features do? Easy, they just make the most out of the fairly old but totally outstanding REDO and UNDO management in Oracle. Why not use what we already got? Ok, some things are totally new features like the improved partitioning and new data types for the medical and life-sciences industry. <br /><br /><B>Partitioning and compression</b><br />The coolest things in 11g is probably the VLDB features for partitioning and compressions, can really reduce storage costs (at the same time as storage costs are plummeting with the competition from iSCSI). It is now possible to create "automatic" partitions, i.e. instead of adding a partition every month with DDL when doing a bulk load Oracle can automatically partition the table as per predefined rules. Pretty cool and saves a lot of time.<br /><br /><B>Direct NFS</B><br />Another pretty cool feature, at least for me as I'm using a lot of <a href="http://www.netapp.com/">Netapp</a> NFS storage, is the Direct NFS client in Oracle (Linux). Switch to the direct NFS lib and Oracle will do NFS ops outside the Linux kernel, which removes the NFS caching layer and kernel block device mapping etc. Gives a quite massive performance increase on some workloads. This is what I'm playing most with at the moment.<br /><br /><B>Data Guard</b><br />Lots of new cool stuff for Data Guard. Most significantly is probably the fact that you can have an open standby, available for real-time queries such as reporting applications and other read only operations. In fact, you can even open a Data Guard instance in read-write mode and mess about with it and later just flashback to a SCN where the db was in sync with the Data Guard master db and just roll forward to the current SCN using the normal FAL services. How sweet isn't that for DR testing and schema change testing. DR testing during live operations, that's a couple of late nights saved.<br />Other new data guard features include redo shipping compression, the normal improvements to the DG broker, sqlplus manageability etc.<br /><br /><br /><b>Workload replay</b><br />Another thing that is really useful for deployment testing and change control is the database replay feature, one can "record" all transactions over a period of time and replay them after a change has happened to ensure performance and functionality has not degraded.<br /><br /><br /><b>Improved ASM features.</b><br />You can now bring online a replaced drive and new writes will go directly to the drive while Oracle sync data from a redundant mirror in the background. Reduces resync time quite a fair bit.<br /><br /><b>PL/SQL</B><br />Couple of improvements to plsql;<br />Use of sequences in declares.<br />Real compound triggers.<br /><br /><b>New data types</B><br />Not really my thing but probably useful for a lot of people.<br />Spatial and multimedia support for medical imaging, life-sciences and other fairly odd things. :) Docs <a href="http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28415/ch_dba.htm#IMURG8000">here</a>.<br /><br />A couple of minor things have changed as well, the alert log lives in a new place (with it's buddies in the new diag system), the logging levels in the alert log can now be adjusted as well (got a blog entry about that in a couple of days).<br /><br />Lots of fun things to play with and to blog about.<br /><br />Ah, almost forgot. Larry pulled a version hack on us again. As some of you may now there was never an Oracle Version 1 release, the first version was called 2 since Larry Ellison didn't think anyone would buy version 1. The first (public) 11g release is 11.1.0.6Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com3tag:blogger.com,1999:blog-13668718.post-18974090346982308132007-08-11T21:40:00.000+01:002007-08-11T21:44:37.101+01:00Oracle Database 11g available for downloadWell, it's about time. Oracle finally made <a href="http://www.oracle.com/database/index.html">11g</a> available for download. Only 32-bit Linux so far though and I have a feeling we'll have to wait a while for most other platforms (possibly a 64-bit Linux download soon).<br /><br />Download it <a href="http://www.oracle.com/technology/software/products/database/index.html">here</a>.<br /><br />Lots of new cool stuff to blog about, I'm away on holiday for a week but my recently upgraded lab machines at home are sitting there waiting. Fun times when I get home.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com2tag:blogger.com,1999:blog-13668718.post-91974633470158367972007-07-30T21:55:00.000+01:002007-07-30T22:07:17.664+01:00Limit user sessions in OracleDevelopers sometimes enjoy configuring their JDBC datasources after their own likings, testing a performance issue by having a gazzillion threads to the database "just in case we need them". <br />Well in my opinion, if you need what is obviously too many threads, then there is a coding issue.<br />So to make people think twice before flooding a service with connections I've started limiting the number of sessions they are allowed to have.<br />Easy as always to do in Oracle. First we allow resource limits by setting the <b>resource_limit</b> to <b>true</b>, then we simply create a <a href="http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm">profile</a> and assign that to the user. The profile can set the limit of how many session a user is allowed to have.<br /><br /><B>A little demonstration</B>;<pre><font color="darkblue">oracle@htpc:~$ rsqlplus "/ as sysdba"<br /><br />SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 30 21:56:12 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br />SQL> show parameter resource_limit<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br />resource_limit boolean FALSE<br />SQL> alter system set RESOURCE_LIMIT=true scope=both;<br /><br />System altered.<br /><br />SQL> create profile sesslimit limit sessions_per_user 2;<br /><br />Profile created.<br /><br />SQL> create user fish identified by fish123<br /> 2 default tablespace data profile sesslimit;<br /><br />User created.<br /><br />SQL> grant create session to fish;<br /><br />Grant succeeded.<br /><br />SQL> connect fish/fish123<br />Connected.<br />SQL> disconnect<br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><font color="darkgreen">-- I'll start two sessions in another terminal.</font><br />SQL> connect fish/fish123<br />ERROR:<br />ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit<br /><br /><br />SQL></pre></font>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com4tag:blogger.com,1999:blog-13668718.post-79483912003803329902007-07-08T18:42:00.000+01:002007-07-08T19:00:28.235+01:00Using SERVICE_NAMES in OracleThe use of "SERVICE_NAMES" in Oracle is quite an old and probably well known feature but perhaps not everyone is familiar with it yet.<br />Got asked today about a recovery scenario where the administrator had a failed instance (broken data files, no logs, no backups, just a nightly exp), a new database was created with 'dbca', but with a new name to test importing the exp file.<br />All worked fine, but there was a problem with the database name. The application had the service name set in a number of config files and there was also a number of ETL scripts with service names hardcoded. The thinking at the time was to delete the old instance, remove all traces of it (oratab etc.) and then create it *again* with the same name.<br />Now hold on here, we have tested the imp in a new database, all is fine and all we want to do is allow connections to the old database instance name?<br />That's pretty much a one-liner, not a new database.<br />We can simply add the new name we want to "listen on" to the SERVICE_NAMES parameter.<br />Easy peasy.<br /><br />Ok, here is what we should do. Quite a long example for something simple.<br />But hey, just want to make it clear.<br /><pre><font color="darkblue">oracle@htpc:admin$ lsnrctl status<br /><font color="red">-- What's the current db_name and service_names?</font><br /><font color="darkgreen">LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:31:22<br /><br />Copyright (c) 1991, 2005, Oracle. All rights reserved.<br /><br />Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))<br />STATUS of the LISTENER<br />------------------------<br />Alias LISTENER<br />Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production<br />Start Date 08-JUL-2007 18:23:39<br />Uptime 0 days 0 hr. 7 min. 43 sec<br />Trace Level off<br />Security ON: Local OS Authentication<br />SNMP OFF<br />Listener Parameter File /u01/oracle/10g/network/admin/listener.ora<br />Listener Log File /u01/oracle/10g/network/log/listener.log<br />Listening Endpoints Summary...<br /> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))<br /> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))<br />Services Summary...<br />Service "PLSExtProc" has 1 instance(s).<br /> Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...<br />Service "peggy" has 2 instance(s).<br /> Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />Service "peggyXDB" has 1 instance(s).<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />Service "peggy_XPT" has 1 instance(s).<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />The command completed successfully</font><br />oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba<br /><br /><font color="darkgreen">SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:24 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br />SQL> show parameter db_name<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br />db_name string peggy<br />SQL> show parameter service_names<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br />service_names string peggy<br />SQL> <br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options</font><br /><font color="red">-- What can we connect to?</font><br />oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/peggy<br /><br /><font color="darkgreen">SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:53 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br />SQL> <br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br />oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog<br /><br />SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:58 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br />ERROR:<br />ORA-12514: TNS:listener does not currently know of service requested in connect<br />descriptor<br /><br /><br />Enter user-name:</font><br /><font color="red">-- Ouch, that's the one we want!</font><br /><br />oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba<br /><br /><font color="darkgreen">SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:32:01 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br /><font color="red">-- Here is the 'one-liner'</font><br />SQL> alter system set service_names='peggy,dog' scope=spfile;<br /><br />System altered.<br /><br />SQL> shutdown immediate<br />Database closed.<br />Database dismounted.<br />ORACLE instance shut down.<br />SQL> startup<br />ORACLE instance started.<br /><br />Total System Global Area 599785472 bytes<br />Fixed Size 2022600 bytes<br />Variable Size 167772984 bytes<br />Database Buffers 423624704 bytes<br />Redo Buffers 6365184 bytes<br />Database mounted.<br />Database opened.<br />SQL> <br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options</font><br /><font color="red">-- Let's see what changed. What can we connect to now?</font><br />oracle@htpc:admin$ lsnrctl status<br /><br /><font color="darkgreen">LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:33:57<br /><br />Copyright (c) 1991, 2005, Oracle. All rights reserved.<br /><br />Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))<br />STATUS of the LISTENER<br />------------------------<br />Alias LISTENER<br />Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production<br />Start Date 08-JUL-2007 18:23:39<br />Uptime 0 days 0 hr. 10 min. 18 sec<br />Trace Level off<br />Security ON: Local OS Authentication<br />SNMP OFF<br />Listener Parameter File /u01/oracle/10g/network/admin/listener.ora<br />Listener Log File /u01/oracle/10g/network/log/listener.log<br />Listening Endpoints Summary...<br /> (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))<br /> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))<br />Services Summary...<br />Service "PLSExtProc" has 1 instance(s).<br /> Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...<br /><b>Service "dog" has 1 instance(s).</b><br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />Service "peggy" has 2 instance(s).<br /> Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />Service "peggyXDB" has 1 instance(s).<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />Service "peggy_XPT" has 1 instance(s).<br /> Instance "peggy", status READY, has 1 handler(s) for this service...<br />The command completed successfully</font><br />oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog<br /><br /><font color="darkgreen">SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:34:18 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><font color="red">-- It works, but where are we?</font><br /><br />SQL> select sys_context('userenv','SERVICE_NAME') from dual;<br /><br />SYS_CONTEXT('USERENV','SERVICE_NAME')<br />------------------------------------------------------------------------------------------------------------------------<br />dog<br /><br />SQL> select sys_context('userenv','DB_NAME') from dual;<br /><br />SYS_CONTEXT('USERENV','DB_NAME')<br />------------------------------------------------------------------------------------------------------------------------<br />peggy<br /><br /></font></font></pre>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com2tag:blogger.com,1999:blog-13668718.post-43363683270145039572007-07-01T22:48:00.000+01:002007-08-06T08:06:38.551+01:00The new top 500 supercomputer list is out!Finally had some time to look at the latest <a href="http://www.top500.org">Top500</a> supercomputer <a href="http://www.top500.org/lists/2007/06">list</a> released last week at ISC '07 last week.<br /><br />Lots of changes compared to the old list; two quite interesting new things. First of all is Intel multi-core chips, secondly we have Blades, loads and loads of blades.<br />A significant number of entries on the list are using Intel Xeon 5160 3.0GHz dual-core CPU's and there is also a number of quad-core systems.<br />Another interesting point is that the crown in terms of number of systems on the list has been passed from <a href="http://www.ibm.com/">IBM</a> (with 192 systems) to <a href="http://www.hp.com/">HP</a> (with 201 systems).<br />The <a href="http://h20311.www2.hp.com/HPC/cache/276446-0-0-0-121.html">HP Cluster Platform BL460c</a> holds quite a few spots on the list. Can't say I've heard much about HP's HPC offering so far, they're not exactly beating the drum on that one (compared to the IBM guy who's calling me every 2 months).<br /><br /><a href="http://www.sgi.com/">SGI</a> holds a few new slots on the list as well, which is extra fun to see considering the hard last year SGI have had. One Altix system even made it to number 10 on the list (with <a href="http://www.nas.nasa.gov/About/Projects/Columbia/columbia.html">Columbia</a> on number 13).<br /><br /><a href="http://www.top500.org/"><img src="http://top500.cachefly.net/themes/top500/logo.gif" alt="Top 500"></a>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com1tag:blogger.com,1999:blog-13668718.post-66925349820102105332007-06-28T09:32:00.001+01:002007-06-28T09:42:28.301+01:00Dell teaming with Oracle Enterprise Linux?Just noticed that <a href="http://www.dell.com/">Dell</a> change the <b>"Red Hat Enterprise Linux"</b> label on their support website to just <b>"Enterprise Linux"</b>.<br />Is this the first step for Dell embracing <a href="http://www.oracle.com/linux">Oracle Enterprise Linux</a>?<br /><img src="http://halisway.hifichoice.com/el4dell.jpg">Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com1tag:blogger.com,1999:blog-13668718.post-31870041049769777352007-06-24T20:54:00.001+01:002007-06-24T21:12:26.801+01:00Sun 2002 flashbackWe've been doing some shuffling around of hardware at work and I've recommissioned some old Sun hardware for a new QA environment.<br />Oh the memories I have of old Sun kit :)<br />The most exciting thing I've installed is a Sun <a href="http://www.sun.com/servers/midrange/v480/">v480</a> connected to a <a href="http://sunsolve.sun.com/handbook_pub/Systems/T3plus/T3plus.html">T3 brick</a>. That combo was pretty much the industry standard Oracle solution when I had my first job in "real" systems administration and it was probably one of the first the real mid-range installs I did (not counting ageing 420r/450's).<br />To be honest, it's a match made in heaven. A quad cpu v480 with a t3 brick (or two) kicked out some real performance numbers in it's day. The v480 is probably one of the nicest boxes to work with (considering age and all), the boards are easy to fit, the RSC is awesome, it's simply rock solid. Running Solaris 8 of course (we still have *lots* of clients on Solaris 8 (with Solaris 10 gaining ground)).<br /><img src="http://sunsolve.sun.com/handbook_pub/Systems/SunFireV480/images/SunFireV480.jpg"><br /><br />The T3 array is pretty cool, but it brings back the frustrations everyone had with early low-end SANs. The limitation to two disk RAID volumes with very limited zoning and slicing. But hey, it's pretty old and is still pretty fast.<br /><img src="http://ru.sun.com/products/storage/T3_workgroup/pics/T3_workgroup.jpg"><br /><br /><br /><i>Now if I only could figure out how to get rid of that darn Sun <a href="http://www.sun.com/servers/entry/250/">E250</a> we still have running <a href="http://www.sybase.com/">Sybase 12.5.4</a>, it just works to well so far.</i>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com1tag:blogger.com,1999:blog-13668718.post-18869678659038704172007-06-14T12:19:00.000+01:002007-06-14T12:38:44.100+01:00Reclaiming LOB space in OracleReclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual <b>alter table table_name shrink space compact;</b>, but what do we do for lobs? <br />We need a manual reclaim for the lob column.<br /><br />A little demo:<br /><pre><font color="blue">(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden<br /><br />SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 14 12:19:02 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All Rights Reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br /><font color="green">-- Create a table and sequence to play with</font><br />HLINDEN@spinner1> create table lob_test (id number, data blob);<br /><br />Table created.<br /><br />HLINDEN@spinner1> create sequence lob_test_seq;<br /><br />Sequence created.<br /><br /><font color="green">-- Load 50 rows with 1.5Mb blobs (see code bellow)</font><br />HLINDEN@spinner1> @lobload<br /><br />PL/SQL procedure successfully completed.<br /><br /><font color="green">-- Find out what our lob segment is called</font><br />HLINDEN@spinner1> select object_name,object_type from user_objects where<br /> 2 created>sysdate-interval '5' minute and object_type='LOB';<br /><br />OBJECT_NAME OBJECT_TYPE<br />------------------------------ -------------------<br />SYS_LOB0000199575C00002$$ LOB<br /><br /><font color="green">-- Display the current size of the lob segment</font><br />HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where <br />segment_name='SYS_LOB0000199575C00002$$';<br /><br /> MB<br />----------<br /> 75<br /><br /><font color="green">-- Ok, let's delete those blobs and see what the size is after</font><br />HLINDEN@spinner1> delete from lob_test purge;<br /><br />50 rows deleted.<br /><br />HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where <br />segment_name='SYS_LOB0000199575C00002$$';<br /><br /> MB<br />----------<br /> 75<br /><br /><font color="green">-- Still 75Mb, hm, perhaps it recycled if we insert more data?</font><br /><br />HLINDEN@spinner1> @lobload<br /><br />PL/SQL procedure successfully completed.<br /><br />HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where <br />segment_name='SYS_LOB0000199575C00002$$';<br /><br /> MB<br />----------<br /> 150<br /><br /><font color="green">-- Nope, not recycled. We need to issue a shrink command to free up the <br />-- space immediately</font><br />HLINDEN@spinner1> delete from lob_test;<br /><br />50 rows deleted.<br /><br />HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);<br /><br />Table altered.<br /><br />HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where <br />segment_name='SYS_LOB0000199575C00002$$';<br /><br /> MB<br />----------<br /> 0<br /><font color="green">-- All gone!</font></font></pre>My simple blob loading code:<br /><pre><font color="darkblue">DECLARE<br /> src_file BFILE := bfilename('TMP', 'data.dat');<br /> dst_file BLOB;<br /> lgh_file BINARY_INTEGER;<br /> cur_id NUMBER(10);<br />BEGIN<br /> FOR i IN 1..50<br /> LOOP<br /> INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())<br /> RETURNING id into cur_id;<br /> -- lock record<br /> SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;<br /><br /> dbms_lob.fileopen(src_file, dbms_lob.file_readonly);<br /> lgh_file := dbms_lob.getlength(src_file);<br /> dbms_lob.loadfromfile(dst_file, src_file, lgh_file);<br /> dbms_lob.fileclose(src_file);<br /> END LOOP;<br />END;<br />/</font></pre>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com7tag:blogger.com,1999:blog-13668718.post-86921670581264324572007-06-12T23:05:00.000+01:002007-06-12T23:09:13.802+01:00Oracle 11g - one month to goYep, exciting times. July 11th, lots of new cool stuff.<br /><br />Read about a couple of new things in <a href="http://www.oracle.com/newsletters/information-indepth/database-insider/jun-07/index.html">Insider </a>.<br /><br />If you're lucky enough to be in New York, sign up for the launch <a href="http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665">event</a>!Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com1tag:blogger.com,1999:blog-13668718.post-56623276965368969792007-06-10T17:34:00.001+01:002007-06-10T21:30:02.323+01:00New blade enclosure from SunEver since <a href="http://en.wikipedia.org/wiki/Andy_Bechtolsheim">Andy Bechtolsheim</a> returned to <a href="http://www.sun.com/">Sun</a> pretty much everything on the <a href="http://www.sun.com/servers/index.jsp">Sun x64</a> server line meet all expectations and then some. Sure, there has been the slight <a href="http://cuddletech.com/blog/pivot/entry.php?id=808">ethernet chipset</a> problem, but generally the AMD kit has really kicked ass<br />Though some people where surprised to see first (new) blade offering from Sun, the <a href="http://www.sun.com/servers/blades/8000/">8000</a> series. A 4-socket blade offering when most of the market moved away from larger blade enclosures and focused on 2-socket systems. The 8000 still made good sense for larger customers like financial institutions and telcos. Server consolidation on a large level.<br /><br />Now the other day Sun released a second (third actually, the 8000 comes in two models) blade offering. The <a href="http://www.sun.com/servers/blades/6000/">6000</a> series. It's a fairly basic 10U enclosure, 10 blade slots and the classic Ethernet modules. Not very exciting at first glance, I have to admit. Sounds like the <a href="http://www.dell.com/content/products/productdetails.aspx/pedge_1955_new?c=us&cs=555&l=en&s=biz&redirect=1">PowerEdge 1955</a> box but not with the same high density (Dell can do 10 blades in 7U).<br /><img src="http://www.sun.com/images/k3/k3_sb6000_1.jpg" alt="6000"><br />The thing cool about the 6000 enclosure is that it is not a Blade enclosure in the classic sense, it's not "stripped down computers in a box". It's actually 10 high capacity servers. It enclosure offers the same expandability as normal 1U servers.<br />All blade modules are dual socket, can take up to 16 DIMMS, <b>four</b> 2.5" SAS drives and even two PCI-e slots. That's right, normal PCI-e slots, no more proprietary on board FC-AL or Infiniband modules. Just slot in a couple of standard c-PCIe cards and off you go. No more opening up the blade to install modules.<br />Another new feature is a hardware RAID controller on-board (for good and bad indeed).<br />The whole enclosure is based around industry standards and open solutions.<br /><br />In addition to all this cool stuff the real winner here is the selection of blades.<br />Sun offers no less than <b>3</b> blade types.<br />The <a href="http://www.sun.com/servers/blades/t6300/">t6300</a> UltraSPARC T1 blade,<br />the <a href="http://www.sun.com/servers/blades/x6220/">x6220</a> AMD blade and<br />the <a href="http://www.sun.com/servers/blades/x6250/">x6250</a> Intel Xeon blade<br />It's the first Intel Xeon (including quad-core procs) offering from Sun, with many more to come. <br /><img src="http://www.sun.com/images/k3/k3_sb6000_5.jpg" alt="t6300"><br /><br />Watch Andy talk about the new box <a href="http://www.sun.com/servers/blades/6000/gallery/index.xml?p=2&s=1">here</a>.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com1tag:blogger.com,1999:blog-13668718.post-26806638549367036282007-05-30T21:55:00.000+01:002007-05-30T22:11:25.342+01:00Run system commands from Oracle with PL/SQLI friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.<br />He had full access to Oracle with sysdba/dba roles etc, but not SSH.<br />I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.<br /><br />One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.<br />Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.<br />I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.<br />The temporary spool file handling in my example is quite poor, but works. :)<br />A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution. <br /><br /><b>Example of use:</b><pre><font color="blue">oracle@htpc:~$ rsqlplus hlinden/password as sysdba<br /><br />SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007<br /><br />Copyright (c) 1982, 2005, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production<br />With the Partitioning, OLAP and Data Mining options<br /><br />SQL> set serveroutput on<br />SQL> @system_run<br /><br />Procedure created.<br /><br />SQL> exec system_run('ls -l /home/oracle/bin');<br /><font color="green">total 12<br />-rwxr-xr-x 1 oracle dba 797 Nov 5 2006 backup_controlfile.sh</font><br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> exec system_run('uname -a');<br /><font color="green">Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux</font><br /><br />PL/SQL procedure successfully completed.<br /><br />SQL></font></pre><b>And here is the procedure code:</b><br /><pre><font color="blue">CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)<br />IS<br /> script_file varchar2(40) := 'my-temp-script.sh';<br /> script_data varchar2(4000);<br /> MyFile utl_file.file_type;<br /> d varchar2(4000);<br /> dump_file varchar2(40) := '/tmp/my-temp-file.dat';<br /> dump_type utl_file.file_type;<br />BEGIN<br /> -- Open file<br /> MyFile := utl_file.fopen('TMP',script_file,'w');<br /> -- Write data to file<br /> script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;<br /> utl_file.put_line(MyFile, script_data, FALSE);<br /> -- Close file<br /> utl_file.fflush(MyFile);<br /> utl_file.fclose(MyFile);<br /> -- Purge old logs, no fun anyway<br /> dbms_scheduler.purge_log(JOB_NAME=>'TEST');<br /> -- Execute script<br /> -- The job is created as disabled as<br /> -- we execute it manually and will<br /> -- drop itself once executed.<br /> dbms_scheduler.create_job(<br /> job_name => 'TEST',<br /> job_type => 'EXECUTABLE',<br /> job_action => '/bin/bash',<br /> number_of_arguments => 1,<br /> start_date => SYSTIMESTAMP,<br /> enabled => FALSE);<br /> dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);<br /> dbms_scheduler.enable('TEST');<br /> -- Wait for the job to be executed<br /> -- usually done within 1 second but<br /> -- I set it to 2 just in case.<br /> dbms_lock.sleep(2);<br /> -- Open the output file and<br /> -- print the result.<br /> dump_type := utl_file.fopen('TMP',dump_file,'r');<br /> loop<br /> begin<br /> utl_file.get_line(dump_type,d);<br /> dbms_output.put_line(d);<br /> exception<br /> when others then<br /> exit;<br /> end;<br /> end loop;<br /> utl_file.fclose(dump_type);<br /> -- Clean up our temp files<br /> utl_file.fremove('TMP', script_file);<br /> utl_file.fremove('TMP', dump_file);<br />END;<br />/</font></pre>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com10tag:blogger.com,1999:blog-13668718.post-3821101731985213312007-05-28T10:59:00.000+01:002007-05-28T11:14:02.943+01:00Cyrus IMAP file system tuningBeen busy, not enough blogging, bla bla bla. I know.<br />Just a lot of stuff going on at work at the moment, mergers and integrations.<br /><br />We've had some problems with one of our IMAP servers at work running <a href="http://www.postfix.org/">Postfix</a> and <a href="http://cyrusimap.web.cmu.edu/">Cyrus IMAPd</a>. A for the job quite well speced machine, dual Xeons and 3x146Gb disk in RAID5 (4x146 in RAID10 would have been nicer). Anyway, the machine has got 50 or so IMAP users and perhaps 100Gb spool data on a <a href="http://www.namesys.com/">ReiserFS</a> partition.<br />The machines' avg. load has peaked at over 6.00 with about 75% in iowait on a bad day. I suspect that modern fancy e-mail search tools are to blame for the problems, applications building search indexes and such (Apple Mail anyone?).<br />Monitoring of the server showed quite a lot of inode update activity, even though there isn't *that* much new email coming in.<br />Must be our old (not so) dear friend atime that's making a little mess, I've used the noatimea and nodiratime mount options in the past with great success. Seen performance improvements of a couple of percent.<br />The mount-options noatime and nodiratime simply disables the feature to update the access timestamp of a file (and directory). I.e. when someone clicks and reads an email in their mail application the inode atime timestamp is updated. When is this atime timestamp used? Never.<br />Did a quick online remount of the spool fs with noatime and nodiratime.<br />The result?<br />Avg. load hasn't touch 1.00 since. Wow! I was expecting an improvement, but not <b>that</b> big.<br />Great and easy way to improve performance on IMAP spools.<br /><pre><font color="blue">mount -o remount,noatime,nodiratime /var/spool/imap</font></pre>And don't forget to update /etc/fstab with the same mount options.<br />Cool.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com0tag:blogger.com,1999:blog-13668718.post-74485995842040103332007-05-09T21:47:00.000+01:002007-05-09T22:05:24.912+01:00Insider for Oracle - funky GUI bitsWe've been doing a lot of application benchmarking and tuning at work in the last couple of months as we are prepare for a new major release. I've been involved in the Oracle side of things and have spent a lot of time tracking down expensive (or plain weird) SQL-statements and trying to optimize the Oracle databases we use.<br /><br />After spending about 6 - 7 hours a day running my normal <a href="http://www.google.com?q=command+line+owns">sqlplus</a> scripts over and over trying different things, monitoring benchmark runs I got a bit bored of text output and had a quick <a href="http://www.google.com/?q=just+google+it">google</a> for some sort of basic v$whats_up GUI tool and found this little cool little app called <a href="http://www.fourthelephant.com/insider.html">Insider for Oracle</a>, it a great app for doing spot-on monitoring, sort of like <a href="http://www.quest.com/spotlight/Overview.aspx">Spot light</a> from Quest, but without the hassle. Insider tries to be a more proactive tool with round the clock monitoring, but for that, it's no good.<br /><br />The thing I find it useful for is when we start a benchmark I can trail what's going on with the system. See new SQL statements, wait events, SGA usage, PGA usage, hot segments etc. Nothing I can't do from sqlplus but it makes my tasks a bit easier.<br />Price wise it's quite a bargain for what I use it for, the license is for each database on the application "workspace" (i.e. how many databases you want to look at at once). And for what I use it for just looking at one DB at a time, that works out pretty cheap, 599USD or so. Cheap as chips.<br />I've found a couple of bugs (and submitted bug reports accordingly) and I've even submitted a couple of enhancement suggestions.<br />Check out their company <a href="http://www.fourthelephant.com/blog/">blog</a>.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com2tag:blogger.com,1999:blog-13668718.post-82590741412939829062007-05-07T22:04:00.000+01:002007-05-07T22:13:18.306+01:00OpenSolaris SXDE upgrade timeIt's been a while since I last played with Solaris Express Community Edition, so long that it actually changed names. There are now two versions, one for "random people" and one version for developers. SXCE is not released as often as SXDE but is more tested and stable, SXDE also comes with a lot more goodies, like <a href="http://developers.sun.com/sunstudio/">Sun Studio 11</a> and <a href="http://www.netbeans.org/">Netbeans</a>.<br />Anyway, the install of SXDE b63 under <a href="http://www.vmware.com/">VMWare workstation 6 RC</a> was as easy as ever.<br />However, at first boot I almost thought the VM was frozen for a minute or so, after grub it just sits there with a cursor in the corner of the screen for about 2 minutes before kicking off the kernel. All good after that though.<br />One thing I wanted to test was the new <a href="http://opensolaris.org/os/community/zones/files/Zones_RM_Improvements.html">simplified resource capping</a> in Solaris Zones. It was very straightforward and easy to use.<br />A couple of extra commands in zonecfg and the zone is capped to one core and 512Megs of memory. No more messing around with projects, except if you want really fine grained capping that is.<br />Cool!<br /><br />Took a screenshoot:<br /><a href="http://halisway.hifichoice.com/sol_b63.jpg"><img src="http://halisway.hifichoice.com/sol_b63_thumb.jpg" alt="b63"></a>Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com0tag:blogger.com,1999:blog-13668718.post-23736444784609550142007-04-30T20:28:00.000+01:002007-04-30T20:39:46.546+01:00How to do an "insert ignore" in OracleOk, I should start off with a disclaimer. This is not a good idea to do.<br />Using the "insert ignore" statement is a way to let <a href="http://www.mysql.com/">MySQL</a> insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.<br />Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.<br /><br />Ok, in MySQL we can do this.<br /><pre><font color="blue">mysql> insert ignore into a select * from b;<br />Query OK, 1 row affected (0.00 sec)<br />Records: 2 <b>Duplicates: <font color="red">1</font></b> Warnings: 0</font></pre>But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the <a href="http://halisway.blogspot.com/2006/11/oracle-merge-statement.html">merge</a> statement?<br />Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.<br /><pre><font color="blue"><font color="green">-- So what do we got?</font><br />SQL> select * from a;<br /><br /> A B<br />---------- ----------<br /> 1 2<br /> 2 3<br /><br />SQL> select * from b;<br /><br /> A B<br />---------- ----------<br /> 1 3<br /> 3 4<br /><br />SQL> merge into a using b on (a.a=b.a) <br /> when not matched then insert values (b.a,b.b);<br /><br />1 row merged.<br /><br />SQL> select * from a;<br /><br /> A B<br />---------- ----------<br /> 1 2<br /> 2 3<br /> 3 4<br /><br />SQL><br /><br /><font color="green">-- Let's rollback that and have a look at the exeuction plan.</font><br /><br />SQL> rollback;<br /><br />Rollback complete.<br /><br />SQL> set autotrace on<br />SQL> merge into a using b on (a.a=b.a) <br /> when not matched then insert values (b.a,b.b);<br /><br />1 row merged.<br /><br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1973318225<br /><br />-----------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-----------------------------------------------------------------------------<br />| 0 | MERGE STATEMENT | | 2 | 64 | 7 (15)| 00:00:01 |<br />| 1 | MERGE | A | | | | |<br />| 2 | VIEW | | | | | |<br />|* 3 | HASH JOIN OUTER | | 2 | 64 | 7 (15)| 00:00:01 |<br />| 4 | TABLE ACCESS FULL| B | 2 | 52 | 3 (0)| 00:00:01 |<br />| 5 | TABLE ACCESS FULL| A | 2 | 12 | 3 (0)| 00:00:01 |<br />-----------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("A"."A"(+)="B"."A")<br /><br />Note<br />-----<br /> - dynamic sampling used for this statement<br /><br /><br />Statistics<br />----------------------------------------------------------<br /> 0 recursive calls<br /> 5 db block gets<br /> 15 consistent gets<br /> 0 physical reads<br /> 0 redo size<br /> 819 bytes sent via SQL*Net to client<br /> 778 bytes received via SQL*Net from client<br /> 4 SQL*Net roundtrips to/from client<br /> 1 sorts (memory)<br /> 0 sorts (disk)<br /> 1 rows processed<br /><br />SQL></font></pre>Looks ok.Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.com2