Don Seiler

Today is my last day at my current employer. It has been an incredible learning experience, and I’ll be forever grateful to the man who gave me the opportunity almost 7 years ago having zero knowledge of either Oracle or database administration.

I have accepted an offer from noted consulting firm The Pythian Group to join their staff of DBAs doing remote administration. This new position should give me exposure to a diverse set of client environments and configurations and a chance to grow in this career path and learn from some of the best in the business. I will be flying out on Sunday for a 3-week stint at the worldwide headquarters in Ottawa, Ontario, Canada to immerse myself in the Pythian way. If I have free time with my nights I hope to finish porting LeagueSite to drupal.

I’ll also try to get more regular with the blogging. Things have been a bit hectic as of late.

When you copy your backup files onto disk for RMAN to use for practicing cold-metal restore/recovery, make sure that they are at least visible to the oracle OS user.  It doesn’t help to have them owned by root with perm 640.

Thank me later.

This post was promised long ago, and I apologize for the tardiness. Some of you may recall my whining about seemingly unexplainable instance hanging since migrating our database to 64-bit hardware in September. Well, after some back and forth and hand-offs from one rep to another, we finally were given a possible explanation: Bug 4367986. The summary of the bug is “bind peeked parallel cursors do not share.” This basically means that parallel queries that use bind variables won’t share cursors. Not only does this defeat the purpose of using the bind variables, but it creates a new cursor for each parallel process. After a while, your cursor count will go up, just as mine did:

select sql_id, count(*)
from v$sql_shared_cursor
where bind_peeked_pq_mismatch='Y'
group by sql_id;

SQL_ID          COUNT(*)
------------- ----------
f3u64ru922snx        520
ckha07wkfaf8v          5
9g26upcqjh8kp          1
gdnga6d26vf4g         15

While I wasn’t able to choke and hang the instance in development, I was able to drive the count up as we saw in production.

We probably didn’t see this before our x86_64 migration because on our 32-bit instance, our parallel_max_servers was only set to 16. After migrating to the new hardware it was raised to 80 based on “the formula”. Dropping it to 0 obviously prevented the problem from coming up as well.

The bug is reportedly fixed in 10.2.0.4, which wasn’t released at the time we were finishing up the SR. There was only a one-off patch for 10.2.0.3, meaning we had to upgrade from 10.2.0.2. Well we did this, applied the patch, and haven’t had a reoccurrence of the problem since. That query listed above now happily returns no rows.

Of course there was also the issue of using bind variables in queries against partitioned tables. Greg Rahn had this to say:

Using PQ with binds can have other adverse effects, specifically if
the partition key is not provided as a literal. When the partition
key is a bind, the resulting plan will be a KEY-KEY plan (for
pstart/pstop) because w/o a literal value the optimizer can not tell
if there is any partition elimination since the literal value is not
provided at parse time. This often times results in a “wost case”
assumption, thus is it possible to have different plans even when the
bind and literal statements use the same values.

I would speculate that the overhead of parsing literals when using PQ
is minimal compared to the side effects it is causing (due to the bug)
and the potential of suboptimal plans. I personally would never mix
the two.

From Married To The Sea, one of my favorite web comics.

Passed the 1Z0-043 exam today, otherwise known as the OCP exam. Just need some other jazz like my hands-on course requirement form to be processed and then I should get my badge and gun and key to the executive washroom.

Thanks to Bradd Piontek for reminding me of how silly it all is, inspiring me to pass it even more so that I wouldn’t look even more foolish failing a silly exam.

Something new I found out today. When performing an ALTER TABLE statement (in this case to add a column) in another schema for which you have a synonym (public or private), you must refer to it with the schema qualifier, e.g. HR.EMPLOYEES rather than just EMPLOYEES. It seems the DDL statement doesn’t bother itself with synonyms. Probably a good thing, as DDL is nothing to be taken lightly and it’s best to fully spell out what it is you are mucking about with.

In the example, my user has been granted the ALTER ANY TABLE privilege, as well as SELECT on HR.EMPLOYEES. I’ve made a private synonym in my own schema, also named EMPLOYEES. Let’s see what happens:

SQL> alter table employees add foo number;
alter table employees add foo number
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter table hr.employees add foo number;

Table altered.

When I first re-installed my laptop with Fedora 8, I naturally set about installing Oracle 10g. However at the end of the dbca process, the launching of netca failed. Trying to run netca manually yielded this fun error:

[oracle ~]$ netca

Oracle Net Services Configuration:
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
#  SIGSEGV (0xb) at pc=0×004f0ab3, pid=19917, tid=3086797008
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_14-b05 mixed mode)
# Problematic frame:
# C  [libc.so.6+0x6fab3]  index+0×63
#
# An error report file with more information is saved as hs_err_pid19917.log
#
# If you would like to submit a bug report, please visit:
#   http://java.sun.com/webapps/bugreport/crash.jsp
#
/u01/app/oracle/product/10.2.0/db_1/bin/netca: line 190: 19917 Aborted
$JRE -mx64m $SRVM_PROPERTY_DEFS -Dsun.java2d.font.DisableAlgorithmicStyles=true -classpath $CLASSPATH oracle.net.ca.NetCA $*

Well that was no good. I saw some threads with similar subject matter on the OTN forums and followed up. However time passed and then for seemingly no reason at all the next week, netca was working fine. Then a kind gentleman replied asking what my hostname was set to. BAM!

That’s when it dawned on me that the hostname that I had set for this laptop was not in any DNS service available at the time. It was set up in my office in Green Bay, but I wasn’t connected to that in Schaumburg. From home last night I disconnected from the VPN and removed my localhost entry for my hostname. Bingo, netca fails. Re-add the localhost entry, netca works.

I had originally added my hostname to the localhost line in /etc/hosts because gnome-terminal would take forever and day launching without DNS resolution of the hostname, waiting for the lookup to timeout. Apparently this same kind of thing is going on just to launch netca, although it just crashes.

Here is the entry in my /etc/hosts, for example:

127.0.0.1               localhost.localdomain   localhost       foo.example.com foo

Say hello to my meager setup to learn and test DataGuard (maybe RAC someday too). BULGY and BULSTRODE, a couple of old Gateway E-series towers (each 2.80 Ghz, Pentium 4 CPU, 2 GB RAM), running CentOS 5 and (soon to have) Oracle 10.2.0.4.

Click for an equally unimpressive larger version.

BULGY is my old workstation-turned-sandbox for Oracle.  I had been wanting to play with RAC, even though it’s probably overkill for us now.  But we are looking to set up a DataGuard standby, so this would be a better fitting use of the hardware.  I had been bookmarking some of the RAC-via-VMWare guides, but then I saw another black E-series sitting on the shelf in the MIS corner of my office space.  I gave one of the techs my sad puppy-dog eyes and he hooked me up and even bumped up the RAM to match BULGY.  I christened it BULSTRODE in keeping with our naming convention of Thomas the Tank Engine characters.

While in Schaumburg, Illinois last week for the Oracle DBA Workshop II, I was taking some photos on my Motorola RAZR camera phone, in the hopes of posting them for my 5-year-old daughter to see. I needed to clear up some misconceptions, since she was under the impression that I “sleep at the school.” I soon learned, however, that transferring photos from the RAZR one-at-a-time over bluetooth to my Fedora 8 laptop became tedious, and waiting for a response from the GUI file browser was just frustrating. Enter: ObexFTP.

Thanks to a tip from a friend, I found ObexFTP and, in my quest to force myself to learn python, set about crafting a script to do the following:

• Transfer files based on a date (default to today).
• Transfer all files in the directory.

So I’m presenting to you my first stab at it. Some of the hard-codings depend on how the RAZR stores photos in the micro-SD card. If anyone wants to submit enhancements or critiques, I’m all ears. Right now it just works for what I needed it to do.

#!/usr/bin/env python

# Don Seiler, don@seiler.us 

import obexftp, ConfigParser, os
from xml.etree.ElementTree import XML
from optparse import OptionParser
from datetime import date

# This script is dependent on the Moto Razr convention of naming
# pictures in an MM-DD-YYYY_XXXX.jpg format

# Users need to create ~/.obexcopier.ini with these variables defined
# [ObexCopier]
# device = 1A:2B:3C:4D:5E:6F
# channel = 6
# source_dir = /MMC(Removable)/motorola/shared/picture
# dest_dir = /media/pictures

# Read config from ~/.obexcopier.ini
config = ConfigParser.ConfigParser()
config.read(os.path.expanduser('~/.obexcopier.ini'))

# Probably a waste of precious memory to store these again
device = config.get('ObexCopier','device')
channel = config.getint('ObexCopier','channel')
source_dir = config.get('ObexCopier','source_dir')
dest_dir = config.get('ObexCopier','dest_dir')

# Get today for default date
today = date.today().strftime("%m-%d-%y")

# Command-line handling to allow for date
parser = OptionParser()
parser.add_option("-d", "–date", dest="date", default=today, help="Grab pictures from this date, defaults to today [default: %default]",metavar="MM-DD-YY")
parser.add_option("-a", "–all", action="store_true", dest="all", default=False, help="Copy all files, regardless of date [default: %default]")
(options, args) = parser.parse_args()

# Connect to the client
print "Connecting to %s on channel %d" % (device, channel)
cli = obexftp.client(obexftp.BLUETOOTH)
cli.connect(device, channel)

# Get the list of files from the SD card picture dir
if options.all:
        print "Copying all files to disk"
else:
        print "Copying files from %s" % options.date

files_xml = cli.list(source_dir)
folder_listing = XML(files_xml)
files = folder_listing.findall('./file/')
for file in files:
        # Only handle pictures taken on the specified date
        if options.all or file.get('name').startswith(options.date):
                print "Copying %s" % file.get('name')
                data = cli.get(source_dir + '/' + file.get('name'))
                localfile = open(dest_dir + '/' + file.get('name'), 'wb')
                localfile.write(data)
                localfile.close()

# Disconnect and delete the client
cli.disconnect()
cli.delete

Yes it’s been quite a while since I’ve posted.  I do have a couple of tales from the crypt regarding a few Oracle SRs that have come and gone recently that I think should be helpful to many others.  I’ve also discovered that my solution for the UNKNOWN header status in ASM was completely stupid.

So I plan to write about those things and their solutions within the week.  I’m just about completely recovered from some kind of virus that took over my being last week.

A week or so ago, I was working with a developer to find out why a SQL statement he was sending would sometimes work and sometimes return an invalid DATE format error. Part of the troubleshooting led us to examine the actual bind values being received by the server, just to make sure it was the same as being sent. I thought I was onto something when I saw that the fields of TIMESTAMP datatype actually were NULL:

SQL> select name, datatype_string, value_string
from v$sql_bind_capture
where sql_id='0wp5c2a3z82jr'
SQL> /

NAME DATATYPE_STRING VALUE_STRING
---- --------------- ------------
:1   TIMESTAMP       {NULL}
:2   VARCHAR2(32)    3WC
:3   VARCHAR2(32)    001
:4   TIMESTAMP       {NULL}
:5   NUMBER          8429721
:6   NUMBER          206

6 rows selected.

Unfortunately it was like this when the query both failed and succeeded. I banged my head against the wall for 30 more minutes before concluding that it must be a bug. Metalink agreed, and Note 444551.1 details the bug, which is not yet fixed and “should be fixed in 11.2,” affecting versions 9.2 to 11.1. Seems kind of long for something like this to still be out in the open, and it’s very annoying when you really want to see the value of a TIMESTAMP bind variable.

EDIT: DON’T FOLLOW THESE INSTRUCTIONS IF YOU DON’T WANT TO SCREW THINGS UP FOR YOURSELF.  I’LL BE REVISING THESE INSTRUCTIONS WITH THE PROPER SOLUTION IN THE NEAR FUTURE. 

On my lil’ old Gateway tower, I was running CentOS5 and Oracle 10.2.0.3. I set up an ASM instance pretty easily using the dbca, after using ASMLib to provision the disk partition that I carved out for it.

However when I went to put this practice to some “real” use on our development RHEL4 server connected to a SAN, things didn’t want to play so easily. The SA had provided me with two beautiful 900GB RAID 10 LUNs, and ASMLib seemed to provision them fine via “/etc/init.d/oracleasm createdisk”. However when I tried to create the diskgroup, either via dbca or sqlplus, I was presented with an ORA-15072 error:

SQL> CREATE DISKGROUP dgroup1
2      EXTERNAL REDUNDANCY
3      DISK 'ORCL:VOL01','ORCL:VOL02';
CREATE DISKGROUP dgroup1
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15072: command requires at least 1 failure groups, discovered only 0

Querying the v$asm_disk view indicated that all was not quite right with what ASM thought of my disks:

SQL> select state,header_status,substr(name,1,12) Name,free_mb,
     substr(path,1,16) PATH from v$asm_disk;
STATE    HEADER_STATU NAME            FREE_MB PATH
-------- ------------ ------------ ---------- ----------------
NORMAL   UNKNOWN                            0 ORCL:VOL02
NORMAL   UNKNOWN                            0 ORCL:VOL01

That “UNKNOWN” header status is obviously why ASM wouldn’t let me use those disks to create my disk group. The “ORCL:VOL0x” path format was no different than I saw in my CentOS sandbox:

SQL> select state,header_status,substr(name,1,12) Name,free_mb,
     substr(path,1,16) PATH from v$asm_disk;
STATE    HEADER_STATU NAME            FREE_MB PATH
-------- ------------ ------------ ---------- ----------------
NORMAL   MEMBER       VOL1               6662 ORCL:VOL1
NORMAL   MEMBER       VOL2               6301 ORCL:VOL2

So I wasn’t sure what the problem might be. Krish Hariharan on the oracle-l list suggested that perhaps the asm_diskstring parameter needed to be set, as my instance had an empty value for it (both instances did, actually). I set asm_diskstring to prefix the actual location of the disk devices created by ASMLib:

SQL> alter system set asm_diskstring='/dev/oracleasm/disks/VOL*' scope=spfile;

And voila. After setting this, the HEADER_STATUS value in v$asm_disk changed to PROVISIONED and the PATH value was the actual file system path, /dev/oracleasm/disks/VOL01 and /dev/oracleasm/disks/VOL02 in my case. I was then able to use those disks in my disk group as I pleased.I’m still not sure why I needed to set asm_diskstring in the one instance but not in the sandbox. The sandbox of course was just a small desktop tower with plain old IDE disk, where was the development server was connected to a Hitachi SAN. If anyone can answer for sure, I’d love to hear it.

In the future, I’ll just make a habit of always setting it.