Search

OakieTags

Who's online

There are currently 0 users and 35 guests online.

Recent comments

Affiliations

December 2010

Funny Developer Tricks – (substr(cust_id,1,length(:b1))

Ha. This one was a little surprising. I ran across a SQL statement that gets fired off hundreds at a time in rapid succession. Each execution taking several seconds – too long for thousands of executions in a row. The statement looked like this (cleaned up to protect the guilty):

b1 := '10355P034001SGL00066';
b2 := '10355P034001SGL00066';
 
select count(cust_id) 
from customers
where substr(cust_id,1,length(:b1)) = :b2;

What was the developer trying to do? How can we fix it? Your comments are welcomed.

Well it only took Jeremiah a couple of minutes. Yes – it looks like the developer had learned to use the substr function, but not the LIKE operator. Maybe the developer didn’t know you could use the same bind variable twice in the same statement as well. Or maybe the variables could have different values, but that seems unlikely. Anyway, here’s the stats for the before and after.

SYS@FYIDOCS> @fss
Enter value for sql_text: 
Enter value for sql_id: f0n7vkcdhqp3v
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
f0n7vkcdhqp3v       182380728      9,933           9933       7.68       1.89      83.06       11,627 select count(cust_id) from customers
                                                                                                      where substr(cust_id,1,length(:b1)) = :b2
 
 
 
SYS@FYIDOCS> @fss                   
Enter value for sql_text: %test f0n7vkcdhqp3v_3.sql%
Enter value for sql_id: 
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
4j1apzncj8dps       611149136          1              1        .00        .00        .00            8 select count(cust_id) from customers
                                                                                                      where cust_id like :b2||'%'

Delphix

testing code

hello

select * from dual union all
select * from dual union all
select * from dual;
pre test

code test
bye

VirtualBox 4.0.0 Released…

VirtualBox 4.0.0 Released. I first saw it mentioned here. Got my download and I’m ready to go. :)

Cheers

Tim…

Adding user equivalence for RAC the easy way

This is the first time I am setting up a new 11.2.0.2 cluster with the automatic SSH setup. Until now, I ensured user equivalence by copying ssh RSA and DSA manually to all cluster nodes. For two nodes that’s not too bad, but recently someone asked a question around a 28 (!) node cluster on a mailing list I am subscribing to. So that’s when I think the whole process  gets a bit too labour intensive.

So setting up user equivalence using a script may be the solution. You can also use OUI to do the same, but I like to run “cluvfy stage -post hwos” to check everything is ok before even thinking about executing ./runInstaller.

Here’s the output of a session, my 2 cluster nodes are acfsprodnode1 and acfsprodnode2 (yes, they are for 11.2 ACFS replication and encryption testing). I am using the grid user as the owner of Grid Infrastructure, and oracle to own the RDBMS binaries. Start by navigating to the location where you unzipped the Grid Infrastructure patch file. Then change into directoy “sshsetup” and run the command:


[grid@acfsprdnode1 sshsetup]$ ./sshUserSetup.sh
Please specify a valid and existing cluster configuration file.
Either user name or host information is missing
Usage ./sshUserSetup.sh -user  [ -hosts "" | -hostfile  ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile  ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]

Next execute the command, I opted for option noPromptPassphrase, as I don’t use them for the key.

[grid@acfsprdnode1 sshsetup]$ ./sshUserSetup.sh -user grid -hosts "acfsprdnode1 acfsprdnode2" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2010-12-22-15-39-18.log
Hosts are acfsprdnode1 acfsprdnode2
user is grid
Platform:- Linux
Checking if the remote hosts are reachable
PING acfsprdnode1.localdomain (192.168.99.100) 56(84) bytes of data.
64 bytes from acfsprdnode1.localdomain (192.168.99.100): icmp_seq=1 ttl=64 time=0.017 ms
64 bytes from acfsprdnode1.localdomain (192.168.99.100): icmp_seq=2 ttl=64 time=0.019 ms
64 bytes from acfsprdnode1.localdomain (192.168.99.100): icmp_seq=3 ttl=64 time=0.017 ms
64 bytes from acfsprdnode1.localdomain (192.168.99.100): icmp_seq=4 ttl=64 time=0.017 ms
64 bytes from acfsprdnode1.localdomain (192.168.99.100): icmp_seq=5 ttl=64 time=0.018 ms

--- acfsprdnode1.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 3999ms
rtt min/avg/max/mdev = 0.017/0.017/0.019/0.004 ms
PING acfsprdnode2.localdomain (192.168.99.101) 56(84) bytes of data.
64 bytes from acfsprdnode2.localdomain (192.168.99.101): icmp_seq=1 ttl=64 time=0.331 ms
64 bytes from acfsprdnode2.localdomain (192.168.99.101): icmp_seq=2 ttl=64 time=0.109 ms
64 bytes from acfsprdnode2.localdomain (192.168.99.101): icmp_seq=3 ttl=64 time=0.324 ms
64 bytes from acfsprdnode2.localdomain (192.168.99.101): icmp_seq=4 ttl=64 time=0.256 ms
64 bytes from acfsprdnode2.localdomain (192.168.99.101): icmp_seq=5 ttl=64 time=0.257 ms

--- acfsprdnode2.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.109/0.255/0.331/0.081 ms
Remote host reachability check succeeded.
The following hosts are reachable: acfsprdnode1 acfsprdnode2.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost acfsprdnode1
numhosts 2
#ff0000;">The script will setup SSH connectivity from the host acfsprdnode1 to all
#ff0000;">the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host acfsprdnode1
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
#ff0000;">yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/grid/.ssh/config, it would be backed up to /home/grid/.ssh/config.backup.
Removing old private/public keys on local host
Running SSH keygen on local host with empty passphrase
Generating public/private rsa key pair.
Your identification has been saved in /home/grid/.ssh/id_rsa.
Your public key has been saved in /home/grid/.ssh/id_rsa.pub.
The key fingerprint is:
de:e3:66:fa:16:e8:6e:36:fd:c5:e3:77:75:07:9a:b0 grid@acfsprdnode1
Creating .ssh directory and setting permissions on remote host acfsprdnode1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host acfsprdnode1. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host acfsprdnode1.
Warning: Permanently added 'acfsprdnode1,192.168.99.100' (RSA) to the list of known hosts.
grid@acfsprdnode1's password:
Done with creating .ssh directory and setting permissions on remote host acfsprdnode1.
Creating .ssh directory and setting permissions on remote host acfsprdnode2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host acfsprdnode2. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host acfsprdnode2.
Warning: Permanently added 'acfsprdnode2,192.168.99.101' (RSA) to the list of known hosts.
grid@acfsprdnode2's password:
Done with creating .ssh directory and setting permissions on remote host acfsprdnode2.
Copying local host public key to the remote host acfsprdnode1
The user may be prompted for a password or passphrase here since the script would be using SCP for host acfsprdnode1.
grid@acfsprdnode1's password:
Done copying local host public key to the remote host acfsprdnode1
Copying local host public key to the remote host acfsprdnode2
The user may be prompted for a password or passphrase here since the script would be using SCP for host acfsprdnode2.
grid@acfsprdnode2's password:
Done copying local host public key to the remote host acfsprdnode2
cat: /home/grid/.ssh/known_hosts.tmp: No such file or directory
cat: /home/grid/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user grid.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~grid or ~grid/.ssh on the remote host may not be owned by grid.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the /sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--acfsprdnode1:--
Running /usr/bin/ssh -x -l grid acfsprdnode1 date to verify SSH connectivity has been setup from local host to acfsprdnode1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Wed Dec 22 15:40:10 GMT 2010
------------------------------------------------------------------------
--acfsprdnode2:--
Running /usr/bin/ssh -x -l grid acfsprdnode2 date to verify SSH connectivity has been setup from local host to acfsprdnode2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Wed Dec 22 15:40:10 GMT 2010
------------------------------------------------------------------------
SSH verification complete.
[grid@acfsprdnode1 sshsetup]$ ssh acfsprdnode1 hostname
acfsprdnode1
[grid@acfsprdnode1 sshsetup]$ ssh acfsprdnode2 hostname
acfsprodnode2
[grid@acfsprdnode1 sshsetup]$

Nice! That’s a lot of work taken away from me, and I can start runing cluvfy now to fix problems before OUI warns me about shortcomings on my system.

You should note that per the above output, the script only distributes the local ssh keys to the remote hosts. When in OUI’s cluster node addition screen (6 of 16 in the advanced installation) you still need to click on the “SSH Connectivity” button and then on “Setup” after providing username and password to establish cluster wide user equivalence.

I don’t think there’s a punch-line scheduled, is there?

I’ve been fighting a really bizarre issue with Oracle Warehouse Builder 11.1.0.7 this last month or so. It looks like it finally got resolved today. The resolution has implications for other people so I’m putting it up here, partly for them and partly so I don’t forget the oddity again. The warehouse Builder architecture is [...]

Guest Post: Gustav's New Year Recommendations

Over the past year or so I've had the pleasure of working with a development lead who is younger, more handsome and more switched on than I could ever hope to be. That's true of most young development leads of course, but Gustav Andersson excels in most areas. He is one of the few young* men who I can be bothered debating Agile with, far less agree to subject myself to it on a continuous (< see what I did there?) basis. But let's not start talking about Agile - I've had enough of that for several life-times.

No, this post is about some of the useful tools and processes that Gustav has used over the past year. He sent a mail to all his geek friends, mistakenly including me, to see if they had any suggestions of their own. As I am an unreconstructed dinosaur who can just about manage Tripit, the seeds fell on rather stoney ground. But I do have a blog and I know I have some readers who are similarly focussed on tools to improve your life.

So here are Gustav's recommendations. He does actually have some good ideas** occasionally ;-) and I'm sure he would be interested in everyone else's, via comments.

* Of course, he probably thinks he is old. He's not, it just feels like that for now. He'll realise he's going to feel much older.
** For example Glympse. That's a family favourite these days. Nirvana HQ is working out ok so far, too.


This year, I've done a lot of tweaking of my tools and processes for
organizing my life and getting work done. I'm a geek through and through, and so
I use a lot of technology to do all of this. Well, I bet that I am not alone in
this.
It struck me that I have a lot of geek friends (what ever happened to that
Geek Pride Parade anyway?) and that you probably have a lot of really good
systems set up on your end too. I would love to know if there is something you
do in your day-to-day life that works really well in making whatever you do more
efficient or just plain old fun.
So below are my tips and tricks:
BACKING UP YOUR DATA:
I use Mozy (http://mozy.co.uk) to back up
my data. It runs in the background and syncs selected folders to a remote site.
My selected folders are all of my hard drives! Why not? Mozy doesn't have a
space limit to their service, and at £5/month, it's pretty cheap.
MUSIC:
I mainly use Spotify to listen to music. I have a paid for account so I get
no adverts. It works well and so far it covers most of the music that I
want.
PODCASTS AND AUDIO BOOKS:
I listen to a lot of podcasts and audio books. I would recommend anyone to
give those a go. It makes washing up dishes and hanging up laundry tolerable. As
for Audiobooks, I would recommend Audible (http://www.audible.co.uk). You pay £8/month
and get a book each month to own for life.
If you are thinking of signing up to Audible, make sure you get a 'first
time customer' deal like £50 off Amazon or the first book for free.
PHOTO EDITING:
Picasa (http://picasa.google.com/)
is really good for organising and doing common editing tasks. It is also nicely
integrated with the Google suite of stuff like your contacts etc.
PHOTO BOOKS:
For photo books, nothing beats Blurb (http://www.blurb.com/). Great prices and great
results. The software is easy to use. No negatives.
PANORAMA PHOTOS:
If you are into photography, and you want an easy way to stitch together
photos into sweet panoramas, check out ICE (http://research.microsoft.com/en-us/um/redmond/groups/ivm/ICE/).
It's free and much easier to use than other systems I've tried.
PASSWORDS:
Don't use the same password on all your sites! You are begging to be robbed
of everything from pride to cash. Develop a system to derive the password from
the page name. If someone finds out the passwords, they still don't know your
system and hence they can't get access to your other sites.
I also use LastPass (http://lastpass.com/) to automatically log me in
to various sites. Works a charm.
DISTRACTION FREE WRITING:
If you write a lot then you should give a thought to your writing
environment. Q10 (http://www.baara.com/q10/) is a neat
writing application. Very simple, totally distraction free and if you choose,
some lovely typewriting noises. It does saving as your go along and in general,
it works well. It is also extremely fast to start-up and is therefore a really
good companion to the next point.
DISTRACTION FREE WRITING ON THE WEB:
If you are writing something on the web, and it is a longer piece, then you
can get really tired of writing on the site directly. There are far too many
distractions there. With various plug-ins your can ask your browser to start
your favourite text editor, let you write there and when you quit, the text is
copied over to the website.
Firefox has got a plug-in for this called "It's All Text" (https://addons.mozilla.org/en-US/firefox/addon/4125/).
If you are using Chrome, then you need to install a separate application
called Listary (http://www.listary.com/).
You can then just press Win+A to edit in an external editor.
SPELLING AND GRAMMAR CHECK ON WEB:
After the Deadline (http://afterthedeadline.com/) is really
good, and it exist for both Firefox and Chrome.
TASK ORGANISATION:
I follow the 'Get Things Done' methodology for, well, getting stuff done.
For an on-line implementation of GTD, check out Nirvana (http://www.nirvanahq.com/). They have a new
version which is all HTML5 snazzy, but you need to opt-in for that version.
Check out their forum for details or just ask me.
INFORMATION CAPTURE AND ORGANIZATION
Work Flowy (http://workflowy.com/) is
an amazing list making web app. It has a clean interface, can cope with a huge
amount of information (as much as you can throw at it) and is entirely keyboard
friendly so you can give that mouse a rest.
SENDING LARGE FILES:
I use Send Space (http://www.sendspace.com/) for that.
GEOTAG:
If you have a GPS tracker when you are snapping away on holidays, and you
want to get the coordinates into your photos, then I recommend using this java
based tool for doing so (http://geotag.sourceforge.net/).
Right, that was about all I had. Like I said, I would love to hear if you
have got similar tips. Come on, share; you know you want to.
Merry Christmas and a productive New Year!
Gustav

Staying fresh

I often spend a few minutes on “random browsing” on the internet, keeping an eye open for new ideas; and it’s interesting how often I find new technology being applied to an old requirement. Here’s one I found recently: how do you recover a user that’s been dropped by accident?

If you’re using “database flashback” technology then you have an option to make it a lot simpler than it used to be.

Yodel-ay

Online, service matters. In common with many households we have been receiving parcels from online stores over the last little while. Today, unfortunately, we were all out when the parcel delivery from Yodel arrived. I wasn’t familiar with Yodel, but it appears it is one of those rebrandings that corporations decide are a good idea [...]

Wheeew, I am now a RedHat Certified Engineer!

A couple of weeks ago, RedHat announced the general availability of RHEL6… also effective on this release is the change on their certification offering. RHCT will now be replaced by RHCSA (Red Hat Certified System Administrator), and if you would like to be RHCE on RHEL6 regardless of your certification on RHEL5 you still have to go through the RHCSA exam.. and then once you pass.. you are then allowed to take RHCE exam for RHEL6. More details here: RHCSA, RHCE