Stephen's Oracle Blog

We have a NetApp FAS filer attached via Gigabit Ethernet to a Sun V440 running Oracle databases the files for which are stored on the filer The NetApp volume is mounted under NFS version 3 at $ORACLE_BASE/oradata. Under that directory are 7 directories .snapshot, AXEM, AXER, AXEM_backup, AXER_backup, AXEM_nobackup, AXER_nobackup. .snapshot is where the NetApp filer stores snapshots of the volume. The server runs two databases, AXEM and AXER. The AXEM and AXER directories hold the datafiles (and a small tempfile) for the appropriate database, *_backup holds the RMAN and cold backup sets for the appropriate databases. *_nobackup holds the bulk of the tempfiles (temporary tablespace, used for sorting) for the appropriate database (when we do cold backups we don't bother to backup the tempfiles). For AXEM, AXER and *_backup both du and ls report the same file sizes for both data and tempfiles. For *_nobackup ls reports the correct sizes (as specified in the Oracle data dictionary) but ...

I was just looking at my stat counter analysis for this blog, it's usually a good way to find out if anyone has linked to me. From the results it looks like the vast majority of people (around 97%) find this blog through a search engine, virtually all (over 99%) through Google. Most common search strings are or include " ORA-07445 ", " opmnctl failed to start process " and " ORA-01031 ".

During his presentation on " Inside RAC " Julian Dyke appeared to suffer laptop difficulties so asked for 4 volunteers to assist in a demonstration. Being the helpful soul (i.e. idiot) that I am, I volunteered. Mr Dyke did seem remarkably wellprepared in terms of props, almost as if he had been expecting his laptop to have problems :-)! Photographic evidence (courtesy of Connor McDonald) can be found here and here . I'm the fat, ugly, balding, bearded one on the far left, aka Node 1. Node 2 was Babbette Turner-Underwood , Node 3 (aka Master) was Murali Vallath and Node 4 was Carel-Jan Engel .

A question just appeared on the Oracle-L mailing list about whether there are any issues with using histograms. I've seen a number of presentations over the last couple of years that have warned about issues where you have histograms on highly skewed data and use bind variable (or have cursor_sharing=force, which synthesises bind variable from literals in queries), so I responded: On 19/11/06, A Joshi wrote: > Hi, > About use of histograms : I think histograms are useful for indexes on > columns that are not very selective. However I came across note 1031826.6 on > metalink. About maintenance and space cost. I think space cost is negligible > and can be ignored. About maintenance : does it mean statistics need to be > gather often? Or does it mean some other cost. > > Question : Is there any other overhead or any other negative impact of using > histograms? One downside I've been hearing a lot about over the last couple of years results from Bind ...

Today was the first day of the United Kingdom Oracle User Group 2006 conference . The day itself was pretty good, lots of good talks (more on that later), but I ran into a couple of issues in the evening. The first issue was when I went into the Handmake Hamburger just accross the canal from the ICC , I was alone and the waitress/Maitre d' said they didn't have any free tables suitable for one person (looking around they had several free tables, just they were for 2, 4 or more people) so after expressing my disatisfaction and feeling about the poor quality of service I went accross Broad Street to Walkabout and had a Kangaroo Burger. The second issues was that I had hoped to go to the bloggers' meetup at All Bar One. Although I blog about Oracle I'm not sure if I qualify as a true Oracle Blogger so when everyone disapeared upstairs to a private party (UKOUG had laid on some sort of 'Thank You' event for the volunteers) I didn't follow. I know I'...

I've just gotten the verbal OK from my line manager to attend the Management and Infrastructure SIG meeting on 4th October . Most of the opresentations look like they will be both interesting and useful. My employer is looking to introduce ITIL so the "Can it help me? What are the pitfalls?" presentation could be useful, similarly the DBA/Database ratio presentation is highly relevant to my current environment. The VLDB and Datawarehouse talks should be useful as some of ourdatabases are growing to be pretty huge, partly just datagrowth but also because we're getting situations where two or more separate applications (each with their own database) are being collapsed into one, total data volume has probably fallen but the data is now all in one place rather than being spread over two or more servers. There's currently a big discussion going on about where the DBAs should sit in the organisation. In the background to the discussion are questions about what a DB...

These are the sessions I'm planning on attending at this year's UKOUG conference: 08:00 - 17:45 Registration Open 09:15 - 09:30 Introduction by Ronan Miles, UKOUG Chairman 09:30 - 10:30 Opening Technical Keynote: "What's Coming Next?" by Tom Kyte, Oracle 10:30 - 11:00 Coffee Break 11:00 - 12:00 Accountability for System Performance (introducing Six Sigma quality in Oracle software performance) Mr Cary Millsap Hotsos Enterprises, Ltd. Hall: 12:10 - 12:55 Moving lots of data quickly Mr Connor McDonald Independent Hall: 12:55 - 13:50 Lunch 13:50 - 14:35 Understanding Logical I/O Mr Daniel Fink 7S Consulting, Inc. Hall: 14:45 - 15:30 All Oracle DBAs have to know about Unix Memory Monitoring Mr Jurijs Velikanovs "IT Alise" Ltd Hall: 15:30 - 16:00 Coffee 16:00 - 16:45 Everything you wanted to know about indexes Mr Ben Bor Acuma Solutions Hall: 16:55 - 17:40 An introduction to SQL*Trace, TKPROF and Execution Plans Mr David Kurtz Go-Faster C...

A thought just crossed my mind. I wonder if there is a market for short (one day) task oriented training for the sort of things that aren't normally covered in general courses. For example I'm aware that there are training courses that mention standby databases and certainly there are courses that cover RMAN, at least for basic backup and recovery. I'm not aware of any that actually take you through setting up a standby database, with a hands on practicum, or that covers things like cloning a database with RMAN. These are things that DBAs might be required to do. Sure you can read up in the documentation and, if you are lucky enough to have the kit, try out but might there be a market for, say, a one day course in how to use RMAN to create a clone database and then convert it to a standby for the database you've cloned? Or something similar with other such tasks ("Your first RAC cluster")? I'm sure that there have been user group and technology day...

Doug Burns recently posted in his journal a list of common search terms that people have used where his journal appeared in the results . I figured I'd post mine (according to Statcounter.com). Fortunately (or unfortunately) mine are not anywhere near as exciting as his. Perc. Search Term 5.80% ora-07445 4.35% orapwd invalid username password 2.90% exception signal: 11 oracle 2.90% alter user sys 2.90% move tempfile oracle 8 2.90% ora-01031 sysdba 1.45% oracle password file orapwd 1.45% htmldb_public_user change password 1.45% htmldb startup 1.45% oracle 10 bind parameters ora-07445 1.45% address not mapped to object ora-07445 1.45% failed to start a managed process after the maximum retry limit 1.45% failed to start a managed process htmldb 1.45% decode(true in oracle 1.45% 9.2.0.7 patchset filename 1.45% opmnctl startall htmldb 1.45% sql number exists oracle htmldb 1.45% opmnctl: starting opmn managed processes 1.45% 3440097 oracle 1.45% shutdown immediate oracle insufficient 1...

This morning someone passed me a link to " The Eight Fallacies of Distributed Computing ". Looks pretty accurate. I'd say that many of them are doubly true of RAC .

Just recieved the following email via one of the Oracle DBA lists I'm on: Hello, I am XXX from Singapore.I am also an Oracle Certified Professional (OCP).Could I know size of any database and what's the size of SGA for that Database and how long it take to complete Logical Backup and Physical Backup.I will be awiting for ur reply. Thanks in Advance Regards XXXX Names have been removed to protect the guilty. Now, I have no particular problems with answering beginner questions (I've asked a fair few myself), even though the list this email appeared on is suposedly an experienced DBA list, not a newbie list. I'm quite happy to ignore the poor standard of English, the questioner probably doesn't speak English as their first language (although in Eric S Raymond's guide to Newbies, on how to ask questions, he does state that they should be asked in clear, grammatical, correctly-spelled language ). The thing is the questioner is an OCP, or at least claims to be, and...

Simon Kelsey commeted on one of my earlier entries on this subject that x$kzsrt may be involved: SQL> select * from v$fixed_view_definition where view_name='GV$PWFILE_USERS'; VIEW_NAME ------------------------------ VIEW_DEFINITION -------------------------------------------------------------------------------- GV$PWFILE_USERS select inst_id,username,decode(sysdba,1,'TRUE','FALSE'), decode(sysoper,1,'TRUE ','FALSE') from x$kzsrt where valid=1 and username != 'INTERNAL' I then got to thinking, does the 'alter user...;' just alter the copy of the password in memory or does it update the password file as well? I constructed a little test case. First I opened two sessions on the server as the Oracle software owner user (i.e. 'oracle'). In the first I connected to the database as 'sys' using the existing SYSDBA password and then changed the password using the 'alter user...;' method: $ sqlplus /nolog SQL...

QUESTION ========= 1) Does Oracle cache the SYSDBA password? 2) Is there a way to force it to re-read the password file to pick up a changed password without havi ng to shutdown the database? RESEARCH ========= Note.1029539.6 - UNIX How to Set up the Oracle Password File Note 185703.1 - How to Avoid Common Flaws and Errors Using Passwordfile Note 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues ANSWER ======= 1) Yes. this is cached. The passwordfile is read only at the beginning, when the instance is started. 2) There is no way to force Oracle read the passwordfile, but one can use : alter user sys identified by ; After this the "cache" should be changed to the new password. Guess that answers that question then. I've double checked the course materials from the Oracle DBA courses I've been on and they all say the way to change the SYSDBA password is to use orapwd, none of them mention using 'alter user...'.

A couple of days ago I posted an entry about an issue I'd run into reseting the SYSDBA password . Today I noticed my hits had jumped, checking my refer log I noticed a lot from Peter Finnigan's blog, he'd posted an entry linking to the post . In the entry he says that, if true, it was interesting. I don't know about interesting, but it's definately a true record of what happened. I'd be suprised if I'm the first person to run into this issue. I did recieve a comment to the entry (from an anonymous user) saying that you have to shutdown the database before recreating the password file. I don't recall ever reading that or hearing it mentioned on a course, could be true or it could be one of the many Oracle myths going around. I found an example on orafaq.com that does include shutting down the database (after recreating the password file) but also talks about changing REMOTE_LOGIN_PASSWORDFILE in the init.ora file, which would require a database rest...

I installed HTMLDB (off the 10.2 Companion disk) on Solaris today. Install seemed to run OK and at the end it told me that the HTMLDB instance had started and could be accessed at: http://wintermute:7778 When I tried to access it, no joy. Said it couldn't connect. I tried using opmnctl to check the status and found that opmn itself was up but none of the managed services were. Stopping and starting opmn and all it's managed processes produced: $ opmnctl stopall opmnctl: stopping opmn and all managed processes... $ opmnctl startall opmnctl: starting opmn and all managed processes... ================================================================================ opmn id=wintermute:6200 0 of 1 processes started. ias-instance id=IAS-X-wintermute.6299 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ias-component/process-type/process-set: HTTP_Server/HTTP_Server/HTTP_Server Error --> Process (pid=24282) failed to start a managed pr...

I was just having a play around on one of my test/sandpit databases ("small database on a low power machine that I use for testing out scripts &c, no live data and if the database gets destroyed I just have to wipe out the files and create a new one" type of thing). I'd forgotten the SYSDBA password (for sandpit systems I use the SID as the SYSDBA password so don't bother to write it down, for some reason this time I didn't) but needed it (I'm installing HTMLDB to try it out on that machine) so wanted to change it. I had been getting 1 : SQL> conn sys/sbtest@sbtest as sysdba ERROR: ORA-01017: invalid username/password; logon denied as you might expect. I used orapwd to change the password $ cp orapwsbtest orapwsbtest.20060629.bak $ orapwd file=orapwsbtest password=sbtest entries=16 OPW-00005: File with same name exists - please delete or rename $ rm orapwsbtest $ orapwd file=orapwsbtest password=sbtest entries=16 and thought I would be good to go....

One of the tasks I've recently been given is to collate information about our Oracle and SQLServer databases. Stuff like sizes, init.ora parameters &c. I've developed a script that gets a lot of the stuff we need out of Oracle. Here it is: set echo off set verify off set feedback off set linesize 132 set pagesize 60 set heading off /* This script must be run as SYS (SYSDBA) */ Prompt "Audit Information" select 'Run on: '||to_char(sysdate, 'HH24:MI DD-MON-YYYY') from dual / prompt prompt set heading on column name heading "Database Name" select name from v$database / prompt column banner heading "Database Version Information" select banner from v$version / prompt prompt column log_mode format a16 heading "Archivelog mode" select log_mode from v$database / prompt prompt column option_param format a70 heading "Option" prompt "Options Installed" select parameter option_param from v$option where value=...

Finally got a response from Oracle, turned out they had put it with their US office who don't start work until 13:00 our time. The eventual response was: 23-MAY-06 14:49:43 GMT DATA COLLECTED =============== On alert.log we can see some: ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to obj ect] [0x000000000] [] [] Trace file uploaded shows: /opt/oracle/app/oracle/admin/dchr/udump/dchr_ora_5662.trc Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production JServer Release 9.2.0.6.0 - Production *** SESSION ID:(37.1317) 2006-05-18 15:34:04.409 Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04] *** 2006-05-18 15:34:04.410 ksedmp: internal or fatal error ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to object] [0x000000000 ] [] [] Current SQL statement for this session: select all count(dm_type.r_object_id) "the_cou...

Late yesterday one of our projects passed me information abvout an ORA-07445 error they'd had earlier in the day, apparently this is stopping one of the modules in their application to stop working. Why they couldn't have told me about it earlier I don't know, I would have had time to raise a Service Request with Oracle during our maintenence hours (the why we don't have 24x7 cover is a rant for another time). I raised a call with Oracle before I went home but there's been no updates on it as of yet, other than a note saying they will update it when they have some information (fair enough I suppose). This long silence is worrying, maybe it's something serious? Here's the header of the trace file: *** 2006-05-19 10:57:39.159 *** SESSION ID:(21.4255) 2006-05-19 10:57:39.158 Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04] *** 2006-05-19 10:57:39.159 ksedmp: internal or fatal error ORA-0744...

Just had a call from a recruitment consultancy offering me an interview with Barclay's bank for a DBA role looking after their share trading systems. Unfortunately it was a production support only role (aka DataBase Baby Sitting), which would bore me silly, so I had to turn it down. The consultant said he'd got my CV off monster.co.uk. I only put my CV up there last Thursday and this is the third recruitment consultant to phone me having seen it there. Fortunately I'm in the position right now where I have a job I can stand so I can afford to be picky about which jobs I go for, unfortunately all the jobs that seem to be coming through are ones that I either couldn't do (I can't drive so any jobs I can't access via public transport are out) or wouldn't want to do (e.g. the Barclay's one above).

Anyone else use Linked-In ? It's kinda a social networking type of site but business/job seeking oriented. The idea being that you might not know anyone who has the skills you need for a job you have on offer or who can offer a job that would suit your skills but someone that you know might know someone or someone you know might know someone who knows someone &c. Of course it does rely on you actually knowing people and knowing that they have a profile on Linked-In. My profile is at : http://www.linkedin.com/pub/0/131/a3b A few people I know are on there but it just seems so crass to invite someone to link to you.

Had a problem last week. About 20 past 4 in the afternnon (I officially finish at 4pm) on Thursday (5th Jan)I had an email telling me that users couldn't run reports on a particular database. This database sits behind an OLTP app written in Oracle Forms 6i and connects, via a database link, to another database which holds metadata for an instance of Documentum (a document management system from EMC). Users login as themselves identified from their network logon and have permissions on the application schema according to their post. It turned out that the reports that wouldn't run were ones that accessed the remote database. The database link logs onto the remote database as a user which has permissions on the Documentum schema, which is owned by a different user. I had, that afternoon, done some work on the remote database to give the username used by the database link select permission on some views that had been created since the link was set up. After much working of th...